-- Reverse t-paliad-160 M2: re-add the required_role column on
-- paliad.approval_policies and re-introduce the dual-read function shape
-- from migration 064.

ALTER TABLE paliad.approval_policies
    ADD COLUMN required_role text;

UPDATE paliad.approval_policies
   SET required_role = CASE
       WHEN requires_approval = false OR min_role IS NULL THEN 'none'
       ELSE min_role
   END;

ALTER TABLE paliad.approval_policies
    ALTER COLUMN required_role SET NOT NULL;

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

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,
    required_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,
        sr.min_role                             AS required_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;
$$;
