-- =========================================================
-- قاعدة بيانات: نظام حسابات الاستضافة (نسخة CodeIgniter 4)
-- استيراد مباشر:  mysql -u root -p hosting_erp < database.sql
-- =========================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `companies` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(150) DEFAULT NULL,
  `is_site_enabled` tinyint(1) NOT NULL DEFAULT '0',
  `site_primary_color` varchar(20) NOT NULL DEFAULT '#1e1e2d',
  `site_font` varchar(50) NOT NULL DEFAULT 'Tajawal',
  `site_tagline` varchar(255) DEFAULT NULL,
  `site_about_text` text,
  `site_vision` text,
  `site_mission` text,
  `site_services_text` text,
  `site_footer_text` text,
  `site_facebook_url` varchar(255) DEFAULT NULL,
  `site_twitter_url` varchar(255) DEFAULT NULL,
  `site_instagram_url` varchar(255) DEFAULT NULL,
  `site_whatsapp_number` varchar(30) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `invoice_header_note` text,
  `invoice_footer_note` text,
  `base_currency` varchar(3) NOT NULL DEFAULT 'USD',
  `tax_number` varchar(255) DEFAULT NULL,
  `address` text,
  `phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `owner_user_id` bigint unsigned DEFAULT NULL,
  `notify_before_days_default` tinyint unsigned NOT NULL DEFAULT '7',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `companies_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned DEFAULT NULL,
  `is_super_admin` tinyint(1) NOT NULL DEFAULT '0',
  `preferred_locale` varchar(2) NOT NULL DEFAULT 'ar',
  `expires_at` date DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_company_id_foreign` (`company_id`),
  CONSTRAINT `users_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `marketers` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `commission_type` enum('percentage','fixed') NOT NULL DEFAULT 'percentage',
  `commission_value` decimal(12,2) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `marketers_company_id_foreign` (`company_id`),
  CONSTRAINT `marketers_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `clients` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `marketer_id` bigint unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `address` text,
  `password` varchar(255) DEFAULT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `preferred_locale` varchar(2) NOT NULL DEFAULT 'ar',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clients_company_id_email_unique` (`company_id`,`email`),
  KEY `clients_marketer_id_foreign` (`marketer_id`),
  CONSTRAINT `clients_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clients_marketer_id_foreign` FOREIGN KEY (`marketer_id`) REFERENCES `marketers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `services` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `client_id` bigint unsigned NOT NULL,
  `type` enum('hosting','domain','other') NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` decimal(12,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `billing_cycle` enum('monthly','quarterly','semi_annual','annual') NOT NULL DEFAULT 'annual',
  `start_date` date NOT NULL,
  `renewal_date` date NOT NULL,
  `status` enum('active','expired','cancelled','suspended') NOT NULL DEFAULT 'active',
  `notes` text,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `services_company_id_foreign` (`company_id`),
  KEY `services_client_id_foreign` (`client_id`),
  KEY `services_renewal_date_index` (`renewal_date`),
  CONSTRAINT `services_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `services_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `invoices` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `client_id` bigint unsigned NOT NULL,
  `created_by` bigint unsigned DEFAULT NULL,
  `invoice_number` varchar(255) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `exchange_rate_at_creation` decimal(12,6) NOT NULL DEFAULT '1.000000',
  `issue_date` date NOT NULL,
  `due_date` date NOT NULL,
  `status` enum('draft','sent','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
  `sent_at` datetime DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `notes` text,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoices_invoice_number_unique` (`invoice_number`),
  KEY `invoices_company_id_foreign` (`company_id`),
  KEY `invoices_client_id_foreign` (`client_id`),
  KEY `invoices_created_by_foreign` (`created_by`),
  KEY `invoices_due_date_index` (`due_date`),
  CONSTRAINT `invoices_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `invoices_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `invoices_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `products` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `cost_price` decimal(12,2) NOT NULL DEFAULT '0.00',
  `price` decimal(12,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `products_company_id_foreign` (`company_id`),
  CONSTRAINT `products_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `invoice_items` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` bigint unsigned NOT NULL,
  `service_id` bigint unsigned DEFAULT NULL,
  `product_id` bigint unsigned DEFAULT NULL,
  `description` varchar(255) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `invoice_items_invoice_id_foreign` (`invoice_id`),
  KEY `invoice_items_service_id_foreign` (`service_id`),
  KEY `invoice_items_product_id_foreign` (`product_id`),
  CONSTRAINT `invoice_items_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `invoice_items_service_id_foreign` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE SET NULL,
  CONSTRAINT `invoice_items_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `payments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` bigint unsigned NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `payment_method` varchar(255) DEFAULT NULL,
  `status` enum('completed','pending') NOT NULL DEFAULT 'completed',
  `reference` varchar(255) DEFAULT NULL,
  `paid_at` date NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `payments_invoice_id_foreign` (`invoice_id`),
  CONSTRAINT `payments_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `quotes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `client_id` bigint unsigned NOT NULL,
  `created_by` bigint unsigned DEFAULT NULL,
  `quote_number` varchar(255) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `issue_date` date NOT NULL,
  `valid_until` date NOT NULL,
  `status` enum('draft','sent','accepted','rejected','converted') NOT NULL DEFAULT 'draft',
  `converted_invoice_id` bigint unsigned DEFAULT NULL,
  `notes` text,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `quotes_quote_number_unique` (`quote_number`),
  KEY `quotes_company_id_foreign` (`company_id`),
  KEY `quotes_client_id_foreign` (`client_id`),
  CONSTRAINT `quotes_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `quotes_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `quote_items` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `quote_id` bigint unsigned NOT NULL,
  `product_id` bigint unsigned DEFAULT NULL,
  `description` varchar(255) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `quote_items_quote_id_foreign` (`quote_id`),
  KEY `quote_items_product_id_foreign` (`product_id`),
  CONSTRAINT `quote_items_quote_id_foreign` FOREIGN KEY (`quote_id`) REFERENCES `quotes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `quote_items_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `expenses` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `category` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `date` date NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `expenses_company_id_foreign` (`company_id`),
  CONSTRAINT `expenses_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `commissions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `marketer_id` bigint unsigned NOT NULL,
  `invoice_id` bigint unsigned NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `status` enum('pending','paid') NOT NULL DEFAULT 'pending',
  `paid_at` date DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `commissions_marketer_id_foreign` (`marketer_id`),
  KEY `commissions_invoice_id_foreign` (`invoice_id`),
  CONSTRAINT `commissions_marketer_id_foreign` FOREIGN KEY (`marketer_id`) REFERENCES `marketers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `commissions_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `exchange_rates` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `currency_from` varchar(3) NOT NULL,
  `currency_to` varchar(3) NOT NULL,
  `rate` decimal(12,6) NOT NULL,
  `date` date NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `exchange_rates_currency_from_currency_to_date_unique` (`currency_from`,`currency_to`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `notification_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `type` varchar(255) NOT NULL,
  `related_id` bigint unsigned NOT NULL,
  `channel` varchar(255) NOT NULL DEFAULT 'mail',
  `sent_at` datetime NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `notification_logs_company_id_foreign` (`company_id`),
  CONSTRAINT `notification_logs_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `notification_settings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `renewal_days_before` varchar(255) NOT NULL DEFAULT '15,7,3,1',
  `invoice_due_days_before` varchar(255) NOT NULL DEFAULT '15,7,3,1',
  `admin_notification_email` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `notification_settings_company_id_foreign` (`company_id`),
  CONSTRAINT `notification_settings_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `currencies` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `code` varchar(3) NOT NULL,
  `name` varchar(100) NOT NULL,
  `symbol` varchar(10) DEFAULT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `currencies_company_id_code_unique` (`company_id`,`code`),
  CONSTRAINT `currencies_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `activity_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned DEFAULT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `description` text,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `activity_logs_company_id_index` (`company_id`),
  KEY `activity_logs_user_id_index` (`user_id`),
  KEY `activity_logs_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `trusted_devices` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `token` varchar(100) NOT NULL,
  `device_label` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `last_used_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `trusted_devices_token_unique` (`token`),
  KEY `trusted_devices_user_id_index` (`user_id`),
  CONSTRAINT `trusted_devices_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `login_otps` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `code` varchar(6) NOT NULL,
  `is_used` tinyint(1) NOT NULL DEFAULT '0',
  `expires_at` datetime NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `login_otps_user_id_index` (`user_id`),
  CONSTRAINT `login_otps_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `site_slides` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `image` varchar(255) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `subtitle` varchar(255) DEFAULT NULL,
  `sort_order` int NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `site_slides_company_id_foreign` (`company_id`),
  CONSTRAINT `site_slides_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `site_testimonials` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `client_name` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `rating` tinyint NOT NULL DEFAULT '5',
  `photo` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `site_testimonials_company_id_foreign` (`company_id`),
  CONSTRAINT `site_testimonials_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `contact_messages` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contact_messages_company_id_foreign` (`company_id`),
  CONSTRAINT `contact_messages_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================
-- بيانات تجريبية (كلمة المرور لجميع الحسابات: password)
-- =========================================================

INSERT INTO `companies` (`id`,`name`,`slug`,`is_site_enabled`,`site_tagline`,`site_about_text`,`site_vision`,`site_mission`,`site_services_text`,`base_currency`,`email`,`phone`,`notify_before_days_default`,`is_active`,`created_at`,`updated_at`) VALUES
(1,'شركة الاستضافة النموذجية','example-hosting',1,'شريكك الموثوق في عالم الاستضافة والنطاقات','شركة الاستضافة النموذجية بدأت رحلتها بهدف تقديم خدمات استضافة مواقع ونطاقات موثوقة وبأسعار منافسة لعملائنا في المنطقة العربية.','أن نكون الخيار الأول لأصحاب الأعمال الرقمية في المنطقة.','تقديم خدمات استضافة مستقرة وسريعة مع دعم فني متاح دائماً.','نقدم باقات استضافة متنوعة، تسجيل وتجديد النطاقات، وشهادات الأمان SSL بأفضل الأسعار.','USD','info@example-hosting.test','+20 100 000 0000',7,1,NOW(),NOW());

INSERT INTO `users` (`id`,`company_id`,`is_super_admin`,`preferred_locale`,`name`,`email`,`password`,`created_at`,`updated_at`) VALUES
(1,NULL,1,'ar','Super Admin','super@admin.test','$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',NOW(),NOW()),
(2,1,0,'ar','مدير الشركة','admin@example-hosting.test','$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',NOW(),NOW());

INSERT INTO `marketers` (`id`,`company_id`,`name`,`email`,`commission_type`,`commission_value`,`is_active`,`created_at`,`updated_at`) VALUES
(1,1,'أحمد المسوّق','marketer@example-hosting.test','percentage',10.00,1,NOW(),NOW());

INSERT INTO `clients` (`id`,`company_id`,`marketer_id`,`name`,`email`,`phone`,`password`,`preferred_locale`,`is_active`,`created_at`,`updated_at`) VALUES
(1,1,1,'محمد عبدالله','client1@example.test','+20 111 111 1111','$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','ar',1,NOW(),NOW()),
(2,1,NULL,'Sara Ahmed','client2@example.test','+20 122 222 2222','$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','en',1,NOW(),NOW());

INSERT INTO `services` (`id`,`company_id`,`client_id`,`type`,`name`,`price`,`currency`,`billing_cycle`,`start_date`,`renewal_date`,`status`,`created_at`,`updated_at`) VALUES
(1,1,1,'hosting','باقة استضافة 10GB',60.00,'USD','annual',DATE_SUB(CURDATE(), INTERVAL 11 MONTH),DATE_ADD(CURDATE(), INTERVAL 5 DAY),'active',NOW(),NOW()),
(2,1,2,'domain','example.com',15.00,'USD','annual',DATE_SUB(CURDATE(), INTERVAL 10 MONTH),DATE_ADD(CURDATE(), INTERVAL 20 DAY),'active',NOW(),NOW());

INSERT INTO `invoices` (`id`,`company_id`,`client_id`,`invoice_number`,`amount`,`currency`,`exchange_rate_at_creation`,`issue_date`,`due_date`,`status`,`sent_at`,`created_at`,`updated_at`) VALUES
(1,1,1,'INV-1-2026-0001',60.00,'USD',1,CURDATE(),DATE_ADD(CURDATE(), INTERVAL 7 DAY),'sent',NOW(),NOW(),NOW());

INSERT INTO `invoice_items` (`invoice_id`,`service_id`,`description`,`amount`,`created_at`,`updated_at`) VALUES
(1,1,'تجديد باقة استضافة 10GB لمدة سنة',60.00,NOW(),NOW());

INSERT INTO `expenses` (`company_id`,`category`,`description`,`amount`,`currency`,`date`,`created_at`,`updated_at`) VALUES
(1,'سيرفرات','اشتراك سيرفر VPS شهري',20.00,'USD',CURDATE(),NOW(),NOW());

INSERT INTO `products` (`company_id`,`name`,`description`,`price`,`currency`,`is_active`,`created_at`,`updated_at`) VALUES
(1,'شهادة SSL','شهادة أمان SSL لمدة سنة',15.00,'USD',1,NOW(),NOW()),
(1,'رسوم تركيب','رسوم تركيب وإعداد أولي',10.00,'USD',1,NOW(),NOW()),
(1,'خصوصية النطاق','حماية بيانات مالك النطاق (WHOIS Privacy)',8.00,'USD',1,NOW(),NOW());

INSERT INTO `exchange_rates` (`currency_from`,`currency_to`,`rate`,`date`,`created_at`,`updated_at`) VALUES
('USD','EGP',48.500000,CURDATE(),NOW(),NOW());

INSERT INTO `notification_settings` (`company_id`,`renewal_days_before`,`invoice_due_days_before`,`admin_notification_email`,`created_at`,`updated_at`) VALUES
(1,'15,7,3,1','15,7,3,1','info@example-hosting.test',NOW(),NOW());

INSERT INTO `currencies` (`company_id`,`code`,`name`,`symbol`,`is_default`,`is_active`,`created_at`,`updated_at`) VALUES
(1,'USD','دولار أمريكي','$',1,1,NOW(),NOW()),
(1,'EGP','جنيه مصري','ج.م',0,1,NOW(),NOW()),
(1,'SAR','ريال سعودي','ر.س',0,1,NOW(),NOW()),
(1,'EUR','يورو','€',0,1,NOW(),NOW());

SET FOREIGN_KEY_CHECKS = 1;

-- حسابات الدخول (كلمة المرور: password)
-- سوبر أدمن: super@admin.test | مدير شركة: admin@example-hosting.test | عميل: client1@example.test
