-- =========================================================================
-- MIGRATION: Sejarah Import & Rollback (Upload Monthly Actuals)
-- Jalankan di phpMyAdmin > tab SQL, pada database SPMS sedia ada anda.
--
-- 2 jadual:
--   import_batches      — satu rekod setiap kali "Sahkan & Simpan" ditekan
--   import_batch_items   — SETIAP nilai yang disentuh dalam batch itu, dengan
--                          nilai SEBELUM (previous_amount) supaya rollback
--                          boleh PULIHKAN nilai asal (bukan sekadar padam —
--                          penting kalau import menimpa data sedia ada).
-- =========================================================================

CREATE TABLE IF NOT EXISTS import_batches (
    batch_id        CHAR(36) PRIMARY KEY,
    import_type     VARCHAR(50) NOT NULL,      -- cth. 'monthly_actuals'
    filename        VARCHAR(255) NULL,
    fiscal_year     SMALLINT NULL,
    period_label    VARCHAR(20) NULL,          -- cth. 'May 2026'
    success_count   INT NOT NULL DEFAULT 0,
    fail_count      INT NOT NULL DEFAULT 0,
    skip_count      INT NOT NULL DEFAULT 0,
    status          ENUM('completed','rolled_back') NOT NULL DEFAULT 'completed',
    created_by      CHAR(36) NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    rolled_back_by  CHAR(36) NULL,
    rolled_back_at  DATETIME NULL,
    FOREIGN KEY (created_by) REFERENCES app_users(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS import_batch_items (
    item_id           CHAR(36) PRIMARY KEY,
    batch_id          CHAR(36) NOT NULL,
    record_id         CHAR(36) NOT NULL,       -- financial_records.record_id yang disentuh
    was_new_row       TINYINT(1) NOT NULL DEFAULT 0,  -- 1 = rekod baharu (rollback = padam), 0 = timpa (rollback = pulih nilai lama)
    previous_amount   DECIMAL(18,2) NULL,      -- NULL kalau was_new_row=1
    new_amount        DECIMAL(18,2) NOT NULL,
    FOREIGN KEY (batch_id) REFERENCES import_batches(batch_id) ON DELETE CASCADE,
    INDEX idx_batch (batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;