-- XChange 2025 Product Management System Migration
-- This script migrates from boolean table fields to flexible product system
-- Run this script to upgrade the database schema

-- Step 1: Create Product Categories Table
CREATE TABLE IF NOT EXISTS product_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Step 2: Create Products Master Table
CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    product_code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    base_price DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    
    -- Inventory Management Fields
    total_stock INT DEFAULT NULL, -- NULL = unlimited, number = limited stock
    reserved_stock INT DEFAULT 0, -- Stock held for pending payments
    sold_stock INT DEFAULT 0, -- Actually sold/confirmed stock
    available_stock INT GENERATED ALWAYS AS (
        CASE 
            WHEN total_stock IS NULL THEN 999999 
            ELSE GREATEST(0, total_stock - reserved_stock - sold_stock)
        END
    ) STORED,
    
    -- Availability Controls
    sale_start_date DATETIME DEFAULT NULL,
    sale_end_date DATETIME DEFAULT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    requires_approval BOOLEAN DEFAULT FALSE,
    
    max_per_registration INT DEFAULT 1, -- Max quantity per registration
    display_order INT DEFAULT 0,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (category_id) REFERENCES product_categories(id),
    INDEX idx_product_code (product_code),
    INDEX idx_category (category_id),
    INDEX idx_active (is_active),
    INDEX idx_stock (available_stock)
);

-- Step 3: Create Product Pricing Tiers Table
CREATE TABLE IF NOT EXISTS product_pricing (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    pricing_tier VARCHAR(50) NOT NULL, -- 'early_bird', 'standard', 'late'
    price DECIMAL(10,2) NOT NULL,
    valid_from DATETIME,
    valid_until DATETIME,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_product_tier (product_id, pricing_tier),
    INDEX idx_pricing_tier (pricing_tier),
    INDEX idx_valid_dates (valid_from, valid_until)
);

-- Step 4: Create Registration Products Table (replaces boolean fields)
CREATE TABLE IF NOT EXISTS registration_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    registration_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    pricing_tier VARCHAR(50),
    
    -- Stock Management
    stock_status ENUM('reserved', 'confirmed', 'cancelled', 'refunded') DEFAULT 'reserved',
    reserved_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    confirmed_at TIMESTAMP NULL,
    expires_at TIMESTAMP NULL, -- Auto-release reservation after X time
    
    product_metadata JSON, -- Store specific product selections
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (registration_id) REFERENCES registrations(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_registration (registration_id),
    INDEX idx_product (product_id),
    INDEX idx_stock_status (stock_status),
    INDEX idx_expires (expires_at)
);

-- Step 5: Create Stock Movements Log Table (for audit trail)
CREATE TABLE IF NOT EXISTS product_stock_movements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    registration_id INT NULL,
    movement_type ENUM('reserve', 'release', 'sell', 'refund', 'adjust') NOT NULL,
    quantity_change INT NOT NULL, -- Positive or negative
    previous_available INT NOT NULL,
    new_available INT NOT NULL,
    reason VARCHAR(255),
    admin_user_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (registration_id) REFERENCES registrations(id),
    INDEX idx_product_movements (product_id, created_at),
    INDEX idx_movement_type (movement_type)
);

-- Step 6: Insert Default Product Categories
INSERT INTO product_categories (name, description, display_order) VALUES
('Event Access', 'Delegate passes and event access products', 1),
('Presentations', 'Table-top presentation opportunities', 2),
('Workshops', 'Educational workshop sessions', 3),
('Networking', 'Networking events and social activities', 4),
('Services', 'Additional services and amenities', 5);

-- Step 7: Insert Current Products
INSERT INTO products (category_id, product_code, name, description, base_price, max_per_registration, display_order) VALUES
-- Delegate Passes (unlimited stock for now)
(1, 'DELEGATE_PASS', 'Conference Delegate Pass', 'Full conference access with networking opportunities', 500.00, 50, 1),

-- Table-top Presentations (limited stock)
(2, 'TABLE_STANDARD', 'Standard Table-top Presentation', '1 table, 4 chairs, up to 2 banners', 550.00, 1, 1),
(2, 'TABLE_DOUBLE', 'Double Table-top Presentation', 'Double table, 8 chairs, up to 2 banners', 850.00, 1, 2);

-- Step 8: Insert Product Pricing Tiers
INSERT INTO product_pricing (product_id, pricing_tier, price, valid_from, valid_until) VALUES
-- Delegate Pass Pricing
(1, 'early_bird', 500.00, '2025-01-01 00:00:00', '2025-08-31 23:59:59'),
(1, 'standard', 550.00, '2025-09-01 00:00:00', '2025-11-25 23:59:59'),

-- Table presentations use base price (no tiers for now)
(2, 'standard', 550.00, '2025-01-01 00:00:00', '2025-11-25 23:59:59'),
(3, 'standard', 850.00, '2025-01-01 00:00:00', '2025-11-25 23:59:59');

-- Step 9: Migrate Existing Registration Data
-- This will convert existing boolean fields to the new product system
INSERT INTO registration_products (registration_id, product_id, quantity, unit_price, total_price, pricing_tier, stock_status, confirmed_at)
SELECT 
    r.id as registration_id,
    1 as product_id, -- Delegate pass product
    r.delegate_count as quantity,
    CASE 
        WHEN r.delegate_type = 'earlyBird' THEN 500.00
        ELSE 550.00
    END as unit_price,
    CASE 
        WHEN r.delegate_type = 'earlyBird' THEN (500.00 * r.delegate_count)
        ELSE (550.00 * r.delegate_count)
    END as total_price,
    CASE 
        WHEN r.delegate_type = 'earlyBird' THEN 'early_bird'
        ELSE 'standard'
    END as pricing_tier,
    CASE 
        WHEN r.payment_status = 'paid' THEN 'confirmed'
        ELSE 'reserved'
    END as stock_status,
    CASE 
        WHEN r.payment_status = 'paid' THEN r.updated_at
        ELSE NULL
    END as confirmed_at
FROM registrations r
WHERE NOT EXISTS (
    SELECT 1 FROM registration_products rp 
    WHERE rp.registration_id = r.id AND rp.product_id = 1
);

-- Migrate Standard Table purchases
INSERT INTO registration_products (registration_id, product_id, quantity, unit_price, total_price, pricing_tier, stock_status, confirmed_at)
SELECT 
    r.id as registration_id,
    2 as product_id, -- Standard table product
    1 as quantity,
    550.00 as unit_price,
    550.00 as total_price,
    'standard' as pricing_tier,
    CASE 
        WHEN r.payment_status = 'paid' THEN 'confirmed'
        ELSE 'reserved'
    END as stock_status,
    CASE 
        WHEN r.payment_status = 'paid' THEN r.updated_at
        ELSE NULL
    END as confirmed_at
FROM registrations r
WHERE r.has_standard_table = 1
AND NOT EXISTS (
    SELECT 1 FROM registration_products rp 
    WHERE rp.registration_id = r.id AND rp.product_id = 2
);

-- Migrate Double Table purchases
INSERT INTO registration_products (registration_id, product_id, quantity, unit_price, total_price, pricing_tier, stock_status, confirmed_at)
SELECT 
    r.id as registration_id,
    3 as product_id, -- Double table product
    1 as quantity,
    850.00 as unit_price,
    850.00 as total_price,
    'standard' as pricing_tier,
    CASE 
        WHEN r.payment_status = 'paid' THEN 'confirmed'
        ELSE 'reserved'
    END as stock_status,
    CASE 
        WHEN r.payment_status = 'paid' THEN r.updated_at
        ELSE NULL
    END as confirmed_at
FROM registrations r
WHERE r.has_double_table = 1
AND NOT EXISTS (
    SELECT 1 FROM registration_products rp 
    WHERE rp.registration_id = r.id AND rp.product_id = 3
);

-- Step 10: Update stock counts based on migrated data
UPDATE products p SET 
    sold_stock = (
        SELECT COALESCE(SUM(rp.quantity), 0)
        FROM registration_products rp
        WHERE rp.product_id = p.id AND rp.stock_status = 'confirmed'
    ),
    reserved_stock = (
        SELECT COALESCE(SUM(rp.quantity), 0)
        FROM registration_products rp
        WHERE rp.product_id = p.id AND rp.stock_status = 'reserved'
    );

-- Step 11: Set reasonable stock limits for table presentations
-- (These can be adjusted based on actual venue capacity)
UPDATE products SET total_stock = 50 WHERE product_code = 'TABLE_STANDARD';
UPDATE products SET total_stock = 25 WHERE product_code = 'TABLE_DOUBLE';

-- Step 12: Create indexes for performance
CREATE INDEX idx_registrations_payment_status ON registrations(payment_status);
CREATE INDEX idx_registrations_delegate_type ON registrations(delegate_type);

-- Migration completed successfully
-- The old boolean fields (has_standard_table, has_double_table) can be removed after testing
-- ALTER TABLE registrations DROP COLUMN has_standard_table;
-- ALTER TABLE registrations DROP COLUMN has_double_table;

SELECT 'Product Management System migration completed successfully!' as status;
