create or replace table temp_eu.200_users_aepd_avant as 
select * from (
select
    *,
    rand() as rand_
from `reby-cloud.misc_eu.user_consent`
where date(from_co_cu_id_created) between date('2020-08-19') AND current_date
and from_co_id = 'c_3r2qzjfpj8tq2sq3pan1'
)
where rand_ < 0.1
limit 200

DECLARE fecha_migracion TIMESTAMP;
SET fecha_migracion = '2021-06-16 00:52:53.131944 UTC';

create or replace table misc_eu.user_consent as
with first_user_session as (
    SELECT
    us.*,
    cu.id as cu_id,
    row_number() over(partition by cu.id order by us.created_at asc) as rank_
FROM `reby-cloud.analytics_reby_v1_eu.pg_user_session` us
LEFT JOIN `reby-cloud.reby_marketing_eu.pg_company_user` cu on us.company_id = cu.company_id and us.user_id = cu.user_id
QUALIFY rank_ = 1
),

rides as (
    select 
        id,
        created_at,
        user_id,
        if(vehicle_company_id is null,'c_3qteetfhxjshx4j54111',vehicle_company_id) as vehicle_company_id,
        if(company_id is null,'c_3qteetfhxjshx4j54111',company_id) as company_id
    from analytics_reby_v1_eu.pg_rides_json
),

first_ride_pre as (
    select
        r.id,
        r.user_id,
        r.created_at as ride_date,
        r.vehicle_company_id,
        r.company_id as app_co_id,
        co.name as vehicle_company_name,
        co2.name as app_company_name,
        cu.id as cu_id_vehicle,
        cu2.id as cu_id_app,
        --row_number() over (partition by r.user_id,r.vehicle_company_id order by r.created_at asc) as rank_
    from rides r
    left join `reby-cloud.analytics_reby_v1_eu.pg_company` co on r.vehicle_company_id = co.id
    left join `reby-cloud.analytics_reby_v1_eu.pg_company` co2 on r.company_id = co2.id
    left join `reby-cloud.reby_marketing_eu.pg_company_user` cu on r.user_id = cu.user_id and r.vehicle_company_id = cu.company_id
    left join `reby-cloud.reby_marketing_eu.pg_company_user` cu2 on r.user_id = cu2.user_id and r.company_id = cu2.company_id
    --where cu2.id = 'cu_3qtjhf229x9t31h4c2n1'
    --order by 2 desc
    --QUALIFY rank_= 1
),

first_ride_per_app_vehicle as (
select *, row_number() over (partition by user_id, app_company_name, vehicle_company_name order by ride_date asc) as rank_ from first_ride_pre qualify rank_ = 1
)
,

join_ride_session as (
select
    ur.*,
    --cu.user_id,
    --u.id as user_id,
    cu.created_at as date_comp_user_created,
    u.created_at as date_user_meta_created,
    fus.created_at as first_session_company,
    up.created_at as user_profile_vehicle_company_created_at,
    consent.consent_from_reby
from first_ride_per_app_vehicle ur
left join `reby-cloud.reby_marketing_eu.pg_company_user` cu on ur.cu_id_vehicle = cu.id
left join `reby-cloud.analytics_reby_v1_eu.pg_users_json` u on ur.user_id = u.id
--join `reby-cloud.temp_eu.200_users_aepd` u on u.cu_id = ur.app_co_id
left join first_user_session fus on fus.cu_id = ur.cu_id_vehicle
left join `reby-cloud.analytics_reby_v1_eu.pg_user_profile` up on ur.cu_id_vehicle = up.company_user_id
left join `reby-cloud.analytics_reby_v1_eu.pg_consent` consent on ur.cu_id_vehicle = consent.company_user_id
)

select
    jrs.*,
    case
        when app_company_name = 'Reby' and vehicle_company_name = 'Reby' then 'no consent necessary'
        --PRE-MIGRACION
        --when vehicle_company_name = app_company_name  and first_session_company is not null and first_session_company < fecha_migración then cast(first_session_company as string)
        --when vehicle_company_name = app_company_name and vehicle_company_name = 'Reby' and first_session_company is null then cast(date_user_meta_created as string)

        when app_company_name != 'Reby' and first_session_company is not null and first_session_company < fecha_migracion then cast(first_session_company as string)
        when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is null and ride_date < fecha_migracion then cast(ride_date as string)
        when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is not null and first_session_company < ride_date and first_session_company < fecha_migracion then cast(first_session_company as string)
        when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is not null and first_session_company >= ride_date and ride_date < fecha_migracion then cast(ride_date as string)

        --POST-MIGRACION
        when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is null then cast(date_comp_user_created as string)
        #when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and ride_date > fecha_migracion then cast(ride_date as string)
        when vehicle_company_name = app_company_name  and first_session_company is not null and first_session_company > fecha_migracion then 'consentimiento no necesaria'
        when app_company_name != 'Reby' and first_session_company is not null and first_session_company >= fecha_migracion then 'consentimiento no necesaria' 
        when app_company_name != 'Reby' and consent_from_reby is true and ride_date is not null then cast(ride_date as string)
        #when app_company_name = 'Reby' and consent_from_reby is true and user_profile_vehicle_company_created_at > date_comp_user_created then cast(user_profile_vehicle_company_created_at as string)
        when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and ride_date > fecha_migracion then cast(ride_date as string)
    
        else 'consentimiento no necesario'
    end as migration_consent,
    case
        when app_company_name = 'Reby' and vehicle_company_name = 'Reby' then 'Caso0 - Aplicacion y Compañia son las mismas'

        --PRE-MIGRACION
        when app_company_name != 'Reby' and first_session_company is not null and first_session_company < fecha_migracion then 'Caso1 - Consentimiento desde app company pre-migracion'
        when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is null and ride_date < fecha_migracion then 'Caso2 - Consentimiento desde app Reby pre-migracion'
        when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is not null and first_session_company < ride_date and first_session_company < fecha_migracion then 'Caso3 - Consentimiento desde app company pre-migracion'
        when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is not null and first_session_company >= ride_date and ride_date < fecha_migracion then 'Caso4 - Consentimiento desde app Reby pre-migracion'

        --POST-MIGRACION
        when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is null then 'Caso5 - Consentimiento desde app Reby post-migracion'
        #when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and ride_date > fecha_migracion then cast(ride_date as string)
        when vehicle_company_name = app_company_name  and first_session_company is not null and first_session_company > fecha_migracion then 'Caso6 - consentimiento no necesaria'
        when app_company_name != 'Reby' and first_session_company is not null and first_session_company >= fecha_migracion then 'Caso7 - consentimiento no necesaria' 
        when app_company_name != 'Reby' and consent_from_reby is true and ride_date is not null then 'Caso8 - Consentimiento desde app Reby post-migracion'
        #when app_company_name = 'Reby' and consent_from_reby is true and user_profile_vehicle_company_created_at > date_comp_user_created then cast(user_profile_vehicle_company_created_at as string)
        when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and ride_date > fecha_migracion then 'Caso9 - Consentimiento desde app Reby post-migracion'

        else 'Caso10 - Otros'
    end as caso
from join_ride_session jrs

select
    cu.id as user_id,
    id_to_time(cu.id) as created_at,
    usr.phone_number,
    co.name as company,
    cu.payment_processor__token,
    cu.consent_from_reby,
    acc.balance__amount/100 as balance_euros,
    up.driving_licence,
    up.national_id,
    email
from company_user cu --tabla principal de usuarios
left join user_profile up on up.company_user_id = cu.id --tabla con información adicional del usuario/a
left join public.user usr on cu.user_id = usr.id
left join company co on cu.company_id = co.id --tabla de compañias
left join account acc on cu.balance_account_id = acc.id
where usr.phone_number = '+34674780704'

with tx_dedup as (
    select
        *
    from (
        select
            transfer_id,
            row_number() over(partition by transfer_id order by amount desc) as rn_
        FROM `reby-cloud.temp_eu.delete_kernel_transactions`
    )
    where rn_ = 1
)

SELECT
    date_trunc(datetime(time_t),month) as month,
    sum(abs(amount))/100 as amount,
    count(*) as num_tx
FROM `reby-cloud.temp_eu.delete_kernel_transactions`
where transfer_id in (select transfer_id from tx_dedup)
group by 1
order by 1 desc
;
select
    transfer_id,
    count(*) as count_,
    array_agg(amount) as amounts,
    array_agg(type)
FROM `reby-cloud.temp_eu.delete_kernel_transactions`
group by 1
order by 1 desc