CREATE VIEW vwassistantqueuesummary AS (
 SELECT q.id AS queueid,
    q.name AS queuename,
    q.description AS queuedescription,
    "substring"((q.description)::text, 'queuegroup=([^;]+)(;|$)'::text) AS queuegroup,
    "substring"((q.description)::text, 'region=([^;]+)(;|$)'::text) AS region,
    q.divisionid,
    q.assistantid,
    q.assistantstate,
    q.assistantmediatypes,
    count(DISTINCT d.conversationid) AS total_conversations,
    count(*) AS total_segments,
    count(DISTINCT
        CASE
            WHEN ((d.mediatype)::text = 'voice'::text) THEN d.conversationid
            ELSE NULL::character varying
        END) AS voice_conversations,
    count(DISTINCT
        CASE
            WHEN ((d.mediatype)::text = 'chat'::text) THEN d.conversationid
            ELSE NULL::character varying
        END) AS chat_conversations,
    count(DISTINCT
        CASE
            WHEN ((d.mediatype)::text = 'email'::text) THEN d.conversationid
            ELSE NULL::character varying
        END) AS email_conversations,
    count(DISTINCT
        CASE
            WHEN ((d.mediatype)::text = 'message'::text) THEN d.conversationid
            ELSE NULL::character varying
        END) AS message_conversations,
    count(DISTINCT s.conversationid) AS conversations_with_summaries,
    count(DISTINCT
        CASE
            WHEN (s.summarygenerated = true) THEN s.conversationid
            ELSE NULL::character varying
        END) AS summaries_generated,
    round((((count(DISTINCT s.conversationid))::numeric / (NULLIF(count(DISTINCT d.conversationid), 0))::numeric) * (100)::numeric), 2) AS summary_coverage_percent,
    sum(d.segmenttime) AS total_segment_time_seconds,
    round(avg(d.segmenttime), 2) AS avg_segment_time_seconds,
    min(d.segmenttime) AS min_segment_time_seconds,
    max(d.segmenttime) AS max_segment_time_seconds,
    min(d.conversationstartdate) AS first_interaction_date,
    max(d.conversationstartdate) AS last_interaction_date,
    count(DISTINCT
        CASE
            WHEN (d.recordingexists = '1'::"bit") THEN d.conversationid
            ELSE NULL::character varying
        END) AS conversations_with_recordings,
    round(avg(d.conversationminmos), 2) AS avg_mos_score,
    round(avg(d.conversationminrfactor), 2) AS avg_rfactor_score
   FROM ((queuedetails q
     LEFT JOIN detailedinteractiondata d ON (((q.id)::text = (d.queueid)::text)))
     LEFT JOIN convvoicesummarydata s ON (((d.conversationid)::text = (s.conversationid)::text)))
  WHERE (q.assistantenabled = true)
  GROUP BY q.id, q.name, q.description, q.divisionid, q.assistantid, q.assistantstate, q.assistantmediatypes
)