/*******************************************************************************
	SQL to adjust the EPG data for various problems.
*******************************************************************************/

DROP PROCEDURE IF EXISTS How_Its_Made_epg_fix;

DELIMITER $$
CREATE PROCEDURE How_Its_Made_epg_fix()
BEGIN
	DECLARE p_finished INTEGER DEFAULT 0;
	DECLARE v_chanid INT(10) UNSIGNED;
	DECLARE v_starttime DATETIME;
	DECLARE v_manualid INT(10) UNSIGNED;
	DECLARE v_description VARCHAR(16000);
	DECLARE v_pos INTEGER UNSIGNED;
	DECLARE v_pos_right INTEGER UNSIGNED;
	DECLARE v_subtitle VARCHAR(16000);
	DECLARE v_right VARCHAR(16000);
	DECLARE p CURSOR FOR
		SELECT `chanid`,`starttime`,`manualid`,`description` FROM program
			WHERE TITLE LIKE "%how it's made%" AND subtitle='' AND
				(description LIKE '%:Find%' OR description LIKE '%: Find%' OR description LIKE '%:See%' OR description LIKE '%: See%')
			order by starttime;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET p_finished = 1;

	SELECT `chanid`,`starttime`,`manualid`,`description` FROM program
		WHERE title="How It's Made" AND subtitle='' AND
			(description LIKE '%:Find%' OR description LIKE '%: Find%' OR description LIKE '%:See%' OR description LIKE '%: See%')
		order by starttime;

	OPEN p;
	bad_subtitle_loop: LOOP
		FETCH p INTO v_chanid,v_starttime,v_manualid,v_description;
		SET v_pos = INSTR(v_description, ':');
		SET v_subtitle = LEFT(v_description, v_pos-1);
		IF SUBSTR(v_description, v_pos+1, 1) = ' ' THEN
			SET v_pos_right = v_pos+2;
		ELSE
			SET v_pos_right = v_pos+1;
		END IF;
		SET v_right=SUBSTR(v_description, v_pos_right);
		/*
		SELECT v_chanid,v_starttime,v_manualid,v_description;
		SELECT v_subtitle, v_right;
		*/
		UPDATE program SET subtitle=v_subtitle, description=v_description WHERE chanid=v_chanid AND starttime=v_starttime AND manualid=v_manualid;
		IF p_finished != 0 THEN
			LEAVE bad_subtitle_loop;
		END IF;
	END LOOP;
	CLOSE p;
END$$
DELIMITER ;

select chanid,starttime,title,subtitle,programid from program where title like 'All New %';
update program set title=substr(title,9) where title like 'All New %';
select chanid,starttime,title,subtitle,programid from program where title like 'All New: %';
update program set title=substr(title,10) where title like 'All New: %';
select chanid,starttime,title,substr(title,6) as title6,subtitle,programid from program where title like 'New: %';
update program set title=substr(title,6) where title like 'New: %';
/*
select chanid,starttime,title,subtitle,programid from program where title like 'Cleverman %';
update program set title='Cleverman' where title like 'Cleverman %';
select chanid,starttime,title,subtitle,left(description,20),season,episode,category,seriesid,programid from program where title like '%trans-si%';
update program set programid='' where title="Joanna Lumley's Trans-Siberian";
*/
select chanid,starttime,title,subtitle,left(description,20),season,episode,category,seriesid,programid from program where title="Going Deep w' David Rees";
update program set title='Going Deep with David Rees' where title="Going Deep w' David Rees";
select chanid,starttime,title,subtitle,left(description,20),season,episode,category,seriesid,programid from program where title="Rebellion:" and subtitle='The Stuarts In Exile';
update program set title='Rebellion: The Stuarts In Exile',subtitle='' where title="Rebellion:" and subtitle='The Stuarts In Exile';
select chanid,starttime,title,subtitle,left(description,20),season,episode,category,seriesid,programid from program where title="WWII Air Crash Detectives";
update program set title='WW2 Air Crash Detectives',subtitle='' where title="WWII Air Crash Detectives";
select chanid,starttime,title,subtitle,programid from program where title='Good Karma Hospital' AND subtitle LIKE 'New S%';
UPDATE program SET subtitle=substr(subtitle,5) WHERE title='Good Karma Hospital' AND subtitle LIKE 'New S%';
select chanid,starttime,title,subtitle,left(description,20),season,episode,category,seriesid,programid from program where title like "live pd%police patrol" order by programid;
update program set title='Live PD: Police Patrol' where title like "live pd%police patrol%";
select chanid,starttime,title,subtitle,left(description,20),season,episode,category,seriesid,programid from program where title like "%Earth's Great%" and subtitle like "%seasons" order by programid;
update program set title="Earth's Great Seasons", subtitle='' where title like "%Earth's Great%" and subtitle like "%seasons";
select chanid,starttime,title,subtitle,left(description,20),season,episode,category,seriesid,programid from program where title like 'The %Houses: Britain%Cheap%' order by programid;
update program set title="The One Pound Houses: Britain's Cheapest Street" where title like 'The %Houses: Britain%Cheap%';
select chanid,starttime,title,subtitle,left(description,20),season,episode,category,seriesid,programid from program where title like '%Drews Honeym%' or title like "%Drew's Honeym%" order by programid;
update program set title="Drew's Honeymoon House" where title like '%Drews Honeym%' or title like "%Drew's Honeym%";
CALL How_Its_Made_epg_fix();
DROP PROCEDURE IF EXISTS How_Its_Made_epg_fix;