schema.sql

create table
  public.platforms (
    id bigint generated by default as identity,
    platform_name text not null,
    platform_url text not null,
    constraint platforms_pkey primary key (id)
  ) tablespace pg_default;

create table
  public.songs (
    id bigint generated by default as identity,
    created_at timestamp with time zone not null default now(),
    song_name text not null,
    artist text not null,
    album text not null,
    authorization_key text not null default substr(md5((random())::text), 0, 25),
    constraint songs_pkey primary key (id)
  ) tablespace pg_default;

create table
  public.playlists (
    id bigint generated by default as identity,
    name text null,
    constraint playlists_pkey primary key (id)
  ) tablespace pg_default;

create table
  public.users (
    id bigint generated by default as identity,
    created_at timestamp with time zone not null default now(),
    password text not null default 'password'::text,
    platform_id bigint not null default '1'::bigint,
    salt text not null default ''::text,
    constraint users_pkey primary key (id),
    constraint users_platform_id_fkey foreign key (platform_id) references platforms (id)
  ) tablespace pg_default;

create table
  public.links (
    id bigint generated by default as identity,
    created_at timestamp with time zone not null default now(),
    song_id bigint not null,
    platform_id bigint not null,
    song_url text not null,
    constraint links_pkey primary key (id),
    constraint links_platform_id_fkey foreign key (platform_id) references platforms (id),
    constraint links_song_id_fkey foreign key (song_id) references songs (id) on delete cascade
  ) tablespace pg_default;

create table
  public.playlist_songs (
    playlist_id bigint not null,
    song_id bigint not null,
    id bigint generated by default as identity,
    constraint playlist_songs_pkey primary key (id),
    constraint playlist_songs_playlist_id_fkey foreign key (playlist_id) references playlists (id),
    constraint playlist_songs_song_id_fkey foreign key (song_id) references songs (id) on delete cascade
  ) tablespace pg_default;
  
create table
  public.ad_campaigns (
    id bigint generated by default as identity,
    created_at timestamp with time zone not null default now(),
    link text not null,
    target_mood text not null,
    constraint ad_campaign_pkey primary key (id)
  ) tablespace pg_default;

create table
  public.song_history (
    id bigint generated by default as identity,
    created_at timestamp with time zone not null default now(),
    user_id bigint not null,
    song_id bigint not null,
    constraint song_history_pkey primary key (id),
    constraint song_history_song_id_fkey foreign key (song_id) references songs (id) on update cascade on delete cascade,
    constraint song_history_user_id_fkey foreign key (user_id) references users (id) on update cascade on delete cascade
  ) tablespace pg_default;
 

create table
  public.users_playlist_position (
    id bigint generated by default as identity,
    user_id bigint not null,
    playlist_song_position bigint null,
    playlist_id bigint null,
    constraint users_playlist_position_pkey primary key (id),
    constraint users_playlist_position_playlist_id_fkey foreign key (playlist_id) references playlists (id),
    constraint users_playlist_position_playlist_song_position_fkey foreign key (playlist_song_position) references playlist_songs (id) on update restrict on delete set null,
    constraint users_playlist_position_user_id_fkey foreign key (user_id) references users (id) on delete cascade
  ) tablespace pg_default;

create table
  public.user_moods (
    user_id bigint generated by default as identity,
    last_updated timestamp with time zone not null default now(),
    mood text not null,
    songs_played bigint not null,
    constraint user_moods_pkey primary key (user_id),
    constraint user_moods_user_id_key unique (user_id),
    constraint user_moods_user_id_fkey foreign key (user_id) references users (id) on update cascade on delete cascade
  ) tablespace pg_default;

  INSERT INTO songs (song_name, artist, album)
  VALUES ('Mr. Brightside', 'The Killers', 'Hot Fuss');

  
  INSERT INTO songs (song_name, artist, album, id)
  VALUES ('The Funeral', 'Band of Horses', 'Single', 294);
  
  INSERT INTO songs (song_name, artist, album, id)
  VALUES ('Sad Lofi Songs 2023', 'Pain', 'Single', 983);

  INSERT INTO platforms (id, platform_name, platform_url)
  VALUES (1, 'Spotify', '%open.spotify.com%');
  
  INSERT INTO platforms (id, platform_name, platform_url)
  VALUES (2, 'Apple', '%music.apple.com%');

  INSERT INTO links (song_id, platform_id, song_url)
  VALUES (1, 1, 'https://open.spotify.com/track/003vvx7Niy0yvhvHt4a68B?si=095e444ca83840c7');

  INSERT INTO links (song_id, platform_id, song_url)
  VALUES (294, 1, 'https://open.spotify.com/track/5lRzWDEe7UuedU2QPsFg0K');

  INSERT INTO links (song_id, platform_id, song_url)
  VALUES (983, 1, 'https://open.spotify.com/playlist/6S0eWfvBCCdxLgkKd9VHGO7');

  INSERT INTO users (id, platform_id)
  VALUES (1,1);

  INSERT INTO playlists (name)
  VALUES ('My Amazing Playlist');

  INSERT INTO playlist_songs (playlist_id, song_id)
  VALUES (1, 1);