CREATE VIEW vwcopilotsummaryinsights AS (
 SELECT cs.conversationid,
    cs.conversationstartdate,
    cs.conversationenddate,
    cs.conversationstartdateltc,
    cs.conversationenddateltc,
    cs.originaldirection,
    cs.firstmediatype,
    cs.lastmediatype,
    cs.ani,
    cs.dnis,
    cs.firstagentid,
    cs.lastagentid,
    cs.firstqueueid,
    cs.lastqueueid,
    cs.ttalkcomplete,
    cs.tqueuetime,
    cs.tacw,
    cs.tansweredcount,
    cs.tanswered,
    cs.tabandonedcount,
    vs.summaryid,
    vs.summarytype,
    vs.summarytext,
    vs.summarylanguage,
    vs.summarymediatype,
    vs.summaryresolution,
    vs.summaryreason,
    vs.summaryactionitem,
    vs.summaryinsights,
    vs.summaryrawjson,
    vs.updated AS summary_updated,
    cu.summaryrating,
    cu.wrapupcoderating,
    cu.copied AS summary_copied,
    cu.presented AS summary_presented,
    cu.summarygenerated,
    cu.wrapupcodesgenerated,
    cu.wrapupcodesuggestionselected,
    cu.editedfield,
    cu.errortype,
    cu.language AS copilot_language,
    cu.mediatype AS copilot_mediatype,
    cu.messagetype,
    cu.triggersourceoutcome,
    cu.triggersourcetype,
    cu.usagedate,
    cu.nconversationsummaries,
    cu.nconversationsummaryengagements,
    cu.tconversationsummary,
    cu.updated AS copilot_updated,
        CASE
            WHEN (vs.summaryid IS NOT NULL) THEN 'Has AI Summary'::text
            ELSE 'No AI Summary'::text
        END AS summary_status,
        CASE
            WHEN (cu.summaryid IS NOT NULL) THEN 'Has Copilot Data'::text
            ELSE 'No Copilot Data'::text
        END AS copilot_status,
        CASE
            WHEN (cu.summaryrating >= 4) THEN 'High Rating'::text
            WHEN (cu.summaryrating >= 3) THEN 'Medium Rating'::text
            WHEN (cu.summaryrating >= 1) THEN 'Low Rating'::text
            ELSE 'Not Rated'::text
        END AS summary_rating_category,
        CASE
            WHEN (cu.copied = true) THEN 'Copied'::text
            WHEN (cu.presented = true) THEN 'Presented Only'::text
            ELSE 'Not Used'::text
        END AS summary_usage_type,
        CASE
            WHEN ((cs.conversationenddate IS NOT NULL) AND (cs.conversationstartdate IS NOT NULL)) THEN (EXTRACT(epoch FROM (cs.conversationenddate - cs.conversationstartdate)) / 60.0)
            ELSE NULL::numeric
        END AS conversation_duration_minutes,
        CASE
            WHEN ((cu.summaryrating IS NOT NULL) AND (cu.copied = true)) THEN 'Effective'::text
            WHEN ((cu.summaryrating IS NOT NULL) AND (cu.presented = true)) THEN 'Viewed'::text
            WHEN (vs.summaryid IS NOT NULL) THEN 'Generated'::text
            ELSE 'None'::text
        END AS summary_effectiveness
   FROM ((convsummarydata cs
     LEFT JOIN convvoicesummarydata vs ON (((cs.conversationid)::text = (vs.conversationid)::text)))
     LEFT JOIN copilotusagedata cu ON ((((vs.summaryid)::text = (cu.summaryid)::text) AND ((cs.conversationid)::text = (cu.conversationid)::text))))
)