from __future__ import print_function

import random
import mysql.connector
from mysql.connector import errorcode
from collections import OrderedDict

mainIngredients = ['pork','turkey','chiken','lamb','shrink','sardines','salmon','perch','tuna','whitefish','tofu']
secondaryIngredients = ['rice','pasta','spaghetti','milk','cheese','paprika','salt','pepper','beans','potatoes','onion','spinach','mushroom','garlic','carrot','broccoli','cabbage','sugar','olive oils']



recipes=[]

DB_NAME = 'rfid'
TABLES = OrderedDict()

TABLES['Ingredient'] = (
	"CREATE TABLE `Ingredient` ("
	" `ID` int not null AUTO_INCREMENT  primary key,"
	"  `name` varchar(30) not null "
") ENGINE=InnoDB")

TABLES['Recipe'] = (
	"CREATE TABLE `Recipe` ("
	" `ID` int  not null primary key,"
	"  `name` varchar(30) "
") ENGINE=InnoDB")

TABLES['RecipeIngredient'] = (
	"CREATE TABLE `RecipeIngredient` ("
 "`RecipeID` int not null  ,"
 "`ingredientID` int not null ,"
 "Primary key(RecipeID,ingredientID),"
 "constraint `fk_ingredient` foreign key(`ingredientID`) references `Ingredient`(`ID`),"
 "constraint `fk_recipe` foreign key(`RecipeID`) references `Recipe`(`ID`) "
") ENGINE=InnoDB")

TABLES['Restritions'] = (
	"CREATE TABLE `Restritions` ("
	"`nameReason` varchar(30) primary key,"
	"`ingredientID` int not null,"
"constraint fk_ingredientRestrict foreign key(ingredientID) references Ingredient(ID)"
") ENGINE=InnoDB")

TABLES['student'] = (
	"CREATE TABLE `student` ("
	"  `studentID` int NOT NULL  AUTO_INCREMENT primary key,"
	"  `birth_date` date NOT NULL,"
	"  `first_name` varchar(30) NOT NULL,"
	"  `last_name` varchar(30) NOT NULL,"
	"  `address` varchar(30) NOT NULL,"
	"	`restritionsname` varchar(30),"
	"constraint `fk_restritions` foreign key(`restritionsname`) references `Restritions`(`nameReason`)"
	") ENGINE=InnoDB")


for i in range(0,1000):
	tmp = []
	tmp.append(random.choice(mainIngredients))
	name = tmp[0];
	secondary = random.randint(3,6)
	for _ in range(0, secondary):
		choice = random.choice(secondaryIngredients)
		if choice not in tmp:
			tmp.append(choice)
			name += choice[0];
	if sorted(tmp) not in sorted(recipes):
		recipes.append([tmp,name])

print(recipes[0])

cnx = mysql.connector.connect(user='root', password='root',
							  host='127.0.0.1',
							  )


cursor = cnx.cursor(buffered=True)
try:
	cursor.execute("drop schema rfid")
except:
	pass

def create_database(cursor):
	try:
		cursor.execute(
			"CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))
	except mysql.connector.Error as err:
		print("Failed creating database: {}".format(err))
		exit(1)

try:
	cnx.database = DB_NAME
except mysql.connector.Error as err:
	if err.errno == errorcode.ER_BAD_DB_ERROR:
		create_database(cursor)
		cnx.database = DB_NAME
	else:
		print(err)
		exit(1)
for name, ddl in TABLES.iteritems():
	try:
		print("Creating table {}: ".format(name), end='')
		cursor.execute(ddl)
	except mysql.connector.Error as err:
		if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
			print("already exists.")
		else:
			print(err.msg)
	else:
		print("OK")
for i in mainIngredients:
	cursor.execute('INSERT INTO Ingredient (name) VALUES ("{}")'.format(i))
for i in secondaryIngredients:
	cursor.execute('INSERT INTO Ingredient (name) VALUES ("{}")'.format(i))
cnx.commit()


for index, item in enumerate(recipes):
	cursor.execute('INSERT INTO Recipe(id,name) VALUES ("{}","{}")'.format(index,item[1]))
	for j in item[0]:
		cursor.execute("SELECT * FROM Ingredient where name ='{}'".format(j))
		for (id,name) in cursor:
			cursor.execute('INSERT INTO RecipeIngredient (RecipeID,ingredientID) VALUES ({},{})'.format(index,id))

cnx.commit()
cursor.close()
cnx.close()