-- MariaDB dump 10.19  Distrib 10.4.32-MariaDB, for Win64 (AMD64)
--
-- Host: localhost    Database: egywhost_crm
-- ------------------------------------------------------
-- Server version	10.4.32-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `audit_logs`
--

DROP TABLE IF EXISTS `audit_logs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `audit_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `action` varchar(50) NOT NULL,
  `entity_type` varchar(50) NOT NULL,
  `entity_id` bigint(20) unsigned DEFAULT NULL,
  `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
  `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_audit_entity` (`entity_type`,`entity_id`),
  KEY `idx_audit_user` (`user_id`),
  KEY `idx_audit_created` (`created_at`),
  CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `audit_logs`
--

LOCK TABLES `audit_logs` WRITE;
/*!40000 ALTER TABLE `audit_logs` DISABLE KEYS */;
/*!40000 ALTER TABLE `audit_logs` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `billing_periods`
--

DROP TABLE IF EXISTS `billing_periods`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `billing_periods` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name_ar` varchar(100) NOT NULL,
  `name_en` varchar(100) NOT NULL,
  `months` smallint(5) unsigned NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_billing_periods_months` (`months`),
  UNIQUE KEY `uniq_billing_periods_code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `billing_periods`
--

LOCK TABLES `billing_periods` WRITE;
/*!40000 ALTER TABLE `billing_periods` DISABLE KEYS */;
INSERT INTO `billing_periods` VALUES (1,'quarterly','ربع سنوي','Quarterly',3,1,1,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(2,'semi_annual','نصف سنوي','Semi-Annual',6,2,1,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(3,'annual','سنوي','Annual',12,3,1,'2026-10-08 23:38:29','2026-10-08 23:38:29');
/*!40000 ALTER TABLE `billing_periods` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `clients`
--

DROP TABLE IF EXISTS `clients`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `clients` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `client_code` varchar(30) NOT NULL,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `whatsapp` varchar(30) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_clients_code` (`client_code`),
  UNIQUE KEY `uniq_clients_email` (`email`),
  KEY `idx_clients_name` (`name`),
  KEY `idx_clients_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `clients`
--

LOCK TABLES `clients` WRITE;
/*!40000 ALTER TABLE `clients` DISABLE KEYS */;
/*!40000 ALTER TABLE `clients` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `domain_registrars`
--

DROP TABLE IF EXISTS `domain_registrars`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `domain_registrars` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `website` varchar(255) DEFAULT NULL,
  `contact_name` varchar(150) DEFAULT NULL,
  `contact_email` varchar(150) DEFAULT NULL,
  `contact_phone` varchar(50) DEFAULT NULL,
  `internal_reference` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_domain_registrars_name` (`name`),
  KEY `idx_domain_registrars_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `domain_registrars`
--

LOCK TABLES `domain_registrars` WRITE;
/*!40000 ALTER TABLE `domain_registrars` DISABLE KEYS */;
/*!40000 ALTER TABLE `domain_registrars` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `domain_renewal_requests`
--

DROP TABLE IF EXISTS `domain_renewal_requests`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `domain_renewal_requests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `domain_id` int(10) unsigned NOT NULL,
  `client_id` int(10) unsigned NOT NULL,
  `status` enum('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `notes` varchar(500) DEFAULT NULL,
  `reviewed_by` int(10) unsigned DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `review_notes` varchar(500) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_drr_domain` (`domain_id`),
  KEY `idx_drr_client` (`client_id`),
  KEY `idx_drr_status` (`status`),
  KEY `fk_drr_reviewer` (`reviewed_by`),
  CONSTRAINT `fk_drr_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_drr_domain` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_drr_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `domain_renewal_requests`
--

LOCK TABLES `domain_renewal_requests` WRITE;
/*!40000 ALTER TABLE `domain_renewal_requests` DISABLE KEYS */;
/*!40000 ALTER TABLE `domain_renewal_requests` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `domain_renewals`
--

DROP TABLE IF EXISTS `domain_renewals`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `domain_renewals` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `domain_id` int(10) unsigned NOT NULL,
  `registrar_id` int(10) unsigned DEFAULT NULL,
  `renewal_date` date NOT NULL,
  `previous_expiration_date` date DEFAULT NULL,
  `new_expiration_date` date NOT NULL,
  `duration_months` int(10) unsigned DEFAULT NULL,
  `cost` decimal(10,2) NOT NULL DEFAULT 0.00,
  `client_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `currency` varchar(10) NOT NULL DEFAULT 'USD',
  `confirmation_source` enum('manual','registrar_confirmed','external','pending_confirmation') NOT NULL DEFAULT 'manual',
  `status` enum('pending_confirmation','confirmed','cancelled') NOT NULL DEFAULT 'confirmed',
  `external_reference` varchar(255) DEFAULT NULL,
  `notes` varchar(500) DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `invoice_id` int(10) unsigned DEFAULT NULL,
  `invoice_item_id` int(10) unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_domain_renewals_transition` (`domain_id`,`previous_expiration_date`,`new_expiration_date`),
  KEY `idx_domain_renewals_domain` (`domain_id`),
  KEY `idx_domain_renewals_registrar` (`registrar_id`),
  KEY `idx_domain_renewals_status` (`status`),
  KEY `idx_domain_renewals_new_expiry` (`new_expiration_date`),
  KEY `fk_domain_renewals_creator` (`created_by`),
  KEY `fk_domain_renewals_invoice` (`invoice_id`),
  CONSTRAINT `fk_domain_renewals_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_domain_renewals_domain` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_domain_renewals_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_domain_renewals_registrar` FOREIGN KEY (`registrar_id`) REFERENCES `domain_registrars` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `domain_renewals`
--

LOCK TABLES `domain_renewals` WRITE;
/*!40000 ALTER TABLE `domain_renewals` DISABLE KEYS */;
/*!40000 ALTER TABLE `domain_renewals` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `domains`
--

DROP TABLE IF EXISTS `domains`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `domains` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `client_id` int(10) unsigned NOT NULL,
  `subscription_id` int(10) unsigned DEFAULT NULL,
  `domain_name` varchar(191) NOT NULL,
  `registrar` varchar(150) DEFAULT NULL,
  `registrar_id` int(10) unsigned DEFAULT NULL,
  `registration_date` date DEFAULT NULL,
  `expiration_date` date DEFAULT NULL,
  `cost_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `selling_price_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `currency` varchar(10) NOT NULL DEFAULT 'USD',
  `exchange_rate_snapshot` decimal(18,6) DEFAULT NULL,
  `auto_renew` tinyint(1) NOT NULL DEFAULT 0,
  `is_subdomain` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','pending','suspended','expired','transferred','cancelled') NOT NULL DEFAULT 'active',
  `notes` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_domains_name` (`domain_name`),
  KEY `idx_domains_client` (`client_id`),
  KEY `idx_domains_expiration` (`expiration_date`),
  KEY `fk_domains_subscription` (`subscription_id`),
  KEY `idx_domains_registrar` (`registrar_id`),
  KEY `fk_domains_creator` (`created_by`),
  CONSTRAINT `fk_domains_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `fk_domains_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_domains_registrar` FOREIGN KEY (`registrar_id`) REFERENCES `domain_registrars` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_domains_subscription` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `domains`
--

LOCK TABLES `domains` WRITE;
/*!40000 ALTER TABLE `domains` DISABLE KEYS */;
/*!40000 ALTER TABLE `domains` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `exchange_rates`
--

DROP TABLE IF EXISTS `exchange_rates`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `exchange_rates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `currency` varchar(10) NOT NULL DEFAULT 'USD',
  `rate` decimal(18,6) NOT NULL,
  `source` varchar(100) DEFAULT NULL,
  `effective_date` date NOT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_exchange_rates_currency_date` (`currency`,`effective_date`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `exchange_rates`
--

LOCK TABLES `exchange_rates` WRITE;
/*!40000 ALTER TABLE `exchange_rates` DISABLE KEYS */;
/*!40000 ALTER TABLE `exchange_rates` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `installments`
--

DROP TABLE IF EXISTS `installments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `installments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` int(10) unsigned NOT NULL,
  `client_id` int(10) unsigned NOT NULL,
  `installment_number` int(10) unsigned NOT NULL,
  `amount_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `due_date` date NOT NULL,
  `paid_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `remaining_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` enum('pending','partially_paid','paid','overdue','cancelled') NOT NULL DEFAULT 'pending',
  `paid_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_installments_invoice_number` (`invoice_id`,`installment_number`),
  KEY `idx_installments_client` (`client_id`),
  KEY `idx_installments_due` (`due_date`),
  KEY `idx_installments_status` (`status`),
  CONSTRAINT `fk_installments_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `fk_installments_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `installments`
--

LOCK TABLES `installments` WRITE;
/*!40000 ALTER TABLE `installments` DISABLE KEYS */;
/*!40000 ALTER TABLE `installments` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `invoice_items`
--

DROP TABLE IF EXISTS `invoice_items`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `invoice_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` int(10) unsigned NOT NULL,
  `item_type` varchar(30) NOT NULL DEFAULT 'service',
  `domain_id` int(10) unsigned DEFAULT NULL,
  `domain_renewal_id` int(10) unsigned DEFAULT NULL,
  `description_ar` varchar(255) DEFAULT NULL,
  `description_en` varchar(255) DEFAULT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `unit_price_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_invoice_items_invoice` (`invoice_id`),
  KEY `idx_invoice_items_domain` (`domain_id`),
  KEY `fk_invoice_items_renewal` (`domain_renewal_id`),
  CONSTRAINT `fk_invoice_items_domain` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_invoice_items_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_invoice_items_renewal` FOREIGN KEY (`domain_renewal_id`) REFERENCES `domain_renewals` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `invoice_items`
--

LOCK TABLES `invoice_items` WRITE;
/*!40000 ALTER TABLE `invoice_items` DISABLE KEYS */;
/*!40000 ALTER TABLE `invoice_items` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `invoices`
--

DROP TABLE IF EXISTS `invoices`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `invoices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_number` varchar(30) NOT NULL,
  `client_id` int(10) unsigned NOT NULL,
  `subscription_id` int(10) unsigned DEFAULT NULL,
  `issue_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `service_period_start` date DEFAULT NULL,
  `service_period_end` date DEFAULT NULL,
  `subtotal_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `discount_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `additional_charges_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `paid_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `remaining_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `currency` varchar(10) NOT NULL DEFAULT 'USD',
  `exchange_rate_id` int(10) unsigned DEFAULT NULL,
  `exchange_rate_snapshot` decimal(18,6) DEFAULT NULL,
  `status` enum('draft','unpaid','partial','paid','overdue','cancelled') NOT NULL DEFAULT 'unpaid',
  `issued_at` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_invoices_number` (`invoice_number`),
  UNIQUE KEY `uniq_invoices_subscription_period` (`subscription_id`,`service_period_start`,`service_period_end`),
  KEY `idx_invoices_client` (`client_id`),
  KEY `idx_invoices_status` (`status`),
  KEY `idx_invoices_due` (`due_date`),
  KEY `fk_invoices_rate` (`exchange_rate_id`),
  KEY `idx_invoices_subscription` (`subscription_id`),
  CONSTRAINT `fk_invoices_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `fk_invoices_rate` FOREIGN KEY (`exchange_rate_id`) REFERENCES `exchange_rates` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_invoices_subscription` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `invoices`
--

LOCK TABLES `invoices` WRITE;
/*!40000 ALTER TABLE `invoices` DISABLE KEYS */;
/*!40000 ALTER TABLE `invoices` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `login_throttles`
--

DROP TABLE IF EXISTS `login_throttles`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `login_throttles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `throttle_key` char(64) NOT NULL,
  `attempts` int(10) unsigned NOT NULL DEFAULT 0,
  `last_attempt_at` datetime DEFAULT NULL,
  `locked_until` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_login_throttle_key` (`throttle_key`),
  KEY `idx_login_throttle_locked` (`locked_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `login_throttles`
--

LOCK TABLES `login_throttles` WRITE;
/*!40000 ALTER TABLE `login_throttles` DISABLE KEYS */;
/*!40000 ALTER TABLE `login_throttles` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `notifications`
--

DROP TABLE IF EXISTS `notifications`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `notifications` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `recipient_user_id` int(10) unsigned NOT NULL,
  `type` varchar(60) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text DEFAULT NULL,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`data`)),
  `related_entity_type` varchar(50) DEFAULT NULL,
  `related_entity_id` bigint(20) unsigned DEFAULT NULL,
  `action_url` varchar(255) DEFAULT NULL,
  `deduplication_key` varchar(191) DEFAULT NULL,
  `read_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_notifications_dedup` (`recipient_user_id`,`deduplication_key`),
  KEY `idx_notifications_recipient` (`recipient_user_id`,`created_at`),
  KEY `idx_notifications_unread` (`recipient_user_id`,`read_at`),
  KEY `idx_notifications_type` (`type`),
  KEY `idx_notifications_entity` (`related_entity_type`,`related_entity_id`),
  CONSTRAINT `fk_notifications_recipient` FOREIGN KEY (`recipient_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `notifications`
--

LOCK TABLES `notifications` WRITE;
/*!40000 ALTER TABLE `notifications` DISABLE KEYS */;
/*!40000 ALTER TABLE `notifications` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `package_prices`
--

DROP TABLE IF EXISTS `package_prices`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `package_prices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `package_id` int(10) unsigned NOT NULL,
  `billing_period_id` int(10) unsigned NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'USD',
  `pricing_model` enum('fixed','prorated','percent_adjustment','fixed_adjustment','combined_adjustment') NOT NULL DEFAULT 'fixed',
  `price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `percent` decimal(6,2) DEFAULT NULL,
  `effective_from` date NOT NULL,
  `effective_to` date DEFAULT NULL,
  `version_no` int(10) unsigned NOT NULL DEFAULT 1,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_package_prices_period` (`billing_period_id`),
  KEY `idx_package_prices_period_currency` (`package_id`,`billing_period_id`,`currency`,`status`),
  CONSTRAINT `fk_package_prices_package` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_package_prices_period` FOREIGN KEY (`billing_period_id`) REFERENCES `billing_periods` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `package_prices`
--

LOCK TABLES `package_prices` WRITE;
/*!40000 ALTER TABLE `package_prices` DISABLE KEYS */;
/*!40000 ALTER TABLE `package_prices` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `packages`
--

DROP TABLE IF EXISTS `packages`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `packages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `package_code` varchar(30) DEFAULT NULL,
  `name_ar` varchar(150) NOT NULL,
  `name_en` varchar(150) NOT NULL,
  `description_ar` text DEFAULT NULL,
  `description_en` text DEFAULT NULL,
  `storage_value` decimal(12,2) DEFAULT NULL,
  `storage_unit` varchar(10) DEFAULT NULL,
  `bandwidth_value` decimal(12,2) DEFAULT NULL,
  `bandwidth_unit` varchar(10) DEFAULT NULL,
  `num_domains` int(10) unsigned DEFAULT NULL,
  `num_email_accounts` int(10) unsigned DEFAULT NULL,
  `num_databases` int(10) unsigned DEFAULT NULL,
  `specs` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`specs`)),
  `base_price_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `domain_included` tinyint(1) NOT NULL DEFAULT 0,
  `domain_optional` tinyint(1) NOT NULL DEFAULT 1,
  `domain_price_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `domain_renewal_price_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_packages_code` (`package_code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `packages`
--

LOCK TABLES `packages` WRITE;
/*!40000 ALTER TABLE `packages` DISABLE KEYS */;
/*!40000 ALTER TABLE `packages` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `payment_methods`
--

DROP TABLE IF EXISTS `payment_methods`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `payment_methods` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name_ar` varchar(150) NOT NULL,
  `name_en` varchar(150) NOT NULL,
  `description_ar` text DEFAULT NULL,
  `description_en` text DEFAULT NULL,
  `instructions_ar` text DEFAULT NULL,
  `instructions_en` text DEFAULT NULL,
  `account_identifier` varchar(150) DEFAULT NULL,
  `account_holder` varchar(150) DEFAULT NULL,
  `qr_image` varchar(255) DEFAULT NULL,
  `type` enum('manual','gateway') NOT NULL DEFAULT 'manual',
  `supports_proof_upload` tinyint(1) NOT NULL DEFAULT 1,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `payment_methods`
--

LOCK TABLES `payment_methods` WRITE;
/*!40000 ALTER TABLE `payment_methods` DISABLE KEYS */;
/*!40000 ALTER TABLE `payment_methods` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `payment_request_status_history`
--

DROP TABLE IF EXISTS `payment_request_status_history`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `payment_request_status_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `payment_request_id` int(10) unsigned NOT NULL,
  `old_status` enum('pending','under_review','approved','rejected','cancelled') DEFAULT NULL,
  `new_status` enum('pending','under_review','approved','rejected','cancelled') NOT NULL,
  `actor_id` int(10) unsigned DEFAULT NULL,
  `note` varchar(500) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_pr_history_request` (`payment_request_id`),
  KEY `fk_pr_history_actor` (`actor_id`),
  CONSTRAINT `fk_pr_history_actor` FOREIGN KEY (`actor_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_pr_history_request` FOREIGN KEY (`payment_request_id`) REFERENCES `payment_requests` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `payment_request_status_history`
--

LOCK TABLES `payment_request_status_history` WRITE;
/*!40000 ALTER TABLE `payment_request_status_history` DISABLE KEYS */;
/*!40000 ALTER TABLE `payment_request_status_history` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `payment_requests`
--

DROP TABLE IF EXISTS `payment_requests`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `payment_requests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `request_number` varchar(30) NOT NULL,
  `client_id` int(10) unsigned NOT NULL,
  `invoice_id` int(10) unsigned DEFAULT NULL,
  `installment_id` int(10) unsigned DEFAULT NULL,
  `subscription_id` int(10) unsigned DEFAULT NULL,
  `payment_method_id` int(10) unsigned NOT NULL,
  `method_name_ar` varchar(150) DEFAULT NULL,
  `method_name_en` varchar(150) DEFAULT NULL,
  `method_instructions_ar` text DEFAULT NULL,
  `method_instructions_en` text DEFAULT NULL,
  `method_account_identifier` varchar(150) DEFAULT NULL,
  `method_proof_required` tinyint(1) NOT NULL DEFAULT 0,
  `amount_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `currency` varchar(10) NOT NULL DEFAULT 'USD',
  `exchange_rate_snapshot` decimal(18,6) DEFAULT NULL,
  `amount_egp` decimal(18,2) DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `proof_file` varchar(255) DEFAULT NULL,
  `status` enum('pending','under_review','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `admin_notes` text DEFAULT NULL,
  `reviewed_by` int(10) unsigned DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `cancelled_by` int(10) unsigned DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `rejection_reason` varchar(500) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_payment_requests_number` (`request_number`),
  KEY `idx_payment_requests_client` (`client_id`),
  KEY `idx_payment_requests_status` (`status`),
  KEY `fk_payment_requests_installment` (`installment_id`),
  KEY `fk_payment_requests_subscription` (`subscription_id`),
  KEY `fk_payment_requests_method` (`payment_method_id`),
  KEY `fk_payment_requests_reviewer` (`reviewed_by`),
  KEY `idx_payment_requests_invoice` (`invoice_id`),
  CONSTRAINT `fk_payment_requests_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `fk_payment_requests_installment` FOREIGN KEY (`installment_id`) REFERENCES `installments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_payment_requests_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_payment_requests_method` FOREIGN KEY (`payment_method_id`) REFERENCES `payment_methods` (`id`),
  CONSTRAINT `fk_payment_requests_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_payment_requests_subscription` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `payment_requests`
--

LOCK TABLES `payment_requests` WRITE;
/*!40000 ALTER TABLE `payment_requests` DISABLE KEYS */;
/*!40000 ALTER TABLE `payment_requests` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `payments`
--

DROP TABLE IF EXISTS `payments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `payments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `payment_number` varchar(30) NOT NULL,
  `client_id` int(10) unsigned NOT NULL,
  `invoice_id` int(10) unsigned DEFAULT NULL,
  `installment_id` int(10) unsigned DEFAULT NULL,
  `subscription_id` int(10) unsigned DEFAULT NULL,
  `payment_method_id` int(10) unsigned DEFAULT NULL,
  `payment_request_id` int(10) unsigned DEFAULT NULL,
  `amount_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `currency` varchar(10) NOT NULL DEFAULT 'USD',
  `exchange_rate_snapshot` decimal(18,6) DEFAULT NULL,
  `amount_egp` decimal(18,2) DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `proof_file` varchar(255) DEFAULT NULL,
  `status` enum('completed','refunded','void') NOT NULL DEFAULT 'completed',
  `paid_at` date NOT NULL,
  `recorded_by` int(10) unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_payments_number` (`payment_number`),
  UNIQUE KEY `uniq_payments_request` (`payment_request_id`),
  KEY `idx_payments_client` (`client_id`),
  KEY `idx_payments_invoice` (`invoice_id`),
  KEY `idx_payments_installment` (`installment_id`),
  KEY `idx_payments_paid_at` (`paid_at`),
  KEY `fk_payments_subscription` (`subscription_id`),
  KEY `fk_payments_method` (`payment_method_id`),
  KEY `fk_payments_recorder` (`recorded_by`),
  CONSTRAINT `fk_payments_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `fk_payments_installment` FOREIGN KEY (`installment_id`) REFERENCES `installments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_payments_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_payments_method` FOREIGN KEY (`payment_method_id`) REFERENCES `payment_methods` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_payments_recorder` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_payments_request` FOREIGN KEY (`payment_request_id`) REFERENCES `payment_requests` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_payments_subscription` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `payments`
--

LOCK TABLES `payments` WRITE;
/*!40000 ALTER TABLE `payments` DISABLE KEYS */;
/*!40000 ALTER TABLE `payments` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `permissions`
--

DROP TABLE IF EXISTS `permissions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `permissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `display_name` varchar(150) NOT NULL,
  `group_name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_permissions_name` (`name`),
  KEY `idx_permissions_group` (`group_name`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `permissions`
--

LOCK TABLES `permissions` WRITE;
/*!40000 ALTER TABLE `permissions` DISABLE KEYS */;
INSERT INTO `permissions` VALUES (1,'users.view','View staff & users','users',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(2,'users.create','Create staff & users','users',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(3,'users.update','Update staff & users','users',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(4,'users.activate','Activate / deactivate','users',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(5,'users.reset_password','Reset user passwords','users',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(6,'users.assign_roles','Assign roles','users',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(7,'clients.view','View customers','clients',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(8,'clients.create','Create customers','clients',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(9,'clients.update','Update customers','clients',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(10,'clients.activate','Activate / deactivate','clients',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(11,'clients.manage_portal','Manage portal access','clients',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(12,'clients.view_notes','View internal notes','clients',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(13,'audit.view','View audit log','audit',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(14,'settings.manage','Manage settings','settings',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(15,'profile.update_own','Update own profile','profile',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(16,'profile.change_password','Change own password','profile',NULL,'2026-10-08 23:52:11','2026-10-08 23:52:11'),(17,'packages.view','View packages','packages',NULL,'2026-10-09 01:22:42','2026-10-09 01:22:42'),(18,'packages.create','Create packages','packages',NULL,'2026-10-09 01:22:42','2026-10-09 01:22:42'),(19,'packages.update','Update packages','packages',NULL,'2026-10-09 01:22:42','2026-10-09 01:22:42'),(20,'packages.activate','Activate / deactivate','packages',NULL,'2026-10-09 01:22:42','2026-10-09 01:22:42'),(21,'packages.manage_prices','Manage package prices','packages',NULL,'2026-10-09 01:22:42','2026-10-09 01:22:42'),(22,'billing_periods.view','View billing periods','billing_periods',NULL,'2026-10-09 01:22:42','2026-10-09 01:22:42'),(23,'billing_periods.create','Create billing periods','billing_periods',NULL,'2026-10-09 01:22:42','2026-10-09 01:22:42'),(24,'billing_periods.update','Update billing periods','billing_periods',NULL,'2026-10-09 01:22:42','2026-10-09 01:22:42'),(25,'billing_periods.activate','Activate / deactivate','billing_periods',NULL,'2026-10-09 01:22:42','2026-10-09 01:22:42'),(26,'subscriptions.view','View subscriptions','subscriptions',NULL,'2026-10-09 11:50:07','2026-10-09 11:50:07'),(27,'subscriptions.create','Create subscriptions','subscriptions',NULL,'2026-10-09 11:50:07','2026-10-09 11:50:07'),(28,'subscriptions.update','Update subscription operational fields','subscriptions',NULL,'2026-10-09 11:50:07','2026-10-09 11:50:07'),(29,'subscriptions.status','Manage subscription lifecycle','subscriptions',NULL,'2026-10-09 11:50:07','2026-10-09 11:50:07'),(30,'invoices.view','View invoices','invoices',NULL,'2026-10-09 13:45:30','2026-10-09 13:45:30'),(31,'invoices.create','Create invoices','invoices',NULL,'2026-10-09 13:45:30','2026-10-09 13:45:30'),(32,'invoices.update','Update invoice notes','invoices',NULL,'2026-10-09 13:45:30','2026-10-09 13:45:30'),(33,'invoices.status','Issue / cancel invoices','invoices',NULL,'2026-10-09 13:45:30','2026-10-09 13:45:30'),(34,'payment_methods.view','View payment methods','payment_methods',NULL,'2026-10-09 17:00:30','2026-10-09 17:00:30'),(35,'payment_methods.create','Create payment methods','payment_methods',NULL,'2026-10-09 17:00:30','2026-10-09 17:00:30'),(36,'payment_methods.update','Update payment methods','payment_methods',NULL,'2026-10-09 17:00:30','2026-10-09 17:00:30'),(37,'payment_methods.activate','Activate / deactivate','payment_methods',NULL,'2026-10-09 17:00:30','2026-10-09 17:00:30'),(38,'payment_requests.view','View payment requests','payment_requests',NULL,'2026-10-09 17:00:30','2026-10-09 17:00:30'),(39,'payment_requests.update','Review payment requests','payment_requests',NULL,'2026-10-09 17:00:30','2026-10-09 17:00:30'),(40,'payment_requests.approve','Approve payment requests','payment_requests',NULL,'2026-10-09 17:00:30','2026-10-09 17:00:30'),(41,'payments.view','View payment ledger','payments',NULL,'2026-10-09 17:00:30','2026-10-09 17:00:30'),(42,'domains.view','View domains','domains',NULL,'2026-10-09 19:02:26','2026-10-09 19:02:26'),(43,'domains.create','Add domains','domains',NULL,'2026-10-09 19:02:26','2026-10-09 19:02:26'),(44,'domains.update','Update domain data','domains',NULL,'2026-10-09 19:02:26','2026-10-09 19:02:26'),(45,'domains.renew','Record / confirm renewals','domains',NULL,'2026-10-09 19:02:26','2026-10-09 19:02:26'),(46,'domains.bill','Issue renewal invoices','domains',NULL,'2026-10-09 19:02:26','2026-10-09 19:02:26'),(47,'domains.view_costs','View company costs','domains',NULL,'2026-10-09 19:02:26','2026-10-09 19:02:26'),(48,'registrars.view','View registrars','registrars',NULL,'2026-10-09 19:02:26','2026-10-09 19:02:26'),(49,'registrars.create','Add registrars','registrars',NULL,'2026-10-09 19:02:26','2026-10-09 19:02:26'),(50,'registrars.update','Update registrars','registrars',NULL,'2026-10-09 19:02:26','2026-10-09 19:02:26'),(51,'registrars.activate','Activate / deactivate','registrars',NULL,'2026-10-09 19:02:26','2026-10-09 19:02:26');
/*!40000 ALTER TABLE `permissions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pricing_rules`
--

DROP TABLE IF EXISTS `pricing_rules`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pricing_rules` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `name_ar` varchar(150) NOT NULL,
  `name_en` varchar(150) NOT NULL,
  `rule_type` enum('discount_percent','discount_fixed','charge_percent','charge_fixed') NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `scope` enum('global','package') NOT NULL DEFAULT 'global',
  `package_id` int(10) unsigned DEFAULT NULL,
  `billing_period_id` int(10) unsigned DEFAULT NULL,
  `starts_at` date DEFAULT NULL,
  `ends_at` date DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_pricing_rules_code` (`code`),
  KEY `idx_pricing_rules_package` (`package_id`),
  KEY `fk_pricing_rules_period` (`billing_period_id`),
  CONSTRAINT `fk_pricing_rules_package` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pricing_rules_period` FOREIGN KEY (`billing_period_id`) REFERENCES `billing_periods` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pricing_rules`
--

LOCK TABLES `pricing_rules` WRITE;
/*!40000 ALTER TABLE `pricing_rules` DISABLE KEYS */;
/*!40000 ALTER TABLE `pricing_rules` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `remember_tokens`
--

DROP TABLE IF EXISTS `remember_tokens`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `remember_tokens` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `selector` char(32) NOT NULL,
  `token_hash` char(64) NOT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `expires_at` datetime NOT NULL,
  `last_used_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_remember_selector` (`selector`),
  KEY `idx_remember_user` (`user_id`),
  KEY `idx_remember_expires` (`expires_at`),
  CONSTRAINT `fk_remember_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `remember_tokens`
--

LOCK TABLES `remember_tokens` WRITE;
/*!40000 ALTER TABLE `remember_tokens` DISABLE KEYS */;
/*!40000 ALTER TABLE `remember_tokens` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `role_permissions`
--

DROP TABLE IF EXISTS `role_permissions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `role_permissions` (
  `role_id` int(10) unsigned NOT NULL,
  `permission_id` int(10) unsigned NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `idx_role_permissions_permission` (`permission_id`),
  CONSTRAINT `fk_role_permissions_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_role_permissions_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `role_permissions`
--

LOCK TABLES `role_permissions` WRITE;
/*!40000 ALTER TABLE `role_permissions` DISABLE KEYS */;
INSERT INTO `role_permissions` VALUES (1,1,'2026-10-08 23:52:11'),(1,2,'2026-10-08 23:52:11'),(1,3,'2026-10-08 23:52:11'),(1,4,'2026-10-08 23:52:11'),(1,5,'2026-10-08 23:52:11'),(1,6,'2026-10-08 23:52:11'),(1,7,'2026-10-08 23:52:11'),(1,8,'2026-10-08 23:52:11'),(1,9,'2026-10-08 23:52:11'),(1,10,'2026-10-08 23:52:11'),(1,11,'2026-10-08 23:52:11'),(1,12,'2026-10-08 23:52:11'),(1,13,'2026-10-08 23:52:11'),(1,14,'2026-10-08 23:52:11'),(1,15,'2026-10-08 23:52:11'),(1,16,'2026-10-08 23:52:11'),(1,17,'2026-10-09 01:22:42'),(1,18,'2026-10-09 01:22:42'),(1,19,'2026-10-09 01:22:42'),(1,20,'2026-10-09 01:22:42'),(1,21,'2026-10-09 01:22:42'),(1,22,'2026-10-09 01:22:42'),(1,23,'2026-10-09 01:22:42'),(1,24,'2026-10-09 01:22:42'),(1,25,'2026-10-09 01:22:42'),(1,26,'2026-10-09 11:50:07'),(1,27,'2026-10-09 11:50:07'),(1,28,'2026-10-09 11:50:07'),(1,29,'2026-10-09 11:50:07'),(1,30,'2026-10-09 13:45:30'),(1,31,'2026-10-09 13:45:30'),(1,32,'2026-10-09 13:45:30'),(1,33,'2026-10-09 13:45:30'),(1,34,'2026-10-09 17:00:30'),(1,35,'2026-10-09 17:00:30'),(1,36,'2026-10-09 17:00:30'),(1,37,'2026-10-09 17:00:30'),(1,38,'2026-10-09 17:00:30'),(1,39,'2026-10-09 17:00:30'),(1,40,'2026-10-09 17:00:30'),(1,41,'2026-10-09 17:00:30'),(1,42,'2026-10-09 19:02:26'),(1,43,'2026-10-09 19:02:26'),(1,44,'2026-10-09 19:02:26'),(1,45,'2026-10-09 19:02:26'),(1,46,'2026-10-09 19:02:26'),(1,47,'2026-10-09 19:02:26'),(1,48,'2026-10-09 19:02:26'),(1,49,'2026-10-09 19:02:26'),(1,50,'2026-10-09 19:02:26'),(1,51,'2026-10-09 19:02:26'),(2,7,'2026-10-08 23:52:11'),(2,9,'2026-10-08 23:52:11'),(2,12,'2026-10-08 23:52:11'),(2,15,'2026-10-08 23:52:11'),(2,16,'2026-10-08 23:52:11'),(2,26,'2026-10-09 11:50:07'),(2,28,'2026-10-09 11:50:07'),(2,30,'2026-10-09 13:45:30'),(2,38,'2026-10-09 17:00:30'),(2,42,'2026-10-09 19:02:26'),(3,15,'2026-10-08 23:52:11'),(3,16,'2026-10-08 23:52:11'),(4,7,'2026-10-08 23:52:11'),(4,8,'2026-10-08 23:52:11'),(4,9,'2026-10-08 23:52:11'),(4,10,'2026-10-08 23:52:11'),(4,11,'2026-10-08 23:52:11'),(4,12,'2026-10-08 23:52:11'),(4,15,'2026-10-08 23:52:11'),(4,16,'2026-10-08 23:52:11'),(4,17,'2026-10-09 01:22:42'),(4,21,'2026-10-09 01:22:42'),(4,22,'2026-10-09 01:22:42'),(4,26,'2026-10-09 11:50:07'),(4,27,'2026-10-09 11:50:07'),(4,28,'2026-10-09 11:50:07'),(4,29,'2026-10-09 11:50:07'),(4,30,'2026-10-09 13:45:30'),(4,31,'2026-10-09 13:45:30'),(4,32,'2026-10-09 13:45:30'),(4,33,'2026-10-09 13:45:30'),(4,34,'2026-10-09 17:00:30'),(4,35,'2026-10-09 17:00:30'),(4,36,'2026-10-09 17:00:30'),(4,37,'2026-10-09 17:00:30'),(4,38,'2026-10-09 17:00:30'),(4,39,'2026-10-09 17:00:30'),(4,40,'2026-10-09 17:00:30'),(4,41,'2026-10-09 17:00:30'),(4,42,'2026-10-09 19:02:26'),(4,43,'2026-10-09 19:02:26'),(4,44,'2026-10-09 19:02:26'),(4,45,'2026-10-09 19:02:26'),(4,46,'2026-10-09 19:02:26'),(4,48,'2026-10-09 19:02:26'),(4,49,'2026-10-09 19:02:26'),(4,50,'2026-10-09 19:02:26'),(4,51,'2026-10-09 19:02:26');
/*!40000 ALTER TABLE `role_permissions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `roles`
--

DROP TABLE IF EXISTS `roles`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `roles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `display_name` varchar(100) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_roles_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `roles`
--

LOCK TABLES `roles` WRITE;
/*!40000 ALTER TABLE `roles` DISABLE KEYS */;
INSERT INTO `roles` VALUES (1,'super_admin','Super Admin',1,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(2,'support','Support',1,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(3,'client','Client',1,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(4,'billing_manager','Billing Manager',1,'2026-10-08 23:52:11','2026-10-08 23:52:11');
/*!40000 ALTER TABLE `roles` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `schema_migrations`
--

DROP TABLE IF EXISTS `schema_migrations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `schema_migrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(191) NOT NULL,
  `ran_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_migration` (`migration`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `schema_migrations`
--

LOCK TABLES `schema_migrations` WRITE;
/*!40000 ALTER TABLE `schema_migrations` DISABLE KEYS */;
INSERT INTO `schema_migrations` VALUES (1,'001_create_roles_and_users','2026-10-08 23:37:10'),(2,'002_create_clients','2026-10-08 23:38:28'),(3,'003_create_packages_and_pricing','2026-10-08 23:38:28'),(4,'004_create_exchange_rates_and_payment_methods','2026-10-08 23:38:28'),(5,'005_create_subscriptions_and_domains','2026-10-08 23:38:28'),(6,'006_create_invoices_and_installments','2026-10-08 23:38:29'),(7,'007_create_payment_requests_and_payments','2026-10-08 23:38:29'),(8,'008_create_settings_and_audit_logs','2026-10-08 23:38:29'),(9,'009_seed_roles_billing_periods_settings','2026-10-08 23:38:29'),(10,'010_create_permissions_and_role_permissions','2026-10-08 23:52:11'),(11,'011_align_roles_and_seed_permissions','2026-10-08 23:52:11'),(12,'012_create_remember_tokens','2026-10-08 23:52:11'),(13,'013_create_login_throttles','2026-10-08 23:52:11'),(14,'014_add_identity_unique_indexes','2026-10-08 23:52:11'),(15,'015_extend_packages_storage_and_domain_specs','2026-10-09 01:22:42'),(16,'016_rework_package_prices_for_versioning','2026-10-09 01:22:42'),(17,'017_add_package_and_billing_period_permissions','2026-10-09 01:22:42'),(18,'018_add_billing_period_codes','2026-10-09 01:31:24'),(19,'019_extend_subscriptions_for_lifecycle','2026-10-09 11:50:07'),(20,'020_add_subscription_permissions','2026-10-09 11:50:07'),(21,'021_extend_invoices_for_billing_period','2026-10-09 13:45:30'),(22,'022_add_invoice_permissions','2026-10-09 13:45:30'),(23,'023_payment_requests_and_payments_hardening','2026-10-09 17:00:30'),(24,'024_add_payment_permissions','2026-10-09 17:00:30'),(25,'025_domain_management','2026-10-09 19:02:26'),(26,'026_add_domain_permissions','2026-10-09 19:02:26'),(27,'027_create_notifications','2026-10-10 21:00:05'),(28,'028_seed_admin_settings','2026-10-12 23:21:34');
/*!40000 ALTER TABLE `schema_migrations` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `settings`
--

DROP TABLE IF EXISTS `settings`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `settings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `setting_type` enum('string','int','bool','json','file') NOT NULL DEFAULT 'string',
  `is_public` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_settings_key` (`setting_key`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `settings`
--

LOCK TABLES `settings` WRITE;
/*!40000 ALTER TABLE `settings` DISABLE KEYS */;
INSERT INTO `settings` VALUES (1,'company_name','Egywhost','string',1,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(2,'company_logo','','file',1,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(3,'default_currency','USD','string',1,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(4,'default_language','ar','string',1,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(5,'timezone','Africa/Cairo','string',1,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(6,'invoice_prefix','INV-','string',0,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(7,'client_code_prefix','CL-','string',0,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(8,'payment_request_prefix','PR-','string',0,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(9,'payment_prefix','PAY-','string',0,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(10,'upload_max_size_kb','5120','int',0,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(11,'payment_request_requires_review','1','bool',0,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(12,'notify_payment_requests','1','bool',0,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(13,'notify_domain_expiry','1','bool',0,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(14,'domain_expiry_warning_days','30','int',0,'2026-10-08 23:38:29','2026-10-08 23:38:29'),(15,'domain_expiry_windows','{\"60\":60,\"30\":30,\"15\":15,\"7\":7}','json',0,'2026-10-09 19:02:26','2026-10-09 19:02:26'),(16,'notify_invoice_issued','1','bool',0,'2026-10-10 21:00:05','2026-10-10 21:00:05'),(17,'notify_invoice_due','1','bool',0,'2026-10-10 21:00:05','2026-10-10 21:00:05'),(18,'notify_invoice_overdue','1','bool',0,'2026-10-10 21:00:05','2026-10-10 21:00:05'),(19,'invoice_due_soon_days','3','int',0,'2026-10-10 21:00:05','2026-10-10 21:00:05'),(20,'company_name_ar','','string',1,'2026-10-12 23:21:34','2026-10-12 23:21:34'),(21,'company_name_en','','string',1,'2026-10-12 23:21:34','2026-10-12 23:21:34'),(22,'company_email','','string',1,'2026-10-12 23:21:34','2026-10-12 23:21:34'),(23,'company_phone','','string',1,'2026-10-12 23:21:34','2026-10-12 23:21:34'),(24,'company_address','','string',1,'2026-10-12 23:21:34','2026-10-12 23:21:34'),(25,'company_website','','string',1,'2026-10-12 23:21:34','2026-10-12 23:21:34'),(26,'app_display_name','','string',1,'2026-10-12 23:21:34','2026-10-12 23:21:34'),(27,'date_format','Y-m-d','string',1,'2026-10-12 23:21:34','2026-10-12 23:21:34'),(28,'records_per_page','25','int',0,'2026-10-12 23:21:34','2026-10-12 23:21:34');
/*!40000 ALTER TABLE `settings` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `subscriptions`
--

DROP TABLE IF EXISTS `subscriptions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `subscriptions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `subscription_code` varchar(40) NOT NULL,
  `client_id` int(10) unsigned NOT NULL,
  `service_type` varchar(30) NOT NULL DEFAULT 'hosting',
  `package_id` int(10) unsigned DEFAULT NULL,
  `billing_period_id` int(10) unsigned DEFAULT NULL,
  `title` varchar(150) NOT NULL,
  `start_date` date NOT NULL,
  `current_period_start` date DEFAULT NULL,
  `current_period_end` date DEFAULT NULL,
  `next_renewal_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `base_price_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `period_price_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `domain_price_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `discount_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `additional_charges_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total_usd` decimal(10,2) NOT NULL DEFAULT 0.00,
  `exchange_rate_id` int(10) unsigned DEFAULT NULL,
  `exchange_rate_snapshot` decimal(18,6) DEFAULT NULL,
  `total_egp` decimal(18,2) DEFAULT NULL,
  `agreed_cycle_price` decimal(10,2) DEFAULT NULL,
  `price_snapshot` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`price_snapshot`)),
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `auto_renew` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('pending','active','suspended','cancellation_pending','cancelled','expired') NOT NULL DEFAULT 'pending',
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `cancellation_requested_at` datetime DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `suspended_at` datetime DEFAULT NULL,
  `suspension_reason` varchar(255) DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_subscriptions_code` (`subscription_code`),
  KEY `idx_subscriptions_client` (`client_id`),
  KEY `idx_subscriptions_status` (`status`),
  KEY `idx_subscriptions_end` (`end_date`),
  KEY `fk_subscriptions_rate` (`exchange_rate_id`),
  KEY `idx_subscriptions_package` (`package_id`),
  KEY `idx_subscriptions_period` (`billing_period_id`),
  KEY `idx_subscriptions_next_renewal` (`next_renewal_date`),
  KEY `fk_subscriptions_created_by` (`created_by`),
  CONSTRAINT `fk_subscriptions_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `fk_subscriptions_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_subscriptions_package` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_subscriptions_period` FOREIGN KEY (`billing_period_id`) REFERENCES `billing_periods` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_subscriptions_rate` FOREIGN KEY (`exchange_rate_id`) REFERENCES `exchange_rates` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `subscriptions`
--

LOCK TABLES `subscriptions` WRITE;
/*!40000 ALTER TABLE `subscriptions` DISABLE KEYS */;
/*!40000 ALTER TABLE `subscriptions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` int(10) unsigned NOT NULL,
  `client_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `password_hash` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `last_login_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_users_username` (`username`),
  UNIQUE KEY `uniq_users_email` (`email`),
  UNIQUE KEY `uniq_users_client` (`client_id`),
  KEY `idx_users_role` (`role_id`),
  CONSTRAINT `fk_users_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping routines for database 'egywhost_crm'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2026-10-14 18:20:04