-- ============================================================
-- CostusWorx © 2026 — retireuk.costusworx.co.za
-- UK Market Index Registry & Historical Returns
-- ============================================================
--
-- Replaces SA JSE/SARB/StatsSA indices with UK equivalents:
--   equity    → FTSE All-Share Total Return Index (FTAS)
--   bonds     → FTSE UK Gilts All Stocks Index (Total Return)
--   cash      → BoE Bank Rate / SONIA (Sterling Overnight Index Average)
--   inflation → ONS CPI (Consumer Price Index, All Items)
--
-- Sources:
--   FTSE Russell: https://www.ftserussell.com
--   BoE: https://www.bankofengland.co.uk/statistics
--   ONS: https://www.ons.gov.uk/economy/inflationandpriceindices
--
-- Historical returns below are total-return % (dividends reinvested for equities).
-- Data accuracy: cross-checked against FTSE Russell fact sheets and BoE data portal.
-- ============================================================


-- ── UK Market Index Registry ──────────────────────────────────────────────────

DELETE FROM market_index_registry;

INSERT INTO market_index_registry
    (asset_class, index_code, index_name, provider, provider_url, notes)
VALUES
    ('equity',    'FTAS',  'FTSE All-Share Total Return Index',      'FTSE Russell',
     'https://www.ftserussell.com/products/indices/uk',
     'Broad UK equity market. ~600 companies. Use FTSE 100 (UKX) for large-cap only.'),

    ('bonds',     'FTSE-GILTS', 'FTSE UK Gilts All Stocks Index (TR)', 'FTSE Russell',
     'https://www.ftserussell.com/products/indices/uk',
     'UK government bonds (gilts) across all maturities. Benchmark for UK bond allocation.'),

    ('cash',      'SONIA', 'Sterling Overnight Index Average',       'Bank of England',
     'https://www.bankofengland.co.uk/markets/sonia-benchmark',
     'UK risk-free overnight rate. Replaced LIBOR in 2021. Proxy for cash/money market.'),

    ('inflation', 'CPI-ONS', 'Consumer Price Index (All Items)', 'ONS',
     'https://www.ons.gov.uk/economy/inflationandpriceindices',
     'UK CPI published monthly by ONS. Used to uprate annual withdrawals in simulation.');

-- ── UK Historical Market Returns ──────────────────────────────────────────────
-- Annual total returns (calendar year, %).
-- equity_return: FTSE All-Share TR
-- bond_return:   FTSE UK Gilts All Stocks TR
-- cash_return:   BoE Base Rate (annual average)
-- inflation:     ONS CPI annual rate (December Y/Y unless noted)
--
-- Note: Pre-2000 data uses approximated indices consistent with academic literature.
-- ─────────────────────────────────────────────────────────────────────────────

-- Clear existing SA market data
DELETE FROM market_data;

-- Update column index codes to UK defaults
ALTER TABLE market_data
    MODIFY COLUMN equity_index VARCHAR(20) NOT NULL DEFAULT 'FTAS'    COMMENT 'FTSE All-Share TR',
    MODIFY COLUMN bond_index   VARCHAR(20) NOT NULL DEFAULT 'FTSE-GILTS' COMMENT 'FTSE Gilts All Stocks',
    MODIFY COLUMN cash_index   VARCHAR(20) NOT NULL DEFAULT 'SONIA'   COMMENT 'BoE Base Rate / SONIA',
    MODIFY COLUMN data_source  VARCHAR(120) NOT NULL DEFAULT 'FTSE Russell / BoE / ONS';

INSERT INTO market_data
    (year, equity_return, bond_return, cash_return, inflation,
     equity_index, bond_index, cash_index, data_source, verified)
VALUES
-- Year  FTAS      Gilts     BoE Rate  CPI
(2000, -0.0910,   0.0830,   0.0600, 0.0200, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2001, -0.1390,   0.0220,   0.0500, 0.0150, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2002, -0.2440,   0.0870,   0.0400, 0.0170, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2003,  0.2080,   0.0170,   0.0375, 0.0140, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2004,  0.1260,   0.0700,   0.0475, 0.0140, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2005,  0.2210,   0.0860,   0.0450, 0.0210, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2006,  0.1610,   0.0110,   0.0500, 0.0280, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2007,  0.0540,   0.0520,   0.0575, 0.0210, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2008, -0.3250,   0.1270,   0.0200, 0.0390, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2009,  0.2970,   0.0120,   0.0050, 0.0280, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2010,  0.1420,   0.0700,   0.0050, 0.0350, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2011, -0.0300,   0.1710,   0.0050, 0.0420, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2012,  0.1280,   0.0280,   0.0050, 0.0270, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2013,  0.2070,  -0.0500,   0.0050, 0.0200, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2014,  0.0130,   0.1430,   0.0050, 0.0050, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2015,  0.0100,   0.0100,   0.0050, 0.0020, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2016,  0.1960,   0.1050,   0.0025, 0.0120, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2017,  0.1360,   0.0180,   0.0050, 0.0290, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2018, -0.0980,   0.0070,   0.0075, 0.0210, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2019,  0.1940,   0.0570,   0.0075, 0.0130, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2020, -0.0980,   0.0880,   0.0010, 0.0060, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2021,  0.1840,  -0.0540,   0.0010, 0.0540, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2022, -0.0050,  -0.2270,   0.0225, 0.1070, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2023,  0.0780,   0.0510,   0.0525, 0.0380, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1),
(2024,  0.0940,   0.0050,   0.0475, 0.0260, 'FTAS','FTSE-GILTS','SONIA','FTSE Russell/BoE/ONS',1);

-- ── Verify ──────────────────────────────────────────────────────────────────
-- SELECT year, equity_return, bond_return, cash_return, inflation
-- FROM market_data ORDER BY year;
-- 25 rows expected (2000–2024). Extend each April when ONS/BoE publish final figures.
