CREATE TABLE IF NOT EXISTS `challans` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `challan_no` varchar(50) NOT NULL,
  `challan_date` date NOT NULL,
  `customer_id` int DEFAULT NULL,
  `customer_name` varchar(255) DEFAULT NULL,
  `customer_phone` varchar(20) DEFAULT NULL,
  `customer_address` text,
  `customer_email` varchar(100) DEFAULT NULL,
  `customer_gstin` varchar(20) DEFAULT NULL,
  `customer_pan` varchar(20) DEFAULT NULL,
  `customer_tan` varchar(20) DEFAULT NULL,
  `customer_tin` varchar(20) DEFAULT NULL,
  `customer_state` varchar(100) DEFAULT NULL,
  `customer_state_code` varchar(10) DEFAULT NULL,
  `consignee_name` varchar(255) DEFAULT NULL,
  `consignee_phone` varchar(20) DEFAULT NULL,
  `consignee_address` text,
  `consignee_email` varchar(100) DEFAULT NULL,
  `consignee_gstin` varchar(20) DEFAULT NULL,
  `consignee_pan` varchar(20) DEFAULT NULL,
  `consignee_tan` varchar(20) DEFAULT NULL,
  `consignee_tin` varchar(20) DEFAULT NULL,
  `consignee_state` varchar(100) DEFAULT NULL,
  `consignee_state_code` varchar(10) DEFAULT NULL,
  `carrier_name` varchar(255) DEFAULT NULL,
  `vehicle_no` varchar(100) DEFAULT NULL,
  `place_of_supply` varchar(255) DEFAULT NULL,
  `order_no` varchar(50) DEFAULT NULL,
  `order_date` date DEFAULT NULL,
  `notes` text,
  `user_id` int NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `challan_no` (`challan_no`, `company_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `challan_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `challan_id` int NOT NULL,
  `product_id` int DEFAULT NULL,
  `product_name` varchar(255) NOT NULL,
  `description` text,
  `hsn_code` varchar(20) DEFAULT NULL,
  `quantity` int NOT NULL,
  `unit` varchar(50) DEFAULT 'PCS',
  `pieces_per_unit` int DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `challan_id` (`challan_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;