-- =====================================================================
-- Sistema de Control de Asistencia Escolar por QR
-- Script de creación de base de datos (MySQL 8.x)
-- Motor: InnoDB | Charset: utf8mb4 | Collation: utf8mb4_unicode_ci
-- =====================================================================

CREATE DATABASE IF NOT EXISTS `qr_asistencia`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE `qr_asistencia`;


-- ---------------------------------------------------------------------
-- 1. ESCUELAS
-- ---------------------------------------------------------------------
CREATE TABLE `schools` (
  `id`               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`             VARCHAR(150)      NOT NULL,
  `short_name`       VARCHAR(50)       NULL,
  `address`          VARCHAR(255)      NULL,
  `timezone`         VARCHAR(50)       NOT NULL DEFAULT 'America/Monterrey',
  `entry_time`       TIME              NOT NULL DEFAULT '08:00:00',
  `late_tolerance_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 10,
  `logo_url`         VARCHAR(255)      NULL,
  `is_active`        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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 2. CICLOS ESCOLARES
-- ---------------------------------------------------------------------
CREATE TABLE `school_terms` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `school_id`   BIGINT UNSIGNED NOT NULL,
  `name`        VARCHAR(50)  NOT NULL,          -- Ej: "2026-2027"
  `start_date`  DATE         NOT NULL,
  `end_date`    DATE         NOT NULL,
  `is_current`  TINYINT(1)   NOT NULL DEFAULT 0,
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_terms_school` FOREIGN KEY (`school_id`)
    REFERENCES `schools`(`id`) ON DELETE CASCADE,
  INDEX `idx_terms_school` (`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 3. GRADOS
-- ---------------------------------------------------------------------
CREATE TABLE `grades` (
  `id`         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `school_id`  BIGINT UNSIGNED NOT NULL,
  `name`       VARCHAR(50)     NOT NULL,        -- Ej: "1er grado"
  `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  CONSTRAINT `fk_grades_school` FOREIGN KEY (`school_id`)
    REFERENCES `schools`(`id`) ON DELETE CASCADE,
  INDEX `idx_grades_school` (`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 4. GRUPOS
-- ---------------------------------------------------------------------
CREATE TABLE `school_groups` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `school_id`   BIGINT UNSIGNED NOT NULL,
  `grade_id`    BIGINT UNSIGNED NOT NULL,
  `term_id`     BIGINT UNSIGNED NOT NULL,
  `name`        VARCHAR(20)     NOT NULL,        -- Ej: "A", "B"
  `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_groups_school` FOREIGN KEY (`school_id`)
    REFERENCES `schools`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_groups_grade` FOREIGN KEY (`grade_id`)
    REFERENCES `grades`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_groups_term` FOREIGN KEY (`term_id`)
    REFERENCES `school_terms`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `uq_group_unique` (`grade_id`, `term_id`, `name`),
  INDEX `idx_groups_school` (`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 7. RELACIÓN PADRE-ALUMNO (N:N)
-- ---------------------------------------------------------------------
CREATE TABLE `parent_student` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `parent_id`   BIGINT UNSIGNED NOT NULL,        -- users.id con role='parent'
  `student_id`  BIGINT UNSIGNED NOT NULL,
  `relationship` VARCHAR(30)    NULL,             -- "Madre", "Padre", "Tutor"
  `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_ps_parent` FOREIGN KEY (`parent_id`)
    REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ps_student` FOREIGN KEY (`student_id`)
    REFERENCES `students`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `uq_parent_student` (`parent_id`, `student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 8. ASIGNACIÓN MAESTRO-GRUPO-MATERIA
-- ---------------------------------------------------------------------
CREATE TABLE `subjects` (
  `id`         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `school_id`  BIGINT UNSIGNED NOT NULL,
  `name`       VARCHAR(100)    NOT NULL,
  CONSTRAINT `fk_subjects_school` FOREIGN KEY (`school_id`)
    REFERENCES `schools`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `teacher_group_subject` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `teacher_id`  BIGINT UNSIGNED NOT NULL,        -- users.id con role='teacher'
  `group_id`    BIGINT UNSIGNED NOT NULL,
  `subject_id`  BIGINT UNSIGNED NULL,            -- NULL si es asistencia general (tutor de grupo)
  `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_tgs_teacher` FOREIGN KEY (`teacher_id`)
    REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tgs_group` FOREIGN KEY (`group_id`)
    REFERENCES `school_groups`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tgs_subject` FOREIGN KEY (`subject_id`)
    REFERENCES `subjects`(`id`) ON DELETE SET NULL,
  UNIQUE KEY `uq_teacher_group_subject` (`teacher_id`, `group_id`, `subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 9. SESIONES DE PASE DE LISTA
-- ---------------------------------------------------------------------
CREATE TABLE `attendance_sessions` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `school_id`   BIGINT UNSIGNED NOT NULL,
  `group_id`    BIGINT UNSIGNED NOT NULL,
  `teacher_id`  BIGINT UNSIGNED NOT NULL,
  `subject_id`  BIGINT UNSIGNED NULL,
  `session_date` DATE           NOT NULL,
  `opened_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `closed_at`   DATETIME        NULL,
  `status`      ENUM('open','closed') NOT NULL DEFAULT 'open',
  CONSTRAINT `fk_sessions_school` FOREIGN KEY (`school_id`)
    REFERENCES `schools`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sessions_group` FOREIGN KEY (`group_id`)
    REFERENCES `school_groups`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sessions_teacher` FOREIGN KEY (`teacher_id`)
    REFERENCES `users`(`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_sessions_subject` FOREIGN KEY (`subject_id`)
    REFERENCES `subjects`(`id`) ON DELETE SET NULL,
  INDEX `idx_sessions_group_date` (`group_id`, `session_date`),
  INDEX `idx_sessions_school_date` (`school_id`, `session_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 12. BITÁCORA DE AUDITORÍA
-- ---------------------------------------------------------------------
CREATE TABLE `audit_log` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`     BIGINT UNSIGNED NULL,
  `action`      VARCHAR(100)    NOT NULL,        -- Ej: "scan.duplicate_attempt"
  `entity_type` VARCHAR(50)     NULL,
  `entity_id`   BIGINT UNSIGNED NULL,
  `ip_address`  VARCHAR(45)     NULL,
  `metadata`    JSON            NULL,
  `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`)
    REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_audit_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =====================================================================
-- DATOS DE PRUEBA (opcional, útil para desarrollo local)
-- =====================================================================
INSERT INTO `schools` (`name`, `short_name`, `entry_time`, `late_tolerance_minutes`)
VALUES ('Escuela Primaria Ejemplo', 'EPE', '08:00:00', 10);

INSERT INTO `school_terms` (`school_id`, `name`, `start_date`, `end_date`, `is_current`)
VALUES (1, '2026-2027', '2026-08-24', '2027-07-10', 1);

INSERT INTO `grades` (`school_id`, `name`, `sort_order`) VALUES
  (1, '1er grado', 1), (1, '2do grado', 2);

INSERT INTO `school_groups` (`school_id`, `grade_id`, `term_id`, `name`) VALUES
  (1, 1, 1, 'A'), (1, 2, 1, 'A');
