/* postgres global */
-- CREATE DATABASE loputood WITH ENCODING 'UTF8' LOCALE 'en_US.utf-8' TEMPLATE template0;
-- ALTER DATABASE loputood SET TIMEZONE TO 'UTC';
-- CREATE USER loputood_server WITH PASSWORD 'saladus';

/***************/
DROP TABLE IF EXISTS public.user;
CREATE TABLE IF NOT EXISTS public.user (
	id serial PRIMARY KEY,
	key VARCHAR (50) NOT NULL,
	username VARCHAR (50) UNIQUE NOT NULL,
	password VARCHAR (255) NOT NULL,
	email VARCHAR (255) NOT NULL,
	created_at TIMESTAMP NOT NULL default now(),
	updated_at TIMESTAMP,
	last_login TIMESTAMP 
);

DROP TABLE IF EXISTS public.unit;
CREATE TABLE IF NOT EXISTS public.unit (
	id serial PRIMARY KEY,
	pid varchar (50),
	date varchar (50),
	title varchar (255) NOT NULL,
	pages int,
	notes text,
	status int NOT NULL default 1,
	created_at TIMESTAMP NOT NULL default now(),
	created_by int NOT NULL default 1,
	updated_at TIMESTAMP,
	updated_by int,
	deleted_at TIMESTAMP,
	deleted_by int
);

DROP TABLE IF EXISTS public.person;
CREATE TABLE IF NOT EXISTS public.person (
	id serial PRIMARY KEY,
	first_name varchar (255),
	last_name varchar (255) NOT NULL,
	birth date,
	death date,
	notes text,
	status int NOT NULL default 1,
	created_at TIMESTAMP NOT NULL default now(),
	created_by int NOT NULL default 1,
	updated_at TIMESTAMP,
	updated_by int,
	deleted_at TIMESTAMP,
	deleted_by int
);

DROP TABLE IF EXISTS public.unit_person;
CREATE TABLE IF NOT EXISTS public.unit_person (
	id serial PRIMARY KEY,
	relation varchar (50) NOT NULL,
	unit_id int NOT NULL,
	person_id int NOT NULL,
	created_at TIMESTAMP NOT NULL default now(),
	created_by int NOT NULL default 1
);
/***************/

/* inside database */
-- GRANT pg_read_all_data TO loputood_server;
-- GRANT pg_write_all_data TO loputood_server;
-- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO loputood_server;
-- GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO loputood_server;
-- GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO loputood_server;


INSERT INTO public.unit (pid, date, title) VALUES (237, '1963', 'Loksa Keskkooli ajalugu 1867-1962');
INSERT INTO public.person (first_name, last_name) VALUES ('Vaike', 'Kivil');
INSERT INTO public.person (first_name, last_name) VALUES ('Sulev', 'Vahtre');
INSERT INTO public.unit_person (relation, unit_id, person_id) VALUES ('autor', 1, 1);
INSERT INTO public.unit_person (relation, unit_id, person_id) VALUES ('juhendaja', 1, 2);