<?php

$host = 'localhost';
$db   = 'jasper';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

$images = [
    ['url' => 'http://jasperphytoextracts.com/wp-content/uploads/2020/09/HACP.png', 'title' => 'HACCP'],
    ['url' => 'http://jasperphytoextracts.com/wp-content/uploads/2020/09/HALAL.png', 'title' => 'HALAL'],
    ['url' => 'http://jasperphytoextracts.com/wp-content/uploads/2020/09/ISO.png', 'title' => 'ISO'],
    ['url' => 'http://jasperphytoextracts.com/wp-content/uploads/2020/09/Spice.png', 'title' => 'Spice Board'],
    ['url' => 'http://jasperphytoextracts.com/wp-content/uploads/2020/09/FSSAI-300x232.png', 'title' => 'FSSAI'],
    ['url' => 'http://jasperphytoextracts.com/wp-content/uploads/2020/09/GMP.png', 'title' => 'GMP']
];

$uploadDir = __DIR__ . '/public/uploads/certifications/';
if (!is_dir($uploadDir)) {
    mkdir($uploadDir, 0777, true);
}

// Clear existing
$pdo->exec("TRUNCATE TABLE certifications");

$sort = 1;
foreach ($images as $img) {
    $content = @file_get_contents($img['url']);
    if ($content !== false) {
        $filename = basename($img['url']);
        file_put_contents($uploadDir . $filename, $content);
        
        $stmt = $pdo->prepare("INSERT INTO certifications (title, image, sort_order, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)");
        $date = date('Y-m-d H:i:s');
        $stmt->execute([$img['title'], $filename, $sort++, 1, $date, $date]);
        echo "Imported: " . $img['title'] . "\n";
    } else {
        echo "Failed to download: " . $img['url'] . "\n";
    }
}

echo "Done.";
