-- Movie Select schema
-- Run once: mysql -u serve -p serve < setup-db.sql

CREATE TABLE IF NOT EXISTS rounds (
	uuid CHAR(36) PRIMARY KEY,
	phase TINYINT NOT NULL DEFAULT 1,
	setup_done TINYINT(1) NOT NULL DEFAULT 0,
	created_at DATETIME NOT NULL DEFAULT NOW(),
	updated_at DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW()
);

CREATE TABLE IF NOT EXISTS round_users (
	round_uuid CHAR(36) NOT NULL,
	name VARCHAR(30) NOT NULL,
	done_phase1 TINYINT(1) NOT NULL DEFAULT 0,
	done_phase2 TINYINT(1) NOT NULL DEFAULT 0,
	sort_order INT NOT NULL DEFAULT 0,
	PRIMARY KEY (round_uuid, name),
	FOREIGN KEY (round_uuid) REFERENCES rounds(uuid) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS movies (
	id INT AUTO_INCREMENT PRIMARY KEY,
	round_uuid CHAR(36) NOT NULL,
	title VARCHAR(60) NOT NULL,
	added_by VARCHAR(30) NOT NULL,
	added_at DATETIME NOT NULL DEFAULT NOW(),
	UNIQUE KEY uq_round_title (round_uuid, title),
	FOREIGN KEY (round_uuid) REFERENCES rounds(uuid) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS votes (
	round_uuid CHAR(36) NOT NULL,
	user_name VARCHAR(30) NOT NULL,
	movie_title VARCHAR(60) NOT NULL,
	rating TINYINT NOT NULL,
	PRIMARY KEY (round_uuid, user_name, movie_title),
	FOREIGN KEY (round_uuid) REFERENCES rounds(uuid) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS round_history (
	id INT AUTO_INCREMENT PRIMARY KEY,
	round_uuid CHAR(36) NOT NULL,
	winner VARCHAR(60),
	movies_json TEXT NOT NULL,
	created_at DATETIME NOT NULL DEFAULT NOW(),
	FOREIGN KEY (round_uuid) REFERENCES rounds(uuid) ON DELETE CASCADE
);