-- Crear base de datos invima --
CREATE DATABASE invima;
-- DROP es para eliminar TABLAS Y BASES DE DATOS si la cago --
DROP TABLE productos;
DROP TABLE registros_sanitarios;
-- Crear la tabla productos lo mismo con las de abajo--
-- OJO QUE TENEMOS RESTRICCIONES ESTO PARA QUE NO LA EMBARREMOS --
CREATE TABLE productos (
	id_producto				text NOT NULL,
	nombre_comercial 		text NOT NULL,
	categoria_general 		text NOT NULL
);

CREATE TABLE registros_sanitarios (
	id_registro			text NOT NULL,
	id_producto			text NOT NULL,
	numero_registro 	numeric CHECK (numero_registro>0) NOT NULL,
	estado				text CONSTRAINT estado_valido CHECK(estado IN ('vencido', 'no vencido')) DEFAULT 'no vencido',
	fecha_expedicion	DATE NOT NULL,
	fecha_vencimiento	DATE NOT NULL,
	modalidad			text NOT NULL,
	titular_registro	text NOT NULL,
	fabricante			text NOT NULL,
	observaciones		text NOT NULL
);

-- INSERTAR INFORMACION ESTO LO HAREMOS MAS ADELANTE CON UN SCRIPT DE PYTHON QUE HAGA ESTO YA TENIENDO EL COMANDO BASE--
INSERT INTO productos (id_producto,	nombre_comercial,	categoria_general) VALUES 
	('10e372', 'NADOREX' ,	'Medicamento' ),
	('12e518', 'Noxpirin Plus' , 'Medicamento' );

-- Actualizar datos
UPDATE productos SET nombre_comercial = 'Acetaminofén' WHERE id_producto = '10e372';


-- BORRAR PRODUCTOS
DELETE FROM productos WHERE nombre_comercial = 'Acetaminofén';
DELETE FROM productos WHERE nombre_comercial = 'Noxpirin Plus';

--Consultar esto para que luego con la API se consulte breve
SELECT * FROM productos;
SELECT * FROM productos ORDER BY id_producto;
SELECT * FROM productos WHERE nombre_comercial = 'Acetaminofén' ORDER BY id_producto;

-- ALTER osea cambios
-- QUITAR NO NULO
ALTER TABLE productos ALTER COLUMN id_producto DROP NOT NULL;
-- AGREGAR NO NULO
ALTER TABLE productos ALTER COLUMN id_producto SET NOT NULL;
-- CAMBIAR NOMBRE DE LA COLUMNA
ALTER TABLE productos RENAME COLUMN id_producto TO identificador;
-- RENOMBRAR UNA TABLA
ALTER TABLE productos_invima RENAME TO productos;
-- AÑADIR PRIMARY KEY 
ALTER TABLE productos ADD PRIMARY KEY (id_producto);
-- AÑADIR UN FOREIGN KEY
ALTER TABLE registros_sanitarios ADD FOREIGN KEY (id_producto) REFERENCES productos(id_producto);
-- AÑADIR UN CHECK A CATEGORIA PRINCIPAL DE PRODUCTOS PARA EL CHECK DE MEDICAMENTOS Y ASI
ALTER TABLE productos ADD CONSTRAINT categoria_general_valido CHECK (categoria_general IN ('Medicamento','Dispositivo','Alimento','Cosmetico'));
ALTER TABLE registros_sanitarios ADD PRIMARY KEY (id_registro);

CREATE TABLE alertas_sanitarias	 (
	id_alerta		text NOT NULL PRIMARY KEY,	
	id_producto		text NOT NULL REFERENCES productos(id_producto),
	fecha_alerta	DATE NOT NULL DEFAULT CURRENT_DATE,
	tipo			text CONSTRAINT tipo_valido CHECK (tipo IN ('Medicamentos','Biológicos', 'Alimentos' ,'Dispositivos', 'Cosméticos')) DEFAULT 'No específico', 
	descripcion		text NOT NULL,
	estado_alerta	text CONSTRAINT estado_alerta_valido CHECK (estado_alerta IN ('Abierto', 'En curso', 'Cerrado')) DEFAULT 'cerrado',
	url_fuente		text NOT NULL
);


CREATE TABLE medicamentos (
	id_producto				text REFERENCES productos(id_producto),
	categoria_general 		text CHECK (categoria_general IN ('Medicamento')) DEFAULT 'Medicamento',
	estado					text REFERENCES evaluaciones(estado),
	subcategoria			text NOT NULL PRIMARY KEY,
	concentracion_base      text NOT NULL,
	forma_farmaceutica      text NOT NULL,
	codigo_atc              text NOT NULL,
	tipo_medicamento        text NOT NULL CHECK (tipo_medicamento IN ('quimico','biologico','biotecnologico','fitoterapeutico','homeopatico','radiofarmaco'))
);

CREATE TABLE dispositivos (
	id_producto				text REFERENCES productos(id_producto),
	categoria_general 		text CHECK (categoria_general IN ('Dispositivo')) DEFAULT 'Dispositivo',
	estado					text REFERENCES evaluaciones(estado),
	subcategoria			text NOT NULL PRIMARY KEY,
  	clase_riesgo            text NOT NULL CHECK (clase_riesgo IN ('I','IIa','IIb','III')),
  	uso_previsto_detallado  text NOT NULL,
  	energia_fuente          text NOT NULL,       
  	software_incluido       boolean NOT NULL,
  	version_software        text NOT NULL,
  	vida_util_meses         integer NOT NULL
);

CREATE TABLE actores (
	id_actor		numeric PRIMARY KEY CHECK (id_actor>0) NOT NULL,
	tipo			text NOT NULL,
	razon_social	text NOT NULL,
	nit				text NOT NULL,
	pais			text NOT NULL,
	contacto		text NOT NULL
);

CREATE TABLE evaluaciones (
	id_producto 	text REFERENCES productos(id_producto),
	id_actor 		numeric REFERENCES actores(id_actor),
	id_registro 	text REFERENCES registros_sanitarios(id_registro),
	id_alerta 		text REFERENCES alertas_sanitarias(id_alerta),
	estado 			text PRIMARY KEY NOT NULL CHECK (estado IN ('buenas condiciones', 'en uso', 'no conforme', 'vencido', 'malas condiciones', 'aceptable')) DEFAULT 'no conforme',
	fecha_revision 	DATE NOT NULL,
	Observaciones 	text
);

CREATE TABLE 

DROP TABLE evaluaciones;
DROP TABLE dispositivos;
DROP TABLE medicamentos;

--- ESTE COMANDO PARA HACER CONSULTAS MEJORES 

SELECT * FROM productos WHERE nombre_comercial ILIKE '%marcapasos%'

-- SI INCLUYE EL NOMBRE MARCAPASOS CON MAYUSCULA O MINUSCULA ENCUENTRA