-- Database Schema for Raoof Grocery Shop

-- Create Database if deploying fresh (optional for cPanel, usually db is created via cPanel interface)
-- CREATE DATABASE IF NOT EXISTS raoof_grocery;
-- USE raoof_grocery;

-- --------------------------------------------------------
-- Table structure for table `users`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('customer','admin') NOT NULL DEFAULT 'customer',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert a default admin user (password: Admin@123)
-- You should change this password in production
INSERT INTO `users` (`name`, `email`, `password`, `role`) VALUES
('Admin', 'admin@raoofgrocery.local', '$2y$10$w6sKozK6K/16uU8XnKkZ/.B1L4.xS1y0o7eIqgG2J5h.0t7lD2tYy', 'admin');

-- --------------------------------------------------------
-- Table structure for table `products`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `description` text,
  `price` decimal(10,2) NOT NULL,
  `image` varchar(255) DEFAULT 'default.png',
  `category` varchar(100) DEFAULT 'General',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert some sample products
INSERT INTO `products` (`name`, `description`, `price`, `image`, `category`) VALUES
('Fresh Organic Apples', 'High quality, hand-picked organic apples. Perfect for a healthy snack.', 4.99, 'default.png', 'Fruits'),
('Whole Wheat Bread', 'Freshly baked whole wheat bread. Soft and nutritious.', 2.49, 'default.png', 'Bakery'),
('Almond Milk 1L', 'Unsweetened original almond milk, dairy-free alternative.', 3.99, 'default.png', 'Dairy Alternatives'),
('Farm Fresh Eggs (12-pack)', 'Grade A large brown eggs from free range chickens.', 5.50, 'default.png', 'Dairy'),
('Organic Spinach', 'Fresh crisp organic spinach leaves.', 2.99, 'default.png', 'Vegetables');

-- --------------------------------------------------------
-- Table structure for table `orders`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `total` decimal(10,2) NOT NULL,
  `status` enum('pending','completed','cancelled') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `fk_order_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- Table structure for table `order_items`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `order_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `price_at_purchase` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `fk_item_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_item_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;