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


-- -----------------------------------------------------
-- Table: aesa_admins
-- Admin users with role-based access
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `aesa_admins` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `employee_number` VARCHAR(50) NULL,
    `name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `role` ENUM('super_admin', 'admin', 'organizer', 'scanner') NOT NULL DEFAULT 'organizer',
    `avatar` VARCHAR(255) NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `last_login_at` DATETIME NULL,
    `last_login_ip` VARCHAR(45) NULL,
    `login_attempts` INT NOT NULL DEFAULT 0,
    `locked_until` DATETIME NULL,
    `remember_token` VARCHAR(100) NULL,
    `api_token` VARCHAR(100) NULL UNIQUE,
    `language` ENUM('ar', 'en') NOT NULL DEFAULT 'ar',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_email` (`email`),
    INDEX `idx_role` (`role`),
    INDEX `idx_api_token` (`api_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Table: aesa_employees
-- All employees eligible for the event
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `aesa_employees` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `sequence_number` INT UNSIGNED NOT NULL,
    `employee_id` VARCHAR(50) NULL,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `name_en` VARCHAR(100) NULL,
    `name_ar` VARCHAR(100) NULL,
    `department_en` VARCHAR(100) NULL,
    `department_ar` VARCHAR(100) NULL,
    `position_en` VARCHAR(100) NULL,
    `position_ar` VARCHAR(100) NULL,
    `phone` VARCHAR(20) NULL,
    `status` ENUM('pending', 'invited', 'registered', 'checked_in', 'no_show') NOT NULL DEFAULT 'pending',
    `invitation_token` VARCHAR(64) NULL UNIQUE,
    `invitation_sent_at` DATETIME NULL,
    `invitation_sent_count` INT NOT NULL DEFAULT 0,
    `invitation_opened_at` DATETIME NULL,
    `qr_code` VARCHAR(64) NULL UNIQUE,
    `qr_generated_at` DATETIME NULL,
    `registration_ip` VARCHAR(45) NULL,
    `registration_user_agent` TEXT NULL,
    `registered_at` DATETIME NULL,
    `checked_in_at` DATETIME NULL,
    `checked_in_by` INT UNSIGNED NULL,
    `check_in_device` VARCHAR(100) NULL,
    `is_eligible_for_draw` TINYINT(1) NOT NULL DEFAULT 0,
    `notes` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_email` (`email`),
    INDEX `idx_status` (`status`),
    INDEX `idx_invitation_token` (`invitation_token`),
    INDEX `idx_qr_code` (`qr_code`),
    INDEX `idx_sequence` (`sequence_number`),
    INDEX `idx_eligible` (`is_eligible_for_draw`),
    FOREIGN KEY (`checked_in_by`) REFERENCES `aesa_admins`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- 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_settings
-- System settings key-value store
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `aesa_settings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) NOT NULL UNIQUE,
    `value` TEXT NULL,
    `type` ENUM('string', 'integer', 'boolean', 'json', 'array') NOT NULL DEFAULT 'string',
    `group` VARCHAR(50) NOT NULL DEFAULT 'general',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_key` (`key`),
    INDEX `idx_group` (`group`)
) 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;

-- -----------------------------------------------------
-- Insert default admin user
-- Password: Admin@2025 (change after first login!)
-- -----------------------------------------------------
INSERT INTO `aesa_admins` (`name`, `email`, `password`, `role`, `language`) VALUES
('System Administrator', 'admin@americanexpress.com', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super_admin', 'en');

-- -----------------------------------------------------
-- Insert default settings
-- -----------------------------------------------------
INSERT INTO `aesa_settings` (`key`, `value`, `type`, `group`) VALUES
('event_name_en', 'AESA Annual Event Party 2025', 'string', 'event'),
('event_name_ar', 'الحفل السنوي لأمريكان إكسبريس 2025', 'string', 'event'),
('event_date', '2025-12-15', 'string', 'event'),
('event_time', '19:00', 'string', 'event'),
('event_venue_en', 'Riyadh, Saudi Arabia', 'string', 'event'),
('event_venue_ar', 'الرياض، المملكة العربية السعودية', 'string', 'event'),
('registration_open', '1', 'boolean', 'event'),
('registration_deadline', '2025-12-10 23:59:59', 'string', 'event'),
('lucky_draw_enabled', '1', 'boolean', 'lucky_draw'),
('lucky_draw_min_attendance', '30', 'integer', 'lucky_draw'),
('theme_primary', '#006FCF', 'string', 'theme'),
('theme_secondary', '#00175A', 'string', 'theme'),
('maintenance_mode', '0', 'boolean', 'system');

