/**
 * Mileage logbook schema — mileage-logbook / יומן נסיעות (wave-10).
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 *   mileage_entries — one row per vehicle trip
 *
 * DB conventions:
 *   - UUID PK .defaultRandom()
 *   - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 *   - Money: NUMERIC
 *   - Enums: text() + check — NEVER pgEnum
 *   - FKs to tenants/users/customers/projects via .references()
 *
 * Israeli tax context:
 *   Business trips are deductible at the ITA standard rate (e.g. ₪1.52/km in 2024).
 *   reimbursementAmount is pre-computed at entry time and stored for reporting.
 */
import { pgTable, uuid, text, boolean, numeric, timestamp, check } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'
import { customers } from './customers'
import { projects } from './projects'

// ── mileage_entries ───────────────────────────────────────────────────────────

export const mileageEntries = pgTable(
  'mileage_entries',
  {
    id: uuid('id').primaryKey().defaultRandom(),

    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),

    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),

    /** Trip date (TIMESTAMPTZ, stored at midnight UTC or local trip start). */
    date: timestamp('date', { withTimezone: true }).notNull(),

    /** Origin location / address. */
    fromLocation: text('from_location').notNull(),

    /** Destination location / address. */
    toLocation: text('to_location').notNull(),

    /** Trip distance in kilometres. */
    distanceKm: numeric('distance_km', { precision: 8, scale: 2 }).notNull(),

    /** Purpose / description of the trip. */
    purpose: text('purpose').notNull(),

    /** Vehicle identifier (plate number or free-text label). */
    vehicleId: text('vehicle_id'),

    /** Whether the trip qualifies as a business trip for tax purposes. */
    isBusinessTrip: boolean('is_business_trip').notNull().default(true),

    /** Optional customer link (billable trip). */
    customerId: uuid('customer_id').references(() => customers.id, { onDelete: 'set null' }),

    /** Optional project link. */
    projectId: uuid('project_id').references(() => projects.id, { onDelete: 'set null' }),

    /**
     * Pre-computed reimbursement amount in ILS (distanceKm × ITA rate).
     * Stored so reports remain stable even if the rate changes later.
     */
    reimbursementAmount: numeric('reimbursement_amount', { precision: 12, scale: 2 }),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    distanceCheck: check(
      'mileage_entries_distance_check',
      sql`${t.distanceKm} > 0`,
    ),
  }),
)

export type MileageEntryRow = typeof mileageEntries.$inferSelect
export type NewMileageEntry = typeof mileageEntries.$inferInsert