import mysql.connector as c

#method to create a fresh instance of the database and tables
#call to this method will delete all existing data
def initialize():
    con = c.connect(host="localhost", user="root", passwd="")
    mycur = con.cursor()
    mycur.execute("CREATE DATABASE IF NOT EXISTS ashok2022")
    mycur.execute("USE ashok2022")
    mycur.execute("DROP TABLE IF EXISTS inventory")
    mycur.execute("DROP TABLE IF EXISTS sale")
    sql='''
    CREATE TABLE inventory(
              productid  INT(4) NOT NULL AUTO_INCREMENT,
              pname      VARCHAR(10)  NOT NULL,
              stock      INT(5)   NOT NULL,
              PRIMARY KEY(productid)
          )
    '''
    mycur.execute(sql)
    mycur.execute("ALTER TABLE inventory AUTO_INCREMENT=1000")
    mycur.execute("SET FOREIGN_KEY_CHECKS=0")
    sql='''
    CREATE TABLE sale(
            billno INT(5) NOT NULL AUTO_INCREMENT,
            customer  VARCHAR(10) NOT NULL,
            productid  INT(4)  NOT NULL,
            quantity   INT(3)  NOT NULL,
            price      INT(4)  NOT NULL,
            PRIMARY KEY(billno),
            CONSTRAINT FOREIGN KEY(productid) REFERENCES inventory(productid)
    )ENGINE="INNODB"
    '''
    mycur.execute(sql)
    mycur.execute("ALTER TABLE sale AUTO_INCREMENT=10000")
    sql="INSERT INTO inventory(pname, stock) VALUES(%s,%s)"
    rows=[("PEN",1000),("PENCIL", 1500),("INK", 900)]
    mycur.executemany(sql,rows)
    con.commit()
    con.close()

# This method will fetch the productid if availabe in inventory table
def getpid(pid):
    con = c.connect(host="localhost", user="root", passwd="", database="ashok2022")
    mycur = con.cursor()
    sql ="SELECT productid FROM inventory WHERE productid='%d'" % (pid)
    mycur.execute(sql)
    result = mycur.fetchone()
    con.close()
    if result==None:
        return False
    else:
        return result[0]

#This method will increase the stock of an existing product.
def updatestock(pid,val):
    if getpid(pid): #calling the getpid method to check if product available
        con = c.connect(host="localhost", user="root", passwd="", database="ashok2022")
        mycur = con.cursor()
        sql="UPDATE inventory SET stock=stock+'%d' WHERE productid='%d'" % (val, pid)
        mycur.execute(sql)
        con.close()
    else:
        print("Product not found")
    con.close()
#Method to display incventory details    
def showinventory():
    con = c.connect(host="localhost", user="root", passwd="", database="ashok2022")
    mycur = con.cursor()
    sql = "SELECT * FROM INVENTORY"
    mycur.execute(sql)
    result=mycur.fetchall()
    print(result)
    
#Method to insert a new record in inventory table     
def addinventory(item,st):
    con = c.connect(host="localhost", user="root", passwd="", database="ashok2022")
    mycur = con.cursor()
    sql = "INSERT INTO inventory(pname,stock) VALUES(%s,%s)"
    rows=[(item,st)]
    mycur.executemany(sql,rows)
    con.commit()
    print("Inventory added")
    con.close()

#Method to decrease the stock
def decreasestock(pid,val):
    if getpid(pid):
        con = c.connect(host="localhost", user="root", passwd="", database="ashok2022")
        mycur = con.cursor()
        sql="UPDATE inventory SET stock=stock-'%d' WHERE productid='%d'" % (val,pid)
        mycur.execute(sql)
        con.commit()
        con.close()

#Method to get the availabe stock of a given product.

def getstock(pid):
    if getpid(pid):
        con = c.connect(host="localhost", user="root", passwd="", database="ashok2022")
        mycur = con.cursor()
        sql = "SELECT stock FROM inventory WHERE productid='%d'" % (pid)
        mycur.execute(sql)
        result = mycur.fetchone()
        con.close()
        return result[0]

#Method to sell a product
def saleproduct(pid,qty,cust,pri):
    if getpid(pid):
        if getstock(pid)-qty>=0:
            con = c.connect(host="localhost", user="root", passwd="", database="ashok2022")
            mycur = con.cursor()
            mycur.execute("SET FOREIGN_KEY_CHECKS=0")
            sql = "INSERT INTO sale(customer,productid, quantity, price) VALUES(%s,%s,%s,%s)"
            rows=[(cust,pid,qty,pri)]
            mycur.executemany(sql,rows)
            con.commit()
            con.close()
            #Calling method to decrease stock after sale
            decreasestock(pid,qty)
            bill(pid)
        else:
            print("Stock not avaiable")
    else:
        print("Product not available")
#Display all the entries in sales table            
def displaysale():
    con = c.connect(host="localhost", user="root", passwd="", database="ashok2022")
    mycur = con.cursor()
    mycur.execute("SELECT * FROM sale")
    result = mycur.fetchall()
    print(result)
    con.close()

def bill(pid):
    try:
        con = c.connect(host="localhost", user="root", passwd="", database="ashok2022")
        mycur = con.cursor()
        mycur.execute("SELECT * FROM sale WHERE productid='%d'" % (pid))
        result = mycur.fetchone()
        print(result)
    except c.Error as err:
        print(err)
    finally:
        con.close()
while True:
    # Call these methods with user input parameters in a menu based application
    print("*"*40)
    print("Type 1 to put the project to entry state.")
    print("Type 2 to add inventory.")
    print("Type 3 to update inventory.")
    print("Type 4 to display inventory.")
    print("Type 5 to perform sale.")
    choice = int(input("Enter your choice between 1-5:"))
    if choice==1:
        initialize()
        print("Application put to return state.")
    elif choice==2:
        prod = input("Enter the name of item:")
        stock = int(input("Enter the initial stock:"))
        addinventory(prod,stock)
    elif choice==3:
        pid = int(input("Enter the productid:"))
        amt = int(input("Enter the amount of stock"))
        updatestock(pid,amt)
    elif choice==4: 
        showinventory()
    elif choice==5:
        pid = int(input("Enter the productid:"))
        name=input("Enter name of customer:")
        qty=int(input("Enter the quantity:"))
        amt=int(input("Enter the price per unit:"))
        saleproduct(pid,qty,name,amt)
    elif choice==6:
        displaysale()