import sqlite3, os, time, pandas as pd
from sqlite3 import Error

def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        print(sqlite3.version)
    except Error as e:
        print(e)
    return conn

def create_table(conn,create_table_sql):
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return:
    """
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)
        
def main():
    database = r'trial.db'
    sql_create_projects_table = """ CREATE TABLE IF NOT EXISTS projects (
                                        id integer PRIMARY KEY,
                                        name text NOT NULL,
                                        begin_date text,
                                        end_date text
                                    ); """
    
    sql_create_tasks_table = """CREATE TABLE IF NOT EXISTS tasks (
                                    id integer PRIMARY KEY,
                                    name text NOT NULL,
                                    priority integer,
                                    status_id integer NOT NULL,
                                    project_id integer NOT NULL,
                                    begin_date text NOT NULL,
                                    end_date text NOT NULL,
                                    FOREIGN KEY (project_id) REFERENCES projects (id)
                                );"""
    
    # create a database connection
    conn = create_connection(database)
    
    # create tables
    if conn is not None:
        # create projects table
        create_table(conn, sql_create_projects_table)
        
        # create tasks table
        create_table(conn, sql_create_tasks_table)
    else:
        print("Error! cannot create the database connection.")
        
def list_tables(db):
    """List all the tables saved in database (db).
    """
    conn = create_connection(db)
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = [table[0] for table in cursor.fetchall()]
    conn.close()
    return tables

def pd_to_sql(tableStr,df,db,if_exists='fail'):
    """Read pandas dataframe to sqlite database. 
    Inputs: 
        tableStr (str) = desired name of table in the database
        db (str) = path of db file
        df = pandas dataframe. Default is to use the table name so the dataframe and sql table have the same name.
    """

    tables = list_tables(db)
    # create a database connection
    conn = create_connection(db)
    if tableStr not in tables | if_exists != 'fail':

        if conn is not None:
            # convert pandas dataframe to sql
            df.to_sql(tableStr,conn,index=False,if_exists=if_exists)
            print(f"{tableStr} saved to {db}")
        else:
            print("Error: No database connection.")
    else:
        print(f"Error: {tableStr} is already in {db}")
    conn.close()
    
def drop_table(table, db):
    #Connecting to sqlite
    conn = create_connection(db)

    #Creating a cursor object using the cursor() method
    cursor = conn.cursor()

    #Doping EMPLOYEE table if already exists
    cursor.execute(f"DROP TABLE {table}")
    print(f"{table} dropped from {db} ")

    #Commit your changes in the database
    conn.commit()

    #Closing the connection
    conn.close()

