import { getDatabase, generateId } from '../database';
import type { Expense, ExpenseCategory } from '../../types';

export class ExpenseRepository {
  async create(expense: Omit<Expense, 'id'>): Promise<Expense> {
    const db = await getDatabase();
    const id = generateId();
    await db.runAsync(
      `INSERT INTO expenses (id, household_id, user_id, amount, category, note, date, is_common)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [id, expense.householdId, expense.userId, expense.amount, expense.category, expense.note, expense.date, expense.isCommon ? 1 : 0]
    );
    return { id, ...expense };
  }

  async getById(id: string): Promise<Expense | null> {
    const db = await getDatabase();
    const row = await db.getFirstAsync<any>('SELECT * FROM expenses WHERE id = ?', [id]);
    return row ? this.mapRow(row) : null;
  }

  async getByHousehold(householdId: string, limit = 50): Promise<Expense[]> {
    const db = await getDatabase();
    const rows = await db.getAllAsync<any>(
      'SELECT * FROM expenses WHERE household_id = ? ORDER BY date DESC LIMIT ?',
      [householdId, limit]
    );
    return rows.map(this.mapRow);
  }

  async getByMonth(householdId: string, year: number, month: number): Promise<Expense[]> {
    const db = await getDatabase();
    const monthStr = `${year}-${String(month).padStart(2, '0')}`;
    const rows = await db.getAllAsync<any>(
      `SELECT * FROM expenses WHERE household_id = ? AND date LIKE ? ORDER BY date DESC`,
      [householdId, `${monthStr}%`]
    );
    return rows.map(this.mapRow);
  }

  async getTotalByCategory(householdId: string, year: number, month: number): Promise<Record<ExpenseCategory, number>> {
    const db = await getDatabase();
    const monthStr = `${year}-${String(month).padStart(2, '0')}`;
    const rows = await db.getAllAsync<any>(
      `SELECT category, SUM(amount) as total FROM expenses WHERE household_id = ? AND date LIKE ? GROUP BY category`,
      [householdId, `${monthStr}%`]
    );
    const result = {} as Record<ExpenseCategory, number>;
    for (const row of rows) {
      result[row.category as ExpenseCategory] = row.total;
    }
    return result;
  }

  async delete(id: string): Promise<void> {
    const db = await getDatabase();
    await db.runAsync('DELETE FROM expenses WHERE id = ?', [id]);
  }

  async deleteAll(householdId: string): Promise<void> {
    const db = await getDatabase();
    await db.runAsync('DELETE FROM expenses WHERE household_id = ?', [householdId]);
  }

  private mapRow(row: any): Expense {
    return {
      id: row.id,
      householdId: row.household_id,
      userId: row.user_id,
      amount: row.amount,
      category: row.category,
      note: row.note || '',
      date: row.date,
      isCommon: !!row.is_common,
    };
  }
}
