CREATE TABLE `product` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `product_code` varchar(255) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_cover` varchar(255) DEFAULT NULL,
  `product_desc` text DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `brand_id` int(11) DEFAULT NULL,
  `engine_id` int(11) DEFAULT NULL,
  `stock_min` int(11) NOT NULL DEFAULT 0,
  `stock` int(11) NOT NULL DEFAULT 0,
  `normal_price` decimal(10,2) DEFAULT NULL,
  `export_price` decimal(10,2) DEFAULT NULL,
  `product_status` varchar(255) DEFAULT NULL,
  `perusahaan_id` int(11) DEFAULT NULL,
  `satuan_id` int(11) DEFAULT NULL,
  `satuan_value` int(11) DEFAULT NULL,
  `engine_model` varchar(255) DEFAULT NULL,
  `fact_no` varchar(255) DEFAULT NULL,
  `oem_no` varchar(255) DEFAULT NULL,
  `part_no` varchar(255) DEFAULT NULL,
  `supplier` varchar(255) DEFAULT NULL,
  `barcode` varchar(255) DEFAULT NULL,
  `product_code_shadow` varchar(255) DEFAULT NULL,
  `is_liner` tinyint(1) NOT NULL DEFAULT 0,
  `location` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `updated_by` bigint(20) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_images` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `product_id` bigint(20) UNSIGNED NOT NULL,
  `filename` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Category Table
CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `image_url` varchar(255) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `parent_id` (`parent_id`),
  KEY `is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Alter product table to add foreign key to category
ALTER TABLE `product` 
ADD CONSTRAINT `fk_product_category` 
FOREIGN KEY (`category_id`) REFERENCES `category`(`id`) 
ON DELETE SET NULL ON UPDATE CASCADE;

-- Add indexes for better performance
ALTER TABLE `product` ADD INDEX `idx_product_category` (`category_id`);
ALTER TABLE `product` ADD INDEX `idx_product_status` (`product_status`);
ALTER TABLE `product` ADD INDEX `idx_product_code` (`product_code`);
ALTER TABLE `product` ADD INDEX `idx_product_name` (`product_name`);
ALTER TABLE `product_images` ADD INDEX `idx_product_images_product` (`product_id`);

-- Add best seller flag
ALTER TABLE `product` ADD COLUMN `is_bestseller` tinyint(1) DEFAULT NULL;