# Rangkuman Query Migrasi & Skema Database

Dokumen ini menggabungkan seluruh query DDL (Data Definition Language) yang sebelumnya tercecer dan terduplikasi menjadi satu berkas SQL utuh yang bersih, terstruktur, dan siap dieksekusi secara berurutan.

---

## 1. Seluruh Query dalam Satu Blok SQL (Siap Eksekusi)

```sql
-- =============================================================================
-- 1. INTEGRASI CORETAX (PAJAK)
-- =============================================================================

CREATE TABLE IF NOT EXISTS `coretax_sync_logs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `invoice_id` INT(11) NULL,
  `status` VARCHAR(128) COLLATE utf8mb4_general_ci NULL,
  `response_code` VARCHAR(128) COLLATE utf8mb4_general_ci NULL,
  `synchronized_at` DATETIME NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- =============================================================================
-- 2. INTEGRASI RABBITMQ (MESSAGE QUEUE & AUDIT WORKER)
-- =============================================================================

CREATE TABLE IF NOT EXISTS `rabbit_publish_shadow` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `transaksi_id` BIGINT(20) UNSIGNED NULL DEFAULT 0,
  `transaksi_no` VARCHAR(64) COLLATE utf8mb3_general_ci NULL DEFAULT '',
  `module` VARCHAR(64) COLLATE utf8mb3_general_ci NOT NULL,
  `action` VARCHAR(64) COLLATE utf8mb3_general_ci NOT NULL,
  `exchange` VARCHAR(128) COLLATE utf8mb3_general_ci NOT NULL,
  `routing_key` VARCHAR(128) COLLATE utf8mb3_general_ci NOT NULL,
  `payload_json` LONGTEXT COLLATE utf8mb3_general_ci NOT NULL COMMENT 'Salinan persis JSON payload yang dikirim ke RabbitMQ',
  `publish_status` ENUM('PUBLISHED','FAILED') COLLATE utf8mb3_general_ci NULL DEFAULT 'PUBLISHED',
  `publish_error` TEXT COLLATE utf8mb3_general_ci NULL,
  `published_by` INT(10) UNSIGNED NULL DEFAULT 1,
  `published_at` DATETIME NOT NULL,
  `worker_status` ENUM('PENDING','PROCESSING','COMPLETED','FAILED') COLLATE utf8mb3_general_ci NULL DEFAULT 'PENDING',
  `worker_response` TEXT COLLATE utf8mb3_general_ci NULL COMMENT 'Hasil output JSON dari CLI Everest',
  `worker_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_transaksi` (`transaksi_id`, `module`),
  KEY `idx_status` (`worker_status`, `published_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


-- =============================================================================
-- 3. MASTER PROJECT: HPP HISTORY & KONTRAK HISTORY
-- =============================================================================

CREATE TABLE IF NOT EXISTS `project_hpp_history` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `project_id` BIGINT(20) NOT NULL,
  `project_nama` VARCHAR(255) COLLATE latin1_swedish_ci NOT NULL,
  `workorder_id` BIGINT(20) NULL DEFAULT 0,
  `komposisi_id` BIGINT(20) NOT NULL DEFAULT 0,
  `produk_id` BIGINT(20) NOT NULL,
  `produk_kode` VARCHAR(100) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `produk_nama` VARCHAR(255) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `hpp_lama` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000,
  `hpp_baru` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000,
  `selisih_hpp` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000,
  `sumber_perubahan` VARCHAR(50) COLLATE latin1_swedish_ci NOT NULL DEFAULT 'PO_PEMBELIAN_1466',
  `ref_transaksi_nomer` VARCHAR(100) COLLATE latin1_swedish_ci NULL,
  `keterangan` TEXT COLLATE latin1_swedish_ci NULL,
  `oleh_id` BIGINT(20) NOT NULL,
  `oleh_nama` VARCHAR(255) COLLATE latin1_swedish_ci NOT NULL,
  `dtime` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_project_prod` (`project_id`, `produk_id`),
  KEY `idx_komposisi` (`komposisi_id`),
  KEY `idx_dtime` (`dtime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

CREATE TABLE IF NOT EXISTS `project_kontrak_history` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` INT(11) NOT NULL,
  `tipe_perubahan` VARCHAR(50) NOT NULL DEFAULT 'REVISI_KONTRAK',
  `jual_bruto_lama` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000,
  `jual_bruto_baru` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000,
  `selisih_jual` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000,
  `budget_lama` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000,
  `budget_baru` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000,
  `selisih_budget` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000,
  `diskon_lama` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000,
  `diskon_baru` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000,
  `profit_lama` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000,
  `profit_baru` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000,
  `keterangan` TEXT NULL,
  `oleh_id` INT(11) NOT NULL DEFAULT 0,
  `oleh_nama` VARCHAR(150) NOT NULL DEFAULT 'System',
  `dtime` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_project_id` (`project_id`),
  KEY `idx_tipe` (`tipe_perubahan`),
  KEY `idx_dtime` (`dtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- =============================================================================
-- 4. MASTER PROJECT: BUDGETING PADA KOMPONEN BIAYA & RAB
-- =============================================================================

ALTER TABLE `project_komponen_biaya_details` 
  ADD COLUMN `harga_budget` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000 AFTER `harga`;

ALTER TABLE `project_komponen_biaya_details_rab` 
  ADD COLUMN `harga_budget` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000 AFTER `harga`;

ALTER TABLE `project_komponen_biaya_details_rab_sub` 
  ADD COLUMN `harga_budget` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000 AFTER `harga`;

ALTER TABLE `project_komponen_biaya_details_rab_sub_tambahan` 
  ADD COLUMN `harga_budget` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000 AFTER `harga`;


-- =============================================================================
-- 5. MASTER PROJECT: AUTOPURCHASE & KOMPOSISI MATERIAL
-- =============================================================================

-- Flag autopurchase di master produk
ALTER TABLE `produk` 
  ADD COLUMN `allow_autopurchase` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0=Manual, 1=Autopurchase Project' AFTER `oleh_nama`;

-- Tambah harga_budget & allow_autopurchase ke seluruh level komposisi
ALTER TABLE `project_komposisi` 
  ADD COLUMN `harga_budget` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000 AFTER `nilai`, 
  ADD COLUMN `allow_autopurchase` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0=Manual, 1=Autopurchase Project' AFTER `trash`;

ALTER TABLE `project_komposisi_workoder` 
  ADD COLUMN `harga_budget` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000 AFTER `nilai`, 
  ADD COLUMN `allow_autopurchase` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0=Manual, 1=Autopurchase Project' AFTER `produk_paket_nama`;

ALTER TABLE `project_komposisi_sub_workoder` 
  ADD COLUMN `harga_budget` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000 AFTER `nilai`, 
  ADD COLUMN `allow_autopurchase` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0=Manual, 1=Autopurchase Project' AFTER `produk_paket_nama`;

ALTER TABLE `project_sub_tasklist_komposisi` 
  ADD COLUMN `harga_budget` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000 AFTER `nilai`,
  ADD COLUMN `allow_autopurchase` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0=Manual, 1=Autopurchase Project' AFTER `produk_paket_nama`;

ALTER TABLE `project_sub_tasklist_komposisi_tambahan` 
  ADD COLUMN `harga_budget` DECIMAL(24,10) NOT NULL DEFAULT 0.0000000000 AFTER `nilai`,
  ADD COLUMN `allow_autopurchase` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0=Manual, 1=Autopurchase Project' AFTER `produk_paket_nama`;

-- Tabel Pool Antrian Pengadaan Pembelian Proyek
CREATE TABLE IF NOT EXISTS `project_purchase_pool` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` INT(11) NOT NULL COMMENT 'Relasi ke project_produk.id',
  `project_nama` VARCHAR(255) COLLATE utf8_general_ci NOT NULL,
  `project_start_id` INT(11) NULL DEFAULT 0 COMMENT 'ID transaksi Kickoff 588st',
  `project_start_nomer` VARCHAR(100) COLLATE utf8_general_ci NULL,
  `cabang_id` INT(11) NOT NULL,
  `supplier_id` INT(11) NULL DEFAULT 0,
  `supplier_nama` VARCHAR(255) COLLATE utf8_general_ci NULL DEFAULT '',
  `produk_id` INT(11) NOT NULL COMMENT 'ID barang FG (produk_dasar_id)',
  `produk_kode` VARCHAR(100) COLLATE utf8_general_ci NULL,
  `produk_nama` VARCHAR(255) COLLATE utf8_general_ci NOT NULL,
  `satuan_nama` VARCHAR(50) COLLATE utf8_general_ci NULL,
  `qty_kebutuhan` DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Qty total dari project_komposisi',
  `qty_po` DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Qty yang sudah dibuatkan PO',
  `qty_sisa` DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'qty_kebutuhan - qty_po',
  `est_harga_beli` DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Estimasi HPP/Nilai dari RAB',
  `status` ENUM('OPEN','PARTIAL','CLOSED','CANCELLED') COLLATE utf8_general_ci NOT NULL DEFAULT 'OPEN',
  `created_dtime` DATETIME NOT NULL,
  `created_by_id` INT(11) NOT NULL,
  `created_by_nama` VARCHAR(100) COLLATE utf8_general_ci NOT NULL,
  `last_updated_dtime` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_project_id` (`project_id`),
  KEY `idx_produk_id` (`produk_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


-- =============================================================================
-- 6. CACHE SISTEM (SYS CACHE TRAY PRIMARY KEY)
-- =============================================================================

ALTER TABLE `sys_cache_tray_transaksi` 
  ADD COLUMN `id` INT AUTO_INCREMENT PRIMARY KEY FIRST;

ALTER TABLE `sys_cache_tray_duedate` 
  ADD COLUMN `id` INT AUTO_INCREMENT PRIMARY KEY FIRST;

ALTER TABLE `sys_cache_tray_payment` 
  ADD COLUMN `id` INT AUTO_INCREMENT PRIMARY KEY FIRST;


-- =============================================================================
-- 7. STOCK OPNAME ASYNCHRONOUS (STAGING & AUDIT LOGS)
-- =============================================================================

CREATE TABLE IF NOT EXISTS `so_headers_staging` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaksi_id` BIGINT UNSIGNED NOT NULL,
  `booking_number` VARCHAR(64) NOT NULL,
  `nomer_nota` VARCHAR(64) NOT NULL,
  `jenis_transaksi` VARCHAR(16) NOT NULL COMMENT '1119, 2229, dll',
  `cabang_id` INT UNSIGNED NOT NULL,
  `gudang_id` INT UNSIGNED NOT NULL,
  `total_item` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` ENUM('QUEUED', 'PROCESSING', 'COMPLETED', 'FAILED') NOT NULL DEFAULT 'QUEUED',
  `retry_count` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `payload_checksum` CHAR(64) NOT NULL COMMENT 'SHA256 hash payload untuk idempotensi',
  `created_by` INT UNSIGNED NOT NULL,
  `approved_by` INT UNSIGNED NOT NULL,
  `error_message` TEXT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  INDEX `idx_status_retry` (`status`, `retry_count`),
  INDEX `idx_transaksi_id` (`transaksi_id`),
  INDEX `idx_checksum` (`payload_checksum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `so_items_staging` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `header_staging_id` BIGINT UNSIGNED NOT NULL,
  `transaksi_id` BIGINT UNSIGNED NOT NULL,
  `produk_id` BIGINT UNSIGNED NOT NULL,
  `produk_kode` VARCHAR(64) NOT NULL,
  `produk_nama` VARCHAR(255) NOT NULL,
  `satuan` VARCHAR(32) NOT NULL,
  `stok_sistem` DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  `qty_fisik` DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  `qty_selisih` DECIMAL(14,4) NOT NULL DEFAULT 0.0000 COMMENT 'qty_fisik - stok_sistem',
  `qty_debet` DECIMAL(14,4) NOT NULL DEFAULT 0.0000 COMMENT 'selisih lebih (+)',
  `qty_kredit` DECIMAL(14,4) NOT NULL DEFAULT 0.0000 COMMENT 'selisih kurang (-)',
  `hpp_satuan` DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `total_nilai_selisih` DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `serial_numbers_json` LONGTEXT NULL COMMENT 'Daftar nomor seri scan/auto-gen',
  `chunk_batch` INT UNSIGNED NOT NULL DEFAULT 1,
  `status` ENUM('PENDING', 'PROCESSED', 'FAILED') NOT NULL DEFAULT 'PENDING',
  INDEX `idx_header_chunk` (`header_staging_id`, `chunk_batch`, `status`),
  CONSTRAINT `fk_so_items_header` FOREIGN KEY (`header_staging_id`) REFERENCES `so_headers_staging` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `so_audit_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `header_staging_id` BIGINT UNSIGNED NOT NULL,
  `transaksi_id` BIGINT UNSIGNED NOT NULL,
  `event_type` VARCHAR(64) NOT NULL COMMENT 'SUBMITTED, WORKER_START, CHUNK_DONE, POSTED_JOURNAL, COMPLETED, FAILED',
  `actor_type` ENUM('USER', 'SYSTEM_WORKER') NOT NULL,
  `actor_id` INT UNSIGNED NOT NULL,
  `details` TEXT NULL,
  `created_at` DATETIME NOT NULL,
  INDEX `idx_trans_event` (`transaksi_id`, `event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

---

## 2. Catatan Khusus

### Frasa `next_step_code dan next_substep_num`
Potongan teks `next_step_code dan next_substep_num` yang ada di teks asli merupakan nama field / kolom alur multi-step transaksi. Jika modul transaksi membutuhkan penambahan kedua kolom ini ke tabel `transaksi`, query-nya adalah sebagai berikut (opsional):
```sql
ALTER TABLE `transaksi` 
  ADD COLUMN `next_step_code` VARCHAR(50) NULL AFTER `step_code`,
  ADD COLUMN `next_substep_num` INT(11) NULL DEFAULT 0 AFTER `substep_num`;
```