drop table if exists user;
CREATE TABLE `user` (
  `id` char(19) NOT NULL COMMENT '用户ID',
  `nickname` varchar(255) DEFAULT NULL COMMENT '微信昵称',
  `avatar` varchar(255) DEFAULT NULL COMMENT '微信头像',
  `unionid` varchar(200) DEFAULT NULL COMMENT '微信用户唯一标识',
  `openid` varchar(200) DEFAULT NULL COMMENT '微信用户唯一标识',
  `telephone` varchar(255) NOT NULL UNIQUE COMMENT '手机号',
  `username` varchar(255) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码',
  `role_list` varchar(255) NOT NULL COMMENT '角色',
  `create_at` timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
  `is_enable` boolean NOT NULL DEFAULT true COMMENT '是否启用',
  `email` varchar(255) DEFAULT NULL COMMENT '邮箱',
  `recover_code` varchar(10) DEFAULT NULL COMMENT '验证码',
  `valid_end` date DEFAULT NULL COMMENT '验证码有效期',
  `current_role` varchar(50) NOT NULL COMMENT '当前角色',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

drop table if exists club;
CREATE TABLE `club` (
  `id` CHAR(19) NOT NULL COMMENT '俱乐部ID',
	`name` VARCHAR(255) NOT NULL COMMENT '俱乐部名',
	`city` VARCHAR(255) NOT NULL COMMENT '所在城市',
	`create_at` timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

drop table if exists club_player;
CREATE TABLE `club_player` (
	`id` char(19) NOT NULL COMMENT 'ID',
  `club_id` char(19) NOT NULL COMMENT '俱乐部ID',
	`player_id` char(19) NOT NULL COMMENT '球员ID',
	`create_at` timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
	`role` VARCHAR(50) NOT NULL COMMENT '在俱乐部中角色',
  `is_enable` boolean NOT NULL DEFAULT true COMMENT '是否启用',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

drop table if exists `event`;
CREATE TABLE `event` (
  `id` char(19) NOT NULL COMMENT '赛事ID',
	`name` VARCHAR(255) NOT NULL COMMENT '赛事名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

drop table if exists game;
CREATE TABLE `game` (
  `id` char(19) NOT NULL COMMENT '比赛ID',
  `year` BIGINT NOT NULL COMMENT '年份',
  `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '比赛日期',
	`our_team_id` char(19) NOT NULL COMMENT '我方俱乐部id',
  `opponent_team_name` varchar(255) NOT NULL COMMENT '对方俱乐部名',
	`our_score` BIGINT NOT NULL DEFAULT(0) COMMENT '我方得分',
	`opponent_score` BIGINT NOT NULL DEFAULT(0) COMMENT '对方得分',
	`event_id` char(19) NOT NULL COMMENT '赛事id',
	`venue` VARCHAR(255) NOT NULL COMMENT '比赛地点',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

drop table if exists `game_player`;
CREATE TABLE `game_player` (
	`id` char(19) NOT NULL COMMENT 'ID',
  `game_id` char(19) NOT NULL COMMENT '比赛ID',
	`player_id` char(19) NOT NULL COMMENT '球员ID',
	`club_id` char(19) NOT NULL COMMENT '主队ID',
	`game_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '比赛时间',
	`is_mvp` boolean NOT NULL DEFAULT false COMMENT '是否mvp',
	`goal` BIGINT NOT NULL DEFAULT(0) COMMENT '进球数',
	`assist` BIGINT NOT NULL DEFAULT(0) COMMENT '助攻数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

drop table if exists `player_last_club`;
CREATE TABLE `player_last_club` (
	`id` char(19) NOT NULL COMMENT 'ID',
	`player_id` char(19) NOT NULL COMMENT '球员ID',
	`last_club_id` CHAR(19) NOT NULL COMMENT '上次登录的俱乐部ID',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

drop table if exists game_image;
CREATE TABLE `game_image` (
  `id` CHAR(19) NOT NULL COMMENT 'UUID',
	`game_id` CHAR(19) NOT NULL COMMENT '比赛ID',
	`image_path` VARCHAR(255) NOT NULL COMMENT '图片相对地址',
	`create_at` timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',
	`image_rank` BIGINT NOT NULL COMMENT '排序',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

insert into `user`
	value('1760191788309561344', null, null, null, null, '18038383020', '陈硕', '$2a$10$QjlKtERrUrhyVs1ynL5k7uU2jSMVhDOHE69QcBz9nUhIGv7McDEam', '["ROLE_ADMIN", "ROLE_USER"]', CURRENT_TIMESTAMP(), 1, null, null, null, 'ROLE_ADMIN');
	
insert into `user`
	value('1760191788309561345', null, null, null, null, '15013153445', '15013153445', '$2a$10$/U7vwhM979HBDMaT6q5B9.5de/tPvDGXTnY6vODEci.H/h6irIr/i', '["ROLE_ADMIN", "ROLE_USER"]', CURRENT_TIMESTAMP(), 1, null, null, null, 'ROLE_ADMIN');
	
insert into `player_last_club`
	value('1771145461005119488', '1760191788309561344', '1760925290416164864');

insert into `player_last_club`
	value('1771145461005119489', '1760191788309561345', '1760925290416164864');
	
insert into `club`
	value('1760925290416164864', '湛江华工校友足球俱乐部', '广东湛江', CURRENT_TIMESTAMP());
	
insert into `club`
	value('1760925290416164865', '顺德茂商足球俱乐部', '广东顺德', CURRENT_TIMESTAMP());
	
insert into `event`
	value('1762371996437528576', '训练赛');