/**
 * Mileage logbook query helpers — mileage-logbook / יומן נסיעות (wave-10).
 *
 * All functions are tenant-scoped.
 * Israeli ITA reimbursement rate is applied at entry creation time and stored.
 */
import { eq, and, gte, lte, desc, sql } from 'drizzle-orm'
import { z } from 'zod'
import type { Db } from '../client'
import { mileageEntries } from '../schema/mileage'

// ── ITA rate (2024 standard) ──────────────────────────────────────────────────

/** Standard ITA reimbursement rate in ILS per km (תקנות שימוש ברכב). */
export const ITA_RATE_PER_KM = 1.52

// ── Zod validation schemas ────────────────────────────────────────────────────

export const createMileageEntrySchema = z.object({
  date: z.string().datetime({ offset: true }),
  fromLocation: z.string().min(1).max(300),
  toLocation: z.string().min(1).max(300),
  distanceKm: z.number().positive(),
  purpose: z.string().min(1).max(500),
  vehicleId: z.string().max(50).nullable().optional(),
  isBusinessTrip: z.boolean().default(true),
  customerId: z.string().uuid().nullable().optional(),
  projectId: z.string().uuid().nullable().optional(),
})

export type CreateMileageEntryInput = z.infer<typeof createMileageEntrySchema>

// ── Types ─────────────────────────────────────────────────────────────────────

export type MileageEntryRow = typeof mileageEntries.$inferSelect

export interface MileageSummary {
  year: number
  totalKm: number
  businessKm: number
  reimbursementTotal: number
  entryCount: number
}

// ── Query helpers ─────────────────────────────────────────────────────────────

/**
 * List mileage entries for a tenant/user, optionally filtered by date range.
 * Ordered by date DESC.
 */
export async function listMileageEntries(
  db: Db,
  tenantId: string,
  userId: string,
  startDate?: string, // ISO date YYYY-MM-DD
  endDate?: string,   // ISO date YYYY-MM-DD
): Promise<MileageEntryRow[]> {
  const conds = [
    eq(mileageEntries.tenantId, tenantId),
    eq(mileageEntries.userId, userId),
  ]

  if (startDate) {
    conds.push(gte(mileageEntries.date, new Date(startDate)))
  }
  if (endDate) {
    // Add 1 day to endDate so it's inclusive
    const end = new Date(endDate)
    end.setUTCDate(end.getUTCDate() + 1)
    conds.push(lte(mileageEntries.date, end))
  }

  return db
    .select()
    .from(mileageEntries)
    .where(and(...conds))
    .orderBy(desc(mileageEntries.date))
}

/**
 * Create a new mileage entry.
 * Reimbursement amount is computed automatically for business trips.
 */
export async function createMileageEntry(
  db: Db,
  tenantId: string,
  userId: string,
  data: CreateMileageEntryInput,
): Promise<MileageEntryRow> {
  const parsed = createMileageEntrySchema.parse(data)

  const reimbursementAmount =
    parsed.isBusinessTrip
      ? String((parsed.distanceKm * ITA_RATE_PER_KM).toFixed(2))
      : null

  const [row] = await db
    .insert(mileageEntries)
    .values({
      tenantId,
      userId,
      date: new Date(parsed.date),
      fromLocation: parsed.fromLocation,
      toLocation: parsed.toLocation,
      distanceKm: String(parsed.distanceKm),
      purpose: parsed.purpose,
      vehicleId: parsed.vehicleId ?? null,
      isBusinessTrip: parsed.isBusinessTrip,
      customerId: parsed.customerId ?? null,
      projectId: parsed.projectId ?? null,
      reimbursementAmount,
    })
    .returning()

  if (!row) throw new Error('Failed to create mileage entry')
  return row
}

/**
 * Annual summary for a tenant/user: total km, business km, reimbursement total.
 */
export async function getMileageSummary(
  db: Db,
  tenantId: string,
  userId: string,
  year: number,
): Promise<MileageSummary> {
  const yearStart = new Date(Date.UTC(year, 0, 1))
  const yearEnd = new Date(Date.UTC(year + 1, 0, 1))

  const result = await db
    .select({
      totalKm: sql<string>`COALESCE(SUM(${mileageEntries.distanceKm}), 0)`,
      businessKm: sql<string>`COALESCE(SUM(CASE WHEN ${mileageEntries.isBusinessTrip} THEN ${mileageEntries.distanceKm} ELSE 0 END), 0)`,
      reimbursementTotal: sql<string>`COALESCE(SUM(${mileageEntries.reimbursementAmount}), 0)`,
      entryCount: sql<number>`COUNT(*)::int`,
    })
    .from(mileageEntries)
    .where(
      and(
        eq(mileageEntries.tenantId, tenantId),
        eq(mileageEntries.userId, userId),
        gte(mileageEntries.date, yearStart),
        lte(mileageEntries.date, yearEnd),
      ),
    )

  const row = result[0]
  return {
    year,
    totalKm: parseFloat(row?.totalKm ?? '0'),
    businessKm: parseFloat(row?.businessKm ?? '0'),
    reimbursementTotal: parseFloat(row?.reimbursementTotal ?? '0'),
    entryCount: row?.entryCount ?? 0,
  }
}