CREATE DATABASE IF NOT EXISTS mabanga_express;
USE mabanga_express;

-- Users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    role ENUM('customer', 'shop_owner', 'rider', 'admin') DEFAULT 'customer',
    phone VARCHAR(20),
    avatar VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Shops table
CREATE TABLE shops (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    location TEXT NOT NULL,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    image VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Products table
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shop_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    image VARCHAR(255),
    category VARCHAR(100),
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
);

-- Deliveries table
CREATE TABLE deliveries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    rider_id INT,
    tracking_id VARCHAR(20) UNIQUE,
    pickup_location TEXT NOT NULL,
    delivery_location TEXT NOT NULL,
    pickup_lat DECIMAL(10, 8),
    pickup_lng DECIMAL(11, 8),
    delivery_lat DECIMAL(10, 8),
    delivery_lng DECIMAL(11, 8),
    package_description TEXT,
    package_weight DECIMAL(8, 2),
    package_value DECIMAL(10, 2),
    status ENUM('pending', 'accepted', 'picked_up', 'in_transit', 'delivered', 'cancelled') DEFAULT 'pending',
    price DECIMAL(8, 2),
    distance DECIMAL(8, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (rider_id) REFERENCES users(id)
);

-- Orders table (for shop purchases)
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    shop_id INT NOT NULL,
    delivery_id INT,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'confirmed', 'preparing', 'ready', 'picked_up', 'delivered', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (shop_id) REFERENCES shops(id),
    FOREIGN KEY (delivery_id) REFERENCES deliveries(id)
);

-- Order items table
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Slides table for promotions
CREATE TABLE slides (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    image_path VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Notifications table
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    message TEXT NOT NULL,
    link VARCHAR(255),
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

--
-- DEMO DATA INSERTION
--
-- The password for all demo users is 'password'
--

--
-- Demo data for table `users`
--
INSERT INTO `users` (`id`, `email`, `password`, `name`, `phone`, `role`) VALUES
(1, 'admin@mabanga.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin User', '111-222-3333', 'admin'),
(2, 'customer@mabanga.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Customer User', '444-555-6666', 'customer'),
(3, 'shop@mabanga.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Shop Owner', '777-888-9999', 'shop_owner'),
(4, 'rider@mabanga.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Rider User', '123-456-7890', 'rider')
ON DUPLICATE KEY UPDATE `id`=`id`;

--
-- Demo data for table `shops`
--
INSERT INTO `shops` (`id`, `owner_id`, `name`, `description`, `location`, `image`) VALUES
(1, 3, 'Gadget Galaxy', 'Your one-stop shop for the latest electronics and gadgets. We have everything from smartphones to smart home devices.', '123 Tech Road, Silicon Valley', 'https://images.unsplash.com/photo-1582220107107-590dc8b0920b?auto=format&fit=crop&w=800&q=60'),
(2, 3, 'Fresh Finds Grocery', 'Organic and locally sourced groceries delivered to your door. Freshness guaranteed!', '456 Farm Lane, Greenfield', 'https://images.unsplash.com/photo-1542838132-92c53300491e?auto=format&fit=crop&w=800&q=60')
ON DUPLICATE KEY UPDATE `id`=`id`;

--
-- Demo data for table `products`
--
INSERT INTO `products` (`id`, `shop_id`, `name`, `description`, `price`, `image`, `category`) VALUES
(1, 1, 'Smartphone X', 'The latest and greatest smartphone with a stunning display and a powerful camera.', 799.99, 'https://images.unsplash.com/photo-1511707171634-5f897ff02aa9?auto=format&fit=crop&w=500&q=60', 'Electronics'),
(2, 1, 'Wireless Headphones', 'Noise-cancelling headphones with superior sound quality and 24-hour battery life.', 149.50, 'https://images.unsplash.com/photo-1505740420928-5e560c06d30e?auto=format&fit=crop&w=500&q=60', 'Electronics'),
(3, 2, 'Organic Avocados (3-pack)', 'Creamy and delicious organic avocados, perfect for toast or guacamole.', 4.99, 'https://images.unsplash.com/photo-1523049673857-eb18f1d7b578?auto=format&fit=crop&w=500&q=60', 'Food'),
(4, 2, 'Artisan Sourdough Bread', 'A freshly baked loaf of sourdough bread with a crispy crust and soft interior.', 6.50, 'https://images.unsplash.com/photo-1534620808146-d33bb39128b2?auto=format&fit=crop&w=500&q=60', 'Food')
ON DUPLICATE KEY UPDATE `id`=`id`;

-- Insert sample slides
INSERT INTO slides (title, description, image_path) VALUES 
('Fast & Reliable Delivery', 'Get your packages delivered in record time across the city', 'assets/images/slides/slide1.jpg'),
('Shop & Deliver', 'Order from local stores and get items delivered to your doorstep', 'assets/images/slides/slide2.jpg'),
('Become a Rider', 'Join our team of delivery riders and earn on your schedule', 'assets/images/slides/slide3.jpg')
ON DUPLICATE KEY UPDATE `id`=`id`;

--
-- Demo data for table `deliveries`
--
INSERT INTO `deliveries` (`id`, `user_id`, `rider_id`, `tracking_id`, `package_description`, `pickup_location`, `delivery_location`, `status`) VALUES
(1, 2, 4, 'MBX12345678', 'Important Documents', '100 Main St, Cityville', '200 Park Ave, Cityville', 'in_transit'),
(2, 2, NULL, 'MBX87654321', 'Birthday Gift', '55 Oak St, Suburbia', '77 Pine Ln, Suburbia', 'pending')
ON DUPLICATE KEY UPDATE `id`=`id`;