File size: 2,005 Bytes
0914e96 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
# File: ai-service/scripts/snapshot_daily_stats.py (NEW FILE)
# This is a backend script, no AI model yet.
import pandas as pd
from sqlalchemy import create_engine, text
from datetime import date, timedelta
import os, sys
# ... (Setup code to get ROOT_DIR and load_dotenv, like in our other scripts)
def take_daily_snapshot():
print("--- 📸 Starting Daily Influencer Stat Snapshot Process ---")
db_url = os.getenv("DATABASE_URL")
if not db_url: return
engine = create_engine(db_url)
today = date.today()
yesterday = today - timedelta(days=1)
# SQL to calculate yesterday's average stats for all influencers who made submissions
sql = text("""
WITH daily_agg AS (
SELECT
cs.influencer_id,
AVG(cs.likes) as avg_likes,
AVG(cs.comments) as avg_comments,
AVG(cs.engagement_rate) as avg_engagement_rate,
-- Get the latest follower count for the influencer
(SELECT ip.follower_count FROM public.influencer_profiles ip WHERE ip.profile_id = cs.influencer_id LIMIT 1) as follower_count
FROM public.campaign_submissions cs
WHERE DATE(cs.created_at) = :yesterday_date
GROUP BY cs.influencer_id
)
INSERT INTO public.daily_influencer_stats (profile_id, date, avg_likes, avg_comments, avg_engagement_rate, follower_count)
SELECT influencer_id, :yesterday_date, avg_likes, avg_comments, avg_engagement_rate, follower_count
FROM daily_agg
ON CONFLICT (profile_id, date) DO NOTHING; -- Avoid duplicates
""")
try:
with engine.connect() as connection:
result = connection.execute(sql, {'yesterday_date': yesterday})
print(f"✅ Snapshot complete. {result.rowcount} influencers' stats were updated for {yesterday}.")
except Exception as e:
print(f"🔴 ERROR during daily snapshot: {e}")
if __name__ == '__main__':
take_daily_snapshot() |