schema.sql

-- ============================================================
--  Portal de Control Escolar
--  Base de datos MySQL — esquema completo v1
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
DROP DATABASE IF EXISTS portal_escolar;
CREATE DATABASE portal_escolar CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE portal_escolar;

-- ------------------------------------------------------------
--  NIVEL ACADÉMICO  (secundaria, prepa, licenciatura)
-- ------------------------------------------------------------
CREATE TABLE nivel_academico (
    id_nivel      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre        VARCHAR(60)  NOT NULL,
    activo        TINYINT(1)   NOT NULL DEFAULT 1,
    UNIQUE KEY uq_nivel_nombre (nombre)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  PROGRAMA  (carrera / grado)
-- ------------------------------------------------------------
CREATE TABLE programa (
    id_programa   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_nivel      INT UNSIGNED NOT NULL,
    nombre        VARCHAR(120) NOT NULL,
    clave         VARCHAR(20)  NOT NULL,
    cuota_semanal DECIMAL(8,2) NOT NULL,
    activo        TINYINT(1)   NOT NULL DEFAULT 1,
    UNIQUE KEY uq_programa_clave (clave),
    CONSTRAINT fk_programa_nivel FOREIGN KEY (id_nivel)
        REFERENCES nivel_academico(id_nivel) ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  MATERIA
-- ------------------------------------------------------------
CREATE TABLE materia (
    id_materia    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_programa   INT UNSIGNED NOT NULL,
    nombre        VARCHAR(120) NOT NULL,
    clave         VARCHAR(20)  NOT NULL,
    creditos      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    activa        TINYINT(1)   NOT NULL DEFAULT 1,
    UNIQUE KEY uq_materia_clave (clave),
    CONSTRAINT fk_materia_programa FOREIGN KEY (id_programa)
        REFERENCES programa(id_programa) ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  PERIODO  (cuatrimestre / ciclo escolar)
-- ------------------------------------------------------------
CREATE TABLE periodo (
    id_periodo    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre        VARCHAR(80)  NOT NULL,
    fecha_inicio  DATE         NOT NULL,
    fecha_fin     DATE         NOT NULL,
    activo        TINYINT(1)   NOT NULL DEFAULT 1,
    CHECK (fecha_fin > fecha_inicio)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  USUARIO  (base para los tres roles)
-- ------------------------------------------------------------
CREATE TABLE usuario (
    id_usuario        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre            VARCHAR(80)  NOT NULL,
    apellido_paterno  VARCHAR(80)  NOT NULL,
    apellido_materno  VARCHAR(80)  NOT NULL DEFAULT '',
    email             VARCHAR(160) NOT NULL,
    password_hash     VARCHAR(255) NOT NULL,
    rol               ENUM('administrador','maestro','alumno') NOT NULL,
    activo            TINYINT(1)   NOT NULL DEFAULT 1,
    created_at        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_usuario_email (email)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  ALUMNO  (extiende usuario)
-- ------------------------------------------------------------
CREATE TABLE alumno (
    id_alumno         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_usuario        INT UNSIGNED NOT NULL,
    id_programa       INT UNSIGNED NOT NULL,
    matricula         VARCHAR(20)  NOT NULL,
    fecha_ingreso     DATE         NOT NULL,
    fecha_egreso      DATE         DEFAULT NULL,
    estado            ENUM('activo','baja','egresado','suspendido') NOT NULL DEFAULT 'activo',
    -- Contacto
    telefono          VARCHAR(20)  DEFAULT NULL,
    celular           VARCHAR(20)  DEFAULT NULL,
    -- Domicilio
    calle             VARCHAR(160) DEFAULT NULL,
    num_exterior      VARCHAR(10)  DEFAULT NULL,
    num_interior      VARCHAR(10)  DEFAULT NULL,
    colonia           VARCHAR(100) DEFAULT NULL,
    ciudad            VARCHAR(100) DEFAULT NULL,
    estado_domicilio  VARCHAR(80)  DEFAULT NULL,
    cp                VARCHAR(10)  DEFAULT NULL,
    UNIQUE KEY uq_alumno_matricula (matricula),
    UNIQUE KEY uq_alumno_usuario (id_usuario),
    CONSTRAINT fk_alumno_usuario  FOREIGN KEY (id_usuario)
        REFERENCES usuario(id_usuario) ON UPDATE CASCADE,
    CONSTRAINT fk_alumno_programa FOREIGN KEY (id_programa)
        REFERENCES programa(id_programa) ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  TUTOR
-- ------------------------------------------------------------
CREATE TABLE tutor (
    id_tutor          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_alumno         INT UNSIGNED NOT NULL,
    nombre            VARCHAR(80)  NOT NULL,
    apellido_paterno  VARCHAR(80)  NOT NULL,
    apellido_materno  VARCHAR(80)  NOT NULL DEFAULT '',
    parentesco        VARCHAR(40)  NOT NULL,
    telefono          VARCHAR(20)  DEFAULT NULL,
    celular           VARCHAR(20)  DEFAULT NULL,
    email             VARCHAR(160) DEFAULT NULL,
    es_contacto_principal TINYINT(1) NOT NULL DEFAULT 0,
    CONSTRAINT fk_tutor_alumno FOREIGN KEY (id_alumno)
        REFERENCES alumno(id_alumno) ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX idx_tutor_alumno (id_alumno)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  PROFESOR  (extiende usuario)
-- ------------------------------------------------------------
CREATE TABLE profesor (
    id_profesor   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_usuario    INT UNSIGNED NOT NULL,
    num_empleado  VARCHAR(20)  NOT NULL,
    UNIQUE KEY uq_profesor_empleado (num_empleado),
    UNIQUE KEY uq_profesor_usuario (id_usuario),
    CONSTRAINT fk_profesor_usuario FOREIGN KEY (id_usuario)
        REFERENCES usuario(id_usuario) ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  PROFESOR_NIVEL  (pivote: un profesor puede dar en varios niveles)
-- ------------------------------------------------------------
CREATE TABLE profesor_nivel (
    id_profesor   INT UNSIGNED NOT NULL,
    id_nivel      INT UNSIGNED NOT NULL,
    PRIMARY KEY (id_profesor, id_nivel),
    CONSTRAINT fk_pnivel_profesor FOREIGN KEY (id_profesor)
        REFERENCES profesor(id_profesor) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_pnivel_nivel    FOREIGN KEY (id_nivel)
        REFERENCES nivel_academico(id_nivel) ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  GRUPO
-- ------------------------------------------------------------
CREATE TABLE grupo (
    id_grupo      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_materia    INT UNSIGNED NOT NULL,
    id_periodo    INT UNSIGNED NOT NULL,
    id_profesor   INT UNSIGNED NOT NULL,
    clave_grupo   VARCHAR(20)  NOT NULL,
    cupo_max      TINYINT UNSIGNED NOT NULL DEFAULT 30,
    UNIQUE KEY uq_grupo_clave_periodo (clave_grupo, id_periodo),
    CONSTRAINT fk_grupo_materia  FOREIGN KEY (id_materia)
        REFERENCES materia(id_materia) ON UPDATE CASCADE,
    CONSTRAINT fk_grupo_periodo  FOREIGN KEY (id_periodo)
        REFERENCES periodo(id_periodo) ON UPDATE CASCADE,
    CONSTRAINT fk_grupo_profesor FOREIGN KEY (id_profesor)
        REFERENCES profesor(id_profesor) ON UPDATE CASCADE,
    INDEX idx_grupo_periodo  (id_periodo),
    INDEX idx_grupo_profesor (id_profesor)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  HORARIO
-- ------------------------------------------------------------
CREATE TABLE horario (
    id_horario    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_grupo      INT UNSIGNED NOT NULL,
    dia_semana    TINYINT UNSIGNED NOT NULL COMMENT '1=Lun … 6=Sab',
    hora_inicio   TIME NOT NULL,
    hora_fin      TIME NOT NULL,
    aula          VARCHAR(40)  DEFAULT NULL,
    CHECK (hora_fin > hora_inicio),
    CHECK (dia_semana BETWEEN 1 AND 6),
    CONSTRAINT fk_horario_grupo FOREIGN KEY (id_grupo)
        REFERENCES grupo(id_grupo) ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX idx_horario_grupo (id_grupo)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  INSCRIPCIÓN
-- ------------------------------------------------------------
CREATE TABLE inscripcion (
    id_inscripcion    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_alumno         INT UNSIGNED NOT NULL,
    id_grupo          INT UNSIGNED NOT NULL,
    fecha_inscripcion DATE         NOT NULL DEFAULT (CURRENT_DATE),
    estado            ENUM('activa','baja','finalizada') NOT NULL DEFAULT 'activa',
    fecha_baja        DATE         DEFAULT NULL,
    motivo_baja       TEXT         DEFAULT NULL,
    UNIQUE KEY uq_inscripcion (id_alumno, id_grupo),
    CONSTRAINT fk_inscripcion_alumno FOREIGN KEY (id_alumno)
        REFERENCES alumno(id_alumno) ON UPDATE CASCADE,
    CONSTRAINT fk_inscripcion_grupo  FOREIGN KEY (id_grupo)
        REFERENCES grupo(id_grupo)   ON UPDATE CASCADE,
    INDEX idx_inscripcion_grupo (id_grupo)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  CALIFICACIÓN
-- ------------------------------------------------------------
CREATE TABLE calificacion (
    id_calificacion   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_inscripcion    INT UNSIGNED NOT NULL,
    id_profesor       INT UNSIGNED NOT NULL,
    calificacion      DECIMAL(5,2) NOT NULL,
    tipo_evaluacion   ENUM('parcial_1','parcial_2','parcial_3','final','extraordinario') NOT NULL,
    observaciones     VARCHAR(255) DEFAULT NULL,
    registrada_en     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (calificacion BETWEEN 0 AND 10),
    UNIQUE KEY uq_calif (id_inscripcion, tipo_evaluacion),
    CONSTRAINT fk_calif_inscripcion FOREIGN KEY (id_inscripcion)
        REFERENCES inscripcion(id_inscripcion) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_calif_profesor    FOREIGN KEY (id_profesor)
        REFERENCES profesor(id_profesor) ON UPDATE CASCADE,
    INDEX idx_calif_inscripcion (id_inscripcion)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  CONCEPTO DE PAGO  (catálogo)
-- ------------------------------------------------------------
CREATE TABLE concepto_pago (
    id_concepto   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre        VARCHAR(80)  NOT NULL,
    tipo          ENUM('inscripcion','semanal','cuatrimestral') NOT NULL,
    monto_base    DECIMAL(8,2) NOT NULL,
    activo        TINYINT(1)   NOT NULL DEFAULT 1
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  PAGO
-- ------------------------------------------------------------
CREATE TABLE pago (
    id_pago               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_alumno             INT UNSIGNED NOT NULL,
    id_concepto           INT UNSIGNED NOT NULL,
    id_periodo            INT UNSIGNED DEFAULT NULL,
    semana_correspondiente DATE        DEFAULT NULL COMMENT 'Lunes de la semana que cubre',
    monto                 DECIMAL(8,2) NOT NULL,
    fecha_limite          DATE         NOT NULL,
    fecha_pago            DATE         DEFAULT NULL,
    estado                ENUM('pendiente','pagado','vencido') NOT NULL DEFAULT 'pendiente',
    referencia            VARCHAR(80)  DEFAULT NULL,
    generado_automatico   TINYINT(1)   NOT NULL DEFAULT 0,
    created_at            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pago_alumno   FOREIGN KEY (id_alumno)
        REFERENCES alumno(id_alumno) ON UPDATE CASCADE,
    CONSTRAINT fk_pago_concepto FOREIGN KEY (id_concepto)
        REFERENCES concepto_pago(id_concepto) ON UPDATE CASCADE,
    CONSTRAINT fk_pago_periodo  FOREIGN KEY (id_periodo)
        REFERENCES periodo(id_periodo) ON UPDATE CASCADE,
    INDEX idx_pago_alumno  (id_alumno),
    INDEX idx_pago_estado  (estado),
    INDEX idx_pago_semana  (semana_correspondiente)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  MULTA
-- ------------------------------------------------------------
CREATE TABLE multa (
    id_multa        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_pago         INT UNSIGNED NOT NULL,
    monto           DECIMAL(8,2) NOT NULL DEFAULT 20.00,
    fecha_generada  DATE         NOT NULL DEFAULT (CURRENT_DATE),
    fecha_pago      DATE         DEFAULT NULL,
    estado          ENUM('pendiente','pagada','condonada') NOT NULL DEFAULT 'pendiente',
    CONSTRAINT fk_multa_pago FOREIGN KEY (id_pago)
        REFERENCES pago(id_pago) ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX idx_multa_pago (id_pago)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  ANUNCIO
-- ------------------------------------------------------------
CREATE TABLE anuncio (
    id_anuncio    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_usuario    INT UNSIGNED NOT NULL,
    titulo        VARCHAR(160) NOT NULL,
    contenido     TEXT         NOT NULL,
    publicado_en  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    activo        TINYINT(1)   NOT NULL DEFAULT 1,
    CONSTRAINT fk_anuncio_usuario FOREIGN KEY (id_usuario)
        REFERENCES usuario(id_usuario) ON UPDATE CASCADE,
    INDEX idx_anuncio_fecha (publicado_en)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
--  MATERIAL DIDÁCTICO
-- ------------------------------------------------------------
CREATE TABLE material (
    id_material   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_grupo      INT UNSIGNED NOT NULL,
    id_profesor   INT UNSIGNED NOT NULL,
    titulo        VARCHAR(160) NOT NULL,
    descripcion   VARCHAR(255) DEFAULT NULL,
    ruta_archivo  VARCHAR(255) NOT NULL,
    tipo_archivo  VARCHAR(20)  NOT NULL COMMENT 'pdf, pptx, docx, mp4, etc.',
    subido_en     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_material_grupo    FOREIGN KEY (id_grupo)
        REFERENCES grupo(id_grupo)   ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_material_profesor FOREIGN KEY (id_profesor)
        REFERENCES profesor(id_profesor) ON UPDATE CASCADE,
    INDEX idx_material_grupo (id_grupo)
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
--  DATOS CATÁLOGO INICIALES
-- ============================================================

INSERT INTO nivel_academico (nombre) VALUES
    ('Secundaria'),
    ('Preparatoria'),
    ('Licenciatura');

INSERT INTO programa (id_nivel, nombre, clave, cuota_semanal) VALUES
    (1, 'Secundaria General',          'SEC-GEN',  350.00),
    (2, 'Preparatoria General',        'PREP-GEN', 400.00),
    (3, 'Ingeniería en Sistemas',      'LIC-ISC',  500.00),
    (3, 'Administración de Empresas',  'LIC-LAE',  500.00),
    (3, 'Contaduría Pública',          'LIC-CP',   500.00);

INSERT INTO concepto_pago (nombre, tipo, monto_base) VALUES
    ('Inscripción',          'inscripcion',    500.00),
    ('Colegiatura semanal',  'semanal',          0.00),  -- monto se toma de programa.cuota_semanal
    ('Cuota cuatrimestral',  'cuatrimestral',  500.00);

-- Usuario administrador por defecto  (password: Admin1234!)
-- Hash generado con werkzeug pbkdf2:sha256
INSERT INTO usuario (nombre, apellido_paterno, apellido_materno, email, password_hash, rol) VALUES
    ('Administrador', 'Sistema', '', 'admin@escuela.edu.mx',
     'pbkdf2:sha256:600000$placeholder$changeme', 'administrador');