integratedhub/services/expenseService.ts

// services/expenseService.ts
// Handle property expense management and reporting

import { and, between, desc, eq } from "drizzle-orm";
import { db } from "../db/db.ts";
import { property_expenses } from "../db/schema.ts";

// Use the type from the schema - this matches what Drizzle returns
export type PropertyExpense = typeof property_expenses.$inferSelect;

export interface ExpenseSummary {
  total: number;
  byCategory: Record<string, number>;
  byMonth: Record<string, number>;
}

/**
 * Create a new property expense record
 */
export async function createExpense(
  property_id: string,
  data: {
    category: string;
    amount: number;
    description?: string;
    expense_date: number;
    payment_method?: string;
    vendor?: string;
    receipt_url?: string;
    is_recurring?: boolean;
    recurring_frequency?: string;
  },
) {
  const now = Date.now();
  return await db.insert(property_expenses)
    .values({
      id: crypto.randomUUID(),
      property_id,
      category: data.category,
      amount: data.amount,
      description: data.description || null,
      expense_date: data.expense_date,
      payment_method: data.payment_method || null,
      vendor: data.vendor || null,
      receipt_url: data.receipt_url || null,
      is_recurring: data.is_recurring || false,
      recurring_frequency: data.recurring_frequency || null,
      created_on: now,
      updated_on: now,
    })
    .returning();
}

/**
 * Get expense by ID
 */
export async function getExpenseById(
  expenseId: string,
): Promise<PropertyExpense | null> {
  const result = await db.select()
    .from(property_expenses)
    .where(eq(property_expenses.id, expenseId));
  return result[0] || null;
}

/**
 * Get all expenses for a property
 */
export async function getExpensesByProperty(
  property_id: string,
): Promise<PropertyExpense[]> {
  return await db.select()
    .from(property_expenses)
    .where(eq(property_expenses.property_id, property_id))
    .orderBy(desc(property_expenses.expense_date));
}

/**
 * Get expenses for a property within a date range
 */
export async function getExpensesByPropertyAndDateRange(
  property_id: string,
  startDate: number,
  endDate: number,
): Promise<PropertyExpense[]> {
  return await db.select()
    .from(property_expenses)
    .where(
      and(
        eq(property_expenses.property_id, property_id),
        between(property_expenses.expense_date, startDate, endDate),
      ),
    )
    .orderBy(desc(property_expenses.expense_date));
}

/**
 * Get expenses by category for a property
 */
export async function getExpensesByPropertyAndCategory(
  property_id: string,
  category: string,
): Promise<PropertyExpense[]> {
  return await db.select()
    .from(property_expenses)
    .where(
      and(
        eq(property_expenses.property_id, property_id),
        eq(property_expenses.category, category),
      ),
    )
    .orderBy(desc(property_expenses.expense_date));
}

/**
 * Get recurring expenses for a property
 */
export async function getRecurringExpenses(
  property_id: string,
): Promise<PropertyExpense[]> {
  return await db.select()
    .from(property_expenses)
    .where(
      and(
        eq(property_expenses.property_id, property_id),
        eq(property_expenses.is_recurring, true),
      ),
    )
    .orderBy(desc(property_expenses.expense_date));
}

/**
 * Update an expense
 */
export async function updateExpense(
  expenseId: string,
  updates: Partial<Omit<PropertyExpense, "id" | "property_id" | "created_on">>,
) {
  const now = Date.now();
  return await db.update(property_expenses)
    .set({ ...updates, updated_on: now })
    .where(eq(property_expenses.id, expenseId))
    .returning();
}

/**
 * Delete an expense
 */
export async function deleteExpense(expenseId: string) {
  return await db.delete(property_expenses)
    .where(eq(property_expenses.id, expenseId))
    .returning();
}

/**
 * Calculate expense summary for a property
 */
export async function getExpenseSummary(
  property_id: string,
  startDate?: number,
  endDate?: number,
): Promise<ExpenseSummary> {
  const expenses = startDate && endDate
    ? await getExpensesByPropertyAndDateRange(property_id, startDate, endDate)
    : await getExpensesByProperty(property_id);

  const total = expenses.reduce((sum, exp) => sum + (exp.amount || 0), 0);

  const byCategory: Record<string, number> = {};
  const byMonth: Record<string, number> = {};

  for (const exp of expenses) {
    const category = exp.category || "uncategorized";
    byCategory[category] = (byCategory[category] || 0) + (exp.amount || 0);

    if (exp.expense_date) {
      const date = new Date(exp.expense_date);
      const monthKey = `${date.getFullYear()}-${
        String(date.getMonth() + 1).padStart(2, "0")
      }`;
      byMonth[monthKey] = (byMonth[monthKey] || 0) + (exp.amount || 0);
    }
  }

  return { total, byCategory, byMonth };
}

/**
 * Get total expenses for a property (all time or within date range)
 */
export async function getTotalExpenses(
  property_id: string,
  startDate?: number,
  endDate?: number,
): Promise<number> {
  const expenses = startDate && endDate
    ? await getExpensesByPropertyAndDateRange(property_id, startDate, endDate)
    : await getExpensesByProperty(property_id);

  return expenses.reduce((sum, exp) => sum + (exp.amount || 0), 0);
}