erp-mecanicas-diesel-database/init/09-purchasing-schema.sql

-- ===========================================
-- MECANICAS DIESEL - Schema de Compras
-- ===========================================
-- Sistema de ordenes de compra, proveedores y recepciones
-- Gestion completa del ciclo de compras del taller

-- ============================================
-- SCHEMA: purchasing
-- ============================================
CREATE SCHEMA IF NOT EXISTS purchasing;
COMMENT ON SCHEMA purchasing IS 'Gestion de compras: ordenes de compra, recepciones, proveedores';

-- Grants
GRANT USAGE ON SCHEMA purchasing TO mecanicas_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA purchasing TO mecanicas_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA purchasing GRANT ALL ON TABLES TO mecanicas_user;

-- ============================================
-- PROVEEDORES (complementa partners existentes)
-- ============================================

-- Extension de datos de proveedor
CREATE TABLE purchasing.suppliers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Datos basicos
    code VARCHAR(20) NOT NULL,
    name VARCHAR(256) NOT NULL,
    trade_name VARCHAR(256),                   -- Nombre comercial
    rfc VARCHAR(13),

    -- Contacto
    contact_name VARCHAR(256),
    email VARCHAR(256),
    phone VARCHAR(50),
    mobile VARCHAR(50),

    -- Direccion
    street VARCHAR(256),
    city VARCHAR(100),
    state VARCHAR(100),
    zip_code VARCHAR(10),
    country VARCHAR(100) DEFAULT 'Mexico',

    -- Datos comerciales
    payment_term_days INTEGER DEFAULT 30,
    credit_limit DECIMAL(20,6) DEFAULT 0,
    currency_code VARCHAR(3) DEFAULT 'MXN',

    -- Clasificacion
    category VARCHAR(50),                      -- refacciones, lubricantes, herramientas, etc.
    is_preferred BOOLEAN NOT NULL DEFAULT false,
    rating INTEGER CHECK (rating BETWEEN 1 AND 5),

    -- Notas
    notes TEXT,

    -- Metadatos
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ,
    created_by UUID,

    CONSTRAINT uq_supplier_code UNIQUE(tenant_id, code)
);

COMMENT ON TABLE purchasing.suppliers IS 'Proveedores del taller';

-- Indices para suppliers
CREATE INDEX idx_suppliers_tenant ON purchasing.suppliers(tenant_id);
CREATE INDEX idx_suppliers_name ON purchasing.suppliers(name);
CREATE INDEX idx_suppliers_category ON purchasing.suppliers(category);
CREATE INDEX idx_suppliers_preferred ON purchasing.suppliers(is_preferred) WHERE is_preferred = true;

-- RLS para suppliers
SELECT create_tenant_rls_policies('purchasing', 'suppliers');

-- Trigger para updated_at
CREATE TRIGGER set_updated_at_suppliers
    BEFORE UPDATE ON purchasing.suppliers
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_updated_at();

-- ============================================
-- ORDENES DE COMPRA
-- ============================================

-- Estados de orden de compra
CREATE TYPE purchasing.po_status AS ENUM (
    'draft',        -- Borrador
    'sent',         -- Enviada a proveedor
    'confirmed',    -- Confirmada por proveedor
    'partial',      -- Parcialmente recibida
    'received',     -- Completamente recibida
    'cancelled'     -- Cancelada
);

-- Ordenes de compra
CREATE TABLE purchasing.purchase_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Identificacion
    order_number VARCHAR(50) NOT NULL,
    reference VARCHAR(100),                    -- Referencia del proveedor

    -- Proveedor
    supplier_id UUID NOT NULL REFERENCES purchasing.suppliers(id),

    -- Estado
    status purchasing.po_status NOT NULL DEFAULT 'draft',

    -- Fechas
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    expected_date DATE,                        -- Fecha esperada de entrega
    received_date DATE,                        -- Fecha de recepcion completa

    -- Importes
    subtotal DECIMAL(20,6) NOT NULL DEFAULT 0,
    discount_amount DECIMAL(20,6) NOT NULL DEFAULT 0,
    tax_amount DECIMAL(20,6) NOT NULL DEFAULT 0,
    total DECIMAL(20,6) NOT NULL DEFAULT 0,
    currency_code VARCHAR(3) DEFAULT 'MXN',

    -- Urgencia (para taller)
    priority VARCHAR(20) DEFAULT 'normal',
    service_order_id UUID,                     -- Orden de servicio relacionada (si aplica)

    -- Notas
    notes TEXT,
    internal_notes TEXT,

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ,
    created_by UUID NOT NULL,
    confirmed_by UUID,
    confirmed_at TIMESTAMPTZ,

    CONSTRAINT uq_po_number UNIQUE(tenant_id, order_number),
    CONSTRAINT chk_po_priority CHECK (priority IN ('low', 'normal', 'high', 'urgent'))
);

COMMENT ON TABLE purchasing.purchase_orders IS 'Ordenes de compra a proveedores';
COMMENT ON COLUMN purchasing.purchase_orders.service_order_id IS 'Orden de servicio que origino la compra (para urgencias)';

-- Indices para purchase_orders
CREATE INDEX idx_po_tenant ON purchasing.purchase_orders(tenant_id);
CREATE INDEX idx_po_supplier ON purchasing.purchase_orders(supplier_id);
CREATE INDEX idx_po_status ON purchasing.purchase_orders(status);
CREATE INDEX idx_po_date ON purchasing.purchase_orders(order_date DESC);
CREATE INDEX idx_po_expected ON purchasing.purchase_orders(expected_date) WHERE status NOT IN ('received', 'cancelled');
CREATE INDEX idx_po_service_order ON purchasing.purchase_orders(service_order_id) WHERE service_order_id IS NOT NULL;

-- RLS para purchase_orders
SELECT create_tenant_rls_policies('purchasing', 'purchase_orders');

-- Triggers
CREATE TRIGGER set_updated_at_purchase_orders
    BEFORE UPDATE ON purchasing.purchase_orders
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_updated_at();

-- ============================================
-- LINEAS DE ORDEN DE COMPRA
-- ============================================

CREATE TABLE purchasing.purchase_order_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    purchase_order_id UUID NOT NULL REFERENCES purchasing.purchase_orders(id) ON DELETE CASCADE,

    -- Linea
    line_number INTEGER NOT NULL DEFAULT 1,

    -- Producto
    part_id UUID,                              -- Referencia a parts_management.parts
    product_code VARCHAR(50),                  -- Codigo del producto (desnormalizado)
    description VARCHAR(500) NOT NULL,

    -- Cantidades
    quantity DECIMAL(20,6) NOT NULL,
    unit_of_measure VARCHAR(20) DEFAULT 'PZA',
    received_quantity DECIMAL(20,6) NOT NULL DEFAULT 0,

    -- Precios
    unit_price DECIMAL(20,6) NOT NULL,
    discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
    subtotal DECIMAL(20,6) NOT NULL,
    tax_percent DECIMAL(5,2) NOT NULL DEFAULT 16.00,  -- IVA Mexico
    tax_amount DECIMAL(20,6) NOT NULL DEFAULT 0,
    total DECIMAL(20,6) NOT NULL,

    -- Fechas
    expected_date DATE,

    -- Estado de linea
    is_closed BOOLEAN NOT NULL DEFAULT false,

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE purchasing.purchase_order_lines IS 'Lineas de detalle de ordenes de compra';

-- Indices para lines
CREATE INDEX idx_pol_order ON purchasing.purchase_order_lines(purchase_order_id);
CREATE INDEX idx_pol_part ON purchasing.purchase_order_lines(part_id) WHERE part_id IS NOT NULL;
CREATE INDEX idx_pol_pending ON purchasing.purchase_order_lines(purchase_order_id)
    WHERE received_quantity < quantity AND is_closed = false;

-- ============================================
-- RECEPCIONES DE COMPRA
-- ============================================

CREATE TABLE purchasing.purchase_receipts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Identificacion
    receipt_number VARCHAR(50) NOT NULL,
    purchase_order_id UUID NOT NULL REFERENCES purchasing.purchase_orders(id),

    -- Fecha y proveedor
    receipt_date DATE NOT NULL DEFAULT CURRENT_DATE,
    supplier_id UUID NOT NULL REFERENCES purchasing.suppliers(id),

    -- Documentos del proveedor
    supplier_invoice VARCHAR(50),              -- Numero de factura proveedor
    supplier_delivery_note VARCHAR(50),        -- Remision del proveedor

    -- Notas
    notes TEXT,

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID NOT NULL,

    CONSTRAINT uq_receipt_number UNIQUE(tenant_id, receipt_number)
);

COMMENT ON TABLE purchasing.purchase_receipts IS 'Recepciones de mercancia de ordenes de compra';

-- Indices para receipts
CREATE INDEX idx_pr_tenant ON purchasing.purchase_receipts(tenant_id);
CREATE INDEX idx_pr_order ON purchasing.purchase_receipts(purchase_order_id);
CREATE INDEX idx_pr_date ON purchasing.purchase_receipts(receipt_date DESC);

-- RLS para receipts
SELECT create_tenant_rls_policies('purchasing', 'purchase_receipts');

-- Lineas de recepcion
CREATE TABLE purchasing.purchase_receipt_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    receipt_id UUID NOT NULL REFERENCES purchasing.purchase_receipts(id) ON DELETE CASCADE,
    order_line_id UUID NOT NULL REFERENCES purchasing.purchase_order_lines(id),

    -- Cantidades
    quantity_received DECIMAL(20,6) NOT NULL,
    quantity_rejected DECIMAL(20,6) NOT NULL DEFAULT 0,
    rejection_reason VARCHAR(256),

    -- Lote/Serie (si aplica)
    lot_number VARCHAR(100),
    serial_numbers TEXT[],

    -- Ubicacion destino
    location_id UUID,                          -- Referencia a inventory location

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE purchasing.purchase_receipt_lines IS 'Detalle de productos recibidos';

CREATE INDEX idx_prl_receipt ON purchasing.purchase_receipt_lines(receipt_id);
CREATE INDEX idx_prl_order_line ON purchasing.purchase_receipt_lines(order_line_id);

-- ============================================
-- FUNCIONES AUXILIARES
-- ============================================

-- Funcion para generar numero de orden de compra
CREATE OR REPLACE FUNCTION purchasing.generate_po_number(p_tenant_id UUID)
RETURNS VARCHAR(50) AS $$
DECLARE
    v_year TEXT;
    v_sequence INTEGER;
    v_number VARCHAR(50);
BEGIN
    v_year := TO_CHAR(CURRENT_DATE, 'YYYY');

    -- Obtener siguiente secuencia del año
    SELECT COALESCE(MAX(
        CAST(SUBSTRING(order_number FROM 'OC-' || v_year || '-(\d+)') AS INTEGER)
    ), 0) + 1
    INTO v_sequence
    FROM purchasing.purchase_orders
    WHERE tenant_id = p_tenant_id
      AND order_number LIKE 'OC-' || v_year || '-%';

    v_number := 'OC-' || v_year || '-' || LPAD(v_sequence::TEXT, 5, '0');

    RETURN v_number;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION purchasing.generate_po_number IS 'Genera numero secuencial de orden de compra (OC-YYYY-NNNNN)';

-- Funcion para generar numero de recepcion
CREATE OR REPLACE FUNCTION purchasing.generate_receipt_number(p_tenant_id UUID)
RETURNS VARCHAR(50) AS $$
DECLARE
    v_year TEXT;
    v_sequence INTEGER;
    v_number VARCHAR(50);
BEGIN
    v_year := TO_CHAR(CURRENT_DATE, 'YYYY');

    SELECT COALESCE(MAX(
        CAST(SUBSTRING(receipt_number FROM 'REC-' || v_year || '-(\d+)') AS INTEGER)
    ), 0) + 1
    INTO v_sequence
    FROM purchasing.purchase_receipts
    WHERE tenant_id = p_tenant_id
      AND receipt_number LIKE 'REC-' || v_year || '-%';

    v_number := 'REC-' || v_year || '-' || LPAD(v_sequence::TEXT, 5, '0');

    RETURN v_number;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION purchasing.generate_receipt_number IS 'Genera numero secuencial de recepcion (REC-YYYY-NNNNN)';

-- Funcion para calcular totales de linea
CREATE OR REPLACE FUNCTION purchasing.calculate_line_totals()
RETURNS TRIGGER AS $$
BEGIN
    -- Calcular subtotal (con descuento)
    NEW.subtotal := NEW.quantity * NEW.unit_price * (1 - NEW.discount_percent / 100);

    -- Calcular impuesto
    NEW.tax_amount := NEW.subtotal * NEW.tax_percent / 100;

    -- Calcular total
    NEW.total := NEW.subtotal + NEW.tax_amount;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER calculate_pol_totals
    BEFORE INSERT OR UPDATE OF quantity, unit_price, discount_percent, tax_percent
    ON purchasing.purchase_order_lines
    FOR EACH ROW
    EXECUTE FUNCTION purchasing.calculate_line_totals();

-- Funcion para actualizar totales de orden
CREATE OR REPLACE FUNCTION purchasing.update_order_totals()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE purchasing.purchase_orders po
    SET
        subtotal = COALESCE((
            SELECT SUM(subtotal) FROM purchasing.purchase_order_lines WHERE purchase_order_id = po.id
        ), 0),
        tax_amount = COALESCE((
            SELECT SUM(tax_amount) FROM purchasing.purchase_order_lines WHERE purchase_order_id = po.id
        ), 0),
        total = COALESCE((
            SELECT SUM(total) FROM purchasing.purchase_order_lines WHERE purchase_order_id = po.id
        ), 0),
        updated_at = NOW()
    WHERE id = COALESCE(NEW.purchase_order_id, OLD.purchase_order_id);

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_po_totals
    AFTER INSERT OR UPDATE OR DELETE
    ON purchasing.purchase_order_lines
    FOR EACH ROW
    EXECUTE FUNCTION purchasing.update_order_totals();

-- Funcion para actualizar cantidades recibidas
CREATE OR REPLACE FUNCTION purchasing.update_received_quantities()
RETURNS TRIGGER AS $$
DECLARE
    v_order_id UUID;
    v_total_lines INTEGER;
    v_received_lines INTEGER;
BEGIN
    -- Actualizar cantidad recibida en linea de orden
    UPDATE purchasing.purchase_order_lines pol
    SET received_quantity = COALESCE((
        SELECT SUM(prl.quantity_received)
        FROM purchasing.purchase_receipt_lines prl
        WHERE prl.order_line_id = pol.id
    ), 0)
    WHERE id = NEW.order_line_id;

    -- Obtener orden de compra
    SELECT purchase_order_id INTO v_order_id
    FROM purchasing.purchase_order_lines
    WHERE id = NEW.order_line_id;

    -- Verificar si toda la orden fue recibida
    SELECT
        COUNT(*),
        COUNT(*) FILTER (WHERE received_quantity >= quantity)
    INTO v_total_lines, v_received_lines
    FROM purchasing.purchase_order_lines
    WHERE purchase_order_id = v_order_id;

    -- Actualizar estado de la orden
    UPDATE purchasing.purchase_orders
    SET status = CASE
        WHEN v_received_lines = v_total_lines THEN 'received'::purchasing.po_status
        WHEN v_received_lines > 0 THEN 'partial'::purchasing.po_status
        ELSE status
    END,
    received_date = CASE
        WHEN v_received_lines = v_total_lines THEN CURRENT_DATE
        ELSE received_date
    END,
    updated_at = NOW()
    WHERE id = v_order_id;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_po_received
    AFTER INSERT
    ON purchasing.purchase_receipt_lines
    FOR EACH ROW
    EXECUTE FUNCTION purchasing.update_received_quantities();

-- ============================================
-- VISTAS UTILES
-- ============================================

-- Vista de ordenes de compra pendientes
CREATE VIEW purchasing.v_pending_orders AS
SELECT
    po.id,
    po.tenant_id,
    po.order_number,
    po.status,
    po.order_date,
    po.expected_date,
    s.name as supplier_name,
    s.contact_name,
    s.phone as supplier_phone,
    po.total,
    po.priority,
    po.service_order_id,
    CASE
        WHEN po.expected_date < CURRENT_DATE THEN 'overdue'
        WHEN po.expected_date = CURRENT_DATE THEN 'today'
        WHEN po.expected_date <= CURRENT_DATE + 3 THEN 'soon'
        ELSE 'normal'
    END as urgency,
    COUNT(pol.id) as line_count,
    SUM(CASE WHEN pol.received_quantity < pol.quantity THEN 1 ELSE 0 END) as pending_lines
FROM purchasing.purchase_orders po
JOIN purchasing.suppliers s ON s.id = po.supplier_id
LEFT JOIN purchasing.purchase_order_lines pol ON pol.purchase_order_id = po.id
WHERE po.status NOT IN ('received', 'cancelled')
GROUP BY po.id, po.tenant_id, po.order_number, po.status, po.order_date,
         po.expected_date, s.name, s.contact_name, s.phone, po.total,
         po.priority, po.service_order_id
ORDER BY po.expected_date ASC NULLS LAST, po.priority DESC;

COMMENT ON VIEW purchasing.v_pending_orders IS 'Ordenes de compra pendientes de recibir';

-- Vista de productos pendientes de recibir
CREATE VIEW purchasing.v_pending_products AS
SELECT
    po.tenant_id,
    pol.part_id,
    pol.product_code,
    pol.description,
    po.order_number,
    po.supplier_id,
    s.name as supplier_name,
    pol.quantity,
    pol.received_quantity,
    pol.quantity - pol.received_quantity as pending_quantity,
    pol.unit_price,
    po.expected_date,
    po.service_order_id
FROM purchasing.purchase_order_lines pol
JOIN purchasing.purchase_orders po ON po.id = pol.purchase_order_id
JOIN purchasing.suppliers s ON s.id = po.supplier_id
WHERE pol.received_quantity < pol.quantity
  AND pol.is_closed = false
  AND po.status NOT IN ('cancelled')
ORDER BY po.expected_date ASC NULLS LAST;

COMMENT ON VIEW purchasing.v_pending_products IS 'Productos pendientes de recibir por orden';

-- Vista de historial de compras por proveedor
CREATE VIEW purchasing.v_supplier_history AS
SELECT
    s.id as supplier_id,
    s.tenant_id,
    s.code as supplier_code,
    s.name as supplier_name,
    s.category,
    s.rating,
    COUNT(DISTINCT po.id) as total_orders,
    COUNT(DISTINCT po.id) FILTER (WHERE po.status = 'received') as completed_orders,
    SUM(po.total) FILTER (WHERE po.status = 'received') as total_purchased,
    AVG(po.received_date - po.expected_date) FILTER (WHERE po.status = 'received' AND po.expected_date IS NOT NULL) as avg_delivery_days,
    MAX(po.order_date) as last_order_date
FROM purchasing.suppliers s
LEFT JOIN purchasing.purchase_orders po ON po.supplier_id = s.id
WHERE s.is_active = true
GROUP BY s.id, s.tenant_id, s.code, s.name, s.category, s.rating
ORDER BY total_purchased DESC NULLS LAST;

COMMENT ON VIEW purchasing.v_supplier_history IS 'Historial y estadisticas de compras por proveedor';

-- ============================================
-- GRANTS ADICIONALES
-- ============================================
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA purchasing TO mecanicas_user;
GRANT SELECT ON ALL TABLES IN SCHEMA purchasing TO mecanicas_user;