-- Fix missing ON DELETE CASCADE for better multi-tenant isolation and cleanup
SET FOREIGN_KEY_CHECKS=0;

-- Sales
ALTER TABLE `sales` DROP FOREIGN KEY `sales_ibfk_2`;
ALTER TABLE `sales` ADD CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `locations`(`id`) ON DELETE CASCADE;

-- Sale Items
ALTER TABLE `sale_items` DROP FOREIGN KEY `sale_items_ibfk_3`;
ALTER TABLE `sale_items` ADD CONSTRAINT `sale_items_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE;

-- Purchases
ALTER TABLE `purchases` DROP FOREIGN KEY `purchases_ibfk_3`;
ALTER TABLE `purchases` ADD CONSTRAINT `purchases_ibfk_3` FOREIGN KEY (`location_id`) REFERENCES `locations`(`id`) ON DELETE CASCADE;

-- Purchase Items
ALTER TABLE `purchase_items` DROP FOREIGN KEY `purchase_items_ibfk_3`;
ALTER TABLE `purchase_items` ADD CONSTRAINT `purchase_items_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE;

-- Stock Transfers
ALTER TABLE `stock_transfers` DROP FOREIGN KEY `stock_transfers_ibfk_2`;
ALTER TABLE `stock_transfers` ADD CONSTRAINT `stock_transfers_ibfk_2` FOREIGN KEY (`from_location_id`) REFERENCES `locations`(`id`) ON DELETE CASCADE;

ALTER TABLE `stock_transfers` DROP FOREIGN KEY `stock_transfers_ibfk_3`;
ALTER TABLE `stock_transfers` ADD CONSTRAINT `stock_transfers_ibfk_3` FOREIGN KEY (`to_location_id`) REFERENCES `locations`(`id`) ON DELETE CASCADE;

-- Stock Transfer Items
ALTER TABLE `stock_transfer_items` DROP FOREIGN KEY `stock_transfer_items_ibfk_3`;
ALTER TABLE `stock_transfer_items` ADD CONSTRAINT `stock_transfer_items_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE;

SET FOREIGN_KEY_CHECKS=1;
