<?php
/**
 * FLOWB0T NEXUS - Database Connection Manager
 * Singleton pattern with connection pooling
 *
 * @package Flowb0t\Core
 * @version 1.0.0
 */

namespace Flowb0t\Core;

class Database {
    private static ?Database $instance = null;
    private \PDO $pdo;
    private array $config;
    private int $queryCount = 0;
    private float $queryTime = 0;

    /**
     * Private constructor - use getInstance()
     */
    private function __construct() {
        $this->loadConfig();
        $this->connect();
    }

    /**
     * Load database configuration from environment
     */
    private function loadConfig(): void {
        // Load .env file if exists
        $envFile = dirname(__DIR__) . '/.env';
        if (file_exists($envFile)) {
            $lines = file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
            foreach ($lines as $line) {
                if (strpos($line, '#') === 0) continue;
                if (strpos($line, '=') !== false) {
                    list($key, $value) = explode('=', $line, 2);
                    $_ENV[trim($key)] = trim($value);
                    putenv(trim($key) . '=' . trim($value));
                }
            }
        }

        $this->config = [
            'host'     => getenv('DB_HOST') ?: 'localhost',
            'port'     => (int)(getenv('DB_PORT') ?: 3306),
            'database' => getenv('DB_NAME') ?: 'digupdog_FEED',
            'username' => getenv('DB_USER') ?: 'digupdog_FEEDadmin',
            'password' => getenv('DB_PASS') ?: 'Raimundinho1',
            'charset'  => 'utf8mb4'
        ];
    }

    /**
     * Establish database connection
     */
    private function connect(): void {
        $dsn = sprintf(
            "mysql:host=%s;port=%d;dbname=%s;charset=%s",
            $this->config['host'],
            $this->config['port'],
            $this->config['database'],
            $this->config['charset']
        );

        try {
            $this->pdo = new \PDO($dsn, $this->config['username'], $this->config['password'], [
                \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
                \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
                \PDO::ATTR_EMULATE_PREPARES   => false,
                \PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
                \PDO::ATTR_PERSISTENT         => true
            ]);
        } catch (\PDOException $e) {
            throw new \Exception("Database connection failed: " . $e->getMessage());
        }
    }

    /**
     * Get singleton instance
     */
    public static function getInstance(): Database {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Get raw PDO connection
     */
    public function getConnection(): \PDO {
        return $this->pdo;
    }

    /**
     * Execute a query with parameters
     */
    public function query(string $sql, array $params = []): \PDOStatement {
        $startTime = microtime(true);

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);

            $this->queryCount++;
            $this->queryTime += microtime(true) - $startTime;

            return $stmt;
        } catch (\PDOException $e) {
            throw new \Exception("Query failed: " . $e->getMessage() . " SQL: " . $sql);
        }
    }

    /**
     * Fetch single row
     */
    public function fetchOne(string $sql, array $params = []): ?array {
        $result = $this->query($sql, $params)->fetch();
        return $result ?: null;
    }

    /**
     * Fetch all rows
     */
    public function fetchAll(string $sql, array $params = []): array {
        return $this->query($sql, $params)->fetchAll();
    }

    /**
     * Fetch single column value
     */
    public function fetchColumn(string $sql, array $params = [], int $column = 0) {
        return $this->query($sql, $params)->fetchColumn($column);
    }

    /**
     * Insert a row
     */
    public function insert(string $table, array $data): int {
        $columns = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));

        $sql = "INSERT INTO {$table} ({$columns}) VALUES ({$placeholders})";
        $this->query($sql, array_values($data));

        return (int) $this->pdo->lastInsertId();
    }

    /**
     * Insert a row, ignore if duplicate
     */
    public function insertIgnore(string $table, array $data): int {
        $columns = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));

        $sql = "INSERT IGNORE INTO {$table} ({$columns}) VALUES ({$placeholders})";
        $this->query($sql, array_values($data));

        return (int) $this->pdo->lastInsertId();
    }

    /**
     * Insert or update on duplicate key
     */
    public function upsert(string $table, array $data, array $updateColumns = []): int {
        $columns = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));

        if (empty($updateColumns)) {
            $updateColumns = array_keys($data);
        }

        $updates = [];
        foreach ($updateColumns as $col) {
            $updates[] = "{$col} = VALUES({$col})";
        }
        $updateSql = implode(', ', $updates);

        $sql = "INSERT INTO {$table} ({$columns}) VALUES ({$placeholders}) ON DUPLICATE KEY UPDATE {$updateSql}";
        $this->query($sql, array_values($data));

        return (int) $this->pdo->lastInsertId();
    }

    /**
     * Batch insert multiple rows
     */
    public function insertBatch(string $table, array $rows): int {
        if (empty($rows)) return 0;

        $columns = array_keys($rows[0]);
        $columnsList = implode(', ', $columns);
        $placeholders = '(' . implode(', ', array_fill(0, count($columns), '?')) . ')';
        $allPlaceholders = implode(', ', array_fill(0, count($rows), $placeholders));

        $values = [];
        foreach ($rows as $row) {
            foreach ($columns as $col) {
                $values[] = $row[$col] ?? null;
            }
        }

        $sql = "INSERT INTO {$table} ({$columnsList}) VALUES {$allPlaceholders}";
        $this->query($sql, $values);

        return count($rows);
    }

    /**
     * Update rows
     */
    public function update(string $table, array $data, string $where, array $whereParams = []): int {
        $set = implode(' = ?, ', array_keys($data)) . ' = ?';

        $sql = "UPDATE {$table} SET {$set} WHERE {$where}";
        $stmt = $this->query($sql, array_merge(array_values($data), $whereParams));

        return $stmt->rowCount();
    }

    /**
     * Delete rows
     */
    public function delete(string $table, string $where, array $whereParams = []): int {
        $sql = "DELETE FROM {$table} WHERE {$where}";
        $stmt = $this->query($sql, $whereParams);
        return $stmt->rowCount();
    }

    /**
     * Count rows
     */
    public function count(string $table, string $where = '1=1', array $whereParams = []): int {
        $sql = "SELECT COUNT(*) FROM {$table} WHERE {$where}";
        return (int) $this->fetchColumn($sql, $whereParams);
    }

    /**
     * Check if row exists
     */
    public function exists(string $table, string $where, array $whereParams = []): bool {
        $sql = "SELECT 1 FROM {$table} WHERE {$where} LIMIT 1";
        return $this->fetchColumn($sql, $whereParams) !== false;
    }

    /**
     * Begin transaction
     */
    public function beginTransaction(): void {
        $this->pdo->beginTransaction();
    }

    /**
     * Commit transaction
     */
    public function commit(): void {
        $this->pdo->commit();
    }

    /**
     * Rollback transaction
     */
    public function rollback(): void {
        if ($this->pdo->inTransaction()) {
            $this->pdo->rollBack();
        }
    }

    /**
     * Check if in transaction
     */
    public function inTransaction(): bool {
        return $this->pdo->inTransaction();
    }

    /**
     * Get last insert ID
     */
    public function lastInsertId(): int {
        return (int) $this->pdo->lastInsertId();
    }

    /**
     * Quote a value
     */
    public function quote($value): string {
        return $this->pdo->quote($value);
    }

    /**
     * Get query statistics
     */
    public function getStats(): array {
        return [
            'query_count' => $this->queryCount,
            'query_time' => round($this->queryTime * 1000, 2) . ' ms',
            'avg_query_time' => $this->queryCount > 0
                ? round(($this->queryTime / $this->queryCount) * 1000, 2) . ' ms'
                : '0 ms'
        ];
    }

    /**
     * Test connection
     */
    public function testConnection(): bool {
        try {
            $this->pdo->query('SELECT 1');
            return true;
        } catch (\Exception $e) {
            return false;
        }
    }

    /**
     * Prevent cloning
     */
    private function __clone() {}

    /**
     * Prevent unserialization
     */
    public function __wakeup() {
        throw new \Exception("Cannot unserialize singleton");
    }
}
