SET lock_timeout = '10s';
SET statement_timeout = '120000';

ALTER TABLE platform.estadisticas
    ADD COLUMN IF NOT EXISTS conversation_flow_code varchar(8)
    GENERATED ALWAYS AS (
        CASE
            WHEN LOWER(COALESCE(rfc3339_mensajes #>> '{0,1,source}', '')) = 'human_upload' THEN 'h2h'
            WHEN LOWER(COALESCE(
                CASE
                    WHEN COALESCE(rfc3339_mensajes #>> '{0,0}', rfc3339_mensajes #>> '{0,0,0}', '') = '_meta'
                        THEN COALESCE(
                            NULLIF(rfc3339_mensajes #>> '{1,0,2,0}', ''),
                            NULLIF(rfc3339_mensajes #>> '{1,2,0}', ''),
                            NULLIF(rfc3339_mensajes #>> '{1,2}', ''),
                            ''
                        )
                    ELSE COALESCE(
                        NULLIF(rfc3339_mensajes #>> '{0,0,2,0}', ''),
                        NULLIF(rfc3339_mensajes #>> '{0,2,0}', ''),
                        NULLIF(rfc3339_mensajes #>> '{0,2}', ''),
                        ''
                    )
                END,
                ''
            )) IN (
                'bot',
                'assistant',
                'empleado digital',
                'empleadodigital',
                'empleado_digital',
                'digital',
                'system',
                'ia',
                'ia apexcare'
            ) THEN
                CASE
                    WHEN jsonb_path_exists(
                        rfc3339_mensajes,
                        '$.** ? (@ == "user" || @ == "cliente" || @ == "client" || @ == "usuario" || @ == "persona" || @ == "persona usuaria" || @ == "persona_usuaria" || @ == "human" || @ == "prospecto" || @ == "lead")'
                    ) THEN 'a2h'
                    ELSE 'a2a'
                END
            ELSE 'h2a'
        END
    ) STORED;

ALTER TABLE platform.estadisticas
    DROP CONSTRAINT IF EXISTS chk_estadisticas_conversation_flow_code_valid;

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

COMMENT ON COLUMN platform.estadisticas.conversation_flow_code
    IS 'Codigo canonico del flujo conversacional derivado automaticamente desde rfc3339_mensajes: h2a=humano->agente, a2h=agente->humano, h2h=humano->humano importado, a2a=agente->agente.';

CREATE INDEX IF NOT EXISTS estadisticas_business_flow_start_idx
    ON platform.estadisticas USING btree (business_id, conversation_flow_code, inicio_conversacion_hora DESC)
    WHERE soft_delete IS NOT TRUE;

ANALYZE platform.estadisticas;
