<gh_stars>0
# DROP TABLES

songplay_table_drop = "DROP TABLE IF EXISTS  songplays;"
user_table_drop = "DROP TABLE IF EXISTS  users;"
song_table_drop = "DROP TABLE IF EXISTS  songs;"
artist_table_drop = "DROP TABLE IF EXISTS  artists;"
time_table_drop = "DROP TABLE IF EXISTS  time;"

# CREATE TABLES


user_table_create = ("""CREATE TABLE IF NOT EXISTS users (
    user_id varchar PRIMARY KEY,
    first_name varchar NOT NULL,
    last_name varchar NOT NULL,
    gender varchar NOT NULL,
    level varchar NOT NULL
    );
""")

song_table_create = ("""CREATE TABLE IF NOT EXISTS songs (
    song_id varchar PRIMARY KEY,
    title varchar NOT NULL,
    artist_id varchar NOT NULL,
    year int NOT NULL,
    duration double precision NOT NULL,
    FOREIGN KEY (artist_id) REFERENCES artists (artist_id)
    );
""")

artist_table_create = ("""CREATE TABLE IF NOT EXISTS artists (
    name varchar NOT NULL,
    artist_id varchar PRIMARY KEY,
    location varchar NOT NULL,
    latitude double precision NOT NULL,
    longitude double precision NOT NULL
    );
""")

time_table_create = ("""CREATE TABLE IF NOT EXISTS time (
    start_time timestamp PRIMARY KEY,
    hour int NOT NULL,
    day int NOT NULL,
    week int NOT NULL,
    month int NOT NULL,
    year int NOT NULL,
    weekday int NOT NULL
    );
""")

songplay_table_create = ("""CREATE TABLE IF NOT EXISTS songplays (
    songplay_id serial PRIMARY KEY,
    start_time timestamp NOT NULL,
    user_id varchar NOT NULL,
    level varchar NOT NULL,
    song_id varchar,
    artist_id varchar,
    session_id varchar NOT NULL,
    user_agent varchar NOT NULL,
    FOREIGN KEY (song_id) REFERENCES songs (song_id),
    FOREIGN KEY (artist_id) REFERENCES artists (artist_id),
    FOREIGN KEY (start_time) REFERENCES time (start_time),
    FOREIGN KEY (user_id) REFERENCES users (user_id)
    );
""")

# INSERT RECORDS

songplay_table_copy = ("""COPY songplays (
    start_time,
    user_id,
    level,
    song_id,
    artist_id,
    session_id,
    user_agent)
FROM STDIN WITH CSV HEADER  NULL AS 'null';
""")
songplay_table_insert = ("""INSERT INTO songplays (
    start_time,
    user_id,
    level,
    song_id,
    artist_id,
    session_id,
    user_agent
    )
 VALUES ( %s, %s, %s ,%s, %s, %s, %s);
""")

user_table_insert = ("""INSERT INTO users (
    user_id,
    first_name,
    last_name,
    gender,
    level
    )
VALUES (%s, %s, %s, %s, %s) ON CONFLICT (user_id) 
    DO UPDATE SET first_name = EXCLUDED.first_name,
                    last_name = EXCLUDED.last_name,
                    gender = EXCLUDED.gender,
                    level = EXCLUDED.level;
""")

song_table_insert = ("""INSERT INTO songs (
    song_id,
    title,
    artist_id,
    year,
    duration
    )
VALUES (%s, %s, %s, %s, %s) ON CONFLICT (song_id) DO NOTHING;
""")

artist_table_insert = ("""INSERT INTO artists (
    artist_id,
    name,
    location,
    latitude,
    longitude
    )
VALUES (%s, %s, %s, %s, %s)  ON CONFLICT (artist_id) DO NOTHING;
""")


time_table_insert = ("""INSERT INTO time (
    start_time,
    hour,
    day,
    week,
    month,
    year,
    weekday)
VALUES (%s, %s, %s, %s, %s, %s, %s) ON CONFLICT (start_time) DO NOTHING;
""")

# FIND SONGS

song_select = ("""SELECT songs.song_id, artists.artist_id
FROM artists
JOIN songs ON artists.artist_id = songs.artist_id
WHERE songs.title = %s AND artists.name = %s AND songs.duration = %s
""")

# QUERY LISTS

create_table_queries = [user_table_create, artist_table_create,
                        song_table_create, time_table_create,
                        songplay_table_create]
drop_table_queries = [songplay_table_drop, user_table_drop,
                      song_table_drop, artist_table_drop, time_table_drop]
