-- CostusWorx © 2026 — Feature 3: Custom Fund Builder
-- Extends funds table and adds blend component breakdown.

SET NAMES utf8mb4;

ALTER TABLE funds
    ADD COLUMN IF NOT EXISTS is_custom   TINYINT(1)   NOT NULL DEFAULT 0
        COMMENT '1 = adviser-created blended fund',
    ADD COLUMN IF NOT EXISTS created_by  INT UNSIGNED DEFAULT NULL
        COMMENT 'users.id of the adviser who created the blend',
    ADD COLUMN IF NOT EXISTS tenant_id   INT UNSIGNED DEFAULT NULL
        COMMENT 'NULL = platform-wide fund; set for adviser-created blends';

CREATE TABLE IF NOT EXISTS fund_components (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_fund_id    INT UNSIGNED  NOT NULL  COMMENT 'The blended fund',
    component_fund_id INT UNSIGNED  NOT NULL  COMMENT 'Underlying unit trust',
    weight            DECIMAL(5,2)  NOT NULL  COMMENT 'e.g. 40.00 = 40%',
    created_at        DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_fund_id)    REFERENCES funds(id) ON DELETE CASCADE,
    FOREIGN KEY (component_fund_id) REFERENCES funds(id),
    INDEX idx_parent (parent_fund_id),
    UNIQUE KEY uq_parent_component (parent_fund_id, component_fund_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;