-- =====================================================================
-- nns 数据库完整建表脚本（与当前 PHP 小程序后端一致）
-- 在云托管 MySQL 中：先 CREATE DATABASE，再 USE 后执行本文件
--
--   CREATE DATABASE IF NOT EXISTS nns CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
--   USE nns;
--   SOURCE schema_nns.sql;
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- 1. 用户（主键 user_id，供视频/积分榜/俱乐部等外键引用）
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `user_follows`;
DROP TABLE IF EXISTS `video_votes`;
DROP TABLE IF EXISTS `videos`;
DROP TABLE IF EXISTS `clubs`;
DROP TABLE IF EXISTS `score_logs`;
DROP TABLE IF EXISTS `leaderboard_users`;
DROP TABLE IF EXISTS `seasons`;
DROP TABLE IF EXISTS `rank_tiers`;
DROP TABLE IF EXISTS `rank_tier_sets`;
DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `user_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `openid` VARCHAR(64) NOT NULL COMMENT '微信 openid',
  `phone` VARCHAR(20) NULL DEFAULT NULL COMMENT '手机号',
  `password_hash` VARCHAR(255) NULL DEFAULT NULL COMMENT '密码 bcrypt',
  `user_no` VARCHAR(32) NULL DEFAULT NULL COMMENT '展示用编号',
  `nickname` VARCHAR(128) NOT NULL DEFAULT '微信用户',
  `avatar_url` VARCHAR(512) NULL DEFAULT NULL,
  `following_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `follower_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `can_create_club` TINYINT NOT NULL DEFAULT 0 COMMENT '是否可创建俱乐部',
  `is_referee` TINYINT NOT NULL DEFAULT 0 COMMENT '是否裁判',
  `last_active` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `created_at` INT NOT NULL COMMENT 'Unix 时间戳',
  `updated_at` INT NOT NULL COMMENT 'Unix 时间戳',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `uk_openid` (`openid`),
  UNIQUE KEY `uk_users_phone` (`phone`),
  KEY `idx_user_no` (`user_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 2. 段位规则集 / 段位阶梯（rank_utils.php）
-- ---------------------------------------------------------------------
CREATE TABLE `rank_tier_sets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) NOT NULL,
  `created_at` INT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `rank_tiers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `set_id` INT UNSIGNED NOT NULL,
  `code` VARCHAR(32) NOT NULL,
  `name` VARCHAR(64) NOT NULL,
  `min_score` INT NOT NULL DEFAULT 0,
  `max_score` INT NULL DEFAULT NULL COMMENT 'NULL 表示无上限',
  `badge_icon` VARCHAR(255) NULL DEFAULT NULL,
  `display_order` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_set_order` (`set_id`, `display_order`, `min_score`),
  CONSTRAINT `fk_rank_tiers_set` FOREIGN KEY (`set_id`) REFERENCES `rank_tier_sets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 3. 赛季（含可选 rank_tier_set_id）
-- ---------------------------------------------------------------------
CREATE TABLE `seasons` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) NOT NULL,
  `start_at` INT NOT NULL,
  `end_at` INT NOT NULL,
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '0=未开始 1=进行中 2=已结束',
  `rank_tier_set_id` INT UNSIGNED NULL DEFAULT NULL,
  `created_at` INT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status_time` (`status`, `start_at`, `end_at`),
  CONSTRAINT `fk_seasons_tier_set` FOREIGN KEY (`rank_tier_set_id`) REFERENCES `rank_tier_sets` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 4. 积分榜报名与积分流水
-- ---------------------------------------------------------------------
CREATE TABLE `leaderboard_users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `season_id` INT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `total_score` INT UNSIGNED NOT NULL DEFAULT 0,
  `joined_at` INT NOT NULL,
  `updated_at` INT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_season_user` (`season_id`, `user_id`),
  KEY `idx_user` (`user_id`),
  CONSTRAINT `fk_lu_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_lu_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `score_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `season_id` INT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `change_amount` INT NOT NULL,
  `balance_after` INT UNSIGNED NOT NULL,
  `reason` VARCHAR(64) NOT NULL DEFAULT '',
  `created_at` INT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_season_user` (`season_id`, `user_id`),
  KEY `idx_season_user_created` (`season_id`, `user_id`, `created_at`),
  CONSTRAINT `fk_sl_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sl_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 5. 俱乐部
-- ---------------------------------------------------------------------
CREATE TABLE `clubs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `owner_user_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(64) NOT NULL,
  `logo_url` VARCHAR(255) NULL DEFAULT NULL,
  `description` VARCHAR(255) NULL DEFAULT NULL,
  `min_score` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '1=正常 0=停用',
  `created_at` INT NOT NULL,
  `updated_at` INT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_club_name` (`name`),
  KEY `idx_owner` (`owner_user_id`),
  CONSTRAINT `fk_clubs_owner` FOREIGN KEY (`owner_user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 6. 挑战视频与投票（setup_videos.php）
-- ---------------------------------------------------------------------
CREATE TABLE `videos` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `season_id` INT UNSIGNED NULL DEFAULT NULL COMMENT '发布时所属赛季，积分榜仅统计该赛季下视频赞积分',
  `title` VARCHAR(120) NOT NULL DEFAULT '',
  `description` VARCHAR(500) NOT NULL DEFAULT '',
  `file_id` VARCHAR(255) NOT NULL COMMENT '微信云 fileID',
  `cover_file_id` VARCHAR(255) NULL DEFAULT NULL,
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '1=正常 0=下线',
  `created_at` INT NOT NULL,
  `updated_at` INT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status_created` (`status`, `created_at`),
  KEY `idx_user` (`user_id`),
  KEY `idx_season_user` (`season_id`, `user_id`),
  CONSTRAINT `fk_videos_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_videos_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `video_votes` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `video_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `vote_type` TINYINT NOT NULL COMMENT '1=赞 -1=踩',
  `created_at` INT NOT NULL,
  `updated_at` INT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_video_user` (`video_id`, `user_id`),
  KEY `idx_video` (`video_id`),
  KEY `idx_user` (`user_id`),
  CONSTRAINT `fk_video_votes_video` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_video_votes_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 7. 关注（userHome / followAction：使用 BIGINT 与 user_id 一致）
-- ---------------------------------------------------------------------
CREATE TABLE `user_follows` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `follower_user_id` BIGINT UNSIGNED NOT NULL,
  `followed_user_id` BIGINT UNSIGNED NOT NULL,
  `created_at` INT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_follow` (`follower_user_id`, `followed_user_id`),
  KEY `idx_followed` (`followed_user_id`),
  CONSTRAINT `fk_uf_follower` FOREIGN KEY (`follower_user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_uf_followed` FOREIGN KEY (`followed_user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 7b. 裁判（H5 待收录 + 已收录资料）
-- ---------------------------------------------------------------------
CREATE TABLE `referee_submissions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NULL,
  `openid` VARCHAR(64) NULL,
  `upload_key` VARCHAR(64) NULL,
  `real_name` VARCHAR(64) NOT NULL DEFAULT '',
  `city` VARCHAR(64) NOT NULL DEFAULT '',
  `job_title` VARCHAR(128) NOT NULL DEFAULT '',
  `honors` TEXT NULL,
  `club_name` VARCHAR(128) NOT NULL DEFAULT '',
  `avatar_url` VARCHAR(512) NOT NULL DEFAULT '',
  `status` ENUM('pending','adopted','rejected') NOT NULL DEFAULT 'pending',
  `reject_reason` VARCHAR(255) NULL,
  `reviewed_by` VARCHAR(64) NULL,
  `reviewed_at` INT NULL,
  `created_at` INT NOT NULL,
  `updated_at` INT NOT NULL,
  KEY `idx_status_created` (`status`, `created_at`),
  KEY `idx_openid_status` (`openid`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `referee_profiles` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `submission_id` BIGINT UNSIGNED NULL,
  `real_name` VARCHAR(64) NOT NULL DEFAULT '',
  `city` VARCHAR(64) NOT NULL DEFAULT '',
  `job_title` VARCHAR(128) NOT NULL DEFAULT '',
  `honors` TEXT NULL,
  `club_name` VARCHAR(128) NOT NULL DEFAULT '',
  `avatar_url` VARCHAR(512) NOT NULL DEFAULT '',
  `created_at` INT NOT NULL,
  `updated_at` INT NOT NULL,
  UNIQUE KEY `uk_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------------
-- 8. 最小初始化数据（可按需改时间范围）
-- ---------------------------------------------------------------------
INSERT INTO `rank_tier_sets` (`id`, `name`, `created_at`) VALUES
(1, '默认规则集', UNIX_TIMESTAMP());

-- 示例段位：积分区间按业务再调
INSERT INTO `rank_tiers` (`set_id`, `code`, `name`, `min_score`, `max_score`, `badge_icon`, `display_order`) VALUES
(1, 'bronze', '青铜', 0, 99, '', 1),
(1, 'silver', '白银', 100, 299, '', 2),
(1, 'gold', '黄金', 300, 599, '', 3),
(1, 'platinum', '铂金', 600, NULL, '', 4);

-- 一个进行中的赛季（2026 整年示例，可按需改）
INSERT INTO `seasons` (`id`, `name`, `start_at`, `end_at`, `status`, `rank_tier_set_id`, `created_at`) VALUES
(1, '2026 赛季', 1735689600, 1767196800, 1, 1, UNIX_TIMESTAMP());
-- start_at/end_at 为 Unix 秒；若需改赛季，改这两个数与 name 即可
