-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: distribution build
-- Server version: compatible MariaDB/MySQL
-- PHP Version: compatible PHP 8.x

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!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 */;

--
-- Database: `app_database`
--

-- --------------------------------------------------------

--
-- Table structure for table `admins`
--

CREATE TABLE IF NOT EXISTS `admins` (
  `id` int(10) UNSIGNED NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `role` enum('owner','manager','confirmer','shipping','accountant') NOT NULL DEFAULT 'owner' COMMENT 'owner=مالك | manager=مدير | confirmer=تأكيد | shipping=شحن | accountant=محاسب',
  `last_login_at` datetime DEFAULT NULL,
  `last_seen_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `admins`
--


-- --------------------------------------------------------

--
-- Table structure for table `admin_notifications`
--

CREATE TABLE IF NOT EXISTS `admin_notifications` (
  `id` int(11) NOT NULL,
  `type` varchar(50) NOT NULL,
  `reference_id` int(11) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `archived` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `admin_password_resets`
--

CREATE TABLE IF NOT EXISTS `admin_password_resets` (
  `id` int(10) UNSIGNED NOT NULL,
  `email` varchar(191) NOT NULL COMMENT 'Admin email requesting reset',
  `token_hash` char(64) NOT NULL COMMENT 'SHA-256 hash of reset token',
  `expires_at` datetime NOT NULL COMMENT 'Token expiration time (now + 30 minutes)',
  `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Request creation time',
  `ip_address` varchar(45) DEFAULT NULL COMMENT 'Requester IP (IPv4 / IPv6)',
  `user_agent` varchar(255) DEFAULT NULL COMMENT 'Requester User Agent'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `admin_password_resets`
--


-- --------------------------------------------------------

--
-- Table structure for table `ai_settings`
--

CREATE TABLE IF NOT EXISTS `ai_settings` (
  `id` int(11) NOT NULL,
  `provider` varchar(50) NOT NULL,
  `api_key` text NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 0,
  `dialect` varchar(50) NOT NULL,
  `model` varchar(191) NOT NULL,
  `auto_seo_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `image_generation_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `image_model` varchar(191) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `ai_settings`
--


-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `categories`
--


-- --------------------------------------------------------

--
-- Table structure for table `footer_links`
--

CREATE TABLE IF NOT EXISTS `footer_links` (
  `id` int(10) UNSIGNED NOT NULL,
  `footer_section_id` int(10) UNSIGNED NOT NULL,
  `label` varchar(150) NOT NULL,
  `type` enum('page','custom') NOT NULL DEFAULT 'custom',
  `page_id` int(10) UNSIGNED DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `position` int(11) NOT NULL DEFAULT 0,
  `is_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `footer_links`
--


-- --------------------------------------------------------

--
-- Table structure for table `footer_sections`
--

CREATE TABLE IF NOT EXISTS `footer_sections` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(150) NOT NULL,
  `position` int(11) NOT NULL DEFAULT 0,
  `is_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `type` varchar(20) NOT NULL DEFAULT 'links',
  `status` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `footer_sections`
--


-- --------------------------------------------------------

--
-- Table structure for table `home_sections`
--

CREATE TABLE IF NOT EXISTS `home_sections` (
  `id` int(10) UNSIGNED NOT NULL,
  `section_type` varchar(100) NOT NULL,
  `section_order` int(11) NOT NULL DEFAULT 0,
  `is_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`settings`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `home_sections`
--


-- --------------------------------------------------------

--
-- Table structure for table `languages`
--

CREATE TABLE IF NOT EXISTS `languages` (
  `id` int(10) UNSIGNED NOT NULL,
  `code` varchar(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `direction` enum('ltr','rtl') NOT NULL DEFAULT 'ltr',
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `orders`
--

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `address` text NOT NULL,
  `payment_method` varchar(50) NOT NULL,
  `payment_status` varchar(50) NOT NULL,
  `order_status` varchar(50) NOT NULL,
  `total_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `extra` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`extra`)),
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `phone2` varchar(20) DEFAULT NULL,
  `delivery_type` enum('home','desk') NOT NULL DEFAULT 'home',
  `shipping_provider` varchar(50) DEFAULT NULL,
  `tracking_number` varchar(100) DEFAULT NULL,
  `subtotal` decimal(10,2) NOT NULL DEFAULT 0.00,
  `shipping_cost` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total` decimal(10,2) NOT NULL DEFAULT 0.00,
  `ip_address` varchar(45) DEFAULT NULL,
  `flag_review` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1 = طلب مشبوه يحتاج مراجعة',
  `cancel_reason` varchar(255) DEFAULT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'pending' COMMENT 'pending|processing|shipped|delivered|returned|cancelled',
  `fraud_score` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
  `fraud_reason` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `pages`
--

CREATE TABLE IF NOT EXISTS `pages` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text DEFAULT NULL,
  `slug` varchar(255) NOT NULL,
  `content` longtext NOT NULL,
  `status` enum('draft','published') DEFAULT 'draft',
  `template` varchar(100) DEFAULT 'default',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `show_in_menu` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `pages`
--


-- --------------------------------------------------------

--
-- Table structure for table `page_layouts`
--

CREATE TABLE IF NOT EXISTS `page_layouts` (
  `id` int(11) NOT NULL,
  `context_type` varchar(50) NOT NULL,
  `context_id` int(11) DEFAULT NULL,
  `layout` longtext NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `page_views`
--

CREATE TABLE IF NOT EXISTS `page_views` (
  `id` int(10) UNSIGNED NOT NULL,
  `session_id` varchar(255) NOT NULL,
  `ip` varchar(64) NOT NULL,
  `user_agent` text DEFAULT NULL,
  `page_url` text NOT NULL,
  `visited_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- --------------------------------------------------------

--
-- Table structure for table `plugins`
--

CREATE TABLE IF NOT EXISTS `plugins` (
  `id` int(10) UNSIGNED NOT NULL,
  `slug` varchar(64) NOT NULL,
  `name` varchar(190) NOT NULL,
  `version` varchar(32) NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 0,
  `installed_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `plugins`
--


-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text DEFAULT NULL,
  `og_title` varchar(255) DEFAULT NULL,
  `og_description` text DEFAULT NULL,
  `og_image` varchar(255) DEFAULT NULL,
  `schema_brand` varchar(190) DEFAULT NULL,
  `schema_gtin` varchar(100) DEFAULT NULL,
  `schema_mpn` varchar(100) DEFAULT NULL,
  `slug` varchar(255) NOT NULL,
  `short_description` text DEFAULT NULL,
  `description` longtext DEFAULT NULL,
  `landing_content` longtext DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `sale_price` decimal(10,2) DEFAULT NULL,
  `discount_type` enum('fixed','percent') DEFAULT NULL,
  `discount_value` decimal(10,2) DEFAULT NULL,
  `discount_start` datetime DEFAULT NULL,
  `discount_end` datetime DEFAULT NULL,
  `currency` varchar(10) DEFAULT 'DZD',
  `sku` varchar(100) DEFAULT NULL,
  `barcode` varchar(100) DEFAULT NULL,
  `stock_quantity` int(11) DEFAULT 0,
  `stock_alert` int(11) DEFAULT 5 COMMENT 'الحد الأدنى للتنبيه بنفاد المخزون',
  `images` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`images`)),
  `featured_image` varchar(255) DEFAULT NULL,
  `variants` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`variants`)),
  `attributes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`attributes`)),
  `status` enum('inactive','active') DEFAULT 'active',
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `badge_text` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `layout` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`layout`)),
  `offers` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`offers`)),
  `stock` int(11) DEFAULT NULL COMMENT 'NULL = لا يتتبع المخزون، رقم = الكمية المتاحة'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `products`
--


-- --------------------------------------------------------

--
-- Table structure for table `product_categories`
--

CREATE TABLE IF NOT EXISTS `product_categories` (
  `product_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `product_categories`
--


-- --------------------------------------------------------

--
-- Table structure for table `product_reviews`
--

CREATE TABLE IF NOT EXISTS `product_reviews` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(190) NOT NULL,
  `rating` tinyint(3) UNSIGNED NOT NULL,
  `comment` text NOT NULL,
  `status` enum('pending','approved') DEFAULT 'approved',
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `product_review_images`
--

CREATE TABLE IF NOT EXISTS `product_review_images` (
  `id` int(11) NOT NULL,
  `review_id` int(11) NOT NULL,
  `image_path` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `product_sections`
--

CREATE TABLE IF NOT EXISTS `product_sections` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `section_type` varchar(50) NOT NULL,
  `section_order` int(11) DEFAULT 0,
  `is_enabled` tinyint(1) DEFAULT 1,
  `settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`settings`)),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `security_logs`
--

CREATE TABLE IF NOT EXISTS `security_logs` (
  `id` int(10) UNSIGNED NOT NULL,
  `type` varchar(50) NOT NULL COMMENT 'ddos | ban | 404 | error | warning',
  `ip` varchar(45) NOT NULL,
  `message` text NOT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `security_rate_limits`
--

CREATE TABLE IF NOT EXISTS `security_rate_limits` (
  `ip` varchar(45) NOT NULL,
  `requests` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `window_start` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

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

CREATE TABLE IF NOT EXISTS `settings` (
  `id` int(11) NOT NULL,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
INSERT IGNORE INTO `settings` (`id`, `setting_key`, `setting_value`) VALUES
(1, 'site_logo', NULL),
(2, 'dark_logo', NULL),
(3, 'mobile_logo', NULL),
(4, 'admin_logo', NULL),
(5, 'footer_logo', NULL),
(6, 'favicon', NULL),
(7, 'admin_small_logo', NULL);
--
-- Table structure for table `shipping_integrations`
--

CREATE TABLE IF NOT EXISTS `shipping_integrations` (
  `id` int(10) UNSIGNED NOT NULL,
  `provider` varchar(50) NOT NULL,
  `display_name` varchar(100) NOT NULL,
  `api_id` varchar(191) DEFAULT NULL,
  `api_token` varchar(191) DEFAULT NULL,
  `extra` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`extra`)),
  `is_active` tinyint(1) NOT NULL DEFAULT 0,
  `country_code` char(2) NOT NULL DEFAULT 'DZ',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `shipping_integrations`
--


-- --------------------------------------------------------

--
-- Table structure for table `shipping_prices`
--

CREATE TABLE IF NOT EXISTS `shipping_prices` (
  `id` int(11) NOT NULL,
  `provider` varchar(50) NOT NULL,
  `country_code` char(2) NOT NULL DEFAULT 'DZ',
  `wilaya_code` varchar(10) NOT NULL,
  `commune_id` int(11) DEFAULT NULL,
  `wilaya_name` varchar(100) NOT NULL,
  `delivery_type` enum('home','desk') NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `return_price` decimal(10,2) DEFAULT 0.00,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `testimonials`
--

CREATE TABLE IF NOT EXISTS `testimonials` (
  `id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `content` text DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `status` enum('inactive','active') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `theme_sections`
--

CREATE TABLE IF NOT EXISTS `theme_sections` (
  `id` int(10) UNSIGNED NOT NULL,
  `section_key` varchar(100) NOT NULL,
  `section_type` varchar(50) NOT NULL,
  `section_order` int(11) NOT NULL DEFAULT 0,
  `is_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `settings` longtext DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `theme_sections`
--


-- --------------------------------------------------------

--
-- Table structure for table `theme_settings`
--

CREATE TABLE IF NOT EXISTS `theme_settings` (
  `key` varchar(191) NOT NULL,
  `value` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `theme_settings`
INSERT IGNORE INTO `theme_settings` (`key`, `value`) VALUES
('active_product_id', ''),
('admin_theme', '{\"fonts\":{\"body\":\"ar_cairo\",\"heading\":\"ar_tajawal\"},\"colors\":{\"primary\":\"#b4419b\",\"sidebar\":\"#0b0a0a\",\"header\":\"#ffffff\",\"background\":\"#f4f6f9\"},\"mode\":\"light\"}'),
('colors', '{\"primary\":\"#598aee\",\"secondary\":\"#f7182e\",\"success\":\"#1ae055\",\"danger\":\"#e8663b\",\"warning\":\"#ced062\",\"info\":\"#39dbba\",\"light\":\"#f6f4f4\",\"dark\":\"#16161d\"}'),
('custom_css', '\"\"'),
('fonts', '{\"body\":\"ar_cairo\",\"heading\":\"ar_tajawal\"}'),
('header_menu', '[{\"label\":\"\\u0627\\u0644\\u0631\\u0626\\u064a\\u0633\\u064a\\u0629\",\"url\":\"\\/\",\"type\":\"home\",\"enabled\":1,\"order\":0},{\"label\":\"\\u0627\\u0644\\u0634\\u062d\\u0646 \\u0648 \\u0627\\u0644\\u062a\\u0633\\u0644\\u064a\\u0645\",\"url\":\"\\/\",\"type\":\"home\",\"enabled\":1,\"order\":0},{\"label\":\"\\u0627\\u062a\\u0635\\u0644 \\u0628\\u0646\\u0627\",\"url\":\"\\/\",\"type\":\"home\",\"enabled\":1,\"order\":0},{\"label\":\"\\u0637\\u0631\\u0642 \\u0627\\u0644\\u062f\\u0641\\u0639\",\"url\":\"\\/\",\"type\":\"home\",\"enabled\":1,\"order\":0}]');

--------------------------------------------------------

--
-- Table structure for table `tracking_integrations`
--

CREATE TABLE IF NOT EXISTS `tracking_integrations` (
  `id` int(11) NOT NULL,
  `provider` varchar(50) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 0,
  `config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`config`)),
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tracking_integrations`
--



-- --------------------------------------------------------
-- Table structure for table `fraud_attempts`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `fraud_attempts` (
  `id` int(10) UNSIGNED NOT NULL,
  `phone` varchar(50) NOT NULL,
  `ip` varchar(45) NOT NULL DEFAULT '',
  `reason` varchar(50) NOT NULL COMMENT 'phone_banned | suspicious',
  `full_name` varchar(255) NOT NULL DEFAULT '',
  `product_id` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `fraud_score` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `fraud_banned_ips`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `fraud_banned_ips` (
  `id` int(10) UNSIGNED NOT NULL,
  `ip` varchar(45) NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `fraud_banned_phones`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `fraud_banned_phones` (
  `id` int(10) UNSIGNED NOT NULL,
  `phone` varchar(20) NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `fraud_order_outcomes`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `fraud_order_outcomes` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `phone` varchar(20) NOT NULL,
  `outcome` enum('delivered','returned','cancelled') NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `order_assignments`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `order_assignments` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `admin_id` int(10) UNSIGNED NOT NULL,
  `assigned_at` datetime NOT NULL DEFAULT current_timestamp(),
  `confirmed_at` datetime DEFAULT NULL,
  `action` enum('confirmed','rejected','pending') NOT NULL DEFAULT 'pending'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `order_items`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `order_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `variant_id` int(10) UNSIGNED DEFAULT NULL,
  `quantity` smallint(5) UNSIGNED NOT NULL DEFAULT 1,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- --------------------------------------------------------
-- Table structure for table `order_lifecycle_events`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `order_lifecycle_events` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `order_id` int(11) NOT NULL,
  `admin_id` int(11) DEFAULT NULL,
  `event_type` varchar(80) NOT NULL,
  `provider` varchar(80) DEFAULT NULL,
  `meta` longtext DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `order_returns`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `order_returns` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_id` int(10) UNSIGNED NOT NULL,
  `type` enum('shipping_refused','admin_request','partial') NOT NULL COMMENT 'shipping_refused=رفض الزبون | admin_request=مشكلة في المنتج | partial=جزئي',
  `quantity` int(11) NOT NULL DEFAULT 1,
  `reason` varchar(500) NOT NULL DEFAULT '',
  `admin_notes` text DEFAULT NULL,
  `received_at` date DEFAULT NULL,
  `stock_restored` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `product_variant_stock`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `product_variant_stock` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_id` int(11) NOT NULL,
  `size` varchar(50) DEFAULT NULL,
  `color` varchar(100) DEFAULT NULL,
  `quantity` int(11) NOT NULL DEFAULT 0,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `team_performance`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `team_performance` (
  `id` int(10) UNSIGNED NOT NULL,
  `admin_id` int(10) UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `confirmed` int(11) NOT NULL DEFAULT 0,
  `rejected` int(11) NOT NULL DEFAULT 0,
  `total` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `admins`
--
ALTER TABLE `admins`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_username` (`username`);

--
-- Indexes for table `admin_notifications`
--
ALTER TABLE `admin_notifications`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `admin_password_resets`
--
ALTER TABLE `admin_password_resets`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_email` (`email`),
  ADD KEY `idx_token_hash` (`token_hash`),
  ADD KEY `idx_expires_at` (`expires_at`);

--
-- Indexes for table `ai_settings`
--
ALTER TABLE `ai_settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `provider_unique` (`provider`);

--
-- Indexes for table `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `categories_slug_unique` (`slug`);

--
-- Indexes for table `footer_links`
--
ALTER TABLE `footer_links`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_footer_links_section` (`footer_section_id`);

--
-- Indexes for table `footer_sections`
--
ALTER TABLE `footer_sections`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `home_sections`
--
ALTER TABLE `home_sections`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `languages`
--
ALTER TABLE `languages`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `code` (`code`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_id` (`product_id`),
  ADD KEY `order_status` (`order_status`),
  ADD KEY `payment_status` (`payment_status`),
  ADD KEY `idx_created_at` (`created_at`);

--
-- Indexes for table `pages`
--
ALTER TABLE `pages`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`);

--
-- Indexes for table `page_layouts`
--
ALTER TABLE `page_layouts`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `page_views`
--
ALTER TABLE `page_views`
  ADD PRIMARY KEY (`id`),
  ADD INDEX `idx_visited_at` (`visited_at`),
  ADD INDEX `idx_session_id` (`session_id`(64));

--
-- Indexes for table `plugins`
--
ALTER TABLE `plugins`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD KEY `idx_active` (`is_active`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD UNIQUE KEY `uq_products_id` (`id`);

--
-- Indexes for table `product_categories`
--
ALTER TABLE `product_categories`
  ADD PRIMARY KEY (`product_id`,`category_id`),
  ADD KEY `category_id` (`category_id`);

--
-- Indexes for table `product_reviews`
--
ALTER TABLE `product_reviews`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_product_id` (`product_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `product_review_images`
--
ALTER TABLE `product_review_images`
  ADD PRIMARY KEY (`id`),
  ADD KEY `review_id` (`review_id`);

--
-- Indexes for table `product_sections`
--
ALTER TABLE `product_sections`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `security_logs`
--
ALTER TABLE `security_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `type_idx` (`type`),
  ADD KEY `ip_idx` (`ip`),
  ADD KEY `created_at_idx` (`created_at`);

--
-- Indexes for table `security_rate_limits`
--
ALTER TABLE `security_rate_limits`
  ADD PRIMARY KEY (`ip`);

--
-- Indexes for table `settings`
--
ALTER TABLE `settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `setting_key` (`setting_key`);

--
-- Indexes for table `shipping_integrations`
--
ALTER TABLE `shipping_integrations`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_provider` (`provider`);

--
-- Indexes for table `shipping_prices`
--
ALTER TABLE `shipping_prices`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_price` (`provider`,`wilaya_code`,`delivery_type`);

--
-- Indexes for table `testimonials`
--
ALTER TABLE `testimonials`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `theme_settings`
--
ALTER TABLE `theme_settings`
  ADD PRIMARY KEY (`key`);

--
-- Indexes for table `tracking_integrations`
--
ALTER TABLE `tracking_integrations`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `provider` (`provider`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `admins`
--
ALTER TABLE `admins`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `admin_notifications`
--
ALTER TABLE `admin_notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `admin_password_resets`
--
ALTER TABLE `admin_password_resets`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `ai_settings`
--
ALTER TABLE `ai_settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `categories`
--
ALTER TABLE `categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `footer_links`
--
ALTER TABLE `footer_links`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

--
-- AUTO_INCREMENT for table `footer_sections`
--
ALTER TABLE `footer_sections`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=34;

--
-- AUTO_INCREMENT for table `home_sections`
--
ALTER TABLE `home_sections`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=59;

--
-- AUTO_INCREMENT for table `languages`
--
ALTER TABLE `languages`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=69;

--
-- AUTO_INCREMENT for table `pages`
--
ALTER TABLE `pages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `page_layouts`
--
ALTER TABLE `page_layouts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `page_views`
--
ALTER TABLE `page_views`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=65;

--
-- AUTO_INCREMENT for table `plugins`
--
ALTER TABLE `plugins`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;

--
-- AUTO_INCREMENT for table `product_reviews`
--
ALTER TABLE `product_reviews`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

--
-- AUTO_INCREMENT for table `product_review_images`
--
ALTER TABLE `product_review_images`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `product_sections`
--
ALTER TABLE `product_sections`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=79;

--
-- AUTO_INCREMENT for table `security_logs`
--
ALTER TABLE `security_logs`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=254;

--
-- AUTO_INCREMENT for table `settings`
--
ALTER TABLE `settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1222;

--
-- AUTO_INCREMENT for table `shipping_integrations`
--
ALTER TABLE `shipping_integrations`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `shipping_prices`
--
ALTER TABLE `shipping_prices`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `testimonials`
--
ALTER TABLE `testimonials`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tracking_integrations`
--
ALTER TABLE `tracking_integrations`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;


--
-- Indexes for table `fraud_attempts`
--
ALTER TABLE `fraud_attempts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_phone` (`phone`),
  ADD KEY `idx_ip` (`ip`),
  ADD KEY `idx_created_at` (`created_at`);

--
-- Indexes for table `fraud_banned_ips`
--
ALTER TABLE `fraud_banned_ips`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `ip` (`ip`);

--
-- Indexes for table `fraud_banned_phones`
--
ALTER TABLE `fraud_banned_phones`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `phone` (`phone`);

--
-- Indexes for table `fraud_order_outcomes`
--
ALTER TABLE `fraud_order_outcomes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `order_id` (`order_id`),
  ADD KEY `idx_phone` (`phone`);

--
-- Indexes for table `order_assignments`
--
ALTER TABLE `order_assignments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_order` (`order_id`),
  ADD KEY `idx_admin_id` (`admin_id`);

--
-- Indexes for table `order_items`
--
ALTER TABLE `order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_order` (`order_id`),
  ADD KEY `idx_product` (`product_id`);


--
-- Indexes for table `order_lifecycle_events`
--
ALTER TABLE `order_lifecycle_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_order_event` (`order_id`,`event_type`),
  ADD KEY `idx_admin_event` (`admin_id`,`event_type`),
  ADD KEY `idx_created_at` (`created_at`);

--
-- Indexes for table `order_returns`
--
ALTER TABLE `order_returns`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_order_id` (`order_id`);

--
-- Indexes for table `product_variant_stock`
--
ALTER TABLE `product_variant_stock`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_variant` (`product_id`,`size`,`color`),
  ADD KEY `idx_product_id` (`product_id`);

--
-- Indexes for table `team_performance`
--
ALTER TABLE `team_performance`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_admin_date` (`admin_id`,`date`);


--
-- AUTO_INCREMENT for table `fraud_attempts`
--
ALTER TABLE `fraud_attempts`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `fraud_banned_ips`
--
ALTER TABLE `fraud_banned_ips`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `fraud_banned_phones`
--
ALTER TABLE `fraud_banned_phones`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `fraud_order_outcomes`
--
ALTER TABLE `fraud_order_outcomes`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `order_assignments`
--
ALTER TABLE `order_assignments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `order_items`
--
ALTER TABLE `order_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;


--
-- AUTO_INCREMENT for table `order_lifecycle_events`
--
ALTER TABLE `order_lifecycle_events`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `order_returns`
--
ALTER TABLE `order_returns`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `product_variant_stock`
--
ALTER TABLE `product_variant_stock`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `team_performance`
--
ALTER TABLE `team_performance`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--
-- ============================================================
-- نظام صلاحيات الأدوار المرنة — Migration
-- نفّذ مرة واحدة فقط
-- ============================================================

CREATE TABLE IF NOT EXISTS `role_permissions` (
    `id`         int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `role`       varchar(50) NOT NULL,
    `permission` varchar(100) NOT NULL,
    `created_at` datetime NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_role_perm` (`role`, `permission`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- القيم الافتراضية لكل دور
INSERT IGNORE INTO `role_permissions` (`role`, `permission`) VALUES
-- manager
('manager', 'view_orders'),
('manager', 'confirm_orders'),
('manager', 'manage_team'),
('manager', 'view_stats'),
('manager', 'manage_shipping'),
('manager', 'view_products'),
('manager', 'view_suspicious'),
('manager', 'manage_returns'),

-- confirmer
('confirmer', 'view_assigned_orders'),
('confirmer', 'confirm_orders'),

-- shipping
('shipping', 'view_confirmed_orders'),
('shipping', 'manage_shipping'),

-- accountant
('accountant', 'view_stats'),
('accountant', 'view_orders_readonly');

-- =====================================================
-- جدول العملاء المستخرج من الطلبات المؤكدة
-- =====================================================
CREATE TABLE IF NOT EXISTS `customers` (
  `id`               int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `phone`            varchar(50)   NOT NULL,
  `phone_normalized` varchar(20)   NOT NULL COMMENT 'الرقم بصيغة دولية +213',
  `full_name`        varchar(255)  NOT NULL,
  `city`             varchar(100)  DEFAULT NULL,
  `wilaya_code`      varchar(10)   DEFAULT NULL,
  `orders_count`     int(11)       NOT NULL DEFAULT 1,
  `total_spent`      decimal(12,2) NOT NULL DEFAULT 0.00,
  `first_order_at`   datetime      NOT NULL,
  `last_order_at`    datetime      NOT NULL,
  `tags`             varchar(500)  DEFAULT NULL COMMENT 'vip,returning,new,...',
  `is_blocked`       tinyint(1)    NOT NULL DEFAULT 0,
  `notes`            text          DEFAULT NULL,
  `created_at`       datetime      NOT NULL DEFAULT current_timestamp(),
  `updated_at`       datetime      DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_phone` (`phone_normalized`),
  KEY `idx_orders_count` (`orders_count`),
  KEY `idx_last_order`   (`last_order_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =====================================================
-- جدول سجل رسائل WhatsApp (تسويقية + تلقائية)
-- =====================================================
CREATE TABLE IF NOT EXISTS `whatsapp_messages` (
  `id`          int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `type`        enum('auto','marketing') NOT NULL DEFAULT 'auto' COMMENT 'auto=تلقائي, marketing=تسويقي',
  `event`       varchar(50)  DEFAULT NULL COMMENT 'order_confirmed,order_shipped,...',
  `campaign`    varchar(100) DEFAULT NULL COMMENT 'اسم الحملة التسويقية',
  `phone`       varchar(50)  NOT NULL,
  `customer_id` int(10) UNSIGNED DEFAULT NULL,
  `order_id`    int(10) UNSIGNED DEFAULT NULL,
  `message`     text         NOT NULL,
  `provider`    varchar(30)  DEFAULT NULL,
  `status`      enum('sent','failed','pending') NOT NULL DEFAULT 'pending',
  `error`       varchar(500) DEFAULT NULL,
  `sent_at`     datetime     DEFAULT NULL,
  `created_at`  datetime     NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_type`     (`type`),
  KEY `idx_sent_at`  (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =====================================================
-- بناء جدول العملاء من الطلبات المؤكدة الموجودة
-- =====================================================
INSERT INTO `customers` (
    phone, phone_normalized, full_name, city, wilaya_code,
    orders_count, total_spent, first_order_at, last_order_at
)
SELECT
    phone,
    CASE
        WHEN phone REGEXP '^0[5-7][0-9]{8}$'
            THEN CONCAT('+213', SUBSTRING(phone, 2))
        WHEN phone REGEXP '^[5-7][0-9]{8}$'
            THEN CONCAT('+213', phone)
        WHEN phone REGEXP '^213[0-9]{9}$'
            THEN CONCAT('+', phone)
        ELSE phone
    END AS phone_normalized,
    full_name,
    JSON_UNQUOTE(JSON_EXTRACT(extra,'$.shipping.destination.city_name'))    AS city,
    JSON_UNQUOTE(JSON_EXTRACT(extra,'$.shipping.destination.state_code'))   AS wilaya_code,
    COUNT(*)                                                                 AS orders_count,
    COALESCE(SUM(JSON_EXTRACT(extra,'$.pricing.total')), SUM(total_amount)) AS total_spent,
    MIN(created_at) AS first_order_at,
    MAX(created_at) AS last_order_at
FROM orders
WHERE order_status = 'confirmed'
GROUP BY phone
ON DUPLICATE KEY UPDATE
    orders_count  = VALUES(orders_count),
    total_spent   = VALUES(total_spent),
    last_order_at = VALUES(last_order_at);
	
	-- =====================================================
-- جدول محاولات التأكيد (مكالمات المؤكدين)
-- =====================================================
CREATE TABLE IF NOT EXISTS `order_call_attempts` (
  `id`          int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id`    int(10) UNSIGNED NOT NULL,
  `admin_id`    int(10) UNSIGNED NOT NULL,
  `admin_name`  varchar(100)     NOT NULL,
  `admin_role`  varchar(50)      NOT NULL DEFAULT 'confirmer',
  `result`      enum(
                  'no_answer',
                  'busy',
                  'wrong_number',
                  'callback_later',
                  'interested',
                  'not_interested',
                  'confirmed',
                  'rejected'
                ) NOT NULL DEFAULT 'no_answer',
  `note`        varchar(500)     DEFAULT NULL,
  `called_at`   datetime         NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_order`  (`order_id`),
  KEY `idx_admin`  (`admin_id`),
  KEY `idx_called` (`called_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Constraints for table `footer_links`
--
ALTER TABLE `footer_links`
  ADD CONSTRAINT `fk_footer_links_section` FOREIGN KEY (`footer_section_id`) REFERENCES `footer_sections` (`id`) ON DELETE CASCADE;

-- --------------------------------------------------------
-- Table structure for table `ai_landing_pages`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `ai_landing_pages` (
  `id`           INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id`   INT(10) UNSIGNED NOT NULL,
  `name`         VARCHAR(255) NOT NULL COMMENT 'Internal admin name',
  `slug`         VARCHAR(255) NOT NULL COMMENT 'URL slug',
  `html_content` LONGTEXT DEFAULT NULL COMMENT 'Full generated HTML',
  `source_mode`  VARCHAR(20) NOT NULL DEFAULT 'manual' COMMENT 'manual or image',
  `source_image` VARCHAR(255) DEFAULT NULL COMMENT 'Uploaded screenshot/design path',
  `analysis_json` LONGTEXT DEFAULT NULL COMMENT 'Vision analysis result JSON',
  `structure_json` LONGTEXT DEFAULT NULL COMMENT 'Normalized layout structure JSON',
  `agent_trace`  LONGTEXT DEFAULT NULL COMMENT 'Plan/generate/evaluate/improve trace',
  `conversation` LONGTEXT DEFAULT NULL COMMENT 'JSON chat history [{role,content}]',
  `status`       ENUM('draft','published') NOT NULL DEFAULT 'draft',
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_slug` (`slug`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `security_rate_limits`
-- Used by SecurityMiddleware for DDoS/brute-force rate limiting.
-- Defined here so fresh installs never need a runtime CREATE TABLE.
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `security_rate_limits` (
  `ip`           VARCHAR(45)      NOT NULL,
  `requests`     INT(10) UNSIGNED NOT NULL DEFAULT 0,
  `window_start` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- =====================================================
-- =====================================================

-- --------------------------------------------------------
-- Table structure for table `team_messages`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `team_messages` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `sender_id` int(10) UNSIGNED NOT NULL,
  `receiver_id` int(10) UNSIGNED NOT NULL,
  `content` text NOT NULL DEFAULT '',
  `attachment_path` varchar(500) DEFAULT NULL,
  `attachment_type` varchar(100) DEFAULT NULL,
  `attachment_name` varchar(255) DEFAULT NULL,
  `attachment_size` int(10) UNSIGNED DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `delivered_at` datetime DEFAULT NULL,
  `seen_at` datetime DEFAULT NULL,
  `is_pinned` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_sender` (`sender_id`),
  KEY `idx_receiver` (`receiver_id`),
  KEY `idx_conversation` (`sender_id`,`receiver_id`),
  KEY `idx_unread` (`receiver_id`,`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `team_meetings`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `team_meetings` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_by` int(10) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `meeting_type` enum('standup','review','training','planning','group_chat','other') NOT NULL DEFAULT 'other',
  `scheduled_at` datetime NOT NULL,
  `duration_minutes` int(11) NOT NULL DEFAULT 60,
  `location` varchar(255) DEFAULT NULL,
  `status` enum('upcoming','completed','cancelled') NOT NULL DEFAULT 'upcoming',
  `has_group_chat` tinyint(1) NOT NULL DEFAULT 0,
  `reminder_minutes` int(11) NOT NULL DEFAULT 0,
  `reminder_sent_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_created_by` (`created_by`),
  KEY `idx_scheduled` (`scheduled_at`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `team_meeting_attendees`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `team_meeting_attendees` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `meeting_id` int(10) UNSIGNED NOT NULL,
  `admin_id` int(10) UNSIGNED NOT NULL,
  `rsvp` enum('pending','accepted','declined') NOT NULL DEFAULT 'pending',
  `responded_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_meeting_admin` (`meeting_id`,`admin_id`),
  KEY `idx_admin` (`admin_id`),
  CONSTRAINT `fk_tma_meeting` FOREIGN KEY (`meeting_id`) REFERENCES `team_meetings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `team_group_messages`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `team_group_messages` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `meeting_id` int(10) UNSIGNED NOT NULL,
  `sender_id` int(10) UNSIGNED NOT NULL,
  `content` text NOT NULL DEFAULT '',
  `attachment_path` varchar(500) DEFAULT NULL,
  `attachment_type` varchar(100) DEFAULT NULL,
  `attachment_name` varchar(255) DEFAULT NULL,
  `attachment_size` int(10) UNSIGNED DEFAULT NULL,
  `is_pinned` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_meeting` (`meeting_id`),
  KEY `idx_sender` (`sender_id`),
  KEY `idx_poll` (`meeting_id`,`id`),
  CONSTRAINT `fk_tgm_meeting` FOREIGN KEY (`meeting_id`) REFERENCES `team_meetings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `admin_activity_log`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `admin_activity_log` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id` int(10) UNSIGNED NOT NULL,
  `action` enum('login','logout') NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_admin_id` (`admin_id`),
  KEY `idx_action` (`action`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `team_announcements`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `team_announcements` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id` int(10) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `is_pinned` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_admin_id` (`admin_id`),
  KEY `idx_pinned` (`is_pinned`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Patch: عمود last_seen_at لجدول admins
-- آمن للتشغيل على قواعد البيانات الموجودة (IF NOT EXISTS)
-- على التثبيت الجديد: العمود موجود في CREATE TABLE فالأمر يُتجاهل
-- =====================================================
ALTER TABLE `admins`
  ADD COLUMN IF NOT EXISTS `last_seen_at` datetime DEFAULT NULL AFTER `last_login_at`;

-- Patch: attachment columns for team_messages
ALTER TABLE `team_messages`
  ADD COLUMN IF NOT EXISTS `attachment_path` varchar(500) DEFAULT NULL AFTER `content`,
  ADD COLUMN IF NOT EXISTS `attachment_type` varchar(100) DEFAULT NULL AFTER `attachment_path`,
  ADD COLUMN IF NOT EXISTS `attachment_name` varchar(255) DEFAULT NULL AFTER `attachment_type`,
  ADD COLUMN IF NOT EXISTS `attachment_size` int(10) UNSIGNED DEFAULT NULL AFTER `attachment_name`;

-- Patch: group chat + reminder fields for team_meetings
ALTER TABLE `team_meetings`
  ADD COLUMN IF NOT EXISTS `has_group_chat` tinyint(1) NOT NULL DEFAULT 0 AFTER `status`,
  ADD COLUMN IF NOT EXISTS `reminder_minutes` int(11) NOT NULL DEFAULT 0 AFTER `has_group_chat`,
  ADD COLUMN IF NOT EXISTS `reminder_sent_at` datetime DEFAULT NULL AFTER `reminder_minutes`,
  MODIFY COLUMN `meeting_type` enum('standup','review','training','planning','group_chat','other') NOT NULL DEFAULT 'other';

-- Patch: phone field for admins
ALTER TABLE `admins`
  ADD COLUMN IF NOT EXISTS `phone` varchar(30) DEFAULT NULL AFTER `email`;

COMMIT;

/*!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 */;

ALTER TABLE `team_messages`
  ADD COLUMN IF NOT EXISTS `delivered_at` datetime DEFAULT NULL AFTER `is_read`,
  ADD COLUMN IF NOT EXISTS `seen_at` datetime DEFAULT NULL AFTER `delivered_at`,
  ADD COLUMN IF NOT EXISTS `is_pinned` tinyint(1) NOT NULL DEFAULT 0 AFTER `seen_at`;

ALTER TABLE `team_group_messages`
  ADD COLUMN IF NOT EXISTS `is_pinned` tinyint(1) NOT NULL DEFAULT 0 AFTER `attachment_size`;

-- Team communication hotfixes
ALTER TABLE `team_messages`
  ADD COLUMN IF NOT EXISTS `attachment_kind` varchar(20) DEFAULT NULL AFTER `attachment_type`,
  ADD COLUMN IF NOT EXISTS `delivered_at` datetime DEFAULT NULL AFTER `is_read`,
  ADD COLUMN IF NOT EXISTS `seen_at` datetime DEFAULT NULL AFTER `delivered_at`,
  ADD COLUMN IF NOT EXISTS `is_pinned` tinyint(1) NOT NULL DEFAULT 0 AFTER `seen_at`;

ALTER TABLE `team_group_messages`
  ADD COLUMN IF NOT EXISTS `attachment_kind` varchar(20) DEFAULT NULL AFTER `attachment_type`,
  ADD COLUMN IF NOT EXISTS `is_pinned` tinyint(1) NOT NULL DEFAULT 0 AFTER `attachment_size`;

ALTER TABLE `team_meetings`
  ADD COLUMN IF NOT EXISTS `last_activity_at` datetime DEFAULT NULL AFTER `updated_at`,
  ADD COLUMN IF NOT EXISTS `start_notification_sent_at` datetime DEFAULT NULL AFTER `reminder_sent_at`;

ALTER TABLE `ai_settings`
  ADD COLUMN IF NOT EXISTS `image_generation_enabled` tinyint(1) NOT NULL DEFAULT 0 AFTER `auto_seo_enabled`,
  ADD COLUMN IF NOT EXISTS `image_model` varchar(191) DEFAULT NULL AFTER `image_generation_enabled`;


-- ─────────────────────────────────────────────
-- Video Meeting Integration
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `video_meeting_settings` (
  `id`         int(11)          NOT NULL AUTO_INCREMENT,
  `provider`   varchar(50)      NOT NULL COMMENT 'jitsi | daily | whereby',
  `api_key`    text             DEFAULT NULL,
  `extra`      longtext         DEFAULT NULL COMMENT 'JSON: domain, room_prefix, subdomain, cached rooms, etc.',
  `is_active`  tinyint(1)       NOT NULL DEFAULT 0,
  `created_at` timestamp        NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp        NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_video_provider` (`provider`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
