-- Add dispatched field to sales table
-- This field tracks whether the items have been dispatched/removed from the system

ALTER TABLE `sales`
ADD COLUMN `dispatched` BOOLEAN NOT NULL DEFAULT FALSE AFTER `status`,
ADD COLUMN `dispatched_at` DATETIME NULL AFTER `dispatched`,
ADD COLUMN `dispatched_by` BIGINT NULL AFTER `dispatched_at`,
ADD INDEX `idx_sales_dispatched` (`dispatched`),
ADD CONSTRAINT `fk_sales_dispatched_by` FOREIGN KEY (`dispatched_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE;

-- Add comment
ALTER TABLE `sales` MODIFY COLUMN `dispatched` BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'Whether items have been dispatched from inventory';

