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,
    netsuite_item_name VARCHAR(255) 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)
) 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;