

CREATE TABLE IF NOT EXISTS bi_perdas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  dt_producao DATE NOT NULL,
  op_trimb VARCHAR(40) NOT NULL,
  cliente VARCHAR(255) NOT NULL,
  pedido INT NULL,

  valor_unitario DECIMAL(12,4) NULL,
  valor_total_previsto DECIMAL(14,2) NULL,
  realizado INT NULL,
  valor_total_realizado DECIMAL(14,2) NULL,

  material VARCHAR(255) NULL,
  chapa_reservada_faturada INT NULL,
  contador_alimentacao INT NULL,
  conferencia INT NULL,

  perda_setup INT NOT NULL DEFAULT 0,
  abaulada INT NOT NULL DEFAULT 0,
  impressao_nao_conforme INT NOT NULL DEFAULT 0,
  materia_prima INT NOT NULL DEFAULT 0,
  acabou_cola INT NOT NULL DEFAULT 0,
  acabou_tinta INT NOT NULL DEFAULT 0,
  delaminacao INT NOT NULL DEFAULT 0,
  fora_de_esquadro INT NOT NULL DEFAULT 0,
  rasgada INT NOT NULL DEFAULT 0,
  enroscou INT NOT NULL DEFAULT 0,
  outros INT NOT NULL DEFAULT 0,

  perda_total INT NOT NULL DEFAULT 0,
  perc_perda DECIMAL(7,2) NOT NULL DEFAULT 0.00,
  diferenca_alimentacao INT NOT NULL DEFAULT 0,
  justificativa_outros TEXT NULL,

  created_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  KEY idx_dt (dt_producao),
  KEY idx_cliente (cliente(60)),
  KEY idx_op (op_trimb),
  CONSTRAINT fk_bi_perdas_user FOREIGN KEY (created_by) REFERENCES bi_users(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
