db.ddl

-- CREATE ROLE mo_osmo LOGIN PASSWORD 'pass';
-- CREATE DATABASE mo_osmo WITH OWNER=mo_osmo;
-- GRANT mo_osmo TO some_admin;

SET ROLE mo_osmo;

-- Uživatelský účet
CREATE TABLE users (
	user_id		serial		PRIMARY KEY,
	email		varchar(255)	UNIQUE NOT NULL,
	first_name	varchar(255)	COLLATE "cs_CZ" NOT NULL,
	last_name	varchar(255)	COLLATE "cs_CZ" NOT NULL,
	is_org		boolean		NOT NULL DEFAULT false,		-- přístup do organizátorské části webu
	is_admin	boolean		NOT NULL DEFAULT false,		-- globální administrátorské právo
	is_test		boolean		NOT NULL DEFAULT false,		-- testovací účastník, není vidět ve výsledkovkách
	created_at	timestamp with time zone	NOT NULL DEFAULT CURRENT_TIMESTAMP,
	last_login_at	timestamp with time zone	DEFAULT NULL,
	reset_at	timestamp with time zone	DEFAULT NULL,	-- poslední požadavek na reset hesla
	password_hash	varchar(255)	DEFAULT NULL,			-- heš hesla (je-li nastaveno)
	note		text		NOT NULL DEFAULT ''		-- poznámka viditelná pro orgy
);

-- Hierarchie regionů a organizací

CREATE TYPE place_type AS ENUM (
	'region',	-- správní celek
	'school',
	'site'		-- soutěžní místo
);

CREATE TABLE places (
	place_id	serial		PRIMARY KEY,
	level		int		NOT NULL,			-- úroveň v hierarchii:
									-- 	0	root
									-- 	1	kraj
									-- 	2	okres
									--	3	město
									-- 	4	škola / soutěžní místo
	parent		int		REFERENCES places(place_id),	-- NULL pro root
	name		varchar(255)	COLLATE "cs_CZ",		-- oficiální jméno
	code		varchar(255)	UNIQUE COLLATE "cs_CZ",		-- zkratka pro zadávání
	type		place_type	NOT NULL,
	nuts		varchar(255)	UNIQUE DEFAULT NULL,		-- evropský NUTS/LAU kód regionu
	note		text		NOT NULL DEFAULT ''		-- uživatelská poznámka
);

CREATE INDEX places_parent_index ON places (parent);

-- Rekurzivní dotaz na nadřazené regiony:
--	WITH RECURSIVE parent_regions(parent, place_id) AS (
--		SELECT parent, place_id FROM places WHERE place_id=...
--		UNION select p.parent, p.place_id FROM parent_regions pr, places p
--			WHERE p.place_id = pr.parent
--	) SELECT parent FROM parent_regions;

-- Podobně na podřízené:
--	WITH RECURSIVE descendant_regions(descendant, place_id) AS (
--		SELECT place_id, parent FROM places WHERE parent=...
--		UNION select p.place_id, p.parent FROM descendant_regions pr, places p
--			WHERE p.parent = pr.descendant
--	) SELECT descendant FROM descendant_regions;

-- Škola je speciální typ místa s doplňujícími informacemi
CREATE TABLE schools (
	place_id	int		PRIMARY KEY REFERENCES places(place_id) ON DELETE CASCADE,
	red_izo		varchar(255)	DEFAULT NULL,
	ico		varchar(255)	DEFAULT NULL,
	official_name	varchar(255)	DEFAULT NULL,
	address		varchar(255)	DEFAULT NULL,
	is_zs		boolean		NOT NULL DEFAULT false,
	is_ss		boolean		NOT NULL DEFAULT false
);

-- Soutěžní kola

CREATE TYPE round_state AS ENUM (
	'preparing',			-- v přípravě (viditelné pouze organizátorům)
	'running',			-- je možno odevzdávat
	'grading',			-- je možno opravovat a vyplňovat body
	'closed',			-- uzavřeno, není dovoleno nic měnit, zveřejněny výsledky
	-- Garanta stavy neomezují, vždycky může všechno.
	-- Ve stavu "running" mohou odevzdávat účastníci i dozor, a to i po termínu,
	-- jen se odevzdaná řešení zobrazují jako opožděná.
	'delegate'			-- každá soutěž má svůj stav
);

CREATE TYPE score_mode AS ENUM (
	'basic',			-- základní mód výsledkovky se sdílenými místy
	'mo'				-- jednoznačné pořadí podle pravidel MO
);

CREATE TABLE rounds (
	round_id	serial		PRIMARY KEY,
	master_round_id	int		DEFAULT NULL REFERENCES rounds(round_id),
	year		int		NOT NULL,			-- ročník MO
	category	varchar(2)	NOT NULL,			-- "A", "Z5" apod.
	seq		int		NOT NULL,			-- 1=domácí kolo atd.
	part		int		NOT NULL DEFAULT 0,		-- část kola (nenulová u dělených kol)
	level		int		NOT NULL,			-- úroveň hierarchie míst
	name		varchar(255)	NOT NULL,			-- zobrazované jméno ("Krajské kolo" apod.)
	state		round_state	NOT NULL DEFAULT 'preparing',	-- stav kola
	tasks_file	varchar(255)	DEFAULT NULL,			-- jméno souboru se zadáním úloh
	ct_tasks_start	timestamp with time zone	DEFAULT NULL,	-- od kdy účastníci vidí zadání
	ct_submit_end	timestamp with time zone	DEFAULT NULL,	-- do kdy účastníci mohou regulérně odevzdávat
	pr_tasks_start	timestamp with time zone	DEFAULT NULL,	-- od kdy dozor vidí zadání
	pr_submit_end	timestamp with time zone	DEFAULT NULL,	-- do kdy dozor může regulérně odevzdávat
	score_mode	score_mode	NOT NULL DEFAULT 'basic',	-- mód výsledkovky
	score_winner_limit	int	DEFAULT NULL,			-- bodový limit na označení za vítěze
	score_successful_limit	int	DEFAULT NULL,			-- bodový limit na označení za úspěšného řešitele
	points_step	numeric(2,1)	NOT NULL DEFAULT 1,		-- s jakou přesností jsou přidělovány body (celé aneb 1, 0.5, 0.1)
	has_messages	boolean		NOT NULL DEFAULT false,		-- má zprávičky
	UNIQUE (year, category, seq, part)
);

CREATE INDEX rounds_master_round_id_index ON rounds (master_round_id);

-- Soutěže (instance kola v konkrétním místě)
CREATE TABLE contests (
	contest_id		serial		PRIMARY KEY,
	master_contest_id	int		DEFAULT NULL REFERENCES contests(contest_id),
	round_id		int		NOT NULL REFERENCES rounds(round_id),
	place_id		int		NOT NULL REFERENCES places(place_id),
	state			round_state	NOT NULL DEFAULT 'preparing',	-- používá se, pokud round.state='delegate', jinak kopíruje round.state
	UNIQUE (round_id, place_id)
);

CREATE INDEX contests_master_contest_id_index ON contests (master_contest_id);

-- Detaily účastníka
CREATE TABLE participants (
	user_id		int		NOT NULL REFERENCES users(user_id),
	year		int		NOT NULL,			-- ročník MO
	school		int		NOT NULL REFERENCES places(place_id),
	birth_year	int		NOT NULL,
	grade		varchar(20)	NOT NULL,			-- třída ve tvaru "X/Y"
	PRIMARY KEY (user_id, year)
);

-- Účast v soutěžním kole

CREATE TYPE part_state AS ENUM (
	'registered',		-- sám se přihlásil
	'invited',		-- pozván
	'refused',		-- odmítl účast
	'present',		-- soutěžil
	'absent',		-- bez omluvy nedorazil
	'disqualified'		-- diskvalifikovaný
);

CREATE TABLE participations (
	user_id		int		NOT NULL REFERENCES users(user_id),
	contest_id	int		NOT NULL REFERENCES contests(contest_id),
	place_id	int		NOT NULL REFERENCES places(place_id),	-- konkrétní soutěžní místo (default: region contestu)
	state		part_state	NOT NULL,
	PRIMARY KEY (user_id, contest_id)
);

CREATE INDEX participations_contest_id_index ON participations (contest_id, place_id);

-- Úloha
CREATE TABLE tasks (
	task_id		serial		PRIMARY KEY,
	round_id	int		NOT NULL REFERENCES rounds(round_id),
	code		varchar(255)	NOT NULL,			-- např. "P-I-1"
	name		varchar(255)	NOT NULL,
	max_points	numeric(5,1)	DEFAULT NULL,			-- maximální počet bodů, pokud je nastaven, nelze zadat více bodů
	UNIQUE (round_id, code)
);

-- Spisek (odevzdané řešení, opoznámkované řešení apod.)

CREATE TYPE paper_type AS ENUM (
	'solution',	-- odevzdané řešení
	'feedback'	-- komentáře opravovatelů
);

CREATE TABLE papers (
	paper_id	serial		PRIMARY KEY,
	for_task	int		NOT NULL REFERENCES tasks(task_id),
	for_user	int		NOT NULL REFERENCES users(user_id),
	type		paper_type	NOT NULL,
	uploaded_by	int		NOT NULL REFERENCES users(user_id),
	uploaded_at	timestamp with time zone	NOT NULL DEFAULT CURRENT_TIMESTAMP,
	pages		int		DEFAULT NULL,			-- počet stránek
	bytes		int		DEFAULT NULL,			-- velikost souboru
	file_name	varchar(255)	DEFAULT NULL,			-- relativní cesta k souboru
	orig_file_name	varchar(255)	DEFAULT NULL,			-- původní cesta, pokud PDF bylo poničené
	note		text		NOT NULL DEFAULT ''		-- komentář uploadujícího
	-- Sémantika práce s poničenými soubory:
	--    - správná PDF mají orig_file_name=NULL
	--    - pokud někdo odevzdá poničené, vyplní se orig_file_name, ale file_name=NULL
	--    - časem se spustí oprava, která vyplní i file_name a přepočítá bytes
);

CREATE INDEX papers_for_task_index ON papers (for_task);
CREATE INDEX papers_for_user_index ON papers (for_user);

-- Odevzdané řešení
CREATE TABLE solutions (
	task_id		int		NOT NULL REFERENCES tasks(task_id),
	user_id		int		NOT NULL REFERENCES users(user_id),
	final_submit	int		DEFAULT NULL REFERENCES papers(paper_id),	-- verze odevzdání, která se má hodnotit
	final_feedback	int		DEFAULT NULL REFERENCES papers(paper_id),	-- verze komentáře opravovatelů, kterou má vidět účastník
	points		numeric(5,1)	DEFAULT NULL,
	note		text		NOT NULL DEFAULT '',			-- komentář pro řešitele
	org_note	text		NOT NULL DEFAULT '',			-- komentář viditelný jen organizátorům
	PRIMARY KEY (task_id, user_id)
);

CREATE INDEX solutions_user_id_index ON solutions (user_id);

-- Historie přidělování bodů
CREATE TABLE points_history (
	points_history_id	serial	PRIMARY KEY,
	task_id		int		NOT NULL REFERENCES tasks(task_id),
	participant_id	int		NOT NULL REFERENCES users(user_id),
	points		numeric(5,1)	DEFAULT NULL,
	points_by	int		NOT NULL REFERENCES users(user_id),		-- kdo přidělil body
	points_at	timestamp with time zone	NOT NULL			-- a kdy
);

CREATE INDEX points_history_index ON points_history (task_id, participant_id);

-- Organizátorská role
CREATE TYPE role_type AS ENUM (
	'garant',		-- celostátní garant (může být omezený na kategorii)
	'garant_kraj',		-- krajský garant
	'garant_okres',		-- okresní garant
	'garant_skola',		-- školní garant
	'dozor',		-- dozor na soutěži (může odevzdávat řešení za účastníky)
	'opravovatel'		-- opravovatel
);

-- Uživatelům majícím is_org=true lze přidělit roli ke konkrétnímu regionu (včetně podregionů) a volitelně kategorii/kolu
-- HACK: Pokud category='Z', role platí pro všechny kategorie začínající na 'Z' (extra výjimka v mo.rights)
CREATE TABLE user_roles (
	user_role_id	serial		PRIMARY KEY,
	user_id		int		NOT NULL REFERENCES users(user_id),
	place_id	int		NOT NULL REFERENCES places(place_id),
	role		role_type	NOT NULL,
	category	varchar(2)	DEFAULT NULL,
	year		int		DEFAULT NULL,
	seq		int		DEFAULT NULL,
	assigned_by	int		DEFAULT NULL REFERENCES users(user_id),		-- kdo a kdy roli přidělil (NULL=admin DB)
	assigned_at	timestamp with time zone	NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Společný log změn (pozor, nezaručujeme referenční integritu!)

CREATE TYPE log_type AS ENUM (
	'general',	-- všeobecné logování, id=0
	'user',		-- users(user_id)
	'place',	-- places(place_id)
	'round',	-- rounds(round_id)
	'contest',	-- contests(contest_id)
	'participant',	-- participants(user_id)
	'task',		-- tasks(task_id)
	'user_role'	-- user_roles(user_id)
);

CREATE TABLE log (
	log_entry_id	serial		PRIMARY KEY,
	changed_by	int		REFERENCES users(user_id),		-- kdo změnu provedl
	changed_at	timestamp with time zone	NOT NULL DEFAULT CURRENT_TIMESTAMP,	-- a kdy
	type		log_type	NOT NULL,
	id		int		NOT NULL,					-- jakého záznamu se změna týká
	details		jsonb		NOT NULL					-- detaily (závislé na typu)
);

CREATE INDEX log_type_id_index ON log (type, id);

-- Asynchronní úlohy

CREATE TYPE job_type AS ENUM (
	'download_submits',
	'upload_feedback',
	'create_protocols',
	'process_scans'
);

CREATE TYPE job_state AS ENUM (
	'preparing',
	'ready',
	'running',
	'done',				-- Hotovo, out_json a out_file jsou platné
	'failed'			-- Interní chyba při zpracování, viz log
);

CREATE TABLE jobs (
	job_id		serial		PRIMARY KEY,
	type		job_type	NOT NULL,
	state		job_state	NOT NULL,
	user_id		int		NOT NULL REFERENCES users(user_id),			-- komu patří
	created_at	timestamp with time zone	NOT NULL DEFAULT CURRENT_TIMESTAMP,	-- kdy byl založen
	finished_at	timestamp with time zone	DEFAULT NULL,				-- kdy doběhl
	expires_at	timestamp with time zone	DEFAULT NULL,				-- kdy bude automaticky smazán
	description	text		NOT NULL DEFAULT '',					-- popis jobu
	result		text		NOT NULL DEFAULT '',					-- jednořádková zpráva o výsledku
	-- Vstupní parametry a výsledky jobu
	in_json		jsonb		DEFAULT NULL,
	out_json	jsonb		DEFAULT NULL,
	-- Soubory jsou součástí úlohy a po její expiraci budou smazány
	in_file		varchar(255)	DEFAULT NULL,
	out_file	varchar(255)	DEFAULT NULL
);

-- Zprávičky k soutěžím

CREATE TABLE messages (
	message_id	serial		PRIMARY KEY,
	round_id	int		NOT NULL REFERENCES rounds(round_id),
	created_at	timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,	-- čas publikování zprávičky
	created_by	int		NOT NULL REFERENCES users(user_id),		-- autor zprávičky
	title		text		NOT NULL,
	markdown	text		NOT NULL,
	html		text		NOT NULL
);

-- Stav zpracování scanů (vázaný na joby)

CREATE TABLE scan_pages (
	job_id		int		NOT NULL REFERENCES jobs(job_id) ON DELETE CASCADE,
	file_nr		int		NOT NULL,					-- co to je za stránku (od 0)
	page_nr		int		NOT NULL,
	user_id		int		DEFAULT NULL REFERENCES users(user_id),		-- přiřazení účastníkovi a úloze
	task_id		int		DEFAULT NULL REFERENCES tasks(task_id),
	seq_id		int		NOT NULL,					-- pořadové číslo v rámci úlohy (od 0)
	-- Pokud user_id i task_id jsou NULL, seq_id znamená:
	--	-1	pro stránku vyžadující pozornost
	--	-2	pro prázdnou stránku
	--	-3	pro pokračovací stránku
	--	-4	pro stránku, která nepatří do této soutěže
	UNIQUE (job_id, file_nr, page_nr)
);