-- =====================================================
-- AESA Event Registration System
-- Database Schema v1.0.0
-- MySQL 5.7+ / MariaDB 10.2+
-- =====================================================


-- -----------------------------------------------------
-- Table: aesa_registrations
-- Registration form submissions
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `aesa_registrations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT UNSIGNED NOT NULL,
    `dietary_requirements` VARCHAR(255) NULL,
    `special_needs` TEXT NULL,
    `emergency_contact_name` VARCHAR(100) NULL,
    `emergency_contact_phone` VARCHAR(20) NULL,
    `transportation_needed` TINYINT(1) NOT NULL DEFAULT 0,
    `plus_one` TINYINT(1) NOT NULL DEFAULT 0,
    `plus_one_name` VARCHAR(100) NULL,
    `terms_accepted` TINYINT(1) NOT NULL DEFAULT 0,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_employee` (`employee_id`),
    FOREIGN KEY (`employee_id`) REFERENCES `aesa_employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: aesa_scan_logs
-- QR scan history for attendance tracking
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `aesa_scan_logs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT UNSIGNED NOT NULL,
    `scanned_by` INT UNSIGNED NULL,
    `qr_code` VARCHAR(64) NOT NULL,
    `scan_result` ENUM('success', 'duplicate', 'invalid', 'expired', 'not_registered') NOT NULL,
    `device_name` VARCHAR(100) NULL,
    `device_id` VARCHAR(100) NULL,
    `ip_address` VARCHAR(45) NULL,
    `location` VARCHAR(100) NULL,
    `notes` TEXT NULL,
    `is_offline_sync` TINYINT(1) NOT NULL DEFAULT 0,
    `offline_scanned_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_employee` (`employee_id`),
    INDEX `idx_scanned_by` (`scanned_by`),
    INDEX `idx_result` (`scan_result`),
    INDEX `idx_created` (`created_at`),
    FOREIGN KEY (`employee_id`) REFERENCES `aesa_employees`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`scanned_by`) REFERENCES `aesa_admins`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: aesa_lucky_draws
-- Lucky draw sessions
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `aesa_lucky_draws` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name_en` VARCHAR(100) NOT NULL,
    `name_ar` VARCHAR(100) NOT NULL,
    `prize_en` VARCHAR(255) NULL,
    `prize_ar` VARCHAR(255) NULL,
    `prize_value` DECIMAL(10,2) NULL,
    `max_winners` INT NOT NULL DEFAULT 1,
    `draw_type` ENUM('manual', 'automatic', 'timed') NOT NULL DEFAULT 'manual',
    `scheduled_at` DATETIME NULL,
    `status` ENUM('pending', 'in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
    `conducted_by` INT UNSIGNED NULL,
    `started_at` DATETIME NULL,
    `completed_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_status` (`status`),
    FOREIGN KEY (`conducted_by`) REFERENCES `aesa_admins`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: aesa_lucky_draw_winners
-- Lucky draw winners
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `aesa_lucky_draw_winners` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `draw_id` INT UNSIGNED NOT NULL,
    `employee_id` INT UNSIGNED NOT NULL,
    `position` INT NOT NULL DEFAULT 1,
    `won_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `claimed` TINYINT(1) NOT NULL DEFAULT 0,
    `claimed_at` DATETIME NULL,
    `notes` TEXT NULL,
    UNIQUE KEY `unique_draw_employee` (`draw_id`, `employee_id`),
    INDEX `idx_draw` (`draw_id`),
    INDEX `idx_employee` (`employee_id`),
    FOREIGN KEY (`draw_id`) REFERENCES `aesa_lucky_draws`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`employee_id`) REFERENCES `aesa_employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: aesa_email_logs
-- Email sending history
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `aesa_email_logs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT UNSIGNED NULL,
    `to_email` VARCHAR(255) NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `template` VARCHAR(50) NOT NULL,
    `language` ENUM('ar', 'en') NOT NULL DEFAULT 'ar',
    `status` ENUM('pending', 'sent', 'failed', 'bounced', 'opened', 'clicked') NOT NULL DEFAULT 'pending',
    `sent_by` INT UNSIGNED NULL,
    `sent_at` DATETIME NULL,
    `opened_at` DATETIME NULL,
    `clicked_at` DATETIME NULL,
    `error_message` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_employee` (`employee_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_template` (`template`),
    FOREIGN KEY (`employee_id`) REFERENCES `aesa_employees`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`sent_by`) REFERENCES `aesa_admins`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: aesa_activity_logs
-- System activity audit trail
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `aesa_activity_logs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `admin_id` INT UNSIGNED NULL,
    `employee_id` INT UNSIGNED NULL,
    `action` VARCHAR(50) NOT NULL,
    `description` TEXT NULL,
    `model_type` VARCHAR(50) NULL,
    `model_id` INT UNSIGNED NULL,
    `old_values` JSON NULL,
    `new_values` JSON NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_admin` (`admin_id`),
    INDEX `idx_action` (`action`),
    INDEX `idx_model` (`model_type`, `model_id`),
    INDEX `idx_created` (`created_at`),
    FOREIGN KEY (`admin_id`) REFERENCES `aesa_admins`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: aesa_sessions
-- Active sessions for security
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `aesa_sessions` (
    `id` VARCHAR(100) PRIMARY KEY,
    `admin_id` INT UNSIGNED NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `payload` TEXT NOT NULL,
    `last_activity` INT NOT NULL,
    INDEX `idx_admin` (`admin_id`),
    INDEX `idx_last_activity` (`last_activity`),
    FOREIGN KEY (`admin_id`) REFERENCES `aesa_admins`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: aesa_imports
-- Excel/CSV import history
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `aesa_imports` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `filename` VARCHAR(255) NOT NULL,
    `original_filename` VARCHAR(255) NOT NULL,
    `file_size` INT UNSIGNED NOT NULL,
    `total_rows` INT UNSIGNED NOT NULL DEFAULT 0,
    `processed_rows` INT UNSIGNED NOT NULL DEFAULT 0,
    `success_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `error_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending',
    `errors` JSON NULL,
    `imported_by` INT UNSIGNED NOT NULL,
    `started_at` DATETIME NULL,
    `completed_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_status` (`status`),
    FOREIGN KEY (`imported_by`) REFERENCES `aesa_admins`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

