427 lines
14 KiB
PL/PgSQL
427 lines
14 KiB
PL/PgSQL
-- ===========================================
|
|
-- MECANICAS DIESEL - Firma Electronica Basica
|
|
-- ===========================================
|
|
-- Resuelve: GAP-12
|
|
-- Sistema de firma canvas para aprobacion de cotizaciones
|
|
-- Nota: Para NOM-151 completa ver SPEC-FIRMA-ELECTRONICA-NOM151.md
|
|
|
|
-- ============================================
|
|
-- EXTENSION DE QUOTES PARA FIRMA
|
|
-- ============================================
|
|
|
|
-- Agregar campos de firma a cotizaciones
|
|
DO $$
|
|
BEGIN
|
|
-- signature_data (canvas base64)
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'service_management'
|
|
AND table_name = 'quotes'
|
|
AND column_name = 'signature_data'
|
|
) THEN
|
|
ALTER TABLE service_management.quotes
|
|
ADD COLUMN signature_data TEXT;
|
|
COMMENT ON COLUMN service_management.quotes.signature_data IS 'Firma canvas en formato base64 PNG';
|
|
END IF;
|
|
|
|
-- signed_at
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'service_management'
|
|
AND table_name = 'quotes'
|
|
AND column_name = 'signed_at'
|
|
) THEN
|
|
ALTER TABLE service_management.quotes
|
|
ADD COLUMN signed_at TIMESTAMPTZ;
|
|
COMMENT ON COLUMN service_management.quotes.signed_at IS 'Fecha y hora de firma';
|
|
END IF;
|
|
|
|
-- signed_by_name
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'service_management'
|
|
AND table_name = 'quotes'
|
|
AND column_name = 'signed_by_name'
|
|
) THEN
|
|
ALTER TABLE service_management.quotes
|
|
ADD COLUMN signed_by_name VARCHAR(256);
|
|
COMMENT ON COLUMN service_management.quotes.signed_by_name IS 'Nombre de quien firmo';
|
|
END IF;
|
|
|
|
-- signed_by_ip
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'service_management'
|
|
AND table_name = 'quotes'
|
|
AND column_name = 'signed_by_ip'
|
|
) THEN
|
|
ALTER TABLE service_management.quotes
|
|
ADD COLUMN signed_by_ip VARCHAR(45);
|
|
COMMENT ON COLUMN service_management.quotes.signed_by_ip IS 'IP desde donde se firmo';
|
|
END IF;
|
|
|
|
-- signed_by_email
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'service_management'
|
|
AND table_name = 'quotes'
|
|
AND column_name = 'signed_by_email'
|
|
) THEN
|
|
ALTER TABLE service_management.quotes
|
|
ADD COLUMN signed_by_email VARCHAR(256);
|
|
COMMENT ON COLUMN service_management.quotes.signed_by_email IS 'Email de quien firmo';
|
|
END IF;
|
|
|
|
-- signature_hash
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'service_management'
|
|
AND table_name = 'quotes'
|
|
AND column_name = 'signature_hash'
|
|
) THEN
|
|
ALTER TABLE service_management.quotes
|
|
ADD COLUMN signature_hash VARCHAR(128);
|
|
COMMENT ON COLUMN service_management.quotes.signature_hash IS 'Hash SHA-256 del documento al momento de firmar';
|
|
END IF;
|
|
|
|
-- approval_token
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'service_management'
|
|
AND table_name = 'quotes'
|
|
AND column_name = 'approval_token'
|
|
) THEN
|
|
ALTER TABLE service_management.quotes
|
|
ADD COLUMN approval_token VARCHAR(64);
|
|
COMMENT ON COLUMN service_management.quotes.approval_token IS 'Token unico para link de aprobacion';
|
|
END IF;
|
|
|
|
-- token_expires_at
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'service_management'
|
|
AND table_name = 'quotes'
|
|
AND column_name = 'token_expires_at'
|
|
) THEN
|
|
ALTER TABLE service_management.quotes
|
|
ADD COLUMN token_expires_at TIMESTAMPTZ;
|
|
COMMENT ON COLUMN service_management.quotes.token_expires_at IS 'Expiracion del token de aprobacion';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Indice para busqueda por token
|
|
CREATE INDEX IF NOT EXISTS idx_quotes_approval_token
|
|
ON service_management.quotes(approval_token)
|
|
WHERE approval_token IS NOT NULL;
|
|
|
|
-- ============================================
|
|
-- TABLA DE HISTORIAL DE FIRMAS
|
|
-- ============================================
|
|
|
|
-- Historial de todas las firmas (para auditoria)
|
|
CREATE TABLE IF NOT EXISTS service_management.signature_audit (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
-- Documento firmado
|
|
document_type VARCHAR(50) NOT NULL, -- 'quote', 'service_order', etc.
|
|
document_id UUID NOT NULL,
|
|
document_number VARCHAR(50),
|
|
|
|
-- Datos del firmante
|
|
signer_name VARCHAR(256) NOT NULL,
|
|
signer_email VARCHAR(256),
|
|
signer_phone VARCHAR(50),
|
|
signer_ip VARCHAR(45),
|
|
signer_user_agent TEXT,
|
|
|
|
-- Firma
|
|
signature_data TEXT NOT NULL, -- Base64 de la imagen de firma
|
|
signature_method VARCHAR(50) NOT NULL DEFAULT 'canvas', -- canvas, typed, upload
|
|
|
|
-- Integridad
|
|
document_hash VARCHAR(128) NOT NULL, -- Hash del documento al firmar
|
|
signature_hash VARCHAR(128), -- Hash de la firma
|
|
document_snapshot JSONB, -- Snapshot del documento
|
|
|
|
-- Contexto
|
|
action VARCHAR(50) NOT NULL, -- 'approve', 'reject', 'acknowledge'
|
|
comments TEXT,
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
-- Geolocation (opcional, si el cliente lo permite)
|
|
geo_latitude DECIMAL(10, 8),
|
|
geo_longitude DECIMAL(11, 8),
|
|
geo_accuracy DECIMAL(10, 2)
|
|
);
|
|
|
|
COMMENT ON TABLE service_management.signature_audit IS 'Registro de auditoria de todas las firmas electronicas';
|
|
|
|
-- Indices
|
|
CREATE INDEX idx_sig_audit_tenant ON service_management.signature_audit(tenant_id);
|
|
CREATE INDEX idx_sig_audit_document ON service_management.signature_audit(document_type, document_id);
|
|
CREATE INDEX idx_sig_audit_signer ON service_management.signature_audit(signer_email);
|
|
CREATE INDEX idx_sig_audit_created ON service_management.signature_audit(created_at DESC);
|
|
|
|
-- RLS
|
|
SELECT create_tenant_rls_policies('service_management', 'signature_audit');
|
|
|
|
-- ============================================
|
|
-- FUNCIONES AUXILIARES
|
|
-- ============================================
|
|
|
|
-- Funcion para generar token de aprobacion
|
|
CREATE OR REPLACE FUNCTION service_management.generate_approval_token(
|
|
p_quote_id UUID,
|
|
p_expires_hours INTEGER DEFAULT 72
|
|
)
|
|
RETURNS VARCHAR(64) AS $$
|
|
DECLARE
|
|
v_token VARCHAR(64);
|
|
BEGIN
|
|
-- Generar token aleatorio
|
|
v_token := encode(gen_random_bytes(32), 'hex');
|
|
|
|
-- Actualizar cotizacion con token
|
|
UPDATE service_management.quotes
|
|
SET
|
|
approval_token = v_token,
|
|
token_expires_at = NOW() + (p_expires_hours || ' hours')::INTERVAL
|
|
WHERE id = p_quote_id;
|
|
|
|
RETURN v_token;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION service_management.generate_approval_token IS 'Genera token de aprobacion para cotizacion';
|
|
|
|
-- Funcion para validar token
|
|
CREATE OR REPLACE FUNCTION service_management.validate_approval_token(
|
|
p_token VARCHAR(64)
|
|
)
|
|
RETURNS TABLE (
|
|
quote_id UUID,
|
|
is_valid BOOLEAN,
|
|
error_message TEXT
|
|
) AS $$
|
|
DECLARE
|
|
v_quote RECORD;
|
|
BEGIN
|
|
-- Buscar cotizacion con token
|
|
SELECT q.id, q.status, q.token_expires_at, q.signed_at
|
|
INTO v_quote
|
|
FROM service_management.quotes q
|
|
WHERE q.approval_token = p_token;
|
|
|
|
-- Token no encontrado
|
|
IF v_quote.id IS NULL THEN
|
|
RETURN QUERY SELECT
|
|
NULL::UUID,
|
|
false,
|
|
'Token invalido o no encontrado'::TEXT;
|
|
RETURN;
|
|
END IF;
|
|
|
|
-- Token expirado
|
|
IF v_quote.token_expires_at < NOW() THEN
|
|
RETURN QUERY SELECT
|
|
v_quote.id,
|
|
false,
|
|
'El token ha expirado'::TEXT;
|
|
RETURN;
|
|
END IF;
|
|
|
|
-- Ya firmada
|
|
IF v_quote.signed_at IS NOT NULL THEN
|
|
RETURN QUERY SELECT
|
|
v_quote.id,
|
|
false,
|
|
'La cotizacion ya fue firmada'::TEXT;
|
|
RETURN;
|
|
END IF;
|
|
|
|
-- Cotizacion no esta en estado correcto
|
|
IF v_quote.status NOT IN ('sent', 'pending') THEN
|
|
RETURN QUERY SELECT
|
|
v_quote.id,
|
|
false,
|
|
'La cotizacion no esta disponible para aprobacion'::TEXT;
|
|
RETURN;
|
|
END IF;
|
|
|
|
-- Token valido
|
|
RETURN QUERY SELECT
|
|
v_quote.id,
|
|
true,
|
|
NULL::TEXT;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION service_management.validate_approval_token IS 'Valida token de aprobacion y estado de cotizacion';
|
|
|
|
-- Funcion para firmar cotizacion
|
|
CREATE OR REPLACE FUNCTION service_management.sign_quote(
|
|
p_quote_id UUID,
|
|
p_signature_data TEXT,
|
|
p_signer_name VARCHAR(256),
|
|
p_signer_email VARCHAR(256) DEFAULT NULL,
|
|
p_signer_ip VARCHAR(45) DEFAULT NULL,
|
|
p_user_agent TEXT DEFAULT NULL,
|
|
p_comments TEXT DEFAULT NULL,
|
|
p_action VARCHAR(50) DEFAULT 'approve'
|
|
)
|
|
RETURNS UUID AS $$
|
|
DECLARE
|
|
v_quote RECORD;
|
|
v_audit_id UUID;
|
|
v_document_hash VARCHAR(128);
|
|
v_signature_hash VARCHAR(128);
|
|
v_new_status VARCHAR(20);
|
|
BEGIN
|
|
-- Obtener cotizacion
|
|
SELECT q.*, c.name as customer_name
|
|
INTO v_quote
|
|
FROM service_management.quotes q
|
|
LEFT JOIN workshop_core.customers c ON c.id = q.customer_id
|
|
WHERE q.id = p_quote_id;
|
|
|
|
IF v_quote.id IS NULL THEN
|
|
RAISE EXCEPTION 'Quote % not found', p_quote_id;
|
|
END IF;
|
|
|
|
-- Verificar que no este ya firmada
|
|
IF v_quote.signed_at IS NOT NULL THEN
|
|
RAISE EXCEPTION 'Quote already signed on %', v_quote.signed_at;
|
|
END IF;
|
|
|
|
-- Generar hash del documento (simplificado - en produccion usar contenido completo)
|
|
v_document_hash := encode(
|
|
sha256(
|
|
(v_quote.id::TEXT || v_quote.total::TEXT || v_quote.created_at::TEXT)::bytea
|
|
),
|
|
'hex'
|
|
);
|
|
|
|
-- Generar hash de la firma
|
|
v_signature_hash := encode(sha256(p_signature_data::bytea), 'hex');
|
|
|
|
-- Determinar nuevo estado
|
|
v_new_status := CASE p_action
|
|
WHEN 'approve' THEN 'approved'
|
|
WHEN 'reject' THEN 'rejected'
|
|
ELSE 'pending'
|
|
END;
|
|
|
|
-- Actualizar cotizacion
|
|
UPDATE service_management.quotes
|
|
SET
|
|
status = v_new_status,
|
|
signature_data = p_signature_data,
|
|
signed_at = NOW(),
|
|
signed_by_name = p_signer_name,
|
|
signed_by_email = p_signer_email,
|
|
signed_by_ip = p_signer_ip,
|
|
signature_hash = v_document_hash,
|
|
approval_token = NULL, -- Invalidar token
|
|
token_expires_at = NULL,
|
|
updated_at = NOW()
|
|
WHERE id = p_quote_id;
|
|
|
|
-- Crear registro de auditoria
|
|
INSERT INTO service_management.signature_audit (
|
|
tenant_id,
|
|
document_type, document_id, document_number,
|
|
signer_name, signer_email, signer_ip, signer_user_agent,
|
|
signature_data, signature_method,
|
|
document_hash, signature_hash,
|
|
document_snapshot,
|
|
action, comments
|
|
)
|
|
VALUES (
|
|
v_quote.tenant_id,
|
|
'quote', p_quote_id, v_quote.quote_number,
|
|
p_signer_name, p_signer_email, p_signer_ip, p_user_agent,
|
|
p_signature_data, 'canvas',
|
|
v_document_hash, v_signature_hash,
|
|
jsonb_build_object(
|
|
'quote_number', v_quote.quote_number,
|
|
'customer_name', v_quote.customer_name,
|
|
'total', v_quote.total,
|
|
'created_at', v_quote.created_at,
|
|
'items_count', (SELECT COUNT(*) FROM service_management.quote_lines WHERE quote_id = p_quote_id)
|
|
),
|
|
p_action, p_comments
|
|
)
|
|
RETURNING id INTO v_audit_id;
|
|
|
|
RETURN v_audit_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION service_management.sign_quote IS 'Firma una cotizacion con firma canvas y crea registro de auditoria';
|
|
|
|
-- ============================================
|
|
-- VISTAS
|
|
-- ============================================
|
|
|
|
-- Vista de cotizaciones pendientes de firma
|
|
CREATE VIEW service_management.v_quotes_pending_signature AS
|
|
SELECT
|
|
q.id,
|
|
q.tenant_id,
|
|
q.quote_number,
|
|
q.status,
|
|
q.total,
|
|
c.name as customer_name,
|
|
c.email as customer_email,
|
|
c.phone as customer_phone,
|
|
q.approval_token IS NOT NULL as has_token,
|
|
q.token_expires_at,
|
|
CASE
|
|
WHEN q.token_expires_at IS NULL THEN 'no_token'
|
|
WHEN q.token_expires_at < NOW() THEN 'expired'
|
|
WHEN q.token_expires_at < NOW() + INTERVAL '24 hours' THEN 'expiring_soon'
|
|
ELSE 'valid'
|
|
END as token_status,
|
|
q.created_at,
|
|
q.updated_at
|
|
FROM service_management.quotes q
|
|
LEFT JOIN workshop_core.customers c ON c.id = q.customer_id
|
|
WHERE q.status IN ('sent', 'pending')
|
|
AND q.signed_at IS NULL
|
|
ORDER BY q.created_at DESC;
|
|
|
|
COMMENT ON VIEW service_management.v_quotes_pending_signature IS 'Cotizaciones pendientes de firma del cliente';
|
|
|
|
-- Vista de historial de firmas
|
|
CREATE VIEW service_management.v_signature_history AS
|
|
SELECT
|
|
sa.id,
|
|
sa.tenant_id,
|
|
sa.document_type,
|
|
sa.document_number,
|
|
sa.signer_name,
|
|
sa.signer_email,
|
|
sa.action,
|
|
sa.created_at as signed_at,
|
|
sa.signer_ip,
|
|
sa.comments,
|
|
-- Info adicional del documento
|
|
CASE sa.document_type
|
|
WHEN 'quote' THEN (SELECT total FROM service_management.quotes WHERE id = sa.document_id)
|
|
ELSE NULL
|
|
END as document_total
|
|
FROM service_management.signature_audit sa
|
|
ORDER BY sa.created_at DESC;
|
|
|
|
COMMENT ON VIEW service_management.v_signature_history IS 'Historial de firmas electronicas';
|
|
|
|
-- ============================================
|
|
-- GRANTS
|
|
-- ============================================
|
|
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA service_management TO mecanicas_user;
|
|
GRANT SELECT ON service_management.signature_audit TO mecanicas_user;
|
|
GRANT INSERT ON service_management.signature_audit TO mecanicas_user;
|