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

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