﻿<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// ---------------------------------------------------------------------
// Helper: Get YouTube Thumbnail by Video ID
// ---------------------------------------------------------------------
function getYouTubeThumbnail($videoId) {
    if (empty($videoId)) {
        return 'assets/default_thumbnail.jpg'; // fallback image
    }
    return "https://img.youtube.com/vi/{$videoId}/default.jpg";
}

// ---------------------------------------------------------------------
// Helper: Fetch URL with cURL
// ---------------------------------------------------------------------
function youtube_curl_get(string $url): string {
    $ch = curl_init();
    curl_setopt_array($ch, [
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_SSL_VERIFYPEER => false, // disable SSL check for localhost
        CURLOPT_CONNECTTIMEOUT => 20,
        CURLOPT_TIMEOUT => 60,
    ]);
    $body = curl_exec($ch);
    if (curl_errno($ch)) {
        $err = curl_error($ch);
        curl_close($ch);
        throw new Exception("cURL error: {$err}");
    }
    $http = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);
    if ($http !== 200) {
        throw new Exception("YouTube API returned HTTP {$http}");
    }
    return $body;
}

// ---------------------------------------------------------------------
// Check if table exists
// ---------------------------------------------------------------------
function tableExists($conn, string $table): bool {
    $query = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table";
    if ($conn instanceof PDO) {
        $stmt = $conn->prepare($query);
        $stmt->execute([':table' => $table]);
        return (bool)$stmt->fetchColumn();
    }
    return false;
}

// ---------------------------------------------------------------------
// Detect FK column
// ---------------------------------------------------------------------
function detectFkColumn($conn, string $video_table, string $main_table): ?string {
    if (!tableExists($conn, $video_table)) return null;

    $stmt = $conn->prepare("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table ORDER BY ORDINAL_POSITION");
    $stmt->execute([':table' => $video_table]);
    $cols = $stmt->fetchAll(PDO::FETCH_COLUMN);

    $candidates = [];
    $main_plural = $main_table;
    $main_singular = rtrim($main_table, 's');

    foreach ($cols as $col) {
        if (substr($col, -3) === '_id' && $col !== 'id' && $col !== 'video_id') {
            $candidates[] = $col;
        }
    }

    foreach ($candidates as $cand) {
        if (stripos($cand, $main_plural) !== false || stripos($cand, $main_singular) !== false) {
            return $cand;
        }
    }
    return $candidates[0] ?? null;
}

// ---------------------------------------------------------------------
// Detect Thumbnail Column
// ---------------------------------------------------------------------
function detectMainThumbColumn($conn, string $main_table): string {
    if ($main_table === 'programmes') return 'cover_image'; // ✅ Force fix

    $preferred = ['thumbnail', 'cover_image', 'cover', 'image'];
    $stmt = $conn->prepare("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table");
    $stmt->execute([':table' => $main_table]);
    $cols = $stmt->fetchAll(PDO::FETCH_COLUMN);

    foreach ($preferred as $p) {
        foreach ($cols as $c) {
            if (strcasecmp($c, $p) === 0) return $c;
        }
    }

    foreach ($cols as $c) {
        if (stripos($c, 'thumb') !== false || stripos($c, 'image') !== false || stripos($c, 'cover') !== false) {
            return $c;
        }
    }

    return 'thumbnail';
}

// ---------------------------------------------------------------------
// Fetch Playlist Videos and Update
// ---------------------------------------------------------------------
function fetchPlaylistVideosAndUpdate(string $playlist_id, string $api_key, $conn, int $item_id, string $video_table, string $main_table): int {
    if (empty($playlist_id) || empty($api_key)) return 0;

    if (!tableExists($conn, $video_table) || !tableExists($conn, $main_table)) return 0;

    $fkCol = detectFkColumn($conn, $video_table, $main_table);
    $thumbCol = detectMainThumbColumn($conn, $main_table);
    error_log("🟢 Thumbnail column detected for {$main_table}: {$thumbCol}");

    $base = "https://www.googleapis.com/youtube/v3/playlistItems";
    $nextPageToken = null;
    $fetched = 0;
    $first_thumb = null;

    do {
        $url = $base . '?part=snippet&maxResults=50&playlistId=' . urlencode($playlist_id) . '&key=' . urlencode($api_key);
        if ($nextPageToken) $url .= '&pageToken=' . urlencode($nextPageToken);

        try {
            $body = youtube_curl_get($url);
        } catch (Exception $e) {
            error_log("❌ YouTube fetch error for playlist {$playlist_id}: " . $e->getMessage());
            break;
        }

        $data = json_decode($body, true);
        if (!isset($data['items'])) break;

        foreach ($data['items'] as $item) {
            if (empty($item['snippet']['resourceId']['videoId'])) continue;

            $videoId = $item['snippet']['resourceId']['videoId'];
            $title = $item['snippet']['title'] ?? null;
            $thumb = $item['snippet']['thumbnails']['maxres']['url'] ??
                     $item['snippet']['thumbnails']['high']['url'] ??
                     $item['snippet']['thumbnails']['medium']['url'] ??
                     $item['snippet']['thumbnails']['default']['url'] ?? null;
            $published_at = !empty($item['snippet']['publishedAt']) ? date('Y-m-d H:i:s', strtotime($item['snippet']['publishedAt'])) : null;

            // UPSERT
            $check = $conn->prepare("SELECT id FROM `$video_table` WHERE video_id = :video_id LIMIT 1");
            $check->execute([':video_id' => $videoId]);
            $exists = $check->fetchColumn();

            if ($exists) {
                $upd = $conn->prepare("UPDATE `$video_table` SET title = :title, thumbnail = :thumbnail, published_at = :published_at, updated_at = NOW() WHERE video_id = :video_id");
                $upd->execute([':title' => $title, ':thumbnail' => $thumb, ':published_at' => $published_at, ':video_id' => $videoId]);
            } else {
                $ins = $conn->prepare("INSERT INTO `$video_table` (`$fkCol`, `video_id`, `title`, `thumbnail`, `published_at`, `created_at`, `updated_at`)
                                       VALUES (:item_id, :video_id, :title, :thumbnail, :published_at, NOW(), NOW())");
                $ins->execute([':item_id' => $item_id, ':video_id' => $videoId, ':title' => $title, ':thumbnail' => $thumb, ':published_at' => $published_at]);
            }

            if ($first_thumb === null && $thumb) $first_thumb = $thumb;
            $fetched++;
        }

        $nextPageToken = $data['nextPageToken'] ?? null;
        if ($nextPageToken) usleep(200000);

    } while ($nextPageToken);

    // ✅ Update main table thumbnail
    if ($fetched > 0 && $first_thumb) {
        $upd = $conn->prepare("UPDATE `$main_table` SET `$thumbCol` = :thumb, videos_updated_at = NOW() WHERE id = :id");
        $upd->execute([':thumb' => $first_thumb, ':id' => $item_id]);
    }

    error_log("✅ fetchPlaylistVideosAndUpdate: {$fetched} videos fetched for {$main_table} id={$item_id}");
    return $fetched;
}

// ---------------------------------------------------------------------
// Update All Teledramas (force update)
// ---------------------------------------------------------------------
function updateTeledramasAndEpisodes($conn, $api_key, $staleHours = 24): int {
    $stmt = $conn->query("SELECT id, youtube_playlist, videos_updated_at FROM teledramas WHERE youtube_playlist IS NOT NULL AND youtube_playlist <> ''");
    $items = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $total = 0;
    foreach ($items as $row) {
        $id = (int)$row['id'];
        $playlist = $row['youtube_playlist'];
        $do = true; // force update every teledrama
        if ($do) {
            fetchPlaylistVideosAndUpdate($playlist, $api_key, $conn, $id, 'teledrama_videos', 'teledramas');
            $total++;
            sleep(1);
        }
    }
    return $total;
}

// ---------------------------------------------------------------------
// Update All Programmes (force update)
// ---------------------------------------------------------------------
function updateProgrammesAndEpisodes($conn, $api_key, $staleHours = 24): int {
    $stmt = $conn->query("SELECT id, youtube_playlist, videos_updated_at FROM programmes WHERE youtube_playlist IS NOT NULL AND youtube_playlist <> ''");
    $items = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $total = 0;
    foreach ($items as $row) {
        $id = (int)$row['id'];
        $playlist = $row['youtube_playlist'];
        $do = true; // force update every programme
        if ($do) {
            fetchPlaylistVideosAndUpdate($playlist, $api_key, $conn, $id, 'programmes_videos', 'programmes');
            $total++;
            sleep(1);
        }
    }
    return $total;
}