#! /usr/bin/env python
import sys

sys.path.insert(0, '.')

from tracker import config, mailer
from tracker.logger import get_logger

logger = get_logger(__name__)

db_name = config.db_connection_uri.split('/')[-1]

from tracker import db
db.setup_session()


table_ctes = '''
days as (
  select generate_series(current_date-15, current_date, '1 day') as d
),

pops as (
  select as_of::date d, count(*) c
  from spy_track_popularity
  where as_of > now() - interval '10 days'
  group by 1
),
tracks as (
  select first_seen::date d, count(*) c
  from spy_tracks 
  where first_seen > now() - interval '10 days'
  group by 1
),
spy_artists as (
  select first_seen::date d, count(*) c
  from spy_artists 
  where first_seen > now() - interval '10 days'
  group by 1
),

twt_follows as (
  select seen_at::date d, count(*) f 
  from followings 
  where seen_at > now() - interval '10 days'
  group by 1
),

yt_new as (
 select first_seen::date d, count(*) f 
 from yt_videos 
 where first_seen > now() - interval '10 days'
 group by 1
),

yt_stats as (
  select as_of::date d, count(*) f 
  from yt_video_statistics 
  where as_of > now() - interval '10 days'
  group by 1 
),

follows as (
  select seen_at::date d, count(*) as f 
  from sc_followings
  where seen_at > now() - interval '10 days' 
  group by 1
),

likes as (
  select seen_at::date d, count(*) f 
  from sc_likes 
  where seen_at > now() - interval '10 days'
  group by 1
),

in_users as (
  select first_seen::date d, count(*) f 
  from in_user 
  where first_seen > now() - interval '10 days'
  group by 1
),

in_follows as (
  select first_seen::date d, count(*) f 
  from in_follows 
  where first_seen > now() - interval '10 days'
  group by 1
),
in_stats as (
  select as_of::date d, count(*) f
  from in_stats
  where as_of > now() - interval '15 days'
  group by 1
),
sc_tracks as (
  select first_seen::date d, count(*) f 
  from sc_tracks 
  where first_seen > now() - interval '10 days'
  group by 1
),
tt_sounds as (
  select first_seen::date d, count(*) f 
  from tiktok_sounds
  where first_seen > now() - interval '10 days'
  group by 1
),
tt_stats as (
  select as_of::date d, count(*) f 
  from tiktok_sound_stats
  where as_of > now() - interval '10 days'
  group by 1
)
'''

qry1 = f'''
with {table_ctes}
select 
  days.d::date date,
  pops.c "Spy Pops", 
  tracks.c "Spy Tracks",
  spy_artists.c "Spy Artists",
  twt_follows.f "Twt Follows"
from days
left join pops using(d) 
left join tracks using(d)
left join spy_artists using(d)
left join twt_follows using(d)

order by days.d desc
limit 10
'''

qry2 = f'''
with {table_ctes}
select 
  days.d::date date,
  follows.f "Sc Follows",
  likes.f "Sc Likes",

  sc_tracks.f as "Sc Tracks",
  tt_sounds.f as "Tk Sounds",
  tt_stats.f as "Tk Stats"
  
from days
left join likes using(d)
left join follows using(d)
left join sc_tracks using(d)
left join tt_sounds using(d)
left join tt_stats using(d)

order by days.d desc
limit 10
'''

qry3 = f'''
with {table_ctes}
select 
  days.d::date date,
  yt_new.f "YT Vids",
  yt_stats.f "YT Stats",
  in_users.f as "In Users",
  in_follows.f as "In Follows",
  in_stats.f as "In Stats"
  
from days
left join yt_new using(d)
left join yt_stats using(d)
left join in_users using(d)
left join in_follows using(d)
left join in_stats using(d)

order by days.d desc
limit 10
'''

table = db.html_table_query(qry1)
logger.info("done query 1")

table += db.html_table_query(qry2)
logger.info("done query 2")

table += db.html_table_query(qry3)
logger.info("done query 3")

# table += db.html_table_query(qry4)
# logger.info("done query 4")



table += "<br><br><h3>Sc Track Stats</h3><br>"

max_part = db.ScTrackStats.max_partition_num()

sc_track_stats_query = "\n UNION ALL \n".join([
    f'''
select
  '{part}' as "Partition",
  count(1) filter (where as_of = current_date) as "Today",
  count(1) filter (where as_of = current_date - 1) as "Yesterday",
  count(1) filter (where as_of = current_date - 2) as "2 Days Ago",
  count(1) filter (where as_of = current_date - 3) as "3 Days Ago",
  count(1) filter (where as_of = current_date - 4) as "4 Days Ago",
  count(1) filter (where as_of = current_date - 5) as "5 Days Ago"
from sc_track_stats_{part}
where as_of = any(array[current_date, current_date-1, current_date-2, current_date-3, current_date-4, current_date-5])
    '''
for part in range(max_part, max_part-4, -1)])

table += db.html_table_query(sc_track_stats_query)
logger.info("Done sc stats query")

table += "<br><h3>Instagram Scouts New Follows</h3><br>"
table += db.html_table_query("""
with
scouts as (
  select
  iu.inid,
  current_date - iu.influencer_last_updated::date updated_days_ago,
  count(distinct followee_inid) filter (where i.first_seen::date = current_date - 0) d0,
  count(distinct followee_inid) filter (where i.first_seen::date = current_date - 1) d1,
  count(distinct followee_inid) filter (where i.first_seen::date = current_date - 2) d2,
  count(distinct followee_inid) filter (where i.first_seen::date = current_date - 3) d3,
  count(distinct followee_inid) filter (where i.first_seen::date between current_date - 7 and current_date - 4) d47,
  count(distinct followee_inid) total,
  count(distinct username) users
from users_in_influencers
join in_user iu on users_in_influencers.inid = iu.inid and iu.active and iu.not_found_at is null
left join in_follows i on iu.inid = i.followed_by_inid
group by 1, 2
)
select
  case
    when updated_days_ago < 7 then updated_days_ago || '. ' || (current_date - updated_days_ago)
    when updated_days_ago is null then 'Z. Never'
    else 'X. > 7 days ago.'
  end as "Last Updated",
  count(1) "Num Scouts",
  sum(d0) "Today",
  sum(d1) "Yesterday",
  sum(d2) "D -2",
  sum(d3) "D -3",
  sum(d47) "D 4-7"
from scouts
group by 1
order by 1 asc""")
logger.info("Done in scouts query")

table += "<br><h3>Spy Artist Page Scrapes</h3><br>"
table += db.html_table_query(f"""
select
  case
    when web_page_last_scraped is null then '7. never'
    when web_page_last_scraped::date < current_date - 5 then '6. > 5 days ago'
    else (current_date - web_page_last_scraped::date)::text || '. ' || (web_page_last_scraped::date::text)
  end as "Page Last Scraped",
  count(*) "Count"
from spy_artists
group by 1
order by 1 asc
""")
logger.info("Dont spy artist page query")


from datetime import date

mailer.send_simple_message(
    subject="Tracker Stats for {}".format(date.today().strftime('%d/%m')),
    html='<html>{table}</html>'.format(table=table),
)