-- ACE2 - Metricas gerenciales y Evaluador de Oportunidades
-- Ejecutar despues de 001_schema.sql y parches previos.

SET NAMES utf8mb4;

ALTER TABLE ace2_metric_market_period_summary
    ADD COLUMN IF NOT EXISTS p25_unit_price DECIMAL(18,4) NULL AFTER avg_unit_price,
    ADD COLUMN IF NOT EXISTS p75_unit_price DECIMAL(18,4) NULL AFTER median_unit_price,
    ADD COLUMN IF NOT EXISTS mode_unit_price DECIMAL(18,4) NULL AFTER p75_unit_price,
    ADD COLUMN IF NOT EXISTS dispersion_percent DECIMAL(9,4) NULL AFTER max_unit_price,
    ADD COLUMN IF NOT EXISTS price_sample_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER dispersion_percent;

ALTER TABLE ace2_purchase_order_lines
    ADD INDEX IF NOT EXISTS idx_ace2_lines_preferred_period (preferred_description_id, period_year, period_month);

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_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;