import mysql.connector

#all the tables we did in our data base in order to do the checks.

#connect to the database
DB = mysql.connector.connect(user='ADMIN', password='ADMIN', host='localhost', database='pym')
sql = DB.cursor()

# msg table (the messaging table)
msg = "CREATE TABLE pym.msg(MsgID INT NOT NULL AUTO_INCREMENT, surce TEXT NOT NULL, dest TEXT NOT NULL, date TEXT, content TEXT, PRIMARY KEY ( MsgID )); "
sql.execute(msg)


# uesers table
msg = "CREATE TABLE pym.users(UserID INT NOT NULL AUTO_INCREMENT, UserName TEXT NOT NULL, UserPassword TEXT NOT NULL, Premition ENUM('Admin', 'Standard','','') NOT NULL, PRIMARY KEY ( UserID ));"
sql.execute(msg)

# devices table
msg = "CREATE TABLE pym.device(DEV_ID INT NOT NULL AUTO_INCREMENT, DEV_Type ENUM('IR_sensor', 'Door_sensor','Relay_modul','Temperature_sensor','Tilt_sensor'), DEV_location TEXT NOT NULL, DEV_options ENUM('active', 'passive','','') NOT NULL, DEV_stat TEXT NOT NULL, PRIMARY KEY ( DEV_ID ));"
sql.execute(msg)

DB.commit()
sql.close()
DB.close()