-- ============================================================================
-- MIGRACIÓN: Triggers de sincronización bidireccional
-- Mantiene knowledge_items y conocimientos_empleado_digital sincronizadas
-- Fecha: 2026-01-25
-- ============================================================================

-- 1. Función: knowledge_items → conocimientos_empleado_digital
CREATE OR REPLACE FUNCTION sync_knowledge_to_conocimientos()
RETURNS TRIGGER AS $$
BEGIN
    -- Prevenir loops infinitos
    IF current_setting('app.sync_in_progress', true) = 'true' THEN
        RETURN NULL;
    END IF;

    PERFORM set_config('app.sync_in_progress', 'true', true);

    IF TG_OP = 'INSERT' THEN
        INSERT INTO conocimientos_empleado_digital (
            id, business_id, tipo_conocimiento, titulo, contenido, etiquetas,
            idioma, vector_busqueda, version, created_at, creado_por_login_id,
            updated_at, actualizado_por_login_id, soft_delete, soft_delete_date,
            clave_unica, sucursal, categorias
        ) VALUES (
            NEW.id, NEW.business_id, NEW.type, NEW.title, NEW.body, NEW.tags,
            NEW.language, NEW.search_vector, NEW.version, NEW.created_at, NEW.created_by_login_id,
            NEW.updated_at, NEW.updated_by_login_id, NEW.soft_delete, NEW.soft_delete_date,
            NEW.unique_key, NEW.sucursal, NEW.categorias
        )
        ON CONFLICT (id) DO UPDATE SET
            tipo_conocimiento = EXCLUDED.tipo_conocimiento,
            titulo = EXCLUDED.titulo,
            contenido = EXCLUDED.contenido,
            etiquetas = EXCLUDED.etiquetas,
            idioma = EXCLUDED.idioma,
            vector_busqueda = EXCLUDED.vector_busqueda,
            version = EXCLUDED.version,
            updated_at = EXCLUDED.updated_at,
            actualizado_por_login_id = EXCLUDED.actualizado_por_login_id,
            soft_delete = EXCLUDED.soft_delete,
            soft_delete_date = EXCLUDED.soft_delete_date,
            sucursal = EXCLUDED.sucursal,
            categorias = EXCLUDED.categorias;
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE conocimientos_empleado_digital SET
            tipo_conocimiento = NEW.type,
            titulo = NEW.title,
            contenido = NEW.body,
            etiquetas = NEW.tags,
            idioma = NEW.language,
            vector_busqueda = NEW.search_vector,
            version = NEW.version,
            updated_at = NEW.updated_at,
            actualizado_por_login_id = NEW.updated_by_login_id,
            soft_delete = NEW.soft_delete,
            soft_delete_date = NEW.soft_delete_date,
            sucursal = NEW.sucursal,
            categorias = NEW.categorias
        WHERE id = NEW.id;
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM conocimientos_empleado_digital WHERE id = OLD.id;
        RETURN OLD;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 2. Función: conocimientos_empleado_digital → knowledge_items
-- NOTA: search_vector es columna generada en knowledge_items, no se puede insertar/actualizar
CREATE OR REPLACE FUNCTION sync_conocimientos_to_knowledge()
RETURNS TRIGGER AS $$
BEGIN
    -- Prevenir loops infinitos
    IF current_setting('app.sync_in_progress', true) = 'true' THEN
        RETURN NULL;
    END IF;

    PERFORM set_config('app.sync_in_progress', 'true', true);

    IF TG_OP = 'INSERT' THEN
        INSERT INTO knowledge_items (
            id, business_id, type, title, body, tags,
            language, version, created_at, created_by_login_id,
            updated_at, updated_by_login_id, soft_delete, soft_delete_date,
            unique_key, sucursal, categorias
        ) VALUES (
            NEW.id, NEW.business_id, NEW.tipo_conocimiento, NEW.titulo, NEW.contenido, NEW.etiquetas,
            NEW.idioma, NEW.version, NEW.created_at, NEW.creado_por_login_id,
            NEW.updated_at, NEW.actualizado_por_login_id, NEW.soft_delete, NEW.soft_delete_date,
            NEW.clave_unica, NEW.sucursal, NEW.categorias
        )
        ON CONFLICT (id) DO UPDATE SET
            type = EXCLUDED.type,
            title = EXCLUDED.title,
            body = EXCLUDED.body,
            tags = EXCLUDED.tags,
            language = EXCLUDED.language,
            version = EXCLUDED.version,
            updated_at = EXCLUDED.updated_at,
            updated_by_login_id = EXCLUDED.updated_by_login_id,
            soft_delete = EXCLUDED.soft_delete,
            soft_delete_date = EXCLUDED.soft_delete_date,
            sucursal = EXCLUDED.sucursal,
            categorias = EXCLUDED.categorias;
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE knowledge_items SET
            type = NEW.tipo_conocimiento,
            title = NEW.titulo,
            body = NEW.contenido,
            tags = NEW.etiquetas,
            language = NEW.idioma,
            version = NEW.version,
            updated_at = NEW.updated_at,
            updated_by_login_id = NEW.actualizado_por_login_id,
            soft_delete = NEW.soft_delete,
            soft_delete_date = NEW.soft_delete_date,
            sucursal = NEW.sucursal,
            categorias = NEW.categorias
        WHERE id = NEW.id;
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM knowledge_items WHERE id = OLD.id;
        RETURN OLD;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 3. Crear trigger en knowledge_items
DROP TRIGGER IF EXISTS sync_to_conocimientos ON knowledge_items;
CREATE TRIGGER sync_to_conocimientos
    AFTER INSERT OR UPDATE OR DELETE ON knowledge_items
    FOR EACH ROW
    EXECUTE FUNCTION sync_knowledge_to_conocimientos();

-- 4. Crear trigger en conocimientos_empleado_digital
DROP TRIGGER IF EXISTS sync_to_knowledge ON conocimientos_empleado_digital;
CREATE TRIGGER sync_to_knowledge
    AFTER INSERT OR UPDATE OR DELETE ON conocimientos_empleado_digital
    FOR EACH ROW
    EXECUTE FUNCTION sync_conocimientos_to_knowledge();
