-- First, create categories table without self-referencing foreign key
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;

-- Add the self-referencing foreign key for parent categories
-- Using ALTER TABLE to avoid issues with table creation
ALTER TABLE `categories` 
    DROP FOREIGN KEY IF EXISTS `fk_parent_category`;
ALTER TABLE `categories`
    ADD CONSTRAINT `fk_parent_category` 
    FOREIGN KEY (`parent_category_id`) 
    REFERENCES `categories` (`category_id`) 
    ON DELETE SET NULL
    ON UPDATE CASCADE;

-- 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;

-- Add foreign key constraint for warehouse_bins
ALTER TABLE `warehouse_bins`
    DROP FOREIGN KEY IF EXISTS `fk_bin_category`;
ALTER TABLE `warehouse_bins`
    ADD CONSTRAINT `fk_bin_category` 
    FOREIGN KEY (`category_id`) 
    REFERENCES `categories` (`category_id`) 
    ON DELETE SET NULL
    ON UPDATE CASCADE;

-- Insert sample categories if table is empty
INSERT INTO `categories` (`category_name`, `parent_category_id`, `is_active`) 
SELECT * FROM (
    SELECT 'Electronics' as category_name, NULL as parent_category_id, 1 as is_active
    UNION ALL SELECT 'Clothing', NULL, 1
    UNION ALL SELECT 'Home & Garden', NULL, 1
    UNION ALL SELECT 'Sports & Outdoors', NULL, 1
    UNION ALL SELECT 'Toys & Games', NULL, 1
    UNION ALL SELECT 'Books & Media', NULL, 1
    UNION ALL SELECT 'Automotive', NULL, 1
    UNION ALL SELECT 'Health & Beauty', NULL, 1
) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `categories` LIMIT 1);

-- Insert subcategories for Electronics
INSERT INTO `categories` (`category_name`, `parent_category_id`, `is_active`)
SELECT 'Computers & Laptops', category_id, 1 FROM `categories` WHERE `category_name` = 'Electronics' AND NOT EXISTS (SELECT 1 FROM `categories` WHERE `category_name` = 'Computers & Laptops');

INSERT INTO `categories` (`category_name`, `parent_category_id`, `is_active`)
SELECT 'Mobile Phones', category_id, 1 FROM `categories` WHERE `category_name` = 'Electronics' AND NOT EXISTS (SELECT 1 FROM `categories` WHERE `category_name` = 'Mobile Phones');

INSERT INTO `categories` (`category_name`, `parent_category_id`, `is_active`)
SELECT 'Audio & Video', category_id, 1 FROM `categories` WHERE `category_name` = 'Electronics' AND NOT EXISTS (SELECT 1 FROM `categories` WHERE `category_name` = 'Audio & Video');

INSERT INTO `categories` (`category_name`, `parent_category_id`, `is_active`)
SELECT 'Gaming', category_id, 1 FROM `categories` WHERE `category_name` = 'Electronics' AND NOT EXISTS (SELECT 1 FROM `categories` WHERE `category_name` = 'Gaming');

-- Insert subcategories for Clothing
INSERT INTO `categories` (`category_name`, `parent_category_id`, `is_active`)
SELECT 'Men\'s Clothing', category_id, 1 FROM `categories` WHERE `category_name` = 'Clothing' AND NOT EXISTS (SELECT 1 FROM `categories` WHERE `category_name` = 'Men\'s Clothing');

INSERT INTO `categories` (`category_name`, `parent_category_id`, `is_active`)
SELECT 'Women\'s Clothing', category_id, 1 FROM `categories` WHERE `category_name` = 'Clothing' AND NOT EXISTS (SELECT 1 FROM `categories` WHERE `category_name` = 'Women\'s Clothing');

INSERT INTO `categories` (`category_name`, `parent_category_id`, `is_active`)
SELECT 'Children\'s Clothing', category_id, 1 FROM `categories` WHERE `category_name` = 'Clothing' AND NOT EXISTS (SELECT 1 FROM `categories` WHERE `category_name` = 'Children\'s Clothing');

INSERT INTO `categories` (`category_name`, `parent_category_id`, `is_active`)
SELECT 'Shoes & Accessories', category_id, 1 FROM `categories` WHERE `category_name` = 'Clothing' AND NOT EXISTS (SELECT 1 FROM `categories` WHERE `category_name` = 'Shoes & Accessories');

-- Insert sample warehouse bins if table is empty
INSERT INTO `warehouse_bins` (`bin_code`, `location`, `capacity`, `current_occupancy`, `category_id`)
SELECT * FROM (
    SELECT 'A1-01' as bin_code, 'Warehouse A, Row 1, Section 1' as location, 100 as capacity, 0 as current_occupancy, NULL as category_id
    UNION ALL SELECT 'A1-02', 'Warehouse A, Row 1, Section 2', 100, 0, NULL
    UNION ALL SELECT 'A1-03', 'Warehouse A, Row 1, Section 3', 100, 0, NULL
    UNION ALL SELECT 'A2-01', 'Warehouse A, Row 2, Section 1', 150, 0, NULL
    UNION ALL SELECT 'A2-02', 'Warehouse A, Row 2, Section 2', 150, 0, NULL
    UNION ALL SELECT 'B1-01', 'Warehouse B, Row 1, Section 1', 200, 0, NULL
    UNION ALL SELECT 'B1-02', 'Warehouse B, Row 1, Section 2', 200, 0, NULL
    UNION ALL SELECT 'B2-01', 'Warehouse B, Row 2, Section 1', 250, 0, NULL
    UNION ALL SELECT 'C1-01', 'Warehouse C, Row 1, Section 1', 300, 0, NULL
    UNION ALL SELECT 'C1-02', 'Warehouse C, Row 1, Section 2', 300, 0, NULL
) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `warehouse_bins` LIMIT 1);

-- Check if products table exists before trying to alter it
-- Add bin_id column to products table if it doesn't exist
-- Note: The IF NOT EXISTS clause for ADD COLUMN is MySQL 8.0+
-- For older versions, you may need to check manually or ignore errors

-- First check if the column exists using information_schema
SELECT COUNT(*) INTO @col_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'products' 
AND COLUMN_NAME = 'bin_id';

SET @sql = IF(@col_exists = 0, 
    'ALTER TABLE `products` ADD COLUMN `bin_id` INT(11) DEFAULT NULL, ADD KEY `idx_bin` (`bin_id`)',
    'SELECT "Column bin_id already exists" AS message');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add foreign key constraint if not exists
ALTER TABLE `products`
    DROP FOREIGN KEY IF EXISTS `fk_product_bin`;
    
-- Only add if products table exists
SET @table_exists = (SELECT COUNT(*) FROM information_schema.tables 
                     WHERE table_schema = DATABASE() 
                     AND table_name = 'products');
                     
SET @sql2 = IF(@table_exists > 0,
    'ALTER TABLE `products` ADD CONSTRAINT `fk_product_bin` FOREIGN KEY (`bin_id`) REFERENCES `warehouse_bins` (`bin_id`) ON DELETE SET NULL ON UPDATE CASCADE',
    'SELECT "Products table does not exist yet" AS message');
    
PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
