File Manager

✏️ Edit File: /home/u625410797/domains/monothailand.com/public_html/scripts/update_database_world_class.php

<?php
/**
 * World-Class Database Schema Application & Upgrader Script
 * Targets absolute website customization and 100% front-to-back alignment.
 */

error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "=== MONOTHAILAND WORLD-CLASS DATABASE MIGRATION ENGINE ===\n\n";

// Load configuration
$config_file = __DIR__ . '/includes/config.php';
if (!file_exists($config_file)) {
    die("ERROR: config.php not found at: $config_file\n");
}

require_once $config_file;

// Test database connection
echo "1. Connecting to MySQL Database...\n";
try {
    $pdo = new PDO(
        "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4",
        DB_USER,
        DB_PASS,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
    );
    echo "   ✓ Database connection successful\n";
    echo "   Host: " . DB_HOST . "\n";
    echo "   Database: " . DB_NAME . "\n\n";
} catch (PDOException $e) {
    die("   ✗ Database connection failed: " . $e->getMessage() . "\n\n");
}

// 1. CREATE theme_configurations
echo "2. Applying `theme_configurations` schema...\n";
try {
    $sql = "CREATE TABLE IF NOT EXISTS `theme_configurations` (
      `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      `theme_mode` ENUM('light', 'dark', 'system') NOT NULL DEFAULT 'dark',
      `primary_color` VARCHAR(7) NOT NULL DEFAULT '#10b981' COMMENT 'Neo-Emerald',
      `secondary_color` VARCHAR(7) NOT NULL DEFAULT '#6366f1' COMMENT 'Technology-Indigo',
      `background_color` VARCHAR(7) NOT NULL DEFAULT '#0f172a' COMMENT 'Obsidian Backdrop',
      `glass_blur_level` INT UNSIGNED NOT NULL DEFAULT 16 COMMENT 'Blur level in px',
      `glass_opacity` FLOAT NOT NULL DEFAULT 0.4 COMMENT 'Opacity from 0.0 to 1.0',
      `font_family_url` VARCHAR(500) DEFAULT 'https://fonts.googleapis.com/css2?family=Outfit:wght@300;400;600;800&display=swap',
      `font_sans` VARCHAR(100) DEFAULT 'Outfit, sans-serif',
      `custom_css` TEXT DEFAULT NULL COMMENT 'Direct custom CSS variables injection',
      `is_active` TINYINT(1) NOT NULL DEFAULT 1,
      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    $pdo->exec($sql);
    echo "   ✓ `theme_configurations` table verified/created\n\n";
} catch (PDOException $e) {
    echo "   ✗ Failed: " . $e->getMessage() . "\n\n";
}

// 2. CREATE solar_provincial_coefficients
echo "3. Applying `solar_provincial_coefficients` schema...\n";
try {
    $sql = "CREATE TABLE IF NOT EXISTS `solar_provincial_coefficients` (
      `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      `province_name_th` VARCHAR(100) NOT NULL,
      `province_name_en` VARCHAR(100) NOT NULL,
      `peak_sun_hours` FLOAT NOT NULL DEFAULT 4.5 COMMENT 'Average Peak Sun Hours per day',
      `mea_pea_rate` FLOAT NOT NULL DEFAULT 4.2 COMMENT 'Baht per electric unit charge',
      `feed_in_tariff` FLOAT NOT NULL DEFAULT 2.2 COMMENT 'Grid feed-in tariff Baht per unit',
      `degradation_factor` FLOAT NOT NULL DEFAULT 0.005 COMMENT 'Panel degradation rate per year',
      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      UNIQUE INDEX `idx_province_en` (`province_name_en`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
    $pdo->exec($sql);
    echo "   ✓ `solar_provincial_coefficients` table verified/created\n\n";
} catch (PDOException $e) {
    echo "   ✗ Failed: " . $e->getMessage() . "\n\n";
}

// 3. CREATE payment_transactions
echo "4. Applying `payment_transactions` schema...\n";
try {
    // Check if orders table exists to avoid foreign key issues
    $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
    if (in_array('orders', $tables)) {
        $sql = "CREATE TABLE IF NOT EXISTS `payment_transactions` (
          `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          `order_id` INT UNSIGNED NOT NULL,
          `transaction_reference` VARCHAR(100) NOT NULL COMMENT 'Gateway transaction ID',
          `payment_method` VARCHAR(50) NOT NULL COMMENT 'Credit Card, PromptPay, Bank Transfer',
          `amount` DECIMAL(12, 2) NOT NULL,
          `currency` VARCHAR(3) NOT NULL DEFAULT 'THB',
          `payment_status` ENUM('pending', 'authorized', 'captured', 'failed', 'refunded') NOT NULL DEFAULT 'pending',
          `gateway_response` JSON DEFAULT NULL COMMENT 'Raw gateway webhook callback response JSON',
          `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
        $pdo->exec($sql);
        echo "   ✓ `payment_transactions` table verified/created\n\n";
    } else {
        echo "   ⚠ Skipped `payment_transactions`: Table `orders` does not exist yet\n\n";
    }
} catch (PDOException $e) {
    echo "   ✗ Failed: " . $e->getMessage() . "\n\n";
}

// 4. CREATE inquiry_proposals
echo "5. Applying `inquiry_proposals` schema...\n";
try {
    $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
    if (in_array('inquiries', $tables)) {
        $sql = "CREATE TABLE IF NOT EXISTS `inquiry_proposals` (
          `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          `inquiry_id` INT UNSIGNED NOT NULL,
          `solution_id` INT UNSIGNED DEFAULT NULL COMMENT 'Link to predefined package',
          `custom_proposal_title` VARCHAR(255) NOT NULL,
          `system_size_kw` FLOAT NOT NULL COMMENT 'Calculated capacity kw',
          `estimated_cost` DECIMAL(12, 2) NOT NULL,
          `payback_period_years` FLOAT NOT NULL,
          `pdf_datasheet_url` VARCHAR(500) DEFAULT NULL,
          `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (`inquiry_id`) REFERENCES `inquiries`(`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
        $pdo->exec($sql);
        echo "   ✓ `inquiry_proposals` table verified/created\n\n";
    } else {
        echo "   ⚠ Skipped `inquiry_proposals`: Table `inquiries` does not exist yet\n\n";
    }
} catch (PDOException $e) {
    echo "   ✗ Failed: " . $e->getMessage() . "\n\n";
}

// Helper function to check if column exists
function columnExists($pdo, $table, $column) {
    try {
        $stmt = $pdo->prepare("SHOW COLUMNS FROM `$table` LIKE :column");
        $stmt->execute(['column' => $column]);
        return (bool)$stmt->fetch();
    } catch (PDOException $e) {
        return false;
    }
}

// 5. ALTER existing tables
echo "6. Performing database migrations & column expansions...\n";

// Upgrade `admin_users`
if (in_array('admin_users', $tables)) {
    echo "   Upgrading `admin_users` table...\n";
    try {
        if (!columnExists($pdo, 'admin_users', 'mfa_secret')) {
            $pdo->exec("ALTER TABLE `admin_users` ADD COLUMN `mfa_secret` VARCHAR(100) DEFAULT NULL AFTER `password_hash`");
            echo "     + Added `mfa_secret` column\n";
        }
        if (!columnExists($pdo, 'admin_users', 'backup_codes')) {
            $pdo->exec("ALTER TABLE `admin_users` ADD COLUMN `backup_codes` JSON DEFAULT NULL AFTER `mfa_secret`");
            echo "     + Added `backup_codes` column\n";
        }
        if (!columnExists($pdo, 'admin_users', 'session_expires_at')) {
            $pdo->exec("ALTER TABLE `admin_users` ADD COLUMN `session_expires_at` DATETIME DEFAULT NULL AFTER `last_login`");
            echo "     + Added `session_expires_at` column\n";
        }
    } catch (PDOException $e) {
        echo "     ✗ Failed admin_users upgrade: " . $e->getMessage() . "\n";
    }
}

// Upgrade `categories`
if (in_array('categories', $tables)) {
    echo "   Upgrading `categories` table...\n";
    try {
        if (!columnExists($pdo, 'categories', 'name_en')) {
            $pdo->exec("ALTER TABLE `categories` ADD COLUMN `name_en` VARCHAR(100) DEFAULT NULL AFTER `name`");
            echo "     + Added `name_en` column\n";
        }
        if (!columnExists($pdo, 'categories', 'slug_en')) {
            $pdo->exec("ALTER TABLE `categories` ADD COLUMN `slug_en` VARCHAR(100) DEFAULT NULL AFTER `slug`");
            echo "     + Added `slug_en` column\n";
        }
    } catch (PDOException $e) {
        echo "     ✗ Failed categories upgrade: " . $e->getMessage() . "\n";
    }
}

// Upgrade `coupons`
if (in_array('coupons', $tables)) {
    echo "   Upgrading `coupons` table...\n";
    try {
        if (!columnExists($pdo, 'coupons', 'excluded_categories')) {
            $pdo->exec("ALTER TABLE `coupons` ADD COLUMN `excluded_categories` JSON DEFAULT NULL COMMENT 'Array of category IDs excluded from discount'");
            echo "     + Added `excluded_categories` column\n";
        }
        if (!columnExists($pdo, 'coupons', 'excluded_products')) {
            $pdo->exec("ALTER TABLE `coupons` ADD COLUMN `excluded_products` JSON DEFAULT NULL COMMENT 'Array of product IDs excluded from discount'");
            echo "     + Added `excluded_products` column\n";
        }
        if (!columnExists($pdo, 'coupons', 'max_discount_amount')) {
            $pdo->exec("ALTER TABLE `coupons` ADD COLUMN `max_discount_amount` DECIMAL(10,2) DEFAULT NULL COMMENT 'Cap discount value'");
            echo "     + Added `max_discount_amount` column\n";
        }
    } catch (PDOException $e) {
        echo "     ✗ Failed coupons upgrade: " . $e->getMessage() . "\n";
    }
}

// Upgrade `documents`
if (in_array('documents', $tables)) {
    echo "   Upgrading `documents` table...\n";
    try {
        if (!columnExists($pdo, 'documents', 'file_version')) {
            $pdo->exec("ALTER TABLE `documents` ADD COLUMN `file_version` VARCHAR(10) NOT NULL DEFAULT '1.0.0'");
            echo "     + Added `file_version` column\n";
        }
        if (!columnExists($pdo, 'documents', 'changelog_summary')) {
            $pdo->exec("ALTER TABLE `documents` ADD COLUMN `changelog_summary` TEXT DEFAULT NULL");
            echo "     + Added `changelog_summary` column\n";
        }
    } catch (PDOException $e) {
        echo "     ✗ Failed documents upgrade: " . $e->getMessage() . "\n";
    }
}

echo "\n✓ World-class database upgrades applied successfully!\n";
?>

Batal

⬅ Naik ke public_html 18 item

Nama	Tipe	Ukuran	Diubah	Aksi
🐘conn_mono.php	php	1.1 KB	2026-05-31 16:39	✏️ Edit 👁️ View
🐘deploy_solar_calculator.php	php	5.6 KB	2026-05-31 16:39	✏️ Edit 👁️ View
🐘fix_all_urls.php	php	1.8 KB	2026-05-31 16:39	✏️ Edit 👁️ View
🐘fix_inquiries.php	php	431 B	2026-05-31 16:39	✏️ Edit 👁️ View
🐘fix_urls.php	php	2 KB	2026-05-31 16:39	✏️ Edit 👁️ View
🐘import_services.php	php	1.8 KB	2026-05-31 16:39	✏️ Edit 👁️ View
🐘insert_about_page.php	php	4.7 KB	2026-05-31 16:39	✏️ Edit 👁️ View
🐘migrate_images.php	php	18.3 KB	2026-05-31 16:39	✏️ Edit 👁️ View
🐘run_about_setup.php	php	2.9 KB	2026-05-31 16:39	✏️ Edit 👁️ View
🐘run_banners_migration.php	php	6.5 KB	2026-05-31 16:39	✏️ Edit 👁️ View
🐘run_banners_setup.php	php	2.9 KB	2026-05-31 16:39	✏️ Edit 👁️ View
🐘run_map_locations_setup.php	php	2.5 KB	2026-05-31 16:39	✏️ Edit 👁️ View
🐘run_variations_migration.php	php	410 B	2026-05-31 16:39	✏️ Edit 👁️ View
🐘setup_production_admin.php	php	10.6 KB	2026-05-31 16:39	✏️ Edit 👁️ View
🐘setup_tables.php	php	2 KB	2026-05-31 16:39	✏️ Edit 👁️ View
🐘show_create.php	php	252 B	2026-05-31 16:39	✏️ Edit 👁️ View
🐘update_database.php	php	7 KB	2026-05-31 16:39	✏️ Edit 👁️ View
🐘update_database_world_class.php	php	10.3 KB	2026-05-31 16:39	✏️ Edit 👁️ View