import mysql.connector
import random
import string

from datetime import datetime

def id_generator(size=5, chars=string.ascii_uppercase + string.digits):
    return ''.join(random.choice(chars) for _ in range(size))


def mySqlRecords(array):
  mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="kelner"
  )

  timestamp = 1545730073
  mycursor = mydb.cursor(buffered=True)
  # Wyciąganie aktualnego aktywnego szablonu oraz przypisywanie ID do activeTemplateID
  mycursor.execute("SELECT id FROM template WHERE active=1")
  myresult = mycursor.fetchone()
  activeTemplateID = str(myresult[0])



  # for x in myresult:
  #   print(x)


  price = 0
  # array = [[1, 0], [2, 0], [3, 1], [4, 0], [5, 0], [6, 0], [7, 1], [8, 0], [9, 1]]

  # Wyliczanie ceny
  for x in array:
    if x[1] == 1:
      # Wyciaganie szablonu dan po id szablonu oraz numerze checkboxu
      checkboxnumber = str(x[0])
      mycursor.execute("SELECT * FROM templatedishmap WHERE template_id=" + activeTemplateID + " AND checkboxnumber=" + checkboxnumber)
      myresult = mycursor.fetchone()
      price = price + myresult[3]*myresult[5]

  # Dodanie zamówienia
  sql = "INSERT INTO orders (number, price, active, created_at) VALUES (%s, %s, 1, %s)"
  val = (str(id_generator()), str(price), str(datetime.fromtimestamp(timestamp)))
  mycursor.execute(sql, val)
  mydb.commit()

  # Sprawdzenie id ostatniego zamowienia
  mycursor = mydb.cursor(buffered=True)
  mycursor.execute("SELECT id FROM orders ORDER BY id DESC")
  myresult = mycursor.fetchone()
  addedID = myresult[0]



  for x in array:
    if x[1] == 1:
      # Wyciaganie szablonu dan po id szablonu oraz numerze checkboxu
      checkboxnumber = str(x[0])
      mycursor.execute("SELECT * FROM templatedishmap WHERE template_id=" + activeTemplateID + " AND checkboxnumber=" + checkboxnumber)
      myresult = mycursor.fetchone()
      templateDishMapID = myresult[0]
      # Dodawanie poszczegolnych potraw do zamowienia
      sql = "INSERT INTO orders_template_dish_map (order_id, template_id, created_at) VALUES (%s, %s, %s)"
      val = (str(addedID), str(templateDishMapID), str(datetime.fromtimestamp(timestamp)))
      mycursor.execute(sql, val)

  mydb.commit()