<?php
require_once __DIR__ . '/config.php';

try {
    // First, connect to MySQL server without selecting a DB
    $pdo_admin = new PDO('mysql:host='.DB_HOST, DB_USER, DB_PASS, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ]);
    // Create the database if it doesn't exist
    $pdo_admin->exec("CREATE DATABASE IF NOT EXISTS `".DB_NAME."`");
    echo "Database '".DB_NAME."' created or already exists.\n";
    
    // Now, connect to the specific database
    $pdo = db();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 1. Run migrations
    $migrations = glob(__DIR__ . '/migrations/*.sql');
    sort($migrations);
    foreach ($migrations as $migration) {
        $sql = file_get_contents($migration);
        $pdo->exec($sql);
        echo "Executed migration: " . basename($migration) . "\n";
    }

    // 2. Check if table is empty before seeding
    $stmt = $pdo->query("SELECT COUNT(*) FROM books");
    if ($stmt->fetchColumn() > 0) {
        echo "Table 'books' is not empty. Seeding skipped.\n";
        exit;
    }

    // 3. Seed data
    $books = [
        [
            'title' => 'The Hitchhiker\'s Guide to the Galaxy',
            'author' => 'Douglas Adams',
            'isbn' => '978-0345391803',
            'description' => 'A comedy science fiction series created by Douglas Adams.',
            'qr_code_hash' => md5('978-0345391803')
        ],
        [
            'title' => 'Pride and Prejudice',
            'author' => 'Jane Austen',
            'isbn' => '978-1503290563',
            'description' => 'A romantic novel of manners written by Jane Austen in 1813.',
            'qr_code_hash' => md5('978-1503290563')
        ],
        [
            'title' => 'To Kill a Mockingbird',
            'author' => 'Harper Lee',
            'isbn' => '978-0061120084',
            'description' => 'A novel by Harper Lee published in 1960. Instantly successful, widely read in high schools and middle schools in the United States, it has become a classic of modern American literature, winning the Pulitzer Prize.',
            'qr_code_hash' => md5('978-0061120084')
        ],
        [
            'title' => '1984',
            'author' => 'George Orwell',
            'isbn' => '978-0451524935',
            'description' => 'A dystopian social science fiction novel and cautionary tale by English writer George Orwell.',
            'qr_code_hash' => md5('978-0451524935')
        ]
    ];

    $stmt = $pdo->prepare(
        "INSERT INTO books (title, author, isbn, description, qr_code_hash) VALUES (:title, :author, :isbn, :description, :qr_code_hash)"
    );

    foreach ($books as $book) {
        $stmt->execute($book);
    }

    echo "Seeded " . count($books) . " books.\n";

} catch (PDOException $e) {
    die("DB ERROR: " . $e->getMessage());
}