/* ============================================================
   PATCH 17 — ADD-ON KALKULATOR OFFSET (DB STRUCTURE)
   MySQL / InnoDB
   ============================================================ */

/* =========================
   MASTER: BAHAN OFFSET
   ========================= */
CREATE TABLE IF NOT EXISTS calc_offset_materials (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  -- nullable: kalau master bahan dibedakan per lokasi; kalau global tenant, isi NULL
  location_id BIGINT UNSIGNED NULL,

  name VARCHAR(120) NOT NULL,
  gsm INT UNSIGNED NULL,

  plano_p_cm DECIMAL(10,2) NOT NULL,
  plano_l_cm DECIMAL(10,2) NOT NULL,

  price_per_rim DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  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,

  PRIMARY KEY (id),
  KEY idx_tenant (tenant_id),
  KEY idx_tenant_loc (tenant_id, location_id),
  KEY idx_active (tenant_id, is_active),
  KEY idx_name (tenant_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


/* =========================
   MASTER: MESIN OFFSET
   ========================= */
CREATE TABLE IF NOT EXISTS calc_offset_machines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED 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,

  PRIMARY KEY (id),
  KEY idx_tenant (tenant_id),
  KEY idx_tenant_loc (tenant_id, location_id),
  KEY idx_active (tenant_id, is_active),
  KEY idx_name (tenant_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


/* =========================
   MASTER: TARIF OFFSET (PER MESIN)
   - harga_plat_per_plat
   - harga_cetak_per_plat (min charge 1000/plat)
   - harga_oplah_per_lembar (kelebihan di atas 1000) => dihitung PER PLAT
   - tarif beda untuk <=300 gsm dan >300 gsm
   ========================= */
CREATE TABLE IF NOT EXISTS calc_offset_tariffs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,

  machine_id BIGINT UNSIGNED NOT NULL,

  -- charge minimal yang disepakati (default 1000)
  min_charge_qty INT UNSIGNED NOT NULL DEFAULT 1000,

  plate_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,

  print_price_le_300 DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  oplah_price_le_300 DECIMAL(15,4) NOT NULL DEFAULT 0.0000,

  print_price_gt_300 DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  oplah_price_gt_300 DECIMAL(15,4) NOT NULL DEFAULT 0.0000,

  notes VARCHAR(255) 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,

  PRIMARY KEY (id),
  KEY idx_tenant (tenant_id),
  KEY idx_tenant_loc (tenant_id, location_id),
  KEY idx_machine (tenant_id, machine_id),
  KEY idx_active (tenant_id, is_active),

  CONSTRAINT fk_calc_tariff_machine
    FOREIGN KEY (machine_id) REFERENCES calc_offset_machines(id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


/* =========================
   MASTER: LAMINASI
   - harga per cm2
   ========================= */
CREATE TABLE IF NOT EXISTS calc_offset_laminations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,

  name VARCHAR(120) NOT NULL,
  price_per_cm2 DECIMAL(15,6) NOT NULL DEFAULT 0.000000,
  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,

  PRIMARY KEY (id),
  KEY idx_tenant (tenant_id),
  KEY idx_tenant_loc (tenant_id, location_id),
  KEY idx_active (tenant_id, is_active),
  KEY idx_name (tenant_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


/* ============================================================
   TRANSAKSI/HISTORI: calc_offset_records
   Menyimpan snapshot input + hasil hitung.
   Model supported:
   - SINGLE (1 sisi)
   - BBS (bolak balik sama) => cetak & oplah x2, plat x1
   - BBL (bolak balik lain) => plat x2, cetak x2, oplah x2
   - MULTIPAGE (kalender)   => engine BBL + digital_total + biaya per pcs
   ============================================================ */
CREATE TABLE IF NOT EXISTS calc_offset_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,

  model_type ENUM('SINGLE','BBS','BBL','MULTIPAGE') NOT NULL DEFAULT 'SINGLE',

  title VARCHAR(160) NOT NULL,
  customer_name VARCHAR(160) NULL,

  material_id BIGINT UNSIGNED NULL,
  machine_id BIGINT UNSIGNED NULL,
  tariff_id BIGINT UNSIGNED NULL,
  lamination_id BIGINT UNSIGNED NULL,

  /* ===== INPUT SNAPSHOT (UMUM) ===== */
  qty_need INT UNSIGNED NOT NULL DEFAULT 0,                 -- jumlah_butuh (lembar jadi)
  plate_sets INT UNSIGNED NOT NULL DEFAULT 1,               -- "jumlah plat" yang user input (anggap sebagai jumlah SET)
  insheet_per_plate INT UNSIGNED NOT NULL DEFAULT 0,        -- insheet per plat (per set)
  up_in_plano INT UNSIGNED NOT NULL DEFAULT 1,              -- UP dalam plano

  finished_p_cm DECIMAL(10,2) NOT NULL DEFAULT 0.00,        -- panjang jadi
  finished_l_cm DECIMAL(10,2) NOT NULL DEFAULT 0.00,        -- lebar jadi

  -- MULTIPLIER khusus (biar fleksibel tapi UI tetap simpel)
  plate_per_set INT UNSIGNED NOT NULL DEFAULT 1,            -- default: 1; BBL bisa 2 (kalau kamu mau tampilkan hanya di BBL)
  print_multiplier INT UNSIGNED NOT NULL DEFAULT 1,          -- SINGLE=1, BBS=2, BBL=2, MULTIPAGE=2 (engine BBL)
  oplah_multiplier INT UNSIGNED NOT NULL DEFAULT 1,          -- SINGLE=1, BBS=2, BBL=2, MULTIPAGE=2 (engine BBL)

  lam_sides TINYINT UNSIGNED NOT NULL DEFAULT 0,            -- 0/1/2 sisi laminasi

  /* ===== BIAYA MANUAL (UMUM) ===== */
  cost_cut DECIMAL(15,2) NOT NULL DEFAULT 0.00,             -- potong
  cost_pond DECIMAL(15,2) NOT NULL DEFAULT 0.00,            -- pond
  cost_emboss DECIMAL(15,2) NOT NULL DEFAULT 0.00,          -- embos
  cost_hotfoil DECIMAL(15,2) NOT NULL DEFAULT 0.00,         -- hotfoil
  cost_spotuv DECIMAL(15,2) NOT NULL DEFAULT 0.00,          -- spot uv
  cost_other DECIMAL(15,2) NOT NULL DEFAULT 0.00,           -- lain-lain

  /* ===== MULTIPAGE ONLY ===== */
  qty_product INT UNSIGNED NOT NULL DEFAULT 0,              -- jumlah kalender (pcs), 0 jika bukan multipage
  digital_total DECIMAL(15,2) NOT NULL DEFAULT 0.00,        -- biaya digital total job (manual)
  cost_kaki_per_pcs DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  cost_spiral_per_pcs DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  cost_packing_per_pcs DECIMAL(15,2) NOT NULL DEFAULT 0.00,

  /* ===== HASIL PERHITUNGAN (DISIMPAN BIAR HISTORI TIDAK BERUBAH) ===== */
  total_insheet INT UNSIGNED NOT NULL DEFAULT 0,            -- insheet_total
  total_sheets INT UNSIGNED NOT NULL DEFAULT 0,             -- total_kertas_jadi (qty_need + total_insheet)
  total_plano INT UNSIGNED NOT NULL DEFAULT 0,              -- ceil(total_sheets / up)

  cost_paper DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  cost_plate DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  cost_print DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  cost_oplah DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  cost_lamination DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  cost_manual_total DECIMAL(15,2) NOT NULL DEFAULT 0.00,

  modal_offset_total DECIMAL(15,2) NOT NULL DEFAULT 0.00,   -- modal hitung offset (SINGLE/BBS/BBL engine)
  modal_production_total DECIMAL(15,2) NOT NULL DEFAULT 0.00,-- offset + digital (khusus multipage)

  modal_per_pcs DECIMAL(15,2) NOT NULL DEFAULT 0.00,        -- multipage: modal isi/pcs (setelah bagi qty_product)
  modal_final_per_pcs DECIMAL(15,2) NOT NULL DEFAULT 0.00,  -- multipage: + kaki/spiral/packing

  margin_pct DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  margin_value DECIMAL(15,2) NOT NULL DEFAULT 0.00,

  total_sell DECIMAL(15,2) NOT NULL DEFAULT 0.00,           -- total jual (global; SINGLE/BBS/BBL)
  sell_per_pcs DECIMAL(15,2) NOT NULL DEFAULT 0.00,         -- multipage: jual per pcs

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,

  PRIMARY KEY (id),

  KEY idx_tenant (tenant_id),
  KEY idx_tenant_loc (tenant_id, location_id),
  KEY idx_model (tenant_id, model_type),
  KEY idx_created_at (tenant_id, created_at),
  KEY idx_material (tenant_id, material_id),
  KEY idx_machine (tenant_id, machine_id),
  KEY idx_tariff (tenant_id, tariff_id),
  KEY idx_lamination (tenant_id, lamination_id),

  CONSTRAINT fk_calc_record_material
    FOREIGN KEY (material_id) REFERENCES calc_offset_materials(id)
    ON UPDATE CASCADE ON DELETE SET NULL,

  CONSTRAINT fk_calc_record_machine
    FOREIGN KEY (machine_id) REFERENCES calc_offset_machines(id)
    ON UPDATE CASCADE ON DELETE SET NULL,

  CONSTRAINT fk_calc_record_tariff
    FOREIGN KEY (tariff_id) REFERENCES calc_offset_tariffs(id)
    ON UPDATE CASCADE ON DELETE SET NULL,

  CONSTRAINT fk_calc_record_lamination
    FOREIGN KEY (lamination_id) REFERENCES calc_offset_laminations(id)
    ON UPDATE CASCADE ON DELETE SET NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


/* ============================================================
   (Opsional, tapi recommended)
   MASTER: setting default multiplier per model (kalau mau)
   - Kalau nggak perlu, skip.
   ============================================================ */
/*
CREATE TABLE IF NOT EXISTS calc_offset_model_defaults (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  model_type ENUM('SINGLE','BBS','BBL','MULTIPAGE') NOT NULL,

  plate_per_set INT UNSIGNED NOT NULL DEFAULT 1,
  print_multiplier INT UNSIGNED NOT NULL DEFAULT 1,
  oplah_multiplier INT UNSIGNED 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_tenant_model (tenant_id, model_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
*/