<?php
/**
 * SonicPesa Database Sync Script
 * 
 * This script adds all new columns to your existing database tables.
 * Upload this to your shared hosting and run it once: yourdomain.com/sync_db.php
 * REMOVE THIS FILE AFTER RUNNING.
 */

require_once 'config.php';
require_once 'includes/db.php';

header('Content-Type: text/plain');
echo "SonicPesa Database Sync Started...\n\n";

function addColumnIfNotExists($pdo, $table, $column, $definition) {
    try {
        $stmt = $pdo->query("DESCRIBE `$table` `$column` ");
        $exists = $stmt->fetch();
        if (!$exists) {
            $pdo->exec("ALTER TABLE `$table` ADD COLUMN `$column` $definition");
            echo "[OK] Added column '$column' to table '$table'.\n";
        } else {
            echo "[SKIP] Column '$column' already exists in table '$table'.\n";
        }
    } catch (PDOException $e) {
        echo "[ERROR] Could not process '$table.$column': " . $e->getMessage() . "\n";
    }
}

// 1. Users Table
addColumnIfNotExists($pdo, 'users', 'profile_pic', "VARCHAR(500) DEFAULT NULL");

// 2. Site Settings Table
addColumnIfNotExists($pdo, 'site_settings', 'footer_copy', "VARCHAR(100) DEFAULT 'SonicPesa Dashboard. All Rights Reserved.'");
addColumnIfNotExists($pdo, 'site_settings', 'footer_made_by', "VARCHAR(100) DEFAULT 'SonicPesa Dev'");
addColumnIfNotExists($pdo, 'site_settings', 'allow_registration', "TINYINT(1) DEFAULT 1");
addColumnIfNotExists($pdo, 'site_settings', 'system_commission', "DECIMAL(5,2) DEFAULT 0.00");
addColumnIfNotExists($pdo, 'site_settings', 'min_withdrawal', "DECIMAL(10,2) DEFAULT 1000.00");

// 3. Withdrawals Table
addColumnIfNotExists($pdo, 'withdrawals', 'withdrawal_id', "VARCHAR(50) DEFAULT NULL");

// 4. Videos Table
addColumnIfNotExists($pdo, 'videos', 'position', "INT(11) DEFAULT 0");
addColumnIfNotExists($pdo, 'videos', 'fake_views', "INT(11) DEFAULT 0");

// 5. Cleanup Old Template Registration
try {
    $pdo->prepare("DELETE FROM templates WHERE slug = ?")->execute(['landing8']);
    echo "[OK] Cleaned up 'landing8' from extended templates (it is now built-in).\n";
} catch (PDOException $e) {
    echo "[ERROR] Could not clean up landing8: " . $e->getMessage() . "\n";
}

echo "\nDatabase sync complete. Please DELETE this file from your server now.";
?>

