-- Fix missing tables for aldudu-academy
-- Created: 2026-03-28

-- Create schools table
CREATE TABLE IF NOT EXISTS `schools` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(200) NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `address` TEXT NULL,
    `phone` VARCHAR(20) NULL,
    `email` VARCHAR(100) NOT NULL,
    `admin_email` VARCHAR(100) NOT NULL,
    `status` ENUM('PENDING', 'VERIFIED', 'ACTIVE', 'SUSPENDED') NOT NULL DEFAULT 'PENDING',
    `created_at` DATETIME NOT NULL,
    `approved_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `ix_schools_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create email_verification_tokens table
CREATE TABLE IF NOT EXISTS `email_verification_tokens` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `token` VARCHAR(64) NOT NULL,
    `user_id` INT NULL,
    `school_id` INT NULL,
    `expires_at` DATETIME NOT NULL,
    `used_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `ix_email_verification_tokens_token` (`token`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
    FOREIGN KEY (`school_id`) REFERENCES `schools`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create password_reset_tokens table
CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `token` VARCHAR(64) NOT NULL,
    `user_id` INT NOT NULL,
    `expires_at` DATETIME NOT NULL,
    `used_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `ix_password_reset_tokens_token` (`token`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create tickets table
CREATE TABLE IF NOT EXISTS `tickets` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `ticket_number` VARCHAR(20) NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `description` TEXT NOT NULL,
    `category` ENUM('TECHNICAL', 'ACCOUNT', 'COURSE', 'QUIZ', 'GENERAL') NOT NULL,
    `status` ENUM('OPEN', 'IN_QUEUE', 'IN_PROGRESS', 'WAITING_USER', 'RESOLVED', 'CLOSED') NOT NULL,
    `priority` ENUM('LOW', 'MEDIUM', 'HIGH', 'URGENT') NOT NULL,
    `school_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    `resolved_at` DATETIME NULL,
    `closed_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `ix_tickets_ticket_number` (`ticket_number`),
    INDEX `ix_tickets_school_id` (`school_id`),
    FOREIGN KEY (`school_id`) REFERENCES `schools`(`id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create ticket_messages table
CREATE TABLE IF NOT EXISTS `ticket_messages` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `ticket_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    `content` TEXT NOT NULL,
    `is_internal` TINYINT(1) NOT NULL,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `ix_ticket_messages_ticket_id` (`ticket_id`),
    FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;