bike-rental-with-ai/import_data.py

import sqlite3
import os
from werkzeug.security import generate_password_hash
from enum import Enum

# Database file path
db_file = '/app/bike_rental.db'

# Check if the database file exists and delete it
if os.path.exists(db_file):
    os.remove(db_file)

# Sample password hashes
hashed_password_1 = generate_password_hash('password1')
hashed_password_2 = generate_password_hash('password2')
hashed_password_3 = generate_password_hash('password3')
admin_password_1 = generate_password_hash('admin1password')
admin_password_2 = generate_password_hash('admin2password')
admin_password_3 = generate_password_hash('admin3password')
admin_password_4 = generate_password_hash('admin4password')
admin_password_5 = generate_password_hash('admin5password')
admin_password_6 = generate_password_hash('admin6password')

# Create the 'user' table
user_table = """
CREATE TABLE IF NOT EXISTS user (
    id INTEGER PRIMARY KEY,
    phone_number VARCHAR(8) UNIQUE NOT NULL,
    password_hash VARCHAR(128) NOT NULL,
    is_member BOOLEAN DEFAULT 0,
    is_admin BOOLEAN DEFAULT 0,
    station_id INTEGER,
    credit_card_number VARCHAR(19) NULL,
    credit_card_expiry VARCHAR(5) NULL,
    credit_card_cvv VARCHAR(4) NULL,
    FOREIGN KEY (station_id) REFERENCES station (id)
);
"""

# Insert sample data into the 'user' table
user_data = [
    (1, '12345678', hashed_password_1, 1, 0, None, '1234567890123456', '12/25', '123'),
    (2, '23456789', hashed_password_2, 1, 0, None, '2345678901234567', '06/27', '456'),
    (3, '34567890', hashed_password_3, 1, 0, None, '3456789012345678', '09/28', '789'),
    (4, '11223344', admin_password_1, 0, 1, 1, '', '', ''),
    (5, '22334455', admin_password_2, 0, 1, 2, '', '', ''),
    (6, '33445566', admin_password_3, 0, 1, 3, '', '', ''),
    (7, '44556677', admin_password_4, 0, 1, 4, '', '', ''),
    (8, '55667788', admin_password_5, 0, 1, 5, '', '', ''),
    (9, '66778899', admin_password_6, 0, 1, 6, '', '', '')
]

# Create the 'station' table
station_table = """
CREATE TABLE IF NOT EXISTS station (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(200) NOT NULL
);
"""

# Insert sample data into the 'station' table
station_data = [
    (1, 'Central Station', 'Central, Hong Kong'),
    (2, 'Kowloon Station', 'Tsim Sha Tsui, Kowloon'),
    (3, 'Sha Tin Station', 'Sha Tin, New Territories'),
    (4, 'Hong Kong Island Station', 'Hong Kong Island, Hong Kong'),
    (5, 'Kowloon Peninsula Station', 'Kowloon Peninsula, Hong Kong'),
    (6, 'New Territories Station', 'New Territories, Hong Kong')
]

class BikeAvailability(Enum):
    A_LOT = 4
    SOME = 3
    A_LITTLE = 2
    NONE = 1

# Create the 'bike' table
bike_table = """
CREATE TABLE IF NOT EXISTS bike (
    id INTEGER PRIMARY KEY,
    bike_type VARCHAR(50) NOT NULL,
    price_per_hour FLOAT NOT NULL,
    availability INTEGER NOT NULL CHECK (availability BETWEEN 1 AND 4)
);
"""

# Insert sample data into the 'bike' table
bike_data = [
    (1, 'Mountain Bike', 50.00, BikeAvailability.A_LOT.value),
    (2, 'Road Bike', 60.00, BikeAvailability.SOME.value),
    (3, 'Hybrid Bike', 45.00, BikeAvailability.A_LITTLE.value),
    (4, 'Electric Bike', 70.00, BikeAvailability.A_LOT.value),
    (5, 'Folding Bike', 40.00, BikeAvailability.SOME.value),
    (6, 'Cruiser Bike', 35.00, BikeAvailability.A_LITTLE.value),
    (7, 'BMX Bike', 55.00, BikeAvailability.SOME.value),
    (8, 'Tandem Bike', 80.00, BikeAvailability.A_LOT.value),
    (9, 'Recumbent Bike', 65.00, BikeAvailability.SOME.value),
    (10, 'Tricycle', 30.00, BikeAvailability.A_LITTLE.value),
    (11, 'Fat Tire Bike', 60.00, BikeAvailability.A_LOT.value)
]

# Create the 'rental' table
rental_table = """
CREATE TABLE IF NOT EXISTS rental (
    id INTEGER PRIMARY KEY,
    bike_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    start_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    end_time DATETIME,
    total_price FLOAT,
    num_bikes INTEGER NOT NULL DEFAULT 1,
    start_station_id INTEGER NOT NULL,
    end_station_id INTEGER,
    FOREIGN KEY (bike_id) REFERENCES bike (id),
    FOREIGN KEY (user_id) REFERENCES user (id),
    FOREIGN KEY (start_station_id) REFERENCES station (id),
    FOREIGN KEY (end_station_id) REFERENCES station (id)
);
"""

# Insert sample data into the 'rental' table
rental_data = [
    (1, 1, 1, '2024-04-01 10:00:00', '2024-04-01 12:30:00', 125.00, 1, 1, 1),
    (2, 2, 1, '2024-04-02 14:00:00', '2024-04-02 16:00:00', 120.00, 1, 2, 2),
    (3, 3, 1, '2024-04-03 09:30:00', '2024-04-03 11:00:00', 90.00, 1, 3, 1),
    (4, 1, 2, '2024-04-04 11:00:00', '2024-04-04 13:30:00', 125.00, 1, 1, 2),
    (5, 2, 2, '2024-04-05 13:00:00', '2024-04-05 15:00:00', 120.00, 1, 2, 3),
    (6, 3, 2, '2024-04-06 16:00:00', '2024-04-06 18:00:00', 90.00, 1, 3, 1),
    (7, 1, 3, '2024-04-07 08:00:00', '2024-04-07 10:00:00', 100.00, 1, 1, 2)
]

# Connect to the SQLite database
conn = sqlite3.connect(db_file)
c = conn.cursor()

# Create the tables
c.execute(station_table)
c.execute(bike_table)
c.execute(user_table)
c.execute(rental_table)

# Insert data into the tables
c.executemany("INSERT INTO station (id, name, location) VALUES (?, ?, ?)", station_data)
c.executemany("INSERT INTO bike (id, bike_type, price_per_hour, availability) VALUES (?, ?, ?, ?)", bike_data)
c.executemany("INSERT INTO user (id, phone_number, password_hash, is_member, is_admin, station_id, credit_card_number, credit_card_expiry, credit_card_cvv) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", user_data)
c.executemany("INSERT INTO rental (id, bike_id, user_id, start_time, end_time, total_price, num_bikes, start_station_id, end_station_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", rental_data)

# Commit the changes and close the connection
conn.commit()
conn.close()

print("Data imported successfully!")