-- =====================================================================
-- SIPIUTANG - Sistem Informasi Piutang & Penagihan
-- Roto-Rooter Indonesia
-- Skema Database MySQL 5.7+ / MariaDB 10.3+
-- File: 01_schema.sql
-- =====================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

-- =====================================================================
-- KELOMPOK 1 : KEAMANAN & PENGGUNA
-- =====================================================================

DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`        VARCHAR(40)  NOT NULL,
  `name`        VARCHAR(100) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `is_system`   TINYINT(1)   NOT NULL DEFAULT 0,
  `data_scope`  ENUM('all','own_collection','own_sales','own_area') NOT NULL DEFAULT 'all',
  `is_active`   TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME     NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`  DATETIME     NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_roles_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `permissions`;
CREATE TABLE `permissions` (
  `id`     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `module` VARCHAR(60)  NOT NULL,
  `action` VARCHAR(30)  NOT NULL,
  `name`   VARCHAR(120) NOT NULL,
  `sort_order` SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_perm` (`module`,`action`),
  KEY `idx_perm_module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `role_permissions`;
CREATE TABLE `role_permissions` (
  `role_id`       INT UNSIGNED NOT NULL,
  `permission_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `idx_rp_perm` (`permission_id`),
  CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rp_perm` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `business_units`;
CREATE TABLE `business_units` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`       VARCHAR(30)  NOT NULL,
  `name`       VARCHAR(120) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `payment_term_days` SMALLINT DEFAULT NULL,
  `is_active`  TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_bu_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `areas`;
CREATE TABLE `areas` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`       VARCHAR(30)  NOT NULL,
  `name`       VARCHAR(120) NOT NULL,
  `region`     VARCHAR(80)  DEFAULT NULL,
  `payment_term_days` SMALLINT DEFAULT NULL,
  `is_active`  TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_area_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `salespersons`;
CREATE TABLE `salespersons` (
  `id`        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`      VARCHAR(40)  NOT NULL,
  `name`      VARCHAR(120) NOT NULL,
  `type`      ENUM('INDIVIDU','KANAL','UNIT') NOT NULL DEFAULT 'INDIVIDU',
  `area_id`   INT UNSIGNED DEFAULT NULL,
  `email`     VARCHAR(150) DEFAULT NULL,
  `phone`     VARCHAR(40)  DEFAULT NULL,
  `is_active` TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sales_code` (`code`),
  KEY `idx_sales_area` (`area_id`),
  CONSTRAINT `fk_sales_area` FOREIGN KEY (`area_id`) REFERENCES `areas` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `collection_officers`;
CREATE TABLE `collection_officers` (
  `id`        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`      VARCHAR(40)  NOT NULL,
  `name`      VARCHAR(120) NOT NULL,
  `area_id`   INT UNSIGNED DEFAULT NULL,
  `email`     VARCHAR(150) DEFAULT NULL,
  `phone`     VARCHAR(40)  DEFAULT NULL,
  `is_active` TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_co_code` (`code`),
  KEY `idx_co_area` (`area_id`),
  CONSTRAINT `fk_co_area` FOREIGN KEY (`area_id`) REFERENCES `areas` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id`                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username`              VARCHAR(60)  NOT NULL,
  `name`                  VARCHAR(120) NOT NULL,
  `email`                 VARCHAR(150) NOT NULL,
  `password_hash`         VARCHAR(255) NOT NULL,
  `role_id`               INT UNSIGNED NOT NULL,
  `must_change_password`  TINYINT(1)   NOT NULL DEFAULT 1,
  `is_active`             TINYINT(1)   NOT NULL DEFAULT 1,
  `phone`                 VARCHAR(40)  DEFAULT NULL,
  `area_id`               INT UNSIGNED DEFAULT NULL,
  `business_unit_id`      INT UNSIGNED DEFAULT NULL,
  `salesperson_id`        INT UNSIGNED DEFAULT NULL,
  `collection_officer_id` INT UNSIGNED DEFAULT NULL,
  `last_login_at`         DATETIME     DEFAULT NULL,
  `last_login_ip`         VARCHAR(64)  DEFAULT NULL,
  `failed_attempts`       SMALLINT     NOT NULL DEFAULT 0,
  `locked_until`          DATETIME     DEFAULT NULL,
  `password_changed_at`   DATETIME     DEFAULT NULL,
  `created_by`            INT UNSIGNED DEFAULT NULL,
  `created_at`            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`            DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at`            DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_username` (`username`),
  UNIQUE KEY `uq_users_email` (`email`),
  KEY `idx_users_role` (`role_id`),
  KEY `idx_users_active` (`is_active`,`deleted_at`),
  CONSTRAINT `fk_users_role`  FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`),
  CONSTRAINT `fk_users_area`  FOREIGN KEY (`area_id`) REFERENCES `areas` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_users_bu`    FOREIGN KEY (`business_unit_id`) REFERENCES `business_units` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_users_sales` FOREIGN KEY (`salesperson_id`) REFERENCES `salespersons` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_users_co`    FOREIGN KEY (`collection_officer_id`) REFERENCES `collection_officers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `user_roles`;
CREATE TABLE `user_roles` (
  `user_id` INT UNSIGNED NOT NULL,
  `role_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `idx_ur_role` (`role_id`),
  CONSTRAINT `fk_ur_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ur_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `login_attempts`;
CREATE TABLE `login_attempts` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username`   VARCHAR(60) NOT NULL,
  `ip_address` VARCHAR(64) NOT NULL,
  `success`    TINYINT(1)  NOT NULL DEFAULT 0,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_la_lookup` (`username`,`ip_address`,`created_at`),
  KEY `idx_la_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`     INT UNSIGNED DEFAULT NULL,
  `user_name`   VARCHAR(120) DEFAULT NULL,
  `module`      VARCHAR(60)  NOT NULL,
  `entity_type` VARCHAR(60)  DEFAULT NULL,
  `entity_id`   BIGINT UNSIGNED DEFAULT NULL,
  `action`      VARCHAR(40)  NOT NULL,
  `description` VARCHAR(500) DEFAULT NULL,
  `old_values`  LONGTEXT     DEFAULT NULL,
  `new_values`  LONGTEXT     DEFAULT NULL,
  `ip_address`  VARCHAR(64)  DEFAULT NULL,
  `user_agent`  VARCHAR(255) DEFAULT NULL,
  `approval_request_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_al_user` (`user_id`),
  KEY `idx_al_module` (`module`,`created_at`),
  KEY `idx_al_entity` (`entity_type`,`entity_id`),
  KEY `idx_al_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- KELOMPOK 2 : MASTER DATA
-- =====================================================================

DROP TABLE IF EXISTS `payment_terms`;
CREATE TABLE `payment_terms` (
  `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(30)  NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `days` SMALLINT     NOT NULL DEFAULT 30,
  `base_date_type` ENUM('invoice_date','sent_date','received_date','tax_invoice_date','wr_date','manual')
                   NOT NULL DEFAULT 'sent_date',
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active`  TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pt_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `job_categories`;
CREATE TABLE `job_categories` (
  `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(40)  NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_jc_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `job_types`;
CREATE TABLE `job_types` (
  `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(60)  NOT NULL,
  `name` VARCHAR(160) NOT NULL,
  `job_category_id` INT UNSIGNED DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_jt_code` (`code`),
  KEY `idx_jt_cat` (`job_category_id`),
  CONSTRAINT `fk_jt_cat` FOREIGN KEY (`job_category_id`) REFERENCES `job_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `revenue_categories`;
CREATE TABLE `revenue_categories` (
  `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(40)  NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `payment_term_days` SMALLINT DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_rc_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
  `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(40)  NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  `npwp` VARCHAR(40)  DEFAULT NULL,
  `nitku` VARCHAR(40) DEFAULT NULL,
  `billing_address` TEXT DEFAULT NULL,
  `tax_address`     TEXT DEFAULT NULL,
  `contact_person`  VARCHAR(120) DEFAULT NULL,
  `phone`           VARCHAR(60)  DEFAULT NULL,
  `email`           VARCHAR(150) DEFAULT NULL,
  `payment_term_id` INT UNSIGNED DEFAULT NULL,
  `payment_term_days` SMALLINT   DEFAULT NULL,
  `credit_limit`    DECIMAL(18,2) NOT NULL DEFAULT 0,
  `status`          ENUM('AKTIF','TIDAK_AKTIF','BLOKIR') NOT NULL DEFAULT 'AKTIF',
  `area_id`         INT UNSIGNED DEFAULT NULL,
  `business_unit_id` INT UNSIGNED DEFAULT NULL,
  `salesperson_id`  INT UNSIGNED DEFAULT NULL,
  `collection_officer_id` INT UNSIGNED DEFAULT NULL,
  `risk_category`   ENUM('RENDAH','SEDANG','TINGGI','SANGAT_TINGGI') NOT NULL DEFAULT 'RENDAH',
  `risk_score`      DECIMAL(5,2) NOT NULL DEFAULT 0,
  `notes`           TEXT DEFAULT NULL,
  `created_by`      INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cust_code` (`code`),
  KEY `idx_cust_name` (`name`),
  KEY `idx_cust_area` (`area_id`),
  KEY `idx_cust_bu` (`business_unit_id`),
  KEY `idx_cust_sales` (`salesperson_id`),
  KEY `idx_cust_co` (`collection_officer_id`),
  KEY `idx_cust_status` (`status`,`deleted_at`),
  CONSTRAINT `fk_cust_pt`    FOREIGN KEY (`payment_term_id`) REFERENCES `payment_terms` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_cust_area`  FOREIGN KEY (`area_id`) REFERENCES `areas` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_cust_bu`    FOREIGN KEY (`business_unit_id`) REFERENCES `business_units` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_cust_sales` FOREIGN KEY (`salesperson_id`) REFERENCES `salespersons` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_cust_co`    FOREIGN KEY (`collection_officer_id`) REFERENCES `collection_officers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `outlets`;
CREATE TABLE `outlets` (
  `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_id` INT UNSIGNED NOT NULL,
  `code` VARCHAR(50)  NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  `address` TEXT DEFAULT NULL,
  `area_id` INT UNSIGNED DEFAULT NULL,
  `contact_person` VARCHAR(120) DEFAULT NULL,
  `phone` VARCHAR(60) DEFAULT NULL,
  `email` VARCHAR(150) DEFAULT NULL,
  `payment_term_days` SMALLINT DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_outlet_code` (`code`),
  KEY `idx_outlet_cust` (`customer_id`),
  KEY `idx_outlet_name` (`name`),
  KEY `idx_outlet_area` (`area_id`),
  CONSTRAINT `fk_outlet_cust` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_outlet_area` FOREIGN KEY (`area_id`) REFERENCES `areas` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `deduction_types`;
CREATE TABLE `deduction_types` (
  `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(30)  NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `calc_type` ENUM('percent_of_dpp','percent_of_gross','fixed','manual') NOT NULL DEFAULT 'manual',
  `rate` DECIMAL(9,4) NOT NULL DEFAULT 0,
  `target_column` ENUM('pph23','admin','other','credit_note') NOT NULL DEFAULT 'other',
  `reduces_revenue` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` SMALLINT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dt_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `banks`;
CREATE TABLE `banks` (
  `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(30)  NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `account_no`   VARCHAR(60)  DEFAULT NULL,
  `account_name` VARCHAR(150) DEFAULT NULL,
  `branch`       VARCHAR(120) DEFAULT NULL,
  `is_active`    TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_bank_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `revision_types`;
CREATE TABLE `revision_types` (
  `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(40)  NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `default_action` ENUM('replaced','cancelled','revised','written_off') NOT NULL DEFAULT 'replaced',
  `requires_new_invoice` TINYINT(1) NOT NULL DEFAULT 1,
  `affects_revenue` TINYINT(1) NOT NULL DEFAULT 1,
  `keywords` VARCHAR(400) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` SMALLINT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_rt_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `collection_statuses`;
CREATE TABLE `collection_statuses` (
  `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(40)  NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `color` VARCHAR(20) NOT NULL DEFAULT 'secondary',
  `is_final` TINYINT(1) NOT NULL DEFAULT 0,
  `is_dispute` TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order` SMALLINT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cs_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `payment_statuses`;
CREATE TABLE `payment_statuses` (
  `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(40)  NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `color` VARCHAR(20) NOT NULL DEFAULT 'secondary',
  `is_settled` TINYINT(1) NOT NULL DEFAULT 0,
  `is_overdue` TINYINT(1) NOT NULL DEFAULT 0,
  `counts_as_ar` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ps_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `attachment_types`;
CREATE TABLE `attachment_types` (
  `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(40)  NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `sort_order` SMALLINT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_at_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `value_aliases`;
CREATE TABLE `value_aliases` (
  `id`        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `dimension` VARCHAR(40)  NOT NULL,
  `raw_value` VARCHAR(190) NOT NULL,
  `target_id` INT UNSIGNED DEFAULT NULL,
  `secondary_target_id` INT UNSIGNED DEFAULT NULL,
  `canonical_text` VARCHAR(190) DEFAULT NULL,
  `is_auto`   TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_alias` (`dimension`,`raw_value`),
  KEY `idx_alias_dim` (`dimension`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- KELOMPOK 3 : KONFIGURASI PERHITUNGAN
-- =====================================================================

DROP TABLE IF EXISTS `aging_configurations`;
CREATE TABLE `aging_configurations` (
  `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(40)  NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `basis` ENUM('outstanding','net','gross') NOT NULL DEFAULT 'outstanding',
  `reference` ENUM('due_date','invoice_date','sent_date') NOT NULL DEFAULT 'due_date',
  `include_paid` TINYINT(1) NOT NULL DEFAULT 0,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active`  TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ac_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `aging_buckets`;
CREATE TABLE `aging_buckets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `aging_configuration_id` INT UNSIGNED NOT NULL,
  `label` VARCHAR(80) NOT NULL,
  `from_days` INT NOT NULL DEFAULT 0,
  `to_days`   INT DEFAULT NULL,
  `color` VARCHAR(20) NOT NULL DEFAULT 'secondary',
  `sort_order` SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_ab_conf` (`aging_configuration_id`,`sort_order`),
  CONSTRAINT `fk_ab_conf` FOREIGN KEY (`aging_configuration_id`) REFERENCES `aging_configurations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `acp_targets`;
CREATE TABLE `acp_targets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `scope` ENUM('global','area','business_unit','customer','salesperson','job_category') NOT NULL DEFAULT 'global',
  `scope_id` INT UNSIGNED DEFAULT NULL,
  `period_year` SMALLINT NOT NULL,
  `period_month` TINYINT DEFAULT NULL,
  `target_days` DECIMAL(8,2) NOT NULL,
  `tolerance_days` DECIMAL(8,2) NOT NULL DEFAULT 5,
  `notes` VARCHAR(255) DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_acpt` (`scope`,`scope_id`,`period_year`,`period_month`),
  KEY `idx_acpt_period` (`period_year`,`period_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `application_settings`;
CREATE TABLE `application_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_group` VARCHAR(40) NOT NULL,
  `setting_key`   VARCHAR(80) NOT NULL,
  `setting_value` TEXT DEFAULT NULL,
  `value_type`    ENUM('string','int','decimal','bool','json','date','select') NOT NULL DEFAULT 'string',
  `label`         VARCHAR(180) NOT NULL,
  `description`   VARCHAR(400) DEFAULT NULL,
  `options_json`  TEXT DEFAULT NULL,
  `sort_order`    SMALLINT NOT NULL DEFAULT 0,
  `is_editable`   TINYINT(1) NOT NULL DEFAULT 1,
  `updated_by`    INT UNSIGNED DEFAULT NULL,
  `updated_at`    DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_as_key` (`setting_key`),
  KEY `idx_as_group` (`setting_group`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- KELOMPOK 4 : IMPORT
-- =====================================================================

DROP TABLE IF EXISTS `import_batches`;
CREATE TABLE `import_batches` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `batch_no`    VARCHAR(40) NOT NULL,
  `file_name`   VARCHAR(255) NOT NULL,
  `stored_name` VARCHAR(255) DEFAULT NULL,
  `file_size`   BIGINT UNSIGNED DEFAULT 0,
  `sheet_name`  VARCHAR(120) DEFAULT NULL,
  `import_type` ENUM('invoice','revision') NOT NULL DEFAULT 'invoice',
  `mapping_json` LONGTEXT DEFAULT NULL,
  `options_json` LONGTEXT DEFAULT NULL,
  `total_rows`     INT NOT NULL DEFAULT 0,
  `success_rows`   INT NOT NULL DEFAULT 0,
  `failed_rows`    INT NOT NULL DEFAULT 0,
  `duplicate_rows` INT NOT NULL DEFAULT 0,
  `warning_rows`   INT NOT NULL DEFAULT 0,
  `skipped_rows`   INT NOT NULL DEFAULT 0,
  `status` ENUM('draft','validated','processing','completed','completed_with_errors','failed','rolled_back')
           NOT NULL DEFAULT 'draft',
  `error_summary` LONGTEXT DEFAULT NULL,
  `uploaded_by`  INT UNSIGNED DEFAULT NULL,
  `uploaded_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `finished_at`  DATETIME DEFAULT NULL,
  `rolled_back_at` DATETIME DEFAULT NULL,
  `rolled_back_by` INT UNSIGNED DEFAULT NULL,
  `deleted_at`   DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ib_no` (`batch_no`),
  KEY `idx_ib_status` (`status`),
  KEY `idx_ib_user` (`uploaded_by`),
  KEY `idx_ib_date` (`uploaded_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `import_rows`;
CREATE TABLE `import_rows` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `import_batch_id` BIGINT UNSIGNED NOT NULL,
  `row_number` INT NOT NULL,
  `raw_json`   LONGTEXT DEFAULT NULL,
  `normalized_json` LONGTEXT DEFAULT NULL,
  `status` ENUM('ok','warning','failed','duplicate','skipped') NOT NULL DEFAULT 'ok',
  `errors_json` LONGTEXT DEFAULT NULL,
  `warnings_json` LONGTEXT DEFAULT NULL,
  `invoice_id` BIGINT UNSIGNED DEFAULT NULL,
  `revision_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ir_batch` (`import_batch_id`,`status`),
  KEY `idx_ir_invoice` (`invoice_id`),
  CONSTRAINT `fk_ir_batch` FOREIGN KEY (`import_batch_id`) REFERENCES `import_batches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- KELOMPOK 5 : TRANSAKSI INTI
-- =====================================================================

DROP TABLE IF EXISTS `invoices`;
CREATE TABLE `invoices` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_no`     VARCHAR(60)  NOT NULL,
  `tax_invoice_no` VARCHAR(60)  DEFAULT NULL,
  `so_no`          VARCHAR(60)  DEFAULT NULL,
  `invoice_date`   DATE NOT NULL,
  `wr_date`        DATE DEFAULT NULL,
  `print_date`     DATE DEFAULT NULL,
  `input_date`     DATE DEFAULT NULL,
  `sent_date`      DATE DEFAULT NULL,
  `received_date`  DATE DEFAULT NULL,
  `tax_invoice_date` DATE DEFAULT NULL,
  `due_date`       DATE NOT NULL,
  `due_date_basis` VARCHAR(24) NOT NULL DEFAULT 'sent_date',
  `due_date_manual` TINYINT(1) NOT NULL DEFAULT 0,
  `payment_term_id`   INT UNSIGNED DEFAULT NULL,
  `payment_term_days` SMALLINT NOT NULL DEFAULT 30,
  `area_id`            INT UNSIGNED DEFAULT NULL,
  `business_unit_id`   INT UNSIGNED DEFAULT NULL,
  `customer_id`        INT UNSIGNED DEFAULT NULL,
  `outlet_id`          INT UNSIGNED DEFAULT NULL,
  `salesperson_id`     INT UNSIGNED DEFAULT NULL,
  `collection_officer_id` INT UNSIGNED DEFAULT NULL,
  `job_category_id`    INT UNSIGNED DEFAULT NULL,
  `job_type_id`        INT UNSIGNED DEFAULT NULL,
  `revenue_category_id` INT UNSIGNED DEFAULT NULL,
  `send_channel`  VARCHAR(30)  DEFAULT NULL,
  `send_pic`      VARCHAR(120) DEFAULT NULL,
  `notes`         TEXT DEFAULT NULL,
  `gross_amount`  DECIMAL(18,2) NOT NULL DEFAULT 0,
  `dpp_amount`    DECIMAL(18,2) NOT NULL DEFAULT 0,
  `vat_amount`    DECIMAL(18,2) NOT NULL DEFAULT 0,
  `pph23_amount`  DECIMAL(18,2) NOT NULL DEFAULT 0,
  `pph23_rate`    DECIMAL(9,4)  NOT NULL DEFAULT 0,
  `admin_fee_amount` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `other_deduction_amount` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `total_deduction` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `net_amount`      DECIMAL(18,2) NOT NULL DEFAULT 0,
  `credit_note_amount` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `paid_amount`     DECIMAL(18,2) NOT NULL DEFAULT 0,
  `outstanding_amount` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `revenue_amount`  DECIMAL(18,2) NOT NULL DEFAULT 0,
  `is_revenue_counted` TINYINT(1) NOT NULL DEFAULT 1,
  `first_payment_date` DATE DEFAULT NULL,
  `last_payment_date`  DATE DEFAULT NULL,
  `payment_count`      SMALLINT NOT NULL DEFAULT 0,
  `payment_status_code` VARCHAR(30) NOT NULL DEFAULT 'BELUM_JATUH_TEMPO',
  `doc_status` ENUM('active','cancelled','replaced','revised','written_off') NOT NULL DEFAULT 'active',
  `replaced_by_invoice_id` BIGINT UNSIGNED DEFAULT NULL,
  `replaces_invoice_id`    BIGINT UNSIGNED DEFAULT NULL,
  `revision_id`            BIGINT UNSIGNED DEFAULT NULL,
  `aging_days`      INT DEFAULT NULL,
  `collection_days` INT DEFAULT NULL,
  `days_late`       INT DEFAULT NULL,
  `aging_recalc_date` DATE DEFAULT NULL,
  `dq_flags`   TEXT DEFAULT NULL,
  `source`     ENUM('import','manual','revision') NOT NULL DEFAULT 'manual',
  `import_batch_id` BIGINT UNSIGNED DEFAULT NULL,
  `import_row_no`   INT DEFAULT NULL,
  `source_sheet`    VARCHAR(60) DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `updated_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inv_no` (`invoice_no`),
  KEY `idx_inv_tax` (`tax_invoice_no`),
  KEY `idx_inv_so` (`so_no`),
  KEY `idx_inv_cust` (`customer_id`),
  KEY `idx_inv_outlet` (`outlet_id`),
  KEY `idx_inv_date` (`invoice_date`),
  KEY `idx_inv_due` (`due_date`),
  KEY `idx_inv_status` (`payment_status_code`),
  KEY `idx_inv_doc` (`doc_status`),
  KEY `idx_inv_area` (`area_id`),
  KEY `idx_inv_bu` (`business_unit_id`),
  KEY `idx_inv_sales` (`salesperson_id`),
  KEY `idx_inv_co` (`collection_officer_id`),
  KEY `idx_inv_jobcat` (`job_category_id`),
  KEY `idx_inv_revcat` (`revenue_category_id`),
  KEY `idx_inv_outstanding` (`outstanding_amount`),
  KEY `idx_inv_worklist` (`doc_status`,`payment_status_code`,`due_date`),
  KEY `idx_inv_period` (`invoice_date`,`area_id`),
  KEY `idx_inv_lastpay` (`last_payment_date`),
  KEY `idx_inv_batch` (`import_batch_id`),
  KEY `idx_inv_deleted` (`deleted_at`),
  CONSTRAINT `fk_inv_cust`   FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_inv_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_inv_area`   FOREIGN KEY (`area_id`) REFERENCES `areas` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_inv_bu`     FOREIGN KEY (`business_unit_id`) REFERENCES `business_units` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_inv_sales`  FOREIGN KEY (`salesperson_id`) REFERENCES `salespersons` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_inv_co`     FOREIGN KEY (`collection_officer_id`) REFERENCES `collection_officers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_inv_jc`     FOREIGN KEY (`job_category_id`) REFERENCES `job_categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_inv_jt`     FOREIGN KEY (`job_type_id`) REFERENCES `job_types` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_inv_rc`     FOREIGN KEY (`revenue_category_id`) REFERENCES `revenue_categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_inv_pt`     FOREIGN KEY (`payment_term_id`) REFERENCES `payment_terms` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `invoice_deductions`;
CREATE TABLE `invoice_deductions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id` BIGINT UNSIGNED NOT NULL,
  `deduction_type_id` INT UNSIGNED NOT NULL,
  `base_amount` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `rate`   DECIMAL(9,4) NOT NULL DEFAULT 0,
  `amount` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `notes`  VARCHAR(255) DEFAULT NULL,
  `is_manual` TINYINT(1) NOT NULL DEFAULT 0,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_id_inv` (`invoice_id`),
  KEY `idx_id_type` (`deduction_type_id`),
  CONSTRAINT `fk_id_inv`  FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_id_type` FOREIGN KEY (`deduction_type_id`) REFERENCES `deduction_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `payment_no`   VARCHAR(40) NOT NULL,
  `payment_date` DATE NOT NULL,
  `customer_id`  INT UNSIGNED DEFAULT NULL,
  `bank_id`      INT UNSIGNED DEFAULT NULL,
  `method` ENUM('TRANSFER','TUNAI','CEK','BILYET_GIRO','VIRTUAL_ACCOUNT','KARTU_KREDIT','KOMPENSASI','LAINNYA')
           NOT NULL DEFAULT 'TRANSFER',
  `reference_no` VARCHAR(80) DEFAULT NULL,
  `payment_type` ENUM('PELUNASAN','SEBAGIAN','DP','RETENSI','REFUND','REVERSAL','PENYESUAIAN')
           NOT NULL DEFAULT 'PELUNASAN',
  `amount`         DECIMAL(18,2) NOT NULL DEFAULT 0,
  `allocated_amount` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `notes` TEXT DEFAULT NULL,
  `status` ENUM('draft','posted','reversed') NOT NULL DEFAULT 'posted',
  `approval_status` ENUM('not_required','pending','approved','rejected') NOT NULL DEFAULT 'approved',
  `approval_request_id` BIGINT UNSIGNED DEFAULT NULL,
  `reversal_of_payment_id` BIGINT UNSIGNED DEFAULT NULL,
  `reversal_reason` VARCHAR(400) DEFAULT NULL,
  `created_by`  INT UNSIGNED DEFAULT NULL,
  `approved_by` INT UNSIGNED DEFAULT NULL,
  `approved_at` DATETIME DEFAULT NULL,
  `import_batch_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pay_no` (`payment_no`),
  KEY `idx_pay_date` (`payment_date`),
  KEY `idx_pay_cust` (`customer_id`),
  KEY `idx_pay_status` (`status`,`approval_status`),
  KEY `idx_pay_bank` (`bank_id`),
  KEY `idx_pay_ref` (`reference_no`),
  KEY `idx_pay_deleted` (`deleted_at`),
  CONSTRAINT `fk_pay_cust` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_pay_bank` FOREIGN KEY (`bank_id`) REFERENCES `banks` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `payment_allocations`;
CREATE TABLE `payment_allocations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `payment_id` BIGINT UNSIGNED NOT NULL,
  `invoice_id` BIGINT UNSIGNED NOT NULL,
  `amount` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `notes`  VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_pa_pay` (`payment_id`),
  KEY `idx_pa_inv` (`invoice_id`),
  CONSTRAINT `fk_pa_pay` FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pa_inv` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `credit_notes`;
CREATE TABLE `credit_notes` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `cn_no` VARCHAR(40) NOT NULL,
  `cn_date` DATE NOT NULL,
  `invoice_id` BIGINT UNSIGNED NOT NULL,
  `cn_type` ENUM('NOTA_KREDIT','PENYESUAIAN','HAPUS_BUKU','DISKON') NOT NULL DEFAULT 'NOTA_KREDIT',
  `amount` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `reason` VARCHAR(500) DEFAULT NULL,
  `status` ENUM('draft','pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `approval_request_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_by`  INT UNSIGNED DEFAULT NULL,
  `approved_by` INT UNSIGNED DEFAULT NULL,
  `approved_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cn_no` (`cn_no`),
  KEY `idx_cn_inv` (`invoice_id`),
  KEY `idx_cn_status` (`status`),
  CONSTRAINT `fk_cn_inv` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `invoice_revisions`;
CREATE TABLE `invoice_revisions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `rf_no`   VARCHAR(40) NOT NULL,
  `rf_date` DATE DEFAULT NULL,
  `rf_date_raw` VARCHAR(60) DEFAULT NULL,
  `old_invoice_id`   BIGINT UNSIGNED DEFAULT NULL,
  `old_invoice_no`   VARCHAR(60) DEFAULT NULL,
  `old_invoice_date` DATE DEFAULT NULL,
  `new_invoice_id`   BIGINT UNSIGNED DEFAULT NULL,
  `new_invoice_no`   VARCHAR(60) DEFAULT NULL,
  `new_invoice_date` DATE DEFAULT NULL,
  `original_amount`  DECIMAL(18,2) NOT NULL DEFAULT 0,
  `revised_amount`   DECIMAL(18,2) NOT NULL DEFAULT 0,
  `revision_type_id` INT UNSIGNED DEFAULT NULL,
  `old_invoice_action` ENUM('replaced','cancelled','revised','written_off') NOT NULL DEFAULT 'replaced',
  `reason` TEXT DEFAULT NULL,
  `status` ENUM('draft','pending','approved','rejected','menunggu_pencocokan') NOT NULL DEFAULT 'pending',
  `approval_request_id` BIGINT UNSIGNED DEFAULT NULL,
  `match_notes` VARCHAR(400) DEFAULT NULL,
  `requested_by` INT UNSIGNED DEFAULT NULL,
  `approved_by`  INT UNSIGNED DEFAULT NULL,
  `approved_at`  DATETIME DEFAULT NULL,
  `applied_at`   DATETIME DEFAULT NULL,
  `source` ENUM('import','manual') NOT NULL DEFAULT 'manual',
  `import_batch_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_rev_no` (`rf_no`),
  KEY `idx_rev_old` (`old_invoice_id`),
  KEY `idx_rev_new` (`new_invoice_id`),
  KEY `idx_rev_oldno` (`old_invoice_no`),
  KEY `idx_rev_status` (`status`),
  KEY `idx_rev_date` (`rf_date`),
  CONSTRAINT `fk_rev_old`  FOREIGN KEY (`old_invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_rev_new`  FOREIGN KEY (`new_invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_rev_type` FOREIGN KEY (`revision_type_id`) REFERENCES `revision_types` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `collection_followups`;
CREATE TABLE `collection_followups` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id`   BIGINT UNSIGNED DEFAULT NULL,
  `customer_id`  INT UNSIGNED DEFAULT NULL,
  `collection_officer_id` INT UNSIGNED DEFAULT NULL,
  `followup_date` DATE NOT NULL,
  `method` ENUM('TELEPON','WHATSAPP','EMAIL','KUNJUNGAN','SURAT','PORTAL','LAINNYA') NOT NULL DEFAULT 'TELEPON',
  `contact_person` VARCHAR(120) DEFAULT NULL,
  `contact_phone`  VARCHAR(60)  DEFAULT NULL,
  `result` TEXT DEFAULT NULL,
  `collection_status_id` INT UNSIGNED DEFAULT NULL,
  `priority` ENUM('RENDAH','NORMAL','TINGGI','KRITIS') NOT NULL DEFAULT 'NORMAL',
  `next_followup_date` DATE DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cf_inv` (`invoice_id`),
  KEY `idx_cf_cust` (`customer_id`),
  KEY `idx_cf_co` (`collection_officer_id`),
  KEY `idx_cf_date` (`followup_date`),
  KEY `idx_cf_next` (`next_followup_date`),
  KEY `idx_cf_status` (`collection_status_id`),
  CONSTRAINT `fk_cf_inv`  FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cf_cust` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_cf_co`   FOREIGN KEY (`collection_officer_id`) REFERENCES `collection_officers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_cf_cs`   FOREIGN KEY (`collection_status_id`) REFERENCES `collection_statuses` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `promise_to_pay`;
CREATE TABLE `promise_to_pay` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id`  BIGINT UNSIGNED DEFAULT NULL,
  `customer_id` INT UNSIGNED DEFAULT NULL,
  `followup_id` BIGINT UNSIGNED DEFAULT NULL,
  `promise_date`   DATE NOT NULL,
  `promise_amount` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `actual_paid`    DECIMAL(18,2) NOT NULL DEFAULT 0,
  `status` ENUM('TERBUKA','DIPENUHI','SEBAGIAN','GAGAL','DIBATALKAN') NOT NULL DEFAULT 'TERBUKA',
  `evaluated_at` DATETIME DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ptp_inv` (`invoice_id`),
  KEY `idx_ptp_cust` (`customer_id`),
  KEY `idx_ptp_date` (`promise_date`),
  KEY `idx_ptp_status` (`status`),
  CONSTRAINT `fk_ptp_inv` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ptp_fu`  FOREIGN KEY (`followup_id`) REFERENCES `collection_followups` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `attachments`;
CREATE TABLE `attachments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `entity_type` VARCHAR(40) NOT NULL,
  `entity_id`   BIGINT UNSIGNED NOT NULL,
  `attachment_type_id` INT UNSIGNED DEFAULT NULL,
  `file_name`   VARCHAR(255) NOT NULL,
  `stored_name` VARCHAR(255) NOT NULL,
  `mime_type`   VARCHAR(120) DEFAULT NULL,
  `file_size`   BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `checksum`    VARCHAR(64) DEFAULT NULL,
  `description` VARCHAR(400) DEFAULT NULL,
  `uploaded_by` INT UNSIGNED DEFAULT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at`  DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_att_entity` (`entity_type`,`entity_id`),
  KEY `idx_att_type` (`attachment_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- KELOMPOK 6 : PERSETUJUAN
-- =====================================================================

DROP TABLE IF EXISTS `approval_flows`;
CREATE TABLE `approval_flows` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`   VARCHAR(50) NOT NULL,
  `module` VARCHAR(60) NOT NULL,
  `action` VARCHAR(60) NOT NULL,
  `name`   VARCHAR(150) NOT NULL,
  `level_count` TINYINT NOT NULL DEFAULT 1,
  `approver_roles_json` VARCHAR(255) DEFAULT NULL,
  `threshold_amount` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_af_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `approval_requests`;
CREATE TABLE `approval_requests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_no` VARCHAR(40) NOT NULL,
  `flow_id`    INT UNSIGNED DEFAULT NULL,
  `module`      VARCHAR(60) NOT NULL,
  `entity_type` VARCHAR(60) NOT NULL,
  `entity_id`   BIGINT UNSIGNED DEFAULT NULL,
  `action`      VARCHAR(60) NOT NULL,
  `title`       VARCHAR(255) DEFAULT NULL,
  `amount`      DECIMAL(18,2) NOT NULL DEFAULT 0,
  `payload_json` LONGTEXT DEFAULT NULL,
  `status` ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `current_level` TINYINT NOT NULL DEFAULT 1,
  `total_level`   TINYINT NOT NULL DEFAULT 1,
  `notes` TEXT DEFAULT NULL,
  `requested_by` INT UNSIGNED DEFAULT NULL,
  `decided_by`   INT UNSIGNED DEFAULT NULL,
  `decided_at`   DATETIME DEFAULT NULL,
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ar_no` (`request_no`),
  KEY `idx_ar_status` (`status`,`current_level`),
  KEY `idx_ar_entity` (`entity_type`,`entity_id`),
  KEY `idx_ar_module` (`module`,`action`),
  KEY `idx_ar_requester` (`requested_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `approval_histories`;
CREATE TABLE `approval_histories` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `approval_request_id` BIGINT UNSIGNED NOT NULL,
  `level`    TINYINT NOT NULL DEFAULT 1,
  `actor_id` INT UNSIGNED DEFAULT NULL,
  `actor_name` VARCHAR(120) DEFAULT NULL,
  `decision` ENUM('submitted','approved','rejected','cancelled','commented') NOT NULL,
  `notes`    TEXT DEFAULT NULL,
  `ip_address` VARCHAR(64) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ah_req` (`approval_request_id`),
  CONSTRAINT `fk_ah_req` FOREIGN KEY (`approval_request_id`) REFERENCES `approval_requests` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- KELOMPOK 7 : REPORT BUILDER
-- =====================================================================

DROP TABLE IF EXISTS `report_templates`;
CREATE TABLE `report_templates` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(60) DEFAULT NULL,
  `name` VARCHAR(180) NOT NULL,
  `description` VARCHAR(500) DEFAULT NULL,
  `data_source` VARCHAR(40) NOT NULL DEFAULT 'invoices',
  `chart_type`  VARCHAR(20) NOT NULL DEFAULT 'none',
  `chart_config_json` TEXT DEFAULT NULL,
  `group_by_json` TEXT DEFAULT NULL,
  `pivot_json`    TEXT DEFAULT NULL,
  `options_json`  TEXT DEFAULT NULL,
  `owner_id`   INT UNSIGNED DEFAULT NULL,
  `visibility` ENUM('private','shared','public') NOT NULL DEFAULT 'private',
  `is_system`  TINYINT(1) NOT NULL DEFAULT 0,
  `use_count`  INT NOT NULL DEFAULT 0,
  `last_used_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_rt_code2` (`code`),
  KEY `idx_rt_owner` (`owner_id`),
  KEY `idx_rt_vis` (`visibility`),
  KEY `idx_rt_src` (`data_source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `report_template_columns`;
CREATE TABLE `report_template_columns` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_template_id` INT UNSIGNED NOT NULL,
  `field_key` VARCHAR(60) NOT NULL,
  `label`     VARCHAR(120) DEFAULT NULL,
  `aggregate` ENUM('none','sum','count','avg','min','max','pct','wavg','dcount') NOT NULL DEFAULT 'none',
  `sort_order` SMALLINT NOT NULL DEFAULT 0,
  `sort_dir`   ENUM('','asc','desc') NOT NULL DEFAULT '',
  `sort_priority` SMALLINT NOT NULL DEFAULT 0,
  `width`  SMALLINT DEFAULT NULL,
  `format` VARCHAR(20) DEFAULT NULL,
  `is_subtotal` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_rtc_tpl` (`report_template_id`,`sort_order`),
  CONSTRAINT `fk_rtc_tpl` FOREIGN KEY (`report_template_id`) REFERENCES `report_templates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `report_template_filters`;
CREATE TABLE `report_template_filters` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_template_id` INT UNSIGNED NOT NULL,
  `field_key` VARCHAR(60) NOT NULL,
  `operator`  VARCHAR(20) NOT NULL DEFAULT 'eq',
  `value1` VARCHAR(500) DEFAULT NULL,
  `value2` VARCHAR(500) DEFAULT NULL,
  `conjunction` ENUM('AND','OR') NOT NULL DEFAULT 'AND',
  `sort_order` SMALLINT NOT NULL DEFAULT 0,
  `is_prompt`  TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_rtf_tpl` (`report_template_id`,`sort_order`),
  CONSTRAINT `fk_rtf_tpl` FOREIGN KEY (`report_template_id`) REFERENCES `report_templates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `report_template_shares`;
CREATE TABLE `report_template_shares` (
  `report_template_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `can_edit` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`report_template_id`,`user_id`),
  CONSTRAINT `fk_rts_tpl`  FOREIGN KEY (`report_template_id`) REFERENCES `report_templates` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rts_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- KELOMPOK 8 : PENDUKUNG
-- =====================================================================

DROP TABLE IF EXISTS `saved_filters`;
CREATE TABLE `saved_filters` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `module`  VARCHAR(60) NOT NULL,
  `name`    VARCHAR(150) NOT NULL,
  `filters_json` TEXT DEFAULT NULL,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sf_user` (`user_id`,`module`),
  CONSTRAINT `fk_sf_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `ar_period_snapshots`;
CREATE TABLE `ar_period_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `snapshot_date` DATE NOT NULL,
  `scope` VARCHAR(30) NOT NULL DEFAULT 'global',
  `scope_id` INT UNSIGNED DEFAULT NULL,
  `ar_balance`  DECIMAL(18,2) NOT NULL DEFAULT 0,
  `invoice_count` INT NOT NULL DEFAULT 0,
  `computed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_arps` (`snapshot_date`,`scope`,`scope_id`),
  KEY `idx_arps_date` (`snapshot_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `backup_logs`;
CREATE TABLE `backup_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `file_name` VARCHAR(255) NOT NULL,
  `file_size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `type` ENUM('manual','otomatis') NOT NULL DEFAULT 'manual',
  `status` ENUM('sukses','gagal') NOT NULL DEFAULT 'sukses',
  `message` VARCHAR(500) DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_bl_date` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
