CREATE TABLE `credit_note_lines` (
	`id` int AUTO_INCREMENT NOT NULL,
	`creditNoteId` int NOT NULL,
	`invoiceLineId` int NOT NULL,
	`stockItemId` int,
	`description` varchar(240) NOT NULL,
	`quantity` int NOT NULL,
	`unitPriceCents` int NOT NULL,
	`taxRateBps` int NOT NULL,
	`lineTotalCents` int NOT NULL,
	CONSTRAINT `credit_note_lines_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `credit_notes` (
	`id` int AUTO_INCREMENT NOT NULL,
	`reference` varchar(100) NOT NULL,
	`invoiceId` int NOT NULL,
	`customerId` int NOT NULL,
	`issueDateUtcMs` bigint NOT NULL,
	`reason` varchar(500) NOT NULL,
	`subtotalCents` int NOT NULL,
	`taxCents` int NOT NULL,
	`totalCents` int NOT NULL,
	`deletedAt` timestamp NULL DEFAULT NULL,
	`createdByUserId` int NOT NULL,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `credit_notes_id` PRIMARY KEY(`id`),
	CONSTRAINT `credit_notes_reference_unique` UNIQUE(`reference`)
);
--> statement-breakpoint
CREATE TABLE `expenses` (
	`id` int AUTO_INCREMENT NOT NULL,
	`reference` varchar(100) NOT NULL,
	`expenseDateUtcMs` bigint NOT NULL,
	`supplierId` int,
	`category` varchar(100) NOT NULL,
	`description` varchar(500) NOT NULL,
	`amountCents` int NOT NULL,
	`method` enum('cash','card','bank','other') NOT NULL,
	`spentBy` varchar(160) NOT NULL,
	`notes` text,
	`deletedAt` timestamp NULL DEFAULT NULL,
	`createdByUserId` int NOT NULL,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `expenses_id` PRIMARY KEY(`id`),
	CONSTRAINT `expenses_reference_unique` UNIQUE(`reference`)
);
--> statement-breakpoint
CREATE TABLE `sales_return_lines` (
	`id` int AUTO_INCREMENT NOT NULL,
	`returnId` int NOT NULL,
	`saleItemId` int NOT NULL,
	`stockItemId` int NOT NULL,
	`stockLotId` int,
	`description` varchar(240) NOT NULL,
	`quantity` int NOT NULL,
	`unitPriceCents` int NOT NULL,
	`unitCostCents` int NOT NULL,
	CONSTRAINT `sales_return_lines_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `sales_returns` (
	`id` int AUTO_INCREMENT NOT NULL,
	`reference` varchar(100) NOT NULL,
	`saleId` int NOT NULL,
	`customerId` int NOT NULL,
	`returnDateUtcMs` bigint NOT NULL,
	`reason` varchar(500) NOT NULL,
	`restock` boolean NOT NULL DEFAULT true,
	`totalCents` int NOT NULL,
	`costCents` int NOT NULL,
	`deletedAt` timestamp NULL DEFAULT NULL,
	`createdByUserId` int NOT NULL,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `sales_returns_id` PRIMARY KEY(`id`),
	CONSTRAINT `sales_returns_reference_unique` UNIQUE(`reference`)
);
--> statement-breakpoint
ALTER TABLE `daily_productions` ADD `supplierId` int;--> statement-breakpoint
CREATE INDEX `credit_note_lines_parent_index` ON `credit_note_lines` (`creditNoteId`);--> statement-breakpoint
CREATE INDEX `credit_notes_invoice_index` ON `credit_notes` (`invoiceId`);--> statement-breakpoint
CREATE INDEX `expenses_date_index` ON `expenses` (`expenseDateUtcMs`);--> statement-breakpoint
CREATE INDEX `expenses_supplier_index` ON `expenses` (`supplierId`);--> statement-breakpoint
CREATE INDEX `sales_return_lines_parent_index` ON `sales_return_lines` (`returnId`);--> statement-breakpoint
CREATE INDEX `sales_returns_sale_index` ON `sales_returns` (`saleId`);