-- ═══════════════════════════════════════════════════════════════════════
-- v3.3 — Marketplace accepts any part number (seller listings)
-- Target: nexus_autoparts (master DB) / tenants with warehouse_inventory
-- Date: 2026-05-17
--
-- Makes warehouse_inventory part_id nullable and adds seller-defined
-- fields so any seller can list parts that don't exist in the OEM catalog.
-- Existing OEM-matched listings are untouched.
-- ═══════════════════════════════════════════════════════════════════════

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'warehouse_inventory') THEN
        -- ─── 1. WAREHOUSE_INVENTORY — add seller listing columns ─────────────
        ALTER TABLE warehouse_inventory
            ADD COLUMN IF NOT EXISTS seller_part_number VARCHAR(100),
            ADD COLUMN IF NOT EXISTS seller_part_name   VARCHAR(300),
            ADD COLUMN IF NOT EXISTS seller_category    VARCHAR(100),
            ADD COLUMN IF NOT EXISTS tenant_inventory_id INTEGER;

        -- Make part_id nullable so seller listings (without catalog match) can exist
        ALTER TABLE warehouse_inventory ALTER COLUMN part_id DROP NOT NULL;

        -- ─── 2. WAREHOUSE_INVENTORY — drop old unique, add partial uniques ───
        ALTER TABLE warehouse_inventory
            DROP CONSTRAINT IF EXISTS warehouse_inventory_user_id_part_id_warehouse_location_key;

        DROP INDEX IF EXISTS idx_wi_unique_composite;

        CREATE UNIQUE INDEX IF NOT EXISTS idx_wi_unique_oem
        ON warehouse_inventory(bodega_id, part_id, warehouse_location)
        WHERE part_id IS NOT NULL;

        CREATE UNIQUE INDEX IF NOT EXISTS idx_wi_unique_seller
        ON warehouse_inventory(bodega_id, seller_part_number, warehouse_location)
        WHERE part_id IS NULL;

        -- Ensure every row has either part_id or seller_part_number
        ALTER TABLE warehouse_inventory
            DROP CONSTRAINT IF EXISTS chk_wi_part_or_seller;

        ALTER TABLE warehouse_inventory
            ADD CONSTRAINT chk_wi_part_or_seller
            CHECK (
                (part_id IS NOT NULL AND seller_part_number IS NULL)
                OR
                (part_id IS NULL AND seller_part_number IS NOT NULL)
            );

        -- ─── 3. WAREHOUSE_INVENTORY — search indexes ─────────────────────────
        CREATE INDEX IF NOT EXISTS idx_wi_seller_pn
        ON warehouse_inventory (bodega_id, seller_part_number)
        WHERE part_id IS NULL;

        CREATE INDEX IF NOT EXISTS idx_wi_seller_category
        ON warehouse_inventory (seller_category)
        WHERE part_id IS NULL;

        -- GIN index for text search on seller listings
        CREATE INDEX IF NOT EXISTS idx_wi_seller_search
        ON warehouse_inventory
        USING gin (to_tsvector('spanish',
            COALESCE(seller_part_name, '') || ' ' || COALESCE(seller_part_number, '')
        ))
        WHERE part_id IS NULL;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'purchase_order_items') THEN
        -- ─── 4. PURCHASE_ORDER_ITEMS — make part_id nullable ─────────────────
        IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'purchase_order_items' AND column_name = 'part_id') THEN
            ALTER TABLE purchase_order_items
                ALTER COLUMN part_id DROP NOT NULL;
        END IF;

        -- Add a flag so seller listings can be distinguished in POs
        ALTER TABLE purchase_order_items
            ADD COLUMN IF NOT EXISTS is_seller_listing BOOLEAN NOT NULL DEFAULT FALSE;
    END IF;
END $$;

-- ─── 5. Back-compat: ensure existing rows are valid ──────────────────
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'warehouse_inventory') THEN
        UPDATE warehouse_inventory
        SET seller_part_number = NULL
        WHERE part_id IS NOT NULL AND seller_part_number IS NOT NULL;

        UPDATE warehouse_inventory
        SET part_id = NULL
        WHERE part_id IS NULL AND seller_part_number IS NULL;
    END IF;
END $$;