server/src/db.ts

import { createClient, Client } from '@libsql/client';
import path from 'path';
import crypto from 'crypto';
import dotenv from 'dotenv';

dotenv.config();

// Compatibility wrapper to make LibSQL behave like the previous sqlite-wrapper
export interface Database {
  get: (sql: string, params?: any[]) => Promise<any>;
  all: (sql: string, params?: any[]) => Promise<any[]>;
  run: (sql: string, params?: any[]) => Promise<{ lastID: number | bigint }>;
  exec: (sql: string) => Promise<void>;
}

let dbInstance: Database | null = null;

export async function getDb(): Promise<Database> {
  if (dbInstance) return dbInstance;

  const url = process.env.DATABASE_URL || `file:${path.resolve(__dirname, '../database.sqlite')}`;
  const authToken = process.env.DATABASE_AUTH_TOKEN;

  const client = createClient({
    url,
    authToken,
  });

  const sanitizeParams = (params: any[]): any[] => {
    return params.map(p => p === undefined ? null : p);
  };

  const sanitizeResult = (row: any) => {
    if (!row) return row;
    const newRow = { ...row };
    for (const [key, value] of Object.entries(newRow)) {
      if (typeof value === 'bigint') {
        newRow[key] = Number(value);
      }
    }
    return newRow;
  };

  const db: Database = {
    get: async (sql: string, params: any[] = []) => {
      const res = await client.execute({ sql, args: sanitizeParams(params) });
      return res.rows[0] ? sanitizeResult(res.rows[0]) : undefined;
    },
    all: async (sql: string, params: any[] = []) => {
      const res = await client.execute({ sql, args: sanitizeParams(params) });
      return res.rows.map(row => sanitizeResult(row));
    },
    run: async (sql: string, params: any[] = []) => {
      const res = await client.execute({ sql, args: sanitizeParams(params) });
      return { lastID: Number(res.lastInsertRowid || 0) };
    },
    exec: async (sql: string) => {
      await client.batch(sql.split(';').filter(s => s.trim()), 'write');
    }
  };

  dbInstance = db;

  // Initialize tables
  await db.exec(`
    CREATE TABLE IF NOT EXISTS wallets (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      type TEXT NOT NULL,
      currency TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS transactions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      type TEXT NOT NULL,
      amount REAL NOT NULL,
      currency TEXT NOT NULL,
      wallet_id INTEGER,
      to_wallet_id INTEGER,
      category TEXT,
      note TEXT,
      date TEXT NOT NULL,
      country_id TEXT,
      FOREIGN KEY(wallet_id) REFERENCES wallets(id),
      FOREIGN KEY(to_wallet_id) REFERENCES wallets(id)
    );

    CREATE TABLE IF NOT EXISTS exchanges (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      from_amount REAL NOT NULL,
      from_currency TEXT NOT NULL,
      to_amount REAL NOT NULL,
      to_currency TEXT NOT NULL,
      rate REAL NOT NULL,
      date TEXT NOT NULL,
      note TEXT,
      from_wallet_id INTEGER,
      to_wallet_id INTEGER,
      FOREIGN KEY(from_wallet_id) REFERENCES wallets(id),
      FOREIGN KEY(to_wallet_id) REFERENCES wallets(id)
    );

    CREATE TABLE IF NOT EXISTS loans (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      person TEXT NOT NULL,
      type TEXT NOT NULL,
      amount REAL NOT NULL,
      currency TEXT NOT NULL,
      paid REAL DEFAULT 0,
      date TEXT NOT NULL,
      note TEXT
    );

    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT NOT NULL UNIQUE,
      password_hash TEXT NOT NULL,
      salt TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS trusted_devices (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      device_id TEXT NOT NULL,
      device_name TEXT NOT NULL,
      last_used TEXT NOT NULL,
      FOREIGN KEY(user_id) REFERENCES users(id)
    );
  `);

  // Run migrations safely
  const runMigration = async (sql: string) => {
    try { await db.exec(sql); } catch (e) { /* ignore */ }
  };

  await runMigration(`ALTER TABLE exchanges ADD COLUMN from_wallet_id INTEGER;`);
  await runMigration(`ALTER TABLE exchanges ADD COLUMN to_wallet_id INTEGER;`);
  await runMigration(`ALTER TABLE transactions ADD COLUMN country_id TEXT;`);

  const requiredWallets = [
    { name: 'Cash USD', type: 'cash', currency: 'USD' },
    { name: 'Cash Dinar', type: 'cash', currency: 'IQD' },
    { name: 'USDT', type: 'crypto', currency: 'USD' },
    { name: 'FIB', type: 'bank', currency: 'IQD' },
    { name: 'FastPay', type: 'ewallet', currency: 'IQD' },
    { name: 'WeChat', type: 'ewallet', currency: 'RMB' },
    { name: 'Alipay', type: 'ewallet', currency: 'RMB' },
    { name: 'Super Qi', type: 'ewallet', currency: 'IQD' },
    { name: 'KJ Wallets', type: 'bank', currency: 'USD' }
  ];

  for (const w of requiredWallets) {
      const existing = await db.get('SELECT id FROM wallets WHERE name = ?', [w.name]);
      if (!existing) {
          await db.run(
              'INSERT INTO wallets (name, type, currency) VALUES (?, ?, ?)',
              [w.name, w.type, w.currency]
          );
      }
  }

  // Seed default user 'amez'
  const defaultUser = 'amez';
  const defaultPass = '902553';
  const existingUser = await db.get('SELECT id FROM users WHERE username = ?', [defaultUser]);
  
  if (!existingUser) {
    const salt = crypto.randomBytes(16).toString('hex');
    const hash = crypto.pbkdf2Sync(defaultPass, salt, 1000, 64, 'sha512').toString('hex');
    
    await db.run(
      'INSERT INTO users (username, password_hash, salt) VALUES (?, ?, ?)',
      [defaultUser, hash, salt]
    );
  }

  return dbInstance;
}