Base de Datos Mejorada - Sistema ReySalud

Estructura Optimizada

1. Tabla Rol

CREATE TABLE Rol (
    id_rol INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(50) NOT NULL UNIQUE,
    descripcion TEXT
);

-- Datos ejemplo:
INSERT INTO Rol VALUES 
(1, 'ADMINISTRADOR', 'Acceso completo al sistema'),
(2, 'MEDICO', 'Acceso a pacientes y consultas'),
(3, 'SECRETARIA', 'Gestión de turnos y pacientes');

2. Tabla Usuario

CREATE TABLE Usuario (
    id_usuario INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    estado ENUM('ACTIVO', 'INACTIVO') DEFAULT 'ACTIVO',
    ultimo_acceso DATETIME,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    id_rol INT NOT NULL,
    FOREIGN KEY (id_rol) REFERENCES Rol(id_rol)
);

-- Datos ejemplo:
INSERT INTO Usuario VALUES 
(101, 'admin', '$2y$10$hash...', 'ACTIVO', NULL, NOW(), 1),
(102, 'dr.garcia', '$2y$10$hash...', 'ACTIVO', NULL, NOW(), 2),
(103, 'secretaria.ana', '$2y$10$hash...', 'ACTIVO', NULL, NOW(), 3);

3. Tabla Especialidad ⭐ NUEVA

CREATE TABLE Especialidad (
    id_especialidad INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL UNIQUE,
    descripcion TEXT,
    duracion_turno_default INT DEFAULT 30, -- minutos
    estado ENUM('ACTIVA', 'INACTIVA') DEFAULT 'ACTIVA'
);

-- Datos ejemplo:
INSERT INTO Especialidad VALUES 
(1, 'CARDIOLOGIA', 'Especialidad del corazón', 45, 'ACTIVA'),
(2, 'PEDIATRIA', 'Atención de niños', 30, 'ACTIVA'),
(3, 'MEDICINA_GENERAL', 'Consulta general', 20, 'ACTIVA');

4. Tabla Personal (MEJORADA)

CREATE TABLE Personal (
    id_personal INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    apellido VARCHAR(100) NOT NULL,
    dni VARCHAR(20) UNIQUE,
    telefono VARCHAR(20),
    email VARCHAR(100),
    tipo_personal ENUM('MEDICO', 'SECRETARIA', 'ADMINISTRADOR') NOT NULL,
    matricula_profesional VARCHAR(50), -- Solo para médicos
    fecha_ingreso DATE,
    estado ENUM('ACTIVO', 'INACTIVO', 'LICENCIA') DEFAULT 'ACTIVO',
    id_usuario INT NOT NULL,
    id_especialidad INT, -- Solo para médicos
    FOREIGN KEY (id_usuario) REFERENCES Usuario(id_usuario),
    FOREIGN KEY (id_especialidad) REFERENCES Especialidad(id_especialidad),
    CHECK (tipo_personal = 'MEDICO' AND matricula_profesional IS NOT NULL AND id_especialidad IS NOT NULL 
           OR tipo_personal != 'MEDICO' AND matricula_profesional IS NULL AND id_especialidad IS NULL)
);

-- Datos ejemplo:
INSERT INTO Personal VALUES 
(1, 'Juan Francisco', 'García', '12345678', '555-0101', 'dr.garcia@clinica.com', 'MEDICO', 'MP12345', '2024-01-15', 'ACTIVO', 102, 1),
(2, 'Ana María', 'López', '87654321', '555-0102', 'ana.lopez@clinica.com', 'SECRETARIA', NULL, '2024-02-01', 'ACTIVO', 103, NULL),
(3, 'Carlos', 'Pérez', '11111111', '555-0103', 'admin@clinica.com', 'ADMINISTRADOR', NULL, '2024-01-01', 'ACTIVO', 101, NULL);

5. Tabla HorarioLaboral (MEJORADA)

CREATE TABLE HorarioLaboral (
    id_horario INT PRIMARY KEY AUTO_INCREMENT,
    id_personal INT NOT NULL,
    dia_semana ENUM('LUNES', 'MARTES', 'MIERCOLES', 'JUEVES', 'VIERNES', 'SABADO', 'DOMINGO') NOT NULL,
    hora_inicio TIME NOT NULL,
    hora_fin TIME NOT NULL,
    duracion_turno_minutos INT DEFAULT 30,
    estado ENUM('ACTIVO', 'INACTIVO', 'TEMPORAL') DEFAULT 'ACTIVO',
    fecha_desde DATE,
    fecha_hasta DATE, -- Para horarios temporales
    FOREIGN KEY (id_personal) REFERENCES Personal(id_personal),
    CHECK (hora_inicio < hora_fin),
    UNIQUE KEY unique_horario (id_personal, dia_semana, hora_inicio, estado)
);

-- Datos ejemplo:
INSERT INTO HorarioLaboral VALUES 
(1, 1, 'LUNES', '08:00:00', '12:00:00', 30, 'ACTIVO', '2024-01-01', NULL),
(2, 1, 'MIERCOLES', '15:00:00', '19:00:00', 30, 'ACTIVO', '2024-01-01', NULL),
(3, 1, 'VIERNES', '10:00:00', '17:00:00', 45, 'ACTIVO', '2024-01-01', NULL);

6. Tabla ObraSocial

CREATE TABLE ObraSocial (
    id_obra_social INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(150) NOT NULL,
    cuit VARCHAR(15),
    telefono VARCHAR(20),
    email VARCHAR(100),
    estado ENUM('ACTIVA', 'INACTIVA') DEFAULT 'ACTIVA'
);

-- Datos ejemplo:
INSERT INTO ObraSocial VALUES 
(1, 'OSDE', '30-12345678-9', '0800-555-6733', 'info@osde.com.ar', 'ACTIVA'),
(2, 'Swiss Medical', '30-87654321-0', '0800-777-9477', 'contacto@swissmedical.com.ar', 'ACTIVA'),
(3, 'PARTICULAR', NULL, NULL, NULL, 'ACTIVA');

7. Tabla Paciente

CREATE TABLE Paciente (
    id_paciente INT PRIMARY KEY AUTO_INCREMENT,
    dni VARCHAR(20) NOT NULL UNIQUE,
    nombre VARCHAR(100) NOT NULL,
    apellido VARCHAR(100) NOT NULL,
    fecha_nacimiento DATE NOT NULL,
    sexo ENUM('M', 'F', 'X') NOT NULL,
    direccion TEXT,
    telefono VARCHAR(20),
    email VARCHAR(100),
    numero_afiliado VARCHAR(50),
    contacto_emergencia VARCHAR(200),
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    estado ENUM('ACTIVO', 'INACTIVO') DEFAULT 'ACTIVO',
    id_obra_social INT,
    FOREIGN KEY (id_obra_social) REFERENCES ObraSocial(id_obra_social)
);

-- Datos ejemplo:
INSERT INTO Paciente VALUES 
(1001, '20123456789', 'María', 'González', '1985-03-15', 'F', 'Av. Siempre Viva 742', '555-1001', 'maria.gonzalez@email.com', 'OSDE123456', 'Juan González - 555-1002', NOW(), 'ACTIVO', 1),
(1002, '27987654321', 'Pedro', 'Martínez', '1992-07-22', 'M', 'Calle Falsa 123', '555-1003', 'pedro.martinez@email.com', NULL, 'Ana Martínez - 555-1004', NOW(), 'ACTIVO', 3);

8. Tabla HistoriaMedica (MEJORADA)

CREATE TABLE HistoriaMedica (
    id_historia_medica INT PRIMARY KEY AUTO_INCREMENT,
    numero_historia VARCHAR(20) UNIQUE, -- Número correlativo
    fecha_creacion DATE NOT NULL,
    fecha_ultima_modificacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    antecedentes_personales TEXT,
    antecedentes_familiares TEXT,
    alergias TEXT,
    medicacion_habitual TEXT,
    observaciones_generales TEXT,
    estado ENUM('ACTIVA', 'FINALIZADA', 'ARCHIVADA') DEFAULT 'ACTIVA',
    id_paciente INT NOT NULL,
    id_medico_responsable INT NOT NULL,
    FOREIGN KEY (id_paciente) REFERENCES Paciente(id_paciente),
    FOREIGN KEY (id_medico_responsable) REFERENCES Personal(id_personal)
);

-- Datos ejemplo:
INSERT INTO HistoriaMedica VALUES 
(2001, 'HM-2025-001', '2025-01-15', NOW(), 'Hipertensión arterial', 'Diabetes tipo 2 (padre)', 'Penicilina', 'Enalapril 10mg', 'Paciente colaborador', 'ACTIVA', 1001, 1),
(2002, 'HM-2025-002', '2025-02-01', NOW(), 'Sin antecedentes relevantes', 'Sin antecedentes', 'No refiere', 'No toma medicación', NULL, 'ACTIVA', 1002, 1);

9. Tabla Turno (MEJORADA)

CREATE TABLE Turno (
    id_turno INT PRIMARY KEY AUTO_INCREMENT,
    numero_turno VARCHAR(20) UNIQUE, -- Número de turno para el paciente
    fecha_turno DATE NOT NULL,
    hora_turno TIME NOT NULL,
    duracion_estimada INT DEFAULT 30, -- minutos
    motivo_consulta TEXT,
    estado ENUM('PROGRAMADO', 'CONFIRMADO', 'EN_ATENCION', 'COMPLETADO', 'CANCELADO', 'NO_ASISTIO') DEFAULT 'PROGRAMADO',
    observaciones TEXT,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_cancelacion DATETIME,
    motivo_cancelacion TEXT,
    id_paciente INT NOT NULL,
    id_medico INT NOT NULL,
    id_usuario_registro INT NOT NULL,
    FOREIGN KEY (id_paciente) REFERENCES Paciente(id_paciente),
    FOREIGN KEY (id_medico) REFERENCES Personal(id_personal),
    FOREIGN KEY (id_usuario_registro) REFERENCES Usuario(id_usuario),
    UNIQUE KEY unique_turno (fecha_turno, hora_turno, id_medico, estado)
);

-- Datos ejemplo:
INSERT INTO Turno VALUES 
(1, 'T-001-2025', '2025-09-15', '08:30:00', 30, 'Control cardiológico', 'CONFIRMADO', NULL, NOW(), NULL, NULL, 1001, 1, 103),
(2, 'T-002-2025', '2025-09-15', '09:00:00', 30, 'Consulta pediatría', 'PROGRAMADO', NULL, NOW(), NULL, NULL, 1002, 1, 103);

10. Tabla ConsultaMedica ⭐ NUEVA

CREATE TABLE ConsultaMedica (
    id_consulta INT PRIMARY KEY AUTO_INCREMENT,
    fecha_consulta DATETIME NOT NULL,
    motivo_consulta TEXT NOT NULL,
    examen_fisico TEXT,
    diagnostico_principal TEXT,
    diagnosticos_secundarios TEXT,
    tratamiento_indicado TEXT,
    medicacion_recetada TEXT,
    estudios_solicitados TEXT,
    proxima_cita DATE,
    observaciones TEXT,
    estado ENUM('EN_CURSO', 'FINALIZADA') DEFAULT 'FINALIZADA',
    id_turno INT NOT NULL,
    id_historia_medica INT NOT NULL,
    id_medico INT NOT NULL,
    FOREIGN KEY (id_turno) REFERENCES Turno(id_turno),
    FOREIGN KEY (id_historia_medica) REFERENCES HistoriaMedica(id_historia_medica),
    FOREIGN KEY (id_medico) REFERENCES Personal(id_personal)
);

-- Datos ejemplo:
INSERT INTO ConsultaMedica VALUES 
(1, '2025-09-15 08:30:00', 'Control rutinario cardiológico', 'PA: 140/90, FC: 78, peso: 75kg', 'Hipertensión arterial controlada', NULL, 'Continuar con medicación actual', 'Enalapril 10mg c/24hs', 'Ecocardiograma en 6 meses', '2026-03-15', 'Paciente estable', 'FINALIZADA', 1, 2001, 1);

Mejoras implementadas:

🎯 Funcionalidad de turnos mejorada:

• Número único de turno para referencia del paciente
• Estados detallados del turno (EN_ATENCION, NO_ASISTIO, etc.)
• Duración estimada por turno
• Motivo de consulta al solicitar turno
• Control de disponibilidad por médico/fecha/hora

🏥 Gestión médica profesional:

• Tabla Especialidad separada para mejor organización
• Números de historia médica únicos
• Estados de historia médica (ACTIVA, FINALIZADA, ARCHIVADA)
• Consultas médicas detalladas con diagnósticos y tratamientos
• Antecedentes médicos estructurados

🔒 Seguridad y trazabilidad:

• Passwords hasheados
• Último acceso registrado
• Fecha de creación en todos los registros
• Usuario que registró cada turno
• Estados en todas las entidades principales

📊 Consultas útiles del sistema:

-- Ver horarios disponibles de un médico
SELECT h.dia_semana, h.hora_inicio, h.hora_fin, h.duracion_turno_minutos
FROM HorarioLaboral h
JOIN Personal p ON h.id_personal = p.id_personal
WHERE p.nombre = 'Juan Francisco' AND p.apellido = 'García'
  AND h.estado = 'ACTIVO';

-- Buscar turnos disponibles para una fecha
SELECT TIME_FORMAT(SEC_TO_TIME(
    TIME_TO_SEC(h.hora_inicio) + (n.n * h.duracion_turno_minutos * 60)
), '%H:%i') as hora_disponible
FROM HorarioLaboral h
JOIN Personal p ON h.id_personal = p.id_personal
JOIN (SELECT 0 as n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) n
WHERE p.id_personal = 1 
  AND h.dia_semana = 'LUNES'
  AND TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(h.hora_inicio) + (n.n * h.duracion_turno_minutos * 60)), '%H:%i') < h.hora_fin
  AND NOT EXISTS (
    SELECT 1 FROM Turno t 
    WHERE t.id_medico = 1 
      AND t.fecha_turno = '2025-09-15'
      AND t.hora_turno = TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(h.hora_inicio) + (n.n * h.duracion_turno_minutos * 60)), '%H:%i')
      AND t.estado IN ('PROGRAMADO', 'CONFIRMADO', 'EN_ATENCION')
  );

Esta estructura es mucho más robusta, cumple todas las especificaciones del documento y permite un crecimiento futuro del sistema.