-- Migration: Add Google License Purchases table and orders_purchasing permissions
-- Run on production: mysql -u root mkl < scripts/migrations/add-google-license-purchases.sql

-- 1) Create the tracking table
CREATE TABLE IF NOT EXISTS google_license_purchases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    line_item_id INT NOT NULL,
    mts_number VARCHAR(50) NOT NULL,
    status ENUM('dry_run', 'processing', 'success', 'failed') NOT NULL DEFAULT 'dry_run',
    quantity INT NOT NULL,
    unit_cost DECIMAL(10,2) NOT NULL DEFAULT 29.26,
    total_cost DECIMAL(10,2) NOT NULL,
    dh_sku VARCHAR(100),
    dh_order_number VARCHAR(100) DEFAULT NULL,
    sp_number VARCHAR(20) DEFAULT NULL,
    customer_po VARCHAR(255),
    customer_name VARCHAR(500),
    contact_email VARCHAR(255),
    domain_name VARCHAR(255),
    shipping_street VARCHAR(500),
    shipping_city VARCHAR(255),
    shipping_state VARCHAR(10),
    shipping_zip VARCHAR(20),
    end_user_contact VARCHAR(255),
    end_user_phone VARCHAR(50),
    request_payload JSON DEFAULT NULL,
    response_payload JSON DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    dry_run TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (line_item_id) REFERENCES order_line_items(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_glp_order (order_id),
    INDEX idx_glp_mts (mts_number),
    INDEX idx_glp_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2) Grant orders_purchasing access to authorized users
--    Justin, Robert Baker, Terry McCrary — look up by email
INSERT IGNORE INTO user_module_access (user_id, module_id, access_level)
SELECT id, 'orders_purchasing', 'admin'
FROM users
WHERE email IN (
    'j.sanderson@techtoschool.com',
    'r.baker@techtoschool.com',
    't.mccrary@techtoschool.com'
);