<?php
/**
 * Database Class - Secure Database Handler
 * Version: 2.0
 * PHP: 7.4+
 */

declare(strict_types=1);

class Database extends mysqli {
    private static ?Database $instance = null;
    
    private function __construct() {
        parent::__construct(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        
        if ($this->connect_error) {
            $this->logError("Database connection failed: " . $this->connect_error);
            die("Database connection failed");
        }
        
        $this->set_charset(DB_CHARSET);
    }
    
    public static function getInstance(): Database {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    /**
     * Insert data into table
     */
    public function insert(string $table, array $data): bool {
        $columns = array_keys($data);
        $values = array_values($data);
        
        $placeholders = str_repeat('?,', count($values) - 1) . '?';
        $columnsList = '`' . implode('`,`', $columns) . '`';
        
        $sql = "INSERT INTO `{$table}` ({$columnsList}) VALUES ({$placeholders})";
        $stmt = $this->prepare($sql);
        
        if (!$stmt) {
            $this->logError("Prepare failed: " . $this->error);
            return false;
        }
        
        $types = $this->getTypes($values);
        $stmt->bind_param($types, ...$values);
        
        $result = $stmt->execute();
        $stmt->close();
        
        return $result;
    }
    
    /**
     * Update data in table
     */
    public function update(string $table, array $data, string $where, array $whereParams = []): bool {
        $sets = [];
        $values = [];
        
        foreach ($data as $column => $value) {
            $sets[] = "`{$column}` = ?";
            $values[] = $value;
        }
        
        $sql = "UPDATE `{$table}` SET " . implode(', ', $sets) . " WHERE {$where}";
        $stmt = $this->prepare($sql);
        
        if (!$stmt) {
            $this->logError("Prepare failed: " . $this->error);
            return false;
        }
        
        $allValues = array_merge($values, $whereParams);
        $types = $this->getTypes($allValues);
        $stmt->bind_param($types, ...$allValues);
        
        $result = $stmt->execute();
        $stmt->close();
        
        return $result;
    }
    
    /**
     * Fetch one row
     */
    public function fetchOne(string $sql, array $params = []): ?array {
        $stmt = $this->prepare($sql);
        
        if (!$stmt) {
            $this->logError("Prepare failed: " . $this->error);
            return null;
        }
        
        if (!empty($params)) {
            $types = $this->getTypes($params);
            $stmt->bind_param($types, ...$params);
        }
        
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        
        return $row ?: null;
    }
    
    /**
     * Fetch all rows
     */
    public function fetchAll(string $sql, array $params = []): array {
        $stmt = $this->prepare($sql);
        
        if (!$stmt) {
            $this->logError("Prepare failed: " . $this->error);
            return [];
        }
        
        if (!empty($params)) {
            $types = $this->getTypes($params);
            $stmt->bind_param($types, ...$params);
        }
        
        $stmt->execute();
        $result = $stmt->get_result();
        $rows = [];
        
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
        
        $stmt->close();
        return $rows;
    }
    
    /**
     * Delete from table
     */
    public function delete(string $table, string $where, array $params = []): bool {
        $sql = "DELETE FROM `{$table}` WHERE {$where}";
        $stmt = $this->prepare($sql);
        
        if (!$stmt) {
            $this->logError("Prepare failed: " . $this->error);
            return false;
        }
        
        if (!empty($params)) {
            $types = $this->getTypes($params);
            $stmt->bind_param($types, ...$params);
        }
        
        $result = $stmt->execute();
        $stmt->close();
        
        return $result;
    }
    
    /**
     * Get parameter types for bind_param
     */
    private function getTypes(array $values): string {
        $types = '';
        foreach ($values as $value) {
            if (is_int($value)) {
                $types .= 'i';
            } elseif (is_float($value)) {
                $types .= 'd';
            } else {
                $types .= 's';
            }
        }
        return $types;
    }
    
    /**
     * Log errors
     */
    private function logError(string $message): void {
        $logFile = LOGS_PATH . '/db_errors_' . date('Y-m-d') . '.log';
        $timestamp = date('Y-m-d H:i:s');
        file_put_contents($logFile, "[{$timestamp}] {$message}\n", FILE_APPEND);
    }
    
    /**
     * Initialize database tables
     */
    public function initTables(): bool {
        $tables = [
            "CREATE TABLE IF NOT EXISTS `groups` (
                `id` BIGINT NOT NULL PRIMARY KEY,
                `creator` BIGINT NULL,
                `vip` JSON NULL,
                `promote` JSON NULL,
                `ban` JSON NULL,
                `flood` JSON NULL,
                `locked` JSON NULL,
                `warn` JSON NULL,
                `silent` JSON NULL,
                `step` JSON NULL,
                `filter` JSON NULL,
                `installer` BIGINT NULL,
                `del` JSON NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX `idx_creator` (`creator`),
                INDEX `idx_installer` (`installer`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            
            "CREATE TABLE IF NOT EXISTS `members` (
                `id` BIGINT NOT NULL PRIMARY KEY,
                `username` VARCHAR(255) NULL,
                `first_name` VARCHAR(255) NULL,
                `language` VARCHAR(5) DEFAULT 'fa',
                `joined_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX `idx_username` (`username`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            
            "CREATE TABLE IF NOT EXISTS `sendall` (
                `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `send` JSON NULL,
                `now` INT NOT NULL DEFAULT 0,
                `total` INT NOT NULL DEFAULT 0,
                `status` ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX `idx_status` (`status`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            
            "CREATE TABLE IF NOT EXISTS `logs` (
                `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `type` VARCHAR(50) NOT NULL,
                `message` TEXT NOT NULL,
                `data` JSON NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX `idx_type` (`type`),
                INDEX `idx_created` (`created_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        ];
        
        foreach ($tables as $sql) {
            if (!$this->query($sql)) {
                $this->logError("Failed to create table: " . $this->error);
                return false;
            }
        }
        
        return true;
    }
}
