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
	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)
);

-- 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)	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
);

-- 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 TABLE rounds (
	round_id	serial		PRIMARY KEY,
	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.
	level		int		NOT NULL,			-- úroveň hierarchie míst
	name		varchar(255)	NOT NULL,			-- zobrazované jméno ("Krajské kolo" apod.)
	-- FIXME: termíny
	-- FIXME: stav odevzdávání/opravování/...
	UNIQUE (year, category, seq)
);

-- Soutěže (instance kola v konkrétním místě)
CREATE TABLE contests (
	contest_id	serial		PRIMARY KEY,
	round_id	int		NOT NULL REFERENCES rounds(round_id),
	place_id	int		NOT NULL REFERENCES places(place_id),
	UNIQUE (round_id, place_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" (FIXME)
	PRIMARY KEY (user_id, year)
);

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

CREATE TYPE part_state AS ENUM (
	'registered',		-- přihlášen, ale jestě nedostal pozvánku
	'invited',		-- pozván
	'refused',		-- odmítl účast
	'present',		-- soutěžil
	'absent'		-- bez omluvy nedorazil
);

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)
);

-- Ú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,
	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)	NOT NULL			-- relativní cesta k souboru
);

-- Odevzdané řešení
CREATE TABLE solutions (
	task_id		int		NOT NULL REFERENCES tasks(task_id),
	user_id		int		NOT NULL REFERENCES users(user_id),
	last_submit	int		DEFAULT NULL REFERENCES papers(paper_id),	-- nejnovější odevzdání
	last_feedback	int		DEFAULT NULL REFERENCES papers(paper_id),	-- nejnovější komentáře opravovatelů
	points		int		DEFAULT NULL,
	PRIMARY KEY (task_id, 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		int		NOT 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
);

-- 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
	'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
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)
);

-- FIXME: Obecná políčka na poznámky
-- FIXME: Indexy