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

# Beginner Example 1: Load a sample 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.head(3))

# Beginner Example 2: Calculate basic engagement rate
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))

# Beginner Example 3: Find most viewed post
top_post = df.sort_values('views', ascending=False).head(1)
print(top_post[['post_id', 'platform', 'views', 'likes', 'comments', 'shares']])

# Intermediate Example 1: Group by platform and calculate average engagement
platform_group = df.groupby('platform')['engagement_rate'].mean().reset_index()
print(platform_group)

# Intermediate Example 2: Identify top 5% highest engagement posts
threshold = df['engagement_rate'].quantile(0.95)
top_engaged = df[df['engagement_rate'] >= threshold]
print(top_engaged[['post_id', 'platform', 'engagement_rate']].head(5))

# Intermediate Example 3: Analyze daily engagement trends
daily = df.groupby(df['date'].dt.date)['engagement_rate'].mean().reset_index()
print(daily.head(5))

# Intermediate Example 4: Find posts with zero comments (low discussion)
no_comment_posts = df[df['comments'] == 0]
print(no_comment_posts[['post_id', 'platform', 'views', 'likes', 'comments']].head())

# Intermediate Example 5: Most shared post per platform
idx = df.groupby('platform')['shares'].idxmax()
most_shared = df.loc[idx][['platform', 'post_id', 'shares', 'engagement_rate']]
print(most_shared)

# Advanced Example 1: Simulated YouTube trending data load (fallback to synthetic)
try:
    from googleapiclient.discovery import build
    from google_auth_oauthlib.flow import InstalledAppFlow
    from google.auth.transport.requests import Request
    import os, pickle
    from pathlib import Path
    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)
    df_yt = fetch_yt_trending()
    print('Live trending data:', df_yt.shape)
except Exception as e:
    print(f'Falling back to synthetic: {e}')
    np.random.seed(42)
    n = 1000
    df_yt = 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_yt.shape)
print(df_yt.head(3))

# Advanced Example 2: Calculate like-to-view and comment-to-view ratios for trending videos
df_yt['like_rate'] = (df_yt['likes'] / df_yt['views']) * 100
df_yt['comment_rate'] = (df_yt['comment_count'] / df_yt['views']) * 100
print(df_yt[['video_id', 'views', 'likes', 'comment_count', 'like_rate', 'comment_rate']].head(5))

# Advanced Example 3: Identify channels with consistently high engagement
engagement_mean = df_yt.groupby('channel_title')[['like_rate', 'comment_rate']].mean().reset_index()
top_channels = engagement_mean.sort_values('like_rate', ascending=False).head(3)
print(top_channels)

# Advanced Example 4: Detect possible viral videos using statistical outliers
viral_threshold = df_yt['views'].quantile(0.99)
viral_videos = df_yt[df_yt['views'] >= viral_threshold]
print(viral_videos[['video_id', 'title', 'views', 'likes']].head())

# Error Handling 1: Check for missing engagement values
missing_any = df.isnull().sum()
print(missing_any)

# Error Handling 2: Aggregate metrics incorrectly (demonstration of a common mistake)
wrong_group = df.groupby('platform')[['likes', 'comments', 'shares']].sum() / df.groupby('platform')[['views']].sum() * 100
print(wrong_group)

# Error Handling 3: Misinterpreting click-through rate (CTR) with made-up data
yt_analytics = pd.DataFrame({
    'video_id': range(1, 6),
    'views': [10000, 20000, 5000, 35000, 8000],
    'ctr': [2.5, 15.0, 4.2, 9.0, 6.5],   # Not all are realistic values
})
print(yt_analytics)

# Debugging 1: Identify wrong groupby columns for content category
if 'category_id' in df_yt.columns:
    cat_agg = df_yt.groupby('category_id')['views'].mean().reset_index()
    print(cat_agg.head())
else:
    print('category_id column not found!')

# Common analytics pattern: Benchmark each post against platform average
platform_avgs = df.groupby('platform')['engagement_rate'].transform('mean')
df['above_platform_avg'] = df['engagement_rate'] > platform_avgs
print(df[['post_id', 'platform', 'engagement_rate', 'above_platform_avg']].head(5))

# Common analytics pattern: Segment by posting time of day
df['hour'] = df['date'].dt.hour
hourly_engagement = df.groupby('hour')['engagement_rate'].mean().reset_index()
print(hourly_engagement.sort_values('engagement_rate', ascending=False).head())

# Step 1: Top 3 posts by engagement rate
top3_posts = df.sort_values('engagement_rate', ascending=False).head(3)
print(top3_posts[['post_id', 'platform', 'date', 'engagement_rate']])

# Step 2: Best posting time window by engagement
best_hour = hourly_engagement.sort_values('engagement_rate', ascending=False).head(1)
print(best_hour)

# Step 3: Platform with highest average engagement
best_platform_row = platform_group.sort_values('engagement_rate', ascending=False).head(1)
platform_name = best_platform_row.iloc[0]['platform']
print(f'Platform with highest engagement rate: {platform_name}')