BEGIN TRANSACTION;

CREATE TABLE account (
   account TEXT NOT NULL,
   fan_id INTEGER NOT NULL,
   currency TEXT,
   PRIMARY KEY (account)
) WITHOUT ROWID;

CREATE TABLE item (
   account TEXT NOT NULL,
   item_id INTEGER NOT NULL,
   item_type TEXT NOT NULL,
   band_id INTEGER NOT NULL,
   added INTEGER NOT NULL,
   updated INTEGER NOT NULL,
   purchased INTEGER NOT NULL,
   featured_track INTEGER,
   why TEXT,
   also_collected_count INTEGER NOT NULL,
   item_title TEXT NOT NULL,
   item_url TEXT NOT NULL,
   item_art_url TEXT NOT NULL,
   band_name TEXT NOT NULL,
   band_url TEXT NOT NULL,
   featured_track_title TEXT,
   featured_track_number INTEGER,
   featured_track_duration NUMERIC,
   album_id INTEGER,
   album_title TEXT,
   price INTEGER NOT NULL,
   currency STRING NOT NULL,
   label TEXT,
   label_id INTEGER,
   PRIMARY KEY (account, item_id)
) WITHOUT ROWID;

CREATE TABLE track (
   account TEXT NOT NULL,
   item_id INTEGER NOT NULL,
   track_id INTEGER NOT NULL,
   title TEXT NOT NULL,
   artist TEXT NOT NULL,
   track_number INTEGER,
   duration NUMERIC NOT NULL,
   mp3 TEXT,
   PRIMARY KEY (account, item_id, track_id),
   FOREIGN KEY (account, item_id) REFERENCES item (account, item_id) ON DELETE CASCADE
) WITHOUT ROWID;

CREATE TABLE item_tag (
   account TEXT NOT NULL,
   item_id INTEGER NOT NULL,
   tag TEXT NOT NULL,
   PRIMARY KEY (account, item_id, tag),
   FOREIGN KEY (account, item_id) REFERENCES item (account, item_id) ON DELETE CASCADE
) WITHOUT ROWID;

COMMIT;