<?php

namespace App;

use PDO;
use PDOException;

class Database
{
    private static $instance = null;
    private $pdo;

    private function __construct()
    {
        $host = Config::get('DB_HOST', 'localhost');
        $dbname = Config::get('DB_NAME', 'dannvtuc_botajs');
        $username = Config::get('DB_USER', 'dannvtuc_botajs');
        $password = Config::get('DB_PASSWORD', '');
        $charset = Config::get('DB_CHARSET', 'utf8mb4');

        $dsn = "mysql:host={$host};dbname={$dbname};charset={$charset}";

        $options = [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        ];

        try {
            $this->pdo = new PDO($dsn, $username, $password, $options);
        } 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 connection
     */
    public function getConnection()
    {
        return $this->pdo;
    }

    /**
     * Store or update SSH key for a user
     */
    public function storeSSHKey($userId, $publicKey, $privateKey)
    {
        $sql = "INSERT INTO ssh_keys (user_id, public_key, private_key, created_at, updated_at)
                VALUES (:user_id, :public_key, :private_key, NOW(), NOW())
                ON DUPLICATE KEY UPDATE
                    public_key = VALUES(public_key),
                    private_key = VALUES(private_key),
                    updated_at = NOW()";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':user_id' => $userId,
            ':public_key' => $publicKey,
            ':private_key' => $privateKey
        ]);

        return true;
    }

    /**
     * Get SSH keys for a user
     */
    public function getSSHKey($userId)
    {
        $sql = "SELECT user_id, public_key, private_key, created_at, updated_at
                FROM ssh_keys
                WHERE user_id = :user_id
                LIMIT 1";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':user_id' => $userId]);

        return $stmt->fetch();
    }

    /**
     * Check if user has SSH key
     */
    public function hasSSHKey($userId)
    {
        $sql = "SELECT COUNT(*) as count FROM ssh_keys WHERE user_id = :user_id";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':user_id' => $userId]);

        $result = $stmt->fetch();
        return $result['count'] > 0;
    }

    /**
     * Delete SSH key for a user
     */
    public function deleteSSHKey($userId)
    {
        $sql = "DELETE FROM ssh_keys WHERE user_id = :user_id";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':user_id' => $userId]);

        return $stmt->rowCount() > 0;
    }
}
