
CREATE SCHEMA IF NOT EXISTS platform;
CREATE SCHEMA IF NOT EXISTS ai;
CREATE SCHEMA IF NOT EXISTS crm;

CREATE OR REPLACE FUNCTION platform.sync_estadisticas_mirror()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_target_schema text;
BEGIN
    IF current_setting('app.sync_platform_estadisticas_mirror', true) = 'true' THEN
        RETURN COALESCE(NEW, OLD);
    END IF;

    IF TG_TABLE_SCHEMA = 'platform' THEN
        v_target_schema := 'public';
    ELSIF TG_TABLE_SCHEMA = 'public' THEN
        v_target_schema := 'platform';
    ELSE
        RETURN COALESCE(NEW, OLD);
    END IF;

    PERFORM set_config('app.sync_platform_estadisticas_mirror', 'true', true);
    BEGIN
        IF TG_OP = 'DELETE' THEN
            EXECUTE format(
                'DELETE FROM %I.estadisticas WHERE id_estadistica = $1',
                v_target_schema
            )
            USING OLD.id_estadistica;

            PERFORM set_config('app.sync_platform_estadisticas_mirror', 'false', true);
            RETURN OLD;
        END IF;

        EXECUTE format(
            'INSERT INTO %I.estadisticas (
                id_estadistica, business_id, sucursal, soft_delete, soft_delete_date,
                mensajes_total, rfc3339_mensajes, inicio_conversacion_hora, fin_conversacion_hora,
                timeout_si_o_no, id_cliente, analisis_id
             ) VALUES (
                $1, $2, $3, $4, $5,
                $6, $7, $8, $9,
                $10, $11, $12
             )
             ON CONFLICT (id_estadistica) DO UPDATE SET
                business_id = EXCLUDED.business_id,
                sucursal = EXCLUDED.sucursal,
                soft_delete = EXCLUDED.soft_delete,
                soft_delete_date = EXCLUDED.soft_delete_date,
                mensajes_total = EXCLUDED.mensajes_total,
                rfc3339_mensajes = EXCLUDED.rfc3339_mensajes,
                inicio_conversacion_hora = EXCLUDED.inicio_conversacion_hora,
                fin_conversacion_hora = EXCLUDED.fin_conversacion_hora,
                timeout_si_o_no = EXCLUDED.timeout_si_o_no,
                id_cliente = EXCLUDED.id_cliente,
                analisis_id = EXCLUDED.analisis_id',
            v_target_schema
        )
        USING
            NEW.id_estadistica,
            NEW.business_id,
            NEW.sucursal,
            NEW.soft_delete,
            NEW.soft_delete_date,
            NEW.mensajes_total,
            NEW.rfc3339_mensajes,
            NEW.inicio_conversacion_hora,
            NEW.fin_conversacion_hora,
            NEW.timeout_si_o_no,
            NEW.id_cliente,
            NEW.analisis_id;

        PERFORM ops.sync_set_sequence_if_needed(
            format('%I.estadisticas', v_target_schema),
            'id_estadistica',
            NEW.id_estadistica
        );

        PERFORM set_config('app.sync_platform_estadisticas_mirror', 'false', true);
        RETURN NEW;
    EXCEPTION
        WHEN OTHERS THEN
            PERFORM set_config('app.sync_platform_estadisticas_mirror', 'false', true);
            RAISE;
    END;
END;
$$;

CREATE OR REPLACE FUNCTION ai.sync_analisis_conversacion_mirror()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_target_schema text;
BEGIN
    IF current_setting('app.sync_ai_analisis_conversacion_mirror', true) = 'true' THEN
        RETURN COALESCE(NEW, OLD);
    END IF;

    IF TG_TABLE_SCHEMA = 'ai' THEN
        v_target_schema := 'public';
    ELSIF TG_TABLE_SCHEMA = 'public' THEN
        v_target_schema := 'ai';
    ELSE
        RETURN COALESCE(NEW, OLD);
    END IF;

    PERFORM set_config('app.sync_ai_analisis_conversacion_mirror', 'true', true);
    BEGIN
        IF TG_OP = 'DELETE' THEN
            EXECUTE format(
                'DELETE FROM %I.analisis_conversacion WHERE id_analisis = $1',
                v_target_schema
            )
            USING OLD.id_analisis;

            PERFORM set_config('app.sync_ai_analisis_conversacion_mirror', 'false', true);
            RETURN OLD;
        END IF;

        EXECUTE format(
            'INSERT INTO %I.analisis_conversacion (
                id_analisis, id_empleado, id_cliente, etiqueta, escala_tipo, valor,
                conversation_started_at, analizado_en, created_at, business_id, sucursal,
                revisado_en, revisado_o_no, justificacion, concepto_calificado
             ) VALUES (
                $1, $2, $3, $4, $5, $6,
                $7, $8, $9, $10, $11,
                $12, $13, $14, $15
             )
             ON CONFLICT (id_analisis) DO UPDATE SET
                id_empleado = EXCLUDED.id_empleado,
                id_cliente = EXCLUDED.id_cliente,
                etiqueta = EXCLUDED.etiqueta,
                escala_tipo = EXCLUDED.escala_tipo,
                valor = EXCLUDED.valor,
                conversation_started_at = EXCLUDED.conversation_started_at,
                analizado_en = EXCLUDED.analizado_en,
                created_at = EXCLUDED.created_at,
                business_id = EXCLUDED.business_id,
                sucursal = EXCLUDED.sucursal,
                revisado_en = EXCLUDED.revisado_en,
                revisado_o_no = EXCLUDED.revisado_o_no,
                justificacion = EXCLUDED.justificacion,
                concepto_calificado = EXCLUDED.concepto_calificado',
            v_target_schema
        )
        USING
            NEW.id_analisis,
            NEW.id_empleado,
            NEW.id_cliente,
            NEW.etiqueta,
            NEW.escala_tipo,
            NEW.valor,
            NEW.conversation_started_at,
            NEW.analizado_en,
            NEW.created_at,
            NEW.business_id,
            NEW.sucursal,
            NEW.revisado_en,
            NEW.revisado_o_no,
            NEW.justificacion,
            NEW.concepto_calificado;

        PERFORM ops.sync_set_sequence_if_needed(
            format('%I.analisis_conversacion', v_target_schema),
            'id_analisis',
            NEW.id_analisis
        );

        PERFORM set_config('app.sync_ai_analisis_conversacion_mirror', 'false', true);
        RETURN NEW;
    EXCEPTION
        WHEN OTHERS THEN
            PERFORM set_config('app.sync_ai_analisis_conversacion_mirror', 'false', true);
            RAISE;
    END;
END;
$$;

CREATE OR REPLACE FUNCTION ai.sync_estadisticas_costos_ia_mirror()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_target_schema text;
BEGIN
    IF current_setting('app.sync_ai_estadisticas_costos_ia_mirror', true) = 'true' THEN
        RETURN COALESCE(NEW, OLD);
    END IF;

    IF TG_TABLE_SCHEMA = 'ai' THEN
        v_target_schema := 'public';
    ELSIF TG_TABLE_SCHEMA = 'public' THEN
        v_target_schema := 'ai';
    ELSE
        RETURN COALESCE(NEW, OLD);
    END IF;

    PERFORM set_config('app.sync_ai_estadisticas_costos_ia_mirror', 'true', true);
    BEGIN
        IF TG_OP = 'DELETE' THEN
            EXECUTE format(
                'DELETE FROM %I.estadisticas_costos_ia WHERE id = $1',
                v_target_schema
            )
            USING OLD.id;

            PERFORM set_config('app.sync_ai_estadisticas_costos_ia_mirror', 'false', true);
            RETURN OLD;
        END IF;

        EXECUTE format(
            'INSERT INTO %I.estadisticas_costos_ia (
                id, origen, identificador_usuario, session_id, client_id,
                desglose_costos, costo_total_usd, costo_total_mxn, created_at, business_id, sucursal
             ) VALUES (
                $1, $2, $3, $4, $5,
                $6, $7, $8, $9, $10, $11
             )
             ON CONFLICT (id) DO UPDATE SET
                origen = EXCLUDED.origen,
                identificador_usuario = EXCLUDED.identificador_usuario,
                session_id = EXCLUDED.session_id,
                client_id = EXCLUDED.client_id,
                desglose_costos = EXCLUDED.desglose_costos,
                costo_total_usd = EXCLUDED.costo_total_usd,
                costo_total_mxn = EXCLUDED.costo_total_mxn,
                created_at = EXCLUDED.created_at,
                business_id = EXCLUDED.business_id,
                sucursal = EXCLUDED.sucursal',
            v_target_schema
        )
        USING
            NEW.id,
            NEW.origen,
            NEW.identificador_usuario,
            NEW.session_id,
            NEW.client_id,
            NEW.desglose_costos,
            NEW.costo_total_usd,
            NEW.costo_total_mxn,
            NEW.created_at,
            NEW.business_id,
            NEW.sucursal;

        PERFORM ops.sync_set_sequence_if_needed(
            format('%I.estadisticas_costos_ia', v_target_schema),
            'id',
            NEW.id
        );

        PERFORM set_config('app.sync_ai_estadisticas_costos_ia_mirror', 'false', true);
        RETURN NEW;
    EXCEPTION
        WHEN OTHERS THEN
            PERFORM set_config('app.sync_ai_estadisticas_costos_ia_mirror', 'false', true);
            RAISE;
    END;
END;
$$;

CREATE OR REPLACE FUNCTION crm.sync_reportes_mirror()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_target_schema text;
BEGIN
    IF current_setting('app.sync_crm_reportes_mirror', true) = 'true' THEN
        RETURN COALESCE(NEW, OLD);
    END IF;

    IF TG_TABLE_SCHEMA = 'crm' THEN
        v_target_schema := 'public';
    ELSIF TG_TABLE_SCHEMA = 'public' THEN
        v_target_schema := 'crm';
    ELSE
        RETURN COALESCE(NEW, OLD);
    END IF;

    PERFORM set_config('app.sync_crm_reportes_mirror', 'true', true);
    BEGIN
        IF TG_OP = 'DELETE' THEN
            EXECUTE format(
                'DELETE FROM %I.reportes WHERE id_reporte = $1',
                v_target_schema
            )
            USING OLD.id_reporte;

            PERFORM set_config('app.sync_crm_reportes_mirror', 'false', true);
            RETURN OLD;
        END IF;

        EXECUTE format(
            'INSERT INTO %I.reportes (
                id_reporte, descripcion, id_cliente, fecha_reporte, business_id,
                user_name, soft_delete, texto_notas_internas, user_name_notas_internas,
                rfc3339_notas_internas, phone, tipo, soft_delete_date, progreso,
                id_login, id_login_notas_internas, sucursal
             ) OVERRIDING SYSTEM VALUE VALUES (
                $1, $2, $3, $4, $5,
                $6, $7, $8, $9,
                $10, $11, $12, $13, $14,
                $15, $16, $17
             )
             ON CONFLICT (id_reporte) DO UPDATE SET
                descripcion = EXCLUDED.descripcion,
                id_cliente = EXCLUDED.id_cliente,
                fecha_reporte = EXCLUDED.fecha_reporte,
                business_id = EXCLUDED.business_id,
                user_name = EXCLUDED.user_name,
                soft_delete = EXCLUDED.soft_delete,
                texto_notas_internas = EXCLUDED.texto_notas_internas,
                user_name_notas_internas = EXCLUDED.user_name_notas_internas,
                rfc3339_notas_internas = EXCLUDED.rfc3339_notas_internas,
                phone = EXCLUDED.phone,
                tipo = EXCLUDED.tipo,
                soft_delete_date = EXCLUDED.soft_delete_date,
                progreso = EXCLUDED.progreso,
                id_login = EXCLUDED.id_login,
                id_login_notas_internas = EXCLUDED.id_login_notas_internas,
                sucursal = EXCLUDED.sucursal',
            v_target_schema
        )
        USING
            NEW.id_reporte,
            NEW.descripcion,
            NEW.id_cliente,
            NEW.fecha_reporte,
            NEW.business_id,
            NEW.user_name,
            NEW.soft_delete,
            NEW.texto_notas_internas,
            NEW.user_name_notas_internas,
            NEW.rfc3339_notas_internas,
            NEW.phone,
            NEW.tipo,
            NEW.soft_delete_date,
            NEW.progreso,
            NEW.id_login,
            NEW.id_login_notas_internas,
            NEW.sucursal;

        PERFORM ops.sync_set_sequence_if_needed(
            format('%I.reportes', v_target_schema),
            'id_reporte',
            NEW.id_reporte
        );

        PERFORM set_config('app.sync_crm_reportes_mirror', 'false', true);
        RETURN NEW;
    EXCEPTION
        WHEN OTHERS THEN
            PERFORM set_config('app.sync_crm_reportes_mirror', 'false', true);
            RAISE;
    END;
END;
$$;

DROP TRIGGER IF EXISTS trg_sync_mirror_platform_estadisticas ON public.estadisticas;
CREATE TRIGGER trg_sync_mirror_platform_estadisticas
    AFTER INSERT OR UPDATE OR DELETE ON public.estadisticas
    FOR EACH ROW
    EXECUTE FUNCTION platform.sync_estadisticas_mirror();

DROP TRIGGER IF EXISTS trg_sync_mirror_platform_estadisticas ON platform.estadisticas;
CREATE TRIGGER trg_sync_mirror_platform_estadisticas
    AFTER INSERT OR UPDATE OR DELETE ON platform.estadisticas
    FOR EACH ROW
    EXECUTE FUNCTION platform.sync_estadisticas_mirror();

DROP TRIGGER IF EXISTS trg_sync_mirror_ai_analisis_conversacion ON public.analisis_conversacion;
CREATE TRIGGER trg_sync_mirror_ai_analisis_conversacion
    AFTER INSERT OR UPDATE OR DELETE ON public.analisis_conversacion
    FOR EACH ROW
    EXECUTE FUNCTION ai.sync_analisis_conversacion_mirror();

DROP TRIGGER IF EXISTS trg_sync_mirror_ai_analisis_conversacion ON ai.analisis_conversacion;
CREATE TRIGGER trg_sync_mirror_ai_analisis_conversacion
    AFTER INSERT OR UPDATE OR DELETE ON ai.analisis_conversacion
    FOR EACH ROW
    EXECUTE FUNCTION ai.sync_analisis_conversacion_mirror();

DROP TRIGGER IF EXISTS trg_sync_mirror_ai_estadisticas_costos_ia ON public.estadisticas_costos_ia;
CREATE TRIGGER trg_sync_mirror_ai_estadisticas_costos_ia
    AFTER INSERT OR UPDATE OR DELETE ON public.estadisticas_costos_ia
    FOR EACH ROW
    EXECUTE FUNCTION ai.sync_estadisticas_costos_ia_mirror();

DROP TRIGGER IF EXISTS trg_sync_mirror_ai_estadisticas_costos_ia ON ai.estadisticas_costos_ia;
CREATE TRIGGER trg_sync_mirror_ai_estadisticas_costos_ia
    AFTER INSERT OR UPDATE OR DELETE ON ai.estadisticas_costos_ia
    FOR EACH ROW
    EXECUTE FUNCTION ai.sync_estadisticas_costos_ia_mirror();

DROP TRIGGER IF EXISTS trg_sync_mirror_crm_reportes ON public.reportes;
CREATE TRIGGER trg_sync_mirror_crm_reportes
    AFTER INSERT OR UPDATE OR DELETE ON public.reportes
    FOR EACH ROW
    EXECUTE FUNCTION crm.sync_reportes_mirror();

DROP TRIGGER IF EXISTS trg_sync_mirror_crm_reportes ON crm.reportes;
CREATE TRIGGER trg_sync_mirror_crm_reportes
    AFTER INSERT OR UPDATE OR DELETE ON crm.reportes
    FOR EACH ROW
    EXECUTE FUNCTION crm.sync_reportes_mirror();

INSERT INTO public.estadisticas (
    id_estadistica, business_id, sucursal, soft_delete, soft_delete_date,
    mensajes_total, rfc3339_mensajes, inicio_conversacion_hora, fin_conversacion_hora,
    timeout_si_o_no, id_cliente, analisis_id
)
SELECT
    id_estadistica, business_id, sucursal, soft_delete, soft_delete_date,
    mensajes_total, rfc3339_mensajes, inicio_conversacion_hora, fin_conversacion_hora,
    timeout_si_o_no, id_cliente, analisis_id
FROM platform.estadisticas
ON CONFLICT (id_estadistica) DO UPDATE SET
    business_id = EXCLUDED.business_id,
    sucursal = EXCLUDED.sucursal,
    soft_delete = EXCLUDED.soft_delete,
    soft_delete_date = EXCLUDED.soft_delete_date,
    mensajes_total = EXCLUDED.mensajes_total,
    rfc3339_mensajes = EXCLUDED.rfc3339_mensajes,
    inicio_conversacion_hora = EXCLUDED.inicio_conversacion_hora,
    fin_conversacion_hora = EXCLUDED.fin_conversacion_hora,
    timeout_si_o_no = EXCLUDED.timeout_si_o_no,
    id_cliente = EXCLUDED.id_cliente,
    analisis_id = EXCLUDED.analisis_id;

INSERT INTO platform.estadisticas (
    id_estadistica, business_id, sucursal, soft_delete, soft_delete_date,
    mensajes_total, rfc3339_mensajes, inicio_conversacion_hora, fin_conversacion_hora,
    timeout_si_o_no, id_cliente, analisis_id
)
SELECT
    id_estadistica, business_id, sucursal, soft_delete, soft_delete_date,
    mensajes_total, rfc3339_mensajes, inicio_conversacion_hora, fin_conversacion_hora,
    timeout_si_o_no, id_cliente, analisis_id
FROM public.estadisticas
ON CONFLICT (id_estadistica) DO UPDATE SET
    business_id = EXCLUDED.business_id,
    sucursal = EXCLUDED.sucursal,
    soft_delete = EXCLUDED.soft_delete,
    soft_delete_date = EXCLUDED.soft_delete_date,
    mensajes_total = EXCLUDED.mensajes_total,
    rfc3339_mensajes = EXCLUDED.rfc3339_mensajes,
    inicio_conversacion_hora = EXCLUDED.inicio_conversacion_hora,
    fin_conversacion_hora = EXCLUDED.fin_conversacion_hora,
    timeout_si_o_no = EXCLUDED.timeout_si_o_no,
    id_cliente = EXCLUDED.id_cliente,
    analisis_id = EXCLUDED.analisis_id;

INSERT INTO public.analisis_conversacion (
    id_analisis, id_empleado, id_cliente, etiqueta, escala_tipo, valor,
    conversation_started_at, analizado_en, created_at, business_id, sucursal,
    revisado_en, revisado_o_no, justificacion, concepto_calificado
)
SELECT
    id_analisis, id_empleado, id_cliente, etiqueta, escala_tipo, valor,
    conversation_started_at, analizado_en, created_at, business_id, sucursal,
    revisado_en, revisado_o_no, justificacion, concepto_calificado
FROM ai.analisis_conversacion
ON CONFLICT (id_analisis) DO UPDATE SET
    id_empleado = EXCLUDED.id_empleado,
    id_cliente = EXCLUDED.id_cliente,
    etiqueta = EXCLUDED.etiqueta,
    escala_tipo = EXCLUDED.escala_tipo,
    valor = EXCLUDED.valor,
    conversation_started_at = EXCLUDED.conversation_started_at,
    analizado_en = EXCLUDED.analizado_en,
    created_at = EXCLUDED.created_at,
    business_id = EXCLUDED.business_id,
    sucursal = EXCLUDED.sucursal,
    revisado_en = EXCLUDED.revisado_en,
    revisado_o_no = EXCLUDED.revisado_o_no,
    justificacion = EXCLUDED.justificacion,
    concepto_calificado = EXCLUDED.concepto_calificado;

INSERT INTO ai.analisis_conversacion (
    id_analisis, id_empleado, id_cliente, etiqueta, escala_tipo, valor,
    conversation_started_at, analizado_en, created_at, business_id, sucursal,
    revisado_en, revisado_o_no, justificacion, concepto_calificado
)
SELECT
    id_analisis, id_empleado, id_cliente, etiqueta, escala_tipo, valor,
    conversation_started_at, analizado_en, created_at, business_id, sucursal,
    revisado_en, revisado_o_no, justificacion, concepto_calificado
FROM public.analisis_conversacion
ON CONFLICT (id_analisis) DO UPDATE SET
    id_empleado = EXCLUDED.id_empleado,
    id_cliente = EXCLUDED.id_cliente,
    etiqueta = EXCLUDED.etiqueta,
    escala_tipo = EXCLUDED.escala_tipo,
    valor = EXCLUDED.valor,
    conversation_started_at = EXCLUDED.conversation_started_at,
    analizado_en = EXCLUDED.analizado_en,
    created_at = EXCLUDED.created_at,
    business_id = EXCLUDED.business_id,
    sucursal = EXCLUDED.sucursal,
    revisado_en = EXCLUDED.revisado_en,
    revisado_o_no = EXCLUDED.revisado_o_no,
    justificacion = EXCLUDED.justificacion,
    concepto_calificado = EXCLUDED.concepto_calificado;

INSERT INTO public.estadisticas_costos_ia (
    id, origen, identificador_usuario, session_id, client_id,
    desglose_costos, costo_total_usd, costo_total_mxn, created_at, business_id, sucursal
)
SELECT
    id, origen, identificador_usuario, session_id, client_id,
    desglose_costos, costo_total_usd, costo_total_mxn, created_at, business_id, sucursal
FROM ai.estadisticas_costos_ia
ON CONFLICT (id) DO UPDATE SET
    origen = EXCLUDED.origen,
    identificador_usuario = EXCLUDED.identificador_usuario,
    session_id = EXCLUDED.session_id,
    client_id = EXCLUDED.client_id,
    desglose_costos = EXCLUDED.desglose_costos,
    costo_total_usd = EXCLUDED.costo_total_usd,
    costo_total_mxn = EXCLUDED.costo_total_mxn,
    created_at = EXCLUDED.created_at,
    business_id = EXCLUDED.business_id,
    sucursal = EXCLUDED.sucursal;

INSERT INTO ai.estadisticas_costos_ia (
    id, origen, identificador_usuario, session_id, client_id,
    desglose_costos, costo_total_usd, costo_total_mxn, created_at, business_id, sucursal
)
SELECT
    id, origen, identificador_usuario, session_id, client_id,
    desglose_costos, costo_total_usd, costo_total_mxn, created_at, business_id, sucursal
FROM public.estadisticas_costos_ia
ON CONFLICT (id) DO UPDATE SET
    origen = EXCLUDED.origen,
    identificador_usuario = EXCLUDED.identificador_usuario,
    session_id = EXCLUDED.session_id,
    client_id = EXCLUDED.client_id,
    desglose_costos = EXCLUDED.desglose_costos,
    costo_total_usd = EXCLUDED.costo_total_usd,
    costo_total_mxn = EXCLUDED.costo_total_mxn,
    created_at = EXCLUDED.created_at,
    business_id = EXCLUDED.business_id,
    sucursal = EXCLUDED.sucursal;

INSERT INTO public.reportes (
    id_reporte, descripcion, id_cliente, fecha_reporte, business_id,
    user_name, soft_delete, texto_notas_internas, user_name_notas_internas,
    rfc3339_notas_internas, phone, tipo, soft_delete_date, progreso,
    id_login, id_login_notas_internas, sucursal
) OVERRIDING SYSTEM VALUE
SELECT
    id_reporte, descripcion, id_cliente, fecha_reporte, business_id,
    user_name, soft_delete, texto_notas_internas, user_name_notas_internas,
    rfc3339_notas_internas, phone, tipo, soft_delete_date, progreso,
    id_login, id_login_notas_internas, sucursal
FROM crm.reportes
ON CONFLICT (id_reporte) DO UPDATE SET
    descripcion = EXCLUDED.descripcion,
    id_cliente = EXCLUDED.id_cliente,
    fecha_reporte = EXCLUDED.fecha_reporte,
    business_id = EXCLUDED.business_id,
    user_name = EXCLUDED.user_name,
    soft_delete = EXCLUDED.soft_delete,
    texto_notas_internas = EXCLUDED.texto_notas_internas,
    user_name_notas_internas = EXCLUDED.user_name_notas_internas,
    rfc3339_notas_internas = EXCLUDED.rfc3339_notas_internas,
    phone = EXCLUDED.phone,
    tipo = EXCLUDED.tipo,
    soft_delete_date = EXCLUDED.soft_delete_date,
    progreso = EXCLUDED.progreso,
    id_login = EXCLUDED.id_login,
    id_login_notas_internas = EXCLUDED.id_login_notas_internas,
    sucursal = EXCLUDED.sucursal;

INSERT INTO crm.reportes (
    id_reporte, descripcion, id_cliente, fecha_reporte, business_id,
    user_name, soft_delete, texto_notas_internas, user_name_notas_internas,
    rfc3339_notas_internas, phone, tipo, soft_delete_date, progreso,
    id_login, id_login_notas_internas, sucursal
) OVERRIDING SYSTEM VALUE
SELECT
    id_reporte, descripcion, id_cliente, fecha_reporte, business_id,
    user_name, soft_delete, texto_notas_internas, user_name_notas_internas,
    rfc3339_notas_internas, phone, tipo, soft_delete_date, progreso,
    id_login, id_login_notas_internas, sucursal
FROM public.reportes
ON CONFLICT (id_reporte) DO UPDATE SET
    descripcion = EXCLUDED.descripcion,
    id_cliente = EXCLUDED.id_cliente,
    fecha_reporte = EXCLUDED.fecha_reporte,
    business_id = EXCLUDED.business_id,
    user_name = EXCLUDED.user_name,
    soft_delete = EXCLUDED.soft_delete,
    texto_notas_internas = EXCLUDED.texto_notas_internas,
    user_name_notas_internas = EXCLUDED.user_name_notas_internas,
    rfc3339_notas_internas = EXCLUDED.rfc3339_notas_internas,
    phone = EXCLUDED.phone,
    tipo = EXCLUDED.tipo,
    soft_delete_date = EXCLUDED.soft_delete_date,
    progreso = EXCLUDED.progreso,
    id_login = EXCLUDED.id_login,
    id_login_notas_internas = EXCLUDED.id_login_notas_internas,
    sucursal = EXCLUDED.sucursal;

SELECT ops.sync_set_sequence_if_needed(
    'public.estadisticas',
    'id_estadistica',
    (SELECT COALESCE(MAX(id_estadistica), 0) FROM public.estadisticas)
);
SELECT ops.sync_set_sequence_if_needed(
    'platform.estadisticas',
    'id_estadistica',
    (SELECT COALESCE(MAX(id_estadistica), 0) FROM platform.estadisticas)
);

SELECT ops.sync_set_sequence_if_needed(
    'public.analisis_conversacion',
    'id_analisis',
    (SELECT COALESCE(MAX(id_analisis), 0) FROM public.analisis_conversacion)
);
SELECT ops.sync_set_sequence_if_needed(
    'ai.analisis_conversacion',
    'id_analisis',
    (SELECT COALESCE(MAX(id_analisis), 0) FROM ai.analisis_conversacion)
);

SELECT ops.sync_set_sequence_if_needed(
    'public.estadisticas_costos_ia',
    'id',
    (SELECT COALESCE(MAX(id), 0) FROM public.estadisticas_costos_ia)
);
SELECT ops.sync_set_sequence_if_needed(
    'ai.estadisticas_costos_ia',
    'id',
    (SELECT COALESCE(MAX(id), 0) FROM ai.estadisticas_costos_ia)
);

SELECT ops.sync_set_sequence_if_needed(
    'public.reportes',
    'id_reporte',
    (SELECT COALESCE(MAX(id_reporte), 0) FROM public.reportes)
);
SELECT ops.sync_set_sequence_if_needed(
    'crm.reportes',
    'id_reporte',
    (SELECT COALESCE(MAX(id_reporte), 0) FROM crm.reportes)
);

ANALYZE public.estadisticas;
ANALYZE platform.estadisticas;
ANALYZE public.analisis_conversacion;
ANALYZE ai.analisis_conversacion;
ANALYZE public.estadisticas_costos_ia;
ANALYZE ai.estadisticas_costos_ia;
ANALYZE public.reportes;
ANALYZE crm.reportes;
