47 lines
1.9 KiB
SQL
47 lines
1.9 KiB
SQL
CREATE TABLE `customers` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`mobile` text NOT NULL,
|
|
`name` text,
|
|
`added_on` text NOT NULL,
|
|
`enterprise_id` integer NOT NULL,
|
|
FOREIGN KEY (`enterprise_id`) REFERENCES `enterprises`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE UNIQUE INDEX `customers_mobile_unique` ON `customers` (`mobile`);--> statement-breakpoint
|
|
CREATE TABLE `bills` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`bill_no` text NOT NULL,
|
|
`customer_mobile` text NOT NULL,
|
|
`customer_name` text,
|
|
`subtotal` real NOT NULL,
|
|
`tax` real NOT NULL,
|
|
`tax_rate` real DEFAULT 18 NOT NULL,
|
|
`total` real NOT NULL,
|
|
`discount` real DEFAULT 0 NOT NULL,
|
|
`discount_percent` integer DEFAULT 0 NOT NULL,
|
|
`generated_by` integer NOT NULL,
|
|
`created_at` text NOT NULL,
|
|
`enterprise_id` integer NOT NULL,
|
|
FOREIGN KEY (`generated_by`) REFERENCES `staff`(`id`) ON UPDATE no action ON DELETE no action,
|
|
FOREIGN KEY (`enterprise_id`) REFERENCES `enterprises`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE `bill_items` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`bill_id` integer NOT NULL,
|
|
`product_id` integer NOT NULL,
|
|
`batch_id` integer,
|
|
`product_name` text NOT NULL,
|
|
`brand` text,
|
|
`strips` integer DEFAULT 0 NOT NULL,
|
|
`loose` integer DEFAULT 0 NOT NULL,
|
|
`qty` integer NOT NULL,
|
|
`original_price` real NOT NULL,
|
|
`selling_price` real NOT NULL,
|
|
`total` real NOT NULL,
|
|
`enterprise_id` integer NOT NULL,
|
|
FOREIGN KEY (`bill_id`) REFERENCES `bills`(`id`) ON UPDATE no action ON DELETE no action,
|
|
FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON UPDATE no action ON DELETE no action,
|
|
FOREIGN KEY (`batch_id`) REFERENCES `stock_batches`(`id`) ON UPDATE no action ON DELETE no action,
|
|
FOREIGN KEY (`enterprise_id`) REFERENCES `enterprises`(`id`) ON UPDATE no action ON DELETE no action
|
|
);
|