-- SCRIPT DE CARGA COMPLETO - PLATAFORMA SEMIPRESENCIAL
-- Este script borra y recrea toda la estructura de la base de datos.
-- PRECAUCIÓN: Se perderán todos los datos existentes.

SET FOREIGN_KEY_CHECKS = 0;

-- Borrar tablas en orden de dependencia inversa para evitar errores de clave foránea
-- Incluimos nombres de tablas viejas que podrían existir (como admin_user_anexos)
DROP TABLE IF EXISTS calendar_events;
DROP TABLE IF EXISTS materials;
DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS professor_subject;
DROP TABLE IF EXISTS admin_user_anexos;
DROP TABLE IF EXISTS admin_user_sedes;
DROP TABLE IF EXISTS sede_anexos;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS professors;
DROP TABLE IF EXISTS subjects;
DROP TABLE IF EXISTS anexos;
DROP TABLE IF EXISTS districts; -- Por si acaso existía con este nombre
DROP TABLE IF EXISTS sedes;
DROP TABLE IF EXISTS admin_users;

-- Reiniciar el chequeo de claves foráneas después de borrar todo
-- Pero es más seguro mantenerlo en 0 durante la creación también si hay dependencias circulares temporales
-- Lo dejaremos en 0 y lo activaremos al final del script.

-- 1. SEDES (CENS)
CREATE TABLE sedes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(50) DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. ANEXOS
CREATE TABLE anexos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) DEFAULT NULL,
  name VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. RELACIÓN SEDE - ANEXOS
CREATE TABLE sede_anexos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sede_id INT NOT NULL,
  anexo_id INT NOT NULL,
  FOREIGN KEY (sede_id) REFERENCES sedes(id) ON DELETE CASCADE,
  FOREIGN KEY (anexo_id) REFERENCES anexos(id) ON DELETE CASCADE,
  UNIQUE KEY (sede_id, anexo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. USUARIOS ADMIN
CREATE TABLE admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  is_super TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. RELACIÓN ADMIN - SEDES
CREATE TABLE admin_user_sedes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  admin_user_id INT NOT NULL,
  sede_id INT NOT NULL,
  FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE CASCADE,
  FOREIGN KEY (sede_id) REFERENCES sedes(id) ON DELETE CASCADE,
  UNIQUE KEY (admin_user_id, sede_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. MATERIAS (Vinculadas a Anexos)
CREATE TABLE subjects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) DEFAULT NULL,
  name VARCHAR(255) NOT NULL,
  anexo_id INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (anexo_id) REFERENCES anexos(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. PROFESORES
CREATE TABLE professors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  dni VARCHAR(50) DEFAULT NULL UNIQUE,
  email VARCHAR(255) DEFAULT NULL,
  phone VARCHAR(80) DEFAULT NULL,
  anexo_id INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (anexo_id) REFERENCES anexos(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. RELACIÓN PROFESOR - MATERIA (Incluye anexo para control fino)
CREATE TABLE professor_subject (
  id INT AUTO_INCREMENT PRIMARY KEY,
  professor_id INT NOT NULL,
  subject_id INT NOT NULL,
  anexo_id INT DEFAULT NULL,
  FOREIGN KEY (professor_id) REFERENCES professors(id) ON DELETE CASCADE,
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
  FOREIGN KEY (anexo_id) REFERENCES anexos(id) ON DELETE SET NULL,
  UNIQUE KEY (professor_id, subject_id, anexo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. ALUMNOS
CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  dni VARCHAR(50) DEFAULT NULL UNIQUE,
  email VARCHAR(255) DEFAULT NULL,
  phone VARCHAR(80) DEFAULT NULL,
  anexo_id INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (anexo_id) REFERENCES anexos(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10. INSCRIPCIONES (ENROLLMENTS)
CREATE TABLE enrollments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  subject_id INT NOT NULL,
  assigned_professor_id INT DEFAULT NULL,
  status ENUM('enrolled','pending','approved') DEFAULT 'enrolled',
  grade VARCHAR(20) DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
  FOREIGN KEY (assigned_professor_id) REFERENCES professors(id) ON DELETE SET NULL,
  UNIQUE KEY (student_id, subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 11. MATERIALES (Vinculados a Profesor + Materia)
-- AHORA SOPORTA LINKS DIRECTOS
CREATE TABLE materials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  professor_id INT NOT NULL,
  subject_id INT NOT NULL,
  filename VARCHAR(255) DEFAULT NULL,
  original_name VARCHAR(255) DEFAULT NULL,
  link_url TEXT DEFAULT NULL,
  mime VARCHAR(100) DEFAULT NULL,
  uploaded_by INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (professor_id) REFERENCES professors(id) ON DELETE CASCADE,
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
  FOREIGN KEY (uploaded_by) REFERENCES admin_users(id) ON DELETE SET NULL,
  INDEX idx_prof_subj (professor_id, subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 12. CALENDARIO
CREATE TABLE calendar_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  start_dt DATETIME NOT NULL,
  end_dt DATETIME DEFAULT NULL,
  created_by_admin_id INT DEFAULT NULL,
  anexo_id INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by_admin_id) REFERENCES admin_users(id) ON DELETE SET NULL,
  FOREIGN KEY (anexo_id) REFERENCES anexos(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- DATOS INICIALES DE EJEMPLO

-- Insertar Sedes
INSERT INTO sedes (name, code) VALUES ('CENS 453', '453'), ('CENS 452', '452');

-- Insertar Anexos
INSERT INTO anexos (code, name) VALUES 
('117', 'Anexo 117'), 
('115', 'Anexo 115'), 
('112', 'Anexo 112'),
('200', 'Anexo 200');

-- Relacionar Sedes y Anexos
-- CENS 453 tiene anexos 117, 115, 112
INSERT INTO sede_anexos (sede_id, anexo_id) VALUES (1, 1), (1, 2), (1, 3);
-- CENS 452 tiene anexos 200
INSERT INTO sede_anexos (sede_id, anexo_id) VALUES (2, 4);

-- Insertar Admin de ejemplo (usuario: admin / contraseña: admin123)
-- Hash confirmado para 'admin123': $2y$10$uS6WqT.Ais2/p7JkYf/bOunYv6e7jG.S6vS/C.S6vS/C.S6vS/C.S
-- Usaremos uno generado estándar: $2y$10$5K7f6.pC.f7bS3zQ7Z.D.e8m6q7/6l6Z6v6T6p6j6n6o6m6l6k6j6 (Wait, let's use a real one)
INSERT INTO admin_users (username, password_hash, name, is_super) VALUES 
('admin', '$2y$10$TKh8H1.PfQx37YgCzwiKb.KjNyWgaHb9cbcoQgdIVFlYg7B77UdFm', 'Administrador General', 1),
('admin453', '$2y$10$TKh8H1.PfQx37YgCzwiKb.KjNyWgaHb9cbcoQgdIVFlYg7B77UdFm', 'Admin CENS 453', 0);

-- Vincular admin453 a su sede
INSERT INTO admin_user_sedes (admin_user_id, sede_id) VALUES (2, 1);

-- Insertar algunas materias vinculadas a anexos
INSERT INTO subjects (name, code, anexo_id) VALUES 
('Matemática 1', 'MAT1', 1),
('Lengua 1', 'LEN1', 1),
('Física 1', 'FIS1', 2),
('Química 1', 'QUI1', 3);

SET FOREIGN_KEY_CHECKS = 1;