-- t-paliad-154 down migration. Reverses 062_approval_policy_unit_defaults.up.sql.
--
-- Order is the reverse of up:
--   0. Drop policy_audit_log table.
--   1. Drop seeded unit-default rows (anything where partner_unit_id IS NOT NULL).
--   2. Drop the resolver function.
--   3. Restore required_role CHECK without 'none'.
--   4. Drop the two partial unique indexes + restore the original UNIQUE composite.
--   5. Drop XOR check + partner_unit_id column.
--   6. Restore project_id NOT NULL.

-- 0. Drop the audit table.
DROP TABLE IF EXISTS paliad.policy_audit_log;
--
-- Best-effort reversibility: any project-specific row with required_role='none'
-- will fail the CHECK restoration. We coerce those to 'associate' before
-- restoring the CHECK so the migration can roll back without data loss.

-- 1. Drop unit-default rows. Keep project rows intact (they pre-date 062).
DELETE FROM paliad.approval_policies WHERE partner_unit_id IS NOT NULL;

-- 2. Drop resolver.
DROP FUNCTION IF EXISTS paliad.approval_policy_effective(uuid, text, text);

-- 3. Coerce 'none' rows to 'associate' so the restored CHECK passes.
UPDATE paliad.approval_policies
   SET required_role = 'associate'
 WHERE required_role = 'none';

ALTER TABLE paliad.approval_policies
    DROP CONSTRAINT IF EXISTS approval_policies_required_role_check;
ALTER TABLE paliad.approval_policies
    ADD CONSTRAINT approval_policies_required_role_check
    CHECK (required_role IN (
        'partner', 'of_counsel', 'associate', 'senior_pa', 'pa'
    ));

-- 4. Drop partial unique indexes; restore composite UNIQUE on project rows
-- (down migration leaves the column NOT NULL so the unique-on-project_id
-- composite is sound again).
DROP INDEX IF EXISTS paliad.approval_policies_project_unique;
DROP INDEX IF EXISTS paliad.approval_policies_unit_unique;
DROP INDEX IF EXISTS paliad.approval_policies_unit_idx;

-- 5. Drop XOR check + partner_unit_id column.
ALTER TABLE paliad.approval_policies
    DROP CONSTRAINT IF EXISTS approval_policies_scope_xor;
ALTER TABLE paliad.approval_policies
    DROP COLUMN IF EXISTS partner_unit_id;

-- 6. Restore NOT NULL on project_id (no rows should be NULL by now since
-- step 1 deleted every unit-default row).
ALTER TABLE paliad.approval_policies
    ALTER COLUMN project_id SET NOT NULL;

-- Restore composite UNIQUE constraint name to match migration 054.
ALTER TABLE paliad.approval_policies
    ADD CONSTRAINT approval_policies_project_id_entity_type_lifecycle_event_key
    UNIQUE (project_id, entity_type, lifecycle_event);
