Filiera diretta

-- public.filiera_diretta source

CREATE OR REPLACE VIEW public.filiera_diretta
AS SELECT timezone('Europe/Rome'::text, 'now'::text::timestamp with time zone) AS data_ultimo_aggiornamento, dati.company, upper(dati.category::text) AS fonte, upper(dati.categoria_lead_d) AS categoria, upper(dati.treatment::text) AS treatment, upper(dati.pricing2::text) AS pricing, upper(dati.campagna::text) AS campagna, regexp_substr(dati.id_italia::text, '[0-9]+'::text) AS id_italia, to_char(dati.first_assign_at, 'YYYY-MM-DD'::text) AS giorno, to_char(dati.created_at_l, 'YYYY-MM-DD'::text) AS giorno_c, upper(dati.first_user_id::text) AS operatore, lower(dati.lastname::text) AS cognome, lower(dati.firstname::text) AS nome, dati.bot, dati.stato_bot, dati.dettaglio_stato_bot, sum(
        CASE
            WHEN dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL THEN 1
            ELSE 0
        END) AS num_lead_comm_ass, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled <= 3 THEN 1
            ELSE 0
        END) AS num_app, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled <= 3 AND NOT (dati.recalled = 3 AND dati.cancelled_at::date = dati.created_at_a::date) THEN date_diff('day'::text, dati.created_at_a, dati.date_start)
            ELSE NULL::bigint
        END) AS delay_agendati, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND dati.date_end < 'now'::text::date THEN 1
            ELSE 0
        END) AS num_app_past, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled < 4 AND dati.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND dati.date_end < 'now'::text::date THEN 1
            ELSE 0
        END) AS num_app_inagdisd, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled = 3 AND dati.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND dati.date_end < 'now'::text::date THEN 1
            ELSE 0
        END) AS num_app_disd, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled = 4 AND dati.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND dati.date_end < 'now'::text::date THEN 1
            ELSE 0
        END) AS num_app_spost, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled < 3 AND dati.paid = 1 AND dati.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND dati.date_end < 'now'::text::date THEN 1
            ELSE 0
        END) AS num_app_pres, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled < 3 AND dati.paid = 1 AND dati.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND dati.date_end < 'now'::text::date THEN dati.price
            ELSE 0::numeric
        END) AS incasso, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled < 3 AND dati.paid = 1 AND dati.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND dati.date_end < 'now'::text::date THEN dati.incasso_succ_tot
            ELSE 0::numeric
        END) AS incasso_succ, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.call_state::text <> 'da chiamare'::text THEN 1
            ELSE 0
        END) AS num_lead_lav, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND (dati.call_state::text = 'fake'::text OR dati.call_state::text = 'fuori zona'::text OR dati.call_state::text = 'non interessato'::text OR dati.call_state::text = 'no download'::text) THEN 1
            ELSE 0
        END) AS num_lead_scarto, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.call_state::text = 'fake'::text THEN 1
            ELSE 0
        END) AS num_lead_scarto_fak, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.call_state::text = 'fuori zona'::text THEN 1
            ELSE 0
        END) AS num_lead_scarto_fz, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.call_state::text = 'non interessato'::text THEN 1
            ELSE 0
        END) AS num_lead_scarto_ni, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.call_state::text = 'no download'::text THEN 1
            ELSE 0
        END) AS num_lead_scarto_nd, sum(
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND (dati.call_state::text = 'NR'::text OR dati.call_state::text = 'NR 2'::text OR dati.call_state::text = 'NR 3'::text OR dati.call_state::text = 'NR 4'::text OR dati.call_state::text = 'NR 5'::text OR dati.call_state::text = 'NR 6'::text OR dati.call_state::text = 'NR 7'::text OR dati.call_state::text = 'NR 8'::text OR dati.call_state::text = 'NR 9'::text OR dati.call_state::text = 'NR 10'::text) THEN 1
            ELSE 0
        END) AS num_lead_nr,
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled <= 3 THEN dati.alias
            ELSE NULL::character varying
        END AS store,
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled <= 3 THEN dati.name
            ELSE NULL::character varying
        END AS categoria_lead,
        conversation_analysis_results.has_price_request,
        conversation_analysis_results.appointment_taken_after_price,
        rimpalli_per_sessione.ai_rimpalli,
        rimpalli_per_sessione.human_rimpalli,
        rimpalli_per_sessione.has_preferred_call_time
   FROM ( SELECT l.id, l.f_oper, l.f_dup, l.f_dup_wrk, l.f_dup_regen, l.f_dup_mkt, l.customer_id AS id_customer, l.call_state,
                CASE
                    WHEN l.chatbot_process_state IS NOT NULL THEN 'Valeria'::text
                    ELSE 'No Valeria'::text
                END AS bot,
                CASE
                    WHEN l.chatbot_process_state IS NOT NULL AND l.chatbot_call_state::text = 'da_richiamare'::text THEN 'richiamo fissato'::text
                    WHEN l.chatbot_process_state IS NOT NULL AND (l.chatbot_call_state::text <> 'da_richiamare'::text OR l.chatbot_call_state IS NULL) THEN 'richiamo non fissato'::text
                    ELSE 'No Valeria'::text
                END AS stato_bot,
                CASE
                    WHEN l.chatbot_process_state IS NOT NULL AND l.chatbot_call_state::text = 'da_richiamare'::text THEN 'richiamo fissato'::text
                    WHEN l.chatbot_process_state IS NOT NULL AND l.chatbot_call_state::text = 'da_chiamare'::text THEN 'interagisce ma no app'::text
                    WHEN l.chatbot_process_state IS NOT NULL AND l.chatbot_call_state::text = 'non_risposto'::text THEN 'no interagisce'::text
                    WHEN l.chatbot_process_state IS NOT NULL AND l.chatbot_call_state::text <> 'non_risposto'::text AND l.chatbot_call_state::text <> 'da_chiamare'::text AND l.chatbot_call_state::text <> 'da_richiamare'::text THEN 'scarto'::text
                    ELSE 'No Valeria'::text
                END AS dettaglio_stato_bot, a.f_ref, a.paid, tc.name,
                CASE
                    WHEN l.f_dup = 0 AND l.f_oper = 0 AND a.recalled < 3 AND a.paid = 1 AND a.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND a.date_end < 'now'::text::date THEN 1
                    ELSE 0
                END AS num_app_paid, a.price, a.customer_id, a.date_start, a.date_end, a.recalled, a.cancelled_at, a.created_at AS created_at_a, s.alias, l.company, l.first_user_id, l.uid AS id_italia, l.tenant, l."region", l.category,
                CASE
                    WHEN lower(l.campagna::text) ~~* '%corpo%'::text THEN 'corpo'::text
                    WHEN lower(l.campagna::text) ~~* '%laser%'::text THEN 'laser'::text
                    ELSE 'altro'::text
                END AS categoria_lead_d, l.treatment, l.pricing2, l.campagna, l.first_assign_at, l.created_at AS created_at_l, users.firstname, users.lastname, l.created_at, ( SELECT sum(
                        CASE
                            WHEN appointments_tmp.paid = 1 THEN 1
                            ELSE NULL::integer
                        END) AS sum
                   FROM appointments appointments_tmp
                  WHERE appointments_tmp.customer_id = a.customer_id AND appointments_tmp.company::text = a.company::text AND appointments_tmp.tenant::text = a.tenant::text AND appointments_tmp.paid = 1 AND appointments_tmp.date_end > a.date_end AND appointments_tmp.date_end < add_months(a.date_end, 3::bigint) AND l.f_dup = 0 AND l.f_oper = 0 AND a.recalled IS NOT NULL AND a.recalled < 3 AND a.paid = 1 AND a.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND a.date_end < 'now'::text::date) AS app_succ_tot, ( SELECT sum(appointments_tmp.price) AS sum
                   FROM appointments appointments_tmp
                  WHERE appointments_tmp.customer_id = a.customer_id AND appointments_tmp.company::text = a.company::text AND appointments_tmp.tenant::text = a.tenant::text AND appointments_tmp.paid = 1 AND appointments_tmp.id <> a.id AND date(appointments_tmp.date_end) = date(a.date_end) AND l.f_dup_mkt = 0 AND a.recalled IS NOT NULL AND a.recalled < 3 AND a.paid = 1 AND a.date_end >= '2022-01-01 00:00:00'::timestamp without time zone AND a.date_end < 'now'::text::date) AS incasso_succ_tot
           FROM leads l
      JOIN customers c ON l.customer_id = c.id AND l.company::text = c.company::text AND l.tenant::text = c.tenant::text
   LEFT JOIN appointments a ON l.id = a.lead_id AND l.company::text = a.company::text AND (l.tenant::text = a.tenant::text OR l.tenant::text = 'Italia'::text)
   LEFT JOIN users ON l.first_user_id = users.id AND l.company::text = users.company::text AND l.tenant::text = users.tenant::text
   LEFT JOIN stores s ON s.id = a.store_id AND s.company::text = a.company::text AND s.tenant::text = a.tenant::text
   LEFT JOIN treatments t ON t.id = a.treatment_id AND t.company::text = a.company::text AND t.tenant::text = a.tenant::text
   LEFT JOIN treatment_categories tc ON t.treatment_category_id = tc.id AND t.company::text = tc.company::text AND t.tenant::text = tc.tenant::text
  WHERE l.first_assign_at >= '2025-04-01 00:00:00'::timestamp without time zone AND l.first_assign_at < 'now'::text::date AND lower(l.category::text) <> 'confermacomm'::text AND lower(l.category::text) <> 'confermaop'::text AND (l.f_dup_mkt = 0 OR l.f_dup_mkt IS NULL) AND NOT (EXISTS ( SELECT a4.id
   FROM appointments a4
  WHERE a4.recalled < 4 AND a4.id < a.id AND a4.lead_id = a.lead_id AND a4.company::text = a.company::text AND a4.tenant::text = a.tenant::text AND a4.deleted_at IS NULL)) AND users.active IS TRUE AND l.deleted_at IS NULL AND a.deleted_at IS NULL AND (a.recalled IS NULL OR a.recalled < 4) AND (l.company::text = 'Biolaser'::text OR l.company::text = 'Biosmed'::text)) dati
   LEFT JOIN conversation_analysis_results ON dati.id_italia=conversation_analysis_results.lead_id
   LEFT JOIN rimpalli_per_sessione ON dati.id_italia=rimpalli_per_sessione.lead_id
  GROUP BY dati.company, dati.bot, upper(dati.category::text), to_char(dati.first_assign_at, 'YYYY-MM-DD'::text), to_char(dati.created_at_l, 'YYYY-MM-DD'::text), upper(dati.first_user_id::text), dati.firstname, dati.lastname, dati.treatment, upper(dati.pricing2::text), dati.campagna,
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled <= 3 THEN dati.alias
            ELSE NULL::character varying
        END,
        CASE
            WHEN (dati.f_dup_mkt = 0 OR dati.f_dup_mkt IS NULL) AND dati.recalled IS NOT NULL AND dati.recalled <= 3 THEN dati.name
            ELSE NULL::character varying
        END, dati.stato_bot, dati.dettaglio_stato_bot, dati.id_italia, upper(dati.categoria_lead_d),
        conversation_analysis_results.has_price_request,
        conversation_analysis_results.appointment_taken_after_price,
        rimpalli_per_sessione.ai_rimpalli,
        rimpalli_per_sessione.human_rimpalli,
        rimpalli_per_sessione.has_preferred_call_time
  ORDER BY dati.company, to_char(dati.first_assign_at, 'YYYY-MM-DD'::text) DESC, upper(dati.first_user_id::text), lower(dati.lastname::text), lower(dati.firstname::text);