-- Sistema de Inscripción a Talleres de Oficio - DIF Toluca
-- Etapa 2: esquema inicial
-- Ejecutar manualmente desde phpMyAdmin.

CREATE DATABASE IF NOT EXISTS dif_talleres
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE dif_talleres;

CREATE TABLE periodos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(150) NOT NULL,
    fecha_inicio DATE NOT NULL,
    fecha_fin DATE NOT NULL,
    estado ENUM('ACTIVO', 'INACTIVO', 'CERRADO') NOT NULL DEFAULT 'INACTIVO',
    periodo_activo_unico TINYINT GENERATED ALWAYS AS (
        CASE WHEN estado = 'ACTIVO' THEN 1 ELSE NULL END
    ) STORED,
    fecha_creacion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_periodo_activo_unico UNIQUE (periodo_activo_unico),
    CONSTRAINT chk_periodo_fechas CHECK (fecha_fin >= fecha_inicio)
) ENGINE=InnoDB;

CREATE TABLE talleres (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(150) NOT NULL,
    descripcion TEXT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_taller_nombre UNIQUE (nombre)
) ENGINE=InnoDB;

CREATE TABLE centros_salud (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(180) NOT NULL,
    direccion VARCHAR(255) NOT NULL,
    delegacion VARCHAR(150) NOT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_centro_nombre UNIQUE (nombre)
) ENGINE=InnoDB;

CREATE TABLE talleres_centros (
    taller_id INT UNSIGNED NOT NULL,
    centro_id INT UNSIGNED NOT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (taller_id, centro_id),
    CONSTRAINT fk_tc_taller FOREIGN KEY (taller_id) REFERENCES talleres(id),
    CONSTRAINT fk_tc_centro FOREIGN KEY (centro_id) REFERENCES centros_salud(id)
) ENGINE=InnoDB;

CREATE TABLE ofertas_talleres (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    periodo_id INT UNSIGNED NOT NULL,
    taller_id INT UNSIGNED NOT NULL,
    centro_id INT UNSIGNED NOT NULL,
    cupo_maximo INT UNSIGNED NULL,
    estado ENUM('ACTIVA', 'INACTIVA', 'CERRADA') NOT NULL DEFAULT 'ACTIVA',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_oferta_periodo_taller_centro UNIQUE (periodo_id, taller_id, centro_id),
    CONSTRAINT fk_oferta_periodo FOREIGN KEY (periodo_id) REFERENCES periodos(id),
    CONSTRAINT fk_oferta_taller FOREIGN KEY (taller_id) REFERENCES talleres(id),
    CONSTRAINT fk_oferta_centro FOREIGN KEY (centro_id) REFERENCES centros_salud(id),
    CONSTRAINT fk_oferta_relacion FOREIGN KEY (taller_id, centro_id)
        REFERENCES talleres_centros(taller_id, centro_id)
) ENGINE=InnoDB;

CREATE TABLE alumnos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombres VARCHAR(120) NOT NULL,
    apellido_paterno VARCHAR(80) NOT NULL,
    apellido_materno VARCHAR(80) NULL,
    curp CHAR(18) NOT NULL,
    fecha_nacimiento DATE NOT NULL,
    calle VARCHAR(180) NOT NULL,
    numero_exterior VARCHAR(30) NOT NULL,
    numero_interior VARCHAR(30) NULL,
    colonia VARCHAR(150) NOT NULL,
    delegacion VARCHAR(150) NOT NULL,
    municipio VARCHAR(100) NOT NULL DEFAULT 'Toluca',
    codigo_postal CHAR(5) NOT NULL,
    telefono_celular CHAR(10) NULL,
    telefono_casa CHAR(10) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_alumno_curp UNIQUE (curp),
    CONSTRAINT chk_alumno_telefono CHECK (telefono_celular IS NOT NULL OR telefono_casa IS NOT NULL)
) ENGINE=InnoDB;

CREATE TABLE folios (
    anio SMALLINT UNSIGNED PRIMARY KEY,
    ultimo_consecutivo INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE inscripciones (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    folio VARCHAR(30) NOT NULL,
    alumno_id INT UNSIGNED NOT NULL,
    oferta_id INT UNSIGNED NOT NULL,
    fecha_inscripcion DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    tipo_inscripcion ENUM('SISTEMA', 'PRESENCIAL') NOT NULL DEFAULT 'SISTEMA',
    estado ENUM('ACTIVA', 'CANCELADA', 'CONCLUIDA') NOT NULL DEFAULT 'ACTIVA',
    acepto_aviso_residencia TINYINT(1) NOT NULL,
    fecha_aceptacion_aviso DATETIME NULL,
    clave_inscripcion_activa VARCHAR(100) GENERATED ALWAYS AS (
        CASE WHEN estado = 'ACTIVA' THEN CONCAT(alumno_id, ':', oferta_id) ELSE NULL END
    ) STORED,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_inscripcion_folio UNIQUE (folio),
    CONSTRAINT uq_inscripcion_activa UNIQUE (clave_inscripcion_activa),
    CONSTRAINT fk_inscripcion_alumno FOREIGN KEY (alumno_id) REFERENCES alumnos(id),
    CONSTRAINT fk_inscripcion_oferta FOREIGN KEY (oferta_id) REFERENCES ofertas_talleres(id),
    CONSTRAINT chk_aviso_aceptado CHECK (acepto_aviso_residencia = 1)
) ENGINE=InnoDB;

CREATE TABLE responsables (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    alumno_id INT UNSIGNED NOT NULL,
    nombres VARCHAR(120) NOT NULL,
    apellido_paterno VARCHAR(80) NOT NULL,
    apellido_materno VARCHAR(80) NULL,
    parentesco VARCHAR(80) NOT NULL,
    telefono CHAR(10) NOT NULL,
    telefono_alterno CHAR(10) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_responsable_alumno FOREIGN KEY (alumno_id) REFERENCES alumnos(id)
) ENGINE=InnoDB;

CREATE TABLE expedientes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    inscripcion_id BIGINT UNSIGNED NOT NULL,
    semestre VARCHAR(80) NULL,
    fotografia VARCHAR(255) NULL,
    turno VARCHAR(50) NULL,
    nivel ENUM('1', '2') NULL,
    instructor VARCHAR(150) NULL,
    diagnostico_socioeconomico VARCHAR(150) NULL,
    contacto_emergencia VARCHAR(180) NULL,
    parentesco_emergencia VARCHAR(80) NULL,
    telefono_emergencia CHAR(10) NULL,
    observaciones TEXT NULL,
    firma_instructor VARCHAR(255) NULL,
    firma_alumno_responsable VARCHAR(255) NULL,
    visto_bueno_supervisor VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_expediente_inscripcion UNIQUE (inscripcion_id),
    CONSTRAINT fk_expediente_inscripcion FOREIGN KEY (inscripcion_id) REFERENCES inscripciones(id)
) ENGINE=InnoDB;

CREATE TABLE documentos_entregados (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    expediente_id BIGINT UNSIGNED NOT NULL,
    tipo_documento VARCHAR(100) NOT NULL,
    entregado TINYINT(1) NOT NULL DEFAULT 0,
    observaciones VARCHAR(255) NULL,
    fecha_entrega DATE NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_documento_expediente FOREIGN KEY (expediente_id) REFERENCES expedientes(id)
) ENGINE=InnoDB;

CREATE TABLE usuarios (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(150) NOT NULL,
    correo VARCHAR(180) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    rol VARCHAR(50) NOT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    ultimo_acceso DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_usuario_correo UNIQUE (correo)
) ENGINE=InnoDB;

CREATE TABLE bitacora (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT UNSIGNED NULL,
    accion VARCHAR(80) NOT NULL,
    tabla_afectada VARCHAR(80) NULL,
    registro_id BIGINT UNSIGNED NULL,
    descripcion TEXT NULL,
    ip VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_bitacora_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB;
