Api reference

components.support.public.blueprint ¶

support_blueprint `module-attribute` ¶

support_blueprint = create_blueprint(
    name="support",
    import_name=__name__,
    template_folder=join(
        dirname(__file__), "..", "templates"
    ),
    cli_group="support",
)

components.support.public.commands ¶

fix_fr_contact_request_data_2025_09_15 ¶

fix_fr_contact_request_data_2025_09_15(*, dry_run=True)

Following a run of import_fr_legacy_data that included a bug that set the US conversation id on contact_requests instead of the EU conversation id (but with the EU workspace id) when updating existing contact_requests, around 16000 contact_requests were affected (most of them from Sept 2024, around the time of the migration from the US workspace to the EU workspace, and when we had a bunch of conversations with both EU and US conversation ids).

This command fixes the data by setting the correct conversation id (the EU one) on the contact_requests.

Source code in components/support/public/commands/fix_fr_contact_request_data_2025_09_15.py

@support.command(requires_authentication=False)
@command_with_dry_run
def fix_fr_contact_request_data_2025_09_15(*, dry_run: bool = True) -> None:
    """
    Following a run of import_fr_legacy_data that included a bug that set the US conversation id
    on contact_requests instead of the EU conversation id (but with the EU workspace id) when updating
    existing contact_requests, around 16000 contact_requests were affected (most of them from Sept 2024,
    around the time of the migration from the US workspace to the EU workspace, and when we had a bunch
    of conversations with both EU and US conversation ids).

    This command fixes the data by setting the correct conversation id (the EU one) on the contact_requests.
    """
    from components.support.internal.business_logic.actions.fix_fr_contact_request_data import (
        fix_fr_contact_request_data,
    )

    try:
        fix_fr_contact_request_data()
        current_logger.info(
            "Successfully fixed contact requests" + (" (dry run)" if dry_run else "")
        )
    except Exception as e:
        current_logger.error(f"Failed to import intercom admins: {str(e)}")
        raise

import_fr_intercom_admins ¶

import_fr_intercom_admins(
    *, dry_run=True, only_intercom_admin_id=None
)

Import intercom admins into support agents.

This command imports data from the intercom_admin model into support_agent and support_agent_company models. It maps the relevant fields and creates the necessary relationships.

Parameters:

Name	Type	Description	Default
`dry_run`	`bool`	Whether to run in dry-run mode (no database changes). Defaults to True.	`True`
`only_intercom_admin_id`	`str`	If provided, only update the specified intercom admin. Defaults to None.	`None`

Source code in components/support/public/commands/import_fr_intercom_admins.py

@support.command(requires_authentication=False)
@click.option(
    "--only-intercom-admin-id",
    help="Optional intercom admin ID to only update a specific admin",
    required=False,
    type=str,
)
@command_with_dry_run
def import_fr_intercom_admins(
    *, dry_run: bool = True, only_intercom_admin_id: str | None = None
) -> None:
    """Import intercom admins into support agents.

    This command imports data from the intercom_admin model into support_agent and support_agent_company models.
    It maps the relevant fields and creates the necessary relationships.

    Args:
        dry_run (bool, optional): Whether to run in dry-run mode (no database changes). Defaults to True.
        only_intercom_admin_id (str, optional): If provided, only update the specified intercom admin. Defaults to None.
    """
    from components.support.internal.business_logic.actions.import_fr_intercom_admins.import_fr_intercom_admins import (
        import_fr_intercom_admins_to_support_agents,
    )

    try:
        import_fr_intercom_admins_to_support_agents(
            save=not dry_run, only_for_intercom_admin_id=only_intercom_admin_id
        )
        current_logger.info(
            "Successfully imported intercom admins" + (" (dry run)" if dry_run else "")
        )
    except Exception as e:
        current_logger.error(f"Failed to import intercom admins: {str(e)}")
        raise

import_fr_intercom_spe_inboxes ¶

import_fr_intercom_spe_inboxes(*, dry_run=True)

Import all existing intercom_spe_inbox entries into support.support_specialization.

Source code in components/support/public/commands/import_fr_intercom_spe_inboxes.py

@support.command(requires_authentication=False)
@command_with_dry_run
def import_fr_intercom_spe_inboxes(*, dry_run: bool = True) -> None:
    """Import all existing intercom_spe_inbox entries into support.support_specialization."""
    from components.support.internal.business_logic.actions.import_fr_intercom_admins.import_fr_intercom_spe_inboxes_to_support_specializations import (
        import_fr_intercom_spe_inboxes_to_support_specializations,
    )

    try:
        import_fr_intercom_spe_inboxes_to_support_specializations(save=not dry_run)
        current_logger.info(
            "Successfully imported intercom_spe_inboxes into support.support_specialization"
            + (" (dry run)" if dry_run else "")
        )
    except Exception as e:
        current_logger.error(f"Failed to import intercom_spe_inboxes: {str(e)}")
        raise

import_fr_legacy_quick_callback_data ¶

run_import_fr_legacy_quick_callback_data ¶

run_import_fr_legacy_quick_callback_data(
    dry_run, from_date, to_date, bulk_size
)

Import legacy quick callback data from CareCallback and CareConversationContext to create ContactRequest, QuickCallbackRequest and PhoneCall records.

Only imports CareCallback records where is_immediate_callback=True. This command should be run to migrate existing quick callback data to the new schema.

Source code in components/support/public/commands/import_fr_legacy_quick_callback_data.py

@support.command(requires_authentication=False)
@command_with_dry_run
@click.option(
    "--from-date",
    type=DateCli(),
    required=True,
    help="Get quick callback data from specified date",
)
@click.option(
    "--to-date",
    type=DateCli(),
    required=False,
    help="Get quick callback data to specified date, if not provided will default to today",
)
@click.option(
    "--bulk-size",
    type=int,
    required=False,
    help="Should we update/insert/delete entries progressively",
    default=2000,
)
def run_import_fr_legacy_quick_callback_data(
    dry_run: bool, from_date: date, to_date: date, bulk_size: int
) -> None:
    """
    Import legacy quick callback data from CareCallback and CareConversationContext
    to create ContactRequest, QuickCallbackRequest and PhoneCall records.

    Only imports CareCallback records where is_immediate_callback=True.
    This command should be run to migrate existing quick callback data to the new schema.
    """
    if not to_date:
        to_date = utctoday()
    import_fr_legacy_quick_callback_data(
        from_date=from_date,
        to_date=to_date,
        dry_run=dry_run,
        bulk_size=bulk_size,
    )

import_fr_support_agents_from_turing ¶

import_fr_support_agents_from_turing(dry_run, verbose)

Import support agents from a Turing table that's filled with data coming from a Google Sheet. In the future we want to replace this with support endpoint calls from eu_tools directly (which contains the source for Alan employees & external agents)

Source code in components/support/public/commands/import_fr_support_agents_from_turing.py

@support.command(requires_authentication=False)
@command_with_dry_run
@click.option(
    "--verbose",
    is_flag=True,
    help="Show verbose output. If not set, a progress bar will be shown.",
)
def import_fr_support_agents_from_turing(dry_run: bool, verbose: bool) -> None:
    """
    Import support agents from a Turing table that's filled with data coming
    from a Google Sheet. In the future we want to replace this with support endpoint calls
    from eu_tools directly (which contains the source for Alan employees & external agents)
    """
    from components.support.internal.business_logic.actions.import_fr_support_agents_from_turing import (
        import_fr_support_agents_from_turing,
    )

    import_fr_support_agents_from_turing(commit=not dry_run, verbose=verbose)

import_intercom_inboxes_command ¶

import_intercom_inboxes ¶

import_intercom_inboxes(
    *, dry_run=True, intercom_workspace_id
)

Import Intercom inboxes from a given Intercom workspace.

Parameters:

Name	Type	Description	Default
`dry_run`	`bool`	Whether to run in dry-run mode (no database changes). Defaults to True.	`True`
`intercom_workspace_id`	`str`	The Intercom workspace ID to import inboxes from.	required

Source code in components/support/public/commands/import_intercom_inboxes_command.py

@support.command(requires_authentication=False)
@click.option(
    "--intercom-workspace-id",
    help="The Intercom workspace ID to import inboxes from.",
    required=True,
    type=str,
)
@command_with_dry_run
def import_intercom_inboxes(
    *, dry_run: bool = True, intercom_workspace_id: str
) -> None:
    """Import Intercom inboxes from a given Intercom workspace.

    Args:
        dry_run (bool, optional): Whether to run in dry-run mode (no database changes). Defaults to True.
        intercom_workspace_id (str): The Intercom workspace ID to import inboxes from.
    """
    from components.support.internal.business_logic.actions.import_intercom_inboxes import (
        import_support_agent_spes_from_workspace_inboxes,
    )

    try:
        import_support_agent_spes_from_workspace_inboxes(
            intercom_workspace_id=intercom_workspace_id, commit=not dry_run
        )
        current_logger.info(
            f"Successfully imported Intercom inboxes from workspace {intercom_workspace_id}"
            + (" (dry run)" if dry_run else "")
        )
    except Exception as e:
        current_logger.error(f"Failed to import Intercom inboxes: {str(e)}")
        raise

import_legacy_contact_request_data ¶

run_import_be_legacy_contact_request_data ¶

run_import_be_legacy_contact_request_data(
    dry_run, from_date, to_date
)

To globalize the Assigner, we need to import all existing contact requests from Belgium. We already are creating new contact requests for new convos. This command should be run regularly automatically until we stop needing it.

Source code in components/support/public/commands/import_legacy_contact_request_data.py

@support.command(requires_authentication=False)
@command_with_dry_run
@click.option(
    "--from-date",
    type=DateCli(),
    required=True,
    help="Get data from specified date",
)
@click.option(
    "--to-date",
    type=DateCli(),
    required=False,
    help="Get data to specified date, if not provided will default to today",
)
def run_import_be_legacy_contact_request_data(
    dry_run: bool,
    from_date: date,
    to_date: date,
) -> None:
    """
    To globalize the Assigner, we need to import all existing contact requests from Belgium.
    We already are creating new contact requests for new convos.
    This command should be run regularly automatically until we stop needing it.
    """
    if not to_date:
        to_date = utctoday()
    import_be_legacy_contact_request_data(
        from_date=from_date,
        to_date=to_date,
        dry_run=dry_run,
    )

run_import_fr_legacy_contact_request_data ¶

run_import_fr_legacy_contact_request_data(
    dry_run, from_date, to_date, bulk_size
)

To globalize the Assigner, we need to import all existing contact requests from France. We already are creating new contact requests for new convos. This command should be run regularly automatically until we stop needing it.

Source code in components/support/public/commands/import_legacy_contact_request_data.py

@support.command(requires_authentication=False)
@command_with_dry_run
@click.option(
    "--from-date",
    type=DateCli(),
    required=True,
    help="Get data from specified date",
)
@click.option(
    "--to-date",
    type=DateCli(),
    required=False,
    help="Get data to specified date, if not provided will default to today",
)
@click.option(
    "--bulk-size",
    type=int,
    required=False,
    help="Should we update/insert/delete entries progressively",
    default=2000,
)
def run_import_fr_legacy_contact_request_data(
    dry_run: bool, from_date: date, to_date: date, bulk_size: int
) -> None:
    """
    To globalize the Assigner, we need to import all existing contact requests from France.
    We already are creating new contact requests for new convos.
    This command should be run regularly automatically until we stop needing it.
    """
    if not to_date:
        to_date = utctoday()
    import_fr_legacy_contact_request_data(
        from_date=from_date,
        to_date=to_date,
        dry_run=dry_run,
        bulk_size=bulk_size,
    )

import_support_agents_from_employees_command ¶

import_support_agents_from_employees ¶

import_support_agents_from_employees(dry_run, verbose)

Import support agents from employees into SupportAgent and SupportAgentWorkspaceAffectation. Creates or updates SupportAgent entries and their associated workspace affectations.

Source code in components/support/public/commands/import_support_agents_from_employees_command.py

@support.command(requires_authentication=False)
@command_with_dry_run
@click.option(
    "--verbose",
    is_flag=True,
    help="Show verbose output. If not set, a progress bar will be shown.",
)
def import_support_agents_from_employees(dry_run: bool, verbose: bool) -> None:
    """
    Import support agents from employees into SupportAgent and SupportAgentWorkspaceAffectation.
    Creates or updates SupportAgent entries and their associated workspace affectations.
    """
    from components.support.internal.business_logic.actions.import_support_agents_from_employees import (
        import_support_agents_from_employees,
    )

    if not _is_employee_import_command_active(get_current_app_name()):
        current_logger.info(
            "Not running import_support_agents_from_employees - feature flag for country is disabled"
        )
        return

    try:
        import_support_agents_from_employees(commit=not dry_run, verbose=verbose)
        current_logger.info(
            "Successfully imported support agents from employees"
            + (" (dry run)" if dry_run else "")
        )
    except Exception as e:
        current_logger.error(f"Failed to import support agents: {str(e)}")
        raise

intercom_fallback ¶

intercom_new_conversation_fallback ¶

process_new_conversations_not_sent_by_intercom ¶

process_new_conversations_not_sent_by_intercom(
    dry_run, lookback_minutes=10
)

Process new conversations not sent by Intercom.

This command gets a list of conversations from Intercom that have been created in the past {lookback-minutes} and processes them to make sure we haven't missed any in case of Intercom errors (webhook not fired, Alan service temporarily unavailable, etc)

Parameters:

Name	Type	Description	Default
`dry_run`	`bool`	Whether to run in dry-run mode (no database changes). Defaults to True.	required
`lookback_minutes`	`int`	If provided, get conversations from Intercom created in the last X minutes. Defaults to 10.	`10`

Source code in components/support/public/commands/intercom_fallback/intercom_new_conversation_fallback.py

@support.command(requires_authentication=False)
@click.option(
    "-l",
    "--lookback-minutes",
    default=10,
    type=int,
    help="Lookback x minutes from now. Defaults to 10min.",
)
@command_with_dry_run
def process_new_conversations_not_sent_by_intercom(
    dry_run: bool,
    lookback_minutes: int = 10,
) -> None:
    """Process new conversations not sent by Intercom.

    This command gets a list of conversations from Intercom that have been created in the past
    {lookback-minutes} and processes them to make sure we haven't missed any in case of
    Intercom errors (webhook not fired, Alan service temporarily unavailable, etc)

    Args:
        dry_run (bool, optional): Whether to run in dry-run mode (no database changes). Defaults to True.
        lookback_minutes (int, optional): If provided, get conversations from Intercom created in the last X minutes. Defaults to 10.
    """
    import datetime
    import time

    from components.support.internal.business_logic.actions.intercom_fallback.process_new_conversation_fallback import (
        find_and_process_new_intercom_conversations_not_sent_by_intercom,
        is_fallback_command_active,
    )

    current_app_name = get_current_app_name()
    if not is_fallback_command_active(current_app_name):
        return
    # give ourselves a buffer of 1 min to process new conversations "normally"
    buffer = 60  # seconds
    end_unix_time = int(time.mktime(datetime.datetime.now().timetuple())) - buffer
    start_unix_time = end_unix_time - (lookback_minutes * 60)
    intercom_client = get_care_intercom_client(current_app_name)
    find_and_process_new_intercom_conversations_not_sent_by_intercom(
        start_unix_time=start_unix_time,
        end_unix_time=end_unix_time,
        intercom_client=intercom_client,
        dry_run=dry_run,
    )

intercom_update_fallback ¶

sync_conversation_updates_not_sent_by_intercom ¶

sync_conversation_updates_not_sent_by_intercom(
    dry_run, end_offset_seconds=0, lookback_hours=6
)

Process conversation updates not sent by Intercom.

This command gets a list of conversations from Intercom that have been updated in the past {lookback-hours} and processes them (syncs the DB) to make sure we haven't missed any in case of Intercom errors (webhook not fired, Alan service temporarily unavailable, etc)

Parameters:

Name	Type	Description	Default
`dry_run`	`bool`	Whether to run in dry-run mode (no database changes). Defaults to True.	required
`lookback_hours`	`int`	If provided, get conversations from Intercom updated in the last X hours. Defaults to 6.	`6`
`end_offset_seconds`	`int`	start at datetime.now() - end-offset-seconds. This allows running the command from cron, disregarding recent conversations. Defaults to 0.	`0`

Source code in components/support/public/commands/intercom_fallback/intercom_update_fallback.py

@support.command(requires_authentication=False)
@click.option(
    "-o",
    "--end-offset-seconds",
    default=0,
    type=int,
    help="when no end-unix-time is given, start at datetime.now() - end-offset-seconds. This allows running the command from cron, disregarding recent conversations. Defaults to 0.",
)
@click.option(
    "-l",
    "--lookback-hours",
    default=6,
    type=int,
    help="Lookback x hours from computed end time. Defaults to 6h.",
)
@command_with_dry_run
def sync_conversation_updates_not_sent_by_intercom(
    dry_run: bool,
    end_offset_seconds: int = 0,
    lookback_hours: int = 6,
) -> None:
    """Process conversation updates not sent by Intercom.

    This command gets a list of conversations from Intercom that have been updated in the past
    {lookback-hours} and processes them (syncs the DB) to make sure we haven't missed any in case of
    Intercom errors (webhook not fired, Alan service temporarily unavailable, etc)

    Args:
        dry_run (bool, optional): Whether to run in dry-run mode (no database changes). Defaults to True.
        lookback_hours (int, optional): If provided, get conversations from Intercom updated in the last X hours. Defaults to 6.
        end_offset_seconds (int, optional): start at datetime.now() - end-offset-seconds. This allows running the command from cron, disregarding recent conversations. Defaults to 0.
    """
    import datetime
    import time

    from components.support.internal.business_logic.actions.intercom_fallback.update_conversation_fallback import (
        find_and_sync_db_for_intercom_conversation_updates_not_sent_by_intercom,
    )

    current_app_name = get_current_app_name()
    if not is_fallback_command_active(current_app_name):
        return

    end_unix_time = (
        int(time.mktime(datetime.datetime.now().timetuple())) - end_offset_seconds
    )
    start_unix_time = end_unix_time - (lookback_hours * 3600)
    intercom_client = get_care_intercom_client(current_app_name)
    find_and_sync_db_for_intercom_conversation_updates_not_sent_by_intercom(
        start_unix_time=start_unix_time,
        end_unix_time=end_unix_time,
        intercom_client=intercom_client,
        dry_run=dry_run,
    )

synchronize_support_specializations ¶

synchronize_support_specializations(dry_run)

Synchronize support specializations from Intercom to our backend.

Source code in components/support/public/commands/synchronize_support_specializations.py

@support.command(requires_authentication=False)
@command_with_dry_run
@do_not_run_in_prod
def synchronize_support_specializations(dry_run: bool) -> None:
    """
    Synchronize support specializations from Intercom to our backend.
    """
    from components.support.internal.business_logic.actions.synchronize_support_specializations_actions import (
        synchronize_support_specializations,
    )

    synchronize_support_specializations(commit=not dry_run)

components.support.public.data ¶

assigner_sql_queries ¶

BE_ASSIGNER_SQL_QUERY `module-attribute` ¶

BE_ASSIGNER_SQL_QUERY = "\n    WITH support_agents_for_computation AS (\n        SELECT\n            sa.id as support_agent_id,\n            sa.intercom_admin_id,\n            sawa.level,\n            sawa.intercom_workspace_id,\n            --Roles are now stored in an array, so we check for the presence of a role in the array.\n            'eligible_for_sync' = ANY(sawa.roles) as is_eligible_for_sync,\n            CASE WHEN (NOW() AT TIME ZONE 'Europe/Paris')::TIME BETWEEN '09:00:00' AND '17:00:00'\n                THEN 'eligible_for_async' = ANY(sawa.roles)\n                ELSE True\n            END as is_eligible_for_async,\n            NOT sa.is_external_admin AS has_role_uce,\n            CASE WHEN (NOW() AT TIME ZONE 'Europe/Paris')::TIME BETWEEN '09:00:00' AND '17:00:00'\n                THEN 'eligible_for_callback' = ANY(sawa.roles)\n                ELSE False\n            END as has_role_callback,\n            'en' = ANY(sa.spoken_languages) as speaks_english,\n            'fr' = ANY(sa.spoken_languages) as speaks_french,\n            'nl' = ANY(sa.spoken_languages) as speaks_dutch\n        FROM support.support_agent as sa\n        JOIN support.support_agent_workspace_affectation sawa ON sa.id = sawa.support_agent_id AND sawa.intercom_workspace_id = (:workspace_id)\n        WHERE sa.id = (:support_agent_id)  -- the admin requesting an assignment\n    ),\n    uce_specialisations_mapping AS (\n      SELECT sa.intercom_admin_id\n      , ss.intercom_inbox_id\n      , sasms.score\n      FROM support_agents_for_computation sa\n      LEFT JOIN support.support_agent_workspace_affectation sawa ON sawa.support_agent_id = sa.support_agent_id AND sawa.intercom_workspace_id = (:workspace_id)\n      LEFT JOIN support.support_agent_spe_matching_score sasms ON sasms.support_agent_workspace_affectation_id = sawa.id\n      LEFT JOIN support.support_specialization ss ON ss.id = sasms.support_specialization_id\n      WHERE COALESCE(sasms.score, 0.2) != 0\n    ),\n    prefiltered_contact_request_backlog AS (\n        SELECT\n            DISTINCT ON (cr.intercom_conversation_id)\n            cr.id as contact_request_id,\n            cr.intercom_conversation_id,\n            cris.assigned_intercom_inbox_id as inbox_id,\n            cris.last_inbox_assignment_by,\n            cris.waiting_since,\n            greatest(cris.waiting_since, cris.last_admin_reply_at) AS time_reference_for_priority_score,\n            cris.last_admin_reply_at,\n            cr.source_type as type,\n            cram.recommended_admin_id,\n            cris.started_at,\n            cris.state,\n            cris.assigned_intercom_admin_id as admin_id,\n            cr.created_at,\n            -- using tags to compute properties of the conversations\n            CASE\n                WHEN tags.sla_tag = 'SLA[12h]' or tags.sla_tag = 'ASYNC' THEN 'SLA[12h]'\n                WHEN tags.sla_tag = 'SLA[5min]' or tags.sla_tag = 'SYNC' THEN 'SLA[5min]'\n                WHEN tags.sla_tag = 'SLA[30min]' OR tags.callback_tag = 'ROLE[CALLBACK]' THEN 'SLA[30min]'\n                WHEN cr.source_type in ('async_conversation_request', 'unknown') THEN 'SLA[12h]'\n                ELSE 'SLA[5min]'\n            END AS priority_level,\n            CASE\n                WHEN tags.workforce_level_tag = 'ROLE[LEVEL3]' OR tags.uce_reserved_tag IS NOT NULL THEN 3\n                WHEN tags.workforce_level_tag = 'ROLE[LEVEL2]' THEN 2\n                ELSE 1\n            END AS conversation_workforce_level,\n            tags.*\n        FROM support.contact_request cr\n        JOIN support.contact_request_intercom_state cris ON cr.id = cris.contact_request_id\n        LEFT JOIN support.contact_request_assignment_metadata cram ON cr.id = cram.contact_request_id\n        LEFT JOIN support.contact_request_tag crt ON cr.id = crt.contact_request_id\n        CROSS JOIN LATERAL (\n            SELECT\n                MAX(CASE WHEN crt.name in ('ROLE[CALLBACK]', '#OTHER - CARE TEAM - Eligible call-back request') THEN crt.name END) AS callback_tag,\n                MAX(CASE WHEN crt.name = 'App language Dutch' THEN crt.name END) AS dutch_tag,\n                MAX(CASE WHEN crt.name = 'App language English' THEN crt.name END) AS english_tag,\n                MAX(CASE WHEN crt.name = 'App language French' THEN crt.name END) AS french_tag,\n                MAX(CASE WHEN crt.name IN ('SLA[5min]', 'SLA[30min]', 'SLA[12h]', 'SYNC', 'ASYNC') THEN crt.name END) as sla_tag,\n                MAX(CASE WHEN crt.name IN ('ROLE[LEVEL1]','ROLE[LEVEL2]','ROLE[LEVEL3]') THEN crt.name END) AS workforce_level_tag,\n                MAX(CASE WHEN crt.name = 'SENSITIVE' THEN crt.name END) AS uce_reserved_tag\n            FROM support.contact_request_tag crt\n            WHERE crt.contact_request_id = cr.id\n        ) tags\n        WHERE\n            cr.intercom_workspace_id = (:workspace_id)\n            AND cris.state = 'open'\n            AND (cris.assigned_intercom_admin_id IS NULL OR cris.assigned_intercom_admin_id = '') -- testing for empty string for records updated via flask admin\n        ORDER BY cr.intercom_conversation_id, cr.id, crt.notified_at DESC\n    ),\n\n    eligible_contact_request_backlog_with_priority_level AS (\n        SELECT\n            pcrb.contact_request_id,\n            pcrb.intercom_conversation_id,\n            pcrb.inbox_id,\n            pcrb.last_inbox_assignment_by,\n            pcrb.time_reference_for_priority_score,\n            pcrb.priority_level,\n            pcrb.conversation_workforce_level,\n            sa.level - pcrb.conversation_workforce_level AS distance_workforce_level_admin_conversation,\n            CASE WHEN pcrb.recommended_admin_id = sa.intercom_admin_id THEN (:member_matching_score_boost) ELSE 1 END AS uce_member_matching_score,\n            pcrb.sla_tag,\n            pcrb.started_at\n        FROM prefiltered_contact_request_backlog pcrb\n        CROSS JOIN support_agents_for_computation sa\n        WHERE sa.support_agent_id = (:support_agent_id) -- join only on the admin requesting an assignment\n            -- reduce scope for agents depending on their sync/async eligibility configuration\n            AND (\n                    (COALESCE(sa.is_eligible_for_sync, True) AND pcrb.priority_level = 'SLA[5min]' AND pcrb.callback_tag IS NULL)\n                    OR\n                    (COALESCE(sa.is_eligible_for_async, True) AND pcrb.priority_level != 'SLA[5min]' AND pcrb.callback_tag IS NULL)\n                    OR\n                    (COALESCE(sa.has_role_callback, True) AND pcrb.callback_tag IS NOT NULL)\n            )\n            -- reduce scope for agents not speaking English when not ASYNC\n            AND (COALESCE(sa.speaks_english, True) OR pcrb.english_tag IS NULL OR (pcrb.priority_level = 'SLA[12h]' AND sa.has_role_uce))\n            -- reduce scope for agents not speaking Dutch when not ASYNC\n            AND (COALESCE(sa.speaks_dutch, True) OR pcrb.dutch_tag IS NULL OR (pcrb.priority_level = 'SLA[12h]' AND sa.has_role_uce))\n            -- reduce scope for agents not speaking French when not ASYNC\n            AND (COALESCE(sa.speaks_french, True) OR pcrb.french_tag IS NULL OR (pcrb.priority_level = 'SLA[12h]' AND sa.has_role_uce))\n    ),\n    eligible_contact_request_backlog_with_context AS (\n        SELECT\n            ecrb.contact_request_id,\n            ecrb.intercom_conversation_id,\n            ecrb.priority_level,\n            sa.intercom_admin_id,\n            CASE\n                WHEN ecrb.priority_level = 'SLA[5min]'\n                    THEN (:sync_sla_score_boost) * (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (5 - 2)\n                WHEN ecrb.priority_level = 'SLA[30min]'\n                    THEN (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (30 - 2)\n                ELSE --  priority_level = 'SLA[12h]'\n                        (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (12 * 60 - 2)\n            END AS sla_score,\n            CASE\n                WHEN ecrb.priority_level = 'SLA[5min]'\n                    THEN count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 5\n                WHEN ecrb.priority_level = 'SLA[30min]'\n                    THEN count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 30\n                ELSE\n                    count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 12 * 60\n            END AS sla_breached,\n            distance_workforce_level_admin_conversation,\n            CASE\n                WHEN distance_workforce_level_admin_conversation = 0 THEN (:workforce_level_score_boost)\n                WHEN distance_workforce_level_admin_conversation < 0 THEN NULL\n                ELSE 1.0 / (distance_workforce_level_admin_conversation + 1)\n            END AS workforce_level_matching_score,\n            ecrb.inbox_id,\n            ss.intercom_inbox_name as inbox_name,\n            ecrb.last_inbox_assignment_by,\n            ecrb.uce_member_matching_score,\n            ecrb.time_reference_for_priority_score,\n            ecrb.sla_tag,\n            ecrb.conversation_workforce_level,\n            CASE WHEN ecrb.priority_level = 'SLA[5min]' AND sa.level < 3 THEN 2 ELSE 0 END as additional_power_weight_c\n        FROM eligible_contact_request_backlog_with_priority_level ecrb\n        INNER JOIN support.support_specialization ss on ss.intercom_inbox_id = ecrb.inbox_id AND ss.is_used_by_assigner\n        CROSS JOIN support_agents_for_computation sa\n        WHERE\n            sa.level - ecrb.conversation_workforce_level >= 0\n            AND CASE\n                    WHEN ecrb.priority_level = 'SLA[5min]' THEN COALESCE(ecrb.started_at < now() at time zone 'utc' - '30 seconds'::interval, TRUE)\n                    ELSE COALESCE(ecrb.started_at < now() at time zone 'utc' - '2 minutes'::interval, TRUE)\n                END\n    ),\n    eligible_convos_with_scores AS (\n        SELECT\n            ec.contact_request_id,\n            ec.intercom_conversation_id,\n            ec.inbox_id,\n            ec.inbox_name,\n            NULL as reserved_for_external_agent_platform,\n            ec.time_reference_for_priority_score,\n            m.score AS expertise_matching_score,\n            m.score AS raw_expertise_matching_score,\n            POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c) AS priority_score,\n            POWER(workforce_level_matching_score, (:power_weight_d)) AS workforce_level_matching_score,\n            uce_member_matching_score,\n            COALESCE(\n                    power(m.score, (:power_weight_a))\n                    * POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)\n                    * POWER(workforce_level_matching_score, (:power_weight_d))\n                    * uce_member_matching_score\n                , 0) AS assignment_score,\n            sla_score,\n            sla_breached,\n            MAX(POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)) OVER () AS max_priority_score,\n            priority_level,\n            MAX(POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)) OVER (PARTITION BY priority_level) AS max_priority_score_per_priority_level,\n            null as n_rejections,\n            ec.sla_tag,\n            ec.priority_level = 'SLA[5min]' as is_sync_conversation,\n            ec.additional_power_weight_c\n        FROM eligible_contact_request_backlog_with_context ec\n        LEFT JOIN support.contact_request_assignment_event crae ON crae.contact_request_id = ec.contact_request_id AND crae.action = 'rejected' AND crae.intercom_admin_id = ec.intercom_admin_id\n        LEFT JOIN uce_specialisations_mapping m ON m.intercom_inbox_id = ec.inbox_id\n        WHERE crae.contact_request_id IS NULL\n          AND COALESCE(m.score, 0.2) >= 0\n    ),\n    backlog_stats AS MATERIALIZED (\n        SELECT\n            count(*) as n_assignable_convos,\n            count(*) FILTER (WHERE priority_level = 'SLA[12h]') as n_assignable_async,\n            count(*) FILTER (WHERE priority_level = 'SLA[5min]') as n_assignable_sync,\n            count(*) FILTER (WHERE priority_level = 'SLA[30min]') as n_assignable_callback,\n            count(*) FILTER (WHERE priority_level = 'SLA[12h]' AND sla_breached) as n_assignable_async_sla_breached,\n            count(*) FILTER (WHERE priority_level = 'SLA[5min]' AND sla_breached) as n_assignable_sync_sla_breached,\n            count(*) FILTER (WHERE priority_level = 'SLA[30min]' AND sla_breached) as n_assignable_callback_sla_breached,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[12h]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_12h,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[5min]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_5m,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[30min]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_30m,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[12h]' THEN expertise_matching_score END) AS q90_expertise_score_sla_12h,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[5min]' THEN expertise_matching_score END) AS q90_expertise_score_sla_5m,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[30min]' THEN expertise_matching_score END) AS q90_expertise_score_sla_30m\n        FROM eligible_convos_with_scores\n    )\n    SELECT *, 1.0 AS shift_matching_score\n    FROM eligible_convos_with_scores\n    CROSS JOIN backlog_stats stats\n    ORDER BY assignment_score DESC NULLS LAST, time_reference_for_priority_score\n    LIMIT 10;\n    "

BE_RANKED_SQL_QUERY `module-attribute` ¶

BE_RANKED_SQL_QUERY = "\n    WITH support_agents_for_computation AS (\n        SELECT\n            sa.id as support_agent_id,\n            sa.intercom_admin_id,\n            sawa.level,\n            sawa.intercom_workspace_id,\n            --Roles are now stored in an array, so we check for the presence of a role in the array.\n            'eligible_for_sync' = ANY(sawa.roles) as is_eligible_for_sync,\n            CASE WHEN (NOW() AT TIME ZONE 'Europe/Paris')::TIME BETWEEN '09:00:00' AND '17:00:00'\n                THEN 'eligible_for_async' = ANY(sawa.roles)\n                ELSE True\n            END as is_eligible_for_async,\n            NOT sa.is_external_admin AS has_role_uce,\n            CASE WHEN (NOW() AT TIME ZONE 'Europe/Paris')::TIME BETWEEN '09:00:00' AND '17:00:00'\n                THEN 'eligible_for_callback' = ANY(sawa.roles)\n                ELSE False\n            END as has_role_callback,\n            'en' = ANY(sa.spoken_languages) as speaks_english,\n            'fr' = ANY(sa.spoken_languages) as speaks_french,\n            'nl' = ANY(sa.spoken_languages) as speaks_dutch\n        FROM support.support_agent as sa\n        JOIN support.support_agent_workspace_affectation sawa ON sa.id = sawa.support_agent_id AND sawa.intercom_workspace_id = (:workspace_id)\n        WHERE sa.id = (:support_agent_id)  -- the admin requesting an assignment\n    ),\n    uce_specialisations_mapping AS (\n      SELECT sa.intercom_admin_id\n      , ss.intercom_inbox_id\n      , sasms.score\n      FROM support_agents_for_computation sa\n      LEFT JOIN support.support_agent_workspace_affectation sawa ON sawa.support_agent_id = sa.support_agent_id AND sawa.intercom_workspace_id = (:workspace_id)\n      LEFT JOIN support.support_agent_spe_matching_score sasms ON sasms.support_agent_workspace_affectation_id = sawa.id\n      LEFT JOIN support.support_specialization ss ON ss.id = sasms.support_specialization_id\n      WHERE COALESCE(sasms.score, 0.2) != 0\n    ),\n    prefiltered_contact_request_backlog AS (\n        SELECT\n            DISTINCT ON (cr.intercom_conversation_id)\n            cr.id as contact_request_id,\n            cr.intercom_conversation_id,\n            cris.assigned_intercom_inbox_id as inbox_id,\n            cris.last_inbox_assignment_by,\n            cris.waiting_since,\n            greatest(cris.waiting_since, cris.last_admin_reply_at) AS time_reference_for_priority_score,\n            cris.last_admin_reply_at,\n            cr.source_type as type,\n            cram.recommended_admin_id,\n            cris.started_at,\n            cris.state,\n            cris.assigned_intercom_admin_id as admin_id,\n            cr.created_at,\n            -- using tags to compute properties of the conversations\n            CASE\n                WHEN tags.sla_tag = 'SLA[12h]' or tags.sla_tag = 'ASYNC' THEN 'SLA[12h]'\n                WHEN tags.sla_tag = 'SLA[5min]' or tags.sla_tag = 'SYNC' THEN 'SLA[5min]'\n                WHEN tags.sla_tag = 'SLA[30min]' OR tags.callback_tag = 'ROLE[CALLBACK]' THEN 'SLA[30min]'\n                WHEN cr.source_type in ('async_conversation_request', 'unknown') THEN 'SLA[12h]'\n                ELSE 'SLA[5min]'\n            END AS priority_level,\n            CASE\n                WHEN tags.workforce_level_tag = 'ROLE[LEVEL3]' OR tags.uce_reserved_tag IS NOT NULL THEN 3\n                WHEN tags.workforce_level_tag = 'ROLE[LEVEL2]' THEN 2\n                ELSE 1\n            END AS conversation_workforce_level,\n            tags.*\n        FROM support.contact_request cr\n        JOIN support.contact_request_intercom_state cris ON cr.id = cris.contact_request_id\n        LEFT JOIN support.contact_request_assignment_metadata cram ON cr.id = cram.contact_request_id\n        LEFT JOIN support.contact_request_tag crt ON cr.id = crt.contact_request_id\n        CROSS JOIN LATERAL (\n            SELECT\n                MAX(CASE WHEN crt.name in ('ROLE[CALLBACK]', '#OTHER - CARE TEAM - Eligible call-back request') THEN crt.name END) AS callback_tag,\n                MAX(CASE WHEN crt.name = 'App language Dutch' THEN crt.name END) AS dutch_tag,\n                MAX(CASE WHEN crt.name = 'App language English' THEN crt.name END) AS english_tag,\n                MAX(CASE WHEN crt.name = 'App language French' THEN crt.name END) AS french_tag,\n                MAX(CASE WHEN crt.name IN ('SLA[5min]', 'SLA[30min]', 'SLA[12h]', 'SYNC', 'ASYNC') THEN crt.name END) as sla_tag,\n                MAX(CASE WHEN crt.name IN ('ROLE[LEVEL1]','ROLE[LEVEL2]','ROLE[LEVEL3]') THEN crt.name END) AS workforce_level_tag,\n                MAX(CASE WHEN crt.name = 'SENSITIVE' THEN crt.name END) AS uce_reserved_tag\n            FROM support.contact_request_tag crt\n            WHERE crt.contact_request_id = cr.id\n        ) tags\n        WHERE\n            cr.intercom_workspace_id = (:workspace_id)\n            AND cris.state = 'open'\n            AND (cris.assigned_intercom_admin_id IS NULL OR cris.assigned_intercom_admin_id = '') -- testing for empty string for records updated via flask admin\n        ORDER BY cr.intercom_conversation_id, cr.id, crt.notified_at DESC\n    ),\n\n    eligible_contact_request_backlog_with_priority_level AS (\n        SELECT\n            pcrb.contact_request_id,\n            pcrb.intercom_conversation_id,\n            pcrb.inbox_id,\n            pcrb.last_inbox_assignment_by,\n            pcrb.time_reference_for_priority_score,\n            pcrb.priority_level,\n            pcrb.conversation_workforce_level,\n            sa.level - pcrb.conversation_workforce_level AS distance_workforce_level_admin_conversation,\n            CASE WHEN pcrb.recommended_admin_id = sa.intercom_admin_id THEN (:member_matching_score_boost) ELSE 1 END AS uce_member_matching_score,\n            pcrb.sla_tag,\n            pcrb.started_at\n        FROM prefiltered_contact_request_backlog pcrb\n        CROSS JOIN support_agents_for_computation sa\n        WHERE sa.support_agent_id = (:support_agent_id) -- join only on the admin requesting an assignment\n            -- reduce scope for agents depending on their sync/async eligibility configuration\n            AND (\n                    (COALESCE(sa.is_eligible_for_sync, True) AND pcrb.priority_level = 'SLA[5min]' AND pcrb.callback_tag IS NULL)\n                    OR\n                    (COALESCE(sa.is_eligible_for_async, True) AND pcrb.priority_level != 'SLA[5min]' AND pcrb.callback_tag IS NULL)\n                    OR\n                    (COALESCE(sa.has_role_callback, True) AND pcrb.callback_tag IS NOT NULL)\n            )\n            -- reduce scope for agents not speaking English when not ASYNC\n            AND (COALESCE(sa.speaks_english, True) OR pcrb.english_tag IS NULL OR (pcrb.priority_level = 'SLA[12h]' AND sa.has_role_uce))\n            -- reduce scope for agents not speaking Dutch when not ASYNC\n            AND (COALESCE(sa.speaks_dutch, True) OR pcrb.dutch_tag IS NULL OR (pcrb.priority_level = 'SLA[12h]' AND sa.has_role_uce))\n            -- reduce scope for agents not speaking French when not ASYNC\n            AND (COALESCE(sa.speaks_french, True) OR pcrb.french_tag IS NULL OR (pcrb.priority_level = 'SLA[12h]' AND sa.has_role_uce))\n    ),\n    eligible_contact_request_backlog_with_context AS (\n        SELECT\n            ecrb.contact_request_id,\n            ecrb.intercom_conversation_id,\n            ecrb.priority_level,\n            sa.intercom_admin_id,\n            CASE\n                WHEN ecrb.priority_level = 'SLA[5min]'\n                    THEN (:sync_sla_score_boost) * (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (5 - 2)\n                WHEN ecrb.priority_level = 'SLA[30min]'\n                    THEN (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (30 - 2)\n                ELSE --  priority_level = 'SLA[12h]'\n                        (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (12 * 60 - 2)\n            END AS sla_score,\n            CASE\n                WHEN ecrb.priority_level = 'SLA[5min]'\n                    THEN count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 5\n                WHEN ecrb.priority_level = 'SLA[30min]'\n                    THEN count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 30\n                ELSE\n                    count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 12 * 60\n            END AS sla_breached,\n            distance_workforce_level_admin_conversation,\n            CASE\n                WHEN distance_workforce_level_admin_conversation = 0 THEN (:workforce_level_score_boost)\n                WHEN distance_workforce_level_admin_conversation < 0 THEN NULL\n                ELSE 1.0 / (distance_workforce_level_admin_conversation + 1)\n            END AS workforce_level_matching_score,\n            ecrb.inbox_id,\n            ss.intercom_inbox_name as inbox_name,\n            ecrb.last_inbox_assignment_by,\n            ecrb.uce_member_matching_score,\n            ecrb.time_reference_for_priority_score,\n            ecrb.sla_tag,\n            ecrb.conversation_workforce_level,\n            CASE WHEN ecrb.priority_level = 'SLA[5min]' AND sa.level < 3 THEN 2 ELSE 0 END as additional_power_weight_c\n        FROM eligible_contact_request_backlog_with_priority_level ecrb\n        INNER JOIN support.support_specialization ss on ss.intercom_inbox_id = ecrb.inbox_id AND ss.is_used_by_assigner\n        CROSS JOIN support_agents_for_computation sa\n        WHERE\n            sa.level - ecrb.conversation_workforce_level >= 0\n            AND CASE\n                    WHEN ecrb.priority_level = 'SLA[5min]' THEN COALESCE(ecrb.started_at < now() at time zone 'utc' - '30 seconds'::interval, TRUE)\n                    ELSE COALESCE(ecrb.started_at < now() at time zone 'utc' - '2 minutes'::interval, TRUE)\n                END\n    ),\n    eligible_convos_with_scores AS (\n        SELECT\n            ec.contact_request_id,\n            ec.intercom_conversation_id,\n            ec.inbox_id,\n            ec.inbox_name,\n            NULL as reserved_for_external_agent_platform,\n            ec.time_reference_for_priority_score,\n            m.score AS expertise_matching_score,\n            m.score AS raw_expertise_matching_score,\n            POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c) AS priority_score,\n            POWER(workforce_level_matching_score, (:power_weight_d)) AS workforce_level_matching_score,\n            uce_member_matching_score,\n            COALESCE(\n                    power(m.score, (:power_weight_a))\n                    * POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)\n                    * POWER(workforce_level_matching_score, (:power_weight_d))\n                    * uce_member_matching_score\n                , 0) AS assignment_score,\n            sla_score,\n            sla_breached,\n            MAX(POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)) OVER () AS max_priority_score,\n            priority_level,\n            MAX(POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)) OVER (PARTITION BY priority_level) AS max_priority_score_per_priority_level,\n            null as n_rejections,\n            ec.sla_tag,\n            ec.priority_level = 'SLA[5min]' as is_sync_conversation,\n            ec.additional_power_weight_c\n        FROM eligible_contact_request_backlog_with_context ec\n        LEFT JOIN support.contact_request_assignment_event crae ON crae.contact_request_id = ec.contact_request_id AND crae.action = 'rejected' AND crae.intercom_admin_id = ec.intercom_admin_id\n        LEFT JOIN uce_specialisations_mapping m ON m.intercom_inbox_id = ec.inbox_id\n        WHERE crae.contact_request_id IS NULL\n          AND COALESCE(m.score, 0.2) >= 0\n    ),\n    backlog_stats AS MATERIALIZED (\n        SELECT\n            count(*) as n_assignable_convos,\n            count(*) FILTER (WHERE priority_level = 'SLA[12h]') as n_assignable_async,\n            count(*) FILTER (WHERE priority_level = 'SLA[5min]') as n_assignable_sync,\n            count(*) FILTER (WHERE priority_level = 'SLA[30min]') as n_assignable_callback,\n            count(*) FILTER (WHERE priority_level = 'SLA[12h]' AND sla_breached) as n_assignable_async_sla_breached,\n            count(*) FILTER (WHERE priority_level = 'SLA[5min]' AND sla_breached) as n_assignable_sync_sla_breached,\n            count(*) FILTER (WHERE priority_level = 'SLA[30min]' AND sla_breached) as n_assignable_callback_sla_breached,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[12h]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_12h,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[5min]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_5m,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[30min]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_30m,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[12h]' THEN expertise_matching_score END) AS q90_expertise_score_sla_12h,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[5min]' THEN expertise_matching_score END) AS q90_expertise_score_sla_5m,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[30min]' THEN expertise_matching_score END) AS q90_expertise_score_sla_30m\n        FROM eligible_convos_with_scores\n    )\n    SELECT\n        row_number() OVER (ORDER BY assignment_score DESC NULLS LAST, time_reference_for_priority_score) AS assignment_rank\n        , (:support_agent_id)\n        , *\n        , row_number() OVER (ORDER BY intercom_conversation_id = (:conversation_id) DESC, assignment_score DESC NULLS LAST, time_reference_for_priority_score) AS row_nb\n    FROM eligible_convos_with_scores\n    CROSS JOIN backlog_stats stats\n    ORDER BY row_nb, assignment_score DESC NULLS LAST, time_reference_for_priority_score\n    LIMIT 10;\n    "

ES_ASSIGNER_SQL_QUERY `module-attribute` ¶

ES_ASSIGNER_SQL_QUERY = "\n    WITH support_agents_for_computation AS (\n        SELECT\n            sa.id as support_agent_id,\n            sa.intercom_admin_id,\n            sawa.level,\n            sawa.intercom_workspace_id,\n            --Roles are now stored in an array, so we check for the presence of a role in the array.\n            'eligible_for_sync' = ANY(sawa.roles) as is_eligible_for_sync,\n            CASE WHEN (NOW() AT TIME ZONE 'Europe/Madrid')::TIME BETWEEN '09:00:00' AND '18:00:00'\n                THEN 'eligible_for_async' = ANY(sawa.roles)\n                ELSE True\n            END as is_eligible_for_async,\n            CASE WHEN (NOW() AT TIME ZONE 'Europe/Madrid')::TIME BETWEEN '09:00:00' AND '18:00:00'\n                THEN 'eligible_for_callback' = ANY(sawa.roles)\n                ELSE False\n            END as has_role_callback, -- not used yet in SP yet but keep it for consistency\n            NOT sa.is_external_admin AS has_role_uce,\n            'en' = ANY(sa.spoken_languages) as speaks_english,\n            'es' = ANY(sa.spoken_languages) as speaks_spanish,\n            'ca' = ANY(sa.spoken_languages) as speaks_catalan\n        FROM support.support_agent as sa\n        JOIN support.support_agent_workspace_affectation sawa on sa.id = sawa.support_agent_id\n        WHERE sa.id = (:support_agent_id)  -- the admin requesting an assignment\n            AND sawa.intercom_workspace_id = (:workspace_id)\n    ),\n    uce_specialisations_mapping AS (\n        SELECT sa.intercom_admin_id\n        , ss.intercom_inbox_id\n        , sasms.score\n        FROM support_agents_for_computation sa\n        LEFT JOIN support.support_agent_workspace_affectation sawa ON sawa.support_agent_id = sa.support_agent_id AND sawa.intercom_workspace_id = (:workspace_id)\n        LEFT JOIN support.support_agent_spe_matching_score sasms ON sasms.support_agent_workspace_affectation_id = sawa.id\n        LEFT JOIN support.support_specialization ss ON ss.id = sasms.support_specialization_id\n        WHERE COALESCE(sasms.score, 0.2) != 0\n    ),\n    prefiltered_contact_request_backlog AS (\n        SELECT\n            DISTINCT ON (cr.intercom_conversation_id)\n            cr.id as contact_request_id,\n            cr.intercom_conversation_id,\n            cris.assigned_intercom_inbox_id as inbox_id,\n            cris.last_inbox_assignment_by,\n            cris.waiting_since,\n            greatest(cris.waiting_since, cris.last_admin_reply_at) AS time_reference_for_priority_score,\n            cris.last_admin_reply_at,\n            cr.source_type as type,\n            cram.recommended_admin_id,\n            cris.started_at,\n            cris.state,\n            cris.assigned_intercom_admin_id as admin_id,\n            cr.created_at,\n            -- using tags to compute properties of the conversations\n            CASE\n                WHEN tags.sla_tag = 'SLA[12h]' or tags.sla_tag = 'ASYNC' THEN 'SLA[12h]'\n                WHEN tags.sla_tag = 'SLA[5min]' or tags.sla_tag = 'SYNC' THEN 'SLA[5min]'\n                WHEN tags.sla_tag = 'SLA[30min]' THEN 'SLA[30min]'\n                WHEN cr.source_type = 'async_conversation_request' THEN 'SLA[12h]'\n                ELSE 'SLA[5min]'\n            END AS priority_level,\n            CASE\n                WHEN tags.workforce_level_tag = 'ROLE[LEVEL3]' THEN 3\n                WHEN tags.workforce_level_tag = 'ROLE[LEVEL2]' THEN 2\n                ELSE 1\n            END AS conversation_workforce_level,\n            tags.*\n        FROM support.contact_request cr\n        JOIN support.contact_request_intercom_state cris ON cr.id = cris.contact_request_id\n        LEFT JOIN support.contact_request_assignment_metadata cram ON cr.id = cram.contact_request_id\n        LEFT JOIN support.contact_request_tag crt ON cr.id = crt.contact_request_id\n        CROSS JOIN LATERAL (\n            SELECT\n                MAX(CASE WHEN crt.name = 'ROLE[SPANISH]' THEN crt.name END) AS spanish_tag,\n                MAX(CASE WHEN crt.name = 'ROLE[ENGLISH]' THEN crt.name END) AS english_tag,\n                MAX(CASE WHEN crt.name = 'ROLE[CATALAN]' THEN crt.name END) AS catalan_tag,\n                MAX(CASE WHEN crt.name IN ('SLA[5min]', 'SLA[30min]', 'SLA[12h]', 'SYNC', 'ASYNC') THEN crt.name END) as sla_tag,\n                MAX(CASE WHEN crt.name IN ('ROLE[LEVEL1]','ROLE[LEVEL2]','ROLE[LEVEL3]') THEN crt.name END) AS workforce_level_tag\n            FROM support.contact_request_tag crt\n            WHERE crt.contact_request_id = cr.id\n        ) tags\n        WHERE\n            cr.intercom_workspace_id = (:workspace_id)\n            AND cris.state = 'open'\n            AND (cris.assigned_intercom_admin_id IS NULL OR cris.assigned_intercom_admin_id = '') -- testing for empty string for records updated via flask admin\n        ORDER BY cr.intercom_conversation_id, cr.id, crt.notified_at DESC\n    ),\n    eligible_contact_request_backlog_with_priority_level AS (\n        SELECT\n            pcrb.contact_request_id,\n            pcrb.intercom_conversation_id,\n            pcrb.inbox_id,\n            pcrb.last_inbox_assignment_by,\n            pcrb.time_reference_for_priority_score,\n            pcrb.priority_level,\n            pcrb.conversation_workforce_level,\n            sa.level - pcrb.conversation_workforce_level AS distance_workforce_level_admin_conversation,\n            CASE WHEN pcrb.recommended_admin_id = sa.intercom_admin_id THEN (:member_matching_score_boost) ELSE 1 END AS uce_member_matching_score,\n            pcrb.sla_tag,\n            pcrb.started_at\n        FROM prefiltered_contact_request_backlog pcrb\n        CROSS JOIN support_agents_for_computation sa\n        WHERE sa.support_agent_id = (:support_agent_id) -- join only on the admin requesting an assignment\n            -- reduce scope for agents depending on their sync/async eligibility configuration\n            AND (\n                    (COALESCE(sa.is_eligible_for_sync, True) AND pcrb.priority_level = 'SLA[5min]')\n                    OR\n                    (COALESCE(sa.is_eligible_for_async, True) AND pcrb.priority_level != 'SLA[5min]')\n            )\n            -- reduce scope for agents not speaking English\n            AND (COALESCE(sa.speaks_english, True) OR pcrb.english_tag IS NULL)\n            -- reduce scope for agents not speaking Spanish\n            AND (COALESCE(sa.speaks_spanish, True) OR pcrb.spanish_tag IS NULL)\n            -- reduce scope for agents not speaking Catalan\n            AND (COALESCE(sa.speaks_catalan, True) OR pcrb.catalan_tag IS NULL)\n    ),\n    eligible_contact_request_backlog_with_context AS (\n        SELECT\n            ecrb.contact_request_id,\n            ecrb.intercom_conversation_id,\n            ecrb.priority_level,\n            sa.intercom_admin_id,\n            CASE\n                WHEN ecrb.priority_level = 'SLA[5min]'\n                    THEN (:sync_sla_score_boost) * (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (5 - 2)\n                WHEN ecrb.priority_level = 'SLA[30min]'\n                    THEN (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (30 - 2)\n                ELSE --  priority_level = 'SLA[12h]'\n                        (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (12 * 60 - 2)\n            END AS sla_score,\n            CASE\n                WHEN ecrb.priority_level = 'SLA[5min]'\n                    THEN count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 5\n                WHEN ecrb.priority_level = 'SLA[30min]'\n                    THEN count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 30\n                ELSE\n                    count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 12 * 60\n            END AS sla_breached,\n            distance_workforce_level_admin_conversation,\n            CASE\n                WHEN distance_workforce_level_admin_conversation = 0 THEN (:workforce_level_score_boost)\n                WHEN distance_workforce_level_admin_conversation < 0 THEN NULL\n                ELSE 1.0 / (distance_workforce_level_admin_conversation + 1)\n            END AS workforce_level_matching_score,\n            ecrb.inbox_id,\n            ss.intercom_inbox_name as inbox_name,\n            ecrb.last_inbox_assignment_by,\n            ecrb.uce_member_matching_score,\n            ecrb.time_reference_for_priority_score,\n            ecrb.sla_tag,\n            ecrb.conversation_workforce_level,\n            CASE WHEN ecrb.priority_level = 'SLA[5min]' AND sa.level < 3 THEN 2 ELSE 0 END as additional_power_weight_c\n        FROM eligible_contact_request_backlog_with_priority_level ecrb\n        INNER JOIN support.support_specialization ss on ss.intercom_inbox_id = ecrb.inbox_id AND ss.is_used_by_assigner\n        CROSS JOIN support_agents_for_computation sa\n        WHERE\n            sa.level - ecrb.conversation_workforce_level >= 0\n            AND CASE\n                    WHEN ecrb.priority_level = 'SLA[5min]' THEN COALESCE(ecrb.started_at < now() at time zone 'utc' - '30 seconds'::interval, TRUE)\n                    ELSE COALESCE(ecrb.started_at < now() at time zone 'utc' - '2 minutes'::interval, TRUE)\n                END\n    ),\n    eligible_convos_with_scores AS (\n        SELECT\n            ec.contact_request_id,\n            ec.intercom_conversation_id,\n            ec.inbox_id,\n            ec.inbox_name,\n            NULL AS reserved_for_external_agent_platform,\n            ec.time_reference_for_priority_score,\n            m.score AS expertise_matching_score,\n            m.score AS raw_expertise_matching_score,\n            POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c) AS priority_score,\n            POWER(workforce_level_matching_score, (:power_weight_d)) AS workforce_level_matching_score,\n            uce_member_matching_score,\n            COALESCE(\n                    power(m.score, ((:power_weight_a)))\n                    * POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)\n                    * POWER(workforce_level_matching_score, (:power_weight_d))\n                    * uce_member_matching_score\n                , 0) AS assignment_score,\n            sla_score,\n            sla_breached,\n            MAX(POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)) OVER () AS max_priority_score,\n            priority_level,\n            MAX(POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)) OVER (PARTITION BY priority_level) AS max_priority_score_per_priority_level,\n            null as n_rejections,\n            ec.sla_tag,\n            ec.priority_level = 'SLA[5min]' as is_sync_conversation,\n            ec.additional_power_weight_c\n        FROM eligible_contact_request_backlog_with_context ec\n        LEFT JOIN support.contact_request_assignment_event crae ON crae.contact_request_id = ec.contact_request_id AND crae.action = 'rejected' AND crae.intercom_admin_id = ec.intercom_admin_id\n        LEFT JOIN uce_specialisations_mapping m ON m.intercom_inbox_id = ec.inbox_id\n        WHERE crae.contact_request_id IS NULL\n        AND COALESCE(m.score, 0.2) >= 0\n    ),\n    backlog_stats AS MATERIALIZED (\n        SELECT\n            count(*) as n_assignable_convos,\n            count(*) FILTER (WHERE priority_level = 'SLA[12h]') as n_assignable_async,\n            count(*) FILTER (WHERE priority_level = 'SLA[5min]') as n_assignable_sync,\n            count(*) FILTER (WHERE priority_level = 'SLA[30min]') as n_assignable_callback,\n            count(*) FILTER (WHERE priority_level = 'SLA[12h]' AND sla_breached) as n_assignable_async_sla_breached,\n            count(*) FILTER (WHERE priority_level = 'SLA[5min]' AND sla_breached) as n_assignable_sync_sla_breached,\n            count(*) FILTER (WHERE priority_level = 'SLA[30min]' AND sla_breached) as n_assignable_callback_sla_breached,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[12h]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_12h,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[5min]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_5m,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[30min]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_30m,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[12h]' THEN expertise_matching_score END) AS q90_expertise_score_sla_12h,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[5min]' THEN expertise_matching_score END) AS q90_expertise_score_sla_5m,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[30min]' THEN expertise_matching_score END) AS q90_expertise_score_sla_30m\n        FROM eligible_convos_with_scores\n    )\n    SELECT *, 1.0 AS shift_matching_score\n    FROM eligible_convos_with_scores\n    CROSS JOIN backlog_stats stats\n    ORDER BY assignment_score DESC NULLS LAST, time_reference_for_priority_score\n    LIMIT 10;\n    "

ES_RANKED_SQL_QUERY `module-attribute` ¶

ES_RANKED_SQL_QUERY = "\n    WITH support_agents_for_computation AS (\n        SELECT\n            sa.id as support_agent_id,\n            sa.intercom_admin_id,\n            sawa.level,\n            sawa.intercom_workspace_id,\n            --Roles are now stored in an array, so we check for the presence of a role in the array.\n            'eligible_for_sync' = ANY(sawa.roles) as is_eligible_for_sync,\n            CASE WHEN (NOW() AT TIME ZONE 'Europe/Madrid')::TIME BETWEEN '09:00:00' AND '18:00:00'\n                THEN 'eligible_for_async' = ANY(sawa.roles)\n                ELSE True\n            END as is_eligible_for_async,\n            CASE WHEN (NOW() AT TIME ZONE 'Europe/Madrid')::TIME BETWEEN '09:00:00' AND '18:00:00'\n                THEN 'eligible_for_callback' = ANY(sawa.roles)\n                ELSE False\n            END as has_role_callback, -- not used yet in SP yet but keep it for consistency\n            NOT sa.is_external_admin AS has_role_uce,\n            'en' = ANY(sa.spoken_languages) as speaks_english,\n            'es' = ANY(sa.spoken_languages) as speaks_spanish,\n            'ca' = ANY(sa.spoken_languages) as speaks_catalan\n        FROM support.support_agent as sa\n        JOIN support.support_agent_workspace_affectation sawa on sa.id = sawa.support_agent_id\n        WHERE sa.id = (:support_agent_id)  -- the admin requesting an assignment\n            AND sawa.intercom_workspace_id = (:workspace_id)\n    ),\n    uce_specialisations_mapping AS (\n        SELECT sa.intercom_admin_id\n        , ss.intercom_inbox_id\n        , sasms.score\n        FROM support_agents_for_computation sa\n        LEFT JOIN support.support_agent_workspace_affectation sawa ON sawa.support_agent_id = sa.support_agent_id AND sawa.intercom_workspace_id = (:workspace_id)\n        LEFT JOIN support.support_agent_spe_matching_score sasms ON sasms.support_agent_workspace_affectation_id = sawa.id\n        LEFT JOIN support.support_specialization ss ON ss.id = sasms.support_specialization_id\n        WHERE COALESCE(sasms.score, 0.2) != 0\n    ),\n    prefiltered_contact_request_backlog AS (\n        SELECT\n            DISTINCT ON (cr.intercom_conversation_id)\n            cr.id as contact_request_id,\n            cr.intercom_conversation_id,\n            cris.assigned_intercom_inbox_id as inbox_id,\n            cris.last_inbox_assignment_by,\n            cris.waiting_since,\n            greatest(cris.waiting_since, cris.last_admin_reply_at) AS time_reference_for_priority_score,\n            cris.last_admin_reply_at,\n            cr.source_type as type,\n            cram.recommended_admin_id,\n            cris.started_at,\n            cris.state,\n            cris.assigned_intercom_admin_id as admin_id,\n            cr.created_at,\n            -- using tags to compute properties of the conversations\n            CASE\n                WHEN tags.sla_tag = 'SLA[12h]' or tags.sla_tag = 'ASYNC' THEN 'SLA[12h]'\n                WHEN tags.sla_tag = 'SLA[5min]' or tags.sla_tag = 'SYNC' THEN 'SLA[5min]'\n                WHEN tags.sla_tag = 'SLA[30min]' THEN 'SLA[30min]'\n                WHEN cr.source_type = 'async_conversation_request' THEN 'SLA[12h]'\n                ELSE 'SLA[5min]'\n            END AS priority_level,\n            CASE\n                WHEN tags.workforce_level_tag = 'ROLE[LEVEL3]' THEN 3\n                WHEN tags.workforce_level_tag = 'ROLE[LEVEL2]' THEN 2\n                ELSE 1\n            END AS conversation_workforce_level,\n            tags.*\n        FROM support.contact_request cr\n        JOIN support.contact_request_intercom_state cris ON cr.id = cris.contact_request_id\n        LEFT JOIN support.contact_request_assignment_metadata cram ON cr.id = cram.contact_request_id\n        LEFT JOIN support.contact_request_tag crt ON cr.id = crt.contact_request_id\n        CROSS JOIN LATERAL (\n            SELECT\n                MAX(CASE WHEN crt.name = 'ROLE[SPANISH]' THEN crt.name END) AS spanish_tag,\n                MAX(CASE WHEN crt.name = 'ROLE[ENGLISH]' THEN crt.name END) AS english_tag,\n                MAX(CASE WHEN crt.name = 'ROLE[CATALAN]' THEN crt.name END) AS catalan_tag,\n                MAX(CASE WHEN crt.name IN ('SLA[5min]', 'SLA[30min]', 'SLA[12h]', 'SYNC', 'ASYNC') THEN crt.name END) as sla_tag,\n                MAX(CASE WHEN crt.name IN ('ROLE[LEVEL1]','ROLE[LEVEL2]','ROLE[LEVEL3]') THEN crt.name END) AS workforce_level_tag\n            FROM support.contact_request_tag crt\n            WHERE crt.contact_request_id = cr.id\n        ) tags\n        WHERE\n            cr.intercom_workspace_id = (:workspace_id)\n            AND cris.state = 'open'\n            AND (cris.assigned_intercom_admin_id IS NULL OR cris.assigned_intercom_admin_id = '') -- testing for empty string for records updated via flask admin\n        ORDER BY cr.intercom_conversation_id, cr.id, crt.notified_at DESC\n    ),\n    eligible_contact_request_backlog_with_priority_level AS (\n        SELECT\n            pcrb.contact_request_id,\n            pcrb.intercom_conversation_id,\n            pcrb.inbox_id,\n            pcrb.last_inbox_assignment_by,\n            pcrb.time_reference_for_priority_score,\n            pcrb.priority_level,\n            pcrb.conversation_workforce_level,\n            sa.level - pcrb.conversation_workforce_level AS distance_workforce_level_admin_conversation,\n            CASE WHEN pcrb.recommended_admin_id = sa.intercom_admin_id THEN (:member_matching_score_boost) ELSE 1 END AS uce_member_matching_score,\n            pcrb.sla_tag,\n            pcrb.started_at\n        FROM prefiltered_contact_request_backlog pcrb\n        CROSS JOIN support_agents_for_computation sa\n        WHERE sa.support_agent_id = (:support_agent_id) -- join only on the admin requesting an assignment\n            -- reduce scope for agents depending on their sync/async eligibility configuration\n            AND (\n                    (COALESCE(sa.is_eligible_for_sync, True) AND pcrb.priority_level = 'SLA[5min]')\n                    OR\n                    (COALESCE(sa.is_eligible_for_async, True) AND pcrb.priority_level != 'SLA[5min]')\n            )\n            -- reduce scope for agents not speaking English\n            AND (COALESCE(sa.speaks_english, True) OR pcrb.english_tag IS NULL)\n            -- reduce scope for agents not speaking Spanish\n            AND (COALESCE(sa.speaks_spanish, True) OR pcrb.spanish_tag IS NULL)\n            -- reduce scope for agents not speaking Catalan\n            AND (COALESCE(sa.speaks_catalan, True) OR pcrb.catalan_tag IS NULL)\n    ),\n    eligible_contact_request_backlog_with_context AS (\n        SELECT\n            ecrb.contact_request_id,\n            ecrb.intercom_conversation_id,\n            ecrb.priority_level,\n            sa.intercom_admin_id,\n            CASE\n                WHEN ecrb.priority_level = 'SLA[5min]'\n                    THEN (:sync_sla_score_boost) * (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (5 - 2)\n                WHEN ecrb.priority_level = 'SLA[30min]'\n                    THEN (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (30 - 2)\n                ELSE --  priority_level = 'SLA[12h]'\n                        (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (12 * 60 - 2)\n            END AS sla_score,\n            CASE\n                WHEN ecrb.priority_level = 'SLA[5min]'\n                    THEN count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 5\n                WHEN ecrb.priority_level = 'SLA[30min]'\n                    THEN count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 30\n                ELSE\n                    count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 12 * 60\n            END AS sla_breached,\n            distance_workforce_level_admin_conversation,\n            CASE\n                WHEN distance_workforce_level_admin_conversation = 0 THEN (:workforce_level_score_boost)\n                WHEN distance_workforce_level_admin_conversation < 0 THEN NULL\n                ELSE 1.0 / (distance_workforce_level_admin_conversation + 1)\n            END AS workforce_level_matching_score,\n            ecrb.inbox_id,\n            ss.intercom_inbox_name as inbox_name,\n            ecrb.last_inbox_assignment_by,\n            ecrb.uce_member_matching_score,\n            ecrb.time_reference_for_priority_score,\n            ecrb.sla_tag,\n            ecrb.conversation_workforce_level,\n            CASE WHEN ecrb.priority_level = 'SLA[5min]' AND sa.level < 3 THEN 2 ELSE 0 END as additional_power_weight_c\n        FROM eligible_contact_request_backlog_with_priority_level ecrb\n        INNER JOIN support.support_specialization ss on ss.intercom_inbox_id = ecrb.inbox_id AND ss.is_used_by_assigner\n        CROSS JOIN support_agents_for_computation sa\n        WHERE\n            sa.level - ecrb.conversation_workforce_level >= 0\n            AND CASE\n                    WHEN ecrb.priority_level = 'SLA[5min]' THEN COALESCE(ecrb.started_at < now() at time zone 'utc' - '30 seconds'::interval, TRUE)\n                    ELSE COALESCE(ecrb.started_at < now() at time zone 'utc' - '2 minutes'::interval, TRUE)\n                END\n    ),\n    eligible_convos_with_scores AS (\n        SELECT\n            ec.contact_request_id,\n            ec.intercom_conversation_id,\n            ec.inbox_id,\n            ec.inbox_name,\n            NULL AS reserved_for_external_agent_platform,\n            ec.time_reference_for_priority_score,\n            m.score AS expertise_matching_score,\n            m.score AS raw_expertise_matching_score,\n            POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c) AS priority_score,\n            POWER(workforce_level_matching_score, (:power_weight_d)) AS workforce_level_matching_score,\n            uce_member_matching_score,\n            COALESCE(\n                    power(m.score, ((:power_weight_a)))\n                    * POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)\n                    * POWER(workforce_level_matching_score, (:power_weight_d))\n                    * uce_member_matching_score\n                , 0) AS assignment_score,\n            sla_score,\n            sla_breached,\n            MAX(POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)) OVER () AS max_priority_score,\n            priority_level,\n            MAX(POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)) OVER (PARTITION BY priority_level) AS max_priority_score_per_priority_level,\n            null as n_rejections,\n            ec.sla_tag,\n            ec.priority_level = 'SLA[5min]' as is_sync_conversation,\n            ec.additional_power_weight_c\n        FROM eligible_contact_request_backlog_with_context ec\n        LEFT JOIN support.contact_request_assignment_event crae ON crae.contact_request_id = ec.contact_request_id AND crae.action = 'rejected' AND crae.intercom_admin_id = ec.intercom_admin_id\n        LEFT JOIN uce_specialisations_mapping m ON m.intercom_inbox_id = ec.inbox_id\n        WHERE crae.contact_request_id IS NULL\n        AND COALESCE(m.score, 0.2) >= 0\n    ),\n    backlog_stats AS MATERIALIZED (\n        SELECT\n            count(*) as n_assignable_convos,\n            count(*) FILTER (WHERE priority_level = 'SLA[12h]') as n_assignable_async,\n            count(*) FILTER (WHERE priority_level = 'SLA[5min]') as n_assignable_sync,\n            count(*) FILTER (WHERE priority_level = 'SLA[30min]') as n_assignable_callback,\n            count(*) FILTER (WHERE priority_level = 'SLA[12h]' AND sla_breached) as n_assignable_async_sla_breached,\n            count(*) FILTER (WHERE priority_level = 'SLA[5min]' AND sla_breached) as n_assignable_sync_sla_breached,\n            count(*) FILTER (WHERE priority_level = 'SLA[30min]' AND sla_breached) as n_assignable_callback_sla_breached,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[12h]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_12h,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[5min]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_5m,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[30min]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_30m,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[12h]' THEN expertise_matching_score END) AS q90_expertise_score_sla_12h,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[5min]' THEN expertise_matching_score END) AS q90_expertise_score_sla_5m,\n            percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[30min]' THEN expertise_matching_score END) AS q90_expertise_score_sla_30m\n        FROM eligible_convos_with_scores\n    )\n    SELECT\n        row_number() OVER (ORDER BY assignment_score DESC NULLS LAST, time_reference_for_priority_score) AS assignment_rank\n        , (:support_agent_id)\n        , *\n        , row_number() OVER (ORDER BY intercom_conversation_id = (:conversation_id) DESC, assignment_score DESC NULLS LAST, time_reference_for_priority_score) AS row_nb\n    FROM eligible_convos_with_scores\n    CROSS JOIN backlog_stats stats\n    ORDER BY row_nb, assignment_score DESC NULLS LAST, time_reference_for_priority_score\n    LIMIT 10;\n    "

FR_ASSIGNER_SQL_QUERY `module-attribute` ¶

FR_ASSIGNER_SQL_QUERY = "\n    -- This query is a translation of the original GET_CONVERSATION_BY_ASSIGNMENT_SCORE_EU_SQL\n    -- from the FR resolution_platform to the global support component.\n    -- Major changes include:\n    -- - Replacing FR tables with global support tables (e.g., care_conversation_backlog -> contact_request_intercom_state).\n    -- - Adapting to new schemas (e.g., support_agent roles are now in an array).\n    -- - Removing the old_spe_new_spe_mapping CTE as it is no longer needed.\n    WITH recalibrated_support_agents AS (\n        SELECT\n            sa.id\n        FROM support.assigner_recalibration ar\n        JOIN support.support_agent sa ON ar.alan_email = sa.alan_email\n        JOIN support.support_agent_workspace_affectation sawa ON sa.id = sawa.support_agent_id AND ar.intercom_workspace_id = sawa.intercom_workspace_id\n        WHERE ar.intercom_workspace_id = (:workspace_id)\n    ),\n    support_agents_for_computation AS (\n        SELECT\n            sa.id as support_agent_id\n            , sa.intercom_admin_id\n            , sawa.level\n            , sawa.intercom_workspace_id\n            --Roles are now stored in an array, so we check for the presence of a role in the array.\n            , 'eligible_for_sync' = ANY(sawa.roles) as is_eligible_for_sync\n            , CASE WHEN (NOW() AT TIME ZONE 'Europe/Paris')::TIME BETWEEN '09:00:00' AND '19:00:00'\n                THEN 'eligible_for_async' = ANY(sawa.roles)\n                ELSE True\n                END as is_eligible_for_async\n            , sa.platform_name as platform\n            , NOT sa.is_external_admin AS has_role_uce\n            , CASE WHEN (NOW() AT TIME ZONE 'Europe/Paris')::TIME BETWEEN '09:00:00' AND '19:00:00'\n                THEN 'eligible_for_callback' = ANY(sawa.roles)\n                ELSE False\n                END as has_role_callback\n            , 'automated_answer_reviewer' = ANY(sawa.roles) as has_reviewer_role\n            , 'en' = ANY(sa.spoken_languages) as speaks_english\n            , sa.work_location != 'fr' as is_offshore\n        FROM support.support_agent as sa\n        JOIN support.support_agent_workspace_affectation sawa ON sa.id = sawa.support_agent_id AND sawa.intercom_workspace_id = (:workspace_id)\n        WHERE (sa.id = ANY((select array(SELECT id FROM recalibrated_support_agents))::uuid[])\n            OR sa.id = (:support_agent_id))  -- the admin requesting an assignment\n    ),\n    uce_specialisations_mapping AS (\n        SELECT sa.intercom_admin_id\n            , ss.intercom_inbox_id\n            , sasms.score\n        FROM support_agents_for_computation sa\n        LEFT JOIN support.support_agent_workspace_affectation sawa ON sawa.support_agent_id = sa.support_agent_id\n        LEFT JOIN support.support_agent_spe_matching_score sasms ON sasms.support_agent_workspace_affectation_id = sawa.id\n        LEFT JOIN support.support_specialization ss ON ss.id = sasms.support_specialization_id\n        WHERE sasms.score != 0\n    ),\n    prefiltered_contact_request_backlog AS (\n        SELECT\n            DISTINCT ON (cr.intercom_conversation_id)\n            cr.id as contact_request_id\n            , cr.intercom_conversation_id\n            , cris.assigned_intercom_inbox_id as inbox_id\n            , cris.last_inbox_assignment_by\n            , cris.waiting_since\n            , greatest(cris.waiting_since, cris.last_admin_reply_at) AS time_reference_for_priority_score\n            , cris.last_admin_reply_at\n            , cram.recommended_admin_id as recommended_admin_id\n            , cris.started_at\n            , COALESCE(cram.reserved_for_automated_answers_review, FALSE) as reserved_for_review\n            , cris.state\n            , cris.assigned_intercom_admin_id as admin_id\n            , cr.created_at\n            -- using tags to compute properties of the conversations\n            , SUBSTRING(tags.platform_tag, 'ROLE\\[(.*)\\]') AS reserved_for_external_agent_platform\n            -- priority level is defined by the sync/async nature of the convo but can be overwritten during a handover\n            , CASE\n                WHEN tags.sla_tag = 'SLA[12h]' THEN 'SLA[12h]'\n                WHEN tags.sla_tag = 'SLA[5min]' THEN 'SLA[5min]'\n                WHEN tags.sla_tag = 'SLA[30min]' OR tags.callback_tag = 'ROLE[CALLBACK]' THEN 'SLA[30min]'\n                -- if not sync tagged or if one of the hotline source type → mark async.\n                WHEN tags.type_tag != 'SYNC' or cr.source_type in ('immediate_callback_request', 'hotline_request')THEN 'SLA[12h]'\n                ELSE 'SLA[5min]'\n                END AS priority_level\n            -- workforce level of the conversation\n            , CASE\n                WHEN tags.workforce_level_tag = 'ROLE[LEVEL3]' OR tags.uce_reserved_tag IS NOT NULL THEN 3\n                WHEN tags.workforce_level_tag = 'ROLE[LEVEL2]' THEN 2\n                ELSE 1\n                END AS conversation_workforce_level\n            , tags.*\n        FROM support.contact_request cr\n        JOIN support.contact_request_intercom_state cris ON cr.id = cris.contact_request_id\n        LEFT JOIN support.contact_request_assignment_metadata cram ON cr.id = cram.contact_request_id\n        LEFT JOIN support.contact_request_tag crt ON cr.id = crt.contact_request_id -- to define priority level\n        CROSS JOIN LATERAL (\n            SELECT\n                MAX(CASE WHEN crt.name IN ('ROLE[UCE]', 'COMPLEX CLAIM', 'SENSITIVE') THEN crt.name END) AS uce_reserved_tag\n                , MAX(CASE WHEN crt.name = 'ROLE[CALLBACK]' THEN crt.name END) AS callback_tag\n                , MAX(CASE WHEN crt.name IN ('ROLE[WEBHELP]', 'ROLE[ONEPILOT]') THEN crt.name END) AS platform_tag\n                , MAX(CASE WHEN crt.name = 'ROLE[ENGLISH]' THEN crt.name END) AS english_tag\n                , MAX(CASE WHEN crt.name = 'ROLE[GEORESTRICTED]' THEN crt.name END) AS geo_tag\n                , MAX(CASE WHEN crt.name IN ('ROLE[LEVEL1]','ROLE[LEVEL2]','ROLE[LEVEL3]') THEN crt.name END) AS workforce_level_tag\n                , MAX(CASE WHEN crt.name IN ('bypass-assigner', 'Abusive Conversation') THEN crt.name END) AS bypass_tag\n                , MAX(CASE WHEN crt.name IN ('SLA[12h]', 'SLA[5min]', 'SLA[30min]') THEN crt.name END) as sla_tag\n                , MAX(CASE WHEN crt.name IN ('SYNC', 'ASYNC') THEN crt.name END) as type_tag\n                , MAX(CASE WHEN crt.name IN ('bypass-classification') THEN crt.name END) AS classification_bypass_tag\n            FROM support.contact_request_tag crt\n            WHERE crt.contact_request_id = cr.id\n        ) tags\n        WHERE\n            cr.intercom_workspace_id = (:workspace_id)\n            AND cris.state = 'open'\n            AND (cris.assigned_intercom_admin_id IS NULL OR cris.assigned_intercom_admin_id = '') -- testing for empty string for records updated via flask admin\n            AND cr.created_at > '2021-11-26' -- until data is cleared in prod\n            AND tags.bypass_tag IS NULL -- exclude convos explicitly flagged as bypassing the Assigner\n        ORDER BY cr.intercom_conversation_id, cr.id, crt.notified_at DESC\n    ),\n    eligible_contact_request_backlog_with_priority_level AS (\n        SELECT\n            pcrb.contact_request_id\n            , pcrb.intercom_conversation_id\n            , pcrb.inbox_id\n            , pcrb.last_inbox_assignment_by\n            , pcrb.time_reference_for_priority_score\n            , pcrb.priority_level\n            , pcrb.conversation_workforce_level\n            , sa.level - pcrb.conversation_workforce_level AS distance_workforce_level_admin_conversation\n            , CASE WHEN pcrb.recommended_admin_id = sa.intercom_admin_id\n                THEN (:member_matching_score_boost)\n                ELSE 1\n                END AS uce_member_matching_score\n            , pcrb.started_at\n            , pcrb.reserved_for_external_agent_platform\n            , pcrb.reserved_for_review\n            , pcrb.classification_bypass_tag IS NOT NULL as bypassed_classifier\n        FROM prefiltered_contact_request_backlog pcrb\n        CROSS JOIN support_agents_for_computation sa\n        WHERE sa.support_agent_id = (:support_agent_id) -- join only on the admin requesting an assignment\n            -- reduce scope for agents depending on their sync/async eligibility configuration\n            AND (\n                -- Admin is ELIGIBLE_FOR_SYNC and conversation is NOT ASYNC and NOT CALLBACK\n                (COALESCE(sa.is_eligible_for_sync, True) AND pcrb.priority_level = 'SLA[5min]' AND pcrb.callback_tag IS NULL)\n                OR\n                -- Admin is ELIGIBLE_FOR_ASYNC and conversation is NOT SLA[5min] and NOT CALLBACK\n                (COALESCE(sa.is_eligible_for_async, True) AND pcrb.priority_level != 'SLA[5min]' AND pcrb.callback_tag IS NULL)\n                OR\n                -- Admin has CALLBACK role and conversation is CALLBACK\n                (COALESCE(sa.has_role_callback, True) AND pcrb.callback_tag IS NOT NULL)\n            )\n            -- assign automated answer reviews for reviewers\n            AND (COALESCE(sa.has_reviewer_role, False) OR pcrb.reserved_for_review IS NOT TRUE)\n            -- reduce scope for agents not speaking english\n            AND (COALESCE(sa.speaks_english, True) OR pcrb.english_tag IS NULL)\n            -- reduce scope of georestricted convos for offshore agents\n            AND (COALESCE(NOT sa.is_offshore, False) OR pcrb.geo_tag IS NULL)\n            AND CASE\n                    WHEN NOT pcrb.reserved_for_review THEN sa.level - pcrb.conversation_workforce_level >= 0\n                    ELSE TRUE\n                END\n                -- handle conversations reserved for specific external agent platforms\n                AND CASE\n                        WHEN pcrb.reserved_for_external_agent_platform IS NOT NULL\n                        THEN UPPER(sa.platform) = UPPER(pcrb.reserved_for_external_agent_platform)\n                        ELSE TRUE\n                END\n    ),\n\n    contact_request_context_digest AS (\n        SELECT\n            cr.id as contact_request_id\n            , cr.duplicated_from_id as original_contact_request_id\n            , COALESCE(cr.classification_result->'raw_prediction_classes', cr.classification_result->'prediction'->'raw_prediction_classes' ) as raw_prediction_classes\n            , COALESCE(cr.classification_result->'raw_prediction', cr.classification_result->'prediction'->'raw_prediction') as raw_prediction\n            , created_at\n        FROM support.contact_request cr\n        INNER JOIN eligible_contact_request_backlog_with_priority_level e ON e.contact_request_id = cr.id\n        WHERE cr.intercom_workspace_id = (:workspace_id)\n    ),\n    classifier_classes AS (\n        SELECT\n            raw_prediction_classes AS classes\n        FROM contact_request_context_digest\n        WHERE jsonb_typeof(raw_prediction) = 'array'\n        ORDER BY created_at DESC\n        LIMIT 1\n    ),\n    classifier_classes_as_array AS (\n        SELECT ARRAY_AGG(cls) classes_array\n        FROM classifier_classes, JSONB_ARRAY_ELEMENTS_TEXT(classifier_classes.classes) cls\n    ),\n\n    -- # 1. define the eligible backlog for the UCE requesting an assignment\n    eligible_contact_request_backlog_with_context AS (\n        SELECT\n            ecrb.contact_request_id\n            , ecrb.intercom_conversation_id\n            , ecrb.priority_level\n            -- 1. SLA score = (1 min + waiting_time in min) / (1 min + SLA in min)\n            , CASE\n                WHEN ecrb.priority_level = 'SLA[5min]'\n                    THEN (:sync_sla_score_boost) * (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (5 - 2)\n                WHEN ecrb.priority_level = 'SLA[30min]'\n                    THEN (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (30 - 2)\n                ELSE --  priority_level = 'SLA[12h]'\n                        (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (12 * 60 - 2)\n                END AS sla_score\n            , CASE\n                WHEN ecrb.priority_level = 'SLA[5min]'\n                    THEN count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 5\n                WHEN ecrb.priority_level = 'SLA[30min]'\n                    THEN count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 30\n                ELSE\n                    count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 12 * 60\n                END AS sla_breached\n            -- 2. workforce level matching score\n            , distance_workforce_level_admin_conversation\n            -- the workforce_level_score_boost is here to give a higher assignment score when the level of the conversation matches the level of the admin\n            -- workforce_level_matching_score doesn't matter the escalation reviews of automated answers\n            , CASE\n                WHEN distance_workforce_level_admin_conversation = 0 THEN (:workforce_level_score_boost)\n                WHEN ecrb.reserved_for_review THEN 1.0\n                WHEN distance_workforce_level_admin_conversation < 0 THEN NULL\n                ELSE 1.0 / (distance_workforce_level_admin_conversation + 1)\n                END AS workforce_level_matching_score\n            -- 3. parsing of classification result (to be used later for the expertise matching score computation)\n            , CASE WHEN jsonb_typeof(COALESCE(crcd.raw_prediction, crcd2.raw_prediction)) = 'array'\n                THEN COALESCE(crcd.raw_prediction, crcd2.raw_prediction)\n                ELSE array_to_json(array_fill(1.0 / jsonb_array_length(cls.classes) , ARRAY[jsonb_array_length(cls.classes)]))::jsonb\n                END AS predicted_probabilities\n            , CASE WHEN jsonb_typeof(COALESCE(crcd.raw_prediction_classes, crcd2.raw_prediction_classes)) = 'array'\n                THEN COALESCE(crcd.raw_prediction_classes, crcd2.raw_prediction_classes)\n                ELSE cls.classes\n                END AS predicted_classes\n            , ecrb.inbox_id\n            , ss.intercom_inbox_name as inbox_name\n            , ecrb.last_inbox_assignment_by\n            , ecrb.uce_member_matching_score\n            , ecrb.time_reference_for_priority_score\n            , ecrb.reserved_for_external_agent_platform\n            , ecrb.conversation_workforce_level\n            , ecrb.reserved_for_review\n            , ecrb.bypassed_classifier\n        FROM eligible_contact_request_backlog_with_priority_level ecrb\n        INNER JOIN support.support_specialization ss ON ss.intercom_inbox_id = ecrb.inbox_id AND ss.is_used_by_assigner AND ss.intercom_workspace_id = (:workspace_id) -- only consider inboxes recognized by the assigner\n        LEFT JOIN contact_request_context_digest crcd ON crcd.contact_request_id = ecrb.contact_request_id\n        LEFT JOIN contact_request_context_digest crcd2 ON crcd.original_contact_request_id = crcd2.contact_request_id  -- inherit context from parent convo when relevant\n        CROSS JOIN classifier_classes cls\n        -- we want to prevent assignments of tickets not yet classified, but don't want to wait more than 30 seconds for sync and 2 minutes for the rest\n        WHERE jsonb_typeof(COALESCE(crcd.raw_prediction, crcd2.raw_prediction)) = 'array'\n            OR CASE\n                    WHEN ecrb.priority_level = 'SLA[5min]' THEN COALESCE(ecrb.started_at < now() at time zone 'utc' - '30 seconds'::interval, TRUE)\n                    ELSE COALESCE(ecrb.started_at < now() at time zone 'utc' - '2 minutes'::interval, TRUE)\n                END\n    ),\n    -- # 2. compute expertise matching scores for all (eligible convo X UCE) pairs\n    convos_x_probabilities AS (\n        -- expand the eligible convos on all predicted classes\n        SELECT\n            s.*\n            , s._predicted_class AS predicted_class\n        FROM (\n            SELECT\n                contact_request_id\n                , intercom_conversation_id\n                , priority_level\n                , sla_score\n                , sla_breached\n                , conversation_workforce_level\n                , workforce_level_matching_score\n                , uce_member_matching_score\n                , jsonb_array_elements(predicted_probabilities)::numeric AS predicted_probability\n                , trim(jsonb_array_elements(predicted_classes)::text, '\"') AS _predicted_class\n                , last_inbox_assignment_by\n                , inbox_id\n                , inbox_name\n                , time_reference_for_priority_score\n                , reserved_for_external_agent_platform\n                , reserved_for_review\n                , bypassed_classifier\n            FROM eligible_contact_request_backlog_with_context\n        ) s\n        UNION ALL\n        -- expand for classes unknown by the classifier as well\n        SELECT\n            contact_request_id\n            , intercom_conversation_id\n            , priority_level\n            , sla_score\n            , sla_breached\n            , conversation_workforce_level\n            , workforce_level_matching_score\n            , uce_member_matching_score\n            , 0 AS predicted_probability\n            , t.inbox_unknown_by_classifier AS _predicted_class\n            , last_inbox_assignment_by\n            , inbox_id\n            , inbox_name\n            , time_reference_for_priority_score\n            , reserved_for_external_agent_platform\n            , reserved_for_review\n            , bypassed_classifier\n            , t.inbox_unknown_by_classifier AS predicted_class\n        FROM eligible_contact_request_backlog_with_context\n        CROSS JOIN (\n                SELECT intercom_inbox_name AS inbox_unknown_by_classifier FROM support.support_specialization\n                EXCEPT\n                SELECT JSONB_ARRAY_ELEMENTS_TEXT(classes) FROM classifier_classes\n        ) t\n    ),\n    -- Calibration pass 1: Compute min/max across ALL agents for calibration (lightweight aggregation)\n    calibration_min_max_per_agent AS (\n        SELECT\n            sa.support_agent_id\n            , cxp.contact_request_id\n            , sum((CASE\n                    WHEN (cxp.last_inbox_assignment_by IN ('manual', 'intercom') OR cxp.bypassed_classifier OR NOT cxp.inbox_name = ANY(cls.classes_array))\n                        AND cxp.inbox_id = ss.intercom_inbox_id THEN 1.0\n                    WHEN (cxp.last_inbox_assignment_by IN ('manual', 'intercom') OR cxp.bypassed_classifier OR NOT cxp.inbox_name = ANY(cls.classes_array))\n                        AND cxp.inbox_id != ss.intercom_inbox_id THEN 0.0\n                    ELSE cxp.predicted_probability::numeric\n                END)\n                * COALESCE(m.score, 0)) AS raw_expertise_matching_score\n        FROM convos_x_probabilities cxp\n        JOIN support.support_specialization ss ON cxp.predicted_class = ss.intercom_inbox_name\n        CROSS JOIN support_agents_for_computation sa\n        LEFT JOIN uce_specialisations_mapping m ON m.intercom_inbox_id = ss.intercom_inbox_id AND m.intercom_admin_id = sa.intercom_admin_id\n        CROSS JOIN classifier_classes_as_array cls\n        WHERE\n            CASE\n                WHEN NOT cxp.reserved_for_review THEN sa.level - cxp.conversation_workforce_level >= 0\n                ELSE TRUE\n            END\n            AND CASE\n                    WHEN cxp.reserved_for_external_agent_platform IS NOT NULL\n                    THEN UPPER(sa.platform) = UPPER(cxp.reserved_for_external_agent_platform)\n                    ELSE TRUE\n            END\n        GROUP BY sa.support_agent_id, cxp.contact_request_id\n    ),\n    min_max_expertise_matching_score_per_convo AS (\n        SELECT\n            contact_request_id\n            , min(power(raw_expertise_matching_score, (:power_weight_a))) AS min_ems\n            , max(power(raw_expertise_matching_score, (:power_weight_a))) AS max_ems\n        FROM calibration_min_max_per_agent\n        GROUP BY contact_request_id\n    ),\n    -- calibration pass 2: Compute detailed scores ONLY for the requesting agent which is less costly\n    eligible_convos_with_expertise_matching_score_per_uce AS (\n        SELECT\n            sa.intercom_admin_id\n            , sa.support_agent_id\n            , cxp.intercom_conversation_id\n            , cxp.contact_request_id\n            , cxp.inbox_id\n            , cxp.inbox_name\n            , cxp.priority_level\n            , cxp.workforce_level_matching_score\n            , cxp.sla_score\n            , cxp.sla_breached\n            , cxp.uce_member_matching_score\n            , cxp.time_reference_for_priority_score\n            , cxp.reserved_for_external_agent_platform\n            , cxp.reserved_for_review\n            , sum((CASE\n                    WHEN (cxp.last_inbox_assignment_by IN ('manual', 'intercom') OR cxp.bypassed_classifier OR NOT cxp.inbox_name = ANY(cls.classes_array))\n                        AND cxp.inbox_id = ss.intercom_inbox_id THEN 1.0\n                    WHEN (cxp.last_inbox_assignment_by IN ('manual', 'intercom') OR cxp.bypassed_classifier OR NOT cxp.inbox_name = ANY(cls.classes_array))\n                        AND cxp.inbox_id != ss.intercom_inbox_id THEN 0.0\n                    ELSE cxp.predicted_probability::numeric\n                END)\n                * COALESCE(m.score, 0)) AS raw_expertise_matching_score\n            -- L1 & L2 agents have an extra boost on priority_level for sync conversations\n            , case when cxp.priority_level = 'SLA[5min]' and sa.level < 3 then 2 else 0 end as additional_power_weight_c\n        FROM convos_x_probabilities cxp\n        JOIN support.support_specialization ss ON cxp.predicted_class = ss.intercom_inbox_name\n        CROSS JOIN support_agents_for_computation sa\n        LEFT JOIN uce_specialisations_mapping m ON m.intercom_inbox_id = ss.intercom_inbox_id AND m.intercom_admin_id = sa.intercom_admin_id\n        CROSS JOIN classifier_classes_as_array cls\n        -- additional exclusion conditions for both the care expert asking for the assignment and the calibration cohort\n        WHERE\n            sa.support_agent_id = (:support_agent_id)::uuid  -- ONLY requesting agent\n            AND CASE\n                WHEN NOT cxp.reserved_for_review THEN sa.level - cxp.conversation_workforce_level >= 0\n                ELSE TRUE\n            END\n            -- handle conversations reserved for specific external agent platforms\n            AND CASE\n                    WHEN cxp.reserved_for_external_agent_platform IS NOT NULL\n                    THEN UPPER(sa.platform) = UPPER(cxp.reserved_for_external_agent_platform)\n                    ELSE TRUE\n            END\n        GROUP BY sa.intercom_admin_id, sa.support_agent_id, cxp.contact_request_id, cxp.inbox_id, cxp.inbox_name, cxp.priority_level, cxp.workforce_level_matching_score, cxp.sla_score, cxp.sla_breached, cxp.uce_member_matching_score,\n    cxp.time_reference_for_priority_score, cxp.reserved_for_external_agent_platform, cxp.reserved_for_review, cxp.intercom_conversation_id, sa.level\n    ),\n    eligible_convos_with_scores AS (\n        SELECT\n            ec.contact_request_id\n            , ec.intercom_conversation_id\n            , ec.inbox_id\n            , ec.inbox_name\n            , ec.time_reference_for_priority_score\n            -- normalization of the expertise matching score (distributed over [20%, 100%])\n            , CASE WHEN max_ems > min_ems\n                    THEN 0.2 + 0.8 * (power(raw_expertise_matching_score, (:power_weight_a)) - min_ems) / (max_ems - min_ems)\n                ELSE 1\n                END AS expertise_matching_score\n            , POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c) AS priority_score\n            , CASE\n                WHEN NOT ec.reserved_for_review THEN POWER(workforce_level_matching_score, (:power_weight_d)) ELSE 1\n                END AS workforce_level_matching_score\n            , uce_member_matching_score\n            , COALESCE(\n                    CASE\n                        WHEN max_ems > min_ems\n                        THEN 0.2 + 0.8 * (power(raw_expertise_matching_score, (:power_weight_a)) - min_ems) / (max_ems - min_ems)\n                        ELSE 1\n                    END\n                    * POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)\n                    * POWER(workforce_level_matching_score, (:power_weight_d))\n                    * uce_member_matching_score\n                , 0) AS assignment_score\n            , raw_expertise_matching_score\n            , sla_score\n            , sla_breached\n            , MAX(POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)) OVER () AS max_priority_score\n            , priority_level\n            , MAX(POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)) OVER (PARTITION BY priority_level) AS max_priority_score_per_priority_level\n            , m.max_ems\n            , ec.reserved_for_external_agent_platform\n            , ec.reserved_for_review\n            , null as n_rejections\n            , ec.priority_level = 'SLA[5min]' as is_sync_conversation\n            , ec.additional_power_weight_c\n        FROM eligible_convos_with_expertise_matching_score_per_uce ec\n        JOIN min_max_expertise_matching_score_per_convo m USING(contact_request_id)\n        LEFT JOIN support.contact_request_assignment_event crae ON crae.contact_request_id = ec.contact_request_id AND crae.action = 'rejected' AND crae.intercom_admin_id = ec.intercom_admin_id\n        WHERE\n            crae.contact_request_id IS NULL\n            AND ec.raw_expertise_matching_score > 0  -- exclude conversations with negative expertise matching score for current UCE\n    ),\n    backlog_stats AS MATERIALIZED (\n        SELECT\n            count(*) as n_assignable_convos\n            , count(*) FILTER (WHERE priority_level = 'SLA[12h]') as n_assignable_async\n            , count(*) FILTER (WHERE priority_level = 'SLA[5min]') as n_assignable_sync\n            , count(*) FILTER (WHERE priority_level = 'SLA[30min]') as n_assignable_callback\n            , count(*) FILTER (WHERE priority_level = 'SLA[12h]' AND sla_breached) as n_assignable_async_sla_breached\n            , count(*) FILTER (WHERE priority_level = 'SLA[5min]' AND sla_breached) as n_assignable_sync_sla_breached\n            , count(*) FILTER (WHERE priority_level = 'SLA[30min]' AND sla_breached) as n_assignable_callback_sla_breached\n            , percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[12h]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_12h\n            , percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[5min]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_5m\n            , percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[30min]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_30m\n            , percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[12h]' THEN expertise_matching_score END) AS q90_expertise_score_sla_12h\n            , percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[5min]' THEN expertise_matching_score END) AS q90_expertise_score_sla_5m\n            , percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[30min]' THEN expertise_matching_score END) AS q90_expertise_score_sla_30m\n        FROM eligible_convos_with_scores\n    )\n    SELECT\n        *\n        , 1.0 AS shift_matching_score\n    FROM eligible_convos_with_scores\n    CROSS JOIN backlog_stats stats\n    ORDER BY assignment_score DESC NULLS LAST, time_reference_for_priority_score\n    LIMIT 10;\n    "

FR_RANKED_SQL_QUERY `module-attribute` ¶

FR_RANKED_SQL_QUERY = "\n    -- This query is a translation of the original GET_CONVERSATION_BY_ASSIGNMENT_SCORE_EU_SQL\n    -- from the FR resolution_platform to the global support component.\n    -- Major changes include:\n    -- - Replacing FR tables with global support tables (e.g., care_conversation_backlog -> contact_request_intercom_state).\n    -- - Adapting to new schemas (e.g., support_agent roles are now in an array).\n    -- - Removing the old_spe_new_spe_mapping CTE as it is no longer needed.\n    WITH recalibrated_support_agents AS (\n        SELECT\n            sa.id\n        FROM support.assigner_recalibration ar\n        JOIN support.support_agent sa ON ar.alan_email = sa.alan_email\n        JOIN support.support_agent_workspace_affectation sawa ON sa.id = sawa.support_agent_id AND ar.intercom_workspace_id = sawa.intercom_workspace_id\n        WHERE ar.intercom_workspace_id = (:workspace_id)\n    ),\n    support_agents_for_computation AS (\n        SELECT\n            sa.id as support_agent_id\n            , sa.intercom_admin_id\n            , sawa.level\n            , sawa.intercom_workspace_id\n            --Roles are now stored in an array, so we check for the presence of a role in the array.\n            , 'eligible_for_sync' = ANY(sawa.roles) as is_eligible_for_sync\n            , CASE WHEN (NOW() AT TIME ZONE 'Europe/Paris')::TIME BETWEEN '09:00:00' AND '19:00:00'\n                THEN 'eligible_for_async' = ANY(sawa.roles)\n                ELSE True\n                END as is_eligible_for_async\n            , sa.platform_name as platform\n            , NOT sa.is_external_admin AS has_role_uce\n            , CASE WHEN (NOW() AT TIME ZONE 'Europe/Paris')::TIME BETWEEN '09:00:00' AND '19:00:00'\n                THEN 'eligible_for_callback' = ANY(sawa.roles)\n                ELSE False\n                END as has_role_callback\n            , 'automated_answer_reviewer' = ANY(sawa.roles) as has_reviewer_role\n            , 'en' = ANY(sa.spoken_languages) as speaks_english\n            , sa.work_location != 'fr' as is_offshore\n        FROM support.support_agent as sa\n        JOIN support.support_agent_workspace_affectation sawa ON sa.id = sawa.support_agent_id AND sawa.intercom_workspace_id = (:workspace_id)\n        WHERE (sa.id = ANY((select array(SELECT id FROM recalibrated_support_agents))::uuid[])\n            OR sa.id = (:support_agent_id))  -- the admin requesting an assignment\n    ),\n    uce_specialisations_mapping AS (\n        SELECT sa.intercom_admin_id\n            , ss.intercom_inbox_id\n            , sasms.score\n        FROM support_agents_for_computation sa\n        LEFT JOIN support.support_agent_workspace_affectation sawa ON sawa.support_agent_id = sa.support_agent_id\n        LEFT JOIN support.support_agent_spe_matching_score sasms ON sasms.support_agent_workspace_affectation_id = sawa.id\n        LEFT JOIN support.support_specialization ss ON ss.id = sasms.support_specialization_id\n        WHERE sasms.score != 0\n    ),\n    prefiltered_contact_request_backlog AS (\n        SELECT\n            DISTINCT ON (cr.intercom_conversation_id)\n            cr.id as contact_request_id\n            , cr.intercom_conversation_id\n            , cris.assigned_intercom_inbox_id as inbox_id\n            , cris.last_inbox_assignment_by\n            , cris.waiting_since\n            , greatest(cris.waiting_since, cris.last_admin_reply_at) AS time_reference_for_priority_score\n            , cris.last_admin_reply_at\n            , cram.recommended_admin_id as recommended_admin_id\n            , cris.started_at\n            , COALESCE(cram.reserved_for_automated_answers_review, FALSE) as reserved_for_review\n            , cris.state\n            , cris.assigned_intercom_admin_id as admin_id\n            , cr.created_at\n            -- using tags to compute properties of the conversations\n            , SUBSTRING(tags.platform_tag, 'ROLE\\[(.*)\\]') AS reserved_for_external_agent_platform\n            -- priority level is defined by the sync/async nature of the convo but can be overwritten during a handover\n            , CASE\n                WHEN tags.sla_tag = 'SLA[12h]' THEN 'SLA[12h]'\n                WHEN tags.sla_tag = 'SLA[5min]' THEN 'SLA[5min]'\n                WHEN tags.sla_tag = 'SLA[30min]' OR tags.callback_tag = 'ROLE[CALLBACK]' THEN 'SLA[30min]'\n                -- if not sync tagged or if one of the hotline source type → mark async.\n                WHEN tags.type_tag != 'SYNC' or cr.source_type in ('immediate_callback_request', 'hotline_request')THEN 'SLA[12h]'\n                ELSE 'SLA[5min]'\n                END AS priority_level\n            -- workforce level of the conversation\n            , CASE\n                WHEN tags.workforce_level_tag = 'ROLE[LEVEL3]' OR tags.uce_reserved_tag IS NOT NULL THEN 3\n                WHEN tags.workforce_level_tag = 'ROLE[LEVEL2]' THEN 2\n                ELSE 1\n                END AS conversation_workforce_level\n            , tags.*\n        FROM support.contact_request cr\n        JOIN support.contact_request_intercom_state cris ON cr.id = cris.contact_request_id\n        LEFT JOIN support.contact_request_assignment_metadata cram ON cr.id = cram.contact_request_id\n        LEFT JOIN support.contact_request_tag crt ON cr.id = crt.contact_request_id -- to define priority level\n        CROSS JOIN LATERAL (\n            SELECT\n                MAX(CASE WHEN crt.name IN ('ROLE[UCE]', 'COMPLEX CLAIM', 'SENSITIVE') THEN crt.name END) AS uce_reserved_tag\n                , MAX(CASE WHEN crt.name = 'ROLE[CALLBACK]' THEN crt.name END) AS callback_tag\n                , MAX(CASE WHEN crt.name IN ('ROLE[WEBHELP]', 'ROLE[ONEPILOT]') THEN crt.name END) AS platform_tag\n                , MAX(CASE WHEN crt.name = 'ROLE[ENGLISH]' THEN crt.name END) AS english_tag\n                , MAX(CASE WHEN crt.name = 'ROLE[GEORESTRICTED]' THEN crt.name END) AS geo_tag\n                , MAX(CASE WHEN crt.name IN ('ROLE[LEVEL1]','ROLE[LEVEL2]','ROLE[LEVEL3]') THEN crt.name END) AS workforce_level_tag\n                , MAX(CASE WHEN crt.name IN ('bypass-assigner', 'Abusive Conversation') THEN crt.name END) AS bypass_tag\n                , MAX(CASE WHEN crt.name IN ('SLA[12h]', 'SLA[5min]', 'SLA[30min]') THEN crt.name END) as sla_tag\n                , MAX(CASE WHEN crt.name IN ('SYNC', 'ASYNC') THEN crt.name END) as type_tag\n                , MAX(CASE WHEN crt.name IN ('bypass-classification') THEN crt.name END) AS classification_bypass_tag\n            FROM support.contact_request_tag crt\n            WHERE crt.contact_request_id = cr.id\n        ) tags\n        WHERE\n            cr.intercom_workspace_id = (:workspace_id)\n            AND cris.state = 'open'\n            AND (cris.assigned_intercom_admin_id IS NULL OR cris.assigned_intercom_admin_id = '') -- testing for empty string for records updated via flask admin\n            AND cr.created_at > '2021-11-26' -- until data is cleared in prod\n            AND tags.bypass_tag IS NULL -- exclude convos explicitly flagged as bypassing the Assigner\n        ORDER BY cr.intercom_conversation_id, cr.id, crt.notified_at DESC\n    ),\n    eligible_contact_request_backlog_with_priority_level AS (\n        SELECT\n            pcrb.contact_request_id\n            , pcrb.intercom_conversation_id\n            , pcrb.inbox_id\n            , pcrb.last_inbox_assignment_by\n            , pcrb.time_reference_for_priority_score\n            , pcrb.priority_level\n            , pcrb.conversation_workforce_level\n            , sa.level - pcrb.conversation_workforce_level AS distance_workforce_level_admin_conversation\n            , CASE WHEN pcrb.recommended_admin_id = sa.intercom_admin_id\n                THEN (:member_matching_score_boost)\n                ELSE 1\n                END AS uce_member_matching_score\n            , pcrb.started_at\n            , pcrb.reserved_for_external_agent_platform\n            , pcrb.reserved_for_review\n            , pcrb.classification_bypass_tag IS NOT NULL as bypassed_classifier\n        FROM prefiltered_contact_request_backlog pcrb\n        CROSS JOIN support_agents_for_computation sa\n        WHERE sa.support_agent_id = (:support_agent_id) -- join only on the admin requesting an assignment\n            -- reduce scope for agents depending on their sync/async eligibility configuration\n            AND (\n                -- Admin is ELIGIBLE_FOR_SYNC and conversation is NOT ASYNC and NOT CALLBACK\n                (COALESCE(sa.is_eligible_for_sync, True) AND pcrb.priority_level = 'SLA[5min]' AND pcrb.callback_tag IS NULL)\n                OR\n                -- Admin is ELIGIBLE_FOR_ASYNC and conversation is NOT SLA[5min] and NOT CALLBACK\n                (COALESCE(sa.is_eligible_for_async, True) AND pcrb.priority_level != 'SLA[5min]' AND pcrb.callback_tag IS NULL)\n                OR\n                -- Admin has CALLBACK role and conversation is CALLBACK\n                (COALESCE(sa.has_role_callback, True) AND pcrb.callback_tag IS NOT NULL)\n            )\n            -- assign automated answer reviews for reviewers\n            AND (COALESCE(sa.has_reviewer_role, False) OR pcrb.reserved_for_review IS NOT TRUE)\n            -- reduce scope for agents not speaking english\n            AND (COALESCE(sa.speaks_english, True) OR pcrb.english_tag IS NULL)\n            -- reduce scope of georestricted convos for offshore agents\n            AND (COALESCE(NOT sa.is_offshore, False) OR pcrb.geo_tag IS NULL)\n            AND CASE\n                    WHEN NOT pcrb.reserved_for_review THEN sa.level - pcrb.conversation_workforce_level >= 0\n                    ELSE TRUE\n                END\n                -- handle conversations reserved for specific external agent platforms\n                AND CASE\n                        WHEN pcrb.reserved_for_external_agent_platform IS NOT NULL\n                        THEN UPPER(sa.platform) = UPPER(pcrb.reserved_for_external_agent_platform)\n                        ELSE TRUE\n                END\n    ),\n\n    contact_request_context_digest AS (\n        SELECT\n            cr.id as contact_request_id\n            , cr.duplicated_from_id as original_contact_request_id\n            , COALESCE(cr.classification_result->'raw_prediction_classes', cr.classification_result->'prediction'->'raw_prediction_classes' ) as raw_prediction_classes\n            , COALESCE(cr.classification_result->'raw_prediction', cr.classification_result->'prediction'->'raw_prediction') as raw_prediction\n            , created_at\n        FROM support.contact_request cr\n        INNER JOIN eligible_contact_request_backlog_with_priority_level e ON e.contact_request_id = cr.id\n        WHERE cr.intercom_workspace_id = (:workspace_id)\n    ),\n    classifier_classes AS (\n        SELECT\n            raw_prediction_classes AS classes\n        FROM contact_request_context_digest\n        WHERE jsonb_typeof(raw_prediction) = 'array'\n        ORDER BY created_at DESC\n        LIMIT 1\n    ),\n    classifier_classes_as_array AS (\n        SELECT ARRAY_AGG(cls) classes_array\n        FROM classifier_classes, JSONB_ARRAY_ELEMENTS_TEXT(classifier_classes.classes) cls\n    ),\n\n    -- # 1. define the eligible backlog for the UCE requesting an assignment\n    eligible_contact_request_backlog_with_context AS (\n        SELECT\n            ecrb.contact_request_id\n            , ecrb.intercom_conversation_id\n            , ecrb.priority_level\n            -- 1. SLA score = (1 min + waiting_time in min) / (1 min + SLA in min)\n            , CASE\n                WHEN ecrb.priority_level = 'SLA[5min]'\n                    THEN (:sync_sla_score_boost) * (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (5 - 2)\n                WHEN ecrb.priority_level = 'SLA[30min]'\n                    THEN (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (30 - 2)\n                ELSE --  priority_level = 'SLA[12h]'\n                        (3 + count_business_minutes_between(ecrb.time_reference_for_priority_score, now()))  / (12 * 60 - 2)\n                END AS sla_score\n            , CASE\n                WHEN ecrb.priority_level = 'SLA[5min]'\n                    THEN count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 5\n                WHEN ecrb.priority_level = 'SLA[30min]'\n                    THEN count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 30\n                ELSE\n                    count_business_minutes_between(ecrb.time_reference_for_priority_score, now()) >= 12 * 60\n                END AS sla_breached\n            -- 2. workforce level matching score\n            , distance_workforce_level_admin_conversation\n            -- the workforce_level_score_boost is here to give a higher assignment score when the level of the conversation matches the level of the admin\n            -- workforce_level_matching_score doesn't matter the escalation reviews of automated answers\n            , CASE\n                WHEN distance_workforce_level_admin_conversation = 0 THEN (:workforce_level_score_boost)\n                WHEN ecrb.reserved_for_review THEN 1.0\n                WHEN distance_workforce_level_admin_conversation < 0 THEN NULL\n                ELSE 1.0 / (distance_workforce_level_admin_conversation + 1)\n                END AS workforce_level_matching_score\n            -- 3. parsing of classification result (to be used later for the expertise matching score computation)\n            , CASE WHEN jsonb_typeof(COALESCE(crcd.raw_prediction, crcd2.raw_prediction)) = 'array'\n                THEN COALESCE(crcd.raw_prediction, crcd2.raw_prediction)\n                ELSE array_to_json(array_fill(1.0 / jsonb_array_length(cls.classes) , ARRAY[jsonb_array_length(cls.classes)]))::jsonb\n                END AS predicted_probabilities\n            , CASE WHEN jsonb_typeof(COALESCE(crcd.raw_prediction_classes, crcd2.raw_prediction_classes)) = 'array'\n                THEN COALESCE(crcd.raw_prediction_classes, crcd2.raw_prediction_classes)\n                ELSE cls.classes\n                END AS predicted_classes\n            , ecrb.inbox_id\n            , ss.intercom_inbox_name as inbox_name\n            , ecrb.last_inbox_assignment_by\n            , ecrb.uce_member_matching_score\n            , ecrb.time_reference_for_priority_score\n            , ecrb.reserved_for_external_agent_platform\n            , ecrb.conversation_workforce_level\n            , ecrb.reserved_for_review\n            , ecrb.bypassed_classifier\n        FROM eligible_contact_request_backlog_with_priority_level ecrb\n        INNER JOIN support.support_specialization ss ON ss.intercom_inbox_id = ecrb.inbox_id AND ss.is_used_by_assigner AND ss.intercom_workspace_id = (:workspace_id) -- only consider inboxes recognized by the assigner\n        LEFT JOIN contact_request_context_digest crcd ON crcd.contact_request_id = ecrb.contact_request_id\n        LEFT JOIN contact_request_context_digest crcd2 ON crcd.original_contact_request_id = crcd2.contact_request_id  -- inherit context from parent convo when relevant\n        CROSS JOIN classifier_classes cls\n        -- we want to prevent assignments of tickets not yet classified, but don't want to wait more than 30 seconds for sync and 2 minutes for the rest\n        WHERE jsonb_typeof(COALESCE(crcd.raw_prediction, crcd2.raw_prediction)) = 'array'\n            OR CASE\n                    WHEN ecrb.priority_level = 'SLA[5min]' THEN COALESCE(ecrb.started_at < now() at time zone 'utc' - '30 seconds'::interval, TRUE)\n                    ELSE COALESCE(ecrb.started_at < now() at time zone 'utc' - '2 minutes'::interval, TRUE)\n                END\n    ),\n    -- # 2. compute expertise matching scores for all (eligible convo X UCE) pairs\n    convos_x_probabilities AS (\n        -- expand the eligible convos on all predicted classes\n        SELECT\n            s.*\n            , s._predicted_class AS predicted_class\n        FROM (\n            SELECT\n                contact_request_id\n                , intercom_conversation_id\n                , priority_level\n                , sla_score\n                , sla_breached\n                , conversation_workforce_level\n                , workforce_level_matching_score\n                , uce_member_matching_score\n                , jsonb_array_elements(predicted_probabilities)::numeric AS predicted_probability\n                , trim(jsonb_array_elements(predicted_classes)::text, '\"') AS _predicted_class\n                , last_inbox_assignment_by\n                , inbox_id\n                , inbox_name\n                , time_reference_for_priority_score\n                , reserved_for_external_agent_platform\n                , reserved_for_review\n                , bypassed_classifier\n            FROM eligible_contact_request_backlog_with_context\n        ) s\n        UNION ALL\n        -- expand for classes unknown by the classifier as well\n        SELECT\n            contact_request_id\n            , intercom_conversation_id\n            , priority_level\n            , sla_score\n            , sla_breached\n            , conversation_workforce_level\n            , workforce_level_matching_score\n            , uce_member_matching_score\n            , 0 AS predicted_probability\n            , t.inbox_unknown_by_classifier AS _predicted_class\n            , last_inbox_assignment_by\n            , inbox_id\n            , inbox_name\n            , time_reference_for_priority_score\n            , reserved_for_external_agent_platform\n            , reserved_for_review\n            , bypassed_classifier\n            , t.inbox_unknown_by_classifier AS predicted_class\n        FROM eligible_contact_request_backlog_with_context\n        CROSS JOIN (\n                SELECT intercom_inbox_name AS inbox_unknown_by_classifier FROM support.support_specialization\n                EXCEPT\n                SELECT JSONB_ARRAY_ELEMENTS_TEXT(classes) FROM classifier_classes\n        ) t\n    ),\n    -- Calibration pass 1: Compute min/max across ALL agents for calibration (lightweight aggregation)\n    calibration_min_max_per_agent AS (\n        SELECT\n            sa.support_agent_id\n            , cxp.contact_request_id\n            , sum((CASE\n                    WHEN (cxp.last_inbox_assignment_by IN ('manual', 'intercom') OR cxp.bypassed_classifier OR NOT cxp.inbox_name = ANY(cls.classes_array))\n                        AND cxp.inbox_id = ss.intercom_inbox_id THEN 1.0\n                    WHEN (cxp.last_inbox_assignment_by IN ('manual', 'intercom') OR cxp.bypassed_classifier OR NOT cxp.inbox_name = ANY(cls.classes_array))\n                        AND cxp.inbox_id != ss.intercom_inbox_id THEN 0.0\n                    ELSE cxp.predicted_probability::numeric\n                END)\n                * COALESCE(m.score, 0)) AS raw_expertise_matching_score\n        FROM convos_x_probabilities cxp\n        JOIN support.support_specialization ss ON cxp.predicted_class = ss.intercom_inbox_name\n        CROSS JOIN support_agents_for_computation sa\n        LEFT JOIN uce_specialisations_mapping m ON m.intercom_inbox_id = ss.intercom_inbox_id AND m.intercom_admin_id = sa.intercom_admin_id\n        CROSS JOIN classifier_classes_as_array cls\n        WHERE\n            CASE\n                WHEN NOT cxp.reserved_for_review THEN sa.level - cxp.conversation_workforce_level >= 0\n                ELSE TRUE\n            END\n            AND CASE\n                    WHEN cxp.reserved_for_external_agent_platform IS NOT NULL\n                    THEN UPPER(sa.platform) = UPPER(cxp.reserved_for_external_agent_platform)\n                    ELSE TRUE\n            END\n        GROUP BY sa.support_agent_id, cxp.contact_request_id\n    ),\n    min_max_expertise_matching_score_per_convo AS (\n        SELECT\n            contact_request_id\n            , min(power(raw_expertise_matching_score, (:power_weight_a))) AS min_ems\n            , max(power(raw_expertise_matching_score, (:power_weight_a))) AS max_ems\n        FROM calibration_min_max_per_agent\n        GROUP BY contact_request_id\n    ),\n    -- calibration pass 2: Compute detailed scores ONLY for the requesting agent which is less costly\n    eligible_convos_with_expertise_matching_score_per_uce AS (\n        SELECT\n            sa.intercom_admin_id\n            , sa.support_agent_id\n            , cxp.intercom_conversation_id\n            , cxp.contact_request_id\n            , cxp.inbox_id\n            , cxp.inbox_name\n            , cxp.priority_level\n            , cxp.workforce_level_matching_score\n            , cxp.sla_score\n            , cxp.sla_breached\n            , cxp.uce_member_matching_score\n            , cxp.time_reference_for_priority_score\n            , cxp.reserved_for_external_agent_platform\n            , cxp.reserved_for_review\n            , sum((CASE\n                    WHEN (cxp.last_inbox_assignment_by IN ('manual', 'intercom') OR cxp.bypassed_classifier OR NOT cxp.inbox_name = ANY(cls.classes_array))\n                        AND cxp.inbox_id = ss.intercom_inbox_id THEN 1.0\n                    WHEN (cxp.last_inbox_assignment_by IN ('manual', 'intercom') OR cxp.bypassed_classifier OR NOT cxp.inbox_name = ANY(cls.classes_array))\n                        AND cxp.inbox_id != ss.intercom_inbox_id THEN 0.0\n                    ELSE cxp.predicted_probability::numeric\n                END)\n                * COALESCE(m.score, 0)) AS raw_expertise_matching_score\n            -- L1 & L2 agents have an extra boost on priority_level for sync conversations\n            , case when cxp.priority_level = 'SLA[5min]' and sa.level < 3 then 2 else 0 end as additional_power_weight_c\n        FROM convos_x_probabilities cxp\n        JOIN support.support_specialization ss ON cxp.predicted_class = ss.intercom_inbox_name\n        CROSS JOIN support_agents_for_computation sa\n        LEFT JOIN uce_specialisations_mapping m ON m.intercom_inbox_id = ss.intercom_inbox_id AND m.intercom_admin_id = sa.intercom_admin_id\n        CROSS JOIN classifier_classes_as_array cls\n        -- additional exclusion conditions for both the care expert asking for the assignment and the calibration cohort\n        WHERE\n            sa.support_agent_id = (:support_agent_id)::uuid  -- ONLY requesting agent\n            AND CASE\n                WHEN NOT cxp.reserved_for_review THEN sa.level - cxp.conversation_workforce_level >= 0\n                ELSE TRUE\n            END\n            -- handle conversations reserved for specific external agent platforms\n            AND CASE\n                    WHEN cxp.reserved_for_external_agent_platform IS NOT NULL\n                    THEN UPPER(sa.platform) = UPPER(cxp.reserved_for_external_agent_platform)\n                    ELSE TRUE\n            END\n        GROUP BY sa.intercom_admin_id, sa.support_agent_id, cxp.contact_request_id, cxp.inbox_id, cxp.inbox_name, cxp.priority_level, cxp.workforce_level_matching_score, cxp.sla_score, cxp.sla_breached, cxp.uce_member_matching_score,\n    cxp.time_reference_for_priority_score, cxp.reserved_for_external_agent_platform, cxp.reserved_for_review, cxp.intercom_conversation_id, sa.level\n    ),\n    eligible_convos_with_scores AS (\n        SELECT\n            ec.contact_request_id\n            , ec.intercom_conversation_id\n            , ec.inbox_id\n            , ec.inbox_name\n            , ec.time_reference_for_priority_score\n            -- normalization of the expertise matching score (distributed over [20%, 100%])\n            , CASE WHEN max_ems > min_ems\n                    THEN 0.2 + 0.8 * (power(raw_expertise_matching_score, (:power_weight_a)) - min_ems) / (max_ems - min_ems)\n                ELSE 1\n                END AS expertise_matching_score\n            , POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c) AS priority_score\n            , CASE\n                WHEN NOT ec.reserved_for_review THEN POWER(workforce_level_matching_score, (:power_weight_d)) ELSE 1\n                END AS workforce_level_matching_score\n            , uce_member_matching_score\n            , COALESCE(\n                    CASE\n                        WHEN max_ems > min_ems\n                        THEN 0.2 + 0.8 * (power(raw_expertise_matching_score, (:power_weight_a)) - min_ems) / (max_ems - min_ems)\n                        ELSE 1\n                    END\n                    * POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)\n                    * POWER(workforce_level_matching_score, (:power_weight_d))\n                    * uce_member_matching_score\n                , 0) AS assignment_score\n            , raw_expertise_matching_score\n            , sla_score\n            , sla_breached\n            , MAX(POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)) OVER () AS max_priority_score\n            , priority_level\n            , MAX(POWER(sla_score, (:power_weight_c) + ec.additional_power_weight_c)) OVER (PARTITION BY priority_level) AS max_priority_score_per_priority_level\n            , m.max_ems\n            , ec.reserved_for_external_agent_platform\n            , ec.reserved_for_review\n            , null as n_rejections\n            , ec.priority_level = 'SLA[5min]' as is_sync_conversation\n            , ec.additional_power_weight_c\n        FROM eligible_convos_with_expertise_matching_score_per_uce ec\n        JOIN min_max_expertise_matching_score_per_convo m USING(contact_request_id)\n        LEFT JOIN support.contact_request_assignment_event crae ON crae.contact_request_id = ec.contact_request_id AND crae.action = 'rejected' AND crae.intercom_admin_id = ec.intercom_admin_id\n        WHERE\n            crae.contact_request_id IS NULL\n            AND ec.raw_expertise_matching_score > 0  -- exclude conversations with negative expertise matching score for current UCE\n    ),\n    backlog_stats AS MATERIALIZED (\n        SELECT\n            count(*) as n_assignable_convos\n            , count(*) FILTER (WHERE priority_level = 'SLA[12h]') as n_assignable_async\n            , count(*) FILTER (WHERE priority_level = 'SLA[5min]') as n_assignable_sync\n            , count(*) FILTER (WHERE priority_level = 'SLA[30min]') as n_assignable_callback\n            , count(*) FILTER (WHERE priority_level = 'SLA[12h]' AND sla_breached) as n_assignable_async_sla_breached\n            , count(*) FILTER (WHERE priority_level = 'SLA[5min]' AND sla_breached) as n_assignable_sync_sla_breached\n            , count(*) FILTER (WHERE priority_level = 'SLA[30min]' AND sla_breached) as n_assignable_callback_sla_breached\n            , percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[12h]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_12h\n            , percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[5min]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_5m\n            , percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[30min]' THEN power(sla_score, (:power_weight_c) + additional_power_weight_c) END) AS q90_priority_score_sla_30m\n            , percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[12h]' THEN expertise_matching_score END) AS q90_expertise_score_sla_12h\n            , percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[5min]' THEN expertise_matching_score END) AS q90_expertise_score_sla_5m\n            , percentile_cont(0.9) WITHIN GROUP (ORDER BY CASE WHEN priority_level = 'SLA[30min]' THEN expertise_matching_score END) AS q90_expertise_score_sla_30m\n        FROM eligible_convos_with_scores\n    )\n    SELECT\n        row_number() OVER (ORDER BY assignment_score DESC NULLS LAST, time_reference_for_priority_score) AS assignment_rank\n        , (:support_agent_id)\n        , *\n        , row_number() OVER (ORDER BY intercom_conversation_id = (:conversation_id) DESC, assignment_score DESC NULLS LAST, time_reference_for_priority_score) AS row_nb\n    FROM eligible_convos_with_scores\n    CROSS JOIN backlog_stats stats\n    ORDER BY row_nb, assignment_score DESC NULLS LAST, time_reference_for_priority_score\n    LIMIT 10;\n"

components.support.public.dependencies ¶

COMPONENT_NAME `module-attribute` ¶

COMPONENT_NAME = 'support'

SupportDependency ¶

Bases: ABC

This allows country-specific apps to define a class to inject dependencies to the support component. This is mainly used as a workaround until everything from resolution_platform has been migrated to the support component, and we can safely rely on a global user & alan employee.