BEGIN;
--
-- This file is owned by the integrator.  It can be customized in any way, and written or read in any way.
-- The MME itself does not use anything in the file.  It is maintained purely by having the triggers create
-- and delete rows as the master tables have theirs inserted and deleted.
--
-- As there is no dependency from the MME on this file and it's contents, triggers can be created to handle
-- updates also.
--

-- *******************************************************************************
-- *******************************************************************************
-- The QDB information for this database
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE _qdb_info_custom_ (
      version INTEGER NOT NULL
);

INSERT INTO _qdb_info_custom_(version) VALUES(3006);

-- *******************************************************************************
-- *******************************************************************************
--                       @table folders_custom
-- Holds a display name for each folder.
-- *******************************************************************************
-- *******************************************************************************

CREATE TABLE fbmcontent_custom(
      fbmuserid      INTEGER PRIMARY KEY AUTOINCREMENT,      
      fbm            INTEGER,
      userid         INTEGER,
      fid            INTEGER,
      folderid       INTEGER,
      idtype         INTEGER,
      objectid       BLOB,
      textcontent    TEXT,
      unique         (fbm, userid) 
);

INSERT INTO fbmcontent_custom(fbm, userid) VALUES(0, 0);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(1, 0);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(2, 0);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(3, 0);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(4, 0);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(5, 0);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(6, 0);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(7, 0);

INSERT INTO fbmcontent_custom(fbm, userid) VALUES(0, 1);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(1, 1);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(2, 1);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(3, 1);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(4, 1);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(5, 1);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(6, 1);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(7, 1);

INSERT INTO fbmcontent_custom(fbm, userid) VALUES(0, 2);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(1, 2);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(2, 2);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(3, 2);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(4, 2);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(5, 2);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(6, 2);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(7, 2);

INSERT INTO fbmcontent_custom(fbm, userid) VALUES(0, 3);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(1, 3);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(2, 3);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(3, 3);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(4, 3);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(5, 3);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(6, 3);
INSERT INTO fbmcontent_custom(fbm, userid) VALUES(7, 3);

CREATE TABLE actplaylist_oe (
      genitemid   integer DEFAULT 0 NOT NULL,
      instid      integer DEFAULT 0,
      sessionid   integer DEFAULT 0
);

CREATE TABLE favourites_custom (
      fid            integer REFERENCES library NOT NULL, 
      msid           integer REFERENCES mediastores NOT NULL,
      userid         integer DEFAULT 0, 
      favourite      integer DEFAULT 0,
      unique         (fid, msid, userid, favourite)
);

CREATE TABLE bookmarks_custom (
      bookmarkid     INTEGER PRIMARY KEY AUTOINCREMENT, 
      msid           integer REFERENCES mediastores, 
      Userid         integer, 
      fid            integer REFERENCES library, 
      time           integer,
      type           integer,
      unique         (msid, Userid,fid) 
);

DROP TABLE IF EXISTS folders_custom;

   CREATE TABLE folders_custom(
      folderid	        INTEGER PRIMARY KEY,
      basepath         TEXT DEFAULT '',
      displayname      TEXT,
      date_added       INTEGER DEFAULT 0 NOT NULL,
      source           INTEGER DEFAULT 0 NOT NULL,
      toc              TEXT,    
      contenttype      INTEGER DEFAULT 0 NOT NULL,        
      toplevelfolderid INTEGER DEFAULT 0 NOT NULL        
);

-- *******************************************************************************
-- * OE Accounts
--   remarks: account is unique by providerid+loginname
--            creates mapping between internal profileid and unique account identifier
-- *******************************************************************************
CREATE TABLE profiles(
      profileid       INTEGER PRIMARY KEY AUTOINCREMENT,
      loginname       TEXT DEFAULT NULL,
      providerid      INTEGER REFERENCES providers,      
      unique (loginname,providerid)
);

-- *******************************************************************************
-- * OE account in-dependent channel info
--   remarks: channel is globally unique by channelid + providerid
--            account dependent channel info (user_channels custom table) is currently not needed
-- *******************************************************************************
CREATE TABLE channels(
      genchannelid           INTEGER PRIMARY KEY AUTOINCREMENT,  --generated channelid
      channelid              TEXT DEFAULT NULL,
      providerid             INTEGER DEFAULT 0 NOT NULL,  --is required since channel is globally unique by itemid + providerid
      type                   INTEGER DEFAULT 0 NOT NULL,  
      coverlargeURI          TEXT,     --download URL/local path
      covermediumURI         TEXT,     --download URL/local path
      coversmallURI          TEXT,     --download URL/local path   
      coverlargesize         INTEGER DEFAULT 0 NOT NULL,
      covermediumsize        INTEGER DEFAULT 0 NOT NULL,
      coversmallsize         INTEGER DEFAULT 0 NOT NULL,
      name                   TEXT DEFAULT NULL,
      artistname             TEXT DEFAULT NULL,
      albumname              TEXT DEFAULT NULL,
      amountforplayback      INTEGER DEFAULT 0 NOT NULL,
      version                INTEGER DEFAULT 0 NOT NULL,
      unique (channelid,providerid)    
);

-- *******************************************************************************
-- * OE account dependent channel info: defines which channels a user has
-- *******************************************************************************
CREATE TABLE user_channels(
      profileid              INTEGER DEFAULT 0 NOT NULL,
      genchannelid           INTEGER DEFAULT 0 NOT NULL,
      visibility             INTEGER DEFAULT 0 NOT NULL,
      unique (profileid,genchannelid)	  
);	
	  
-- *******************************************************************************
-- * OE account in-dependent channel tracklists
-- (the channel window will be handled by the App, therefore it can be shared between all users)
-- *******************************************************************************
CREATE TABLE items(
      genitemid              INTEGER PRIMARY KEY AUTOINCREMENT,
      itemid                 TEXT DEFAULT NULL,
      providerid             INTEGER DEFAULT 0 NOT NULL,                            --download, delete
      status                 INTEGER DEFAULT 0 NOT NULL,                            --maps to DMMNBTAuxiliaryInputTypes::eItemStatus subset (eItemStatus_Available or eItemStatus_Undefined)
      filename               TEXT DEFAULT NULL,
	    size                   INTEGER DEFAULT 0 NOT NULL,
      gencollectionid        INTEGER DEFAULT 0 NOT NULL, 
      unique (itemid,providerid)
);

-- *******************************************************************************
-- * OE account in-dependent channels' items references
-- *******************************************************************************
CREATE TABLE channel_items(
      genchannelid           INTEGER DEFAULT 0 NOT NULL,
      genitemid              INTEGER DEFAULT 0 NOT NULL,
      unique (genchannelid, genitemid)
);

-- *******************************************************************************
-- * OE browser app cache
-- *******************************************************************************
CREATE TABLE oe_appcache(
      genkeyid               INTEGER PRIMARY KEY AUTOINCREMENT,
      key                    TEXT NOT NULL,
      size                   INTEGER DEFAULT 0 NOT NULL,
      unique (key)	
);

-- *******************************************************************************
-- * OE cover cache
-- *******************************************************************************
CREATE TABLE oe_covercache(
      coverid                INTEGER PRIMARY KEY AUTOINCREMENT,
      file                   TEXT NOT NULL,
      size                   INTEGER DEFAULT 0 NOT NULL,
      unique (file)	
);

-- *******************************************************************************
-- * OE CollectionID table
-- *******************************************************************************
CREATE TABLE oe_collectionids(
      gencollectionid        INTEGER PRIMARY KEY AUTOINCREMENT,
      collectionid           TEXT NOT NULL,
      providerid             INTEGER DEFAULT 0 NOT NULL,
      coverlargeid           INTEGER DEFAULT 0 NOT NULL,
      covermediumid          INTEGER DEFAULT 0 NOT NULL,
      coversmallid           INTEGER DEFAULT 0 NOT NULL,
      unique (collectionid, providerid)	
);

CREATE TABLE playlists_custom(
      plid                   INTEGER PRIMARY KEY AUTOINCREMENT,
      name                   TEXT NOT NULL,
      type                   INTEGER DEFAULT 0 NOT NULL,
      date_added             INTEGER DEFAULT 0 NOT NULL,
      profileid              INTEGER REFERENCES profiles,
      date_modified          INTEGER DEFAULT 0 NOT NULL,
      msid                   INTEGER DEFAULT 0 NOT NULL REFERENCES mediastores
);

CREATE TRIGGER playlists_custom_insert AFTER INSERT ON playlists_custom
    BEGIN
        UPDATE playlists_custom SET date_modified=strftime('%s','now') WHERE plid=NEW.plid;
    END;

CREATE TABLE playlistdata_custom(
      plid                   INTEGER NOT NULL REFERENCES playlists,
      fid                    INTEGER NOT NULL REFERENCES library,
      msid                   INTEGER DEFAULT 0 NOT NULL REFERENCES mediastores
);

CREATE TABLE software_info (
      version                TEXT
);

INSERT INTO software_info(version) VALUES('not known');

-- *******************************************************************************
-- *******************************************************************************
--                         @table categorysyncs_custom
-- This table stores category sync options for external devices like iPod.
--
-- @field syncId Unique identifier for a category sync job (clock, primary key)
-- @field msid Mediastore id of the category item
-- @field category Category string (custom types start with the unique prefix 'CUSTOM_')
-- @field basepath Explore path of the given category on the external device, e.g. Music/Artists/
--
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE categorysyncs_custom (
      jobId         INTEGER PRIMARY KEY AUTOINCREMENT,
      syncId        INTEGER NOT NULL,
      msid          INTEGER NOT NULL,
      category      TEXT DEFAULT '',
      basepath      TEXT DEFAULT ''
);

-- *******************************************************************************
-- *******************************************************************************
--                         @table categorydata_custom
-- This table stores data from category syncs of external
-- devices like iPod.
--
-- @field uniqueId Unique identifier of a category sync data entry (clock, primary key)
-- @field syncId Unique identifier for a category sync job (foreign key)
-- @field msid Mediastore id of the category item
-- @field category Category string (custom types start with the unique prefix 'CUSTOM_')
-- @field folderIdx Index of the category item within its category folder
-- @field name Textual i.e. human readable representation of the category item
--
-- *******************************************************************************
-- *******************************************************************************
CREATE TABLE categorydata_custom (
      uniqueId      INTEGER PRIMARY KEY AUTOINCREMENT,
      jobId         INTEGER REFERENCES categorysyncs_custom,
      folderIdx     INTEGER NOT NULL,
      name          TEXT DEFAULT '',
      name_normal   TEXT DEFAULT NULL,
      msid          INTEGER DEFAULT 0 NOT NULL,
      name_asia     TEXT DEFAULT NULL

);

CREATE TABLE metadataselection(
      tocid                INTEGER PRIMARY KEY AUTOINCREMENT,
      toc                  TEXT,
      folderid             INTEGER,
      folderMetaDataID     TEXT,
      folderOrigin         INTEGER,
      mediumid             INTEGER,	  
      mediumMetaDataID     TEXT,
      mediumOrigin         INTEGER,
      unique (toc)
);

CREATE TABLE odl_album(
      albumid              INTEGER PRIMARY KEY AUTOINCREMENT,
      tocid                INTEGER REFERENCES metadataselection,
      metaDataID           TEXT,	  
      artist               TEXT,
      name                 TEXT,
      unique (tocid,metaDataID)
);

CREATE TABLE odl_track(
      trackid              INTEGER PRIMARY KEY AUTOINCREMENT,
      albumid              INTEGER REFERENCES odl_album,
      artist               TEXT,
      title                TEXT,
      genre                TEXT
);

CREATE TABLE pendingtags(
	  ambigioustag         INTEGER,
	  buttonpressed        INTEGER,
	  name                 TEXT,
	  artist               TEXT,
	  album                TEXT,
	  genre                TEXT,
	  itunessongid         INTEGER NOT NULL,
	  itunesstorefrontit   INTEGER NOT NULL,
	  stationfrequency     TEXT,
	  staticcallletters    TEXT,
	  timestamp            TEXT,
	  podcastfeedurl       INTEGER,
	  stationurl           TEXT,
	  programnumber        INTEGER,
	  itunesaffiliateid    TEXT,
	  itunesstationid      TEXT,
	  unknowndata          TEXT   
);

CREATE TABLE features(
      feature    TEXT,
      available  INTEGER
);

CREATE TABLE usbdevicedetails(
      deviceserialno       TEXT,
      msid                 INTEGER,
      lastseen             INTEGER
);

INSERT INTO features(feature, available) VALUES('fep', 0);

-- *******************************************************************************
-- * Coverflow Custom Table
-- *******************************************************************************
CREATE TABLE album_cover(
      coverid                INTEGER PRIMARY KEY AUTOINCREMENT,
      album_id               INTEGER REFERENCES library_albums,
      album_artist_id        INTEGER DEFAULT 1 NOT NULL,
      artist_id              INTEGER DEFAULT 1 NOT NULL,
      msid                   INTEGER DEFAULT 0 NOT NULL,
      fid                    INTEGER DEFAULT 0 NOT NULL,
      state                  INTEGER DEFAULT 0 NOT NULL,
      unique (album_id, album_artist_id, artist_id, msid)
);

-- Fid Deletion Trigger
CREATE TRIGGER album_fid_delete AFTER DELETE ON library
   BEGIN
      DELETE FROM album_cover WHERE fid=OLD.fid;
   END;

CREATE TRIGGER album_image_prune AFTER DELETE ON mdi_image_cache
   BEGIN
      UPDATE album_cover SET state=0 WHERE fid=OLD.fid AND OLD.profile_index=-1;
   END;

-- *****************
-- Optional TRIGGERS
-- *****************
-- Folders custom
CREATE TRIGGER folders_custom_insert AFTER INSERT ON folders
   BEGIN
         INSERT INTO folders_custom(folderid, basepath, date_added) VALUES(NEW.folderid, replace((SELECT basepath FROM folders WHERE folderid=NEW.folderid), '/', x'01'), ((strftime('%s','now')+30)/60)*60);
			   UPDATE folders_custom SET toplevelfolderid =
			                        CASE WHEN (  select ((select length(basepath) from folders where folderid=(select parentid from folders where folderid=NEW.folderid)) > 1)     )
			                                  THEN (select toplevelfolderid from folders_custom where folderid = (select parentid from folders where folderid=NEW.folderid))
			                                  ELSE (select NEW.folderid)
			                                  END WHERE folderid=NEW.folderid;



   END;
   
CREATE TRIGGER folders_update_contenttype AFTER UPDATE OF contenttype ON library
  BEGIN
       UPDATE folders_custom SET contenttype = (contenttype | NEW.contenttype) where (folderid = NEW.folderid) OR (folderid = (select toplevelfolderid from folders_custom where folders_custom.folderid = NEW.folderid));
  END;
   

CREATE TRIGGER folders_custom_delete DELETE ON folders
   BEGIN
         DELETE FROM folders_custom WHERE folderid=OLD.folderid;
         UPDATE fbmcontent_custom SET fid=0, folderid=0, idtype=0, objectid=null, textcontent=null WHERE folderid=OLD.folderid;
   END;


CREATE TRIGGER folders_custom_update_displayname UPDATE OF displayname ON folders_custom
  BEGIN
    UPDATE folders_custom SET basepath = (SELECT basepath FROM folders_custom WHERE folderid = (SELECT parentid FROM folders WHERE folderid = new.folderid)) 
                                          || new.displayname
                                          || SUBSTR(basepath, (SELECT LENGTH(old.basepath) FROM folders_custom WHERE folderid = new.folderid),1000000)
    WHERE folderid IN (SELECT folderid FROM folders WHERE (msid = (SELECT msid FROM folders WHERE folderid=new.folderid)) AND (SUBSTR(basepath,1,(SELECT LENGTH(basepath) FROM folders WHERE folderid = new.folderid)) == (SELECT basepath FROM folders WHERE folderid = new.folderid)))
    ;
 END;

-- playlistdata_custom
CREATE TRIGGER playlistdata_custom_delete DELETE ON library
    BEGIN
        DELETE FROM playlistdata_custom WHERE fid=OLD.fid;
        UPDATE fbmcontent_custom SET fid=0, folderid=0, idtype=0, objectid=null, textcontent=null WHERE fid=OLD.fid;
    END;

CREATE INDEX library_index_msid ON library(msid);
CREATE INDEX playlistdata_fid_index ON playlistdata(fid);
CREATE INDEX folders_indexbasepath ON folders(basepath);
CREATE INDEX mdi_image_cache_index_fid on mdi_image_cache(fid);

--custom table cleanup upon MME pruning
CREATE TRIGGER customtable_trigger_delete DELETE ON mediastores
    BEGIN
--      DELETE FROM categorydata_custom WHERE jobId IN (SELECT jobId FROM categorysyncs_custom WHERE msid=OLD.msid);
      DELETE FROM categorydata_custom WHERE msid=OLD.msid;
      DELETE FROM categorysyncs_custom WHERE msid=OLD.msid;
      DELETE FROM usbdevicedetails WHERE msid=OLD.msid;
      DELETE FROM album_cover WHERE msid=OLD.msid;
      DELETE FROM favourites_custom WHERE msid=OLD.msid;
      DELETE FROM playlists_custom WHERE msid=OLD.msid;
      DELETE FROM playlistdata_custom WHERE msid=OLD.msid;
    END;

    
-- odl_album
CREATE TRIGGER odl_album_delete DELETE ON metadataselection
    BEGIN
        DELETE FROM odl_album WHERE tocid=OLD.tocid;
    END;
	
-- odl_track	
CREATE TRIGGER odl_track DELETE ON odl_album
    BEGIN
        DELETE FROM odl_track WHERE albumid=OLD.albumid;
    END;

-- set ext_image flag in mdi_image_cache in order to be able to remove(unlink) MME covers as well from the cache
CREATE TRIGGER mdi_image_cache_update AFTER INSERT ON mdi_image_cache
    BEGIN
        UPDATE mdi_image_cache SET ext_image=1 WHERE insertion_sequence=NEW.insertion_sequence;
    END;

-- delete/update metadataselection ETS entries
CREATE TRIGGER metadataselection_ETS_delete DELETE ON folders
    BEGIN
        DELETE FROM metadataselection WHERE folderid=OLD.folderid AND mediumMetaDataID ISNULL;
        UPDATE metadataselection SET folderid=NULL, folderMetaDataID=NULL, folderOrigin=NULL WHERE folderid=OLD.folderid;  --remove selection for remaining entries that could not be deleted
    END;	
	
-- delete/update metadataselection CDA entries
CREATE TRIGGER metadataselection_CDA_delete DELETE ON mediastores
    BEGIN
        DELETE FROM metadataselection WHERE mediumid=OLD.msid AND folderMetaDataID ISNULL;
        UPDATE metadataselection SET mediumid=NULL, mediumMetaDataID=NULL, mediumOrigin=NULL WHERE mediumid=OLD.msid;  --remove selection for remaining entries that could not be deleted
    END;

CREATE TRIGGER channel_items_delete DELETE ON items
      BEGIN
        DELETE FROM channel_items WHERE genitemid=OLD.genitemid;
      END;

CREATE TRIGGER user_channels_delete DELETE ON channels
      BEGIN
        DELETE FROM user_channels WHERE genchannelid=OLD.genchannelid;
      END;
      
CREATE TRIGGER categorydata_custom_insert AFTER INSERT ON categorydata_custom WHEN (((select available from features where feature='fep')=1) OR ((select available from features where feature='fep')=2) OR ((select available from features where feature='fep')=3) OR ((select available from features where feature='fep')=4))
      BEGIN
         UPDATE categorydata_custom SET name_asia=convertStringToAsiaString(NEW.name, (select available from features where feature='fep')), name_normal=normalizeString(NEW.name, (select available from features where feature='fep'), 1) WHERE uniqueId=NEW.uniqueId;
      END;
      
CREATE TRIGGER IF NOT EXISTS mediastore_update_pcslotid AFTER UPDATE ON mediastores WHEN NEW.slotid != 0
   BEGIN
      UPDATE mediastores SET pcslotid = NEW.slotid WHERE rowid=NEW.rowid;
   END;
CREATE TRIGGER IF NOT EXISTS mediastore_insert_pcslotid AFTER INSERT ON mediastores WHEN NEW.slotid != 0
   BEGIN
      UPDATE mediastores SET pcslotid = NEW.slotid WHERE rowid=NEW.rowid;
   END;
   
-- Chinese to asia trigger 
    
CREATE TRIGGER library_insert_asia AFTER INSERT ON library WHEN (((select available from features where feature='fep')=1) OR ((select available from features where feature='fep')=2) OR ((select available from features where feature='fep')=4))
    BEGIN        
        UPDATE library SET title_asia=convertStringToAsiaString(NEW.title, (select available from features where feature='fep')), title_normal=normalizeString(NEW.title, (select available from features where feature='fep'), 1) WHERE fid=NEW.fid;
    END;

CREATE TRIGGER library_update_asia AFTER UPDATE OF title ON library WHEN (((select available from features where feature='fep')=1) OR ((select available from features where feature='fep')=2) OR ((select available from features where feature='fep')=4))
    BEGIN        
        UPDATE library SET title_asia=convertStringToAsiaString(NEW.title, (select available from features where feature='fep')), title_normal=normalizeString(NEW.title, (select available from features where feature='fep'), 1) WHERE fid=NEW.fid;
    END;
    
CREATE TRIGGER library_genres_insert_asia AFTER INSERT ON library_genres WHEN (((select available from features where feature='fep')=1) OR ((select available from features where feature='fep')=2) OR ((select available from features where feature='fep')=3) OR ((select available from features where feature='fep')=4))
   BEGIN        
      UPDATE library_genres SET genre_asia=convertStringToAsiaString(NEW.genre, (select available from features where feature='fep')), genre_normal=normalizeString(NEW.genre, (select available from features where feature='fep'), 1) WHERE genre_id=NEW.genre_id;
   END;

CREATE TRIGGER library_artists_insert_asia AFTER INSERT ON library_artists WHEN (((select available from features where feature='fep')=1) OR ((select available from features where feature='fep')=2) OR ((select available from features where feature='fep')=3) OR ((select available from features where feature='fep')=4))
    BEGIN        
      UPDATE library_artists SET artist_asia=convertStringToAsiaString(NEW.artist, (select available from features where feature='fep')), artist_normal=normalizeString(NEW.artist, (select available from features where feature='fep'), 1) WHERE artist_id=NEW.artist_id;
    END;

CREATE TRIGGER library_albums_insert_asia AFTER INSERT ON library_albums WHEN (((select available from features where feature='fep')=1) OR ((select available from features where feature='fep')=2) OR ((select available from features where feature='fep')=3) OR ((select available from features where feature='fep')=4))
   BEGIN        
      UPDATE library_albums SET album_asia=convertStringToAsiaString(NEW.album, (select available from features where feature='fep')), album_normal=normalizeString(NEW.album, (select available from features where feature='fep'), 1) WHERE album_id=NEW.album_id;
   END;    

CREATE TRIGGER library_composers_insert_asia AFTER INSERT ON library_composers WHEN (((select available from features where feature='fep')=1) OR ((select available from features where feature='fep')=2) OR ((select available from features where feature='fep')=3) OR ((select available from features where feature='fep')=4))
   BEGIN        
      UPDATE library_composers SET composer_asia=convertStringToAsiaString(NEW.composer, (select available from features where feature='fep')), composer_normal=normalizeString(NEW.composer, (select available from features where feature='fep'), 1) WHERE composer_id=NEW.composer_id;
   END;

CREATE TRIGGER playlistdata_delete DELETE ON library
    BEGIN
        DELETE FROM playlistdata WHERE fid=OLD.fid;
    END;

CREATE TRIGGER library_update_yomi_artist AFTER UPDATE OF artist_secret_id ON library WHEN ((select available from features where feature='fep')=3)
BEGIN 
   UPDATE library_artists SET artist_asia=(select case when artist_secret != '' AND artist_secret IS NOT NULL then normalizeString(artist_secret, 3, 0) else normalizeString(artist, 3, 0) end from (select artist_secret, artist from library_artists_secret, library_artists where artist_secret_id=NEW.artist_secret_id and artist_id=NEW.artist_id)) where artist_id=NEW.artist_id;
END;

CREATE TRIGGER library_update_yomi_album AFTER UPDATE OF album_secret_id ON library WHEN ((select available from features where feature='fep')=3)
BEGIN 
   UPDATE library_albums SET album_asia=(select case when album_secret != '' AND album_secret IS NOT NULL then normalizeString(album_secret, 3, 0) else normalizeString(album, 3, 0) end from (select album_secret, album from library_albums_secret, library_albums where album_secret_id=NEW.album_secret_id and album_id=NEW.album_id)) where album_id=NEW.album_id;
END;

CREATE TRIGGER library_update_yomi_title AFTER UPDATE OF title_secret ON library WHEN ((select available from features where feature='fep')=3)
BEGIN 
	 UPDATE library SET title_asia=(select case when NEW.title_secret != '' AND NEW.title_secret IS NOT NULL then normalizeString(NEW.title_secret, 3, 0) else normalizeString(NEW.title, 3, 0) end) WHERE fid=NEW.fid;
END;

CREATE TRIGGER library_insert_yomi AFTER INSERT ON library WHEN ((select available from features where feature='fep')=3)
BEGIN
   UPDATE library SET title_asia=(select case when NEW.title_secret != '' AND NEW.title_secret IS NOT NULL then normalizeString(NEW.title_secret, 3, 0) else normalizeString(NEW.title, 3, 0) end), title_normal=normalizeString(NEW.title, 3, 1) WHERE fid=NEW.fid;
   UPDATE library_artists SET artist_asia=(select case when artist_secret != '' AND artist_secret IS NOT NULL then normalizeString(artist_secret, 3, 0) else normalizeString(artist, 3, 0) end from (select artist_secret, artist from library_artists_secret, library_artists where artist_secret_id=NEW.artist_secret_id and artist_id=NEW.artist_id)) where artist_id=NEW.artist_id;
   UPDATE library_albums SET album_asia=(select case when album_secret != '' AND album_secret IS NOT NULL then normalizeString(album_secret, 3, 0) else normalizeString(album, 3, 0) end from (select album_secret, album from library_albums_secret, library_albums where album_secret_id=NEW.album_secret_id and album_id=NEW.album_id)) where album_id=NEW.album_id;
END;

CREATE TRIGGER library_update_yomi AFTER UPDATE OF title ON library WHEN ((select available from features where feature='fep')=3)
    BEGIN        
        UPDATE library SET title_asia=(select case when NEW.title_secret != '' AND NEW.title_secret IS NOT NULL then normalizeString(NEW.title_secret, 3, 0) else normalizeString(NEW.title, 3, 0) end), title_normal=normalizeString(NEW.title, 3, 1) WHERE fid=NEW.fid;
    END;
      
COMMIT;
