erp-construccion-database-v2/schemas/07-purchase-ext-schema-ddl.sql

-- ============================================================================
-- PURCHASE EXTENSION Schema DDL - Extensiones de Compras para Construcción
-- Modulos: MAI-004 (Compras e Inventarios)
-- Version: 1.0.0
-- Fecha: 2025-12-08
-- ============================================================================
-- TIPO: Extensión del ERP Core (MGN-006 Purchase)
-- NOTA: Contiene SOLO extensiones específicas de construcción.
--       Las tablas base están en el ERP Core.
-- ============================================================================
-- PREREQUISITOS:
--   1. ERP-Core instalado (auth.tenants, auth.users)
--   2. Schema construction instalado (fraccionamientos)
--   3. Schema inventory extension instalado (requisiciones_obra)
--   4. Schema purchase de ERP-Core instalado (opcional, para FKs)
-- ============================================================================

-- Verificar prerequisitos
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'auth') THEN
        RAISE EXCEPTION 'Schema auth no existe. Ejecutar primero ERP-Core DDL';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'construction') THEN
        RAISE EXCEPTION 'Schema construction no existe. Ejecutar primero construction DDL';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'inventory') THEN
        RAISE EXCEPTION 'Schema inventory no existe. Ejecutar primero inventory extension DDL';
    END IF;
END $$;

-- Crear schema si no existe (puede ya existir desde ERP-Core)
CREATE SCHEMA IF NOT EXISTS purchase;

-- ============================================================================
-- TABLES - EXTENSIONES CONSTRUCCIÓN
-- ============================================================================

-- Tabla: purchase_order_construction (extensión de órdenes de compra)
-- Extiende: purchase.purchase_orders (ERP Core)
CREATE TABLE IF NOT EXISTS purchase.purchase_order_construction (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    purchase_order_id UUID NOT NULL,  -- FK a purchase.purchase_orders (ERP Core)
    fraccionamiento_id UUID REFERENCES construction.fraccionamientos(id),
    requisicion_id UUID REFERENCES inventory.requisiciones_obra(id),
    delivery_location VARCHAR(255),
    delivery_contact VARCHAR(100),
    delivery_phone VARCHAR(20),
    received_by UUID REFERENCES auth.users(id),
    received_at TIMESTAMPTZ,
    quality_approved BOOLEAN,
    quality_notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES auth.users(id),
    CONSTRAINT uq_po_construction_po_id UNIQUE (purchase_order_id)
);

-- Tabla: supplier_construction (extensión de proveedores)
-- Extiende: purchase.suppliers (ERP Core)
CREATE TABLE IF NOT EXISTS purchase.supplier_construction (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    supplier_id UUID NOT NULL,  -- FK a purchase.suppliers (ERP Core)
    is_materials_supplier BOOLEAN DEFAULT FALSE,
    is_services_supplier BOOLEAN DEFAULT FALSE,
    is_equipment_supplier BOOLEAN DEFAULT FALSE,
    specialties TEXT[],
    quality_rating DECIMAL(3,2),
    delivery_rating DECIMAL(3,2),
    price_rating DECIMAL(3,2),
    overall_rating DECIMAL(3,2) GENERATED ALWAYS AS (
        (COALESCE(quality_rating, 0) + COALESCE(delivery_rating, 0) + COALESCE(price_rating, 0)) / 3
    ) STORED,
    last_evaluation_date DATE,
    credit_limit DECIMAL(14,2),
    payment_days INTEGER DEFAULT 30,
    has_valid_documents BOOLEAN DEFAULT FALSE,
    documents_expiry_date DATE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES auth.users(id),
    CONSTRAINT uq_supplier_construction_supplier_id UNIQUE (supplier_id)
);

-- Tabla: comparativo_cotizaciones (cuadro comparativo)
CREATE TABLE IF NOT EXISTS purchase.comparativo_cotizaciones (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    requisicion_id UUID REFERENCES inventory.requisiciones_obra(id),
    code VARCHAR(30) NOT NULL,
    name VARCHAR(255) NOT NULL,
    comparison_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    winner_supplier_id UUID,  -- FK a purchase.suppliers (ERP Core)
    approved_by UUID REFERENCES auth.users(id),
    approved_at TIMESTAMPTZ,
    notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES auth.users(id),
    CONSTRAINT uq_comparativo_code_tenant UNIQUE (tenant_id, code)
);

-- Tabla: comparativo_proveedores (proveedores en comparativo)
CREATE TABLE IF NOT EXISTS purchase.comparativo_proveedores (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    comparativo_id UUID NOT NULL REFERENCES purchase.comparativo_cotizaciones(id) ON DELETE CASCADE,
    supplier_id UUID NOT NULL,  -- FK a purchase.suppliers (ERP Core)
    quotation_number VARCHAR(50),
    quotation_date DATE,
    delivery_days INTEGER,
    payment_conditions VARCHAR(100),
    total_amount DECIMAL(16,2),
    is_selected BOOLEAN DEFAULT FALSE,
    evaluation_notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id)
);

-- Tabla: comparativo_productos (productos en comparativo)
CREATE TABLE IF NOT EXISTS purchase.comparativo_productos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    comparativo_proveedor_id UUID NOT NULL REFERENCES purchase.comparativo_proveedores(id) ON DELETE CASCADE,
    product_id UUID NOT NULL,  -- FK a inventory.products (ERP Core)
    quantity DECIMAL(12,4) NOT NULL,
    unit_price DECIMAL(12,4) NOT NULL,
    total_price DECIMAL(14,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id)
);

-- ============================================================================
-- INDICES
-- ============================================================================

CREATE INDEX IF NOT EXISTS idx_po_construction_tenant_id ON purchase.purchase_order_construction(tenant_id);
CREATE INDEX IF NOT EXISTS idx_po_construction_po_id ON purchase.purchase_order_construction(purchase_order_id);
CREATE INDEX IF NOT EXISTS idx_po_construction_fraccionamiento_id ON purchase.purchase_order_construction(fraccionamiento_id);
CREATE INDEX IF NOT EXISTS idx_po_construction_requisicion_id ON purchase.purchase_order_construction(requisicion_id);

CREATE INDEX IF NOT EXISTS idx_supplier_construction_tenant_id ON purchase.supplier_construction(tenant_id);
CREATE INDEX IF NOT EXISTS idx_supplier_construction_supplier_id ON purchase.supplier_construction(supplier_id);
CREATE INDEX IF NOT EXISTS idx_supplier_construction_rating ON purchase.supplier_construction(overall_rating);

CREATE INDEX IF NOT EXISTS idx_comparativo_tenant_id ON purchase.comparativo_cotizaciones(tenant_id);
CREATE INDEX IF NOT EXISTS idx_comparativo_requisicion_id ON purchase.comparativo_cotizaciones(requisicion_id);
CREATE INDEX IF NOT EXISTS idx_comparativo_status ON purchase.comparativo_cotizaciones(status);

CREATE INDEX IF NOT EXISTS idx_comparativo_prov_tenant_id ON purchase.comparativo_proveedores(tenant_id);
CREATE INDEX IF NOT EXISTS idx_comparativo_prov_comparativo_id ON purchase.comparativo_proveedores(comparativo_id);
CREATE INDEX IF NOT EXISTS idx_comparativo_prov_supplier_id ON purchase.comparativo_proveedores(supplier_id);

CREATE INDEX IF NOT EXISTS idx_comparativo_prod_tenant_id ON purchase.comparativo_productos(tenant_id);
CREATE INDEX IF NOT EXISTS idx_comparativo_prod_proveedor_id ON purchase.comparativo_productos(comparativo_proveedor_id);

-- ============================================================================
-- ROW LEVEL SECURITY (RLS)
-- ============================================================================

ALTER TABLE purchase.purchase_order_construction ENABLE ROW LEVEL SECURITY;
ALTER TABLE purchase.supplier_construction ENABLE ROW LEVEL SECURITY;
ALTER TABLE purchase.comparativo_cotizaciones ENABLE ROW LEVEL SECURITY;
ALTER TABLE purchase.comparativo_proveedores ENABLE ROW LEVEL SECURITY;
ALTER TABLE purchase.comparativo_productos ENABLE ROW LEVEL SECURITY;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_po_construction ON purchase.purchase_order_construction;
    CREATE POLICY tenant_isolation_po_construction ON purchase.purchase_order_construction
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_supplier_construction ON purchase.supplier_construction;
    CREATE POLICY tenant_isolation_supplier_construction ON purchase.supplier_construction
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_comparativo ON purchase.comparativo_cotizaciones;
    CREATE POLICY tenant_isolation_comparativo ON purchase.comparativo_cotizaciones
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_comparativo_prov ON purchase.comparativo_proveedores;
    CREATE POLICY tenant_isolation_comparativo_prov ON purchase.comparativo_proveedores
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_comparativo_prod ON purchase.comparativo_productos;
    CREATE POLICY tenant_isolation_comparativo_prod ON purchase.comparativo_productos
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

-- ============================================================================
-- COMENTARIOS
-- ============================================================================

COMMENT ON TABLE purchase.purchase_order_construction IS 'Extensión: datos adicionales de OC para construcción';
COMMENT ON TABLE purchase.supplier_construction IS 'Extensión: datos adicionales de proveedores para construcción';
COMMENT ON TABLE purchase.comparativo_cotizaciones IS 'Extensión: cuadro comparativo de cotizaciones';
COMMENT ON TABLE purchase.comparativo_proveedores IS 'Extensión: proveedores participantes en comparativo';
COMMENT ON TABLE purchase.comparativo_productos IS 'Extensión: productos cotizados por proveedor';

-- ============================================================================
-- FK CONSTRAINTS A TABLAS ERP-CORE (Condicionales)
-- NOTA: Estos constraints solo se crean si las tablas del ERP-Core existen.
-- ============================================================================

-- FK: purchase_order_construction.purchase_order_id → purchases.purchase_orders
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'purchases' AND tablename = 'purchase_orders') THEN
        IF NOT EXISTS (
            SELECT 1 FROM pg_constraint WHERE conname = 'fk_po_construction_purchase_order'
        ) THEN
            ALTER TABLE purchase.purchase_order_construction
            ADD CONSTRAINT fk_po_construction_purchase_order
            FOREIGN KEY (purchase_order_id) REFERENCES purchases.purchase_orders(id)
            ON DELETE CASCADE;
            RAISE NOTICE 'FK creada: purchase_order_construction.purchase_order_id → purchases.purchase_orders';
        END IF;
    ELSE
        RAISE NOTICE 'AVISO: purchases.purchase_orders no existe. FK purchase_order_id pendiente.';
    END IF;
END $$;

-- FK: supplier_construction.supplier_id → partners.partners
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'partners' AND tablename = 'partners') THEN
        IF NOT EXISTS (
            SELECT 1 FROM pg_constraint WHERE conname = 'fk_supplier_construction_partner'
        ) THEN
            ALTER TABLE purchase.supplier_construction
            ADD CONSTRAINT fk_supplier_construction_partner
            FOREIGN KEY (supplier_id) REFERENCES partners.partners(id)
            ON DELETE RESTRICT;
            RAISE NOTICE 'FK creada: supplier_construction.supplier_id → partners.partners';
        END IF;
    ELSE
        RAISE NOTICE 'AVISO: partners.partners no existe. FK supplier_id pendiente.';
    END IF;
END $$;

-- FK: comparativo_cotizaciones.winner_supplier_id → partners.partners
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'partners' AND tablename = 'partners') THEN
        IF NOT EXISTS (
            SELECT 1 FROM pg_constraint WHERE conname = 'fk_comparativo_winner_supplier'
        ) THEN
            ALTER TABLE purchase.comparativo_cotizaciones
            ADD CONSTRAINT fk_comparativo_winner_supplier
            FOREIGN KEY (winner_supplier_id) REFERENCES partners.partners(id)
            ON DELETE SET NULL;
            RAISE NOTICE 'FK creada: comparativo_cotizaciones.winner_supplier_id → partners.partners';
        END IF;
    END IF;
END $$;

-- FK: comparativo_proveedores.supplier_id → partners.partners
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'partners' AND tablename = 'partners') THEN
        IF NOT EXISTS (
            SELECT 1 FROM pg_constraint WHERE conname = 'fk_comparativo_prov_supplier'
        ) THEN
            ALTER TABLE purchase.comparativo_proveedores
            ADD CONSTRAINT fk_comparativo_prov_supplier
            FOREIGN KEY (supplier_id) REFERENCES partners.partners(id)
            ON DELETE RESTRICT;
            RAISE NOTICE 'FK creada: comparativo_proveedores.supplier_id → partners.partners';
        END IF;
    END IF;
END $$;

-- FK: comparativo_productos.product_id → products.products
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'products' AND tablename = 'products') THEN
        IF NOT EXISTS (
            SELECT 1 FROM pg_constraint WHERE conname = 'fk_comparativo_prod_product'
        ) THEN
            ALTER TABLE purchase.comparativo_productos
            ADD CONSTRAINT fk_comparativo_prod_product
            FOREIGN KEY (product_id) REFERENCES products.products(id)
            ON DELETE RESTRICT;
            RAISE NOTICE 'FK creada: comparativo_productos.product_id → products.products';
        END IF;
    END IF;
END $$;

-- Índice adicional para product_id (faltaba)
CREATE INDEX IF NOT EXISTS idx_comparativo_prod_product_id ON purchase.comparativo_productos(product_id);

-- ============================================================================
-- FIN DE EXTENSIONES PURCHASE
-- Total tablas: 5
-- FK constraints condicionales: 5
-- ============================================================================