~primes2h/+junk/ubuntu-qa-website

CREATE TABLE qapoll_permission
(
   id serial, 
   "name" text, 
   permissions text, 
   date timestamp without time zone, 
   description text,
   permission_level integer DEFAULT 0,
   icon_name text,
   ordering integer DEFAULT 0,
   PRIMARY KEY (id)
);
ALTER TABLE qapoll_permission ADD COLUMN display_name text;

INSERT INTO qapoll_permission(name, permissions, date, description, permission_level, icon_name, ordering, display_name) VALUES('Brainstorm admin', 'a:1:{s:6:"global";a:1:{s:3:"all";b:1;}} ', NOW(), 'The admin has all rights over Brainstorm', 2, 'images/minilogo.png', 9999, 'Brainstorm admin');
INSERT INTO qapoll_permission(name, permissions, date, description, permission_level, icon_name, ordering) VALUES('Registered Brainstorm user', 'a:4:{s:6:"global";a:0:{}s:6:"Choice";a:3:{s:11:"owner_perms";a:1:{s:13:"edit_relation";b:1;}s:11:"submit_idea";b:1;s:15:"submit_solution";b:1;}s:4:"Menu";a:1:{s:22:"show_my_dashboard_link";b:1;}s:14:"ChoiceSolution";a:1:{s:11:"owner_perms";a:1:{s:13:"edit_solution";b:1;}}}', NOW(), 'A registered user have some basic rights over ideas', 0, null, 0);

INSERT INTO qapoll_permission(name, permissions, date, description, permission_level, icon_name, ordering, display_name) VALUES('Ubuntu Developer', 'a:2:{s:6:"global";a:0:{}s:6:"Choice";a:7:{s:11:"edit_status";b:1;s:19:"edit_target_release";b:1;s:17:"edit_dev_comments";b:1;s:13:"edit_relation";b:1;s:19:"search_by_admintags";b:1;s:12:"approve_idea";b:1;s:15:"select_solution";b:1;}}', NOW(), 'The Ubuntu developer can set the status and developers comments of an idea.', 2, 'images/minilogo.png', 50, 'Ubuntu developer');

INSERT INTO qapoll_permission(name, permissions, date, description, permission_level, icon_name, ordering, display_name) VALUES('Global moderator', 'a:3:{s:6:"global";a:1:{s:14:"process_report";b:1;}s:6:"Choice";a:10:{s:11:"edit_status";b:1;s:19:"edit_target_release";b:1;s:13:"edit_relation";b:1;s:12:"approve_idea";b:1;s:15:"delete_solution";b:1;s:17:"mark_solution_dup";b:1;s:8:"mark_dup";b:1;s:13:"edit_solution";b:1;s:10:"edit_title";b:1;s:16:"edit_description";b:1;}s:4:"User";a:1:{s:17:"delete_user_items";b:1;}}', NOW(), 'The moderator can delete an idea, change its status.', 1, 'images/minilogo.png', 2, 'Brainstorm moderator');

INSERT INTO qapoll_permission(name, permissions, date, description, permission_level, icon_name, ordering, display_name) VALUES('Idea reviewer', 'a:2:{s:6:"global";a:0:{}s:6:"Choice";a:1:{s:14:"filtered_perms";a:1:{s:82:"state_workinprogress=0&state_done=0&state_awaiting_moderation=1&duplicate_items=-2";a:7:{s:22:"status_mark_as_nonidea";b:1;s:34:"status_mark_as_already_implemented";b:1;s:13:"edit_relation";b:1;s:12:"approve_idea";b:1;s:13:"edit_solution";b:1;s:10:"edit_title";b:1;s:16:"edit_description";b:1;}}}} ', NOW(), 'The idea reviewer can add its vote on an idea in the candidate list.', 1, 'images/minilogo.png', 1, 'Idea reviewer');

INSERT INTO qapoll_permission(name, permissions, date, description, permission_level, icon_name, ordering, display_name) VALUES('Amarok developer', 'a:3:{s:6:"global";a:0:{}s:6:"Choice";a:1:{s:14:"filtered_perms";a:1:{s:24:"relation=21&all_states=1";a:6:{s:11:"edit_status";b:1;s:19:"edit_target_release";b:1;s:17:"edit_dev_comments";b:1;s:13:"edit_relation";b:1;s:12:"approve_idea";b:1;s:15:"select_solution";b:1;}}}s:10:"EntryPoint";a:1:{s:14:"filtered_perms";a:1:{s:17:"entry_point_ids=4";a:3:{s:15:"show_admin_page";b:1;s:19:"edit_frontpage_desc";b:1;s:21:"show_process_dup_page";b:1;}}}}', NOW(), 'The Amarok developer has admin rights over their ideas.', 1, null, 100, 'Amarok developer');

INSERT INTO qapoll_permission(name, permissions, date, description, permission_level, icon_name, ordering, display_name) VALUES('Mythbuntu developer', 'a:3:{s:6:"global";a:0:{}s:6:"Choice";a:1:{s:14:"filtered_perms";a:1:{s:25:"relation=151&all_states=1";a:6:{s:11:"edit_status";b:1;s:19:"edit_target_release";b:1;s:17:"edit_dev_comments";b:1;s:13:"edit_relation";b:1;s:12:"approve_idea";b:1;s:15:"select_solution";b:1;}}}s:10:"EntryPoint";a:1:{s:14:"filtered_perms";a:1:{s:17:"entry_point_ids=4";a:3:{s:15:"show_admin_page";b:1;s:19:"edit_frontpage_desc";b:1;s:21:"show_process_dup_page";b:1;}}}}', NOW(), 'The Mythbuntu developer has admin rights over their ideas.', 1, null, 100, 'Mythbuntu developer');


UPDATE qawebsite_site SET subdomain='qa.ubuntu.com|brainstorm.ubuntu.com|[a-z]*.brainstorm.ubuntu.com|bugvote.ubuntu.com|blog.qa.ubuntu.com|pkgstatus.qa.ubuntu.com', logo='idea-logo.png' WHERE id=6;



DROP TABLE qapoll_search_name;


CREATE TABLE qapoll_poll_relation_subcategory
(
  id serial NOT NULL,
  relationid integer NOT NULL,
  "name" character varying(100) NOT NULL,
  description text,
  ordering integer,
  CONSTRAINT qapoll_poll_relation_subcategory_pkey PRIMARY KEY (id)
);


ALTER TABLE qapoll_choice ADD COLUMN relation_subcategory_id integer DEFAULT -1;




ALTER TABLE qapoll_entry_point_menu_entry DROP COLUMN userrole;
ALTER TABLE qapoll_entry_point_menu_entry DROP COLUMN adminrole;
ALTER TABLE qapoll_entry_point_menu_entry ADD COLUMN permission_needed text;

UPDATE qapoll_entry_point_menu_entry SET permission_needed = 'EntryPoint:show_admin_page' WHERE name = 'Admin panel';
UPDATE qapoll_entry_point_menu_entry SET permission_needed = 'Menu:show_my_dashboard_link' WHERE name = 'My dashboard';
UPDATE qapoll_entry_point_menu_entry SET permission_needed = 'Choice:submit_idea' WHERE name = 'Submit your idea';
UPDATE qapoll_entry_point_menu_entry SET permission_needed = 'EntryPoint:show_process_dup_page' WHERE name = 'Process duplicate reports';

/*ALTER TABLE qawebsite_site ADD COLUMN defaultpath character varying;
ALTER TABLE qawebsite_site ALTER COLUMN userrole DROP NOT NULL;
ALTER TABLE qawebsite_site ALTER COLUMN adminrole DROP NOT NULL;
UPDATE qawebsite_site SET defaultpath='/qatracker';
UPDATE qawebsite_site SET subdomain='brainstorm.ubuntu.com|bugvote.ubuntu.com', title='Ubuntu Brainstorm', defaultpath='/qapoll' WHERE id=6;
INSERT INTO qawebsite_site (subdomain, title, logo, defaultpath) VALUES ('blog.qa.ubuntu.com','Ubuntu QA blog','blog-logo.png','/node');
INSERT INTO qawebsite_site (subdomain, title, logo, userrole, adminrole, defaultpath) VALUES ('qa.ubuntu.com','Ubuntu QA','portal-logo.png','QA Portal User','QA Portal admin','/qaportal');
ALTER TABLE qawebsite_site ALTER COLUMN defaultpath SET NOT NULL;*/

INSERT INTO qawebsite_site_module (siteid, path, logo, title, status) VALUES (6, 'http://pkgstatus.qa.ubuntu.com', 'pkg-logo.png', 'Package status', 1);
UPDATE qawebsite_site_module SET logo='blog-logo.png' WHERE id='10';

ALTER TABLE qapoll_poll_category ADD COLUMN url_name character varying(100);
UPDATE qapoll_poll_category SET url_name = 'installation' WHERE id=1;
UPDATE qapoll_poll_category SET url_name = 'look_and_feel' WHERE id=2;
UPDATE qapoll_poll_category SET url_name = 'graphics' WHERE id=3;
UPDATE qapoll_poll_category SET url_name = 'internet_networking' WHERE id=4;
UPDATE qapoll_poll_category SET url_name = 'office' WHERE id=5;
UPDATE qapoll_poll_category SET url_name = 'multimedia' WHERE id=6;
UPDATE qapoll_poll_category SET url_name = 'gaming' WHERE id=7;
UPDATE qapoll_poll_category SET url_name = 'server' WHERE id=8;
UPDATE qapoll_poll_category SET url_name = 'system' WHERE id=9;
UPDATE qapoll_poll_category SET url_name = 'others' WHERE id=10;
UPDATE qapoll_poll_category SET url_name = 'brainstorm' WHERE id=11;
UPDATE qapoll_poll_category SET url_name = 'accessibility' WHERE id=12;
UPDATE qapoll_poll_category SET url_name = 'hardware' WHERE id=13;
UPDATE qapoll_poll_category SET url_name = 'education' WHERE id=14;
UPDATE qapoll_poll_category SET url_name = 'programming' WHERE id=15;
UPDATE qapoll_poll_category SET url_name = 'security' WHERE id=16;
UPDATE qapoll_poll_category SET url_name = 'marketing' WHERE id=17;
UPDATE qapoll_poll_category SET url_name = 'documentation' WHERE id=18;
UPDATE qapoll_poll_category SET url_name = 'usability' WHERE id=19;

ALTER TABLE qapoll_poll_relation_subcategory ADD COLUMN url_name character varying(100);


DELETE FROM qapoll_entry_point_menu WHERE id=9 OR id<=6;
DELETE FROM qapoll_entry_point_menu_entry WHERE id>25 OR (id <= 22 AND id >= 10) OR id < 7;


CREATE TABLE qapoll_choice_approval_vote
(
   id serial, 
   choiceid integer NOT NULL, 
   userid integer NOT NULL, 
   "value" integer NOT NULL, 
   status smallint_unsigned NOT NULL DEFAULT 1, 
   date timestamp without time zone NOT NULL, 
    PRIMARY KEY (id)
);

ALTER TABLE qapoll_choice ADD COLUMN totalapprovalvotes integer NOT NULL DEFAULT 0;
ALTER TABLE qapoll_choice ADD COLUMN last_edit_date timestamp without time zone;

UPDATE qapoll_poll_relation_category SET ordering='-2' WHERE id=2;
UPDATE qapoll_poll_relation_category SET name='Ubuntu website' WHERE id=1;
UPDATE qapoll_poll_relation_category SET name='Ubuntu derivate' WHERE id=4;
UPDATE qapoll_poll_relation_category SET ordering='-1' WHERE id=3;
UPDATE qapoll_poll_relation_category SET ordering='0' WHERE id=4;


CREATE TABLE qapoll_choice_solution
(
   id serial, 
   duplicate_choice_solution_id integer NOT NULL DEFAULT -1, 
   title text, 
   userid integer, 
   date timestamp without time zone NOT NULL, 
   last_edit_date timestamp without time zone, 
   description text, 
   solution_votes integer DEFAULT 0, 
   solution_plus_votes integer DEFAULT 0,
   solution_equal_votes integer DEFAULT 0,
   solution_minus_votes integer DEFAULT 0,
   total_votes integer DEFAULT 0, 
   total_plus_votes integer DEFAULT 0, 
   total_equal_votes integer DEFAULT 0, 
   total_minus_votes integer DEFAULT 0, 
   status integer NOT NULL DEFAULT 1, 
    PRIMARY KEY (id)
);


CREATE TABLE qapoll_choice_solution_link
(
   id serial, 
   choiceid integer NOT NULL, 
   choicesolutionid integer NOT NULL, 
   advertize integer DEFAULT 1, 
   solution_number integer DEFAULT 1, 
   status integer DEFAULT 1, 
   selected smallint_unsigned DEFAULT 0,
   userid integer,
    PRIMARY KEY (id)
);

CREATE INDEX qapoll_choice_solution_link_choiceid ON qapoll_choice_solution_link ( choiceid );
CREATE INDEX qapoll_choice_solution_link_choicesolutionid ON qapoll_choice_solution_link ( choicesolutionid );

ALTER TABLE qapoll_vote RENAME choiceid  TO choicesolutionid;



INSERT INTO qapoll_poll_relation(relation_category_id, name, url_name, description)
VALUES(1, 'brainstorm.ubuntu.com', 'brainstorm-ubuntu-com', 'The brainstorm.ubuntu.com website');

UPDATE qapoll_choice SET relation_id = 150, categoryid = 10
WHERE categoryid = 11;

DELETE FROM qapoll_poll_category WHERE id=11;

INSERT INTO qapoll_poll_relation_subcategory(relationid, name, description, url_name, ordering)
	VALUES (150, 'Idea structure', 'The structure of an idea.', 'idea_structure', 0);
INSERT INTO qapoll_poll_relation_subcategory(relationid, name, description, url_name, ordering)
	VALUES (150, 'Website structure', 'The structure of the website.', 'website_structure', 0);
INSERT INTO qapoll_poll_relation_subcategory(relationid, name, description, url_name, ordering)
	VALUES (150, 'Website navigation', 'The navigation of the website.', 'website_navigation', 0);
INSERT INTO qapoll_poll_relation_subcategory(relationid, name, description, url_name, ordering)
	VALUES (150, 'Ideas/comments moderation', 'Anything about the moderation of content in Brainstorm.', 'moderation', 0);
INSERT INTO qapoll_poll_relation_subcategory(relationid, name, description, url_name, ordering)
	VALUES (150, 'Developer feedback', 'Anything about of developer feedback process.', 'developer_feedback', 0);
INSERT INTO qapoll_poll_relation_subcategory(relationid, name, description, url_name, ordering)
	VALUES (150, 'Others', 'Anything else', 'others', 10);



DELETE FROM qapoll_entry_point WHERE id=1;




CREATE OR REPLACE FUNCTION qapoll_compute_number_of_votes(integer, integer) RETURNS integer[3] AS $$
DECLARE
	choice RECORD;
	choiceresult integer[3];
	result integer[3];
BEGIN
	-- Avoid recursive loop
	IF $2 > 10 THEN
		RETURN '{0, 0, 0}';
	END IF;

	SELECT 
		'{' || (SELECT COUNT(*) FROM qapoll_vote WHERE choicesolutionid=$1 AND VALUE='1') || ', ' ||
		(SELECT COUNT(*) FROM qapoll_vote WHERE choicesolutionid=$1 AND VALUE='0') || ', ' ||
		(SELECT COUNT(*) FROM qapoll_vote WHERE choicesolutionid=$1 AND VALUE='-1') || '}'
	INTO result;

	FOR choice IN SELECT id FROM qapoll_choice WHERE duplicatenumber = $1
	LOOP
		SELECT qapoll_compute_number_of_votes(choice.id, $2+1) INTO choiceresult;
		result[1] := result[1] + choiceresult[1];
		result[2] := result[2] + choiceresult[2];
		result[3] := result[3] + choiceresult[3];
	END LOOP;

	RETURN result;
END;
$$ LANGUAGE plpgsql;





--
-- Function and trigger to maintain the sums of votes
-- Used as a trigger in qapoll_vote
--
CREATE OR REPLACE FUNCTION qapoll_maintain_votes_sums() RETURNS TRIGGER AS $qapoll_maintain_votes_sums$
    DECLARE
        delta_vote          	integer;
	delta_vote_plus		integer;
	delta_vote_equal	integer;
	delta_vote_minus	integer;
        affected_choicesolution	integer;
	choice RECORD;
    BEGIN
	delta_vote_minus = 0;
	delta_vote_equal = 0;
	delta_vote_plus = 0;

        -- Work out the increment/decrement amount(s).
        IF (TG_OP = 'DELETE') THEN

		delta_vote = -1 * OLD.value;
		affected_choicesolution = OLD.choicesolutionid;
		IF(OLD.value = -1) THEN
			delta_vote_minus = -1;
		ELSIF(OLD.value = 0) THEN
			delta_vote_equal = -1;
		ELSIF(OLD.value = 1) THEN
			delta_vote_plus = -1;
		END IF;


        ELSIF (TG_OP = 'UPDATE') THEN

		delta_vote = NEW.value - OLD.value;
		affected_choicesolution = OLD.choicesolutionid;

		IF(OLD.value = -1) THEN
			delta_vote_minus = -1;
		ELSIF(OLD.value = 0) THEN
			delta_vote_equal = -1;
		ELSIF(OLD.value = 1) THEN
			delta_vote_plus = -1;
		END IF;

		IF(NEW.value = -1) THEN
			delta_vote_minus = delta_vote_minus + 1;
		ELSIF(NEW.value = 0) THEN
			delta_vote_equal = delta_vote_equal + 1;
		ELSIF(NEW.value = 1) THEN
			delta_vote_plus = delta_vote_plus + 1;
		END IF;

        ELSIF (TG_OP = 'INSERT') THEN

		delta_vote = NEW.value;
		affected_choicesolution = NEW.choicesolutionid;

		IF(NEW.value = -1) THEN
			delta_vote_minus = 1;
		ELSIF(NEW.value = 0) THEN
			delta_vote_equal = 1;
		ELSIF(NEW.value = 1) THEN
			delta_vote_plus = 1;
		END IF;

        END IF;
		
	UPDATE qapoll_choice_solution
		SET solution_votes = solution_votes + delta_vote,
			solution_plus_votes = solution_plus_votes + delta_vote_plus,
			solution_equal_votes = solution_equal_votes + delta_vote_equal,
			solution_minus_votes = solution_minus_votes + delta_vote_minus,
			total_votes = total_votes + delta_vote,
			total_plus_votes = total_plus_votes + delta_vote_plus,
			total_equal_votes = total_equal_votes + delta_vote_equal,
			total_minus_votes = total_minus_votes + delta_vote_minus
		WHERE id = affected_choicesolution;


	-- Update the totalvotes field of the affected ideas. It reflects the max sum of votes of its solutions.
	FOR choice IN SELECT DISTINCT choiceid FROM qapoll_choice_solution_link
		WHERE choicesolutionid = affected_choicesolution
	LOOP
		EXECUTE 'UPDATE qapoll_choice SET totalvotes = (SELECT COALESCE(MAX(qapoll_choice_solution.total_votes), 0)  
				FROM qapoll_choice_solution
				JOIN qapoll_choice_solution_link  ON qapoll_choice_solution_link.choicesolutionid = qapoll_choice_solution.id
				WHERE qapoll_choice_solution_link.choiceid = ' || choice.choiceid || ' AND 
				qapoll_choice_solution.status = 1 AND 
				qapoll_choice_solution_link.status = 1 AND 
				qapoll_choice_solution_link.advertize = 1)  
			WHERE id=' || choice.choiceid;

	END LOOP;

        RETURN NULL;

    END;
$qapoll_maintain_votes_sums$ LANGUAGE plpgsql;



--
-- Function and trigger to maintain the sums of votes
-- Used as a trigger in the qapoll_choice_solution table
-- Affected field: qapoll_choice_solution total_[plus|equal|minus]+_votes, qapoll_choice totalvotes
--
CREATE OR REPLACE FUNCTION qapoll_maintain_votes_sums_2() RETURNS TRIGGER AS $qapoll_maintain_votes_sums_2$
    DECLARE
    	choice RECORD;
	affected_solution integer;
    BEGIN

        -- Work out the increment/decrement amount(s).
        IF (TG_OP = 'DELETE') THEN
		-- SHOULD NEVER OCCUR

        ELSIF (TG_OP = 'UPDATE') THEN
		IF(OLD.duplicate_choice_solution_id != NEW.duplicate_choice_solution_id) THEN
			IF(OLD.duplicate_choice_solution_id != -1) THEN

				UPDATE qapoll_choice_solution
				SET
					total_votes = total_votes - OLD.solution_votes,
					total_plus_votes = total_plus_votes - OLD.solution_plus_votes,
					total_equal_votes = total_equal_votes - OLD.solution_equal_votes,
					total_minus_votes = total_minus_votes - OLD.solution_minus_votes
				WHERE id = OLD.duplicate_choice_solution_id;

				affected_solution = OLD.duplicate_choice_solution_id;

			ELSE

				affected_solution = OLD.id;

			END IF;
			--RAISE NOTICE 'affected %', affected_solution;
			-- Update the totalvotes field of the affected ideas. It reflects the max sum of votes of its solutions.
			FOR choice IN SELECT DISTINCT choiceid FROM qapoll_choice_solution_link
				WHERE choicesolutionid = affected_solution
			LOOP
				EXECUTE 'UPDATE qapoll_choice SET totalvotes = (SELECT COALESCE(MAX(qapoll_choice_solution.total_votes), 0)  
						FROM qapoll_choice_solution
						JOIN qapoll_choice_solution_link  ON qapoll_choice_solution_link.choicesolutionid = qapoll_choice_solution.id
						WHERE qapoll_choice_solution_link.choiceid = ' || choice.choiceid || ' AND 
						qapoll_choice_solution.status = 1 AND 
						qapoll_choice_solution_link.status = 1 AND 
						qapoll_choice_solution_link.advertize = 1)  
					WHERE id=' || choice.choiceid;
				--RAISE NOTICE 'd %', choice.choiceid;
			END LOOP;



			IF(NEW.duplicate_choice_solution_id != -1) THEN

				UPDATE qapoll_choice_solution
				SET
					total_votes = total_votes + NEW.solution_votes,
					total_plus_votes = total_plus_votes + NEW.solution_plus_votes,
					total_equal_votes = total_equal_votes+ NEW.solution_equal_votes,
					total_minus_votes = total_minus_votes + NEW.solution_minus_votes
				WHERE id = NEW.duplicate_choice_solution_id;

				affected_solution = NEW.duplicate_choice_solution_id;

			ELSE

				affected_solution = NEW.id;


			END IF;
			--RAISE NOTICE 'affected %', affected_solution;
			-- Update the totalvotes field of the affected ideas. It reflects the max sum of votes of its solutions.
			FOR choice IN SELECT DISTINCT choiceid FROM qapoll_choice_solution_link
				WHERE choicesolutionid = affected_solution
			LOOP
				--RAISE NOTICE 'd %', choice.choiceid;
				EXECUTE 'UPDATE qapoll_choice SET totalvotes = (SELECT COALESCE(MAX(qapoll_choice_solution.total_votes), 0)  
						FROM qapoll_choice_solution
						JOIN qapoll_choice_solution_link  ON qapoll_choice_solution_link.choicesolutionid = qapoll_choice_solution.id
						WHERE qapoll_choice_solution_link.choiceid = ' || choice.choiceid || ' AND 
						qapoll_choice_solution.status = 1 AND 
						qapoll_choice_solution_link.status = 1 AND 
						qapoll_choice_solution_link.advertize = 1)  
					WHERE id=' || choice.choiceid;

			END LOOP;




		END IF;

		--If the status change
		IF(OLD.status != NEW.status) THEN
			--Let's say the duplicate number won't change at the same time.
			IF(NEW.duplicate_choice_solution_id != -1) THEN
				affected_solution = NEW.duplicate_choice_solution_id;
			ELSE
				affected_solution = NEW.id;
			END IF;

			--RAISE NOTICE 'affected %', affected_solution;
			-- Update the totalvotes field of the affected ideas. It reflects the max sum of votes of its solutions.
			FOR choice IN SELECT DISTINCT choiceid FROM qapoll_choice_solution_link
				WHERE choicesolutionid = affected_solution
			LOOP
				--RAISE NOTICE 'd %', choice.choiceid;
				EXECUTE 'UPDATE qapoll_choice SET totalvotes = (SELECT COALESCE(MAX(qapoll_choice_solution.total_votes), 0)  
						FROM qapoll_choice_solution
						JOIN qapoll_choice_solution_link  ON qapoll_choice_solution_link.choicesolutionid = qapoll_choice_solution.id
						WHERE qapoll_choice_solution_link.choiceid = ' || choice.choiceid || ' AND 
						qapoll_choice_solution.status = 1 AND 
						qapoll_choice_solution_link.status = 1 AND 
						qapoll_choice_solution_link.advertize = 1)  
					WHERE id=' || choice.choiceid;

			END LOOP;

		END IF;
		
        ELSIF (TG_OP = 'INSERT') THEN

		-- NORMALLY ALL INSERTED SOLUTION ARE NOT DUPLICATE. Would be silly ;)

	END IF;


        RETURN NULL;

    END;
$qapoll_maintain_votes_sums_2$ LANGUAGE plpgsql;


--
-- Used as a trigger in the qapoll_choice_solution_link table
-- This trigger will keep the integrity of the totalvotes qapoll_choice field, which contains the higher totalvotes of its solutions
--
CREATE OR REPLACE FUNCTION qapoll_maintain_votes_sums_3() RETURNS TRIGGER AS $qapoll_maintain_votes_sums_3$
    DECLARE
    	choice RECORD;
    BEGIN

        -- Work out the increment/decrement amount(s).
        IF (TG_OP = 'DELETE') THEN
		-- SHOULD NEVER OCCUR

        ELSIF (TG_OP = 'UPDATE') THEN
		IF(OLD.status != NEW.status OR OLD.advertize != NEW.advertize) THEN

			EXECUTE 'UPDATE qapoll_choice SET totalvotes = (SELECT COALESCE(MAX(qapoll_choice_solution.total_votes), 0)  
					FROM qapoll_choice_solution
					JOIN qapoll_choice_solution_link  ON qapoll_choice_solution_link.choicesolutionid = qapoll_choice_solution.id
					WHERE qapoll_choice_solution_link.choiceid = ' || NEW.choiceid || ' AND 
					qapoll_choice_solution.status = 1 AND 
					qapoll_choice_solution_link.status = 1 AND 
					qapoll_choice_solution_link.advertize = 1)  
				WHERE id=' || NEW.choiceid;



		END IF;
        ELSIF (TG_OP = 'INSERT') THEN

		EXECUTE 'UPDATE qapoll_choice SET totalvotes = (SELECT COALESCE(MAX(qapoll_choice_solution.total_votes), 0)  
				FROM qapoll_choice_solution
				JOIN qapoll_choice_solution_link  ON qapoll_choice_solution_link.choicesolutionid = qapoll_choice_solution.id
				WHERE qapoll_choice_solution_link.choiceid = ' || NEW.choiceid || ' AND 
				qapoll_choice_solution.status = 1 AND 
				qapoll_choice_solution_link.status = 1 AND 
				qapoll_choice_solution_link.advertize = 1)  
			WHERE id=' || NEW.choiceid;

	END IF;


        RETURN NULL;

    END;
$qapoll_maintain_votes_sums_3$ LANGUAGE plpgsql;


CREATE TRIGGER qapoll_maintain_votes_sums
AFTER INSERT OR UPDATE OR DELETE ON qapoll_vote
    FOR EACH ROW EXECUTE PROCEDURE qapoll_maintain_votes_sums();

CREATE TRIGGER qapoll_maintain_votes_sums_2
AFTER INSERT OR UPDATE OR DELETE ON qapoll_choice_solution
    FOR EACH ROW EXECUTE PROCEDURE qapoll_maintain_votes_sums_2();

CREATE TRIGGER qapoll_maintain_votes_sums_3
AFTER INSERT OR UPDATE OR DELETE ON qapoll_choice_solution_link
    FOR EACH ROW EXECUTE PROCEDURE qapoll_maintain_votes_sums_3();

--UPDATE qapoll_vote SET value=-1 WHERE id=981947;
--INSERT INTO qapoll_vote(choicesolutionid, userid, date, value) VALUES (76987, 234, NOW(), 0);
--DELETE FROM qapoll_vote WHERE id=981948;



ALTER TABLE qapoll_choice ADD COLUMN searchable_index_col tsvector;
ALTER TABLE qapoll_choice ADD COLUMN solutionssearchable_index_col tsvector;
ALTER TABLE qapoll_choice ADD COLUMN totalsearchable_index_col tsvector;
UPDATE qapoll_choice SET searchable_index_col =
     setweight(to_tsvector('pg_catalog.english', coalesce(title,'')), 'A') || ' ' ||  setweight(to_tsvector('pg_catalog.english', coalesce(description,'')), 'D');

ALTER TABLE qapoll_choice_solution ADD COLUMN searchable_index_col tsvector;
UPDATE qapoll_choice_solution SET searchable_index_col =
     setweight(to_tsvector('pg_catalog.english', coalesce(title,'')), 'A') || ' ' ||  setweight(to_tsvector('pg_catalog.english', coalesce(description,'')), 'D');

--
-- Update the index of the title + description, for a fast and efficient search!!
--
CREATE OR REPLACE FUNCTION update_searchable_index_trigger() RETURNS trigger AS $$
begin
  new.searchable_index_col :=
     setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') ||
     setweight(to_tsvector('pg_catalog.english', coalesce(new.description,'')), 'D');
  new.totalsearchable_index_col = new.searchable_index_col || new.solutionssearchable_index_col;
  
  return new;
end
$$ LANGUAGE plpgsql;

CREATE TRIGGER qapoll_choice_update_searchable_index BEFORE INSERT OR UPDATE
ON qapoll_choice FOR EACH ROW EXECUTE PROCEDURE update_searchable_index_trigger();


--
-- Update the index of the title + description of the solution, plus all the corresponding choice
-- Used as a trigger on qapoll_choice_solution
--
CREATE OR REPLACE FUNCTION update_solution_searchable_index_trigger() RETURNS trigger AS $update_solution_searchable_index_trigger$
DECLARE

	totalsearchindex tsvector;

BEGIN
	new.searchable_index_col :=
		setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') ||
		setweight(to_tsvector('pg_catalog.english', coalesce(new.description,'')), 'D');

	return new;
end
$update_solution_searchable_index_trigger$ LANGUAGE plpgsql;

CREATE TRIGGER qapoll_choice_solution_update_searchable_index BEFORE INSERT OR UPDATE
ON qapoll_choice_solution FOR EACH ROW EXECUTE PROCEDURE update_solution_searchable_index_trigger();


--
-- When we create a link, update the pre-hashed index of the title + description of the solution with their solution
--
CREATE OR REPLACE FUNCTION update_searchable_index_on_link_change_trigger() RETURNS trigger AS $update_searchable_index_on_link_change_trigger$
DECLARE
	choice RECORD;
	choicesolution RECORD;
	totalsearchindex tsvector;
	updatesearchchoiceindex integer;
	affectedchoice integer;
	affectedchoice2 integer;
BEGIN
	updatesearchchoiceindex = 1;
	affectedchoice = 0;
	affectedchoice2 = 0;

        IF (TG_OP = 'DELETE') THEN
		updatesearchchoiceindex = 1;
		affectedchoice = OLD.choiceid;

        ELSIF (TG_OP = 'UPDATE') THEN

		IF(OLD.choiceid != NEW.choiceid OR OLD.choicesolutionid != NEW.choicesolutionid OR OLD.status != NEW.status) THEN
			updatesearchchoiceindex = 1;
			affectedchoice = OLD.choiceid;
			affectedchoice2 = NEW.choiceid;

		END IF;

        ELSIF (TG_OP = 'INSERT') THEN
		updatesearchchoiceindex = 1;
		affectedchoice = NEW.choiceid;

	END IF;

	IF (updatesearchchoiceindex = 1) THEN

		-- Update the searchable index field of the affected ideas.

		IF(affectedchoice != 0) THEN
			totalsearchindex = '';
        
			FOR choicesolution IN SELECT * FROM qapoll_choice_solution
				JOIN qapoll_choice_solution_link ON qapoll_choice_solution_link.choicesolutionid = qapoll_choice_solution.id
				WHERE choiceid = affectedchoice AND qapoll_choice_solution_link.status = 1
			LOOP
				totalsearchindex = totalsearchindex || choicesolution.searchable_index_col;

			END LOOP;


			UPDATE qapoll_choice SET solutionssearchable_index_col =   totalsearchindex::tsvector WHERE id= affectedchoice;
			UPDATE qapoll_choice SET totalsearchable_index_col = searchable_index_col || solutionssearchable_index_col WHERE id= affectedchoice;
		END IF;

		IF(affectedchoice2 != 0) THEN
			totalsearchindex = '';

			FOR choicesolution IN SELECT * FROM qapoll_choice_solution
				JOIN qapoll_choice_solution_link ON qapoll_choice_solution_link.choicesolutionid = qapoll_choice_solution.id
				WHERE choiceid = affectedchoice2 AND qapoll_choice_solution_link.status = 1
			LOOP
				totalsearchindex = totalsearchindex || choicesolution.searchable_index_col;

			END LOOP;

			UPDATE qapoll_choice SET solutionssearchable_index_col =   totalsearchindex::tsvector WHERE id=affectedchoice2;
			UPDATE qapoll_choice SET totalsearchable_index_col = searchable_index_col || solutionssearchable_index_col WHERE id=affectedchoice2;
		END IF;

	END IF;

	RETURN NULL;
end
$update_searchable_index_on_link_change_trigger$ LANGUAGE plpgsql;



CREATE TRIGGER qapoll_choice_solution_link_update_searchable_index AFTER INSERT OR UPDATE OR DELETE
ON qapoll_choice_solution_link FOR EACH ROW EXECUTE PROCEDURE update_searchable_index_on_link_change_trigger();

CREATE INDEX qapoll_choice_textsearch_idx ON qapoll_choice USING gin(totalsearchable_index_col);





--
-- Break the duplicate loops, make all the dups in one level
--
CREATE OR REPLACE FUNCTION qapoll_move_choice_dup_one_one_level() RETURNS integer AS $$
DECLARE
    choice RECORD;
    choiceparent RECORD;
    dupid integer;
    count integer;
BEGIN
    RAISE NOTICE 'Beginning moving the duplicate to one single level';

   FOR choice IN SELECT * FROM qapoll_choice ORDER BY id LOOP

	IF (choice.duplicatenumber != -1) THEN
		RAISE NOTICE 'Idea #% is a dup', choice.id;

		-- Search for the real parent
		SELECT * FROM qapoll_choice WHERE id=choice.duplicatenumber INTO choiceparent;

		count = 0;
		WHILE(choiceparent.duplicatenumber != -1 AND choiceparent.duplicatenumber != choice.id AND count < 200) LOOP
			dupid = choiceparent.duplicatenumber;
			SELECT * FROM qapoll_choice WHERE id=dupid INTO choiceparent;
			count = count + 1;
		END LOOP;

		IF(choiceparent.duplicatenumber = choice.id OR COUNT > 199)
		THEN
			--Duplicate loop. Since choice.id is the first, cut it here
			RAISE NOTICE 'Found a loop. Idea #% is now unmarked duplicate', choice.id;
			UPDATE qapoll_choice SET duplicatenumber = -1
				WHERE id=choice.id;
		ELSE
			--Found the real parent.
			RAISE NOTICE 'Idea #% is now marked duplicate of Idea #%', choice.id, choiceparent.id;
			UPDATE qapoll_choice SET duplicatenumber = choiceparent.id
				WHERE id=choice.id;
		END IF;
		

	END IF;


    END LOOP;

    RAISE NOTICE 'Done.';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;
SELECT qapoll_move_choice_dup_one_one_level();


--
-- Remove all pending dup reports
--
DELETE FROM qapoll_choice_duplicate_report
WHERE status = 0;




CREATE OR REPLACE FUNCTION add_default_solution_for_every_choice() RETURNS integer AS $$
DECLARE
    choice RECORD;
    choice_solution RECORD;
    choicesolutionid integer;
    votes integer[3];
    votessum integer;
    --totalvotessum integer;
    --totalvotes integer[3];
BEGIN
    RAISE NOTICE 'Beginning adding a default solution for every existing idea';

   FOR choice IN SELECT * FROM qapoll_choice ORDER BY id LOOP
	RAISE NOTICE 'Adding default solution for idea #%', choice.id;

	--SELECT qapoll_compute_number_of_votes(choice.id, 1) INTO totalvotes;
	--totalvotessum := totalvotes[1] - totalvotes[3];
	SELECT qapoll_compute_number_of_votes(choice.id, 10) INTO votes;
	votessum := votes[1] - votes[3];
	--EXECUTE 'SELECT COALESCE(SUM(qapoll_vote.value),0) FROM qapoll_vote WHERE choicesolutionid=' || choice.id INTO votes;

	EXECUTE 'INSERT INTO qapoll_choice_solution(id, duplicate_choice_solution_id, title, userid, date, description, solution_votes, solution_plus_votes, solution_equal_votes, solution_minus_votes, total_votes, total_plus_votes, total_equal_votes, total_minus_votes) ' ||
		'VALUES (' || choice.id || ', ' || -1 || 
		', ''Auto-generated solution of idea #' || choice.id || ''', ' || choice.userid || ', ''' || choice.date ||
		''', ''<i>Ubuntu Brainstorm was updated in January 2009. Since the idea #' ||
		choice.id || ' was submitted before this update, its rationale and solution are not separated. Please vote accordingly, and if you have the necessary rights, please separate the rationale from the solution.</i><br /> Thanks!'', ' ||
		votessum || ', ' || votes[1] || ', ' || votes[2] || ', ' || votes[3] || ', ' || votessum || ', ' || votes[1] || ', ' ||
		votes[2] || ', ' || votes[3] ||
		')';

	EXECUTE 'INSERT INTO qapoll_choice_solution_link(choiceid, choicesolutionid, userid) ' ||
		'VALUES (' || choice.id || ', ' || choice.id || ', ' || choice.userid || ')';
    END LOOP;


   FOR choice_solution IN SELECT * FROM qapoll_choice_solution ORDER BY id LOOP
	RAISE NOTICE 'Setting up the duplicate info for the default solution #%', choice_solution.id;
	-- Now set the duplicate number. We do it as an update, as the sum coherence triggers do not take care of dup links in the INSERT queries
	EXECUTE 'UPDATE qapoll_choice_solution SET duplicate_choice_solution_id=COALESCE((SELECT link2.choicesolutionid FROM qapoll_choice_solution_link LEFT JOIN qapoll_choice ON qapoll_choice.id = qapoll_choice_solution_link.choiceid   LEFT JOIN qapoll_choice_solution_link AS link2 ON link2.choiceid = qapoll_choice.duplicatenumber WHERE qapoll_choice_solution_link.choicesolutionid = ' || choice_solution.id || '), -1) WHERE id=' || choice_solution.id;
   END LOOP;

    RAISE NOTICE 'Done.';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;
SELECT add_default_solution_for_every_choice();




SELECT setval('qapoll_choice_solution_id_seq', (SELECT MAX(id) FROM qapoll_choice), true);




CREATE TABLE qapoll_report
(
   id serial, 
   itemid integer, 
   report_type integer, 
   item_type integer, 
   status integer NOT NULL DEFAULT 1, 
   votes integer, 
   approver_id integer,
   date timestamp without time zone,
    PRIMARY KEY (id)
);

CREATE TABLE qapoll_report_vote
(
   id serial, 
   userid integer, 
   itemid integer, 
   item_type integer, 
   report_type integer, 
   votevalue integer DEFAULT 1, 
   date timestamp without time zone,
    PRIMARY KEY (id)
);

INSERT INTO qapoll_entry_point_menu_entry(name, url, menu_id, user_id, status, ordering, permission_needed)
	VALUES('Process spam reports', '/process_spam_reports/', '7', '-1', '0', '1', ':process_report');
INSERT INTO qapoll_entry_point_menu_entry(name, url, menu_id, user_id, status, ordering, permission_needed)
	VALUES('Process offensive reports', '/process_offensive_reports/', '7', '-1', '0', '3', ':process_report');
INSERT INTO qapoll_entry_point_menu_entry(name, url, menu_id, user_id, status, ordering, permission_needed)
	VALUES('Process in dev reports', '/process_indev_reports/', '7', '-1', '0', '4', ':process_report');
INSERT INTO qapoll_entry_point_menu_entry(name, url, menu_id, user_id, status, ordering, permission_needed)
	VALUES('Process implemented reports', '/process_implemented_reports/', '7', '-1', '0', '5', ':process_report');
INSERT INTO qapoll_entry_point_menu_entry(name, url, menu_id, user_id, status, ordering, permission_needed)
	VALUES('Process irrelevance reports', '/process_irrelevance_reports/', '7', '-1', '0', '2', ':process_report');
UPDATE qapoll_entry_point_menu_entry SET ordering=100 WHERE id=23;
UPDATE qapoll_entry_point_menu_entry SET permission_needed=':process_report' WHERE id=9;


--
-- Migration from qapoll to ideatorrent
--

UPDATE system SET filename='modules/ideatorrent/ideatorrent.module', name='ideatorrent', description='IdeaTorrent Open Innovation module'
	WHERE filename='modules/qapoll/qapoll.module';
UPDATE menu SET path='ideatorrent', title='IdeaTorrent'
	WHERE path='qapoll' AND title='Poll';

TRUNCATE TABLE cache_menu;

UPDATE qawebsite_module_setting SET module='ideatorrent'
	WHERE module='qapoll';

--
-- End of migration
--

INSERT INTO qawebsite_module_setting(siteid, module, option, value)
	VALUES(6, 'ideatorrent', 'choice_number_approvals_needed', '2');
INSERT INTO qawebsite_module_setting(siteid, module, option, value)
	VALUES(6, 'ideatorrent', 'selected_theme', 'ubuntu_brainstorm');
INSERT INTO qawebsite_module_setting(siteid, module, option, value)
	VALUES(6, 'ideatorrent', 'theme_ubuntu_brainstorm_choice_solution_max_visible_lines', '20');
INSERT INTO qawebsite_module_setting(siteid, module, option, value)
	VALUES(6, 'ideatorrent', 'theme_ubuntu_brainstorm_show_choice_attachments', '1');
INSERT INTO qawebsite_module_setting(siteid, module, option, value)
	VALUES(6, 'ideatorrent', 'theme_ubuntu_brainstorm_project_name', 'Ubuntu');
UPDATE qawebsite_module_setting SET option='theme_ubuntu_brainstorm_item_description_auth_tags'
	WHERE option='item_description_auth_tags';
UPDATE qawebsite_module_setting SET option='theme_ubuntu_brainstorm_item_comment_auth_tags'
	WHERE option='item_comment_auth_tags';
UPDATE qawebsite_module_setting SET option='theme_ubuntu_brainstorm_entry_point_desc_auth_tags'
	WHERE option='entry_point_desc_auth_tags';
UPDATE qawebsite_module_setting SET siteid=6
	WHERE id >= 20 AND id <= 23;

UPDATE qapoll_entry_point_menu_entry SET url='/ideatorrent_admin/' WHERE url='/qapoll_admin';

UPDATE qapoll_poll_relation SET name='Nautilus', url_name='nautilus' WHERE id=39;


INSERT INTO qawebsite_module_setting(siteid, module, option, value)
	VALUES(6, 'ideatorrent', 'theme_ubuntu_brainstorm_submit_idea_first_part_description', '<br />

<table style="width:100%">
<tr>
<td style="padding-right:10px">
<img src="/$theme_path$/images/package.png" alt="package"/>
</td>
<td style="font-size:1.3em; padding-top:3px; padding-bottom:3px">
Are you requesting a new package to be included in Ubuntu? Brainstorm is <b>not</b> the right place for it! See the new package request guide.
</td>
<td style="width:1%; padding-left:10px">

	<b class="postbutton">
	<b class="postbutton1"><b></b></b>
	<b class="postbutton2"><b></b></b>
	<b class="postbutton3"></b>
	<b class="postbutton4"></b>
	<b class="postbutton5"></b></b>
	<div class="postbutton_main" style="padding-left:15px; padding-right:15px">

	<a href="https://wiki.ubuntu.com/UbuntuDevelopment/NewPackages" class="blacklink2" style="font-size:17px; padding-left:2px; white-space:nowrap">
	See the guide &raquo;
	</a>


	</div>
	<b class="postbutton">
	<b class="postbutton5"></b>
	<b class="postbutton4"></b>
	<b class="postbutton3"></b>
	<b class="postbutton2"><b></b></b>
	<b class="postbutton1"><b></b></b></b>


</td>

<tr>
<td style="padding-right:10px">
<img src="/$theme_path$/images/bigbug.png" alt="bug"/>
</td>
<td style="font-size:1.3em; padding-top:3px; padding-bottom:3px">
Are you reporting something that is not working as it should be? Brainstorm is <b>not</b> the right place for it! You should file a bug report on Launchpad, the Ubuntu bug tracker.
</td>
<td style="width:1%; padding-left:10px">

	<b class="postbutton">
	<b class="postbutton1"><b></b></b>
	<b class="postbutton2"><b></b></b>
	<b class="postbutton3"></b>
	<b class="postbutton4"></b>
	<b class="postbutton5"></b></b>
	<div class="postbutton_main" style="padding-left:15px; padding-right:15px">

	<a href="https://launchpad.net/ubuntu/+filebug" class="blacklink2" style="font-size:17px; padding-left:2px; white-space:nowrap">
	File a bug report &raquo;
	</a>


	</div>
	<b class="postbutton">
	<b class="postbutton5"></b>
	<b class="postbutton4"></b>
	<b class="postbutton3"></b>
	<b class="postbutton2"><b></b></b>
	<b class="postbutton1"><b></b></b></b>


</td>


</table>

<br />

<div style="font-size:1.3em">
If your request does not belong to the above categories, you can submit it as an idea in Ubuntu Brainstorm.
</div>');

UPDATE qawebsite_user_setting SET module='ideatorrent' WHERE module='qapoll';


CREATE TABLE qapoll_choice_log
(
  id serial NOT NULL,
  choiceid integer,
  userid integer,
  date timestamp without time zone,
  change integer,
  old_value text,
  new_value text,
  CONSTRAINT qapoll_choice_log_pkey PRIMARY KEY (id)
);

INSERT INTO qawebsite_module_setting(siteid, module, option, value)
	VALUES(6, 'ideatorrent', 'start_page', '');

ALTER TABLE qapoll_choice_log ADD COLUMN choicesolutionlinkid integer DEFAULT -1;
ALTER TABLE qapoll_choice_log ADD COLUMN old_value2 text;
ALTER TABLE qapoll_choice_log ADD COLUMN new_value2 text;

INSERT INTO qapoll_poll_relation(relation_category_id, name, url_name, description, ordering)
	VALUES('4', 'Mythbuntu', 'mythbuntu', 'The Mythbuntu distribution', 0);

INSERT INTO qapoll_poll_relation_subcategory(relationid, name, description, url_name, ordering)
	VALUES (151, 'Artwork', 'Mythbuntu Artwork.', 'artwork', 0);
INSERT INTO qapoll_poll_relation_subcategory(relationid, name, description, url_name, ordering)
	VALUES (151, 'Hardware Support', 'Hardware support in Mythbuntu.', 'hardware_support', 0);
INSERT INTO qapoll_poll_relation_subcategory(relationid, name, description, url_name, ordering)
	VALUES (151, 'Additional software', '', 'additional_software', 0);

INSERT INTO qapoll_poll_relation(relation_category_id, name, url_name, description, ordering)
	VALUES('2', 'Phoronix test suite', 'phoronix-test-suite', 'The Phoronix test suite', 0);
INSERT INTO qapoll_poll_relation(relation_category_id, name, url_name, description, ordering)
	VALUES('4', 'Ubuntu muslim edition', 'ubuntume', 'The Ubuntu muslim edition', 0);



UPDATE qawebsite_site_module SET status = 0 WHERE path = 'http://pkgstatus.qa.ubuntu.com';


INSERT INTO qawebsite_module_setting(siteid, module, option, value)
	VALUES(6, 'ideatorrent', 'interval_between_idea_submissions', '1');
INSERT INTO qawebsite_module_setting(siteid, module, option, value)
	VALUES(6, 'ideatorrent', 'interval_between_solution_submissions', '5');