CREATE TABLE `customers` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(160) NOT NULL,
	`company` varchar(160),
	`email` varchar(320),
	`phone` varchar(64),
	`address` text,
	`notes` text,
	`createdByUserId` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `customers_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `inventory_adjustments` (
	`id` int AUTO_INCREMENT NOT NULL,
	`stockItemId` int NOT NULL,
	`quantityChange` int NOT NULL,
	`reason` varchar(120) NOT NULL,
	`notes` text,
	`createdByUserId` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `inventory_adjustments_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `payments` (
	`id` int AUTO_INCREMENT NOT NULL,
	`saleId` int NOT NULL,
	`amountCents` int NOT NULL,
	`method` enum('cash','card','bank','other') NOT NULL DEFAULT 'bank',
	`status` enum('received','pending','void') NOT NULL DEFAULT 'received',
	`notes` text,
	`receivedAt` timestamp NOT NULL DEFAULT (now()),
	`createdByUserId` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `payments_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `sale_items` (
	`id` int AUTO_INCREMENT NOT NULL,
	`saleId` int NOT NULL,
	`stockItemId` int NOT NULL,
	`nameSnapshot` varchar(160) NOT NULL,
	`quantity` int NOT NULL,
	`unitPriceCents` int NOT NULL,
	`unitCostCents` int NOT NULL,
	`lineGrossCents` int NOT NULL,
	`lineCostCents` int NOT NULL,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `sale_items_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `sales` (
	`id` int AUTO_INCREMENT NOT NULL,
	`reference` varchar(80) NOT NULL,
	`customerId` int NOT NULL,
	`status` enum('draft','confirmed','fulfilled','cancelled') NOT NULL DEFAULT 'draft',
	`saleDate` timestamp NOT NULL DEFAULT (now()),
	`notes` text,
	`grossTotalCents` int NOT NULL DEFAULT 0,
	`costTotalCents` int NOT NULL DEFAULT 0,
	`benefitCents` int NOT NULL DEFAULT 0,
	`balanceCents` int NOT NULL DEFAULT 0,
	`createdByUserId` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `sales_id` PRIMARY KEY(`id`),
	CONSTRAINT `sales_reference_unique` UNIQUE(`reference`)
);
--> statement-breakpoint
CREATE TABLE `stock_items` (
	`id` int AUTO_INCREMENT NOT NULL,
	`sku` varchar(80) NOT NULL,
	`name` varchar(160) NOT NULL,
	`description` text,
	`quantity` int NOT NULL DEFAULT 0,
	`unitCostCents` int NOT NULL DEFAULT 0,
	`lowStockThreshold` int NOT NULL DEFAULT 5,
	`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 `stock_items_id` PRIMARY KEY(`id`),
	CONSTRAINT `stock_items_sku_unique` UNIQUE(`sku`)
);
--> statement-breakpoint
CREATE INDEX `customers_name_index` ON `customers` (`name`);--> statement-breakpoint
CREATE INDEX `inventory_adjustments_stock_item_index` ON `inventory_adjustments` (`stockItemId`);--> statement-breakpoint
CREATE INDEX `inventory_adjustments_created_at_index` ON `inventory_adjustments` (`createdAt`);--> statement-breakpoint
CREATE INDEX `payments_sale_index` ON `payments` (`saleId`);--> statement-breakpoint
CREATE INDEX `payments_received_at_index` ON `payments` (`receivedAt`);--> statement-breakpoint
CREATE INDEX `sale_items_sale_index` ON `sale_items` (`saleId`);--> statement-breakpoint
CREATE INDEX `sales_customer_index` ON `sales` (`customerId`);--> statement-breakpoint
CREATE INDEX `sales_sale_date_index` ON `sales` (`saleDate`);--> statement-breakpoint
CREATE INDEX `stock_items_name_index` ON `stock_items` (`name`);