<?php
/**
 * Database Connection and Query Builder
 * PDO-based database abstraction layer
 */

namespace Core;

class DB {
    private static $instance = null;
    private $pdo;
    
    private function __construct() {
        try {
            $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=" . DB_CHARSET;
            $this->pdo = new \PDO($dsn, 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) {
            throw new \Exception("Database connection failed: " . $e->getMessage());
        }
    }
    
    /**
     * Get singleton instance
     */
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        
        return self::$instance;
    }
    
    /**
     * Get PDO instance
     */
    public function getPdo() {
        return $this->pdo;
    }
    
    /**
     * Execute a query
     */
    public function query($sql, $params = []) {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt;
        } catch (\PDOException $e) {
            $this->logError("Query failed: " . $e->getMessage() . " SQL: " . $sql);
            throw $e;
        }
    }
    
    /**
     * Fetch single row
     */
    public function fetchOne($sql, $params = []) {
        $stmt = $this->query($sql, $params);
        return $stmt->fetch();
    }
    
    /**
     * Fetch all rows
     */
    public function fetchAll($sql, $params = []) {
        $stmt = $this->query($sql, $params);
        return $stmt->fetchAll();
    }
    
    /**
     * Insert record
     */
    public function insert($table, $data) {
        $fields = array_keys($data);
        $placeholders = ':' . implode(', :', $fields);
        
        $sql = "INSERT INTO {$table} (" . implode(', ', $fields) . ") VALUES ({$placeholders})";
        
        $this->query($sql, $data);
        
        return $this->pdo->lastInsertId();
    }
    
    /**
     * Update record
     */
    public function update($table, $data, $where, $whereParams = []) {
        $fields = [];
        foreach (array_keys($data) as $field) {
            $fields[] = "{$field} = :{$field}";
        }
        
        $sql = "UPDATE {$table} SET " . implode(', ', $fields) . " WHERE {$where}";
        
        $params = array_merge($data, $whereParams);
        
        $stmt = $this->query($sql, $params);
        
        return $stmt->rowCount();
    }
    
    /**
     * Delete record
     */
    public function delete($table, $where, $whereParams = []) {
        $sql = "DELETE FROM {$table} WHERE {$where}";
        
        $stmt = $this->query($sql, $whereParams);
        
        return $stmt->rowCount();
    }
    
    /**
     * Begin transaction
     */
    public function beginTransaction() {
        return $this->pdo->beginTransaction();
    }
    
    /**
     * Commit transaction
     */
    public function commit() {
        return $this->pdo->commit();
    }
    
    
    /**
     * Rollback transaction
     */
    public function rollback() {
        return $this->pdo->rollback();
    }
    
    /**
     * Check if in transaction
     */
    public function inTransaction() {
        return $this->pdo->inTransaction();
    }
    
    /**
     * Get last insert ID
     */
    public function lastInsertId() {
        return $this->pdo->lastInsertId();
    }
    
    /**
     * Count records
     */
    public function count($table, $where = '', $params = []) {
        $sql = "SELECT COUNT(*) FROM {$table}";
        
        if (!empty($where)) {
            $sql .= " WHERE {$where}";
        }
        
        $stmt = $this->query($sql, $params);
        return $stmt->fetchColumn();
    }
    
    /**
     * Check if record exists
     */
    public function exists($table, $where, $params = []) {
        return $this->count($table, $where, $params) > 0;
    }
    
    /**
     * Calculate distance between two coordinates using Haversine formula
     */
    public function calculateDistance($lat1, $lng1, $lat2, $lng2) {
        $sql = "SELECT (
            6371 * acos(
                cos(radians(?)) * 
                cos(radians(?)) * 
                cos(radians(?) - radians(?)) + 
                sin(radians(?)) * 
                sin(radians(?))
            )
        ) AS distance";
        
        $stmt = $this->query($sql, [$lat1, $lat2, $lng2, $lng1, $lat1, $lat2]);
        $result = $stmt->fetch();
        
        return round($result['distance'], 2);
    }
    
    /**
     * Find nearby records with distance
     */
    public function findNearby($table, $lat, $lng, $radius = 50, $where = '', $params = []) {
        $distanceFormula = "
            (6371 * acos(
                cos(radians({$lat})) * 
                cos(radians(lat)) * 
                cos(radians(lng) - radians({$lng})) + 
                sin(radians({$lat})) * 
                sin(radians(lat))
            ))";
        
        $sql = "SELECT *, {$distanceFormula} AS distance 
                FROM {$table} 
                WHERE {$distanceFormula} <= {$radius}";
        
        if (!empty($where)) {
            $sql .= " AND {$where}";
        }
        
        $sql .= " ORDER BY distance ASC";
        
        return $this->fetchAll($sql, $params);
    }
    
    /**
     * Log database errors
     */
    private function logError($message) {
        $logFile = LOG_PATH . 'database.log';
        $timestamp = date('Y-m-d H:i:s');
        $logMessage = "[{$timestamp}] ERROR: {$message}" . PHP_EOL;
        
        file_put_contents($logFile, $logMessage, FILE_APPEND | LOCK_EX);
    }
    
    /**
     * Get database information
     */
    public function getInfo() {
        $version = $this->fetchOne("SELECT VERSION() as version");
        $status = $this->fetchAll("SHOW STATUS LIKE 'Threads_connected'");
        
        return [
            'version' => $version['version'],
            'connected_threads' => $status[0]['Value'] ?? 0
        ];
    }
}
?>
