-- schema_pre_sync.sql
-- Drops partial unique indexes created by schema_extras.sql before every
-- sea-orm schema sync. The sync layer cannot express partial predicates via
-- entity attributes, and treats these manually-created unique indexes as
-- stale constraints (DROP CONSTRAINT), which fails because they are plain
-- indexes. schema_extras.sql recreates all of them after the sync succeeds.

DROP INDEX IF EXISTS idx_issue_watch_issue_user;
DROP INDEX IF EXISTS idx_issue_watch_project_user;
DROP INDEX IF EXISTS idx_issue_reaction_issue;
DROP INDEX IF EXISTS idx_issue_reaction_comment;
DROP INDEX IF EXISTS idx_issue_reaction_project;
DROP INDEX IF EXISTS idx_branch_protection_whitelist_user;
DROP INDEX IF EXISTS idx_branch_protection_whitelist_group;
DROP INDEX IF EXISTS idx_chat_message_room_nonce;
DROP INDEX IF EXISTS idx_chat_dm_pair;
DROP INDEX IF EXISTS idx_chat_gdm_key;
DROP INDEX IF EXISTS idx_chat_draft_unique;
DROP INDEX IF EXISTS idx_space_join_request_pending;
DROP INDEX IF EXISTS idx_ci_run_event_key;
DROP INDEX IF EXISTS idx_ci_job_run_key;
DROP INDEX IF EXISTS idx_ci_step_key_stage;
DROP INDEX IF EXISTS idx_ci_env_repo_name;
DROP INDEX IF EXISTS idx_ci_schedule_key;
DROP INDEX IF EXISTS idx_ci_variable_name;
DROP INDEX IF EXISTS idx_ci_actions_cache_key;

-- scheduled messages feature removed; drop the legacy table before sync
DROP TABLE IF EXISTS user_scheduled_message;

-- legacy REST admin retired; drop its tables before sync
DROP TABLE IF EXISTS admin_audit_log;
DROP TABLE IF EXISTS admin_user;

-- rooms.parent_id used to be declared as a self-reference, so sync created
-- `fk-rooms-parent_id -> rooms(id)`. The column actually points at a directory
-- node (dir.id) — update_room validates the target is a same-space DirKind::Dir
-- row before writing — so every attempt to file a channel into a folder was
-- rejected by that constraint and the feature never once succeeded. Sync only
-- adds missing foreign keys and treats a same-named constraint as present, so
-- the wrong one has to go before the corrected entity can be built.
-- Guarded on confrelid so this is a no-op once the constraint is correct.
DO $$ BEGIN
    IF to_regclass('rooms') IS NOT NULL
       AND to_regclass('dir') IS NOT NULL
       AND EXISTS (
            SELECT 1 FROM pg_constraint
            WHERE conname = 'fk-rooms-parent_id'
              AND conrelid = to_regclass('rooms')
              AND confrelid <> to_regclass('dir')
       ) THEN
        ALTER TABLE rooms DROP CONSTRAINT "fk-rooms-parent_id";
        -- Defensive: anything the old constraint let through cannot be a dir
        -- node, so it is not a valid folder. Reset it instead of letting the
        -- corrected foreign key fail to build.
        UPDATE rooms SET parent_id = NULL
        WHERE parent_id IS NOT NULL
          AND NOT EXISTS (SELECT 1 FROM dir WHERE dir.id = rooms.parent_id);
    END IF;
END $$;

-- pipeline_run was replaced by workflow_run/workflow_job/workflow_step
DROP INDEX IF EXISTS idx_workflow_run_repo;
DROP INDEX IF EXISTS idx_workflow_run_concurrency;
DROP INDEX IF EXISTS idx_workflow_run_commit;
DROP INDEX IF EXISTS idx_workflow_job_claim;
DROP INDEX IF EXISTS idx_workflow_job_lease;
DROP INDEX IF EXISTS idx_workflow_step_job;
DROP INDEX IF EXISTS idx_ci_log_chunk_job;
DROP INDEX IF EXISTS idx_ci_secret_repo;
DROP INDEX IF EXISTS idx_ci_secret_space;
DROP INDEX IF EXISTS idx_ci_secret_environment;
DROP INDEX IF EXISTS idx_ci_environment_space;
DROP INDEX IF EXISTS idx_ci_deployment_environment;
DROP INDEX IF EXISTS idx_repo_webhook_delivery_due;
DROP INDEX IF EXISTS idx_ci_runner_state;
DROP TABLE IF EXISTS pipeline_run;

-- idempotent column adds for phase-2 model changes (sync layer cannot
-- express NOT NULL DEFAULT additions on populated tables).
-- Guarded with to_regclass: on a fresh database the tables do not exist yet
-- (they are created by the registry sync right after, with these columns
-- already included), so the ALTERs must be skipped instead of failing.
DO $$ BEGIN
    IF to_regclass('room_members') IS NOT NULL THEN
        ALTER TABLE room_members ADD COLUMN IF NOT EXISTS role SMALLINT NOT NULL DEFAULT 0;
    END IF;
END $$;
DO $$ BEGIN
    IF to_regclass('issue') IS NOT NULL THEN
        ALTER TABLE issue ADD COLUMN IF NOT EXISTS priority SMALLINT NOT NULL DEFAULT 0;
        ALTER TABLE issue ADD COLUMN IF NOT EXISTS due_at TIMESTAMP WITH TIME ZONE;
    END IF;
END $$;
DO $$ BEGIN
    IF to_regclass('user_access_key') IS NOT NULL THEN
        ALTER TABLE user_access_key ADD COLUMN IF NOT EXISTS expires_at TIMESTAMP WITH TIME ZONE;
        ALTER TABLE user_access_key ADD COLUMN IF NOT EXISTS last_used_at TIMESTAMP WITH TIME ZONE;
    END IF;
END $$;
DO $$ BEGIN
    IF to_regclass('user_preferences') IS NOT NULL THEN
        ALTER TABLE user_preferences ADD COLUMN IF NOT EXISTS board_view_mode VARCHAR NOT NULL DEFAULT 'board';
    END IF;
END $$;
DO $$ BEGIN
    IF to_regclass('chat_message_reminder') IS NOT NULL THEN
        ALTER TABLE chat_message_reminder ADD COLUMN IF NOT EXISTS note TEXT;
    END IF;
END $$;

-- pull_request domain (PR API): the sync layer cannot relax NOT NULL nor add
-- NOT NULL DEFAULT columns on populated tables, so both are applied here.
DO $$ BEGIN
    IF to_regclass('pull_request') IS NOT NULL THEN
        ALTER TABLE pull_request ALTER COLUMN issue_id DROP NOT NULL;
        ALTER TABLE pull_request ADD COLUMN IF NOT EXISTS head_repo_id UUID;
        ALTER TABLE pull_request ADD COLUMN IF NOT EXISTS head_label VARCHAR;
        ALTER TABLE pull_request ADD COLUMN IF NOT EXISTS is_draft BOOLEAN NOT NULL DEFAULT false;
        ALTER TABLE pull_request ADD COLUMN IF NOT EXISTS merge_method SMALLINT NOT NULL DEFAULT 0;
        ALTER TABLE pull_request ADD COLUMN IF NOT EXISTS squash_commit_sha VARCHAR;
        ALTER TABLE pull_request ADD COLUMN IF NOT EXISTS auto_merge BOOLEAN NOT NULL DEFAULT false;
    END IF;
END $$;
DO $$ BEGIN
    IF to_regclass('pull_request_comment') IS NOT NULL THEN
        ALTER TABLE pull_request_comment ALTER COLUMN commit_sha DROP NOT NULL;
        ALTER TABLE pull_request_comment ALTER COLUMN path DROP NOT NULL;
        ALTER TABLE pull_request_comment ADD COLUMN IF NOT EXISTS parent_comment_id UUID;
        ALTER TABLE pull_request_comment ADD COLUMN IF NOT EXISTS original_commit_sha VARCHAR;
        ALTER TABLE pull_request_comment ADD COLUMN IF NOT EXISTS side VARCHAR;
        ALTER TABLE pull_request_comment ADD COLUMN IF NOT EXISTS original_line INTEGER;
        ALTER TABLE pull_request_comment ADD COLUMN IF NOT EXISTS resolved_at TIMESTAMP WITH TIME ZONE;
        ALTER TABLE pull_request_comment ADD COLUMN IF NOT EXISTS resolved_by UUID;
    END IF;
END $$;

DO $$ BEGIN
    IF to_regclass('repo_branch_protection_policy') IS NOT NULL THEN
        ALTER TABLE repo_branch_protection_policy
            ADD COLUMN IF NOT EXISTS required_status_contexts VARCHAR NOT NULL DEFAULT '';
    END IF;
END $$;