// 本地数据库管理模块

const DB_NAME = 'accounting.db'
const DB_VERSION = 1
let database = null

// 跨平台数据库兼容性处理
function openDatabaseSync(name, version, displayName, estimatedSize) {
  // #ifdef APP-PLUS
  // App环境使用plus.sqlite
  return plus.sqlite.openSQLiteSync({
    name: name,
    path: `_doc/${name}`
  })
  // #endif

  // #ifdef H5
  // H5环境使用Web SQL（如果支持）
  if (window.openDatabase) {
    return window.openDatabase(name, version, displayName, estimatedSize)
  } else {
    throw new Error('浏览器不支持Web SQL数据库，请使用支持的浏览器或App版本')
  }
  // #endif

  // #ifdef MP
  // 小程序环境暂不支持本地SQLite数据库
  throw new Error('小程序环境暂不支持离线数据库功能')
  // #endif
}

// 初始化数据库
export async function initDatabase() {
  return new Promise((resolve, reject) => {
    try {
    // 打开数据库
    database = openDatabaseSync(DB_NAME, DB_VERSION, '记账数据库', 10 * 1024 * 1024)
    
    // 创建表结构
    createTables().then(() => {
      // 插入预设分类数据
      insertDefaultCategories().then(() => {
        resolve(true)
      }).catch(err => {
        console.error('插入预设分类失败:', err)
        reject(err)
      })
    }).catch(err => {
      console.error('创建表结构失败:', err)
      reject(err)
    })
  } catch (error) {
    console.error('初始化数据库失败:', error)
    reject(error)
  }
  })
}

// 创建表结构
async function createTables() {
  return new Promise((resolve, reject) => {
    try {
    const createTableSqls = [
      // 用户表
      `CREATE TABLE IF NOT EXISTS user (
        id TEXT PRIMARY KEY,
        username TEXT,
        nickname TEXT,
        is_offline INTEGER DEFAULT 0,
        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        update_time DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
      
      // 分类表
      `CREATE TABLE IF NOT EXISTS category (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id TEXT,
        name TEXT NOT NULL,
        icon TEXT,
        type INTEGER NOT NULL,
        sort_order INTEGER DEFAULT 0,
        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        update_time DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
      
      // 账单表
      `CREATE TABLE IF NOT EXISTS bill (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id TEXT NOT NULL,
        category_id INTEGER NOT NULL,
        amount REAL NOT NULL,
        description TEXT,
        bill_date DATE NOT NULL,
        type INTEGER NOT NULL,
        is_synced INTEGER DEFAULT 0,
        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        update_time DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
      
      // 账户表
      `CREATE TABLE IF NOT EXISTS account (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id TEXT NOT NULL,
        name TEXT NOT NULL,
        initial_balance REAL NOT NULL DEFAULT 0.00,
        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(user_id, name)
      )`,
      
      // 预算表
      `CREATE TABLE IF NOT EXISTS budget (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id TEXT NOT NULL,
        year INTEGER NOT NULL,
        month INTEGER NOT NULL,
        amount REAL NOT NULL DEFAULT 0.00,
        is_synced INTEGER DEFAULT 0,
        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(user_id, year, month)
      )`
    ]
    
    // 执行事务创建所有表
    database.transaction((tx) => {
      for (const sql of createTableSqls) {
        tx.executeSql(sql)
      }
    })
    
    resolve(true)
  } catch (error) {
    console.error('创建表结构失败:', error)
    reject(error)
  }
  })
}

// 插入预设分类数据
async function insertDefaultCategories() {
  return new Promise((resolve, reject) => {
    try {
    // 检查是否已存在预设分类
    const checkSql = 'SELECT COUNT(*) as count FROM category WHERE user_id IS NULL'
    database.transaction((tx) => {
      tx.executeSql(checkSql, [], (tx, result) => {
        if (result.rows[0].count === 0) {
          // 插入预设支出分类
          const expenseCategories = [
            { name: '餐饮', icon: '🍔', type: 1, sort_order: 1 },
            { name: '交通', icon: '🚗', type: 1, sort_order: 2 },
            { name: '购物', icon: '🛍️', type: 1, sort_order: 3 },
            { name: '娱乐', icon: '🎬', type: 1, sort_order: 4 },
            { name: '医疗', icon: '🏥', type: 1, sort_order: 5 },
            { name: '教育', icon: '📚', type: 1, sort_order: 6 },
            { name: '住房', icon: '🏠', type: 1, sort_order: 7 },
            { name: '水电', icon: '💡', type: 1, sort_order: 8 },
            { name: '通讯', icon: '📱', type: 1, sort_order: 9 },
            { name: '其他', icon: '📦', type: 1, sort_order: 10 }
          ]
          
          // 插入预设收入分类
          const incomeCategories = [
            { name: '工资', icon: '💰', type: 2, sort_order: 1 },
            { name: '奖金', icon: '🎁', type: 2, sort_order: 2 },
            { name: '投资', icon: '📈', type: 2, sort_order: 3 },
            { name: '兼职', icon: '💼', type: 2, sort_order: 4 },
            { name: '其他', icon: '📦', type: 2, sort_order: 5 }
          ]
          
          // 执行事务插入所有预设分类
          database.transaction((tx) => {
            for (const cat of expenseCategories) {
              const sql = `INSERT INTO category (user_id, name, icon, type, sort_order) VALUES (NULL, ?, ?, ?, ?)`
              tx.executeSql(sql, [cat.name, cat.icon, cat.type, cat.sort_order])
            }
            
            for (const cat of incomeCategories) {
              const sql = `INSERT INTO category (user_id, name, icon, type, sort_order) VALUES (NULL, ?, ?, ?, ?)`
              tx.executeSql(sql, [cat.name, cat.icon, cat.type, cat.sort_order])
            }
          })
        }
        resolve(true)
      }, (tx, error) => {
        console.error('检查预设分类失败:', error)
        reject(error)
      })
    })
  } catch (error) {
    console.error('插入预设分类失败:', error)
    reject(error)
  }
  })
}

// 创建离线用户
export async function createOfflineUser() {
  return new Promise((resolve, reject) => {
    try {
    // 检查数据库是否已初始化
    if (!database) {
      console.error('数据库未初始化，正在初始化...')
      initDatabase().then(() => {
        // 数据库初始化成功后，递归调用自己
        createOfflineUser().then(resolve).catch(reject)
      }).catch(err => {
        console.error('数据库初始化失败:', err)
        reject(new Error('数据库初始化失败: ' + err.message))
      })
      return
    }

    const userId = 'local_user'
    const username = '离线用户'
    const nickname = '离线用户'

    // 检查是否已存在离线用户
    const checkSql = 'SELECT * FROM user WHERE id = ?'
    database.transaction((tx) => {
      tx.executeSql(checkSql, [userId], (tx, result) => {
        if (result.rows.length === 0) {
          // 创建离线用户
          const insertSql = `INSERT INTO user (id, username, nickname, is_offline) VALUES (?, ?, ?, 1)`
          tx.executeSql(insertSql, [userId, username, nickname], (tx, result) => {
            // 创建默认账户
            const accountSql = `INSERT INTO account (user_id, name, initial_balance) VALUES (?, '默认账户', 0.00)`
            tx.executeSql(accountSql, [userId], (tx, result) => {
              resolve(userId)
            }, (tx, error) => {
              console.error('创建默认账户失败:', error)
              reject(error)
            })
          }, (tx, error) => {
            console.error('创建离线用户失败:', error)
            reject(error)
          })
        } else {
          resolve(userId)
        }
      }, (tx, error) => {
        console.error('检查离线用户失败:', error)
        reject(error)
      })
    })
  } catch (error) {
    console.error('创建离线用户失败:', error)
    reject(error)
  }
  })
}

// 数据库操作封装
const db = {
  // 执行查询
  query: (sql, params = []) => {
    return new Promise((resolve, reject) => {
      try {
        database.transaction((tx) => {
          tx.executeSql(sql, params, (tx, result) => {
            const rows = []
            for (let i = 0; i < result.rows.length; i++) {
              rows.push(result.rows[i])
            }
            resolve(rows)
          }, (tx, error) => {
            console.error('查询失败:', error)
            reject(error)
          })
        })
      } catch (error) {
        console.error('查询失败:', error)
        reject(error)
      }
    })
  },
  
  // 执行更新（INSERT, UPDATE, DELETE）
  update: (sql, params = []) => {
    return new Promise((resolve, reject) => {
      try {
        database.transaction((tx) => {
          tx.executeSql(sql, params, (tx, result) => {
            resolve(result.rowsAffected)
          }, (tx, error) => {
            console.error('更新失败:', error)
            reject(error)
          })
        })
      } catch (error) {
        console.error('更新失败:', error)
        reject(error)
      }
    })
  },
  
  // 执行事务
  transaction: (callback) => {
    return new Promise((resolve, reject) => {
      try {
        database.transaction((tx) => {
          callback(tx)
          resolve(true)
        }, (error) => {
          console.error('事务执行失败:', error)
          reject(error)
        })
      } catch (error) {
        console.error('事务执行失败:', error)
        reject(error)
      }
    })
  },
  
  // 获取数据库实例
  getInstance: () => {
    if (!database) {
      throw new Error('数据库未初始化')
    }
    return database
  }
}

export { db }