db.ddl

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

SET ROLE mo_osmo;

-- Funkce pro odakcentování textu pomocí extension unaccent.
-- Je immutable, takže se dá používat i v indexech.
-- Zdroj: http://stackoverflow.com/questions/11005036/does-postgresql-support-accent-insensitive-collations
CREATE OR REPLACE FUNCTION f_unaccent(text)
  RETURNS text AS
$func$
SELECT unaccent('unaccent', $1)
$func$  LANGUAGE sql IMMUTABLE SET search_path = public, pg_temp;

-- 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í reset/aktivace nebo žádost o ně
	password_hash	varchar(255)	DEFAULT NULL,			-- heš hesla (je-li nastaveno)
	note		text		NOT NULL DEFAULT '',		-- poznámka viditelná pro orgy
	email_notify	boolean		NOT NULL DEFAULT true		-- přeje si dostávat mailové notifikace
);

-- Uživatel s user_id=0 vždy existuje a je to systémový uživatel s právy admina.
-- Nemá nastavené heslo, takže se na něj nejde přihlásit.
-- Nelze mu ani heslo resetovat (to nejde adminům obecně), ani poslat pozvánku (má last_login_at).
INSERT INTO users(user_id, email, first_name, last_name, is_admin, email_notify, last_login_at, note)
	VALUES(0, 'system', 'Systém', 'OSMO', true, false, NOW(), 'Systémový uživatel');

-- 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
	hidden		boolean	NOT NULL DEFAULT false			-- schovat pro účastníky
);

CREATE INDEX places_parent_index ON places (parent);
-- XXX: Potřebujeme operator class text_pattern_ops, aby index fungoval na prefixové LIKE
CREATE INDEX places_noacc_index ON places ((lower(f_unaccent(name))) text_pattern_ops);

-- 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
	'graded',			-- opraveno, soutěžící mohou protestovat
	'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 round_type AS ENUM (
	'domaci',
	'skolni',
	'okresni',
	'krajske',
	'ustredni',
	'other'		-- ad-hoc kola jako výběrko, neumožnuje export na web MO
);

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 TYPE enroll_mode AS ENUM (	-- režim přihlašování účastníků
	'manual',			-- přihlašuje organizátor
	'register',			-- účastník se registruje
	'confirm'			-- účastník se registruje, ale org ho musí potvrdit
);

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,			-- pořadí kola v kategorii (od 1)
	part		int		NOT NULL DEFAULT 0,		-- část kola (nenulová u dělených kol)
	level		int		NOT NULL,			-- úroveň hierarchie míst
	code		varchar(255)	NOT NULL,			-- kód kola ("1", "S" apod.)
	name		varchar(255)	NOT NULL,			-- zobrazované jméno ("Krajské kolo" apod.)
	round_type	round_type	NOT NULL DEFAULT 'other',	-- typ kola ("domácí", "školní" 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
	enroll_mode	enroll_mode	NOT NULL DEFAULT 'manual',	-- režim přihlašování (pro vyšší kola vždy 'manual')
	enroll_advert	varchar(255)	NOT NULL DEFAULT '',		-- popis v přihlašovacím formuláři
	export_score_to_mo_web	boolean	NOT NULL DEFAULT false,		-- automaticky exportovat výsledkovou listinu na web MO
	UNIQUE (year, category, seq, part),
	UNIQUE (year, category, code, 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
	scoretable_id		int		DEFAULT NULL,			-- odkaz na snapshot představující oficiální výsledkovou listinu soutěže
	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"
	registered_on	timestamp with time zone	DEFAULT NULL,	-- kdy se účastník přihlásil (NULL, pokud ho přihlásil organizátor)
	PRIMARY KEY (user_id, year)
);

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

CREATE TYPE part_state AS ENUM (
	'registered',		-- sám se přihlásil, čeká na potvrzení organizátorem
	-- 'invited',		-- pozván (už nepoužíváme)
	'active',		-- soutěží (přihlášku zadal/potvrdil organizátor)
	'refused',		-- organizátor odmítl přihlášku
	-- 'present',		-- soutěžil (už nepoužíváme)
	'absent',		-- 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,
	score_suborder	int		DEFAULT NULL,
	PRIMARY KEY (user_id, contest_id)
);

CREATE INDEX participations_contest_id_index ON participations (contest_id, place_id);

-- Úlohy

CREATE TYPE task_type AS ENUM (
	'regular',		-- obyčejná úloha
	'cms'			-- praktická úloha kategorie P odevzdávaná přes CMS
);

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ů
	type		task_type	NOT NULL DEFAULT 'regular',
	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
	fixed_at	timestamp with time zone	DEFAULT NULL
	-- 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, přepočítá bytes a nastaví fixed_at
	--    - pokud oprava selže, nastaví pouze fixed_at
);

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
	'pozorovatel'		-- pozorovatel (má read-only práva ke všemu)
);

-- 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: category='Z 'platí pro všechny kategorie začínající na 'Z'
--       category='S' platí pro kategorie 'A', 'B', 'C'
--       (ošetřeno v mo.db.UserRight)
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)		-- momentálně nepoužíváme, změny rolí logujeme pod 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',
	'fix_submits',
	'send_grading_info',
	'snapshot_score',
	'export_score_to_mo_web',
	'revert_export_score_to_mo_web'
);

CREATE TYPE job_state AS ENUM (
	'preparing',
	'ready',
	'running',
	'done',				-- Hotovo, out_json a out_file jsou platné
	'failed',			-- Chyba při zpracování
	'internal_error'		-- 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
);

-- Požadavky na registraci a změny vlastností účtu

CREATE TYPE reg_req_type AS ENUM (
	'register',
	'change_email',
	'reset_password'
);

CREATE TABLE reg_requests (
	reg_id		serial		PRIMARY KEY,
	type		reg_req_type	NOT NULL,
	created_at	timestamp with time zone NOT NULL,
	expires_at	timestamp with time zone NOT NULL,
	used_at		timestamp with time zone DEFAULT NULL,
	email		varchar(255)	DEFAULT NULL,			-- adresa, kterou potvrzujeme
	captcha_token	varchar(255)	DEFAULT NULL,			-- token pro fázi 1 registrace
	email_token	varchar(255)	UNIQUE NOT NULL,		-- token pro fázi 2 registrace
	user_id		int		DEFAULT NULL REFERENCES users(user_id) ON DELETE CASCADE,
	client		varchar(255)	NOT NULL			-- kdo si registraci vyžádal
);

-- Statistiky

-- Pro každý region spočítáme všechna podřízená místa
CREATE VIEW region_descendants AS
	WITH RECURSIVE descendant_regions(place_id, descendant) AS (
		SELECT place_id, place_id FROM places
		UNION SELECT r.place_id, p.place_id
		      FROM descendant_regions r, places p
		      WHERE p.parent = r.descendant
	) SELECT place_id AS region, descendant FROM descendant_regions;

-- Pro každou trojici (kolo, region, stav soutěže) spočítáme soutěže.
CREATE VIEW region_contest_stats AS
	SELECT c.round_id, rd.region, c.state, count(*) AS count
	FROM contests c
	JOIN region_descendants rd ON rd.descendant = c.place_id
	WHERE rd.region <> c.place_id
	GROUP BY c.round_id, rd.region, c.state;

-- Pro každou trojici (kolo, region, stav účasti) spočítáme účastníky.
-- Pokud se to ukáže být příliš pomalé, přejdeme na materializovaná views.
CREATE VIEW region_participant_stats AS
	SELECT c.round_id, rd.region, p.state, count(*) AS count
	FROM participations p
	JOIN contests c USING(contest_id)
	JOIN region_descendants rd ON rd.descendant = c.place_id
	GROUP BY c.round_id, rd.region, p.state;

-- Pro každou trojici (kolo, region, úloha) spočítáme řešení.
CREATE VIEW region_task_stats AS
	SELECT r.round_id, rd.region, t.task_id, count(*) AS count
	FROM rounds r
	JOIN contests c USING(round_id)
	JOIN participations p USING(contest_id)
	JOIN tasks t USING(round_id)
	JOIN solutions s USING(user_id, task_id)
	JOIN region_descendants rd ON rd.descendant = c.place_id
	GROUP BY r.round_id, rd.region, t.task_id;

-- Stav zpracování skenů (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)
);

-- Uložené výsledkové listiny (pro zveřejnění)

CREATE TABLE score_tables (
	scoretable_id	serial		PRIMARY KEY,
	contest_id	int		NOT NULL REFERENCES contests(contest_id) ON DELETE CASCADE,	-- soutěž ke které patří
	created_at	timestamp with time zone	NOT NULL DEFAULT CURRENT_TIMESTAMP,		-- datum vytvoření snapshotu
	created_by	int		NOT NULL REFERENCES users(user_id),				-- autor snapshotu
	score_mode	score_mode	NOT NULL,							-- mód výsledkovky
	note		text		NOT NULL,							-- poznámka viditelná pro orgy
	tasks		jsonb		NOT NULL,							-- seznam názvů a kódů úloh
	rows		jsonb		NOT NULL,							-- seznam řádků výsledkové listiny
	pdf_file	varchar(255)	DEFAULT NULL,							-- cesta k PDF souboru s výsledkovkou relativně vůči mo.util.data_dir('score')
	score_metadata	jsonb		NOT NULL DEFAULT '{}'							-- viz dokumentace doc/score-snapshot.md
);

ALTER TABLE contests ADD CONSTRAINT "contests_scoretable_id" FOREIGN KEY (scoretable_id) REFERENCES score_tables(scoretable_id);

-- Odeslané mailové notifikace

CREATE TABLE sent_email (
	user_id		int		NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
	key		varchar(100)	NOT NULL,
	sent_at		timestamp with time zone	NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (user_id, key)
);