import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')

# Example 1: Load a synthetic social media content dataset
np.random.seed(42)
n_posts = 500
views = np.random.randint(100, 100000, n_posts)
df = pd.DataFrame({
    'post_id': range(1, n_posts+1),
    'platform': np.random.choice(['YouTube','Instagram','TikTok'], n_posts),
    'date': pd.date_range('2023-01-01', periods=n_posts, freq='6h'),
    'views': views,
    'likes': (views * np.random.uniform(0.02, 0.15, n_posts)).astype(int),
    'comments': (views * np.random.uniform(0.001, 0.05, n_posts)).astype(int),
    'shares': (views * np.random.uniform(0.001, 0.03, n_posts)).astype(int)
})
print(df.shape)
print(df.head(3))

# Example 2: Calculate engagement rate for each post
df['engagement_rate'] = ((df['likes'] + df['comments'] + df['shares']) / df['views']) * 100
print(df[['post_id','platform','views','likes','comments','shares','engagement_rate']].head(3))

# Example 3: Find the most popular post by total views
top_post = df.loc[df['views'].idxmax()]
print('Top post by views:')
print(top_post[['post_id','platform','date','views','likes','comments','shares','engagement_rate']])

# Example 4: Calculate average engagement rate by platform
avg_engage_platform = df.groupby('platform')['engagement_rate'].mean().sort_values(ascending=False)
print('Average engagement rate by platform (descending):')
print(avg_engage_platform)

# Example 5: Time trend of content posting and engagement
df_sorted = df.sort_values('date')
df_sorted.set_index('date', inplace=True)
rolling_engage = df_sorted['engagement_rate'].rolling(window=20).mean()
import matplotlib.pyplot as plt
plt.figure(figsize=(10, 4))
plt.plot(rolling_engage, label='Rolling Engagement Rate (%)')
plt.xlabel('Date')
plt.ylabel('Engagement Rate (%)')
plt.title('Rolling Engagement Rate Over Time')
plt.legend()
plt.tight_layout()
plt.show()

# Example 6: Identify posts with viral-like engagement
viral = df[df['engagement_rate'] > df['engagement_rate'].quantile(0.99)]
print(f'Number of viral-like posts: {len(viral)}')
print(viral[['post_id','platform','views','likes','comments','shares','engagement_rate']].head())

# Example 7: Import and preview a YouTube trending dataset (synthetic fallback)
import os, pickle
from pathlib import Path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

SCOPES = ['https://www.googleapis.com/auth/youtube.readonly']

def get_yt_service():
    api_key = os.environ.get('YOUTUBE_API_KEY')
    if api_key:
        return build('youtube', 'v3', developerKey=api_key)
    if Path('client_secret.json').exists():
        creds = None
        if Path('token_ro.pickle').exists():
            with open('token_ro.pickle', 'rb') as f:
                creds = pickle.load(f)
        if not creds or not creds.valid:
            if creds and creds.expired and creds.refresh_token:
                creds.refresh(Request())
            else:
                flow = InstalledAppFlow.from_client_secrets_file('client_secret.json', SCOPES)
                creds = flow.run_local_server(port=0)
            with open('token_ro.pickle', 'wb') as f:
                pickle.dump(creds, f)
        return build('youtube', 'v3', credentials=creds)
    raise EnvironmentError('Set YOUTUBE_API_KEY or provide client_secret.json')

def fetch_yt_trending(max_results=200, region='US'):
    youtube = get_yt_service()
    records, token = [], None
    while len(records) < max_results:
        resp = youtube.videos().list(
            part='snippet,statistics',
            chart='mostPopular',
            regionCode=region,
            maxResults=min(50, max_results - len(records)),
            pageToken=token
        ).execute()
        for item in resp.get('items', []):
            s = item['snippet']; st = item.get('statistics', {})
            records.append({
                'video_id':      item['id'],
                'trending_date': pd.Timestamp.today().date(),
                'title':         s.get('title', ''),
                'channel_title': s.get('channelTitle', ''),
                'category_id':   s.get('categoryId', ''),
                'views':         int(st.get('viewCount', 0)),
                'likes':         int(st.get('likeCount', 0)),
                'comment_count': int(st.get('commentCount', 0)),
            })
        token = resp.get('nextPageToken')
        if not token: break
    return pd.DataFrame(records)

try:
    yt_df = fetch_yt_trending()
    print('Live trending data:', yt_df.shape)
except Exception as e:
    print(f'Falling back to synthetic: {e}')
    np.random.seed(42)
    n = 1000
    yt_df = pd.DataFrame({
        'video_id':      [f'vid{i}' for i in range(n)],
        'trending_date': pd.date_range('2023-01-01', periods=n, freq='D'),
        'title':         [f'Video Title {i}' for i in range(n)],
        'channel_title': np.random.choice(['ChannelA','ChannelB','ChannelC'], n),
        'category_id':   np.random.choice([1,2,10,22,24,28], n),
        'views':         np.random.randint(10000, 5000000, n),
        'likes':         np.random.randint(100, 200000, n),
        'comment_count': np.random.randint(10, 50000, n),
    })
    print('Synthetic fallback:', yt_df.shape)

print(yt_df.head(3))

# Example 8: Calculate average views and likes by YouTube category
category_stats = yt_df.groupby('category_id')[['views','likes']].mean().sort_values('views', ascending=False)
print('Mean views and likes by category (descending views):')
print(category_stats.head())

# Example 9: Top trending channels by total engagement (likes + comment_count)
yt_df['total_engagement'] = yt_df['likes'] + yt_df['comment_count']
channel_stats = yt_df.groupby('channel_title')['total_engagement'].sum().sort_values(ascending=False)
print('Top 5 channels by total engagement:')
print(channel_stats.head())

# Example 10: Identify possible viral trending videos
viral_trending = yt_df[yt_df['views'] > yt_df['views'].quantile(0.995)]
print(f'Top trending videos with viral-like view counts (N={len(viral_trending)}):')
print(viral_trending[['video_id','title','channel_title','views','likes','comment_count']].head())

# Example 11: Which day of the week trends the most?
yt_df['weekday'] = pd.to_datetime(yt_df['trending_date']).dt.day_name()
weekday_stats = yt_df.groupby('weekday')['views'].mean().sort_values(ascending=False)
print('Average trending views by weekday:')
print(weekday_stats)

# Example 12: Breakdown of engagement rate by channel
yt_df['engagement_rate'] = ((yt_df['likes'] + yt_df['comment_count']) / yt_df['views']) * 100
chan_rate = yt_df.groupby('channel_title')['engagement_rate'].mean().sort_values(ascending=False)
print('Channel engagement rates (mean, %):')
print(chan_rate.head())

# Example 13: Error Handling - What if some videos are missing likes?
yt_df_missing = yt_df.copy()
yt_df_missing.loc[yt_df_missing.sample(frac=0.02, random_state=42).index, 'likes'] = np.nan
yt_df_missing['likes_filled'] = yt_df_missing['likes'].fillna(0)
yt_df_missing['engagement_rate_recalculated'] = ((yt_df_missing['likes_filled'] + yt_df_missing['comment_count']) / yt_df_missing['views']) * 100
print('Null likes count:', yt_df_missing["likes"].isnull().sum())
print(yt_df_missing[['likes','likes_filled','engagement_rate_recalculated']].head(5))

# Example 14: Debugging - Incorrect metric aggregation
wrong_total_engage = yt_df_missing['likes'].sum() + yt_df_missing['comment_count'].sum()
correct_total_engage = yt_df_missing[['likes_filled','comment_count']].sum().sum()
print(f'Total engagement (ignoring fills): {wrong_total_engage:,.0f}')
print(f'Total engagement (handling missing likes): {correct_total_engage:,.0f}')

# Example 15: Debugging - Misinterpreting ratios (CTR, engagement rate)
fake_ctr = yt_df['likes'] / (yt_df['views'] * 2)
true_engage_rate = ((yt_df['likes'] + yt_df['comment_count']) / yt_df['views']) * 100
print('Fake CTR (misinterpreted):', fake_ctr.head(3).to_list())
print('True Engagement Rate (%):', np.round(true_engage_rate.head(3),2).to_list())

# Example 16: Debugging - Grouping mistakes: wrong platform aggregation
df_wrong = df.copy()
df_wrong['platform_low'] = df_wrong['platform'].str.lower()
df_wrong.loc[df_wrong.sample(frac=0.01, random_state=1).index,'platform_low'] = 'unknown'
counts = df_wrong.groupby('platform_low')['post_id'].count()
print('Counts by possibly-wrong platform labels:')
print(counts)

# Example 17: Content performance benchmarking vs. platform average
platform_avg = df.groupby('platform')['engagement_rate'].mean()
df['benchmark_vs_platform'] = df.apply(lambda row: row['engagement_rate'] - platform_avg.loc[row['platform']], axis=1)
print(df[['platform','engagement_rate','benchmark_vs_platform']].head(5))

# Example 18: Segment content by posting hour
df['hour'] = pd.to_datetime(df['date']).dt.hour
hourly = df.groupby('hour')['engagement_rate'].mean().sort_values(ascending=False)
print('Average engagement rate by hour posted:')
print(hourly)

# Example 19: Trend and growth analysis for engagement rates
window = 30
df_trend = df.sort_values('date').set_index('date').copy()
df_trend['trend30'] = df_trend['engagement_rate'].rolling(window=window, min_periods=1).mean()
import matplotlib.pyplot as plt
plt.figure(figsize=(8,4))
plt.plot(df_trend['trend30'], label='30-Post Rolling Mean Engagement Rate')
plt.title('Content Engagement Rate 30-Post Trend')
plt.ylabel('Engage Rate (%)')
plt.xlabel('Date')
plt.legend()
plt.tight_layout()
plt.show()

# Example 20: Content optimization - Find keywords in viral titles
viral_titles = yt_df[yt_df['views'] > yt_df['views'].quantile(0.995)]['title'].str.lower().str.cat(sep=' ')
from collections import Counter
keyword_counts = Counter([w for w in viral_titles.split() if len(w) > 3])
print('Most common long keywords in viral trending titles:')
print(keyword_counts.most_common(10))

# End-to-End Example: Recommend a content strategy from analytics
top_platform = df.groupby('platform')['engagement_rate'].mean().idxmax()
best_time = df.groupby('hour')['engagement_rate'].mean().idxmax()
viral_keywords = [k for k, _ in keyword_counts.most_common(5)]
print(f'Recommend posting most often on {top_platform}.')
print(f'Peak audience engagement hours: {best_time} local time.')
print(f'Include these keywords in titles or descriptions: {viral_keywords}')