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

# Load sample social media posts data
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))

# Beginner Example 1: Calculate engagement rate
df['engagement_rate'] = (df['likes'] + df['comments'] + df['shares']) / df['views'] * 100
print(df[['platform', 'views', 'likes', 'comments', 'shares', 'engagement_rate']].head(5))

# Beginner Example 2: Identify top 5 posts by views
top_views = df.sort_values('views', ascending=False).head(5)
print(top_views[['platform', 'views', 'likes', 'comments', 'shares', 'date']])

# Beginner Example 3: Most engaging posts overall
most_engaging = df.sort_values('engagement_rate', ascending=False).head(5)
print(most_engaging[['post_id', 'platform', 'views', 'engagement_rate']])

# Intermediate Example 1: Average engagement rate by platform
avg_engagement = df.groupby('platform')['engagement_rate'].mean().sort_values(ascending=False)
print('Average engagement rate by platform:')
print(avg_engagement)

# Intermediate Example 2: Engagement trends over time
df['week'] = df['date'].dt.isocalendar().week
weekly_trend = df.groupby(['platform', 'week'])['engagement_rate'].mean().reset_index()
pivot = weekly_trend.pivot(index='week', columns='platform', values='engagement_rate')
pivot.plot(figsize=(10,5), title='Weekly Engagement Rate Trend by Platform')

# Intermediate Example 3: Find posts with above-average engagement
overall_avg = df['engagement_rate'].mean()
above_avg = df[df['engagement_rate'] > overall_avg]
print(f'Number of posts above global average engagement rate: {len(above_avg)}')
print(above_avg[['platform', 'views', 'engagement_rate']].head(5))

# Intermediate Example 4: Best posting time analysis
df['hour'] = df['date'].dt.hour
hourly_engagement = df.groupby('hour')['engagement_rate'].mean()
import matplotlib.pyplot as plt
plt.figure(figsize=(8,4))
hourly_engagement.plot(kind='bar')
plt.title('Average Engagement Rate by Posting Hour')
plt.xlabel('Hour of Day')
plt.ylabel('Engagement Rate (%)')
plt.show()

# Advanced Example 1: Load and join YouTube trending data for multi-platform analysis
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))

# Advanced Example 2: Detect viral YouTube videos using like-to-view ratio
yt_df['like_view_ratio'] = yt_df['likes'] / yt_df['views']
viral = yt_df[yt_df['like_view_ratio'] > 0.05]
print(f'Total viral videos detected: {len(viral)}')
print(viral[['video_id', 'title', 'views', 'likes', 'like_view_ratio']].head(5))

# Advanced Example 3: Combine platform datasets for unified analysis
social_summary = df.groupby('platform').agg({
    'views': 'sum',
    'likes': 'sum',
    'comments': 'sum',
    'shares': 'sum',
    'engagement_rate': 'mean'
}).reset_index()
yt_summary = yt_df.agg({
    'views': 'sum',
    'likes': 'sum',
    'comment_count': 'sum',
    'like_view_ratio': 'mean'
}).rename({'comment_count':'comments','like_view_ratio':'engagement_rate'})
yt_summary['platform'] = 'YouTube (Trending)'
combined = pd.concat([social_summary, yt_summary.to_frame().T], ignore_index=True)
print(combined[['platform','views','likes','comments','engagement_rate']])

# Error Example 1: Handle missing engagement values
with_missing = df.copy()
with_missing.loc[::10, 'likes'] = np.nan
print('Rows with missing likes:', with_missing['likes'].isna().sum())
filled = with_missing.fillna({'likes': 0})
print('Preview row where value was set to zero:')
print(filled.iloc[10][['platform','views','likes']])

# Error Example 2: Incorrect metric aggregation (sum vs mean)
grouped_sum = df.groupby('platform')['engagement_rate'].sum()
grouped_mean = df.groupby('platform')['engagement_rate'].mean()
print('Sum of engagement rates (incorrect):')
print(grouped_sum)
print('Mean engagement rates (correct):')
print(grouped_mean)

# Error Example 3: Wrong engagement rate denominator
bad_df = df.copy()
bad_df['bad_engagement'] = (bad_df['likes'] + bad_df['comments'] + bad_df['shares']) / (bad_df['likes'] + 1)
comparison = bad_df[['views','likes','bad_engagement','engagement_rate']].head(3)
print(comparison)

# Error Example 4: Misinterpreting or mis-grouping data
wrong_group = df.groupby('likes')['engagement_rate'].mean()
print('Grouped by likes value, which is rarely useful!')
print(wrong_group.head())

# Analytics Pattern 1: Content benchmarking report
benchmarks = df.groupby('platform').agg({'engagement_rate':'mean','views':'mean'}).reset_index()
benchmarks['engagement_rate'] = benchmarks['engagement_rate'].round(2)
benchmarks['views'] = benchmarks['views'].round(0).astype(int)
print('Average engagement rate and views by platform:')
print(benchmarks)

# Analytics Pattern 2: Trend and growth analysis using time series
df['date_only'] = df['date'].dt.date
daily_eng = df.groupby('date_only')['engagement_rate'].mean()
import matplotlib.pyplot as plt
plt.figure(figsize=(8,4))
daily_eng.plot()
plt.title('Daily Average Engagement Rate Over Time')
plt.xlabel('Date')
plt.ylabel('Engagement Rate (%)')
plt.tight_layout()
plt.show()

# Analytics Pattern 3: Audience segmentation example
segmented = df.groupby(['platform','hour'])['engagement_rate'].mean().reset_index()
best_times = segmented.sort_values('engagement_rate', ascending=False).groupby('platform').first().reset_index()
print('Best posting hour by platform:')
print(best_times[['platform','hour','engagement_rate']])

# Analytics Pattern 4: Outlier/content anomaly detection
q3 = df['engagement_rate'].quantile(0.75)
iqr = q3 - df['engagement_rate'].quantile(0.25)
outliers = df[df['engagement_rate'] > q3 + 1.5*iqr]
print(f'Total outlier (anomalously engaging) posts: {len(outliers)}')
print(outliers[['platform', 'views', 'engagement_rate']].head(3))

# End-to-End Problem: Recommend best posting strategy from raw data
top_posts = df.sort_values(['engagement_rate'], ascending=False).groupby('platform').head(3)
recommendations = top_posts.groupby('platform').agg({'hour':'median','engagement_rate':'mean'}).reset_index()
recommendations['hour'] = recommendations['hour'].astype(int)
print('Recommended posting hour and content focus per platform:')
print(recommendations)

# Export dashboard as CSV for wider team use
combined.to_csv('content_analytics_report.csv', index=False)
print('Report written to content_analytics_report.csv')

# Save a visualization as an image file
import matplotlib.pyplot as plt
fig, ax = plt.subplots(figsize=(8,4))
hourly_engagement.plot(kind='bar', ax=ax)
plt.title('Average Engagement Rate by Posting Hour')
plt.xlabel('Hour of Day')
plt.ylabel('Engagement Rate (%)')
plt.tight_layout()
plt.savefig('best_time_chart.png')
plt.close()
print('Chart saved as best_time_chart.png')