-- ============================================================
-- JSE Historical Data Seed — CostusWorx © 2026
-- retire.costusworx.co.za
-- ============================================================
--
-- HOW TO OBTAIN THE REAL DATA
-- ────────────────────────────
-- These values must be sourced from authoritative SA data providers.
-- Do NOT use the placeholder CSV data in production client meetings.
--
-- 1. EQUITY (J203 — FTSE/JSE All Share Index, Total Return):
--    Source: Sharenet (sharenet.co.za) → Indices → J203 → Historical
--    Or:     JSE Data Portal (jse.co.za/services/data)
--    Or:     IRESS / Bloomberg (Enterprise clients)
--    Column: Total Return Index (includes dividends), annual % change
--
-- 2. BONDS (ALBI — FTSE/JSE All Bond Index):
--    Source: JSE Debt Market Data → ALBI Total Return
--    Or:     SARB website (resbank.co.za) → Statistics → Financial Markets
--    Column: Annual total return % (price + coupon)
--
-- 3. CASH (STeFI Composite):
--    Source: SARB → Statistics → Money Market Rates
--    Or:     Sharenet → STeFI Composite
--    Column: Annual compounded return %
--
-- 4. INFLATION (CPI Headline):
--    Source: Stats SA (statssa.gov.za) → CPI → P0141 release
--    Published monthly — take December-on-December % change per year
--
-- ────────────────────────────────────────────────────────────
-- ALTERNATIVE: SWIX (J406) instead of ALSI (J203) for equity
-- ────────────────────────────────────────────────────────────
-- Regulation 28 / ASISA classification typically uses SWIX (J406)
-- or Capped SWIX (J433) as the equity benchmark for balanced funds.
-- If your clients are predominantly in balanced/life-stage funds,
-- update equity_index to 'J406' in each row below.
--
-- To switch globally after seeding:
--   UPDATE market_data SET equity_index = 'J406', equity_return = <swix_values>;
--
-- ────────────────────────────────────────────────────────────
-- BULK IMPORT FROM CSV
-- ────────────────────────────────────────────────────────────
-- Once you have a CSV with columns: year,equity,bonds,cash,inflation
-- (sourced from above providers), run:
--
--   LOAD DATA LOCAL INFILE '/path/to/your/jse_data.csv'
--   INTO TABLE market_data
--   FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
--   IGNORE 1 ROWS
--   (year, equity_return, bond_return, cash_return, inflation);
--
-- Then update the metadata columns:
--   UPDATE market_data
--   SET equity_index = 'J203',
--       bond_index   = 'ALBI',
--       cash_index   = 'STeFI',
--       data_source  = 'Sharenet historical export',
--       verified     = 0;
-- ============================================================

-- Sample structure insert (replace values with real sourced data):
INSERT INTO market_data
    (year, equity_return, bond_return, cash_return, inflation, equity_index, bond_index, cash_index, data_source, verified)
VALUES
-- year   J203 ALSI   ALBI      STeFI     CPI
(1993,   0.3521,     0.1423,   0.1201,   0.0975,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(1994,   0.1234,     0.1189,   0.1245,   0.0898,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(1995,   0.2341,     0.1534,   0.1378,   0.0856,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(1996,   0.1012,     0.1623,   0.1456,   0.0765,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(1997,  -0.0432,     0.1312,   0.1389,   0.0854,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(1998,  -0.1123,     0.1098,   0.1312,   0.0692,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(1999,   0.6123,     0.1523,   0.1234,   0.0523,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2000,   0.1234,     0.1245,   0.1189,   0.0502,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2001,   0.2987,     0.1678,   0.1023,   0.0578,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2002,  -0.0876,     0.1534,   0.1312,   0.0993,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2003,   0.1789,     0.1234,   0.1145,   0.0589,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2004,   0.2532,     0.1012,   0.0923,   0.0412,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2005,   0.4712,     0.0987,   0.0789,   0.0389,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2006,   0.4123,     0.0812,   0.0712,   0.0481,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2007,   0.1987,     0.0756,   0.0934,   0.0698,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2008,  -0.2634,     0.0634,   0.1123,   0.1134,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2009,   0.2845,     0.1023,   0.0834,   0.0712,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2010,   0.1923,     0.0923,   0.0667,   0.0347,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2011,   0.0234,     0.0812,   0.0589,   0.0521,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2012,   0.2678,     0.1512,   0.0512,   0.0524,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2013,   0.2156,     0.0034,  -0.0012,   0.0545,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2014,   0.1023,     0.1023,   0.0567,   0.0612,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2015,  -0.0312,     0.0189,   0.0612,   0.0476,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2016,   0.0212,     0.1523,   0.0723,   0.0634,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2017,   0.2089,     0.1012,   0.0712,   0.0532,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2018,  -0.1134,     0.0134,   0.0734,   0.0479,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2019,   0.1289,     0.1234,   0.0712,   0.0394,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2020,   0.0712,     0.1623,   0.0634,   0.0328,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2021,   0.2756,     0.1012,   0.0412,   0.0463,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2022,  -0.0912,     0.0234,   0.0623,   0.0693,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2023,   0.1923,     0.1089,   0.0812,   0.0529,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2024,   0.1534,     0.0923,   0.0756,   0.0478,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0),
(2025,   0.1245,     0.0856,   0.0723,   0.0456,   'J203', 'ALBI', 'STeFI', 'Sharenet/SARB — VERIFY BEFORE PRODUCTION USE', 0);

-- After loading verified data, mark rows as verified:
-- UPDATE market_data SET verified = 1 WHERE data_source = 'Sharenet export 2026-01';

-- ── Optional: SWIX equity alternative ─────────────────────────
-- If you want to run simulations against J406 (SWIX) instead of J203,
-- add a second set of rows with a different equity_index tag,
-- then filter by equity_index in DatabaseMarketData::load().
-- Example:
-- INSERT INTO market_data (year, equity_return, ..., equity_index, ...) VALUES
-- (2024, 0.1412, ..., 'J406', ...);
