<?php
declare(strict_types=1);

$dsn = 'mysql:host=127.0.0.1;dbname=shquier_easychair;charset=utf8mb4';
$user = 'root';
$pass = '_MohdShquier@2025!';

$pdo = new \PDO($dsn, $user, $pass, [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
]);

function firstId(\PDO $pdo, string $table): ?int {
    $stmt = $pdo->query("SELECT id FROM {$table} ORDER BY id ASC LIMIT 1");
    $id = $stmt->fetchColumn();
    return $id !== false ? (int) $id : null;
}

$authorRoleId = null;
$stmt = $pdo->prepare("SELECT id FROM roles WHERE code = 'author' LIMIT 1");
$stmt->execute();
$authorRoleId = $stmt->fetchColumn();

if (!$authorRoleId) {
    echo "Author role not found.\n";
    exit(1);
}

$authorEmail = 'demo.author@example.com';
$stmt = $pdo->prepare("SELECT id FROM users WHERE email = :email LIMIT 1");
$stmt->execute([':email' => $authorEmail]);
$authorId = $stmt->fetchColumn();

if (!$authorId) {
    $hash = password_hash('Demo@12345', PASSWORD_DEFAULT);
    $stmt = $pdo->prepare("
        INSERT INTO users (role_id, first_name, last_name, email, password_hash, preferred_lang, is_active)
        VALUES (:role_id, 'Demo', 'Author', :email, :password_hash, 'en', 1)
    ");
    $stmt->execute([
        ':role_id' => $authorRoleId,
        ':email' => $authorEmail,
        ':password_hash' => $hash,
    ]);
    $authorId = (int) $pdo->lastInsertId();
    echo "Inserted demo author.\n";
} else {
    $authorId = (int) $authorId;
}

$conferenceId = firstId($pdo, 'conferences');
$trackId = firstId($pdo, 'tracks');

if (!$conferenceId || !$trackId) {
    echo "Conference or track missing.\n";
    exit(1);
}

for ($i = 1; $i <= 3; $i++) {
    $submissionNo = 'DEMO-' . date('Ymd') . '-' . $i;
    $title = "Demo Submission {$i}";
    $stmt = $pdo->prepare("SELECT id FROM submissions WHERE submission_no = :submission_no LIMIT 1");
    $stmt->execute([':submission_no' => $submissionNo]);
    $exists = $stmt->fetchColumn();

    if ($exists) {
        continue;
    }

    $stmt = $pdo->prepare("
        INSERT INTO submissions (
            conference_id, track_id, author_id, submission_no, title, abstract, keywords,
            file_path, original_filename, status, current_version, submitted_at
        ) VALUES (
            :conference_id, :track_id, :author_id, :submission_no, :title, :abstract, :keywords,
            :file_path, :original_filename, 'submitted', 1, NOW()
        )
    ");
    $stmt->execute([
        ':conference_id' => $conferenceId,
        ':track_id' => $trackId,
        ':author_id' => $authorId,
        ':submission_no' => $submissionNo,
        ':title' => $title,
        ':abstract' => "Demo abstract for submission {$i}",
        ':keywords' => 'demo,test,conference',
        ':file_path' => 'submissions/main/demo.pdf',
        ':original_filename' => 'demo.pdf',
    ]);

    $submissionId = (int) $pdo->lastInsertId();

    $stmt = $pdo->prepare("
        INSERT INTO submission_authors (
            submission_id, full_name, email, affiliation, country, is_corresponding, author_order
        ) VALUES (
            :submission_id, 'Demo Author', :email, 'Jerash University', 'Jordan', 1, 1
        )
    ");
    $stmt->execute([
        ':submission_id' => $submissionId,
        ':email' => $authorEmail,
    ]);

    echo "Inserted {$submissionNo}\n";
}

echo "Demo seed completed.\n";
