import psycopg2
from sql_queries import create_table_queries, drop_table_queries


def create_database():
    """
    Create  database
    """
    # connect to default database
    print("connect to default database")
    conn = psycopg2.connect("host=127.0.0.1 dbname=postgres user=allen.wang password=")
    conn.set_session(autocommit=True)
    cur = conn.cursor()
    
    # create metro database with UTF8 encoding
    print("create metro database")
    cur.execute("DROP DATABASE IF EXISTS metro")
    cur.execute("CREATE DATABASE metro WITH ENCODING 'utf8' TEMPLATE template1")

    # close connection to default database
    conn.close()    
    
    # connect to metro database
    print("connect to metro database")
    conn = psycopg2.connect("host=127.0.0.1 dbname=metro user=allen.wang password=")
    cur = conn.cursor()
    
    return cur, conn


def drop_tables(cur, conn):
    """Drop table
    Args:
        cur: The curser of database connection
        conn: The database connection
    """
    print("DROP TABLES")
    for query in drop_table_queries:
        cur.execute(query)
        conn.commit()


def create_tables(cur, conn):
    """Create table
    Args:
        cur: The curser of database connection
        conn: The database connection
    """
    print("CREATE TABLES")
    for query in create_table_queries:
        cur.execute(query)
        conn.commit()

def main():
    """
    Drop tables and than create tables
    """
    cur, conn = create_database()
    
    drop_tables(cur, conn)
    create_tables(cur, conn)
    
    conn.close()
    print("Finish create table, close connection")


if __name__ == "__main__":
    main()