/**
 * nii_advance_payments — bituach-leumi (spec 175, wave-15).
 * Tracks monthly NII advance payments made by self-employed users.
 */
import { pgTable, uuid, text, integer, numeric, timestamp, date, index, check } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'

export const niiAdvancePayments = pgTable(
  'nii_advance_payments',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    year: integer('year').notNull(),
    month: integer('month').notNull(),
    amount: numeric('amount', { precision: 12, scale: 2 }).notNull(),
    paidAt: date('paid_at').notNull(),
    notes: text('notes'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index('idx_nii_advances_lookup').on(t.tenantId, t.userId, t.year),
    check('nii_advances_month_range', sql`${t.month} BETWEEN 1 AND 12`),
  ],
)

export type NiiAdvancePaymentRow = typeof niiAdvancePayments.$inferSelect
export type NewNiiAdvancePayment = typeof niiAdvancePayments.$inferInsert