bazar3/server/schema.sql

CREATE DATABASE IF NOT EXISTS kingdb_prod;
USE kingdb_prod;

CREATE TABLE teams (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE results (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team_id INT NOT NULL,
  result_time DATETIME NOT NULL, -- When the result will be shown
  result VARCHAR(10) NOT NULL DEFAULT '-1', -- Default result if admin hasn't set it
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  access_key CHAR(64) UNIQUE NOT NULL,
  argon2_hash TEXT NOT NULL,
  session_token CHAR(64),
  is_active BOOLEAN DEFAULT TRUE,
  last_access TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;