-- ═══════════════════════════════════════════════════════════════════════════
-- v4.28 — MeLi commerce: listing economics, inventory-match metadata
-- Additive + idempotent. Runs against tenant DBs.
--
-- Goal: make MercadoLibre listings sellable end-to-end by persisting, per
-- listing, the data that today is only computed transiently:
--   * how the ML item was linked to local inventory (backfill audit)
--   * the ML commission and the resulting net price / margin
--   * the cost at the moment of publishing (so margin stays comparable)
--   * the listing type and the description actually published
--
-- Also normalizes marketplace_questions.status to lowercase: ML returns
-- "UNANSWERED"/"ANSWERED" and the auto-answer/SLA queries filter lowercase.
-- ═══════════════════════════════════════════════════════════════════════════

-- ─── 1. marketplace_listings: match metadata + economics ────────────────────
ALTER TABLE marketplace_listings
    ADD COLUMN IF NOT EXISTS listing_type_id       VARCHAR(30),
    ADD COLUMN IF NOT EXISTS inventory_match_source VARCHAR(40),
    ADD COLUMN IF NOT EXISTS inventory_matched_at  TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS meli_fee              NUMERIC(12,2),
    ADD COLUMN IF NOT EXISTS meli_fee_pct          NUMERIC(6,2),
    ADD COLUMN IF NOT EXISTS meli_net_price        NUMERIC(12,2),
    ADD COLUMN IF NOT EXISTS meli_fee_source       VARCHAR(20),
    ADD COLUMN IF NOT EXISTS cost_at_publish       NUMERIC(12,2),
    ADD COLUMN IF NOT EXISTS margin_pct            NUMERIC(8,2),
    ADD COLUMN IF NOT EXISTS meli_description      TEXT,
    ADD COLUMN IF NOT EXISTS economics_updated_at  TIMESTAMPTZ;

-- Fast lookup of the listings that still need an inventory match (backfill).
CREATE INDEX IF NOT EXISTS idx_marketplace_listings_unmapped
    ON marketplace_listings(channel)
    WHERE inventory_id IS NULL;

-- ─── 2. marketplace_questions: status normalization ─────────────────────────
-- ML's API returns "UNANSWERED" / "ANSWERED"; the service compares lowercase.
-- Normalize historical rows so auto-answer and the SLA job see them.
UPDATE marketplace_questions
SET status = LOWER(status), updated_at = NOW()
WHERE status IS NOT NULL AND status <> LOWER(status);

-- Index for the SLA sweep: oldest unanswered questions first.
CREATE INDEX IF NOT EXISTS idx_marketplace_questions_status_date
    ON marketplace_questions(status, question_date);

-- ─── 3. Allow several active ML listings per local product ──────────────────
-- The original v3.4 unique index assumed one ML listing per inventory item.
-- Real MeLi catalogs routinely publish several listings (different fitments /
-- titles) that share the same internal part number; the inventory backfill
-- must be able to link them all, and order conversion maps each order item by
-- external_item_id. Replace the uniqueness with a plain lookup index — the
-- publish path now upserts by (inventory_id, channel) explicitly.
DROP INDEX IF EXISTS idx_marketplace_listings_unique;
CREATE INDEX IF NOT EXISTS idx_marketplace_listings_inv_channel
    ON marketplace_listings(inventory_id, channel) WHERE is_active = true;