BEGIN;

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

INSERT INTO strategy_run (run_id, user_id, status)
SELECT 'default_' || id, id, 'STOPPED'
FROM app_user
ON CONFLICT (run_id) DO NOTHING;

DO $$
DECLARE
  default_user_id TEXT;
BEGIN
  SELECT id INTO default_user_id FROM app_user ORDER BY username LIMIT 1;
  IF default_user_id IS NULL THEN
    RAISE EXCEPTION 'No app_user rows exist for default_user_id';
  END IF;

  UPDATE strategy_config
    SET user_id = default_user_id
  WHERE user_id IS NULL;
  UPDATE strategy_log
    SET user_id = default_user_id
  WHERE user_id IS NULL;
  UPDATE engine_status
    SET user_id = default_user_id
  WHERE user_id IS NULL;
  UPDATE engine_event
    SET user_id = default_user_id
  WHERE user_id IS NULL;
  UPDATE paper_broker_account
    SET user_id = default_user_id
  WHERE user_id IS NULL;
  UPDATE paper_position
    SET user_id = default_user_id
  WHERE user_id IS NULL;
  UPDATE paper_order
    SET user_id = default_user_id
  WHERE user_id IS NULL;
  UPDATE paper_trade
    SET user_id = default_user_id
  WHERE user_id IS NULL;
  UPDATE paper_equity_curve
    SET user_id = default_user_id
  WHERE user_id IS NULL;
  UPDATE engine_state
    SET user_id = default_user_id
  WHERE user_id IS NULL;
  UPDATE engine_state_paper
    SET user_id = default_user_id
  WHERE user_id IS NULL;
  UPDATE mtm_ledger
    SET user_id = default_user_id
  WHERE user_id IS NULL;
  UPDATE event_ledger
    SET user_id = default_user_id
  WHERE user_id IS NULL;

  UPDATE strategy_config
    SET run_id = 'default_' || user_id
  WHERE run_id IS NULL OR run_id = 'default_run';
  UPDATE strategy_log
    SET run_id = 'default_' || user_id
  WHERE run_id IS NULL OR run_id = 'default_run';
  UPDATE engine_status
    SET run_id = 'default_' || user_id
  WHERE run_id IS NULL OR run_id = 'default_run';
  UPDATE engine_event
    SET run_id = 'default_' || user_id
  WHERE run_id IS NULL OR run_id = 'default_run';
  UPDATE paper_broker_account
    SET run_id = 'default_' || user_id
  WHERE run_id IS NULL OR run_id = 'default_run';
  UPDATE paper_position
    SET run_id = 'default_' || user_id
  WHERE run_id IS NULL OR run_id = 'default_run';
  UPDATE paper_order
    SET run_id = 'default_' || user_id
  WHERE run_id IS NULL OR run_id = 'default_run';
  UPDATE paper_trade
    SET run_id = 'default_' || user_id
  WHERE run_id IS NULL OR run_id = 'default_run';
  UPDATE paper_equity_curve
    SET run_id = 'default_' || user_id
  WHERE run_id IS NULL OR run_id = 'default_run';
  UPDATE engine_state
    SET run_id = 'default_' || user_id
  WHERE run_id IS NULL OR run_id = 'default_run';
  UPDATE engine_state_paper
    SET run_id = 'default_' || user_id
  WHERE run_id IS NULL OR run_id = 'default_run';
  UPDATE mtm_ledger
    SET run_id = 'default_' || user_id
  WHERE run_id IS NULL OR run_id = 'default_run';
  UPDATE event_ledger
    SET run_id = 'default_' || user_id
  WHERE run_id IS NULL OR run_id = 'default_run';
END $$;

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

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'strategy_config_pkey') THEN
    ALTER TABLE strategy_config DROP CONSTRAINT strategy_config_pkey;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'strategy_config_user_run_pk') THEN
    ALTER TABLE strategy_config ADD CONSTRAINT strategy_config_user_run_pk PRIMARY KEY (user_id, run_id);
  END IF;
END $$;

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'engine_status_pkey') THEN
    ALTER TABLE engine_status DROP CONSTRAINT engine_status_pkey;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'engine_status_user_run_pk') THEN
    ALTER TABLE engine_status ADD CONSTRAINT engine_status_user_run_pk PRIMARY KEY (user_id, run_id);
  END IF;
END $$;

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'engine_state_pkey') THEN
    ALTER TABLE engine_state DROP CONSTRAINT engine_state_pkey;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'engine_state_user_run_pk') THEN
    ALTER TABLE engine_state ADD CONSTRAINT engine_state_user_run_pk PRIMARY KEY (user_id, run_id);
  END IF;
END $$;

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'engine_state_paper_pkey') THEN
    ALTER TABLE engine_state_paper DROP CONSTRAINT engine_state_paper_pkey;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'engine_state_paper_user_run_pk') THEN
    ALTER TABLE engine_state_paper ADD CONSTRAINT engine_state_paper_user_run_pk PRIMARY KEY (user_id, run_id);
  END IF;
END $$;

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'paper_broker_account_pkey') THEN
    ALTER TABLE paper_broker_account DROP CONSTRAINT paper_broker_account_pkey;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'paper_broker_account_user_run_pk') THEN
    ALTER TABLE paper_broker_account ADD CONSTRAINT paper_broker_account_user_run_pk PRIMARY KEY (user_id, run_id);
  END IF;
END $$;

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'paper_position_pkey') THEN
    ALTER TABLE paper_position DROP CONSTRAINT paper_position_pkey;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'paper_position_user_run_pk') THEN
    ALTER TABLE paper_position ADD CONSTRAINT paper_position_user_run_pk PRIMARY KEY (user_id, run_id, symbol);
  END IF;
END $$;

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'paper_equity_curve_pkey') THEN
    ALTER TABLE paper_equity_curve DROP CONSTRAINT paper_equity_curve_pkey;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'paper_equity_curve_user_run_pk') THEN
    ALTER TABLE paper_equity_curve ADD CONSTRAINT paper_equity_curve_user_run_pk PRIMARY KEY (user_id, run_id, "timestamp");
  END IF;
END $$;

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'mtm_ledger_pkey') THEN
    ALTER TABLE mtm_ledger DROP CONSTRAINT mtm_ledger_pkey;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'mtm_ledger_user_run_pk') THEN
    ALTER TABLE mtm_ledger ADD CONSTRAINT mtm_ledger_user_run_pk PRIMARY KEY (user_id, run_id, "timestamp");
  END IF;
END $$;

CREATE UNIQUE INDEX IF NOT EXISTS idx_strategy_run_one_running_per_user
  ON strategy_run (user_id)
  WHERE status = 'RUNNING';

COMMIT;