-- t-paliad-154: approval-policy authoring UI substrate.
--
-- Design: docs/design-approval-policy-ui-2026-05-07.md (hilbert, m-locked
-- 2026-05-07). Surfaces the dormant t-138 4-eye system by adding:
--
--   1. partner_unit_id column on paliad.approval_policies — XOR with project_id
--      so a row applies to either one project or one partner unit (firm-wide
--      default for projects attached to that unit). Existing rows
--      (project_id IS NOT NULL) keep their meaning unchanged.
--   2. 'none' sentinel value for required_role — explicit "no approval needed"
--      override at project-row level. Suppresses inherited defaults.
--   3. paliad.approval_policy_effective() resolver — most-restrictive across
--      project row | ancestor rows | unit defaults. Project-specific row
--      wins outright (any value including 'none'). Otherwise MAX(level) across
--      ancestor + unit_default candidates.
--   4. Conservative seed defaults for every existing partner_unit:
--      deadline+appointment × create/update/delete=associate, complete=none.
--   5. paliad.policy_audit_log — fifth audit source for /admin/audit-log only.
--      Q8 of the locked design: policy changes audit on /admin/audit-log,
--      NOT on per-project /verlauf. Distinct table keeps the verlauf union
--      clean while letting AuditService union the new source.
--
-- Idempotent on re-run (ON CONFLICT DO NOTHING on the seed). Safe on a DB
-- with 0 partner_units (the seed simply inserts zero rows).
--
-- Sections:
--   1. Make project_id nullable + ADD partner_unit_id + XOR check.
--   2. Replace UNIQUE composite with two partial unique indexes.
--   3. Extend required_role CHECK with 'none' sentinel.
--   4. CREATE FUNCTION paliad.approval_policy_effective(uuid, text, text).
--   5. Seed conservative defaults for every existing partner_unit.
--   6. CREATE TABLE paliad.policy_audit_log (admin-only audit source).

-- ============================================================================
-- 1. project_id becomes nullable; add partner_unit_id; XOR check.
-- ============================================================================

ALTER TABLE paliad.approval_policies
    ALTER COLUMN project_id DROP NOT NULL;

ALTER TABLE paliad.approval_policies
    ADD COLUMN partner_unit_id uuid
        REFERENCES paliad.partner_units(id) ON DELETE CASCADE;

-- Exactly one of (project_id, partner_unit_id) must be set. NEVER both NULL,
-- NEVER both set. Defence against orphaned rows.
ALTER TABLE paliad.approval_policies
    ADD CONSTRAINT approval_policies_scope_xor CHECK (
        (project_id IS NOT NULL AND partner_unit_id IS NULL) OR
        (project_id IS NULL AND partner_unit_id IS NOT NULL)
    );

-- ============================================================================
-- 2. Replace UNIQUE (project_id, ...) with two partial unique indexes.
--
-- The original UNIQUE composite assumed project_id NOT NULL. Now that
-- project_id is nullable, we split: one partial unique index per scope
-- (project rows | unit rows). Cells stay 1:1 within their scope.
-- ============================================================================

ALTER TABLE paliad.approval_policies
    DROP CONSTRAINT IF EXISTS approval_policies_project_id_entity_type_lifecycle_event_key;

CREATE UNIQUE INDEX approval_policies_project_unique
    ON paliad.approval_policies (project_id, entity_type, lifecycle_event)
    WHERE project_id IS NOT NULL;

CREATE UNIQUE INDEX approval_policies_unit_unique
    ON paliad.approval_policies (partner_unit_id, entity_type, lifecycle_event)
    WHERE partner_unit_id IS NOT NULL;

CREATE INDEX approval_policies_unit_idx
    ON paliad.approval_policies (partner_unit_id)
    WHERE partner_unit_id IS NOT NULL;

-- ============================================================================
-- 3. 'none' sentinel value for required_role.
--
-- Project row with required_role='none' suppresses inherited defaults
-- explicitly (caller-side: LookupPolicy returns nil when the resolver
-- yields 'none'). Unit-default rows can also carry 'none' but are
-- structurally invisible to the MAX(level) computation since
-- approval_role_level('none')=0 (loses to any non-none).
--
-- approval_role_level('none') already returns 0 via the existing ELSE
-- branch in migration 059 §7. No function update needed.
-- ============================================================================

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', 'none'
    ));

-- ============================================================================
-- 4. paliad.approval_policy_effective(project, entity_type, lifecycle).
--
-- Returns at most one row, or zero rows when no policy applies.
--
-- Resolution order:
--   Step 1: if a project-specific row exists for (project, entity, lifecycle),
--           return it outright. Any value (including 'none') wins.
--   Step 2: else MAX(approval_role_level) across:
--             - ancestor project rows on this project's ltree path
--             - unit-default rows for partner units attached to this project
--           Tied levels: 'ancestor' beats 'unit_default' alphabetically for
--           stable attribution.
--   Step 3: else (no candidates) — return zero rows. Caller treats as
--           "no policy applies".
--
-- Returned columns:
--   required_role  text  — one of partner|of_counsel|associate|senior_pa|pa|none
--   source         text  — 'project' | 'ancestor' | 'unit_default'
--   source_id      uuid  — project_id for project/ancestor, partner_unit_id for unit_default
-- ============================================================================

CREATE OR REPLACE FUNCTION paliad.approval_policy_effective(
    p_project_id  uuid,
    p_entity_type text,
    p_lifecycle   text
) RETURNS TABLE (
    required_role text,
    source        text,
    source_id     uuid
)
LANGUAGE plpgsql STABLE AS $$
BEGIN
    -- Step 1: project-specific row wins outright.
    RETURN QUERY
    SELECT ap.required_role, 'project'::text AS source, ap.project_id AS source_id
      FROM paliad.approval_policies ap
     WHERE ap.project_id = p_project_id
       AND ap.entity_type = p_entity_type
       AND ap.lifecycle_event = p_lifecycle;
    IF FOUND THEN
        RETURN;
    END IF;

    -- Step 2: MAX(level) across ancestor rows + unit defaults attached to project.
    RETURN QUERY
    WITH path AS (
        SELECT string_to_array(p.path, '.')::uuid[] AS ids
          FROM paliad.projects p WHERE p.id = p_project_id
    ),
    ancestor_rows AS (
        SELECT ap.required_role,
               'ancestor'::text AS src,
               ap.project_id AS sid,
               paliad.approval_role_level(ap.required_role) AS lvl
          FROM paliad.approval_policies ap, path
         WHERE ap.project_id = ANY(path.ids)
           AND ap.project_id <> p_project_id
           AND ap.entity_type = p_entity_type
           AND ap.lifecycle_event = p_lifecycle
    ),
    unit_rows AS (
        SELECT ap.required_role,
               'unit_default'::text AS src,
               ap.partner_unit_id AS sid,
               paliad.approval_role_level(ap.required_role) AS lvl
          FROM paliad.approval_policies ap
          JOIN paliad.project_partner_units ppu
            ON ppu.partner_unit_id = ap.partner_unit_id
         WHERE ppu.project_id = p_project_id
           AND ap.entity_type = p_entity_type
           AND ap.lifecycle_event = p_lifecycle
    )
    SELECT a.required_role, a.src, a.sid
      FROM (
            SELECT * FROM ancestor_rows
            UNION ALL
            SELECT * FROM unit_rows
           ) AS a
     ORDER BY a.lvl DESC, a.src ASC
     LIMIT 1;
END;
$$;

COMMENT ON FUNCTION paliad.approval_policy_effective(uuid, text, text) IS
    'Effective approval policy resolver (t-paliad-154). '
    'Project-specific row wins outright; else MAX(level) across ancestor '
    'rows on the project path and unit-default rows for attached partner '
    'units; else no row. Caller treats 0 rows or required_role=''none'' as '
    '"no policy applies".';

-- ============================================================================
-- 5. Seed conservative defaults for every existing partner_unit.
--
-- 8 rows per unit: deadline+appointment × create/update/delete=associate,
-- complete=none. Marking-as-done is low-risk; planning operations need 4-eye.
--
-- ON CONFLICT DO NOTHING on (partner_unit_id, entity_type, lifecycle_event)
-- via the partial unique index so re-running the migration (or topping up
-- after a partner_unit is added) is a no-op for existing rows.
--
-- Safe on a DB with 0 partner_units — the SELECT returns no rows.
-- ============================================================================

INSERT INTO paliad.approval_policies (
    project_id, partner_unit_id, entity_type, lifecycle_event, required_role
)
SELECT NULL, pu.id, t.entity_type, t.lifecycle_event, t.required_role
  FROM paliad.partner_units pu
  CROSS JOIN (
    VALUES
        ('deadline',    'create',   'associate'),
        ('deadline',    'update',   'associate'),
        ('deadline',    'delete',   'associate'),
        ('deadline',    'complete', 'none'),
        ('appointment', 'create',   'associate'),
        ('appointment', 'update',   'associate'),
        ('appointment', 'delete',   'associate'),
        ('appointment', 'complete', 'none')
  ) AS t(entity_type, lifecycle_event, required_role)
ON CONFLICT (partner_unit_id, entity_type, lifecycle_event)
    WHERE partner_unit_id IS NOT NULL
    DO NOTHING;

-- ============================================================================
-- 6. paliad.policy_audit_log — admin-only audit source.
--
-- Q8 of the design: emit audit on /admin/audit-log, NOT on per-project
-- /verlauf. Distinct table from project_events so AuditService can union it
-- as a fifth source without polluting the verlauf SELECT.
--
-- One row per policy mutation (set or cleared, project-scoped or unit-scoped).
-- The actor is always a global_admin (the route gate enforces this).
--
-- scope_type / scope_id: ('project', project_id) or ('unit', partner_unit_id).
-- The FK columns (project_id, partner_unit_id) are nullable so a deleted
-- target row doesn't cascade-purge the audit history.
-- ============================================================================

CREATE TABLE paliad.policy_audit_log (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    actor_id        uuid NOT NULL REFERENCES paliad.users(id) ON DELETE RESTRICT,
    event_type      text NOT NULL CHECK (event_type IN (
                        'approval_policy_set', 'approval_policy_cleared'
                    )),
    scope_type      text NOT NULL CHECK (scope_type IN ('project', 'unit')),
    project_id      uuid REFERENCES paliad.projects(id) ON DELETE SET NULL,
    partner_unit_id uuid REFERENCES paliad.partner_units(id) ON DELETE SET NULL,
    -- Snapshot of the target's name at event time so a later cascade-set-null
    -- doesn't lose the human label.
    scope_name      text NOT NULL,
    entity_type     text NOT NULL CHECK (entity_type IN ('deadline', 'appointment')),
    lifecycle_event text NOT NULL CHECK (lifecycle_event IN ('create', 'update', 'complete', 'delete')),
    old_required_role text,
    new_required_role text,
    created_at      timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT policy_audit_log_scope_xor CHECK (
        (scope_type = 'project' AND project_id      IS NOT NULL AND partner_unit_id IS NULL) OR
        (scope_type = 'unit'    AND partner_unit_id IS NOT NULL AND project_id      IS NULL) OR
        (scope_type = 'project' AND project_id      IS NULL    AND partner_unit_id IS NULL) OR  -- post-cascade
        (scope_type = 'unit'    AND partner_unit_id IS NULL    AND project_id      IS NULL)     -- post-cascade
    )
);

CREATE INDEX policy_audit_log_time_idx  ON paliad.policy_audit_log (created_at DESC);
CREATE INDEX policy_audit_log_actor_idx ON paliad.policy_audit_log (actor_id, created_at DESC);

ALTER TABLE paliad.policy_audit_log ENABLE ROW LEVEL SECURITY;

-- Read access: any authenticated user (the audit-log page itself is
-- admin-gated at the route layer; this is defence-in-depth).
CREATE POLICY policy_audit_log_select ON paliad.policy_audit_log
    FOR SELECT USING (auth.uid() IS NOT NULL);

-- Write access: only global_admin (defence-in-depth; service layer also
-- gates).
CREATE POLICY policy_audit_log_write ON paliad.policy_audit_log
    FOR INSERT WITH CHECK (
      EXISTS (
        SELECT 1 FROM paliad.users u
         WHERE u.id = auth.uid() AND u.global_role = 'global_admin'
      )
    );

COMMENT ON TABLE paliad.policy_audit_log IS
    'Audit trail for paliad.approval_policies CRUD (t-paliad-154). '
    'Surfaces on /admin/audit-log only — not on per-project /verlauf, per '
    'design Q8 lock-in. Unioned by services.AuditService alongside '
    'project_events / partner_unit_events / caldav_sync_log / reminder_log.';
