මොඩියුලය 2

-- Users Table CREATE TABLE `users` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `email` VARCHAR(255) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL, `role` VARCHAR(50) NOT NULL DEFAULT 'cashier', -- 'admin' or 'cashier' `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Categories Table CREATE TABLE `categories` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Products Table CREATE TABLE `products` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `category_id` INT(11) NOT NULL, `price` DECIMAL(10, 2) NOT NULL, `quantity` INT(11) NOT NULL, `image` VARCHAR(255) NULL, `supplier` VARCHAR(255) NULL, PRIMARY KEY (`id`), FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Sales Table CREATE TABLE `sales` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `user_id` INT(11) NOT NULL, `total` DECIMAL(10, 2) NOT NULL, `sale_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Sale Items Table CREATE TABLE `sale_items` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `sale_id` INT(11) NOT NULL, `product_id` INT(11) NOT NULL, `qty` INT(11) NOT NULL, `subtotal` DECIMAL(10, 2) NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`sale_id`) REFERENCES `sales`(`id`) ON DELETE CASCADE, FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert a sample category INSERT INTO `categories` (`name`) VALUES ('Beverages'); -- Insert a sample product INSERT INTO `products` (`name`, `category_id`, `price`, `quantity`, `supplier`) VALUES ('Milk Packet', 1, 150.00, 50, 'Highland');