CREATE TABLE `production_steps` (
	`id` int AUTO_INCREMENT NOT NULL,
	`productionOrderId` int NOT NULL,
	`sequence` int NOT NULL,
	`name` varchar(160) NOT NULL,
	`status` enum('pending','in_progress','completed','blocked') NOT NULL DEFAULT 'pending',
	`plannedMinutes` int NOT NULL DEFAULT 0,
	`actualMinutes` int NOT NULL DEFAULT 0,
	`notes` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `production_steps_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `stock_lots` (
	`id` int AUTO_INCREMENT NOT NULL,
	`stockItemId` int NOT NULL,
	`supplierId` int,
	`sourceProductionOrderId` int,
	`lotNumber` varchar(120) NOT NULL,
	`receivedAt` timestamp NOT NULL DEFAULT (now()),
	`expiryDate` timestamp,
	`quantity` int NOT NULL,
	`availableQuantity` int NOT NULL,
	`unitCostCents` int NOT NULL,
	`qualityStatus` enum('pending','released','blocked') NOT NULL DEFAULT 'pending',
	`createdByUserId` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `stock_lots_id` PRIMARY KEY(`id`),
	CONSTRAINT `stock_lots_item_lot_unique` UNIQUE(`stockItemId`,`lotNumber`)
);
--> statement-breakpoint
ALTER TABLE `stock_items` ADD `itemType` enum('raw_material','packaging','finished_product','semi_finished') DEFAULT 'finished_product' NOT NULL;--> statement-breakpoint
ALTER TABLE `stock_items` ADD `baseUnit` enum('kg','g','unit','carton','sac') DEFAULT 'unit' NOT NULL;--> statement-breakpoint
ALTER TABLE `stock_items` ADD `trackLots` boolean DEFAULT false NOT NULL;--> statement-breakpoint
CREATE INDEX `production_steps_order_index` ON `production_steps` (`productionOrderId`);--> statement-breakpoint
CREATE INDEX `stock_lots_expiry_index` ON `stock_lots` (`expiryDate`);