-- System

CREATE TABLE user_role (
	id SERIAL PRIMARY KEY,
	name TEXT
);

CREATE TABLE "user" (
  id SERIAL PRIMARY KEY,
  username TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL,
  role_id INTEGER NOT NULL,
  FOREIGN KEY (role_id) REFERENCES user_role (id)
);

CREATE TABLE post (
  id SERIAL PRIMARY KEY,
  author_id INTEGER NOT NULL,
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES "user" (id)
);

-- Students

create table desertion_risk_class (
  id serial primary key,
  "name" text
);

create table pucp_unit (
  id serial primary key,
  "name" text
);

create table administrative_division (
  id serial primary key,
  "name" text,
  country text,
  subdivision1 text,
  subdivision2 text,
  subdivision3 text,
  subdivision10 text,
  ubigeo text,
  "population" integer,
  poverty real
);

CREATE TABLE student (
	id SERIAL PRIMARY KEY,
	pucp_code INTEGER,
	first_name TEXT,
	last_name TEXT,
	email TEXT,
	-- district TEXT,
  	administrative_division_id integer,
	pucp_unit_id integer,
	tuition_bracket TEXT,
	current_semester integer,
	current_attendance REAL,
	current_craest REAL,
	current_ppne3 REAL,
	current_merit REAL,
	est_study_length INTEGER,
	est_desertion_risk_class_id integer,
	FOREIGN KEY (administrative_division_id) REFERENCES administrative_division (id),
	FOREIGN KEY (pucp_unit_id) REFERENCES pucp_unit (id),
	FOREIGN KEY (est_desertion_risk_class_id) REFERENCES desertion_risk_class (id)
);

-- alter table student 
-- add constraint fk_administrative_division
-- FOREIGN KEY (administrative_division_id) 
-- REFERENCES administrative_division (id)
-- ;

-- Sample data  (later move to tests)

INSERT INTO user_role ("id", "name") VALUES
	 (1, 'admin'),
	 (2, 'user')
;

INSERT INTO "user" ("username","password","role_id") VALUES
	 ('mitsuo','scrypt:32768:8:1$DNNf1Ah1SwUcFHIU$c0e40293c9ecff498b74e7727a8d9a348371262b85cc880e8c653438b1afa84849adb7230f4fcd995c82de543a7836a5cda2a411a45c17dc653e7036fe3de482',1)
;

INSERT INTO desertion_risk_class ("name") VALUES
	 ('Alto'),
	 ('Bajo'),
	 ('Medio');

INSERT INTO pucp_unit ("name") VALUES
	 ('Facultad de Ciencias e Ingenieria'),
	 ('Estudios Generales Ciencias');

-- sample 2019 data
INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
	 ('Peru','','','','','',29009326,28.9,'country'),
	 ('Peru','Lima','','','','150000',8981440,15.4,'region'),
	 ('Peru','Lima','Lima','','','150100',8095747,17.5,'province'),
	 ('Peru','Lima','Lima','LIMA                     ','Lima Centro','150101',302056,12.3,'district'),
	 ('Peru','Lima','Lima','ANCÓN                    ','Lima Norte','150102',36401,19.6,'district'),
	 ('Peru','Lima','Lima','ATE                      ','Lima Este','150103',521692,18.9,'district'),
	 ('Peru','Lima','Lima','BARRANCO                 ','Lima Centro','150104',33996,5.3,'district'),
	 ('Peru','Lima','Lima','BREÑA                    ','Lima Centro','150105',82987,8.5,'district'),
	 ('Peru','Lima','Lima','CARABAYLLO               ','Lima Norte','150106',237269,26.3,'district'),
	 ('Peru','Lima','Lima','CHACLACAYO               ','Lima Este','150107',42884,10.1,'district');
INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
	 ('Peru','Lima','Lima','CHORRILLOS               ','Lima Centro','150108',303913,17.2,'district'),
	 ('Peru','Lima','Lima','CIENEGUILLA              ','Lima Este','150109',31160,24.8,'district'),
	 ('Peru','Lima','Lima','COMAS                    ','Lima Norte','150110',509976,22.3,'district'),
	 ('Peru','Lima','Lima','EL AGUSTINO              ','Lima Este','150111',188138,22.1,'district'),
	 ('Peru','Lima','Lima','INDEPENDENCIA            ','Lima Norte','150112',215941,21.3,'district'),
	 ('Peru','Lima','Lima','JESÚS MARÍA              ','Lima Centro','150113',71139,1.7,'district'),
	 ('Peru','Lima','Lima','LA MOLINA                ','Lima Este','150114',144491,0.7,'district'),
	 ('Peru','Lima','Lima','LA VICTORIA              ','Lima Centro','150115',193592,14.9,'district'),
	 ('Peru','Lima','Lima','LINCE                    ','Lima Centro','150116',55733,4.0,'district'),
	 ('Peru','Lima','Lima','LOS OLIVOS               ','Lima Norte','150117',339028,13.4,'district');
INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
	 ('Peru','Lima','Lima','LURIGANCHO               ','Lima Este','150118',184593,24.4,'district'),
	 ('Peru','Lima','Lima','LURÍN                    ','Lima Sur','150119',69282,30.2,'district'),
	 ('Peru','Lima','Lima','MAGDALENA DEL MAR        ','Lima Centro','150120',54116,2.3,'district'),
	 ('Peru','Lima','Lima','PUEBLO LIBRE','Lima Centro','150121',77892,2.0,'district'),
	 ('Peru','Lima','Lima','MIRAFLORES               ','Lima Centro','150122',86920,0.8,'district'),
	 ('Peru','Lima','Lima','PACHACAMAC               ','Lima Este','150123',81145,32.9,'district'),
	 ('Peru','Lima','Lima','PUCUSANA                 ','Lima Sur','150124',12148,26.7,'district'),
	 ('Peru','Lima','Lima','PUENTE PIEDRA            ','Lima Norte','150125',263594,35.6,'district'),
	 ('Peru','Lima','Lima','PUNTA HERMOSA            ','Lima Sur','150126',6309,7.4,'district'),
	 ('Peru','Lima','Lima','PUNTA NEGRA              ','Lima Sur','150127',5951,9.5,'district');
INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
	 ('Peru','Lima','Lima','RIMAC                    ','Lima Centro','150128',178869,16.5,'district'),
	 ('Peru','Lima','Lima','SAN BARTOLO              ','Lima Sur','150129',6368,9.7,'district'),
	 ('Peru','Lima','Lima','SAN BORJA                ','Lima Centro','150130',111208,0.8,'district'),
	 ('Peru','Lima','Lima','SAN ISIDRO               ','Lima Centro','150131',58920,0.6,'district'),
	 ('Peru','Lima','Lima','SAN JUAN DE LURIGANCHO   ','Lima Este','150132',962554,27.0,'district'),
	 ('Peru','Lima','Lima','SAN JUAN DE MIRAFLORES   ','Lima Sur','150133',382531,19.7,'district'),
	 ('Peru','Lima','Lima','SAN LUIS                 ','Lima Este','150134',57080,7.1,'district'),
	 ('Peru','Lima','Lima','SAN MARTÍN DE PORRES     ','Lima Norte','150135',620193,10.9,'district'),
	 ('Peru','Lima','Lima','SAN MIGUEL               ','Lima Centro','150136',134666,2.3,'district'),
	 ('Peru','Lima','Lima','SANTA ANITA              ','Lima Este','150137',199282,12.0,'district');
INSERT INTO public.administrative_division (country,subdivision1,subdivision2,subdivision3,subdivision10,ubigeo,population,poverty,name) VALUES
	 ('Peru','Lima','Lima','SANTA MARÍA DEL MAR      ','Lima Sur','150138',924,4.0,'district'),
	 ('Peru','Lima','Lima','SANTA ROSA               ','Lima Norte','150139',12641,12.0,'district'),
	 ('Peru','Lima','Lima','SANTIAGO DE SURCO        ','Lima Centro','150140',309889,3.3,'district'),
	 ('Peru','Lima','Lima','SURQUILLO                ','Lima Centro','150141',93271,5.2,'district'),
	 ('Peru','Lima','Lima','VILLA EL SALVADOR        ','Lima Sur','150142',410313,25.9,'district'),
	 ('Peru','Lima','Lima','VILLA MARÍA DEL TRIUNFO  ','Lima Sur','150143',404692,21.1,'district');


INSERT INTO student (pucp_code,first_name,last_name,email,administrative_division_id,pucp_unit_id,tuition_bracket,current_semester,current_attendance,current_craest,current_ppne3,current_merit,est_study_length,est_desertion_risk_class_id) VALUES
	 (20171234,'Alberto','Alvarez','aalvarez@pucp.edu.pe',40,1,'G5',5,0.8,58.35,55.23,0.1532,10,1),
	 (20171235,'Benito','Bueno','bbueno@pucp.edu.pe',41,2,'G5',5,0.8,58.35,55.23,0.1532,10,1),
	 (20171236,'Carlos','Canto','ccanto@pucp.edu.pe',42,1,'G5',5,0.8,58.35,55.23,0.1532,10,2);