create or replace view V_OUT_OUTGOING_ALL as

SELECT

    CASE c.incomming
        WHEN 'true' THEN 'received'
        ELSE 'dialed'
        END `zoho_type`,

    CASE e.event
        WHEN 'callRinging' THEN 'ringing'
        WHEN 'callTalking' THEN 'answered'
        WHEN 'callDropped' THEN

            CASE e.`drop_cause`
                WHEN 'disconnect'  THEN 'ended'
                WHEN 'transferred'  THEN 'ended'
                WHEN 'busy'  THEN 'ended'
                WHEN 'noAnswer'  THEN 'ended'
                WHEN 'unavailable'  THEN 'ended'
                WHEN 'error'  THEN 'ended'
                ELSE 'ERROR_DROP'
                END
        ELSE 'ERROR'
        END `zoho_state`,

    e.`call_id` `zoho_id`,

    c.`display_number` `zoho_from`,

    c.`custom_caller_id` `zoho_to`,

    "null" `zoho_zohouser`,

    c.`created_at` `zoho_start_time`,

    e.`talking_time` `zoho_duration`,

    "null" `zoho_voice_uri`,

    c.`incomming` `centile_incomming`,

    e.`event` `centile_event`,

    e.`talk_cause` `centile_talk_cause`,

    e.`drop_cause` `centile_drop_cause`,

    e.`id` `centile_event_id`,

    c.`original_dialed_number` `original_dialed_number`

FROM `event` e
         INNER JOIN `call` c on e.call_id = c.id
         INNER JOIN `call_flow` cf on c.call_flow_id = cf.id
         INNER JOIN listener l on  cf.`listener_id` = l.id
where c.incomming= "false";



create or replace view V_OUT_INCOMMING_ALL as

SELECT

    CASE c.incomming
        WHEN 'false' THEN 'dialed'
        ELSE 'received'
        END `zoho_type`,

    CASE e.event
        WHEN 'callRinging' THEN 'ringing'
        WHEN 'callTalking' THEN 'answered'
        WHEN 'callDropped' THEN

            CASE e.`drop_cause`
                WHEN 'disconnect'  THEN 'ended'
                WHEN 'transferred'  THEN 'ended'
                WHEN 'busy'  THEN 'ended'
                WHEN 'noAnswer'  THEN 'ended'
                WHEN 'unavailable'  THEN 'ended'
                WHEN 'error'  THEN 'ended'
                ELSE 'ERROR_DROP'
                END
        ELSE 'ERROR'
        END `zoho_state`,
    e.`call_id` `zoho_id`,
    c.`custom_caller_id` `zoho_from`,
    c.display_number `zoho_to`,
    "null" `zoho_zohouser`,
    c.created_at `zoho_start_time`,
    e.`talking_time` `zoho_duration`,
    "null" `zoho_voice_uri`,
    c.incomming `centile_incomming`,
    e.event `centile_event`,
    e.talk_cause `centile_talk_cause`,
    e.drop_cause `centile_drop_cause`,
    e.id `centile_event_id`

FROM `event` e
         INNER JOIN `call` c on e.call_id = c.id
         INNER JOIN `call_flow` cf on c.call_flow_id = cf.id
         INNER JOIN listener l on  cf.`listener_id` = l.id
where c.incomming= "true";



create or replace view centile_call_event_parser.v_in_ext_user_centrex as SELECT `id`,`customCallerID` FROM napi.ext_user_centrex;


CREATE OR REPLACE VIEW Z_DEBUG_CALLFLOW AS
SELECT
    mi.`id` mi_id, mi.`created_at` mi_created_at, mi.`data` mi_data,
    mo.`id` mo_id, mo.`created_at` mo_created_at, mo.`message` mo_message,
    cm.id cm_id,  cm. `created_at` cm_created_at, cm.`data` cm_data, cm.uid cm_uid
FROM call_notify_webservice .message_input mi
         LEFT JOIN centile_call_event_parser.message_output mo
                   ON mi.id = mo.message_input_id
         LEFT JOIN call_notify_webservice.message_input cm
                   ON mi.uid = cm.uid