238 lines
9.6 KiB
PHP
238 lines
9.6 KiB
PHP
<?php
|
||
/**
|
||
* MSPE Database Seed Script
|
||
* ============================================================
|
||
* Imports all data/*.json files into MySQL on first deployment.
|
||
*
|
||
* !! RUN ONCE via browser or CLI, then DELETE THIS FILE !!
|
||
*
|
||
* Browser: https://mspe.pro/db_seed.php?token=YOUR_TOKEN
|
||
* CLI: php db_seed.php --token=YOUR_TOKEN
|
||
*
|
||
* Generate a token: php -r "echo bin2hex(random_bytes(16));"
|
||
* Paste it below, then delete this file after running.
|
||
* ============================================================
|
||
*/
|
||
|
||
define('SEED_TOKEN', 'CHANGE_THIS_TO_A_RANDOM_STRING');
|
||
|
||
// ── Token gate ────────────────────────────────────────────────
|
||
$cliToken = '';
|
||
foreach ($argv ?? [] as $arg) {
|
||
if (str_starts_with($arg, '--token=')) {
|
||
$cliToken = substr($arg, 8);
|
||
}
|
||
}
|
||
$providedToken = $_GET['token'] ?? $cliToken;
|
||
|
||
if (!hash_equals(SEED_TOKEN, (string)$providedToken)) {
|
||
http_response_code(403);
|
||
die('403 Forbidden — provide ?token=YOUR_TOKEN');
|
||
}
|
||
|
||
// ── Bootstrap ─────────────────────────────────────────────────
|
||
define('IS_SEED', true);
|
||
$seedStartTime = microtime(true);
|
||
ob_start();
|
||
|
||
function loadEnvSeed($path) {
|
||
if (!file_exists($path)) return;
|
||
$lines = file($path, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
|
||
foreach ($lines as $line) {
|
||
$line = trim($line);
|
||
if ($line === '' || $line[0] === '#') continue;
|
||
if (strpos($line, '=') === false) continue;
|
||
[$key, $value] = explode('=', $line, 2);
|
||
$key = trim($key);
|
||
$value = trim($value);
|
||
if (strlen($value) > 1 && (
|
||
($value[0] === '"' && substr($value, -1) === '"') ||
|
||
($value[0] === "'" && substr($value, -1) === "'")
|
||
)) {
|
||
$value = substr($value, 1, -1);
|
||
}
|
||
$_ENV[$key] = $value;
|
||
putenv("$key=$value");
|
||
}
|
||
}
|
||
|
||
loadEnvSeed(__DIR__ . '/.env');
|
||
loadEnvSeed(dirname(__DIR__) . '/.env');
|
||
|
||
function envSeed($key, $default = '') {
|
||
return $_ENV[$key] ?? getenv($key) ?: $default;
|
||
}
|
||
|
||
// ── Connect to MySQL ──────────────────────────────────────────
|
||
$dsn = 'mysql:host=' . envSeed('DB_HOST', 'localhost')
|
||
. ';dbname=' . envSeed('DB_NAME')
|
||
. ';charset=utf8mb4';
|
||
|
||
try {
|
||
$pdo = new PDO($dsn, envSeed('DB_USER'), envSeed('DB_PASS'), [
|
||
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
||
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
|
||
PDO::ATTR_EMULATE_PREPARES => false,
|
||
]);
|
||
} catch (Exception $e) {
|
||
http_response_code(500);
|
||
die('Database connection failed: ' . $e->getMessage());
|
||
}
|
||
|
||
// ── Helpers ───────────────────────────────────────────────────
|
||
$results = [];
|
||
|
||
function ensureTable(PDO $pdo, string $table): void {
|
||
$safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
|
||
$pdo->exec("CREATE TABLE IF NOT EXISTS `{$safe}` (
|
||
`id` VARCHAR(64) NOT NULL PRIMARY KEY,
|
||
`data` JSON NOT NULL,
|
||
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
|
||
}
|
||
|
||
function importJsonFile(PDO $pdo, string $table, string $file): array {
|
||
if (!file_exists($file)) {
|
||
return ['table' => $table, 'status' => 'skipped', 'reason' => 'File not found'];
|
||
}
|
||
|
||
$records = json_decode(file_get_contents($file), true);
|
||
if (!is_array($records)) {
|
||
return ['table' => $table, 'status' => 'error', 'reason' => 'Invalid JSON'];
|
||
}
|
||
if (empty($records)) {
|
||
return ['table' => $table, 'status' => 'skipped', 'reason' => 'Empty file — nothing to import'];
|
||
}
|
||
|
||
ensureTable($pdo, $table);
|
||
$safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
|
||
|
||
$inserted = 0;
|
||
$updated = 0;
|
||
$errors = 0;
|
||
|
||
foreach ($records as $record) {
|
||
if (empty($record['id'])) {
|
||
$record['id'] = bin2hex(random_bytes(16));
|
||
}
|
||
$record['created_at'] = $record['created_at'] ?? date('Y-m-d H:i:s');
|
||
$record['updated_at'] = $record['updated_at'] ?? date('Y-m-d H:i:s');
|
||
|
||
try {
|
||
// REPLACE INTO = INSERT if new, UPDATE if id already exists (idempotent)
|
||
$stmt = $pdo->prepare(
|
||
"REPLACE INTO `{$safe}` (`id`, `data`, `created_at`, `updated_at`)
|
||
VALUES (?, ?, ?, ?)"
|
||
);
|
||
$stmt->execute([
|
||
$record['id'],
|
||
json_encode($record, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
|
||
$record['created_at'],
|
||
$record['updated_at'],
|
||
]);
|
||
// REPLACE INTO returns 1 for insert, 2 for replace
|
||
($stmt->rowCount() === 1) ? $inserted++ : $updated++;
|
||
} catch (Exception $e) {
|
||
$errors++;
|
||
error_log("Seed error [{$table}] id={$record['id']}: " . $e->getMessage());
|
||
}
|
||
}
|
||
|
||
return [
|
||
'table' => $table,
|
||
'status' => $errors === 0 ? 'ok' : 'partial',
|
||
'inserted' => $inserted,
|
||
'updated' => $updated,
|
||
'errors' => $errors,
|
||
'total' => count($records),
|
||
];
|
||
}
|
||
|
||
// ── Seed each table ───────────────────────────────────────────
|
||
$dataDir = __DIR__ . '/data/';
|
||
|
||
$tables = [
|
||
'settings' => 'settings.json',
|
||
'services' => 'services.json',
|
||
'news' => 'news.json',
|
||
'portfolio' => 'portfolio.json',
|
||
'availability_slots' => 'availability_slots.json',
|
||
'blocked_times' => 'blocked_times.json',
|
||
'bookings' => 'bookings.json',
|
||
'messages' => 'messages.json',
|
||
'subscribers' => 'subscribers.json',
|
||
];
|
||
|
||
foreach ($tables as $table => $file) {
|
||
$results[] = importJsonFile($pdo, $table, $dataDir . $file);
|
||
}
|
||
|
||
// ── Also ensure empty-but-needed tables exist ─────────────────
|
||
$ensureOnly = ['users', 'login_attempts', 'password_resets', 'admin_auth', 'testimonials', 'team'];
|
||
foreach ($ensureOnly as $table) {
|
||
ensureTable($pdo, $table);
|
||
$results[] = ['table' => $table, 'status' => 'table_created', 'reason' => 'Schema ensured'];
|
||
}
|
||
|
||
// ── Clear stats cache ─────────────────────────────────────────
|
||
$cacheDir = __DIR__ . '/data/cache/';
|
||
$cleared = 0;
|
||
if (is_dir($cacheDir)) {
|
||
foreach (glob($cacheDir . 'stats_*.json') as $cacheFile) {
|
||
if (@unlink($cacheFile)) {
|
||
$cleared++;
|
||
}
|
||
}
|
||
}
|
||
|
||
// ── Output ────────────────────────────────────────────────────
|
||
$isCli = PHP_SAPI === 'cli';
|
||
$elapsed = round(microtime(true) - $seedStartTime, 3);
|
||
$allOk = array_reduce($results, fn($c, $r) => $c && in_array($r['status'], ['ok', 'skipped', 'table_created']), true);
|
||
|
||
if ($isCli) {
|
||
echo "\n=== MSPE Database Seed ===\n\n";
|
||
foreach ($results as $r) {
|
||
$icon = match($r['status']) { 'ok' => '✓', 'partial' => '⚠', 'error' => '✗', default => '–' };
|
||
printf(" %s %-25s %s\n", $icon, $r['table'], json_encode(array_diff_key($r, array_flip(['table']))));
|
||
}
|
||
echo "\n Cache files cleared: {$cleared}\n";
|
||
echo " Elapsed time: {$elapsed}s\n";
|
||
echo $allOk ? "\n All done. DELETE THIS FILE NOW.\n\n" : "\n Some errors occurred — check error log.\n\n";
|
||
} else {
|
||
header('Content-Type: text/html; charset=UTF-8');
|
||
$badge = fn($s) => match($s) {
|
||
'ok' => '<span style="color:#22c55e">✓ ok</span>',
|
||
'skipped' => '<span style="color:#94a3b8">– skipped</span>',
|
||
'table_created'=> '<span style="color:#60a5fa">○ ensured</span>',
|
||
'partial' => '<span style="color:#f59e0b">⚠ partial</span>',
|
||
default => '<span style="color:#ef4444">✗ error</span>',
|
||
};
|
||
|
||
echo '<!doctype html><html><head><meta charset="utf-8">
|
||
<title>MSPE DB Seed</title>
|
||
<style>body{font-family:monospace;background:#0f172a;color:#e2e8f0;padding:2rem;max-width:780px;margin:0 auto}
|
||
h1{color:#38bdf8}table{width:100%;border-collapse:collapse;margin:1rem 0}
|
||
td,th{padding:.4rem .8rem;border:1px solid #1e293b;text-align:left}th{background:#1e293b}
|
||
.ok{color:#22c55e}.warn{color:#f59e0b}.err{color:#ef4444}.note{background:#1e293b;padding:1rem;border-left:4px solid #f59e0b;margin:1.5rem 0}
|
||
</style></head><body>';
|
||
echo '<h1>MSPE Database Seed</h1>';
|
||
echo '<table><tr><th>Table</th><th>Status</th><th>Details</th></tr>';
|
||
foreach ($results as $r) {
|
||
$detail = array_diff_key($r, array_flip(['table', 'status']));
|
||
echo '<tr><td>' . htmlspecialchars($r['table']) . '</td><td>' . $badge($r['status']) . '</td><td>' . htmlspecialchars(json_encode($detail)) . '</td></tr>';
|
||
}
|
||
echo '</table>';
|
||
echo '<p>Stats cache files cleared: <strong>' . $cleared . '</strong></p>';
|
||
echo '<p>Elapsed time: <strong>' . $elapsed . 's</strong></p>';
|
||
|
||
if ($allOk) {
|
||
echo '<div class="note">✅ Seed complete. <strong style="color:#ef4444">DELETE db_seed.php from your server immediately!</strong></div>';
|
||
} else {
|
||
echo '<div class="note">⚠️ Some tables had errors. Check your Hostinger error logs.</div>';
|
||
}
|
||
echo '</body></html>';
|
||
ob_end_flush();
|
||
}
|