const sqlite3 = require('sqlite3').verbose();
const path = require('path');

const dbPath = path.join(__dirname, 'maa_test.db');

// 建立資料庫連線
const db = new sqlite3.Database(dbPath, (err) => {
  if (err) {
    console.error('❌ 資料庫連線失敗:', err.message);
  } else {
    console.log('✅ 已連接到SQLite資料庫');
  }
});

// 初始化資料庫表格
const initDatabase = () => {
  return new Promise((resolve, reject) => {
    db.serialize(() => {
      // 建立專案表格
      db.run(`
        CREATE TABLE IF NOT EXISTS projects (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL,
          description TEXT,
          status TEXT DEFAULT 'active',
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 建立問題表格
      db.run(`
        CREATE TABLE IF NOT EXISTS issues (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          project_id INTEGER,
          title TEXT NOT NULL,
          description TEXT,
          status TEXT DEFAULT 'open',
          priority TEXT DEFAULT 'medium',
          assignee TEXT,
          reporter TEXT,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (project_id) REFERENCES projects (id)
        )
      `);

      // 建立問題評論表格
      db.run(`
        CREATE TABLE IF NOT EXISTS issue_comments (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          issue_id INTEGER,
          author TEXT NOT NULL,
          content TEXT NOT NULL,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (issue_id) REFERENCES issues (id)
        )
      `);

      // 插入範例資料
      db.run(`
        INSERT OR IGNORE INTO projects (id, name, description, status) VALUES 
        (1, 'MAA核心功能', 'MAA自動化測試核心功能開發', 'active'),
        (2, 'UI介面優化', '使用者介面改進和優化', 'active'),
        (3, '效能測試', '系統效能測試和優化', 'planning')
      `);

      db.run(`
        INSERT OR IGNORE INTO issues (id, project_id, title, description, status, priority, assignee, reporter) VALUES 
        (1, 1, '登入功能異常', '使用者無法正常登入系統', 'open', 'high', 'developer1', 'tester1'),
        (2, 1, '資料同步問題', '資料同步時出現延遲', 'in_progress', 'medium', 'developer2', 'tester2'),
        (3, 2, '按鈕樣式調整', '需要調整按鈕的顏色和大小', 'open', 'low', 'designer1', 'product1'),
        (4, 2, '響應式設計', '手機版介面需要優化', 'open', 'medium', 'designer2', 'product2'),
        (5, 3, '載入速度優化', '頁面載入速度過慢', 'closed', 'high', 'developer3', 'tester3')
      `);

      db.run(`
        INSERT OR IGNORE INTO issue_comments (issue_id, author, content) VALUES 
        (1, 'developer1', '已確認問題，正在修復中'),
        (2, 'developer2', '問題已定位，預計明天完成修復'),
        (3, 'designer1', '設計稿已完成，等待開發實作'),
        (4, 'designer2', '需要更多時間進行響應式設計'),
        (5, 'developer3', '已優化完成，效能提升50%')
      `, (err) => {
        if (err) {
          console.error('❌ 範例資料插入失敗:', err);
          reject(err);
        } else {
          console.log('✅ 資料庫初始化完成');
          resolve();
        }
      });
    });
  });
};

module.exports = { db, initDatabase };