ALTER TABLE ignoredpages ENGINE=InnoDB;
ALTER TABLE categoryitems ENGINE=InnoDB;
ALTER TABLE channel ENGINE=InnoDB;
ALTER TABLE channelwatchers ENGINE=InnoDB;
ALTER TABLE watcher ENGINE=InnoDB;
ALTER TABLE site ENGINE=InnoDB;


ALTER TABLE watcher ADD basewiki int NULL;
UPDATE watcher SET basewiki = 1;
ALTER TABLE watcher
  CHANGE basewiki basewiki int NOT NULL,
  ADD CONSTRAINT watcher_site_site_id_fk FOREIGN KEY (basewiki) REFERENCES site (site_id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE channelwatchers DROP PRIMARY KEY;
ALTER TABLE channelwatchers 
  ADD COLUMN id int NOT NULL auto_increment PRIMARY KEY FIRST,
  ADD COLUMN sleeptime int DEFAULT 1200 NOT NULL,
  ADD COLUMN showwait INT NOT NULL DEFAULT 1,
  ADD COLUMN minwait INT NOT NULL DEFAULT 3600,
  ADD COLUMN showlink INT NOT NULL DEFAULT 1,
  ADD COLUMN alertadditions INT NOT NULL DEFAULT 0,
  ADD COLUMN alertremovals INT NOT NULL DEFAULT 0,
  CHANGE cw_channel channel int(11) NOT NULL,
  CHANGE cw_watcher watcher int(11) NOT NULL,
  ADD CONSTRAINT channelwatchers_channel_channel_id_fk FOREIGN KEY (channel) REFERENCES channel (channel_id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT channelwatchers_watcher_watcher_id_fk FOREIGN KEY (watcher) REFERENCES watcher (watcher_id) ON DELETE CASCADE ON UPDATE CASCADE;

UPDATE channelwatchers cw SET cw.sleeptime = (SELECT w.watcher_sleeptime FROM watcher w WHERE w.watcher_id = cw.watcher);
UPDATE channelwatchers cw
  SET cw.showwait = coalesce(
      (select case coalesce(cc.cc_value, c.configuration_value)
        when 'true' then 1
        else 0
      end
      from configuration c
      left join channelconfig cc ON cc.cc_config = c.configuration_id
      where c.configuration_name = 'showWaitTime'
        and cc.cc_channel = cw.channel), cw.showwait)
  , cw.minwait = coalesce(
      (select time_to_sec(cast(coalesce(cc.cc_value, c.configuration_value) as time)) 
      from configuration c
      left join channelconfig cc ON cc.cc_config = c.configuration_id
      where c.configuration_name = 'minimumWaitTime'
        and cc.cc_channel = cw.channel), cw.minwait)
  , cw.showlink = coalesce(
      (select case coalesce(cc.cc_value, c.configuration_value)
        when 'true' then 1
        else 0
      end
      from configuration c
      left join channelconfig cc ON cc.cc_config = c.configuration_id
      where c.configuration_name = 'useShortUrlsInsteadOfWikilinks'
        and cc.cc_channel = cw.channel), cw.showlink)
;

ALTER TABLE watcher
  DROP COLUMN watcher_priority,
  DROP COLUMN watcher_sleeptime,
  CHANGE watcher_category category varchar(255) NOT NULL,
  CHANGE watcher_keyword keyword varchar(10) NOT NULL;

ALTER TABLE categoryitems
  CHANGE item_id id int(10) unsigned NOT NULL auto_increment,
  CHANGE item_name title varchar(255) NOT NULL,
  CHANGE item_entrytime inserttime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  DROP COLUMN item_updateflag,
  ADD COLUMN watcher int(11) null;

UPDATE categoryitems c SET c.watcher = (SELECT w.watcher_id FROM watcher w WHERE c.item_keyword = w.keyword);
DELETE FROM categoryitems WHERE watcher IS NULL;

ALTER TABLE categoryitems
  DROP COLUMN item_keyword,
  CHANGE watcher watcher int(11) NOT NULL,
  ADD CONSTRAINT categoryitems_watcher_watcher_id_fk FOREIGN KEY (watcher) REFERENCES watcher (watcher_id) ON DELETE CASCADE ON UPDATE CASCADE;

<component type="CategoryWatcherConfiguration">
    <parameters>
        <enabled>true</enabled>
        <updateFrequency>60</updateFrequency>
        <crossoverTimeout>30</crossoverTimeout>
    </parameters>
</component>