begin transaction;

create table songs(
    path text not null primary key,
    artist text,
    album text,
    last_queued timestamp with time zone,
    generation bigint not null);

create index songs_artist_idx on songs(artist);
create index songs_album_idx on songs(album);

create table albums(
    artist text not null,
    album text not null,
    last_queued timestamp with time zone,
    generation bigint not null,
    primary key(album,artist));

create table artists(
    artist text not null primary key,
    last_queued timestamp with time zone,
    generation bigint not null);

create table unwanted_albums(
    artist text not null,
    album text not null,
    generation bigint not null,
    primary key(album,artist));

create table unwanted_artists(
    artist text not null primary key,
    generation bigint not null
);

create table options(
    generation bigint not null
);

insert into options(generation) values(0);

CREATE FUNCTION update_song_data(
    new_generation bigint
  , OUT total_songs bigint
  , OUT total_artists bigint
  , OUT total_albums bigint
  , OUT new_songs bigint
  , OUT new_artists bigint
  , OUT new_albums bigint)
AS
$$
DECLARE
    song_data RECORD;
BEGIN
    total_songs = 0;
    new_songs = 0;
    new_artists = 0;
    new_albums = 0;

    FOR song_data IN
        SELECT path, artist, album
        FROM tmp_songs
    LOOP
        total_songs = total_songs + 1;

        UPDATE songs
        SET generation = new_generation
        WHERE path = song_data.path;

        IF ( NOT FOUND ) THEN
            new_songs = new_songs + 1;
            INSERT INTO songs(path, artist, album, generation)
            values(song_data.path, song_data.artist,
                song_data.album, new_generation);
        END IF;
        -----------------------------
        UPDATE artists
        SET generation = new_generation
        WHERE artist = song_data.artist;

        IF ( NOT FOUND ) THEN
            new_artists = new_artists + 1;
            INSERT INTO artists(artist, generation)
            VALUES(song_data.artist, new_generation);
        END IF;
        -----------------------------
        UPDATE albums
        SET generation = new_generation
        WHERE album = song_data.album
          AND artist = song_data.artist;

        IF ( NOT FOUND ) THEN
            new_albums = new_albums + 1;
            INSERT INTO albums(album, artist, generation)
            VALUES(song_data.album, song_data.artist, new_generation);
        END IF;
    END LOOP;

    SELECT COUNT(*) INTO total_artists FROM artists WHERE generation = new_generation;
    SELECT COUNT(*) INTO total_albums  FROM albums  WHERE generation = new_generation;

    RETURN;
END;
$$ LANGUAGE plpgsql;

commit;
