CREATE TABLE `machines` (
	`id` int AUTO_INCREMENT NOT NULL,
	`productionLineId` int,
	`code` varchar(40) NOT NULL,
	`name` varchar(160) NOT NULL,
	`status` enum('available','maintenance','down') NOT NULL DEFAULT 'available',
	`lastMaintenanceAt` timestamp,
	`notes` text,
	`createdByUserId` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `machines_id` PRIMARY KEY(`id`),
	CONSTRAINT `machines_code_unique` UNIQUE(`code`)
);
--> statement-breakpoint
CREATE TABLE `production_lines` (
	`id` int AUTO_INCREMENT NOT NULL,
	`code` varchar(40) NOT NULL,
	`name` varchar(160) NOT NULL,
	`capacityPerHour` int NOT NULL DEFAULT 0,
	`capacityUnit` enum('kg','g','unit','carton','sac') NOT NULL DEFAULT 'kg',
	`isActive` boolean NOT NULL DEFAULT true,
	`createdByUserId` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `production_lines_id` PRIMARY KEY(`id`),
	CONSTRAINT `production_lines_code_unique` UNIQUE(`code`)
);
--> statement-breakpoint
CREATE TABLE `production_losses` (
	`id` int AUTO_INCREMENT NOT NULL,
	`productionOrderId` int NOT NULL,
	`stockItemId` int,
	`quantity` int NOT NULL,
	`reason` varchar(160) NOT NULL,
	`notes` text,
	`createdByUserId` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `production_losses_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `production_material_consumptions` (
	`id` int AUTO_INCREMENT NOT NULL,
	`productionOrderId` int NOT NULL,
	`stockItemId` int NOT NULL,
	`quantity` int NOT NULL,
	`unitCostCents` int NOT NULL,
	`notes` text,
	`createdByUserId` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `production_material_consumptions_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `production_orders` (
	`id` int AUTO_INCREMENT NOT NULL,
	`reference` varchar(80) NOT NULL,
	`finishedStockItemId` int NOT NULL,
	`productionLineId` int,
	`machineId` int,
	`plannedQuantity` int NOT NULL,
	`producedQuantity` int NOT NULL DEFAULT 0,
	`lostQuantity` int NOT NULL DEFAULT 0,
	`status` enum('draft','released','in_progress','paused','completed','cancelled') NOT NULL DEFAULT 'draft',
	`plannedStartAt` timestamp,
	`startedAt` timestamp,
	`completedAt` timestamp,
	`notes` text,
	`createdByUserId` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `production_orders_id` PRIMARY KEY(`id`),
	CONSTRAINT `production_orders_reference_unique` UNIQUE(`reference`)
);
--> statement-breakpoint
CREATE TABLE `production_outputs` (
	`id` int AUTO_INCREMENT NOT NULL,
	`productionOrderId` int NOT NULL,
	`stockItemId` int NOT NULL,
	`lotNumber` varchar(120),
	`quantity` 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 `production_outputs_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `supplier_payments` (
	`id` int AUTO_INCREMENT NOT NULL,
	`supplierId` int NOT NULL,
	`receiptId` int,
	`amountCents` int NOT NULL,
	`method` enum('cash','card','bank','other') NOT NULL DEFAULT 'bank',
	`status` enum('received','pending','void') NOT NULL DEFAULT 'received',
	`reference` varchar(120),
	`notes` text,
	`paidAt` timestamp NOT NULL DEFAULT (now()),
	`createdByUserId` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `supplier_payments_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `supplier_receipt_lines` (
	`id` int AUTO_INCREMENT NOT NULL,
	`receiptId` int NOT NULL,
	`stockItemId` int NOT NULL,
	`lotNumber` varchar(120),
	`quantity` int NOT NULL,
	`unitCostCents` int NOT NULL,
	`expiryDate` timestamp,
	`qualityStatus` enum('pending','released','blocked') NOT NULL DEFAULT 'pending',
	`lineTotalCents` int NOT NULL,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `supplier_receipt_lines_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `supplier_receipts` (
	`id` int AUTO_INCREMENT NOT NULL,
	`reference` varchar(80) NOT NULL,
	`supplierId` int NOT NULL,
	`deliveryReference` varchar(120),
	`invoiceReference` varchar(120),
	`receivedAt` timestamp NOT NULL DEFAULT (now()),
	`totalCents` int NOT NULL DEFAULT 0,
	`notes` text,
	`createdByUserId` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `supplier_receipts_id` PRIMARY KEY(`id`),
	CONSTRAINT `supplier_receipts_reference_unique` UNIQUE(`reference`)
);
--> statement-breakpoint
CREATE TABLE `suppliers` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(160) NOT NULL,
	`contactName` varchar(160),
	`phone` varchar(64),
	`email` varchar(320),
	`address` text,
	`paymentTerms` varchar(120),
	`notes` text,
	`isActive` boolean NOT NULL DEFAULT true,
	`createdByUserId` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `suppliers_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE INDEX `machines_line_index` ON `machines` (`productionLineId`);--> statement-breakpoint
CREATE INDEX `production_losses_order_index` ON `production_losses` (`productionOrderId`);--> statement-breakpoint
CREATE INDEX `production_consumptions_order_index` ON `production_material_consumptions` (`productionOrderId`);--> statement-breakpoint
CREATE INDEX `production_orders_status_index` ON `production_orders` (`status`);--> statement-breakpoint
CREATE INDEX `production_outputs_order_index` ON `production_outputs` (`productionOrderId`);--> statement-breakpoint
CREATE INDEX `supplier_payments_supplier_index` ON `supplier_payments` (`supplierId`);--> statement-breakpoint
CREATE INDEX `supplier_payments_receipt_index` ON `supplier_payments` (`receiptId`);--> statement-breakpoint
CREATE INDEX `supplier_receipt_lines_receipt_index` ON `supplier_receipt_lines` (`receiptId`);--> statement-breakpoint
CREATE INDEX `supplier_receipts_supplier_index` ON `supplier_receipts` (`supplierId`);--> statement-breakpoint
CREATE INDEX `suppliers_name_index` ON `suppliers` (`name`);