470 lines
15 KiB
PL/PgSQL
470 lines
15 KiB
PL/PgSQL
-- ===========================================
|
|
-- MECANICAS DIESEL - Tracking de Garantias
|
|
-- ===========================================
|
|
-- Resuelve: GAP-10
|
|
-- Sistema de seguimiento de garantias de refacciones
|
|
-- Permite reclamar garantias a proveedores
|
|
|
|
-- ============================================
|
|
-- EXTENSION DE PARTS PARA GARANTIAS
|
|
-- ============================================
|
|
|
|
-- Agregar campos de garantia a parts si no existen
|
|
DO $$
|
|
BEGIN
|
|
-- warranty_months
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'parts_management'
|
|
AND table_name = 'parts'
|
|
AND column_name = 'warranty_months'
|
|
) THEN
|
|
ALTER TABLE parts_management.parts
|
|
ADD COLUMN warranty_months INTEGER DEFAULT 0;
|
|
COMMENT ON COLUMN parts_management.parts.warranty_months IS 'Meses de garantia del fabricante';
|
|
END IF;
|
|
|
|
-- warranty_policy
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'parts_management'
|
|
AND table_name = 'parts'
|
|
AND column_name = 'warranty_policy'
|
|
) THEN
|
|
ALTER TABLE parts_management.parts
|
|
ADD COLUMN warranty_policy TEXT;
|
|
COMMENT ON COLUMN parts_management.parts.warranty_policy IS 'Descripcion de politica de garantia';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- ============================================
|
|
-- TABLA DE GARANTIAS DE PARTES INSTALADAS
|
|
-- ============================================
|
|
|
|
-- Estados de garantia
|
|
CREATE TYPE parts_management.warranty_status AS ENUM (
|
|
'active', -- Garantia vigente
|
|
'expired', -- Garantia expirada
|
|
'claimed', -- Reclamo en proceso
|
|
'approved', -- Reclamo aprobado
|
|
'rejected', -- Reclamo rechazado
|
|
'replaced' -- Pieza reemplazada por garantia
|
|
);
|
|
|
|
-- Garantias de piezas instaladas
|
|
CREATE TABLE parts_management.warranty_claims (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
-- Referencia a la pieza
|
|
part_id UUID NOT NULL,
|
|
part_name VARCHAR(256) NOT NULL, -- Desnormalizado para historial
|
|
part_sku VARCHAR(50),
|
|
|
|
-- Referencia al proveedor/fabricante
|
|
supplier_id UUID,
|
|
supplier_name VARCHAR(256),
|
|
manufacturer VARCHAR(256),
|
|
|
|
-- Datos de instalacion
|
|
service_order_id UUID,
|
|
service_order_number VARCHAR(50),
|
|
installation_date DATE NOT NULL,
|
|
installation_notes TEXT,
|
|
|
|
-- Datos de garantia
|
|
warranty_months INTEGER NOT NULL DEFAULT 12,
|
|
expiration_date DATE NOT NULL,
|
|
serial_number VARCHAR(100),
|
|
lot_number VARCHAR(100),
|
|
|
|
-- Vehiculo (contexto)
|
|
vehicle_id UUID,
|
|
vehicle_plate VARCHAR(20),
|
|
vehicle_description VARCHAR(256),
|
|
|
|
-- Cliente
|
|
customer_id UUID,
|
|
customer_name VARCHAR(256),
|
|
|
|
-- Estado y reclamo
|
|
status parts_management.warranty_status NOT NULL DEFAULT 'active',
|
|
|
|
-- Datos del reclamo (si aplica)
|
|
claim_date DATE,
|
|
claim_reason TEXT,
|
|
claim_description TEXT,
|
|
defect_photos TEXT[], -- URLs de fotos del defecto
|
|
|
|
-- Resolucion
|
|
resolution_date DATE,
|
|
resolution_type VARCHAR(50), -- replacement, refund, repair, rejected
|
|
resolution_notes TEXT,
|
|
replacement_part_id UUID, -- Nueva pieza si fue reemplazo
|
|
|
|
-- Costos
|
|
original_cost DECIMAL(20,6),
|
|
claim_amount DECIMAL(20,6),
|
|
approved_amount DECIMAL(20,6),
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ,
|
|
created_by UUID,
|
|
claimed_by UUID,
|
|
resolved_by UUID
|
|
);
|
|
|
|
COMMENT ON TABLE parts_management.warranty_claims IS 'Registro de garantias de piezas instaladas en vehiculos';
|
|
COMMENT ON COLUMN parts_management.warranty_claims.defect_photos IS 'Array de URLs a fotos del defecto';
|
|
|
|
-- Indices para warranty_claims
|
|
CREATE INDEX idx_wc_tenant ON parts_management.warranty_claims(tenant_id);
|
|
CREATE INDEX idx_wc_part ON parts_management.warranty_claims(part_id);
|
|
CREATE INDEX idx_wc_service_order ON parts_management.warranty_claims(service_order_id) WHERE service_order_id IS NOT NULL;
|
|
CREATE INDEX idx_wc_vehicle ON parts_management.warranty_claims(vehicle_id) WHERE vehicle_id IS NOT NULL;
|
|
CREATE INDEX idx_wc_customer ON parts_management.warranty_claims(customer_id);
|
|
CREATE INDEX idx_wc_status ON parts_management.warranty_claims(status);
|
|
CREATE INDEX idx_wc_expiration ON parts_management.warranty_claims(expiration_date)
|
|
WHERE status = 'active';
|
|
CREATE INDEX idx_wc_supplier ON parts_management.warranty_claims(supplier_id) WHERE supplier_id IS NOT NULL;
|
|
|
|
-- RLS para warranty_claims
|
|
SELECT create_tenant_rls_policies('parts_management', 'warranty_claims');
|
|
|
|
-- Trigger para updated_at
|
|
CREATE TRIGGER set_updated_at_warranty_claims
|
|
BEFORE UPDATE ON parts_management.warranty_claims
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION trigger_set_updated_at();
|
|
|
|
-- ============================================
|
|
-- FUNCIONES AUXILIARES
|
|
-- ============================================
|
|
|
|
-- Funcion para crear registro de garantia al instalar pieza
|
|
CREATE OR REPLACE FUNCTION parts_management.create_warranty_record(
|
|
p_tenant_id UUID,
|
|
p_part_id UUID,
|
|
p_service_order_id UUID,
|
|
p_vehicle_id UUID DEFAULT NULL,
|
|
p_customer_id UUID DEFAULT NULL,
|
|
p_serial_number VARCHAR(100) DEFAULT NULL,
|
|
p_supplier_id UUID DEFAULT NULL
|
|
)
|
|
RETURNS UUID AS $$
|
|
DECLARE
|
|
v_warranty_id UUID;
|
|
v_part RECORD;
|
|
v_service_order RECORD;
|
|
v_vehicle RECORD;
|
|
v_customer RECORD;
|
|
v_supplier RECORD;
|
|
BEGIN
|
|
-- Obtener datos de la pieza
|
|
SELECT id, sku, name, warranty_months, warranty_policy, cost
|
|
INTO v_part
|
|
FROM parts_management.parts
|
|
WHERE id = p_part_id;
|
|
|
|
IF v_part.id IS NULL THEN
|
|
RAISE EXCEPTION 'Part % not found', p_part_id;
|
|
END IF;
|
|
|
|
-- Si no tiene garantia, no crear registro
|
|
IF COALESCE(v_part.warranty_months, 0) <= 0 THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
|
|
-- Obtener datos de orden de servicio
|
|
SELECT id, order_number
|
|
INTO v_service_order
|
|
FROM service_management.service_orders
|
|
WHERE id = p_service_order_id;
|
|
|
|
-- Obtener datos de vehiculo (si aplica)
|
|
IF p_vehicle_id IS NOT NULL THEN
|
|
SELECT id, plate_number,
|
|
CONCAT(brand, ' ', model, ' ', COALESCE(year::TEXT, '')) as description
|
|
INTO v_vehicle
|
|
FROM vehicle_management.vehicles
|
|
WHERE id = p_vehicle_id;
|
|
END IF;
|
|
|
|
-- Obtener datos de cliente (si aplica)
|
|
IF p_customer_id IS NOT NULL THEN
|
|
SELECT id, name
|
|
INTO v_customer
|
|
FROM workshop_core.customers
|
|
WHERE id = p_customer_id;
|
|
END IF;
|
|
|
|
-- Obtener datos de proveedor (si aplica)
|
|
IF p_supplier_id IS NOT NULL THEN
|
|
SELECT id, name
|
|
INTO v_supplier
|
|
FROM purchasing.suppliers
|
|
WHERE id = p_supplier_id;
|
|
END IF;
|
|
|
|
-- Crear registro de garantia
|
|
INSERT INTO parts_management.warranty_claims (
|
|
tenant_id,
|
|
part_id, part_name, part_sku,
|
|
supplier_id, supplier_name,
|
|
service_order_id, service_order_number,
|
|
installation_date, warranty_months, expiration_date,
|
|
serial_number,
|
|
vehicle_id, vehicle_plate, vehicle_description,
|
|
customer_id, customer_name,
|
|
original_cost,
|
|
created_by
|
|
)
|
|
VALUES (
|
|
p_tenant_id,
|
|
v_part.id, v_part.name, v_part.sku,
|
|
p_supplier_id, v_supplier.name,
|
|
p_service_order_id, v_service_order.order_number,
|
|
CURRENT_DATE, v_part.warranty_months,
|
|
CURRENT_DATE + (v_part.warranty_months || ' months')::INTERVAL,
|
|
p_serial_number,
|
|
p_vehicle_id, v_vehicle.plate_number, v_vehicle.description,
|
|
p_customer_id, v_customer.name,
|
|
v_part.cost,
|
|
get_current_user_id()
|
|
)
|
|
RETURNING id INTO v_warranty_id;
|
|
|
|
RETURN v_warranty_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION parts_management.create_warranty_record IS 'Crea registro de garantia al instalar una pieza';
|
|
|
|
-- Funcion para iniciar reclamo de garantia
|
|
CREATE OR REPLACE FUNCTION parts_management.initiate_warranty_claim(
|
|
p_warranty_id UUID,
|
|
p_claim_reason TEXT,
|
|
p_claim_description TEXT DEFAULT NULL,
|
|
p_defect_photos TEXT[] DEFAULT NULL
|
|
)
|
|
RETURNS BOOLEAN AS $$
|
|
DECLARE
|
|
v_warranty RECORD;
|
|
BEGIN
|
|
-- Obtener garantia
|
|
SELECT * INTO v_warranty
|
|
FROM parts_management.warranty_claims
|
|
WHERE id = p_warranty_id;
|
|
|
|
IF v_warranty.id IS NULL THEN
|
|
RAISE EXCEPTION 'Warranty record % not found', p_warranty_id;
|
|
END IF;
|
|
|
|
-- Verificar que este activa
|
|
IF v_warranty.status != 'active' THEN
|
|
RAISE EXCEPTION 'Warranty is not active (current status: %)', v_warranty.status;
|
|
END IF;
|
|
|
|
-- Verificar que no este expirada
|
|
IF v_warranty.expiration_date < CURRENT_DATE THEN
|
|
-- Actualizar a expirada primero
|
|
UPDATE parts_management.warranty_claims
|
|
SET status = 'expired', updated_at = NOW()
|
|
WHERE id = p_warranty_id;
|
|
|
|
RAISE EXCEPTION 'Warranty expired on %', v_warranty.expiration_date;
|
|
END IF;
|
|
|
|
-- Actualizar con datos del reclamo
|
|
UPDATE parts_management.warranty_claims
|
|
SET
|
|
status = 'claimed',
|
|
claim_date = CURRENT_DATE,
|
|
claim_reason = p_claim_reason,
|
|
claim_description = p_claim_description,
|
|
defect_photos = p_defect_photos,
|
|
claim_amount = original_cost,
|
|
claimed_by = get_current_user_id(),
|
|
updated_at = NOW()
|
|
WHERE id = p_warranty_id;
|
|
|
|
RETURN TRUE;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION parts_management.initiate_warranty_claim IS 'Inicia un reclamo de garantia';
|
|
|
|
-- Funcion para resolver reclamo
|
|
CREATE OR REPLACE FUNCTION parts_management.resolve_warranty_claim(
|
|
p_warranty_id UUID,
|
|
p_resolution_type VARCHAR(50),
|
|
p_approved_amount DECIMAL(20,6) DEFAULT NULL,
|
|
p_resolution_notes TEXT DEFAULT NULL,
|
|
p_replacement_part_id UUID DEFAULT NULL
|
|
)
|
|
RETURNS BOOLEAN AS $$
|
|
DECLARE
|
|
v_new_status parts_management.warranty_status;
|
|
BEGIN
|
|
-- Determinar nuevo estado segun resolucion
|
|
v_new_status := CASE p_resolution_type
|
|
WHEN 'replacement' THEN 'replaced'::parts_management.warranty_status
|
|
WHEN 'refund' THEN 'approved'::parts_management.warranty_status
|
|
WHEN 'repair' THEN 'approved'::parts_management.warranty_status
|
|
WHEN 'rejected' THEN 'rejected'::parts_management.warranty_status
|
|
ELSE 'approved'::parts_management.warranty_status
|
|
END;
|
|
|
|
UPDATE parts_management.warranty_claims
|
|
SET
|
|
status = v_new_status,
|
|
resolution_date = CURRENT_DATE,
|
|
resolution_type = p_resolution_type,
|
|
resolution_notes = p_resolution_notes,
|
|
approved_amount = CASE
|
|
WHEN p_resolution_type = 'rejected' THEN 0
|
|
ELSE COALESCE(p_approved_amount, claim_amount)
|
|
END,
|
|
replacement_part_id = p_replacement_part_id,
|
|
resolved_by = get_current_user_id(),
|
|
updated_at = NOW()
|
|
WHERE id = p_warranty_id
|
|
AND status = 'claimed';
|
|
|
|
RETURN FOUND;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION parts_management.resolve_warranty_claim IS 'Resuelve un reclamo de garantia';
|
|
|
|
-- Funcion para actualizar garantias expiradas (ejecutar diariamente)
|
|
CREATE OR REPLACE FUNCTION parts_management.expire_warranties()
|
|
RETURNS INTEGER AS $$
|
|
DECLARE
|
|
v_count INTEGER;
|
|
BEGIN
|
|
UPDATE parts_management.warranty_claims
|
|
SET
|
|
status = 'expired',
|
|
updated_at = NOW()
|
|
WHERE status = 'active'
|
|
AND expiration_date < CURRENT_DATE;
|
|
|
|
GET DIAGNOSTICS v_count = ROW_COUNT;
|
|
RETURN v_count;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION parts_management.expire_warranties IS 'Marca como expiradas las garantias vencidas';
|
|
|
|
-- ============================================
|
|
-- VISTAS DE REPORTES
|
|
-- ============================================
|
|
|
|
-- Vista de garantias activas
|
|
CREATE VIEW parts_management.v_active_warranties AS
|
|
SELECT
|
|
wc.id,
|
|
wc.tenant_id,
|
|
wc.part_id,
|
|
wc.part_name,
|
|
wc.part_sku,
|
|
wc.supplier_name,
|
|
wc.manufacturer,
|
|
wc.service_order_number,
|
|
wc.installation_date,
|
|
wc.expiration_date,
|
|
wc.serial_number,
|
|
wc.vehicle_plate,
|
|
wc.vehicle_description,
|
|
wc.customer_name,
|
|
wc.original_cost,
|
|
-- Dias restantes
|
|
wc.expiration_date - CURRENT_DATE as days_remaining,
|
|
-- Urgencia de expiracion
|
|
CASE
|
|
WHEN wc.expiration_date - CURRENT_DATE <= 7 THEN 'critical'
|
|
WHEN wc.expiration_date - CURRENT_DATE <= 30 THEN 'warning'
|
|
WHEN wc.expiration_date - CURRENT_DATE <= 90 THEN 'notice'
|
|
ELSE 'ok'
|
|
END as expiration_urgency
|
|
FROM parts_management.warranty_claims wc
|
|
WHERE wc.status = 'active'
|
|
AND wc.expiration_date >= CURRENT_DATE
|
|
ORDER BY wc.expiration_date ASC;
|
|
|
|
COMMENT ON VIEW parts_management.v_active_warranties IS 'Garantias vigentes con dias restantes';
|
|
|
|
-- Vista de reclamos pendientes
|
|
CREATE VIEW parts_management.v_pending_claims AS
|
|
SELECT
|
|
wc.id,
|
|
wc.tenant_id,
|
|
wc.part_name,
|
|
wc.part_sku,
|
|
wc.supplier_id,
|
|
wc.supplier_name,
|
|
wc.claim_date,
|
|
wc.claim_reason,
|
|
wc.claim_amount,
|
|
wc.vehicle_plate,
|
|
wc.customer_name,
|
|
CURRENT_DATE - wc.claim_date as days_pending
|
|
FROM parts_management.warranty_claims wc
|
|
WHERE wc.status = 'claimed'
|
|
ORDER BY wc.claim_date ASC;
|
|
|
|
COMMENT ON VIEW parts_management.v_pending_claims IS 'Reclamos de garantia pendientes de resolucion';
|
|
|
|
-- Vista resumen de garantias por proveedor
|
|
CREATE VIEW parts_management.v_warranty_summary_by_supplier AS
|
|
SELECT
|
|
wc.tenant_id,
|
|
wc.supplier_id,
|
|
wc.supplier_name,
|
|
COUNT(*) as total_warranties,
|
|
COUNT(*) FILTER (WHERE status = 'active') as active_warranties,
|
|
COUNT(*) FILTER (WHERE status = 'claimed') as pending_claims,
|
|
COUNT(*) FILTER (WHERE status IN ('approved', 'replaced')) as approved_claims,
|
|
COUNT(*) FILTER (WHERE status = 'rejected') as rejected_claims,
|
|
COALESCE(SUM(approved_amount) FILTER (WHERE status IN ('approved', 'replaced')), 0) as total_approved_amount,
|
|
ROUND(
|
|
COUNT(*) FILTER (WHERE status IN ('approved', 'replaced'))::DECIMAL /
|
|
NULLIF(COUNT(*) FILTER (WHERE status IN ('approved', 'replaced', 'rejected')), 0) * 100,
|
|
2
|
|
) as approval_rate
|
|
FROM parts_management.warranty_claims wc
|
|
WHERE wc.supplier_id IS NOT NULL
|
|
GROUP BY wc.tenant_id, wc.supplier_id, wc.supplier_name
|
|
ORDER BY total_warranties DESC;
|
|
|
|
COMMENT ON VIEW parts_management.v_warranty_summary_by_supplier IS 'Resumen de garantias agrupado por proveedor';
|
|
|
|
-- Vista de garantias por vehiculo
|
|
CREATE VIEW parts_management.v_vehicle_warranties AS
|
|
SELECT
|
|
wc.tenant_id,
|
|
wc.vehicle_id,
|
|
wc.vehicle_plate,
|
|
wc.vehicle_description,
|
|
wc.customer_id,
|
|
wc.customer_name,
|
|
COUNT(*) as total_parts_with_warranty,
|
|
COUNT(*) FILTER (WHERE status = 'active' AND expiration_date >= CURRENT_DATE) as active_warranties,
|
|
COUNT(*) FILTER (WHERE status = 'active' AND expiration_date >= CURRENT_DATE AND expiration_date - CURRENT_DATE <= 30) as expiring_soon,
|
|
COALESCE(SUM(original_cost), 0) as total_warranty_value
|
|
FROM parts_management.warranty_claims wc
|
|
WHERE wc.vehicle_id IS NOT NULL
|
|
GROUP BY wc.tenant_id, wc.vehicle_id, wc.vehicle_plate, wc.vehicle_description,
|
|
wc.customer_id, wc.customer_name
|
|
ORDER BY active_warranties DESC;
|
|
|
|
COMMENT ON VIEW parts_management.v_vehicle_warranties IS 'Resumen de garantias por vehiculo';
|
|
|
|
-- ============================================
|
|
-- GRANTS
|
|
-- ============================================
|
|
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA parts_management TO mecanicas_user;
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA parts_management TO mecanicas_user;
|