create table cities(
	city_id int auto_increment,
    name varchar(30) not null,
    constraint PK_city primary key(city_id)
);

CREATE TABLE users(
	user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255),
    balance DECIMAL(15, 2) NOT NULL,
    city_id INT NOT NULL,
    register_date DATETIME NOT NULL,
    CONSTRAINT FK_users_cities FOREIGN KEY(city_id) REFERENCES cities(city_id)
);

CREATE TABLE scooters(
	scooter_id INT PRIMARY KEY AUTO_INCREMENT,
    model VARCHAR(255) NOT NULL,
    gps_position VARCHAR(255) NOT NULL,
    is_taken BOOL DEFAULT FALSE
);

CREATE TABLE rents(
	rent_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    scooter_id INT,
    start_date DATETIME NOT NULL,
    end_date DATETIME,
	is_completed BOOL DEFAULT FALSE,
    CONSTRAINT FK_rents_users FOREIGN KEY(user_id) REFERENCES users(user_id),
    CONSTRAINT FK_rents_scooters FOREIGN KEY(scooter_id) REFERENCES scooters(scooter_id)
);
-- Query 2
SELECT name FROM cities
ORDER BY name
LIMIT 5;

-- Query 3
SELECT username, register_date FROM users
WHERE balance = 0
ORDER BY register_date DESC, username ASC;

-- Query 4
SELECT username, balance FROM users AS u
JOIN cities AS c
ON c.city_id = u.city_id
WHERE c.name = 'Varna'
ORDER BY balance DESC
LIMIT 1;

-- Query 5
SELECT username, balance FROM users AS u
LEFT JOIN rents AS r
ON u.user_id = r.user_id
WHERE r.rent_id IS NULL
ORDER BY balance DESC, username
LIMIT 5;


SELECT username, balance FROM users
WHERE user_id NOT IN (SELECT DISTINCT user_id FROM rents)
ORDER BY balance DESC, username
LIMIT 5;

-- Query 6
SELECT
	COUNT(*) AS total_rents,
    c.name
FROM rents AS r
JOIN users AS u
ON u.user_id = r.user_id
JOIN cities AS c
ON c.city_id = u.city_id
GROUP BY c.name
ORDER BY total_rents DESC, r.rent_id
LIMIT 10;

-- Query 7
SELECT
	COUNT(*) AS 'total_rents',
    u.username
FROM users AS u
JOIN rents AS r
ON r.user_id = u.user_id
GROUP BY u.username
ORDER BY total_rents DESC, u.username
LIMIT 5;

-- Query 8
SELECT COUNT(*) AS 'not_returned' FROM rents AS r
JOIN users AS u
ON u.user_id = r.user_id
JOIN cities AS c
ON c.city_id = u.city_id
WHERE c.name = 'Plovdiv' AND r.is_completed = FALSE;