import sqlite3 from 'sqlite3';
import { open, Database } from 'sqlite';
import path from 'path';
import fs from 'fs';

// SQLite数据库配置
const DB_PATH = path.join(process.cwd(), 'database', 'goguryeo_video.db');
const INIT_SQL_PATH = path.join(process.cwd(), 'database', 'init.sql');

let db: Database | null = null;

/**
 * 初始化数据库连接
 */
export async function initDatabase(): Promise<Database> {
  try {
    // 确保数据库目录存在
    const dbDir = path.dirname(DB_PATH);
    if (!fs.existsSync(dbDir)) {
      fs.mkdirSync(dbDir, { recursive: true });
    }

    // 打开数据库连接
    db = await open({
      filename: DB_PATH,
      driver: sqlite3.Database
    });

    console.log('SQLite数据库连接成功');

    // 检查是否需要初始化数据库
    await initializeTables();

    return db;
  } catch (error) {
    console.error('数据库连接失败:', error);
    throw error;
  }
}

/**
 * 初始化数据库表结构
 */
async function initializeTables(): Promise<void> {
  if (!db) {
    throw new Error('数据库连接未初始化');
  }

  try {
    // 检查用户表是否存在
    const userTableExists = await db.get(
      "SELECT name FROM sqlite_master WHERE type='table' AND name='users'"
    );

    if (!userTableExists) {
      console.log('初始化数据库表结构...');
      
      // 读取并执行初始化SQL脚本
      if (fs.existsSync(INIT_SQL_PATH)) {
        const initSql = fs.readFileSync(INIT_SQL_PATH, 'utf8');
        
        // 清理SQL内容，移除注释和空行
        const cleanSql = initSql
          .split('\n')
          .filter(line => !line.trim().startsWith('--') && line.trim().length > 0)
          .join('\n');
        
        // 分割SQL语句并执行
        const statements = cleanSql
          .split(';')
          .map(stmt => stmt.trim())
          .filter(stmt => stmt.length > 0);

        for (const statement of statements) {
          if (statement.trim()) {
            try {
              await db.exec(statement);
              console.log('执行SQL成功:', statement.substring(0, 50) + '...');
            } catch (error) {
              console.error('执行SQL失败:', statement.substring(0, 50) + '...', error);
              throw error;
            }
          }
        }
        
        console.log('数据库表结构初始化完成');
      } else {
        console.warn('初始化SQL文件不存在:', INIT_SQL_PATH);
      }
    }
  } catch (error) {
    console.error('初始化数据库表结构失败:', error);
    throw error;
  }
}

/**
 * 获取数据库连接
 */
export function getDatabase(): Database {
  if (!db) {
    throw new Error('数据库连接未初始化，请先调用 initDatabase()');
  }
  return db;
}

/**
 * 关闭数据库连接
 */
export async function closeDatabase(): Promise<void> {
  if (db) {
    await db.close();
    db = null;
    console.log('数据库连接已关闭');
  }
}

/**
 * 执行查询
 */
export async function query(sql: string, params: any[] = []): Promise<any[]> {
  const database = getDatabase();
  return await database.all(sql, params);
}

/**
 * 执行单条查询
 */
export async function queryOne(sql: string, params: any[] = []): Promise<any> {
  const database = getDatabase();
  return await database.get(sql, params);
}

/**
 * 执行插入/更新/删除操作
 */
export async function execute(sql: string, params: any[] = []): Promise<any> {
  const database = getDatabase();
  return await database.run(sql, params);
}

export default {
  initDatabase,
  getDatabase,
  closeDatabase,
  query,
  queryOne,
  execute
};