460 lines
16 KiB
PL/PgSQL
460 lines
16 KiB
PL/PgSQL
-- ===========================================
|
|
-- MECANICAS DIESEL - Schema de Notificaciones
|
|
-- ===========================================
|
|
-- Sistema de tracking, mensajes, followers y actividades
|
|
-- Permite historial de cambios y notificaciones automaticas
|
|
|
|
-- ============================================
|
|
-- SCHEMA: notifications
|
|
-- ============================================
|
|
CREATE SCHEMA IF NOT EXISTS notifications;
|
|
COMMENT ON SCHEMA notifications IS 'Sistema de tracking, mensajes, followers y actividades (patron mail.thread)';
|
|
|
|
-- Grants
|
|
GRANT USAGE ON SCHEMA notifications TO mecanicas_user;
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA notifications TO mecanicas_user;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA notifications GRANT ALL ON TABLES TO mecanicas_user;
|
|
|
|
-- ============================================
|
|
-- GAP-01: Sistema de Tracking de Cambios
|
|
-- ============================================
|
|
|
|
-- Subtipos de mensaje (clasificacion)
|
|
CREATE TABLE notifications.message_subtypes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
code VARCHAR(50) NOT NULL UNIQUE,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
res_model VARCHAR(100), -- NULL = aplica a todos los modelos
|
|
is_internal BOOLEAN NOT NULL DEFAULT false,
|
|
is_default BOOLEAN NOT NULL DEFAULT false,
|
|
sequence INTEGER NOT NULL DEFAULT 10,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE notifications.message_subtypes IS 'Clasificacion de tipos de mensaje (creacion, edicion, nota, etc.)';
|
|
|
|
-- Seed de subtipos predeterminados
|
|
INSERT INTO notifications.message_subtypes (code, name, description, is_internal, is_default, sequence) VALUES
|
|
('mt_note', 'Nota', 'Nota interna', true, true, 1),
|
|
('mt_comment', 'Comentario', 'Comentario publico', false, true, 2),
|
|
('mt_tracking', 'Cambio de valor', 'Cambio en campo trackeado', true, false, 10),
|
|
('mt_creation', 'Creacion', 'Documento creado', false, false, 5),
|
|
('mt_status_change', 'Cambio de estado', 'Estado del documento modificado', false, false, 6),
|
|
('mt_assignment', 'Asignacion', 'Documento asignado a usuario', false, false, 7);
|
|
|
|
-- Mensajes (chatter/historial)
|
|
CREATE TABLE notifications.messages (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
-- Referencia al documento
|
|
res_model VARCHAR(100) NOT NULL, -- ej: 'service_management.service_orders'
|
|
res_id UUID NOT NULL, -- ID del documento
|
|
|
|
-- Tipo y subtipo
|
|
message_type VARCHAR(20) NOT NULL DEFAULT 'notification',
|
|
subtype_id UUID REFERENCES notifications.message_subtypes(id),
|
|
|
|
-- Autor
|
|
author_id UUID, -- Usuario que escribio el mensaje
|
|
author_name VARCHAR(256), -- Nombre del autor (desnormalizado)
|
|
|
|
-- Contenido
|
|
subject VARCHAR(500),
|
|
body TEXT,
|
|
|
|
-- Tracking de cambios (JSON array)
|
|
-- Formato: [{"field": "status", "old_value": "draft", "new_value": "confirmed", "field_label": "Estado"}]
|
|
tracking_values JSONB DEFAULT '[]'::jsonb,
|
|
|
|
-- Metadatos
|
|
is_internal BOOLEAN NOT NULL DEFAULT false,
|
|
parent_id UUID REFERENCES notifications.messages(id),
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT chk_message_type CHECK (message_type IN ('comment', 'notification', 'note', 'email', 'system'))
|
|
);
|
|
|
|
COMMENT ON TABLE notifications.messages IS 'Historial de mensajes y cambios en documentos (chatter)';
|
|
COMMENT ON COLUMN notifications.messages.res_model IS 'Nombre completo del modelo (schema.table)';
|
|
COMMENT ON COLUMN notifications.messages.res_id IS 'ID del registro referenciado';
|
|
COMMENT ON COLUMN notifications.messages.tracking_values IS 'Array JSON con cambios de campos trackeados';
|
|
|
|
-- Indices para messages
|
|
CREATE INDEX idx_messages_resource ON notifications.messages(res_model, res_id);
|
|
CREATE INDEX idx_messages_tenant ON notifications.messages(tenant_id);
|
|
CREATE INDEX idx_messages_created ON notifications.messages(created_at DESC);
|
|
CREATE INDEX idx_messages_author ON notifications.messages(author_id);
|
|
CREATE INDEX idx_messages_parent ON notifications.messages(parent_id) WHERE parent_id IS NOT NULL;
|
|
|
|
-- RLS para messages
|
|
SELECT create_tenant_rls_policies('notifications', 'messages');
|
|
|
|
-- ============================================
|
|
-- GAP-02: Sistema de Followers/Suscriptores
|
|
-- ============================================
|
|
|
|
-- Seguidores de documentos
|
|
CREATE TABLE notifications.followers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
-- Referencia al documento
|
|
res_model VARCHAR(100) NOT NULL,
|
|
res_id UUID NOT NULL,
|
|
|
|
-- Seguidor (puede ser usuario o partner/cliente)
|
|
partner_id UUID NOT NULL, -- ID del contacto/usuario
|
|
partner_type VARCHAR(20) NOT NULL DEFAULT 'user',
|
|
|
|
-- Metadatos
|
|
reason VARCHAR(100), -- Por que sigue (manual, asignacion, creador, etc.)
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT uq_follower UNIQUE(tenant_id, res_model, res_id, partner_id),
|
|
CONSTRAINT chk_partner_type CHECK (partner_type IN ('user', 'customer', 'supplier'))
|
|
);
|
|
|
|
COMMENT ON TABLE notifications.followers IS 'Suscriptores a documentos para notificaciones automaticas';
|
|
|
|
-- Subtipos a los que esta suscrito cada follower
|
|
CREATE TABLE notifications.follower_subtypes (
|
|
follower_id UUID NOT NULL REFERENCES notifications.followers(id) ON DELETE CASCADE,
|
|
subtype_id UUID NOT NULL REFERENCES notifications.message_subtypes(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (follower_id, subtype_id)
|
|
);
|
|
|
|
COMMENT ON TABLE notifications.follower_subtypes IS 'Tipos de mensaje a los que esta suscrito cada follower';
|
|
|
|
-- Indices para followers
|
|
CREATE INDEX idx_followers_resource ON notifications.followers(res_model, res_id);
|
|
CREATE INDEX idx_followers_partner ON notifications.followers(partner_id);
|
|
CREATE INDEX idx_followers_tenant ON notifications.followers(tenant_id);
|
|
|
|
-- RLS para followers
|
|
SELECT create_tenant_rls_policies('notifications', 'followers');
|
|
|
|
-- ============================================
|
|
-- GAP-03: Actividades Programadas
|
|
-- ============================================
|
|
|
|
-- Tipos de actividad
|
|
CREATE TABLE notifications.activity_types (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
code VARCHAR(50) NOT NULL UNIQUE,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
|
|
-- Configuracion
|
|
icon VARCHAR(50) DEFAULT 'fa-tasks',
|
|
color VARCHAR(20) DEFAULT 'primary',
|
|
default_days INTEGER DEFAULT 0, -- Dias por defecto para deadline
|
|
|
|
-- Restriccion por modelo (NULL = todos)
|
|
res_model VARCHAR(100),
|
|
|
|
-- Metadatos
|
|
sequence INTEGER NOT NULL DEFAULT 10,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE notifications.activity_types IS 'Tipos de actividad disponibles (llamar, reunion, tarea, etc.)';
|
|
|
|
-- Seed de tipos predeterminados para taller mecanico
|
|
INSERT INTO notifications.activity_types (code, name, description, icon, color, default_days, sequence) VALUES
|
|
('call', 'Llamar cliente', 'Llamada telefonica al cliente', 'fa-phone', 'info', 0, 1),
|
|
('meeting', 'Cita de entrega', 'Cita para entregar vehiculo', 'fa-calendar-check', 'success', 0, 2),
|
|
('todo', 'Tarea pendiente', 'Tarea generica por completar', 'fa-tasks', 'warning', 1, 3),
|
|
('reminder', 'Recordatorio mantenimiento', 'Recordar cliente sobre proximo mantenimiento', 'fa-bell', 'secondary', 30, 4),
|
|
('followup', 'Seguimiento cotizacion', 'Dar seguimiento a cotizacion enviada', 'fa-envelope', 'primary', 3, 5),
|
|
('approval', 'Pendiente aprobacion', 'Esperar aprobacion de cliente o supervisor', 'fa-check-circle', 'danger', 1, 6),
|
|
('parts_arrival', 'Llegada de refacciones', 'Refacciones pendientes de llegar', 'fa-truck', 'info', 2, 7),
|
|
('quality_check', 'Revision de calidad', 'Inspeccion de trabajo terminado', 'fa-search', 'warning', 0, 8);
|
|
|
|
-- Actividades programadas
|
|
CREATE TABLE notifications.activities (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
-- Referencia al documento
|
|
res_model VARCHAR(100) NOT NULL,
|
|
res_id UUID NOT NULL,
|
|
|
|
-- Tipo y asignacion
|
|
activity_type_id UUID NOT NULL REFERENCES notifications.activity_types(id),
|
|
user_id UUID NOT NULL, -- Usuario asignado
|
|
|
|
-- Programacion
|
|
date_deadline DATE NOT NULL,
|
|
|
|
-- Contenido
|
|
summary VARCHAR(500),
|
|
note TEXT,
|
|
|
|
-- Estado
|
|
state VARCHAR(20) NOT NULL DEFAULT 'planned',
|
|
date_done TIMESTAMPTZ,
|
|
feedback TEXT, -- Comentario al completar
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID NOT NULL,
|
|
|
|
CONSTRAINT chk_activity_state CHECK (state IN ('planned', 'today', 'overdue', 'done', 'canceled'))
|
|
);
|
|
|
|
COMMENT ON TABLE notifications.activities IS 'Actividades y recordatorios programados asociados a documentos';
|
|
COMMENT ON COLUMN notifications.activities.state IS 'Estado: planned (futuro), today (hoy), overdue (vencida), done (completada), canceled';
|
|
|
|
-- Indices para activities
|
|
CREATE INDEX idx_activities_resource ON notifications.activities(res_model, res_id);
|
|
CREATE INDEX idx_activities_user ON notifications.activities(user_id);
|
|
CREATE INDEX idx_activities_deadline ON notifications.activities(date_deadline);
|
|
CREATE INDEX idx_activities_tenant ON notifications.activities(tenant_id);
|
|
CREATE INDEX idx_activities_pending ON notifications.activities(user_id, date_deadline)
|
|
WHERE state NOT IN ('done', 'canceled');
|
|
|
|
-- RLS para activities
|
|
SELECT create_tenant_rls_policies('notifications', 'activities');
|
|
|
|
-- ============================================
|
|
-- FUNCIONES AUXILIARES
|
|
-- ============================================
|
|
|
|
-- Funcion para actualizar estado de actividades (planned -> today -> overdue)
|
|
CREATE OR REPLACE FUNCTION notifications.update_activity_states()
|
|
RETURNS INTEGER AS $$
|
|
DECLARE
|
|
v_updated INTEGER;
|
|
BEGIN
|
|
-- Actualizar a 'today' las que vencen hoy
|
|
UPDATE notifications.activities
|
|
SET state = 'today'
|
|
WHERE state = 'planned'
|
|
AND date_deadline = CURRENT_DATE;
|
|
|
|
GET DIAGNOSTICS v_updated = ROW_COUNT;
|
|
|
|
-- Actualizar a 'overdue' las vencidas
|
|
UPDATE notifications.activities
|
|
SET state = 'overdue'
|
|
WHERE state IN ('planned', 'today')
|
|
AND date_deadline < CURRENT_DATE;
|
|
|
|
RETURN v_updated;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION notifications.update_activity_states() IS 'Actualiza estados de actividades segun fecha (ejecutar diariamente)';
|
|
|
|
-- Funcion para agregar follower automaticamente
|
|
CREATE OR REPLACE FUNCTION notifications.add_follower(
|
|
p_tenant_id UUID,
|
|
p_res_model VARCHAR(100),
|
|
p_res_id UUID,
|
|
p_partner_id UUID,
|
|
p_partner_type VARCHAR(20) DEFAULT 'user',
|
|
p_reason VARCHAR(100) DEFAULT 'manual'
|
|
)
|
|
RETURNS UUID AS $$
|
|
DECLARE
|
|
v_follower_id UUID;
|
|
BEGIN
|
|
INSERT INTO notifications.followers (tenant_id, res_model, res_id, partner_id, partner_type, reason)
|
|
VALUES (p_tenant_id, p_res_model, p_res_id, p_partner_id, p_partner_type, p_reason)
|
|
ON CONFLICT (tenant_id, res_model, res_id, partner_id) DO NOTHING
|
|
RETURNING id INTO v_follower_id;
|
|
|
|
-- Si ya existia, obtener el ID
|
|
IF v_follower_id IS NULL THEN
|
|
SELECT id INTO v_follower_id
|
|
FROM notifications.followers
|
|
WHERE tenant_id = p_tenant_id
|
|
AND res_model = p_res_model
|
|
AND res_id = p_res_id
|
|
AND partner_id = p_partner_id;
|
|
END IF;
|
|
|
|
RETURN v_follower_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION notifications.add_follower IS 'Agrega un follower a un documento (idempotente)';
|
|
|
|
-- Funcion para registrar mensaje de tracking
|
|
CREATE OR REPLACE FUNCTION notifications.log_tracking_message(
|
|
p_tenant_id UUID,
|
|
p_res_model VARCHAR(100),
|
|
p_res_id UUID,
|
|
p_author_id UUID,
|
|
p_tracking_values JSONB,
|
|
p_body TEXT DEFAULT NULL
|
|
)
|
|
RETURNS UUID AS $$
|
|
DECLARE
|
|
v_message_id UUID;
|
|
v_subtype_id UUID;
|
|
BEGIN
|
|
-- Obtener subtipo de tracking
|
|
SELECT id INTO v_subtype_id
|
|
FROM notifications.message_subtypes
|
|
WHERE code = 'mt_tracking';
|
|
|
|
INSERT INTO notifications.messages (
|
|
tenant_id, res_model, res_id, message_type,
|
|
subtype_id, author_id, body, tracking_values, is_internal
|
|
)
|
|
VALUES (
|
|
p_tenant_id, p_res_model, p_res_id, 'notification',
|
|
v_subtype_id, p_author_id, p_body, p_tracking_values, true
|
|
)
|
|
RETURNING id INTO v_message_id;
|
|
|
|
RETURN v_message_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION notifications.log_tracking_message IS 'Registra un mensaje de tracking de cambios';
|
|
|
|
-- Funcion para crear actividad
|
|
CREATE OR REPLACE FUNCTION notifications.create_activity(
|
|
p_tenant_id UUID,
|
|
p_res_model VARCHAR(100),
|
|
p_res_id UUID,
|
|
p_activity_type_code VARCHAR(50),
|
|
p_user_id UUID,
|
|
p_date_deadline DATE DEFAULT NULL,
|
|
p_summary VARCHAR(500) DEFAULT NULL,
|
|
p_note TEXT DEFAULT NULL,
|
|
p_created_by UUID DEFAULT NULL
|
|
)
|
|
RETURNS UUID AS $$
|
|
DECLARE
|
|
v_activity_id UUID;
|
|
v_activity_type_id UUID;
|
|
v_default_days INTEGER;
|
|
BEGIN
|
|
-- Obtener tipo de actividad
|
|
SELECT id, default_days INTO v_activity_type_id, v_default_days
|
|
FROM notifications.activity_types
|
|
WHERE code = p_activity_type_code AND is_active = true;
|
|
|
|
IF v_activity_type_id IS NULL THEN
|
|
RAISE EXCEPTION 'Activity type % not found', p_activity_type_code;
|
|
END IF;
|
|
|
|
-- Calcular deadline si no se proporciono
|
|
IF p_date_deadline IS NULL THEN
|
|
p_date_deadline := CURRENT_DATE + v_default_days;
|
|
END IF;
|
|
|
|
-- Determinar estado inicial
|
|
INSERT INTO notifications.activities (
|
|
tenant_id, res_model, res_id, activity_type_id, user_id,
|
|
date_deadline, summary, note, state, created_by
|
|
)
|
|
VALUES (
|
|
p_tenant_id, p_res_model, p_res_id, v_activity_type_id, p_user_id,
|
|
p_date_deadline, p_summary, p_note,
|
|
CASE
|
|
WHEN p_date_deadline < CURRENT_DATE THEN 'overdue'
|
|
WHEN p_date_deadline = CURRENT_DATE THEN 'today'
|
|
ELSE 'planned'
|
|
END,
|
|
COALESCE(p_created_by, get_current_user_id())
|
|
)
|
|
RETURNING id INTO v_activity_id;
|
|
|
|
RETURN v_activity_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION notifications.create_activity IS 'Crea una nueva actividad programada';
|
|
|
|
-- Funcion para completar actividad
|
|
CREATE OR REPLACE FUNCTION notifications.complete_activity(
|
|
p_activity_id UUID,
|
|
p_feedback TEXT DEFAULT NULL
|
|
)
|
|
RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
UPDATE notifications.activities
|
|
SET state = 'done',
|
|
date_done = NOW(),
|
|
feedback = p_feedback
|
|
WHERE id = p_activity_id
|
|
AND state NOT IN ('done', 'canceled');
|
|
|
|
RETURN FOUND;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION notifications.complete_activity IS 'Marca una actividad como completada';
|
|
|
|
-- ============================================
|
|
-- VISTAS UTILES
|
|
-- ============================================
|
|
|
|
-- Vista de actividades pendientes por usuario
|
|
CREATE VIEW notifications.v_pending_activities AS
|
|
SELECT
|
|
a.id,
|
|
a.tenant_id,
|
|
a.res_model,
|
|
a.res_id,
|
|
at.code as activity_type_code,
|
|
at.name as activity_type_name,
|
|
at.icon,
|
|
at.color,
|
|
a.user_id,
|
|
a.date_deadline,
|
|
a.summary,
|
|
a.note,
|
|
a.state,
|
|
CASE
|
|
WHEN a.date_deadline < CURRENT_DATE THEN a.date_deadline - CURRENT_DATE
|
|
ELSE 0
|
|
END as days_overdue,
|
|
a.created_at
|
|
FROM notifications.activities a
|
|
JOIN notifications.activity_types at ON at.id = a.activity_type_id
|
|
WHERE a.state NOT IN ('done', 'canceled')
|
|
ORDER BY a.date_deadline ASC;
|
|
|
|
COMMENT ON VIEW notifications.v_pending_activities IS 'Actividades pendientes con informacion de tipo';
|
|
|
|
-- Vista de historial de mensajes por documento
|
|
CREATE VIEW notifications.v_message_history AS
|
|
SELECT
|
|
m.id,
|
|
m.tenant_id,
|
|
m.res_model,
|
|
m.res_id,
|
|
m.message_type,
|
|
ms.code as subtype_code,
|
|
ms.name as subtype_name,
|
|
m.author_id,
|
|
m.author_name,
|
|
m.subject,
|
|
m.body,
|
|
m.tracking_values,
|
|
m.is_internal,
|
|
m.parent_id,
|
|
m.created_at
|
|
FROM notifications.messages m
|
|
LEFT JOIN notifications.message_subtypes ms ON ms.id = m.subtype_id
|
|
ORDER BY m.created_at DESC;
|
|
|
|
COMMENT ON VIEW notifications.v_message_history IS 'Historial de mensajes formateado con subtipos';
|
|
|
|
-- ============================================
|
|
-- GRANTS ADICIONALES
|
|
-- ============================================
|
|
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA notifications TO mecanicas_user;
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA notifications TO mecanicas_user;
|