Edit

Editing: /home/goacom/public_html/scratch_extract/db_patch.php

<?php
/**
 * Database Migration Script
 * Run this once to add support ticket tables and update settings columns.
 * Access via: yourdomain.com/db_patch.php
 */

// --- Database Configuration ---
$host = 'localhost';
$db   = 'tishanw1_melbets';
$user = 'tishanw1_melbets';
$pass = 'tishanw1_melbets';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

$results = [];

// 1. Alter settings table to add site_logo, site_meta_name, favicon columns
$migrations = [
    "1 - Add site_logo to settings" => "ALTER TABLE `settings` ADD COLUMN `site_logo` VARCHAR(255) DEFAULT '/favicon.png'",
    "2 - Add site_meta_name to settings" => "ALTER TABLE `settings` ADD COLUMN `site_meta_name` VARCHAR(255) DEFAULT 'MELBETE'",
    "3 - Add favicon to settings" => "ALTER TABLE `settings` ADD COLUMN `favicon` VARCHAR(255) DEFAULT '/favicon.png'",
    "3b - Add site_lang to settings" => "ALTER TABLE `settings` ADD COLUMN `site_lang` VARCHAR(10) NOT NULL DEFAULT 'en'",
    "4 - Create support_tickets table" => "CREATE TABLE IF NOT EXISTS `support_tickets` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `user_id` INT(11) NOT NULL,
        `subject` VARCHAR(255) NOT NULL,
        `status` ENUM('open','closed','answered','customer_reply') NOT NULL DEFAULT 'open',
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `user_id` (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci",
    "5 - Create support_messages table" => "CREATE TABLE IF NOT EXISTS `support_messages` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `ticket_id` INT(11) NOT NULL,
        `sender_type` ENUM('user','admin') NOT NULL,
        `message` TEXT NOT NULL,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `ticket_id` (`ticket_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci",
    "6 - Create promotions table" => "CREATE TABLE IF NOT EXISTS `promotions` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `title` VARCHAR(255) NOT NULL,
        `image_url` VARCHAR(500) DEFAULT '#',
        `category` VARCHAR(100) DEFAULT 'deposit',
        `bonus_code` VARCHAR(100) DEFAULT '',
        `offer_type` VARCHAR(100) DEFAULT 'Percentage',
        `bonus_value` VARCHAR(100) DEFAULT '',
        `valid_from` DATE DEFAULT NULL,
        `valid_to` DATE DEFAULT NULL,
        `apply_on` VARCHAR(255) DEFAULT '',
        `content` TEXT DEFAULT NULL,
        `is_active` TINYINT(1) DEFAULT 1,
        `sort_order` INT(11) DEFAULT 0,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci",
    "7 - Insert default promotion 1" => "INSERT INTO `promotions` (`title`, `image_url`, `category`, `bonus_code`, `offer_type`, `bonus_value`, `valid_from`, `valid_to`, `apply_on`, `is_active`, `sort_order`) SELECT 'ALL TIME BONUS', '#', 'deposit', 'HehshshHH', 'Percentage', '2%', '2025-12-18', '2028-08-16', 'Slot, Fishing, Bingo, Table, Live, Exchange, E-Game, Sports, Virtual, E-Sports', 1, 1 FROM (SELECT 1) AS tmp WHERE NOT EXISTS (SELECT 1 FROM `promotions` WHERE `title` = 'ALL TIME BONUS')",
    "8 - Insert default promotion 2" => "INSERT INTO `promotions` (`title`, `image_url`, `category`, `bonus_code`, `offer_type`, `bonus_value`, `valid_from`, `valid_to`, `apply_on`, `is_active`, `sort_order`) SELECT 'Welcome Bonus', '#', 'sign-up', 'Hehhshsh', 'Fixed', '2', '2026-05-10', '2026-11-28', 'Slot, Fishing, Bingo, Virtual, E-Sports, E-Game, Table, Live', 1, 2 FROM (SELECT 1) AS tmp WHERE NOT EXISTS (SELECT 1 FROM `promotions` WHERE `title` = 'Welcome Bonus')"
];

foreach ($migrations as $name => $sql) {
    try {
        $pdo->exec($sql);
        $results[] = ["status" => "success", "name" => $name, "msg" => "OK"];
    } catch (\PDOException $e) {
        // If column already exists, that's fine
        if (strpos($e->getMessage(), 'Duplicate column name') !== false || strpos($e->getMessage(), 'already exists') !== false) {
            $results[] = ["status" => "skipped", "name" => $name, "msg" => "Already exists, skipped."];
        } else {
            $results[] = ["status" => "error", "name" => $name, "msg" => $e->getMessage()];
        }
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>DB Patch - MELBETE</title>
    <link href="https://fonts.googleapis.com/css2?family=Outfit:wght@400;600;700&display=swap" rel="stylesheet">
    <style>
        * { box-sizing: border-box; margin: 0; padding: 0; }
        body { background: #0b0f19; font-family: 'Outfit', sans-serif; color: #e2e8f0; min-height: 100vh; display: flex; align-items: center; justify-content: center; padding: 20px; }
        .container { max-width: 680px; width: 100%; background: #111827; border: 1px solid #1f2937; border-radius: 24px; padding: 32px; }
        h1 { font-size: 20px; font-weight: 700; color: #fff; margin-bottom: 6px; }
        p.sub { font-size: 12px; color: #6b7280; margin-bottom: 24px; }
        .row { display: flex; align-items: center; gap: 12px; padding: 10px 14px; border-radius: 12px; margin-bottom: 8px; font-size: 13px; }
        .row.success { background: rgba(52,211,153,0.08); border: 1px solid rgba(52,211,153,0.2); }
        .row.skipped { background: rgba(251,191,36,0.08); border: 1px solid rgba(251,191,36,0.2); }
        .row.error { background: rgba(248,113,113,0.08); border: 1px solid rgba(248,113,113,0.2); }
        .badge { font-size: 10px; font-weight: 800; padding: 2px 8px; border-radius: 99px; text-transform: uppercase; white-space: nowrap; }
        .success .badge { background: rgba(52,211,153,0.15); color: #34d399; }
        .skipped .badge { background: rgba(251,191,36,0.15); color: #fbbf24; }
        .error .badge { background: rgba(248,113,113,0.15); color: #f87171; }
        .name { font-weight: 600; color: #d1d5db; flex: 1; }
        .msg { font-size: 11px; color: #6b7280; }
        .btn { display: inline-block; margin-top: 24px; background: #f06827; color: white; text-decoration: none; padding: 12px 24px; border-radius: 14px; font-weight: 700; font-size: 13px; }
    </style>
</head>
<body>
<div class="container">
    <h1>🔧 MELBETE — Database Migration</h1>
    <p class="sub">Running schema patches for settings customization and support tickets system.</p>

<?php foreach ($results as $r): ?>
        <div class="row <?php echo $r['status']; ?>">
            <span class="badge"><?php echo $r['status']; ?></span>
            <span class="name"><?php echo htmlspecialchars($r['name']); ?></span>
            <span class="msg"><?php echo htmlspecialchars($r['msg']); ?></span>
        </div>
    <?php endforeach; ?>

<a href="index.php" class="btn">← Back to Site</a>
    <a href="admin/settings.php" class="btn" style="background:#1f2937; border:1px solid #374151; margin-left:10px;">Admin Settings →</a>
</div>
</body>
</html>