import os
import sqlite3

# create a default path to connect to and create (if necessary) a database
# called 'database.sqlite3' in the same directory as this script
DEFAULT_PATH = os.path.join(os.path.dirname(__file__), 'database.sqlite3')


def main():
    con = db_connect()  # connect to the database
    cur = con.cursor()  # instantiate a cursor obj

    trains_sql = """
CREATE TABLE trains (
    id_pociagu text PRIMARY KEY,
    nazwa text NOT NULL,
    miejsca integer NOT NULL,
    predkosc real NOT NULL)"""
    cur.execute(trains_sql)  # add trains_sql table to db
    connections_sql = """
CREATE TABLE connections (
    id_pociagu text PRIMARY KEY,
    destination text NOT NULL,
    price real NOT NULL,
    FOREIGN KEY (id_pociagu) REFERENCES trains (id_pociagu))"""
    cur.execute(connections_sql)  # add connections_sql table to db

    create_train(con, 'IC 64106/7', 'DAMROT', 150, 80)
    create_connection(con, 'IC 64106/7', 'Opole Główne', 20)
    create_train(con, 'IC 6300/1', 'WYSPIAŃSKI', 200, 90)
    create_connection(con, 'IC 6300/1', 'Kraków Główny', 30)
    create_train(con, 'IC 6128/9', 'NAŁKOWSKA', 250, 85.5)
    create_connection(con, 'IC 6128/9', 'Warszawa Centralna', 45)
    create_train(con, 'TLK 74100/1', 'ŚLĄZAK', 125, 82)
    create_connection(con, 'TLK 74100/1', 'Katowice', 27.33)
    create_train(con, 'TLK 71106/7', 'KONOPNICKA', 155, 80)
    create_connection(con, 'TLK 71106/7', 'Częstochowa', 35.40)
    create_train(con, 'IC 6504/5', 'HEWELIUSZ', 250, 78)
    create_connection(con, 'IC 6504/5', 'Bydgoszcz Główna', 30)

    cur.execute("SELECT * FROM trains INNER JOIN connections ON trains.id_pociagu = connections.id_pociagu WHERE predkosc>85")
    for i in cur.fetchall():
        print(i)
    print()
    cur.execute("SELECT * FROM trains INNER JOIN connections ON trains.id_pociagu = connections.id_pociagu WHERE nazwa='KONOPNICKA'")
    for i in cur.fetchall():
        print(i)
    print()
    cur.execute("SELECT * FROM trains INNER JOIN connections ON trains.id_pociagu = connections.id_pociagu WHERE miejsca>150 AND price<=30")
    for i in cur.fetchall():
        print(i)
    print()
    # ponizej : usuwa wszystkie pociagi ktorych ceny biletu sa mniejsze rowne 30
    cur.execute("DELETE FROM trains WHERE id_pociagu IN ( SELECT id_pociagu FROM connections WHERE price <=30)")
    cur.execute("SELECT * FROM trains INNER JOIN connections ON trains.id_pociagu = connections.id_pociagu")
    for i in cur.fetchall():
        print(i)
    # jak widac, zostaly tylko 2 pociagi, bo reszta miala tansze bilety niz 30


def db_connect(db_path=DEFAULT_PATH):
    con = sqlite3.connect(db_path)
    return con


def create_train(con, id_pociagu, nazwa, miejsca, predkosc):
    sql = """
        INSERT INTO trains (id_pociagu, nazwa, miejsca, predkosc) VALUES (?, ?, ?, ?)"""
    cur = con.cursor()
    cur.execute(sql, (id_pociagu, nazwa, miejsca, predkosc))


def create_connection(con, id_pociagu, destination, price):
    sql = """
        INSERT INTO connections (id_pociagu, destination, price) VALUES (?, ?, ?)"""
    cur = con.cursor()
    cur.execute(sql, (id_pociagu, destination, price))


if __name__ == '__main__':
    main()