-- Migration: Add po_warranties table for eBay warranty tracking
-- Run: ssh mkl 'mysql -u root mkl < /var/www/html/mkl/scripts/migrate-warranty-tracking.sql'

CREATE TABLE IF NOT EXISTS po_warranties (
    id INT AUTO_INCREMENT PRIMARY KEY,
    po_id INT DEFAULT NULL,
    vendor_key VARCHAR(50) DEFAULT 'ebay',
    warranty_provider VARCHAR(100) DEFAULT 'SquareTrade',
    warranty_duration_months INT NOT NULL,
    purchase_date DATE NOT NULL,
    expiration_date DATE NOT NULL,
    ebay_order_number VARCHAR(100),
    ebay_item_id VARCHAR(50),
    item_title VARCHAR(500),
    quantity INT DEFAULT 1,
    source_id VARCHAR(150) NOT NULL,
    email_date TIMESTAMP NULL,
    raw_details JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE SET NULL,
    UNIQUE KEY uq_warranty_source (source_id),
    INDEX idx_pw_po (po_id),
    INDEX idx_pw_ebay_order (ebay_order_number),
    INDEX idx_pw_expiration (expiration_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;