-- ============================================================
-- Risk Rating Defaults — CostusWorx © 2026
-- Idempotent: only fills NULL risk_rating values.
-- Re-run after any new fund import to back-fill.
-- ============================================================
-- Mapping rationale:
--   ENUM values: low, medium_low, medium, medium_high, high
--   Equity / commodities / property → high
--   Multi-Asset High Equity → medium_high
--   Multi-Asset Medium / Balanced → medium
--   Multi-Asset Low Equity / Income → medium_low
--   Bonds / Money Market / Cash → low to medium_low
-- ============================================================

UPDATE funds SET risk_rating = 'high' WHERE risk_rating IS NULL AND (
       asisa_category LIKE '%Equity%'
    OR asisa_category LIKE '%Real Estate%'
    OR asisa_category LIKE '%Property%'
    OR asisa_category LIKE '%Commodities%'
    OR asisa_category LIKE '%Resource%'
    OR asisa_category LIKE '%Sector%'
);

UPDATE funds SET risk_rating = 'medium_high' WHERE risk_rating IS NULL AND (
       asisa_category LIKE '%Multi%Asset%High%'
    OR asisa_category LIKE '%MA High Equity%'
    OR asisa_category LIKE '%Aggressive%'
);

UPDATE funds SET risk_rating = 'medium' WHERE risk_rating IS NULL AND (
       asisa_category LIKE '%Multi%Asset%Medium%'
    OR asisa_category LIKE '%MA Medium Equity%'
    OR asisa_category LIKE '%Balanced%'
    OR asisa_category LIKE '%Flexible%'
);

UPDATE funds SET risk_rating = 'medium_low' WHERE risk_rating IS NULL AND (
       asisa_category LIKE '%Multi%Asset%Low%'
    OR asisa_category LIKE '%MA Low Equity%'
    OR asisa_category LIKE '%Multi%Asset%Income%'
    OR asisa_category LIKE '%Conservative%'
);

UPDATE funds SET risk_rating = 'medium' WHERE risk_rating IS NULL AND (
       asisa_category LIKE '%Bond%'
    OR asisa_category LIKE '%Fixed Income%'
);

UPDATE funds SET risk_rating = 'low' WHERE risk_rating IS NULL AND (
       asisa_category LIKE '%Money Market%'
    OR asisa_category LIKE '%Cash%'
    OR asisa_category LIKE '%Income%'
);

-- For JSE ETFs (no asisa_category populated), derive coarsely from name keywords.
UPDATE funds SET risk_rating = 'high' WHERE risk_rating IS NULL AND source = 'jse-etf' AND (
       name LIKE '%Equity%' OR name LIKE '%Top 40%' OR name LIKE '%S&P 500%'
    OR name LIKE '%MSCI%' OR name LIKE '%FTSE%' OR name LIKE '%Nasdaq%'
    OR name LIKE '%Property%' OR name LIKE '%REIT%' OR name LIKE '%Gold%'
    OR name LIKE '%Palladium%' OR name LIKE '%Platinum%' OR name LIKE '%Rhodium%'
);

UPDATE funds SET risk_rating = 'medium' WHERE risk_rating IS NULL AND source = 'jse-etf' AND (
       name LIKE '%Bond%' OR name LIKE '%Balanced%' OR name LIKE '%Multi-Asset%'
    OR name LIKE '%Multi Asset%'
);

UPDATE funds SET risk_rating = 'low' WHERE risk_rating IS NULL AND source = 'jse-etf' AND (
       name LIKE '%Money Market%' OR name LIKE '%TRACI%' OR name LIKE '%Income%'
);

-- Anything still NULL gets 'medium' as a safe default. Admin can refine via the UI.
UPDATE funds SET risk_rating = 'medium' WHERE risk_rating IS NULL;