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'
43 lines
1.7 KiB
PL/PgSQL
43 lines
1.7 KiB
PL/PgSQL
-- : SKIP
|
|
-- v3.9_supplier_catalog_prices.sql
|
|
-- Per-tenant supplier pricing for items in the master supplier_catalog.
|
|
-- This table lives in the master DB and is joined by tenant_id.
|
|
-- Apply manually to the master database.
|
|
|
|
CREATE TABLE IF NOT EXISTS supplier_catalog_prices (
|
|
id SERIAL PRIMARY KEY,
|
|
tenant_id INTEGER NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
|
|
catalog_id INTEGER NOT NULL REFERENCES supplier_catalog(id) ON DELETE CASCADE,
|
|
price NUMERIC(12,2) NOT NULL,
|
|
currency VARCHAR(3) DEFAULT 'MXN',
|
|
effective_from DATE DEFAULT CURRENT_DATE,
|
|
effective_to DATE,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(tenant_id, catalog_id, effective_from)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_supplier_catalog_prices_tenant_catalog
|
|
ON supplier_catalog_prices(tenant_id, catalog_id, effective_from DESC)
|
|
WHERE is_active = true;
|
|
|
|
-- Index for quick "latest active price" lookups per tenant+item.
|
|
CREATE INDEX IF NOT EXISTS idx_supplier_catalog_prices_lookup
|
|
ON supplier_catalog_prices(tenant_id, catalog_id, effective_from DESC, is_active);
|
|
|
|
-- Trigger to keep updated_at current on row changes.
|
|
CREATE OR REPLACE FUNCTION update_supplier_catalog_prices_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_supplier_catalog_prices_updated_at ON supplier_catalog_prices;
|
|
CREATE TRIGGER trg_supplier_catalog_prices_updated_at
|
|
BEFORE UPDATE ON supplier_catalog_prices
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_supplier_catalog_prices_updated_at();
|