-- =============================================
-- Migration: Multi-Lot Support for Sale Module
-- =============================================

-- 1. Create sale_items table
CREATE TABLE IF NOT EXISTS `sale_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `lot_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `weight` decimal(12,2) NOT NULL,
  `rate` decimal(12,2) NOT NULL,
  `rate_basis` int(11) DEFAULT 40,
  `gross_amount` decimal(15,2) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`sale_id`) REFERENCES `sale_master` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`lot_id`) REFERENCES `purchase_lots` (`id`),
  FOREIGN KEY (`item_id`) REFERENCES `items` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Migrate existing data from sale_master to sale_items
INSERT INTO `sale_items` (`sale_id`, `lot_id`, `item_id`, `weight`, `rate`, `rate_basis`, `gross_amount`)
SELECT `id`, `lot_id`, `item_id`, `sale_weight`, `rate`, `rate_basis`, `gross_amount` 
FROM `sale_master`;

-- Note: We are keeping the old columns in sale_master for fallback/rollback purposes temporarily.
-- They can be dropped in a future cleanup:
-- ALTER TABLE `sale_master`
--  DROP FOREIGN KEY `sale_master_ibfk_3`, -- lot_id
--  DROP FOREIGN KEY `sale_master_ibfk_4`; -- item_id
-- ALTER TABLE `sale_master`
--  DROP COLUMN `lot_id`,
--  DROP COLUMN `item_id`,
--  DROP COLUMN `sale_weight`,
--  DROP COLUMN `rate_basis`,
--  DROP COLUMN `rate`;
