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;
