CREATE TABLE `invoice_lines` (
	`id` int AUTO_INCREMENT NOT NULL,
	`invoiceId` int NOT NULL,
	`description` varchar(240) NOT NULL,
	`stockItemId` int,
	`quantity` int NOT NULL DEFAULT 1,
	`unitPriceCents` int NOT NULL DEFAULT 0,
	`taxRateBps` int NOT NULL DEFAULT 0,
	`lineSubtotalCents` int NOT NULL DEFAULT 0,
	`lineTaxCents` int NOT NULL DEFAULT 0,
	`lineTotalCents` int NOT NULL DEFAULT 0,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `invoice_lines_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `invoices` (
	`id` int AUTO_INCREMENT NOT NULL,
	`reference` varchar(100) NOT NULL,
	`invoiceType` enum('sale','purchase') NOT NULL,
	`customerId` int,
	`supplierId` int,
	`issueDate` timestamp NOT NULL DEFAULT (now()),
	`dueDate` timestamp,
	`status` enum('draft','issued','partially_paid','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
	`source` enum('manual','scan') NOT NULL DEFAULT 'manual',
	`subtotalCents` int NOT NULL DEFAULT 0,
	`taxCents` int NOT NULL DEFAULT 0,
	`totalCents` int NOT NULL DEFAULT 0,
	`paidCents` int NOT NULL DEFAULT 0,
	`balanceCents` int NOT NULL DEFAULT 0,
	`currency` varchar(3) NOT NULL DEFAULT 'MAD',
	`documentUrl` text,
	`documentKey` varchar(500),
	`originalFileName` varchar(255),
	`mimeType` varchar(120),
	`ocrStatus` enum('not_applicable','pending','processed','review_required','failed') NOT NULL DEFAULT 'not_applicable',
	`ocrData` text,
	`notes` text,
	`createdByUserId` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `invoices_id` PRIMARY KEY(`id`),
	CONSTRAINT `invoices_reference_unique` UNIQUE(`reference`)
);
--> statement-breakpoint
CREATE INDEX `invoice_lines_invoice_index` ON `invoice_lines` (`invoiceId`);--> statement-breakpoint
CREATE INDEX `invoices_type_status_index` ON `invoices` (`invoiceType`,`status`);--> statement-breakpoint
CREATE INDEX `invoices_customer_index` ON `invoices` (`customerId`);--> statement-breakpoint
CREATE INDEX `invoices_supplier_index` ON `invoices` (`supplierId`);