-- Final Auction & Marketplace Setup - Optimized for Existing Database Structure
-- Based on debug results: categories table exists with category_id as primary key

-- Disable foreign key checks
SET FOREIGN_KEY_CHECKS = 0;

-- Add auction and marketplace specific columns to products table
-- Using individual ALTER statements with IF NOT EXISTS logic

-- Basic item information
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'item_name') = 0,
    'ALTER TABLE `products` ADD COLUMN `item_name` VARCHAR(255) DEFAULT NULL COMMENT "Alternative item name/title"',
    'SELECT "item_name column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'tags') = 0,
    'ALTER TABLE `products` ADD COLUMN `tags` TEXT DEFAULT NULL COMMENT "Comma-separated tags for search and categorization"',
    'SELECT "tags column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Physical dimensions
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'width_cm') = 0,
    'ALTER TABLE `products` ADD COLUMN `width_cm` DECIMAL(8,2) DEFAULT NULL COMMENT "Item width in centimeters"',
    'SELECT "width_cm column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'length_cm') = 0,
    'ALTER TABLE `products` ADD COLUMN `length_cm` DECIMAL(8,2) DEFAULT NULL COMMENT "Item length in centimeters"',
    'SELECT "length_cm column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'height_cm') = 0,
    'ALTER TABLE `products` ADD COLUMN `height_cm` DECIMAL(8,2) DEFAULT NULL COMMENT "Item height in centimeters"',
    'SELECT "height_cm column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'dimensions_notes') = 0,
    'ALTER TABLE `products` ADD COLUMN `dimensions_notes` VARCHAR(500) DEFAULT NULL COMMENT "Additional dimension information"',
    'SELECT "dimensions_notes column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Ownership and acquisition
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'owner_name') = 0,
    'ALTER TABLE `products` ADD COLUMN `owner_name` VARCHAR(255) DEFAULT NULL COMMENT "Item owner name"',
    'SELECT "owner_name column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'owner_contact') = 0,
    'ALTER TABLE `products` ADD COLUMN `owner_contact` VARCHAR(255) DEFAULT NULL COMMENT "Owner contact information"',
    'SELECT "owner_contact column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'bought_for') = 0,
    'ALTER TABLE `products` ADD COLUMN `bought_for` DECIMAL(10,2) DEFAULT NULL COMMENT "Original purchase price"',
    'SELECT "bought_for column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'acquired_on') = 0,
    'ALTER TABLE `products` ADD COLUMN `acquired_on` DATE DEFAULT NULL COMMENT "Date item was acquired"',
    'SELECT "acquired_on column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Cost structure
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'commission_rate') = 0,
    'ALTER TABLE `products` ADD COLUMN `commission_rate` DECIMAL(5,2) DEFAULT NULL COMMENT "Commission percentage (0-100)"',
    'SELECT "commission_rate column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'commission_amount') = 0,
    'ALTER TABLE `products` ADD COLUMN `commission_amount` DECIMAL(10,2) DEFAULT NULL COMMENT "Fixed commission amount"',
    'SELECT "commission_amount column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'transport_cost') = 0,
    'ALTER TABLE `products` ADD COLUMN `transport_cost` DECIMAL(10,2) DEFAULT NULL COMMENT "Transportation/shipping costs"',
    'SELECT "transport_cost column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'total_landed_cost') = 0,
    'ALTER TABLE `products` ADD COLUMN `total_landed_cost` DECIMAL(10,2) DEFAULT NULL COMMENT "Total cost including all fees"',
    'SELECT "total_landed_cost column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Restoration information
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'requires_restoration') = 0,
    'ALTER TABLE `products` ADD COLUMN `requires_restoration` TINYINT(1) DEFAULT 0 COMMENT "Whether item needs restoration"',
    'SELECT "requires_restoration column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'restoration_cost') = 0,
    'ALTER TABLE `products` ADD COLUMN `restoration_cost` DECIMAL(10,2) DEFAULT NULL COMMENT "Cost of restoration work"',
    'SELECT "restoration_cost column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'restoration_notes') = 0,
    'ALTER TABLE `products` ADD COLUMN `restoration_notes` TEXT DEFAULT NULL COMMENT "Restoration work details"',
    'SELECT "restoration_notes column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'total_restored_cost') = 0,
    'ALTER TABLE `products` ADD COLUMN `total_restored_cost` DECIMAL(10,2) DEFAULT NULL COMMENT "Total cost after restoration"',
    'SELECT "total_restored_cost column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Valuation
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'estimated_value_low') = 0,
    'ALTER TABLE `products` ADD COLUMN `estimated_value_low` DECIMAL(10,2) DEFAULT NULL COMMENT "Conservative estimate value"',
    'SELECT "estimated_value_low column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'estimated_value_high') = 0,
    'ALTER TABLE `products` ADD COLUMN `estimated_value_high` DECIMAL(10,2) DEFAULT NULL COMMENT "Optimistic estimate value"',
    'SELECT "estimated_value_high column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'valuation_date') = 0,
    'ALTER TABLE `products` ADD COLUMN `valuation_date` DATE DEFAULT NULL COMMENT "Date of valuation"',
    'SELECT "valuation_date column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'valuation_notes') = 0,
    'ALTER TABLE `products` ADD COLUMN `valuation_notes` TEXT DEFAULT NULL COMMENT "Valuation methodology and notes"',
    'SELECT "valuation_notes column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Location and tracking
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'storage_location') = 0,
    'ALTER TABLE `products` ADD COLUMN `storage_location` VARCHAR(255) DEFAULT NULL COMMENT "Physical storage location"',
    'SELECT "storage_location column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'storage_notes') = 0,
    'ALTER TABLE `products` ADD COLUMN `storage_notes` TEXT DEFAULT NULL COMMENT "Additional storage information"',
    'SELECT "storage_notes column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Sales information
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'sold_on') = 0,
    'ALTER TABLE `products` ADD COLUMN `sold_on` DATETIME DEFAULT NULL COMMENT "Date and time item was sold"',
    'SELECT "sold_on column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'sold_price') = 0,
    'ALTER TABLE `products` ADD COLUMN `sold_price` DECIMAL(10,2) DEFAULT NULL COMMENT "Final sale price"',
    'SELECT "sold_price column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'sale_method') = 0,
    'ALTER TABLE `products` ADD COLUMN `sale_method` ENUM("auction", "fixed_price", "negotiated", "consignment") DEFAULT NULL COMMENT "How item was sold"',
    'SELECT "sale_method column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'buyer_info') = 0,
    'ALTER TABLE `products` ADD COLUMN `buyer_info` VARCHAR(500) DEFAULT NULL COMMENT "Buyer information (encrypted)"',
    'SELECT "buyer_info column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Tracking and analytics
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'stock_held_days') = 0,
    'ALTER TABLE `products` ADD COLUMN `stock_held_days` INT DEFAULT NULL COMMENT "Days item was in stock"',
    'SELECT "stock_held_days column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'view_count') = 0,
    'ALTER TABLE `products` ADD COLUMN `view_count` INT DEFAULT 0 COMMENT "Number of times item was viewed"',
    'SELECT "view_count column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'watch_count') = 0,
    'ALTER TABLE `products` ADD COLUMN `watch_count` INT DEFAULT 0 COMMENT "Number of users watching item"',
    'SELECT "watch_count column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'inquiry_count') = 0,
    'ALTER TABLE `products` ADD COLUMN `inquiry_count` INT DEFAULT 0 COMMENT "Number of inquiries received"',
    'SELECT "inquiry_count column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Status and notes
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'item_status') = 0,
    'ALTER TABLE `products` ADD COLUMN `item_status` ENUM("draft", "active", "sold", "withdrawn", "archived") DEFAULT "draft" COMMENT "Current item status"',
    'SELECT "item_status column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'description_detailed') = 0,
    'ALTER TABLE `products` ADD COLUMN `description_detailed` LONGTEXT DEFAULT NULL COMMENT "Detailed item description for listings"',
    'SELECT "description_detailed column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'internal_notes') = 0,
    'ALTER TABLE `products` ADD COLUMN `internal_notes` TEXT DEFAULT NULL COMMENT "Internal notes not shown to buyers"',
    'SELECT "internal_notes column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'condition_report') = 0,
    'ALTER TABLE `products` ADD COLUMN `condition_report` TEXT DEFAULT NULL COMMENT "Detailed condition assessment"',
    'SELECT "condition_report column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Create sub_categories table with proper foreign key constraint to existing categories table
CREATE TABLE IF NOT EXISTS `sub_categories` (
    `sub_category_id` INT(11) NOT NULL AUTO_INCREMENT,
    `category_id` INT(10) UNSIGNED NOT NULL,
    `sub_category_name` VARCHAR(100) NOT NULL,
    `sub_category_description` TEXT DEFAULT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `display_order` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`sub_category_id`),
    INDEX `idx_category_id` (`category_id`),
    INDEX `idx_is_active` (`is_active`),
    CONSTRAINT `fk_sub_categories_category` FOREIGN KEY (`category_id`) REFERENCES `categories`(`category_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Sub-categories for detailed product classification';

-- Add sub_category_id column to products table
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'sub_category_id') = 0,
    'ALTER TABLE `products` ADD COLUMN `sub_category_id` INT(11) DEFAULT NULL COMMENT "Sub-category ID for more specific categorization"',
    'SELECT "sub_category_id column already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add foreign key constraint for sub_category_id (after column is created)
SET @constraint_exists = (
    SELECT COUNT(*) 
    FROM information_schema.TABLE_CONSTRAINTS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'products' 
    AND CONSTRAINT_NAME = 'fk_products_sub_category'
);

SET @sql = IF(@constraint_exists = 0,
    'ALTER TABLE `products` ADD CONSTRAINT `fk_products_sub_category` FOREIGN KEY (`sub_category_id`) REFERENCES `sub_categories`(`sub_category_id`) ON DELETE SET NULL',
    'SELECT "sub_category foreign key already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Insert sample sub-categories (using existing category_id values)
INSERT IGNORE INTO `sub_categories` (`category_id`, `sub_category_name`, `sub_category_description`) VALUES
-- Using the actual category_id values from your existing categories
(1, 'Laptops', 'Portable computers and notebooks'),
(1, 'Smartphones', 'Mobile phones and accessories'),
(1, 'TVs & Monitors', 'Television and display devices'),
(1, 'Audio Equipment', 'Speakers, headphones, and sound systems'),
(1, 'Gaming Consoles', 'Video game consoles and accessories'),
(2, 'Designer Clothing', 'High-end and designer fashion items'),
(2, 'Vintage Fashion', 'Retro and vintage clothing'),  
(2, 'Jewelry', 'Fine and costume jewelry'),
(2, 'Watches', 'Timepieces and watch accessories'),
(2, 'Handbags', 'Purses, bags, and leather goods'),
(3, 'Furniture', 'Tables, chairs, and home furnishing'),
(3, 'Antiques', 'Vintage and antique home items'),
(3, 'Art & Decor', 'Paintings, sculptures, and decorative items'),
(3, 'Kitchen Appliances', 'Cooking and kitchen equipment'),
(3, 'Garden Tools', 'Outdoor and gardening equipment');

-- Add indexes for performance (check if they exist first)
SET @index_exists = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_owner_name');
SET @sql = IF(@index_exists = 0, 'ALTER TABLE `products` ADD INDEX `idx_owner_name` (`owner_name`)', 'SELECT "idx_owner_name already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_acquired_date');
SET @sql = IF(@index_exists = 0, 'ALTER TABLE `products` ADD INDEX `idx_acquired_date` (`acquired_on`)', 'SELECT "idx_acquired_date already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_sold_date');
SET @sql = IF(@index_exists = 0, 'ALTER TABLE `products` ADD INDEX `idx_sold_date` (`sold_on`)', 'SELECT "idx_sold_date already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_storage_location');
SET @sql = IF(@index_exists = 0, 'ALTER TABLE `products` ADD INDEX `idx_storage_location` (`storage_location`)', 'SELECT "idx_storage_location already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_item_status');
SET @sql = IF(@index_exists = 0, 'ALTER TABLE `products` ADD INDEX `idx_item_status` (`item_status`)', 'SELECT "idx_item_status already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_requires_restoration');
SET @sql = IF(@index_exists = 0, 'ALTER TABLE `products` ADD INDEX `idx_requires_restoration` (`requires_restoration`)', 'SELECT "idx_requires_restoration already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @index_exists = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_sub_category');
SET @sql = IF(@index_exists = 0, 'ALTER TABLE `products` ADD INDEX `idx_sub_category` (`sub_category_id`)', 'SELECT "idx_sub_category already exists" as message');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Create triggers for automatic calculations
DELIMITER $$

DROP TRIGGER IF EXISTS `calculate_landed_cost`$$
CREATE TRIGGER `calculate_landed_cost`
BEFORE INSERT ON `products`
FOR EACH ROW
BEGIN
    -- Calculate total landed cost if components are provided
    IF NEW.bought_for IS NOT NULL THEN
        SET NEW.total_landed_cost = COALESCE(NEW.bought_for, 0) + 
                                   COALESCE(NEW.commission_amount, 0) + 
                                   COALESCE(NEW.transport_cost, 0);
    END IF;
    
    -- Calculate total restored cost
    IF NEW.total_landed_cost IS NOT NULL AND NEW.restoration_cost IS NOT NULL THEN
        SET NEW.total_restored_cost = NEW.total_landed_cost + NEW.restoration_cost;
    END IF;
END$$

DROP TRIGGER IF EXISTS `calculate_stock_days_on_sale`$$
CREATE TRIGGER `calculate_stock_days_on_sale`
BEFORE UPDATE ON `products`
FOR EACH ROW
BEGIN
    -- Calculate stock held days when item is marked as sold
    IF OLD.sold_on IS NULL AND NEW.sold_on IS NOT NULL THEN
        IF NEW.acquired_on IS NOT NULL THEN
            SET NEW.stock_held_days = DATEDIFF(NEW.sold_on, NEW.acquired_on);
        ELSE
            SET NEW.stock_held_days = DATEDIFF(NEW.sold_on, NEW.created_at);
        END IF;
        
        -- Set item status to sold
        SET NEW.item_status = 'sold';
    END IF;
END$$

DELIMITER ;

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

-- Show completion status and summary
SELECT 'Final Auction & Marketplace setup completed successfully!' as status;

SELECT 'Summary of additions:' as info;
SELECT COUNT(*) as new_columns_added 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'products' 
AND COLUMN_NAME IN (
    'item_name', 'tags', 'sub_category_id', 'width_cm', 'length_cm', 'height_cm', 'dimensions_notes',
    'owner_name', 'owner_contact', 'bought_for', 'acquired_on',
    'commission_rate', 'commission_amount', 'transport_cost', 'total_landed_cost',
    'requires_restoration', 'restoration_cost', 'restoration_notes', 'total_restored_cost',
    'estimated_value_low', 'estimated_value_high', 'valuation_date', 'valuation_notes',
    'storage_location', 'storage_notes', 'sold_on', 'sold_price', 'sale_method', 'buyer_info',
    'stock_held_days', 'view_count', 'watch_count', 'inquiry_count',
    'item_status', 'description_detailed', 'internal_notes', 'condition_report'
);

SELECT 'Sub-categories created:' as info, COUNT(*) as count FROM sub_categories;