#!/usr/bin/env python3
import sqlite3
from datetime import datetime, timedelta
from collections import defaultdict, Counter
import json

def get_analytics():
    conn = sqlite3.connect('data/data.db')
    cursor = conn.cursor()

    # Date cutoff
    cutoff_date = '2024-11-03'

    # Total channels
    cursor.execute("SELECT COUNT(*) FROM channels")
    total_channels = cursor.fetchone()[0]

    # Channels that actually have videos (after cutoff)
    cursor.execute("SELECT COUNT(DISTINCT channel_id) FROM videos WHERE published_at >= ?", (cutoff_date,))
    active_channels = cursor.fetchone()[0]

    # Get channel names with video counts (after cutoff)
    cursor.execute("""
        SELECT c.channel_name, COUNT(v.id) as video_count
        FROM channels c
        LEFT JOIN videos v ON c.id = v.channel_id AND v.published_at >= ?
        GROUP BY c.id
        ORDER BY video_count DESC
    """, (cutoff_date,))
    channels_with_counts = cursor.fetchall()

    # Total videos (after cutoff)
    cursor.execute("SELECT COUNT(*) FROM videos WHERE published_at >= ?", (cutoff_date,))
    total_videos = cursor.fetchone()[0]

    # Watched vs unwatched (after cutoff)
    cursor.execute("SELECT COUNT(*) FROM videos WHERE published_at >= ? AND watched_at IS NOT NULL", (cutoff_date,))
    watched_videos = cursor.fetchone()[0]
    unwatched_videos = total_videos - watched_videos

    # Videos per day
    cursor.execute("""
        SELECT DATE(published_at) as day, COUNT(*) as count
        FROM videos
        WHERE published_at >= ?
        GROUP BY day
        ORDER BY day DESC
        LIMIT 30
    """, (cutoff_date,))
    videos_per_day = cursor.fetchall()

    # Time to watch (published_at to watched_at)
    cursor.execute("""
        SELECT
            video_id,
            title,
            published_at,
            watched_at,
            julianday(watched_at) - julianday(published_at) as days_to_watch
        FROM videos
        WHERE published_at >= ? AND watched_at IS NOT NULL
        ORDER BY days_to_watch
    """, (cutoff_date,))
    watch_times = cursor.fetchall()

    # Most productive channels (most videos)
    cursor.execute("""
        SELECT c.channel_name, COUNT(v.id) as video_count
        FROM channels c
        JOIN videos v ON c.id = v.channel_id
        WHERE v.published_at >= ?
        GROUP BY c.id
        ORDER BY video_count DESC
        LIMIT 10
    """, (cutoff_date,))
    top_channels = cursor.fetchall()

    # Watch speed by day of week
    cursor.execute("""
        SELECT
            CASE CAST(strftime('%w', published_at) AS INTEGER)
                WHEN 0 THEN 'Sunday'
                WHEN 1 THEN 'Monday'
                WHEN 2 THEN 'Tuesday'
                WHEN 3 THEN 'Wednesday'
                WHEN 4 THEN 'Thursday'
                WHEN 5 THEN 'Friday'
                WHEN 6 THEN 'Saturday'
            END as day_of_week,
            AVG(julianday(watched_at) - julianday(published_at)) as avg_days_to_watch,
            COUNT(*) as count
        FROM videos
        WHERE published_at >= ? AND watched_at IS NOT NULL
        GROUP BY strftime('%w', published_at)
        ORDER BY CAST(strftime('%w', published_at) AS INTEGER)
    """, (cutoff_date,))
    watch_by_weekday = cursor.fetchall()

    # Publishing patterns by day of week
    cursor.execute("""
        SELECT
            CASE CAST(strftime('%w', published_at) AS INTEGER)
                WHEN 0 THEN 'Sunday'
                WHEN 1 THEN 'Monday'
                WHEN 2 THEN 'Tuesday'
                WHEN 3 THEN 'Wednesday'
                WHEN 4 THEN 'Thursday'
                WHEN 5 THEN 'Friday'
                WHEN 6 THEN 'Saturday'
            END as day_of_week,
            COUNT(*) as count
        FROM videos
        WHERE published_at >= ?
        GROUP BY strftime('%w', published_at)
        ORDER BY CAST(strftime('%w', published_at) AS INTEGER)
    """, (cutoff_date,))
    publish_by_weekday = cursor.fetchall()

    # Fastest and slowest watches
    if watch_times:
        fastest_watches = sorted(watch_times, key=lambda x: x[4])[:5]
        slowest_watches = sorted(watch_times, key=lambda x: x[4], reverse=True)[:5]
        avg_watch_time = sum(w[4] for w in watch_times) / len(watch_times)
    else:
        fastest_watches = []
        slowest_watches = []
        avg_watch_time = 0

    # Videos published per hour of day
    cursor.execute("""
        SELECT
            CAST(strftime('%H', published_at) AS INTEGER) as hour,
            COUNT(*) as count
        FROM videos
        WHERE published_at >= ?
        GROUP BY hour
        ORDER BY hour
    """, (cutoff_date,))
    videos_by_hour = cursor.fetchall()

    conn.close()

    return {
        'total_channels': total_channels,
        'active_channels': active_channels,
        'channels_with_counts': channels_with_counts,
        'total_videos': total_videos,
        'watched_videos': watched_videos,
        'unwatched_videos': unwatched_videos,
        'videos_per_day': videos_per_day,
        'top_channels': top_channels,
        'watch_by_weekday': watch_by_weekday,
        'publish_by_weekday': publish_by_weekday,
        'fastest_watches': fastest_watches,
        'slowest_watches': slowest_watches,
        'avg_watch_time': avg_watch_time,
        'videos_by_hour': videos_by_hour,
    }

def generate_html(analytics):
    html = f"""<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>YouTube Analytics</title>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <style>
        * {{
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }}

        body {{
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Oxygen, Ubuntu, Cantarell, sans-serif;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            padding: 20px;
            color: #333;
        }}

        .container {{
            max-width: 1400px;
            margin: 0 auto;
        }}

        h1 {{
            text-align: center;
            color: white;
            margin-bottom: 30px;
            font-size: 3em;
            text-shadow: 2px 2px 4px rgba(0,0,0,0.3);
        }}

        .stats-grid {{
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(250px, 1fr));
            gap: 20px;
            margin-bottom: 30px;
        }}

        .stat-card {{
            background: white;
            border-radius: 15px;
            padding: 25px;
            box-shadow: 0 10px 30px rgba(0,0,0,0.2);
            transition: transform 0.3s;
        }}

        .stat-card:hover {{
            transform: translateY(-5px);
        }}

        .stat-value {{
            font-size: 3em;
            font-weight: bold;
            color: #667eea;
            margin: 10px 0;
        }}

        .stat-label {{
            font-size: 1em;
            color: #666;
            text-transform: uppercase;
            letter-spacing: 1px;
        }}

        .chart-container {{
            background: white;
            border-radius: 15px;
            padding: 30px;
            margin-bottom: 30px;
            box-shadow: 0 10px 30px rgba(0,0,0,0.2);
        }}

        .chart-title {{
            font-size: 1.5em;
            margin-bottom: 20px;
            color: #333;
        }}

        canvas {{
            max-height: 400px;
        }}

        .table-container {{
            background: white;
            border-radius: 15px;
            padding: 30px;
            margin-bottom: 30px;
            box-shadow: 0 10px 30px rgba(0,0,0,0.2);
            overflow-x: auto;
        }}

        table {{
            width: 100%;
            border-collapse: collapse;
        }}

        th {{
            background: #667eea;
            color: white;
            padding: 15px;
            text-align: left;
            font-weight: 600;
        }}

        td {{
            padding: 12px 15px;
            border-bottom: 1px solid #eee;
        }}

        tr:hover {{
            background: #f8f9fa;
        }}

        .highlight {{
            color: #667eea;
            font-weight: bold;
        }}

        .progress-bar {{
            height: 20px;
            background: #e0e0e0;
            border-radius: 10px;
            overflow: hidden;
        }}

        .progress-fill {{
            height: 100%;
            background: linear-gradient(90deg, #667eea, #764ba2);
            transition: width 0.3s;
        }}
    </style>
</head>
<body>
    <div class="container">
        <h1>📊 YouTube Analytics Dashboard</h1>

        <div class="stats-grid">
            <div class="stat-card">
                <div class="stat-label">Total Channels</div>
                <div class="stat-value">{analytics['total_channels']}</div>
            </div>
            <div class="stat-card">
                <div class="stat-label">Active Channels</div>
                <div class="stat-value">{analytics['active_channels']}</div>
                <div class="stat-label">{(analytics['active_channels']/max(analytics['total_channels'],1)*100):.1f}% publishing</div>
            </div>
            <div class="stat-card">
                <div class="stat-label">Total Videos</div>
                <div class="stat-value">{analytics['total_videos']}</div>
            </div>
            <div class="stat-card">
                <div class="stat-label">Watched Videos</div>
                <div class="stat-value">{analytics['watched_videos']}</div>
            </div>
            <div class="stat-card">
                <div class="stat-label">Unwatched Videos</div>
                <div class="stat-value">{analytics['unwatched_videos']}</div>
            </div>
            <div class="stat-card">
                <div class="stat-label">Avg Time to Watch</div>
                <div class="stat-value">{analytics['avg_watch_time']:.1f}</div>
                <div class="stat-label">days</div>
            </div>
        </div>

        <div class="chart-container">
            <div class="chart-title">Watch Rate Progress</div>
            <div class="progress-bar">
                <div class="progress-fill" style="width: {(analytics['watched_videos']/max(analytics['total_videos'],1)*100):.1f}%"></div>
            </div>
            <p style="margin-top: 10px; color: #666;">{(analytics['watched_videos']/max(analytics['total_videos'],1)*100):.1f}% of videos watched</p>
        </div>

        <div class="chart-container">
            <div class="chart-title">Videos Published Per Day (Last 30 Days)</div>
            <canvas id="videosPerDayChart"></canvas>
        </div>

        <div class="chart-container">
            <div class="chart-title">Videos Published by Day of Week</div>
            <canvas id="publishByWeekdayChart"></canvas>
        </div>

        <div class="chart-container">
            <div class="chart-title">Average Days to Watch by Publish Day</div>
            <canvas id="watchByWeekdayChart"></canvas>
        </div>

        <div class="chart-container">
            <div class="chart-title">Videos Published by Hour of Day</div>
            <canvas id="videosByHourChart"></canvas>
        </div>

        <div class="table-container">
            <div class="chart-title">Top 10 Most Productive Channels</div>
            <table>
                <thead>
                    <tr>
                        <th>Rank</th>
                        <th>Channel</th>
                        <th>Videos</th>
                    </tr>
                </thead>
                <tbody>
                    {"".join(f'<tr><td>{i+1}</td><td>{channel}</td><td class="highlight">{count}</td></tr>'
                             for i, (channel, count) in enumerate(analytics['top_channels']))}
                </tbody>
            </table>
        </div>

        <div class="table-container">
            <div class="chart-title">⚡ Fastest Watches (Lightning Speed!)</div>
            <table>
                <thead>
                    <tr>
                        <th>Title</th>
                        <th>Time to Watch</th>
                    </tr>
                </thead>
                <tbody>
                    {"".join(f'<tr><td>{title[:80]}...</td><td class="highlight">{days:.1f} days</td></tr>'
                             for vid_id, title, pub, watch, days in analytics['fastest_watches'])}
                </tbody>
            </table>
        </div>

        <div class="table-container">
            <div class="chart-title">🐌 Slowest Watches (Better Late Than Never!)</div>
            <table>
                <thead>
                    <tr>
                        <th>Title</th>
                        <th>Time to Watch</th>
                    </tr>
                </thead>
                <tbody>
                    {"".join(f'<tr><td>{title[:80]}...</td><td class="highlight">{days:.1f} days</td></tr>'
                             for vid_id, title, pub, watch, days in analytics['slowest_watches'])}
                </tbody>
            </table>
        </div>

        <div class="table-container">
            <div class="chart-title">All Channels Status</div>
            <table>
                <thead>
                    <tr>
                        <th>Channel</th>
                        <th>Video Count</th>
                        <th>Status</th>
                    </tr>
                </thead>
                <tbody>
                    {"".join(f'<tr><td>{channel}</td><td class="highlight">{count}</td><td>{"✅ Active" if count > 0 else "💤 Inactive"}</td></tr>'
                             for channel, count in analytics['channels_with_counts'])}
                </tbody>
            </table>
        </div>
    </div>

    <script>
        // Videos per day chart
        const videosPerDayData = {json.dumps([(day, count) for day, count in analytics['videos_per_day']])};
        new Chart(document.getElementById('videosPerDayChart'), {{
            type: 'line',
            data: {{
                labels: videosPerDayData.map(d => d[0]).reverse(),
                datasets: [{{
                    label: 'Videos Published',
                    data: videosPerDayData.map(d => d[1]).reverse(),
                    borderColor: '#667eea',
                    backgroundColor: 'rgba(102, 126, 234, 0.1)',
                    tension: 0.4,
                    fill: true
                }}]
            }},
            options: {{
                responsive: true,
                maintainAspectRatio: true,
                plugins: {{
                    legend: {{
                        display: false
                    }}
                }},
                scales: {{
                    y: {{
                        beginAtZero: true
                    }}
                }}
            }}
        }});

        // Publish by weekday chart
        const publishByWeekdayData = {json.dumps([(day, count) for day, count in analytics['publish_by_weekday']])};
        new Chart(document.getElementById('publishByWeekdayChart'), {{
            type: 'bar',
            data: {{
                labels: publishByWeekdayData.map(d => d[0]),
                datasets: [{{
                    label: 'Videos Published',
                    data: publishByWeekdayData.map(d => d[1]),
                    backgroundColor: 'rgba(102, 126, 234, 0.8)',
                }}]
            }},
            options: {{
                responsive: true,
                maintainAspectRatio: true,
                plugins: {{
                    legend: {{
                        display: false
                    }}
                }},
                scales: {{
                    y: {{
                        beginAtZero: true
                    }}
                }}
            }}
        }});

        // Watch by weekday chart
        const watchByWeekdayData = {json.dumps([(day, float(avg), count) for day, avg, count in analytics['watch_by_weekday']])};
        new Chart(document.getElementById('watchByWeekdayChart'), {{
            type: 'bar',
            data: {{
                labels: watchByWeekdayData.map(d => d[0]),
                datasets: [{{
                    label: 'Avg Days to Watch',
                    data: watchByWeekdayData.map(d => d[1]),
                    backgroundColor: 'rgba(118, 75, 162, 0.8)',
                }}]
            }},
            options: {{
                responsive: true,
                maintainAspectRatio: true,
                plugins: {{
                    legend: {{
                        display: false
                    }}
                }},
                scales: {{
                    y: {{
                        beginAtZero: true
                    }}
                }}
            }}
        }});

        // Videos by hour chart
        const videosByHourData = {json.dumps([(hour, count) for hour, count in analytics['videos_by_hour']])};
        new Chart(document.getElementById('videosByHourChart'), {{
            type: 'line',
            data: {{
                labels: videosByHourData.map(d => d[0] + ':00'),
                datasets: [{{
                    label: 'Videos Published',
                    data: videosByHourData.map(d => d[1]),
                    borderColor: '#764ba2',
                    backgroundColor: 'rgba(118, 75, 162, 0.1)',
                    tension: 0.4,
                    fill: true
                }}]
            }},
            options: {{
                responsive: true,
                maintainAspectRatio: true,
                plugins: {{
                    legend: {{
                        display: false
                    }}
                }},
                scales: {{
                    y: {{
                        beginAtZero: true
                    }}
                }}
            }}
        }});
    </script>
</body>
</html>
"""
    return html

def main():
    print("Generating analytics...")
    analytics = get_analytics()

    print("Creating HTML report...")
    html = generate_html(analytics)

    with open('analytics.html', 'w', encoding='utf-8') as f:
        f.write(html)

    print("✅ Analytics report generated: analytics.html")
    print(f"   📊 {analytics['total_videos']} total videos")
    print(f"   📺 {analytics['active_channels']}/{analytics['total_channels']} active channels")
    print(f"   ✅ {analytics['watched_videos']} watched, {analytics['unwatched_videos']} unwatched")
    print(f"   ⏱️  Avg {analytics['avg_watch_time']:.1f} days to watch")

if __name__ == '__main__':
    main()