<?php

require_once __DIR__ . '/vendor/autoload.php';

// Load environment variables
$dotenv = Dotenv\Dotenv::createImmutable(__DIR__);
$dotenv->load();

// Load database configuration
$dbConfig = require __DIR__ . '/config/database.php';
\App\Database\Database::setConfig($dbConfig);

// Initialize database
try {
    $pdo = \App\Database\Database::getInstance();
    echo "✅ Database connection successful\n";
} catch (Exception $e) {
    die('❌ Database connection failed: ' . $e->getMessage());
}

// Check TV shows count
try {
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM tv_shows");
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "📺 TV shows in database: {$result['count']}\n";
} catch (Exception $e) {
    echo "❌ Error checking TV shows: " . $e->getMessage() . "\n";
}

// Check actor_tv_episode table exists and has data
try {
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM actor_tv_episode");
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "🔗 Actor-TV episode relationships: {$result['count']}\n";
} catch (Exception $e) {
    echo "❌ Error checking actor_tv_episode table: " . $e->getMessage() . "\n";
}

// Check actors count
try {
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM actors");
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "👥 Actors in database: {$result['count']}\n";
} catch (Exception $e) {
    echo "❌ Error checking actors: " . $e->getMessage() . "\n";
}

// Sample TV show with cast
try {
    $stmt = $pdo->query("SELECT id, title, cast FROM tv_shows WHERE cast IS NOT NULL AND cast != '' LIMIT 3");
    $tvshows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($tvshows)) {
        echo "⚠️  No TV shows with cast data found\n";
    } else {
        echo "📋 Sample TV shows with cast:\n";
        foreach ($tvshows as $tvshow) {
            echo "  - ID: {$tvshow['id']}, Title: {$tvshow['title']}, Cast: " . substr($tvshow['cast'], 0, 100) . "...\n";
        }
    }
} catch (Exception $e) {
    echo "❌ Error checking TV show cast data: " . $e->getMessage() . "\n";
}

// Sample actor relationships
try {
    $stmt = $pdo->query("
        SELECT ts.title, a.name 
        FROM tv_shows ts 
        JOIN tv_episodes te ON ts.id = te.tv_show_id
        JOIN actor_tv_episode ate ON te.id = ate.tv_episode_id 
        JOIN actors a ON ate.actor_id = a.id 
        LIMIT 5
    ");
    $relationships = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($relationships)) {
        echo "⚠️  No actor-TV episode relationships found\n";
    } else {
        echo "🔗 Sample actor-TV show relationships (via episodes):\n";
        foreach ($relationships as $rel) {
            echo "  - {$rel['name']} in {$rel['title']}\n";
        }
    }
} catch (Exception $e) {
    echo "❌ Error checking actor relationships: " . $e->getMessage() . "\n";
}

echo "\n✨ Check completed!\n";