import initSqlJs, { Database } from 'sql.js';

let db: Database | null = null;

export default {
  initialize: async () => {
    if (db) {
      return db;
    }

    try {
      const SQL = await initSqlJs({
        locateFile: (file) => `https://sql.js.org/dist/${file}`,
      });
      
      db = new SQL.Database();

      // Create tables if they don't exist
      db.exec(`
        CREATE TABLE IF NOT EXISTS transactions (
          id TEXT PRIMARY KEY,
          title TEXT NOT NULL,
          amount REAL NOT NULL,
          type TEXT NOT NULL,
          category TEXT NOT NULL,
          date TEXT NOT NULL,
          note TEXT,
          message TEXT,
          product_id TEXT,
          quantity INTEGER
        );
        
        CREATE TABLE IF NOT EXISTS products (
          id TEXT PRIMARY KEY,
          name TEXT NOT NULL,
          sku TEXT NOT NULL,
          quantity INTEGER NOT NULL,
          price REAL NOT NULL,
          category TEXT NOT NULL,
          last_updated TEXT NOT NULL,
          reorder_point INTEGER DEFAULT 5,
          trend TEXT DEFAULT 'stable',
          demand TEXT DEFAULT 'medium'
        );
      `);

      console.info('SQLite database initialized successfully');
      
      return db;
    } catch (err) {
      console.error('Failed to initialize SQLite database:', err);
      throw err;
    }
  },
  
  exec: (sql: string, params: any[] = []) => {
    if (!db) {
      throw new Error('Database not initialized. Call initialize() first.');
    }

    try {
      db.run(sql, params);
      
      const selectStatement = db.prepare(sql);
      
      if (selectStatement.sql.startsWith('SELECT')) {
        selectStatement.bind(params);
        
        const result = [];
        while (selectStatement.step()) {
          const row = selectStatement.getAsObject();
          result.push(row);
        }
        selectStatement.free();
        
        return result;
      }
      
      return [];
    } catch (error: any) {
      console.error(`Failed to execute SQL: ${sql}`, error);
      throw error;
    }
  }
};