-- Core Tables (no dependencies)
CREATE TABLE `users` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(100) NOT NULL,
	`email` varchar(100) NOT NULL,
	`phoneNumber` varchar(20) DEFAULT '' NOT NULL,
	`password` varchar(255) NOT NULL,
	`role` enum('Admin','Shopkeeper','Tailor','Master') DEFAULT 'Admin' NOT NULL,
	`modules` json DEFAULT ('[]'),
	`isDeleted` boolean DEFAULT false NOT NULL,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `users_id` PRIMARY KEY(`id`),
	CONSTRAINT `users_email_unique` UNIQUE(`email`)
);

CREATE TABLE `schools` (
	`id` int AUTO_INCREMENT NOT NULL,
	`logo` varchar(255) NOT NULL,
	`name` varchar(255) NOT NULL,
	`address` varchar(255) NOT NULL,
	`principalName` varchar(100),
	`principalContact` varchar(15),
	`receptionistName` varchar(100),
	`receptionistContact` varchar(15),
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `schools_id` PRIMARY KEY(`id`)
);

CREATE TABLE `customers` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(255) NOT NULL,
	`emailAddress` varchar(255) NOT NULL,
	`phoneNumber` varchar(15),
	`address` varchar(255),
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `customers_id` PRIMARY KEY(`id`),
	CONSTRAINT `customers_emailAddress_unique` UNIQUE(`emailAddress`)
);

CREATE TABLE `employees` (
	`id` int AUTO_INCREMENT NOT NULL,
	`employeeName` varchar(255) NOT NULL,
	`email` varchar(255) NOT NULL,
	`address` varchar(255),
	`phoneNumber` varchar(15),
	`skills` varchar(500),
	`paymentType` enum('Daily','Monthly') DEFAULT 'Daily',
	`price` decimal(10,2) DEFAULT '0.00',
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `employees_id` PRIMARY KEY(`id`),
	CONSTRAINT `employees_email_unique` UNIQUE(`email`)
);

CREATE TABLE `products` (
	`id` int AUTO_INCREMENT NOT NULL,
	`productName` varchar(255) NOT NULL,
	`productUnit` varchar(100) NOT NULL,
	`remainingStock` int NOT NULL DEFAULT 0,
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `products_id` PRIMARY KEY(`id`)
);

CREATE TABLE `shops` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(100) NOT NULL,
	`location` varchar(255) NOT NULL,
	`number` varchar(15) NOT NULL,
	`shopkeeperIds` json DEFAULT ('[]'),
	`shopkeeperNames` json DEFAULT ('[]'),
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `shops_id` PRIMARY KEY(`id`)
);

CREATE TABLE `size_definitions` (
	`id` int AUTO_INCREMENT NOT NULL,
	`sizeNumber` varchar(50) NOT NULL,
	`gender` varchar(50) NOT NULL,
	`upperWear` json NOT NULL,
	`lowerWear` json NOT NULL,
	`headCover` json,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `size_definitions_id` PRIMARY KEY(`id`)
);

CREATE TABLE `user_management` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(255) NOT NULL,
	`phoneNumber` varchar(15) NOT NULL,
	`role` enum('Admin','Shopkeeper','Tailor','Master') DEFAULT 'Admin',
	`hasShopAccess` boolean DEFAULT false,
	`hasUserAccess` boolean DEFAULT false,
	`hasOrderAccess` boolean DEFAULT false,
	`hasSizeDefinitionAccess` boolean DEFAULT false,
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `user_management_id` PRIMARY KEY(`id`)
);

CREATE TABLE `expenses` (
	`id` int AUTO_INCREMENT NOT NULL,
	`date` datetime NOT NULL,
	`expense` varchar(255) NOT NULL,
	`title` varchar(255),
	`price` varchar(255) NOT NULL,
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `expenses_id` PRIMARY KEY(`id`)
);

CREATE TABLE `attendance` (
	`id` int AUTO_INCREMENT NOT NULL,
	`employeeId` int NOT NULL,
	`employeeName` varchar(255) NOT NULL,
	`status` enum('Present','Absent','Leave') DEFAULT 'Present',
	`attendanceDate` datetime DEFAULT CURRENT_TIMESTAMP,
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `attendance_id` PRIMARY KEY(`id`)
);

CREATE TABLE `addons` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(255) NOT NULL,
	`price` varchar(255) NOT NULL,
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `addons_id` PRIMARY KEY(`id`)
);

-- Tables with dependencies (foreign keys)
CREATE TABLE `classes` (
	`id` int AUTO_INCREMENT NOT NULL,
	`schoolId` int NOT NULL,
	`name` varchar(255) NOT NULL,
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `classes_id` PRIMARY KEY(`id`),
	CONSTRAINT `classes_schoolId_fk` FOREIGN KEY (`schoolId`) REFERENCES `schools`(`id`)
);

CREATE TABLE `sessions` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(255) NOT NULL,
	`classId` int NOT NULL,
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `sessions_id` PRIMARY KEY(`id`),
	CONSTRAINT `sessions_classId_fk` FOREIGN KEY (`classId`) REFERENCES `classes`(`id`)
);

CREATE TABLE `students` (
	`id` int AUTO_INCREMENT NOT NULL,
	`sessionId` int NOT NULL,
	`name` varchar(255) NOT NULL,
	`gender` varchar(50) NOT NULL,
	`sizeNumber` varchar(50) NOT NULL,
	`upperWear` json NOT NULL,
	`lowerWear` json NOT NULL,
	`headcover` json,
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `students_id` PRIMARY KEY(`id`),
	CONSTRAINT `students_sessionId_fk` FOREIGN KEY (`sessionId`) REFERENCES `sessions`(`id`)
);

CREATE TABLE `orders` (
	`id` int AUTO_INCREMENT NOT NULL,
	`schoolId` int NOT NULL,
	`name` varchar(255) NOT NULL,
	`address` varchar(255) NOT NULL,
	`phoneNumber` varchar(15) NOT NULL,
	`totalPrice` varchar(255) NOT NULL,
	`type` enum('Bulk Order','Custom Order') NOT NULL,
	`totalQuantity` int NOT NULL,
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `orders_id` PRIMARY KEY(`id`),
	CONSTRAINT `orders_schoolId_fk` FOREIGN KEY (`schoolId`) REFERENCES `schools`(`id`)
);

CREATE TABLE `order_details` (
	`id` int AUTO_INCREMENT NOT NULL,
	`orderId` int NOT NULL,
	`studentName` varchar(255) NOT NULL,
	`sizeNumber` varchar(50) NOT NULL,
	`gender` enum('male','female') NOT NULL,
	`orderOf` enum('upperWear','lowerWear','both') NOT NULL,
	`upperWear` json,
	`lowerWear` json,
	`headCover` json,
	`addons` json,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `order_details_id` PRIMARY KEY(`id`),
	CONSTRAINT `order_details_orderId_fk` FOREIGN KEY (`orderId`) REFERENCES `orders`(`id`)
);

CREATE TABLE `purchases` (
	`id` int AUTO_INCREMENT NOT NULL,
	`productId` int NOT NULL,
	`productName` varchar(255) NOT NULL,
	`productUnit` varchar(100) NOT NULL,
	`quantity` int NOT NULL,
	`totalPrice` decimal(10,2) NOT NULL,
	`perUnitPrice` decimal(10,2) NOT NULL,
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `purchases_id` PRIMARY KEY(`id`),
	CONSTRAINT `purchases_productId_fk` FOREIGN KEY (`productId`) REFERENCES `products`(`id`)
);

CREATE TABLE `shop_products` (
	`id` int AUTO_INCREMENT NOT NULL,
	`shopId` int NOT NULL,
	`shopName` varchar(100) NOT NULL,
	`shopProductName` varchar(100) NOT NULL,
	`shopProductUnit` varchar(50) NOT NULL,
	`remainingStock` int NOT NULL DEFAULT 0,
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `shop_products_id` PRIMARY KEY(`id`),
	CONSTRAINT `shop_products_shopId_fk` FOREIGN KEY (`shopId`) REFERENCES `shops`(`id`)
);

CREATE TABLE `shop_purchases` (
	`id` int AUTO_INCREMENT NOT NULL,
	`shopId` int NOT NULL,
	`shopName` varchar(255) NOT NULL,
	`productId` int NOT NULL,
	`productName` varchar(255) NOT NULL,
	`productUnit` varchar(100) NOT NULL,
	`quantity` int NOT NULL,
	`totalPrice` decimal(10,2) NOT NULL,
	`perUnitPrice` decimal(10,2) NOT NULL,
	`isDeleted` boolean DEFAULT false,
	`createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
	`updatedAt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `shop_purchases_id` PRIMARY KEY(`id`),
	CONSTRAINT `shop_purchases_shopId_fk` FOREIGN KEY (`shopId`) REFERENCES `shops`(`id`),
	CONSTRAINT `shop_purchases_productId_fk` FOREIGN KEY (`productId`) REFERENCES `shop_products`(`id`)
);
