CREATE TABLE IF NOT EXISTS shopify_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shopify_order_id BIGINT NOT NULL,
    order_number VARCHAR(50) NOT NULL,
    financial_status VARCHAR(50) NOT NULL,
    created_at DATETIME NOT NULL,
    subtotal_price DECIMAL(10, 2) NOT NULL,
    shipping_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'GBP',
    customer_first_name VARCHAR(100),
    customer_last_name VARCHAR(100),
    customer_email VARCHAR(255),
    shipping_address1 VARCHAR(255),
    shipping_address2 VARCHAR(255),
    shipping_city VARCHAR(100),
    shipping_province VARCHAR(100),
    shipping_country VARCHAR(100),
    shipping_zip VARCHAR(20),
    master_sku VARCHAR(100),
    shelf_skus TEXT,
    processed BOOLEAN DEFAULT FALSE,
    created_in_db TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_in_db TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_shopify_order (shopify_order_id),
    INDEX idx_master_sku (master_sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS shopify_order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    shopify_order_id BIGINT NOT NULL,
    title VARCHAR(255) NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    sku VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES shopify_orders(id),
    INDEX idx_shopify_order_id (shopify_order_id),
    INDEX idx_sku (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 