-- verifications, verification_logs, approval_requests, and approval_logs
-- back the entire two-stage admin approval system (Verification Management
-- -> Approval Management) used by every role's profile submission, job
-- posting, and requirement approval flow. None of these tables were ever
-- created by any active (non .skip) migration — they only existed in
-- scripts/init-db.sql, a "complete schema" reference that documents the
-- correct/intended schema but is never actually executed by the real
-- migration runner (crates/db-migrate only reads crates/db/migrations/,
-- per Dockerfile.migrate's `COPY crates/db/migrations /migrations`).
-- Schema below matches scripts/init-db.sql, with two corrections found by
-- cross-referencing the Rust code that actually reads/writes these tables:
--   - verification_logs.verification_request_id references `verifications`
--     directly (not the legacy, separate verification_requests table
--     init-db.sql pointed it at — see 20260718210823_fix_verification_logs_fk
--     for the migration that already patches this exact mistake).
--   - approval_requests needs a UNIQUE(entity_type, entity_id) constraint;
--     apps/users/src/handlers/verifications.rs does
--     `INSERT ... ON CONFLICT (entity_type, entity_id) DO UPDATE`, which
--     requires it and would otherwise fail with
--     "there is no unique or exclusion constraint matching the ON CONFLICT".

CREATE TABLE IF NOT EXISTS verifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_key VARCHAR(50) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'PENDING',
    priority VARCHAR(10) NOT NULL DEFAULT 'LOW',
    case_type VARCHAR(50) NOT NULL,
    payload JSONB NOT NULL DEFAULT '{}',
    documents JSONB NOT NULL DEFAULT '[]',
    notes TEXT,
    rejection_reason TEXT,
    assigned_to UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_verifications_user_id ON verifications(user_id);
CREATE INDEX IF NOT EXISTS idx_verifications_status ON verifications(status);
CREATE INDEX IF NOT EXISTS idx_verifications_case_type ON verifications(case_type);

CREATE TABLE IF NOT EXISTS verification_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    verification_request_id UUID NOT NULL REFERENCES verifications(id) ON DELETE CASCADE,
    action VARCHAR(50) NOT NULL,
    old_status VARCHAR(50),
    new_status VARCHAR(50),
    acted_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    remarks TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_verification_logs_verification_request_id ON verification_logs(verification_request_id);

CREATE TABLE IF NOT EXISTS approval_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entity_type VARCHAR(50) NOT NULL,
    entity_id UUID NOT NULL,
    approval_type VARCHAR(50) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'PENDING',
    submitted_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    reviewed_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    submitted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    reviewed_at TIMESTAMPTZ,
    remarks TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_approval_requests_status ON approval_requests(status);

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'approval_requests_entity_type_entity_id_key') THEN
    ALTER TABLE approval_requests ADD CONSTRAINT approval_requests_entity_type_entity_id_key UNIQUE (entity_type, entity_id);
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS approval_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    approval_request_id UUID NOT NULL REFERENCES approval_requests(id) ON DELETE CASCADE,
    action VARCHAR(50) NOT NULL,
    old_status VARCHAR(50),
    new_status VARCHAR(50),
    acted_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    remarks TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
