398 lines
14 KiB
SQL
398 lines
14 KiB
SQL
-- ===========================================
|
|
-- MECANICAS DIESEL - Schema parts_management
|
|
-- ===========================================
|
|
-- Inventario de refacciones especifico del taller
|
|
|
|
SET search_path TO parts_management, public;
|
|
|
|
-- -------------------------------------------
|
|
-- PART_CATEGORIES - Categorías de refacciones
|
|
-- -------------------------------------------
|
|
CREATE TABLE parts_management.part_categories (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
name VARCHAR(100) NOT NULL,
|
|
description VARCHAR(300),
|
|
|
|
parent_id UUID REFERENCES parts_management.part_categories(id),
|
|
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_part_categories_tenant ON parts_management.part_categories(tenant_id);
|
|
CREATE INDEX idx_part_categories_parent ON parts_management.part_categories(parent_id);
|
|
|
|
CREATE TRIGGER trg_part_categories_updated_at
|
|
BEFORE UPDATE ON parts_management.part_categories
|
|
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
|
|
|
|
SELECT create_tenant_rls_policies('parts_management', 'part_categories');
|
|
|
|
-- -------------------------------------------
|
|
-- SUPPLIERS - Proveedores
|
|
-- -------------------------------------------
|
|
CREATE TABLE parts_management.suppliers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
name VARCHAR(200) NOT NULL,
|
|
legal_name VARCHAR(300),
|
|
rfc VARCHAR(13),
|
|
|
|
contact_name VARCHAR(200),
|
|
email VARCHAR(200),
|
|
phone VARCHAR(20),
|
|
|
|
address TEXT,
|
|
|
|
credit_days INTEGER DEFAULT 0 CHECK (credit_days >= 0),
|
|
discount_pct DECIMAL(5,2) DEFAULT 0 CHECK (discount_pct >= 0 AND discount_pct <= 100),
|
|
|
|
rating DECIMAL(3,2) CHECK (rating >= 0 AND rating <= 5),
|
|
|
|
notes TEXT,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_suppliers_tenant ON parts_management.suppliers(tenant_id);
|
|
CREATE INDEX idx_suppliers_name ON parts_management.suppliers(name);
|
|
|
|
CREATE TRIGGER trg_suppliers_updated_at
|
|
BEFORE UPDATE ON parts_management.suppliers
|
|
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
|
|
|
|
SELECT create_tenant_rls_policies('parts_management', 'suppliers');
|
|
|
|
-- -------------------------------------------
|
|
-- WAREHOUSE_LOCATIONS - Ubicaciones de almacén
|
|
-- -------------------------------------------
|
|
CREATE TABLE parts_management.warehouse_locations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
code VARCHAR(20) NOT NULL,
|
|
name VARCHAR(100),
|
|
description VARCHAR(200),
|
|
|
|
zone VARCHAR(10),
|
|
aisle VARCHAR(10),
|
|
level VARCHAR(10),
|
|
|
|
max_weight DECIMAL(10,2) CHECK (max_weight > 0),
|
|
max_volume DECIMAL(10,2) CHECK (max_volume > 0),
|
|
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
|
|
CONSTRAINT uq_location_code UNIQUE (tenant_id, code)
|
|
);
|
|
|
|
CREATE INDEX idx_locations_tenant ON parts_management.warehouse_locations(tenant_id);
|
|
CREATE INDEX idx_locations_zone ON parts_management.warehouse_locations(zone);
|
|
|
|
CREATE TRIGGER trg_warehouse_locations_updated_at
|
|
BEFORE UPDATE ON parts_management.warehouse_locations
|
|
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
|
|
|
|
SELECT create_tenant_rls_policies('parts_management', 'warehouse_locations');
|
|
|
|
-- -------------------------------------------
|
|
-- PARTS - Refacciones del taller
|
|
-- -------------------------------------------
|
|
CREATE TABLE parts_management.parts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
sku VARCHAR(50) NOT NULL,
|
|
name VARCHAR(300) NOT NULL,
|
|
description TEXT,
|
|
|
|
category_id UUID REFERENCES parts_management.part_categories(id),
|
|
|
|
brand VARCHAR(100),
|
|
manufacturer VARCHAR(100),
|
|
|
|
compatible_engines TEXT[],
|
|
|
|
cost DECIMAL(12,2) CHECK (cost >= 0),
|
|
price DECIMAL(12,2) NOT NULL CHECK (price >= 0),
|
|
|
|
-- Inventario
|
|
current_stock DECIMAL(10,3) DEFAULT 0 CHECK (current_stock >= 0),
|
|
reserved_stock DECIMAL(10,3) DEFAULT 0 CHECK (reserved_stock >= 0),
|
|
min_stock DECIMAL(10,3) DEFAULT 0 CHECK (min_stock >= 0),
|
|
max_stock DECIMAL(10,3) CHECK (max_stock > 0),
|
|
reorder_point DECIMAL(10,3) CHECK (reorder_point >= 0),
|
|
|
|
location_id UUID REFERENCES parts_management.warehouse_locations(id),
|
|
|
|
unit VARCHAR(20) DEFAULT 'pza',
|
|
|
|
barcode VARCHAR(50),
|
|
|
|
preferred_supplier_id UUID REFERENCES parts_management.suppliers(id),
|
|
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
|
|
CONSTRAINT uq_part_sku UNIQUE (tenant_id, sku),
|
|
CONSTRAINT chk_min_max_stock CHECK (max_stock IS NULL OR max_stock >= min_stock)
|
|
);
|
|
|
|
CREATE INDEX idx_parts_tenant ON parts_management.parts(tenant_id);
|
|
CREATE INDEX idx_parts_sku ON parts_management.parts(sku);
|
|
CREATE INDEX idx_parts_barcode ON parts_management.parts(barcode);
|
|
CREATE INDEX idx_parts_category ON parts_management.parts(category_id);
|
|
CREATE INDEX idx_parts_supplier ON parts_management.parts(preferred_supplier_id);
|
|
CREATE INDEX idx_parts_location ON parts_management.parts(location_id);
|
|
|
|
CREATE TRIGGER trg_parts_updated_at
|
|
BEFORE UPDATE ON parts_management.parts
|
|
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
|
|
|
|
SELECT create_tenant_rls_policies('parts_management', 'parts');
|
|
|
|
-- -------------------------------------------
|
|
-- PART_ALTERNATES - Códigos alternos
|
|
-- -------------------------------------------
|
|
CREATE TABLE parts_management.part_alternates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
part_id UUID NOT NULL REFERENCES parts_management.parts(id) ON DELETE CASCADE,
|
|
|
|
alternate_code VARCHAR(50) NOT NULL,
|
|
manufacturer VARCHAR(100),
|
|
|
|
is_preferred BOOLEAN DEFAULT FALSE,
|
|
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_alternates_code ON parts_management.part_alternates(alternate_code);
|
|
CREATE INDEX idx_alternates_part ON parts_management.part_alternates(part_id);
|
|
|
|
-- -------------------------------------------
|
|
-- PART_LOCATIONS - Multi-ubicación por refacción
|
|
-- -------------------------------------------
|
|
CREATE TABLE parts_management.part_locations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
part_id UUID NOT NULL REFERENCES parts_management.parts(id) ON DELETE CASCADE,
|
|
location_id UUID NOT NULL REFERENCES parts_management.warehouse_locations(id),
|
|
|
|
quantity DECIMAL(10,3) DEFAULT 0 CHECK (quantity >= 0),
|
|
is_primary BOOLEAN DEFAULT FALSE,
|
|
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
|
|
CONSTRAINT uq_part_location UNIQUE (part_id, location_id)
|
|
);
|
|
|
|
CREATE INDEX idx_part_locations_part ON parts_management.part_locations(part_id);
|
|
CREATE INDEX idx_part_locations_location ON parts_management.part_locations(location_id);
|
|
|
|
-- -------------------------------------------
|
|
-- INVENTORY_MOVEMENTS - Kardex
|
|
-- -------------------------------------------
|
|
CREATE TABLE parts_management.inventory_movements (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
part_id UUID NOT NULL REFERENCES parts_management.parts(id),
|
|
|
|
movement_type VARCHAR(30) NOT NULL
|
|
CHECK (movement_type IN ('purchase', 'consumption', 'adjustment_in', 'adjustment_out', 'return', 'transfer')),
|
|
|
|
reference_type VARCHAR(30) CHECK (reference_type IN ('service_order', 'purchase_order', 'adjustment', 'return')),
|
|
reference_id UUID,
|
|
reference_number VARCHAR(50),
|
|
|
|
quantity DECIMAL(10,3) NOT NULL,
|
|
previous_stock DECIMAL(10,3) NOT NULL,
|
|
new_stock DECIMAL(10,3) NOT NULL,
|
|
|
|
unit_cost DECIMAL(12,2) CHECK (unit_cost >= 0),
|
|
total_cost DECIMAL(12,2) CHECK (total_cost >= 0),
|
|
|
|
location_id UUID REFERENCES parts_management.warehouse_locations(id),
|
|
|
|
notes TEXT,
|
|
|
|
created_by UUID,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_movements_tenant ON parts_management.inventory_movements(tenant_id);
|
|
CREATE INDEX idx_movements_part ON parts_management.inventory_movements(part_id);
|
|
CREATE INDEX idx_movements_date ON parts_management.inventory_movements(created_at DESC);
|
|
CREATE INDEX idx_movements_reference ON parts_management.inventory_movements(reference_type, reference_id);
|
|
|
|
SELECT create_tenant_rls_policies('parts_management', 'inventory_movements');
|
|
|
|
-- -------------------------------------------
|
|
-- INVENTORY_ADJUSTMENTS - Ajustes de inventario
|
|
-- -------------------------------------------
|
|
CREATE TABLE parts_management.inventory_adjustments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
adjustment_number VARCHAR(20) NOT NULL,
|
|
|
|
adjustment_type VARCHAR(30) NOT NULL
|
|
CHECK (adjustment_type IN ('count', 'damage', 'expiry', 'correction', 'other')),
|
|
|
|
status VARCHAR(20) DEFAULT 'pending'
|
|
CHECK (status IN ('pending', 'approved', 'applied', 'rejected')),
|
|
|
|
total_items INTEGER DEFAULT 0,
|
|
total_value DECIMAL(12,2) DEFAULT 0,
|
|
|
|
reason TEXT NOT NULL,
|
|
notes TEXT,
|
|
|
|
approved_by UUID,
|
|
approved_at TIMESTAMP WITH TIME ZONE,
|
|
|
|
created_by UUID,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
|
|
CONSTRAINT uq_adjustment_number UNIQUE (tenant_id, adjustment_number)
|
|
);
|
|
|
|
CREATE INDEX idx_adjustments_tenant ON parts_management.inventory_adjustments(tenant_id);
|
|
CREATE INDEX idx_adjustments_status ON parts_management.inventory_adjustments(status);
|
|
|
|
CREATE TRIGGER trg_adjustments_updated_at
|
|
BEFORE UPDATE ON parts_management.inventory_adjustments
|
|
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
|
|
|
|
SELECT create_tenant_rls_policies('parts_management', 'inventory_adjustments');
|
|
|
|
-- -------------------------------------------
|
|
-- ADJUSTMENT_ITEMS - Ítems del ajuste
|
|
-- -------------------------------------------
|
|
CREATE TABLE parts_management.adjustment_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
adjustment_id UUID NOT NULL REFERENCES parts_management.inventory_adjustments(id) ON DELETE CASCADE,
|
|
|
|
part_id UUID NOT NULL REFERENCES parts_management.parts(id),
|
|
|
|
system_qty DECIMAL(10,3) NOT NULL,
|
|
physical_qty DECIMAL(10,3) NOT NULL,
|
|
difference DECIMAL(10,3) NOT NULL,
|
|
|
|
unit_cost DECIMAL(12,2) CHECK (unit_cost >= 0),
|
|
value_impact DECIMAL(12,2),
|
|
|
|
notes TEXT,
|
|
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_adjustment_items_adjustment ON parts_management.adjustment_items(adjustment_id);
|
|
CREATE INDEX idx_adjustment_items_part ON parts_management.adjustment_items(part_id);
|
|
|
|
-- -------------------------------------------
|
|
-- STOCK_ALERTS - Alertas de stock
|
|
-- -------------------------------------------
|
|
CREATE TABLE parts_management.stock_alerts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
part_id UUID NOT NULL REFERENCES parts_management.parts(id),
|
|
|
|
alert_type VARCHAR(30) NOT NULL
|
|
CHECK (alert_type IN ('low_stock', 'out_of_stock', 'overstock')),
|
|
|
|
current_stock DECIMAL(10,3),
|
|
threshold DECIMAL(10,3),
|
|
|
|
status VARCHAR(20) DEFAULT 'active'
|
|
CHECK (status IN ('active', 'acknowledged', 'resolved')),
|
|
|
|
acknowledged_by UUID,
|
|
acknowledged_at TIMESTAMP WITH TIME ZONE,
|
|
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_alerts_tenant ON parts_management.stock_alerts(tenant_id);
|
|
CREATE INDEX idx_alerts_status ON parts_management.stock_alerts(status);
|
|
CREATE INDEX idx_alerts_part ON parts_management.stock_alerts(part_id);
|
|
|
|
SELECT create_tenant_rls_policies('parts_management', 'stock_alerts');
|
|
|
|
-- -------------------------------------------
|
|
-- PHYSICAL_INVENTORY - Sesiones de inventario físico
|
|
-- -------------------------------------------
|
|
CREATE TABLE parts_management.physical_inventory (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
inventory_number VARCHAR(20) NOT NULL,
|
|
|
|
inventory_type VARCHAR(30) NOT NULL
|
|
CHECK (inventory_type IN ('full', 'zone', 'category', 'cyclic')),
|
|
|
|
status VARCHAR(20) DEFAULT 'in_progress'
|
|
CHECK (status IN ('in_progress', 'completed', 'cancelled')),
|
|
|
|
zones TEXT[],
|
|
categories UUID[],
|
|
|
|
total_items INTEGER DEFAULT 0,
|
|
counted_items INTEGER DEFAULT 0,
|
|
with_difference INTEGER DEFAULT 0,
|
|
|
|
started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
completed_at TIMESTAMP WITH TIME ZONE,
|
|
|
|
adjustment_id UUID REFERENCES parts_management.inventory_adjustments(id),
|
|
|
|
created_by UUID,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
|
|
CONSTRAINT uq_inventory_number UNIQUE (tenant_id, inventory_number)
|
|
);
|
|
|
|
CREATE INDEX idx_physical_inv_tenant ON parts_management.physical_inventory(tenant_id);
|
|
CREATE INDEX idx_physical_inv_status ON parts_management.physical_inventory(status);
|
|
|
|
SELECT create_tenant_rls_policies('parts_management', 'physical_inventory');
|
|
|
|
-- -------------------------------------------
|
|
-- INVENTORY_COUNTS - Conteos del inventario físico
|
|
-- -------------------------------------------
|
|
CREATE TABLE parts_management.inventory_counts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
inventory_id UUID NOT NULL REFERENCES parts_management.physical_inventory(id) ON DELETE CASCADE,
|
|
|
|
part_id UUID NOT NULL REFERENCES parts_management.parts(id),
|
|
location_id UUID REFERENCES parts_management.warehouse_locations(id),
|
|
|
|
system_qty DECIMAL(10,3) NOT NULL,
|
|
counted_qty DECIMAL(10,3),
|
|
difference DECIMAL(10,3),
|
|
|
|
counted_by UUID,
|
|
counted_at TIMESTAMP WITH TIME ZONE,
|
|
|
|
notes TEXT,
|
|
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
|
|
CONSTRAINT uq_count_part_location UNIQUE (inventory_id, part_id, location_id)
|
|
);
|
|
|
|
CREATE INDEX idx_inventory_counts_inventory ON parts_management.inventory_counts(inventory_id);
|
|
CREATE INDEX idx_inventory_counts_part ON parts_management.inventory_counts(part_id);
|