<?php

declare(strict_types=1);

namespace PortalPeople\datadash\models;

use Yii;
use yii\base\Model;

/**
 * ConfigForm
 *
 * Manages named external database connections for ngauge-datadash.
 * Each connection is stored under params['plugins']['ngauge-datadash']['connections'][$name]
 * and bootstrapped as a Yii component named datadash_{name}.
 */
class ConfigForm extends Model
{
    /** @var string Machine-safe connection identifier (lowercase, digits, underscores) */
    public string $name = '';

    /** @var string Optional human-readable label */
    public string $label = '';

    /** @var bool Whether this connection is active */
    public bool $enabled = true;

    /** @var string PDO driver: mysql | pgsql | sqlsrv | sqlite */
    public string $driver = 'mysql';

    /** @var string Database host */
    public string $host = '127.0.0.1';

    /** @var int Database port (0 = use driver default) */
    public int $port = 3306;

    /** @var string Database / schema name */
    public string $dbname = '';

    /** @var string Connection charset */
    public string $charset = 'utf8mb4';

    /** @var string Database username */
    public string $username = '';

    /** @var string Database password (plain-text in memory; encrypted on disk) */
    public string $password = '';

    /** @var bool Encrypt sensitive credentials when persisting to params file */
    public bool $encryptAtRest = true;

    /** @var bool Enable PDO persistent connections */
    public bool $persistent = false;

    // ─── Validation ───────────────────────────────────────────────────────────

    public function rules(): array
    {
        return [
            [['name'], 'required'],
            [['name'], 'match', 'pattern' => '/^[a-z][a-z0-9_]*$/', 'message' => 'Must start with a letter; only lowercase letters, digits and underscores.'],
            [['name', 'label'], 'string', 'max' => 64],
            [['enabled', 'encryptAtRest', 'persistent'], 'boolean'],
            [['driver', 'host', 'dbname', 'username', 'charset', 'label'], 'string'],
            [['password'], 'string'],
            [['port'], 'integer', 'min' => 0, 'max' => 65535],
            [['driver', 'host', 'dbname', 'username'], 'required'],
            [['driver'], 'in', 'range' => ['mysql', 'pgsql', 'sqlsrv', 'sqlite']],
            [['host', 'dbname', 'username', 'charset'], 'string', 'max' => 255],
            [['name', 'host', 'dbname', 'username'], 'filter', 'filter' => 'trim'],
        ];
    }

    public function attributeLabels(): array
    {
        return [
            'name'         => 'Connection Name (ID)',
            'label'        => 'Display Label',
            'enabled'      => 'Enabled',
            'driver'       => 'Database Driver',
            'host'         => 'Host',
            'port'         => 'Port',
            'dbname'       => 'Database Name',
            'charset'      => 'Charset',
            'username'     => 'Username',
            'password'     => 'Password',
            'encryptAtRest'=> 'Encrypt Credentials at Rest',
            'persistent'   => 'Persistent Connections',
        ];
    }

    public function attributeHints(): array
    {
        return [
            'name'          => 'Machine-safe ID used as Yii component: <code>datadash_{name}</code>. Lowercase letters, digits, underscores only.',
            'label'         => 'Optional human-readable display name shown in the connections list.',
            'driver'        => 'PDO driver: mysql, pgsql, sqlsrv, or sqlite.',
            'host'          => 'Server hostname or IP address.',
            'port'          => 'Leave 0 to use the driver default (MySQL 3306, Postgres 5432, SQL Server 1433).',
            'dbname'        => 'Schema / database name to connect to.',
            'charset'       => 'Connection charset; utf8mb4 is recommended for MySQL.',
            'username'      => 'Database account username.',
            'password'      => 'Database account password. Stored encrypted when "Encrypt Credentials at Rest" is enabled.',
            'encryptAtRest' => 'When enabled, the password is encrypted in params-local.php using the backend Security component.',
            'persistent'    => 'Enable PDO persistent connections. Use with caution on shared servers.',
        ];
    }

    /**
     * Yii component ID for this connection (e.g. datadash_myconn).
     */
    public function getComponentId(): string
    {
        return 'datadash_' . $this->name;
    }

    // ─── DSN builder ──────────────────────────────────────────────────────────

    /**
     * Build the PDO DSN string from current field values.
     */
    public function buildDsn(): string
    {
        $port = $this->port > 0 ? $this->port : $this->defaultPort();

        return match ($this->driver) {
            'mysql'  => sprintf('mysql:host=%s;port=%d;dbname=%s;charset=%s', $this->host, $port, $this->dbname, $this->charset),
            'pgsql'  => sprintf('pgsql:host=%s;port=%d;dbname=%s', $this->host, $port, $this->dbname),
            'sqlsrv' => sprintf('sqlsrv:Server=%s,%d;Database=%s', $this->host, $port, $this->dbname),
            'sqlite' => sprintf('sqlite:%s', $this->dbname),
            default  => sprintf('%s:host=%s;dbname=%s', $this->driver, $this->host, $this->dbname),
        };
    }

    private function defaultPort(): int
    {
        return match ($this->driver) {
            'mysql'  => 3306,
            'pgsql'  => 5432,
            'sqlsrv' => 1433,
            default  => 3306,
        };
    }

    // ─── Params persistence ────────────────────────────────────────────────────

    /**
     * Load all connection summaries from application params.
     *
     * @return array<string, array<string, mixed>>
     */
    public static function loadAllConnections(): array
    {
        $params = Yii::$app->params;
        $config = $params['plugins']['ngauge-datadash'] ?? [];
        return (array) ($config['connections'] ?? []);
    }

    /**
     * Populate this model from a named connection in params.
     */
    public function loadFromParams(string $name): bool
    {
        $connections = self::loadAllConnections();

        if (!isset($connections[$name]) || !is_array($connections[$name])) {
            return false;
        }

        $config = $connections[$name];

        $this->name          = $name;
        $this->label         = (string) ($config['label'] ?? '');
        $this->enabled       = (bool) ($config['enabled'] ?? true);
        $this->driver        = (string) ($config['driver'] ?? 'mysql');
        $this->host          = (string) ($config['host'] ?? '127.0.0.1');
        $this->port          = (int) ($config['port'] ?? 3306);
        $this->dbname        = (string) ($config['dbname'] ?? '');
        $this->charset       = (string) ($config['charset'] ?? 'utf8mb4');
        $this->username      = (string) ($config['username'] ?? '');
        $this->password      = $this->decryptField((string) ($config['password'] ?? ''));
        $this->encryptAtRest = (bool) ($config['encryptAtRest'] ?? true);
        $this->persistent    = (bool) ($config['persistent'] ?? false);

        return true;
    }

    /**
     * Persist this connection to params-local.php under connections[$name].
     */
    public function saveToParams(): bool
    {
        try {
            $paramsFile = $this->resolveParamsFile();

            $params = [];
            if (file_exists($paramsFile)) {
                $params = include $paramsFile;
                if (!is_array($params)) {
                    $params = [];
                }
            }

            if (!isset($params['plugins']) || !is_array($params['plugins'])) {
                $params['plugins'] = [];
            }

            if (!isset($params['plugins']['ngauge-datadash']) || !is_array($params['plugins']['ngauge-datadash'])) {
                $params['plugins']['ngauge-datadash'] = [];
            }

            if (!isset($params['plugins']['ngauge-datadash']['connections']) || !is_array($params['plugins']['ngauge-datadash']['connections'])) {
                $params['plugins']['ngauge-datadash']['connections'] = [];
            }

            $params['plugins']['ngauge-datadash']['connections'][$this->name] = [
                'label'        => $this->label,
                'enabled'      => $this->enabled,
                'driver'       => $this->driver,
                'host'         => $this->host,
                'port'         => $this->port > 0 ? $this->port : $this->defaultPort(),
                'dbname'       => $this->dbname,
                'charset'      => $this->charset,
                'username'     => $this->username,
                'password'     => $this->encryptAtRest
                    ? $this->encryptField($this->password)
                    : $this->password,
                'encryptAtRest'=> $this->encryptAtRest,
                'persistent'   => $this->persistent,
            ];

            $dir = dirname($paramsFile);
            if (!is_dir($dir)) {
                @mkdir($dir, 0750, true);
            }

            $content = "<?php\n\nreturn " . var_export($params, true) . ";\n";
            return file_put_contents($paramsFile, $content) !== false;
        } catch (\Throwable $ex) {
            Yii::error('DataDash: failed to save config — ' . $ex->getMessage(), __METHOD__);
            return false;
        }
    }

    /**
     * Delete a named connection from params-local.php.
     */
    public static function deleteFromParams(string $name): bool
    {
        try {
            $instance   = new self();
            $paramsFile = $instance->resolveParamsFile();

            $params = [];
            if (file_exists($paramsFile)) {
                $params = include $paramsFile;
                if (!is_array($params)) {
                    $params = [];
                }
            }

            unset($params['plugins']['ngauge-datadash']['connections'][$name]);

            $content = "<?php\n\nreturn " . var_export($params, true) . ";\n";
            return file_put_contents($paramsFile, $content) !== false;
        } catch (\Throwable $ex) {
            Yii::error('DataDash: failed to delete connection — ' . $ex->getMessage(), __METHOD__);
            return false;
        }
    }

    // ─── Test connection ───────────────────────────────────────────────────────

    /**
     * Attempt a real PDO connection with current field values.
     *
     * @return array{success: bool, message: string, details?: array<string, mixed>}
     */
    public function testConnection(): array
    {
        try {
            $dsn  = $this->buildDsn();
            $opts = [
                \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
                \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
                \PDO::ATTR_TIMEOUT            => 5,
            ];

            $pdo = new \PDO($dsn, $this->username, $this->password, $opts);

            $version = (string) $pdo->query('SELECT VERSION()')->fetchColumn();

            return [
                'success' => true,
                'message' => 'Connection successful.',
                'details' => [
                    'dsn'            => $dsn,
                    'server_version' => $version,
                    'driver'         => $this->driver,
                ],
            ];
        } catch (\PDOException $ex) {
            return [
                'success' => false,
                'message' => 'Connection failed: ' . $ex->getMessage(),
            ];
        } catch (\Throwable $ex) {
            return [
                'success' => false,
                'message' => 'Unexpected error: ' . $ex->getMessage(),
            ];
        }
    }

    // ─── List tables ──────────────────────────────────────────────────────────

    /**
     * Connect and return all tables with their approximate sizes.
     *
     * @return array{success: bool, message: string, tables?: list<array<string, mixed>>}
     */
    public function listTables(): array
    {
        try {
            $dsn  = $this->buildDsn();
            $opts = [
                \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
                \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
                \PDO::ATTR_TIMEOUT            => 10,
            ];

            $pdo = new \PDO($dsn, $this->username, $this->password, $opts);

            $tables = match ($this->driver) {
                'mysql'  => $this->listTablesMysql($pdo),
                'pgsql'  => $this->listTablesPgsql($pdo),
                'sqlsrv' => $this->listTablesSqlsrv($pdo),
                default  => $this->listTablesGeneric($pdo),
            };

            return [
                'success' => true,
                'message' => count($tables) . ' table(s) found.',
                'tables'  => $tables,
            ];
        } catch (\PDOException $ex) {
            return [
                'success' => false,
                'message' => 'Failed to list tables: ' . $ex->getMessage(),
            ];
        } catch (\Throwable $ex) {
            return [
                'success' => false,
                'message' => 'Unexpected error: ' . $ex->getMessage(),
            ];
        }
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function listTablesMysql(\PDO $pdo): array
    {
        $stmt = $pdo->prepare(
            'SELECT
                TABLE_NAME          AS `table`,
                TABLE_ROWS          AS `rows`,
                ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS `size_mb`,
                ROUND(DATA_LENGTH   / 1024 / 1024, 2) AS `data_mb`,
                ROUND(INDEX_LENGTH  / 1024 / 1024, 2) AS `index_mb`,
                ENGINE              AS `engine`,
                TABLE_COLLATION     AS `collation`
            FROM   information_schema.TABLES
            WHERE  TABLE_SCHEMA = :db
            ORDER  BY (DATA_LENGTH + INDEX_LENGTH) DESC'
        );
        $stmt->execute([':db' => $this->dbname]);
        return $stmt->fetchAll() ?: [];
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function listTablesPgsql(\PDO $pdo): array
    {
        $stmt = $pdo->prepare(
            "SELECT
                relname                        AS \"table\",
                n_live_tup                     AS \"rows\",
                ROUND(pg_total_relation_size(c.oid)::numeric / 1024 / 1024, 2) AS \"size_mb\",
                ROUND(pg_relation_size(c.oid)::numeric       / 1024 / 1024, 2) AS \"data_mb\",
                ROUND(pg_indexes_size(c.oid)::numeric        / 1024 / 1024, 2) AS \"index_mb\"
            FROM   pg_class c
            JOIN   pg_stat_user_tables s ON s.relid = c.oid
            WHERE  c.relkind = 'r'
            ORDER  BY pg_total_relation_size(c.oid) DESC"
        );
        $stmt->execute();
        return $stmt->fetchAll() ?: [];
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function listTablesSqlsrv(\PDO $pdo): array
    {
        $stmt = $pdo->prepare(
            "SELECT
                t.name                                              AS [table],
                p.rows                                              AS [rows],
                ROUND((SUM(a.total_pages) * 8) / 1024.0, 2)       AS [size_mb],
                ROUND((SUM(a.used_pages)  * 8) / 1024.0, 2)       AS [data_mb],
                ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.0, 2) AS [index_mb]
            FROM   sys.tables t
            INNER  JOIN sys.indexes i     ON t.object_id = i.object_id
            INNER  JOIN sys.partitions p  ON i.object_id = p.object_id AND i.index_id = p.index_id
            INNER  JOIN sys.allocation_units a ON p.partition_id = a.container_id
            WHERE  t.is_ms_shipped = 0 AND i.object_id > 255
            GROUP  BY t.name, p.rows
            ORDER  BY SUM(a.total_pages) DESC"
        );
        $stmt->execute();
        return $stmt->fetchAll() ?: [];
    }

    /**
     * Generic ANSI fallback — just table names, no size info.
     *
     * @return list<array<string, mixed>>
     */
    private function listTablesGeneric(\PDO $pdo): array
    {
        $stmt = $pdo->query("SELECT name AS `table`, 0 AS `rows`, 0 AS `size_mb` FROM sqlite_master WHERE type='table' ORDER BY name");
        return $stmt ? $stmt->fetchAll() : [];
    }

    // ─── Gii model generation ─────────────────────────────────────────────────

    /**
     * Generate ActiveRecord models using the Chalvington Gii template.
     *
     * Requires common\components\Gii\model\Generator to be available in the
     * host application (standard in NGauge_Base).
     *
     * The datadash_{name} component must already be registered in the app
     * (happens automatically via Module::bootstrap()).
     *
     * @param string[] $tables    Table names to generate; use ['*'] for all tables.
     * @param string   $namespace Target PHP namespace, e.g. common\models\remote
     * @return array{success: bool, message: string, files?: string[]}
     */
    public function generateModels(array $tables, string $namespace): array
    {
        $generatorClass = 'common\\components\\Gii\\model\\Generator';

        if (!class_exists($generatorClass)) {
            return [
                'success' => false,
                'message' => 'common\\components\\Gii\\model\\Generator is not available in this application.',
            ];
        }

        $componentId = $this->getComponentId();

        // Ensure the datadash component is registered for the generator to use
        if (!Yii::$app->has($componentId)) {
            try {
                $dsn  = $this->buildDsn();
                $port = $this->port > 0 ? $this->port : $this->defaultPort();

                Yii::$app->set($componentId, [
                    'class'    => \yii\db\Connection::class,
                    'dsn'      => $dsn,
                    'username' => $this->username,
                    'password' => $this->password,
                    'charset'  => $this->charset,
                ]);
            } catch (\Throwable $ex) {
                return [
                    'success' => false,
                    'message' => 'Could not register DB component: ' . $ex->getMessage(),
                ];
            }
        }

        // Resolve Chalvington template path
        $chalvingtonPath = null;
        $candidates = [
            Yii::getAlias('@common/components/Gii/model/Chalvington', false),
        ];

        foreach ($candidates as $c) {
            if (is_string($c) && is_dir($c)) {
                $chalvingtonPath = $c;
                break;
            }
        }

        $generated = [];
        $errors    = [];

        foreach ($tables as $tableName) {
            /** @var \yii\gii\Generator $generator */
            $generator = new $generatorClass();
            $generator->db        = $componentId;
            $generator->ns        = trim($namespace, '\\');
            $generator->tableName = $tableName;
            $generator->generateQuery = false;

            if ($chalvingtonPath !== null) {
                $generator->templates = ['Chalvington' => $chalvingtonPath];
                $generator->template  = 'Chalvington';
            }

            if (!$generator->validate()) {
                $errs = [];
                foreach ($generator->getErrors() as $attr => $msgs) {
                    $errs[] = $attr . ': ' . implode(', ', $msgs);
                }
                $errors[] = $tableName . ' — ' . implode('; ', $errs);
                continue;
            }

            try {
                /** @var \yii\gii\CodeFile[] $files */
                $files = $generator->generate();

                foreach ($files as $file) {
                    /** @phpstan-ignore-next-line */
                    $file->save();
                    /** @phpstan-ignore-next-line */
                    $generated[] = $file->path;
                }
            } catch (\Throwable $ex) {
                $errors[] = $tableName . ' — ' . $ex->getMessage();
            }
        }

        if (!empty($errors) && empty($generated)) {
            return [
                'success' => false,
                'message' => implode("\n", $errors),
            ];
        }

        return [
            'success' => true,
            'message' => count($generated) . ' file(s) generated.' . (!empty($errors) ? ' Some errors: ' . implode('; ', $errors) : ''),
            'files'   => $generated,
        ];
    }

    // ─── Encryption helpers ────────────────────────────────────────────────────

    private function encryptField(string $value): string
    {
        if ($value === '') {
            return '';
        }

        $securityClass = 'backend\\components\\Security';
        if (class_exists($securityClass)) {
            try {
                /** @phpstan-ignore-next-line */
                return $securityClass::encryptSensitiveData($value);
            } catch (\Throwable $ex) {
            }
        }

        return $value;
    }

    private function decryptField(string $value): string
    {
        if ($value === '') {
            return '';
        }

        $securityClass = 'backend\\components\\Security';
        if (class_exists($securityClass)) {
            try {
                /** @phpstan-ignore-next-line */
                return $securityClass::decryptSensitiveData($value);
            } catch (\Throwable $ex) {
            }
        }

        return $value;
    }

    // ─── Params file resolver ──────────────────────────────────────────────────

    private function resolveParamsFile(): string
    {
        $candidates = [
            '@backend/config/params-local.php',
            '@common/config/params-local.php',
            '@frontend/config/params-local.php',
        ];

        $resolved = [];
        foreach ($candidates as $alias) {
            try {
                $path = Yii::getAlias($alias);
                if (is_string($path)) {
                    $resolved[] = $path;
                }
            } catch (\Throwable $ex) {
            }
        }

        foreach ($resolved as $path) {
            if (file_exists($path)) {
                return $path;
            }
        }

        if (!empty($resolved)) {
            return $resolved[0];
        }

        throw new \RuntimeException('Cannot resolve params-local.php for DataDash configuration.');
    }
}
