# frozen_string_literal: true

require 'sequel'
require 'logger'
require 'fileutils'
require_relative 'settings'

# 数据库连接管理（SQLite + WAL 模式版本）
module Database
  # 获取数据库连接
  def self.connection
    @connection ||= create_connection
  end

  # 创建数据库连接
  def self.create_connection
    # 优先使用环境变量（Docker环境），否则使用配置文件
    db_path = ENV['DATABASE_PATH'] || Settings.instance.database_path
    # 确保目录存在
    FileUtils.mkdir_p(File.dirname(db_path))

    # SQLite 连接字符串 - 处理 Windows 路径
    # 将反斜杠转换为正斜杠，并确保绝对路径有三个斜杠
    db_path_for_uri = db_path.gsub('\\', '/')
    if db_path_for_uri.match?(/^[A-Za-z]:\//)
      # Windows 绝对路径: C:/path/to/db -> sqlite:///C:/path/to/db
      connection_string = "sqlite:///#{db_path_for_uri}"
    else
      # 相对路径或 Unix 路径
      connection_string = "sqlite://#{db_path_for_uri}"
    end

    db = Sequel.connect(connection_string)

    # 配置日志
    db.loggers << Logger.new($stdout) if ENV['DB_DEBUG']

    # 启用 WAL (Write-Ahead Logging) 模式，大幅提升并发性能
    # - 读操作不阻塞写操作
    # - 写操作之间的并发也更好
    db.run('PRAGMA journal_mode = WAL')
    db.run('PRAGMA synchronous = NORMAL')  # WAL 模式下 NORMAL 就足够安全
    db.run('PRAGMA busy_timeout = 5000')     # 锁等待超时 5 秒
    db.run('PRAGMA foreign_keys = ON')        # 启用外键约束

    # 设置为Sequel模型的默认数据库连接
    Sequel::Model.db = db

    db
  end

  # 迁移数据库
  def self.migrate!
    puts "运行数据库迁移..."

    connection.transaction do
      # 创建本地设备表（使用UUID主键）
      connection.create_table? :local_devices do
        String :id, primary_key: true, null: false
        String :name, null: false
        String :device_model
        String :ip_address, null: false
        Integer :port, default: 23
        String :connection_type, default: 'telnet'
        String :username
        String :encrypted_password  # 加密存储
        String :enable_command
        String :encrypted_enable_password
        String :encrypted_root_password  # root密码加密存储
        String :ssh_key_path
        String :description
        Boolean :active, default: true
        DateTime :created_at
        DateTime :updated_at
        DateTime :last_connected_at

        index [:name], unique: true
        index [:active]
      end

      # 创建本地任务表
      connection.create_table? :local_tasks do
        String :id, primary_key: true, null: false
        String :name, null: false
        String :description
        String :natural_language_input
        String :task_definition, text: true
        Integer :device_count, default: 0
        String :unselected_device_ids, text: true
        String :unselected_script_ids, text: true
        DateTime :created_at
        DateTime :updated_at
      end

      # 创建任务执行记录表
      connection.create_table? :local_task_executions do
        String :id, primary_key: true, null: false
        String :task_id, null: false
        String :status, default: 'pending'
        Integer :progress, default: 0
        String :log_directory
        String :execution_result, text: true
        String :execution_units, text: true
        DateTime :started_at
        DateTime :completed_at
        DateTime :created_at
        DateTime :updated_at

        index [:task_id]
        index [:status]
      end

      # 创建脚本缓存表
      connection.create_table? :cached_scripts do
        String :id, primary_key: true, null: false
        String :name, null: false
        String :description
        String :code, text: true, null: false
        String :commands, text: true
        String :version
        String :device_model_pattern
        DateTime :created_at
        DateTime :updated_at
        DateTime :last_used_at
      end

      # 创建系统设置表
      connection.create_table? :system_settings do
        primary_key :id
        String :key, null: false, unique: true
        String :value, text: true
        DateTime :updated_at

        index [:key], unique: true
      end

      # 创建自定义集成代码表（支持双版本）
      connection.create_table? :custom_integration_codes do
        String :id, primary_key: true, null: false
        String :server_version_hash, null: false
        String :server_code, text: true, null: false
        DateTime :server_updated_at
        String :user_version_hash
        String :user_code, text: true
        DateTime :user_updated_at
        DateTime :last_synced_at
        String :metadata, text: true  # JSON格式（包含use_user_version等配置）
        DateTime :created_at
        DateTime :updated_at

        index [:server_version_hash], unique: true
        index [:user_version_hash]
        index [:last_synced_at]
      end

      # 创建升级记录表
      connection.create_table? :upgrade_records do
        String :id, primary_key: true, null: false
        String :package_name, null: false
        String :package_version
        String :status, default: 'pending'
        String :deployment_result, text: true  # JSON格式
        String :file_results, text: true  # JSON格式
        DateTime :started_at
        DateTime :completed_at
        DateTime :created_at
        DateTime :updated_at

        index [:status]
        index [:created_at]
      end
    end

    puts "数据库迁移完成 (WAL 模式已启用)"
  rescue StandardError => e
    puts "数据库迁移失败: #{e.message}"
    puts e.backtrace
    raise
  end

  # 添加未选择字段的迁移（用于现有数据库）
  def self.add_execution_units_column!
    db = connection
    unless db.columns(:local_task_executions).map(&:name).include?(:execution_units)
      db.alter_table :local_task_executions do
        add_column :execution_units, String, text: true
      end
    end
  end

  # 添加未选择字段的迁移
  def self.add_unselected_columns!
    db = connection
    existing_columns = db.schema(:local_tasks).map { |c| c[0] }
    unless existing_columns.include?(:unselected_device_ids)
      db.alter_table :local_tasks do
        add_column :unselected_device_ids, String, text: true
      end
      puts "已添加列: unselected_device_ids"
    end
    unless existing_columns.include?(:unselected_script_ids)
      db.alter_table :local_tasks do
        add_column :unselected_script_ids, String, text: true
      end
      puts "已添加列: unselected_script_ids"
    end
  end

  # 添加 root 密码字段的迁移
  def self.add_root_password_column!
    db = connection
    existing_columns = db.schema(:local_devices).map { |c| c[0] }
    unless existing_columns.include?(:encrypted_root_password)
      db.alter_table :local_devices do
        add_column :encrypted_root_password, String
      end
      puts "已添加列: encrypted_root_password"
    end
  end

  # 重置数据库（开发用）
  def self.reset!
    puts "重置数据库..."
    db_path = Settings.instance.database_path
    if File.exist?(db_path)
      # 断开连接
      @connection&.disconnect
      @connection = nil
      # 删除数据库文件（包括 WAL 文件）
      FileUtils.rm_f(db_path)
      FileUtils.rm_f("#{db_path}-wal")
      FileUtils.rm_f("#{db_path}-shm")
    end
    migrate!
    puts "数据库已重置"
  end

  # 备份数据库
  def self.backup!(backup_path = nil)
    db_path = Settings.instance.database_path
    backup_path ||= "#{db_path}.#{Time.now.strftime('%Y%m%d_%H%M%S')}.backup"

    # SQLite 在线备份：先检查点，把 WAL 内容合并到主数据库
    connection.run('PRAGMA wal_checkpoint(TRUNCATE)') if @connection

    FileUtils.cp(db_path, backup_path)
    puts "数据库已备份到: #{backup_path}"
    backup_path
  end

  # 检查数据库连接
  def self.connected?
    begin
      connection.test_connection
      true
    rescue StandardError
      false
    end
  end

  # 获取当前数据库状态信息
  def self.database_info
    return {} unless connected?

    {
      journal_mode: connection.get('PRAGMA journal_mode'),
      synchronous: connection.get('PRAGMA synchronous'),
      busy_timeout: connection.get('PRAGMA busy_timeout'),
      foreign_keys: connection.get('PRAGMA foreign_keys')
    }
  end
end

