-- =====================================================================
-- 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`;

SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- 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;

-- ---------------------------------------------------------------------
-- 5. USUARIOS (admin / teacher / parent) — tabla única con rol
-- ---------------------------------------------------------------------
CREATE TABLE `users` (
  `id`            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `school_id`     BIGINT UNSIGNED NULL,          -- NULL para super_admin global
  `role`          ENUM('super_admin','school_admin','teacher','parent')
                    NOT NULL,
  `first_name`    VARCHAR(100)    NOT NULL,
  `last_name`     VARCHAR(100)    NOT NULL,
  `email`         VARCHAR(150)    NOT NULL,
  `phone`         VARCHAR(20)     NULL,
  `password_hash` VARCHAR(255)    NOT NULL,
  `is_active`     TINYINT(1)      NOT NULL DEFAULT 1,
  `last_login_at` DATETIME        NULL,
  `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                     ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_users_school` FOREIGN KEY (`school_id`)
    REFERENCES `schools`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `uq_users_email` (`email`),
  INDEX `idx_users_school_role` (`school_id`, `role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 6. ALUMNOS
-- ---------------------------------------------------------------------
CREATE TABLE `students` (
  `id`              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `school_id`       BIGINT UNSIGNED NOT NULL,
  `group_id`        BIGINT UNSIGNED NOT NULL,
  `control_number`  VARCHAR(30)     NULL,        -- matrícula visible (NO va en el QR)
  `first_name`      VARCHAR(100)    NOT NULL,
  `last_name`       VARCHAR(100)    NOT NULL,
  `qr_token`        CHAR(36)        NOT NULL,     -- UUID v4, esto sí va en el QR
  `qr_token_active` TINYINT(1)      NOT NULL DEFAULT 1,
  `photo_url`       VARCHAR(255)    NULL,
  `birth_date`      DATE            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,
  CONSTRAINT `fk_students_school` FOREIGN KEY (`school_id`)
    REFERENCES `schools`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_students_group` FOREIGN KEY (`group_id`)
    REFERENCES `school_groups`(`id`) ON DELETE RESTRICT,
  UNIQUE KEY `uq_students_qr_token` (`qr_token`),
  INDEX `idx_students_school_group` (`school_id`, `group_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;

-- ---------------------------------------------------------------------
-- 10. REGISTROS DE ASISTENCIA (cada escaneo)
-- ---------------------------------------------------------------------
CREATE TABLE `attendance_records` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id`  BIGINT UNSIGNED NOT NULL,
  `student_id`  BIGINT UNSIGNED NOT NULL,
  `status`      ENUM('present','late','absent','justified') NOT NULL DEFAULT 'present',
  `scanned_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `device_id`   VARCHAR(100)    NULL,            -- identificador del dispositivo móvil
  `synced_offline` TINYINT(1)   NOT NULL DEFAULT 0, -- 1 si vino de cola offline
  `notes`       VARCHAR(255)    NULL,
  `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_records_session` FOREIGN KEY (`session_id`)
    REFERENCES `attendance_sessions`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_records_student` FOREIGN KEY (`student_id`)
    REFERENCES `students`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `uq_session_student` (`session_id`, `student_id`),
  INDEX `idx_records_student_date` (`student_id`, `scanned_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 11. TOKENS PUSH (Firebase Cloud Messaging)
-- ---------------------------------------------------------------------
CREATE TABLE `push_tokens` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`     BIGINT UNSIGNED NOT NULL,        -- users.id con role='parent' (o teacher)
  `fcm_token`   VARCHAR(255)    NOT NULL,
  `platform`    ENUM('android','ios') NOT NULL,
  `device_label` VARCHAR(100)   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,
  CONSTRAINT `fk_push_user` FOREIGN KEY (`user_id`)
    REFERENCES `users`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `uq_push_token` (`fcm_token`),
  INDEX `idx_push_user` (`user_id`)
) 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;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- 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');

-- Contraseña de ejemplo: "Admin1234!" (hashear con password_hash() antes de usar en real)
INSERT INTO `users` (`school_id`, `role`, `first_name`, `last_name`, `email`, `password_hash`)
VALUES (1, 'school_admin', 'Admin', 'Escuela', 'admin@escuela-ejemplo.mx',
        '$2y$10$examplehashreplacewithrealbcrypthash');