From 7550b896ce360a998da788beae4b4c6f734f473e Mon Sep 17 00:00:00 2001
From: Mitsuo Tokumori <rtokumori@pucp.edu.pe>
Date: Sun, 29 Oct 2023 18:47:23 -0500
Subject: Add filtering and pagination to bp.estudiantes

---
 ustayml/schema.sql | 23 +++++++++++++++++------
 1 file changed, 17 insertions(+), 6 deletions(-)

(limited to 'ustayml/schema.sql')

diff --git a/ustayml/schema.sql b/ustayml/schema.sql
index 8062be8..e7e3e68 100644
--- a/ustayml/schema.sql
+++ b/ustayml/schema.sql
@@ -24,6 +24,11 @@ CREATE TABLE post (
 
 -- Students
 
+create table desertion_risk_class (
+  id serial primary key,
+  "name" text
+);
+
 create table pucp_unit (
   id serial primary key,
   "name" text
@@ -58,9 +63,10 @@ CREATE TABLE student (
 	current_ppne3 REAL,
 	current_merit REAL,
 	est_study_length INTEGER,
-	est_dessertion_risk TEXT,
+	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 (pucp_unit_id) REFERENCES pucp_unit (id),
+	FOREIGN KEY (est_desertion_risk_class_id) REFERENCES desertion_risk_class (id)
 );
 
 -- alter table student 
@@ -80,6 +86,11 @@ 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');
@@ -138,7 +149,7 @@ INSERT INTO public.administrative_division (country,subdivision1,subdivision2,su
 	 ('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_dessertion_risk) VALUES
-	 (20171234,'Alberto','Alvarez','aalvarez@pucp.edu.pe',40,1,'G5',5,0.8,58.35,55.23,0.1532,10,'L'),
-	 (20171235,'Benito','Bueno','bbueno@pucp.edu.pe',41,2,'G5',5,0.8,58.35,55.23,0.1532,10,'H'),
-	 (20171236,'Carlos','Canto','ccanto@pucp.edu.pe',42,1,'G5',5,0.8,58.35,55.23,0.1532,10,'L');
\ No newline at end of file
+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);
\ No newline at end of file
-- 
cgit v1.2.3

