si

create database Consultorio2

use Consultorio2;

-- Tablas Principales


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 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 (64) not null,
    estado varchar(32) not null
);

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 cita(
    idCita int not null primary key,
    estado varchar(32) 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 cascade
        on delete cascade


);


create table materia_prima(
    idMatPrim int not null primary key,
    nombre varchar(32) not null,
    descripcion varchar(128),
    estado varchar(32) 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,
    fecha date not null,
    hora_inicio time not null,
    hora_fin time not null
)


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 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
)

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

-- Tablas con llaves foraneas

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 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 agenda(
    idAgenda int not null primary key,
    idCita int not null foreign key references cita(idCita)
        on update cascade
        on delete cascade,
)

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

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 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,
 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 cascade
        on delete cascade,
 fecha DATE NOT NULL,
 monto INT NOT NULL

 )

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

 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 usuario (
 idUsuario INT NOT NULL PRIMARY KEY,
 nombre VARCHAR(20)NOT NULL,
 estado bit NOT NULL,
 contraseña VARCHAR(50) NOT NULL,
 idNota INT NOT NULL FOREIGN KEY REFERENCES nota_Compra(idNota)
        on update cascade
        on delete cascade,
 idRol int not null foreign key references rol(idRol)
        on update cascade
        on delete cascade
 )

 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)