/********************* ROLES **********************/

/********************* UDFS ***********************/

/****************** SEQUENCES ********************/

/******************** DOMAINS *********************/

CREATE DOMAIN DOM_DESCR
 AS varchar(100)
 NOT NULL
 CHECK (CHAR_LENGTH(VALUE) >= 2 AND VALUE = UPPER(VALUE))
 COLLATE NONE;
CREATE DOMAIN DOM_DNI
 AS integer
 DEFAULT 1000000
 NOT NULL
 CHECK (VALUE >= 1000000)
;
CREATE DOMAIN DOM_ID
 AS integer
 DEFAULT 1
 NOT NULL
 CHECK (VALUE >= 1)
;
/******************* PROCEDURES ******************/

/******************** TABLES **********************/

CREATE TABLE TBL_A_CARGO
(
  ID DOM_ID NOT NULL,
  IDOS DOM_ID,
  IMPORTE numeric(10,2) NOT NULL,
  CONSTRAINT PK_TBL_A_CARGO PRIMARY KEY (ID)
);
CREATE TABLE TBL_A_CARGO2
(
  ID DOM_ID NOT NULL,
  CODIGO DOM_ID NOT NULL,
  IDOS DOM_ID,
  IMPORTE numeric(10,2) NOT NULL,
  CONSTRAINT PK_TBL_A_CARGO2 PRIMARY KEY (ID,CODIGO)
);
CREATE TABLE TBL_CONSULTA
(
  ID DOM_ID NOT NULL,
  FECHA_HORA timestamp NOT NULL,
  DIAGNOSTICO varchar(50) NOT NULL,
  INDICACIONES varchar(50) NOT NULL,
  IMPORTE numeric(10,2) NOT NULL,
  DNI DOM_DNI,
  MATRICULA DOM_ID,
  CONSTRAINT PK_TBL_CONSULTA PRIMARY KEY (ID)
);
CREATE TABLE TBL_MEDICO
(
  MATRICULA DOM_ID NOT NULL,
  APELLIDO DOM_DESCR,
  NOMBRE DOM_DESCR,
  CONSTRAINT PK_TBL_MEDICO PRIMARY KEY (MATRICULA)
);
CREATE TABLE TBL_OBRA_SOCIAL
(
  ID DOM_ID NOT NULL,
  NOMBRE DOM_DESCR,
  CONSTRAINT PK_TBL_OBRA_SOCIAL PRIMARY KEY (ID)
);
CREATE TABLE TBL_PACIENTE
(
  DNI DOM_DNI NOT NULL,
  APELLIDO DOM_DESCR,
  NOMBRE DOM_DESCR,
  CONSTRAINT PK_TBL_PACIENTE PRIMARY KEY (DNI)
);
CREATE TABLE TBL_PRACTICA
(
  CODIGO DOM_ID NOT NULL,
  DESCRIPCION DOM_DESCR,
  PRECIO numeric(10,2) NOT NULL,
  CONSTRAINT PK_TBL_PRACTICA PRIMARY KEY (CODIGO)
);
CREATE TABLE TBL_SOLICITA
(
  ID DOM_ID NOT NULL,
  CODIGO DOM_ID NOT NULL,
  RESULTADO varchar(100),
  FECHA_HORA timestamp,
  PRECIO numeric(10,2),
  CONSTRAINT PK_TBL_SOLICITA PRIMARY KEY (ID,CODIGO)
);
/********************* VIEWS **********************/

CREATE VIEW V_CONSULTA (ID, IMPORTE, CON_OS)
AS 
/* write select statement here */
SELECT C.ID,C.IMPORTE,SUM(COALESCE(AC.IMPORTE,0))
FROM TBL_CONSULTA C LEFT JOIN TBL_A_CARGO AC ON C.ID = AC.ID
GROUP BY C.ID,C.IMPORTE ;
CREATE VIEW V_CONSULTA_OS (ID, IMPORTE, CON_OS, PRA_OS)
AS    
/* write select statement here */
SELECT C.ID,C.IMPORTE,C.CON_OS,SUM(COALESCE(AC2.IMPORTE,0))
FROM V_CONSULTA C 
      LEFT JOIN TBL_SOLICITA S ON C.ID = S.ID 
      LEFT JOIN TBL_A_CARGO2 AC2 ON S.ID = AC2.ID AND S.CODIGO = AC2.CODIGO
GROUP BY C.ID,C.IMPORTE,C.CON_OS
;
CREATE VIEW V_CONSULTA_TOTAL (ID, USR_IMPORTE, OS_IMPORTE)
AS 
/* ESTA VISTA DEVUELVE EL IMPORTE QUE PAGO EL USUARIO Y 
   EL IMPORTE TOTAL PAGADO POR LA(S) OBRAS SOCIALES QUE
   UTILIZO EL USUARIO EN PAGAR LA CONSULTA O LAS PRACTICAS  */
SELECT C.ID,C.IMPORTE,C.CON_OS+C.PRA_OS
FROM V_CONSULTA_OS C;
CREATE VIEW V_CONSULTA_TOTAL_F (ID, FECHA, USR_IMPORTE, OS_IMPORTE)
AS 
/* APARTE DE LOS DATOS TOTALES DE LAS CONSULTAS, AGREGA LA FECHA
   DE CADA CONSULTA */
SELECT C.ID,CAST(CC.FECHA_HORA AS DATE),C.USR_IMPORTE,C.OS_IMPORTE
FROM V_CONSULTA_TOTAL C INNER JOIN TBL_CONSULTA CC ON C.ID = CC.ID
;
CREATE VIEW V_CONSULTA_TOTAL_FAM (ANIO, MES, TUSR_IMPORTE, TOS_IMPORTE)
AS 
/* DEVUELVE LOS TOTALES PAGADOS
   POR EL USUARIO POR LAS CONSULTAS 
   Y LO QUE PAGO CADA OBRA SOCIAL
   POR ANIO - MES */
SELECT EXTRACT(YEAR FROM C.FECHA),EXTRACT(MONTH FROM C.FECHA),SUM(C.USR_IMPORTE),SUM(C.OS_IMPORTE)
FROM V_CONSULTA_TOTAL_F C
GROUP BY EXTRACT(YEAR FROM C.FECHA),EXTRACT(MONTH FROM C.FECHA);
/******************* EXCEPTIONS *******************/

/******************** TRIGGERS ********************/

/******************** DB TRIGGERS ********************/


ALTER TABLE TBL_A_CARGO ADD CONSTRAINT FK_TBL_A_CARGO_CONSULTA
  FOREIGN KEY (ID) REFERENCES TBL_CONSULTA (ID);
ALTER TABLE TBL_A_CARGO ADD CONSTRAINT FK_TBL_A_CARGO_OBRA_SOCIAL
  FOREIGN KEY (IDOS) REFERENCES TBL_OBRA_SOCIAL (ID);
ALTER TABLE TBL_A_CARGO2 ADD CONSTRAINT FK_TBL_A_CARGO2_OBRA_SOCIAL
  FOREIGN KEY (IDOS) REFERENCES TBL_OBRA_SOCIAL (ID);
ALTER TABLE TBL_A_CARGO2 ADD CONSTRAINT FK_TBL_A_CARGO2_SOLICITA
  FOREIGN KEY (ID,CODIGO) REFERENCES TBL_SOLICITA (ID,CODIGO);
ALTER TABLE TBL_A_CARGO2 ADD CONSTRAINT CK_TBL_A_CARGO2_IMPORTE
  CHECK ( IMPORTE > 0 );
ALTER TABLE TBL_CONSULTA ADD CONSTRAINT FK_TBL_CONSULTA_MEDICO
  FOREIGN KEY (MATRICULA) REFERENCES TBL_MEDICO (MATRICULA);
ALTER TABLE TBL_CONSULTA ADD CONSTRAINT FK_TBL_CONSULTA_PACIENTE
  FOREIGN KEY (DNI) REFERENCES TBL_PACIENTE (DNI);
CREATE INDEX IDX_MEDICO ON TBL_MEDICO (APELLIDO);
CREATE INDEX IDX_PACIENTE ON TBL_PACIENTE (APELLIDO);
CREATE INDEX IDX_PRACTICA ON TBL_PRACTICA (DESCRIPCION);
ALTER TABLE TBL_SOLICITA ADD CONSTRAINT FK_TBL_SOLICITA_CONSULTA
  FOREIGN KEY (ID) REFERENCES TBL_CONSULTA (ID);
ALTER TABLE TBL_SOLICITA ADD CONSTRAINT FK_TBL_SOLICITA_PRACTICA
  FOREIGN KEY (CODIGO) REFERENCES TBL_PRACTICA (CODIGO);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON TBL_A_CARGO TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON TBL_A_CARGO2 TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON TBL_CONSULTA TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON TBL_MEDICO TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON TBL_OBRA_SOCIAL TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON TBL_PACIENTE TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON TBL_PRACTICA TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON TBL_SOLICITA TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON V_CONSULTA TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON V_CONSULTA_OS TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON V_CONSULTA_TOTAL TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON V_CONSULTA_TOTAL_F TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON V_CONSULTA_TOTAL_FAM TO  SYSDBA WITH GRANT OPTION;