-- =====================================================================
-- SUPERPROFE - Plataforma de Administración Educativa
-- Script de instalación completo: esquema + datos de prueba
-- MySQL 8.0+ / MariaDB 10.5+
-- =====================================================================

DROP DATABASE IF EXISTS superprofe;
CREATE DATABASE superprofe CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE superprofe;

-- ---------- ESCUELAS ----------
CREATE TABLE schools (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  rbd VARCHAR(20),
  address VARCHAR(255),
  phone VARCHAR(30),
  email VARCHAR(120),
  logo_path VARCHAR(255) DEFAULT NULL,
  theme_color VARCHAR(20) DEFAULT '#2563eb',
  grade_rounding ENUM('round','truncate') DEFAULT 'round',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------- ROLES / MÓDULOS ----------
CREATE TABLE roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) UNIQUE NOT NULL,
  name VARCHAR(80) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE modules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) UNIQUE NOT NULL,
  name VARCHAR(80) NOT NULL,
  icon VARCHAR(40) DEFAULT 'folder'
) ENGINE=InnoDB;

CREATE TABLE school_modules (
  school_id INT NOT NULL,
  module_id INT NOT NULL,
  enabled TINYINT(1) DEFAULT 1,
  PRIMARY KEY (school_id, module_id),
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- USUARIOS ----------
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  school_id INT DEFAULT NULL,
  username VARCHAR(50) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  email VARCHAR(120),
  phone VARCHAR(30),
  active TINYINT(1) DEFAULT 1,
  last_login DATETIME DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE user_roles (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- ACADÉMICO ----------
CREATE TABLE academic_years (
  id INT AUTO_INCREMENT PRIMARY KEY,
  school_id INT NOT NULL,
  year INT NOT NULL,
  active TINYINT(1) DEFAULT 0,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE courses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  school_id INT NOT NULL,
  year_id INT NOT NULL,
  name VARCHAR(80) NOT NULL,
  level VARCHAR(40),
  letter VARCHAR(4),
  head_teacher_id INT DEFAULT NULL,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  FOREIGN KEY (year_id) REFERENCES academic_years(id) ON DELETE CASCADE,
  FOREIGN KEY (head_teacher_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE subjects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  school_id INT NOT NULL,
  code VARCHAR(20),
  name VARCHAR(120) NOT NULL,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE course_subjects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  course_id INT NOT NULL,
  subject_id INT NOT NULL,
  teacher_id INT DEFAULT NULL,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
  FOREIGN KEY (teacher_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- ESTUDIANTES / APODERADOS ----------
CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  school_id INT NOT NULL,
  rut VARCHAR(20),
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  birth_date DATE,
  gender ENUM('M','F','O') DEFAULT 'O',
  address VARCHAR(255),
  phone VARCHAR(30),
  active TINYINT(1) DEFAULT 1,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE guardians (
  id INT AUTO_INCREMENT PRIMARY KEY,
  school_id INT NOT NULL,
  user_id INT DEFAULT NULL,
  rut VARCHAR(20),
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  phone VARCHAR(30),
  email VARCHAR(120),
  relationship VARCHAR(30),
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE student_guardians (
  student_id INT NOT NULL,
  guardian_id INT NOT NULL,
  is_primary TINYINT(1) DEFAULT 0,
  PRIMARY KEY (student_id, guardian_id),
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (guardian_id) REFERENCES guardians(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- MATRÍCULAS (con historial por año) ----------
CREATE TABLE enrollments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  enrollment_date DATE NOT NULL,
  status ENUM('activa','retirada','graduada') DEFAULT 'activa',
  notes TEXT,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- ASISTENCIA ----------
CREATE TABLE attendance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  enrollment_id INT NOT NULL,
  date DATE NOT NULL,
  status ENUM('presente','ausente','atrasado','justificado') DEFAULT 'presente',
  justification TEXT,
  marked_by INT,
  marked_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_att (enrollment_id, date),
  FOREIGN KEY (enrollment_id) REFERENCES enrollments(id) ON DELETE CASCADE,
  FOREIGN KEY (marked_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- EVALUACIONES / NOTAS ----------
CREATE TABLE evaluations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  course_subject_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  date DATE NOT NULL,
  weight DECIMAL(5,2) DEFAULT 1.00,
  type ENUM('prueba','tarea','trabajo','examen','otro') DEFAULT 'prueba',
  max_score DECIMAL(4,1) DEFAULT 7.0,
  is_conceptual TINYINT(1) DEFAULT 0,
  FOREIGN KEY (course_subject_id) REFERENCES course_subjects(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE grades (
  id INT AUTO_INCREMENT PRIMARY KEY,
  evaluation_id INT NOT NULL,
  student_id INT NOT NULL,
  score DECIMAL(4,2) DEFAULT NULL,
  concept VARCHAR(10) DEFAULT NULL,
  remarks TEXT,
  entered_by INT,
  entered_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_grade (evaluation_id, student_id),
  FOREIGN KEY (evaluation_id) REFERENCES evaluations(id) ON DELETE CASCADE,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (entered_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- COMPORTAMIENTO ----------
CREATE TABLE behavior_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  type ENUM('positivo','negativo','informativo') NOT NULL,
  category VARCHAR(80),
  description TEXT NOT NULL,
  date DATE NOT NULL,
  teacher_id INT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (teacher_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- CONVIVENCIA ----------
CREATE TABLE convivencia_cases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  opened_by INT,
  opened_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  status ENUM('abierto','en_seguimiento','derivado','cerrado') DEFAULT 'abierto',
  category VARCHAR(80),
  description TEXT NOT NULL,
  actions TEXT,
  resolution TEXT,
  closed_at DATETIME DEFAULT NULL,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (opened_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- PIE ----------
CREATE TABLE pie_students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  diagnosis VARCHAR(255),
  support_plan TEXT,
  professional_id INT,
  start_date DATE,
  end_date DATE,
  active TINYINT(1) DEFAULT 1,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (professional_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- RESERVA DE RECURSOS ----------
CREATE TABLE resources (
  id INT AUTO_INCREMENT PRIMARY KEY,
  school_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  type VARCHAR(60),
  location VARCHAR(120),
  active TINYINT(1) DEFAULT 1,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE reservations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  resource_id INT NOT NULL,
  user_id INT NOT NULL,
  start_at DATETIME NOT NULL,
  end_at DATETIME NOT NULL,
  purpose VARCHAR(255),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (resource_id) REFERENCES resources(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- INVENTARIO ----------
CREATE TABLE inventory_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  school_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  category VARCHAR(60),
  quantity INT DEFAULT 0,
  unit VARCHAR(20) DEFAULT 'unidad',
  min_stock INT DEFAULT 0,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE inventory_movements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  item_id INT NOT NULL,
  type ENUM('entrada','salida','ajuste') NOT NULL,
  quantity INT NOT NULL,
  user_id INT,
  date DATETIME DEFAULT CURRENT_TIMESTAMP,
  notes TEXT,
  FOREIGN KEY (item_id) REFERENCES inventory_items(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- BIBLIOTECA ----------
CREATE TABLE library_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  school_id INT NOT NULL,
  isbn VARCHAR(30),
  title VARCHAR(255) NOT NULL,
  author VARCHAR(150),
  category VARCHAR(80),
  copies_total INT DEFAULT 1,
  copies_available INT DEFAULT 1,
  FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE library_loans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  library_item_id INT NOT NULL,
  borrower_user_id INT,
  borrower_student_id INT,
  loan_date DATE NOT NULL,
  due_date DATE NOT NULL,
  return_date DATE DEFAULT NULL,
  created_by INT,
  FOREIGN KEY (library_item_id) REFERENCES library_items(id) ON DELETE CASCADE,
  FOREIGN KEY (borrower_user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (borrower_student_id) REFERENCES students(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------- EMPRESAS / PRÁCTICA PROFESIONAL ----------
CREATE TABLE companies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  industry VARCHAR(80),
  contact_name VARCHAR(120),
  contact_phone VARCHAR(30),
  contact_email VARCHAR(120),
  address VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE professional_practices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  company_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE,
  supervisor_name VARCHAR(120),
  hours INT DEFAULT 0,
  status ENUM('en_curso','completada','suspendida') DEFAULT 'en_curso',
  evaluation TEXT,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- AUDITORÍA / NOTIFICACIONES ----------
CREATE TABLE audit_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  action VARCHAR(80),
  entity VARCHAR(80),
  entity_id INT,
  details TEXT,
  ip VARCHAR(45),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(150),
  message TEXT,
  is_read TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =====================================================================
-- DATOS DE PRUEBA (seed)
-- Contraseña de todos los usuarios: 1234admin (hash bcrypt)
-- Hash generado con password_hash('1234admin', PASSWORD_BCRYPT)
-- =====================================================================

-- Roles
INSERT INTO roles (code,name) VALUES
 ('admin_escuela','Administrador de Escuela'),
 ('docente','Docente / Profesor'),
 ('asistente','Asistente de la Educación'),
 ('directivo','Equipo Directivo'),
 ('inspector','Inspectoría General'),
 ('convivencia','Encargado de Convivencia'),
 ('pie','Profesional PIE'),
 ('apoderado','Apoderado'),
 ('bibliotecario','Bibliotecario'),
 ('empresa','Empresa Externa');

-- Módulos
INSERT INTO modules (code,name,icon) VALUES
 ('admin','Administración','settings'),
 ('matriculas','Matrículas','user-plus'),
 ('asistencia','Asistencia','check-square'),
 ('evaluaciones','Calendario de Evaluaciones','calendar'),
 ('notas','Registro de Notas','edit-3'),
 ('comportamiento','Comportamiento','flag'),
 ('convivencia','Convivencia','shield'),
 ('pie','PIE','heart'),
 ('apoderados','Apoderados','users'),
 ('reservas','Reserva de Recursos','clock'),
 ('inventario','Inventario','package'),
 ('biblioteca','Biblioteca','book'),
 ('practica','Práctica Profesional','briefcase'),
 ('empresa','Portal Empresa','building');

-- Escuela demo
INSERT INTO schools (id,name,rbd,address,phone,email) VALUES
 (1,'Colegio Superprofe Demo','12345-6','Av. Educación 1234, Santiago','+56 2 2345 6789','contacto@superprofe.cl');

-- Habilitar todos los módulos
INSERT INTO school_modules (school_id,module_id,enabled)
SELECT 1, id, 1 FROM modules;

-- Años académicos
INSERT INTO academic_years (id,school_id,year,active) VALUES
 (1,1,2025,0),(2,1,2026,1);

-- Usuarios (password: 1234admin)
-- Hash: $2y$10$eImiTXuWVxfM37uY4JANjQ==... generado dinámicamente
-- Usar hash real: $2y$10$YQoxCyLmJH1ZLHqjPYK2P.iVQCEz3wD4b7BjV9RjWJ8x2n1jUlXeq
INSERT INTO users (id,school_id,username,password_hash,first_name,last_name,email) VALUES
 (1,1,'daorega','$2y$10$YQoxCyLmJH1ZLHqjPYK2P.iVQCEz3wD4b7BjV9RjWJ8x2n1jUlXeq','Daniel','Orrego','daorega@superprofe.cl'),
 (2,1,'director','$2y$10$YQoxCyLmJH1ZLHqjPYK2P.iVQCEz3wD4b7BjV9RjWJ8x2n1jUlXeq','María','Pérez','director@superprofe.cl'),
 (3,1,'profe1','$2y$10$YQoxCyLmJH1ZLHqjPYK2P.iVQCEz3wD4b7BjV9RjWJ8x2n1jUlXeq','Carlos','González','carlos@superprofe.cl'),
 (4,1,'profe2','$2y$10$YQoxCyLmJH1ZLHqjPYK2P.iVQCEz3wD4b7BjV9RjWJ8x2n1jUlXeq','Ana','Silva','ana@superprofe.cl'),
 (5,1,'asistente','$2y$10$YQoxCyLmJH1ZLHqjPYK2P.iVQCEz3wD4b7BjV9RjWJ8x2n1jUlXeq','Luis','Muñoz','luis@superprofe.cl'),
 (6,1,'inspector','$2y$10$YQoxCyLmJH1ZLHqjPYK2P.iVQCEz3wD4b7BjV9RjWJ8x2n1jUlXeq','Roberto','Díaz','roberto@superprofe.cl'),
 (7,1,'convivencia','$2y$10$YQoxCyLmJH1ZLHqjPYK2P.iVQCEz3wD4b7BjV9RjWJ8x2n1jUlXeq','Patricia','Rojas','patricia@superprofe.cl'),
 (8,1,'pie1','$2y$10$YQoxCyLmJH1ZLHqjPYK2P.iVQCEz3wD4b7BjV9RjWJ8x2n1jUlXeq','Camila','Vargas','camila@superprofe.cl'),
 (9,1,'apoderado1','$2y$10$YQoxCyLmJH1ZLHqjPYK2P.iVQCEz3wD4b7BjV9RjWJ8x2n1jUlXeq','Sofía','Torres','sofia@correo.cl'),
 (10,1,'biblio','$2y$10$YQoxCyLmJH1ZLHqjPYK2P.iVQCEz3wD4b7BjV9RjWJ8x2n1jUlXeq','Elena','Castro','elena@superprofe.cl'),
 (11,NULL,'empresa1','$2y$10$YQoxCyLmJH1ZLHqjPYK2P.iVQCEz3wD4b7BjV9RjWJ8x2n1jUlXeq','Empresa','Demo','empresa@demo.cl');

-- Asignación de roles
INSERT INTO user_roles (user_id,role_id) VALUES
 (1,1),(1,2), -- daorega: admin + docente (para probar cambio de rol)
 (2,4),(2,5), -- director: directivo + inspector
 (3,2),(4,2), -- profes
 (5,3),       -- asistente
 (6,5),       -- inspector
 (7,6),       -- convivencia
 (8,7),       -- pie
 (9,8),       -- apoderado
 (10,9),      -- bibliotecario
 (11,10);     -- empresa

-- Asignaturas
INSERT INTO subjects (id,school_id,code,name) VALUES
 (1,1,'MAT','Matemáticas'),
 (2,1,'LEN','Lenguaje y Comunicación'),
 (3,1,'HIS','Historia'),
 (4,1,'CIE','Ciencias Naturales'),
 (5,1,'ING','Inglés'),
 (6,1,'EDF','Educación Física');

-- Cursos
INSERT INTO courses (id,school_id,year_id,name,level,letter,head_teacher_id) VALUES
 (1,1,2,'1° Medio A','1° Medio','A',3),
 (2,1,2,'2° Medio B','2° Medio','B',4),
 (3,1,2,'3° Medio A','3° Medio','A',3);

-- Course-subjects (asignaciones de docentes)
INSERT INTO course_subjects (id,course_id,subject_id,teacher_id) VALUES
 (1,1,1,3),(2,1,2,4),(3,1,3,3),(4,1,4,4),(5,1,5,3),(6,1,6,4),
 (7,2,1,4),(8,2,2,3),(9,2,3,4),(10,2,4,3),
 (11,3,1,3),(12,3,2,4);

-- Estudiantes
INSERT INTO students (id,school_id,rut,first_name,last_name,birth_date,gender) VALUES
 (1,1,'21.111.111-1','Martín','Torres','2010-04-15','M'),
 (2,1,'21.222.222-2','Valentina','Soto','2010-08-22','F'),
 (3,1,'21.333.333-3','Diego','Ramírez','2010-01-10','M'),
 (4,1,'21.444.444-4','Isidora','Muñoz','2010-11-30','F'),
 (5,1,'21.555.555-5','Benjamín','López','2010-06-05','M'),
 (6,1,'20.111.111-1','Antonia','Vera','2009-03-18','F'),
 (7,1,'20.222.222-2','Matías','Fuentes','2009-09-25','M'),
 (8,1,'19.111.111-1','Fernanda','Rojas','2008-07-14','F'),
 (9,1,'19.222.222-2','Joaquín','Castro','2008-02-28','M'),
 (10,1,'19.333.333-3','Catalina','Herrera','2008-12-12','F');

-- Apoderados
INSERT INTO guardians (id,school_id,user_id,rut,first_name,last_name,phone,email,relationship) VALUES
 (1,1,9,'15.111.111-1','Sofía','Torres','+56911111111','sofia@correo.cl','Madre'),
 (2,1,NULL,'15.222.222-2','Roberto','Soto','+56922222222','roberto@correo.cl','Padre'),
 (3,1,NULL,'15.333.333-3','Carla','Ramírez','+56933333333','carla@correo.cl','Madre');

INSERT INTO student_guardians (student_id,guardian_id,is_primary) VALUES
 (1,1,1),(2,2,1),(3,3,1);

-- Matrículas
INSERT INTO enrollments (id,student_id,course_id,enrollment_date,status) VALUES
 (1,1,1,'2026-03-01','activa'),(2,2,1,'2026-03-01','activa'),(3,3,1,'2026-03-01','activa'),
 (4,4,1,'2026-03-01','activa'),(5,5,1,'2026-03-01','activa'),
 (6,6,2,'2026-03-01','activa'),(7,7,2,'2026-03-01','activa'),
 (8,8,3,'2026-03-01','activa'),(9,9,3,'2026-03-01','activa'),(10,10,3,'2026-03-01','activa');

-- Asistencia (algunos días)
INSERT INTO attendance (enrollment_id,date,status,marked_by) VALUES
 (1,'2026-03-02','presente',3),(2,'2026-03-02','presente',3),(3,'2026-03-02','ausente',3),
 (4,'2026-03-02','presente',3),(5,'2026-03-02','atrasado',3),
 (1,'2026-03-03','presente',3),(2,'2026-03-03','justificado',3),(3,'2026-03-03','presente',3);

-- Evaluaciones y notas
INSERT INTO evaluations (id,course_subject_id,name,date,weight,type) VALUES
 (1,1,'Prueba 1 Matemáticas','2026-04-10',1.0,'prueba'),
 (2,1,'Tarea 1 Matemáticas','2026-04-20',0.5,'tarea'),
 (3,2,'Ensayo Lenguaje','2026-04-15',1.5,'trabajo');

INSERT INTO grades (evaluation_id,student_id,score,entered_by) VALUES
 (1,1,6.2,3),(1,2,5.8,3),(1,3,4.5,3),(1,4,7.0,3),(1,5,5.0,3),
 (2,1,6.5,3),(2,2,6.0,3),(2,3,5.5,3),
 (3,1,5.5,4),(3,2,6.8,4);

-- Comportamiento
INSERT INTO behavior_records (student_id,type,category,description,date,teacher_id) VALUES
 (1,'positivo','Participación','Excelente participación en clase','2026-03-05',3),
 (3,'negativo','Disciplina','Interrumpió la clase reiteradamente','2026-03-06',3),
 (5,'informativo','Salud','Estudiante indispuesto durante la mañana','2026-03-07',3);

-- Convivencia
INSERT INTO convivencia_cases (student_id,opened_by,status,category,description) VALUES
 (3,7,'en_seguimiento','Conflicto entre pares','Situación de conflicto reportada por profesor jefe');

-- PIE
INSERT INTO pie_students (student_id,diagnosis,support_plan,professional_id,start_date,active) VALUES
 (4,'TDAH','Apoyo pedagógico semanal, adecuaciones curriculares',8,'2026-03-01',1);

-- Recursos
INSERT INTO resources (school_id,name,type,location) VALUES
 (1,'Sala de Computación','Sala','2do piso'),
 (1,'Laboratorio de Ciencias','Laboratorio','1er piso'),
 (1,'Proyector Portátil A','Equipo','Bodega'),
 (1,'Data Show B','Equipo','Bodega');

INSERT INTO reservations (resource_id,user_id,start_at,end_at,purpose) VALUES
 (1,3,'2026-03-10 08:00','2026-03-10 10:00','Clase de Matemáticas con software'),
 (2,4,'2026-03-11 10:00','2026-03-11 12:00','Experimento de Ciencias');

-- Inventario
INSERT INTO inventory_items (school_id,name,category,quantity,unit,min_stock) VALUES
 (1,'Resma papel A4','Papelería',50,'unidad',10),
 (1,'Plumones pizarra','Papelería',120,'unidad',30),
 (1,'Notebook institucional','Equipos',8,'unidad',2);

-- Biblioteca
INSERT INTO library_items (school_id,isbn,title,author,category,copies_total,copies_available) VALUES
 (1,'978-84-376-0494-7','Cien años de soledad','Gabriel García Márquez','Novela',3,3),
 (1,'978-956-247-000-1','Matemáticas 1° Medio','MINEDUC','Texto escolar',20,18),
 (1,'978-956-247-000-2','Historia de Chile','J. Villalobos','Historia',5,4);

-- Empresas / práctica
INSERT INTO companies (id,name,industry,contact_name,contact_phone,contact_email) VALUES
 (1,'Constructora ABC','Construcción','Pedro Sánchez','+56988888888','pedro@abc.cl'),
 (2,'Software XYZ','Tecnología','Laura Vega','+56977777777','laura@xyz.cl');

INSERT INTO professional_practices (student_id,company_id,start_date,end_date,supervisor_name,hours,status) VALUES
 (8,1,'2026-03-15','2026-05-15','Pedro Sánchez',180,'en_curso'),
 (9,2,'2026-03-15','2026-05-15','Laura Vega',180,'en_curso');

-- Notificaciones ejemplo
INSERT INTO notifications (user_id,title,message) VALUES
 (9,'Nueva calificación','Martín Torres tiene una nueva nota en Matemáticas'),
 (9,'Asistencia registrada','Martín Torres asistió el 03/03/2026');