pguzman
663fde3909
Full source for loveandrosary.com: slide-based Rosary/novena/Divine Mercy Chaplet presentation tool with multi-user roles, SVG bead ring, audio uploads, donate strip, and public session profiles. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
278 lines
13 KiB
PHP
278 lines
13 KiB
PHP
<?php
|
|
/**
|
|
* migrate_v3.php — Run once then DELETE this file.
|
|
* Creates multi-user tables, adds columns, seeds data.
|
|
*/
|
|
require_once __DIR__ . '/config/db.php';
|
|
|
|
$pdo = get_pdo();
|
|
$log = [];
|
|
$errors = [];
|
|
|
|
function run_sql(PDO $pdo, string $label, string $sql, array &$log, array &$errors): void {
|
|
try {
|
|
$pdo->exec($sql);
|
|
$log[] = ['ok', $label];
|
|
} catch (PDOException $e) {
|
|
// Ignore "already exists" / "duplicate column" errors (1060, 1061, 1050)
|
|
if (in_array($e->errorInfo[1], [1060, 1061, 1050], true)) {
|
|
$log[] = ['skip', $label . ' (already exists, skipped)'];
|
|
} else {
|
|
$errors[] = $label . ': ' . $e->getMessage();
|
|
$log[] = ['err', $label . ': ' . $e->getMessage()];
|
|
}
|
|
}
|
|
}
|
|
|
|
// ── 1. Create users table ────────────────────────────────────────────────────
|
|
run_sql($pdo, 'Create users table', "
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
display_name VARCHAR(100) NULL,
|
|
role ENUM('superadmin','admin','superuser','user') NOT NULL DEFAULT 'user',
|
|
rosary_limit INT NOT NULL DEFAULT 1,
|
|
email_confirmed TINYINT(1) NOT NULL DEFAULT 0,
|
|
confirm_token VARCHAR(64) NULL,
|
|
reset_token VARCHAR(64) NULL,
|
|
reset_expires DATETIME NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
", $log, $errors);
|
|
|
|
// ── 2. Create site_settings table ───────────────────────────────────────────
|
|
run_sql($pdo, 'Create site_settings table', "
|
|
CREATE TABLE IF NOT EXISTS site_settings (
|
|
key_name VARCHAR(100) PRIMARY KEY,
|
|
val TEXT NULL,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
", $log, $errors);
|
|
|
|
// ── 3. Create sessions table (fresh install) ────────────────────────────────
|
|
run_sql($pdo, 'Create sessions table', "
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
occasion VARCHAR(50) NOT NULL,
|
|
mystery_set VARCHAR(50) NOT NULL,
|
|
novena_day TINYINT NULL,
|
|
subject_name VARCHAR(255) NULL,
|
|
subject_pronoun VARCHAR(10) NULL,
|
|
subject_dates VARCHAR(150) NULL,
|
|
photo_path VARCHAR(500) NULL,
|
|
novena_group_id INT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
", $log, $errors);
|
|
|
|
// ── 4. Create novena_groups table (fresh install) ────────────────────────────
|
|
run_sql($pdo, 'Create novena_groups table', "
|
|
CREATE TABLE IF NOT EXISTS novena_groups (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
mystery_set VARCHAR(50) NOT NULL DEFAULT 'sorrowful',
|
|
subject_name VARCHAR(255) NULL,
|
|
subject_pronoun VARCHAR(10) NULL,
|
|
subject_dates VARCHAR(150) NULL,
|
|
photo_path VARCHAR(500) NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
", $log, $errors);
|
|
|
|
// ── 5. Add columns to sessions ───────────────────────────────────────────────
|
|
foreach ([
|
|
['Add sessions.user_id', "ALTER TABLE sessions ADD COLUMN user_id INT NULL AFTER id"],
|
|
['Add sessions.is_public', "ALTER TABLE sessions ADD COLUMN is_public TINYINT(1) NOT NULL DEFAULT 1 AFTER user_id"],
|
|
['Add sessions.slug', "ALTER TABLE sessions ADD COLUMN slug VARCHAR(255) NULL AFTER is_public"],
|
|
] as [$label, $sql]) {
|
|
run_sql($pdo, $label, $sql, $log, $errors);
|
|
}
|
|
|
|
// ── 6. Add columns to novena_groups ─────────────────────────────────────────
|
|
foreach ([
|
|
['Add novena_groups.user_id', "ALTER TABLE novena_groups ADD COLUMN user_id INT NULL AFTER id"],
|
|
['Add novena_groups.is_public', "ALTER TABLE novena_groups ADD COLUMN is_public TINYINT(1) NOT NULL DEFAULT 1 AFTER user_id"],
|
|
['Add novena_groups.slug', "ALTER TABLE novena_groups ADD COLUMN slug VARCHAR(255) NULL AFTER is_public"],
|
|
] as [$label, $sql]) {
|
|
run_sql($pdo, $label, $sql, $log, $errors);
|
|
}
|
|
|
|
// ── 7. Seed site_settings ────────────────────────────────────────────────────
|
|
$settings = [
|
|
'smtp_host' => '',
|
|
'smtp_port' => '587',
|
|
'smtp_user' => '',
|
|
'smtp_pass' => '',
|
|
'smtp_from' => '',
|
|
'smtp_from_name' => 'Rosary Presenter',
|
|
'site_name' => 'Rosary Presenter',
|
|
'site_url' => '',
|
|
];
|
|
$ins_setting = $pdo->prepare('INSERT IGNORE INTO site_settings (key_name, val) VALUES (?, ?)');
|
|
foreach ($settings as $k => $v) {
|
|
try {
|
|
$ins_setting->execute([$k, $v]);
|
|
$log[] = ['ok', "Seeded site_settings: {$k}"];
|
|
} catch (PDOException $e) {
|
|
$errors[] = "site_settings {$k}: " . $e->getMessage();
|
|
}
|
|
}
|
|
|
|
// ── 8. Seed superadmin user ──────────────────────────────────────────────────
|
|
$supadmin_hash = password_hash('supadmin', PASSWORD_BCRYPT);
|
|
try {
|
|
$pdo->prepare("
|
|
INSERT IGNORE INTO users (username, email, password_hash, display_name, role, rosary_limit, email_confirmed)
|
|
VALUES ('supadmin', 'admin@example.com', ?, 'Super Admin', 'superadmin', -1, 1)
|
|
")->execute([$supadmin_hash]);
|
|
$log[] = ['ok', 'Seeded superadmin user'];
|
|
} catch (PDOException $e) {
|
|
$errors[] = 'Seed superadmin: ' . $e->getMessage();
|
|
}
|
|
|
|
// Get superadmin ID
|
|
$supadmin_row = $pdo->query("SELECT id FROM users WHERE username = 'supadmin'")->fetch();
|
|
$supadmin_id = $supadmin_row ? (int)$supadmin_row['id'] : null;
|
|
|
|
if ($supadmin_id) {
|
|
// ── 9. Assign unowned sessions to superadmin ─────────────────────────────
|
|
try {
|
|
$affected = $pdo->prepare("UPDATE sessions SET user_id = ? WHERE user_id IS NULL")
|
|
->execute([$supadmin_id]);
|
|
$log[] = ['ok', 'Assigned orphan sessions to superadmin'];
|
|
} catch (PDOException $e) {
|
|
$errors[] = 'Assign sessions: ' . $e->getMessage();
|
|
}
|
|
|
|
// ── 10. Assign unowned novena_groups to superadmin ────────────────────────
|
|
try {
|
|
$pdo->prepare("UPDATE novena_groups SET user_id = ? WHERE user_id IS NULL")
|
|
->execute([$supadmin_id]);
|
|
$log[] = ['ok', 'Assigned orphan novena_groups to superadmin'];
|
|
} catch (PDOException $e) {
|
|
$errors[] = 'Assign novena_groups: ' . $e->getMessage();
|
|
}
|
|
|
|
// ── 11. Generate slugs for sessions without one ───────────────────────────
|
|
try {
|
|
$sessions_no_slug = $pdo->query("SELECT id, name, user_id FROM sessions WHERE slug IS NULL OR slug = ''")->fetchAll();
|
|
$upd_slug = $pdo->prepare("UPDATE sessions SET slug = ? WHERE id = ?");
|
|
foreach ($sessions_no_slug as $row) {
|
|
$uid = (int)($row['user_id'] ?? $supadmin_id);
|
|
$base = slugify($row['name']);
|
|
$slug = unique_slug($row['name'], $uid, 'sessions', (int)$row['id']);
|
|
$upd_slug->execute([$slug, $row['id']]);
|
|
}
|
|
$log[] = ['ok', 'Generated slugs for ' . count($sessions_no_slug) . ' sessions'];
|
|
} catch (PDOException $e) {
|
|
$errors[] = 'Generate session slugs: ' . $e->getMessage();
|
|
}
|
|
|
|
// ── 12. Generate slugs for novena_groups without one ─────────────────────
|
|
try {
|
|
$groups_no_slug = $pdo->query("SELECT id, name, user_id FROM novena_groups WHERE slug IS NULL OR slug = ''")->fetchAll();
|
|
$upd_gslug = $pdo->prepare("UPDATE novena_groups SET slug = ? WHERE id = ?");
|
|
foreach ($groups_no_slug as $row) {
|
|
$uid = (int)($row['user_id'] ?? $supadmin_id);
|
|
$slug = unique_slug($row['name'], $uid, 'novena_groups', (int)$row['id']);
|
|
$upd_gslug->execute([$slug, $row['id']]);
|
|
}
|
|
$log[] = ['ok', 'Generated slugs for ' . count($groups_no_slug) . ' novena groups'];
|
|
} catch (PDOException $e) {
|
|
$errors[] = 'Generate novena_group slugs: ' . $e->getMessage();
|
|
}
|
|
}
|
|
|
|
// ── Render result page ───────────────────────────────────────────────────────
|
|
$overall_ok = empty($errors);
|
|
?>
|
|
<!DOCTYPE html>
|
|
<html lang="en">
|
|
<head>
|
|
<meta charset="UTF-8">
|
|
<meta name="viewport" content="width=device-width,initial-scale=1">
|
|
<title>Migration v3 — <?= APP_NAME ?></title>
|
|
<style>
|
|
*{box-sizing:border-box}
|
|
body{font-family:system-ui,-apple-system,sans-serif;background:#f4f4f5;margin:0;padding:32px 16px}
|
|
.wrap{max-width:720px;margin:0 auto}
|
|
h1{font-size:26px;margin-bottom:4px}
|
|
.banner{border-radius:8px;padding:20px 24px;margin-bottom:24px;font-size:15px}
|
|
.banner.ok{background:#d1fae5;border:1px solid #6ee7b7;color:#065f46}
|
|
.banner.err{background:#fee2e2;border:1px solid #fca5a5;color:#991b1b}
|
|
.warn{background:#fef3c7;border:1px solid #fcd34d;color:#92400e;border-radius:8px;padding:16px 20px;margin-bottom:24px;font-weight:600}
|
|
.card{background:#fff;border-radius:8px;padding:24px;margin-bottom:20px;box-shadow:0 1px 3px rgba(0,0,0,.07)}
|
|
.cred{background:#1e3a5f;color:#e0f2fe;border-radius:6px;padding:16px 20px;font-family:monospace;font-size:15px;line-height:1.7}
|
|
table{width:100%;border-collapse:collapse;font-size:13px}
|
|
th,td{text-align:left;padding:6px 10px;border-bottom:1px solid #e5e7eb}
|
|
th{background:#f9fafb;font-weight:600}
|
|
.ok{color:#15803d}.err{color:#b91c1c}.skip{color:#d97706}
|
|
</style>
|
|
</head>
|
|
<body>
|
|
<div class="wrap">
|
|
<h1>✝ <?= APP_NAME ?> — Migration v3</h1>
|
|
|
|
<?php if ($overall_ok): ?>
|
|
<div class="banner ok">
|
|
<strong>Migration completed successfully.</strong> All steps passed (or were already applied).
|
|
</div>
|
|
<?php else: ?>
|
|
<div class="banner err">
|
|
<strong>Migration finished with errors.</strong> Review the log below. Some steps may need manual attention.
|
|
</div>
|
|
<?php endif; ?>
|
|
|
|
<div class="warn">
|
|
⚠ DELETE this file (<code>migrate_v3.php</code>) from your server immediately after reviewing this page.
|
|
</div>
|
|
|
|
<div class="card">
|
|
<h2 style="margin-top:0">Superadmin Credentials</h2>
|
|
<div class="cred">
|
|
Username: supadmin<br>
|
|
Password: supadmin<br>
|
|
Role: superadmin
|
|
</div>
|
|
<p style="color:#b91c1c;font-weight:600;margin-top:12px">
|
|
CHANGE THE PASSWORD IMMEDIATELY — go to <a href="/admin/profile">/admin/profile</a> after logging in.<br>
|
|
Also update the email from <code>admin@example.com</code> to your real email.
|
|
</p>
|
|
</div>
|
|
|
|
<div class="card">
|
|
<h2 style="margin-top:0">Migration Log</h2>
|
|
<table>
|
|
<thead><tr><th>Status</th><th>Step</th></tr></thead>
|
|
<tbody>
|
|
<?php foreach ($log as [$status, $msg]): ?>
|
|
<tr>
|
|
<td class="<?= $status ?>">
|
|
<?= $status === 'ok' ? '✓ OK' : ($status === 'skip' ? '— SKIP' : '✗ ERROR') ?>
|
|
</td>
|
|
<td><?= htmlspecialchars($msg) ?></td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
|
|
<div class="card">
|
|
<h2 style="margin-top:0">Next Steps</h2>
|
|
<ol style="line-height:1.9">
|
|
<li>Delete <code>migrate_v3.php</code> from your server.</li>
|
|
<li>Go to <a href="/login">/login</a> and sign in with <strong>supadmin / supadmin</strong>.</li>
|
|
<li>Go to <a href="/admin/profile">/admin/profile</a> and change your password and email.</li>
|
|
<li>Go to <a href="/admin/settings">/admin/settings</a> to configure SMTP and your site URL.</li>
|
|
</ol>
|
|
</div>
|
|
</div>
|
|
</body>
|
|
</html>
|