CREATE VIEW vwbotreportingturns AS (
 SELECT brt.id,
    brt.sessionid,
    brt.conversationid,
    brt.actionid,
    brt.userinput,
    brt.botprompt,
    brt.actionname,
    brt.actionnumber,
    brt.actiontype,
        CASE
            WHEN ((brt.actiontype)::text = 'AskAction'::text) THEN 'Question/Prompt'::character varying
            WHEN ((brt.actiontype)::text = 'CommunicateAction'::text) THEN 'Communication'::character varying
            WHEN ((brt.actiontype)::text = 'DecisionAction'::text) THEN 'Decision Point'::character varying
            WHEN ((brt.actiontype)::text = 'DataAction'::text) THEN 'Data Processing'::character varying
            WHEN ((brt.actiontype)::text = 'TransferAction'::text) THEN 'Transfer'::character varying
            WHEN ((brt.actiontype)::text = 'DisconnectAction'::text) THEN 'Disconnect'::character varying
            ELSE brt.actiontype
        END AS actiontypedescription,
    brt.intentname,
    brt.intentconfidence,
        CASE
            WHEN (brt.intentconfidence >= 0.80) THEN 'High Confidence (≥80%)'::text
            WHEN (brt.intentconfidence >= 0.60) THEN 'Medium Confidence (60-79%)'::text
            WHEN (brt.intentconfidence >= 0.40) THEN 'Low Confidence (40-59%)'::text
            WHEN (brt.intentconfidence IS NOT NULL) THEN 'Very Low Confidence (<40%)'::text
            ELSE 'No Confidence Score'::text
        END AS intentconfidencecategory,
    brt.intentslotname,
    brt.intentsslotvalue,
    brt.intentslottype,
    brt.intentslotconfidence,
        CASE
            WHEN (brt.intentslotconfidence >= 0.80) THEN 'High Confidence (≥80%)'::text
            WHEN (brt.intentslotconfidence >= 0.60) THEN 'Medium Confidence (60-79%)'::text
            WHEN (brt.intentslotconfidence >= 0.40) THEN 'Low Confidence (40-59%)'::text
            WHEN (brt.intentslotconfidence IS NOT NULL) THEN 'Very Low Confidence (<40%)'::text
            ELSE 'No Confidence Score'::text
        END AS intentslotconfidencecategory,
    brt.knowledgebaseid,
    kb.name AS knowledgebasename,
    kb.description AS knowledgebasedescription,
    brt.feedbacksearchid,
    brt.feedbackrating,
        CASE
            WHEN (brt.feedbackrating >= 4) THEN 'Positive (4-5)'::text
            WHEN (brt.feedbackrating = 3) THEN 'Neutral (3)'::text
            WHEN (brt.feedbackrating >= 1) THEN 'Negative (1-2)'::text
            ELSE 'No Rating'::text
        END AS feedbackratingcategory,
    brt.feedbackdocid,
    brt.feedbackdocquestion,
    brt.feedbackdocanswer,
    brt.feedbackdocconfidence,
        CASE
            WHEN (brt.feedbackdocconfidence >= 0.80) THEN 'High Confidence (≥80%)'::text
            WHEN (brt.feedbackdocconfidence >= 0.60) THEN 'Medium Confidence (60-79%)'::text
            WHEN (brt.feedbackdocconfidence >= 0.40) THEN 'Low Confidence (40-59%)'::text
            WHEN (brt.feedbackdocconfidence IS NOT NULL) THEN 'Very Low Confidence (<40%)'::text
            ELSE 'No Confidence Score'::text
        END AS feedbackdocconfidencecategory,
    brt.searchid,
    brt.searchquery,
    brt.searchdoc,
    brt.eventssearchid,
    brt.eventsknwowledgebaseid,
    brt.eventssearchquery,
    brt.eventsanswerdocid,
    brt.eventsdoc,
    brt.eventsfeedbackid,
    brt.eventsfeedbackkbid,
    brt.eventsfeedbackrating,
        CASE
            WHEN (brt.eventsfeedbackrating >= 4) THEN 'Positive (4-5)'::text
            WHEN (brt.eventsfeedbackrating = 3) THEN 'Neutral (3)'::text
            WHEN (brt.eventsfeedbackrating >= 1) THEN 'Negative (1-2)'::text
            ELSE 'No Rating'::text
        END AS eventsfeedbackratingcategory,
    brt.eventsfeedbackdoc,
    brt.eventsdocvariationid,
    brt.eventsdocversionid,
    brt.datecreated,
    brt.datecompleted,
    EXTRACT(epoch FROM (brt.datecompleted - brt.datecreated)) AS durationseconds,
    round((EXTRACT(epoch FROM (brt.datecompleted - brt.datecreated)) / 60.0), 2) AS durationminutes,
        CASE
            WHEN (EXTRACT(epoch FROM (brt.datecompleted - brt.datecreated)) <= (30)::numeric) THEN 'Very Fast (≤30s)'::text
            WHEN (EXTRACT(epoch FROM (brt.datecompleted - brt.datecreated)) <= (60)::numeric) THEN 'Fast (30-60s)'::text
            WHEN (EXTRACT(epoch FROM (brt.datecompleted - brt.datecreated)) <= (120)::numeric) THEN 'Normal (1-2min)'::text
            WHEN (EXTRACT(epoch FROM (brt.datecompleted - brt.datecreated)) <= (300)::numeric) THEN 'Slow (2-5min)'::text
            WHEN (brt.datecompleted IS NOT NULL) THEN 'Very Slow (>5min)'::text
            ELSE 'Not Completed'::text
        END AS durationcategory,
    brt.askactionresult,
        CASE
            WHEN ((brt.askactionresult)::text = 'SuccessCollection'::text) THEN 'Success - Data Collected'::character varying
            WHEN ((brt.askactionresult)::text = 'SuccessConfirmationYes'::text) THEN 'Success - Confirmed Yes'::character varying
            WHEN ((brt.askactionresult)::text = 'SuccessConfirmationNo'::text) THEN 'Success - Confirmed No'::character varying
            WHEN ((brt.askactionresult)::text = 'NoMatchCollection'::text) THEN 'No Match - Collection Failed'::character varying
            WHEN ((brt.askactionresult)::text = 'NoInputCollection'::text) THEN 'No Input - Timeout'::character varying
            WHEN ((brt.askactionresult)::text = 'AgentRequestedByUser'::text) THEN 'User Requested Agent'::character varying
            WHEN ((brt.askactionresult)::text = 'DisambiguationRequired'::text) THEN 'Disambiguation Needed'::character varying
            WHEN ((brt.askactionresult)::text = 'Error'::text) THEN 'Error Occurred'::character varying
            ELSE brt.askactionresult
        END AS askactionresultdescription,
    brt.sessionendtype,
    brt.sessionendreason,
        CASE
            WHEN ((brt.sessionendtype)::text = 'Default'::text) THEN 'Normal Completion'::character varying
            WHEN ((brt.sessionendtype)::text = 'UserExit'::text) THEN 'User Initiated Exit'::character varying
            WHEN ((brt.sessionendtype)::text = 'Recognized'::text) THEN 'Recognized Exit'::character varying
            WHEN ((brt.sessionendtype)::text = 'FlowError'::text) THEN 'Flow Error'::character varying
            WHEN ((brt.sessionendtype)::text = 'SessionExpired'::text) THEN 'Session Timeout'::character varying
            ELSE brt.sessionendtype
        END AS sessionendtypedescription,
    cs.conversationstartdate,
    cs.conversationenddate,
    cs.firstmediatype,
    cs.lastmediatype,
    cs.firstagentid,
    cs.lastagentid,
    cs.firstqueueid,
    cs.lastqueueid,
    cs.firstwrapupcode,
    cs.lastwrapupcode,
    cs.divisionid,
    brt.updated AS lastupdated
   FROM ((botreportingturns brt
     LEFT JOIN convsummarydata cs ON (((brt.conversationid)::text = (cs.conversationid)::text)))
     LEFT JOIN knowledgebase kb ON (((brt.knowledgebaseid)::text = (kb.id)::text)))
)