CREATE TABLE IF NOT EXISTS calc_nota_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  harga_plat_per_warna DECIMAL(18,2) NOT NULL DEFAULT 0,
  minimum_rim_cetak_per_warna INT UNSIGNED NOT NULL DEFAULT 0,
  biaya_cetak_minimum_per_warna DECIMAL(18,2) NOT NULL DEFAULT 0,
  tarif_oplah_per_rim_per_warna DECIMAL(18,2) NOT NULL DEFAULT 0,
  share_link_expired_hari SMALLINT UNSIGNED NOT NULL DEFAULT 7,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_calc_nota_settings_scope (tenant_id, location_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS calc_nota_papers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  layer_key VARCHAR(40) NOT NULL,
  warna VARCHAR(60) NOT NULL,
  harga_per_rim DECIMAL(18,2) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  deleted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_calc_nota_papers_scope (tenant_id, location_id, layer_key, is_active),
  UNIQUE KEY uq_calc_nota_papers_scope_layer_warna (tenant_id, location_id, layer_key, warna, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS calc_nota_finishings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  ply TINYINT UNSIGNED NOT NULL,
  jumlah_warna_desain SMALLINT UNSIGNED NOT NULL,
  tarif_finishing_per_set_rim DECIMAL(18,2) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  deleted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_calc_nota_finishings_scope (tenant_id, location_id, ply, jumlah_warna_desain, is_active),
  UNIQUE KEY uq_calc_nota_finishings_scope (tenant_id, location_id, ply, jumlah_warna_desain, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS calc_nota_records (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  tipe_hitung VARCHAR(20) NOT NULL,
  judul_pekerjaan VARCHAR(190) NOT NULL,
  customer_name VARCHAR(190) NULL,
  qty_buku INT UNSIGNED NULL,
  minimum_buku INT UNSIGNED NULL,
  sheet_per_buku INT UNSIGNED NOT NULL,
  isi_dalam_1_lembar INT UNSIGNED NOT NULL,
  ply TINYINT UNSIGNED NOT NULL,
  jumlah_warna_desain SMALLINT UNSIGNED NOT NULL,
  warna_layers_json LONGTEXT NULL,
  margin_persen DECIMAL(8,2) NOT NULL DEFAULT 0,
  biaya_lain_lain DECIMAL(18,2) NOT NULL DEFAULT 0,
  lembar_f4_dasar INT UNSIGNED NULL,
  insheet_persen DECIMAL(8,2) NOT NULL DEFAULT 0,
  insheet_lembar_f4 INT UNSIGNED NOT NULL DEFAULT 0,
  insheet_dari_persen INT UNSIGNED NULL,
  total_lembar_f4_cetak INT UNSIGNED NULL,
  rim_per_layer INT UNSIGNED NOT NULL DEFAULT 0,
  total_rim_cetak INT UNSIGNED NOT NULL DEFAULT 0,
  biaya_kertas DECIMAL(18,2) NOT NULL DEFAULT 0,
  biaya_plat DECIMAL(18,2) NOT NULL DEFAULT 0,
  biaya_cetak_minimum DECIMAL(18,2) NOT NULL DEFAULT 0,
  biaya_oplah DECIMAL(18,2) NOT NULL DEFAULT 0,
  biaya_finishing DECIMAL(18,2) NOT NULL DEFAULT 0,
  total_modal DECIMAL(18,2) NOT NULL DEFAULT 0,
  modal_per_buku DECIMAL(18,4) NOT NULL DEFAULT 0,
  harga_jual_per_buku DECIMAL(18,4) NOT NULL DEFAULT 0,
  layer_snapshot_json LONGTEXT NULL,
  review_snapshot_json LONGTEXT NULL,
  deleted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_calc_nota_records_scope_created (tenant_id, location_id, created_at),
  KEY idx_calc_nota_records_scope_title (tenant_id, location_id, judul_pekerjaan),
  KEY idx_calc_nota_records_scope_customer (tenant_id, location_id, customer_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;