--Creación de usuarixs nuevxs
with base_data as (
select 
    date(u.created_at) as date,
    date_trunc(date(u.created_at),MONTH) as month,
    mv.service_area,
    sum(if(mv.first_ride_date is not null,1,0)) as has_ride,
    count(*) users_created
from `reby-cloud.analytics_reby_v1_eu.pg_users_json` u
left join `reby-cloud.reby_marketing_eu.users_servicearea_first_mv_ride` mv
    on u.id = mv.user_id
where mv.service_area = 'Sevilla'
group by 1,2,3
--order by 1 desc
)
select 
    *
    --lag(users_created) over (partition by month order by month asc) as prev_month_users
from base_data
order by date desc
;
--métricas de viajes individuales
select
    date(created_at) as date,
    r.id as ride_id,
    r.minutes,
    extract(hour from datetime(created_at,"Europe/Madrid")) as hour,
    if(plan_usage_id is null,0,1) as plan_usage,
    FORMAT_DATE('%A', date(created_at)) AS weekday_name,
    r.distance/100 as distance_km,
    r.ride_cost_time as ride_cost,
    geo.Distri_11D as distrito_inicial,
    geo2.Distri_11D as distrito_final,
from analytics_reby_v1_eu.pg_rides r
join `reby-cloud.analytics_reby_v1_eu.geo_sevilla_distritos` geo
    on st_within(st_geogpoint(r.longitude_initial,r.latitude_initial),geo.geometry)
join `reby-cloud.analytics_reby_v1_eu.geo_sevilla_distritos` geo2
    on st_within(st_geogpoint(r.longitude_final,r.latitude_final),geo2.geometry)
where r.service_area_id = 'sa_3qr9213ajv94b6v49h5h'
order by 1 desc
;
--ride linestrings
with rides_data as (
select
	id,
  created_at,
  starting.latitude as lat_init,
  starting.longitude as long_init,
  ending.latitude as lat_fin,
  ending.longitude as long_fin,
  distance/1000 as dist_km,
  minutes,
	--CONCAT(starting.latitude,',',starting.longitude) AS marker_start,
	--CONCAT(ending.latitude,',',ending.longitude) AS marker_end
from (
select
  r.created_at,
  r.minutes,
  r.distance,
  r.id,
  --path,
  path[offset(0)] as starting,
  path[offset(array_length(path)-1)] as ending,
  --array_length(path)
from `analytics_reby_v1_eu.pg_rides_json` r
left join `reby-cloud.analytics_reby_v1_eu.pg_vehicles` v
  on r.vehicle_id = v.id
where array_length(path) > 3
and date(r.created_at) >= '2021-06-01'
and date(r.created_at) <= '2021-09-01'
and r.service_area_id = 'sa_3qr9213ajv94b6v49h5h'
--and v.vehicle_type = 'motorcycle'
--where id = 'r_3qm5ua4jymv1ta3tbmq1'
--group by 1
  )
),

linestrings as (
select 
  ri.id,
  --st_asgeojson(st_geogpoint(rd.long_init,rd.lat_init)) as starting_point,
  concat("Linestring(",string_agg(concat(p.longitude," ",p.latitude), ","),")") as path
from `analytics_reby_v1_eu.pg_rides_json` ri, unnest(path) as p
where id in (select id from rides_data)
and date(created_at) >= '2021-06-01'
and date(created_at) <= '2021-09-01'
group by 1
),

linestrings_geo as (
select 
  rd.id,
  st_asgeojson(st_geogpoint(rd.long_init,rd.lat_init)) as starting_point,
  st_asgeojson(st_geogpoint(rd.long_fin,rd.lat_fin)) as ending_point,
  st_asgeojson(SAFE.st_geogfromtext(path)) as paths
from linestrings ls
join rides_data rd on ls.id = rd.id
)

select * from linestrings_geo