-- Safe Auction & Marketplace Setup with Foreign Key Constraint Handling
-- This script safely adds auction/marketplace columns with proper error handling

-- Disable foreign key checks
SET FOREIGN_KEY_CHECKS = 0;

-- Add auction and marketplace specific columns to products table
-- Using individual ALTER statements for better compatibility

-- Basic item information
ALTER TABLE `products` ADD COLUMN `item_name` VARCHAR(255) DEFAULT NULL COMMENT 'Alternative item name/title';
ALTER TABLE `products` ADD COLUMN `tags` TEXT DEFAULT NULL COMMENT 'Comma-separated tags for search and categorization';

-- Physical dimensions  
ALTER TABLE `products` ADD COLUMN `width_cm` DECIMAL(8,2) DEFAULT NULL COMMENT 'Item width in centimeters';
ALTER TABLE `products` ADD COLUMN `length_cm` DECIMAL(8,2) DEFAULT NULL COMMENT 'Item length in centimeters';
ALTER TABLE `products` ADD COLUMN `height_cm` DECIMAL(8,2) DEFAULT NULL COMMENT 'Item height in centimeters';
ALTER TABLE `products` ADD COLUMN `dimensions_notes` VARCHAR(500) DEFAULT NULL COMMENT 'Additional dimension information';

-- Ownership and acquisition
ALTER TABLE `products` ADD COLUMN `owner_name` VARCHAR(255) DEFAULT NULL COMMENT 'Item owner name';
ALTER TABLE `products` ADD COLUMN `owner_contact` VARCHAR(255) DEFAULT NULL COMMENT 'Owner contact information';
ALTER TABLE `products` ADD COLUMN `bought_for` DECIMAL(10,2) DEFAULT NULL COMMENT 'Original purchase price';
ALTER TABLE `products` ADD COLUMN `acquired_on` DATE DEFAULT NULL COMMENT 'Date item was acquired';

-- Cost structure
ALTER TABLE `products` ADD COLUMN `commission_rate` DECIMAL(5,2) DEFAULT NULL COMMENT 'Commission percentage (0-100)';
ALTER TABLE `products` ADD COLUMN `commission_amount` DECIMAL(10,2) DEFAULT NULL COMMENT 'Fixed commission amount';
ALTER TABLE `products` ADD COLUMN `transport_cost` DECIMAL(10,2) DEFAULT NULL COMMENT 'Transportation/shipping costs';
ALTER TABLE `products` ADD COLUMN `total_landed_cost` DECIMAL(10,2) DEFAULT NULL COMMENT 'Total cost including all fees';

-- Restoration information
ALTER TABLE `products` ADD COLUMN `requires_restoration` TINYINT(1) DEFAULT 0 COMMENT 'Whether item needs restoration';
ALTER TABLE `products` ADD COLUMN `restoration_cost` DECIMAL(10,2) DEFAULT NULL COMMENT 'Cost of restoration work';
ALTER TABLE `products` ADD COLUMN `restoration_notes` TEXT DEFAULT NULL COMMENT 'Restoration work details';
ALTER TABLE `products` ADD COLUMN `total_restored_cost` DECIMAL(10,2) DEFAULT NULL COMMENT 'Total cost after restoration';

-- Valuation
ALTER TABLE `products` ADD COLUMN `estimated_value_low` DECIMAL(10,2) DEFAULT NULL COMMENT 'Conservative estimate value';
ALTER TABLE `products` ADD COLUMN `estimated_value_high` DECIMAL(10,2) DEFAULT NULL COMMENT 'Optimistic estimate value';
ALTER TABLE `products` ADD COLUMN `valuation_date` DATE DEFAULT NULL COMMENT 'Date of valuation';
ALTER TABLE `products` ADD COLUMN `valuation_notes` TEXT DEFAULT NULL COMMENT 'Valuation methodology and notes';

-- Location and tracking
ALTER TABLE `products` ADD COLUMN `storage_location` VARCHAR(255) DEFAULT NULL COMMENT 'Physical storage location';
ALTER TABLE `products` ADD COLUMN `storage_notes` TEXT DEFAULT NULL COMMENT 'Additional storage information';

-- Sales information
ALTER TABLE `products` ADD COLUMN `sold_on` DATETIME DEFAULT NULL COMMENT 'Date and time item was sold';
ALTER TABLE `products` ADD COLUMN `sold_price` DECIMAL(10,2) DEFAULT NULL COMMENT 'Final sale price';
ALTER TABLE `products` ADD COLUMN `sale_method` ENUM('auction', 'fixed_price', 'negotiated', 'consignment') DEFAULT NULL COMMENT 'How item was sold';
ALTER TABLE `products` ADD COLUMN `buyer_info` VARCHAR(500) DEFAULT NULL COMMENT 'Buyer information (encrypted)';

-- Tracking and analytics
ALTER TABLE `products` ADD COLUMN `stock_held_days` INT DEFAULT NULL COMMENT 'Days item was in stock';
ALTER TABLE `products` ADD COLUMN `view_count` INT DEFAULT 0 COMMENT 'Number of times item was viewed';
ALTER TABLE `products` ADD COLUMN `watch_count` INT DEFAULT 0 COMMENT 'Number of users watching item';
ALTER TABLE `products` ADD COLUMN `inquiry_count` INT DEFAULT 0 COMMENT 'Number of inquiries received';

-- Status and notes
ALTER TABLE `products` ADD COLUMN `item_status` ENUM('draft', 'active', 'sold', 'withdrawn', 'archived') DEFAULT 'draft' COMMENT 'Current item status';
ALTER TABLE `products` ADD COLUMN `description_detailed` LONGTEXT DEFAULT NULL COMMENT 'Detailed item description for listings';
ALTER TABLE `products` ADD COLUMN `internal_notes` TEXT DEFAULT NULL COMMENT 'Internal notes not shown to buyers';
ALTER TABLE `products` ADD COLUMN `condition_report` TEXT DEFAULT NULL COMMENT 'Detailed condition assessment';

-- Create sub_categories table WITHOUT foreign key constraint initially
CREATE TABLE IF NOT EXISTS `sub_categories` (
    `sub_category_id` INT(11) NOT NULL AUTO_INCREMENT,
    `category_id` INT(11) 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Sub-categories for detailed product classification';

-- Add sub_category_id column to products table (without foreign key for now)
ALTER TABLE `products` ADD COLUMN `sub_category_id` INT(11) DEFAULT NULL COMMENT 'Sub-category ID for more specific categorization';

-- Insert sample sub-categories
INSERT IGNORE INTO `sub_categories` (`category_id`, `sub_category_name`, `sub_category_description`) VALUES
-- Electronics sub-categories (assuming category_id 1 is Electronics)
(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'),

-- Other categories (using common category IDs)
(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
ALTER TABLE `products` ADD INDEX `idx_owner_name` (`owner_name`);
ALTER TABLE `products` ADD INDEX `idx_acquired_date` (`acquired_on`);
ALTER TABLE `products` ADD INDEX `idx_sold_date` (`sold_on`);
ALTER TABLE `products` ADD INDEX `idx_storage_location` (`storage_location`);
ALTER TABLE `products` ADD INDEX `idx_item_status` (`item_status`);
ALTER TABLE `products` ADD INDEX `idx_requires_restoration` (`requires_restoration`);
ALTER TABLE `products` ADD INDEX `idx_stock_held_days` (`stock_held_days`);
ALTER TABLE `products` ADD INDEX `idx_sub_category` (`sub_category_id`);

-- Create triggers for automatic calculations
DELIMITER $$

-- Trigger to calculate total_landed_cost
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$$

-- Trigger to calculate stock holding days when sold
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
SELECT 'Safe Auction & Marketplace setup completed successfully!' as status;

-- Show count of added columns
SELECT 'New columns added to products table' as info;
SELECT COUNT(*) as column_count 
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',
    'owner_name', 'bought_for', 'commission_rate', 'transport_cost', 'total_landed_cost',
    'requires_restoration', 'restoration_cost', 'total_restored_cost',
    'estimated_value_low', 'estimated_value_high', 'storage_location', 
    'acquired_on', 'sold_on', 'stock_held_days', 'description_detailed', 'item_status'
);

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