BEGIN;

CREATE TABLE IF NOT EXISTS strategy_run (
    run_id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL,
    started_at TIMESTAMPTZ,
    stopped_at TIMESTAMPTZ,
    status TEXT NOT NULL,
    strategy TEXT,
    mode TEXT,
    broker TEXT,
    meta JSONB
);

CREATE INDEX IF NOT EXISTS idx_strategy_run_user_created ON strategy_run(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_strategy_run_user_status ON strategy_run(user_id, status);

ALTER TABLE strategy_log ADD COLUMN IF NOT EXISTS user_id TEXT;
ALTER TABLE strategy_log ADD COLUMN IF NOT EXISTS run_id TEXT;

ALTER TABLE engine_status ADD COLUMN IF NOT EXISTS user_id TEXT;
ALTER TABLE engine_status ADD COLUMN IF NOT EXISTS run_id TEXT;

ALTER TABLE engine_event ADD COLUMN IF NOT EXISTS user_id TEXT;
ALTER TABLE engine_event ADD COLUMN IF NOT EXISTS run_id TEXT;

ALTER TABLE paper_broker_account ADD COLUMN IF NOT EXISTS user_id TEXT;
ALTER TABLE paper_broker_account ADD COLUMN IF NOT EXISTS run_id TEXT;

ALTER TABLE paper_position ADD COLUMN IF NOT EXISTS user_id TEXT;
ALTER TABLE paper_position ADD COLUMN IF NOT EXISTS run_id TEXT;

ALTER TABLE paper_order ADD COLUMN IF NOT EXISTS user_id TEXT;
ALTER TABLE paper_order ADD COLUMN IF NOT EXISTS run_id TEXT;

ALTER TABLE paper_trade ADD COLUMN IF NOT EXISTS user_id TEXT;
ALTER TABLE paper_trade ADD COLUMN IF NOT EXISTS run_id TEXT;

ALTER TABLE paper_equity_curve ADD COLUMN IF NOT EXISTS user_id TEXT;
ALTER TABLE paper_equity_curve ADD COLUMN IF NOT EXISTS run_id TEXT;

ALTER TABLE engine_state_paper ADD COLUMN IF NOT EXISTS user_id TEXT;
ALTER TABLE engine_state_paper ADD COLUMN IF NOT EXISTS run_id TEXT;

ALTER TABLE engine_state ADD COLUMN IF NOT EXISTS user_id TEXT;
ALTER TABLE engine_state ADD COLUMN IF NOT EXISTS run_id TEXT;

ALTER TABLE mtm_ledger ADD COLUMN IF NOT EXISTS user_id TEXT;
ALTER TABLE mtm_ledger ADD COLUMN IF NOT EXISTS run_id TEXT;

ALTER TABLE event_ledger ADD COLUMN IF NOT EXISTS user_id TEXT;
ALTER TABLE event_ledger ADD COLUMN IF NOT EXISTS run_id TEXT;

ALTER TABLE strategy_config ADD COLUMN IF NOT EXISTS user_id TEXT;
ALTER TABLE strategy_config ADD COLUMN IF NOT EXISTS run_id TEXT;

DO $$
DECLARE
    v_user_id TEXT;
BEGIN
    SELECT id INTO v_user_id FROM app_user ORDER BY username LIMIT 1;
    IF v_user_id IS NULL THEN
        RAISE EXCEPTION 'No users available for backfill';
    END IF;

    INSERT INTO strategy_run (
        run_id, user_id, created_at, started_at, stopped_at, status, strategy, mode, broker, meta
    )
    VALUES (
        'default_run',
        v_user_id,
        now(),
        NULL,
        NULL,
        'STOPPED',
        NULL,
        NULL,
        NULL,
        '{}'::jsonb
    )
    ON CONFLICT (run_id) DO NOTHING;

    UPDATE strategy_run
    SET user_id = v_user_id
    WHERE run_id = 'default_run' AND user_id IS NULL;

    UPDATE strategy_log SET user_id = v_user_id, run_id = 'default_run'
    WHERE user_id IS NULL OR run_id IS NULL;

    UPDATE engine_status SET user_id = v_user_id, run_id = 'default_run'
    WHERE user_id IS NULL OR run_id IS NULL;

    UPDATE engine_event SET user_id = v_user_id, run_id = 'default_run'
    WHERE user_id IS NULL OR run_id IS NULL;

    UPDATE paper_broker_account SET user_id = v_user_id, run_id = 'default_run'
    WHERE user_id IS NULL OR run_id IS NULL;

    UPDATE paper_position SET user_id = v_user_id, run_id = 'default_run'
    WHERE user_id IS NULL OR run_id IS NULL;

    UPDATE paper_order SET user_id = v_user_id, run_id = 'default_run'
    WHERE user_id IS NULL OR run_id IS NULL;

    UPDATE paper_trade SET user_id = v_user_id, run_id = 'default_run'
    WHERE user_id IS NULL OR run_id IS NULL;

    UPDATE paper_equity_curve SET user_id = v_user_id, run_id = 'default_run'
    WHERE user_id IS NULL OR run_id IS NULL;

    UPDATE engine_state_paper SET user_id = v_user_id, run_id = 'default_run'
    WHERE user_id IS NULL OR run_id IS NULL;

    UPDATE engine_state SET user_id = v_user_id, run_id = 'default_run'
    WHERE user_id IS NULL OR run_id IS NULL;

    UPDATE mtm_ledger SET user_id = v_user_id, run_id = 'default_run'
    WHERE user_id IS NULL OR run_id IS NULL;

    UPDATE event_ledger SET user_id = v_user_id, run_id = 'default_run'
    WHERE user_id IS NULL OR run_id IS NULL;

    UPDATE strategy_config SET user_id = v_user_id, run_id = 'default_run'
    WHERE user_id IS NULL OR run_id IS NULL;
END $$;

ALTER TABLE strategy_log ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE strategy_log ALTER COLUMN run_id SET NOT NULL;

ALTER TABLE engine_status ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE engine_status ALTER COLUMN run_id SET NOT NULL;

ALTER TABLE engine_event ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE engine_event ALTER COLUMN run_id SET NOT NULL;

ALTER TABLE paper_broker_account ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE paper_broker_account ALTER COLUMN run_id SET NOT NULL;

ALTER TABLE paper_position ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE paper_position ALTER COLUMN run_id SET NOT NULL;

ALTER TABLE paper_order ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE paper_order ALTER COLUMN run_id SET NOT NULL;

ALTER TABLE paper_trade ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE paper_trade ALTER COLUMN run_id SET NOT NULL;

ALTER TABLE paper_equity_curve ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE paper_equity_curve ALTER COLUMN run_id SET NOT NULL;

ALTER TABLE engine_state_paper ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE engine_state_paper ALTER COLUMN run_id SET NOT NULL;

ALTER TABLE engine_state ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE engine_state ALTER COLUMN run_id SET NOT NULL;

ALTER TABLE mtm_ledger ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE mtm_ledger ALTER COLUMN run_id SET NOT NULL;

ALTER TABLE event_ledger ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE event_ledger ALTER COLUMN run_id SET NOT NULL;

ALTER TABLE strategy_config ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE strategy_config ALTER COLUMN run_id SET NOT NULL;

ALTER TABLE paper_trade DROP CONSTRAINT IF EXISTS paper_trade_order_id_fkey;
ALTER TABLE paper_trade DROP CONSTRAINT IF EXISTS fk_paper_trade_order;

DO $$
DECLARE
    rec record;
BEGIN
    FOR rec IN
        SELECT conrelid::regclass AS tbl, conname
        FROM pg_constraint
        WHERE contype = 'p'
          AND conrelid::regclass::text IN (
            'paper_broker_account',
            'paper_position',
            'paper_equity_curve',
            'mtm_ledger',
            'engine_state_paper',
            'engine_state',
            'engine_status',
            'strategy_config'
          )
    LOOP
        EXECUTE format('ALTER TABLE %s DROP CONSTRAINT %I', rec.tbl, rec.conname);
    END LOOP;
END $$;

ALTER TABLE paper_broker_account ADD CONSTRAINT paper_broker_account_pkey PRIMARY KEY (user_id, run_id);
ALTER TABLE paper_position ADD CONSTRAINT paper_position_pkey PRIMARY KEY (user_id, run_id, symbol);
ALTER TABLE paper_equity_curve ADD CONSTRAINT paper_equity_curve_pkey PRIMARY KEY (user_id, run_id, timestamp);
ALTER TABLE mtm_ledger ADD CONSTRAINT mtm_ledger_pkey PRIMARY KEY (user_id, run_id, timestamp);
ALTER TABLE engine_state_paper ADD CONSTRAINT engine_state_paper_pkey PRIMARY KEY (user_id, run_id);
ALTER TABLE engine_state ADD CONSTRAINT engine_state_pkey PRIMARY KEY (user_id, run_id);
ALTER TABLE engine_status ADD CONSTRAINT engine_status_pkey PRIMARY KEY (user_id, run_id);
ALTER TABLE strategy_config ADD CONSTRAINT strategy_config_pkey PRIMARY KEY (user_id, run_id);

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'uq_paper_order_scope_id'
    ) THEN
        ALTER TABLE paper_order ADD CONSTRAINT uq_paper_order_scope_id UNIQUE (user_id, run_id, id);
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'uq_paper_trade_scope_id'
    ) THEN
        ALTER TABLE paper_trade ADD CONSTRAINT uq_paper_trade_scope_id UNIQUE (user_id, run_id, id);
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_strategy_log_user'
    ) THEN
        ALTER TABLE strategy_log
            ADD CONSTRAINT fk_strategy_log_user
            FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_strategy_log_run'
    ) THEN
        ALTER TABLE strategy_log
            ADD CONSTRAINT fk_strategy_log_run
            FOREIGN KEY (run_id) REFERENCES strategy_run(run_id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_engine_status_user'
    ) THEN
        ALTER TABLE engine_status
            ADD CONSTRAINT fk_engine_status_user
            FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_engine_status_run'
    ) THEN
        ALTER TABLE engine_status
            ADD CONSTRAINT fk_engine_status_run
            FOREIGN KEY (run_id) REFERENCES strategy_run(run_id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_engine_event_user'
    ) THEN
        ALTER TABLE engine_event
            ADD CONSTRAINT fk_engine_event_user
            FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_engine_event_run'
    ) THEN
        ALTER TABLE engine_event
            ADD CONSTRAINT fk_engine_event_run
            FOREIGN KEY (run_id) REFERENCES strategy_run(run_id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_paper_broker_user'
    ) THEN
        ALTER TABLE paper_broker_account
            ADD CONSTRAINT fk_paper_broker_user
            FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_paper_broker_run'
    ) THEN
        ALTER TABLE paper_broker_account
            ADD CONSTRAINT fk_paper_broker_run
            FOREIGN KEY (run_id) REFERENCES strategy_run(run_id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_paper_position_user'
    ) THEN
        ALTER TABLE paper_position
            ADD CONSTRAINT fk_paper_position_user
            FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_paper_position_run'
    ) THEN
        ALTER TABLE paper_position
            ADD CONSTRAINT fk_paper_position_run
            FOREIGN KEY (run_id) REFERENCES strategy_run(run_id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_paper_order_user'
    ) THEN
        ALTER TABLE paper_order
            ADD CONSTRAINT fk_paper_order_user
            FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_paper_order_run'
    ) THEN
        ALTER TABLE paper_order
            ADD CONSTRAINT fk_paper_order_run
            FOREIGN KEY (run_id) REFERENCES strategy_run(run_id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_paper_trade_user'
    ) THEN
        ALTER TABLE paper_trade
            ADD CONSTRAINT fk_paper_trade_user
            FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_paper_trade_run'
    ) THEN
        ALTER TABLE paper_trade
            ADD CONSTRAINT fk_paper_trade_run
            FOREIGN KEY (run_id) REFERENCES strategy_run(run_id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_paper_trade_scope_order'
    ) THEN
        ALTER TABLE paper_trade
            ADD CONSTRAINT fk_paper_trade_scope_order
            FOREIGN KEY (user_id, run_id, order_id)
            REFERENCES paper_order(user_id, run_id, id)
            ON DELETE SET NULL;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_paper_equity_user'
    ) THEN
        ALTER TABLE paper_equity_curve
            ADD CONSTRAINT fk_paper_equity_user
            FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_paper_equity_run'
    ) THEN
        ALTER TABLE paper_equity_curve
            ADD CONSTRAINT fk_paper_equity_run
            FOREIGN KEY (run_id) REFERENCES strategy_run(run_id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_engine_state_paper_user'
    ) THEN
        ALTER TABLE engine_state_paper
            ADD CONSTRAINT fk_engine_state_paper_user
            FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_engine_state_paper_run'
    ) THEN
        ALTER TABLE engine_state_paper
            ADD CONSTRAINT fk_engine_state_paper_run
            FOREIGN KEY (run_id) REFERENCES strategy_run(run_id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_engine_state_user'
    ) THEN
        ALTER TABLE engine_state
            ADD CONSTRAINT fk_engine_state_user
            FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_engine_state_run'
    ) THEN
        ALTER TABLE engine_state
            ADD CONSTRAINT fk_engine_state_run
            FOREIGN KEY (run_id) REFERENCES strategy_run(run_id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_mtm_ledger_user'
    ) THEN
        ALTER TABLE mtm_ledger
            ADD CONSTRAINT fk_mtm_ledger_user
            FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_mtm_ledger_run'
    ) THEN
        ALTER TABLE mtm_ledger
            ADD CONSTRAINT fk_mtm_ledger_run
            FOREIGN KEY (run_id) REFERENCES strategy_run(run_id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_event_ledger_user'
    ) THEN
        ALTER TABLE event_ledger
            ADD CONSTRAINT fk_event_ledger_user
            FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_event_ledger_run'
    ) THEN
        ALTER TABLE event_ledger
            ADD CONSTRAINT fk_event_ledger_run
            FOREIGN KEY (run_id) REFERENCES strategy_run(run_id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_strategy_config_user'
    ) THEN
        ALTER TABLE strategy_config
            ADD CONSTRAINT fk_strategy_config_user
            FOREIGN KEY (user_id) REFERENCES app_user(id) ON DELETE CASCADE;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_strategy_config_run'
    ) THEN
        ALTER TABLE strategy_config
            ADD CONSTRAINT fk_strategy_config_run
            FOREIGN KEY (run_id) REFERENCES strategy_run(run_id) ON DELETE CASCADE;
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_strategy_log_user_run_ts ON strategy_log(user_id, run_id, ts DESC);
CREATE INDEX IF NOT EXISTS idx_engine_event_user_run_ts ON engine_event(user_id, run_id, ts DESC);
CREATE INDEX IF NOT EXISTS idx_paper_order_user_run_ts ON paper_order(user_id, run_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_paper_trade_user_run_ts ON paper_trade(user_id, run_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_paper_equity_user_run_ts ON paper_equity_curve(user_id, run_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_mtm_ledger_user_run_ts ON mtm_ledger(user_id, run_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_event_ledger_user_run_ts ON event_ledger(user_id, run_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_engine_state_paper_user_run ON engine_state_paper(user_id, run_id);
CREATE INDEX IF NOT EXISTS idx_engine_state_user_run ON engine_state(user_id, run_id);
CREATE INDEX IF NOT EXISTS idx_engine_status_user_run ON engine_status(user_id, run_id);
CREATE INDEX IF NOT EXISTS idx_paper_position_user_run ON paper_position(user_id, run_id);
CREATE INDEX IF NOT EXISTS idx_paper_broker_user_run ON paper_broker_account(user_id, run_id);

COMMIT;