-- ============================================================
-- CostusWorx © 2026 — retireuk.costusworx.co.za
-- HMRC UK Income Tax Brackets, Allowances & NI — 2024/25
-- ============================================================
--
-- Run AFTER tax_schema.sql (tables must exist).
-- Safe to re-run (INSERT IGNORE on unique key).
--
-- Sources:
--   HMRC Income Tax rates and Personal Allowances 2024/25
--   https://www.gov.uk/income-tax-rates
--   https://www.gov.uk/national-insurance/how-much-you-pay
--
-- UK Income Tax structure (England, Wales, Northern Ireland — NOT Scotland):
--   Personal Allowance:  £0       – £12,570   →  0%
--   Basic Rate:          £12,571  – £50,270   → 20%
--   Higher Rate:         £50,271  – £125,140  → 40%
--   Additional Rate:     £125,141+            → 45%
--   PA taper:            PA reduces £1 per £2 above £100,000
--                        (effective 60% marginal rate £100,001–£125,140)
--
-- Bracket encoding: tax = base_tax + marginal_rate × (income − threshold_start)
--   Bracket 1 (0% personal allowance):
--     income = 12,570 → tax = 0 + 0.00 × 12,570 = £0
--   Bracket 2 (basic rate):
--     base_tax = £0 (PA already absorbed 0% on first £12,570)
--     income = 50,270 → tax = 0 + 0.20 × (50,270 − 12,570) = 0.20 × 37,700 = £7,540
--   Bracket 3 (higher rate):
--     base_tax = £7,540
--     income = 100,000 → tax = 7,540 + 0.40 × (100,000 − 50,270) = 7,540 + 19,892 = £27,432
--   Bracket 4 (PA taper — effective 60% marginal rate):
--     base_tax = 7,540 + 0.40 × (125,140 − 50,270) = 7,540 + 29,948 = £37,488
--     But the PA also tapers to zero, adding further tax. Net effective rate ≈ 60%.
--     We encode this as marginal_rate = 0.60 for simplicity.
--     income = 125,140 → tax ≈ 7,540 + 0.40 × (100,000 − 50,270) + 0.60 × (125,140 − 100,000)
--                            = 7,540 + 19,892 + 15,084 = £42,516  (≈ £42,475 HMRC calc, rounding)
--   Bracket 5 (additional rate 45%):
--     base_tax = £45,000 (approx — PA fully tapered at £125,140)
--     Actual: tax at £125,140 = no PA × full income (45%) less already paid above.
--     Simplified: base_tax at £125,140 computed from marginal brackets above.
--
-- NOTE: The taper bracket (£100,001–£125,140) is the most complex. Admin can
-- refine via the Tax Admin UI. The values below are the standard HMRC simplified
-- presentation (without encoding every taper penny). For the simulation's Monte
-- Carlo accuracy, the bracket below (60% effective rate) is sufficient.
-- ============================================================

SET NAMES utf8mb4;

-- ── Tax Brackets 2024/25 (tenant_id = 0 = HMRC global defaults) ──────────────

INSERT IGNORE INTO tax_brackets
    (tenant_id, tax_year, threshold_start, threshold_end, base_tax, marginal_rate)
VALUES
    -- Personal Allowance (0%)
    (0, 2025,       0.00,   12570.00,      0.00, 0.0000),
    -- Basic Rate (20%)
    (0, 2025,   12570.00,   50270.00,      0.00, 0.2000),
    -- Higher Rate (40%)
    (0, 2025,   50270.00,  100000.00,   7540.00, 0.4000),
    -- PA taper zone (effective ~60% marginal rate where personal allowance tapers)
    (0, 2025,  100000.00,  125140.00,  27432.00, 0.6000),
    -- Additional Rate (45%, PA fully withdrawn — all income taxable)
    (0, 2025,  125140.00,       NULL,  42516.00, 0.4500);

-- ── UK Tax Rebates / Allowances 2024/25 ────────────────────────────────────────
-- Reusing tax_rebates table for UK allowances.
-- rebate_type values extended for UK:
--   'savings_allowance'  → Personal Savings Allowance (£1,000 basic / £500 higher)
--   'dividend_allowance' → Dividend Allowance (£500 from 2024/25)

DELETE FROM tax_rebates WHERE tax_year = 2025;

INSERT INTO tax_rebates (tax_year, rebate_type, amount, age_threshold) VALUES
    -- Personal Savings Allowance (basic-rate taxpayer default; adviser adjusts for HR)
    (2025, 'savings_allowance',  1000.00, NULL),
    -- Dividend Allowance 2024/25 (reduced from £1,000 to £500)
    (2025, 'dividend_allowance',  500.00, NULL);

-- ── National Insurance (Class 1 Employee) 2024/25 ────────────────────────────
-- Stored in a separate ni_brackets table (see uk_ni_schema.sql).
-- Rates for reference:
--   0%  on earnings up to £12,570 (Primary Threshold)
--   8%  on earnings £12,571 – £50,270 (Upper Earnings Limit)
--   2%  on earnings above £50,270

-- ── Quick sanity check after running: ────────────────────────────────────────
-- SELECT tax_year, threshold_start, threshold_end, base_tax, marginal_rate
-- FROM tax_brackets WHERE tax_year = 2025 ORDER BY threshold_start;
-- Expected: 5 rows. At income=50270 → tax=7540; at income=125140 → tax≈42516.