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

# Example 1: Load and preview the Social Media Content Dataset
np.random.seed(42)
n_posts = 500
views = np.random.randint(100, 100000, n_posts)
df_content = 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_content.shape)
df_content.head(3)

# Example 2: Load and preview daily content trends (time series)
np.random.seed(42)
n_days = 365
dates = pd.date_range('2023-01-01', periods=n_days, freq='D')
views_daily = np.random.randint(1000, 50000, n_days)
likes_daily = (views_daily * np.random.uniform(0.03, 0.12, n_days)).astype(int)
df_time = pd.DataFrame({
    'date': dates,
    'views': views_daily,
    'likes': likes_daily,
    'engagement_rate': np.round(likes_daily / views_daily * 100, 2)
})
print(df_time.shape)
df_time.head(3)

# Example 3: Calculate engagement rate per platform
df_content['engagement_rate'] = (df_content['likes'] + df_content['comments'] + df_content['shares']) / df_content['views'] * 100
platform_engagement = df_content.groupby('platform')['engagement_rate'].mean().round(2)
print(platform_engagement)

# Example 4: Identify top 5 posts by engagement rate
top_posts = df_content.sort_values('engagement_rate', ascending=False).head(5)
print(top_posts[['post_id', 'platform', 'views', 'engagement_rate']])

# Example 5: Time series trend analysis for engagement rate
import matplotlib.pyplot as plt
plt.figure(figsize=(10,5))
plt.plot(df_time['date'], df_time['engagement_rate'], label='Engagement Rate (%)')
plt.xlabel('Date')
plt.ylabel('Engagement Rate (%)')
plt.title('Daily Engagement Rate Over Time')
plt.legend()
plt.tight_layout()
plt.show()

# Example 6: Aggregate engagement by month
df_time['month'] = df_time['date'].dt.to_period('M')
monthly_engagement = df_time.groupby('month')['engagement_rate'].mean()
print(monthly_engagement.round(2))

# Example 7: Load YouTube Trending Videos Dataset (synthetic fallback if needed)
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:
    df_trending = fetch_yt_trending()
    print('Live trending data:', df_trending.shape)
except Exception as e:
    print(f'Falling back to synthetic: {e}')
    np.random.seed(42)
    n = 1000
    df_trending = 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:', df_trending.shape)

df_trending.head(3)

# Example 8: Calculate like-to-view ratio for trending videos
df_trending['like_view_ratio'] = (df_trending['likes'] / df_trending['views'] * 100).round(2)
print(df_trending[['title','likes','views','like_view_ratio']].head(5))

# Example 9: Find the top trending categories
top_cats = df_trending.groupby('category_id')['views'].sum().sort_values(ascending=False).head(3)
print('Top trending YouTube categories by total views:')
print(top_cats)

# Example 10: Identify most commented trending video
most_commented = df_trending.sort_values('comment_count', ascending=False).iloc[0]
print(f"Most commented trending video: {most_commented['title']} (Comments: {most_commented['comment_count']})")

# Example 11: Check for missing values in trending dataset (error handling)
missing = df_trending.isnull().sum()
print(missing[missing > 0])

# Example 12: Handle missing engagement by zero-filling
df_trending.fillna({'likes': 0, 'comment_count': 0}, inplace=True)
print(df_trending[['likes','comment_count']].isnull().sum())

# Example 13: Correcting wrong metric aggregation (sum vs mean)
wrong_sum = df_content.groupby('platform')['engagement_rate'].sum()
right_mean = df_content.groupby('platform')['engagement_rate'].mean()
print('Wrong (sum):')
print(wrong_sum)
print('Correct (mean):')
print(right_mean)

# Example 14: Misinterpreting engagement ratios (catching divide-by-zero)
df_content.loc[df_content['views'] == 0, 'engagement_rate'] = np.nan
n_zeros = df_content['engagement_rate'].isnull().sum()
print(f'Number of posts with undefined engagement rate due to zero views: {n_zeros}')

# Example 15: Wrong vs right grouping logic for YouTube categories
cat_mean_engagement = df_trending.groupby('category_id')['like_view_ratio'].mean().sort_values(ascending=False).head(3)
cat_wrong_agg = df_trending.groupby('category_id')[['likes','views']].sum()
cat_wrong_agg['ratio'] = (cat_wrong_agg['likes'] / cat_wrong_agg['views'] * 100).round(2)
wrong_top = cat_wrong_agg['ratio'].sort_values(ascending=False).head(3)
print(f'Right way (mean of individual ratios):\n{cat_mean_engagement}')
print(f'Wrong way (ratio of totals):\n{wrong_top}')

# Example 16: Benchmark content performance vs category average
chosen_category = df_trending['category_id'].iloc[0]
cat_avg = df_trending[df_trending['category_id'] == chosen_category]['like_view_ratio'].mean()
first_video_ratio = df_trending.iloc[0]['like_view_ratio']
print(f"First trending video like/view ratio: {first_video_ratio:.2f}%")
print(f"Category average like/view ratio: {cat_avg:.2f}%")

# Example 17: Segment audience by content type (platform)
segment = df_content.groupby('platform')[['views','likes','comments','shares']].mean().round(0)
print('Average metrics per platform:')
print(segment)

# Example 18: Detecting viral content patterns
viral_threshold = df_content['engagement_rate'].mean() + 2 * df_content['engagement_rate'].std()
viral_posts = df_content[df_content['engagement_rate'] > viral_threshold]
print(f'There are {len(viral_posts)} viral posts based on engagement rate > 2 standard deviations above the mean.')
viral_posts[['post_id','platform','engagement_rate']].head()

# Example 19: Visualize distribution of engagement rate
plt.hist(df_content['engagement_rate'].dropna(), bins=30, color='skyblue', edgecolor='black')
plt.xlabel('Engagement Rate (%)')
plt.ylabel('Number of Posts')
plt.title('Distribution of Engagement Rate Across All Social Posts')
plt.show()

# Example 20: Analyze growth trend in audience engagement over time
df_time['7d_avg'] = df_time['engagement_rate'].rolling(window=7).mean()
plt.figure(figsize=(10,5))
plt.plot(df_time['date'], df_time['7d_avg'], label='7-day Moving Average')
plt.xlabel('Date')
plt.ylabel('Engagement Rate (%)')
plt.title('Weekly Moving Average of Engagement Rate')
plt.legend()
plt.show()

# Example 21: End-to-end strategy: Find best posting time for highest engagement
df_content['hour'] = df_content['date'].dt.hour
hourly_engage = df_content.groupby('hour')['engagement_rate'].mean()
best_hour = hourly_engage.idxmax()
best_rate = hourly_engage.max()
print(f'Best posting hour (average engagement): {best_hour}:00 with {best_rate:.2f}% engagement rate')

# Example 22: End-to-end strategy: Recommend category to focus on
best_cat_id = cat_mean_engagement.idxmax()
print(f'Recommend focusing on category {best_cat_id} for the highest average YouTube engagement!')