
ALTER TABLE platform.estadisticas
    ADD COLUMN IF NOT EXISTS conversation_flow_code VARCHAR(8);

ALTER TABLE public.estadisticas
    ADD COLUMN IF NOT EXISTS conversation_flow_code VARCHAR(8);

ALTER TABLE platform.estadisticas
    DROP CONSTRAINT IF EXISTS chk_platform_estadisticas_conversation_flow_code;

ALTER TABLE platform.estadisticas
    ADD CONSTRAINT chk_platform_estadisticas_conversation_flow_code
    CHECK (conversation_flow_code IN ('h2a', 'a2h', 'h2h', 'a2a'));

ALTER TABLE public.estadisticas
    DROP CONSTRAINT IF EXISTS chk_public_estadisticas_conversation_flow_code;

ALTER TABLE public.estadisticas
    ADD CONSTRAINT chk_public_estadisticas_conversation_flow_code
    CHECK (conversation_flow_code IN ('h2a', 'a2h', 'h2h', 'a2a'));

COMMENT ON COLUMN platform.estadisticas.conversation_flow_code
    IS 'Codigo canonico de flujo conversacional. origen->destino: h2a = humano inicia con agente, a2h = agente inicia con humano, h2h = humano con humano, a2a = agente con agente. Se usa para visibilidad y clasificacion de conversaciones.';

COMMENT ON COLUMN public.estadisticas.conversation_flow_code
    IS 'Codigo canonico de flujo conversacional. origen->destino: h2a = humano inicia con agente, a2h = agente inicia con humano, h2h = humano con humano, a2a = agente con agente. Espejo legacy de platform.estadisticas.';

CREATE OR REPLACE FUNCTION platform.derive_conversation_flow_code(
    p_rfc3339_mensajes jsonb,
    p_existing_code text DEFAULT NULL
)
RETURNS varchar(8)
LANGUAGE sql
IMMUTABLE
AS $$
    WITH normalized AS (
        SELECT
            lower(NULLIF(BTRIM(COALESCE(p_existing_code, '')), '')) AS existing_code,
            lower(NULLIF(BTRIM(COALESCE(p_rfc3339_mensajes #>> '{0,1,conversation_flow_code}', '')), '')) AS meta_flow_code,
            lower(NULLIF(BTRIM(COALESCE(p_rfc3339_mensajes #>> '{0,1,source}', '')), '')) AS meta_source,
            (
                SELECT lower(NULLIF(BTRIM(elem.value->>2), ''))
                FROM jsonb_array_elements(
                    CASE
                        WHEN jsonb_typeof(p_rfc3339_mensajes) = 'array' THEN p_rfc3339_mensajes
                        ELSE '[]'::jsonb
                    END
                ) WITH ORDINALITY AS elem(value, ord)
                WHERE jsonb_typeof(elem.value) = 'array'
                  AND jsonb_array_length(elem.value) >= 3
                  AND COALESCE(elem.value->>0, '') <> '_meta'
                ORDER BY elem.ord
                LIMIT 1
            ) AS first_role
    )
    SELECT CASE
        WHEN existing_code IN ('h2a', 'a2h', 'h2h', 'a2a') THEN existing_code
        WHEN meta_flow_code IN ('h2a', 'a2h', 'h2h', 'a2a') THEN meta_flow_code
        WHEN meta_source IN ('human_upload', 'human-upload') THEN 'h2h'
        WHEN first_role IN ('bot', 'assistant', 'empleado digital', 'empleadodigital', 'empleado_digital', 'digital', 'system', 'ia', 'ia apexcare') THEN 'a2h'
        WHEN first_role IN ('user', 'cliente', 'client', 'usuario', 'persona', 'persona usuaria', 'persona_usuaria', 'human', 'prospecto', 'lead') THEN 'h2a'
        ELSE 'h2a'
    END
    FROM normalized;
$$;

CREATE OR REPLACE FUNCTION platform.apply_conversation_flow_code()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.conversation_flow_code := platform.derive_conversation_flow_code(
        NEW.rfc3339_mensajes,
        NEW.conversation_flow_code
    );
    RETURN NEW;
END;
$$;

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, conversation_flow_code
             ) VALUES (
                $1, $2, $3, $4, $5,
                $6, $7, $8, $9,
                $10, $11, $12, $13
             )
             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,
                conversation_flow_code = EXCLUDED.conversation_flow_code',
            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,
            NEW.conversation_flow_code;

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

DROP TRIGGER IF EXISTS trg_apply_conversation_flow_code_platform_estadisticas ON platform.estadisticas;
CREATE TRIGGER trg_apply_conversation_flow_code_platform_estadisticas
    BEFORE INSERT OR UPDATE OF rfc3339_mensajes, conversation_flow_code ON platform.estadisticas
    FOR EACH ROW
    EXECUTE FUNCTION platform.apply_conversation_flow_code();

DROP TRIGGER IF EXISTS trg_apply_conversation_flow_code_public_estadisticas ON public.estadisticas;
CREATE TRIGGER trg_apply_conversation_flow_code_public_estadisticas
    BEFORE INSERT OR UPDATE OF rfc3339_mensajes, conversation_flow_code ON public.estadisticas
    FOR EACH ROW
    EXECUTE FUNCTION platform.apply_conversation_flow_code();

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();

SELECT set_config('app.sync_platform_estadisticas_mirror', 'true', false);

UPDATE platform.estadisticas AS e
SET conversation_flow_code = CASE
    WHEN EXISTS (
        SELECT 1
        FROM crm.historiales_conversacion_importados AS h
        WHERE h.id_estadistica = e.id_estadistica
          AND h.soft_delete IS NOT TRUE
    ) THEN 'h2h'
    ELSE platform.derive_conversation_flow_code(e.rfc3339_mensajes, e.conversation_flow_code)
END;

UPDATE public.estadisticas AS e
SET conversation_flow_code = CASE
    WHEN EXISTS (
        SELECT 1
        FROM crm.historiales_conversacion_importados AS h
        WHERE h.id_estadistica = e.id_estadistica
          AND h.soft_delete IS NOT TRUE
    ) THEN 'h2h'
    ELSE platform.derive_conversation_flow_code(e.rfc3339_mensajes, e.conversation_flow_code)
END;

SELECT set_config('app.sync_platform_estadisticas_mirror', 'false', false);

ALTER TABLE platform.estadisticas
    ALTER COLUMN conversation_flow_code SET NOT NULL;

ALTER TABLE public.estadisticas
    ALTER COLUMN conversation_flow_code SET NOT NULL;

ANALYZE platform.estadisticas;
ANALYZE public.estadisticas;
