-- ============================================================
-- CostusWorx © 2026 — retireuk.costusworx.co.za
-- UK State Pension & National Insurance Contributions Schema
-- ============================================================
--
-- Run once on initial deploy. Safe to re-run (CREATE TABLE IF NOT EXISTS).
--
-- UK New State Pension (2024/25):
--   Full amount: £221.20/week → £11,502.40/year
--   Qualifying years: 35 for full; 10 minimum for any entitlement
--   State Pension age: 66 (rising to 67 by 2028)
--   Triple Lock: rises annually by max(CPI, earnings, 2.5%)
--
-- National Insurance Classes:
--   Class 1 (employee/employer): primary route to qualifying years
--   Class 2 (self-employed): £3.45/week 2024/25
--   Class 3 (voluntary gaps): £17.45/week 2024/25
-- ============================================================

SET NAMES utf8mb4;

-- ── NI Contribution Brackets (Class 1 Employee) ───────────────────────────────
CREATE TABLE IF NOT EXISTS ni_brackets (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tax_year         INT          NOT NULL  COMMENT 'e.g. 2025 = 2024/25',
    threshold_start  DECIMAL(10,2) NOT NULL COMMENT 'Lower limit (£/yr)',
    threshold_end    DECIMAL(10,2) NULL     COMMENT 'Upper limit (NULL = open-ended)',
    ni_rate          DECIMAL(6,4)  NOT NULL COMMENT 'Employee Class 1 rate e.g. 0.0800',
    label            VARCHAR(60)   NOT NULL COMMENT 'e.g. Primary Threshold, UEL',
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_ni (tax_year, threshold_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2024/25 Class 1 Employee NI rates
INSERT IGNORE INTO ni_brackets (tax_year, threshold_start, threshold_end, ni_rate, label)
VALUES
    (2025,       0.00,   12570.00, 0.0000, 'Below Primary Threshold'),
    (2025,   12570.00,   50270.00, 0.0800, 'Primary Threshold to UEL'),
    (2025,   50270.00,       NULL, 0.0200, 'Above Upper Earnings Limit');

-- ── State Pension Reference Table ─────────────────────────────────────────────
-- Stores the weekly rates published each April (triple lock).
-- The simulation inflates the client's state_pension asset by CPI each year;
-- this table is for admin reference and the "new client" default pre-fill.
CREATE TABLE IF NOT EXISTS state_pension_rates (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tax_year    INT          NOT NULL UNIQUE COMMENT '2025 = 2024/25 rate',
    weekly_rate DECIMAL(8,2) NOT NULL  COMMENT 'Full New State Pension £/week',
    annual_rate DECIMAL(10,2) GENERATED ALWAYS AS (weekly_rate * 52) STORED
                              COMMENT 'Approx annual (52 weeks)',
    increase_pct DECIMAL(5,2) NULL    COMMENT 'Triple-lock % applied this year',
    source       VARCHAR(120) NULL,
    updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO state_pension_rates (tax_year, weekly_rate, increase_pct, source) VALUES
    (2023, 185.15, 10.10, 'Gov.uk — earnings-driven'),
    (2024, 203.85,  8.50, 'Gov.uk — earnings-driven'),
    (2025, 221.20,  8.50, 'Gov.uk — earnings-driven triple lock 2024/25');

-- ── Client NI Records (optional detail per client) ────────────────────────────
-- Advisers can record a client's qualifying NI years to auto-calculate
-- their State Pension entitlement fraction.
CREATE TABLE IF NOT EXISTS client_ni_records (
    id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id            INT UNSIGNED NOT NULL,
    client_id            INT UNSIGNED NOT NULL,
    qualifying_years     TINYINT UNSIGNED NOT NULL DEFAULT 0
                         COMMENT 'Confirmed NI qualifying years (from HMRC forecast)',
    projected_years      TINYINT UNSIGNED NOT NULL DEFAULT 0
                         COMMENT 'Projected total years at State Pension age',
    state_pension_age    TINYINT UNSIGNED NOT NULL DEFAULT 66,
    estimated_weekly_gbp DECIMAL(8,2) NULL
                         COMMENT 'Overrides calculated value if set by adviser',
    notes                TEXT NULL,
    updated_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_client_ni (tenant_id, client_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    FOREIGN KEY (client_id) REFERENCES clients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── State Pension Deferral Table ──────────────────────────────────────────────
-- UK deferral: for every 9 weeks you delay claiming, the State Pension
-- increases by 1% (≈5.8%/year). This table stores the deferral rules.
CREATE TABLE IF NOT EXISTS state_pension_deferral (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    effective_from   YEAR         NOT NULL COMMENT 'Rules effective from this year',
    increase_per_week DECIMAL(8,6) NOT NULL COMMENT 'Fractional increase per deferred week',
    notes            TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO state_pension_deferral (effective_from, increase_per_week, notes) VALUES
    (2016, 0.010000, '1% per 9 weeks deferred = ~5.8%/yr for New State Pension (post-2016 rules)');