<?php

namespace base\common\service\tools;

use think\console\Input;
use think\console\Output;
use think\facade\Db;
use think\facade\Env;
use think\facade\Config;
use think\Exception;

class DbServiceBase
{
    protected $connection = 'main';

    public function setConnection(string $connection): void
    {
        $this->connection = $connection;
    }

    public function getConnection(): string
    {
        return $this->connection;
    }

    public function checkDebugMode(Output $output): bool
    {
        if (!Env::get('APP_DEBUG', false)) {
            $output->error('数据库操作命令仅在开发者模式下可用，请检查 APP_DEBUG 配置');
            return false;
        }
        return true;
    }

    public function getDbConnection(Input $input): string
    {
        return $input->getOption('connection') ?: Config::get('database.default', 'main');
    }

    public function getTablePrefix(): string
    {
        $connection = $this->connection;
        return Config::get('database.connections.' . $connection . '.prefix', '');
    }

    public function getFullTableName(string $tableName): string
    {
        $prefix = $this->getTablePrefix();
        if ($prefix && !str_starts_with($tableName, $prefix)) {
            return $prefix . $tableName;
        }
        return $tableName;
    }

    public function formatTableOutput(array $data, Output $output): void
    {
        if (empty($data)) {
            $output->writeln('无数据');
            return;
        }

        $columns = array_keys($data[0]);
        $columnWidths = [];

        foreach ($columns as $column) {
            $maxLen = strlen($column);
            foreach ($data as $row) {
                $valueLen = strlen((string)$row[$column]);
                if ($valueLen > $maxLen) {
                    $maxLen = $valueLen;
                }
            }
            $columnWidths[$column] = $maxLen + 2;
        }

        $separator = '+';
        foreach ($columnWidths as $width) {
            $separator .= str_repeat('-', $width) . '+';
        }

        $output->writeln($separator);

        $header = '|';
        foreach ($columns as $column) {
            $header .= ' ' . str_pad($column, $columnWidths[$column] - 1) . '|';
        }
        $output->writeln($header);
        $output->writeln($separator);

        foreach ($data as $row) {
            $line = '|';
            foreach ($columns as $column) {
                $value = $row[$column] ?? '';
                if (is_null($value)) {
                    $value = 'NULL';
                }
                $line .= ' ' . str_pad((string)$value, $columnWidths[$column] - 1) . '|';
            }
            $output->writeln($line);
        }

        $output->writeln($separator);
    }

    public function formatJsonOutput(array $data, Output $output): void
    {
        $output->writeln(json_encode($data, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT));
    }

    public function showHelp(string $command, Output $output): void
    {
        $helpTexts = [
            'tools:db:query' => [
                'title' => '数据库查询命令 - tools:db:query',
                'description' => '执行 SQL 查询语句（SELECT）并显示结果',
                'usage' => 'php think tools:db:query <SQL语句> [选项]',
                'options' => [
                    '--format=table' => '输出格式，可选值：table（默认）、json',
                    '--limit=N' => '限制显示行数',
                    '--connection=main' => '指定数据库连接配置',
                    '-h, --help' => '显示帮助信息',
                ],
                'examples' => [
                    'php think tools:db:query "SELECT * FROM ul_system_admin"' => '查询所有管理员',
                    'php think tools:db:query "SELECT id, username, nickname FROM ul_system_admin" --limit 10' => '查询前10条记录',
                    'php think tools:db:query "SELECT * FROM ul_system_admin WHERE status = 1" --format=json' => 'JSON格式输出',
                ],
                'notes' => [
                    '支持 SELECT 语句',
                    '自动显示执行时间和结果行数',
                    '支持多数据库连接',
                ],
            ],
            'tools:db:execute' => [
                'title' => '数据库执行命令 - tools:db:execute',
                'description' => '执行 SQL 非查询语句（INSERT/UPDATE/DELETE）',
                'usage' => 'php think tools:db:execute <SQL语句> [选项]',
                'options' => [
                    '--force' => '跳过确认直接执行',
                    '--transaction' => '在事务中执行（失败自动回滚）',
                    '--connection=main' => '指定数据库连接配置',
                    '-h, --help' => '显示帮助信息',
                ],
                'examples' => [
                    'php think tools:db:execute "UPDATE ul_system_admin SET status = 1 WHERE id = 1"' => '更新管理员状态',
                    'php think tools:db:execute "UPDATE ul_system_admin SET status = 1 WHERE id = 1" --transaction' => '在事务中执行',
                    'php think tools:db:execute "UPDATE ul_system_admin SET status = 1 WHERE id = 1" --force' => '强制执行（跳过确认）',
                ],
                'notes' => [
                    '支持 INSERT、UPDATE、DELETE 语句',
                    '默认需要用户确认',
                    '使用 --force 可跳过确认',
                    '使用 --transaction 可在事务中执行',
                ],
            ],
            'tools:db:table' => [
                'title' => '表查询命令 - tools:db:table',
                'description' => '使用查询构建器操作表',
                'usage' => 'php think tools:db:table <表名> [选项]',
                'options' => [
                    '--where=条件' => 'WHERE 条件',
                    '--field=字段' => '查询字段，多个用逗号分隔',
                    '--limit=N' => '限制行数',
                    '--order=排序' => '排序，如 "id DESC"',
                    '--count' => '只统计数量',
                    '--connection=main' => '指定数据库连接配置',
                    '-h, --help' => '显示帮助信息',
                ],
                'examples' => [
                    'php think tools:db:table system_admin --where "status=1" --limit 10' => '查询表数据',
                    'php think tools:db:table system_admin --field "id,username,nickname"' => '查询指定字段',
                    'php think tools:db:table system_admin --order "id DESC" --limit 5' => '排序查询',
                    'php think tools:db:table system_admin --count' => '统计记录数',
                ],
                'notes' => [
                    '表名不需要带前缀（会自动添加）',
                    '支持查询构建器所有常用方法',
                    '自动处理表前缀',
                ],
            ],
            'tools:db:info' => [
                'title' => '数据库信息命令 - tools:db:info',
                'description' => '显示数据库连接信息和表列表',
                'usage' => 'php think tools:db:info [选项]',
                'options' => [
                    '--connection=main' => '指定数据库连接配置',
                    '--with-count' => '显示每个表的记录数',
                    '-h, --help' => '显示帮助信息',
                ],
                'examples' => [
                    'php think tools:db:info' => '显示数据库基本信息',
                    'php think tools:db:info --with-count' => '显示表记录数',
                ],
                'notes' => [
                    '显示数据库连接信息',
                    '显示所有表列表',
                    '使用 --with-count 可显示记录数',
                ],
            ],
            'tools:db:desc' => [
                'title' => '表结构命令 - tools:db:desc',
                'description' => '显示表结构信息',
                'usage' => 'php think tools:db:desc <表名> [选项]',
                'options' => [
                    '--connection=main' => '指定数据库连接配置',
                    '--show-index' => '显示索引信息',
                    '-h, --help' => '显示帮助信息',
                ],
                'examples' => [
                    'php think tools:db:desc system_admin' => '显示表结构',
                    'php think tools:db:desc system_admin --show-index' => '显示索引信息',
                ],
                'notes' => [
                    '表名不需要带前缀（会自动添加）',
                    '显示字段类型、默认值、是否为空、注释',
                    '使用 --show-index 可显示索引信息',
                ],
            ],
            'tools:db:count' => [
                'title' => '统计命令 - tools:db:count',
                'description' => '统计表记录数',
                'usage' => 'php think tools:db:count <表名> [选项]',
                'options' => [
                    '--connection=main' => '指定数据库连接配置',
                    '--where=条件' => 'WHERE 条件',
                    '-h, --help' => '显示帮助信息',
                ],
                'examples' => [
                    'php think tools:db:count system_admin' => '统计所有记录',
                    'php think tools:db:count system_admin --where "status=1"' => '统计符合条件的记录',
                ],
                'notes' => [
                    '表名不需要带前缀（会自动添加）',
                    '只显示记录数量',
                ],
            ],
        ];

        if (!isset($helpTexts[$command])) {
            $output->writeln('帮助信息不存在');
            return;
        }

        $help = $helpTexts[$command];

        $output->writeln('');
        $output->writeln('<comment>' . str_repeat('=', 60) . '</comment>');
        $output->writeln('<info>' . $help['title'] . '</info>');
        $output->writeln('<comment>' . str_repeat('=', 60) . '</comment>');
        $output->writeln('');
        $output->writeln('<info>描述：</info>');
        $output->writeln('  ' . $help['description']);
        $output->writeln('');
        $output->writeln('<info>用法：</info>');
        $output->writeln('  ' . $help['usage']);
        $output->writeln('');
        $output->writeln('<info>选项：</info>');
        foreach ($help['options'] as $option => $desc) {
            $output->writeln('  <info>' . str_pad($option, 30) . '</info>' . $desc);
        }
        $output->writeln('');
        $output->writeln('<info>示例：</info>');
        foreach ($help['examples'] as $example => $desc) {
            $output->writeln('  ' . $desc);
            $output->writeln('    <info>' . $example . '</info>');
        }
        $output->writeln('');
        $output->writeln('<info>注意事项：</info>');
        foreach ($help['notes'] as $note) {
            $output->writeln('  - ' . $note);
        }
        $output->writeln('');
        $output->writeln('<comment>' . str_repeat('=', 60) . '</comment>');
        $output->writeln('');
    }
}