-- Recreate main `data` table from scratch (expected_weight and comment included).
-- WARNING: This DROPS the existing table. Backup first: mysqldump -u root -p bt data > data_backup.sql
-- Run: mysql -u root -p bt < migrations/recreate_data_table_fresh.sql

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `data`;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE `data` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `product` varchar(200) NOT NULL,
  `items` varchar(200) NOT NULL,
  `batch_no` varchar(200) NOT NULL,
  `expected_weight` varchar(200) NOT NULL DEFAULT '',
  `actual_weight` varchar(200) NOT NULL,
  `scan_time` varchar(200) NOT NULL,
  `operator` varchar(200) NOT NULL,
  `analyst` varchar(200) NOT NULL,
  `scan_date` varchar(200) DEFAULT NULL,
  `week` varchar(20) DEFAULT '',
  `units` varchar(20) DEFAULT 'kg',
  `serial_no` varchar(100) DEFAULT '',
  `status` varchar(10) DEFAULT '1',
  `mdn` varchar(200) DEFAULT '',
  `date_manufacturer` varchar(200) DEFAULT NULL,
  `expiry_date` varchar(200) DEFAULT NULL,
  `teamLead` varchar(100) DEFAULT NULL,
  `shift` varchar(100) DEFAULT '',
  `analyst_status` varchar(10) DEFAULT '0',
  `factory` varchar(20) DEFAULT NULL,
  `tl_status` varchar(10) DEFAULT '0',
  `comment` text,
  PRIMARY KEY (`id`),
  KEY `idx_serial_no` (`serial_no`),
  KEY `idx_scan_date` (`scan_date`),
  KEY `idx_product` (`product`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
