# this program creates a database with random sample data for the contact book
# table name is contacts
# columns are phone_number, first_name, last_name, email
# mock data generated by mockaroo

import sqlite3 # database
import os # removing files
import os.path # file paths
import csv # reading csv files

db_file_name = 'contact book.db'

def create_database():
    # see if database already exists
    if os.path.exists(db_file_name):
        # if it does, ask user if they want to replace it
        while True:
            choice = input('Database already exists. Erase current one and reinitialize it? (y/n)\n')
            if choice == 'y':
                choice = input('Are you sure? (y/n)\n')
                if choice == 'y':
                    break
                if choice == 'n':
                    print('Operation aborted.')
                    return
            if choice == 'n':
                print('Operation aborted.')
                return
        # remove current database
        os.remove(db_file_name)
    else:
        print('Database does not exist. ', end='')
        
    conn = sqlite3.connect(db_file_name)
    c = conn.cursor()
    
    while True:
        choice = input('Choose an option:\n'
                        '1: Create empty database\n'
                        '2: Create sample database\n')
        if choice == '1':
            create_empty_database(conn, c)
            break
        elif choice == '2':
            create_sample_database(conn, c)
            break
    
    # save database changes
    conn.commit()
    conn.close()
    
def create_empty_database(conn, c):
    c.execute('''create table contacts (
                    phone_number text primary key,
                    first_name text,
                    last_name text, 
                    email text
                )''')

def create_sample_database(conn, c):
    create_empty_database(conn, c)
    
    # load mock data file
    with open('MOCK_DATA.csv') as datafile:
        reader = csv.reader(datafile)
        # skip first line
        next(reader)
        # for each entry in file, insert values into table
        for row in reader:
            row = tuple(row)
            values = (row[2],row[0],row[1],row[3])
            c.execute('insert into contacts values (?,?,?,?)', values)

if __name__ == '__main__':
    create_database()