import requests
from bs4 import BeautifulSoup
from datetime import datetime, timedelta
import mysql.connector
from mysql.connector import Error
import logging
import os
import sys

sys.path.insert(0, os.path.dirname(__file__))

def app(environ, statr_response):
    statr_response('200 OK', [('Content-Type', 'text/plain')])
    message = 'Radi!\n'
    version = 'Python v' + sys.version.split()[0] + '\n'
    response = '\n'.join([message, version])
    return [response.encode()]

def insert_db(groblje, tip, ime, god, sat, datum):
    try:
        connection = mysql.connector.connect(host="localhost", user="nikolaca_root", password="YeVu,q~$amvr", database="nikolaca_pogrebno")
        #connection = mysql.connector.connect(host="localhost", user="root", password="", database="jkp")
        cursor = connection.cursor()
        mySql_insert_query = "INSERT INTO raspored (groblje, tip, ime, god, sat, datum) VALUES (%s, %s, %s, %s, %s, %s)"
        recordTuple = (groblje, tip, ime, god, sat, datum)
        cursor.execute(mySql_insert_query, recordTuple)
        connection.commit()
        print(cursor.rowcount, "Record inserted successfully ")
    except mysql.connector.Error as error:
        print("Failed to insert record into MySQL table {}".format(error))
    finally:
        if (connection.is_connected()):
            cursor.close()
            connection.close()
            print("MySQL connection is closed")

def is_not_blank(s):
    return bool(s and s.strip())

def get_res(URL,datum):
    #connection = mysql.connector.connect(host="localhost", user="root", password="", database="jkp")
    connection = mysql.connector.connect(host="localhost", user="nikolaca_root", password="YeVu,q~$amvr", database="nikolaca_pogrebno")
    page = requests.get(URL)
    soup = BeautifulSoup(page.content, 'html.parser')
   
    results = soup.find(id='contentArea')
    for e in soup.findAll('br'):
        e.extract()

    el = []
    table_elem = results.find_all('table', class_='black_table')
    for i, table in enumerate(table_elem):
        if is_not_blank(table.previous_element.previous_element):
            groblje = table.previous_element.previous_element
        tr = table.select('thead tr')[0]
        tip_sahrane = tr.find('td').text

        for row in table.select('tbody tr'):
            row_text = [x.text for x in row.find_all('td')]
            ime = row_text[0]
            god = row_text[1]
            sat = row_text[2]
            el.append({'groblje':groblje,'tip':tip_sahrane,'ime':ime,'god':god,'sat':sat,'datum':datum})
            
    mycursor = connection.cursor()
    sql = "SELECT COUNT(1) FROM `raspored` WHERE `datum` = %s"
    datum2 = (datum,)
    mycursor.execute(sql, datum2)

    if not mycursor.fetchone()[0]:
        for data in el:			
            insert_db(str(data['groblje']), str(data['tip']), str(data['ime']), str(data['god']), str(data['sat']), str(data['datum']))

    if (connection.is_connected()):
        mycursor.close()
        connection.close()
        print("MySQL connection is closed")

today = datetime.today() #danas
tomorrow = today + timedelta(days = 1) #sutra
aftertomorrow = today + timedelta(days = 2) #prekosutra
l1 = 'http://www.beogradskagroblja.rs/Termini.htm?language=sr-Latn-CS&day='+today.strftime("%d%m%Y")
l2 = 'http://www.beogradskagroblja.rs/Termini.htm?language=sr-Latn-CS&day='+tomorrow.strftime("%d%m%Y")
l3 = 'http://www.beogradskagroblja.rs/Termini.htm?language=sr-Latn-CS&day='+aftertomorrow.strftime("%d%m%Y")
d1 = today.strftime('%Y-%m-%d')
d2 = tomorrow.strftime('%Y-%m-%d')
d3 = aftertomorrow.strftime('%Y-%m-%d')
get_res(l1, d1)
get_res(l2, d2)
get_res(l3, d3)