"""
sql_init_tables.py
usage: python3 sql_init_tables.py

Developed by Prof. Xin Yuan at Florida State University for COP4521
to practice SQL. Setting up some initial tables in the database.
"""

import sqlite3
conn = sqlite3.connect('./sql_demo.db')
cur = conn.cursor()

cur.execute('''DROP TABLE IF EXISTS beers;''')
cur.execute('''DROP TABLE IF EXISTS sells;''')
cur.execute('''DROP TABLE IF EXISTS bars;''')
cur.execute('''DROP TABLE IF EXISTS frequents;''')
cur.execute('''DROP TABLE IF EXISTS likes;''')
cur.execute('''DROP TABLE IF EXISTS employees;''')
cur.execute('''DROP TABLE IF EXISTS students;''')


conn.commit()

# create table in database

cur.execute('''CREATE TABLE IF NOT EXISTS beers(
  name VARCHAR(20) PRIMARY KEY NOT NULL,
  manf VARCHAR(40));
  ''')

cur.execute('''CREATE TABLE IF NOT EXISTS Sells(
   bar VARCHAR(20),
   beer VARCHAR(20),
   price REAL,
   PRIMARY KEY (bar, beer));
   ''')

cur.execute('''CREATE TABLE if NOT EXISTS frequents(
   drinker VARCHAR(20), 
   bar VARCHAR(20),
   PRIMARY KEY (drinker, bar));
  ''')

cur.execute('''CREATE TABLE if NOT EXISTS likes(
   drinker VARCHAR(20), 
   beer VARCHAR(20),
   PRIMARY KEY (drinker, beer));
  ''')

# execute one command
cur.execute('''Insert Into beers (name, manf)
            Values ('Bud', 'Busch');''')
cur.execute('''Insert Into beers (name, manf)
            Values ('Miller Lite', 'MillerCoors');''')
cur.execute('''Insert Into beers (name, manf)
            Values ('Bud Lite', 'Busch');''')
cur.execute('''Insert Into beers (name, manf)
            Values ('Blue Moon', 'Molson Coors');''')
cur.execute('''Insert Into beers (name, manf)
            Values ('Corona Extra', 'Constellation Brands');''')
cur.execute('''Insert Into beers (name, manf)
            Values ('Michelob', 'Busch');''')

cur.execute('''Insert Into Sells (bar, beer)
            Values ('Joe''s', 'Bud Lite');''')
cur.execute('''Insert Into Sells (bar, beer, price)
            Values ('Joe''s', 'Bud', 2.00);''')
cur.execute('''Insert Into Sells (bar, beer, price)
            Values ('Joe''s', 'Blue Moon', 10.00);''')
cur.execute('''Insert Into Sells (bar, beer, price)
            Values ('Joe''s', 'Michelob', 3.00);''')
cur.execute('''Insert Into Sells (bar, beer, price)
            Values ('Jane''s', 'Bud', 3.00);''')
cur.execute('''Insert Into Sells (bar, beer, price)
            Values ('Jane''s', 'Bud Lite', 3.30);''')
cur.execute('''Insert Into Sells (bar, beer, price)
            Values ('Jane''s', 'Blue Moon', 8.00);''')
cur.execute('''Insert Into Sells (bar, beer, price)
            Values ('Jane''s', 'Miller Lite', 1.00);''')
cur.execute('''Insert Into Sells (bar, beer, price)
            Values ('Jane''s', 'Michelob', 3.30);''')
cur.execute('''Insert Into Sells (bar, beer, price)
            Values ('Big Mama''s', 'Miller', 2.00);''')
cur.execute('''Insert Into Sells (bar, beer, price)
            Values ('Big Daddy''s', 'Bud Lite', 3.40);''')
cur.execute('''Insert Into Sells (bar, beer, price)
            Values ('Big Mama''s', 'Bud Lite', 5.00);''')

cur.execute('''Insert Into frequents (drinker, bar)
            Values ('Sally', 'Joe''s');''')
cur.execute('''Insert Into frequents (drinker, bar)
            Values ('Sally', 'BigMama''s');''')
cur.execute('''Insert Into frequents (drinker, bar)
            Values ('Ashley', 'BigMama''s');''')
cur.execute('''Insert Into frequents (drinker, bar)
            Values ('Adam', 'BigMama''s');''')
cur.execute('''Insert Into frequents (drinker, bar)
            Values ('Robert', 'Jane''s');''')
cur.execute('''Insert Into frequents (drinker, bar)
            Values ('Robert', 'Joe''s');''')
cur.execute('''Insert Into frequents (drinker, bar)
            Values ('Kate', 'Jane''s');''')

cur.execute('''Insert Into likes (drinker, beer)
            Values ('Sally', 'Bud');''')

cur.execute('''Insert Into likes (drinker, beer)
            Values ('Sally', 'Miller Lite');''')
cur.execute('''Insert Into likes (drinker, beer)
            Values ('Robert', 'Blue Moon');''')
cur.execute('''Insert Into likes (drinker, beer)
            Values ('Adam', 'Corona Extra');''')





# commit and save changes to database
conn.commit()

print('Beers:')
for rows in cur.execute('''SELECT * 
                           FROM beers;'''):
    print(rows)

print('\nBars:')
for rows in cur.execute('''SELECT * 
                           FROM Sells;'''):
    print(rows)

print('\nFrequents:')
for rows in cur.execute('''SELECT * 
                           FROM frequents;'''):
    print(rows)

print('\nLikes:')
for rows in cur.execute('''SELECT * 
                           FROM likes;'''):
    print(rows)


conn.close()