--
-- Host: localhost    Database: aesa_event
-- ------------------------------------------------------


--
-- Table structure for table `aesa_activity_logs`
--

CREATE TABLE `aesa_activity_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `admin_id` int unsigned DEFAULT NULL,
  `employee_id` int unsigned DEFAULT NULL,
  `action` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `model_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `model_id` int unsigned DEFAULT NULL,
  `old_values` json DEFAULT NULL,
  `new_values` json DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_admin` (`admin_id`),
  KEY `idx_action` (`action`),
  KEY `idx_model` (`model_type`,`model_id`),
  KEY `idx_created` (`created_at`),
  CONSTRAINT `aesa_activity_logs_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `aesa_admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `aesa_activity_logs`
--

INSERT INTO `aesa_activity_logs` VALUES (1,1,NULL,'login','Admin logged in successfully',NULL,NULL,NULL,NULL,'::1','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/144.0.0.0 Safari/537.36','2026-01-23 23:47:46'),(2,1,NULL,'logout','Admin logged out',NULL,NULL,NULL,NULL,'::1','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/144.0.0.0 Safari/537.36','2026-01-24 00:45:31'),(3,1,NULL,'login','Admin logged in successfully',NULL,NULL,NULL,NULL,'::1','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/144.0.0.0 Safari/537.36','2026-01-24 10:07:53'),(4,1,NULL,'logout','Admin logged out',NULL,NULL,NULL,NULL,'::1','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/144.0.0.0 Safari/537.36','2026-01-24 11:46:35'),(5,1,NULL,'login','Admin logged in successfully',NULL,NULL,NULL,NULL,'::1','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/144.0.0.0 Safari/537.36','2026-01-24 11:46:38');

--
-- Table structure for table `aesa_email_logs`
--

CREATE TABLE `aesa_email_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int unsigned DEFAULT NULL,
  `to_email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `subject` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `template` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `language` enum('ar','en') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'ar',
  `status` enum('pending','sent','failed','bounced','opened','clicked') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `sent_by` int unsigned DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `opened_at` datetime DEFAULT NULL,
  `clicked_at` datetime DEFAULT NULL,
  `error_message` text COLLATE utf8mb4_unicode_ci,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_employee` (`employee_id`),
  KEY `idx_status` (`status`),
  KEY `idx_template` (`template`),
  KEY `sent_by` (`sent_by`),
  CONSTRAINT `aesa_email_logs_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `aesa_employees` (`id`) ON DELETE SET NULL,
  CONSTRAINT `aesa_email_logs_ibfk_2` FOREIGN KEY (`sent_by`) REFERENCES `aesa_admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `aesa_email_logs`
--

INSERT INTO `aesa_email_logs` VALUES (1,1,'mrea99@gmail.com','دعوة لحضور الحفل السنوي لأمريكان إكسبريس السعودية','invitation','ar','failed',NULL,NULL,NULL,NULL,'SMTP connection failed: php_network_getaddresses: getaddrinfo for  failed: nodename nor servname provided, or not known','2026-01-24 11:14:36'),(2,1,'mrea99@gmail.com','دعوة لحضور الحفل السنوي لأمريكان إكسبريس السعودية','invitation','ar','failed',NULL,NULL,NULL,NULL,'SMTP connection failed: php_network_getaddresses: getaddrinfo for  failed: nodename nor servname provided, or not known','2026-01-24 11:14:54'),(3,1,'mrea99@gmail.com','دعوة لحضور الحفل السنوي لأمريكان إكسبريس السعودية','invitation','ar','failed',NULL,NULL,NULL,NULL,'SMTP connection failed: php_network_getaddresses: getaddrinfo for  failed: nodename nor servname provided, or not known','2026-01-24 11:15:05'),(4,1,'mrea99@gmail.com','دعوة لحضور الحفل السنوي لأمريكان إكسبريس السعودية','invitation','ar','sent',NULL,'2026-01-24 09:19:21',NULL,NULL,NULL,'2026-01-24 11:19:19'),(5,1,'mrea99@gmail.com','دعوة لحضور الحفل السنوي لأمريكان إكسبريس السعودية','invitation','ar','sent',NULL,'2026-01-24 09:32:55',NULL,NULL,NULL,'2026-01-24 11:32:53'),(6,1,'mrea99@gmail.com','دعوة لحضور الحفل السنوي لأمريكان إكسبريس السعودية','invitation','ar','sent',NULL,'2026-01-24 09:35:21',NULL,NULL,NULL,'2026-01-24 11:35:17'),(7,1,'mrea99@gmail.com','دعوة لحضور الحفل السنوي لأمريكان إكسبريس السعودية','invitation','ar','sent',NULL,'2026-01-24 09:37:20',NULL,NULL,NULL,'2026-01-24 11:37:16'),(8,1,'mrea99@gmail.com','دعوة لحضور الحفل السنوي لأمريكان إكسبريس السعودية','invitation','ar','sent',NULL,'2026-01-24 18:21:04',NULL,NULL,NULL,'2026-01-24 20:21:01'),(9,1,'mrea99@gmail.com','دعوة لحضور الحفل السنوي لأمريكان إكسبريس السعودية','invitation','ar','sent',NULL,'2026-01-24 18:30:40',NULL,NULL,NULL,'2026-01-24 20:30:38'),(10,1,'mrea99@gmail.com','دعوة لحضور الحفل السنوي لأمريكان إكسبريس السعودية','invitation','ar','sent',NULL,'2026-01-24 18:38:49',NULL,NULL,NULL,'2026-01-24 20:38:47'),(11,1,'mrea99@gmail.com','دعوة لحضور الحفل السنوي لأمريكان إكسبريس السعودية','invitation','ar','sent',NULL,'2026-01-24 20:04:19',NULL,NULL,NULL,'2026-01-24 22:04:17');

--
-- Table structure for table `aesa_imports`
--

CREATE TABLE `aesa_imports` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `original_filename` varchar(255) COLLATE utf8mb4_unicode_ci 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') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `errors` json DEFAULT NULL,
  `imported_by` int unsigned NOT NULL,
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `imported_by` (`imported_by`),
  CONSTRAINT `aesa_imports_ibfk_1` FOREIGN KEY (`imported_by`) REFERENCES `aesa_admins` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `aesa_imports`
--


--
-- Table structure for table `aesa_lucky_draw_winners`
--

CREATE TABLE `aesa_lucky_draw_winners` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `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 DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_draw_employee` (`draw_id`,`employee_id`),
  KEY `idx_draw` (`draw_id`),
  KEY `idx_employee` (`employee_id`),
  CONSTRAINT `aesa_lucky_draw_winners_ibfk_1` FOREIGN KEY (`draw_id`) REFERENCES `aesa_lucky_draws` (`id`) ON DELETE CASCADE,
  CONSTRAINT `aesa_lucky_draw_winners_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `aesa_employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `aesa_lucky_draw_winners`
--


--
-- Table structure for table `aesa_lucky_draws`
--

CREATE TABLE `aesa_lucky_draws` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name_en` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_ar` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `prize_en` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `prize_ar` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `prize_value` decimal(10,2) DEFAULT NULL,
  `max_winners` int NOT NULL DEFAULT '1',
  `draw_type` enum('manual','automatic','timed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'manual',
  `scheduled_at` datetime DEFAULT NULL,
  `status` enum('pending','in_progress','completed','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `conducted_by` int unsigned DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `conducted_by` (`conducted_by`),
  CONSTRAINT `aesa_lucky_draws_ibfk_1` FOREIGN KEY (`conducted_by`) REFERENCES `aesa_admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `aesa_lucky_draws`
--


--
-- Table structure for table `aesa_registrations`
--

CREATE TABLE `aesa_registrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int unsigned NOT NULL,
  `dietary_requirements` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `special_needs` text COLLATE utf8mb4_unicode_ci,
  `emergency_contact_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `emergency_contact_phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `transportation_needed` tinyint(1) NOT NULL DEFAULT '0',
  `plus_one` tinyint(1) NOT NULL DEFAULT '0',
  `plus_one_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `terms_accepted` tinyint(1) NOT NULL DEFAULT '0',
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_employee` (`employee_id`),
  CONSTRAINT `aesa_registrations_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `aesa_employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `aesa_registrations`
--


--
-- Table structure for table `aesa_scan_logs`
--

CREATE TABLE `aesa_scan_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int unsigned NOT NULL,
  `scanned_by` int unsigned DEFAULT NULL,
  `qr_code` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `scan_result` enum('success','duplicate','invalid','expired','not_registered') COLLATE utf8mb4_unicode_ci NOT NULL,
  `device_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `device_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `location` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `is_offline_sync` tinyint(1) NOT NULL DEFAULT '0',
  `offline_scanned_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_employee` (`employee_id`),
  KEY `idx_scanned_by` (`scanned_by`),
  KEY `idx_result` (`scan_result`),
  KEY `idx_created` (`created_at`),
  CONSTRAINT `aesa_scan_logs_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `aesa_employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `aesa_scan_logs_ibfk_2` FOREIGN KEY (`scanned_by`) REFERENCES `aesa_admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `aesa_scan_logs`
--


--
-- Table structure for table `aesa_sessions`
--

CREATE TABLE `aesa_sessions` (
  `id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `admin_id` int unsigned DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `payload` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_activity` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_admin` (`admin_id`),
  KEY `idx_last_activity` (`last_activity`),
  CONSTRAINT `aesa_sessions_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `aesa_admins` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `aesa_sessions`
--



