-- ============================================================
-- SMS & Email Gateway SaaS Platform - Database Schema
-- MySQL 5.7+ / 8.0 - utf8mb4
-- ============================================================

CREATE DATABASE IF NOT EXISTS `sms_gateway_saas`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE `sms_gateway_saas`;

-- ============================================================
-- جدول مديري النظام (لوحة التحكم - أنت وفريقك، مختلف عن العملاء)
-- ============================================================
CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) DEFAULT NULL,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_admins_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- جدول الباقات
-- ============================================================
CREATE TABLE IF NOT EXISTS `packages` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `max_sms` INT UNSIGNED NOT NULL DEFAULT 0,
  `max_emails` INT UNSIGNED NOT NULL DEFAULT 0,
  `max_whatsapp` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'رصيد رسائل واتساب المتاح بهذه الباقة',
  `duration_days` INT UNSIGNED NOT NULL DEFAULT 30,
  `status` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- جدول العملاء (Tenants) - كل عميل يملك API Key خاص فيه
-- ============================================================
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `api_key` VARCHAR(64) NOT NULL,
  `package_id` INT UNSIGNED DEFAULT NULL,
  `sms_credits` INT NOT NULL DEFAULT 0,
  `email_credits` INT NOT NULL DEFAULT 0,
  `whatsapp_status` ENUM('not_linked','pending','connected') NOT NULL DEFAULT 'not_linked' COMMENT 'حالة ربط رقم واتساب الخاص بالعميل',
  `whatsapp_credits` INT NOT NULL DEFAULT 1000 COMMENT 'رصيد رسائل واتساب المتبقي',
  `subscription_expires_at` DATETIME DEFAULT NULL,
  `expiry_notice_sent_at` DATETIME DEFAULT NULL COMMENT 'آخر مرة أُرسل فيها تنبيه اقتراب انتهاء الاشتراك',
  `status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=active 0=suspended',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_email` (`email`),
  UNIQUE KEY `uq_users_api_key` (`api_key`),
  KEY `fk_users_package` (`package_id`),
  CONSTRAINT `fk_users_package` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- جدول طابور الرسائل
-- ============================================================
CREATE TABLE IF NOT EXISTS `messages_queue` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `type` ENUM('sms','email','whatsapp') NOT NULL,
  `recipient` VARCHAR(255) NOT NULL,
  `subject` VARCHAR(255) DEFAULT NULL,
  `body` TEXT NOT NULL,
  `is_html` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 لو body محتوى HTML منسّق بدل نص عادي',
  `status` ENUM('pending','processing','sent','failed') NOT NULL DEFAULT 'pending',
  `error_message` VARCHAR(500) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `processing_at` DATETIME DEFAULT NULL,
  `sent_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_mq_user` (`user_id`),
  KEY `idx_mq_status` (`status`),
  KEY `idx_mq_type_status` (`type`,`status`),
  CONSTRAINT `fk_mq_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- جدول سجل استخدام الـ API (اختياري لكن مفيد جدًا للمراقبة)
-- ============================================================
CREATE TABLE IF NOT EXISTS `api_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `endpoint` VARCHAR(100) NOT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `request_data` TEXT DEFAULT NULL,
  `response_code` SMALLINT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_logs_user` (`user_id`),
  KEY `idx_logs_user_endpoint_created` (`user_id`, `endpoint`, `created_at`),
  KEY `idx_logs_ip_endpoint_created` (`ip_address`, `endpoint`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- جدول الإعدادات القابلة للتغيير من لوحة التحكم
-- (يستخدم لتخزين Gateway Secret Key بدلًا من تعديل الملف يدويًا)
-- ============================================================
CREATE TABLE IF NOT EXISTS `settings` (
  `setting_key`   VARCHAR(100) NOT NULL,
  `setting_value` TEXT NOT NULL,
  `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- جدول طلبات ترقية/تجديد الباقات (نظام فوترة يدوي - تحويل بنكي
-- بموافقة الأدمن، بدون الحاجة لربط بوابة دفع خارجية)
-- ============================================================
CREATE TABLE IF NOT EXISTS `purchase_requests` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `package_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `payment_method` VARCHAR(50) NOT NULL DEFAULT 'bank_transfer',
  `reference_note` VARCHAR(255) DEFAULT NULL COMMENT 'رقم عملية التحويل أو أي ملاحظة من العميل',
  `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `admin_note` VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reviewed_at` DATETIME DEFAULT NULL,
  `reviewed_by` INT UNSIGNED DEFAULT NULL COMMENT 'admin id اللي راجع الطلب',
  PRIMARY KEY (`id`),
  KEY `fk_pr_user` (`user_id`),
  KEY `fk_pr_package` (`package_id`),
  KEY `idx_pr_status` (`status`),
  CONSTRAINT `fk_pr_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pr_package` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- بيانات أولية (Seed)
-- ============================================================

-- أدمن افتراضي: username = admin / password = Admin@12345
-- (غيّر كلمة المرور فورًا بعد أول تسجيل دخول من settings)
INSERT INTO `admins` (`username`, `password_hash`, `name`, `email`) VALUES
('admin', '$2y$10$ua8zAXGsCfu8JY7N4tkIq.ABgIN6SdoLm/noZdeobUyP59C5zLnuK', 'مدير النظام', 'admin@example.com');

-- باقات افتراضية
INSERT INTO `packages` (`name`, `price`, `max_sms`, `max_emails`, `duration_days`) VALUES
('الباقة المجانية', 0.00, 50, 200, 30),
('الباقة الاحترافية', 199.00, 1000, 5000, 30),
('باقة الأعمال', 499.00, 5000, 20000, 30);
