-- ACE2 - Esquema base optimizado
-- Ejecutar sobre una base nueva o una base ya vaciada y respaldada.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS ace2_upload_batches (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    upload_kind ENUM('csv','zip') NOT NULL DEFAULT 'csv',
    original_filename VARCHAR(255) NOT NULL,
    original_archive_filename VARCHAR(255) NULL,
    stored_filename VARCHAR(255) NULL,
    file_sha256 CHAR(64) NULL,
    file_size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
    delimiter_char VARCHAR(8) NOT NULL DEFAULT ';',
    has_header TINYINT(1) NOT NULL DEFAULT 1,
    total_rows INT UNSIGNED NOT NULL DEFAULT 0,
    imported_rows INT UNSIGNED NOT NULL DEFAULT 0,
    failed_rows INT UNSIGNED NOT NULL DEFAULT 0,
    current_row INT UNSIGNED NOT NULL DEFAULT 1,
    current_byte_offset BIGINT UNSIGNED NOT NULL DEFAULT 0,
    cleanup_file_on_complete TINYINT(1) NOT NULL DEFAULT 1,
    cleanup_done TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
    error_message VARCHAR(500) NULL,
    uploaded_by INT UNSIGNED NULL,
    started_at DATETIME NULL,
    finished_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ace2_upload_status (status, id),
    KEY idx_ace2_upload_file_hash (file_sha256)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_ingest_errors (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    upload_id INT UNSIGNED NOT NULL,
    source_row_number INT UNSIGNED NULL,
    error_code VARCHAR(80) NOT NULL,
    error_message VARCHAR(500) NOT NULL,
    row_excerpt TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ace2_ingest_upload_row (upload_id, source_row_number),
    CONSTRAINT fk_ace2_ingest_upload
        FOREIGN KEY (upload_id) REFERENCES ace2_upload_batches(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_statuses (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_code VARCHAR(40) NULL,
    source_label VARCHAR(120) NOT NULL,
    status_scope ENUM('order','supplier') NOT NULL,
    status_group ENUM('valid','cancelled','closed','other') NOT NULL DEFAULT 'other',
    include_in_market TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ace2_status_scope_label (status_scope, source_label),
    KEY idx_ace2_status_include (include_in_market, status_group)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_buyers (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    buyer_code VARCHAR(60) NULL,
    buyer_rut VARCHAR(30) NULL,
    unit_code VARCHAR(60) NULL,
    unit_name VARCHAR(255) NULL,
    organization_code VARCHAR(60) NULL,
    organization_name VARCHAR(255) NOT NULL,
    sector VARCHAR(160) NULL,
    activity VARCHAR(255) NULL,
    city VARCHAR(120) NULL,
    region VARCHAR(120) NULL,
    country VARCHAR(80) NULL,
    buyer_hash CHAR(32) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ace2_buyers_hash (buyer_hash),
    KEY idx_ace2_buyers_org (organization_code, id),
    KEY idx_ace2_buyers_region (region, id),
    KEY idx_ace2_buyers_activity (activity(120), id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_suppliers (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    supplier_code VARCHAR(60) NULL,
    supplier_rut VARCHAR(30) NULL,
    supplier_name VARCHAR(255) NOT NULL,
    activity VARCHAR(255) NULL,
    commune VARCHAR(120) NULL,
    region VARCHAR(120) NULL,
    country VARCHAR(80) NULL,
    supplier_hash CHAR(32) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ace2_suppliers_hash (supplier_hash),
    KEY idx_ace2_suppliers_code (supplier_code, id),
    KEY idx_ace2_suppliers_name (supplier_name(120), id),
    KEY idx_ace2_suppliers_region (region, id),
    KEY idx_ace2_suppliers_activity (activity(120), id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_product_taxonomy (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_category_code VARCHAR(80) NULL,
    source_category_name VARCHAR(255) NULL,
    onu_product_code VARCHAR(80) NULL,
    generic_product_name VARCHAR(255) NULL,
    rubro_n1 VARCHAR(255) NULL,
    rubro_n2 VARCHAR(255) NULL,
    rubro_n3 VARCHAR(255) NULL,
    taxonomy_hash CHAR(32) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ace2_taxonomy_hash (taxonomy_hash),
    KEY idx_ace2_taxonomy_onu (onu_product_code, id),
    KEY idx_ace2_taxonomy_category (source_category_code, id),
    KEY idx_ace2_taxonomy_rubros (rubro_n1(80), rubro_n2(80), rubro_n3(80))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_descriptions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_field ENUM('buyer','supplier') NOT NULL,
    raw_description TEXT NOT NULL,
    normalized_description TEXT NOT NULL,
    description_hash CHAR(32) NOT NULL,
    first_seen_upload_id INT UNSIGNED NULL,
    first_seen_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ace2_desc_source_hash (source_field, description_hash),
    KEY idx_ace2_desc_hash (description_hash),
    KEY idx_ace2_desc_active_source (active, source_field, id),
    CONSTRAINT fk_ace2_desc_upload
        FOREIGN KEY (first_seen_upload_id) REFERENCES ace2_upload_batches(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_market_categories (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    parent_id INT UNSIGNED NULL,
    category_name VARCHAR(180) NOT NULL,
    category_slug VARCHAR(180) NOT NULL,
    notes TEXT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ace2_market_categories_slug (category_slug),
    KEY idx_ace2_market_categories_parent (parent_id),
    KEY idx_ace2_market_categories_active (active, category_name),
    CONSTRAINT fk_ace2_market_categories_parent
        FOREIGN KEY (parent_id) REFERENCES ace2_market_categories(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_market_products (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    category_id INT UNSIGNED NULL,
    canonical_name VARCHAR(255) NOT NULL,
    brand VARCHAR(160) NULL,
    model VARCHAR(160) NULL,
    presentation VARCHAR(160) NULL,
    unit_measure DECIMAL(18,4) NULL,
    unit_label VARCHAR(40) NULL,
    notes TEXT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ace2_products_category (category_id, active),
    KEY idx_ace2_products_name (canonical_name(120), id),
    CONSTRAINT fk_ace2_products_category
        FOREIGN KEY (category_id) REFERENCES ace2_market_categories(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_purchase_orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    codigo_oc VARCHAR(90) NOT NULL,
    link_url VARCHAR(700) NULL,
    order_name VARCHAR(500) NULL,
    order_type VARCHAR(80) NULL,
    source_procedure VARCHAR(120) NULL,
    is_direct_deal TINYINT(1) NOT NULL DEFAULT 0,
    is_agile_purchase TINYINT(1) NOT NULL DEFAULT 0,
    status_id INT UNSIGNED NULL,
    supplier_status_id INT UNSIGNED NULL,
    buyer_id INT UNSIGNED NULL,
    supplier_id INT UNSIGNED NULL,
    created_date DATE NULL,
    sent_date DATE NULL,
    accepted_date DATE NULL,
    cancelled_date DATE NULL,
    total_order_amount DECIMAL(18,2) NULL,
    total_order_net DECIMAL(18,2) NULL,
    currency_code CHAR(3) NULL,
    include_in_market TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ace2_orders_codigo (codigo_oc),
    KEY idx_ace2_orders_sent (sent_date, id),
    KEY idx_ace2_orders_buyer_sent (buyer_id, sent_date),
    KEY idx_ace2_orders_supplier_sent (supplier_id, sent_date),
    KEY idx_ace2_orders_include_sent (include_in_market, sent_date),
    CONSTRAINT fk_ace2_orders_status
        FOREIGN KEY (status_id) REFERENCES ace2_statuses(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_ace2_orders_supplier_status
        FOREIGN KEY (supplier_status_id) REFERENCES ace2_statuses(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_ace2_orders_buyer
        FOREIGN KEY (buyer_id) REFERENCES ace2_buyers(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_ace2_orders_supplier
        FOREIGN KEY (supplier_id) REFERENCES ace2_suppliers(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_purchase_order_lines (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_line_hash CHAR(64) NOT NULL,
    upload_id INT UNSIGNED NULL,
    order_id BIGINT UNSIGNED NOT NULL,
    codigo_oc VARCHAR(90) NOT NULL,
    id_item VARCHAR(80) NULL,
    period_year SMALLINT UNSIGNED NULL,
    period_month TINYINT UNSIGNED NULL,
    buyer_id INT UNSIGNED NULL,
    supplier_id INT UNSIGNED NULL,
    status_id INT UNSIGNED NULL,
    supplier_status_id INT UNSIGNED NULL,
    taxonomy_id INT UNSIGNED NULL,
    buyer_description_id BIGINT UNSIGNED NULL,
    supplier_description_id BIGINT UNSIGNED NULL,
    preferred_description_id BIGINT UNSIGNED NULL,
    quantity DECIMAL(18,4) NULL,
    unit_label VARCHAR(60) NULL,
    currency_code CHAR(3) NULL,
    net_price DECIMAL(18,4) NULL,
    line_charges DECIMAL(18,2) NULL,
    line_discounts DECIMAL(18,2) NULL,
    line_taxes DECIMAL(18,2) NULL,
    line_total_net DECIMAL(18,2) NULL,
    payment_form VARCHAR(255) NULL,
    include_in_market TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ace2_lines_source_hash (source_line_hash),
    KEY idx_ace2_lines_period (period_year, period_month, include_in_market),
    KEY idx_ace2_lines_buyer_period (buyer_id, period_year, period_month),
    KEY idx_ace2_lines_supplier_period (supplier_id, period_year, period_month),
    KEY idx_ace2_lines_tax_period (taxonomy_id, period_year, period_month),
    KEY idx_ace2_lines_desc_buyer (buyer_description_id),
    KEY idx_ace2_lines_desc_supplier (supplier_description_id),
    KEY idx_ace2_lines_preferred_period (preferred_description_id, period_year, period_month),
    KEY idx_ace2_lines_order (order_id),
    CONSTRAINT fk_ace2_lines_upload
        FOREIGN KEY (upload_id) REFERENCES ace2_upload_batches(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_ace2_lines_order
        FOREIGN KEY (order_id) REFERENCES ace2_purchase_orders(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_ace2_lines_buyer
        FOREIGN KEY (buyer_id) REFERENCES ace2_buyers(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_ace2_lines_supplier
        FOREIGN KEY (supplier_id) REFERENCES ace2_suppliers(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_ace2_lines_taxonomy
        FOREIGN KEY (taxonomy_id) REFERENCES ace2_product_taxonomy(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_ace2_lines_buyer_desc
        FOREIGN KEY (buyer_description_id) REFERENCES ace2_descriptions(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_ace2_lines_supplier_desc
        FOREIGN KEY (supplier_description_id) REFERENCES ace2_descriptions(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_ace2_lines_preferred_desc
        FOREIGN KEY (preferred_description_id) REFERENCES ace2_descriptions(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_description_classifications (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    description_id BIGINT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NULL,
    product_id INT UNSIGNED NULL,
    brand VARCHAR(160) NULL,
    model VARCHAR(160) NULL,
    presentation VARCHAR(160) NULL,
    unit_measure DECIMAL(18,4) NULL,
    confidence DECIMAL(5,4) NOT NULL DEFAULT 1.0000,
    status ENUM('pending','suggested','classified','rejected') NOT NULL DEFAULT 'pending',
    classification_source ENUM('manual','rule','alias','dictionary','imported') NOT NULL DEFAULT 'manual',
    imported_source_id INT UNSIGNED NULL,
    notes TEXT NULL,
    reviewed_by INT UNSIGNED NULL,
    reviewed_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ace2_classification_description (description_id),
    KEY idx_ace2_classification_category (category_id, status),
    KEY idx_ace2_classification_product (product_id, status),
    KEY idx_ace2_classification_status (status, classification_source),
    CONSTRAINT fk_ace2_classification_desc
        FOREIGN KEY (description_id) REFERENCES ace2_descriptions(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_ace2_classification_category
        FOREIGN KEY (category_id) REFERENCES ace2_market_categories(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_ace2_classification_product
        FOREIGN KEY (product_id) REFERENCES ace2_market_products(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_classification_rules (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    rule_name VARCHAR(180) NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    priority INT NOT NULL DEFAULT 100,
    applies_to_source_field ENUM('any','buyer','supplier') NOT NULL DEFAULT 'any',
    match_type ENUM('contains','equals','starts_with','word') NOT NULL DEFAULT 'contains',
    match_text VARCHAR(255) NOT NULL,
    negative_text VARCHAR(255) NULL,
    category_id INT UNSIGNED NULL,
    product_id INT UNSIGNED NULL,
    brand VARCHAR(160) NULL,
    model VARCHAR(160) NULL,
    presentation VARCHAR(160) NULL,
    unit_measure DECIMAL(18,4) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ace2_rules_active_priority (active, priority, id),
    KEY idx_ace2_rules_source (applies_to_source_field, active),
    KEY idx_ace2_rules_category (category_id),
    KEY idx_ace2_rules_product (product_id),
    CONSTRAINT fk_ace2_rules_category
        FOREIGN KEY (category_id) REFERENCES ace2_market_categories(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_ace2_rules_product
        FOREIGN KEY (product_id) REFERENCES ace2_market_products(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_metric_market_period_summary (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    category_id INT UNSIGNED NULL,
    product_id INT UNSIGNED NULL,
    period_year SMALLINT UNSIGNED NOT NULL,
    period_month TINYINT UNSIGNED NULL,
    period_scope ENUM('year','month') NOT NULL DEFAULT 'month',
    total_amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    order_count INT UNSIGNED NOT NULL DEFAULT 0,
    line_count INT UNSIGNED NOT NULL DEFAULT 0,
    buyer_count INT UNSIGNED NOT NULL DEFAULT 0,
    supplier_count INT UNSIGNED NOT NULL DEFAULT 0,
    avg_unit_price DECIMAL(18,4) NULL,
    p25_unit_price DECIMAL(18,4) NULL,
    median_unit_price DECIMAL(18,4) NULL,
    p75_unit_price DECIMAL(18,4) NULL,
    mode_unit_price DECIMAL(18,4) NULL,
    min_unit_price DECIMAL(18,4) NULL,
    max_unit_price DECIMAL(18,4) NULL,
    dispersion_percent DECIMAL(9,4) NULL,
    price_sample_count INT UNSIGNED NOT NULL DEFAULT 0,
    outlier_count INT UNSIGNED NOT NULL DEFAULT 0,
    hhi DECIMAL(12,4) NULL,
    refreshed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ace2_metric_scope (category_id, product_id, period_year, period_month, period_scope),
    KEY idx_ace2_metric_category_period (category_id, period_year, period_month),
    KEY idx_ace2_metric_product_period (product_id, period_year, period_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_metric_generation_runs (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    status ENUM('running','completed','failed') NOT NULL DEFAULT 'running',
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    source_line_count INT UNSIGNED NOT NULL DEFAULT 0,
    summary_count INT UNSIGNED NOT NULL DEFAULT 0,
    supplier_share_count INT UNSIGNED NOT NULL DEFAULT 0,
    buyer_share_count INT UNSIGNED NOT NULL DEFAULT 0,
    error_message VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ace2_metric_runs_status (status, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_reset_runs (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    status ENUM('completed','failed') NOT NULL DEFAULT 'completed',
    confirmation_text VARCHAR(120) NOT NULL,
    tables_cleared INT UNSIGNED NOT NULL DEFAULT 0,
    error_message VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ace2_reset_runs_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_metric_supplier_share (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    category_id INT UNSIGNED NULL,
    product_id INT UNSIGNED NULL,
    period_year SMALLINT UNSIGNED NOT NULL,
    period_month TINYINT UNSIGNED NULL,
    supplier_id INT UNSIGNED NOT NULL,
    total_amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    order_count INT UNSIGNED NOT NULL DEFAULT 0,
    buyer_count INT UNSIGNED NOT NULL DEFAULT 0,
    share_percent DECIMAL(9,4) NOT NULL DEFAULT 0.0000,
    rank_position INT UNSIGNED NOT NULL DEFAULT 0,
    refreshed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ace2_supplier_share (category_id, product_id, period_year, period_month, supplier_id),
    KEY idx_ace2_supplier_share_rank (category_id, product_id, period_year, period_month, rank_position),
    CONSTRAINT fk_ace2_supplier_share_supplier
        FOREIGN KEY (supplier_id) REFERENCES ace2_suppliers(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_metric_buyer_share (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    category_id INT UNSIGNED NULL,
    product_id INT UNSIGNED NULL,
    period_year SMALLINT UNSIGNED NOT NULL,
    period_month TINYINT UNSIGNED NULL,
    buyer_id INT UNSIGNED NOT NULL,
    total_amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    order_count INT UNSIGNED NOT NULL DEFAULT 0,
    supplier_count INT UNSIGNED NOT NULL DEFAULT 0,
    share_percent DECIMAL(9,4) NOT NULL DEFAULT 0.0000,
    rank_position INT UNSIGNED NOT NULL DEFAULT 0,
    refreshed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ace2_buyer_share (category_id, product_id, period_year, period_month, buyer_id),
    KEY idx_ace2_buyer_share_rank (category_id, product_id, period_year, period_month, rank_position),
    CONSTRAINT fk_ace2_buyer_share_buyer
        FOREIGN KEY (buyer_id) REFERENCES ace2_buyers(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_legacy_import_batches (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    import_type VARCHAR(80) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    stored_filename VARCHAR(255) NULL,
    delimiter_char VARCHAR(8) NOT NULL DEFAULT ',',
    total_rows INT UNSIGNED NOT NULL DEFAULT 0,
    imported_rows INT UNSIGNED NOT NULL DEFAULT 0,
    failed_rows INT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('completed','failed') NOT NULL DEFAULT 'completed',
    error_message VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ace2_legacy_batches_type (import_type, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_opportunity_evaluations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    search_query VARCHAR(255) NOT NULL,
    period_year SMALLINT UNSIGNED NULL,
    landed_unit_clp DECIMAL(18,4) NOT NULL DEFAULT 0,
    moq DECIMAL(18,4) NOT NULL DEFAULT 0,
    target_margin_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    market_line_count INT UNSIGNED NOT NULL DEFAULT 0,
    market_total_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
    p25_unit_price DECIMAL(18,4) NULL,
    median_unit_price DECIMAL(18,4) NULL,
    p75_unit_price DECIMAL(18,4) NULL,
    hhi DECIMAL(12,4) NULL,
    verdict VARCHAR(40) NULL,
    score DECIMAL(9,4) NULL,
    notes TEXT NULL,
    stats_payload LONGTEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ace2_opp_query_created (search_query(120), created_at),
    KEY idx_ace2_opp_verdict (verdict, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_legacy_id_map (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    entity_type VARCHAR(80) NOT NULL,
    legacy_id VARCHAR(80) NOT NULL,
    ace2_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ace2_legacy_map (entity_type, legacy_id),
    KEY idx_ace2_legacy_map_ace2 (entity_type, ace2_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_legacy_import_errors (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    batch_id INT UNSIGNED NULL,
    import_type VARCHAR(80) NOT NULL,
    source_row_number INT UNSIGNED NULL,
    error_message VARCHAR(500) NOT NULL,
    row_excerpt TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ace2_legacy_errors_batch (batch_id, source_row_number),
    KEY idx_ace2_legacy_errors_type (import_type, id),
    CONSTRAINT fk_ace2_legacy_errors_batch
        FOREIGN KEY (batch_id) REFERENCES ace2_legacy_import_batches(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_market_product_aliases (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    product_id INT UNSIGNED NULL,
    alias_name VARCHAR(255) NOT NULL,
    applies_to_source_field ENUM('any','buyer','supplier') NOT NULL DEFAULT 'any',
    provider_name VARCHAR(255) NULL,
    match_type VARCHAR(30) NOT NULL DEFAULT 'contains',
    match_text VARCHAR(255) NOT NULL,
    priority INT NOT NULL DEFAULT 100,
    notes TEXT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ace2_aliases_product (product_id),
    KEY idx_ace2_aliases_active_priority (active, priority, id),
    KEY idx_ace2_aliases_source (applies_to_source_field),
    CONSTRAINT fk_ace2_aliases_product
        FOREIGN KEY (product_id) REFERENCES ace2_market_products(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_dictionary_terms (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    term VARCHAR(160) NOT NULL,
    normalized_term VARCHAR(160) NOT NULL,
    term_type ENUM('category','brand','model','presentation','unit','synonym','negative') NOT NULL DEFAULT 'category',
    canonical_value VARCHAR(255) NULL,
    category_id INT UNSIGNED NULL,
    product_id INT UNSIGNED NULL,
    match_type ENUM('contains','equals','starts_with','word') NOT NULL DEFAULT 'word',
    priority INT NOT NULL DEFAULT 100,
    confidence DECIMAL(5,4) NOT NULL DEFAULT 0.7000,
    notes TEXT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ace2_dictionary_type_term (term_type, normalized_term),
    KEY idx_ace2_dictionary_active_priority (active, priority, id),
    KEY idx_ace2_dictionary_category (category_id),
    KEY idx_ace2_dictionary_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ace2_legacy_sql_jobs (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    filename VARCHAR(255) NOT NULL,
    byte_offset BIGINT UNSIGNED NOT NULL DEFAULT 0,
    current_table VARCHAR(120) NULL,
    columns_json TEXT NULL,
    total_rows BIGINT UNSIGNED NOT NULL DEFAULT 0,
    imported_rows BIGINT UNSIGNED NOT NULL DEFAULT 0,
    failed_rows BIGINT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
    last_message VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ace2_legacy_sql_jobs_status (status, id),
    KEY idx_ace2_legacy_sql_jobs_file (filename)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO ace2_statuses (source_code, source_label, status_scope, status_group, include_in_market) VALUES
('6', 'Aceptada', 'order', 'valid', 1),
('4', 'Aceptada', 'supplier', 'valid', 1),
(NULL, 'Cancelada', 'order', 'cancelled', 0),
(NULL, 'Cancelada', 'supplier', 'cancelled', 0);

SET FOREIGN_KEY_CHECKS = 1;