CREATE TABLE IF NOT EXISTS app_settings (
    `key` VARCHAR(190) PRIMARY KEY,
    `value` LONGTEXT NULL,
    updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS webhook_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source VARCHAR(50) NOT NULL,
    topic VARCHAR(190) NOT NULL,
    external_id VARCHAR(190) NULL,
    headers_json LONGTEXT NULL,
    payload_json LONGTEXT NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'received',
    received_at DATETIME NOT NULL,
    processed_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_source_topic (source, topic),
    KEY idx_external_id (external_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sync_jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_type VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    external_id VARCHAR(190) NOT NULL,
    payload_json LONGTEXT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    attempts INT NOT NULL DEFAULT 0,
    max_attempts INT NOT NULL DEFAULT 5,
    available_at DATETIME NOT NULL,
    locked_at DATETIME NULL,
    last_error LONGTEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_status_available (status, available_at),
    KEY idx_entity (entity_type, external_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS entity_mappings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entity_type VARCHAR(50) NOT NULL,
    source_system VARCHAR(50) NOT NULL,
    source_id VARCHAR(190) NOT NULL,
    target_system VARCHAR(50) NOT NULL,
    target_id VARCHAR(190) NOT NULL,
    extra_json LONGTEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uniq_map (entity_type, source_system, source_id, target_system),
    KEY idx_target (entity_type, target_system, target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sku_mappings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shopify_sku VARCHAR(190) NOT NULL,
    netsuite_item_id VARCHAR(190) NOT NULL DEFAULT '',
    netsuite_item_name VARCHAR(255) NULL,
    shopify_product_id VARCHAR(190) NULL,
    shopify_variant_id VARCHAR(190) NULL,
    shopify_product_title VARCHAR(255) NULL,
    shopify_variant_title VARCHAR(255) NULL,
    barcode VARCHAR(190) NULL,
    vendor VARCHAR(255) NULL,
    product_type VARCHAR(255) NULL,
    handle VARCHAR(255) NULL,
    status VARCHAR(100) NULL,
    payload_json LONGTEXT NULL,
    last_seen_order_id VARCHAR(190) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uniq_shopify_sku (shopify_sku),
    KEY idx_sku_map_product_id (shopify_product_id),
    KEY idx_sku_map_variant_id (shopify_variant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sync_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    level VARCHAR(20) NOT NULL,
    channel VARCHAR(50) NOT NULL,
    message TEXT NOT NULL,
    context_json LONGTEXT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_level_created (level, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS payment_methods (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    method_key VARCHAR(190) NOT NULL,
    gateway_name VARCHAR(255) NULL,
    formatted_gateway VARCHAR(255) NULL,
    method_kind VARCHAR(100) NULL,
    status VARCHAR(100) NULL,
    source_type VARCHAR(50) NOT NULL,
    source_order_id VARCHAR(190) NULL,
    details_json LONGTEXT NULL,
    first_seen_at DATETIME NOT NULL,
    last_seen_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uniq_method_key (method_key),
    KEY idx_gateway_name (gateway_name),
    KEY idx_source_order_id (source_order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    unique_key VARCHAR(190) NOT NULL,
    shopify_product_id VARCHAR(190) NOT NULL,
    shopify_graphql_product_id VARCHAR(255) NULL,
    title VARCHAR(255) NOT NULL,
    handle VARCHAR(255) NULL,
    vendor VARCHAR(255) NULL,
    product_type VARCHAR(255) NULL,
    status VARCHAR(100) NULL,
    tags_json LONGTEXT NULL,
    options_json LONGTEXT NULL,
    media_json LONGTEXT NULL,
    variants_json LONGTEXT NULL,
    primary_sku VARCHAR(190) NULL,
    primary_barcode VARCHAR(190) NULL,
    last_seen_order_id VARCHAR(190) NULL,
    payload_json LONGTEXT NULL,
    first_seen_at DATETIME NOT NULL,
    last_seen_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uniq_products_unique_key (unique_key),
    UNIQUE KEY uniq_products_shopify_product_id (shopify_product_id),
    KEY idx_products_primary_sku (primary_sku),
    KEY idx_products_handle (handle)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shopify_order_id VARCHAR(190) NOT NULL,
    shopify_graphql_id VARCHAR(255) NULL,
    order_name VARCHAR(190) NULL,
    order_date DATE NULL,
    created_at_shopify DATETIME NULL,
    updated_at_shopify DATETIME NULL,
    processed_at_shopify DATETIME NULL,
    financial_status VARCHAR(100) NULL,
    fulfillment_status VARCHAR(100) NULL,
    currency_code VARCHAR(20) NULL,
    customer_email VARCHAR(255) NULL,
    customer_phone VARCHAR(100) NULL,
    gateway_names_json LONGTEXT NULL,
    archived_file_path VARCHAR(500) NULL,
    payload_json LONGTEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uniq_orders_shopify_order_id (shopify_order_id),
    KEY idx_orders_order_date (order_date),
    KEY idx_orders_created_at_shopify (created_at_shopify)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;