         check (outcome or (not successful) or (comment is not null))
 ) engine=InnoDB;
 
+-- Trigger to insert the correct test_id on every transcoder_report.
+
+DELIMITER ;;
+CREATE TRIGGER ins_test 
+	BEFORE INSERT ON transcoder_reports 
+	FOR EACH ROW 
+BEGIN 
+	DECLARE last_test INTEGER;
+	SELECT MAX(id) INTO last_test FROM test_index;
+	SET NEW.test_id = last_test;
+END ;;
+DELIMITER ;
+
 
 -- Various helpful views follow, encompassing some QoS metrics that we keep track of
 

insert into schema_information(schema_version, upgrading_soon, upgrading_currently)
values ('2008123000', false, false);



-- a lookup table of possible transcode outcomes
create table if not exists transcoder_failures (


create or replace view transcoder_outcomes_per_customer as
select
	test_id,
        customer_id,
        sum(case when (outcome and not invalid_output) then 1 else 0 end) as successful_transcods,
        sum(case when (not outcome or invalid_output) then 1 else 0 end) as failed_transcods,
        sum(case when (not outcome or invalid_output) and (failure_id is null) then 1 else 0 end) as failed_unexpectedly_transcods
from
        transcoder_reports
group by test_id, customer_id;

create or replace view transcoder_outcomes_per_profile as
select
	test_id,
        customer_id,
        profile_id,
        sum(case when (outcome and not invalid_output) then 1 else 0 end) as successful_transcods,
