-- Add `status` column to resident_documents for document approval workflow
-- Possible values: 'pending', 'approved', 'rejected'
-- Run this manually against your database (do NOT use project migration runner if you can't run it).
-- Examples:
-- psql "host=... user=... dbname=..." -f sql/maintenance/alter-resident-documents-add-status.sql
-- or (docker): docker exec -i <pg_container> psql -U <user> -d <db> -f /path/in/container/alter-resident-documents-add-status.sql

BEGIN;
-- Add column (idempotent with IF NOT EXISTS)
ALTER TABLE public.resident_documents
    ADD COLUMN IF NOT EXISTS status VARCHAR(20) NOT NULL DEFAULT 'pending';

-- Add check constraint to restrict values
-- Add check constraint to restrict values (only add if missing)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_resident_documents_status'
    ) THEN
        ALTER TABLE public.resident_documents
            ADD CONSTRAINT chk_resident_documents_status CHECK (status IN ('pending','approved','rejected'));
    END IF;
END
$$;

COMMIT;
