-- =========================================================================
-- MIGRATION: Penyesuaian Konsolidasi Group (Adjustment & Cluster Total)
-- Jalankan di phpMyAdmin > tab SQL, pada database SPMS sedia ada anda.
--
-- Fail CSOPL konsolidasi MBI ada lajur KHAS (bukan syarikat individu):
--   TOTAL (jumlah syarikat Commercial), PERMANENT ADJUSTMENT,
--   CURRENT YEAR ADJUSTMENT, CONSOLIDATED SOFP - COMMERCIAL CLUSTER,
--   CONSOLIDATED SOFP - STRATEGIC CLUSTER, CONSOLLIDATED SOPL (Jumlah Besar)
--
-- Jadual ini simpan nilai-nilai tersebut, satu baris setiap
-- cluster+line_item+tempoh, supaya sistem boleh papar jambatan
-- rekonsiliasi "Jumlah Syarikat -> Adjustment -> Konsolidasi Rasmi".
-- =========================================================================

CREATE TABLE IF NOT EXISTS group_adjustments (
    adjustment_id             CHAR(36) PRIMARY KEY,
    fiscal_year                SMALLINT NOT NULL,
    quarter                     ENUM('Q1','Q2','Q3','Q4') NULL,
    month                       TINYINT NULL,
    value_type                  ENUM('ACTUAL','BUDGET') NOT NULL DEFAULT 'ACTUAL',
    cluster                     ENUM('commercial','strategy') NOT NULL,
    line_item_id                INT NOT NULL,
    company_total               DECIMAL(18,2) NOT NULL DEFAULT 0,
    permanent_adjustment        DECIMAL(18,2) NOT NULL DEFAULT 0,
    current_year_adjustment     DECIMAL(18,2) NOT NULL DEFAULT 0,
    consolidated_total          DECIMAL(18,2) NOT NULL DEFAULT 0,
    batch_id                    CHAR(36) NULL,
    UNIQUE KEY uniq_adjustment (fiscal_year, quarter, month, value_type, cluster, line_item_id),
    FOREIGN KEY (line_item_id) REFERENCES financial_line_items(line_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;