create database ConsultorioDental;

use ConsultorioDental;

-- Tablas Principales

create table rol(
	idRol int not null primary key,
	nombre varchar(16)
 )

create table nota_compra(
	idNota int not null primary key,
	nombre varchar(64) not null,
	fecha date not null,
	nit int not null,
	monto int not null,
	idUsuario int not null foreign key references usuario(idUsuario)
)

create table cara_dental
(
	idCarDent int not null primary key,
	nombre varchar(32)
)

create table historial(
	idHist int not null primary key,
	fechaRegistro date not null,
	ultimaConsulta date not null
)

create table especialidad(
	idEsp int not null  primary key,
	descripcion varchar(64) not null
);


create table servicio(
	idServ int not null primary key,
	nombre varchar(32) not null,
	descripcion varchar (500) not null,
	estado bit not null
);

create table materia_prima(
	idMatPrim int not null primary key,
	nombre varchar(32) not null,
	descripcion varchar(128),
	estado bit not null,
	stock int not null,
	cant_usos int not null,
	total_usos int not null
);

create table horario (
	idHor int not null primary key,
	estado bit not null,
	dia varchar(10) not null,
	hora_inicio time not null,
	hora_fin time not null
)


create table agenda(
	idAgenda int not null primary key
)


-- Tablas con llaves foraneas

CREATE TABLE usuario (
 idUsuario INT NOT NULL PRIMARY KEY,
 nombre VARCHAR(20)NOT NULL,
 estado bit NOT NULL,
 contraseña VARCHAR(50) NOT NULL,
 idRol int not null foreign key references rol(idRol)
 		on update cascade
		on delete cascade
 )

create table recepcionista(
	ciRecep int not null primary key,
	nombre varchar(64) not null,
	sexo char not null,
	telefono int not null,
	idUsuario int not null foreign key references usuario(idUsuario)
		on update cascade
		on delete cascade
)

create table paciente(
	ciPaci int not null primary key,
	nombre varchar(64) not null,
	sexo char not null,
	telefono int not null,
	idHist int not null foreign key references historial(idHist)
		on update cascade
		on delete cascade,
	fecha_nacimiento date not null,
	correo varchar(32) not null,
	idUsuario int not null foreign key references usuario(idUsuario)
		on update cascade
		on delete cascade,
)

create table consulta(
	idCon int not null,
	idHist int not null,
	primary key(idCon,idHist),
	foreign key (idHist) references historial(idHist)
		on update cascade
		on delete cascade,
	diagnostico varchar(128) not null,
	fecha_retorno date not null,
	motivo varchar(128) not null,
	tratamiento varchar(128) not null
)

create table odontologo(
	ciOdon int not null primary key,
	nombre varchar(64) not null,
	sexo char not null,
	telefono int not null,
	idAgenda int not null foreign key references agenda(idAgenda)
		on update cascade
		on delete cascade,
	idUsuario int not null foreign key references usuario(idUsuario)
		on update cascade
		on delete cascade
);
create table tiene_especialidad(
	idOdon int not null foreign key references odontologo(ciOdon)
		on update cascade
		on delete cascade,
	idEspe int not null foreign key references especialidad(idEsp)
		on update cascade
		on delete cascade,
	primary key (idOdon,idEspe)
)

create table cita(
	idCita int not null primary key,
	estado bit not null,
	fecha date not null,
	hora time not null,

	idHist int not null,
	idCon int not null,

	foreign key (idCon,idHist) references consulta(idCon,idHist)
		on update cascade
		on delete cascade,

	idRecep int not null foreign key references recepcionista(ciRecep)
		on update cascade
		on delete cascade,

	idAgenda int not null foreign key references agenda(idAgenda)
	on update cascade
		on delete cascade,

		ciPaci int not null foreign key references paciente(ciPaci)
	on update no action
		on delete no action


);

create table ficha_serv(
	idCita int not null foreign key references cita(idCita)
		on update cascade
		on delete cascade,
	idServ int not null foreign key references servicio(idServ)
		on update cascade
		on delete cascade,
	primary key (idCita,idServ)
)







create table receta(
	idRec int not null primary key,
	descripcion varchar(128) not null,
	idCon int not null,
	idHist int not null,
	foreign key (idCon,idHist) references consulta(idCon,idHist)
		on update cascade
		on delete cascade,
)

create table odontograma(
	idOdonto int not null primary key,
	idHist int not null,
	idCon int not null ,
	foreign  key (idCon,idHist) references consulta(idCon,idHist)
		on update cascade
		on delete cascade,
)



create table diente(
	idDiente int not null,
	idOdonto int not null foreign key references odontograma(idOdonto)
		on update cascade
		on delete cascade,
	primary key (idDiente,idOdonto),
	nombre varchar(64) not null,
	estadoActual varchar(32) not null,
)

create table serv_cons(
	idServ int not null foreign key references servicio(idServ)
		on update cascade
		on delete cascade,
	idHist int not null,
	idCon  int not null,
	foreign key (idCon,idHist) references consulta(idCon,idHist)
		on update cascade
		on delete cascade,
	primary key (idServ,idHist,idCon)
)

create table odont_horario(
	idHor int not null foreign key references horario(idHor)
		on update cascade
		on delete cascade,
	ciOdon int not null foreign key references odontologo(ciOdon)
		on update cascade
		on delete cascade,
	primary key (idHor,ciOdon)
)

 create table serv_mat(
	cant_usos int not null,
	idMatPrim int not null foreign key references materia_prima(idMatPrim)
		on update cascade
		on delete cascade,
	idServ int not null foreign key references servicio(idServ)
		on update cascade
		on delete cascade,
	primary key (idMatPrim,idServ)

 )

 CREATE TABLE odont_serv(
ciOdon INT NOT NULL FOREIGN KEY REFERENCES odontologo(ciOdon )
		on update cascade
		on delete cascade,
idServ INT NOT NULL FOREIGN KEY REFERENCES servicio(idServ)
		on update cascade
		on delete cascade,
PRIMARY KEY (ciOdon,idServ)
)

CREATE TABLE nota_vent
(idNotaVent INT NOT NULL PRIMARY KEY,
 idConsult INT NOT NULL,
 idHist INT NOT NULL,
 idUsuario int not null foreign key references usuario(idUsuario)
	on update cascade
	on delete cascade,
 FOREIGN KEY (idConsult,idHist) REFERENCES consulta(idCon,idHist)
		on update cascade
		on delete cascade,
 fecha DATE NOT NULL,
 monto_total INT NOT NULL,
 saldo INT NOT NULL
)

CREATE TABLE cuota(
 idNotaVenta INT NOT NULL FOREIGN KEY REFERENCES nota_vent(idNotaVent)
		on update cascade
		on delete cascade,
 idCuota INT NOT NULL,
 PRIMARY KEY (idNotaVenta,idCuota),
 ciPaci INT NOT NULL FOREIGN KEY REFERENCES paciente(ciPaci)
		on update no action
		on delete no action,
 fecha DATE NOT NULL,
 monto INT NOT NULL

 )


 create table permiso(
	idPer int not null,
	idRol int not null foreign key references rol(idRol)
		on update cascade
		on delete cascade,
	primary key (idPer,idRol),

 )



 CREATE TABLE estudio_complementario
 (
  idEstudio INT NOT NULL PRIMARY KEY,
  nombre VARCHAR(20),
  descripcion VARCHAR(128) NOT NULL,
  idServicio INT NOT NULL FOREIGN KEY REFERENCES servicio(idServ)
  		on update cascade
		on delete cascade,

 )



 CREATE TABLE cp_dental(
 idCarDent INT NOT NULL FOREIGN KEY REFERENCES cara_dental(idCarDent)
 		on update cascade
		on delete cascade,
 idDiente INT NOT NULL ,
 idOdontoG INT NOT NULL,
 FOREIGN KEY (idDiente,idOdontoG) REFERENCES diente(idDiente,idOdonto)
 		on update cascade
		on delete cascade,
 PRIMARY KEY (idCarDent,idDiente,idOdontoG),
 estado_diagnostico VARCHAR(100) NOT NULL,
 estado_tratamiento VARCHAR(100)NOT NULL
 )

 CREATE TABLE nota_mat_prima(
 idNota INT NOT NULL FOREIGN KEY REFERENCES nota_compra(idNota)
 		on update cascade
		on delete cascade,
 idMatPrim INT NOT NULL FOREIGN KEY REFERENCES materia_prima(idMatPrim)
 		on update cascade
		on delete cascade,
 PRIMARY KEY (idNota,idMatPrim),
 Precio INT NOT NULL)



 -- ROL
 insert into rol values(01,'Administrador')
 insert into rol values(02,'Odontologo')
 insert into rol values(03,'Recepcionista')
 insert into rol values(04,'Paciente')

-- USUARIO
 insert into usuario values(1,'silviaventura',1,'silviaventura09',2)
 insert into usuario values(02,'pedrotardio',1,'pedrotardio1234',02)

 insert into usuario values(03,'toborochi98',1,'12345tortas',04)
 insert into usuario values(04,'pablotb',1,'mrpablo45',04)
 insert into usuario values(05,'ochoq_lo',1,'octavia45',04)
 insert into usuario values(06,'hentailover',1,'seibazero13',04)
 insert into usuario values(07,'sedusau',1,'sedusau334455',04)

 insert into usuario values(08,'naomilopez',1,'naomirosita',03)
 insert into usuario values(09,'sebaroca69',1,'lamole33',03)

-- CARA DENTAL
 insert into cara_dental values (01,'Oclusal')
 insert into cara_dental values (02,'Lingual')
 insert into cara_dental values (03,'Vestibular')
 insert into cara_dental values (04,'Mesial')
 insert into cara_dental values (05,'Distal')
 insert into cara_dental values (06,'Todas')

 -- ESPECIALIDAD
insert into especialidad values (111,'Ortodoncia');
insert into especialidad values (222,'Endodoncia');
insert into especialidad values (333,'Implantologia oral');
insert into especialidad values (444,'Periodoncia');

-- AGENDA
insert into agenda values (1);
insert into agenda values (2);

-- HISTORIAL
insert into historial values (01,'2019-05-03','2019-08-02');
insert into historial values (02,'2019-07-26','2019-09-05');
insert into historial values (03,'2019-06-14','2019-08-13');
insert into historial values (04,'2019-08-10','2019-09-02');
insert into historial values (05,'2019-04-19','2019-09-10');


-- PACIENTE
 insert into paciente values(111,'Leonardo Anez Vladimirovna','M',70840755,05,'1998-11-07','toborochi98@outlook.com',03)
 insert into paciente values(222,'Pablo Tardio Ventura','M',74513555,04,'1998-11-07','pablotv@gmail.com',04)
 insert into paciente values(333,'Luis Octavio Antelo','M',69445123,03,'1998-11-07','magicrainbow98@gmail.com',05)
 insert into paciente values(444,'Javier Selaya','M',78912321,02,'1998-11-07','nojomo13@gmail.com',06)
 insert into paciente values(555,'Sebastian Duran','M',64513000,01,'1998-11-07','sedusau97@gmail.com',07)

 -- CONSULTA
 insert into consulta values (01,02,'El paciente presenta leves indicios de caries','2019-09-15','Checkeo','Limpieza dental')
 insert into consulta values (02,01,'Paciente presenta dolores al masticar','2019-09-10','Dolores dentales','Endodoncia')
 insert into consulta values (03,04,'Paciente presenta encias inflamadas','2019-04-12','Inflamacion','Profilaxis')

 -- RECEPCIONISTA
insert into Recepcionista values(21902954,'Naomi López Rocha','F',76855331,08);
insert into Recepcionista values(62904529,'Sebastián Roca Ibañez','M',70401101,09);

-- ODONTOLOGO
insert into odontologo values (3834393,'Silvia Ventura','M',79064047,01,1)
insert into odontologo values (1052248,'Pedro Tardio','M',79064047,02,2)


-- HORARIO
insert into horario values(1,1,'Lunes','16:00:00','19:30:00');
insert into horario values(2,1,'Lunes','19:30:00','22:00:00');
insert into horario values(3,1,'Martes','16:00:00','19:30:00');
insert into horario values(4,1,'Martes','19:30:00','22:00:00');
insert into horario values(5,1,'Miercoles','16:00:00','19:30:00');
insert into horario values(6,1,'Miercoles','19:30:00','22:00:00');
insert into horario values(7,1,'Jueves','16:00:00','19:30:00');
insert into horario values(8,1,'Jueves','19:30:00','22:00:00');
insert into horario values(9,1,'Viernes','16:00:00','19:30:00');
insert into horario values(10,1,'Viernes','19:30:00','22:00:00');

-- ODONT_HORARIO
 insert into odont_horario values (1,3834393)
  insert into odont_horario values (3,3834393)
   insert into odont_horario values (5,3834393)
    insert into odont_horario values (7,3834393)
	 insert into odont_horario values (9,3834393)

	  insert into odont_horario values (2,1052248)
  insert into odont_horario values (4,1052248)
   insert into odont_horario values (6,1052248)
    insert into odont_horario values (8,1052248)
	 insert into odont_horario values (10,1052248)

	 select * from odont_horario

-- SERVICIO
insert into servicio values (01,'endodoncia','estudia las enfermedades de la pulpa de los dientes y sus técnicas de curación.',1)
insert into servicio values (02,'puente','Se realizan, fundas, coronas si faltan piezas dentales se realiza reemplazo, hasta 6 piezas',1)
insert into servicio values (03,'tartrectomía','Si un paciente tiene dientes manchados cerca a la encía, como una clasificación,(una caries inicial) se elimina',1)
insert into servicio values (04,'profilaxis','Se realiza limpieza de placa bacteriana',1)

-- ODONT_SERV
 insert into odont_serv values(1052248,01)
 insert into odont_serv values(3834393,03)
 insert into odont_serv values(1052248,02)
 insert into odont_serv values(3834393,04)

 -- ESTUDIO_COMPLEMENTARIO
 insert into estudio_complementario values(01,'Rayos X','Se hace una vista de la estructura interna actual de los dientes.',01)
 insert into estudio_complementario values(02,'Examen Sanguineo','Toma de muestras de sangre para evaluar el estado de globulos rojos, blancos y tiempo de coagulacion.',02)
 insert into estudio_complementario values(03,'Toma Presion','Verificacion de afecciones cardiacas.',03)

 -- MATERIA_PRIMA
 insert into materia_prima values (01,'sellante',null,1,10,2,8);
 insert into materia_prima values (02,'resina',null,1,200,10,190);
 insert into materia_prima values (03,'acrilico',null,1,300,30,270);

 -- SERV_MAT
 insert into serv_mat values(2,01,02)
 insert into serv_mat values(5,02,03)
 insert into serv_mat values(15,03,01)

 -- CITA
 insert into cita values(1,1,'2019-09-10','15:30',1,2,21902954,1,111)
 insert into cita values(2,1,'2019-09-01','14:30',2,1,21902954,2,333)
 insert into cita values(3,1,'2019-09-01','14:30',4,3,62904529,2,555)

-- PERMISO
insert into permiso values (1,01);
insert into permiso values (2,02);
insert into permiso values (3,03);
insert into permiso values (4,04);

-- ODONTOGRAMA
insert into odontograma values (123,2,1);
insert into odontograma values (345,1,2);
insert into odontograma values (567,4,3);

-- TIENE ESPECIALIDAD
insert into tiene_especialidad values (3834393,111);
insert into tiene_especialidad values (3834393,222);
insert into tiene_especialidad values (1052248,333);
insert into tiene_especialidad values (1052248,444);

-- RECETA
insert into receta values(1,'Tomar Paracetamol y calmantes',1,2)
insert into receta values(2,'Tomar antiinflamatorios',2,1)
insert into receta values(3,'Cada 5 Horas realizar enjuague bucal',3,4)

-- SERV_CONS
insert into serv_cons values (1,2,1)
insert into serv_cons values (2,1,2)
insert into serv_cons values (3,4,3)

-- NOTA VENTA
Insert into nota_vent values(111,1,2,3,'2019-06-25',80,0)
Insert into nota_vent values(222,2,1,4,'2019-09-30',100,50)
Insert into nota_vent values(333,3,4,6,'2019-12-04',300,100)

-- CUOTA
 insert into cuota values(111,01,111,'2019-05-25',30)
 insert into cuota values(222,02,555,'2019-06-15',30)
 insert into cuota values(222,03,555,'2019-08-30',20)
 insert into cuota values(333,04,222,'2019-10-17',50)

 -- DIENTE
insert into diente values(01,123,'Canino','Leche')
insert into diente values(02,345,'Molar','Permanente')
insert into diente values(04,123,'Incisivo','Leche')
insert into diente values(04,567,'Premolar','Permanente')

-- CP_DIENTE
 insert into cp_dental values(1,1,123,'El diente presenta una carie','El paciente tiene progreso')
 insert into cp_dental values(3,2,345,'El diente presenta una carie','Paciente debe seguir aplicando medicamento')
 insert into cp_dental values(4,4,123,'El diente presenta inflamacion alrededor','Seguir tomando antinflamatorio')

 -- FICHA_SERV
 insert into ficha_serv values(1,1)
 insert into ficha_serv values(2,3)

-- Mostrar los horarios de atencion de los odontologos X
select odontologo.nombre, horario.dia,horario.hora_inicio,horario.hora_fin
from horario,odontologo,odont_horario
where odont_horario.ciOdon=odontologo.ciOdon and odont_horario.idHor=horario.idHor and odontologo.nombre = 'Silvia Ventura'

-- Ultima fecha del paciente X
select ultimaConsulta
from historial,paciente
where historial.idHist=paciente.idHist and paciente.nombre = 'Luis Octavio Antelo'

-- Servicios que realiza el odontologo
select servicio.nombre,servicio.descripcion
from servicio,odont_serv,odontologo
where odont_serv.ciOdon=odontologo.ciOdon and servicio.idServ=odont_serv.idServ and odontologo.nombre='Silvia Ventura'

-- Diagnosticos de un paciente
select consulta.motivo,consulta.diagnostico,cita.fecha
from consulta,cita,paciente
where consulta.idCon=cita.idCon and consulta.idHist=paciente.idHist and cita.estado=1 and paciente.nombre='Sebastian Duran'



-- Mostrar las piezas dentales en que caras y en que consultas se le realizaron tratamientos al paciente con el nombre
/*
select cara_dental.nombre,diente.nombre,consulta.tratamiento,cp_dental.estado_diagnostico,cita.fecha
from cara_dental,diente,cp_dental,odontograma,consulta,cita,paciente
where  cara_dental.idCarDent=cp_dental.idCarDent and
	   cp_dental.idOdontoG=odontograma.idOdonto  and
	   diente.idOdonto = odontograma.idOdonto    and
	   odontograma.idCon = consulta.idCon	     and
	   cita.idCon = consulta.idCon				 and
	   cita.ciPaci = paciente.ciPaci			 and
	   paciente.nombre = 'Luis Octavio Antelo'*/