begin;

-- TODO MAYBE: l_*_* link tables?
-- TODO MAYBE: label, label_alias, label_gid_redirect, release_label ??

create table if not exists artist
(
    id               bigint primary key,
    gid              text    not null,
    name             text    not null,
    sort_name        text    not null,
    begin_date_year  int,
    begin_date_month int,
    begin_date_day   int,
    end_date_year    int,
    end_date_month   int,
    end_date_day     int,
    type             int,
    area             int,
    gender           int,
    comment          text,
    edits_pending    int,
    last_update      datetime,
    ended            text not null,
    begin_area       int,
    end_area         int
);

create table if not exists artist_alias
(
    id                 bigint primary key,
    artist             bigint  not null references artist (id),
    name               text    not null,
    locale             text,
    edits_pending      int,
    last_update        datetime,
    type               int,
    sort_name          text    not null,
    begin_date_year    int,
    begin_date_month   int,
    begin_date_day     int,
    end_date_year      int,
    end_date_month     int,
    end_date_day       int,
    primary_for_locale text not null,
    ended              text not null
);

create table if not exists artist_credit
(
    id            bigint primary key,
    name          text     not null,
    artist_count  int      not null,
    ref_count     int      not null,
    created       datetime not null,
    edits_pending int,
    gid           text     not null
);

create table if not exists artist_credit_name
(
    artist_credit bigint not null references artist_credit (id),
    position      int    not null,
    artist        bigint not null references artist (id),
    name          text   not null,
    join_phrase   text,

    primary key (artist_credit, position)
);

create table if not exists artist_gid_redirect
(
    gid     text primary key,
    new_id  bigint   not null references artist (id),
    created datetime not null
);

create table if not exists artist_tag
(
    artist      bigint not null references artist (id),
    tag         bigint not null references tag (id),
    count       int,
    last_update datetime,

    primary key (artist, tag)
);


create table if not exists genre
(
    id            bigint primary key,
    gid           text not null,
    name          text not null,
    comment       text,
    edits_pending int,
    last_update   datetime
);

create table if not exists genre_alias
(
    id                 bigint primary key,
    genre              bigint  not null references genre (id),
    name               text    not null,
    locale             text,
    edits_pending      int,
    last_update        datetime,
    type               int,
    sort_name          text    not null,
    begin_date_year    int,
    begin_date_month   int,
    begin_date_day     int,
    end_date_year      int,
    end_date_month     int,
    end_date_day       int,
    primary_for_locale text not null,
    ended              text not null
);

create table if not exists isrc
(
    id            bigint primary key,
    recording     bigint not null references recording (id),
    isrc          text   not null,
    source        int,
    edits_pending int,
    created       datetime
);

create table if not exists medium
(
    id            bigint primary key,
    "release"     bigint not null references "release" (id),
    position      int    not null,
    format        int,
    name          text,
    edits_pending int,
    last_update   datetime,
    track_count   int,
    gid           text   not null
);

create table if not exists recording
(
    id            bigint primary key,
    gid           text    not null,
    name          text    not null,
    artist_credit bigint  not null references artist_credit (id),
    length        int,
    comment       text,
    edits_pending int,
    last_update   datetime,
    video         text not null
);

create table if not exists recording_alias
(
    id                 bigint primary key,
    recording          bigint  not null references recording (id),
    name               text    not null,
    locale             text,
    edits_pending      int,
    last_update        datetime,
    type               int,
    sort_name          text    not null,
    begin_date_year    int,
    begin_date_month   int,
    begin_date_day     int,
    end_date_year      int,
    end_date_month     int,
    end_date_day       int,
    primary_for_locale text not null,
    ended              text not null
);

create table if not exists recording_gid_redirect
(
    gid     text primary key,
    new_id  bigint   not null references recording (id),
    created datetime not null
);

create table if not exists recording_tag
(
    recording    bigint not null references recording (id),
    tag          bigint not null references tag (id),
    count        int,
    last_updated datetime,

    primary key (recording, tag)
);

create table if not exists "release"
(
    id            bigint primary key,
    gid           text   not null,
    name          text   not null,
    artist_credit bigint not null references artist_credit (id),
    release_group bigint not null references release_group (id),
    status        int,
    packaging     int,
    language      int,
    script        int,
    barcode       text,
    comment       text,
    edits_pending int,
    quality       int,
    last_update   datetime
);

create table if not exists release_alias
(
    id                 bigint primary key,
    "release"          bigint  not null references "release" (id),
    name               text    not null,
    locale             text,
    edits_pending      int,
    last_update        datetime,
    type               int,
    sort_name          text    not null,
    begin_date_year    int,
    begin_date_month   int,
    begin_date_day     int,
    end_date_year      int,
    end_date_month     int,
    end_date_day       int,
    primary_for_locale text not null,
    ended              text not null
);

create table if not exists release_gid_redirect
(
    gid     text primary key,
    new_id  bigint   not null,
    created datetime not null
);

create table if not exists release_group
(
    id            bigint primary key,
    gid           text   not null,
    name          text   not null,
    artist_credit bigint not null references artist_credit (id),
    type          int,
    comment       text,
    edits_pending int,
    last_update   datetime
);

create table if not exists release_group_alias
(
    id                 bigint primary key,
    release_group      bigint  not null references release_group (id),
    name               text    not null,
    locale             text,
    edits_pending      int,
    last_update        datetime,
    type               int,
    sort_name          text    not null,
    begin_date_year    int,
    begin_date_month   int,
    begin_date_day     int,
    end_date_year      int,
    end_date_month     int,
    end_date_day       int,
    primary_for_locale text not null,
    ended              text not null
);

create table if not exists release_group_gid_redirect
(
    gid     text primary key,
    new_id  bigint   not null,
    created datetime not null
);

create table if not exists release_group_tag
(
    release_group bigint not null references release_group (id),
    tag           bigint not null references tag (id),
    count         int,
    last_update   datetime,

    primary key (release_group, tag)
);

create table if not exists release_tag
(
    "release"   bigint not null references "release" (id),
    tag         bigint not null references tag (id),
    count       int,
    last_update datetime,

    primary key ("release", tag)
);

create table if not exists tag
(
    id        bigint primary key,
    name      text not null,
    ref_count bigint
);

create table if not exists track
(
    id            bigint primary key,
    gid           text    not null,
    recording     bigint  not null references recording (id),
    medium        bigint  not null references medium (id),
    position      int,
    number        text,
    name          text    not null,
    artist_credit bigint  not null references artist_credit (id),
    length        int,
    edits_pending int,
    last_updated  datetime,
    is_data_track text not null
);

create table if not exists track_gid_redirect
(
    gid     text primary key,
    new_id  bigint   not null,
    created datetime not null
);
commit;