CREATE DATABASE IF NOT EXISTS itzitacu_equipo3 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE itzitacu_equipo3;

CREATE TABLE IF NOT EXISTS disponibilidad_slots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  medico_id INT NOT NULL,
  semana_inicio DATE NOT NULL,
  inicio DATETIME NOT NULL,
  fin DATETIME NOT NULL,
  activo TINYINT NOT NULL DEFAULT 1,
  reservado_cita_id INT NULL,
  UNIQUE KEY uq_medico_inicio (medico_id, inicio),
  INDEX idx_semana (medico_id, semana_inicio),
  CONSTRAINT fk_slot_medico FOREIGN KEY (medico_id) REFERENCES medico(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS citas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  medico_id INT NOT NULL,
  paciente_id INT NOT NULL,
  slot_id INT NOT NULL UNIQUE,
  inicio DATETIME NOT NULL,
  fin DATETIME NOT NULL,
  estado ENUM('AGENDADA','COMPLETADA','CANCELADA','NO_ASISTIO') NOT NULL DEFAULT 'AGENDADA',
  nota_medico TEXT NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_cita_medico FOREIGN KEY (medico_id) REFERENCES medico(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_cita_paciente FOREIGN KEY (paciente_id) REFERENCES pacientes(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_cita_slot FOREIGN KEY (slot_id) REFERENCES disponibilidad_slots(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
