-- Warehouse Phase 2 Migration: Device tracking + PO tracking numbers
-- Run: mysql mkl < scripts/migrate-warehouse-phase2.sql

-- 1. Device ID sequence table
CREATE TABLE IF NOT EXISTS device_sequences (
    prefix VARCHAR(10) PRIMARY KEY,
    next_number INT NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO device_sequences (prefix, next_number) VALUES ('DI-A', 1);

-- 2. Multiple tracking numbers per PO
CREATE TABLE IF NOT EXISTS purchase_order_tracking (
    id INT AUTO_INCREMENT PRIMARY KEY,
    po_id INT NOT NULL,
    tracking_number VARCHAR(255) NOT NULL,
    carrier VARCHAR(100),
    ship_date DATE,
    eta_date DATE,
    source ENUM('vendor_api','shipstation','email','manual') DEFAULT 'manual',
    vendor_key VARCHAR(50),
    invoice_number VARCHAR(100),
    shipment_invoice_id INT,
    status ENUM('pre_transit','in_transit','delivered','returned','exception') DEFAULT 'in_transit',
    details JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (shipment_invoice_id) REFERENCES shipment_invoices(id) ON DELETE SET NULL,
    UNIQUE KEY uq_po_tracking (po_id, tracking_number),
    INDEX idx_pot_po (po_id),
    INDEX idx_pot_tracking (tracking_number),
    INDEX idx_pot_source (source),
    INDEX idx_pot_vendor (vendor_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Individual devices received against PO line items
CREATE TABLE IF NOT EXISTS purchase_order_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(20) UNIQUE NOT NULL,
    po_id INT NOT NULL,
    po_item_id INT,
    serial_number VARCHAR(100),
    vendor_sku VARCHAR(100),
    part_number VARCHAR(255),
    product_name VARCHAR(500),
    tracking_number VARCHAR(255),
    status ENUM('expected','received','audited','graded','in_stock','shipped_out','rma') DEFAULT 'expected',
    condition_grade VARCHAR(10),
    received_at TIMESTAMP NULL,
    received_by INT,
    audited_at TIMESTAMP NULL,
    audited_by INT,
    source ENUM('vendor_api','macservice','shipstation','manual') DEFAULT 'manual',
    macservice_onum VARCHAR(30),
    legacy_data JSON,
    notes TEXT,
    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 CASCADE,
    FOREIGN KEY (po_item_id) REFERENCES purchase_order_items(id) ON DELETE SET NULL,
    FOREIGN KEY (received_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (audited_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_pod_device (device_id),
    INDEX idx_pod_po (po_id),
    INDEX idx_pod_item (po_item_id),
    INDEX idx_pod_serial (serial_number),
    INDEX idx_pod_status (status),
    INDEX idx_pod_tracking (tracking_number),
    INDEX idx_pod_macservice (macservice_onum)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;