import configparser


# CONFIG
config = configparser.ConfigParser()
config.read('dwh.cfg')

# DROP TABLES

staging_events_table_drop = "DROP TABLE IF EXISTS staging_events"
staging_songs_table_drop = "DROP TABLE IF EXISTS staging_artist_songs"
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

staging_events_table_create= ("""
CREATE TABLE IF NOT EXISTS staging_events(
    artist varchar(512),
    auth varchar(64),
    firstName varchar(18),
    gender varchar(1),
    itemInSession int,
    lastName varchar(25),
    length varchar(255),
    level varchar(10),
    location varchar(255),
    method  varchar(10),
    page varchar(64),
    registration bigint,
    sessionId int,
    song varchar(512),
    status int,
    ts numeric,
    userAgent varchar(255),
    userId int
)
""")

staging_songs_table_create = ("""
CREATE TABLE IF NOT EXISTS staging_artist_songs(
    artist_id char(18), 
    artist_latitude numeric,
    artist_location varchar(255),
    artist_longitude numeric,
    artist_name varchar(255),
    duration float,
    num_songs int,
    song_id char(18),
    title varchar(255),
    year int
)
""")

songplay_table_create = ("""
CREATE TABLE IF NOT EXISTS songplays(
    songplay_id int GENERATED BY DEFAULT AS IDENTITY,
    start_time timestamp NOT NULL,
    user_id int,
    level varchar(10) NOT NULL,
    song_id char(64) NOT NULL,
    artist_id char(64) NOT NULL,
    session_id int,
    location varchar(255),
    user_agent varchar NOT NULL
)
""")

user_table_create = ("""
CREATE TABLE IF NOT EXISTS users (
    user_id int,
    first_name varchar(18),
    last_name varchar(25),
    gender varchar(1) NOT NULL,
    level varchar(10) NOT NULL
)
""")

song_table_create = ("""
CREATE TABLE IF NOT EXISTS songs (
    song_id char(64),
    title varchar(512) NOT NULL,
    artist_id varchar(25) NOT NULL,
    year int NOT NULL,
    duration float NOT NULL
)
""")

artist_table_create = ("""
CREATE TABLE IF NOT EXISTS artists(
    artist_id char(64),
    name varchar(512) NOT NULL,
    latitude numeric,
    location varchar(255),
    longitude numeric
)
""")

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

# STAGING TABLES

ARN = config.get("IAM_ROLE", "ARN")
print('ARN', ARN)

staging_events_copy = ("""
    copy staging_events from 's3://udacity-dend/log_data'
    credentials 'aws_iam_role={}'
    format as json 's3://spartkify-etl-config/jpath.json' ;
""").format(ARN)

staging_songs_copy = ("""
    copy staging_artist_songs from 's3://udacity-dend/song_data'
    credentials 'aws_iam_role={}'
    format as json 'auto';
""").format(ARN)

# FINAL TABLES

songplay_table_insert = ("""
INSERT INTO songplays(start_time, user_id, level, song_id, artist_id, session_id, location, user_agent)
SELECT DISTINCT TIMESTAMP 'epoch' + ts/1000 *INTERVAL '1 second',
    userId,
    level,
    s.song_id,
    a.artist_id,
    sessionId,
    e.location,
    userAgent
FROM staging_events e
JOIN songs s ON (e.song = s.title and e.length = s.duration) 
JOIN artists a ON (e.artist = a.name)
WHERE e.page='NextSong'
""")

user_table_insert = ("""
INSERT INTO users(user_id, first_name, last_name, gender, level)
SELECT DISTINCT userId,
    firstName,
    lastName,
    gender,
    level
FROM staging_events
WHERE userId is not null
  AND page='NextSong'
""")

song_table_insert = ("""
INSERT INTO songs(song_id, title, artist_id, year, duration) 
SELECT DISTINCT song_id,
    title,
    artist_id,
    year,
    duration
FROM staging_artist_songs
""")

artist_table_insert = ("""
INSERT INTO artists(artist_id, name, latitude, location, longitude)
SELECT DISTINCT artist_id,
    artist_name,
    artist_latitude,
    artist_location,
    artist_longitude
FROM staging_artist_songs
""")

time_table_insert = ("""
INSERT INTO time (start_time, hour, day, week, month, year, weekday)
SELECT DISTINCT TIMESTAMP 'epoch' + ts/1000 *INTERVAL '1 second' AS start_time_key,
       EXTRACT(hour from TIMESTAMP 'epoch' + ts/1000 *INTERVAL '1 second')  AS hour,
       EXTRACT(day  from TIMESTAMP 'epoch' + ts/1000 *INTERVAL '1 second')  AS day,
       EXTRACT(week from TIMESTAMP 'epoch' + ts/1000 *INTERVAL '1 second')  AS week,
       EXTRACT(month from TIMESTAMP 'epoch' + ts/1000 *INTERVAL '1 second') AS month,
       EXTRACT(year from TIMESTAMP 'epoch' + ts/1000 *INTERVAL '1 second')  AS year,
       EXTRACT(dow from TIMESTAMP 'epoch' + ts/1000 *INTERVAL '1 second')   AS weekday
FROM staging_events
WHERE page='NextSong'
""")

# QUERY LISTS

create_table_queries = [staging_events_table_create, staging_songs_table_create, user_table_create, song_table_create, artist_table_create, time_table_create, songplay_table_create]
drop_table_queries = [staging_events_table_drop, staging_songs_table_drop, songplay_table_drop, user_table_drop, song_table_drop, artist_table_drop, time_table_drop]
copy_table_queries = [staging_events_copy, staging_songs_copy]
insert_table_queries = [songplay_table_insert, user_table_insert, song_table_insert, artist_table_insert, time_table_insert]

