-- schema_extras.sql
-- Idempotent PostgreSQL DDL for constraints that SeaORM entity-first `sync`
-- cannot express via entity attributes. Runs after every `sync` when
-- auto_migrate is enabled (see lib/db/schema.rs). Safe to re-run on every
-- startup; every statement is guarded with IF NOT EXISTS / DO-block checks.
--
-- This is the single hand-maintained SQL file kept after the old
-- lib/db/migrations/ folder was removed (entity-first strategy).

-- ---------------------------------------------------------------------------
-- Legacy tables dropped by the channel-tree refactor (no entities remain).
-- `sync` is non-destructive, so these must be dropped here.
-- ---------------------------------------------------------------------------
DROP TABLE IF EXISTS channel_members;
DROP TABLE IF EXISTS chat_members;

-- ---------------------------------------------------------------------------
-- Partial unique indexes (polymorphic targets, WHERE-clause filtered).
-- Not expressible as #[sea_orm(unique_key)] — overlapping columns and partial
-- predicates have no entity attribute. `sync` drops undeclared unique indexes,
-- so these are recreated here on every startup.
-- ---------------------------------------------------------------------------
CREATE UNIQUE INDEX IF NOT EXISTS idx_issue_watch_issue_user
    ON issue_watch (issue_id, user_id) WHERE issue_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_issue_watch_project_user
    ON issue_watch (project_id, user_id) WHERE project_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_issue_reaction_issue
    ON issue_reaction (issue_id, user_id, emoji) WHERE issue_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_issue_reaction_comment
    ON issue_reaction (comment_id, user_id, emoji) WHERE comment_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_project_favorites_unique
    ON project_favorites (project_id, user_id, COALESCE(folder, ''));

-- ---------------------------------------------------------------------------
-- Composite non-unique indexes (entity attributes are single-column only).
-- ---------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_project_space_order       ON project (space_id, sort_order);
CREATE INDEX IF NOT EXISTS idx_project_views_project     ON project_views (project_id, sort_order);
CREATE INDEX IF NOT EXISTS idx_project_activities_feed   ON project_activities (project_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_project_activities_actor  ON project_activities (actor_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_project_favorites_user    ON project_favorites (user_id, created_at DESC);

-- Single-column indexes from the removed migrations (parity on fresh DBs).
CREATE INDEX IF NOT EXISTS idx_project_members_user     ON project_members (user_id);
CREATE INDEX IF NOT EXISTS idx_project_members_project  ON project_members (project_id);
CREATE INDEX IF NOT EXISTS idx_project_views_creator    ON project_views (creator_id);
CREATE INDEX IF NOT EXISTS idx_project_documents_project ON project_documents (project_id);
CREATE INDEX IF NOT EXISTS idx_project_documents_parent ON project_documents (parent_id);
CREATE INDEX IF NOT EXISTS idx_project_links_project    ON project_links (project_id);
CREATE INDEX IF NOT EXISTS idx_project_links_related_project ON project_links (related_project_id);
CREATE INDEX IF NOT EXISTS idx_project_state            ON project (state);
CREATE INDEX IF NOT EXISTS idx_project_target_date      ON project (target_date);
CREATE INDEX IF NOT EXISTS idx_project_lead             ON project (lead_id);
CREATE INDEX IF NOT EXISTS idx_comment_parent_id        ON comment (parent_id);
CREATE INDEX IF NOT EXISTS idx_comment_project          ON comment (project_id);
CREATE INDEX IF NOT EXISTS idx_issue_reaction_project   ON issue_reaction (project_id);
CREATE INDEX IF NOT EXISTS idx_issue_watch_project      ON issue_watch (project_id);
CREATE INDEX IF NOT EXISTS idx_issue_event_project      ON issue_event_line (project_id);
CREATE INDEX IF NOT EXISTS idx_issue_pin_project        ON issue_pin (project_id);

-- ---------------------------------------------------------------------------
-- CHECK constraints (polymorphic XOR / exactly-one targets).
-- ALTER TABLE ... ADD CONSTRAINT has no IF NOT EXISTS, so guard each with a
-- DO block against pg_constraint.
-- ---------------------------------------------------------------------------
DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'comment_target_xor') THEN
        ALTER TABLE comment
            ADD CONSTRAINT comment_target_xor
            CHECK ((issue_id IS NOT NULL) <> (project_id IS NOT NULL));
    END IF;
END $$;

DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'issue_reaction_target_exactly_one') THEN
        ALTER TABLE issue_reaction
            ADD CONSTRAINT issue_reaction_target_exactly_one
            CHECK (((issue_id IS NOT NULL)::int + (comment_id IS NOT NULL)::int + (project_id IS NOT NULL)::int) = 1);
    END IF;
END $$;

DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'issue_watch_target_xor') THEN
        ALTER TABLE issue_watch
            ADD CONSTRAINT issue_watch_target_xor
            CHECK ((issue_id IS NOT NULL) <> (project_id IS NOT NULL));
    END IF;
END $$;

DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'issue_event_target_xor') THEN
        ALTER TABLE issue_event_line
            ADD CONSTRAINT issue_event_target_xor
            CHECK ((issue_id IS NOT NULL) <> (project_id IS NOT NULL));
    END IF;
END $$;

DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'issue_pin_target_xor') THEN
        ALTER TABLE issue_pin
            ADD CONSTRAINT issue_pin_target_xor
            CHECK ((issue_id IS NOT NULL) <> (project_id IS NOT NULL));
    END IF;
END $$;

DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'project_links_not_self') THEN
        ALTER TABLE project_links
            ADD CONSTRAINT project_links_not_self CHECK (project_id <> related_project_id);
    END IF;
END $$;

-- ---------------------------------------------------------------------------
-- Payment composite indexes (multi-column; entity attributes are single-column).
-- ---------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_payment_transaction_ref ON payment_transaction (ref_type, ref_id);
CREATE INDEX IF NOT EXISTS idx_payment_webhook_status_created ON payment_webhook_event (status, created_at);

-- ---------------------------------------------------------------------------
-- LiveKit meet / participant indexes.
-- Partial unique index: each channel has at most one active meet at a time.
-- ---------------------------------------------------------------------------
CREATE UNIQUE INDEX IF NOT EXISTS idx_meet_active_per_channel ON meet (channel_id) WHERE status = 1;
CREATE UNIQUE INDEX IF NOT EXISTS idx_meet_room_name ON meet (room_name);

-- ---------------------------------------------------------------------------
-- repo_branch_protection_policy additions for H6 (pre-receive enforcement).
-- Both columns are non-breaking; existing rows get `enable_force_push=false`
-- and `branch_pattern=NULL`. `sync` is non-destructive on existing columns,
-- so for environments with `auto_migrate: false` these ALTERs make the
-- columns available immediately on next startup.
-- ---------------------------------------------------------------------------
ALTER TABLE repo_branch_protection_policy
    ADD COLUMN IF NOT EXISTS enable_force_push BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE repo_branch_protection_policy
    ADD COLUMN IF NOT EXISTS branch_pattern TEXT NULL;
CREATE INDEX IF NOT EXISTS idx_meet_participants_meet_user ON meet_participants (meet_id, user_id);
CREATE INDEX IF NOT EXISTS idx_meet_participants_user_joined ON meet_participants (user_id, joined_at DESC);