CREATE DATABASE IF NOT EXISTS shopdb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE shopdb;

-- ======================
-- USERS
-- ======================
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NULL,
  google_id VARCHAR(80) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ======================
-- PASSWORD RESETS
-- ======================
CREATE TABLE password_resets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ======================
-- CATEGORIES
-- ======================
CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(80) NOT NULL
);

-- ======================
-- PRODUCTS (UPDATED)
-- ======================
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  title VARCHAR(150) NOT NULL,

  -- ✅ NEW FIELDS
  short_description VARCHAR(255),
  description TEXT,
  stock INT DEFAULT 10,

  price DECIMAL(10,2) NOT NULL,
  rating DECIMAL(2,1) NOT NULL DEFAULT 4.5,
  image_url VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

-- ======================
-- INSERT CATEGORIES
-- ======================
INSERT INTO categories (slug, name) VALUES
('kitchen','Kitchen Accessories'),
('household','House Hold'),
('beauty','Beauty'),
('pets','Pets');

-- ======================
-- INSERT SAMPLE PRODUCTS (WITH DESCRIPTION)
-- ======================
INSERT INTO products
(category_id, title, short_description, description, price, rating, image_url, stock)
VALUES

(1,'Premium Knife Set',
 'High-quality stainless steel knife set',
 'This premium knife set is made from durable stainless steel and is ideal for chopping, slicing, and cutting. Perfect for everyday kitchen use.',
 29.99,4.6,'assets/img/kitchen1.jpg',10),

(1,'Mixing bowls',
 'High-quality stainless steel knife set',
 'This premium knife set is made from durable stainless steel and is ideal for chopping, slicing, and cutting. Perfect for everyday kitchen use.',
 29.99,4.6,'assets/img/kitchen1.jpg',11),

 (1,'spoons',
 'High-quality stainless steel knife set',
 'This premium knife set is made from durable stainless steel and is ideal for chopping, slicing, and cutting. Perfect for everyday kitchen use.',
 29.99,4.6,'assets/img/kitchen1.jpg',12),


(1,'Silicone Spatula Pack',
 'Heat resistant silicone spatulas',
 'A set of heat-resistant silicone spatulas suitable for cooking and baking. Easy to clean and long lasting.',
 9.99,4.4,'assets/img/kitchen2.jpg',15),

(1,'Air-tight Storage Jars',
 'Keep food fresh longer',
 'Air-tight storage jars designed to keep dry foods fresh. Ideal for kitchens and pantries.',
 14.50,4.7,'assets/img/kitchen3.jpg',12),

(2,'Multipurpose Cleaner',
 'Effective household cleaner',
 'Multipurpose cleaner suitable for floors, kitchens, and bathrooms. Removes tough stains easily.',
 7.50,4.2,'assets/img/house1.jpg',20),

 (2,'Pillow',
 'Effective household cleaner',
 'Multipurpose cleaner suitable for floors, kitchens, and bathrooms. Removes tough stains easily.',
 7.50,4.2,'assets/img/house1.jpg',20),

 (2,'Lamp',
 'Effective household cleaner',
 'Multipurpose cleaner suitable for floors, kitchens, and bathrooms. Removes tough stains easily.',
 7.50,4.2,'assets/img/house1.jpg',20),

 (2,'Bookshelf',
 'Effective household cleaner',
 'Multipurpose cleaner suitable for floors, kitchens, and bathrooms. Removes tough stains easily.',
 7.50,4.2,'assets/img/house1.jpg',20),

(2,'Microfiber Cloth Set',
 'Soft and reusable cleaning cloths',
 'Reusable microfiber cloth set ideal for cleaning surfaces without scratching.',
 6.99,4.5,'assets/img/house2.jpg',25),

(2,'Laundry Basket',
 'Lightweight and durable basket',
 'Durable laundry basket with ample space and easy-grip handles.',
 12.00,4.3,'assets/img/house3.jpg',8),

(3,'Vitamin C Serum',
 'Brightening skincare serum',
 'Vitamin C serum that helps brighten skin tone and reduce dark spots. Suitable for daily use.',
 18.99,4.6,'assets/img/beauty1.jpg',10),

 (3,'Toner',
 'Brightening skincare serum',
 'Vitamin C serum that helps brighten skin tone and reduce dark spots. Suitable for daily use.',
 18.99,4.6,'assets/img/beauty1.jpg',10),

 (3,'Face mask',
 'Brightening skincare serum',
 'Vitamin C serum that helps brighten skin tone and reduce dark spots. Suitable for daily use.',
 18.99,4.6,'assets/img/beauty1.jpg',10),

 (3,'Eye Cream',
 'Brightening skincare serum',
 'Vitamin C serum that helps brighten skin tone and reduce dark spots. Suitable for daily use.',
 18.99,4.6,'assets/img/beauty1.jpg',10),

(3,'Matte Lipstick',
 'Long-lasting matte finish',
 'Matte lipstick with rich color and long-lasting wear. Smooth application.',
 11.49,4.4,'assets/img/beauty2.jpg',18),

(3,'Face Wash',
 'Gentle daily cleanser',
 'Gentle face wash suitable for all skin types. Cleanses and refreshes skin.',
 8.99,4.3,'assets/img/beauty3.jpg',22),

(4,'Pet Food Bowl',
 'Durable bowl for pets',
 'Strong and easy-to-clean pet food bowl suitable for cats and dogs.',
 6.50,4.5,'assets/img/pet1.jpg',30),

 (4,'Dog Food',
 'Durable bowl for pets',
 'Strong and easy-to-clean pet food bowl suitable for cats and dogs.',
 4.50,4.3,'assets/img/pet1.jpg',16),

(4,'Poop Bag',
 'Durable bowl for pets',
 'Strong and easy-to-clean pet food bowl suitable for cats and dogs.',
 6.50,4.5,'assets/img/pet1.jpg',30),

(4,'Nail Cliper',
 'Durable bowl for pets',
 'Strong and easy-to-clean pet food bowl suitable for cats and dogs.',
 6.50,4.5,'assets/img/pet1.jpg',30),


(4,'Pet Grooming Brush',
 'Comfortable grooming brush',
 'Pet grooming brush designed to remove loose fur and keep coats healthy.',
 10.00,4.4,'assets/img/pet2.jpg',14),

(4,'Pet Toy Ball',
 'Fun and safe pet toy',
 'Durable toy ball for pets that encourages play and exercise.',
 4.99,4.2,'assets/img/pet3.jpg',40);
