-- DEFINICION DE BASE DE DATOS OS --
drop database if exists OSHDB;
create database OSHDB;
use OSHDB;

create table Departamentos (
  id int(11) NOT NULL,
  nombre varchar(100) NOT NULL,
  constraint departamentos_PK primary key (id)
);

create table Municipios (
  id int(11) NOT NULL,
  nombre varchar(100) NOT NULL,
  codigo char(2) NOT NULL,
  id_departamento int NOT NULL,
  constraint municipios_PK primary key (id),
  constraint municipio_departamento_FK foreign key (id_departamento) references Departamentos(id) on update cascade on delete cascade
);

create table Prioridades(
	id char(2) not null,
	descripcion varchar(100) not null,
	constraint prioridad_PK primary key (id)
);

create table Procedimientos(
	id char(3) not null,
	descripcion varchar(100) not null,
	constraint procedimiento_PK primary key (id)
);

create table Tipos_Doctor(
	id char(10) not null,
	descripcion varchar(100) not null,
	constraint tipo_Doctor_PK primary key (id)
);

create table Tipos_Condicion(
	id char(2) not null,
	descripcion varchar(50) not null,
	constraint tipo_C_PK primary key (id)
);

create table Condiciones(
	id char(10) not null,
	descripcion varchar(100) not null,
	id_tipo char(2) not null,
	constraint condicion_PK primary key (id),
	constraint condicion_tipo_FK foreign key (id_tipo) references Tipos_Condicion(id) on update cascade on delete cascade
);

create table Hospitales(
	id int not null,
	nombre varchar(100) not null,
	id_departamento int(11),
    id_municipio int(11),
	constraint hospital_PK primary key (id),
    constraint hospital_departamento_FK foreign key (id_departamento) references Departamentos(id) on update cascade on delete cascade,
    constraint hospital_municipio_FK foreign key (id_municipio) references Municipios(id) on update cascade on delete cascade
);

create table Misiones(
	id char(8) not null,
	id_departamento int(11),
    id_municipio int(11),
	mes int not null,
	anio char(4) not null,
	constraint mision_PK primary key (id),
	constraint mision_departamento_FK foreign key (id_departamento) references Departamentos(id) on update cascade on delete cascade,
    constraint mision_municipio_FK foreign key (id_municipio) references Municipios(id) on update cascade on delete cascade
);

create table Doctores(
	dni char(13) not null,
	nombres varchar(100) not null,
	apellidos varchar(100) not null,
	id_tipo char(10) not null,
	constraint doctor_PK primary key (dni),
	constraint doctor_tipo_FK foreign key (id_tipo) references Tipos_Doctor(id) on update cascade on delete cascade
);

create table Pacientes(
	osh_id char(10) not null,
	dni char(13) not null,
	nombres varchar(100) not null,
	apellidos varchar(100) not null,
	fecha_nacimiento date,
	genero char(1),
	id_departamento int(11),
    id_municipio int(11),
    colonia varchar(250),
	retorno char(1) not null,
	constraint paciente_PK primary key (osh_id),
	constraint paciente_departamento_FK foreign key (id_departamento) references Departamentos(id) on update cascade on delete cascade,
    constraint paciente_municipio_FK foreign key (id_municipio) references Municipios(id) on update cascade on delete cascade
);

create table Encargados_Pacientes(
	osh_id char(10) not null,
	nombres_encargado varchar(100),
	apellidos_encargado varchar(100),
	telefono1 char(8),
	telefono2 char(8),
	telefono3 char(8),
	constraint encargado_paciente_PK primary key (osh_id),
	constraint encargado_paciente_FK foreign key (osh_id) references Pacientes(osh_id) on update cascade on delete cascade
);

create table Historiales(
	osh_id char(10) not null,
	id_mision char(8) not null,
	num_mision char(8) not null,
	distancia double,
	examen_dental char(1),
	lenguaje char(1),
	id_prioridad char(2) not null,
	candidato char(1),
	operado char(1),
	motivo_no_cirugia varchar(200),
	fecha date,
	ingerto_oseo varchar(100),
	id_anestesiologo char(13) not null,
	id_cirujano char(13) not null,
	id_hospital int not null,
	constraint historial_PK primary key (osh_id,id_mision),
	constraint historial_paciente_FK foreign key (osh_id) references Pacientes(osh_id) on update cascade on delete cascade,
	constraint historial_mision_FK foreign key (id_mision) references Misiones(id) on update cascade on delete cascade,
	constraint historial_prioridad_FK foreign key (id_prioridad) references Prioridades(id) on update cascade on delete cascade,
	constraint historial_anestesiologo_FK foreign key (id_anestesiologo) references Doctores(dni) on update cascade on delete cascade,
	constraint historial_cirujano_FK foreign key (id_cirujano) references Doctores(dni) on update cascade on delete cascade,
	constraint historial_hospital_FK foreign key (id_hospital) references Hospitales(id) on update cascade on delete cascade
);

create table Historiales_Procedimientos(
	osh_id char(10) not null,
	id_mision char(8) not null,
	id_procedimiento char(2) not null,
	constraint historial_P_PK primary key (osh_id,id_mision,id_procedimiento),
	constraint historial_P_historial_FK foreign key (osh_id,id_mision) references Historiales(osh_id,id_mision) on update cascade on delete cascade,
	constraint historial_P_procedimiento_FK foreign key (id_procedimiento) references Procedimientos(id) on update cascade on delete cascade
);

create table Historiales_Condiciones(
	osh_id char(10) not null,
	id_mision char(8) not null,
	id_condicion char(2) not null,
	constraint historial_condicion_PK primary key (osh_id,id_mision,id_condicion),
	constraint historial_C_historial_FK foreign key (osh_id,id_mision) references Historiales(osh_id,id_mision) on update cascade on delete cascade,
	constraint historial_C_condicion_FK foreign key (id_condicion) references Condiciones(id) on update cascade on delete cascade
);
