-- =========================================================================
-- MIGRATION: Modul Laporan Board (Overall Performance, Division Analysis,
-- Peer Benchmarking) — Remarks/Commentary boleh edit + data Peer Companies
-- Jalankan di phpMyAdmin > tab SQL, pada database SPMS sedia ada anda.
-- =========================================================================

-- Remarks/Commentary boleh edit — dikongsi untuk laporan "Overall" (Group)
-- dan "Division" (per-sector). Boleh ada BEBERAPA remark (bullet points)
-- setiap laporan/tempoh, susun ikut remark_order.
CREATE TABLE IF NOT EXISTS report_remarks (
    remark_id      CHAR(36) PRIMARY KEY,
    report_type    ENUM('overall','division') NOT NULL,
    sector         VARCHAR(100) NULL,           -- NULL untuk 'overall'; wajib untuk 'division'
    fiscal_year    SMALLINT NOT NULL,
    quarter        ENUM('Q1','Q2','Q3','Q4') NOT NULL,
    remark_order   SMALLINT NOT NULL DEFAULT 1,
    content        TEXT NOT NULL,
    created_by     CHAR(36) NULL,
    updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_remark (report_type, sector, fiscal_year, quarter, remark_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Syarikat sebanding (peer) LUARAN — bukan anak syarikat MBI, data pasaran
-- yang dimasukkan admin secara manual untuk perbandingan margin.
CREATE TABLE IF NOT EXISTS peer_companies (
    peer_id        CHAR(36) PRIMARY KEY,
    sector         VARCHAR(100) NOT NULL,
    peer_name      VARCHAR(150) NOT NULL,
    gp_margin      DECIMAL(6,2) NULL,
    pat_margin     DECIMAL(6,2) NULL,
    ebitda_margin  DECIMAL(6,2) NULL,
    display_order  SMALLINT NOT NULL DEFAULT 1,
    created_by     CHAR(36) NULL,
    updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;