CREATE TABLE IF NOT EXISTS ingest_waves (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    wave_id VARCHAR(128) NOT NULL,
    endpoint VARCHAR(32) NOT NULL,
    entity_scope VARCHAR(64) NOT NULL,
    snapshot_scope VARCHAR(32) NOT NULL,
    is_full_snapshot TINYINT(1) NOT NULL DEFAULT 0,
    payload_shape VARCHAR(64) NOT NULL DEFAULT 'unknown',
    catalog_core_supported TINYINT(1) NOT NULL DEFAULT 1,
    completeness_token VARCHAR(128) NULL,
    source_system VARCHAR(32) NOT NULL DEFAULT '1c',
    source_window_started_at DATETIME NULL,
    source_window_finished_at DATETIME NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'active',
    next_request_seq BIGINT UNSIGNED NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_ingest_waves_wave_id (wave_id),
    KEY idx_ingest_waves_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS raw_ingest_requests (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    request_id VARCHAR(128) NOT NULL,
    wave_id VARCHAR(128) NOT NULL,
    wave_internal_id BIGINT UNSIGNED NOT NULL,
    request_seq BIGINT UNSIGNED NOT NULL,
    endpoint VARCHAR(32) NOT NULL,
    source_system VARCHAR(32) NOT NULL,
    content_type VARCHAR(64) NOT NULL,
    raw_body LONGTEXT NULL,
    raw_body_sha256 CHAR(64) NOT NULL,
    payload_size_bytes BIGINT UNSIGNED NOT NULL,
    storage_path VARCHAR(1024) NULL,
    storage_driver VARCHAR(32) NULL,
    compression VARCHAR(32) NULL,
    received_at DATETIME NOT NULL,
    parsed_at DATETIME NOT NULL,
    parse_status VARCHAR(32) NOT NULL,
    parse_error TEXT NULL,
    is_full_snapshot TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_raw_ingest_requests_request_id (request_id),
    KEY idx_raw_ingest_requests_wave_seq (wave_internal_id, request_seq)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_brands (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    business_key VARCHAR(128) NOT NULL,
    source_version VARCHAR(128) NOT NULL,
    payload_hash CHAR(64) NOT NULL,
    wave_id VARCHAR(128) NOT NULL,
    wave_internal_id BIGINT UNSIGNED NOT NULL,
    request_id VARCHAR(128) NOT NULL,
    request_seq BIGINT UNSIGNED NOT NULL,
    record_ordinal INT UNSIGNED NOT NULL,
    normalized_payload_json LONGTEXT NOT NULL,
    is_tombstone TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_catalog_brands_business_key (business_key),
    KEY idx_catalog_brands_wave_seq (wave_internal_id, request_seq, record_ordinal),
    KEY idx_catalog_brands_tombstone (is_tombstone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_sections (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    business_key VARCHAR(128) NOT NULL,
    source_version VARCHAR(128) NOT NULL,
    payload_hash CHAR(64) NOT NULL,
    wave_id VARCHAR(128) NOT NULL,
    wave_internal_id BIGINT UNSIGNED NOT NULL,
    request_id VARCHAR(128) NOT NULL,
    request_seq BIGINT UNSIGNED NOT NULL,
    record_ordinal INT UNSIGNED NOT NULL,
    normalized_payload_json LONGTEXT NOT NULL,
    is_tombstone TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_catalog_sections_business_key (business_key),
    KEY idx_catalog_sections_wave_seq (wave_internal_id, request_seq, record_ordinal),
    KEY idx_catalog_sections_tombstone (is_tombstone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_products (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    business_key VARCHAR(128) NOT NULL,
    source_version VARCHAR(128) NOT NULL,
    payload_hash CHAR(64) NOT NULL,
    wave_id VARCHAR(128) NOT NULL,
    wave_internal_id BIGINT UNSIGNED NOT NULL,
    request_id VARCHAR(128) NOT NULL,
    request_seq BIGINT UNSIGNED NOT NULL,
    record_ordinal INT UNSIGNED NOT NULL,
    normalized_payload_json LONGTEXT NOT NULL,
    is_tombstone TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_catalog_products_business_key (business_key),
    KEY idx_catalog_products_wave_seq (wave_internal_id, request_seq, record_ordinal),
    KEY idx_catalog_products_tombstone (is_tombstone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_product_section_bindings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    business_key VARCHAR(255) NOT NULL,
    source_version VARCHAR(128) NOT NULL,
    payload_hash CHAR(64) NOT NULL,
    wave_id VARCHAR(128) NOT NULL,
    wave_internal_id BIGINT UNSIGNED NOT NULL,
    request_id VARCHAR(128) NOT NULL,
    request_seq BIGINT UNSIGNED NOT NULL,
    record_ordinal INT UNSIGNED NOT NULL,
    normalized_payload_json LONGTEXT NOT NULL,
    is_tombstone TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_catalog_bindings_business_key (business_key),
    KEY idx_catalog_bindings_wave_seq (wave_internal_id, request_seq, record_ordinal),
    KEY idx_catalog_bindings_tombstone (is_tombstone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_conflicts_audit (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    entity_type VARCHAR(32) NOT NULL,
    business_key VARCHAR(255) NOT NULL,
    wave_id VARCHAR(128) NOT NULL,
    request_id VARCHAR(128) NOT NULL,
    request_seq BIGINT UNSIGNED NOT NULL,
    record_ordinal INT UNSIGNED NOT NULL,
    existing_payload_hash CHAR(64) NOT NULL,
    incoming_payload_hash CHAR(64) NOT NULL,
    existing_payload_json LONGTEXT NOT NULL,
    incoming_payload_json LONGTEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_catalog_conflicts_entity (entity_type, business_key),
    KEY idx_catalog_conflicts_wave (wave_id, request_seq, record_ordinal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS outbox_messages (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    message_id CHAR(64) NOT NULL,
    entity_type VARCHAR(32) NOT NULL,
    business_key VARCHAR(255) NOT NULL,
    operation VARCHAR(32) NOT NULL,
    normalized_body_json LONGTEXT NOT NULL,
    wave_id VARCHAR(128) NOT NULL,
    request_id VARCHAR(128) NOT NULL,
    request_seq BIGINT UNSIGNED NOT NULL,
    record_ordinal INT UNSIGNED NOT NULL,
    source_version VARCHAR(128) NOT NULL,
    payload_hash CHAR(64) NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'pending',
    attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_error TEXT NULL,
    published_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_outbox_message_id (message_id),
    KEY idx_outbox_status_id (status, id),
    KEY idx_outbox_business_key (entity_type, business_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS projector_receipts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    message_id CHAR(64) NOT NULL,
    consumer_name VARCHAR(64) NOT NULL,
    apply_status VARCHAR(32) NOT NULL,
    source_version VARCHAR(128) NOT NULL,
    payload_hash CHAR(64) NOT NULL,
    applied_at DATETIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_projector_receipts_message_consumer (message_id, consumer_name),
    KEY idx_projector_receipts_status (apply_status, applied_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_decision_facts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    business_key VARCHAR(128) NOT NULL,
    wave_set_id VARCHAR(128) NOT NULL,
    products_wave_id VARCHAR(128) NOT NULL,
    decision_policy_version VARCHAR(32) NOT NULL,
    decision_scope VARCHAR(64) NOT NULL,
    requested_active CHAR(1) NOT NULL DEFAULT 'Y',
    effective_quantity DECIMAL(18,3) NOT NULL DEFAULT 0,
    supply_method_xml_id VARCHAR(128) NULL,
    visibility_deactivate TINYINT(1) NOT NULL DEFAULT 0,
    brand_xml_id VARCHAR(128) NULL,
    has_business_section TINYINT(1) NOT NULL DEFAULT 0,
    has_structurally_publishable_section TINYINT(1) NOT NULL DEFAULT 0,
    eligible_for_publication TINYINT(1) NOT NULL DEFAULT 0,
    eligibility_reason VARCHAR(64) NULL,
    has_effective_published_section TINYINT(1) NOT NULL DEFAULT 0,
    quarantined TINYINT(1) NOT NULL DEFAULT 0,
    publication_state VARCHAR(32) NOT NULL,
    publication_reason VARCHAR(64) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_product_decision_business_key (business_key),
    KEY idx_product_decision_state (publication_state),
    KEY idx_product_decision_brand (brand_xml_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS section_structure_facts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    business_key VARCHAR(128) NOT NULL,
    parent_business_key VARCHAR(128) NULL,
    sections_wave_id VARCHAR(128) NOT NULL,
    decision_policy_version VARCHAR(32) NOT NULL,
    decision_scope VARCHAR(64) NOT NULL,
    active CHAR(1) NOT NULL DEFAULT 'Y',
    global_active_computed TINYINT(1) NOT NULL DEFAULT 0,
    is_service TINYINT(1) NOT NULL DEFAULT 0,
    is_quarantine TINYINT(1) NOT NULL DEFAULT 0,
    structurally_publishable TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_section_structure_business_key (business_key),
    KEY idx_section_structure_parent (parent_business_key),
    KEY idx_section_structure_publishable (structurally_publishable)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS section_publication_facts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    business_key VARCHAR(128) NOT NULL,
    wave_set_id VARCHAR(128) NOT NULL,
    decision_policy_version VARCHAR(32) NOT NULL,
    decision_scope VARCHAR(64) NOT NULL,
    has_eligible_products_direct TINYINT(1) NOT NULL DEFAULT 0,
    has_eligible_products_branch TINYINT(1) NOT NULL DEFAULT 0,
    has_published_products_direct TINYINT(1) NOT NULL DEFAULT 0,
    has_published_products_branch TINYINT(1) NOT NULL DEFAULT 0,
    effective_active TINYINT(1) NOT NULL DEFAULT 0,
    effective_published TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_section_publication_business_key (business_key),
    KEY idx_section_publication_effective (effective_published)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS brand_decision_facts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    business_key VARCHAR(128) NOT NULL,
    wave_set_id VARCHAR(128) NOT NULL,
    brands_wave_id VARCHAR(128) NOT NULL,
    decision_policy_version VARCHAR(32) NOT NULL,
    decision_scope VARCHAR(64) NOT NULL,
    has_eligible_products TINYINT(1) NOT NULL DEFAULT 0,
    has_published_products TINYINT(1) NOT NULL DEFAULT 0,
    effective_active TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_brand_decision_business_key (business_key),
    KEY idx_brand_decision_effective (effective_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
