-- Create Database
CREATE DATABASE IF NOT EXISTS jinja_tubes;
USE jinja_tubes;

-- 1. Users Table
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    fullname VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'driver', 'customer') DEFAULT 'customer',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Orders Table
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_ref VARCHAR(50) UNIQUE NOT NULL,
    customer_id INT,
    product_name VARCHAR(100),
    pickup_addr VARCHAR(255),
    drop_addr VARCHAR(255),
    price DECIMAL(10, 2),
    pickup_lat DECIMAL(10, 6),
    pickup_lng DECIMAL(10, 6),
    status ENUM('In Transit', 'Completed', 'Delivered', 'Pending') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES users(user_id)
);