v1.1.0.0
StocketBase
- <?php
-
- declare(strict_types=1);
-
- require_once __DIR__ . '/config.php';
-
- final class Database
- {
- private static ?PDO $site = null;
- private static ?PDO $users = null;
- private static bool $schemaChecked = false;
- private static bool $usersSchemaChecked = false;
-
- public static function site(): PDO
- {
- if (self::$site instanceof PDO) {
- return self::$site;
- }
-
- self::$site = self::connect(
- Config::get('DB_SITE_HOST', '127.0.0.1'),
- Config::get('DB_SITE_PORT', '3306'),
- Config::get('DB_SITE_NAME', ''),
- Config::get('DB_SITE_USER', ''),
- Config::get('DB_SITE_PASS', '')
- );
-
- self::ensureSiteSchema(self::$site);
-
- return self::$site;
- }
-
- public static function users(): PDO
- {
- if (self::$users instanceof PDO) {
- return self::$users;
- }
-
- self::$users = self::connect(
- Config::get('DB_USERS_HOST', '127.0.0.1'),
- Config::get('DB_USERS_PORT', '3306'),
- Config::get('DB_USERS_NAME', ''),
- Config::get('DB_USERS_USER', ''),
- Config::get('DB_USERS_PASS', '')
- );
-
- self::ensureUsersSchema(self::$users);
-
- return self::$users;
- }
-
- private static function ensureUsersSchema(PDO $pdo): void
- {
- if (self::$usersSchemaChecked) {
- return;
- }
- self::$usersSchemaChecked = true;
-
- try {
- $dbName = (string) $pdo->query('SELECT DATABASE()')->fetchColumn();
- if ($dbName === '') {
- return;
- }
-
- $tablesStatement = $pdo->prepare('SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = :db');
- $tablesStatement->execute(['db' => $dbName]);
- $existingTables = array_map('strtolower', $tablesStatement->fetchAll(PDO::FETCH_COLUMN));
-
- $columnsStatement = $pdo->prepare('SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = :db AND TABLE_NAME IN (\'orders\', \'user_accounts\')');
- $columnsStatement->execute(['db' => $dbName]);
- $existingColumns = [];
- foreach ($columnsStatement->fetchAll() as $row) {
- $existingColumns[strtolower((string) $row['TABLE_NAME'])][strtolower((string) $row['COLUMN_NAME'])] = true;
- }
-
- $requiredColumns = [
- 'orders' => [
- 'payment_method' => 'ALTER TABLE orders ADD COLUMN payment_method VARCHAR(30) NULL AFTER provider',
- 'shipping_method' => 'ALTER TABLE orders ADD COLUMN shipping_method VARCHAR(160) NULL AFTER shipping_cents',
- 'shipping_phone' => 'ALTER TABLE orders ADD COLUMN shipping_phone VARCHAR(40) NULL AFTER shipping_country',
- 'billing_company' => 'ALTER TABLE orders ADD COLUMN billing_company VARCHAR(190) NULL AFTER billing_name',
- 'billing_tax_id' => 'ALTER TABLE orders ADD COLUMN billing_tax_id VARCHAR(60) NULL AFTER billing_company',
- 'prices_include_tax' => 'ALTER TABLE orders ADD COLUMN prices_include_tax TINYINT(1) NOT NULL DEFAULT 0 AFTER tax_cents',
- 'tax_rate_bp' => 'ALTER TABLE orders ADD COLUMN tax_rate_bp INT UNSIGNED NOT NULL DEFAULT 0 AFTER prices_include_tax',
- 'access_token' => 'ALTER TABLE orders ADD COLUMN access_token CHAR(64) NULL, ADD UNIQUE KEY uq_order_access_token (access_token)',
- 'invoice_number' => 'ALTER TABLE orders ADD COLUMN invoice_number VARCHAR(40) NULL',
- 'paid_at' => 'ALTER TABLE orders ADD COLUMN paid_at DATETIME NULL',
- 'canceled_at' => 'ALTER TABLE orders ADD COLUMN canceled_at DATETIME NULL',
- 'stock_reserved' => 'ALTER TABLE orders ADD COLUMN stock_reserved TINYINT(1) NOT NULL DEFAULT 0',
- 'cod_fee_cents' => 'ALTER TABLE orders ADD COLUMN cod_fee_cents INT UNSIGNED NOT NULL DEFAULT 0 AFTER shipping_cents',
- ],
- 'user_accounts' => [
- 'phone' => 'ALTER TABLE user_accounts ADD COLUMN phone VARCHAR(40) NULL AFTER display_name',
- 'marketing_opt_in' => 'ALTER TABLE user_accounts ADD COLUMN marketing_opt_in TINYINT(1) NOT NULL DEFAULT 0',
- ],
- ];
-
- foreach ($requiredColumns as $table => $columns) {
- if (!in_array($table, $existingTables, true)) {
- continue;
- }
- foreach ($columns as $column => $sql) {
- if (isset($existingColumns[$table][$column])) {
- continue;
- }
- try {
- $pdo->exec($sql);
- } catch (PDOException $e) {
- error_log('StocketBase: could not auto-add column ' . $table . '.' . $column . ': ' . $e->getMessage());
- }
- }
- }
-
- foreach (self::usersTableDefinitions() as $table => $sql) {
- if (in_array($table, $existingTables, true)) {
- continue;
- }
- try {
- $pdo->exec($sql);
- } catch (PDOException $e) {
- error_log('StocketBase: could not auto-create table ' . $table . ': ' . $e->getMessage());
- }
- }
- } catch (PDOException $e) {
- error_log('StocketBase: users schema auto-check failed: ' . $e->getMessage());
- }
- }
-
- public static function usersTableDefinitions(): array
- {
- return [
- 'user_addresses' => "CREATE TABLE user_addresses (
- id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
- user_account_id INT UNSIGNED NOT NULL,
- label VARCHAR(60) NULL,
- full_name VARCHAR(190) NOT NULL,
- company VARCHAR(190) NULL,
- tax_id VARCHAR(60) NULL,
- address_line1 VARCHAR(255) NOT NULL,
- address_line2 VARCHAR(255) NULL,
- city VARCHAR(120) NOT NULL,
- region VARCHAR(120) NULL,
- postal_code VARCHAR(30) NOT NULL,
- country CHAR(2) NOT NULL,
- phone VARCHAR(40) NULL,
- is_default_shipping TINYINT(1) NOT NULL DEFAULT 0,
- is_default_billing TINYINT(1) NOT NULL DEFAULT 0,
- created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- KEY idx_address_account (user_account_id),
- CONSTRAINT fk_address_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE CASCADE
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
- 'account_tokens' => "CREATE TABLE account_tokens (
- id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
- user_account_id INT UNSIGNED NOT NULL,
- purpose VARCHAR(20) NOT NULL,
- token_hash CHAR(64) NOT NULL,
- expires_at DATETIME NOT NULL,
- used_at DATETIME NULL,
- created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
- UNIQUE KEY uq_account_token_hash (token_hash),
- KEY idx_account_token_user (user_account_id, purpose),
- CONSTRAINT fk_account_token_account FOREIGN KEY (user_account_id) REFERENCES user_accounts(id) ON DELETE CASCADE
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
- 'license_keys' => "CREATE TABLE license_keys (
- id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
- product_id INT UNSIGNED NOT NULL,
- license_key VARCHAR(255) NOT NULL,
- source ENUM('pool','generated') NOT NULL DEFAULT 'pool',
- order_id INT UNSIGNED NULL,
- order_item_id INT UNSIGNED NULL,
- user_account_id INT UNSIGNED NULL,
- assigned_at DATETIME NULL,
- revoked_at DATETIME NULL,
- created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
- UNIQUE KEY uq_license_product_key (product_id, license_key),
- KEY idx_license_free (product_id, order_id),
- KEY idx_license_order (order_id),
- KEY idx_license_account (user_account_id)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
- ];
- }
-
- private static function connect(?string $host, ?string $port, ?string $name, ?string $user, ?string $pass): PDO
- {
- $dsn = sprintf('mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4', $host, $port, $name);
-
- return new PDO($dsn, $user, $pass, [
- PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
- PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
- PDO::ATTR_EMULATE_PREPARES => false,
- ]);
- }
-
- private static function ensureSettingsKey(PDO $pdo, string $dbName): void
- {
- $tableCheck = $pdo->prepare("SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = :db AND TABLE_NAME = 'site_settings'");
- $tableCheck->execute(['db' => $dbName]);
- if ((int) $tableCheck->fetchColumn() === 0) {
- return;
- }
-
- $keyCheck = $pdo->prepare(
- "SELECT COUNT(*) FROM information_schema.STATISTICS
- WHERE TABLE_SCHEMA = :db AND TABLE_NAME = 'site_settings' AND COLUMN_NAME = 'setting_key' AND NON_UNIQUE = 0 AND SEQ_IN_INDEX = 1"
- );
- $keyCheck->execute(['db' => $dbName]);
- if ((int) $keyCheck->fetchColumn() > 0) {
- return;
- }
-
- try {
- $helperCheck = $pdo->prepare(
- "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = :db AND TABLE_NAME = 'site_settings' AND COLUMN_NAME = 'sb_row_id'"
- );
- $helperCheck->execute(['db' => $dbName]);
- if ((int) $helperCheck->fetchColumn() === 0) {
- $pdo->exec('ALTER TABLE site_settings ADD COLUMN sb_row_id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE');
- }
- $pdo->exec('DELETE older FROM site_settings older JOIN site_settings newer ON newer.setting_key = older.setting_key AND newer.sb_row_id > older.sb_row_id');
- $pdo->exec('ALTER TABLE site_settings ADD PRIMARY KEY (setting_key)');
- $pdo->exec('ALTER TABLE site_settings DROP COLUMN sb_row_id');
- } catch (PDOException $e) {
- error_log('StocketBase: could not repair site_settings key: ' . $e->getMessage());
- }
- }
-
- private static function ensureSiteSchema(PDO $pdo): void
- {
- if (self::$schemaChecked) {
- return;
- }
- self::$schemaChecked = true;
-
- try {
- $dbName = $pdo->query('SELECT DATABASE()')->fetchColumn();
- if (!$dbName) {
- return;
- }
-
- $columnCheck = $pdo->prepare(
- 'SELECT COUNT(*) FROM information_schema.COLUMNS
- WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :table AND COLUMN_NAME = :column'
- );
-
- $requiredColumns = [
- 'team_accounts' => [
- 'avatar_path' => "ALTER TABLE team_accounts ADD COLUMN avatar_path VARCHAR(255) NULL",
- 'totp_secret' => "ALTER TABLE team_accounts ADD COLUMN totp_secret VARCHAR(64) NULL",
- 'totp_enabled' => "ALTER TABLE team_accounts ADD COLUMN totp_enabled TINYINT(1) NOT NULL DEFAULT 0",
- 'totp_recovery' => "ALTER TABLE team_accounts ADD COLUMN totp_recovery TEXT NULL",
- 'totp_last_step' => "ALTER TABLE team_accounts ADD COLUMN totp_last_step BIGINT NULL",
- ],
- 'products' => [
- 'deleted_at' => "ALTER TABLE products ADD COLUMN deleted_at DATETIME NULL",
- 'trashed_status' => "ALTER TABLE products ADD COLUMN trashed_status VARCHAR(20) NULL",
- 'license_mode' => "ALTER TABLE products ADD COLUMN license_mode VARCHAR(10) NOT NULL DEFAULT 'none'",
- 'license_pattern' => "ALTER TABLE products ADD COLUMN license_pattern VARCHAR(80) NULL",
- 'low_stock_alerted_at' => "ALTER TABLE products ADD COLUMN low_stock_alerted_at DATETIME NULL",
- ],
- 'pages' => [
- 'is_home' => "ALTER TABLE pages ADD COLUMN is_home TINYINT(1) NOT NULL DEFAULT 0",
- ],
- 'ads' => [
- 'consent_run' => "ALTER TABLE ads ADD COLUMN consent_run VARCHAR(20) NULL AFTER link_url",
- ],
- 'analytics_scripts' => [
- 'consent_category' => "ALTER TABLE analytics_scripts ADD COLUMN consent_category VARCHAR(20) NOT NULL DEFAULT 'analytics' AFTER requires_consent",
- ],
- 'categories' => [
- 'show_in_menu' => "ALTER TABLE categories ADD COLUMN show_in_menu TINYINT(1) NOT NULL DEFAULT 1 AFTER sort_order",
- ],
- 'product_files' => [
- 'product_title' => "ALTER TABLE product_files ADD COLUMN product_title VARCHAR(255) NULL",
- 'detached_at' => "ALTER TABLE product_files ADD COLUMN detached_at DATETIME NULL",
- 'button_label' => "ALTER TABLE product_files ADD COLUMN button_label VARCHAR(120) NULL",
- 'download_count' => "ALTER TABLE product_files ADD COLUMN download_count INT UNSIGNED NOT NULL DEFAULT 0",
- ],
- 'nav_items' => [
- 'icon_key' => "ALTER TABLE nav_items ADD COLUMN icon_key VARCHAR(30) NULL AFTER target",
- 'icon_svg' => "ALTER TABLE nav_items ADD COLUMN icon_svg MEDIUMTEXT NULL AFTER icon_key",
- ],
- ];
-
- foreach ($requiredColumns as $table => $columns) {
- $tableCheck = $pdo->prepare(
- 'SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :table'
- );
- $tableCheck->execute(['db' => $dbName, 'table' => $table]);
- if ((int) $tableCheck->fetchColumn() === 0) {
- continue;
- }
-
- foreach ($columns as $column => $alterSql) {
- $columnCheck->execute(['db' => $dbName, 'table' => $table, 'column' => $column]);
- if ((int) $columnCheck->fetchColumn() === 0) {
- try {
- $pdo->exec($alterSql);
- } catch (PDOException $e) {
- error_log('StocketBase: could not auto-add column ' . $table . '.' . $column . ': ' . $e->getMessage());
- }
- }
- }
- }
-
- self::ensureSettingsKey($pdo, (string) $dbName);
-
- $authorColumns = ['products' => 'created_by', 'pages' => 'author_id', 'ads' => 'created_by', 'discounts' => 'created_by'];
- $authorState = $pdo->prepare(
- "SELECT c.TABLE_NAME, c.IS_NULLABLE, k.CONSTRAINT_NAME, r.DELETE_RULE
- FROM information_schema.COLUMNS c
- LEFT JOIN information_schema.KEY_COLUMN_USAGE k
- ON k.TABLE_SCHEMA = c.TABLE_SCHEMA AND k.TABLE_NAME = c.TABLE_NAME AND k.COLUMN_NAME = c.COLUMN_NAME AND k.REFERENCED_TABLE_NAME = 'team_accounts'
- LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS r
- ON r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND r.TABLE_NAME = k.TABLE_NAME
- WHERE c.TABLE_SCHEMA = :db
- AND ((c.TABLE_NAME = 'products' AND c.COLUMN_NAME = 'created_by')
- OR (c.TABLE_NAME = 'pages' AND c.COLUMN_NAME = 'author_id')
- OR (c.TABLE_NAME = 'ads' AND c.COLUMN_NAME = 'created_by')
- OR (c.TABLE_NAME = 'discounts' AND c.COLUMN_NAME = 'created_by'))"
- );
- $authorState->execute(['db' => $dbName]);
- foreach ($authorState->fetchAll(PDO::FETCH_ASSOC) as $state) {
- $table = (string) $state['TABLE_NAME'];
- $column = $authorColumns[$table] ?? null;
- if ($column === null) {
- continue;
- }
- $constraint = (string) ($state['CONSTRAINT_NAME'] ?? '');
- $needsNullable = $state['IS_NULLABLE'] === 'NO';
- $needsRule = $constraint !== '' && strtoupper((string) $state['DELETE_RULE']) !== 'SET NULL';
- if (!$needsNullable && !$needsRule) {
- continue;
- }
- if (preg_match('/^[A-Za-z0-9_]+$/', $constraint) !== 1 && $constraint !== '') {
- continue;
- }
- try {
- if ($needsRule) {
- $pdo->exec('ALTER TABLE ' . $table . ' DROP FOREIGN KEY ' . $constraint);
- }
- if ($needsNullable) {
- $pdo->exec('ALTER TABLE ' . $table . ' MODIFY ' . $column . ' INT UNSIGNED NULL');
- }
- if ($needsRule) {
- $pdo->exec('ALTER TABLE ' . $table . ' ADD CONSTRAINT ' . $constraint . ' FOREIGN KEY (' . $column . ') REFERENCES team_accounts(id) ON DELETE SET NULL');
- }
- if ($table === 'pages') {
- $pdo->exec('UPDATE pages SET author_id = NULL WHERE is_home = 1');
- }
- } catch (PDOException $e) {
- error_log('StocketBase: could not update author key on ' . $table . ': ' . $e->getMessage());
- }
- }
-
- $requiredTables = [
- 'site_settings' => "CREATE TABLE site_settings (
- setting_key VARCHAR(120) NOT NULL PRIMARY KEY,
- setting_value LONGTEXT NULL,
- updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
- 'nav_items' => "CREATE TABLE nav_items (
- id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
- item_type ENUM('category','page','custom') NOT NULL,
- ref_id INT UNSIGNED NULL,
- label VARCHAR(120) NULL,
- url VARCHAR(255) NULL,
- target ENUM('_self','_blank') NOT NULL DEFAULT '_self',
- icon_key VARCHAR(30) NULL,
- icon_svg MEDIUMTEXT NULL,
- sort_order INT NOT NULL DEFAULT 0,
- created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- KEY idx_nav_sort (sort_order)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
- 'activity_log' => "CREATE TABLE activity_log (
- id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
- user_id INT UNSIGNED NULL,
- user_name VARCHAR(120) NULL,
- action VARCHAR(60) NOT NULL,
- entity_type VARCHAR(30) NULL,
- entity_id INT UNSIGNED NULL,
- details VARCHAR(500) NULL,
- ip_address VARCHAR(45) NULL,
- created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
- KEY idx_activity_created (created_at)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
- 'product_versions' => "CREATE TABLE product_versions (
- id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
- product_id INT UNSIGNED NOT NULL,
- content_hash CHAR(40) NOT NULL,
- title VARCHAR(255) NOT NULL,
- excerpt TEXT NULL,
- content_blocks LONGTEXT NULL,
- category_id INT UNSIGNED NULL,
- cover_image_path VARCHAR(500) NULL,
- seo_title VARCHAR(255) NULL,
- seo_description TEXT NULL,
- price_cents INT UNSIGNED NULL,
- compare_at_price_cents INT UNSIGNED NULL,
- saved_by INT UNSIGNED NULL,
- saved_by_name VARCHAR(120) NULL,
- note VARCHAR(60) NOT NULL DEFAULT 'save',
- created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
- KEY idx_version_product (product_id, id)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
- 'tags' => "CREATE TABLE tags (
- id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
- name VARCHAR(60) NOT NULL,
- slug VARCHAR(80) NOT NULL,
- UNIQUE KEY uniq_tag_slug (slug)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
- 'product_tags' => "CREATE TABLE product_tags (
- product_id INT UNSIGNED NOT NULL,
- tag_id INT UNSIGNED NOT NULL,
- PRIMARY KEY (product_id, tag_id),
- KEY idx_product_tags_tag (tag_id)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
- 'product_files' => "CREATE TABLE product_files (
- id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
- product_id INT UNSIGNED NOT NULL,
- original_name VARCHAR(190) NOT NULL,
- stored_name CHAR(32) NOT NULL,
- mime_type VARCHAR(120) NOT NULL DEFAULT 'application/octet-stream',
- size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
- sha256 CHAR(64) NULL,
- sort_order INT NOT NULL DEFAULT 0,
- product_title VARCHAR(255) NULL,
- detached_at DATETIME NULL,
- button_label VARCHAR(120) NULL,
- download_count INT UNSIGNED NOT NULL DEFAULT 0,
- created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
- UNIQUE KEY uq_product_file_stored (stored_name),
- KEY idx_product_files_product (product_id)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
- 'login_attempts' => "CREATE TABLE login_attempts (
- id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
- scope VARCHAR(20) NOT NULL,
- identifier_hash CHAR(64) NOT NULL,
- ip_address VARCHAR(45) NOT NULL,
- created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
- KEY idx_attempt_identifier (scope, identifier_hash, created_at),
- KEY idx_attempt_ip (scope, ip_address, created_at)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
- 'indexnow_log' => "CREATE TABLE indexnow_log (
- product_id INT UNSIGNED NOT NULL PRIMARY KEY,
- submitted_at DATETIME NOT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
- 'site_languages' => "CREATE TABLE site_languages (
- code VARCHAR(10) NOT NULL PRIMARY KEY,
- name VARCHAR(60) NOT NULL,
- sort_order INT NOT NULL DEFAULT 0,
- is_active TINYINT(1) NOT NULL DEFAULT 0
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
- 'content_translations' => "CREATE TABLE content_translations (
- id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
- entity_type VARCHAR(12) NOT NULL,
- entity_id INT UNSIGNED NOT NULL,
- lang VARCHAR(10) NOT NULL,
- title VARCHAR(255) NOT NULL DEFAULT '',
- excerpt TEXT NULL,
- content_blocks LONGTEXT NULL,
- seo_title VARCHAR(255) NULL,
- seo_description VARCHAR(500) NULL,
- updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- UNIQUE KEY uniq_translation (entity_type, entity_id, lang),
- KEY idx_translation_lang (lang, entity_type)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
- ];
-
- $tableExistsCheck = $pdo->prepare(
- 'SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = :db AND TABLE_NAME = :table'
- );
-
- foreach ($requiredTables as $table => $createSql) {
- $tableExistsCheck->execute(['db' => $dbName, 'table' => $table]);
- if ((int) $tableExistsCheck->fetchColumn() === 0) {
- try {
- $pdo->exec($createSql);
- } catch (PDOException $e) {
- error_log('StocketBase: could not auto-create table ' . $table . ': ' . $e->getMessage());
- }
- }
- }
- } catch (PDOException $e) {
- error_log('StocketBase: schema auto-check failed: ' . $e->getMessage());
- }
- }
- }
-