-- Rockview University Official Request Routing (PostgreSQL)
-- Fixed workflow:
-- Staff -> Registrar (logged-in campus) -> DVC1 -> Accountant -> CFO
-- CFO may route back to Accountant for correction/review.
-- Safe to run more than once. PHP endpoints also create/upgrade these tables.

CREATE TABLE IF NOT EXISTS workflow_requests (
    id BIGSERIAL PRIMARY KEY,
    requester_user_id BIGINT NOT NULL,
    requester_name VARCHAR(255) NOT NULL,
    requester_email VARCHAR(255) NOT NULL,
    requester_position VARCHAR(180),
    requester_campus VARCHAR(180),
    recipient_position_id BIGINT,
    recipient_position VARCHAR(180) NOT NULL,
    target_campus VARCHAR(180) NOT NULL,
    current_stage VARCHAR(32),
    reference VARCHAR(240) NOT NULL,
    body TEXT NOT NULL,
    category VARCHAR(80) NOT NULL DEFAULT 'General',
    priority VARCHAR(20) NOT NULL DEFAULT 'normal',
    amount NUMERIC(14,2),
    table_data JSONB,
    status VARCHAR(24) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','approved','declined','cancelled')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finalized_at TIMESTAMPTZ
);

ALTER TABLE workflow_requests ADD COLUMN IF NOT EXISTS current_stage VARCHAR(32);
ALTER TABLE workflow_requests ADD COLUMN IF NOT EXISTS table_data JSONB;

CREATE TABLE IF NOT EXISTS workflow_request_routes (
    id BIGSERIAL PRIMARY KEY,
    request_id BIGINT NOT NULL REFERENCES workflow_requests(id) ON DELETE CASCADE,
    action VARCHAR(40) NOT NULL,
    actor_user_id BIGINT NOT NULL,
    actor_name VARCHAR(255) NOT NULL,
    actor_position VARCHAR(180),
    actor_campus VARCHAR(180),
    from_position VARCHAR(180),
    from_campus VARCHAR(180),
    to_position_id BIGINT,
    to_position VARCHAR(180),
    to_campus VARCHAR(180),
    remarks TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS workflow_request_notifications (
    id BIGSERIAL PRIMARY KEY,
    request_id BIGINT NOT NULL REFERENCES workflow_requests(id) ON DELETE CASCADE,
    recipient_user_id BIGINT NOT NULL,
    notification_type VARCHAR(50) NOT NULL,
    title VARCHAR(220) NOT NULL,
    body TEXT NOT NULL,
    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    read_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS workflow_office_holders (
    id BIGSERIAL PRIMARY KEY,
    position_id BIGINT NOT NULL,
    position_title VARCHAR(180) NOT NULL,
    campus VARCHAR(180) NOT NULL,
    user_id BIGINT NOT NULL,
    assigned_by_user_id BIGINT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(position_id, campus)
);

CREATE INDEX IF NOT EXISTS idx_workflow_requests_requester ON workflow_requests(requester_user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_workflow_requests_target ON workflow_requests(target_campus, recipient_position, status);
CREATE INDEX IF NOT EXISTS idx_workflow_requests_stage ON workflow_requests(current_stage, target_campus, status);
CREATE INDEX IF NOT EXISTS idx_workflow_routes_request ON workflow_request_routes(request_id, created_at);
CREATE INDEX IF NOT EXISTS idx_workflow_routes_actor ON workflow_request_routes(actor_user_id, request_id);
CREATE INDEX IF NOT EXISTS idx_workflow_notifications_user ON workflow_request_notifications(recipient_user_id, is_read, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_workflow_office_holders_user ON workflow_office_holders(user_id, is_active, campus);
CREATE INDEX IF NOT EXISTS idx_workflow_office_holders_position ON workflow_office_holders(position_id, campus, is_active);
