-- t-paliad-160 (M2): drop the legacy `required_role` column from
-- paliad.approval_policies. Migration 064 (M1) introduced the
-- requires_approval + min_role split-grammar columns and kept
-- required_role as a dual-read mirror so a rollback to pre-deploy
-- code would still work. M2 retires the mirror once all writers have
-- cut over.
--
-- Deploy ordering — IMPORTANT:
--
--   1. Migration 064 must already be applied (introduces the new
--      columns and rewrites approval_policy_effective() to the
--      new shape). The Go service layer in slice 1+2 reads the new
--      columns AND writes both old + new on every Upsert*. With
--      this migration the writes drop the legacy column path.
--   2. After 065, NO code path may reference
--      paliad.approval_policies.required_role.
--   3. paliad.approval_requests.required_role is a different column
--      (the in-flight snapshot of the policy at submission time) and
--      is intentionally untouched here.
--
-- The function paliad.approval_policy_effective() is also updated to
-- stop returning the redundant required_role column.

-- ============================================================================
-- 1. Replace approval_policy_effective() with a 4-column return that no
--    longer mirrors required_role.
-- ============================================================================

DROP FUNCTION IF EXISTS paliad.approval_policy_effective(uuid, text, text);

CREATE FUNCTION paliad.approval_policy_effective(
    p_project_id  uuid,
    p_entity_type text,
    p_lifecycle   text
) RETURNS TABLE (
    requires_approval boolean,
    min_role          text,
    source            text,
    source_id         uuid
)
LANGUAGE plpgsql STABLE AS $$
BEGIN
    RETURN QUERY
    WITH path AS (
        SELECT string_to_array(p.path, '.')::uuid[] AS ids
          FROM paliad.projects p WHERE p.id = p_project_id
    ),
    project_rows AS (
        SELECT ap.requires_approval,
               ap.min_role,
               'project'::text AS src,
               ap.project_id   AS sid,
               1               AS src_priority
          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
    ),
    ancestor_rows AS (
        SELECT ap.requires_approval,
               ap.min_role,
               'ancestor'::text AS src,
               ap.project_id    AS sid,
               2                AS src_priority
          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.requires_approval,
               ap.min_role,
               'unit_default'::text AS src,
               ap.partner_unit_id   AS sid,
               3                    AS src_priority
          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
    ),
    candidates AS (
        SELECT * FROM project_rows
         UNION ALL
        SELECT * FROM ancestor_rows
         UNION ALL
        SELECT * FROM unit_rows
    ),
    strictest_role AS (
        SELECT c.min_role,
               c.src AS source,
               c.sid AS source_id
          FROM candidates c
         WHERE c.requires_approval = true
           AND c.min_role IS NOT NULL
         ORDER BY paliad.approval_role_level(c.min_role) DESC,
                  c.src_priority ASC
         LIMIT 1
    ),
    no_approval_attribution AS (
        SELECT c.src AS source, c.sid AS source_id
          FROM candidates c
         WHERE c.requires_approval = false
         ORDER BY c.src_priority ASC
         LIMIT 1
    ),
    summary AS (
        SELECT bool_or(c.requires_approval) AS req
          FROM candidates c
    )
    SELECT
        COALESCE(s.req, false)                                  AS requires_approval,
        sr.min_role                                             AS min_role,
        COALESCE(sr.source, na.source)                          AS source,
        COALESCE(sr.source_id, na.source_id)                    AS source_id
      FROM summary s
      LEFT JOIN strictest_role sr ON true
      LEFT JOIN no_approval_attribution na ON true
     WHERE EXISTS (SELECT 1 FROM candidates);
END;
$$;

COMMENT ON FUNCTION paliad.approval_policy_effective(uuid, text, text) IS
    'Effective approval policy resolver (t-paliad-160 M2). Returns '
    'requires_approval (OR across candidates) + min_role (MAX along the '
    'role ladder among requires_approval=true candidates) + source '
    'attribution. Zero rows when no policy candidates exist.';

-- ============================================================================
-- 2. Drop the legacy column.
-- ============================================================================

ALTER TABLE paliad.approval_policies
    DROP CONSTRAINT IF EXISTS approval_policies_required_role_check;

ALTER TABLE paliad.approval_policies
    DROP COLUMN required_role;
