import mysql.connector as connector
import hashlib

if __name__ == '__main__':
    # Make sure you start the server with
    # sudo service mysql start
    # Make sure you create a database demodb, and tables clients, orders
    # Check if server is running with
    # systemctl status mysql

    # Connect with root
    con = connector.connect(user='root')
    cur = con.cursor()

    # Use the demo database
    cur.execute('USE demodb;')
    # Print all databases
    cur.execute('SHOW DATABASES;')
    print('Databases')
    for line in cur:
        print(''.join(line))
    # Print all tables in demodb
    cur.execute('SHOW TABLES FROM demodb;')
    print('Tables')
    for line in cur:
        print(line)
    # Print all columns in tables
    cur.execute('SHOW COLUMNS FROM clients;')
    print('Clients columns')
    for line in cur:
        print(line)
    print('Orders columns')
    cur.execute('SHOW COLUMNS FROM orders;')
    for line in cur:
        print(line)
    # Add data to clients and orders tables
    for i in range(5):
        cur.execute('INSERT INTO clients VALUES ("John", "Hopkins", 100.02, %i);' %i)
    for i in range(5):
        cur.execute('INSERT INTO orders VALUES (%i, 0, 50.123);' %i)
    # Print data
    cur.execute('SELECT * FROM clients;')
    for line in cur:
        print(line)
    cur.execute('SELECT * FROM orders;')
    for line in cur:
        print(line)

    """
    Other usefull commands

    SELECT DISTINCT Country FROM Customers;
    SELECT * FROM Customers WHERE Country = '' AND (City = '' OR NOT City = ''); 
    SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC; 
    SELECT * FROM Customers LIMIT 3; 
    SELECT MAX(Price) AS LargestPrice FROM Products; 
    COUNT() AVG() SUM()
    SELECT * FROM Customers WHERE CustomerName LIKE 'a__%'; # at least 3 chars
    SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');
    SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);
    SELECT * FROM Orders WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
    SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;
    SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;
    SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
    SELECT City, Country FROM Customers WHERE Country='Germany' UNION SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;
    """

    # Empty tables
    cur.execute('DELETE FROM clients;')
    cur.execute('DELETE FROM orders;')

    # passw = input('User "berkan" password:')
    passw = b'berkan123'
    passw = hashlib.sha256(passw).hexdigest()
    
    # Add a user with all privileges
    cur.execute('CREATE USER "%s"@localhost;' % 'berkan')
    cur.execute('UPDATE mysql.user SET plugin="mysql_native_password" WHERE user="%s";' % 'berkan')
    cur.execute('UPDATE mysql.user SET authentication_string="" WHERE user="%s";' % 'berkan')
    cur.execute('GRANT ALL PRIVILEGES ON demodb.* TO "%s"@localhost;' % 'berkan')
    cur.execute('FLUSH PRIVILEGES;')
    # Remove an existing user
    cur.execute('DROP USER "%s"@localhost;' % 'berkan')
    # Print all users
    cur.execute('SELECT user, plugin, authentication_string FROM mysql.user;')
    for line in cur:
        print(line)
    
    cur.close()
    con.close()
