schema

-- Create database
CREATE DATABASE IF NOT EXISTS easyshop_db;
USE easyshop_db;

-- Users table for authentication (admin and vendors)
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('ADMIN', 'VENDOR') NOT NULL,
    enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Vendors table (shop information)
CREATE TABLE vendors (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    shop_name VARCHAR(100) NOT NULL,
    shop_address TEXT,
    phone VARCHAR(20),
    email VARCHAR(100),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Products table
CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    vendor_id BIGINT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    image_url VARCHAR(255),
    category VARCHAR(50),
    available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE
);

-- Bookings table (customer pre-bookings)
CREATE TABLE bookings (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    customer_name VARCHAR(100) NOT NULL,
    customer_phone VARCHAR(20) NOT NULL,
    quantity INT DEFAULT 1,
    status ENUM('PENDING', 'CONFIRMED', 'CANCELLED', 'COMPLETED') DEFAULT 'PENDING',
    booking_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    pickup_date DATE,
    notes TEXT,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Insert initial admin user (password: admin123 - plain text)
INSERT INTO users (username, password, role) VALUES 
('admin', 'admin123', 'ADMIN');

-- Insert test vendors and products (plain text passwords)

-- First, create vendor users (passwords are all 'password123' - plain text)
INSERT INTO users (username, password, role) VALUES 
('vendor1', 'password123', 'VENDOR'),
('vendor2', 'password123', 'VENDOR'),
('vendor3', 'password123', 'VENDOR');

-- Create vendor profiles
INSERT INTO vendors (user_id, shop_name, shop_address, phone, email, latitude, longitude, description) VALUES
(2, 'Fresh Grocery Mart', '123 Main Street, New York, NY 10001', '1234567890', 'fresh@example.com', 40.7128, -74.0060, 'Fresh fruits, vegetables, and groceries'),
(3, 'Tech Gadget Store', '456 Broadway, New York, NY 10002', '2345678901', 'tech@example.com', 40.7589, -73.9851, 'Latest electronics and gadgets'),
(4, 'Fashion Boutique', '789 5th Avenue, New York, NY 10003', '3456789012', 'fashion@example.com', 40.7614, -73.9776, 'Trendy clothing and accessories');

-- Add products for Fresh Grocery Mart
INSERT INTO products (vendor_id, name, description, price, image_url, category, available) VALUES
(1, 'Organic Apples', 'Fresh organic apples from local farms', 2.99, '/images/apples.jpg', 'Food & Beverages', TRUE),
(1, 'Bananas (bunch)', 'Fresh ripe bananas', 1.99, '/images/bananas.jpg', 'Food & Beverages', TRUE),
(1, 'Whole Wheat Bread', 'Freshly baked whole wheat bread', 3.49, '/images/bread.jpg', 'Food & Beverages', TRUE),
(1, 'Milk (1 gallon)', 'Fresh whole milk', 4.29, '/images/milk.jpg', 'Food & Beverages', TRUE),
(1, 'Eggs (dozen)', 'Farm fresh eggs', 3.99, '/images/eggs.jpg', 'Food & Beverages', TRUE);

-- Add products for Tech Gadget Store
INSERT INTO products (vendor_id, name, description, price, image_url, category, available) VALUES
(2, 'Wireless Headphones', 'Noise cancelling Bluetooth headphones', 89.99, '/images/headphones.jpg', 'Electronics', TRUE),
(2, 'Smart Watch', 'Fitness tracking smart watch', 149.99, '/images/smartwatch.jpg', 'Electronics', TRUE),
(2, 'Phone Charger', 'Fast charging USB-C cable', 19.99, '/images/charger.jpg', 'Electronics', TRUE),
(2, 'Portable Speaker', 'Waterproof Bluetooth speaker', 59.99, '/images/speaker.jpg', 'Electronics', TRUE),
(2, 'Power Bank', '10000mAh portable charger', 39.99, '/images/powerbank.jpg', 'Electronics', TRUE);

-- Add products for Fashion Boutique
INSERT INTO products (vendor_id, name, description, price, image_url, category, available) VALUES
(3, 'Summer Dress', 'Lightweight cotton summer dress', 45.99, '/images/dress.jpg', 'Clothing', TRUE),
(3, 'Men''s T-Shirt', '100% cotton crew neck t-shirt', 24.99, '/images/tshirt.jpg', 'Clothing', TRUE),
(3, 'Jeans', 'Classic blue denim jeans', 59.99, '/images/jeans.jpg', 'Clothing', TRUE),
(3, 'Running Shoes', 'Comfortable athletic running shoes', 79.99, '/images/shoes.jpg', 'Clothing', TRUE),
(3, 'Handbag', 'Leather crossbody handbag', 89.99, '/images/handbag.jpg', 'Clothing', TRUE);

-- Add some test bookings
INSERT INTO bookings (product_id, customer_name, customer_phone, quantity, status, booking_date, pickup_date, notes) VALUES
(1, 'John Doe', '1234567890', 2, 'PENDING', NOW(), DATE_ADD(NOW(), INTERVAL 3 DAY), 'Please pick ripe apples'),
(2, 'Jane Smith', '2345678901', 1, 'CONFIRMED', NOW(), DATE_ADD(NOW(), INTERVAL 2 DAY), ''),
(6, 'Bob Johnson', '3456789012', 1, 'PENDING', NOW(), DATE_ADD(NOW(), INTERVAL 4 DAY), 'Gift wrapping needed'),
(11, 'Alice Brown', '4567890123', 3, 'COMPLETED', DATE_SUB(NOW(), INTERVAL 2 DAY), DATE_SUB(NOW(), INTERVAL 1 DAY), 'Size medium please'),
(14, 'Charlie Wilson', '5678901234', 1, 'CANCELLED', DATE_SUB(NOW(), INTERVAL 3 DAY), DATE_SUB(NOW(), INTERVAL 2 DAY), 'Changed mind');