BEGIN;

CREATE TABLE IF NOT EXISTS hr_payroll.recruitment_compliance_reviews (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    review_no varchar(80) NOT NULL,
    recruitment_job_id bigint NOT NULL REFERENCES hr_payroll.recruitment_jobs(id),
    status varchar(30) NOT NULL DEFAULT 'under_review',
    score integer NOT NULL DEFAULT 0 CHECK (score BETWEEN 0 AND 100),
    risk_level varchar(20) NOT NULL DEFAULT 'medium',
    checklist jsonb NOT NULL DEFAULT '[]'::jsonb,
    review_note text NULL,
    reviewed_by uuid NULL,
    reviewed_at timestamp NULL,
    remediation_requested_by uuid NULL,
    remediation_requested_at timestamp NULL,
    remediation_note text NULL,
    history jsonb NOT NULL DEFAULT '[]'::jsonb,
    is_active boolean NOT NULL DEFAULT TRUE,
    is_deleted boolean NOT NULL DEFAULT FALSE,
    deleted_at timestamp NULL,
    deleted_by uuid NULL,
    created_by uuid NULL,
    updated_by uuid NULL,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_recruitment_compliance_review_job
    ON hr_payroll.recruitment_compliance_reviews (tenant_id, recruitment_job_id)
    WHERE is_deleted = FALSE;
CREATE UNIQUE INDEX IF NOT EXISTS uq_recruitment_compliance_review_no
    ON hr_payroll.recruitment_compliance_reviews (tenant_id, review_no)
    WHERE is_deleted = FALSE;
CREATE INDEX IF NOT EXISTS idx_recruitment_compliance_reviews_status
    ON hr_payroll.recruitment_compliance_reviews (tenant_id, status, risk_level, reviewed_at DESC)
    WHERE is_deleted = FALSE;

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'cutehorse_app') THEN
        GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE hr_payroll.recruitment_compliance_reviews TO cutehorse_app;
        GRANT USAGE, SELECT, UPDATE ON SEQUENCE hr_payroll.recruitment_compliance_reviews_id_seq TO cutehorse_app;
    END IF;
END $$;

COMMIT;