create database if not exists community_activity default character set utf8mb4 collate utf8mb4_general_ci;
use community_activity;

create table if not exists sys_user (
  id bigint primary key auto_increment,
  username varchar(64) not null unique,
  password_hash varchar(255) not null,
  nickname varchar(64) not null,
  phone varchar(32),
  role varchar(32) not null default 'user',
  created_at datetime not null,
  updated_at datetime not null
);

create table if not exists activity (
  id bigint primary key auto_increment,
  title varchar(255) not null,
  term varchar(64) not null,
  summary varchar(255),
  content text,
  location varchar(255) not null,
  start_time datetime not null,
  end_time datetime not null,
  signup_start datetime not null,
  signup_end datetime not null,
  quota int not null,
  status varchar(32) not null,
  cover_url varchar(255),
  created_by bigint,
  created_at datetime not null,
  updated_at datetime not null,
  index idx_activity_status (status),
  index idx_activity_time (start_time)
);

create table if not exists activity_signup (
  id bigint primary key auto_increment,
  activity_id bigint not null,
  user_id bigint not null,
  status varchar(32) not null,
  checkin_status varchar(32) not null,
  signed_at datetime not null,
  canceled_at datetime,
  checkin_at datetime,
  unique key uk_activity_user (activity_id, user_id),
  index idx_activity (activity_id),
  index idx_user (user_id)
);