import { Database } from "sqlite-async";
import { Transaction, TransactionsQuery, TransactionType } from "../schema";
import { PaginatedTransactions } from "../../generated";

export class TransactionService {
  constructor(private db: Database) {}

  getTransactions(
    userId: number,
    filters: TransactionsQuery,
  ): Promise<Transaction[]> {
    const [query, params] = this.buildTransactionsQuery(userId, filters);

    const { page, order, limit, sort } = filters;
    const offset = (page - 1) * limit;

    params.push(limit, offset);

    const finalQuery = query.concat(
      ` ORDER BY ${sort} ${order} LIMIT ? OFFSET ?`,
    );

    return this.db.all<Transaction>(finalQuery, params);
  }

  async countTransactions(
    userId: number,
    filters: TransactionsQuery,
  ): Promise<number> {
    const [query, params] = this.buildTransactionsQuery(userId, filters);

    const { count } = await this.db.get<{ count: number }>(
      query.replace("SELECT *", "SELECT COUNT(*) as count"),
      params,
    );

    return count;
  }

  async paginatedTransactions(
    userId: number,
    filters: TransactionsQuery,
  ): Promise<PaginatedTransactions> {
    const [transactions, total] = await Promise.all([
      this.getTransactions(userId, filters),
      this.countTransactions(userId, filters),
    ]);

    const { page, limit } = filters;

    return {
      data: transactions.map(({ user_id, account_id, ...rest }) => ({
        userId: user_id,
        accountId: account_id,
        ...rest,
      })),
      meta: {
        total,
        page,
        limit,
        hasMore: total > (page - 1) * limit + transactions.length,
      },
    };
  }

  private buildTransactionsQuery(
    userId: number,
    filters: TransactionsQuery,
  ): [string, Array<string | number>] {
    const { search, type, accountId } = filters;

    let query = "SELECT * FROM transactions WHERE user_id = ?";

    let params: Array<string | number> = [userId];

    if (search.length) {
      query += " AND (description LIKE ? OR type LIKE ?)";
      params.push(`%${search}%`, `%${search}%`);
    }

    if (accountId) {
      query += " AND (account_id = ?)";
      params.push(accountId);
    }

    if (type) {
      query += " AND (type = ?)";
      params.push(type);
    }

    return [query, params];
  }

  getTransactionTypes(
    userId: number,
    accountId?: number,
  ): Promise<TransactionType[]> {
    let query = `
      SELECT    count(*) as count,
                type as name
      FROM      transactions
      WHERE     user_id = ?
    `;

    let params: Array<string | number> = [userId];

    if (accountId) {
      query += " AND (account_id = ?)";
      params.push(accountId);
    }

    query += " GROUP BY type ORDER BY count DESC";

    return this.db.all<TransactionType>(query, params);
  }
}