-- Symmetric reverse of 023.up.sql. Restores paliad.users.role + drops
-- global_role + restores can_see_project + RLS to the migration-021 shape.
--
-- WARNING: this loses any job_title that was NULL after the up migration
-- (admins lose their NULL slot — they get role='admin' restored from
-- global_role='global_admin'). Real job titles from non-admins (e.g.
-- "Counsel Knowledge Lawyer") are preserved by the role rename. tester
-- whose job_title is NULL gets role='admin' back from global_role.

ALTER TABLE paliad.users
    DROP CONSTRAINT IF EXISTS users_job_title_check;

ALTER TABLE paliad.users
    RENAME COLUMN job_title TO role;

-- Restore role='admin' for global_admins whose role is currently NULL.
UPDATE paliad.users
   SET role = 'admin'
 WHERE global_role = 'global_admin' AND role IS NULL;

-- Re-add the original CHECK (role <> '') from migration 015.
ALTER TABLE paliad.users
    ADD CONSTRAINT users_role_check CHECK (role <> '');

ALTER TABLE paliad.users
    DROP CONSTRAINT IF EXISTS users_global_role_check;

ALTER TABLE paliad.users
    DROP COLUMN IF EXISTS global_role;

-- Restore the migration-021 visibility functions + RLS policies.
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.role = '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
    );
$$;

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;
$$;

-- 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.role IN ('partner','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.role IN ('partner','admin')
            )
        )
    );

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));

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))
    );

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));

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))
    );
