-- ============================================================================
-- CostusWorx © 2026 — retireuk.costusworx.co.za
-- Deployment bundle for 2026-06-13: segment engine + offshore + deferred-retirement
--
-- Run this ENTIRE file once (phpMyAdmin → SQL tab, or `mysql < deploy_2026_06_13.sql`)
-- BEFORE uploading the application files. All schema changes are additive and
-- idempotent (ADD COLUMN IF NOT EXISTS), so the file is safe to re-run and safe
-- against the currently-deployed code.
--
-- Order matters: section 2 (offshore column) precedes section 3 (offshore data).
-- After this runs and files are uploaded, run on the server:
--     php bin/funds-backfill-segments.php --dry-run    (then without --dry-run)
-- or, if you have no CLI access, run database/funds_backfill_segments.sql in phpMyAdmin.
-- ============================================================================
SET NAMES utf8mb4;

-- ── 1. Fund segment-allocation columns ──────────────────────────────────────
-- (Complements fund_alloc.sql; re-asserts the base four so this is self-sufficient.)
ALTER TABLE funds
    ADD COLUMN IF NOT EXISTS alloc_equity_local DECIMAL(5,2) DEFAULT NULL COMMENT 'UK Equities %',
    ADD COLUMN IF NOT EXISTS alloc_equity_intl  DECIMAL(5,2) DEFAULT NULL COMMENT 'International Equities %',
    ADD COLUMN IF NOT EXISTS alloc_bonds        DECIMAL(5,2) DEFAULT NULL COMMENT 'Bonds %',
    ADD COLUMN IF NOT EXISTS alloc_cash         DECIMAL(5,2) DEFAULT NULL COMMENT 'Cash / Money Market %',
    ADD COLUMN IF NOT EXISTS alloc_property     DECIMAL(5,2) DEFAULT NULL COMMENT 'UK/Global Listed Property %',
    ADD COLUMN IF NOT EXISTS alloc_source       VARCHAR(20)  DEFAULT NULL COMMENT 'ia_default|benchmark|default|mdd|manual',
    ADD COLUMN IF NOT EXISTS alloc_asof         DATE         DEFAULT NULL COMMENT 'As-of date of the stored allocation';

-- ── 2. Offshore-equity return slot on market_data ───────────────────────────
ALTER TABLE market_data
    ADD COLUMN IF NOT EXISTS offshore_equity_return DECIMAL(6,4) DEFAULT NULL
        COMMENT 'Global equity total return in GBP (e.g. MSCI World x GBP/USD), annual fraction',
    ADD COLUMN IF NOT EXISTS offshore_equity_index  VARCHAR(20)  DEFAULT NULL
        COMMENT 'Offshore equity index used, e.g. MSCI World / S&P 500';

-- ── 3. Offshore-equity-in-GBP series (BEST-EFFORT, VERIFY before client use) ─
-- offshore_gbp = (1 + MSCI World USD) x (GBP/USD move). Inputs are approximate.
START TRANSACTION;
UPDATE market_data SET offshore_equity_return = -0.0594, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2000;
UPDATE market_data SET offshore_equity_return = -0.1456, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2001;
UPDATE market_data SET offshore_equity_return = -0.2761, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2002;
UPDATE market_data SET offshore_equity_return = 0.1981, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2003;
UPDATE market_data SET offshore_equity_return = 0.0720, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2004;
UPDATE market_data SET offshore_equity_return = 0.2231, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2005;
UPDATE market_data SET offshore_equity_return = 0.0524, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2006;
UPDATE market_data SET offshore_equity_return = 0.0772, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2007;
UPDATE market_data SET offshore_equity_return = -0.1952, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2008;
UPDATE market_data SET offshore_equity_return = 0.1765, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2009;
UPDATE market_data SET offshore_equity_return = 0.1577, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2010;
UPDATE market_data SET offshore_equity_return = -0.0506, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2011;
UPDATE market_data SET offshore_equity_return = 0.1059, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2012;
UPDATE market_data SET offshore_equity_return = 0.2443, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2013;
UPDATE market_data SET offshore_equity_return = 0.1150, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2014;
UPDATE market_data SET offshore_equity_return = 0.0466, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2015;
UPDATE market_data SET offshore_equity_return = 0.2843, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2016;
UPDATE market_data SET offshore_equity_return = 0.1182, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2017;
UPDATE market_data SET offshore_equity_return = -0.0327, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2018;
UPDATE market_data SET offshore_equity_return = 0.2281, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2019;
UPDATE market_data SET offshore_equity_return = 0.1252, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2020;
UPDATE market_data SET offshore_equity_return = 0.2296, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2021;
UPDATE market_data SET offshore_equity_return = -0.0824, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2022;
UPDATE market_data SET offshore_equity_return = 0.1722, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2023;
UPDATE market_data SET offshore_equity_return = 0.2097, offshore_equity_index = 'MSCI World (GBP)' WHERE year = 2024;
COMMIT;

-- ── 4. Persist the run-time deferral on each saved simulation run ────────────
ALTER TABLE simulation_runs
    ADD COLUMN IF NOT EXISTS accumulation_months INT DEFAULT NULL
        COMMENT 'Months to retirement captured at run time (deferral); NULL = legacy run';

-- ============================================================================
-- Done. Now upload the application files (see DEPLOY.md), then run the backfill:
--     php bin/funds-backfill-segments.php --dry-run
--     php bin/funds-backfill-segments.php
-- (or database/funds_backfill_segments.sql in phpMyAdmin if no CLI access).
-- ============================================================================