265 lines
16 KiB
SQL
265 lines
16 KiB
SQL
-- ==============================================================================
|
|
-- Uruk International Prize Platform - Database Schema (MySQL 8.0)
|
|
-- Engine: InnoDB | Collation: utf8mb4_unicode_ci
|
|
-- Designed for High Security, Fingerprint Verification, and Audit Compliance
|
|
-- ==============================================================================
|
|
|
|
SET NAMES utf8mb4;
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
-- 1. جدول المستخدمين الأساسي (Users)
|
|
CREATE TABLE IF NOT EXISTS `users` (
|
|
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
`phone` VARCHAR(20) NOT NULL UNIQUE,
|
|
`email` VARCHAR(191) NULL UNIQUE,
|
|
`full_name` VARCHAR(191) NOT NULL,
|
|
`country` VARCHAR(64) NOT NULL DEFAULT 'Iraq',
|
|
`city` VARCHAR(64) NULL,
|
|
`national_id_hash` VARCHAR(64) NULL COMMENT 'Encrypted or hashed national ID',
|
|
`avatar_url` VARCHAR(255) NULL,
|
|
`role` ENUM('MEMBER', 'PARTNER_STAFF', 'ADMIN', 'SUPER_ADMIN') NOT NULL DEFAULT 'MEMBER',
|
|
`status` ENUM('PENDING_VERIFICATION', 'ACTIVE', 'SUSPENDED', 'BANNED') NOT NULL DEFAULT 'PENDING_VERIFICATION',
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX `idx_users_phone` (`phone`),
|
|
INDEX `idx_users_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 2. جدول أمان الجهاز والبصمة والترويسات (User Security & Fingerprint)
|
|
CREATE TABLE IF NOT EXISTS `user_security` (
|
|
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
`user_id` BIGINT UNSIGNED NOT NULL,
|
|
`device_fingerprint` VARCHAR(64) NOT NULL COMMENT 'SHA-256 derived from hardware & SecureStorage',
|
|
`device_secret` VARCHAR(64) NOT NULL COMMENT 'HMAC secret key unique to this device',
|
|
`device_model` VARCHAR(128) NULL,
|
|
`os_version` VARCHAR(64) NULL,
|
|
`last_token` VARCHAR(255) NULL,
|
|
`last_ip` VARCHAR(45) NULL,
|
|
`last_active_at` TIMESTAMP NULL,
|
|
`failed_attempts` INT UNSIGNED NOT NULL DEFAULT 0,
|
|
`is_locked` TINYINT(1) NOT NULL DEFAULT 0,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT `fk_security_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
|
|
UNIQUE KEY `uk_user_device` (`user_id`, `device_fingerprint`),
|
|
INDEX `idx_fingerprint` (`device_fingerprint`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 3. جدول العضويات والاشتراكات (Memberships / Subscriptions)
|
|
CREATE TABLE IF NOT EXISTS `subscriptions` (
|
|
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
`user_id` BIGINT UNSIGNED NOT NULL,
|
|
`membership_number` VARCHAR(32) NOT NULL UNIQUE COMMENT 'e.g. URUK-2026-XXXXX',
|
|
`plan_name` VARCHAR(64) NOT NULL DEFAULT '2-Year Executive Membership',
|
|
`price_usd` DECIMAL(10, 2) NOT NULL DEFAULT 100.00,
|
|
`price_local` DECIMAL(14, 2) NOT NULL DEFAULT 132000.00,
|
|
`currency` VARCHAR(8) NOT NULL DEFAULT 'IQD',
|
|
`starts_at` DATE NOT NULL,
|
|
`expires_at` DATE NOT NULL,
|
|
`status` ENUM('PENDING_PAYMENT', 'ACTIVE', 'EXPIRED', 'REVOKED') NOT NULL DEFAULT 'PENDING_PAYMENT',
|
|
`qr_seed` VARCHAR(64) NOT NULL COMMENT 'Cryptographic salt for dynamic rotating QR',
|
|
`activated_by` BIGINT UNSIGNED NULL COMMENT 'Admin user ID if activated manually',
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT `fk_subscription_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
|
|
INDEX `idx_membership_number` (`membership_number`),
|
|
INDEX `idx_status_expiry` (`status`, `expires_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 4. جدول المعاملات والمدفوعات (Financial Transactions)
|
|
CREATE TABLE IF NOT EXISTS `transactions` (
|
|
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
`subscription_id` BIGINT UNSIGNED NOT NULL,
|
|
`user_id` BIGINT UNSIGNED NOT NULL,
|
|
`method` ENUM('SUPER_QI', 'ZAIN_CASH', 'SWIFTPAY_IQ', 'RAFIDAIN_CARD', 'CLIQ_JORDAN', 'CASH_DIRECT') NOT NULL,
|
|
`reference_number` VARCHAR(128) NOT NULL UNIQUE COMMENT 'Transaction ID / Movement ID from SuperQi, ZainCash, etc.',
|
|
`sender_account_or_phone` VARCHAR(64) NULL,
|
|
`recipient_account` VARCHAR(64) NULL,
|
|
`amount` DECIMAL(14, 2) NOT NULL,
|
|
`currency` VARCHAR(8) NOT NULL DEFAULT 'IQD',
|
|
`status` ENUM('SUBMITTED', 'VERIFIED_AUTO', 'VERIFIED_ADMIN', 'REJECTED') NOT NULL DEFAULT 'SUBMITTED',
|
|
`receipt_image_url` VARCHAR(255) NULL,
|
|
`raw_webhook_payload` JSON NULL,
|
|
`verified_at` TIMESTAMP NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT `fk_transaction_sub` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`) ON DELETE CASCADE,
|
|
CONSTRAINT `fk_transaction_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
|
|
INDEX `idx_ref_number` (`reference_number`),
|
|
INDEX `idx_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 5. جدول الشركاء (المستشفيات والفنادق والمراكز)
|
|
CREATE TABLE IF NOT EXISTS `partners` (
|
|
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
`type` ENUM('HOSPITAL', 'HOTEL', 'TRAINING_CENTER', 'OTHER') NOT NULL,
|
|
`name_ar` VARCHAR(191) NOT NULL,
|
|
`name_en` VARCHAR(191) NULL,
|
|
`category` VARCHAR(64) NOT NULL COMMENT 'Eyes, Cancer, Heart, General, 5-Star Hotel...',
|
|
`country` VARCHAR(64) NOT NULL DEFAULT 'Iraq',
|
|
`city` VARCHAR(64) NOT NULL,
|
|
`discount_percentage` DECIMAL(5, 2) NOT NULL COMMENT 'e.g. 50.00 for hospitals, 40-60 for hotels',
|
|
`address` TEXT NULL,
|
|
`phone` VARCHAR(64) NULL,
|
|
`email` VARCHAR(128) NULL,
|
|
`latitude` DECIMAL(10, 8) NULL,
|
|
`longitude` DECIMAL(11, 8) NULL,
|
|
`contract_starts_at` DATE NULL,
|
|
`contract_ends_at` DATE NULL,
|
|
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`added_by_admin` BIGINT UNSIGNED NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX `idx_type_country_city` (`type`, `country`, `city`),
|
|
INDEX `idx_is_active` (`is_active`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 6. جدول الدورات التدريبية المعتمدة (Academy & Workshops)
|
|
CREATE TABLE IF NOT EXISTS `courses` (
|
|
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
`title` VARCHAR(191) NOT NULL,
|
|
`specialty` VARCHAR(64) NOT NULL COMMENT 'Medical, Languages, Engineering, Management...',
|
|
`instructor_name` VARCHAR(128) NOT NULL,
|
|
`delivery_format` ENUM('IN_PERSON_HOTEL', 'ONLINE_STREAM', 'HYBRID') NOT NULL DEFAULT 'HYBRID',
|
|
`hotel_partner_id` BIGINT UNSIGNED NULL,
|
|
`scheduled_start` DATETIME NOT NULL,
|
|
`scheduled_end` DATETIME NULL,
|
|
`broadcast_url` VARCHAR(255) NULL,
|
|
`max_seats` INT UNSIGNED NULL,
|
|
`is_published` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT `fk_course_hotel` FOREIGN KEY (`hotel_partner_id`) REFERENCES `partners` (`id`) ON DELETE SET NULL,
|
|
INDEX `idx_specialty` (`specialty`),
|
|
INDEX `idx_scheduled_start` (`scheduled_start`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 7. جدول تسجيل المتدربين والشهادات (Course Enrollments & Certificates)
|
|
CREATE TABLE IF NOT EXISTS `course_enrollments` (
|
|
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
`user_id` BIGINT UNSIGNED NOT NULL,
|
|
`course_id` BIGINT UNSIGNED NOT NULL,
|
|
`status` ENUM('REGISTERED', 'ATTENDED', 'COMPLETED', 'CERTIFICATE_ISSUED') NOT NULL DEFAULT 'REGISTERED',
|
|
`certificate_serial` VARCHAR(64) NULL UNIQUE COMMENT 'Accredited certificate tracking number',
|
|
`certificate_url` VARCHAR(255) NULL,
|
|
`issued_at` DATETIME NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT `fk_enrollment_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
|
|
CONSTRAINT `fk_enrollment_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE,
|
|
UNIQUE KEY `uk_user_course` (`user_id`, `course_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 8. سجل التحقق والزيارات الميدانية (Verification Logs at Partners)
|
|
CREATE TABLE IF NOT EXISTS `verification_logs` (
|
|
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
`subscription_id` BIGINT UNSIGNED NOT NULL,
|
|
`partner_id` BIGINT UNSIGNED NOT NULL,
|
|
`verifier_staff_name` VARCHAR(128) NULL,
|
|
`applied_discount` DECIMAL(5, 2) NOT NULL,
|
|
`service_type` VARCHAR(128) NULL,
|
|
`scanned_token_signature` VARCHAR(64) NOT NULL,
|
|
`verified_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT `fk_verif_sub` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`) ON DELETE CASCADE,
|
|
CONSTRAINT `fk_verif_partner` FOREIGN KEY (`partner_id`) REFERENCES `partners` (`id`) ON DELETE CASCADE,
|
|
INDEX `idx_partner_verified_at` (`partner_id`, `verified_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 9. جدول التدقيق الأمني ومراقبة الترويسات (Audit & Header Logs)
|
|
CREATE TABLE IF NOT EXISTS `audit_logs` (
|
|
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
`user_id` BIGINT UNSIGNED NULL,
|
|
`action` VARCHAR(64) NOT NULL,
|
|
`endpoint` VARCHAR(128) NOT NULL,
|
|
`method` VARCHAR(10) NOT NULL,
|
|
`ip_address` VARCHAR(45) NOT NULL,
|
|
`device_fingerprint` VARCHAR(64) NULL,
|
|
`nonce_used` VARCHAR(64) NULL,
|
|
`response_code` SMALLINT UNSIGNED NOT NULL,
|
|
`payload_snippet` TEXT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX `idx_user_action` (`user_id`, `action`),
|
|
INDEX `idx_created_at` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 10. جدول أجهزة بوابات الاتصال الثلاثة (Gateway Caller Devices - Zain, Asiacell, Korek)
|
|
CREATE TABLE IF NOT EXISTS `gateway_devices` (
|
|
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
`device_id` VARCHAR(64) NOT NULL UNIQUE COMMENT 'e.g. zain-node-1, asiacell-node-2, korek-node-3',
|
|
`operator` ENUM('ZAIN', 'ASIACELL', 'KOREK', 'UNKNOWN') NOT NULL DEFAULT 'UNKNOWN',
|
|
`phone_number` VARCHAR(32) NULL,
|
|
`sim_slot` TINYINT UNSIGNED NOT NULL DEFAULT 1,
|
|
`status` ENUM('ACTIVE', 'OFFLINE', 'BUSY') NOT NULL DEFAULT 'ACTIVE',
|
|
`last_heartbeat` TIMESTAMP NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX `idx_gw_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 11. جدول مهام الاتصال الفلاش والرسائل النصية (Gateway Tasks Queue)
|
|
CREATE TABLE IF NOT EXISTS `gateway_tasks` (
|
|
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
`task_type` ENUM('FLASH_CALL', 'SMS') NOT NULL DEFAULT 'FLASH_CALL',
|
|
`target_phone` VARCHAR(32) NOT NULL,
|
|
`otp_code` VARCHAR(16) NOT NULL,
|
|
`assigned_device_id` VARCHAR(64) NULL,
|
|
`status` ENUM('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'EXPIRED') NOT NULL DEFAULT 'PENDING',
|
|
`timeout_seconds` INT UNSIGNED NOT NULL DEFAULT 25,
|
|
`completed_at` TIMESTAMP NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX `idx_task_status_type` (`status`, `task_type`, `created_at`),
|
|
INDEX `idx_assigned_device` (`assigned_device_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 12. جدول محفظة التوفير المالي التراكمي للمشترك (Member Savings Ledger)
|
|
CREATE TABLE IF NOT EXISTS `savings_ledger` (
|
|
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
`subscription_id` BIGINT UNSIGNED NOT NULL,
|
|
`user_id` BIGINT UNSIGNED NOT NULL,
|
|
`partner_id` BIGINT UNSIGNED NOT NULL,
|
|
`service_name` VARCHAR(191) NOT NULL COMMENT 'e.g. تحاليل وفحوصات شاملة، إقامة فندقية ليلتين، دورة تدريبية',
|
|
`original_amount` DECIMAL(14, 2) NOT NULL COMMENT 'Original bill before discount in IQD',
|
|
`discount_percentage` DECIMAL(5, 2) NOT NULL COMMENT 'e.g. 25.00',
|
|
`saved_amount` DECIMAL(14, 2) NOT NULL COMMENT 'Actual cash saved by member in IQD',
|
|
`paid_amount` DECIMAL(14, 2) NOT NULL COMMENT 'Amount actually paid by member in IQD',
|
|
`currency` VARCHAR(8) NOT NULL DEFAULT 'IQD',
|
|
`verified_by_staff` VARCHAR(128) NULL,
|
|
`notes` TEXT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT `fk_savings_sub` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`) ON DELETE CASCADE,
|
|
CONSTRAINT `fk_savings_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
|
|
CONSTRAINT `fk_savings_partner` FOREIGN KEY (`partner_id`) REFERENCES `partners` (`id`) ON DELETE CASCADE,
|
|
INDEX `idx_savings_user` (`user_id`),
|
|
INDEX `idx_savings_sub` (`subscription_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 13. جدول إرساليات المصادقة وتتبع القنوات (OTP Dispatches & Multi-Channel Routing)
|
|
CREATE TABLE IF NOT EXISTS `otp_dispatches` (
|
|
`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
`phone` VARCHAR(32) NOT NULL,
|
|
`user_id` BIGINT UNSIGNED NULL,
|
|
`channel` ENUM('SILENT_CALL', 'TELEGRAM', 'WHATSAPP_CAPTCHA', 'SMS_OTPIQ') NOT NULL,
|
|
`otp_code_hash` VARCHAR(64) NOT NULL,
|
|
`status` ENUM('QUEUED', 'DISPATCHED', 'DELIVERED', 'VERIFIED', 'EXPIRED', 'FAILED') NOT NULL DEFAULT 'QUEUED',
|
|
`gateway_device_id` VARCHAR(64) NULL,
|
|
`provider_message_id` VARCHAR(128) NULL,
|
|
`cost_iqd` DECIMAL(8, 2) NOT NULL DEFAULT 0.00,
|
|
`expires_at` TIMESTAMP NOT NULL,
|
|
`verified_at` TIMESTAMP NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX `idx_otp_phone_status` (`phone`, `status`),
|
|
INDEX `idx_otp_expires` (`expires_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 14. بيانات أولية للشركاء المعتمدين في العراق (Seed Partners)
|
|
INSERT INTO `partners` (`id`, `type`, `name_ar`, `name_en`, `category`, `country`, `city`, `discount_percentage`, `address`, `phone`, `is_active`) VALUES
|
|
(1, 'HOSPITAL', 'مستشفى الكفيل التخصصي', 'Al-Kafeel Super Speciality Hospital', 'مستشفى جراحي وتخصصي متقدم', 'Iraq', 'كربلاء المقدسة', 25.00, 'طريق كربلاء - النجف', '+9647801234001', 1),
|
|
(2, 'HOSPITAL', 'مستشفى الفاروق التخصصي', 'Al-Farouk Hospital', 'جراحة عامة وباطنية ومختبرات', 'Iraq', 'بغداد', 30.00, 'المنصور - شارع 14 رمضان', '+9647801234002', 1),
|
|
(3, 'HOTEL', 'فندق بابل روتانا', 'Babylon Rotana Hotel', 'فندق 5 نجوم وضيافة فاخرة', 'Iraq', 'بغداد', 30.00, 'الجادرية - شارع الكرادة', '+9647801234003', 1),
|
|
(4, 'HOTEL', 'فندق أربيل الدولي', 'Erbil International Hotel', 'فندق 5 نجوم ومركز مؤتمرات', 'Iraq', 'أربيل', 35.00, 'شارع 30 متري - قرب القلعة', '+9647801234004', 1),
|
|
(5, 'TRAINING_CENTER', 'أكاديمية أوروك الدولية للتدريب والتطوير', 'Uruk International Training Academy', 'إدارة وقيادة وتقنيات حديثة', 'Iraq', 'بغداد', 50.00, 'العرصات - مجمع أوروك الأكاديمي', '+9647801234005', 1)
|
|
ON DUPLICATE KEY UPDATE `name_ar` = VALUES(`name_ar`);
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
|