-- 添加外键约束以确保数据完整性
-- V17__Add_foreign_key_constraints.sql

-- 为 api_keys 表添加 activity_id 外键约束
ALTER TABLE api_keys
ADD CONSTRAINT fk_api_keys_activity
FOREIGN KEY (activity_id) REFERENCES activities(id)
ON DELETE SET NULL;

-- 为 short_links 表添加 activity_id 外键约束
ALTER TABLE short_links
ADD CONSTRAINT fk_short_links_activity
FOREIGN KEY (activity_id) REFERENCES activities(id)
ON DELETE SET NULL;

-- 为 short_links 表添加 inviter_user_id 外键约束 (假设有 users 表)
-- 如果没有 users 表，此约束需要调整或移除
-- ALTER TABLE short_links
-- ADD CONSTRAINT fk_short_links_inviter
-- FOREIGN KEY (inviter_user_id) REFERENCES users(id)
-- ON DELETE SET NULL;

-- 为 user_invites 表添加外键约束（已在 V9 创建时声明，避免重复）
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'fk_user_invites_activity'
  ) THEN
    ALTER TABLE user_invites
    ADD CONSTRAINT fk_user_invites_activity
    FOREIGN KEY (activity_id) REFERENCES activities(id)
    ON DELETE CASCADE;
  END IF;
END $$;

-- 仅当 users 表存在时再添加 inviter/invitee 外键约束
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.tables
    WHERE table_schema = 'public' AND table_name = 'users'
  ) THEN
    IF NOT EXISTS (
      SELECT 1 FROM pg_constraint WHERE conname = 'fk_user_invites_inviter'
    ) THEN
      ALTER TABLE user_invites
      ADD CONSTRAINT fk_user_invites_inviter
      FOREIGN KEY (inviter_user_id) REFERENCES users(id)
      ON DELETE CASCADE;
    END IF;

    IF NOT EXISTS (
      SELECT 1 FROM pg_constraint WHERE conname = 'fk_user_invites_invitee'
    ) THEN
      ALTER TABLE user_invites
      ADD CONSTRAINT fk_user_invites_invitee
      FOREIGN KEY (invitee_user_id) REFERENCES users(id)
      ON DELETE CASCADE;
    END IF;
  END IF;
END $$;

-- 为 link_clicks 表添加外键约束
ALTER TABLE link_clicks
ADD CONSTRAINT fk_link_clicks_activity
FOREIGN KEY (activity_id) REFERENCES activities(id)
ON DELETE SET NULL;

-- 为 daily_activity_stats 表添加外键约束
ALTER TABLE daily_activity_stats
ADD CONSTRAINT fk_daily_stats_activity
FOREIGN KEY (activity_id) REFERENCES activities(id)
ON DELETE CASCADE;

-- 为 activity_rewards 表添加外键约束
ALTER TABLE activity_rewards
ADD CONSTRAINT fk_activity_rewards_activity
FOREIGN KEY (activity_id) REFERENCES activities(id)
ON DELETE CASCADE;

-- 为 reward_jobs 表添加外键约束
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name = 'reward_jobs' AND column_name = 'activity_id'
  ) THEN
    IF NOT EXISTS (
      SELECT 1 FROM pg_constraint WHERE conname = 'fk_reward_jobs_activity'
    ) THEN
      ALTER TABLE reward_jobs
      ADD CONSTRAINT fk_reward_jobs_activity
      FOREIGN KEY (activity_id) REFERENCES activities(id)
      ON DELETE SET NULL;
    END IF;
  END IF;
END $$;

-- 添加缺失的索引以优化查询性能
CREATE INDEX IF NOT EXISTS idx_user_invites_activity_invitee ON user_invites(activity_id, invitee_user_id);
CREATE INDEX IF NOT EXISTS idx_link_clicks_code ON link_clicks(code);
CREATE INDEX IF NOT EXISTS idx_link_clicks_activity ON link_clicks(activity_id);
CREATE INDEX IF NOT EXISTS idx_daily_stats_activity_date ON daily_activity_stats(activity_id, stat_date);
CREATE INDEX IF NOT EXISTS idx_reward_jobs_status ON reward_jobs(status);
CREATE INDEX IF NOT EXISTS idx_reward_jobs_next_run ON reward_jobs(next_run_at);

-- 注意: 如果 users 表不存在，需要先创建
-- CREATE TABLE IF NOT EXISTS users (
--     id BIGSERIAL PRIMARY KEY,
--     username VARCHAR(255) NOT NULL UNIQUE,
--     email VARCHAR(255) NOT NULL UNIQUE,
--     created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
-- );
