.timeout 30000

ATTACH DATABASE '/etc/pihole/gravity.db' AS OLD;

BEGIN TRANSACTION;

DROP TRIGGER tr_domainlist_add;
DROP TRIGGER tr_client_add;
DROP TRIGGER tr_adlist_add;

INSERT OR REPLACE INTO "group" SELECT * FROM OLD."group";
INSERT OR REPLACE INTO domain_audit SELECT * FROM OLD.domain_audit;

INSERT OR REPLACE INTO domainlist SELECT * FROM OLD.domainlist;
INSERT OR REPLACE INTO domainlist_by_group SELECT * FROM OLD.domainlist_by_group;

INSERT OR REPLACE INTO adlist SELECT * FROM OLD.adlist;
INSERT OR REPLACE INTO adlist_by_group SELECT * FROM OLD.adlist_by_group;

INSERT OR REPLACE INTO info SELECT * FROM OLD.info;

INSERT OR REPLACE INTO client SELECT * FROM OLD.client;
INSERT OR REPLACE INTO client_by_group SELECT * FROM OLD.client_by_group;


CREATE TRIGGER tr_domainlist_add AFTER INSERT ON domainlist
    BEGIN
      INSERT INTO domainlist_by_group (domainlist_id, group_id) VALUES (NEW.id, 0);
      INSERT INTO domainlist_by_group (domainlist_id, group_id) VALUES (NEW.id, 1);
    END;

CREATE TRIGGER tr_client_add AFTER INSERT ON client
    BEGIN
      INSERT INTO client_by_group (client_id, group_id) VALUES (NEW.id, 0);
    END;

CREATE TRIGGER tr_adlist_add AFTER INSERT ON adlist
    BEGIN
      INSERT INTO adlist_by_group (adlist_id, group_id) VALUES (NEW.id, 0);
      INSERT INTO adlist_by_group (adlist_id, group_id) VALUES (NEW.id, 1);
    END;

CREATE TRIGGER tr_group_add AFTER INSERT ON "group"
	BEGIN
		INSERT INTO domainlist_by_group SELECT domainlist.id AS domainlist_id, new_group.group_id
											FROM (
													SELECT domainlist.id
													FROM domainlist
													JOIN domainlist_by_group
														ON domainlist_by_group.domainlist_id = domainlist.id
													WHERE group_id = 0 and domainlist.enabled = 1
												) AS domainlist
											LEFT JOIN
												(
													SELECT "group".id AS group_id
													FROM "group"
													WHERE "group".id NOT IN
													(
														SELECT group_id
														FROM domainlist_by_group
													)
												) AS new_group;
		INSERT INTO adlist_by_group SELECT adlist.id AS adlist_id, new_group.group_id
											FROM (
													SELECT adlist.id
													FROM adlist
													JOIN adlist_by_group
														ON adlist_by_group.adlist_id = adlist.id
													WHERE group_id = 0 and adlist.enabled = 1
												) AS adlist
											LEFT JOIN
												(
													SELECT "group".id AS group_id
													FROM "group"
													WHERE "group".id NOT IN
													(
														SELECT group_id
														FROM adlist_by_group
													)
												) AS new_group;
	END;
COMMIT;