# This Python file uses the following encoding: utf-8
#!/usr/bin/env python
import json
import requests
from bs4 import BeautifulSoup
import pymysql


# functions -------------------------------------------------------------------------------

def zarosice():
    URL = "http://www.zerogas.cz/pobocka/zarosice"
    page = requests.get(URL)
    soup = BeautifulSoup(page.content, "html.parser")

    result = soup.select_one("#totembody table tr:nth-child(2) td div")
    natural = result.string

    result = soup.select_one("#totembody table tr:nth-child(4) td div")
    diesel = result.string

    return natural, diesel

def zdanice():
    URL = "https://www.jolik.cz/chemis/cerpaci-stanice-phm/?phm_pobocka=9"
    page = requests.get(URL)
    soup = BeautifulSoup(page.content, "html.parser")

    natural1 = soup.select_one("#phm_ceny .phm_cena:nth-child(2) span:nth-child(1) strong")
    natural2 = soup.select_one("#phm_ceny .phm_cena:nth-child(2) span:nth-child(2) strong")
    natural3 = soup.select_one("#phm_ceny .phm_cena:nth-child(2) span:nth-child(4) strong")
    natural4 = soup.select_one("#phm_ceny .phm_cena:nth-child(2) span:nth-child(5) strong")
    natural = natural1.string + natural2.string + "." + natural3.string + natural4.string

    diesel1 = soup.select_one("#phm_ceny .phm_cena:nth-child(4) span:nth-child(1) strong")
    diesel2 = soup.select_one("#phm_ceny .phm_cena:nth-child(4) span:nth-child(2) strong")
    diesel3 = soup.select_one("#phm_ceny .phm_cena:nth-child(4) span:nth-child(4) strong")
    diesel4 = soup.select_one("#phm_ceny .phm_cena:nth-child(4) span:nth-child(5) strong")
    diesel = diesel1.string + diesel2.string + "." + diesel3.string + diesel4.string
    
    return natural, diesel

def kyjov_csad():
    URL = "http://www.csadkyjov.cz/cerpaci-stanice/"
    page = requests.get(URL)
    soup = BeautifulSoup(page.content, "html.parser")

    result = soup.select_one("footer .price-box .price:nth-child(2) strong")
    natural = result.string
    natural = natural.replace(",", ".")

    result = soup.select_one("footer .price-box .price:nth-child(1) strong")
    diesel = result.string
    diesel = diesel.replace(",", ".")

    return natural, diesel

def kyjov_sebesta():
    URL = "https://www.mbenzin.cz/Ceny-benzinu-a-nafty/Kyjov/Sebesta-Svatoborska-59187/18741"
    page = requests.get(URL)
    soup = BeautifulSoup(page.content, "html.parser")

    result = soup.select_one("#sectionHighlight #ContentPlaceHolder1_lN95Cost")
    natural = result.string
    natural = natural.replace(",", ".")

    result = soup.select_one("#sectionHighlight #ContentPlaceHolder1_lDieselCost")
    diesel = result.string
    diesel = diesel.replace(",", ".")

    return natural, diesel

# run -------------------------------------------------------------------------------

try:
    conn = pymysql.connect(
        user="michaldb",
        password="JakSeTiDari12",
        host="127.0.0.1",
        port=3306,
        database="skoula"

    )
except pymysql.Error as e:
    print(f"Error connecting to MariaDB Platform: {e}")
    sys.exit(1)

cur = conn.cursor(pymysql.cursors.DictCursor)

zarosice_natural, zarosice_diesel = zarosice()
zdanice_natural, zdanice_diesel = zdanice()
kyjov_csad_natural, kyjov_csad_diesel = kyjov_csad()
kyjov_sebesta_natural, kyjov_sebesta_diesel = kyjov_sebesta()

sql = """
INSERT INTO diesel 
(zarosice_natural, zarosice_diesel, zdanice_natural, zdanice_diesel, kyjov_csad_natural, kyjov_csad_diesel, kyjov_sebesta_natural, kyjov_sebesta_diesel)
VALUES 
(%s, %s, %s, %s, %s, %s, %s, %s)
"""

val = (
    zarosice_natural,
    zarosice_diesel,
    zdanice_natural,
    zdanice_diesel,
    kyjov_csad_natural,
    kyjov_csad_diesel,
    kyjov_sebesta_natural,
    kyjov_sebesta_diesel,
)
print(val)
cur.execute(sql, val)
conn.commit()