-- 手工迁移兜底：为所有业务表补 created_at / updated_at
-- 执行前建议先备份数据库。

BEGIN;

-- 1) 大多数表已有 created_at，只补 updated_at
ALTER TABLE messages ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
ALTER TABLE bans ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
ALTER TABLE actions ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
ALTER TABLE verify_queue ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
ALTER TABLE warnings ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
ALTER TABLE game_scores ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
ALTER TABLE game_scores ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();

-- 2) user_bindings 原来有 bound_at，这里补统一审计字段
ALTER TABLE user_bindings ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
ALTER TABLE user_bindings ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
UPDATE user_bindings SET created_at = COALESCE(bound_at, created_at) WHERE bound_at IS NOT NULL;

-- 3) group_config 原来只有 updated_at，这里补 created_at，并修正 updated_at 默认值
ALTER TABLE group_config ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
ALTER TABLE group_config ALTER COLUMN updated_at SET DEFAULT NOW();

-- 4) warnings 字段统一：issued_by -> operator_id
ALTER TABLE warnings ADD COLUMN IF NOT EXISTS operator_id BIGINT;
UPDATE warnings SET operator_id = issued_by WHERE operator_id IS NULL AND issued_by IS NOT NULL;

-- 5) 常用索引
CREATE INDEX IF NOT EXISTS idx_messages_chat_msg ON messages (chat_id, msg_id);
CREATE INDEX IF NOT EXISTS idx_messages_user_created ON messages (user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_messages_spam_created ON messages (is_spam, created_at);
CREATE INDEX IF NOT EXISTS idx_bans_user_created ON bans (user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_bans_auto_unban ON bans (auto_unban, unban_at);
CREATE INDEX IF NOT EXISTS idx_actions_action_created ON actions (action, created_at);
CREATE INDEX IF NOT EXISTS idx_actions_user_created ON actions (user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_actions_operator_created ON actions (operator_id, created_at);
CREATE INDEX IF NOT EXISTS idx_verify_user_status ON verify_queue (user_id, status);
CREATE INDEX IF NOT EXISTS idx_verify_status_created ON verify_queue (status, created_at);
CREATE INDEX IF NOT EXISTS idx_warnings_user_active ON warnings (user_id, active);
CREATE INDEX IF NOT EXISTS idx_warnings_operator_created ON warnings (operator_id, created_at);
CREATE INDEX IF NOT EXISTS idx_game_scores_type_score ON game_scores (game_type, score);
CREATE INDEX IF NOT EXISTS idx_game_scores_user_type ON game_scores (user_id, game_type);
CREATE INDEX IF NOT EXISTS idx_user_bindings_harvest_uid ON user_bindings (harvest_uid);

COMMIT;