SIP_GoldBees_Database/db_migrations/20260114_identity_fk_fix.sql

BEGIN;

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

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_engine_status_user_run') THEN
    ALTER TABLE engine_status
      ADD CONSTRAINT fk_engine_status_user_run
      FOREIGN KEY (user_id, run_id)
      REFERENCES strategy_run(user_id, run_id)
      ON DELETE CASCADE;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_engine_state_user_run') THEN
    ALTER TABLE engine_state
      ADD CONSTRAINT fk_engine_state_user_run
      FOREIGN KEY (user_id, run_id)
      REFERENCES strategy_run(user_id, run_id)
      ON DELETE CASCADE;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_engine_state_paper_user_run') THEN
    ALTER TABLE engine_state_paper
      ADD CONSTRAINT fk_engine_state_paper_user_run
      FOREIGN KEY (user_id, run_id)
      REFERENCES strategy_run(user_id, run_id)
      ON DELETE CASCADE;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_paper_broker_account_user_run') THEN
    ALTER TABLE paper_broker_account
      ADD CONSTRAINT fk_paper_broker_account_user_run
      FOREIGN KEY (user_id, run_id)
      REFERENCES strategy_run(user_id, run_id)
      ON DELETE CASCADE;
  END IF;
END $$;

ALTER TABLE strategy_config ALTER COLUMN id TYPE bigint;
ALTER TABLE engine_state ALTER COLUMN id TYPE bigint;
ALTER TABLE engine_status ALTER COLUMN id TYPE bigint;
ALTER TABLE engine_state_paper ALTER COLUMN id TYPE bigint;
ALTER TABLE paper_broker_account ALTER COLUMN id TYPE bigint;

DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM pg_attribute
    WHERE attrelid = 'strategy_config'::regclass AND attname = 'id' AND attidentity = ''
  ) THEN
    ALTER TABLE strategy_config ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
  END IF;
  IF EXISTS (
    SELECT 1 FROM pg_attribute
    WHERE attrelid = 'engine_state'::regclass AND attname = 'id' AND attidentity = ''
  ) THEN
    ALTER TABLE engine_state ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
  END IF;
  IF EXISTS (
    SELECT 1 FROM pg_attribute
    WHERE attrelid = 'engine_status'::regclass AND attname = 'id' AND attidentity = ''
  ) THEN
    ALTER TABLE engine_status ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
  END IF;
  IF EXISTS (
    SELECT 1 FROM pg_attribute
    WHERE attrelid = 'engine_state_paper'::regclass AND attname = 'id' AND attidentity = ''
  ) THEN
    ALTER TABLE engine_state_paper ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
  END IF;
  IF EXISTS (
    SELECT 1 FROM pg_attribute
    WHERE attrelid = 'paper_broker_account'::regclass AND attname = 'id' AND attidentity = ''
  ) THEN
    ALTER TABLE paper_broker_account ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
  END IF;
END $$;

DO $$
DECLARE
  seq_name text;
  max_id bigint;
BEGIN
  seq_name := pg_get_serial_sequence('strategy_config', 'id');
  IF seq_name IS NOT NULL THEN
    SELECT MAX(id) INTO max_id FROM strategy_config;
    IF max_id IS NULL OR max_id < 1 THEN
      EXECUTE format('SELECT setval(%L, 1, false)', seq_name);
    ELSE
      EXECUTE format('SELECT setval(%L, %s, true)', seq_name, max_id);
    END IF;
  END IF;
  seq_name := pg_get_serial_sequence('engine_state', 'id');
  IF seq_name IS NOT NULL THEN
    SELECT MAX(id) INTO max_id FROM engine_state;
    IF max_id IS NULL OR max_id < 1 THEN
      EXECUTE format('SELECT setval(%L, 1, false)', seq_name);
    ELSE
      EXECUTE format('SELECT setval(%L, %s, true)', seq_name, max_id);
    END IF;
  END IF;
  seq_name := pg_get_serial_sequence('engine_status', 'id');
  IF seq_name IS NOT NULL THEN
    SELECT MAX(id) INTO max_id FROM engine_status;
    IF max_id IS NULL OR max_id < 1 THEN
      EXECUTE format('SELECT setval(%L, 1, false)', seq_name);
    ELSE
      EXECUTE format('SELECT setval(%L, %s, true)', seq_name, max_id);
    END IF;
  END IF;
  seq_name := pg_get_serial_sequence('engine_state_paper', 'id');
  IF seq_name IS NOT NULL THEN
    SELECT MAX(id) INTO max_id FROM engine_state_paper;
    IF max_id IS NULL OR max_id < 1 THEN
      EXECUTE format('SELECT setval(%L, 1, false)', seq_name);
    ELSE
      EXECUTE format('SELECT setval(%L, %s, true)', seq_name, max_id);
    END IF;
  END IF;
  seq_name := pg_get_serial_sequence('paper_broker_account', 'id');
  IF seq_name IS NOT NULL THEN
    SELECT MAX(id) INTO max_id FROM paper_broker_account;
    IF max_id IS NULL OR max_id < 1 THEN
      EXECUTE format('SELECT setval(%L, 1, false)', seq_name);
    ELSE
      EXECUTE format('SELECT setval(%L, %s, true)', seq_name, max_id);
    END IF;
  END IF;
END $$;

DROP INDEX IF EXISTS idx_engine_state_user_run;
DROP INDEX IF EXISTS idx_engine_status_user_run;
DROP INDEX IF EXISTS idx_engine_state_paper_user_run;
DROP INDEX IF EXISTS idx_paper_broker_account_user_run;

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'chk_engine_state_paper_cash_non_negative') THEN
    ALTER TABLE engine_state_paper VALIDATE CONSTRAINT chk_engine_state_paper_cash_non_negative;
  END IF;
  IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'chk_paper_broker_cash_non_negative') THEN
    ALTER TABLE paper_broker_account VALIDATE CONSTRAINT chk_paper_broker_cash_non_negative;
  END IF;
END $$;

COMMIT;