131 lines
6.0 KiB
PHP
131 lines
6.0 KiB
PHP
<?php
|
|
|
|
function schema_table_exists(PDO $pdo, string $table): bool {
|
|
$stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?');
|
|
$stmt->execute([$table]);
|
|
return (int) $stmt->fetchColumn() > 0;
|
|
}
|
|
|
|
function schema_column_exists(PDO $pdo, string $table, string $column): bool {
|
|
$stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?');
|
|
$stmt->execute([$table, $column]);
|
|
return (int) $stmt->fetchColumn() > 0;
|
|
}
|
|
|
|
function ensure_app_schema(PDO $pdo): void {
|
|
static $hasRun = false;
|
|
if ($hasRun) {
|
|
return;
|
|
}
|
|
$hasRun = true;
|
|
|
|
$pdo->exec('CREATE TABLE IF NOT EXISTS migrations (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
migration VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
|
|
|
|
$pdo->exec('CREATE TABLE IF NOT EXISTS webinars (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
presenter VARCHAR(255),
|
|
scheduled_at DATETIME NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
|
|
|
|
$pdo->exec('CREATE TABLE IF NOT EXISTS admin_users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
email VARCHAR(255) NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
display_name VARCHAR(255) NOT NULL DEFAULT "Admin",
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uniq_admin_users_email (email)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
|
|
|
|
$pdo->exec('CREATE TABLE IF NOT EXISTS attendees (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
webinar_id INT NOT NULL,
|
|
first_name VARCHAR(255) NOT NULL DEFAULT "",
|
|
last_name VARCHAR(255) NOT NULL DEFAULT "",
|
|
name VARCHAR(255) NULL,
|
|
email VARCHAR(255) NOT NULL,
|
|
company VARCHAR(255) DEFAULT NULL,
|
|
timezone VARCHAR(255) DEFAULT NULL,
|
|
how_did_you_hear VARCHAR(255) DEFAULT NULL,
|
|
password VARCHAR(255) NOT NULL DEFAULT "",
|
|
consented TINYINT(1) NOT NULL DEFAULT 0,
|
|
deleted_at TIMESTAMP NULL DEFAULT NULL,
|
|
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_attendees_webinar FOREIGN KEY (webinar_id) REFERENCES webinars(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
|
|
|
|
if (schema_table_exists($pdo, 'attendees')) {
|
|
if (schema_column_exists($pdo, 'attendees', 'name')) {
|
|
$pdo->exec('ALTER TABLE attendees MODIFY COLUMN name VARCHAR(255) NULL');
|
|
}
|
|
|
|
if (!schema_column_exists($pdo, 'attendees', 'first_name')) {
|
|
$pdo->exec('ALTER TABLE attendees ADD COLUMN first_name VARCHAR(255) NOT NULL DEFAULT "" AFTER webinar_id');
|
|
}
|
|
|
|
if (!schema_column_exists($pdo, 'attendees', 'last_name')) {
|
|
$pdo->exec('ALTER TABLE attendees ADD COLUMN last_name VARCHAR(255) NOT NULL DEFAULT "" AFTER first_name');
|
|
}
|
|
|
|
if (!schema_column_exists($pdo, 'attendees', 'company')) {
|
|
$pdo->exec('ALTER TABLE attendees ADD COLUMN company VARCHAR(255) DEFAULT NULL AFTER email');
|
|
}
|
|
|
|
if (!schema_column_exists($pdo, 'attendees', 'timezone')) {
|
|
$pdo->exec('ALTER TABLE attendees ADD COLUMN timezone VARCHAR(255) DEFAULT NULL AFTER company');
|
|
}
|
|
|
|
if (!schema_column_exists($pdo, 'attendees', 'how_did_you_hear')) {
|
|
$pdo->exec('ALTER TABLE attendees ADD COLUMN how_did_you_hear VARCHAR(255) DEFAULT NULL AFTER timezone');
|
|
}
|
|
|
|
if (!schema_column_exists($pdo, 'attendees', 'password')) {
|
|
$pdo->exec('ALTER TABLE attendees ADD COLUMN password VARCHAR(255) NOT NULL DEFAULT "" AFTER how_did_you_hear');
|
|
}
|
|
|
|
if (!schema_column_exists($pdo, 'attendees', 'consented')) {
|
|
$pdo->exec('ALTER TABLE attendees ADD COLUMN consented TINYINT(1) NOT NULL DEFAULT 0 AFTER password');
|
|
}
|
|
|
|
if (!schema_column_exists($pdo, 'attendees', 'deleted_at')) {
|
|
$pdo->exec('ALTER TABLE attendees ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER consented');
|
|
}
|
|
|
|
if (!schema_column_exists($pdo, 'attendees', 'created_at')) {
|
|
$pdo->exec('ALTER TABLE attendees ADD COLUMN created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP AFTER company');
|
|
}
|
|
|
|
if (schema_column_exists($pdo, 'attendees', 'registered_at')) {
|
|
$pdo->exec('UPDATE attendees SET created_at = COALESCE(created_at, registered_at)');
|
|
}
|
|
|
|
if (schema_column_exists($pdo, 'attendees', 'name')) {
|
|
$pdo->exec("UPDATE attendees SET first_name = TRIM(SUBSTRING_INDEX(name, ' ', 1)) WHERE (first_name = '' OR first_name IS NULL) AND name IS NOT NULL AND name <> ''");
|
|
$pdo->exec("UPDATE attendees SET last_name = TRIM(SUBSTRING(name, CHAR_LENGTH(SUBSTRING_INDEX(name, ' ', 1)) + 1)) WHERE (last_name = '' OR last_name IS NULL) AND name IS NOT NULL AND name LIKE '% %'");
|
|
}
|
|
}
|
|
|
|
$desiredTitle = 'Building Scalable Apps with AppWizzy';
|
|
$desiredDescription = 'The fastest way to go from an idea to a working app you own, running on your server, with your database, using real frameworks.';
|
|
$desiredPresenter = 'AppWizzy Team';
|
|
$desiredScheduledAt = '2026-03-25 18:00:00';
|
|
|
|
$stmt = $pdo->prepare('SELECT COUNT(*) FROM webinars WHERE id = 1');
|
|
$stmt->execute();
|
|
$hasPrimaryWebinar = (int) $stmt->fetchColumn() > 0;
|
|
|
|
if ($hasPrimaryWebinar) {
|
|
$update = $pdo->prepare('UPDATE webinars SET title = ?, description = ?, presenter = ?, scheduled_at = ? WHERE id = 1');
|
|
$update->execute([$desiredTitle, $desiredDescription, $desiredPresenter, $desiredScheduledAt]);
|
|
} else {
|
|
$insert = $pdo->prepare('INSERT INTO webinars (id, title, description, presenter, scheduled_at) VALUES (1, ?, ?, ?, ?)');
|
|
$insert->execute([$desiredTitle, $desiredDescription, $desiredPresenter, $desiredScheduledAt]);
|
|
}
|
|
}
|