-- Simple version of category tables creation
-- Run this script in your MySQL database

-- Drop existing foreign key constraints if they exist
SET FOREIGN_KEY_CHECKS=0;

-- Create categories table
CREATE TABLE IF NOT EXISTS `categories` (
    `category_id` INT(11) NOT NULL AUTO_INCREMENT,
    `category_name` VARCHAR(100) NOT NULL,
    `parent_category_id` INT(11) DEFAULT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`category_id`),
    KEY `idx_parent` (`parent_category_id`),
    KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create warehouse_bins table
CREATE TABLE IF NOT EXISTS `warehouse_bins` (
    `bin_id` INT(11) NOT NULL AUTO_INCREMENT,
    `bin_code` VARCHAR(50) NOT NULL UNIQUE,
    `location` VARCHAR(200) NOT NULL,
    `capacity` INT(11) NOT NULL DEFAULT 100,
    `current_occupancy` INT(11) NOT NULL DEFAULT 0,
    `category_id` INT(11) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`bin_id`),
    KEY `idx_bin_code` (`bin_code`),
    KEY `idx_category` (`category_id`),
    KEY `idx_occupancy` (`current_occupancy`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS=1;

-- Insert sample main categories
INSERT IGNORE INTO `categories` (`category_id`, `category_name`, `parent_category_id`, `is_active`) VALUES
(1, 'Electronics', NULL, 1),
(2, 'Clothing', NULL, 1),
(3, 'Home & Garden', NULL, 1),
(4, 'Sports & Outdoors', NULL, 1),
(5, 'Toys & Games', NULL, 1),
(6, 'Books & Media', NULL, 1),
(7, 'Automotive', NULL, 1),
(8, 'Health & Beauty', NULL, 1);

-- Insert subcategories for Electronics
INSERT IGNORE INTO `categories` (`category_name`, `parent_category_id`, `is_active`) VALUES
('Computers & Laptops', 1, 1),
('Mobile Phones & Tablets', 1, 1),
('Audio & Video', 1, 1),
('Cameras & Photography', 1, 1),
('Gaming & Consoles', 1, 1),
('Smart Home', 1, 1);

-- Insert sub-subcategories for Computers & Laptops
INSERT IGNORE INTO `categories` (`category_name`, `parent_category_id`, `is_active`) VALUES
('Desktop Computers', (SELECT category_id FROM (SELECT category_id FROM categories WHERE category_name = 'Computers & Laptops' LIMIT 1) AS tmp), 1),
('Laptops', (SELECT category_id FROM (SELECT category_id FROM categories WHERE category_name = 'Computers & Laptops' LIMIT 1) AS tmp), 1),
('Computer Components', (SELECT category_id FROM (SELECT category_id FROM categories WHERE category_name = 'Computers & Laptops' LIMIT 1) AS tmp), 1),
('Computer Accessories', (SELECT category_id FROM (SELECT category_id FROM categories WHERE category_name = 'Computers & Laptops' LIMIT 1) AS tmp), 1);

-- Insert subcategories for Clothing
INSERT IGNORE INTO `categories` (`category_name`, `parent_category_id`, `is_active`) VALUES
('Men\'s Clothing', 2, 1),
('Women\'s Clothing', 2, 1),
('Kids\' Clothing', 2, 1),
('Shoes', 2, 1),
('Accessories', 2, 1);

-- Insert sub-subcategories for Men's Clothing
INSERT IGNORE INTO `categories` (`category_name`, `parent_category_id`, `is_active`) VALUES
('Men\'s Shirts', (SELECT category_id FROM (SELECT category_id FROM categories WHERE category_name = 'Men\'s Clothing' LIMIT 1) AS tmp), 1),
('Men\'s Pants', (SELECT category_id FROM (SELECT category_id FROM categories WHERE category_name = 'Men\'s Clothing' LIMIT 1) AS tmp), 1),
('Men\'s Jackets', (SELECT category_id FROM (SELECT category_id FROM categories WHERE category_name = 'Men\'s Clothing' LIMIT 1) AS tmp), 1),
('Men\'s Suits', (SELECT category_id FROM (SELECT category_id FROM categories WHERE category_name = 'Men\'s Clothing' LIMIT 1) AS tmp), 1);

-- Insert subcategories for Home & Garden
INSERT IGNORE INTO `categories` (`category_name`, `parent_category_id`, `is_active`) VALUES
('Furniture', 3, 1),
('Kitchen & Dining', 3, 1),
('Bedding & Bath', 3, 1),
('Home Decor', 3, 1),
('Garden & Outdoor', 3, 1),
('Tools & Hardware', 3, 1);

-- Insert sub-subcategories for Furniture
INSERT IGNORE INTO `categories` (`category_name`, `parent_category_id`, `is_active`) VALUES
('Living Room Furniture', (SELECT category_id FROM (SELECT category_id FROM categories WHERE category_name = 'Furniture' LIMIT 1) AS tmp), 1),
('Bedroom Furniture', (SELECT category_id FROM (SELECT category_id FROM categories WHERE category_name = 'Furniture' LIMIT 1) AS tmp), 1),
('Office Furniture', (SELECT category_id FROM (SELECT category_id FROM categories WHERE category_name = 'Furniture' LIMIT 1) AS tmp), 1),
('Dining Room Furniture', (SELECT category_id FROM (SELECT category_id FROM categories WHERE category_name = 'Furniture' LIMIT 1) AS tmp), 1);

-- Insert subcategories for Sports & Outdoors
INSERT IGNORE INTO `categories` (`category_name`, `parent_category_id`, `is_active`) VALUES
('Exercise & Fitness', 4, 1),
('Outdoor Recreation', 4, 1),
('Team Sports', 4, 1),
('Water Sports', 4, 1),
('Winter Sports', 4, 1);

-- Insert sample bins if needed
INSERT IGNORE INTO `warehouse_bins` (`bin_code`, `location`, `capacity`, `current_occupancy`) VALUES
('A1-01', 'Warehouse A, Row 1, Section 1', 100, 0),
('A1-02', 'Warehouse A, Row 1, Section 2', 100, 0),
('A1-03', 'Warehouse A, Row 1, Section 3', 100, 0),
('A2-01', 'Warehouse A, Row 2, Section 1', 150, 0),
('A2-02', 'Warehouse A, Row 2, Section 2', 150, 0),
('B1-01', 'Warehouse B, Row 1, Section 1', 200, 0),
('B1-02', 'Warehouse B, Row 1, Section 2', 200, 0),
('B2-01', 'Warehouse B, Row 2, Section 1', 250, 0),
('C1-01', 'Warehouse C, Row 1, Section 1', 300, 0),
('C1-02', 'Warehouse C, Row 1, Section 2', 300, 0);