Ride Sharing DB

001-schema.sql

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone_number VARCHAR(20) NOT NULL UNIQUE,
    password_hash CHAR(60) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active TINYINT(1) DEFAULT 1,
    INDEX idx_email (email),
    INDEX idx_phone (phone_number)
);

CREATE TABLE drivers (
    driver_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    license_number VARCHAR(50) NOT NULL UNIQUE,
    license_expiry DATE NOT NULL,
    vehicle_id BIGINT NOT NULL,
    current_location POINT NOT NULL,
    is_available TINYINT(1) DEFAULT 0,
    rating DECIMAL(3,2),
    total_trips INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    SPATIAL INDEX idx_location (current_location),
    INDEX idx_availability (is_available)
);

CREATE TABLE vehicles (
    vehicle_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    driver_id BIGINT NOT NULL,
    make VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    year INT NOT NULL,
    color VARCHAR(30) NOT NULL,
    license_plate VARCHAR(20) NOT NULL UNIQUE,
    vehicle_type ENUM('STANDARD', 'PREMIUM', 'XL') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (driver_id) REFERENCES drivers(driver_id)
);

CREATE TABLE rides (
    ride_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    passenger_id BIGINT NOT NULL,
    driver_id BIGINT NOT NULL,
    pickup_location POINT NOT NULL,
    dropoff_location POINT NOT NULL,
    request_time TIMESTAMP NOT NULL,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    status ENUM('REQUESTED', 'ACCEPTED', 'STARTED', 'COMPLETED', 'CANCELLED') NOT NULL,
    base_fare DECIMAL(10,2) NOT NULL,
    final_fare DECIMAL(10,2),
    distance_km DECIMAL(10,2),
    duration_minutes INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (passenger_id) REFERENCES users(user_id),
    FOREIGN KEY (driver_id) REFERENCES drivers(driver_id),
    SPATIAL INDEX idx_pickup (pickup_location),
    SPATIAL INDEX idx_dropoff (dropoff_location),
    INDEX idx_status (status),
    INDEX idx_request_time (request_time)
);

CREATE TABLE payments (
    payment_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    ride_id BIGINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method_id BIGINT NOT NULL,
    status ENUM('PENDING', 'COMPLETED', 'FAILED', 'REFUNDED') NOT NULL,
    transaction_id VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (ride_id) REFERENCES rides(ride_id),
    INDEX idx_status (status)
);

CREATE TABLE payment_methods (
    payment_method_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    type ENUM('CREDIT_CARD', 'DEBIT_CARD', 'PAYPAL', 'APPLE_PAY', 'GOOGLE_PAY') NOT NULL,
    last_four CHAR(4),
    expiry_date DATE,
    is_default TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    INDEX idx_user_default (user_id, is_default)
);

CREATE TABLE ratings (
    rating_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    ride_id BIGINT NOT NULL,
    rater_user_id BIGINT NOT NULL,
    rated_user_id BIGINT NOT NULL,
    rating TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ride_id) REFERENCES rides(ride_id),
    FOREIGN KEY (rater_user_id) REFERENCES users(user_id),
    FOREIGN KEY (rated_user_id) REFERENCES users(user_id),
    UNIQUE KEY unique_ride_rating (ride_id, rater_user_id, rated_user_id)
);

002-data.sql

-- Insert Users
INSERT INTO users (email, phone_number, password_hash, first_name, last_name, date_of_birth) VALUES
('[email protected]', '+1234567890', '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewKyBAQ', 'John', 'Doe', '1990-01-15'),
('[email protected]', '+1234567891', '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewKyBAR', 'Jane', 'Smith', '1992-03-21'),
('[email protected]', '+1234567892', '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewKyBAS', 'Mike', 'Johnson', '1988-07-12'),
('[email protected]', '+1234567893', '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewKyBAT', 'Sarah', 'Williams', '1991-11-30');

-- Insert Drivers
INSERT INTO drivers (driver_id, user_id, license_number, license_expiry, vehicle_id, current_location, is_available, rating) VALUES
(1, 3, 'DL123456', '2025-12-31', 1, ST_GeomFromText('POINT(90.38424608678892 23.74358672905388)'), TRUE, 4.8),
(2, 4, 'DL789012', '2024-10-15', 2, ST_GeomFromText('POINT(90.35599132737624 23.74968242239482)'), TRUE, 4.9);

-- Insert Vehicles
INSERT INTO vehicles (driver_id, make, model, year, color, license_plate, vehicle_type) VALUES
(1, 'Toyota', 'Camry', 2020, 'Silver', 'ABC123', 'STANDARD'),
(2, 'Honda', 'Accord', 2021, 'Black', 'XYZ789', 'PREMIUM');

-- Insert Sample Rides
INSERT INTO rides (passenger_id, driver_id, pickup_location, dropoff_location, request_time, start_time, end_time, status, base_fare, final_fare, distance_km, duration_minutes) VALUES
(1, 1, ST_GeomFromText('POINT(-73.9857 40.7484)'), ST_GeomFromText('POINT(-74.0060 40.7128)'), 
 '2024-03-01 10:00:00', '2024-03-01 10:05:00', '2024-03-01 10:25:00', 'COMPLETED', 15.00, 18.50, 3.2, 20),
(2, 2, ST_GeomFromText('POINT(-73.9654 40.7829)'), ST_GeomFromText('POINT(-73.9472 40.7876)'),
 '2024-03-01 11:30:00', '2024-03-01 11:35:00', '2024-03-01 11:50:00', 'COMPLETED', 12.00, 14.75, 2.1, 15);

-- Insert Payments
INSERT INTO payments (ride_id, amount, payment_method_id, status, transaction_id) VALUES
(1, 18.50, 1, 'COMPLETED', 'TXN123456'),
(2, 14.75, 2, 'COMPLETED', 'TXN789012');

-- Insert Ratings
INSERT INTO ratings (ride_id, rater_user_id, rated_user_id, rating, comment) VALUES
(1, 1, 3, 5, 'Excellent service, very professional driver'),
(1, 3, 1, 4, 'Pleasant passenger, clear communication'),
(2, 2, 4, 5, 'Great experience, driver was very helpful'),
(2, 4, 2, 5, 'Wonderful passenger, very polite');

003-query.sql

-- Find all available drivers from a point
SELECT 
    d.driver_id,
    CONCAT(u.first_name, ' ', u.last_name) as driver_name,
    v.make, 
    v.model,
    d.rating,
    ST_Distance_Sphere(
        ST_GeomFromText('POINT(90.3934 23.7507)'),
        d.current_location
    ) / 1000 as distance_km
FROM drivers d
JOIN users u ON d.user_id = u.user_id
JOIN vehicles v ON d.vehicle_id = v.vehicle_id
WHERE d.is_available = 1
HAVING distance_km < 5
ORDER BY distance_km;