-- ============================================
-- SICA - Sistema de Control de Asistencias e Incidencias
-- Script de Creación de Tablas
-- Versión: 1.0
-- Fecha: 20 de Octubre, 2025
-- ============================================

-- Configurar la base de datos
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================
-- TABLAS DE CONFIGURACIÓN ACADÉMICA
-- ============================================

-- Tabla: años lectivos
CREATE TABLE IF NOT EXISTS anos_lectivos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ano VARCHAR(4) NOT NULL UNIQUE,
    estado ENUM('ACTIVO', 'INACTIVO') DEFAULT 'ACTIVO',
    fecha_inicio DATE NOT NULL,
    fecha_fin DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_estado (estado),
    INDEX idx_ano (ano)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: niveles educativos
CREATE TABLE IF NOT EXISTS niveles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    descripcion VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: grados
CREATE TABLE IF NOT EXISTS grados (
    id INT PRIMARY KEY AUTO_INCREMENT,
    descripcion VARCHAR(50) NOT NULL,
    orden INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_orden (orden)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: secciones
CREATE TABLE IF NOT EXISTS secciones (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(10) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: áreas curriculares
CREATE TABLE IF NOT EXISTS areas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    descripcion VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: turnos
CREATE TABLE IF NOT EXISTS turnos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    descripcion VARCHAR(50) NOT NULL,
    hora_inicio TIME NOT NULL,
    hora_fin TIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: cargos administrativos
CREATE TABLE IF NOT EXISTS cargos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    descripcion VARCHAR(100) NOT NULL,
    observaciones TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- CALENDARIO DE DÍAS NO LABORABLES
-- ============================================

CREATE TABLE IF NOT EXISTS calendario_no_laborables (
    id INT PRIMARY KEY AUTO_INCREMENT,
    fecha DATE NOT NULL,
    motivo VARCHAR(255) NOT NULL,
    tipo ENUM('FERIADO', 'DIA_INSTITUCIONAL', 'SUSPENSION') NOT NULL,
    ano_lectivo_id INT NOT NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ano_lectivo_id) REFERENCES anos_lectivos(id) ON DELETE CASCADE,
    UNIQUE KEY unique_fecha_ano (fecha, ano_lectivo_id),
    INDEX idx_fecha (fecha),
    INDEX idx_ano_lectivo (ano_lectivo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- USUARIOS Y AUTENTICACIÓN
-- ============================================

CREATE TABLE IF NOT EXISTS usuarios (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    rol ENUM('SUPER_ADMIN', 'ADMIN', 'DOCENTE', 'AUXILIAR', 'APODERADO', 'ESTUDIANTE') NOT NULL,
    estado ENUM('ACTIVO', 'INACTIVO', 'BLOQUEADO') DEFAULT 'ACTIVO',
    ultimo_acceso TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_rol (rol),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- PERSONAS: ESTUDIANTES, DOCENTES, APODERADOS
-- ============================================

-- Tabla: estudiantes
CREATE TABLE IF NOT EXISTS estudiantes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombres VARCHAR(100) NOT NULL,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100) NOT NULL,
    dni VARCHAR(20) NOT NULL UNIQUE,
    direccion VARCHAR(255),
    celular VARCHAR(20),
    email VARCHAR(100),
    genero ENUM('M', 'F') NOT NULL,
    fecha_nacimiento DATE NOT NULL,
    foto VARCHAR(255),
    usuario_id INT,
    estado ENUM('ACTIVO', 'RETIRADO', 'TRASLADADO') DEFAULT 'ACTIVO',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    INDEX idx_dni (dni),
    INDEX idx_nombres (apellido_paterno, apellido_materno, nombres),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: apoderados
CREATE TABLE IF NOT EXISTS apoderados (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombres VARCHAR(100) NOT NULL,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100) NOT NULL,
    dni VARCHAR(20) NOT NULL UNIQUE,
    direccion VARCHAR(255),
    celular VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    genero ENUM('M', 'F') NOT NULL,
    fecha_nacimiento DATE,
    usuario_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    INDEX idx_dni (dni),
    INDEX idx_celular (celular)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: relación estudiante-apoderado
CREATE TABLE IF NOT EXISTS estudiante_apoderado (
    estudiante_id INT NOT NULL,
    apoderado_id INT NOT NULL,
    parentesco ENUM('PADRE', 'MADRE', 'TUTOR', 'ABUELO', 'OTRO') NOT NULL,
    es_principal BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (estudiante_id, apoderado_id),
    FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id) ON DELETE CASCADE,
    FOREIGN KEY (apoderado_id) REFERENCES apoderados(id) ON DELETE CASCADE,
    INDEX idx_principal (es_principal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: docentes
CREATE TABLE IF NOT EXISTS docentes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombres VARCHAR(100) NOT NULL,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100) NOT NULL,
    dni VARCHAR(20) NOT NULL UNIQUE,
    direccion VARCHAR(255),
    celular VARCHAR(20),
    email VARCHAR(100),
    fecha_nacimiento DATE,
    horario_atencion VARCHAR(100),
    usuario_id INT,
    estado ENUM('ACTIVO', 'INACTIVO', 'LICENCIA') DEFAULT 'ACTIVO',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    INDEX idx_dni (dni),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: relación docente-área
CREATE TABLE IF NOT EXISTS docente_area (
    docente_id INT NOT NULL,
    area_id INT NOT NULL,
    PRIMARY KEY (docente_id, area_id),
    FOREIGN KEY (docente_id) REFERENCES docentes(id) ON DELETE CASCADE,
    FOREIGN KEY (area_id) REFERENCES areas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: auxiliares de educación
CREATE TABLE IF NOT EXISTS auxiliares (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombres VARCHAR(100) NOT NULL,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100) NOT NULL,
    dni VARCHAR(20) NOT NULL UNIQUE,
    celular VARCHAR(20),
    email VARCHAR(100),
    usuario_id INT,
    estado ENUM('ACTIVO', 'INACTIVO') DEFAULT 'ACTIVO',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    INDEX idx_dni (dni)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: administradores
CREATE TABLE IF NOT EXISTS administradores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombres VARCHAR(100) NOT NULL,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100) NOT NULL,
    dni VARCHAR(20) NOT NULL UNIQUE,
    cargo_id INT NOT NULL,
    celular VARCHAR(20),
    email VARCHAR(100),
    usuario_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (cargo_id) REFERENCES cargos(id),
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    INDEX idx_dni (dni)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- PROGRAMACIÓN ACADÉMICA
-- ============================================

CREATE TABLE IF NOT EXISTS programaciones (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ano_lectivo_id INT NOT NULL,
    nivel_id INT NOT NULL,
    grado_id INT NOT NULL,
    seccion_id INT NOT NULL,
    turno_id INT NOT NULL,
    fecha_inicio DATE NOT NULL,
    fecha_fin DATE NOT NULL,
    auxiliar_id INT,
    estado ENUM('ACTIVO', 'FINALIZADO') DEFAULT 'ACTIVO',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (ano_lectivo_id) REFERENCES anos_lectivos(id),
    FOREIGN KEY (nivel_id) REFERENCES niveles(id),
    FOREIGN KEY (grado_id) REFERENCES grados(id),
    FOREIGN KEY (seccion_id) REFERENCES secciones(id),
    FOREIGN KEY (turno_id) REFERENCES turnos(id),
    FOREIGN KEY (auxiliar_id) REFERENCES auxiliares(id) ON DELETE SET NULL,
    INDEX idx_ano_lectivo (ano_lectivo_id),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: estudiantes asignados a programación
CREATE TABLE IF NOT EXISTS programacion_estudiantes (
    programacion_id INT NOT NULL,
    estudiante_id INT NOT NULL,
    fecha_inscripcion DATE DEFAULT (CURRENT_DATE),
    estado ENUM('MATRICULADO', 'RETIRADO') DEFAULT 'MATRICULADO',
    PRIMARY KEY (programacion_id, estudiante_id),
    FOREIGN KEY (programacion_id) REFERENCES programaciones(id) ON DELETE CASCADE,
    FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id) ON DELETE CASCADE,
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: docentes asignados a programación
CREATE TABLE IF NOT EXISTS programacion_docentes (
    programacion_id INT NOT NULL,
    docente_id INT NOT NULL,
    area_id INT NOT NULL,
    es_tutor BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (programacion_id, docente_id, area_id),
    FOREIGN KEY (programacion_id) REFERENCES programaciones(id) ON DELETE CASCADE,
    FOREIGN KEY (docente_id) REFERENCES docentes(id) ON DELETE CASCADE,
    FOREIGN KEY (area_id) REFERENCES areas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- ASISTENCIAS
-- ============================================

CREATE TABLE IF NOT EXISTS asistencias (
    id INT PRIMARY KEY AUTO_INCREMENT,
    fecha DATE NOT NULL,
    hora TIME NOT NULL,
    descripcion ENUM('PRESENTE', 'TARDANZA', 'FALTA_JUSTIFICADA', 'FALTA_INJUSTIFICADA', 'TARDANZA_JUSTIFICADA') NOT NULL,
    programacion_id INT NOT NULL,
    estudiante_id INT NOT NULL,
    ano_lectivo_id INT NOT NULL,
    observaciones TEXT,
    justificacion_archivo VARCHAR(255),
    registrado_por INT NOT NULL,
    metodo_registro ENUM('MANUAL', 'QR', 'IMPORTACION') DEFAULT 'MANUAL',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (programacion_id) REFERENCES programaciones(id),
    FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id),
    FOREIGN KEY (ano_lectivo_id) REFERENCES anos_lectivos(id),
    FOREIGN KEY (registrado_por) REFERENCES usuarios(id),
    UNIQUE KEY unique_asistencia (fecha, programacion_id, estudiante_id),
    INDEX idx_fecha (fecha),
    INDEX idx_estudiante (estudiante_id),
    INDEX idx_programacion (programacion_id),
    INDEX idx_descripcion (descripcion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- INCIDENCIAS (CONDUCTA)
-- ============================================

-- Tabla: tipos de incidencia
CREATE TABLE IF NOT EXISTS tipos_incidencia (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(100) NOT NULL,
    categoria ENUM('DISCIPLINARIA', 'UNIFORME', 'CONVIVENCIA', 'ACADEMICA', 'OTRA') NOT NULL,
    gravedad ENUM('LEVE', 'MODERADA', 'GRAVE', 'MUY_GRAVE') NOT NULL,
    descripcion TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_categoria (categoria),
    INDEX idx_gravedad (gravedad)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: incidencias
CREATE TABLE IF NOT EXISTS incidencias (
    id INT PRIMARY KEY AUTO_INCREMENT,
    fecha DATE NOT NULL,
    hora TIME NOT NULL,
    programacion_id INT NOT NULL,
    estudiante_id INT NOT NULL,
    tipo_incidencia_id INT NOT NULL,
    descripcion TEXT NOT NULL,
    acciones_tomadas TEXT,
    compromiso_estudiante TEXT,
    compromiso_apoderado TEXT,
    foto VARCHAR(255),
    estado ENUM('ABIERTA', 'EN_SEGUIMIENTO', 'RESUELTA', 'CERRADA') DEFAULT 'ABIERTA',
    ano_lectivo_id INT NOT NULL,
    registrado_por INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (programacion_id) REFERENCES programaciones(id),
    FOREIGN KEY (estudiante_id) REFERENCES estudiantes(id),
    FOREIGN KEY (tipo_incidencia_id) REFERENCES tipos_incidencia(id),
    FOREIGN KEY (ano_lectivo_id) REFERENCES anos_lectivos(id),
    FOREIGN KEY (registrado_por) REFERENCES usuarios(id),
    INDEX idx_fecha (fecha),
    INDEX idx_estudiante (estudiante_id),
    INDEX idx_estado (estado),
    INDEX idx_tipo (tipo_incidencia_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: seguimientos de incidencias
CREATE TABLE IF NOT EXISTS seguimientos_incidencia (
    id INT PRIMARY KEY AUTO_INCREMENT,
    incidencia_id INT NOT NULL,
    fecha DATE NOT NULL,
    descripcion TEXT NOT NULL,
    responsable_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (incidencia_id) REFERENCES incidencias(id) ON DELETE CASCADE,
    FOREIGN KEY (responsable_id) REFERENCES usuarios(id),
    INDEX idx_incidencia (incidencia_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- NOTIFICACIONES
-- ============================================

CREATE TABLE IF NOT EXISTS notificaciones (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tipo ENUM('EMAIL', 'SMS', 'PUSH', 'SISTEMA') NOT NULL,
    destinatario_id INT NOT NULL,
    destinatario_tipo ENUM('APODERADO', 'DOCENTE', 'ESTUDIANTE', 'ADMIN') NOT NULL,
    asunto VARCHAR(255),
    mensaje TEXT NOT NULL,
    estado ENUM('PENDIENTE', 'ENVIADO', 'FALLIDO', 'LEIDO') DEFAULT 'PENDIENTE',
    referencia_tipo ENUM('ASISTENCIA', 'INCIDENCIA', 'GENERAL') NULL,
    referencia_id INT NULL,
    enviado_at TIMESTAMP NULL,
    leido_at TIMESTAMP NULL,
    error_mensaje TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_estado (estado),
    INDEX idx_destinatario (destinatario_id, destinatario_tipo),
    INDEX idx_tipo (tipo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- AUDITORÍA
-- ============================================

CREATE TABLE IF NOT EXISTS auditoria (
    id INT PRIMARY KEY AUTO_INCREMENT,
    usuario_id INT NOT NULL,
    accion VARCHAR(100) NOT NULL,
    tabla VARCHAR(100) NOT NULL,
    registro_id INT,
    datos_anteriores JSON,
    datos_nuevos JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id),
    INDEX idx_usuario (usuario_id),
    INDEX idx_fecha (created_at),
    INDEX idx_tabla (tabla)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- FINALIZAR
-- ============================================

SET FOREIGN_KEY_CHECKS = 1;

-- Mensaje de confirmación
SELECT 'Base de datos creada exitosamente' AS mensaje;
SELECT COUNT(*) AS total_tablas FROM information_schema.tables WHERE table_schema = DATABASE();
