<?php
/**
 * SecHub 数据库管理类
 * 负责JSON数据到SQLite的转换和管理
 */

class SecHubDatabase {
    private $dbPath;
    private $jsonDir;
    private $db;
    private $needsInitialSync = false;

    public function __construct($dbPath, $jsonDir) {
        $this->dbPath = $dbPath;
        $this->jsonDir = $jsonDir;
        $this->initDatabase();
    }

    /**
     * 初始化数据库连接
     */
    private function initDatabase() {
        try {
            // 检查数据库文件是否存在
            $dbExists = file_exists($this->dbPath);
            
            $this->db = new PDO('sqlite:' . $this->dbPath);
            $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            
            // 创建同步日志表
            $this->createSyncLogTable();
            
            // 如果数据库文件是新创建的，或者没有任何业务数据表，标记需要同步
            if (!$dbExists || $this->isEmptyDatabase()) {
                $this->needsInitialSync = true;
            }
        } catch (PDOException $e) {
            error_log("数据库连接失败: " . $e->getMessage());
            throw $e;
        }
    }

    /**
     * 检查数据库是否为空（没有业务数据表）
     */
    private function isEmptyDatabase() {
        try {
            $sql = "SELECT count(*) as table_count FROM sqlite_master 
                    WHERE type='table' 
                      AND name NOT LIKE 'sqlite_%' 
                      AND name != 'json_sync_log'";
            $stmt = $this->db->query($sql);
            $result = $stmt->fetch();
            return $result['table_count'] == 0;
        } catch (Exception $e) {
            return true;
        }
    }

    /**
     * 创建同步日志表
     */
    private function createSyncLogTable() {
        $sql = "CREATE TABLE IF NOT EXISTS json_sync_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            json_filename TEXT UNIQUE NOT NULL,
            table_name TEXT NOT NULL,
            section_no INTEGER DEFAULT 0,
            last_sync_time DATETIME NOT NULL,
            json_file_mtime INTEGER NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )";
        
        $this->db->exec($sql);
    }

    /**
     * 检查并同步JSON数据到数据库
     */
    public function syncJsonToDatabase() {
        // 如果是初始同步，强制同步所有文件
        if ($this->needsInitialSync) {
            $jsonFiles = glob($this->jsonDir . '*.json');
            
            foreach ($jsonFiles as $file) {
                $this->syncSingleFile($file);
            }
            $this->needsInitialSync = false;
            return;
        }
        
        // 正常增量同步
        $jsonFiles = glob($this->jsonDir . '*.json');
        
        foreach ($jsonFiles as $file) {
            $this->syncSingleFile($file);
        }
    }

    /**
     * 同步单个JSON文件到数据库
     */
    private function syncSingleFile($filePath) {
        $filename = basename($filePath);
        $tableName = pathinfo($filename, PATHINFO_FILENAME);
        
        // 检查是否需要更新
        if (!$this->shouldUpdate($filePath, $tableName)) {
            return;
        }

        // 读取JSON数据
        $data = $this->loadJsonData($filePath);
        if (empty($data)) {
            return;
        }

        // 获取排序号（从第一个数据项的 no 字段）
        $sectionNo = $data[0]['no'] ?? 0;
        
        // 创建或更新表
        $this->createTable($tableName, $data[0]);
        
        // 清空旧数据
        $this->clearTable($tableName);
        
        // 插入新数据（跳过第一个section项）
        $items = array_slice($data, 1);
        foreach ($items as $item) {
            $this->insertItem($tableName, $item, $data[0]['section'] ?? $tableName);
        }
        
        // 更新同步日志（包含排序号）
        $this->updateSyncLog($filename, $tableName, $sectionNo);
    }

    /**
     * 更新同步日志
     */
    private function updateSyncLog($filename, $tableName, $sectionNo = 0) {
        $jsonFile = $this->jsonDir . $filename;
        $jsonModified = filemtime($jsonFile);
        $syncTime = date('Y-m-d H:i:s');
        
        // 检查是否已存在记录
        $sql = "SELECT id FROM json_sync_log WHERE json_filename = :filename";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':filename' => $filename]);
        $exists = $stmt->fetch();
        
        if ($exists) {
            // 更新现有记录
            $sql = "UPDATE json_sync_log 
                    SET table_name = :table_name,
                        section_no = :section_no,
                        last_sync_time = :sync_time, 
                        json_file_mtime = :mtime 
                    WHERE json_filename = :filename";
        } else {
            // 插入新记录
            $sql = "INSERT INTO json_sync_log (json_filename, table_name, section_no, last_sync_time, json_file_mtime) 
                    VALUES (:filename, :table_name, :section_no, :sync_time, :mtime)";
        }
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([
            ':filename' => $filename,
            ':table_name' => $tableName,
            ':section_no' => $sectionNo,
            ':sync_time' => $syncTime,
            ':mtime' => $jsonModified
        ]);
    }

    /**
     * 判断是否需要更新
     */
    private function shouldUpdate($jsonFile, $tableName) {
        $filename = basename($jsonFile);
        $jsonModified = filemtime($jsonFile);
        
        // 查询该JSON文件的同步记录
        $sql = "SELECT * FROM json_sync_log WHERE json_filename = :filename";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':filename' => $filename]);
        $log = $stmt->fetch();
        
        // 如果没有同步记录，需要更新
        if (!$log) {
            return true;
        }
        
        // 计算时间差（秒）
        $timeDiff = $jsonModified - $log['json_file_mtime'];
        
        // 如果JSON文件修改时间比记录的晚至少5分钟（300秒），则需要更新
        if ($timeDiff >= 300) {
            return true;
        }
        
        // 否则不需要更新
        return false;
    }


    /**
     * 加载JSON数据
     */
    private function loadJsonData($filePath) {
        if (!file_exists($filePath)) {
            return [];
        }
        
        $content = file_get_contents($filePath);
        if ($content === false) {
            return [];
        }
        
        $data = json_decode($content, true);
        if (json_last_error() !== JSON_ERROR_NONE) {
            error_log("JSON解析错误: " . json_last_error_msg());
            return [];
        }
        
        return is_array($data) ? $data : [];
    }

    /**
     * 创建数据表
     */
    private function createTable($tableName, $firstItem) {
        // 清理表名，只保留字母、数字和下划线
        $tableName = preg_replace('/[^a-zA-Z0-9_]/', '_', $tableName);
        
        $sql = "CREATE TABLE IF NOT EXISTS {$tableName} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            section TEXT NOT NULL,
            name TEXT NOT NULL,
            url TEXT,
            description TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )";
        
        $this->db->exec($sql);
    }

    /**
     * 清空表数据
     */
    private function clearTable($tableName) {
        $tableName = preg_replace('/[^a-zA-Z0-9_]/', '_', $tableName);
        $this->db->exec("DELETE FROM {$tableName}");
    }

    /**
     * 插入数据项
     */
    private function insertItem($tableName, $item, $section) {
        $tableName = preg_replace('/[^a-zA-Z0-9_]/', '_', $tableName);
        
        $sql = "INSERT INTO {$tableName} (section, name, url, description) 
                VALUES (:section, :name, :url, :description)";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([
            ':section' => $section,
            ':name' => $item['name'] ?? '',
            ':url' => $item['url'] ?? '',
            ':description' => $item['description'] ?? ''
        ]);
    }

    /**
     * 全局搜索
     */
    public function globalSearch($keyword) {
        if (empty($keyword)) {
            return [];
        }

        $tables = $this->getAllTables();
        $results = [];

        foreach ($tables as $table) {
            $tableName = $table['name'];
            $sql = "SELECT *, '{$tableName}' as source_table FROM {$tableName} 
                    WHERE name LIKE :keyword 
                       OR description LIKE :keyword 
                       OR url LIKE :keyword
                    ORDER BY name";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([':keyword' => '%' . $keyword . '%']);
            $items = $stmt->fetchAll();
            
            if (!empty($items)) {
                $results[$tableName] = [
                    'section' => $items[0]['section'] ?? $tableName,
                    'items' => $items
                ];
            }
        }

        return $results;
    }

    /**
     * 按栏目搜索
     */
    public function searchBySection($section, $keyword) {
        if (empty($keyword)) {
            return [];
        }

        $tables = $this->getAllTables();
        $results = [];

        foreach ($tables as $table) {
            $tableName = $table['name'];
            $sql = "SELECT * FROM {$tableName} 
                    WHERE section = :section 
                      AND (name LIKE :keyword 
                           OR description LIKE :keyword 
                           OR url LIKE :keyword)
                    ORDER BY name";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                ':section' => $section,
                ':keyword' => '%' . $keyword . '%'
            ]);
            $items = $stmt->fetchAll();
            
            if (!empty($items)) {
                $results[] = $items;
            }
        }

        return array_merge(...$results);
    }

    /**
     * 获取所有栏目配置
     */
    public function getSectionsConfig() {
        $tables = $this->getAllTables();
        $sections = [];

        foreach ($tables as $table) {
            $tableName = $table['name'];
            
            // 从同步日志中获取排序号
            $sql = "SELECT section_no FROM json_sync_log WHERE table_name = :table_name LIMIT 1";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([':table_name' => $tableName]);
            $log = $stmt->fetch();
            $sectionNo = $log ? $log['section_no'] : 0;
            
            // 获取栏目名称
            $sql = "SELECT DISTINCT section FROM {$tableName} LIMIT 1";
            $stmt = $this->db->query($sql);
            $row = $stmt->fetch();
            
            if ($row) {
                $sections[$tableName] = [
                    'title' => $row['section'],
                    'table' => $tableName,
                    'no' => $sectionNo
                ];
            }
        }

        // 按 no 字段排序
        uasort($sections, function($a, $b) {
            return ($a['no'] ?? 0) - ($b['no'] ?? 0);
        });

        return $sections;
    }

    /**
     * 获取指定栏目的所有项目
     */
    public function getItemsBySection($tableName) {
        if (!$this->validateTableName($tableName)) {
            return [];
        }
        $tableName = preg_replace('/[^a-zA-Z0-9_]/', '_', $tableName);
        $sql = "SELECT * FROM {$tableName} ORDER BY name";
        $stmt = $this->db->query($sql);
        return $stmt->fetchAll();
    }

    /**
     * 获取所有表名
     */
    private function getAllTables() {
        $sql = "SELECT name FROM sqlite_master 
                WHERE type='table' 
                  AND name NOT LIKE 'sqlite_%' 
                  AND name != 'json_sync_log'
                ORDER BY name";
        $stmt = $this->db->query($sql);
        return $stmt->fetchAll();
    }

    /**
     * 关闭数据库连接
     */
    public function close() {
        $this->db = null;
    }

    // ======================== 表名安全验证 ========================

    /**
     * 验证表名是否存在于数据库中（防SQL注入）
     */
    private function validateTableName($tableName) {
        $tables = $this->getAllTables();
        $validNames = array_column($tables, 'name');
        return in_array($tableName, $validNames, true);
    }

    // ======================== JSON 文件操作 ========================

    /**
     * 读取JSON文件并解析
     * @return array|null
     */
    private function readJsonArray($filePath) {
        if (!file_exists($filePath)) return null;
        $content = file_get_contents($filePath);
        if ($content === false) return null;
        $data = json_decode($content, true);
        if (json_last_error() !== JSON_ERROR_NONE) return null;
        return is_array($data) ? $data : null;
    }

    /**
     * 写入JSON文件（保持格式）
     */
    private function writeJsonArray($filePath, $data) {
        $json = json_encode($data, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);
        return file_put_contents($filePath, $json . "\n") !== false;
    }

    /**
     * 从表名获取JSON文件路径
     */
    private function getJsonFilePath($tableName) {
        return $this->jsonDir . $tableName . '.json';
    }

    /**
     * 获取所有栏目带详细信息
     * @return array 每个元素包含 name, title, no, item_count, filename
     */
    public function getAllSectionsWithInfo() {
        $tables = $this->getAllTables();
        $sections = [];

        foreach ($tables as $table) {
            $info = $this->getSectionInfo($table['name']);
            if ($info) {
                $sections[] = $info;
            }
        }

        // 按 no 排序
        usort($sections, function($a, $b) {
            return ($a['no'] ?? 0) - ($b['no'] ?? 0);
        });

        return $sections;
    }

    /**
     * 获取单个栏目配置信息
     * @return array|null
     */
    public function getSectionInfo($tableName) {
        if (!$this->validateTableName($tableName)) return null;

        $filePath = $this->getJsonFilePath($tableName);
        $data = $this->readJsonArray($filePath);
        if (!$data || empty($data)) return null;

        $meta = $data[0];
        return [
            'name' => $tableName,
            'title' => $meta['section'] ?? $tableName,
            'no' => $meta['no'] ?? 0,
            'item_count' => count($data) - 1,
            'filename' => $tableName . '.json'
        ];
    }

    // ======================== 工具条目 CRUD ========================

    /**
     * 在栏目中添加工具条目
     */
    public function addItemToSection($tableName, $name, $url, $description) {
        if (!$this->validateTableName($tableName)) {
            return ['success' => false, 'error' => '无效的栏目'];
        }

        $filePath = $this->getJsonFilePath($tableName);
        $data = $this->readJsonArray($filePath);
        if (!$data) {
            return ['success' => false, 'error' => '读取JSON文件失败'];
        }

        $data[] = [
            'name' => $name,
            'url' => $url,
            'description' => $description
        ];

        if (!$this->writeJsonArray($filePath, $data)) {
            return ['success' => false, 'error' => '写入JSON文件失败'];
        }

        $this->syncSingleFile($filePath);
        return ['success' => true];
    }

    /**
     * 更新栏目中的工具条目
     * @param string $tableName 表名
     * @param int $itemIndex 工具在JSON中的索引（0表示第一个工具项）
     */
    public function updateItemInSection($tableName, $itemIndex, $name, $url, $description) {
        if (!$this->validateTableName($tableName)) {
            return ['success' => false, 'error' => '无效的栏目'];
        }

        $filePath = $this->getJsonFilePath($tableName);
        $data = $this->readJsonArray($filePath);
        if (!$data) {
            return ['success' => false, 'error' => '读取JSON文件失败'];
        }

        // itemIndex 从 0 开始，对应 JSON 数组中的 data[1], data[2], ...
        $arrayIndex = $itemIndex + 1;
        if (!isset($data[$arrayIndex])) {
            return ['success' => false, 'error' => '工具条目不存在'];
        }

        $data[$arrayIndex]['name'] = $name;
        $data[$arrayIndex]['url'] = $url;
        $data[$arrayIndex]['description'] = $description;

        if (!$this->writeJsonArray($filePath, $data)) {
            return ['success' => false, 'error' => '写入JSON文件失败'];
        }

        $this->syncSingleFile($filePath);
        return ['success' => true];
    }

    /**
     * 删除栏目中的工具条目
     */
    public function deleteItemFromSection($tableName, $itemIndex) {
        if (!$this->validateTableName($tableName)) {
            return ['success' => false, 'error' => '无效的栏目'];
        }

        $filePath = $this->getJsonFilePath($tableName);
        $data = $this->readJsonArray($filePath);
        if (!$data) {
            return ['success' => false, 'error' => '读取JSON文件失败'];
        }

        $arrayIndex = $itemIndex + 1;
        if (!isset($data[$arrayIndex])) {
            return ['success' => false, 'error' => '工具条目不存在'];
        }

        array_splice($data, $arrayIndex, 1);

        if (!$this->writeJsonArray($filePath, $data)) {
            return ['success' => false, 'error' => '写入JSON文件失败'];
        }

        $this->syncSingleFile($filePath);
        return ['success' => true];
    }

    /**
     * 批量添加工具条目
     * @param string $tableName 表名
     * @param array $items 条目数组，每个元素包含 [name, url, description]
     */
    public function batchAddItems($tableName, $items) {
        if (!$this->validateTableName($tableName)) {
            return ['success' => false, 'error' => '无效的栏目', 'count' => 0, 'total' => count($items)];
        }

        $filePath = $this->getJsonFilePath($tableName);
        $data = $this->readJsonArray($filePath);
        if (!$data) {
            return ['success' => false, 'error' => '读取JSON文件失败', 'count' => 0, 'total' => count($items)];
        }

        $added = 0;
        foreach ($items as $item) {
            $name = trim($item[0] ?? '');
            $url = trim($item[1] ?? '');
            $description = trim($item[2] ?? '');

            if (empty($name) && empty($url)) continue;

            $data[] = [
                'name' => $name,
                'url' => $url,
                'description' => $description
            ];
            $added++;
        }

        if ($added === 0) {
            return ['success' => false, 'error' => '没有有效的条目可添加', 'count' => 0, 'total' => count($items)];
        }

        if (!$this->writeJsonArray($filePath, $data)) {
            return ['success' => false, 'error' => '写入JSON文件失败', 'count' => $added, 'total' => count($items)];
        }

        $this->syncSingleFile($filePath);
        return ['success' => true, 'count' => $added, 'total' => count($items)];
    }

    // ======================== 栏目管理 ========================

    /**
     * 创建新栏目
     * @param string $sectionName 栏目显示名称
     * @return array
     */
    public function createSection($sectionName) {
        if (empty(trim($sectionName))) {
            return ['success' => false, 'error' => '栏目名称不能为空'];
        }

        $tableName = $this->generateTableName($sectionName);

        // 检查JSON文件是否已存在
        if (file_exists($this->getJsonFilePath($tableName))) {
            return ['success' => false, 'error' => '栏目文件已存在: ' . $tableName . '.json'];
        }

        // 获取最大排序号
        $maxNo = $this->getMaxSectionNo();

        // 创建JSON数据
        $data = [
            ['no' => $maxNo + 1, 'section' => $sectionName]
        ];

        if (!$this->writeJsonArray($this->getJsonFilePath($tableName), $data)) {
            return ['success' => false, 'error' => '创建JSON文件失败'];
        }

        // 同步到数据库
        try {
            $this->syncSingleFile($this->getJsonFilePath($tableName));
        } catch (Exception $e) {
            // 清理已创建的文件
            unlink($this->getJsonFilePath($tableName));
            return ['success' => false, 'error' => '数据库同步失败: ' . $e->getMessage()];
        }

        return ['success' => true, 'table_name' => $tableName];
    }

    /**
     * 删除栏目（删除JSON文件 + 数据库表 + 同步日志）
     */
    public function removeSection($tableName) {
        if (!$this->validateTableName($tableName)) {
            return ['success' => false, 'error' => '栏目不存在'];
        }

        // 删除JSON文件
        $filePath = $this->getJsonFilePath($tableName);
        if (file_exists($filePath)) {
            if (!unlink($filePath)) {
                return ['success' => false, 'error' => '删除JSON文件失败'];
            }
        }

        // 删除数据库表
        $safeName = preg_replace('/[^a-zA-Z0-9_]/', '_', $tableName);
        $this->db->exec("DROP TABLE IF EXISTS {$safeName}");

        // 删除同步日志
        $stmt = $this->db->prepare("DELETE FROM json_sync_log WHERE table_name = :tn");
        $stmt->execute([':tn' => $tableName]);

        return ['success' => true];
    }

    /**
     * 重命名栏目显示名称
     */
    public function renameSection($tableName, $newSectionName) {
        if (empty(trim($newSectionName))) {
            return ['success' => false, 'error' => '栏目名称不能为空'];
        }

        if (!$this->validateTableName($tableName)) {
            return ['success' => false, 'error' => '栏目不存在'];
        }

        $filePath = $this->getJsonFilePath($tableName);
        $data = $this->readJsonArray($filePath);
        if (!$data) {
            return ['success' => false, 'error' => '读取JSON文件失败'];
        }

        // 更新元数据中的section名称
        $data[0]['section'] = $newSectionName;

        if (!$this->writeJsonArray($filePath, $data)) {
            return ['success' => false, 'error' => '写入JSON文件失败'];
        }

        $this->syncSingleFile($filePath);
        return ['success' => true];
    }

    /**
     * 获取全部栏目数据用于导出
     * @return array
     */
    public function exportAllSections() {
        $tables = $this->getAllTables();
        $export = [];

        foreach ($tables as $table) {
            $filePath = $this->getJsonFilePath($table['name']);
            $data = $this->readJsonArray($filePath);
            if ($data) {
                $export[$table['name']] = $data;
            }
        }

        return $export;
    }

    /**
     * 获取栏目CSV导出内容
     */
    public function exportSectionToCsv($tableName) {
        if (!$this->validateTableName($tableName)) return '';

        $items = $this->getItemsBySection($tableName);
        $info = $this->getSectionInfo($tableName);

        $csv = "名称,URL,描述\r\n";
        foreach ($items as $item) {
            $name = str_replace('"', '""', $item['name'] ?? '');
            $url = str_replace('"', '""', $item['url'] ?? '');
            $desc = str_replace('"', '""', $item['description'] ?? '');
            $csv .= "\"{$name}\",\"{$url}\",\"{$desc}\"\r\n";
        }

        return $csv;
    }

    // ======================== 辅助方法 ========================

    /**
     * 获取最大排序号
     */
    private function getMaxSectionNo() {
        $sql = "SELECT MAX(section_no) as max_no FROM json_sync_log";
        $stmt = $this->db->query($sql);
        $row = $stmt->fetch();
        return $row['max_no'] ?? 0;
    }

    /**
     * 从栏目名称生成表名/文件名
     */
    private function generateTableName($name) {
        // 先做基本清理
        $name = preg_replace('/[^a-zA-Z0-9_\x{4e00}-\x{9fff}]/u', '_', $name);
        $name = preg_replace('/_+/', '_', $name);
        $name = trim($name, '_');

        if (empty($name)) $name = 'section';

        // 如果包含中文，提取英文部分+hash
        if (preg_match('/[\x{4e00}-\x{9fff}]/u', $name)) {
            $eng = preg_replace('/[\x{4e00}-\x{9fff}]/u', '', $name);
            $eng = trim(preg_replace('/_+/', '_', $eng), '_');
            if (empty($eng)) $eng = 'section';
            $hash = substr(md5($name), 0, 4);
            $name = $eng . '_' . $hash;
        }

        return strtolower($name);
    }
}