mirror of
https://github.com/myronblair/tomtomgames-app
synced 2026-06-30 17:49:57 -05:00
542 lines
25 KiB
PHP
542 lines
25 KiB
PHP
<?php
|
|
require_once __DIR__ . '/config.php';
|
|
|
|
class Database {
|
|
private static $instance = null;
|
|
private $pdo;
|
|
private function __construct() {
|
|
try {
|
|
$this->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,
|
|
PDO::ATTR_EMULATE_PREPARES => false,
|
|
]
|
|
);
|
|
} catch (PDOException $e) {
|
|
http_response_code(500);
|
|
die(json_encode(['success'=>false,'error'=>'Database connection failed.']));
|
|
}
|
|
}
|
|
public static function getInstance(): self {
|
|
if (!self::$instance) self::$instance = new self();
|
|
return self::$instance;
|
|
}
|
|
public function getConnection(): PDO { return $this->pdo; }
|
|
}
|
|
|
|
function db(): PDO { return Database::getInstance()->getConnection(); }
|
|
|
|
// Check if a column exists — MySQL 5.x compatible
|
|
function colExists(PDO $pdo, string $table, string $col): bool {
|
|
return (bool)$pdo->query("SHOW COLUMNS FROM `$table` LIKE '$col'")->fetch();
|
|
}
|
|
|
|
function initDB(): void {
|
|
$pdo = db();
|
|
|
|
// Each table in its own exec() — PDO only runs one statement per call
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
password VARCHAR(255) NOT NULL,
|
|
alias VARCHAR(100) NOT NULL,
|
|
email VARCHAR(150) UNIQUE,
|
|
email_verified TINYINT(1) DEFAULT 0,
|
|
tokens DECIMAL(10,2) DEFAULT 0,
|
|
is_admin TINYINT(1) DEFAULT 0,
|
|
status ENUM('active','suspended') DEFAULT 'active',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
last_login DATETIME
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS pending_registrations (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL,
|
|
password VARCHAR(255) NOT NULL,
|
|
alias VARCHAR(100) NOT NULL,
|
|
email VARCHAR(150) NOT NULL,
|
|
token VARCHAR(64) UNIQUE NOT NULL,
|
|
referred_by INT DEFAULT NULL,
|
|
expires_at DATETIME NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS token_purchases (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
tokens INT NOT NULL,
|
|
amount_cents INT NOT NULL,
|
|
payment_method VARCHAR(20) DEFAULT 'card',
|
|
square_payment_id VARCHAR(255),
|
|
platform_id VARCHAR(50),
|
|
game_alias VARCHAR(100),
|
|
player_name VARCHAR(100),
|
|
billing_name VARCHAR(160),
|
|
billing_address VARCHAR(200),
|
|
billing_city VARCHAR(80),
|
|
billing_state VARCHAR(2),
|
|
billing_zip VARCHAR(10),
|
|
billing_email VARCHAR(150),
|
|
is_custom TINYINT(1) DEFAULT 0,
|
|
failure_reason TEXT,
|
|
card_brand VARCHAR(30),
|
|
card_last4 VARCHAR(4),
|
|
receipt_url VARCHAR(512),
|
|
status ENUM('pending','completed','failed') DEFAULT 'pending',
|
|
admin_note TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS cashout_requests (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
platform_id VARCHAR(50) NOT NULL,
|
|
alias VARCHAR(100) NOT NULL,
|
|
tokens DECIMAL(10,2) NOT NULL,
|
|
status ENUM('pending','approved','rejected') DEFAULT 'pending',
|
|
admin_note TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
resolved_at DATETIME,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS platform_accounts (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
platform_slug VARCHAR(50) NOT NULL,
|
|
requested_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
status ENUM('pending','approved','denied','deleted') DEFAULT 'pending',
|
|
platform_username VARCHAR(100),
|
|
platform_password VARCHAR(200),
|
|
admin_note VARCHAR(300),
|
|
resolved_at DATETIME,
|
|
admin_id INT,
|
|
UNIQUE KEY uq_user_platform (user_id, platform_slug),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS admin_payout_settings (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
method_key VARCHAR(50) UNIQUE NOT NULL,
|
|
label VARCHAR(100) NOT NULL,
|
|
method_type ENUM('manual','square_gift_card') DEFAULT 'manual',
|
|
is_enabled TINYINT(1) DEFAULT 1,
|
|
handle VARCHAR(200),
|
|
instructions TEXT,
|
|
sort_order INT DEFAULT 0
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS cashout_transactions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
cashout_id INT NOT NULL,
|
|
admin_id INT NOT NULL,
|
|
payout_method VARCHAR(50) NOT NULL,
|
|
payout_type ENUM('manual','square_gift_card') DEFAULT 'manual',
|
|
amount_cents INT NOT NULL,
|
|
square_txn_id VARCHAR(200),
|
|
gift_card_gan VARCHAR(100),
|
|
gift_card_balance INT,
|
|
note TEXT,
|
|
status ENUM('pending','completed','failed') DEFAULT 'pending',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (cashout_id) REFERENCES cashout_requests(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS activity_log (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT,
|
|
admin_id INT,
|
|
action VARCHAR(120) NOT NULL,
|
|
category VARCHAR(40) DEFAULT 'general',
|
|
entity_type VARCHAR(40),
|
|
entity_id INT,
|
|
detail TEXT,
|
|
old_value TEXT,
|
|
new_value TEXT,
|
|
ip VARCHAR(45),
|
|
user_agent VARCHAR(300),
|
|
page VARCHAR(200),
|
|
session_id VARCHAR(64),
|
|
severity ENUM('info','warning','critical') DEFAULT 'info',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_created (created_at),
|
|
INDEX idx_user (user_id),
|
|
INDEX idx_admin (admin_id),
|
|
INDEX idx_category (category),
|
|
INDEX idx_severity (severity)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS broadcasts (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
admin_id INT NOT NULL,
|
|
subject VARCHAR(200) NOT NULL,
|
|
message TEXT NOT NULL,
|
|
target ENUM('all','verified','unverified','admins') DEFAULT 'all',
|
|
sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (admin_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS broadcast_reads (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
broadcast_id INT NOT NULL,
|
|
user_id INT NOT NULL,
|
|
read_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uq_br (broadcast_id, user_id),
|
|
FOREIGN KEY (broadcast_id) REFERENCES broadcasts(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS broadcast_replies (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
broadcast_id INT NOT NULL,
|
|
user_id INT NOT NULL,
|
|
message TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (broadcast_id) REFERENCES broadcasts(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS cashout_method_types (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
slug VARCHAR(50) UNIQUE NOT NULL,
|
|
label VARCHAR(100) NOT NULL,
|
|
icon VARCHAR(10) DEFAULT '💰',
|
|
description VARCHAR(200),
|
|
is_active TINYINT(1) DEFAULT 1,
|
|
sort_order INT DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS payout_methods (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
method_type VARCHAR(50) NOT NULL,
|
|
label VARCHAR(100) NOT NULL,
|
|
account_handle VARCHAR(200) NOT NULL,
|
|
is_default TINYINT(1) DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
// Add payout fields to cashout_requests if not present
|
|
try {
|
|
$cols = array_column($pdo->query("SHOW COLUMNS FROM cashout_requests")->fetchAll(), 'Field');
|
|
if (!in_array('payout_method_type', $cols)) {
|
|
$pdo->exec("ALTER TABLE cashout_requests ADD COLUMN payout_method_type VARCHAR(50) AFTER alias");
|
|
$pdo->exec("ALTER TABLE cashout_requests ADD COLUMN payout_handle VARCHAR(200) AFTER payout_method_type");
|
|
}
|
|
if (!in_array('sent_note', $cols)) {
|
|
$pdo->exec("ALTER TABLE cashout_requests ADD COLUMN sent_note TEXT AFTER admin_note");
|
|
}
|
|
$pdo->exec("ALTER TABLE cashout_requests MODIFY COLUMN status ENUM('pending','approved','sent','rejected','deleted') DEFAULT 'pending'");
|
|
} catch (Exception $e) { /* ignore — columns may already exist */ }
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS saved_billing (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT UNIQUE NOT NULL,
|
|
first_name VARCHAR(80),
|
|
last_name VARCHAR(80),
|
|
email VARCHAR(150),
|
|
address VARCHAR(200),
|
|
city VARCHAR(80),
|
|
state VARCHAR(2),
|
|
zip VARCHAR(10),
|
|
card_brand VARCHAR(30),
|
|
card_last4 VARCHAR(4),
|
|
card_exp_month VARCHAR(2),
|
|
card_exp_year VARCHAR(4),
|
|
sq_card_id VARCHAR(255),
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS platforms (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
slug VARCHAR(50) UNIQUE NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
player_url VARCHAR(500) NOT NULL,
|
|
console_url VARCHAR(500),
|
|
color VARCHAR(20) DEFAULT '#f0c040',
|
|
icon_path VARCHAR(200),
|
|
is_active TINYINT(1) DEFAULT 1,
|
|
sort_order INT DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS payment_settings (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
method_key VARCHAR(50) UNIQUE NOT NULL,
|
|
label VARCHAR(100) NOT NULL,
|
|
is_enabled TINYINT(1) DEFAULT 1,
|
|
handle VARCHAR(200),
|
|
instructions TEXT,
|
|
sort_order INT DEFAULT 0,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS game_aliases (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
platform_slug VARCHAR(50) NOT NULL,
|
|
alias VARCHAR(100) NOT NULL,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uq_user_platform (user_id, platform_slug),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
|
|
|
|
// Track whether user has seen the post-verify onboarding
|
|
try {
|
|
if (!colExists($pdo,'users','onboarding_done')) {
|
|
$pdo->exec("ALTER TABLE users ADD COLUMN onboarding_done TINYINT(1) DEFAULT 0");
|
|
}
|
|
} catch (Exception $e) {}
|
|
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS chat_messages (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
sender ENUM('user','admin') NOT NULL,
|
|
message TEXT NOT NULL,
|
|
is_read TINYINT(1) DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
// Add missing columns to existing tables — MySQL 5.x compatible (no IF NOT EXISTS)
|
|
$addCols = [
|
|
['token_purchases', 'billing_name', "VARCHAR(160)", 'player_name'],
|
|
['token_purchases', 'billing_address', "VARCHAR(200)", 'billing_name'],
|
|
['token_purchases', 'billing_city', "VARCHAR(80)", 'billing_address'],
|
|
['token_purchases', 'billing_state', "VARCHAR(2)", 'billing_city'],
|
|
['token_purchases', 'billing_zip', "VARCHAR(10)", 'billing_state'],
|
|
['token_purchases', 'billing_email', "VARCHAR(150)", 'billing_zip'],
|
|
['token_purchases', 'is_custom', "TINYINT(1) DEFAULT 0", 'billing_email'],
|
|
['token_purchases', 'failure_reason', "TEXT", 'is_custom'],
|
|
['token_purchases', 'card_brand', "VARCHAR(30)", 'failure_reason'],
|
|
['token_purchases', 'card_last4', "VARCHAR(4)", 'card_brand'],
|
|
['token_purchases', 'receipt_url', "VARCHAR(512)", 'card_last4'],
|
|
['token_purchases', 'admin_note', "TEXT", 'status'],
|
|
['users', 'email_verified', "TINYINT(1) DEFAULT 0", 'email'],
|
|
];
|
|
|
|
foreach ($addCols as [$tbl, $col, $def, $after]) {
|
|
if (!colExists($pdo, $tbl, $col)) {
|
|
try {
|
|
$pdo->exec("ALTER TABLE `$tbl` ADD COLUMN `$col` $def AFTER `$after`");
|
|
} catch (Exception $e) { /* ignore — concurrent init */ }
|
|
}
|
|
}
|
|
}
|
|
|
|
try { initDB(); } catch (Exception $e) { /* already initialised */ }
|
|
|
|
|
|
// Seed admin_payout_settings if empty
|
|
try {
|
|
if (db()->query("SELECT COUNT(*) FROM admin_payout_settings")->fetchColumn() == 0) {
|
|
$seeds = [
|
|
['venmo', 'Venmo', 'manual', 1, '@your-venmo', 'Send via Venmo app'],
|
|
['cashapp', 'Cash App', 'manual', 1, '$yourcashtag', 'Send via Cash App'],
|
|
['zelle', 'Zelle', 'manual', 1, 'your@email', 'Send via Zelle'],
|
|
['chime', 'Chime', 'manual', 1, '', 'Send via Chime'],
|
|
['square_gift', 'Square Gift Card','square_gift_card',1, '', 'Instant Square gift card — player redeems anywhere Square is accepted'],
|
|
];
|
|
$st = db()->prepare("INSERT IGNORE INTO admin_payout_settings (method_key,label,method_type,is_enabled,handle,instructions,sort_order) VALUES (?,?,?,?,?,?,?)");
|
|
foreach ($seeds as $i => $s) { $st->execute([$s[0],$s[1],$s[2],$s[3],$s[4],$s[5],$i]); }
|
|
}
|
|
} catch(Exception $e) {}
|
|
|
|
// ─── REFERRAL TABLES ──────────────────────────────────────
|
|
try {
|
|
db()->exec("CREATE TABLE IF NOT EXISTS referral_tiers (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
min_referrals INT NOT NULL DEFAULT 1,
|
|
tokens_per_ref DECIMAL(10,2) NOT NULL DEFAULT 10,
|
|
bonus_tokens DECIMAL(10,2) NOT NULL DEFAULT 0,
|
|
description VARCHAR(300),
|
|
is_active TINYINT(1) DEFAULT 1,
|
|
sort_order INT DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
db()->exec("CREATE TABLE IF NOT EXISTS referrals (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
referrer_id INT NOT NULL,
|
|
referred_id INT NOT NULL UNIQUE,
|
|
tier_id INT,
|
|
status ENUM('pending','verified','denied','deleted') DEFAULT 'pending',
|
|
tokens_awarded DECIMAL(10,2) DEFAULT 0,
|
|
admin_id INT,
|
|
admin_note VARCHAR(300),
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
resolved_at DATETIME,
|
|
FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
|
|
db()->exec("CREATE TABLE IF NOT EXISTS referral_social_shares (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
platform VARCHAR(50) NOT NULL,
|
|
bonus_tokens DECIMAL(10,2) DEFAULT 0,
|
|
status ENUM('pending','approved','denied') DEFAULT 'pending',
|
|
admin_id INT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
resolved_at DATETIME,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
} catch(Exception $e) {}
|
|
|
|
// Seed default referral tiers
|
|
try {
|
|
if (db()->query("SELECT COUNT(*) FROM referral_tiers")->fetchColumn() == 0) {
|
|
$seeds = [
|
|
['Bronze Referrer', 1, 5, 0, 'Earn 5 tokens for each verified referral', 1, 0],
|
|
['Silver Referrer', 5, 8, 25, 'Earn 8 tokens per referral + 25 bonus at 5 referrals', 1, 1],
|
|
['Gold Referrer', 10, 10, 100,'Earn 10 tokens per referral + 100 bonus at 10 referrals',1, 2],
|
|
['Elite Referrer', 25, 15, 250,'Earn 15 tokens per referral + 250 bonus at 25 referrals',1, 3],
|
|
];
|
|
$st = db()->prepare("INSERT INTO referral_tiers (name,min_referrals,tokens_per_ref,bonus_tokens,description,is_active,sort_order) VALUES (?,?,?,?,?,?,?)");
|
|
foreach ($seeds as $s) $st->execute($s);
|
|
}
|
|
} catch(Exception $e) {}
|
|
|
|
// Add referral_code to users if missing
|
|
try {
|
|
$cols = array_column(db()->query("SHOW COLUMNS FROM users")->fetchAll(), 'Field');
|
|
if (!in_array('referral_code', $cols)) {
|
|
db()->exec("ALTER TABLE users ADD COLUMN referral_code VARCHAR(20) UNIQUE");
|
|
// Generate codes for existing users
|
|
$users = db()->query("SELECT id FROM users WHERE referral_code IS NULL")->fetchAll();
|
|
$upd = db()->prepare("UPDATE users SET referral_code=? WHERE id=?");
|
|
foreach ($users as $u) {
|
|
$code = strtoupper(substr(md5($u['id'].uniqid()),0,8));
|
|
$upd->execute([$code, $u['id']]);
|
|
}
|
|
}
|
|
} catch(Exception $e) {}
|
|
|
|
// Add referred_by to users if missing
|
|
try {
|
|
$cols = array_column(db()->query("SHOW COLUMNS FROM users")->fetchAll(), 'Field');
|
|
if (!in_array('referred_by', $cols)) {
|
|
db()->exec("ALTER TABLE users ADD COLUMN referred_by INT DEFAULT NULL");
|
|
}
|
|
} catch(Exception $e) {}
|
|
|
|
// Add card payment_settings row if missing
|
|
try {
|
|
$cardCount = db()->query("SELECT COUNT(*) FROM payment_settings WHERE method_key='card'")->fetchColumn();
|
|
if ($cardCount == 0) {
|
|
db()->exec("INSERT IGNORE INTO payment_settings (method_key,label,handle,instructions,is_enabled,sort_order) VALUES ('card','Credit / Debit Card','','Processed via Square',1,-1)");
|
|
}
|
|
} catch(Exception $e) {}
|
|
|
|
// Expand activity_log if columns missing
|
|
try {
|
|
$alCols = array_column(db()->query("SHOW COLUMNS FROM activity_log")->fetchAll(), 'Field');
|
|
$alAdd = [
|
|
'category' => "ALTER TABLE activity_log ADD COLUMN category VARCHAR(40) DEFAULT 'general' AFTER action",
|
|
'old_value' => "ALTER TABLE activity_log ADD COLUMN old_value TEXT AFTER detail",
|
|
'new_value' => "ALTER TABLE activity_log ADD COLUMN new_value TEXT AFTER old_value",
|
|
'user_agent' => "ALTER TABLE activity_log ADD COLUMN user_agent VARCHAR(300) AFTER ip",
|
|
'page' => "ALTER TABLE activity_log ADD COLUMN page VARCHAR(200) AFTER user_agent",
|
|
'session_id' => "ALTER TABLE activity_log ADD COLUMN session_id VARCHAR(64) AFTER page",
|
|
'severity' => "ALTER TABLE activity_log ADD COLUMN severity ENUM('info','warning','critical') DEFAULT 'info' AFTER session_id",
|
|
];
|
|
foreach ($alAdd as $col => $sql) {
|
|
if (!in_array($col, $alCols)) db()->exec($sql);
|
|
}
|
|
// Change detail to TEXT if it's VARCHAR
|
|
$detailType = '';
|
|
foreach (db()->query("SHOW COLUMNS FROM activity_log")->fetchAll() as $col) {
|
|
if ($col['Field'] === 'detail') $detailType = $col['Type'];
|
|
}
|
|
if (stripos($detailType, 'varchar') !== false) {
|
|
db()->exec("ALTER TABLE activity_log MODIFY COLUMN detail TEXT");
|
|
}
|
|
} catch(Exception $e) {}
|
|
|
|
// Add platform_onboarding_done to users if not exists
|
|
try {
|
|
$cols = array_column(db()->query("SHOW COLUMNS FROM users")->fetchAll(), 'Field');
|
|
if (!in_array('platform_onboarding_done', $cols)) {
|
|
db()->exec("ALTER TABLE users ADD COLUMN platform_onboarding_done TINYINT(1) DEFAULT 0");
|
|
}
|
|
} catch(Exception $e){}
|
|
|
|
// App version table
|
|
try {
|
|
db()->exec("CREATE TABLE IF NOT EXISTS app_version (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
version VARCHAR(20) NOT NULL,
|
|
notes TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
|
|
// Seed initial version if empty
|
|
if (db()->query("SELECT COUNT(*) FROM app_version")->fetchColumn() == 0) {
|
|
db()->exec("INSERT INTO app_version (version, notes) VALUES ('1.0.0', 'Initial release')");
|
|
}
|
|
} catch(Exception $e) {}
|
|
|
|
// Seed cashout_method_types if empty
|
|
try {
|
|
$cmtCount = db()->query("SELECT COUNT(*) FROM cashout_method_types")->fetchColumn();
|
|
if ($cmtCount == 0) {
|
|
$types = [
|
|
['venmo', 'Venmo', '💙', 'Send via Venmo username', 1, 0],
|
|
['cashapp', 'Cash App', '💚', 'Send via Cash App $cashtag', 1, 1],
|
|
['zelle', 'Zelle', '💜', 'Send via Zelle phone or email', 1, 2],
|
|
['chime', 'Chime', '🟢', 'Send via Chime account', 1, 3],
|
|
['bank', 'Bank Transfer', '🏦', 'Direct bank transfer', 1, 4],
|
|
['other', 'Other', '💰', 'Other payment method', 1, 5],
|
|
];
|
|
$stmt = db()->prepare("INSERT IGNORE INTO cashout_method_types (slug,label,icon,description,is_active,sort_order) VALUES (?,?,?,?,?,?)");
|
|
foreach ($types as $t) $stmt->execute($t);
|
|
}
|
|
} catch (Exception $e) { /* ignore */ }
|
|
|
|
// Seed payment_settings from config if empty
|
|
try {
|
|
$pmtCount = db()->query("SELECT COUNT(*) FROM payment_settings")->fetchColumn();
|
|
if ($pmtCount == 0) {
|
|
$methods = [
|
|
['card', 'Credit / Debit Card', '', 'Processed via Square', 1, -1],
|
|
['venmo', 'Venmo', PAY_VENMO, 'Send payment via Venmo', 1, 0],
|
|
['chime', 'Chime', PAY_CHIME, 'Send payment via Chime', 1, 1],
|
|
['cashapp', 'Cash App', PAY_CASHAPP, 'Send payment via Cash App',1, 2],
|
|
['zelle', 'Zelle', PAY_ZELLE, 'Send payment via Zelle', 1, 3],
|
|
];
|
|
$stmt = db()->prepare("INSERT IGNORE INTO payment_settings (method_key,label,handle,instructions,is_enabled,sort_order) VALUES (?,?,?,?,1,?)");
|
|
foreach ($methods as $m) {
|
|
$stmt->execute([$m[0],$m[1],$m[2],$m[3],$m[5]]);
|
|
}
|
|
}
|
|
} catch (Exception $e) { /* ignore */ }
|
|
|
|
// Seed platforms table from config if empty
|
|
try {
|
|
$count = db()->query("SELECT COUNT(*) FROM platforms")->fetchColumn();
|
|
if ($count == 0) {
|
|
$platforms = json_decode(PLATFORMS, true);
|
|
$stmt = db()->prepare("INSERT IGNORE INTO platforms (slug,name,player_url,color,sort_order) VALUES (?,?,?,?,?)");
|
|
foreach ($platforms as $i => $p) {
|
|
$stmt->execute([$p['id'], $p['name'], $p['url'], $p['color'], $i]);
|
|
}
|
|
}
|
|
} catch (Exception $e) { /* ignore */ }
|
|
|
|
// Always ensure admin accounts are email-verified
|
|
try {
|
|
if (colExists(db(), 'users', 'email_verified')) {
|
|
db()->exec("UPDATE users SET email_verified=1 WHERE is_admin=1 AND email_verified=0");
|
|
}
|
|
} catch (Exception $e) { /* ignore */ }
|