-- ============================================================
-- Simulado Online — Database Schema (PRODUÇÃO)
-- MySQL / MariaDB
-- Sem CREATE DATABASE (hosting define o banco)
-- ============================================================

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- ============================================================
-- 1. USERS
-- ============================================================
CREATE TABLE IF NOT EXISTS `users` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name`           VARCHAR(120)  NOT NULL,
    `email`          VARCHAR(255)  NOT NULL,
    `password_hash`  VARCHAR(255)  NOT NULL,
    `role`           ENUM('user','admin') NOT NULL DEFAULT 'user',
    `avatar_url`     VARCHAR(500)  DEFAULT NULL,
    `active`         TINYINT(1)    NOT NULL DEFAULT 1,
    `reset_token`    VARCHAR(255)  DEFAULT NULL,
    `reset_expires`  DATETIME      DEFAULT NULL,
    `created_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_users_email` (`email`),
    INDEX `idx_users_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 2. COURSES
-- ============================================================
CREATE TABLE IF NOT EXISTS `courses` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `title`       VARCHAR(200)  NOT NULL,
    `description` TEXT          DEFAULT NULL,
    `image_url`   VARCHAR(500)  DEFAULT NULL,
    `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,
    INDEX `idx_courses_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. DISCIPLINES
-- ============================================================
CREATE TABLE IF NOT EXISTS `disciplines` (
    `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `course_id`  INT UNSIGNED  NOT NULL,
    `title`      VARCHAR(200)  NOT NULL,
    `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,
    INDEX `idx_disciplines_course` (`course_id`),
    CONSTRAINT `fk_disciplines_course`
        FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. EXAMS
-- ============================================================
CREATE TABLE IF NOT EXISTS `exams` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `course_id`        INT UNSIGNED  NOT NULL,
    `title`            VARCHAR(300)  NOT NULL,
    `description`      TEXT          DEFAULT NULL,
    `difficulty`       ENUM('facil','medio','dificil') NOT NULL DEFAULT 'medio',
    `min_passing_score` TINYINT UNSIGNED NOT NULL DEFAULT 70,
    `duration_minutes` INT UNSIGNED  NOT NULL DEFAULT 60,
    `max_dynamic_questions` INT UNSIGNED DEFAULT NULL,
    `random_order`     TINYINT(1)    NOT NULL DEFAULT 0,
    `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,
    INDEX `idx_exams_course` (`course_id`),
    INDEX `idx_exams_difficulty` (`difficulty`),
    CONSTRAINT `fk_exams_course`
        FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 5. QUESTIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `questions` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `discipline_id` INT UNSIGNED  NOT NULL,
    `statement`     TEXT          NOT NULL,
    `explanation`   TEXT          DEFAULT NULL,
    `video`         VARCHAR(150)  DEFAULT NULL,
    `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,
    INDEX `idx_questions_discipline` (`discipline_id`),
    CONSTRAINT `fk_questions_discipline`
        FOREIGN KEY (`discipline_id`) REFERENCES `disciplines`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. TAGS
-- ============================================================
CREATE TABLE IF NOT EXISTS `tags` (
    `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `course_id`  INT UNSIGNED  NOT NULL,
    `name`       VARCHAR(120)  NOT NULL,
    `slug`       VARCHAR(140)  NOT NULL,
    `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,
    UNIQUE KEY `uk_tags_course_slug` (`course_id`, `slug`),
    INDEX `idx_tags_course` (`course_id`),
    INDEX `idx_tags_name` (`name`),
    CONSTRAINT `fk_tags_course`
        FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. QUESTION_TAGS
-- ============================================================
CREATE TABLE IF NOT EXISTS `question_tags` (
    `question_id` INT UNSIGNED NOT NULL,
    `tag_id`      INT UNSIGNED NOT NULL,
    PRIMARY KEY (`question_id`, `tag_id`),
    INDEX `idx_question_tags_tag` (`tag_id`),
    CONSTRAINT `fk_question_tags_question`
        FOREIGN KEY (`question_id`) REFERENCES `questions`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_question_tags_tag`
        FOREIGN KEY (`tag_id`) REFERENCES `tags`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. EXAM_TAGS
-- ============================================================
CREATE TABLE IF NOT EXISTS `exam_tags` (
    `exam_id` INT UNSIGNED NOT NULL,
    `tag_id`  INT UNSIGNED NOT NULL,
    PRIMARY KEY (`exam_id`, `tag_id`),
    INDEX `idx_exam_tags_tag` (`tag_id`),
    CONSTRAINT `fk_exam_tags_exam`
        FOREIGN KEY (`exam_id`) REFERENCES `exams`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_exam_tags_tag`
        FOREIGN KEY (`tag_id`) REFERENCES `tags`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 9. ALTERNATIVES
-- ============================================================
CREATE TABLE IF NOT EXISTS `alternatives` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `question_id` INT UNSIGNED  NOT NULL,
    `letter`      CHAR(1)       NOT NULL,
    `content`     TEXT          NOT NULL,
    `is_correct`  TINYINT(1)    NOT NULL DEFAULT 0,
    INDEX `idx_alternatives_question` (`question_id`),
    CONSTRAINT `fk_alternatives_question`
        FOREIGN KEY (`question_id`) REFERENCES `questions`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 10. QUESTOES-PROVA (junction table)
-- ============================================================
CREATE TABLE IF NOT EXISTS `questoes-prova` (
    `exam_id`     INT UNSIGNED NOT NULL,
    `question_id` INT UNSIGNED NOT NULL,
    `position`    INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`exam_id`, `question_id`),
    INDEX `idx_qp_exam` (`exam_id`),
    INDEX `idx_qp_question` (`question_id`),
    CONSTRAINT `fk_qp_exam`
        FOREIGN KEY (`exam_id`) REFERENCES `exams`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_qp_question`
        FOREIGN KEY (`question_id`) REFERENCES `questions`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 11. SIMULADO_SESSIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS `simulado_sessions` (
    `id`                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`              INT UNSIGNED  NOT NULL,
    `exam_id`              INT UNSIGNED  NOT NULL,
    `status`               ENUM('in_progress','completed','abandoned') NOT NULL DEFAULT 'in_progress',
    `time_spent_seconds`   INT UNSIGNED  NOT NULL DEFAULT 0,
    `bookmarked_questions` JSON          DEFAULT NULL,
    `question_order`       JSON          DEFAULT NULL,
    `started_at`           DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `finished_at`          DATETIME      DEFAULT NULL,
    INDEX `idx_sessions_user` (`user_id`),
    INDEX `idx_sessions_exam` (`exam_id`),
    INDEX `idx_sessions_status` (`status`),
    CONSTRAINT `fk_sessions_user`
        FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_sessions_exam`
        FOREIGN KEY (`exam_id`) REFERENCES `exams`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 12. USER_ANSWERS
-- ============================================================
CREATE TABLE IF NOT EXISTS `user_answers` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `session_id`     INT UNSIGNED NOT NULL,
    `question_id`    INT UNSIGNED NOT NULL,
    `alternative_id` INT UNSIGNED DEFAULT NULL,
    `is_correct`     TINYINT(1)   DEFAULT NULL,
    `answered_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_answer_session_question` (`session_id`, `question_id`),
    INDEX `idx_answers_session` (`session_id`),
    CONSTRAINT `fk_answers_session`
        FOREIGN KEY (`session_id`) REFERENCES `simulado_sessions`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_answers_question`
        FOREIGN KEY (`question_id`) REFERENCES `questions`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_answers_alternative`
        FOREIGN KEY (`alternative_id`) REFERENCES `alternatives`(`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 13. SIMULADO_RESULTS
-- ============================================================
CREATE TABLE IF NOT EXISTS `simulado_results` (
    `id`                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `session_id`            INT UNSIGNED  NOT NULL,
    `total_questions`       INT UNSIGNED  NOT NULL DEFAULT 0,
    `correct_answers`       INT UNSIGNED  NOT NULL DEFAULT 0,
    `score`                 DECIMAL(5,2)  NOT NULL DEFAULT 0.00,
    `percentage`            DECIMAL(5,2)  NOT NULL DEFAULT 0.00,
    `time_spent_seconds`    INT UNSIGNED  NOT NULL DEFAULT 0,
    `details_by_discipline` JSON          DEFAULT NULL,
    `created_at`            DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_result_session` (`session_id`),
    CONSTRAINT `fk_results_session`
        FOREIGN KEY (`session_id`) REFERENCES `simulado_sessions`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 14. ACCESS_LOGS
-- ============================================================
CREATE TABLE IF NOT EXISTS `access_logs` (
    `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`    INT UNSIGNED  DEFAULT NULL,
    `action`     VARCHAR(100)  NOT NULL,
    `ip_address` VARCHAR(45)   DEFAULT NULL,
    `user_agent` TEXT          DEFAULT NULL,
    `details`    JSON          DEFAULT NULL,
    `created_at` DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_logs_user` (`user_id`),
    INDEX `idx_logs_action` (`action`),
    INDEX `idx_logs_date` (`created_at`),
    CONSTRAINT `fk_logs_user`
        FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 15. PASSWORD_RESETS (auxiliary)
-- ============================================================
CREATE TABLE IF NOT EXISTS `password_resets` (
    `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `email`      VARCHAR(255)  NOT NULL,
    `token`      VARCHAR(255)  NOT NULL,
    `expires_at` DATETIME      NOT NULL,
    `used`       TINYINT(1)    NOT NULL DEFAULT 0,
    `created_at` DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_resets_email` (`email`),
    INDEX `idx_resets_token` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Admin user padrão (senha: Admin@2026)
-- ============================================================
INSERT INTO `users` (`name`, `email`, `password_hash`, `role`)
VALUES ('Administrador', 'admin@cursosmanaus.com.br', '$2y$10$placeholder', 'admin')
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);