-- Rockview University leave workflow upgrade
-- QR-verifiable endorsements + HR annual leave ledger + 2-day monthly accrual.
-- The PHP backend also creates/upgrades these tables automatically.

CREATE TABLE IF NOT EXISTS leave_applications (
    id BIGSERIAL PRIMARY KEY,
    applicant_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    applicant_name VARCHAR(255) NOT NULL,
    applicant_email VARCHAR(255) NOT NULL,
    applicant_phone VARCHAR(120),
    personnel_no VARCHAR(120),
    position_title VARCHAR(180) NOT NULL,
    department VARCHAR(180) NOT NULL,
    campus VARCHAR(180) NOT NULL,
    leave_type VARCHAR(60) NOT NULL,
    other_leave_type VARCHAR(180),
    reason TEXT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    expected_return_date DATE NOT NULL,
    requested_days NUMERIC(8,2) NOT NULL DEFAULT 0,
    accrual_rate_month NUMERIC(8,2),
    available_days NUMERIC(8,2),
    previously_taken_days NUMERIC(8,2),
    closing_balance NUMERIC(8,2),
    balance_confirmed_by_user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    balance_confirmed_at TIMESTAMPTZ,
    address_while_on_leave TEXT,
    emergency_contact_name VARCHAR(255),
    emergency_contact_phone VARCHAR(120),
    emergency_contact_relationship VARCHAR(120),
    emergency_contact_address TEXT,
    current_stage VARCHAR(40) NOT NULL DEFAULT 'hod',
    current_recipient_user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    current_recipient_label VARCHAR(180),
    current_recipient_campus VARCHAR(180),
    status VARCHAR(30) NOT NULL DEFAULT 'pending',
    applicant_signature_mark VARCHAR(30) NOT NULL DEFAULT 'QR', -- legacy compatibility only
    applicant_qr_token VARCHAR(64),
    applicant_signed_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    submitted_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    final_decision_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE leave_applications ADD COLUMN IF NOT EXISTS applicant_qr_token VARCHAR(64);
CREATE UNIQUE INDEX IF NOT EXISTS idx_leave_applications_qr_token ON leave_applications(applicant_qr_token) WHERE applicant_qr_token IS NOT NULL;

CREATE TABLE IF NOT EXISTS leave_routes (
    id BIGSERIAL PRIMARY KEY,
    leave_id BIGINT NOT NULL REFERENCES leave_applications(id) ON DELETE CASCADE,
    action VARCHAR(50) NOT NULL,
    from_stage VARCHAR(40),
    to_stage VARCHAR(40),
    actor_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    actor_name VARCHAR(255) NOT NULL,
    actor_position VARCHAR(180),
    actor_campus VARCHAR(180),
    to_user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    to_label VARCHAR(180),
    to_campus VARCHAR(180),
    decision VARCHAR(40),
    remarks TEXT,
    signature_mark VARCHAR(30) NOT NULL DEFAULT 'QR', -- legacy compatibility only
    qr_token VARCHAR(64),
    signed_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE leave_routes ADD COLUMN IF NOT EXISTS qr_token VARCHAR(64);
CREATE UNIQUE INDEX IF NOT EXISTS idx_leave_routes_qr_token ON leave_routes(qr_token) WHERE qr_token IS NOT NULL;

CREATE TABLE IF NOT EXISTS leave_notifications (
    id BIGSERIAL PRIMARY KEY,
    leave_id BIGINT REFERENCES leave_applications(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    kind VARCHAR(60) 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
);
ALTER TABLE leave_notifications ALTER COLUMN leave_id DROP NOT NULL;
CREATE INDEX IF NOT EXISTS idx_leave_notifications_user ON leave_notifications(user_id, is_read, created_at DESC);

CREATE TABLE IF NOT EXISTS leave_balances (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    leave_type VARCHAR(60) NOT NULL,
    leave_year INT NOT NULL,
    entitlement_days NUMERIC(8,2) NOT NULL DEFAULT 0,
    carried_forward_days NUMERIC(8,2) NOT NULL DEFAULT 0,
    used_days NUMERIC(8,2) NOT NULL DEFAULT 0,
    adjustment_days NUMERIC(8,2) NOT NULL DEFAULT 0,
    accrual_rate_month NUMERIC(8,2) NOT NULL DEFAULT 2,
    accrual_started_on DATE,
    last_accrual_month DATE,
    updated_by_user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, leave_type, leave_year)
);
ALTER TABLE leave_balances ADD COLUMN IF NOT EXISTS accrual_rate_month NUMERIC(8,2) NOT NULL DEFAULT 2;
ALTER TABLE leave_balances ADD COLUMN IF NOT EXISTS accrual_started_on DATE;
ALTER TABLE leave_balances ADD COLUMN IF NOT EXISTS last_accrual_month DATE;

CREATE TABLE IF NOT EXISTS leave_balance_adjustments (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    leave_year INT NOT NULL,
    amount NUMERIC(8,2) NOT NULL,
    before_balance NUMERIC(8,2) NOT NULL,
    after_balance NUMERIC(8,2) NOT NULL,
    reason TEXT NOT NULL,
    actor_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    actor_name VARCHAR(255) NOT NULL,
    actor_position VARCHAR(180),
    actor_campus VARCHAR(180),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_leave_balance_adjustments_user ON leave_balance_adjustments(user_id, leave_year, created_at DESC);

-- Executive HR visibility and DVC1 account-control support.
-- Senior HR, DVC1, DVC2, CFO, CEO, CAO and COO receive institution-wide
-- HR leave-ledger visibility/actions. Junior HR remains campus-scoped.
ALTER TABLE users ADD COLUMN IF NOT EXISTS account_status VARCHAR(30) NOT NULL DEFAULT 'active';
ALTER TABLE users ADD COLUMN IF NOT EXISTS suspended_at TIMESTAMPTZ NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS suspended_by_user_id BIGINT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS suspension_reason TEXT NULL;
CREATE INDEX IF NOT EXISTS idx_users_account_status ON users(account_status);

CREATE TABLE IF NOT EXISTS leave_account_status_audit (
    id BIGSERIAL PRIMARY KEY,
    target_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    action VARCHAR(30) NOT NULL,
    previous_status VARCHAR(30) NOT NULL,
    new_status VARCHAR(30) NOT NULL,
    reason TEXT NOT NULL,
    actor_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    actor_name VARCHAR(255) NOT NULL,
    actor_position VARCHAR(180),
    actor_campus VARCHAR(180),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_leave_account_status_audit_target
    ON leave_account_status_audit(target_user_id, created_at DESC);
