BEGIN EXCLUSIVE;

-- *******************************************************************************
-- *******************************************************************************
-- @table iap2_last_revision
--
-- The iap2_metadata contains metadata specific to ipod tracks.
--
-- @field id               The ID to replace with the last revision.
-- @field rev              The last successful revision.
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE iap2_last_revision (
      id               INTEGER PRIMARY KEY DEFAULT 1,
      rev              INTEGER NOT NULL
);

-- *******************************************************************************
-- *******************************************************************************
-- @table iap2_metadata
--
-- The iap2_metadata contains metadata specific to ipod tracks.
--
-- @field fid              The file ID (fid) for the media track.
-- @field uid              iPod track unique ID (cast to a uint64_t after reading
--                         this value from the database).
-- @field type             iPod track type
-- @field rating           iPod rating
-- @field ondevice         If track is resident on device
-- @field chp_count        Chapter count
-- @field composer         Index to the iap2_composers table
-- @field albumartist      Index to the iap2_albumartists table
-- @field album_uid        Index to the iap2_album_uids table
-- @field artist_uid       Index to the iap2_artist_uids table
-- @field genre_uid        Index to the iap2_genre_uids table
-- @field composer_uid     Index to the iap2_composer_uids table
-- @field albumartist_uid  Index to the iap2_albumartist_uids table
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE iap2_metadata (
      fid              INTEGER PRIMARY KEY NOT NULL REFERENCES files,
      uid              TEXT DEFAULT NULL,
      type             TEXT DEFAULT NULL,
      rating           TEXT DEFAULT NULL,
      ondevice         TEXT DEFAULT NULL,
      chp_count        INTEGER DEFAULT 0 NOT NULL,
      composer         INTEGER DEFAULT 1 NOT NULL REFERENCES iap2_composers,
      albumartist      INTEGER DEFAULT 1 NOT NULL REFERENCES iap2_albumartists,
      album_uid        INTEGER DEFAULT 1 NOT NULL REFERENCES iap2_album_uids,
      artist_uid       INTEGER DEFAULT 1 NOT NULL REFERENCES iap2_artist_uids,
      genre_uid        INTEGER DEFAULT 1 NOT NULL REFERENCES iap2_genre_uids,
      composer_uid     INTEGER DEFAULT 1 NOT NULL REFERENCES iap2_composer_uids,
      albumartist_uid  INTEGER DEFAULT 1 NOT NULL REFERENCES iap2_albumartist_uids
);


-- *******************************************************************************
-- *******************************************************************************
--
-- iPod metadata support table
--
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE iap2_composers (
      composer_id      INTEGER PRIMARY KEY AUTOINCREMENT,
      composer         TEXT
);
CREATE INDEX iap2_composers_index_composer ON iap2_composers(composer);
-- Unknown is always 1
INSERT INTO iap2_composers(composer_id, composer)
   VALUES(1, NULL);

CREATE TABLE iap2_albumartists (
      albumartist_id   INTEGER PRIMARY KEY AUTOINCREMENT,
      albumartist      TEXT
);
CREATE INDEX iap2_albumartists_index_albumartist ON iap2_albumartists(albumartist);
-- Unknown is always 1
INSERT INTO iap2_albumartists(albumartist_id, albumartist)
   VALUES(1, NULL);

CREATE TABLE iap2_album_uids (
      id               INTEGER PRIMARY KEY AUTOINCREMENT,
      album_uid        TEXT
);
CREATE INDEX iap2_album_uids_index_album_uid ON iap2_album_uids(album_uid);
-- Unknown is always 1
INSERT INTO iap2_album_uids(id, album_uid)
   VALUES(1, NULL);

CREATE TABLE iap2_artist_uids (
      id               INTEGER PRIMARY KEY AUTOINCREMENT,
      artist_uid       TEXT
);
CREATE INDEX iap2_artist_uids_index_artist_uid ON iap2_artist_uids(artist_uid);
-- Unknown is always 1
INSERT INTO iap2_artist_uids(id, artist_uid)
   VALUES(1, NULL);

CREATE TABLE iap2_genre_uids (
      id               INTEGER PRIMARY KEY AUTOINCREMENT,
      genre_uid        TEXT
);
CREATE INDEX iap2_genre_uids_index_genre_uid ON iap2_genre_uids(genre_uid);
-- Unknown is always 1
INSERT INTO iap2_genre_uids(id, genre_uid)
   VALUES(1, NULL);

CREATE TABLE iap2_composer_uids (
      id               INTEGER PRIMARY KEY AUTOINCREMENT,
      composer_uid     TEXT
);
CREATE INDEX iap2_composer_uids_index_composer_uid ON iap2_composer_uids(composer_uid);
-- Unknown is always 1
INSERT INTO iap2_composer_uids(id, composer_uid)
   VALUES(1, NULL);

CREATE TABLE iap2_albumartist_uids (
      id               INTEGER PRIMARY KEY AUTOINCREMENT,
      albumartist_uid  TEXT
);
CREATE INDEX iap2_albumartist_uids_index_albumartist_uid ON iap2_albumartist_uids(albumartist_uid);
-- Unknown is always 1
INSERT INTO iap2_albumartist_uids(id, albumartist_uid)
   VALUES(1, NULL);

-- *****************
-- Required TRIGGERS
-- *****************
--
---- Metadata custom triggers --
CREATE TRIGGER iap2_metadata_delete AFTER DELETE on files
   BEGIN
      DELETE FROM iap2_metadata WHERE fid=OLD.fid;
   END;

-- **********************
-- Optional Tables (IAP2)
-- **********************
--
-- *******************************************************************************
-- *******************************************************************************
-- @table iap2_revision_history
--
-- This table tracks the complete revision history of the iPod.  It is used more
-- for debugging although technically it can be used to continue a sync from any
-- point on the iPod although this may leave the database in an incosistent state.
--
-- @field rev              The revision ID from the iPod.
-- @field uid              iPod UID of the last known entry before the revision.
-- @field type             The type associated with the stored UID.
-- *******************************************************************************
-- *******************************************************************************
--CREATE TABLE iap2_revision_history (
--      rev              INTEGER NOT NULL,
--      uid              TEXT NOT NULL,
--      type             INTEGER,
--      primary key(rev, uid, type)
--);

-- ***********************
-- Optional Indexes (IAP2)
-- ***********************
--
-- Indexing the files.filename field will speed up deletions and the playlist entry to filename mapping
-- done during the playlist sync at the expense of DB size (IAP2).
-- CREATE INDEX files_index_filename ON files(filename);
--
-- Indexing the playlist_entries.unresolved_entry_text field will drastically speed up playlist entry to
-- filename mapping done during the playlist sync at the expense of DB size (IAP2).
-- CREATE INDEX playlist_entries_index_unresolved_entry_text ON playlist_entries(unresolved_entry_text);

COMMIT;