=== factories ===
CREATE TABLE `factories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `is_active` (`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== amperages ===
CREATE TABLE `amperages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `amperage` int(10) unsigned NOT NULL,
  `mandatory_amperage` int(11) DEFAULT NULL,
  `label` varchar(20) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_value` (`amperage`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== battery_case_types ===
CREATE TABLE `battery_case_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT 'e.g., L1, L2, D26',
  `standard` enum('DIN','JIS','BCI') DEFAULT 'DIN' COMMENT '??????????????????: ??????????????/??????????/????????????????',
  `length_mm` int(10) unsigned DEFAULT NULL COMMENT '?????? ???? ?????????????????',
  `width_mm` int(10) unsigned DEFAULT NULL COMMENT '?????? ???? ?????????????????',
  `height_mm` int(10) unsigned DEFAULT NULL COMMENT '???????????? ???? ?????????????????',
  `terminal_type` enum('A','B','SIDE') DEFAULT 'A' COMMENT '?????? ??????????????: A=?????????????? ?????????? B=?????????? ?????????? SIDE=??????????',
  `is_tall_base` tinyint(1) DEFAULT 1 COMMENT '???????? ????????=TRUE?? ???????? ??????????=FALSE',
  `display_order` int(11) DEFAULT 0 COMMENT '?????????? ??????????',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== customers ===
CREATE TABLE `customers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mobile` varchar(11) NOT NULL COMMENT 'Format: 09xxxxxxxxx',
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `national_code` varchar(10) DEFAULT NULL COMMENT 'Iranian National ID',
  `employee_code` varchar(20) DEFAULT NULL COMMENT '┌®Ï» ┘à┘åÏ¡ÏÁÏ▒ Ï¿┘ç ┘üÏ▒Ï» ┘ç┘à┌®ÏºÏ▒ (EMP-XXXX) - Ï¬┘å┘çÏº Ï¿Ï▒Ïº█î ┘ç┘à┌®ÏºÏ▒Ïº┘å█î ┌®┘ç user_type=partner Ï»ÏºÏ▒┘åÏ»',
  `accepts_used_battery` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Partner purchases with used battery trade-in by default',
  `email` varchar(100) DEFAULT NULL,
  `user_type` enum('customer','partner') NOT NULL DEFAULT 'customer',
  `status` enum('active','inactive','suspended') NOT NULL DEFAULT 'active',
  `cod_allowed` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Can use Cash on Delivery',
  `cod_blocked_reason` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `last_login_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mobile` (`mobile`),
  UNIQUE KEY `uk_national_code` (`national_code`),
  UNIQUE KEY `uk_employee_code` (`employee_code`),
  KEY `idx_status` (`status`),
  KEY `idx_user_type` (`user_type`),
  KEY `idx_user_type_employee` (`user_type`,`employee_code`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== admin_users ===
CREATE TABLE `admin_users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `mobile` varchar(11) DEFAULT NULL,
  `role` enum('super_admin','admin','operator','warehouse','accountant') NOT NULL DEFAULT 'operator',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_login_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `password_changed_at` datetime DEFAULT NULL,
  `failed_login_count` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `locked_until` datetime DEFAULT NULL,
  `two_factor_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `two_factor_secret` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== orders ===
CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_number` varchar(20) NOT NULL,
  `customer_id` int(10) unsigned NOT NULL,
  `order_type` enum('ONLINE','INSTORE','wholesale','pos','ampere_credit') NOT NULL DEFAULT 'ONLINE',
  `status` enum('pending_payment','paid','processing','shipped','delivered','completed','cancelled','expired') NOT NULL DEFAULT 'pending_payment',
  `subtotal` bigint(20) unsigned NOT NULL,
  `discount_amount` bigint(20) unsigned NOT NULL DEFAULT 0,
  `shipping_fee` bigint(20) unsigned NOT NULL DEFAULT 0,
  `installation_fee` bigint(20) unsigned NOT NULL DEFAULT 0,
  `used_battery_credit` bigint(20) unsigned NOT NULL DEFAULT 0,
  `total_amount` bigint(20) unsigned NOT NULL,
  `shipping_address` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`shipping_address`)),
  `has_installation` tinyint(1) NOT NULL DEFAULT 0,
  `installation_date` date DEFAULT NULL,
  `installation_time_slot` enum('morning','noon','afternoon','evening','night') DEFAULT NULL,
  `customer_notes` text DEFAULT NULL,
  `admin_notes` text DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `shipped_at` datetime DEFAULT NULL,
  `delivered_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL COMMENT 'Auto-cancel if not paid',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_number` (`order_number`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created` (`created_at`),
  KEY `idx_expires` (`expires_at`),
  KEY `idx_customer_status_created` (`customer_id`,`status`,`created_at`),
  KEY `idx_order_type_status` (`order_type`,`status`),
  KEY `idx_pending_expires` (`status`,`expires_at`),
  CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  CONSTRAINT `chk_ord_total_positive` CHECK (`total_amount` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== order_items ===
CREATE TABLE `order_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `quantity` int(10) unsigned NOT NULL,
  `returned_quantity` int(11) NOT NULL DEFAULT 0,
  `unit_price` bigint(20) unsigned NOT NULL,
  `total_price` bigint(20) unsigned NOT NULL,
  `has_used_battery` tinyint(1) NOT NULL DEFAULT 0,
  `used_battery_amperage` int(10) unsigned DEFAULT NULL,
  `used_battery_amperage_2` int(10) unsigned DEFAULT NULL,
  `used_battery_rate` int(10) unsigned DEFAULT NULL COMMENT 'Rate per amp at order time',
  `used_battery_credit` bigint(20) unsigned NOT NULL DEFAULT 0,
  `used_battery_received` tinyint(1) NOT NULL DEFAULT 0,
  `used_battery_received_at` datetime DEFAULT NULL,
  `has_installation` tinyint(1) NOT NULL DEFAULT 0,
  `installation_fee` bigint(20) unsigned NOT NULL DEFAULT 0,
  `warranty_months` int(11) NOT NULL,
  `warranty_start_date` date DEFAULT NULL,
  `warranty_end_date` date DEFAULT NULL,
  `warranty_code` varchar(50) DEFAULT NULL,
  `product_snapshot` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`product_snapshot`)),
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_order` (`order_id`),
  KEY `idx_product` (`product_id`),
  KEY `idx_warranty` (`warranty_code`),
  CONSTRAINT `fk_order_items_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_order_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `chk_oi_quantity_positive` CHECK (`quantity` > 0),
  CONSTRAINT `chk_oi_unit_price_positive` CHECK (`unit_price` >= 0),
  CONSTRAINT `chk_oi_total_price_positive` CHECK (`total_price` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== payments ===
CREATE TABLE `payments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(10) unsigned NOT NULL,
  `method` enum('online','card_transfer','cod','cash','pos','credit','check','transfer') NOT NULL,
  `amount` bigint(20) unsigned NOT NULL,
  `status` enum('pending','processing','completed','failed','refunded') NOT NULL DEFAULT 'pending',
  `gateway` varchar(50) DEFAULT NULL COMMENT 'e.g., zarinpal',
  `gateway_ref` varchar(100) DEFAULT NULL,
  `gateway_track_id` varchar(100) DEFAULT NULL,
  `transfer_receipt_image` varchar(255) DEFAULT NULL,
  `transfer_verified_at` datetime DEFAULT NULL,
  `transfer_verified_by` int(10) unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `check_number` varchar(50) DEFAULT NULL COMMENT 'شماره چک',
  `check_date` date DEFAULT NULL COMMENT 'تاریخ چک',
  `check_bank` varchar(100) DEFAULT NULL COMMENT 'نام بانک',
  `check_account_holder` varchar(100) DEFAULT NULL COMMENT 'نام صاحب حساب',
  `completed_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_order` (`order_id`),
  KEY `idx_status` (`status`),
  KEY `idx_gateway_ref` (`gateway_ref`),
  KEY `idx_check_number` (`check_number`),
  KEY `fk_payments_verified_by` (`transfer_verified_by`),
  KEY `idx_method_status` (`method`,`status`),
  KEY `idx_order_status` (`order_id`,`status`),
  CONSTRAINT `fk_payments_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
  CONSTRAINT `fk_payments_verified_by` FOREIGN KEY (`transfer_verified_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `chk_pay_amount_positive` CHECK (`amount` > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== otp_codes ===
CREATE TABLE `otp_codes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mobile` varchar(11) NOT NULL,
  `code` varchar(6) NOT NULL,
  `expires_at` datetime NOT NULL,
  `attempts` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `verified_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `ip_address` varchar(45) DEFAULT NULL,
  `purpose` varchar(30) NOT NULL DEFAULT 'login',
  PRIMARY KEY (`id`),
  KEY `idx_mobile_created` (`mobile`,`created_at`),
  KEY `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== refresh_tokens ===
CREATE TABLE `refresh_tokens` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `user_type` varchar(20) NOT NULL DEFAULT 'customer',
  `jti` varchar(32) NOT NULL COMMENT 'JWT ID',
  `expires_at` datetime NOT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_jti` (`jti`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== admin_refresh_tokens ===
CREATE TABLE `admin_refresh_tokens` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `admin_id` int(10) unsigned NOT NULL,
  `jti` varchar(32) NOT NULL COMMENT 'JWT ID',
  `expires_at` datetime NOT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_jti` (`jti`),
  KEY `idx_admin_id` (`admin_id`),
  KEY `idx_expires` (`expires_at`),
  CONSTRAINT `fk_admin_refresh_tokens_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== permissions ===
CREATE TABLE `permissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL COMMENT 'e.g., products.create, orders.view',
  `name` varchar(100) NOT NULL COMMENT 'Persian name',
  `group_name` varchar(50) NOT NULL COMMENT 'e.g., products, orders',
  `description` varchar(255) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`),
  KEY `idx_group` (`group_name`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== admin_permissions ===
CREATE TABLE `admin_permissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `admin_id` int(10) unsigned NOT NULL,
  `permission_id` int(10) unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_admin_permission` (`admin_id`,`permission_id`),
  KEY `idx_admin` (`admin_id`),
  KEY `idx_permission` (`permission_id`),
  CONSTRAINT `fk_admin_perm_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_admin_perm_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== carts ===
CREATE TABLE `carts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_customer` (`customer_id`),
  CONSTRAINT `fk_carts_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== cart_items ===
CREATE TABLE `cart_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cart_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `quantity` int(10) unsigned NOT NULL DEFAULT 1,
  `has_used_battery` tinyint(1) NOT NULL DEFAULT 0,
  `used_battery_amperage` int(10) unsigned DEFAULT NULL,
  `used_battery_amperage_2` int(10) unsigned DEFAULT NULL,
  `has_installation` tinyint(1) NOT NULL DEFAULT 0,
  `price_snapshot` bigint(20) unsigned NOT NULL COMMENT 'Price at time of adding',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_cart` (`cart_id`),
  KEY `idx_product` (`product_id`),
  CONSTRAINT `fk_cart_items_cart` FOREIGN KEY (`cart_id`) REFERENCES `carts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cart_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== products ===
CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(10) unsigned NOT NULL,
  `brand_id` int(10) unsigned NOT NULL,
  `amperage_id` int(10) unsigned DEFAULT NULL,
  `case_type_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(200) NOT NULL,
  `slug` varchar(200) NOT NULL,
  `sku` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `short_description` varchar(500) DEFAULT NULL,
  `polarity` enum('R','L','LR') DEFAULT 'R',
  `voltage` decimal(4,1) DEFAULT NULL COMMENT 'Battery voltage',
  `price` bigint(20) unsigned NOT NULL COMMENT 'Price in Rial',
  `wholesale_price` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT 'Wholesale selling price in Rial (???????? ???????? ????????)',
  `retail_price` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT 'Retail selling price in Rial (???????? ???????? ????????)',
  `original_price` bigint(20) unsigned DEFAULT NULL COMMENT 'Original price before discount',
  `cost_price` bigint(20) unsigned DEFAULT NULL COMMENT 'Cost price for profit calculation',
  `stock_quantity` int(11) NOT NULL DEFAULT 0,
  `min_stock_alert` int(11) NOT NULL DEFAULT 5,
  `warranty_months` int(11) NOT NULL DEFAULT 12,
  `weight` decimal(8,2) DEFAULT NULL COMMENT 'Weight in kg',
  `dimensions` varchar(50) DEFAULT NULL COMMENT 'LxWxH in cm',
  `has_installation` tinyint(1) NOT NULL DEFAULT 1,
  `installation_fee` bigint(20) unsigned NOT NULL DEFAULT 0,
  `max_per_order` int(10) unsigned DEFAULT NULL COMMENT 'Max quantity per order',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `view_count` int(10) unsigned NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  `cca` int(10) unsigned DEFAULT NULL COMMENT 'Cold Cranking Amps - ?????????? ????????????????????? ??????',
  `rc` int(10) unsigned DEFAULT NULL COMMENT 'Reserve Capacity - ?????????? ?????????? ???? ?????? ??????????',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_slug` (`slug`),
  UNIQUE KEY `uk_sku` (`sku`),
  KEY `idx_category` (`category_id`),
  KEY `idx_brand` (`brand_id`),
  KEY `idx_active` (`is_active`),
  KEY `idx_price` (`price`),
  KEY `fk_products_amperage` (`amperage_id`),
  KEY `fk_products_case_type` (`case_type_id`),
  KEY `idx_catalog_search` (`is_active`,`deleted_at`,`category_id`,`price`),
  KEY `idx_brand_amperage` (`brand_id`,`is_active`),
  FULLTEXT KEY `ft_search` (`name`,`description`,`short_description`),
  CONSTRAINT `fk_products_amperage` FOREIGN KEY (`amperage_id`) REFERENCES `amperages` (`id`),
  CONSTRAINT `fk_products_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`),
  CONSTRAINT `fk_products_case_type` FOREIGN KEY (`case_type_id`) REFERENCES `battery_case_types` (`id`),
  CONSTRAINT `fk_products_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`),
  CONSTRAINT `chk_prod_price_positive` CHECK (`price` > 0),
  CONSTRAINT `chk_prod_stock_non_negative` CHECK (`stock_quantity` >= 0),
  CONSTRAINT `chk_prod_warranty_positive` CHECK (`warranty_months` > 0)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== categories ===
CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `slug` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `has_used_battery` tinyint(1) NOT NULL DEFAULT 0,
  `has_installation` tinyint(1) NOT NULL DEFAULT 0,
  `is_combinable` tinyint(1) NOT NULL DEFAULT 1,
  `shipping_type` enum('store_only','nationwide_cod') NOT NULL DEFAULT 'nationwide_cod',
  `max_per_order` int(10) unsigned DEFAULT NULL COMMENT 'NULL = Unlimited',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== brands ===
CREATE TABLE `brands` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `factory_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(50) NOT NULL,
  `slug` varchar(50) NOT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_slug` (`slug`),
  KEY `fk_brands_factory` (`factory_id`),
  CONSTRAINT `fk_brands_factory` FOREIGN KEY (`factory_id`) REFERENCES `factories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== partner_credits ===
CREATE TABLE `partner_credits` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL,
  `credit_limit` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT 'Maximum credit in Rial',
  `credit_used` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT 'Currently used credit',
  `is_blocked` tinyint(1) NOT NULL DEFAULT 0,
  `blocked_reason` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_customer` (`customer_id`),
  CONSTRAINT `fk_partner_credits_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== party_ledger ===
CREATE TABLE `party_ledger` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `voucher_id` varchar(36) NOT NULL,
  `party_id` int(10) unsigned NOT NULL COMMENT 'Customer/Supplier ID',
  `account_code` varchar(50) NOT NULL COMMENT 'e.g., payable, receivable',
  `debit` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT 'Rial',
  `credit` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT 'Rial',
  `reference_type` varchar(50) NOT NULL,
  `reference_id` int(10) unsigned NOT NULL,
  `description` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_voucher` (`voucher_id`),
  KEY `idx_party` (`party_id`),
  KEY `idx_reference` (`reference_type`,`reference_id`),
  KEY `idx_party_account` (`party_id`,`account_code`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== order_status_history ===
CREATE TABLE `order_status_history` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(10) unsigned NOT NULL,
  `from_status` varchar(20) DEFAULT NULL,
  `to_status` varchar(20) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL COMMENT 'Admin user ID',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_order` (`order_id`),
  KEY `fk_osh_created_by` (`created_by`),
  CONSTRAINT `fk_osh_created_by` FOREIGN KEY (`created_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_status_history_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== registration_tokens ===
CREATE TABLE `registration_tokens` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mobile` varchar(11) NOT NULL,
  `token` varchar(64) NOT NULL,
  `expires_at` datetime NOT NULL,
  `used_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_token` (`token`),
  KEY `idx_mobile` (`mobile`),
  KEY `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

=== uploaded_files ===
CREATE TABLE `uploaded_files` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `type` enum('receipt','product','profile','general') NOT NULL DEFAULT 'general',
  `filename` varchar(100) NOT NULL,
  `original_name` varchar(200) NOT NULL,
  `mime_type` varchar(50) NOT NULL,
  `size` int(10) unsigned NOT NULL,
  `path` varchar(255) NOT NULL,
  `url` varchar(500) NOT NULL,
  `thumbnail_url` varchar(500) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

