consultoria-as
6aff32f93b
- Register all missing migrations in runner.py - Make v4.3 idempotent (rename xml_unsigned only if exists) - Make v3.3 idempotent (skip warehouse_inventory/purchase_order_items ops when tables/columns missing) - Mark v3.3.1 and v3.9 as master-only (SKIP) - Mark v3.5.1 as optional (skip if whatsapp tables missing) - Runner skips files marked with '-- : SKIP'
94 lines
4.4 KiB
SQL
94 lines
4.4 KiB
SQL
-- ═══════════════════════════════════════════════════════════════════════
|
|
-- 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 $$;
|