-- ===========================================
-- 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;