207 lines
8.6 KiB
SQL
207 lines
8.6 KiB
SQL
-- =====================================================
|
|
-- SCHEMA: inventory (EXTENSION)
|
|
-- PROPOSITO: Extensiones de inventario para construccion
|
|
-- MODULOS: MAI-004 (Compras e Inventarios)
|
|
-- FECHA: 2025-11-24
|
|
-- TIPO: Extension del ERP Generico (MGN-005)
|
|
-- =====================================================
|
|
|
|
-- NOTA: Este archivo contiene SOLO las extensiones especificas
|
|
-- de construccion. Las tablas base estan en el ERP Generico.
|
|
|
|
-- =====================================================
|
|
-- TYPES (ENUMs) ADICIONALES
|
|
-- =====================================================
|
|
|
|
CREATE TYPE inventory.warehouse_type_construction AS ENUM (
|
|
'central', -- Almacen central
|
|
'obra', -- Almacen en obra
|
|
'temporal', -- Almacen temporal
|
|
'transito' -- En transito
|
|
);
|
|
|
|
CREATE TYPE inventory.requisition_status AS ENUM (
|
|
'draft', -- Borrador
|
|
'submitted', -- Enviada
|
|
'approved', -- Aprobada
|
|
'partially_served', -- Parcialmente surtida
|
|
'served', -- Surtida
|
|
'cancelled' -- Cancelada
|
|
);
|
|
|
|
-- =====================================================
|
|
-- TABLES - EXTENSIONES CONSTRUCCION
|
|
-- =====================================================
|
|
|
|
-- Tabla: almacenes_proyecto (almacen por proyecto/obra)
|
|
-- Extiende: inventory.warehouses
|
|
CREATE TABLE inventory.almacenes_proyecto (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
warehouse_id UUID NOT NULL, -- FK a inventory.warehouses (ERP Generico)
|
|
fraccionamiento_id UUID NOT NULL REFERENCES construction.fraccionamientos(id),
|
|
warehouse_type inventory.warehouse_type_construction NOT NULL DEFAULT 'obra',
|
|
location_description TEXT,
|
|
location GEOMETRY(POINT, 4326), -- PostGIS
|
|
responsible_id UUID REFERENCES auth.users(id),
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMP,
|
|
deleted_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_almacenes_proyecto_warehouse UNIQUE (warehouse_id)
|
|
);
|
|
|
|
-- Tabla: requisiciones_obra (requisiciones desde obra)
|
|
CREATE TABLE inventory.requisiciones_obra (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
fraccionamiento_id UUID NOT NULL REFERENCES construction.fraccionamientos(id),
|
|
requisition_number VARCHAR(30) NOT NULL,
|
|
requisition_date DATE NOT NULL,
|
|
required_date DATE NOT NULL, -- Fecha requerida
|
|
status inventory.requisition_status NOT NULL DEFAULT 'draft',
|
|
priority VARCHAR(20) DEFAULT 'medium', -- low, medium, high, urgent
|
|
|
|
-- Solicitante
|
|
requested_by UUID NOT NULL REFERENCES auth.users(id),
|
|
destination_warehouse_id UUID, -- FK a inventory.warehouses
|
|
|
|
-- Aprobacion
|
|
approved_by UUID REFERENCES auth.users(id),
|
|
approved_at TIMESTAMP,
|
|
rejection_reason TEXT,
|
|
|
|
-- Relacion con OC
|
|
purchase_order_id UUID, -- FK a purchase.purchase_orders
|
|
|
|
notes TEXT,
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMP,
|
|
deleted_by UUID REFERENCES auth.users(id),
|
|
|
|
CONSTRAINT uq_requisiciones_obra_number UNIQUE (tenant_id, requisition_number)
|
|
);
|
|
|
|
-- Tabla: requisicion_lineas (lineas de requisicion)
|
|
CREATE TABLE inventory.requisicion_lineas (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
requisicion_id UUID NOT NULL REFERENCES inventory.requisiciones_obra(id) ON DELETE CASCADE,
|
|
product_id UUID NOT NULL, -- FK a inventory.products (ERP Generico)
|
|
concepto_id UUID REFERENCES construction.conceptos(id),
|
|
lote_id UUID REFERENCES construction.lotes(id),
|
|
quantity_requested DECIMAL(12,4) NOT NULL,
|
|
quantity_approved DECIMAL(12,4),
|
|
quantity_served DECIMAL(12,4) DEFAULT 0,
|
|
unit_id UUID, -- FK a core.uom
|
|
notes TEXT,
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMP,
|
|
deleted_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- Tabla: consumos_obra (consumos de materiales por obra/lote)
|
|
CREATE TABLE inventory.consumos_obra (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
stock_move_id UUID, -- FK a inventory.stock_moves (ERP Generico)
|
|
fraccionamiento_id UUID NOT NULL REFERENCES construction.fraccionamientos(id),
|
|
lote_id UUID REFERENCES construction.lotes(id),
|
|
departamento_id UUID REFERENCES construction.departamentos(id),
|
|
concepto_id UUID REFERENCES construction.conceptos(id),
|
|
product_id UUID NOT NULL, -- FK a inventory.products
|
|
quantity DECIMAL(12,4) NOT NULL,
|
|
unit_cost DECIMAL(12,4),
|
|
total_cost DECIMAL(14,2) GENERATED ALWAYS AS (quantity * unit_cost) STORED,
|
|
consumption_date DATE NOT NULL,
|
|
registered_by UUID NOT NULL REFERENCES auth.users(id),
|
|
notes TEXT,
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMP,
|
|
deleted_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- =====================================================
|
|
-- INDICES
|
|
-- =====================================================
|
|
|
|
-- Almacenes Proyecto
|
|
CREATE INDEX idx_almacenes_proyecto_tenant_id ON inventory.almacenes_proyecto(tenant_id);
|
|
CREATE INDEX idx_almacenes_proyecto_warehouse_id ON inventory.almacenes_proyecto(warehouse_id);
|
|
CREATE INDEX idx_almacenes_proyecto_fraccionamiento_id ON inventory.almacenes_proyecto(fraccionamiento_id);
|
|
|
|
-- Requisiciones Obra
|
|
CREATE INDEX idx_requisiciones_obra_tenant_id ON inventory.requisiciones_obra(tenant_id);
|
|
CREATE INDEX idx_requisiciones_obra_fraccionamiento_id ON inventory.requisiciones_obra(fraccionamiento_id);
|
|
CREATE INDEX idx_requisiciones_obra_status ON inventory.requisiciones_obra(status);
|
|
CREATE INDEX idx_requisiciones_obra_date ON inventory.requisiciones_obra(requisition_date);
|
|
CREATE INDEX idx_requisiciones_obra_required_date ON inventory.requisiciones_obra(required_date);
|
|
|
|
-- Requisicion Lineas
|
|
CREATE INDEX idx_requisicion_lineas_tenant_id ON inventory.requisicion_lineas(tenant_id);
|
|
CREATE INDEX idx_requisicion_lineas_requisicion_id ON inventory.requisicion_lineas(requisicion_id);
|
|
CREATE INDEX idx_requisicion_lineas_product_id ON inventory.requisicion_lineas(product_id);
|
|
|
|
-- Consumos Obra
|
|
CREATE INDEX idx_consumos_obra_tenant_id ON inventory.consumos_obra(tenant_id);
|
|
CREATE INDEX idx_consumos_obra_fraccionamiento_id ON inventory.consumos_obra(fraccionamiento_id);
|
|
CREATE INDEX idx_consumos_obra_lote_id ON inventory.consumos_obra(lote_id);
|
|
CREATE INDEX idx_consumos_obra_concepto_id ON inventory.consumos_obra(concepto_id);
|
|
CREATE INDEX idx_consumos_obra_product_id ON inventory.consumos_obra(product_id);
|
|
CREATE INDEX idx_consumos_obra_date ON inventory.consumos_obra(consumption_date);
|
|
|
|
-- =====================================================
|
|
-- ROW LEVEL SECURITY (RLS)
|
|
-- =====================================================
|
|
|
|
ALTER TABLE inventory.almacenes_proyecto ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE inventory.requisiciones_obra ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE inventory.requisicion_lineas ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE inventory.consumos_obra ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY tenant_isolation_almacenes_proyecto ON inventory.almacenes_proyecto
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
CREATE POLICY tenant_isolation_requisiciones_obra ON inventory.requisiciones_obra
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
CREATE POLICY tenant_isolation_requisicion_lineas ON inventory.requisicion_lineas
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
CREATE POLICY tenant_isolation_consumos_obra ON inventory.consumos_obra
|
|
USING (tenant_id = get_current_tenant_id());
|
|
|
|
-- =====================================================
|
|
-- COMENTARIOS
|
|
-- =====================================================
|
|
|
|
COMMENT ON TABLE inventory.almacenes_proyecto IS 'Extension: almacenes por proyecto de construccion';
|
|
COMMENT ON TABLE inventory.requisiciones_obra IS 'Extension: requisiciones de material desde obra';
|
|
COMMENT ON TABLE inventory.requisicion_lineas IS 'Extension: lineas de requisicion de obra';
|
|
COMMENT ON TABLE inventory.consumos_obra IS 'Extension: consumos de materiales por obra/lote';
|
|
|
|
-- =====================================================
|
|
-- FIN DE EXTENSIONES INVENTORY
|
|
-- =====================================================
|