CREATE TABLE IF NOT EXISTS orgs (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(140) NOT NULL, type ENUM('CHSLD','RPA','Autre','ETCC') NOT NULL, active BOOLEAN NOT NULL DEFAULT TRUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS users (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, org INT UNSIGNED NULL, name VARCHAR(100) NOT NULL, email VARCHAR(254) NOT NULL UNIQUE,
 role ENUM('super','admin','member') NOT NULL, password VARCHAR(255) NULL, active BOOLEAN NOT NULL DEFAULT TRUE, revision INT UNSIGNED NOT NULL DEFAULT 0,
 FOREIGN KEY (org) REFERENCES orgs(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS invitations (
 user_id INT UNSIGNED PRIMARY KEY, digest CHAR(64) CHARACTER SET ascii NOT NULL UNIQUE, expires_at BIGINT NOT NULL,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS attempts (
 bucket CHAR(64) CHARACTER SET ascii PRIMARY KEY, hits INT UNSIGNED NOT NULL, expires_at BIGINT NOT NULL, INDEX(expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS game_sessions (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, org INT UNSIGNED NOT NULL,
 game VARCHAR(40) NOT NULL, mode VARCHAR(30) NOT NULL, format VARCHAR(60) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (org) REFERENCES orgs(id), INDEX(org,id), INDEX(user_id,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS posts (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, session_id INT UNSIGNED NOT NULL, author INT UNSIGNED NOT NULL,
 heading VARCHAR(110) NOT NULL, body TEXT NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'À lire', visible BOOLEAN NOT NULL DEFAULT TRUE,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (session_id) REFERENCES game_sessions(id), FOREIGN KEY (author) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
