-- ============================================================================
-- CostusWorx © 2026 — retireuk.costusworx.co.za
-- SQL backfill of fund segment allocations (no console needed)
--
-- Pure-SQL alternative to bin/funds-backfill-segments.php for shared hosting
-- without CLI access. Run in phpMyAdmin AFTER deploy_2026_06_13.sql (it needs the
-- funds.alloc_* columns). NON-DESTRUCTIVE: only fills funds whose allocation is
-- currently empty and that carry some classification (IA sector OR benchmark).
--
-- CAVEAT: cruder than the PHP classifier — it keyword-matches a normalized string
-- and cannot perfectly handle every messy IA label. Good for the clean sectors.
-- To refine later, run the PHP backfill (it overwrites nothing already set unless
-- --overwrite). VERIFY a few funds against fact sheets before client use.
--
-- Percentages match config/fund_segments.php (segments: UK equity / offshore /
-- bonds / cash / property). Offshore equity (alloc_equity_intl) is split out;
-- listed property folds where applicable. Classification prefers ia_sector_category,
-- falling back to the mirrored asisa_category, then benchmark_index.
-- ============================================================================
SET NAMES utf8mb4;

-- ── STEP 1 (optional) — DRY RUN: how many funds land in each class ───────────
-- Run this SELECT on its own first to preview. It writes nothing.
SELECT
  CASE
    WHEN n LIKE '%money market%' THEN 'money_market'
    WHEN n LIKE '%real estate%'  OR n LIKE '%property%' OR n LIKE '%reit%' THEN 'property'
    WHEN n NOT LIKE '%mixed%' AND (n LIKE '%gilt%' OR n LIKE '%corporate bond%'
         OR n LIKE '%strategic bond%' OR n LIKE '%high yield%' OR n LIKE '%index linked%'
         OR n LIKE '%fixed interest%' OR n LIKE '%bond%') THEN 'bonds'
    WHEN n LIKE '%income%' AND n NOT LIKE '%equity income%' THEN 'income'
    WHEN n LIKE '%mixed investment%' OR n LIKE '%balanced%' OR n LIKE '%flexible%'
         OR n LIKE '%diversified%' OR n LIKE '%volatility managed%' THEN
         CASE
           WHEN n LIKE '%40 85%' OR n LIKE '%high equity%' THEN 'high_equity'
           WHEN n LIKE '%20 60%' OR n LIKE '%medium equity%' THEN 'medium_equity'
           WHEN n LIKE '%0 35%' OR n LIKE '%low equity%' OR n LIKE '%cautious%'
                OR n LIKE '%conservative%' OR n LIKE '%defensive%' THEN 'low_equity'
           ELSE 'high_equity'
         END
    WHEN n LIKE '%absolute return%' OR n LIKE '%targeted return%' THEN 'low_equity'
    WHEN n LIKE '%equity%' OR n LIKE '%companies%' OR n LIKE '%shares%' OR n LIKE '%smaller%'
         OR n LIKE '%global%' OR n LIKE '%america%' OR n LIKE '%europe%' OR n LIKE '%japan%'
         OR n LIKE '%asia%' OR n LIKE '%pacific%' OR n LIKE '%emerging%'
         OR n LIKE '%technology%' OR n LIKE '%ftse%' OR n LIKE '%msci%' THEN 'equity_general'
    ELSE 'high_equity'
  END AS archetype,
  CASE
    WHEN n LIKE '%global%' OR n LIKE '%international%' OR n LIKE '%world%'
      OR n LIKE '%overseas%' OR n LIKE '%offshore%' OR n LIKE '%foreign%' OR n LIKE '%emerging%'
      OR n LIKE '%america%' OR n LIKE '%europe%' OR n LIKE '%japan%' OR n LIKE '%asia%'
      OR n LIKE '%pacific%' OR n LIKE '%china%' OR n LIKE '%india%' OR n LIKE '%nasdaq%'
      OR n LIKE '%technology%'
    THEN 'global' ELSE 'uk'
  END AS geography,
  COUNT(*) AS funds
FROM (
  SELECT CONCAT(' ', LOWER(REPLACE(REPLACE(REPLACE(REPLACE(
           COALESCE(NULLIF(TRIM(ia_sector_category),''), NULLIF(TRIM(asisa_category),''), TRIM(benchmark_index), ''),
           '-',' '),'/',' '),'%',' '),'  ',' ')), ' ') AS n
  FROM funds
  WHERE is_active = 1
    AND alloc_equity_local IS NULL AND alloc_equity_intl IS NULL
    AND alloc_bonds IS NULL AND alloc_cash IS NULL AND alloc_property IS NULL
    AND (TRIM(COALESCE(ia_sector_category,'')) <> '' OR TRIM(COALESCE(asisa_category,'')) <> ''
         OR TRIM(COALESCE(benchmark_index,'')) <> '')
) s
GROUP BY archetype, geography
ORDER BY funds DESC;

-- ── STEP 2 — APPLY: classify + stamp the alloc_* columns ─────────────────────
-- Run everything below together (phpMyAdmin executes the temp tables in one session).

DROP TEMPORARY TABLE IF EXISTS _seg_cls;
CREATE TEMPORARY TABLE _seg_cls AS
SELECT id,
  CONCAT(
    CASE
      WHEN n LIKE '%money market%' THEN 'money_market'
      WHEN n LIKE '%real estate%'  OR n LIKE '%property%' OR n LIKE '%reit%' THEN 'property'
      WHEN n NOT LIKE '%mixed%' AND (n LIKE '%gilt%' OR n LIKE '%corporate bond%'
           OR n LIKE '%strategic bond%' OR n LIKE '%high yield%' OR n LIKE '%index linked%'
           OR n LIKE '%fixed interest%' OR n LIKE '%bond%') THEN 'bonds'
      WHEN n LIKE '%income%' AND n NOT LIKE '%equity income%' THEN 'income'
      WHEN n LIKE '%mixed investment%' OR n LIKE '%balanced%' OR n LIKE '%flexible%'
           OR n LIKE '%diversified%' OR n LIKE '%volatility managed%' THEN
           CASE
             WHEN n LIKE '%40 85%' OR n LIKE '%high equity%' THEN 'high_equity'
             WHEN n LIKE '%20 60%' OR n LIKE '%medium equity%' THEN 'medium_equity'
             WHEN n LIKE '%0 35%' OR n LIKE '%low equity%' OR n LIKE '%cautious%'
                  OR n LIKE '%conservative%' OR n LIKE '%defensive%' THEN 'low_equity'
             ELSE 'high_equity'
           END
      WHEN n LIKE '%absolute return%' OR n LIKE '%targeted return%' THEN 'low_equity'
      WHEN n LIKE '%equity%' OR n LIKE '%companies%' OR n LIKE '%shares%' OR n LIKE '%smaller%'
           OR n LIKE '%global%' OR n LIKE '%america%' OR n LIKE '%europe%' OR n LIKE '%japan%'
           OR n LIKE '%asia%' OR n LIKE '%pacific%' OR n LIKE '%emerging%'
           OR n LIKE '%technology%' OR n LIKE '%ftse%' OR n LIKE '%msci%' THEN 'equity_general'
      ELSE 'high_equity'
    END,
    '_',
    CASE
      WHEN n LIKE '%global%' OR n LIKE '%international%' OR n LIKE '%world%'
        OR n LIKE '%overseas%' OR n LIKE '%offshore%' OR n LIKE '%foreign%' OR n LIKE '%emerging%'
        OR n LIKE '%america%' OR n LIKE '%europe%' OR n LIKE '%japan%' OR n LIKE '%asia%'
        OR n LIKE '%pacific%' OR n LIKE '%china%' OR n LIKE '%india%' OR n LIKE '%nasdaq%'
        OR n LIKE '%technology%'
      THEN 'global' ELSE 'uk'
    END
  ) COLLATE utf8mb4_general_ci AS k,   -- pin collation so the join key matches _seg_w
  CASE WHEN TRIM(COALESCE(ia_sector_category,'')) <> '' OR TRIM(COALESCE(asisa_category,'')) <> ''
       THEN 'ia_default' ELSE 'benchmark' END AS src_tag
FROM (
  SELECT id, ia_sector_category, asisa_category,
    CONCAT(' ', LOWER(REPLACE(REPLACE(REPLACE(REPLACE(
      COALESCE(NULLIF(TRIM(ia_sector_category),''), NULLIF(TRIM(asisa_category),''), TRIM(benchmark_index), ''),
      '-',' '),'/',' '),'%',' '),'  ',' ')), ' ') AS n
  FROM funds
  WHERE is_active = 1
    AND alloc_equity_local IS NULL AND alloc_equity_intl IS NULL
    AND alloc_bonds IS NULL AND alloc_cash IS NULL AND alloc_property IS NULL
    AND (TRIM(COALESCE(ia_sector_category,'')) <> '' OR TRIM(COALESCE(asisa_category,'')) <> ''
         OR TRIM(COALESCE(benchmark_index,'')) <> '')
) src;

DROP TEMPORARY TABLE IF EXISTS _seg_w;
CREATE TEMPORARY TABLE _seg_w (
  k VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci PRIMARY KEY,
  eql DECIMAL(5,2), eqi DECIMAL(5,2), bnd DECIMAL(5,2), csh DECIMAL(5,2), prp DECIMAL(5,2)
);
INSERT INTO _seg_w (k,eql,eqi,bnd,csh,prp) VALUES
  ('equity_general_uk',     95, 0,  0,  5,  0),
  ('equity_general_global',  0, 95, 0,  5,  0),
  ('high_equity_uk',        25, 50, 15, 8,  2),
  ('high_equity_global',     0, 78, 12, 10, 0),
  ('medium_equity_uk',      15, 30, 35, 18, 2),
  ('medium_equity_global',   0, 45, 35, 20, 0),
  ('low_equity_uk',          7, 15, 45, 30, 3),
  ('low_equity_global',      0, 22, 45, 33, 0),
  ('income_uk',              6,  6, 48, 40, 0),
  ('income_global',          0, 10, 45, 45, 0),
  ('bonds_uk',               0,  0, 85, 15, 0),
  ('bonds_global',           0,  0, 85, 15, 0),
  ('money_market_uk',        0,  0,  0,100, 0),
  ('money_market_global',    0,  0,  0,100, 0),
  ('property_uk',            0,  0,  0, 10, 90),
  ('property_global',        0, 85,  0, 15, 0);

UPDATE funds f
  JOIN _seg_cls c ON c.id = f.id
  JOIN _seg_w   w ON w.k COLLATE utf8mb4_general_ci = c.k COLLATE utf8mb4_general_ci
SET f.alloc_equity_local = w.eql,
    f.alloc_equity_intl  = w.eqi,
    f.alloc_bonds        = w.bnd,
    f.alloc_cash         = w.csh,
    f.alloc_property     = w.prp,
    f.alloc_source       = c.src_tag,
    f.alloc_asof         = CURDATE();

DROP TEMPORARY TABLE IF EXISTS _seg_cls;
DROP TEMPORARY TABLE IF EXISTS _seg_w;

-- Done. Spot-check: SELECT name, ia_sector_category, alloc_equity_local, alloc_equity_intl,
--   alloc_bonds, alloc_cash, alloc_property, alloc_source FROM funds WHERE alloc_source IS NOT NULL LIMIT 50;