/**
 * Tax rates seed data — admin-dashboard / tax-rates-seed-data spec.
 *
 * Full Israeli (IL) statutory non-VAT tax history. VAT stays in vat-rates.seed.ts.
 *
 * Coverage:
 *  - Corporate income tax: 12 rows, 1995-2018+ (מס חברות)
 *  - Default withholding tax (§164): 2 rows, 1995 + 2012+ (ניכוי מס במקור ברירת מחדל)
 *  - Personal income brackets: 7 years × 6 brackets = 42 rows
 *      2015, 2017, 2020, 2022, 2023, 2024, 2025
 *
 * Total: 56 rows for country_code='IL'.
 *
 * `rate` values are string NUMERIC(6,4) decimals matching the column type.
 * `threshold_ils` is the income ceiling (ILS/yr) for bracket types; NULL for
 * the top bracket and non-bracket types (corporate, withholding).
 *
 * Sources:
 *  - Corporate: Israeli Ministry of Finance / Tax Authority
 *    (Trading Economics, Wikipedia, PwC Tax Summaries 2025)
 *  - Withholding: Income Tax Ordinance (פקודת מס הכנסה) Section 164
 *  - Personal brackets 2025: PwC Israel Individual Tax Summary 2025
 *    (taxsummaries.pwc.com) — verified
 *  - Personal brackets 2015-2024: thresholds estimated from known
 *    inflation adjustments; rate structure confirmed
 *
 * IMPORTANT: created_by must reference a valid admin_users.id.
 * Call seedAdminRoles first (which creates the system admin user),
 * then pass that admin's id as createdById.
 */
import type { Db } from '../client'
import { taxRates } from '../schema/tax'

export interface TaxRateSeedRow {
  countryCode: string
  taxType: string
  rate: string
  effectiveFrom: string
  thresholdIls: string | null
  notes: string | null
}

export const IL_TAX_RATES: TaxRateSeedRow[] = [
  // ============================================================
  // CORPORATE INCOME TAX (מס חברות)
  // Source: Israeli Ministry of Finance / Tax Authority
  // ============================================================

  // 1995 row stays effective until the 2002 reduction.
  {
    countryCode: 'IL',
    taxType: 'corporate_income',
    rate: '0.3600',
    effectiveFrom: '1995-01-01',
    thresholdIls: null,
    notes: 'פקודת מס הכנסה — שיעור מס חברות 36%',
  },
  // 2002: reduced to 34%
  {
    countryCode: 'IL',
    taxType: 'corporate_income',
    rate: '0.3400',
    effectiveFrom: '2002-01-01',
    thresholdIls: null,
    notes: 'תיקון פקודת מס הכנסה — הפחתה ל-34%',
  },
  // 2003: reduced to 31%
  {
    countryCode: 'IL',
    taxType: 'corporate_income',
    rate: '0.3100',
    effectiveFrom: '2003-01-01',
    thresholdIls: null,
    notes: 'תיקון פקודת מס הכנסה — הפחתה ל-31%',
  },
  // 2004-2005: reduced to 29%
  {
    countryCode: 'IL',
    taxType: 'corporate_income',
    rate: '0.2900',
    effectiveFrom: '2004-01-01',
    thresholdIls: null,
    notes: 'תיקון פקודת מס הכנסה — הפחתה ל-29%',
  },
  // 2006: reduced to 27%
  {
    countryCode: 'IL',
    taxType: 'corporate_income',
    rate: '0.2700',
    effectiveFrom: '2006-01-01',
    thresholdIls: null,
    notes: 'תיקון פקודת מס הכנסה — הפחתה ל-27%',
  },
  // 2007: raised back to 29% (Trajtenberg-era reversal)
  {
    countryCode: 'IL',
    taxType: 'corporate_income',
    rate: '0.2900',
    effectiveFrom: '2007-01-01',
    thresholdIls: null,
    notes: 'תיקון פקודת מס הכנסה — העלאה ל-29%',
  },
  // 2008: reduced to 27%
  {
    countryCode: 'IL',
    taxType: 'corporate_income',
    rate: '0.2700',
    effectiveFrom: '2008-01-01',
    thresholdIls: null,
    notes: 'תיקון פקודת מס הכנסה — הפחתה ל-27%',
  },
  // 2009-2010: reduced to 26%
  {
    countryCode: 'IL',
    taxType: 'corporate_income',
    rate: '0.2600',
    effectiveFrom: '2009-01-01',
    thresholdIls: null,
    notes: 'תיקון פקודת מס הכנסה — הפחתה ל-26%',
  },
  // 2011: reduced to 24%
  {
    countryCode: 'IL',
    taxType: 'corporate_income',
    rate: '0.2400',
    effectiveFrom: '2011-01-01',
    thresholdIls: null,
    notes: 'תיקון פקודת מס הכנסה — הפחתה ל-24%',
  },
  // 2012 row stays effective until the 2017 reduction.
  {
    countryCode: 'IL',
    taxType: 'corporate_income',
    rate: '0.2500',
    effectiveFrom: '2012-01-01',
    thresholdIls: null,
    notes: 'תיקון פקודת מס הכנסה (ועדת טרכטנברג) — העלאה ל-25%',
  },
  // 2017: reduced to 24%
  {
    countryCode: 'IL',
    taxType: 'corporate_income',
    rate: '0.2400',
    effectiveFrom: '2017-01-01',
    thresholdIls: null,
    notes: 'תיקון 245 לפקודת מס הכנסה — הפחתה ל-24% (2017)',
  },
  // 2018-present: reduced to 23%
  {
    countryCode: 'IL',
    taxType: 'corporate_income',
    rate: '0.2300',
    effectiveFrom: '2018-01-01',
    thresholdIls: null,
    notes: 'תיקון 245 לפקודת מס הכנסה — הפחתה ל-23% (2018)',
  },

  // ============================================================
  // DEFAULT WITHHOLDING TAX (ניכוי מס במקור — ברירת מחדל)
  // Source: פקודת מס הכנסה סעיף 164 + תקנות ניכוי מס במקור
  // Rate applied when contractor has no exemption certificate.
  // SINGLE SOURCE OF TRUTH for NULL-rate fallback in contractor-payouts
  // and vendors-suppliers. Resolve via: latest effective_from <= bill date.
  // ============================================================

  // Pre-2012: 20% default for companies, inconsistently applied
  {
    countryCode: 'IL',
    taxType: 'withholding_default',
    rate: '0.2000',
    effectiveFrom: '1995-01-01',
    thresholdIls: null,
    notes: 'סעיף 164 פקודת מס הכנסה — שיעור ניכוי ברירת מחדל 20% לחברות',
  },
  // 2012-present: harmonised to 30% for all service providers/contractors without certificate
  {
    countryCode: 'IL',
    taxType: 'withholding_default',
    rate: '0.3000',
    effectiveFrom: '2012-01-01',
    thresholdIls: null,
    notes: 'סעיף 164 פקודת מס הכנסה — שיעור ניכוי מס במקור ברירת מחדל 30% לקבלנים/נותני שירות ללא אישור',
  },

  // ============================================================
  // PERSONAL INCOME TAX BRACKETS (מדרגות מס הכנסה)
  // Source: PwC Israel Tax Summaries 2025, Israeli Tax Authority
  //
  // threshold_ils = income ceiling (ILS/yr) for that bracket.
  // NULL = top bracket (no ceiling).
  // 2015 bracket_5 is 34% (35% bracket introduced in 2017).
  // 2025 bracket_6 is 0.50 (47% + 3% surtax combined).
  // ============================================================

  // ---- 2015 BRACKETS (estimated from known structure) ----
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_1',
    rate: '0.1000',
    effectiveFrom: '2015-01-01',
    thresholdIls: '63840',
    notes: 'מדרגת מס 10% — תקרה שנתית 2015',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_2',
    rate: '0.1400',
    effectiveFrom: '2015-01-01',
    thresholdIls: '91440',
    notes: 'מדרגת מס 14% — תקרה שנתית 2015',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_3',
    rate: '0.2000',
    effectiveFrom: '2015-01-01',
    thresholdIls: '130800',
    notes: 'מדרגת מס 20% — תקרה שנתית 2015',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_4',
    rate: '0.3100',
    effectiveFrom: '2015-01-01',
    thresholdIls: '216720',
    notes: 'מדרגת מס 31% — תקרה שנתית 2015',
  },
  // bracket_5 is 34% in 2015 — 35% bracket introduced in 2017
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_5',
    rate: '0.3400',
    effectiveFrom: '2015-01-01',
    thresholdIls: '496920',
    notes: 'מדרגת מס 34% — תקרה שנתית 2015',
  },
  // bracket_6 is 48% in 2015 (includes 3% surtax above ~640k)
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_6',
    rate: '0.4800',
    effectiveFrom: '2015-01-01',
    thresholdIls: null,
    notes: 'מדרגת מס 48% — ללא תקרה 2015 (כולל היטל 3% על הכנסה מעל ~640k)',
  },

  // ---- 2017 BRACKETS (35% bracket introduced, 34% removed) ----
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_1',
    rate: '0.1000',
    effectiveFrom: '2017-01-01',
    thresholdIls: '73560',
    notes: 'מדרגת מס 10% — תקרה שנתית 2017',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_2',
    rate: '0.1400',
    effectiveFrom: '2017-01-01',
    thresholdIls: '103920',
    notes: 'מדרגת מס 14% — תקרה שנתית 2017',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_3',
    rate: '0.2000',
    effectiveFrom: '2017-01-01',
    thresholdIls: '167280',
    notes: 'מדרגת מס 20% — תקרה שנתית 2017',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_4',
    rate: '0.3100',
    effectiveFrom: '2017-01-01',
    thresholdIls: '239760',
    notes: 'מדרגת מס 31% — תקרה שנתית 2017',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_5',
    rate: '0.3500',
    effectiveFrom: '2017-01-01',
    thresholdIls: '498360',
    notes: 'מדרגת מס 35% — תקרה שנתית 2017 (מדרגה חדשה)',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_6',
    rate: '0.4700',
    effectiveFrom: '2017-01-01',
    thresholdIls: null,
    notes: 'מדרגת מס 47% — ללא תקרה 2017 (+ היטל 3% = 50% על הכנסה מעל ~651k)',
  },

  // ---- 2020 BRACKETS (thresholds updated for inflation) ----
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_1',
    rate: '0.1000',
    effectiveFrom: '2020-01-01',
    thresholdIls: '75480',
    notes: 'מדרגת מס 10% — תקרה שנתית 2020',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_2',
    rate: '0.1400',
    effectiveFrom: '2020-01-01',
    thresholdIls: '108360',
    notes: 'מדרגת מס 14% — תקרה שנתית 2020',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_3',
    rate: '0.2000',
    effectiveFrom: '2020-01-01',
    thresholdIls: '173880',
    notes: 'מדרגת מס 20% — תקרה שנתית 2020',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_4',
    rate: '0.3100',
    effectiveFrom: '2020-01-01',
    thresholdIls: '241680',
    notes: 'מדרגת מס 31% — תקרה שנתית 2020',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_5',
    rate: '0.3500',
    effectiveFrom: '2020-01-01',
    thresholdIls: '502920',
    notes: 'מדרגת מס 35% — תקרה שנתית 2020',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_6',
    rate: '0.4700',
    effectiveFrom: '2020-01-01',
    thresholdIls: null,
    notes: 'מדרגת מס 47% — ללא תקרה 2020 (+ היטל 3% = 50% על הכנסה מעל ~651k)',
  },

  // ---- 2022 BRACKETS (significant inflation adjustment post-COVID) ----
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_1',
    rate: '0.1000',
    effectiveFrom: '2022-01-01',
    thresholdIls: '77400',
    notes: 'מדרגת מס 10% — תקרה שנתית 2022',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_2',
    rate: '0.1400',
    effectiveFrom: '2022-01-01',
    thresholdIls: '110880',
    notes: 'מדרגת מס 14% — תקרה שנתית 2022',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_3',
    rate: '0.2000',
    effectiveFrom: '2022-01-01',
    thresholdIls: '178080',
    notes: 'מדרגת מס 20% — תקרה שנתית 2022',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_4',
    rate: '0.3100',
    effectiveFrom: '2022-01-01',
    thresholdIls: '247440',
    notes: 'מדרגת מס 31% — תקרה שנתית 2022',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_5',
    rate: '0.3500',
    effectiveFrom: '2022-01-01',
    thresholdIls: '514920',
    notes: 'מדרגת מס 35% — תקרה שנתית 2022',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_6',
    rate: '0.4700',
    effectiveFrom: '2022-01-01',
    thresholdIls: null,
    notes: 'מדרגת מס 47% — ללא תקרה 2022 (+ היטל 3% = 50% על הכנסה מעל ~663k)',
  },

  // ---- 2023 BRACKETS ----
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_1',
    rate: '0.1000',
    effectiveFrom: '2023-01-01',
    thresholdIls: '81480',
    notes: 'מדרגת מס 10% — תקרה שנתית 2023',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_2',
    rate: '0.1400',
    effectiveFrom: '2023-01-01',
    thresholdIls: '116760',
    notes: 'מדרגת מס 14% — תקרה שנתית 2023',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_3',
    rate: '0.2000',
    effectiveFrom: '2023-01-01',
    thresholdIls: '187440',
    notes: 'מדרגת מס 20% — תקרה שנתית 2023',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_4',
    rate: '0.3100',
    effectiveFrom: '2023-01-01',
    thresholdIls: '260520',
    notes: 'מדרגת מס 31% — תקרה שנתית 2023',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_5',
    rate: '0.3500',
    effectiveFrom: '2023-01-01',
    thresholdIls: '542160',
    notes: 'מדרגת מס 35% — תקרה שנתית 2023',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_6',
    rate: '0.4700',
    effectiveFrom: '2023-01-01',
    thresholdIls: null,
    notes: 'מדרגת מס 47% — ללא תקרה 2023 (+ היטל 3% = 50% על הכנסה מעל ~698k)',
  },

  // ---- 2024 BRACKETS ----
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_1',
    rate: '0.1000',
    effectiveFrom: '2024-01-01',
    thresholdIls: '83040',
    notes: 'מדרגת מס 10% — תקרה שנתית 2024',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_2',
    rate: '0.1400',
    effectiveFrom: '2024-01-01',
    thresholdIls: '118800',
    notes: 'מדרגת מס 14% — תקרה שנתית 2024',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_3',
    rate: '0.2000',
    effectiveFrom: '2024-01-01',
    thresholdIls: '190800',
    notes: 'מדרגת מס 20% — תקרה שנתית 2024',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_4',
    rate: '0.3100',
    effectiveFrom: '2024-01-01',
    thresholdIls: '265320',
    notes: 'מדרגת מס 31% — תקרה שנתית 2024',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_5',
    rate: '0.3500',
    effectiveFrom: '2024-01-01',
    thresholdIls: '553800',
    notes: 'מדרגת מס 35% — תקרה שנתית 2024',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_6',
    rate: '0.4700',
    effectiveFrom: '2024-01-01',
    thresholdIls: null,
    notes: 'מדרגת מס 47% — ללא תקרה 2024 (+ היטל 3% = 50% על הכנסה מעל ~712k)',
  },

  // ---- 2025 BRACKETS (CURRENT — verified from PwC Tax Summaries 2025) ----
  // Source: https://taxsummaries.pwc.com/israel/individual/taxes-on-personal-income
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_1',
    rate: '0.1000',
    effectiveFrom: '2025-01-01',
    thresholdIls: '84120',
    notes: 'מדרגת מס 10% — תקרה שנתית 2025 (מאומת PwC)',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_2',
    rate: '0.1400',
    effectiveFrom: '2025-01-01',
    thresholdIls: '120720',
    notes: 'מדרגת מס 14% — תקרה שנתית 2025 (מאומת PwC)',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_3',
    rate: '0.2000',
    effectiveFrom: '2025-01-01',
    thresholdIls: '193800',
    notes: 'מדרגת מס 20% — תקרה שנתית 2025 (מאומת PwC)',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_4',
    rate: '0.3100',
    effectiveFrom: '2025-01-01',
    thresholdIls: '269280',
    notes: 'מדרגת מס 31% — תקרה שנתית 2025 (מאומת PwC)',
  },
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_5',
    rate: '0.3500',
    effectiveFrom: '2025-01-01',
    thresholdIls: '560280',
    notes: 'מדרגת מס 35% — תקרה שנתית 2025 (מאומת PwC)',
  },
  // bracket_6: 47% base + 3% surtax on income above 721,560; modelled as combined 50%
  {
    countryCode: 'IL',
    taxType: 'personal_bracket_6',
    rate: '0.5000',
    effectiveFrom: '2025-01-01',
    thresholdIls: null,
    notes: 'מדרגת מס 47% + היטל 3% = 50% — ללא תקרה 2025 (על הכנסה מעל 721,560 ₪; מאומת PwC)',
  },
]

export interface IlTaxRateSeedIntegritySummary {
  totalRows: number
  corporateRows: number
  withholdingRows: number
  personalBracketRows: number
  personalBracketYears: string[]
}

const EXPECTED_BRACKET_YEARS = [
  '2015-01-01',
  '2017-01-01',
  '2020-01-01',
  '2022-01-01',
  '2023-01-01',
  '2024-01-01',
  '2025-01-01',
] as const

export function assertIlTaxRatesIntegrity(
  rows: readonly TaxRateSeedRow[] = IL_TAX_RATES,
): IlTaxRateSeedIntegritySummary {
  const corporateRows = rows.filter((row) => row.taxType === 'corporate_income')
  const withholdingRows = rows.filter((row) => row.taxType === 'withholding_default')
  const personalBracketRows = rows.filter((row) => row.taxType.startsWith('personal_bracket_'))
  const personalBracketYears = [...new Set(personalBracketRows.map((row) => row.effectiveFrom))].sort()

  const summary: IlTaxRateSeedIntegritySummary = {
    totalRows: rows.length,
    corporateRows: corporateRows.length,
    withholdingRows: withholdingRows.length,
    personalBracketRows: personalBracketRows.length,
    personalBracketYears,
  }

  if (summary.totalRows !== 56) {
    throw new Error(`IL tax seed row count mismatch: expected 56, got ${summary.totalRows}`)
  }
  if (summary.corporateRows !== 12) {
    throw new Error(`IL corporate tax seed count mismatch: expected 12, got ${summary.corporateRows}`)
  }
  if (summary.withholdingRows !== 2) {
    throw new Error(`IL withholding seed count mismatch: expected 2, got ${summary.withholdingRows}`)
  }
  if (summary.personalBracketRows !== 42) {
    throw new Error(`IL personal bracket seed count mismatch: expected 42, got ${summary.personalBracketRows}`)
  }

  const expectedYears = [...EXPECTED_BRACKET_YEARS]
  if (JSON.stringify(summary.personalBracketYears) !== JSON.stringify(expectedYears)) {
    throw new Error(
      `IL personal bracket years mismatch: expected ${expectedYears.join(', ')}, got ${summary.personalBracketYears.join(', ')}`,
    )
  }

  for (const year of expectedYears) {
    const rowsForYear = personalBracketRows.filter((row) => row.effectiveFrom === year)
    if (rowsForYear.length !== 6) {
      throw new Error(`IL personal bracket seed for ${year} must contain 6 rows, got ${rowsForYear.length}`)
    }

    const topBracket = rowsForYear.find((row) => row.taxType === 'personal_bracket_6')
    if (!topBracket) {
      throw new Error(`IL personal bracket seed for ${year} is missing personal_bracket_6`)
    }
    if (topBracket.thresholdIls !== null) {
      throw new Error(`IL personal_bracket_6 for ${year} must have thresholdIls=null`)
    }
  }

  return summary
}

/**
 * Seed IL non-VAT tax rates.
 * Requires a valid admin_users.id as createdById (must already exist).
 * Idempotent via onConflictDoNothing on (country_code, tax_type, effective_from).
 *
 * Expected row counts for country_code='IL':
 *  - corporate_income:    12 rows
 *  - withholding_default:  2 rows
 *  - personal_bracket_*:  42 rows (7 years × 6 brackets)
 *  - Total:               56 rows
 */
export async function seedTaxRates(db: Db, createdById: string): Promise<void> {
  assertIlTaxRatesIntegrity()
  const rows = IL_TAX_RATES.map((r) => ({ ...r, createdBy: createdById }))
  await db.insert(taxRates).values(rows).onConflictDoNothing()
  console.log(`Seeded ${rows.length} IL non-VAT tax rate rows.`)
}