-- =========================================================================
-- MIGRATION: Komen Kewangan Utama Boleh Edit (Key Financial Commentary)
-- Jalankan di phpMyAdmin > tab SQL, pada database SPMS sedia ada anda.
--
-- Sebelum ini komen di Subsidiary Monitoring (Dashboard) dijana automatik
-- SAHAJA (fungsi auto_commentary(), tiada cara edit). Jadual ini simpan
-- komen MANUAL Group Strategy — kalau wujud, dipaparkan GANTI auto-jana;
-- kalau tiada/dipadam, sistem kembali ke auto-jana seperti biasa.
-- =========================================================================

CREATE TABLE IF NOT EXISTS subsidiary_commentary (
    commentary_id   CHAR(36) PRIMARY KEY,
    subsidiary_id   CHAR(36) NOT NULL,
    fiscal_year     SMALLINT NOT NULL,
    quarter         ENUM('Q1','Q2','Q3','Q4') NULL,
    month           TINYINT NULL,
    commentary_text TEXT NOT NULL,
    updated_by      CHAR(36) NULL,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_commentary_period (subsidiary_id, fiscal_year, quarter, month),
    FOREIGN KEY (subsidiary_id) REFERENCES subsidiaries(subsidiary_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;