Insert from temp_leads

Insert from temp_leads to leads

SELECT t.*
FROM public.temp_leads t
WHERE 
    EXISTS (
        SELECT 1
        FROM public.leads l
        WHERE l.uid = t.id OR l.phone = t.phone
    )
    OR EXISTS (
        SELECT 1
        FROM public.family f
        WHERE f.uid = t.id  OR family_phone = t.phone
    )
ORDER BY t.id;

SELECT t.*
FROM public.temp_leads t
WHERE 
    EXISTS (
        SELECT 1
        FROM public.leads l
        WHERE l.phone = t.phone
    )
    OR EXISTS (
        SELECT 1
        FROM public.family f
        WHERE f.family_phone = t.phone  -- ganti kolom jika berbeda
    )
ORDER BY t.phone;

WITH rankedtempleads AS (
    SELECT 
        t.id,
        t."timestamp",
        t.branch,
        t.name,
        t.phone,
        t.email,
        t.dob,
        t.source,
        t.sremarks,
        t.medium,
        t.mremarks,
        t.content,
        t.pic,
        t.referrer,
        t."currentPath",
        t."agree_privacyPolicy",
        t.agree_newsletter,
        t.gclid,
        t.google_key,
        t.ttclid,
        t.form_id,
        t.gcampaign_id,
        t.gadgroup_id,
        t.gcreative_id,
        t.category_program,
        t.trial_day,
        t.free_trial,
        t.fbclid,
        t.fadset_id,
        t.fcampaign_id,
        t.fadset_name,
        t.fcampaign_name,
        t.tour_date,
        t.tour_time,
        t."dup?",
        t.contact_owner,
        t.sleekflow_id,
        t."conversationId",
        t.leads_qualification,
        t.interest,
        t.interest_remarks,
        t.offer_trial,
        t.reply_after_offer,
        t.sremarks_id,
        t.uid,
        t."assigned?",
        t.assigned_date,
        t.member_id,
        t.gender,
        t.hide,
        t.leads_notes,
        t.family_id,
        -- Ranking: tetap PARTITION BY phone, urut waktu ascending
        ROW_NUMBER() OVER (PARTITION BY t.phone ORDER BY t."timestamp") AS row_num
    FROM public.temp_leads t
    WHERE 
        -- Menyamakan dengan query pertama:
        NOT EXISTS (
            SELECT 1
            FROM public.leads l
            WHERE l.phone = t.phone
        )
        AND NOT EXISTS (
            SELECT 1
            FROM public.family f
            WHERE f.family_phone = t.phone
        )
        AND t.phone IS NOT NULL
)
INSERT INTO public.leads (
    uid,
    "timestamp",
    phone,
    email,
    name,
    gender,
    date_of_birth,
    age,
    age_group,
    branch,
    language,
    contact_owner,
    source,
    sremarks,
    content,
    medium,
    mremarks,
    assigned_pic,
    interest,
    leads_notes,
    leads_qualification,
    user_type,
    kyzn_account,
    sleekflow_id,
    leads_date,
    mql_date,
    "mqlPlus_date",
    "tourPlus_date",
    convert_date,
    loss_date,
    tour_date,
    "is_tour_show?",
    trial_date,
    "is_trial_show?",
    "is_inbound?",
    "is_leads?",
    "is_mql?",
    "is_mqlPlus?",
    "is_tourPlus?",
    "is_trialPlus?",
    "is_convert?",
    interest_remarks,
    submitted_email,
    assigned_email,
    "trialPlus_date",
    membership_name,
    package_name,
    start_membership,
    member_id,
    total_amount,
    payment_method,
    promotion,
    ref_program,
    ref_id,
    ref_name,
    ref_phone,
    identity_photo,
    status,
    convert_by,
    end_membership,
    signature,
    contract,
    offer_trial,
    reply_after_offer,
    last_appt_category,
    "conversationId",
    assigned_date,
    last_created_by,
    is_renewal,
    approach_by,
    app_status,
    start_approach,
    expired_approach,
    hide,
    id
)
SELECT 
    t.id AS uid,
    t."timestamp",
    t.phone,
    t.email,
    t.name,
    t.gender,
    t.dob AS date_of_birth,
    NULL AS age,
    NULL AS age_group,
    t.branch,
    NULL AS language,
    t.contact_owner,
    t.source,
    t.sremarks,
    t.content,
    t.medium,
    t.mremarks,
    t.pic AS assigned_pic,
    t.interest,
    COALESCE(t.free_trial, t.content, t.leads_notes) AS leads_notes,
    t.leads_qualification,
    NULL AS user_type,
    NULL AS kyzn_account,
    t.sleekflow_id,
    NULL AS leads_date,
    NULL AS mql_date,
    NULL AS "mqlPlus_date",
    NULL AS "tourPlus_date",
    NULL AS convert_date,
    NULL AS loss_date,
    t.tour_date,
    NULL AS "is_tour_show?",
    NULL AS trial_date,
    NULL AS "is_trial_show?",
    TRUE AS "is_inbound?",
    NULL AS "is_leads?",
    NULL AS "is_mql?",
    NULL AS "is_mqlPlus?",
    NULL AS "is_tourPlus?",
    NULL AS "is_trialPlus?",
    NULL AS "is_convert?",
    t.interest_remarks,
    NULL AS submitted_email,
    NULL AS assigned_email,
    NULL AS "trialPlus_date",
    NULL AS membership_name,
    NULL AS package_name,
    NULL AS start_membership,
    NULL AS member_id,
    NULL AS total_amount,
    NULL AS payment_method,
    NULL AS promotion,
    NULL AS ref_program,
    t.referrer AS ref_id,
    NULL AS ref_name,
    NULL AS ref_phone,
    NULL AS identity_photo,
    NULL AS status,
    NULL AS convert_by,
    NULL AS end_membership,
    NULL AS signature,
    NULL AS contract,
    t.offer_trial,
    t.reply_after_offer,
    NULL AS last_appt_category,
    t."conversationId",
    t.assigned_date,
    NULL AS last_created_by,
    NULL AS is_renewal,
    NULL AS approach_by,
    NULL AS app_status,
    NULL AS start_approach,
    NULL AS expired_approach,
    NULL AS hide,
    t.id
FROM rankedtempleads t
WHERE row_num = 1;

generate uid

UPDATE public.leads
SET id = uuid_generate_v4()
WHERE id IS NULL;