BEGIN;

-- Step 1:
-- For all rateplanschedule records
-- where description is 'Weekend', set timewindow to 4,
-- where description is 'Off Peak', set timewindow to 2,
-- where description is 'Peak', set timewindow to 1,
-- where description is 'Night', set timewindow to 3.
--
UPDATE rateplanschedule SET timewindow = '4' WHERE description = 'Weekend';
UPDATE rateplanschedule SET timewindow = '2' WHERE description = 'Off Peak';
UPDATE rateplanschedule SET timewindow = '1' WHERE description = 'Peak';
UPDATE rateplanschedule SET timewindow = '3' WHERE description = 'Night';

-- Step 3:
-- Set default value of timewindow to '1' (instead of '0').
--
ALTER TABLE rateplanschedule ALTER COLUMN timewindow SET DEFAULT '1';

-- Step 4:
-- Remove the description field.
--
ALTER TABLE rateplanschedule DROP COLUMN description;

-- Step 5:
-- Replace trigger insert_rateplan: drop current, insert new.
--
-- 0 = Sunday, 6 = Saturday
-- 1 = Peak, 2 = Off Peak, 3 = Night, 4 = Weekend
--
CREATE OR REPLACE FUNCTION insert_rateplan() RETURNS trigger SECURITY DEFINER AS '
  BEGIN
    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''0'', ''00:00:00'', ''4'');

    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''1'', ''00:00:00'', ''3'');
    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''1'', ''08:00:00'', ''1'');
    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''1'', ''19:00:00'', ''2'');

    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''2'', ''00:00:00'', ''3'');
    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''2'', ''08:00:00'', ''1'');
    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''2'', ''19:00:00'', ''2'');

    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''3'', ''00:00:00'', ''3'');
    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''3'', ''08:00:00'', ''1'');
    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''3'', ''19:00:00'', ''2'');

    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''4'', ''00:00:00'', ''3'');
    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''4'', ''08:00:00'', ''1'');
    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''4'', ''19:00:00'', ''2'');

    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''5'', ''00:00:00'', ''3'');
    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''5'', ''08:00:00'', ''1'');
    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''5'', ''19:00:00'', ''2'');

    insert into rateplanschedule (rateplan, weekday, start, timewindow) VALUES
                                (NEW.id, ''6'', ''00:00:00'', ''4'');
    RETURN NEW;
  END;
' LANGUAGE plpgsql;

DROP TRIGGER insert_rateplan ON rateplan;
CREATE TRIGGER insert_rateplan AFTER INSERT ON rateplan FOR EACH ROW EXECUTE PROCEDURE insert_rateplan();

COMMIT;
