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

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

print(df.head(3))

# Example 2: Load a synthetic Social Media Content dataset
np.random.seed(42)
n_posts = 500
views = np.random.randint(100, 100000, n_posts)
df_posts = 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_posts.shape)
print(df_posts.head(3))

# Example 3: Load a synthetic YouTube Analytics dataset
np.random.seed(42)
n_videos = 300
views_yta = np.random.randint(100, 500000, n_videos)
df_ytanal = pd.DataFrame({
    'video_id': range(1, n_videos+1),
    'publish_date': pd.date_range('2022-01-01', periods=n_videos, freq='D'),
    'views': views_yta,
    'watch_time': np.random.randint(1000, 500000, n_videos),
    'likes': (views_yta * np.random.uniform(0.01, 0.08, n_videos)).astype(int),
    'comments': (views_yta * np.random.uniform(0.001, 0.02, n_videos)).astype(int),
    'ctr': np.round(np.random.uniform(2, 10, n_videos), 2)
})

print(df_ytanal.shape)
print(df_ytanal.head(3))

# Example 4: Load a simple social media time series dataset
np.random.seed(42)
n_days = 365
dates = pd.date_range('2023-01-01', periods=n_days, freq='D')
views_ts = np.random.randint(1000, 50000, n_days)
likes_ts = (views_ts * np.random.uniform(0.03, 0.12, n_days)).astype(int)
df_timeseries = pd.DataFrame({
    'date': dates,
    'views': views_ts,
    'likes': likes_ts,
    'engagement_rate': np.round(likes_ts / views_ts * 100, 2)
})

print(df_timeseries.shape)
print(df_timeseries.head(3))

# Example 5: Load social media comments data for sentiment and text analysis
np.random.seed(42)
comments_data = [
    {'post_id': 1, 'platform': 'YouTube',   'comment': 'Great video, learned a lot!'},
    {'post_id': 1, 'platform': 'YouTube',   'comment': 'Very clear explanation, thank you.'},
    {'post_id': 2, 'platform': 'Instagram', 'comment': 'Amazing content as always.'},
    {'post_id': 2, 'platform': 'Instagram', 'comment': 'Not what I expected, a bit confusing.'},
    {'post_id': 3, 'platform': 'TikTok',    'comment': 'This is so helpful!'},
    {'post_id': 3, 'platform': 'TikTok',    'comment': 'Could you make a follow-up video?'},
    {'post_id': 4, 'platform': 'YouTube',   'comment': 'Terrible content, waste of time.'},
    {'post_id': 4, 'platform': 'YouTube',   'comment': 'I disagree with this approach.'},
    {'post_id': 5, 'platform': 'Instagram', 'comment': 'Loved it, sharing with my friends.'},
    {'post_id': 5, 'platform': 'TikTok',    'comment': 'The best tutorial I have seen.'},
]
df_comments = pd.DataFrame(comments_data)
df_comments['timestamp'] = pd.date_range('2023-01-01', periods=len(df_comments), freq='3h')

print(df_comments.shape)
print(df_comments.head(3))

# Example 6: Basic calculation  Engagement rate on posts dataset
df_posts['engagement_rate'] = ((df_posts['likes'] + df_posts['comments'] + df_posts['shares']) / df_posts['views'] * 100).round(2)
print(df_posts[['platform','views','likes','comments','shares','engagement_rate']].head(5))

# Example 7: Identify top-performing videos by views and engagement
top_views = df.sort_values('views', ascending=False).head(3)
top_engagement = (df.assign(engagement_rate=(df['likes']+df['comment_count'])/df['views']*100)
                 .sort_values('engagement_rate', ascending=False).head(3))
print('Top Videos by Views:')
print(top_views[['video_id','title','views','likes','comment_count']])
print('\nTop Videos by Engagement Rate:')
print(top_engagement[['video_id','title','views','likes','comment_count','engagement_rate']])

# Example 8: Summarize median metrics by content platform
by_platform = df_posts.groupby('platform').agg({'views':'median','likes':'median','comments':'median','shares':'median','engagement_rate':'median'})
print(by_platform)

# Example 9: Visualize trends  Daily engagement time series
import matplotlib.pyplot as plt
plt.figure(figsize=(11,5))
plt.plot(df_timeseries['date'], df_timeseries['engagement_rate'], label='Engagement Rate (%)')
plt.xlabel('Date')
plt.ylabel('Engagement Rate (%)')
plt.title('Daily Content Engagement Rate Over Time')
plt.legend()
plt.tight_layout()
plt.show()

# Example 10: Analyze CTR and watch time for video effectiveness
df_ytanal['engagement_index'] = (df_ytanal['likes'] + df_ytanal['comments']) / df_ytanal['views'] * df_ytanal['ctr']
top_effective = df_ytanal.sort_values('engagement_index', ascending=False).head(5)
print(top_effective[['video_id','views','watch_time','likes','comments','ctr','engagement_index']])

# Example 11: Detect viral spikes in content performance
viral_dates = df_timeseries[df_timeseries['engagement_rate'] > df_timeseries['engagement_rate'].quantile(0.99)]
print('Dates with viral engagement spikes:')
print(viral_dates[['date','engagement_rate']])

# Example 12: Find the best posting times for likes and shares
df_posts['hour'] = df_posts['date'].dt.hour
by_hour = df_posts.groupby('hour').agg({'likes':'mean','shares':'mean'}).sort_values('likes', ascending=False)
print(by_hour.head())

# Example 13: Analyze comment text for positive and negative sentiment clues
pos_keywords = ['great','clear','amazing','helpful','loved','best']
neg_keywords = ['confusing','terrible','disagree','waste']
df_comments['sentiment'] = df_comments['comment'].str.lower().apply(lambda x: 'positive' if any(word in x for word in pos_keywords) else ('negative' if any(word in x for word in neg_keywords) else 'neutral'))
print(df_comments[['comment','sentiment']])

# Example 14: Advanced  Detect inconsistent engagement metric definitions
posts_zero_views = df_posts[df_posts['views'] == 0]
if not posts_zero_views.empty:
    print('Warning: Some posts have zero views, which would make engagement rate unreliable!')
    print(posts_zero_views[['post_id','platform']])

# Example 15: Advanced  Check for missing values before grouping or aggregating
missing_engagement = df_posts[['likes','comments','shares']].isnull().sum()
print('Missing values in engagement columns:')
print(missing_engagement)
if missing_engagement.any():
    df_posts[['likes','comments','shares']] = df_posts[['likes','comments','shares']].fillna(0)
    print('Filled missing engagement values with zeros.')

# Example 16: Best practice  Benchmark content performance by percentile
p80 = df['views'].quantile(0.80)
benchmark80 = df[df['views'] >= p80]
print(f'There are {len(benchmark80)} trending videos above the 80th percentile of views.')

# Example 17: Best practice  Segment audience by post platform for optimization
audience_segments = df_posts.groupby('platform')['views'].agg(['count', 'mean', 'median', 'max'])
print(audience_segments)

# Example 18: Best practice  Analyze trend growth using rolling averages
df_timeseries['views_7d_avg'] = df_timeseries['views'].rolling(7).mean()
print(df_timeseries[['date','views','views_7d_avg']].tail(10))

# Example 19: Best practice  Consistent metric definitions for benchmarking and comparison
def compute_engagement_rate(row):
    if row['views'] < 1:
        return np.nan
    return (row['likes'] + row.get('comments',0) + row.get('shares',0)) / row['views'] * 100

df_posts['engagement_rate'] = df_posts.apply(compute_engagement_rate, axis=1).round(2)
print(df_posts[['platform','views','likes','comments','shares','engagement_rate']].head(3))

# End-to-end: Find top content categories for video engagement
df['engagement_rate'] = ((df['likes'] + df['comment_count']) / df['views'] * 100).round(2)
cat_summary = df.groupby('category_id')['engagement_rate'].mean().sort_values(ascending=False)
print('Top content categories by mean engagement rate:')
print(cat_summary.head())
best_cat = cat_summary.idxmax()
print(f'Strategy: Focus next content on category {best_cat} for the highest engagement potential.')