-- ============================================
-- 2. CREAR LA TABLA DOCTORES
-- ============================================
CREATE TABLE Doctores (
    ID_Doctor INT AUTO_INCREMENT PRIMARY KEY,
    Nombre VARCHAR(60) NOT NULL,
    Apellido VARCHAR(60) NOT NULL,
    Especialidad VARCHAR(100) NOT NULL,
    Telefono VARCHAR(20),
    Email VARCHAR(100) UNIQUE
);

-- ============================================
-- 3. CREAR LA TABLA PACIENTES
-- ============================================
CREATE TABLE Pacientes (
    ID_Paciente INT AUTO_INCREMENT PRIMARY KEY,
    Nombre VARCHAR(60) NOT NULL,
    Apellido VARCHAR(60) NOT NULL,
    Fecha_Nacimiento DATE,
    Direccion VARCHAR(150),
    Telefono VARCHAR(20),
    Email VARCHAR(100) UNIQUE
);

-- ============================================
-- 4. CREAR LA TABLA CAMAS
-- ============================================
CREATE TABLE Camas (
    ID_Cama INT AUTO_INCREMENT PRIMARY KEY,
    Numero_Cama VARCHAR(10) NOT NULL UNIQUE,
    Tipo_Cama VARCHAR(50) NOT NULL,
    Estado VARCHAR(20) NOT NULL DEFAULT 'Disponible',
    CHECK (Estado IN ('Disponible', 'Ocupada', 'Mantenimiento'))
);

-- ============================================
-- 5. CREAR LA TABLA SERVICIOS MEDICOS
-- ============================================
CREATE TABLE Servicios_Medicos (
    ID_Servicio INT AUTO_INCREMENT PRIMARY KEY,
    Nombre_Servicio VARCHAR(100) NOT NULL,
    Descripcion TEXT,
    Costo DECIMAL(10,2) NOT NULL,
    CHECK (Costo >= 0)
);

-- ============================================
-- 6. CREAR LA TABLA INGRESOS
-- ============================================
CREATE TABLE Ingresos (
    ID_Ingreso INT AUTO_INCREMENT PRIMARY KEY,
    ID_Paciente INT NOT NULL,
    ID_Doctor INT NOT NULL,
    ID_Cama INT NOT NULL,
    Fecha_Ingreso DATE NOT NULL,
    Fecha_Egreso DATE,
    Diagnostico TEXT,

    CONSTRAINT fk_ingreso_paciente
        FOREIGN KEY (ID_Paciente)
        REFERENCES Pacientes(ID_Paciente),

    CONSTRAINT fk_ingreso_doctor
        FOREIGN KEY (ID_Doctor)
        REFERENCES Doctores(ID_Doctor),

    CONSTRAINT fk_ingreso_cama
        FOREIGN KEY (ID_Cama)
        REFERENCES Camas(ID_Cama),

    CHECK (
        Fecha_Egreso IS NULL
        OR Fecha_Egreso >= Fecha_Ingreso
    )
);

-- ============================================
-- 7. CREAR LA TABLA TRATAMIENTOS
-- ============================================
CREATE TABLE Tratamientos (
    ID_Tratamiento INT AUTO_INCREMENT PRIMARY KEY,
    ID_Ingreso INT NOT NULL,
    ID_Servicio INT NOT NULL,
    Fecha_Tratamiento DATE NOT NULL,
    Descripcion TEXT,

    CONSTRAINT fk_tratamiento_ingreso
        FOREIGN KEY (ID_Ingreso)
        REFERENCES Ingresos(ID_Ingreso),

    CONSTRAINT fk_tratamiento_servicio
        FOREIGN KEY (ID_Servicio)
        REFERENCES Servicios_Medicos(ID_Servicio)
);

-- ============================================
-- 8. INSERTAR DOCTORES
-- ============================================
INSERT INTO Doctores
(Nombre, Apellido, Especialidad, Telefono, Email)
VALUES
('Carlos', 'Mendoza', 'Cardiologia', '0991111111',
 'carlos.mendoza@hospital.com'),
('Ana', 'Torres', 'Pediatria', '0992222222',
 'ana.torres@hospital.com'),
('Luis', 'Vera', 'Medicina General', '0993333333',
 'luis.vera@hospital.com');

-- ============================================
-- 9. INSERTAR PACIENTES
-- ============================================
INSERT INTO Pacientes
(Nombre, Apellido, Fecha_Nacimiento, Direccion, Telefono, Email)
VALUES
('Maria', 'Lopez', '1995-04-12', 'Guayaquil', '0981111111',
 'maria.lopez@email.com'),
('Pedro', 'Ramirez', '1988-09-20', 'Durán', '0982222222',
 'pedro.ramirez@email.com'),
('Sofia', 'Castro', '2001-01-15', 'Samborondon', '0983333333',
 'sofia.castro@email.com');

-- ============================================
-- 10. INSERTAR CAMAS
-- ============================================
INSERT INTO Camas
(Numero_Cama, Tipo_Cama, Estado)
VALUES
('C-101', 'Habitacion', 'Ocupada'),
('C-102', 'Habitacion', 'Ocupada'),
('UCI-01', 'UCI', 'Disponible'),
('C-103', 'Habitacion', 'Disponible');

-- ============================================
-- 11. INSERTAR SERVICIOS MEDICOS
-- ============================================
INSERT INTO Servicios_Medicos
(Nombre_Servicio, Descripcion, Costo)
VALUES
('Consulta general', 'Evaluacion medica general', 25.00),
('Radiografia', 'Estudio de imagenes medicas', 40.00),
('Examen de laboratorio', 'Analisis de muestras clinicas', 30.00),
('Electrocardiograma', 'Registro de la actividad electrica del corazon', 35.00);

-- ============================================
-- 12. INSERTAR INGRESOS
-- ============================================
INSERT INTO Ingresos
(ID_Paciente, ID_Doctor, ID_Cama, Fecha_Ingreso,
 Fecha_Egreso, Diagnostico)
VALUES
(1, 1, 1, '2026-10-01', NULL, 'Observacion cardiaca'),
(2, 2, 2, '2026-10-05', '2026-10-08', 'Control medico'),
(3, 3, 3, '2026-10-10', NULL, 'Evaluacion general');

-- ============================================
-- 13. INSERTAR TRATAMIENTOS
-- ============================================
INSERT INTO Tratamientos
(ID_Ingreso, ID_Servicio, Fecha_Tratamiento, Descripcion)
VALUES
(1, 4, '2026-10-01', 'Evaluacion del funcionamiento cardiaco'),
(2, 2, '2026-10-06', 'Radiografia de control'),
(3, 1, '2026-10-10', 'Consulta y valoracion general'),
(3, 3, '2026-10-10', 'Solicitud de examenes de laboratorio');

-- ============================================
-- 14. CONSULTAS SOLICITADAS EN EL PROYECTO
-- ============================================

-- A. Listar todos los doctores
SELECT * FROM Doctores;

-- B. Listar todos los pacientes
SELECT * FROM Pacientes;

-- C. Listar las camas disponibles
SELECT * FROM Camas
WHERE Estado = 'Disponible';

-- D. Listar todos los servicios medicos
SELECT * FROM Servicios_Medicos;

-- E. Mostrar la informacion de un paciente especifico
SELECT *
FROM Pacientes
WHERE ID_Paciente = 1;

-- F. Mostrar la informacion de un doctor especifico
SELECT *
FROM Doctores
WHERE ID_Doctor = 1;

-- G. Listar los pacientes atendidos por un doctor especifico
SELECT
    D.ID_Doctor,
    D.Nombre AS Nombre_Doctor,
    D.Apellido AS Apellido_Doctor,
    P.ID_Paciente,
    P.Nombre AS Nombre_Paciente,
    P.Apellido AS Apellido_Paciente,
    I.Fecha_Ingreso,
    I.Diagnostico
FROM Ingresos I
INNER JOIN Doctores D
    ON I.ID_Doctor = D.ID_Doctor
INNER JOIN Pacientes P
    ON I.ID_Paciente = P.ID_Paciente
WHERE D.ID_Doctor = 1;

-- ============================================
-- 15. CONSULTAS ADICIONALES PARA INFORMES
-- ============================================

-- A. Lista de pacientes por doctor
SELECT
    D.Nombre AS Nombre_Doctor,
    D.Apellido AS Apellido_Doctor,
    P.Nombre AS Nombre_Paciente,
    P.Apellido AS Apellido_Paciente
FROM Ingresos I
INNER JOIN Doctores D
    ON I.ID_Doctor = D.ID_Doctor
INNER JOIN Pacientes P
    ON I.ID_Paciente = P.ID_Paciente
ORDER BY D.Apellido, P.Apellido;

-- B. Lista de camas disponibles
SELECT ID_Cama, Numero_Cama, Tipo_Cama, Estado
FROM Camas
WHERE Estado = 'Disponible';

-- C. Lista de servicios medicos
SELECT ID_Servicio, Nombre_Servicio, Descripcion, Costo
FROM Servicios_Medicos;

-- D. Informe de ingresos por mes
SELECT
    YEAR(Fecha_Ingreso) AS Anio,
    MONTH(Fecha_Ingreso) AS Mes,
    COUNT(*) AS Total_Ingresos
FROM Ingresos
GROUP BY YEAR(Fecha_Ingreso), MONTH(Fecha_Ingreso)
ORDER BY Anio, Mes;

-- ============================================
-- 16. COMPROBAR LOS DATOS REGISTRADOS
-- ============================================
SELECT * FROM Doctores;
SELECT * FROM Pacientes;
SELECT * FROM Camas;
SELECT * FROM Servicios_Medicos;
SELECT * FROM Ingresos;
SELECT * FROM Tratamientos;
select * from Doctores ;
select * from Pacientes ;
select * from Doctores ;