CREATE TABLE IF NOT EXISTS machine_assets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  production_department_id BIGINT UNSIGNED NOT NULL,
  kode_mesin VARCHAR(80) NOT NULL,
  nama_mesin VARCHAR(190) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'aktif',
  catatan TEXT 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_machine_assets_location_code (location_id, kode_mesin),
  KEY idx_machine_assets_location_status (location_id, status),
  KEY idx_machine_assets_department (production_department_id),
  CONSTRAINT fk_machine_assets_location FOREIGN KEY (location_id) REFERENCES locations(id),
  CONSTRAINT fk_machine_assets_department FOREIGN KEY (production_department_id) REFERENCES production_departments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS machine_maintenance_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  machine_id BIGINT UNSIGNED NOT NULL,
  tanggal DATE NOT NULL,
  nominal DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  catatan TEXT NULL,
  vendor VARCHAR(190) NULL,
  jenis_maintenance VARCHAR(120) NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_by_name_snapshot VARCHAR(190) 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_machine_maintenance_logs_machine_date (machine_id, tanggal),
  KEY idx_machine_maintenance_logs_location_date (location_id, tanggal),
  CONSTRAINT fk_machine_maintenance_logs_machine FOREIGN KEY (machine_id) REFERENCES machine_assets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;