-- ============================================================
-- Fund Master Data & Performance — CostusWorx © 2026
-- Run this after schema.sql
-- ============================================================

-- ── Fund master ───────────────────────────────────────────────
CREATE TABLE funds (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    isin            VARCHAR(12)  DEFAULT NULL UNIQUE,     -- e.g. ZAE000164462
    jse_code        VARCHAR(12)  DEFAULT NULL,            -- e.g. STXSWX (ETFs only)
    asisa_code      VARCHAR(30)  DEFAULT NULL,            -- ASISA classification code
    name            VARCHAR(200) NOT NULL,
    fund_manager    VARCHAR(100) NOT NULL,                -- Allan Gray, Coronation, Satrix, etc.
    asisa_category  VARCHAR(100) DEFAULT NULL,            -- SA Equity General, MA High Equity, etc.
    fund_type       ENUM('unit_trust','etf','living_annuity','money_market','other') NOT NULL DEFAULT 'unit_trust',
    benchmark_index VARCHAR(50)  DEFAULT NULL,            -- J203, ALBI, STeFI, etc.
    ter             DECIMAL(5,4) DEFAULT NULL,            -- Total Expense Ratio e.g. 0.0085 = 0.85%
    risk_rating     ENUM('low','medium_low','medium','medium_high','high') DEFAULT NULL,
    inception_date  DATE         DEFAULT NULL,
    fact_sheet_url  VARCHAR(255) DEFAULT NULL,
    is_active       TINYINT(1)   NOT NULL DEFAULT 1,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_isin         (isin),
    INDEX idx_jse_code     (jse_code),
    INDEX idx_fund_manager (fund_manager),
    INDEX idx_fund_type    (fund_type),
    FULLTEXT idx_search    (name, fund_manager, asisa_category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Fund annual performance ───────────────────────────────────
CREATE TABLE fund_performance (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fund_id        INT UNSIGNED  NOT NULL,
    year           YEAR          NOT NULL,
    annual_return  DECIMAL(8,4)  NOT NULL,   -- e.g. 0.1523 = 15.23%
    source         VARCHAR(120)  NOT NULL DEFAULT 'ASISA/Morningstar',
    verified       TINYINT(1)    NOT NULL DEFAULT 0,
    UNIQUE KEY uq_fund_year (fund_id, year),
    FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE,
    INDEX idx_fund_id (fund_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Link assets to funds ──────────────────────────────────────
ALTER TABLE assets
    ADD COLUMN fund_id  INT UNSIGNED DEFAULT NULL AFTER asset_type,
    ADD COLUMN isin     VARCHAR(12)  DEFAULT NULL AFTER fund_id,
    ADD COLUMN jse_code VARCHAR(12)  DEFAULT NULL AFTER isin,
    ADD FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE SET NULL,
    ADD INDEX idx_fund (fund_id);