-- ═══════════════════════════════════════════════
-- Media Serve Platform — Complete Database Schema
-- ═══════════════════════════════════════════════

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

-- ── Settings ──────────────────────────────────
CREATE TABLE IF NOT EXISTS `ms_settings` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `key`        VARCHAR(100) NOT NULL UNIQUE,
  `value`      TEXT,
  `group`      VARCHAR(50) NOT NULL DEFAULT 'general',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `ms_settings` (`key`,`value`,`group`) VALUES
('site_name',       'Media Serve',              'general'),
('site_tagline_ar', 'برمجيات WordPress احترافية','general'),
('site_tagline_en', 'Professional WordPress Software','general'),
('site_email',      'support@mediaserves.com',  'general'),
('site_phone',      '',                          'general'),
('site_address',    '',                          'general'),
('site_logo',       '',                          'general'),
('site_favicon',    '',                          'general'),
('default_lang',    'ar',                        'general'),
('active_theme',    'default',                   'general'),
('meta_desc_ar',    'إضافات WordPress احترافية من Media Serve', 'seo'),
('meta_desc_en',    'Professional WordPress plugins by Media Serve','seo'),
('meta_keywords',   'wordpress,seo,plugins,media serve','seo'),
('google_analytics','',                          'seo'),
('captcha_enabled', '0',                         'security'),
('captcha_site_key','',                          'security'),
('captcha_secret',  '',                          'security'),
('msseo_api_url',   'https://seo.mediaserves.com/api', 'api'),
('currency',        'USD',                       'payment'),
('currency_symbol', '$',                         'payment');

-- ── Users ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS `ms_users` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`           VARCHAR(200) NOT NULL,
  `email`          VARCHAR(255) NOT NULL UNIQUE,
  `password`       VARCHAR(255) NOT NULL,
  `avatar`         VARCHAR(255) DEFAULT NULL,
  `role`           ENUM('admin','member') NOT NULL DEFAULT 'member',
  `status`         ENUM('active','suspended','pending') NOT NULL DEFAULT 'active',
  `email_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `phone`          VARCHAR(50) DEFAULT NULL,
  `bio`            TEXT DEFAULT NULL,
  `reset_token`    VARCHAR(100) DEFAULT NULL,
  `reset_expires`  TIMESTAMP NULL DEFAULT NULL,
  `last_login`     TIMESTAMP NULL DEFAULT NULL,
  `created_at`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (`role`), INDEX (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default admin
INSERT INTO `ms_users` (`name`,`email`,`password`,`role`,`email_verified`,`status`) VALUES
('Admin','admin@mediaserves.com','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','admin',1,'active');
-- password: "password"

-- ── Sessions ──────────────────────────────────
CREATE TABLE IF NOT EXISTS `ms_sessions` (
  `id`           VARCHAR(255) PRIMARY KEY,
  `user_id`      INT UNSIGNED NULL,
  `ip_address`   VARCHAR(45) NULL,
  `user_agent`   TEXT NULL,
  `payload`      LONGTEXT NOT NULL,
  `last_activity`INT NOT NULL,
  INDEX (`user_id`), INDEX (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Cache ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS `ms_cache` (
  `key`       VARCHAR(255) PRIMARY KEY,
  `value`     MEDIUMTEXT NOT NULL,
  `expiration`INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ms_cache_locks` (
  `key`       VARCHAR(255) PRIMARY KEY,
  `owner`     VARCHAR(255) NOT NULL,
  `expiration`INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Plans / Subscriptions ─────────────────────
CREATE TABLE IF NOT EXISTS `ms_plans` (
  `id`         VARCHAR(50) PRIMARY KEY,
  `name`       VARCHAR(100) NOT NULL,
  `price`      DECIMAL(10,2) NOT NULL DEFAULT 0,
  `sites`      INT NOT NULL DEFAULT 1,
  `days`       INT NOT NULL DEFAULT 0,
  `features`   TEXT DEFAULT NULL,
  `active`     TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `ms_plans` VALUES
('free',      'مجاني',       0,   1, 0,   NULL, 1, 0),
('monthly',   'شهري',        29,  1, 30,  NULL, 1, 1),
('quarterly', 'ربع سنوي',   69,  1, 90,  NULL, 1, 2),
('yearly',    'سنوي',        199, 3, 365, NULL, 1, 3),
('lifetime',  'مدى الحياة', 499, 5, 0,   NULL, 1, 4);

-- ── Member Subscriptions ──────────────────────
CREATE TABLE IF NOT EXISTS `ms_subscriptions` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`        INT UNSIGNED NOT NULL,
  `plan_id`        VARCHAR(50) NOT NULL,
  `status`         ENUM('active','expired','suspended') NOT NULL DEFAULT 'active',
  `license_key`    VARCHAR(100) DEFAULT NULL UNIQUE,
  `sites_allowed`  INT NOT NULL DEFAULT 1,
  `expires_at`     DATE DEFAULT NULL,
  `created_at`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `ms_users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`plan_id`) REFERENCES `ms_plans`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Products ──────────────────────────────────
CREATE TABLE IF NOT EXISTS `ms_products` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `slug`           VARCHAR(100) NOT NULL UNIQUE,
  `name`           VARCHAR(200) NOT NULL,
  `tagline_ar`     VARCHAR(255) NOT NULL DEFAULT '',
  `tagline_en`     VARCHAR(255) NOT NULL DEFAULT '',
  `desc_ar`        TEXT NOT NULL,
  `desc_en`        TEXT NOT NULL,
  `icon`           VARCHAR(10)  NOT NULL DEFAULT '📦',
  `category`       VARCHAR(100) NOT NULL DEFAULT 'WordPress Plugin',
  `color`          VARCHAR(20)  NOT NULL DEFAULT '#2563eb',
  `badge`          VARCHAR(50)  NOT NULL DEFAULT '',
  `is_free`        TINYINT(1)   NOT NULL DEFAULT 1,
  `starting_price` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `min_plan`       VARCHAR(50)  NOT NULL DEFAULT 'free',
  `features_ar`    TEXT,
  `features_en`    TEXT,
  `faqs_ar`        TEXT,
  `faqs_en`        TEXT,
  `changelog`      TEXT,
  `version`        VARCHAR(20) NOT NULL DEFAULT '1.0.0',
  `wp_requires`    VARCHAR(10) DEFAULT '6.0',
  `php_requires`   VARCHAR(10) DEFAULT '7.4',
  `download_file`  VARCHAR(255) DEFAULT NULL,
  `sort_order`     INT          NOT NULL DEFAULT 0,
  `is_active`      TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at`     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`min_plan`) REFERENCES `ms_plans`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `ms_products` (`slug`,`name`,`tagline_ar`,`tagline_en`,`desc_ar`,`desc_en`,`icon`,`category`,`color`,`badge`,`is_free`,`starting_price`,`min_plan`,`features_ar`,`features_en`,`version`,`sort_order`) VALUES
('ms-seo-pro','MS SEO Pro','إضافة SEO احترافية لـ WordPress','Professional WordPress SEO Plugin','إضافة SEO متكاملة تحتوي على Meta Tags، Sitemap، Schema، تحليل المحتوى، Redirects وأكثر من 20 ميزة.','Complete SEO plugin with Meta Tags, Sitemap, Schema, Content Analysis, Redirects and 20+ features.','🔍','WordPress Plugin','#2563eb','الأكثر مبيعاً',1,29.00,'free','Meta Tags ديناميكية|XML Sitemap ذكي|Schema Markup|تحليل المحتوى|Redirects Manager|استيراد من Yoast','Dynamic Meta Tags|Smart XML Sitemap|Full Schema Markup|Content Analysis|Redirects Manager|Import from Yoast','4.0.0',1),
('ms-seo-elementor-widgets','MS SEO Elementor Widgets','Widgets احترافية لـ Elementor','Professional Elementor Widgets','مجموعة Widgets احترافية لـ Elementor — Hero، Pricing، Features، FAQ، Stats.','Professional Elementor widgets — Hero, Pricing, Features, FAQ, Stats.','⚡','Elementor Plugin','#7c3aed','مجاني',1,0,'free','11 Widget احترافي|لوحة تحكم العضو|Hero Section|Pricing Table|FAQ Accordion','11 Professional Widgets|User Dashboard|Hero Section|Pricing Table|FAQ Accordion','2.0.0',2),
('wp-ai-livechat','WP AI LiveChat','دردشة مباشرة بالذكاء الاصطناعي','AI-Powered Live Chat Plugin','إضافة دردشة تعمل بالذكاء الاصطناعي — Gemini، GPT، Groq.','AI live chat plugin — Gemini, GPT, Groq with human agent handoff.','💬','WordPress Plugin','#16a34a','جديد',0,49.00,'monthly','دعم Gemini & GPT & Groq|تحويل للوكيل البشري|إشعارات Firebase|تطبيق Flutter','Gemini & GPT & Groq|Human Agent Handoff|Firebase Notifications|Flutter App','1.0.0',3),
('ai-news-autopilot','AI News Autopilot','نشر الأخبار تلقائياً','Auto-publish news with AI','جمع الأخبار من RSS وإعادة كتابتها بالذكاء الاصطناعي ونشرها تلقائياً.','Collect news from RSS, rewrite with AI, auto-publish.','📰','WordPress Plugin','#dc2626','Premium',0,39.00,'monthly','مصادر RSS غير محدودة|إعادة كتابة بالذكاء الاصطناعي|جدولة النشر|كشف التكرار','Unlimited RSS Sources|AI Rewriting|Publish Scheduling|Duplicate Detection','2.0.0',4);

-- ── Downloads Log ──────────────────────────────
CREATE TABLE IF NOT EXISTS `ms_downloads` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`    INT UNSIGNED NOT NULL,
  `product_id` INT UNSIGNED NOT NULL,
  `version`    VARCHAR(20) NOT NULL,
  `ip`         VARCHAR(45) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`)    REFERENCES `ms_users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `ms_products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Support Tickets ────────────────────────────
CREATE TABLE IF NOT EXISTS `ms_tickets` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`    INT UNSIGNED NOT NULL,
  `subject`    VARCHAR(255) NOT NULL,
  `message`    TEXT NOT NULL,
  `reply`      TEXT DEFAULT NULL,
  `status`     ENUM('open','replied','closed') NOT NULL DEFAULT 'open',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `ms_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Languages ──────────────────────────────────
CREATE TABLE IF NOT EXISTS `ms_languages` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code`       VARCHAR(10) NOT NULL UNIQUE,
  `name`       VARCHAR(100) NOT NULL,
  `native`     VARCHAR(100) NOT NULL,
  `flag`       VARCHAR(10) NOT NULL DEFAULT '🌐',
  `rtl`        TINYINT(1) NOT NULL DEFAULT 0,
  `is_active`  TINYINT(1) NOT NULL DEFAULT 1,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order` INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `ms_languages` VALUES
(1,'ar','Arabic','العربية','🇸🇦',1,1,1,0),
(2,'en','English','English','🇺🇸',0,1,0,1);

SET FOREIGN_KEY_CHECKS = 1;

-- ── Theme Settings ─────────────────────────────
INSERT INTO `ms_settings` (`key`,`value`,`group`) VALUES
-- Colors
('theme_primary',        '#2563eb',  'theme'),
('theme_primary_dark',   '#1d4ed8',  'theme'),
('theme_accent',         '#7c3aed',  'theme'),
('theme_success',        '#16a34a',  'theme'),
('theme_danger',         '#dc2626',  'theme'),
-- Hero
('hero_enabled',         '1',        'hero'),
('hero_bg_from',         '#0f172a',  'hero'),
('hero_bg_mid',          '#1e3a8a',  'hero'),
('hero_bg_to',           '#2563eb',  'hero'),
('hero_title_ar',        'اجعل موقعك',    'hero'),
('hero_title_en',        'Make Your Website', 'hero'),
('hero_words_ar',        'يتصدر البحث|ينمو بسرعة|يحقق أهدافك', 'hero'),
('hero_words_en',        'Rank Higher|Grow Faster|Achieve Goals', 'hero'),
('hero_desc_ar',         'إضافات WordPress احترافية للـ SEO، الدردشة الذكية، نشر الأخبار التلقائي وأكثر', 'hero'),
('hero_desc_en',         'Professional WordPress plugins for SEO, AI Chat, Auto News Publishing and more', 'hero'),
('hero_btn1_ar',         'عرض المنتجات', 'hero'),
('hero_btn1_en',         'View Products', 'hero'),
('hero_btn2_ar',         'ابدأ مجاناً',  'hero'),
('hero_btn2_en',         'Start Free',    'hero'),
('hero_particles',       '1',        'hero'),
-- Sections visibility
('section_products',     '1',        'sections'),
('section_pricing',      '1',        'sections'),
('section_why',          '1',        'sections'),
('section_cta',          '1',        'sections'),
('section_stats',        '1',        'sections'),
-- CTA Section
('cta_title_ar',         'جاهز للبدء اليوم؟',    'cta'),
('cta_title_en',         'Ready to Get Started?', 'cta'),
('cta_desc_ar',          'انضم لآلاف المواقع التي تستخدم منتجاتنا', 'cta'),
('cta_desc_en',          'Join thousands of websites using our products', 'cta'),
-- Typography
('font_family_ar',       'Cairo',    'typography'),
('font_family_en',       'Inter',    'typography'),
('font_size_base',       '16',       'typography'),
-- Sitemap
('sitemap_changefreq',   'weekly',   'sitemap'),
('sitemap_priority_home','1.0',      'sitemap'),
('sitemap_auto_ping',    '1',        'sitemap'),
('sitemap_include_products','1',     'sitemap'),
('sitemap_include_pages','1',        'sitemap'),
('sitemap_last_generated','',        'sitemap')
ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);

-- ── Sitemap Log ────────────────────────────────
CREATE TABLE IF NOT EXISTS `ms_sitemap_log` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `urls_count` INT NOT NULL DEFAULT 0,
  `file_size`  INT NOT NULL DEFAULT 0,
  `pinged`     TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── License Sites (Active Activations) ────────
CREATE TABLE IF NOT EXISTS `ms_license_sites` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `license_key`  VARCHAR(100) NOT NULL,
  `site_url`     VARCHAR(255) NOT NULL,
  `plugin`       VARCHAR(100) NOT NULL DEFAULT 'ms-seo-pro',
  `activated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `last_check`   TIMESTAMP NULL DEFAULT NULL,
  UNIQUE KEY `unique_site` (`license_key`,`site_url`,`plugin`),
  INDEX (`license_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;