-- CostusWorx © 2026 — Feature 5: Extended Asset Types
-- Adds fixed_deposit and offshore to the asset_type ENUM and new metadata columns.

SET NAMES utf8mb4;

ALTER TABLE assets MODIFY COLUMN asset_type
    ENUM('cash','unit_trust','living_annuity','fixed_deposit','offshore') NOT NULL;

ALTER TABLE assets
    ADD COLUMN IF NOT EXISTS interest_rate       DECIMAL(5,4) DEFAULT NULL
        COMMENT 'Fixed deposit or call account rate p.a.',
    ADD COLUMN IF NOT EXISTS maturity_date        DATE         DEFAULT NULL
        COMMENT 'Fixed deposit maturity date',
    ADD COLUMN IF NOT EXISTS currency             CHAR(3)      NOT NULL DEFAULT 'ZAR'
        COMMENT 'ISO 4217 currency code',
    ADD COLUMN IF NOT EXISTS offshore_return_est  DECIMAL(5,4) DEFAULT NULL
        COMMENT 'Estimated offshore annual return',
    ADD COLUMN IF NOT EXISTS offshore_variance    DECIMAL(5,4) NOT NULL DEFAULT 0.0500
        COMMENT 'Monte Carlo variance band for offshore assets';