-- ============================================================
-- Superprofe v2.0.0 · Migración incremental
-- Ejecutar UNA VEZ tras la instalación v1.
-- Es idempotente (usa IF NOT EXISTS / IGNORE / IF EXISTS).
-- ============================================================
USE superprofe;

CREATE TABLE IF NOT EXISTS schema_version (v INT PRIMARY KEY, applied_at DATETIME DEFAULT CURRENT_TIMESTAMP);

-- ----- Master lists (listas maestras editables por admin) -----
CREATE TABLE IF NOT EXISTS master_lists (
  id INT AUTO_INCREMENT PRIMARY KEY,
  list_code VARCHAR(40) NOT NULL,
  value VARCHAR(150) NOT NULL,
  active TINYINT(1) DEFAULT 1,
  sort_order INT DEFAULT 0,
  KEY (list_code)
) ENGINE=InnoDB;

-- ----- Extend students -----
ALTER TABLE students
  ADD COLUMN last_name_p VARCHAR(80) AFTER last_name,
  ADD COLUMN last_name_m VARCHAR(80) AFTER last_name_p,
  ADD COLUMN email VARCHAR(120),
  ADD COLUMN autoriza_imagen TINYINT(1) DEFAULT 0,
  ADD COLUMN nacionalidad VARCHAR(60),
  ADD COLUMN comuna VARCHAR(80),
  ADD COLUMN establecimiento_procedencia VARCHAR(150),
  ADD COLUMN curso_ano_anterior VARCHAR(60),
  ADD COLUMN pie TINYINT(1) DEFAULT 0,
  ADD COLUMN repitencia TINYINT(1) DEFAULT 0,
  ADD COLUMN repitencia_detalle VARCHAR(255),
  ADD COLUMN sistema_salud VARCHAR(80),
  ADD COLUMN religion VARCHAR(80),
  ADD COLUMN con_quien_vive VARCHAR(255),
  ADD COLUMN cantidad_hermanos INT DEFAULT 0,
  ADD COLUMN cantidad_integrantes INT DEFAULT 0,
  ADD COLUMN etnia VARCHAR(255);

-- ----- Extend guardians -----
ALTER TABLE guardians
  ADD COLUMN tipo ENUM('mama','papa','apoderado1','apoderado2') AFTER school_id,
  ADD COLUMN last_name_p VARCHAR(80) AFTER last_name,
  ADD COLUMN last_name_m VARCHAR(80),
  ADD COLUMN fecha_nacimiento DATE,
  ADD COLUMN direccion VARCHAR(255),
  ADD COLUMN comuna VARCHAR(80),
  ADD COLUMN escolaridad VARCHAR(80),
  ADD COLUMN misma_direccion_alumno TINYINT(1) DEFAULT 0;

-- ----- Ficha médica / emergencia / legal / otros -----
CREATE TABLE IF NOT EXISTS medical_info (
  student_id INT PRIMARY KEY,
  en_tratamiento TINYINT(1) DEFAULT 0,
  tratamientos VARCHAR(500),
  toma_medicamentos TINYINT(1) DEFAULT 0,
  medicamentos_detalle TEXT,
  alergias TINYINT(1) DEFAULT 0,
  alergias_detalle TEXT,
  peso_nacer DECIMAL(5,2),
  medida_nacer DECIMAL(5,2),
  peso_actual DECIMAL(5,2),
  medida_actual DECIMAL(5,2),
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS emergency_contacts (
  student_id INT PRIMARY KEY,
  nombre VARCHAR(150),
  relacion VARCHAR(80),
  numero1 VARCHAR(30),
  numero2 VARCHAR(30),
  email VARCHAR(120),
  direccion VARCHAR(255),
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS student_legal (
  student_id INT PRIMARY KEY,
  observacion TEXT,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS student_other (
  student_id INT PRIMARY KEY,
  informacion TEXT,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----- Bloques de reserva (definidos por admin) -----
CREATE TABLE IF NOT EXISTS reservation_blocks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  school_id INT NOT NULL,
  block_order INT DEFAULT 0,
  name VARCHAR(40) NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS reservation_slots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  resource_id INT NOT NULL,
  block_id INT NOT NULL,
  date DATE NOT NULL,
  user_id INT NOT NULL,
  purpose VARCHAR(255),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_slot (resource_id, block_id, date),
  FOREIGN KEY (resource_id) REFERENCES resources(id) ON DELETE CASCADE,
  FOREIGN KEY (block_id) REFERENCES reservation_blocks(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS reservation_blocked (
  id INT AUTO_INCREMENT PRIMARY KEY,
  resource_id INT NOT NULL,
  block_id INT NOT NULL,
  date DATE NOT NULL,
  reason VARCHAR(255) NOT NULL,
  blocked_by INT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_blocked (resource_id, block_id, date),
  FOREIGN KEY (resource_id) REFERENCES resources(id) ON DELETE CASCADE,
  FOREIGN KEY (block_id) REFERENCES reservation_blocks(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----- Semillas: listas maestras -----
INSERT INTO master_lists (list_code, value, sort_order) VALUES
 ('escolaridad','Sin estudios',1),('escolaridad','Básica incompleta',2),('escolaridad','Básica completa',3),
 ('escolaridad','Media incompleta',4),('escolaridad','Media completa',5),('escolaridad','Técnica',6),
 ('escolaridad','Universitaria',7),('escolaridad','Postgrado',8),
 ('religion','Católica',1),('religion','Evangélica',2),('religion','Otra',3),('religion','Ninguna',4),
 ('sistema_salud','Fonasa',1),('sistema_salud','Isapre',2),('sistema_salud','Particular',3),('sistema_salud','Otro',4),
 ('con_quien_vive','Madre',1),('con_quien_vive','Padre',2),('con_quien_vive','Hermanos',3),
 ('con_quien_vive','Abuelos',4),('con_quien_vive','Tíos',5),('con_quien_vive','Otros',6),
 ('etnia','Mapuche',1),('etnia','Aymara',2),('etnia','Rapa Nui',3),('etnia','Quechua',4),
 ('etnia','Diaguita',5),('etnia','Ninguna',6),
 ('tratamiento','Psicológico',1),('tratamiento','Psiquiátrico',2),('tratamiento','Fonoaudiológico',3),
 ('tratamiento','Neurológico',4),('tratamiento','Terapia Ocupacional',5),('tratamiento','Otro',6),
 ('nacionalidad','Chilena',1),('nacionalidad','Peruana',2),('nacionalidad','Boliviana',3),
 ('nacionalidad','Colombiana',4),('nacionalidad','Venezolana',5),('nacionalidad','Otra',6);

-- ----- Semillas: bloques horarios estándar -----
INSERT INTO reservation_blocks (school_id, block_order, name, start_time, end_time) VALUES
 (1,1,'Bloque 1','08:00:00','09:30:00'),
 (1,2,'Bloque 2','09:45:00','11:15:00'),
 (1,3,'Bloque 3','11:30:00','13:00:00'),
 (1,4,'Almuerzo','13:00:00','14:00:00'),
 (1,5,'Bloque 4','14:00:00','15:30:00'),
 (1,6,'Bloque 5','15:45:00','17:15:00');

-- ----- Nuevo rol: administrativo, profesional -----
INSERT IGNORE INTO roles (code, name) VALUES ('administrativo','Administrativo'),('profesional','Profesional');

-- ----- Nuevo módulo: Alumnos + Cursos -----
INSERT IGNORE INTO modules (code, name, icon) VALUES ('alumnos','Alumnos','graduation-cap'),('cursos','Cursos','layers');
INSERT IGNORE INTO school_modules (school_id, module_id, enabled)
  SELECT s.id, m.id, 1 FROM schools s CROSS JOIN modules m WHERE m.code IN ('alumnos','cursos');

-- Columna curso en reservation_slots (opcional; ignorar si ya existe)
ALTER TABLE reservation_slots ADD COLUMN course_id INT DEFAULT NULL AFTER user_id;

INSERT INTO schema_version (v) VALUES (2);