-- =========================================================================
-- SPMS — Skema Tambahan (Pusingan 2)
-- Jalankan fail ini di phpMyAdmin SELEPAS spms_mysql_schema.sql dan
-- migration_remove_mfa_add_security_question.sql sudah diimport.
-- =========================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- =========================================================================
-- A) HUMAN CAPITAL (Non-Financial HR)
-- =========================================================================

CREATE TABLE hr_headcount_matrix (
    matrix_id         CHAR(36) PRIMARY KEY,
    subsidiary_id     CHAR(36) NOT NULL,
    fiscal_year       SMALLINT NOT NULL,
    quarter           ENUM('Q1','Q2','Q3','Q4') NOT NULL,
    role_category     ENUM('strategic','operational','support') NOT NULL,
    department        VARCHAR(50) NULL,
    management_level  ENUM('top_management','senior_management','middle_management','executive','non_executive') NOT NULL,
    headcount         SMALLINT NOT NULL DEFAULT 0,
    created_by        CHAR(36) NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_hr_matrix (subsidiary_id, fiscal_year, quarter, role_category, department, management_level),
    FOREIGN KEY (subsidiary_id) REFERENCES subsidiaries(subsidiary_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE hr_line_items (
    hr_line_item_id   INT AUTO_INCREMENT PRIMARY KEY,
    category          ENUM('headcount_movement','hc_ratio') NOT NULL,
    label             VARCHAR(100) NOT NULL,
    display_order     SMALLINT NOT NULL,
    unit              ENUM('count','percent') NOT NULL DEFAULT 'count'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO hr_line_items (category, label, display_order, unit) VALUES
('headcount_movement','Opening Headcount',1,'count'),
('headcount_movement','Transfer In (Inter Company)',2,'count'),
('headcount_movement','Seconded In',3,'count'),
('headcount_movement','New Hiring',4,'count'),
('headcount_movement','Resigned',5,'count'),
('headcount_movement','End of Contract/Retire',6,'count'),
('headcount_movement','Termination/Retirement/Death',7,'count'),
('headcount_movement','Seconded Out',8,'count'),
('headcount_movement','Transfer Out (Inter Company)',9,'count'),
('headcount_movement','Active Staff',10,'count'),
('hc_ratio','Staff Cost/Opex',11,'percent'),
('hc_ratio','Staff Turnover (Resign/Retire)',12,'percent'),
('hc_ratio','Staff Cost/Revenue',13,'percent');

CREATE TABLE hr_records (
    hr_record_id      CHAR(36) PRIMARY KEY,
    subsidiary_id     CHAR(36) NOT NULL,
    hr_line_item_id   INT NOT NULL,
    fiscal_year       SMALLINT NOT NULL,
    quarter           ENUM('Q1','Q2','Q3','Q4') NULL,
    amount            DECIMAL(12,2) NOT NULL DEFAULT 0,
    note              TEXT NULL,
    created_by        CHAR(36) NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_hr_record (subsidiary_id, hr_line_item_id, fiscal_year, quarter),
    FOREIGN KEY (subsidiary_id) REFERENCES subsidiaries(subsidiary_id) ON DELETE CASCADE,
    FOREIGN KEY (hr_line_item_id) REFERENCES hr_line_items(hr_line_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================================
-- B) KPI SCORECARD
-- =========================================================================

CREATE TABLE kpi_perspectives (
    perspective_id    INT AUTO_INCREMENT PRIMARY KEY,
    label             VARCHAR(60) NOT NULL,
    display_order     SMALLINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO kpi_perspectives (label, display_order) VALUES
('Financials',1),('Customers',2),('Internal Business Processes',3),('Sustainability',4),('Learning & Growth',5);

CREATE TABLE kpi_scorecard_items (
    kpi_id             CHAR(36) PRIMARY KEY,
    subsidiary_id      CHAR(36) NOT NULL,
    fiscal_year        SMALLINT NOT NULL,
    perspective_id     INT NOT NULL,
    strategic_objective VARCHAR(200) NULL,
    kpi_no             SMALLINT NOT NULL,
    kpi_label          VARCHAR(200) NOT NULL,
    unit               ENUM('number','currency_rm_mil','percent','date','count') NOT NULL DEFAULT 'number',
    weightage_pct      DECIMAL(5,2) NOT NULL DEFAULT 0,
    below_threshold    DECIMAL(18,4) NULL,
    threshold          DECIMAL(18,4) NULL,
    target             DECIMAL(18,4) NULL,
    exceed_target      DECIMAL(18,4) NULL,
    stretch_target     DECIMAL(18,4) NULL,
    created_by         CHAR(36) NULL,
    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_kpi_item (subsidiary_id, fiscal_year, kpi_no),
    FOREIGN KEY (subsidiary_id) REFERENCES subsidiaries(subsidiary_id) ON DELETE CASCADE,
    FOREIGN KEY (perspective_id) REFERENCES kpi_perspectives(perspective_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE kpi_actuals (
    actual_id          CHAR(36) PRIMARY KEY,
    kpi_id             CHAR(36) NOT NULL,
    quarter            ENUM('Q1','Q2','Q3','Q4') NOT NULL,
    actual_value       DECIMAL(18,4) NULL,
    note               TEXT NULL,
    created_by         CHAR(36) NULL,
    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_kpi_actual (kpi_id, quarter),
    FOREIGN KEY (kpi_id) REFERENCES kpi_scorecard_items(kpi_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================================
-- C) BUSINESS PLAN
-- =========================================================================

CREATE TABLE business_plan_targets (
    target_id          CHAR(36) PRIMARY KEY,
    subsidiary_id      CHAR(36) NOT NULL,
    fiscal_year        SMALLINT NOT NULL,
    metric_type        ENUM('financial_target','financial_ratio') NOT NULL,
    label              VARCHAR(60) NOT NULL,
    value              DECIMAL(18,4) NOT NULL,
    unit               VARCHAR(20) NOT NULL,
    display_order      SMALLINT NOT NULL,
    UNIQUE KEY uniq_bp_target (subsidiary_id, fiscal_year, metric_type, label),
    FOREIGN KEY (subsidiary_id) REFERENCES subsidiaries(subsidiary_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE business_plan_objectives (
    objective_id       CHAR(36) PRIMARY KEY,
    subsidiary_id      CHAR(36) NOT NULL,
    fiscal_year        SMALLINT NOT NULL,
    objective_no       SMALLINT NOT NULL,
    objective_title    VARCHAR(200) NOT NULL,
    UNIQUE KEY uniq_bp_objective (subsidiary_id, fiscal_year, objective_no),
    FOREIGN KEY (subsidiary_id) REFERENCES subsidiaries(subsidiary_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE business_plan_initiatives (
    initiative_id      CHAR(36) PRIMARY KEY,
    objective_id       CHAR(36) NOT NULL,
    initiative_text    TEXT NOT NULL,
    display_order      SMALLINT NOT NULL,
    FOREIGN KEY (objective_id) REFERENCES business_plan_objectives(objective_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================================
-- D) NARRATIVE NOTES (Portfolio Optimization / IT & Digital Transformation)
-- =========================================================================

CREATE TABLE subsidiary_narrative_notes (
    note_id            CHAR(36) PRIMARY KEY,
    subsidiary_id      CHAR(36) NOT NULL,
    fiscal_year        SMALLINT NOT NULL,
    quarter            ENUM('Q1','Q2','Q3','Q4') NOT NULL,
    category           ENUM('portfolio_optimization','it_digital_transformation') NOT NULL,
    content            TEXT NOT NULL,
    created_by         CHAR(36) NULL,
    updated_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_note (subsidiary_id, fiscal_year, quarter, category),
    FOREIGN KEY (subsidiary_id) REFERENCES subsidiaries(subsidiary_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================================
-- E) API KEYS (Power BI) — SUDAH dicipta dalam spms_mysql_schema.sql.
-- Bahagian ini sengaja dibiarkan kosong (tiada CREATE TABLE berulang) —
-- jika anda nampak "Table api_keys already exists" semasa import fail
-- ini, itu tanda anda guna versi fail lama; abaikan ralat itu dan
-- teruskan ke bahagian F) di bawah sahaja.
-- =========================================================================

-- =========================================================================
-- F) Master data line item audit (reuse audit_logs via app-layer logging —
-- MySQL triggers only added on the two highest-write-volume tables to keep
-- trigger maintenance manageable; app code in db_write() covers the rest)
-- =========================================================================

DELIMITER $$
CREATE TRIGGER trg_hr_records_insert AFTER INSERT ON hr_records FOR EACH ROW
BEGIN
    INSERT INTO audit_logs (table_name, record_id, action, new_value, changed_by)
    VALUES ('hr_records', NEW.hr_record_id, 'INSERT', JSON_OBJECT('amount', NEW.amount), @app_user_id);
END$$

CREATE TRIGGER trg_hr_records_update AFTER UPDATE ON hr_records FOR EACH ROW
BEGIN
    INSERT INTO audit_logs (table_name, record_id, action, old_value, new_value, changed_by)
    VALUES ('hr_records', NEW.hr_record_id, 'UPDATE', JSON_OBJECT('amount', OLD.amount), JSON_OBJECT('amount', NEW.amount), @app_user_id);
END$$

CREATE TRIGGER trg_kpi_items_insert AFTER INSERT ON kpi_scorecard_items FOR EACH ROW
BEGIN
    INSERT INTO audit_logs (table_name, record_id, action, new_value, changed_by)
    VALUES ('kpi_scorecard_items', NEW.kpi_id, 'INSERT', JSON_OBJECT('kpi_label', NEW.kpi_label, 'target', NEW.target), @app_user_id);
END$$
DELIMITER ;

SET FOREIGN_KEY_CHECKS = 1;