-- t-paliad-051: separate job title from global permissions.
--
-- Until now, paliad.users.role conflated three concepts: free-text job title
-- (Partner / Counsel / PA / Trainee / Sekretariat / "Counsel Knowledge
-- Lawyer"), the global-admin permission ('admin'), and — by accident —
-- a half-broken "partner" capability gate. The collision broke as soon as
-- m wanted to record his real job title without losing admin access: the
-- only way to be a global admin was to set role='admin', which by definition
-- destroyed the job-title slot. The /admin/team UI from t-paliad-050 made
-- the bug visible by hard-rejecting role='admin' on edit.
--
-- This migration splits the column:
--   * paliad.users.job_title  — free text, NULL allowed, display only
--   * paliad.users.global_role — 'standard' | 'global_admin', drives every
--                                permission check that used to look at
--                                role='admin'
--
-- Per-project paliad.project_teams.role is unrelated and untouched.
--
-- Idempotent on a fresh DB: the ADD COLUMN IF NOT EXISTS / DROP CONSTRAINT
-- IF EXISTS guards mean re-running this on a partially-applied DB doesn't
-- explode, and the UPDATEs are no-ops if global_role is already populated.

-- ---------------------------------------------------------------------------
-- 1. Add global_role with a default so existing rows pick up 'standard'.
-- ---------------------------------------------------------------------------
ALTER TABLE paliad.users
    ADD COLUMN IF NOT EXISTS global_role text NOT NULL DEFAULT 'standard';

ALTER TABLE paliad.users
    DROP CONSTRAINT IF EXISTS users_global_role_check;

ALTER TABLE paliad.users
    ADD CONSTRAINT users_global_role_check
    CHECK (global_role IN ('standard', 'global_admin'));

-- ---------------------------------------------------------------------------
-- 2. Drop the legacy NOT NULL + DEFAULT 'associate' on role. The new model
--    allows NULL job_title (admins without a real firm role legitimately
--    store NULL), so the column-level constraint must go before the data
--    UPDATEs that NULL out admin rows; otherwise those UPDATEs would fail
--    silently under psql auto-commit.
-- ---------------------------------------------------------------------------
ALTER TABLE paliad.users
    ALTER COLUMN role DROP NOT NULL;

ALTER TABLE paliad.users
    ALTER COLUMN role DROP DEFAULT;

-- ---------------------------------------------------------------------------
-- 3. Promote anyone with the legacy role='admin' to global_admin, then
--    clear out their role text so it stops masquerading as a job title.
--    m gets a real job title; tester stays NULL (synthetic admin, no title).
-- ---------------------------------------------------------------------------
UPDATE paliad.users SET global_role = 'global_admin' WHERE role = 'admin';

UPDATE paliad.users SET role = NULL WHERE role = 'admin';

UPDATE paliad.users
   SET role = 'Counsel Knowledge Lawyer'
 WHERE email = 'matthias.siebels@hoganlovells.com';

-- ---------------------------------------------------------------------------
-- 3. Rename role -> job_title and adjust the non-empty CHECK so NULL is
--    allowed (admins without a job title legitimately store NULL now).
-- ---------------------------------------------------------------------------
ALTER TABLE paliad.users
    DROP CONSTRAINT IF EXISTS users_role_check;

ALTER TABLE paliad.users
    RENAME COLUMN role TO job_title;

ALTER TABLE paliad.users
    ADD CONSTRAINT users_job_title_check
    CHECK (job_title IS NULL OR job_title <> '');

-- ---------------------------------------------------------------------------
-- 4. Rebuild can_see_project() with the global_role predicate. CASCADE
--    drops the dependent RLS policies; we recreate them below to match
--    the canonical English set from migration 021 — with two changes:
--      * every u.role='admin' becomes u.global_role='global_admin'
--      * the historic u.role IN ('partner','admin') gate (projects_delete,
--        project_teams_delete) becomes u.global_role='global_admin' only.
--        Per m: firm roles ≠ project roles ≠ tool roles. job_title (a
--        firm role / display) must NEVER gate ops. Only tool-level
--        permissions (here: global_admin) authorise mutations. No prod
--        rows have job_title='partner', so this drops zero capability.
-- ---------------------------------------------------------------------------
DROP FUNCTION IF EXISTS paliad.note_is_visible(uuid, uuid, uuid, uuid) CASCADE;
DROP FUNCTION IF EXISTS paliad.can_see_project(uuid)                   CASCADE;

CREATE FUNCTION paliad.can_see_project(_project_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = paliad, public
AS $$
    SELECT EXISTS (
        SELECT 1 FROM paliad.users u
         WHERE u.id = auth.uid() AND u.global_role = 'global_admin'
    )
    OR EXISTS (
        SELECT 1
          FROM paliad.projects      target
          JOIN paliad.project_teams pt
            ON pt.user_id = auth.uid()
           AND pt.project_id = ANY(string_to_array(target.path, '.')::uuid[])
         WHERE target.id = _project_id
    );
$$;

COMMENT ON FUNCTION paliad.can_see_project(uuid) IS
    'Team-based visibility predicate for paliad.projects. Direct or inherited '
    '(ancestor) membership grants access. Global admins see all.';

CREATE FUNCTION paliad.note_is_visible(
    _project_id       uuid,
    _deadline_id      uuid,
    _appointment_id   uuid,
    _project_event_id uuid
) RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = paliad, public
AS $$
    SELECT CASE
        WHEN _project_id IS NOT NULL THEN paliad.can_see_project(_project_id)
        WHEN _deadline_id IS NOT NULL THEN paliad.can_see_project(
            (SELECT project_id FROM paliad.deadlines WHERE id = _deadline_id))
        WHEN _appointment_id IS NOT NULL THEN
            CASE
                WHEN (SELECT project_id FROM paliad.appointments WHERE id = _appointment_id) IS NULL
                    THEN (SELECT created_by FROM paliad.appointments WHERE id = _appointment_id) = auth.uid()
                ELSE paliad.can_see_project(
                    (SELECT project_id FROM paliad.appointments WHERE id = _appointment_id))
            END
        WHEN _project_event_id IS NOT NULL THEN paliad.can_see_project(
            (SELECT project_id FROM paliad.project_events WHERE id = _project_event_id))
        ELSE false
    END;
$$;

-- ---------------------------------------------------------------------------
-- 5. Rebuild every RLS policy CASCADE just dropped. Identical shape to
--    migration 021, with the two predicate swaps noted above.
-- ---------------------------------------------------------------------------

-- projects ------------------------------------------------------------------
CREATE POLICY projects_select ON paliad.projects
    FOR SELECT TO authenticated
    USING (paliad.can_see_project(id));

CREATE POLICY projects_insert ON paliad.projects
    FOR INSERT TO authenticated
    WITH CHECK (
        parent_id IS NULL
        OR paliad.can_see_project(parent_id)
    );

CREATE POLICY projects_update ON paliad.projects
    FOR UPDATE TO authenticated
    USING      (paliad.can_see_project(id))
    WITH CHECK (paliad.can_see_project(id));

CREATE POLICY projects_delete ON paliad.projects
    FOR DELETE TO authenticated
    USING (
        paliad.can_see_project(id)
        AND EXISTS (
            SELECT 1 FROM paliad.users u
             WHERE u.id = auth.uid()
               AND u.global_role = 'global_admin'
        )
    );

-- project_teams -------------------------------------------------------------
CREATE POLICY project_teams_select ON paliad.project_teams
    FOR SELECT TO authenticated
    USING (paliad.can_see_project(project_id));

CREATE POLICY project_teams_insert ON paliad.project_teams
    FOR INSERT TO authenticated
    WITH CHECK (
        user_id = auth.uid()
        OR paliad.can_see_project(project_id)
    );

CREATE POLICY project_teams_update ON paliad.project_teams
    FOR UPDATE TO authenticated
    USING      (paliad.can_see_project(project_id))
    WITH CHECK (paliad.can_see_project(project_id));

CREATE POLICY project_teams_delete ON paliad.project_teams
    FOR DELETE TO authenticated
    USING (
        paliad.can_see_project(project_id)
        AND (
            user_id = auth.uid()
            OR EXISTS (
                SELECT 1 FROM paliad.users u
                 WHERE u.id = auth.uid()
                   AND u.global_role = 'global_admin'
            )
        )
    );

-- parties / deadlines / documents / project_events --------------------------
CREATE POLICY parties_all ON paliad.parties
    FOR ALL TO authenticated
    USING      (paliad.can_see_project(project_id))
    WITH CHECK (paliad.can_see_project(project_id));

CREATE POLICY deadlines_all ON paliad.deadlines
    FOR ALL TO authenticated
    USING      (paliad.can_see_project(project_id))
    WITH CHECK (paliad.can_see_project(project_id));

CREATE POLICY documents_all ON paliad.documents
    FOR ALL TO authenticated
    USING      (paliad.can_see_project(project_id))
    WITH CHECK (paliad.can_see_project(project_id));

CREATE POLICY project_events_all ON paliad.project_events
    FOR ALL TO authenticated
    USING      (paliad.can_see_project(project_id))
    WITH CHECK (paliad.can_see_project(project_id));

-- appointments (project_id nullable; personal appts stay creator-only) -----
CREATE POLICY appointments_select ON paliad.appointments
    FOR SELECT TO authenticated
    USING (
        (project_id IS NULL     AND created_by = auth.uid())
        OR (project_id IS NOT NULL AND paliad.can_see_project(project_id))
    );

CREATE POLICY appointments_insert ON paliad.appointments
    FOR INSERT TO authenticated
    WITH CHECK (
        (project_id IS NULL     AND created_by = auth.uid())
        OR (project_id IS NOT NULL AND paliad.can_see_project(project_id))
    );

CREATE POLICY appointments_update ON paliad.appointments
    FOR UPDATE TO authenticated
    USING (
        (project_id IS NULL     AND created_by = auth.uid())
        OR (project_id IS NOT NULL AND paliad.can_see_project(project_id))
    )
    WITH CHECK (
        (project_id IS NULL     AND created_by = auth.uid())
        OR (project_id IS NOT NULL AND paliad.can_see_project(project_id))
    );

CREATE POLICY appointments_delete ON paliad.appointments
    FOR DELETE TO authenticated
    USING (
        (project_id IS NULL     AND created_by = auth.uid())
        OR (project_id IS NOT NULL AND paliad.can_see_project(project_id))
    );

-- notes (polymorphic parent dispatch) --------------------------------------
CREATE POLICY notes_all ON paliad.notes
    FOR ALL TO authenticated
    USING      (paliad.note_is_visible(project_id, deadline_id, appointment_id, project_event_id))
    WITH CHECK (paliad.note_is_visible(project_id, deadline_id, appointment_id, project_event_id));

-- checklist_instances (project_id nullable; personal stays creator-only) ---
CREATE POLICY checklist_instances_select ON paliad.checklist_instances
    FOR SELECT TO authenticated
    USING (
        (project_id IS NULL     AND created_by = auth.uid())
        OR (project_id IS NOT NULL AND paliad.can_see_project(project_id))
    );

CREATE POLICY checklist_instances_insert ON paliad.checklist_instances
    FOR INSERT TO authenticated
    WITH CHECK (
        created_by = auth.uid()
        AND (project_id IS NULL OR paliad.can_see_project(project_id))
    );

CREATE POLICY checklist_instances_update ON paliad.checklist_instances
    FOR UPDATE TO authenticated
    USING (
        (project_id IS NULL     AND created_by = auth.uid())
        OR (project_id IS NOT NULL AND paliad.can_see_project(project_id))
    )
    WITH CHECK (
        (project_id IS NULL     AND created_by = auth.uid())
        OR (project_id IS NOT NULL AND paliad.can_see_project(project_id))
    );

CREATE POLICY checklist_instances_delete ON paliad.checklist_instances
    FOR DELETE TO authenticated
    USING (
        (project_id IS NULL     AND created_by = auth.uid())
        OR (project_id IS NOT NULL AND paliad.can_see_project(project_id))
    );
