import sqlite3
import requests
from bs4 import BeautifulSoup



r = requests.get('https://www.myauto.ge/ka/s/00/0/3/00/00/00/00/bmw?stype=0&marka=3&currency_id=3&det_search=0&ord=7&category_id=m0&keyword=')
c = r.content
soup = BeautifulSoup(c, "html.parser")
rows = soup.find_all("div",{"class": "current-item-inner"})
carinfolist = []
for item in rows:
    name = item.find("div", {"class":"car-name-left"}).text.strip().split(" ")
    fullname = name[1] + " " +name[2]
    price = []
    try:
        prices = item.find_all("span", {"class": "car-price"})
        lari = prices[0].text
        dolari = prices[1].text.strip()

        price.append([lari,dolari])

    except:
        price.append("ფასი შეთანხმებით")
    carinfolist.append([fullname, price])


def create_db_tble() -> None:


    conn = sqlite3.connect('carinfoDB.db')
    cursor = conn.cursor()

    _SQL = 'CREATE TABLE IF NOT EXISTS carinfo(ID INTEGER  PRIMARY KEY AUTOINCREMENT, Carname TEXT, lari TEXT, dolari TEXT)'
    cursor.execute(_SQL)
    conn.commit()
    cursor.close()
    conn.close()

def insert_carinfo(carname: str, lari: str, dolari: str) -> None:
    conn = sqlite3.connect('carinfoDB.db')
    cursor = conn.cursor()

    _SQL = 'INSERT INTO carinfo(Carname,lari,dolari) VALUES(?,?,?)'

    cursor.execute(_SQL, (carname,lari,dolari))

    conn.commit()
    cursor.close()
    conn.close()


def delete_carinfo(ID: int) ->None:
    conn = sqlite3.connect('carinfoDB.db')
    cursor = conn.cursor()

    _SQL = 'DELETE FROM carinfo WHERE ID = ?'

    cursor.execute(_SQL,(ID, ))

    conn.commit()
    cursor.close()
    conn.close()

def view_carinfo():
    conn = sqlite3.connect('carinfoDB.db')
    cursor = conn.cursor()

    _SQL = 'SELECT * FROM carinfo'

    cursor.execute(_SQL)

    data = cursor.fetchall()


    for index, item in enumerate(data):
        print(str(index + 1) + '. ' + str(item))


    cursor.close()
    conn.close()


create_db_tble()


def options_menu() -> str:
    print('\nStudents database program')

    print('1.Insert carinfo')

    print('2.delete carinfo')

    print('3.view carinfo')

    print('enter q to exit\n')

    opt = input("please choose option: ")
    return opt

option = options_menu()

while option != 'q':
    if option == '1':
        for a in carinfolist:
            if(len(a[1][0]) == 2):
                insert_carinfo(a[0], a[1][0][0],a[1][0][1])
            else:
                insert_carinfo(a[0], a[1][0],a[1][0])
        option = options_menu()

    elif option == '2':
        view_carinfo()

        ID = int(input("\nplease choose student ID: "))
        option = options_menu()

        delete_carinfo(ID)
    elif option == '3':
        view_carinfo()
        option = options_menu()
    else:
        print('please choose correct option!')
        option = options_menu()