-- =========================================================
-- 108_digital_production_phase1.sql
-- Produksi Digital phase 1 foundation
-- =========================================================

CREATE TABLE IF NOT EXISTS digital_production_shifts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(80) NOT NULL,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  production_date DATE NOT NULL,
  shift_label VARCHAR(40) NOT NULL,
  machine_asset_id BIGINT UNSIGNED NOT NULL,
  operator_user_id BIGINT UNSIGNED DEFAULT NULL,
  operator_name_snapshot VARCHAR(190) DEFAULT NULL,
  counter_start DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  counter_end DECIMAL(14,2) DEFAULT NULL,
  total_click_production DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_click_waste DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_click_machine DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_click_gap DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  status VARCHAR(30) NOT NULL DEFAULT 'draft',
  note TEXT DEFAULT NULL,
  stock_posted_at DATETIME DEFAULT NULL,
  stock_posted_by_user_id BIGINT UNSIGNED DEFAULT NULL,
  closed_at DATETIME DEFAULT NULL,
  closed_by_user_id BIGINT UNSIGNED DEFAULT NULL,
  created_by_user_id BIGINT UNSIGNED DEFAULT 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_digital_production_shifts_code (code),
  KEY idx_digital_production_shifts_location_date (location_id, production_date),
  KEY idx_digital_production_shifts_machine (machine_asset_id),
  KEY idx_digital_production_shifts_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS digital_production_shift_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  shift_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  product_name_snapshot VARCHAR(190) NOT NULL,
  sku_snapshot VARCHAR(120) DEFAULT NULL,
  unit_snapshot VARCHAR(100) DEFAULT NULL,
  warehouse_group_snapshot VARCHAR(120) DEFAULT NULL,
  qty_sheets DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  side_mode TINYINT UNSIGNED NOT NULL DEFAULT 1,
  is_long_media TINYINT(1) NOT NULL DEFAULT 0,
  click_rule_key VARCHAR(60) NOT NULL,
  click_rule_name_snapshot VARCHAR(120) NOT NULL,
  click_per_side_snapshot DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_click_snapshot DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  note TEXT DEFAULT 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_digital_production_shift_items_shift (shift_id),
  KEY idx_digital_production_shift_items_product (product_id),
  CONSTRAINT fk_digital_production_shift_items_shift FOREIGN KEY (shift_id) REFERENCES digital_production_shifts(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS digital_production_shift_wastes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  shift_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  product_name_snapshot VARCHAR(190) NOT NULL,
  unit_snapshot VARCHAR(100) DEFAULT NULL,
  qty_sheets DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  side_mode TINYINT UNSIGNED NOT NULL DEFAULT 1,
  is_long_media TINYINT(1) NOT NULL DEFAULT 0,
  click_rule_key VARCHAR(60) NOT NULL,
  click_rule_name_snapshot VARCHAR(120) NOT NULL,
  click_per_side_snapshot DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_click_snapshot DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  note TEXT DEFAULT 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_digital_production_shift_wastes_shift (shift_id),
  KEY idx_digital_production_shift_wastes_product (product_id),
  CONSTRAINT fk_digital_production_shift_wastes_shift FOREIGN KEY (shift_id) REFERENCES digital_production_shifts(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS digital_production_click_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  rule_key VARCHAR(60) NOT NULL,
  rule_name VARCHAR(120) NOT NULL,
  click_per_side DECIMAL(14,2) NOT NULL DEFAULT 1.00,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  note TEXT DEFAULT 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_digital_production_click_rules_location_key (location_id, rule_key),
  KEY idx_digital_production_click_rules_location_active (location_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO permissions (code, name, module, created_at) VALUES
  ('digital_production.view', 'Lihat Produksi Digital', 'digital_production', NOW()),
  ('digital_production.create', 'Buat Shift Produksi Digital', 'digital_production', NOW()),
  ('digital_production.edit', 'Edit Shift Produksi Digital', 'digital_production', NOW()),
  ('digital_production.close_shift', 'Tutup Buku Shift Produksi Digital', 'digital_production', NOW()),
  ('digital_production.manage_click_rules', 'Kelola Aturan Klik Produksi Digital', 'digital_production', NOW());