<?php
// Database configuration for Togel Live Draw System
require_once __DIR__ . '/../site-identity.php';
// SiteConfig class for table suffix management
class SiteConfig {
    private static $siteMapping = [
        'mayortogel' => '_mayortogel',
        'yuki585' => '_yuki585', 
        'wajahtoto' => '_wajahtoto'
    ];
    
    // Tables that have site-specific versions
    private static $siteTables = [
        'market_schedules',
        'live_draw_results', 
        'prediction_history',
        'paito_data',
        'website_settings',
        'bukti_jackpot',
        'buku_mimpi',
        'banners'
    ];
    
    // Tables that are shared/central (no site suffix)
    private static $centralTables = [
        'admin_users',
        'master_logs',
        'site_configurations',
        'support_tickets',
        'support_responses'
    ];
    
    public static function getCurrentSite() {
        // Check for defined CURRENT_SITE constant first
        if (defined('CURRENT_SITE')) {
            return CURRENT_SITE;
        }
        
        // Domain-based detection for production
        $host = $_SERVER['HTTP_HOST'] ?? 'localhost';
        
        if (strpos($host, 'mayortogel') !== false) {
            return 'mayortogel';
        } elseif (strpos($host, 'yuki585') !== false) {
            return 'yuki585';
        } elseif (strpos($host, 'wajahtoto') !== false) {
            return 'wajahtoto';
        }
        
        // For localhost development, check REQUEST_URI
        if ($host === 'localhost' || strpos($host, 'localhost') !== false) {
            $uri = $_SERVER['REQUEST_URI'] ?? '';
            
            if (strpos($uri, '/mayortogel') !== false) {
                return 'mayortogel';
            } elseif (strpos($uri, '/wajahtoto') !== false) {
                return 'wajahtoto';
            } elseif (strpos($uri, '/yuki585') !== false) {
                return 'yuki585';
            }
        }
        
        // Default to mayortogel if no match
        return 'mayortogel';
    }
    
    public static function getTableSuffix() {
        $site = self::getCurrentSite();
        return self::$siteMapping[$site] ?? '_mayortogel';
    }
    
    public static function getTableName($baseTable) {
        // Check if this table should have site-specific version
        if (in_array($baseTable, self::$siteTables)) {
            return $baseTable . self::getTableSuffix();
        }
        
        // Check if this is a central table (no suffix)
        if (in_array($baseTable, self::$centralTables)) {
            return $baseTable;
        }
        
        // Default: add suffix for unknown tables (safer approach)
        return $baseTable . self::getTableSuffix();
    }
    
    public static function tableExists($tableName) {
        try {
            $host = defined('DB_HOST') ? DB_HOST : 'localhost';
            $dbname = defined('DB_NAME') ? DB_NAME : 'linkslot88_autoprediksikjmngpfjr941';
            $user = defined('DB_USER') ? DB_USER : 'linkslot88_autopred97841dellon9781a';
            $pass = defined('DB_PASS') ? DB_PASS : 'prediksiautomanjur9854ikjbnienfxx';

            $pdo = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8mb4", $user, $pass, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
            ]);

            // Beberapa versi MariaDB/MySQL tidak menerima placeholder (?) untuk SHOW TABLES.
            // Gunakan quote() untuk menghindari injection dan jalankan query langsung.
            $quoted = $pdo->quote($tableName);
            $stmt = $pdo->query("SHOW TABLES LIKE {$quoted}");
            $row = $stmt ? $stmt->fetch(PDO::FETCH_NUM) : false;
            return $row !== false;
        } catch (Exception $e) {
            return false;
        }
    }
}

class Database {
    private $host = 'localhost';
    private $dbname = 'linkslot88_autoprediksikjmngpfjr941';
    private $username = 'linkslot88_autopred97841dellon9781a';
    private $password = 'prediksiautomanjur9854ikjbnienfxx';
    private $pdo;
    
    public function __construct() {
        try {
            // Check if MySQL PDO driver is available
            if (!extension_loaded('pdo_mysql')) {
                throw new Exception("PDO MySQL extension not loaded. Please enable pdo_mysql in php.ini");
            }

            // Jika konstanta DB_* didefinisikan (mis. site-identity.php), gunakan itu
            $this->host = defined('DB_HOST') ? DB_HOST : $this->host;
            $this->dbname = defined('DB_NAME') ? DB_NAME : $this->dbname;
            $this->username = defined('DB_USER') ? DB_USER : $this->username;
            $this->password = defined('DB_PASS') ? DB_PASS : $this->password;

            $this->pdo = new PDO(
                "mysql:host={$this->host};dbname={$this->dbname};charset=utf8mb4",
                $this->username,
                $this->password,
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false
                ]
            );
            
            // Initialize tables
            $this->createBuktiJackpotTable();
            // $this->ensureWebsiteSettingsStructure(); // Temporarily disabled due to foreign key issues
            $this->ensureMarketSchedulesStructure();
        } catch (Exception $e) {
            error_log("Database connection error: " . $e->getMessage());
            throw $e;
        }
    }
    
    // Get PDO connection for direct queries
    public function getConnection() {
        return $this->pdo;
    }
    
    // Get current site ID for multi-tenant isolation - SIMPLE & STABLE
    public function getCurrentSiteId() {
        try {
            // Simple domain-based site detection - NO EXTERNAL DEPENDENCIES
            $host = $_SERVER['HTTP_HOST'] ?? 'localhost';
            
            // Map domains to site IDs (customize based on your sites)
            $siteMapping = [
                'localhost' => 1,
                'autoprediksi.local' => 1,
                'newsite.localhost' => 8,  // DOMAIN NEWSITE MENGGUNAKAN SITE_ID 8
                'wajahtoto.localhost' => 9,  // DOMAIN WAJAHTOTO MENGGUNAKAN SITE_ID 9
                'site1.com' => 1,
                'site2.com' => 2,
                'site3.com' => 3,
                // Add more mappings as needed
            ];
            
            return isset($siteMapping[$host]) ? $siteMapping[$host] : 1; // Default to site ID 1
        } catch (Exception $e) {
            error_log("Error getting current site ID: " . $e->getMessage());
            return 1; // Always return 1 as safe fallback
        }
    }
    
    // Live Draw Results Methods
    public function getLiveDrawResults($limit = 10) {
        try {
            $tableName = SiteConfig::getTableName('live_draw_results');
            $stmt = $this->pdo->prepare("
                SELECT * FROM $tableName 
                ORDER BY 
                    CASE 
                        WHEN updated_at IS NOT NULL THEN updated_at 
                        ELSE created_at 
                    END DESC,
                    draw_date DESC, 
                    id DESC 
                LIMIT ?
            ");
            $stmt->execute([$limit]);
            return $stmt->fetchAll();
        } catch (Exception $e) {
            error_log("Error fetching live draw results: " . $e->getMessage());
            return [];
        }
    }
    
    // Buku Mimpi Methods - SITE ISOLATED
    public function getBukuMimpi2D($siteId = null, $search = '', $page = 1, $limit = 50) {
        try {
            // ALWAYS get site ID for proper isolation
            if ($siteId === null) {
                $siteId = isset($_SESSION['current_site_id']) ? $_SESSION['current_site_id'] : $this->getCurrentSiteId();
            }
            
            // SAFETY CHECK: Force site_id = 1 if NULL or empty
            $siteId = $siteId ?? 1;
            if (empty($siteId)) $siteId = 1;
            
            $offset = ($page - 1) * $limit;
            $searchCondition = $search ? "AND (angka LIKE ? OR judul_mimpi LIKE ?)" : "";
            
            // ALWAYS use site isolation - get data for specific site only
            $sql = "SELECT * FROM buku_mimpi_2d WHERE site_id = ? $searchCondition ORDER BY angka LIMIT ? OFFSET ?";
            $stmt = $this->pdo->prepare($sql);
            
            if ($search) {
                $searchParam = "%$search%";
                $stmt->execute([$siteId, $searchParam, $searchParam, $limit, $offset]);
            } else {
                $stmt->execute([$siteId, $limit, $offset]);
            }
            
            return $stmt->fetchAll();
        } catch (Exception $e) {
            error_log("Error fetching buku mimpi 2D: " . $e->getMessage());
            return [];
        }
    }
    
    public function getBukuMimpi3D($siteId = null, $search = '', $page = 1, $limit = 50) {
        try {
            // ALWAYS get site ID for proper isolation
            if ($siteId === null) {
                $siteId = isset($_SESSION['current_site_id']) ? $_SESSION['current_site_id'] : $this->getCurrentSiteId();
            }
            
            // SAFETY CHECK: Force site_id = 1 if NULL or empty
            $siteId = $siteId ?? 1;
            if (empty($siteId)) $siteId = 1;
            
            $offset = ($page - 1) * $limit;
            $searchCondition = $search ? "AND (angka LIKE ? OR judul_mimpi LIKE ?)" : "";
            
            // ALWAYS use site isolation - get data for specific site only
            $sql = "SELECT * FROM buku_mimpi_3d WHERE site_id = ? $searchCondition ORDER BY angka LIMIT ? OFFSET ?";
            $stmt = $this->pdo->prepare($sql);
            
            if ($search) {
                $searchParam = "%$search%";
                $stmt->execute([$siteId, $searchParam, $searchParam, $limit, $offset]);
            } else {
                $stmt->execute([$siteId, $limit, $offset]);
            }
            
            return $stmt->fetchAll();
        } catch (Exception $e) {
            error_log("Error fetching buku mimpi 3D: " . $e->getMessage());
            return [];
        }
    }
    
    public function getBukuMimpi4D($siteId = null, $search = '', $page = 1, $limit = 50) {
        try {
            // ALWAYS get site ID for proper isolation
            if ($siteId === null) {
                $siteId = isset($_SESSION['current_site_id']) ? $_SESSION['current_site_id'] : $this->getCurrentSiteId();
            }
            
            // SAFETY CHECK: Force site_id = 1 if NULL or empty
            $siteId = $siteId ?? 1;
            if (empty($siteId)) $siteId = 1;
            
            $offset = ($page - 1) * $limit;
            $searchCondition = $search ? "AND (angka LIKE ? OR judul_mimpi LIKE ?)" : "";
            
            // ALWAYS use site isolation - get data for specific site only
            $sql = "SELECT * FROM buku_mimpi_4d WHERE site_id = ? $searchCondition ORDER BY angka LIMIT ? OFFSET ?";
            $stmt = $this->pdo->prepare($sql);
            
            if ($search) {
                $searchParam = "%$search%";
                $stmt->execute([$siteId, $searchParam, $searchParam, $limit, $offset]);
            } else {
                $stmt->execute([$siteId, $limit, $offset]);
            }
            
            return $stmt->fetchAll();
        } catch (Exception $e) {
            error_log("Error fetching buku mimpi 4D: " . $e->getMessage());
            return [];
        }
    }
    
    // Website Settings Methods - FIXED FOR COLUMN-BASED STRUCTURE  
    public function getWebsiteSettings($siteId = null) {
        try {
            // ALWAYS get site ID - no fallback without isolation
            if ($siteId === null) {
                $siteId = isset($_SESSION['current_site_id']) ? $_SESSION['current_site_id'] : $this->getCurrentSiteId();
            }
            
            // SAFETY CHECK: Force site_id = 1 jika NULL atau empty
            $siteId = $siteId ?? 1;
            if (empty($siteId)) $siteId = 1;
            
            // Get the full row for specific site - COLUMN-BASED
            $stmt = $this->pdo->prepare("SELECT * FROM website_settings WHERE site_id = ? LIMIT 1");
            $stmt->execute([$siteId]);
            $settings = $stmt->fetch(PDO::FETCH_ASSOC);
            
            // Return the associative array directly
            return $settings ?: [];
        } catch (Exception $e) {
            error_log("Error fetching website settings: " . $e->getMessage());
            // Return default settings on error
            return [
                'site_name' => 'Togel Live Draw',
                'site_description' => 'Live Draw Togel Online',
                'primary_color' => '#007bff',
                'secondary_color' => '#6c757d',
                'logo_path' => 'assets/images/logo.png'
            ];
        }
    }
    
    // Market Schedules Methods WITH SITE ISOLATION
    public function getMarketSchedules($activeOnly = true) {
        try {
            $sql = "SELECT * FROM " . SiteConfig::getTableName('market_schedules');
            if ($activeOnly) {
                $sql .= " WHERE is_active = 1";
            }
            $sql .= " ORDER BY open_time";
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute();
            return $stmt->fetchAll();
        } catch (Exception $e) {
            error_log("Error getting market schedules: " . $e->getMessage());
            return [];
        }
    }
    
    // Market Management Methods WITH SITE ISOLATION
    public function marketCodeExists($marketCode, $excludeId = null) {
        try {
            if ($excludeId !== null) {
                // When editing, exclude the current record from check
                $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM " . SiteConfig::getTableName('market_schedules') . " WHERE market_code = ? AND id != ?");
                $stmt->execute([$marketCode, $excludeId]);
            } else {
                // When adding new, check all records
                $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM " . SiteConfig::getTableName('market_schedules') . " WHERE market_code = ?");
                $stmt->execute([$marketCode]);
            }
            return $stmt->fetchColumn() > 0;
        } catch (Exception $e) {
            error_log("Error checking market code exists: " . $e->getMessage());
            return false;
        }
    }
    
    public function addMarketSchedule($marketName, $marketCode, $openTime, $closeTime, $logoPath = null, $officialSite = null) {
        try {
            // Use site-specific table
            $tableName = SiteConfig::getTableName('market_schedules');
            
            $stmt = $this->pdo->prepare("
                INSERT INTO $tableName (market_name, market_code, open_time, close_time, logo_path, official_site, is_active, created_at) 
                VALUES (?, ?, ?, ?, ?, ?, 1, NOW())
            ");
            
            return $stmt->execute([$marketName, $marketCode, $openTime, $closeTime, $logoPath, $officialSite]);
        } catch (Exception $e) {
            error_log("Error adding market schedule: " . $e->getMessage());
            return false;
        }
    }
    
    public function updateMarketSchedule($id, $marketName, $marketCode, $openTime, $closeTime, $logoPath = null, $officialSite = null) {
        try {
            if ($logoPath !== null) {
                $stmt = $this->pdo->prepare("
                    UPDATE " . SiteConfig::getTableName('market_schedules') . " 
                    SET market_name = ?, market_code = ?, open_time = ?, close_time = ?, logo_path = ?, official_site = ?, updated_at = NOW()
                    WHERE id = ?
                ");
                return $stmt->execute([$marketName, $marketCode, $openTime, $closeTime, $logoPath, $officialSite, $id]);
            } else {
                $stmt = $this->pdo->prepare("
                    UPDATE " . SiteConfig::getTableName('market_schedules') . " 
                    SET market_name = ?, market_code = ?, open_time = ?, close_time = ?, official_site = ?, updated_at = NOW()
                    WHERE id = ?
                ");
                return $stmt->execute([$marketName, $marketCode, $openTime, $closeTime, $officialSite, $id]);
            }
        } catch (Exception $e) {
            error_log("Error updating market schedule: " . $e->getMessage());
            return false;
        }
    }
    
    // Update market schedule with optional logo update - required by admin/markets.php
    public function updateMarketScheduleWithLogo($id, $marketName, $marketCode, $openTime, $closeTime, $logoPath, $officialSite = null, $updateLogo = false) {
        try {
            $tableName = SiteConfig::getTableName('market_schedules');
            
            if ($updateLogo && $logoPath !== null) {
                // Update with logo
                $stmt = $this->pdo->prepare("
                    UPDATE $tableName 
                    SET market_name = ?, market_code = ?, open_time = ?, close_time = ?, logo_path = ?, official_site = ?, updated_at = NOW()
                    WHERE id = ?
                ");
                return $stmt->execute([$marketName, $marketCode, $openTime, $closeTime, $logoPath, $officialSite, $id]);
            } else {
                // Update without logo
                $stmt = $this->pdo->prepare("
                    UPDATE $tableName 
                    SET market_name = ?, market_code = ?, open_time = ?, close_time = ?, official_site = ?, updated_at = NOW()
                    WHERE id = ?
                ");
                return $stmt->execute([$marketName, $marketCode, $openTime, $closeTime, $officialSite, $id]);
            }
        } catch (Exception $e) {
            error_log("Error updating market schedule with logo: " . $e->getMessage());
            return false;
        }
    }
    
    public function deleteMarketSchedule($id) {
        try {
            $stmt = $this->pdo->prepare("DELETE FROM " . SiteConfig::getTableName('market_schedules') . " WHERE id = ?");
            return $stmt->execute([$id]);
        } catch (Exception $e) {
            error_log("Error deleting market schedule: " . $e->getMessage());
            return false;
        }
    }
    
    public function getMarketScheduleById($id) {
        try {
            $stmt = $this->pdo->prepare("SELECT * FROM " . SiteConfig::getTableName('market_schedules') . " WHERE id = ?");
            $stmt->execute([$id]);
            return $stmt->fetch();
        } catch (Exception $e) {
            error_log("Error getting market schedule by ID: " . $e->getMessage());
            return false;
        }
    }
    
    // Today Results Methods
    public function getTodayResults($limit = 10) {
        try {
            $today = date('Y-m-d');
            $tableName = SiteConfig::getTableName('live_draw_results');
            $stmt = $this->pdo->prepare("
                SELECT * FROM $tableName 
                WHERE DATE(draw_date) = ? 
                ORDER BY draw_date DESC 
                LIMIT ?
            ");
            $stmt->execute([$today, $limit]);
            return $stmt->fetchAll();
        } catch (Exception $e) {
            error_log("Error fetching today results: " . $e->getMessage());
            return [];
        }
    }
    
    // Single Website Setting Method - FIXED FOR COLUMN-BASED STRUCTURE
    public function getWebsiteSetting($key, $siteId = null) {
        try {
            // ALWAYS get site ID - no fallback without isolation
            if ($siteId === null) {
                $siteId = isset($_SESSION['current_site_id']) ? $_SESSION['current_site_id'] : $this->getCurrentSiteId();
            }
            
            // SAFETY CHECK: Force site_id = 1 jika NULL atau empty
            $siteId = $siteId ?? 1;
            if (empty($siteId)) $siteId = 1;
            
            // ALWAYS use site isolation with proper prepared statement - Dynamic column query
            $stmt = $this->pdo->prepare("SELECT `{$key}` FROM website_settings WHERE site_id = ? LIMIT 1");
            $stmt->execute();
            
            $result = $stmt->fetch();
            return $result ? $result[$key] : null;
        } catch (Exception $e) {
            error_log("Error fetching website setting '$key': " . $e->getMessage());
            return null;
        }
    }
    
    // Update Website Setting Method - FIXED FOR COLUMN-BASED STRUCTURE
    public function updateWebsiteSetting($key, $value, $siteId = null) {
        try {
            // ALWAYS get site ID - no fallback without isolation
            if ($siteId === null) {
                $siteId = isset($_SESSION['current_site_id']) ? $_SESSION['current_site_id'] : $this->getCurrentSiteId();
            }
            
            // SAFETY CHECK: Force site_id = 1 jika NULL atau empty
            $siteId = $siteId ?? 1;
            if (empty($siteId)) $siteId = 1;
            
            // Check if record exists for this site_id
            $stmt = $this->pdo->prepare("SELECT id FROM website_settings WHERE site_id = ?");
            $stmt->execute();
            $exists = $stmt->fetch();
            
            if ($exists) {
                // Update existing record - Dynamic column update
                $stmt = $this->pdo->prepare("UPDATE website_settings SET `{$key}` = ?, updated_at = NOW() WHERE site_id = ?");
                $result = $stmt->execute([$value, $siteId]);
            } else {
                // Create new record with default values for this site
                $this->createDefaultSettings($siteId);
                // Then update the specific column
                $stmt = $this->pdo->prepare("UPDATE website_settings SET `{$key}` = ?, updated_at = NOW() WHERE site_id = ?");
                $result = $stmt->execute([$value, $siteId]);
            }
            
            return $result;
        } catch (Exception $e) {
            error_log("Error updating website setting '$key': " . $e->getMessage());
            return false;
        }
    }
    
    // Bulk update website settings WITH SITE ISOLATION
    public function updateWebsiteSettings($settings = []) {
        try {
            if (empty($settings) || !is_array($settings)) {
                return false;
            }
            
            $success = true;
            foreach ($settings as $key => $value) {
                if (!$this->updateWebsiteSetting($key, $value, $siteId)) {
                    $success = false;
                    error_log("Failed to update setting: $key");
                }
            }
            
            return $success;
        } catch (Exception $e) {
            error_log("Error bulk updating website settings: " . $e->getMessage());
            return false;
        }
    }
    
    // Bukti Jackpot Methods WITH SITE ISOLATION
    public function getBuktiJackpotCount($filter = null) {
        try {
            $sql = "SELECT COUNT(*) FROM bukti_jackpot WHERE site_id = ?";
            $params = [$siteId];
            
            if ($filter === true || $filter === 'active') {
                $sql .= " AND is_active = 1";
            } else if ($filter === 'inactive') {
                $sql .= " AND is_active = 0";
            } else if ($filter === 'month') {
                $sql .= " AND MONTH(created_at) = MONTH(CURRENT_DATE()) AND YEAR(created_at) = YEAR(CURRENT_DATE())";
            }
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchColumn();
        } catch (Exception $e) {
            error_log("Error counting bukti jackpot: " . $e->getMessage());
            return 0;
        }
    }
    
    public function getBuktiJackpotPaginated($limit = 10, $offset = 0) {
        try {
            $stmt = $this->pdo->prepare("
                SELECT * FROM bukti_jackpot 
                WHERE site_id = ?
                ORDER BY created_at DESC 
                LIMIT ? OFFSET ?
            ");
            $stmt->execute([$siteId, $limit, $offset]);
            return $stmt->fetchAll();
        } catch (Exception $e) {
            error_log("Error fetching bukti jackpot paginated: " . $e->getMessage());
            return [];
        }
    }
    
    public function getBuktiJackpotById($id) {
        try {
            $stmt = $this->pdo->prepare("SELECT * FROM bukti_jackpot WHERE id = ? AND site_id = ?");
            $stmt->execute([$id]);
            return $stmt->fetch();
        } catch (Exception $e) {
            error_log("Error fetching bukti jackpot by ID: " . $e->getMessage());
            return null;
        }
    }
    
    // Method for bukti-jackpot.php (public page) WITH SITE ISOLATION
    public function getBuktiJackpot($limit = 10, $offset = 0, $activeOnly = false) {
        try {
            $sql = "SELECT * FROM bukti_jackpot WHERE site_id = ?";
            $params = [$siteId];
            
            if ($activeOnly) {
                $sql .= " AND is_active = 1";
            }
            
            $sql .= " ORDER BY created_at DESC";
            
            if ($limit > 0) {
                $sql .= " LIMIT ? OFFSET ?";
                $params[] = $limit;
                $params[] = $offset;
            }
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
        } catch (Exception $e) {
            error_log("Error fetching bukti jackpot: " . $e->getMessage());
            return [];
        }
    }
    
    public function addBuktiJackpot($data) {
        try {
            $stmt = $this->pdo->prepare("
                INSERT INTO bukti_jackpot (site_id, title, jackpot_date, amount, description, image_path, image_type, is_active, created_at) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW())
            ");
            return $stmt->execute([
                $siteId,
                $data['title'] ?? '',
                $data['jackpot_date'] ?? date('Y-m-d'),
                $data['amount'] ?? 0,
                $data['description'] ?? '',
                $data['image_path'] ?? '',
                $data['image_type'] ?? 'upload',
                $data['is_active'] ?? 1
            ]);
        } catch (Exception $e) {
            error_log("Error adding bukti jackpot: " . $e->getMessage());
            return false;
        }
    }
    
    public function updateBuktiJackpot($id, $data) {
        try {
            $stmt = $this->pdo->prepare("
                UPDATE bukti_jackpot 
                SET title = ?, jackpot_date = ?, amount = ?, description = ?, image_path = ?, image_type = ?, is_active = ?, updated_at = NOW()
                WHERE id = ? AND site_id = ?
            ");
            return $stmt->execute([
                $data['title'] ?? '',
                $data['jackpot_date'] ?? date('Y-m-d'),
                $data['amount'] ?? 0,
                $data['description'] ?? '',
                $data['image_path'] ?? '',
                $data['image_type'] ?? 'upload',
                $data['is_active'] ?? 1,
                $id,
                $siteId
            ]);
        } catch (Exception $e) {
            error_log("Error updating bukti jackpot: " . $e->getMessage());
            return false;
        }
    }
    
    public function deleteBuktiJackpot($id) {
        try {
            $stmt = $this->pdo->prepare("DELETE FROM bukti_jackpot WHERE id = ? AND site_id = ?");
            return $stmt->execute([$id]);
        } catch (Exception $e) {
            error_log("Error deleting bukti jackpot: " . $e->getMessage());
            return false;
        }
    }
    
    // Create bukti_jackpot table if not exists WITH SITE ISOLATION
    public function createBuktiJackpotTable() {
        try {
            $sql = "CREATE TABLE IF NOT EXISTS bukti_jackpot (
                id INT PRIMARY KEY AUTO_INCREMENT,
                site_id INT NOT NULL DEFAULT 1,
                title VARCHAR(255) NOT NULL,
                jackpot_date DATE NOT NULL,
                amount VARCHAR(100) NOT NULL,
                description TEXT,
                image_path VARCHAR(500),
                image_type ENUM('upload', 'url') DEFAULT 'upload',
                is_active TINYINT(1) DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_site_id (site_id),
                INDEX idx_site_active (site_id, is_active)
            )";
            
            $this->pdo->exec($sql);
            
            // Check if we need to add new columns to existing table
            try {
                $this->pdo->exec("ALTER TABLE bukti_jackpot ADD COLUMN site_id INT NOT NULL DEFAULT 1");
                $this->pdo->exec("ALTER TABLE bukti_jackpot ADD COLUMN title VARCHAR(255) DEFAULT ''");
                $this->pdo->exec("ALTER TABLE bukti_jackpot ADD COLUMN description TEXT");
                $this->pdo->exec("ALTER TABLE bukti_jackpot ADD COLUMN image_type ENUM('upload', 'url') DEFAULT 'upload'");
                $this->pdo->exec("ALTER TABLE bukti_jackpot ADD COLUMN is_active TINYINT(1) DEFAULT 1");
                $this->pdo->exec("ALTER TABLE bukti_jackpot CHANGE win_date jackpot_date DATE");
                $this->pdo->exec("ALTER TABLE bukti_jackpot CHANGE win_amount amount VARCHAR(100)");
                $this->pdo->exec("ALTER TABLE bukti_jackpot DROP COLUMN winner_name");
                $this->pdo->exec("ALTER TABLE bukti_jackpot DROP COLUMN market_code");
                $this->pdo->exec("ALTER TABLE bukti_jackpot ADD INDEX idx_site_id (site_id)");
                $this->pdo->exec("ALTER TABLE bukti_jackpot ADD INDEX idx_site_active (site_id, is_active)");
            } catch (Exception $e) {
                // Columns might already exist or not exist, continue
            }
            
            return true;
        } catch (Exception $e) {
            error_log("Error creating bukti_jackpot table: " . $e->getMessage());
            return false;
        }
    }
    
    // Ensure website_settings table has proper structure for banners
    public function ensureWebsiteSettingsStructure() {
        try {
            // Check if table exists and get its structure
            $stmt = $this->pdo->prepare("SHOW TABLES LIKE 'website_settings'");
            $stmt->execute();
            $tableExists = $stmt->fetch();
            
            if (!$tableExists) {
                // Create table with proper structure INCLUDING site_id for isolation
                $sql = "CREATE TABLE website_settings (
                    id INT PRIMARY KEY AUTO_INCREMENT,
                    site_id INT DEFAULT NULL,
                    site_name VARCHAR(255) DEFAULT 'Togel Live Draw',
                    site_description TEXT DEFAULT 'Live Draw Togel Online',
                    primary_color VARCHAR(10) DEFAULT '#007bff',
                    secondary_color VARCHAR(10) DEFAULT '#6c757d',
                    logo_path VARCHAR(500) DEFAULT 'assets/images/logo.png',
                    banner_1_image VARCHAR(500) DEFAULT NULL,
                    banner_1_link VARCHAR(500) DEFAULT NULL,
                    banner_2_image VARCHAR(500) DEFAULT NULL,
                    banner_2_link VARCHAR(500) DEFAULT NULL,
                    banner_3_image VARCHAR(500) DEFAULT NULL,
                    banner_3_link VARCHAR(500) DEFAULT NULL,
                    banner_4_image VARCHAR(500) DEFAULT NULL,
                    banner_4_link VARCHAR(500) DEFAULT NULL,
                    banner_5_image VARCHAR(500) DEFAULT NULL,
                    banner_5_link VARCHAR(500) DEFAULT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE
                )";
                $this->pdo->exec($sql);
                
            } else {
                // Add site_id column if it doesn't exist (for isolation)
                try {
                    $this->pdo->exec("ALTER TABLE website_settings ADD COLUMN site_id INT DEFAULT NULL");
                    $this->pdo->exec("ALTER TABLE website_settings ADD FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE");
                } catch (Exception $e) {
                    // Column probably already exists
                }
                
                // Add banner columns if they don't exist
                $bannerColumns = [
                    'banner_1_image', 'banner_1_link',
                    'banner_2_image', 'banner_2_link', 
                    'banner_3_image', 'banner_3_link',
                    'banner_4_image', 'banner_4_link',
                    'banner_5_image', 'banner_5_link'
                ];
                
                foreach ($bannerColumns as $column) {
                    try {
                        $this->pdo->exec("ALTER TABLE website_settings ADD COLUMN $column VARCHAR(500) DEFAULT NULL");
                    } catch (Exception $e) {
                        // Column probably already exists, continue
                    }
                }
            }
            
            return true;
        } catch (Exception $e) {
            error_log("Error ensuring website_settings structure: " . $e->getMessage());
            return false;
        }
    }
    
    // Ensure market_schedules table has proper structure WITH SITE ISOLATION
    public function ensureMarketSchedulesStructure() {
        try {
            // Create market_schedules table if not exists
            $sql = "CREATE TABLE IF NOT EXISTS market_schedules (
                id INT PRIMARY KEY AUTO_INCREMENT,
                site_id INT NOT NULL DEFAULT 1,
                market_name VARCHAR(255) NOT NULL,
                market_code VARCHAR(50) NOT NULL,
                open_time TIME NOT NULL,
                close_time TIME NOT NULL,
                logo_path VARCHAR(500) DEFAULT NULL,
                official_site VARCHAR(500) DEFAULT NULL,
                is_active TINYINT(1) DEFAULT 1,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY unique_market_code_per_site (site_id, market_code)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
            
            $this->pdo->exec($sql);
            
            // Check if site_id column exists, add if not
            $stmt = $this->pdo->query("SHOW COLUMNS FROM market_schedules LIKE 'site_id'");
            if ($stmt->rowCount() == 0) {
                $this->pdo->exec("ALTER TABLE market_schedules ADD COLUMN site_id INT NOT NULL DEFAULT 1 AFTER id");
                
                // Update existing records to have site_id = 1
                $this->pdo->exec("UPDATE market_schedules SET site_id = 1 WHERE site_id IS NULL OR site_id = 0");
                
                // Add unique constraint for market_code per site
                try {
                    $this->pdo->exec("ALTER TABLE market_schedules ADD UNIQUE KEY unique_market_code_per_site (site_id, market_code)");
                } catch (Exception $e) {
                    // Constraint might already exist, continue
                }
            }
            
            return true;
        } catch (Exception $e) {
            error_log("Error ensuring market_schedules structure: " . $e->getMessage());
            return false;
        }
    }

    // History pagination methods
    public function getHistoryCount($marketCode, $searchNumber = '') {
        try {
            $tableName = SiteConfig::getTableName('live_draw_results');
            
            $sql = "SELECT COUNT(*) FROM $tableName WHERE market = ?";
            $params = [$marketCode];
            
            if (!empty($searchNumber)) {
                $sql .= " AND result_4d LIKE ?";
                $params[] = "%$searchNumber%";
            }
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchColumn();
        } catch (PDOException $e) {
            error_log("Error getting history count: " . $e->getMessage());
            return 0;
        }
    }
    
    public function getHistoryResults($marketCode, $limit = 35, $offset = 0, $searchNumber = '') {
        try {
            $tableName = SiteConfig::getTableName('live_draw_results');
            
            $sql = "SELECT * FROM $tableName WHERE market = ?";
            $params = [$marketCode];
            
            if (!empty($searchNumber)) {
                $sql .= " AND result_4d LIKE ?";
                $params[] = "%$searchNumber%";
            }
            
            $sql .= " ORDER BY draw_date DESC LIMIT ? OFFSET ?";
            $params[] = $limit;
            $params[] = $offset;
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            error_log("Error getting history results: " . $e->getMessage());
            return [];
        }
    }
}
?>