<?php

namespace SiteZen\Telemetry\Connectors;

use SiteZen\Telemetry\Bootstrap\helper;
use SiteZen\Telemetry\Connectors\DatabaseAdapters\DbAdapter;

class Database
{

    var $host;
    var $username;
    var $password;
    var $database;
    var $port;
    var $socket;

    private $th_time_long_running;
    private $th_time_sleeping;
    private $th_high_resource_query_length;
    private $th_join_count;

    /** @var DbAdapter|null */
    private $adapter = null;
    /**
     * @var array|mixed
     */
    private $parameters = [];

    public function __construct($db_config, $parameters = [])
    {
        $this->host = $db_config['host'] ?? '';
        $this->username = $db_config['username'] ?? '';
        $this->password = $db_config['password'] ?? '';
        $this->database = $db_config['database'] ?? '';
        $this->port = (int)($db_config['port'] ?? 0);
        $this->socket = $db_config['socket'] ?? '';

        // adapter must be provided from bootstrap (outside core)
        $this->adapter = $db_config['db_adapter'] ?? null;
        if (!is_object($this->adapter) || !method_exists($this->adapter, 'query')) {
            throw new \Exception('Database adapter not provided. Assign an adapter via $config[\'db_adapter\']');
        }

        $this->parameters = $parameters;

        // Thresholds
        $this->th_time_long_running = $parameters['long_running'] ?? 6; // Long running query threshold (in seconds)
        $this->th_time_sleeping = $parameters['sleeping'] ?? 75; // Threshold for sleeping connections
        $this->th_high_resource_query_length = $parameters['query_length'] ?? 1000; // Threshold for high resource query length
        $this->th_join_count = $parameters['join_count'] ?? 3; // Max number of joins in a query

    }

    private function connect()
    {
        $this->adapter->connect();
    }

    public function query($query)
    {
        $this->connect();
        return $this->adapter->query($query);
    }

    public function connections(): array
    {
        $variables = $this->variables(['max_connections']);
        $status = $this->status(['Max_used_connections', 'Max_used_connections_time', 'Threads_connected', 'Locked_connects', 'Connections']);

        return [
            'available' => helper::arr_get($variables, 'max_connections'),
            'used' => helper::arr_get($status, 'Threads_connected'),
            'max_used' => helper::arr_get($status, 'Max_used_connections'),
            'max_used_time' => helper::arr_get($status, 'Max_used_connections_time'),
            'total_count' => helper::arr_get($status, 'Connections')
        ];
    }

    private function _show($type = 'VARIABLES', $constrains = []): array
    {
        if (!in_array($type, ['VARIABLES', 'STATUS'])) {
            throw new \Exception("Invalid SHOW type: Only 'VARIABLES' and 'STATUS' are supported.");
        }

        $this->connect();

        $variableList = null;
        if (!empty($constrains)) {
            $variableList = implode(', ', array_map(function ($name) {
                return "'" . $name . "'";
            }, $constrains));
        }

        $results = [];

        try {
            $statusResult = $this->adapter->query("SHOW $type" . (!empty($constrains) ? " WHERE Variable_name IN ({$variableList});" : ''));
            while ($row = $statusResult->fetch_assoc()) {
                if (isset($row['Variable_name']) && array_key_exists('Value', $row)) {
                $results[$row['Variable_name']] = $row['Value'];
            }
            }
            $statusResult->free();
        } catch (\Exception $e) {
            throw new \Exception("Error executing SHOW $type: " . (($this->adapter && method_exists($this->adapter, 'error')) ? $this->adapter->error() : $e->getMessage()));
        }
        return $results;
    }

    public function variables($variables = []): array
    {
        return $this->_show('VARIABLES', $variables);
    }

    public function status($variables = []): array
    {
        return $this->_show('STATUS', $variables);
    }

    public function insight(): array
    {
        $variables = $this->variables();

        return [
            'server' => [
                'database' => $this->database,
                'storage' => $this->usage(),
                'version' => helper::arr_get($variables, 'version'),
                'version_comment' => helper::arr_get($variables, 'version_comment'),
                'version_compile_machine' => helper::arr_get($variables, 'version_compile_machine'),
                'version_compile_os' => helper::arr_get($variables, 'version_compile_os')
            ],
            'variables' => $this->variables(),
            'status' => $this->status()
        ];
    }

    public function usage(): array
    {
        $this->connect();

        try {
            $dbSizeResult = $this->adapter->query("SELECT SUM(data_length + index_length) AS 'size' FROM information_schema.TABLES WHERE table_schema = '{$this->database}'");
            $dbSizeRow = $dbSizeResult->fetch_assoc();
            $dbSizeResult->free();
            $result = $dbSizeRow && isset($dbSizeRow['size']) ? round($dbSizeRow['size'] / 1024 / 1024, 2) : 0.0;
        } catch (\Exception $e) {
            throw new \Exception("Error executing usage: " . (($this->adapter && method_exists($this->adapter, 'error')) ? $this->adapter->error() : $e->getMessage()));
        }
        return ['used' => $result];
    }

    public function largestTables($limit = 10): array
    {
        $this->connect();
        try {
            $largestTablesResult = $this->adapter->query("SELECT TABLE_NAME, TABLE_ROWS AS 'number_of_rows', ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'size_mb', ROUND((data_length) / 1024 / 1024, 2) AS 'data_size_mb', ROUND((index_length) / 1024 / 1024, 2) AS 'index_size_mb' FROM information_schema.TABLES WHERE table_schema = '{$this->database}' ORDER BY size_mb DESC LIMIT {$limit}");
            $result = [];
            while ($row = $largestTablesResult->fetch_assoc()) {
                $result[$row['TABLE_NAME']] = [
                    'rows' => helper::arr_get($row, 'number_of_rows'),
                    'total' => helper::arr_get($row, 'size_mb'),
                    'data' => helper::arr_get($row, 'data_size_mb'),
                    'index' => helper::arr_get($row, 'index_size_mb')
                ];
            }
            $largestTablesResult->free();
        } catch (\Exception $e) {
            throw new \Exception("Error executing largestTables: " . (($this->adapter && method_exists($this->adapter, 'error')) ? $this->adapter->error() : $e->getMessage()));
        }
        return $result;
    }

    public function analyzeProcessList(): array
    {
        $this->connect();
        $issues = [
            'long_running_queries' => [],
            'sleeping_connections' => [],
            'locked_tables' => [],
            'high_resource_usage' => []
        ];

        try {
            $result = $this->adapter->query("SHOW PROCESSLIST");
            while ($process = $result->fetch_assoc()) {
                // Detect long-running queries
                if (($process['Command'] ?? '') === 'Query' && ($process['Time'] ?? 0) > $this->th_time_long_running) {
                    $issues['long_running_queries'][] = $process;
                }

                // Detect locked tables
                if (($process['State'] ?? '') === 'Locked') {
                    $issues['locked_tables'][] = $process;
                }

                // Detect sleeping connections
                if (($process['Command'] ?? '') === 'Sleep' && ($process['Time'] ?? 0) > $this->th_time_sleeping) {
                    $issues['sleeping_connections'][] = $process;
                }

                // Detect high resource usage (e.g., long or complex queries)
                if (($process['Command'] ?? '') === 'Query' && !empty($process['Info'] ?? '')) {
                    // Long queries
                    if (strlen($process['Info']) > $this->th_high_resource_query_length) {
                        $issues['high_resource_usage'][] = $process;
                    }
                    // Queries with many joins
                    if (substr_count($process['Info'], 'JOIN') > $this->th_join_count) {
                        $issues['high_resource_usage'][] = $process;
                    }
                }
            }
            $result->free();
        } catch (\Exception $e) {
            throw new \Exception("Error executing SHOW PROCESSLIST: " . (($this->adapter && method_exists($this->adapter, 'error')) ? $this->adapter->error() : $e->getMessage()));
        }

        return $issues;
    }

    public function processList(): array
    {
        $this->connect();
        $processlist = [];

        try {
            $result = $this->adapter->query("SHOW PROCESSLIST");
            while ($process = $result->fetch_assoc()) {
                $processlist[] = $process;
            }
            $result->free();
        } catch (\Exception $e) {
            throw new \Exception("Error executing SHOW PROCESSLIST: " . (($this->adapter && method_exists($this->adapter, 'error')) ? $this->adapter->error() : $e->getMessage()));
        }

        return $processlist;

    }

    public function schema()
    {
        $this->connect();
        $schema = [];

        $query = "
    SELECT 
        c.TABLE_NAME, 
        t.TABLE_COLLATION,  -- Get the collation of the table
        c.COLUMN_NAME, 
        c.DATA_TYPE, 
        c.COLUMN_TYPE, 
        c.COLLATION_NAME, 
        c.IS_NULLABLE, 
        c.COLUMN_DEFAULT, 
        c.COLUMN_KEY, 
        c.EXTRA 
    FROM information_schema.COLUMNS c
    JOIN information_schema.TABLES t 
        ON c.TABLE_SCHEMA = t.TABLE_SCHEMA 
        AND c.TABLE_NAME = t.TABLE_NAME
    WHERE c.TABLE_SCHEMA = '{$this->database}'
";

        try {
            $result = $this->adapter->query($query);
            while ($column = $result->fetch_assoc()) {
                $table = $column['TABLE_NAME'];
                unset($column['TABLE_NAME']); // Remove redundant table name from each column entry
                $column_name = $column['COLUMN_NAME'];
                unset($column['COLUMN_NAME']); // Remove redundant table name from each column entry
                $schema[$table][$column_name] = $column;
            }
            $result->free();
        } catch (\Exception $e) {
            throw new \Exception("Error executing schema: " . (($this->adapter && method_exists($this->adapter, 'error')) ? $this->adapter->error() : $e->getMessage()));
        }

        return $schema;
    }

    public function close(): void
    {
        if ($this->adapter) {
            $this->adapter->close();
        }
    }
}