SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS docente_horario_importacion (
    id_importacion INT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
    nivel_origen VARCHAR(10) NOT NULL,
    archivo_nombre VARCHAR(255) NOT NULL,
    total_registros INT UNSIGNED NOT NULL DEFAULT 0,
    total_insertados INT UNSIGNED NOT NULL DEFAULT 0,
    total_docentes_vinculados INT UNSIGNED NOT NULL DEFAULT 0,
    observacion TEXT NULL,
    importado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_importacion),
    KEY idx_dhi_anio_nivel (id_anio_lectivo, nivel_origen),
    CONSTRAINT fk_dhi_anio
        FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS docente_horario_clase (
    id_horario INT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_importacion INT UNSIGNED NULL,
    id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
    id_docente INT UNSIGNED NULL,
    id_aula INT UNSIGNED NULL,
    id_area INT UNSIGNED NULL,
    nivel_origen CHAR(3) NOT NULL,
    docente_nombre VARCHAR(180) NOT NULL,
    dni_docente VARCHAR(20) NULL,
    area_nombre VARCHAR(180) NULL,
    aula_texto VARCHAR(80) NULL,
    grado_texto VARCHAR(80) NULL,
    seccion_texto VARCHAR(30) NULL,
    dia_semana VARCHAR(15) NOT NULL,
    dia_orden TINYINT UNSIGNED NOT NULL,
    bloque_orden TINYINT UNSIGNED NOT NULL,
    hora_inicio TIME NULL,
    hora_fin TIME NULL,
    actividad VARCHAR(220) NOT NULL,
    turno VARCHAR(40) NULL,
    hoja_excel VARCHAR(120) NULL,
    fila_excel INT UNSIGNED NULL,
    columna_excel VARCHAR(8) NULL,
    fuente_archivo VARCHAR(255) NULL,
    origen_hash CHAR(40) NOT NULL,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id_horario),
    UNIQUE KEY uq_dhc_origen (id_anio_lectivo, origen_hash),
    KEY idx_dhc_docente (id_anio_lectivo, id_docente, dia_orden, bloque_orden),
    KEY idx_dhc_aula (id_anio_lectivo, id_aula, dia_orden, bloque_orden),
    KEY idx_dhc_area (id_area),
    KEY idx_dhc_fuente (id_anio_lectivo, fuente_archivo(120)),
    CONSTRAINT fk_dhc_importacion
        FOREIGN KEY (id_importacion) REFERENCES docente_horario_importacion(id_importacion)
        ON DELETE SET NULL,
    CONSTRAINT fk_dhc_anio
        FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo),
    CONSTRAINT fk_dhc_docente
        FOREIGN KEY (id_docente) REFERENCES docente(id_docente)
        ON DELETE SET NULL,
    CONSTRAINT fk_dhc_aula
        FOREIGN KEY (id_aula) REFERENCES aula(id_aula)
        ON DELETE SET NULL,
    CONSTRAINT fk_dhc_area
        FOREIGN KEY (id_area) REFERENCES area_curricular(id_area)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE OR REPLACE VIEW vw_docente_horario AS
SELECT
    h.id_horario,
    h.id_importacion,
    h.id_anio_lectivo,
    h.nivel_origen AS id_nivel,
    COALESCE(ne.descripcion, h.nivel_origen) AS nivel,
    h.id_docente,
    h.docente_nombre,
    h.dni_docente,
    COALESCE(NULLIF(p.nombre_completo, ''), TRIM(CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres)), h.docente_nombre) AS docente_vinculado,
    COALESCE(p.numero_documento, h.dni_docente) AS dni_vinculado,
    h.id_aula,
    h.grado_texto,
    h.seccion_texto,
    COALESCE(g.descripcion, h.grado_texto) AS grado,
    COALESCE(s.nombre_corto, h.seccion_texto) AS seccion,
    TRIM(CONCAT(COALESCE(g.descripcion, h.grado_texto, ''), ' ', COALESCE(s.nombre_corto, h.seccion_texto, ''))) AS aula_label,
    h.id_area,
    COALESCE(ar.nombre, h.area_nombre) AS area_vinculada,
    h.area_nombre,
    h.aula_texto,
    h.dia_semana,
    h.dia_orden,
    h.bloque_orden,
    h.hora_inicio,
    h.hora_fin,
    CONCAT(IFNULL(TIME_FORMAT(h.hora_inicio, '%H:%i'), ''), ' - ', IFNULL(TIME_FORMAT(h.hora_fin, '%H:%i'), '')) AS tramo_horario,
    h.actividad,
    h.turno,
    h.hoja_excel,
    h.fila_excel,
    h.columna_excel,
    h.fuente_archivo,
    h.estado,
    h.creado_en,
    h.actualizado_en
FROM docente_horario_clase h
LEFT JOIN nivel_educativo ne ON ne.id_nivel = h.nivel_origen
LEFT JOIN docente d ON d.id_docente = h.id_docente
LEFT JOIN persona p ON p.id_persona = d.id_persona
LEFT JOIN aula a ON a.id_aula = h.id_aula
LEFT JOIN grado g ON g.id_grado = a.id_grado AND g.id_nivel = a.id_nivel
LEFT JOIN seccion s ON s.id_seccion = a.id_seccion
LEFT JOIN area_curricular ar ON ar.id_area = h.id_area;