-- Rockview University asset management tables (PostgreSQL).
-- Run this script once in pgAdmin 4 using the application database owner.

CREATE TABLE IF NOT EXISTS assets (
    id BIGSERIAL PRIMARY KEY,
    asset_tag VARCHAR(50) NOT NULL,
    asset_name VARCHAR(180) NOT NULL,
    category VARCHAR(100) NOT NULL,
    description TEXT,
    campus VARCHAR(180) NOT NULL,
    location VARCHAR(240),
    building VARCHAR(160),
    room VARCHAR(100),
    serial_number VARCHAR(140),
    manufacturer VARCHAR(140),
    model VARCHAR(140),
    purchase_date DATE,
    purchase_cost NUMERIC(12,2),
    condition_status VARCHAR(20) NOT NULL DEFAULT 'good'
        CHECK (condition_status IN ('new','good','fair','poor','damaged')),
    status VARCHAR(20) NOT NULL DEFAULT 'available'
        CHECK (status IN ('available','assigned','maintenance','retired','lost')),
    created_by_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_assets_asset_tag UNIQUE (asset_tag)
);
CREATE INDEX IF NOT EXISTS idx_assets_campus_status ON assets(campus,status);
CREATE INDEX IF NOT EXISTS idx_assets_name ON assets(asset_name);
ALTER TABLE assets ADD COLUMN IF NOT EXISTS location VARCHAR(240);
UPDATE assets
SET location = NULLIF(CONCAT_WS(' / ', NULLIF(building, ''), NULLIF(room, '')), '')
WHERE NULLIF(location, '') IS NULL;

CREATE TABLE IF NOT EXISTS asset_assignments (
    id BIGSERIAL PRIMARY KEY,
    asset_id BIGINT NOT NULL REFERENCES assets(id) ON DELETE RESTRICT,
    staff_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    assigned_by_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    assigned_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    due_back_date DATE,
    assignment_note TEXT,
    returned_at TIMESTAMPTZ,
    returned_by_user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    condition_on_return VARCHAR(20)
        CHECK (condition_on_return IS NULL OR condition_on_return IN ('new','good','fair','poor','damaged')),
    return_note TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_asset_one_current_assignment
    ON asset_assignments(asset_id) WHERE returned_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_asset_assignments_staff
    ON asset_assignments(staff_user_id,returned_at,assigned_at DESC);

CREATE TABLE IF NOT EXISTS asset_maintenance (
    id BIGSERIAL PRIMARY KEY,
    asset_id BIGINT NOT NULL REFERENCES assets(id) ON DELETE RESTRICT,
    reported_by_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    opened_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMPTZ,
    issue TEXT NOT NULL,
    work_done TEXT,
    cost NUMERIC(12,2),
    condition_after VARCHAR(20)
        CHECK (condition_after IS NULL OR condition_after IN ('new','good','fair','poor','damaged'))
);
CREATE INDEX IF NOT EXISTS idx_asset_maintenance_open
    ON asset_maintenance(asset_id,closed_at,opened_at DESC);
CREATE UNIQUE INDEX IF NOT EXISTS uq_asset_one_open_maintenance
    ON asset_maintenance(asset_id) WHERE closed_at IS NULL;

-- An append-only history of inventory and custody operations.
CREATE TABLE IF NOT EXISTS asset_events (
    id BIGSERIAL PRIMARY KEY,
    asset_id BIGINT NOT NULL REFERENCES assets(id) ON DELETE RESTRICT,
    actor_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    action VARCHAR(40) NOT NULL,
    summary TEXT NOT NULL,
    details JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_asset_events_asset ON asset_events(asset_id,created_at DESC);
