-- Add Auction & Marketplace Specific Columns to Products Table
-- This script adds comprehensive fields for auction and marketplace functionality

-- Disable foreign key checks
SET FOREIGN_KEY_CHECKS = 0;

-- Add auction and marketplace specific columns
ALTER TABLE `products`
-- Basic item information
ADD COLUMN IF NOT EXISTS `item_name` VARCHAR(255) DEFAULT NULL COMMENT 'Alternative item name/title',
ADD COLUMN IF NOT EXISTS `tags` TEXT DEFAULT NULL COMMENT 'Comma-separated tags for search and categorization',
ADD COLUMN IF NOT EXISTS `sub_category_id` INT(11) DEFAULT NULL COMMENT 'Sub-category ID for more specific categorization',

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

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

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

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

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

-- Location and tracking
ADD COLUMN IF NOT EXISTS `storage_location` VARCHAR(255) DEFAULT NULL COMMENT 'Physical storage location',
ADD COLUMN IF NOT EXISTS `storage_notes` TEXT DEFAULT NULL COMMENT 'Additional storage information',

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

-- Tracking and analytics
ADD COLUMN IF NOT EXISTS `stock_held_days` INT DEFAULT NULL COMMENT 'Days item was in stock',
ADD COLUMN IF NOT EXISTS `view_count` INT DEFAULT 0 COMMENT 'Number of times item was viewed',
ADD COLUMN IF NOT EXISTS `watch_count` INT DEFAULT 0 COMMENT 'Number of users watching item',
ADD COLUMN IF NOT EXISTS `inquiry_count` INT DEFAULT 0 COMMENT 'Number of inquiries received',

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

-- Add indexes for performance
ALTER TABLE `products`
ADD INDEX IF NOT EXISTS `idx_sub_category` (`sub_category_id`),
ADD INDEX IF NOT EXISTS `idx_owner_name` (`owner_name`),
ADD INDEX IF NOT EXISTS `idx_acquired_date` (`acquired_on`),
ADD INDEX IF NOT EXISTS `idx_sold_date` (`sold_on`),
ADD INDEX IF NOT EXISTS `idx_storage_location` (`storage_location`),
ADD INDEX IF NOT EXISTS `idx_item_status` (`item_status`),
ADD INDEX IF NOT EXISTS `idx_requires_restoration` (`requires_restoration`),
ADD INDEX IF NOT EXISTS `idx_stock_held_days` (`stock_held_days`);

-- Create sub_categories table if it doesn't exist
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`),
    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 foreign key for sub_category_id in products table
ALTER TABLE `products`
ADD CONSTRAINT `fk_products_sub_category` FOREIGN KEY (`sub_category_id`) REFERENCES `sub_categories`(`sub_category_id`) ON DELETE SET NULL;

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

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

-- Fashion sub-categories  
(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'),

-- Home & Garden sub-categories
(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');

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

-- Show completion status
SELECT 'Auction & Marketplace columns added successfully!' as status;

-- Show summary of new columns
SELECT 'New columns added to products table:' as info;
SELECT 
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_COMMENT
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'
)
ORDER BY ORDINAL_POSITION;