CREATE TABLE IF NOT EXISTS nii_advance_payments (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  year        INTEGER NOT NULL,
  month       INTEGER NOT NULL,
  amount      NUMERIC(12, 2) NOT NULL,
  paid_at     DATE NOT NULL,
  notes       TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT nii_advances_month_range CHECK (month BETWEEN 1 AND 12),
  CONSTRAINT nii_advance_payments_tenant_user_year_month_unique UNIQUE (tenant_id, user_id, year, month)
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS idx_nii_advances_lookup
  ON nii_advance_payments (tenant_id, user_id, year);
