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