CREATE TABLE user ( 
nick varchar UNIQUE NOT NULL COLLATE NOCASE PRIMARY KEY, 
hostmask varchar NOT NULL, 
releaseCount integer DEFAULT 0 
);

CREATE TABLE network ( 
channel varchar UNIQUE NOT NULL COLLATE NOCASE, 
network varchar UNIQUE NOT NULL COLLATE NOCASE, 
PRIMARY KEY(channel, network) 
);

CREATE TABLE category ( 
categoryName varchar UNIQUE NOT NULL COLLATE NOCASE PRIMARY KEY, 
releasecount integer DEFAULT 0, 
nukeCount integer DEFAULT 0 
);

CREATE TABLE pre ( 
releaseName varchar UNIQUE NOT NULL COLLATE NOCASE PRIMARY KEY, 
category varchar NOT NULL CONSTRAINT fk_precategory REFERENCES category(categoryName), 
pretime integer NOT NULL, 
releaseGroup varchar UNIQUE NOT NULL COLLATE NOCASE, 
userID integer NOT NULL CONSTRAINT fk_preuser REFERENCES user(ROWID), 
networkID integer NOT NULL CONSTRAINT fk_prenetwork REFERENCES network(ROWID), 
nukeID integer
);

CREATE TABLE delpre ( 
releaseName varchar NOT NULL PRIMARY KEY, 
category varchar NOT NULL, 
pretime integer NOT NULL, 
releaseGroup varchar NOT NULL, 
userID integer NOT NULL, 
networkID integer NOT NULL, 
nukeID integer 
);

CREATE TABLE nuke ( 
releaseName varchar UNIQUE NOT NULL COLLATE NOCASE CONSTRAINT fk_nukepre REFERENCES pre(releaseName) ON DELETE CASCADE, 
nuketime integer NOT NULL, 
nukeReason varchar NOT NULL, 
userID integer NOT NULL CONSTRAINT fk_nukeuser REFERENCES user(ROWID), 
networkID integer NOT NULL CONSTRAINT fk_nukenetwork REFERENCES network(ROWID), 
PRIMARY KEY(releaseName, nukeReason) 
);

CREATE TABLE unnuke ( 
releaseName varchar UNIQUE NOT NULL COLLATE NOCASE CONSTRAINT fk_unnukepre REFERENCES pre(releaseName) ON DELETE CASCADE, 
unnuketime integer NOT NULL, 
unnukeReason varchar NOT NULL, 
userID integer NOT NULL CONSTRAINT fk_unnukeuser REFERENCES user(ROWID), 
networkID integer NOT NULL CONSTRAINT fk_unnukenetwork REFERENCES network(ROWID), 
PRIMARY KEY(releaseName, unnukeReason) 
);

CREATE TABLE info ( 
releaseName varchar UNIQUE NOT NULL COLLATE NOCASE CONSTRAINT fk_infopre REFERENCES pre(releaseName) ON DELETE CASCADE, 
releaseSize integer, 
fileCount integer, 
userID integer NOT NULL CONSTRAINT fk_infouser REFERENCES user(ROWID), 
networkID integer NOT NULL CONSTRAINT fk_infonetwork REFERENCES network(ROWID), 
PRIMARY KEY(releaseName) 
);

CREATE TABLE stats ( 
groupName varchar UNIQUE NOT NULL COLLATE NOCASE PRIMARY KEY, 
releaseCount integer DEFAULT 0, 
nukeCount integer DEFAULT 0 
);

-- Foreign Key Preventing insert
CREATE TRIGGER fki_pre_category_category_categoryName
BEFORE INSERT ON [pre]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "pre" violates foreign key constraint "fki_pre_category_category_categoryName"')
  WHERE (SELECT categoryName FROM category WHERE categoryName = NEW.category) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_pre_category_category_categoryName
BEFORE UPDATE ON [pre]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "pre" violates foreign key constraint "fku_pre_category_category_categoryName"')
      WHERE (SELECT categoryName FROM category WHERE categoryName = NEW.category) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_pre_category_category_categoryName
BEFORE DELETE ON category
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "category" violates foreign key constraint "fkd_pre_category_category_categoryName"')
  WHERE (SELECT category FROM pre WHERE category = OLD.categoryName) IS NOT NULL;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_pre_userID_user_ROWID
BEFORE INSERT ON [pre]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "pre" violates foreign key constraint "fki_pre_userID_user_ROWID"')
  WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_pre_userID_user_ROWID
BEFORE UPDATE ON [pre]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "pre" violates foreign key constraint "fku_pre_userID_user_ROWID"')
      WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_pre_userID_user_ROWID
BEFORE DELETE ON user
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "user" violates foreign key constraint "fkd_pre_userID_user_ROWID"')
  WHERE (SELECT userID FROM pre WHERE userID = OLD.ROWID) IS NOT NULL;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_pre_networkID_network_ROWID
BEFORE INSERT ON [pre]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "pre" violates foreign key constraint "fki_pre_networkID_network_ROWID"')
  WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_pre_networkID_network_ROWID
BEFORE UPDATE ON [pre]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "pre" violates foreign key constraint "fku_pre_networkID_network_ROWID"')
      WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_pre_networkID_network_ROWID
BEFORE DELETE ON network
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "network" violates foreign key constraint "fkd_pre_networkID_network_ROWID"')
  WHERE (SELECT networkID FROM pre WHERE networkID = OLD.ROWID) IS NOT NULL;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_nuke_releaseName_pre_releaseName
BEFORE INSERT ON [nuke]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "nuke" violates foreign key constraint "fki_nuke_releaseName_pre_releaseName"')
  WHERE (SELECT releaseName FROM pre WHERE releaseName = NEW.releaseName) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_nuke_releaseName_pre_releaseName
BEFORE UPDATE ON [nuke]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "nuke" violates foreign key constraint "fku_nuke_releaseName_pre_releaseName"')
      WHERE (SELECT releaseName FROM pre WHERE releaseName = NEW.releaseName) IS NULL;
END;

-- Cascading Delete
CREATE TRIGGER fkdc_nuke_releaseName_pre_releaseName
BEFORE DELETE ON pre
FOR EACH ROW BEGIN
    DELETE FROM nuke WHERE nuke.releaseName = OLD.releaseName;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_nuke_userID_user_ROWID
BEFORE INSERT ON [nuke]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "nuke" violates foreign key constraint "fki_nuke_userID_user_ROWID"')
  WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_nuke_userID_user_ROWID
BEFORE UPDATE ON [nuke]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "nuke" violates foreign key constraint "fku_nuke_userID_user_ROWID"')
      WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_nuke_userID_user_ROWID
BEFORE DELETE ON user
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "user" violates foreign key constraint "fkd_nuke_userID_user_ROWID"')
  WHERE (SELECT userID FROM nuke WHERE userID = OLD.ROWID) IS NOT NULL;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_nuke_networkID_network_ROWID
BEFORE INSERT ON [nuke]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "nuke" violates foreign key constraint "fki_nuke_networkID_network_ROWID"')
  WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_nuke_networkID_network_ROWID
BEFORE UPDATE ON [nuke]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "nuke" violates foreign key constraint "fku_nuke_networkID_network_ROWID"')
      WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_nuke_networkID_network_ROWID
BEFORE DELETE ON network
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "network" violates foreign key constraint "fkd_nuke_networkID_network_ROWID"')
  WHERE (SELECT networkID FROM nuke WHERE networkID = OLD.ROWID) IS NOT NULL;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_unnuke_releaseName_pre_releaseName
BEFORE INSERT ON [unnuke]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "unnuke" violates foreign key constraint "fki_unnuke_releaseName_pre_releaseName"')
  WHERE (SELECT releaseName FROM pre WHERE releaseName = NEW.releaseName) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_unnuke_releaseName_pre_releaseName
BEFORE UPDATE ON [unnuke]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "unnuke" violates foreign key constraint "fku_unnuke_releaseName_pre_releaseName"')
      WHERE (SELECT releaseName FROM pre WHERE releaseName = NEW.releaseName) IS NULL;
END;

-- Cascading Delete
CREATE TRIGGER fkdc_unnuke_releaseName_pre_releaseName
BEFORE DELETE ON pre
FOR EACH ROW BEGIN
    DELETE FROM unnuke WHERE unnuke.releaseName = OLD.releaseName;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_unnuke_userID_user_ROWID
BEFORE INSERT ON [unnuke]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "unnuke" violates foreign key constraint "fki_unnuke_userID_user_ROWID"')
  WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_unnuke_userID_user_ROWID
BEFORE UPDATE ON [unnuke]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "unnuke" violates foreign key constraint "fku_unnuke_userID_user_ROWID"')
      WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_unnuke_userID_user_ROWID
BEFORE DELETE ON user
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "user" violates foreign key constraint "fkd_unnuke_userID_user_ROWID"')
  WHERE (SELECT userID FROM unnuke WHERE userID = OLD.ROWID) IS NOT NULL;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_unnuke_networkID_network_ROWID
BEFORE INSERT ON [unnuke]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "unnuke" violates foreign key constraint "fki_unnuke_networkID_network_ROWID"')
  WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_unnuke_networkID_network_ROWID
BEFORE UPDATE ON [unnuke]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "unnuke" violates foreign key constraint "fku_unnuke_networkID_network_ROWID"')
      WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_unnuke_networkID_network_ROWID
BEFORE DELETE ON network
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "network" violates foreign key constraint "fkd_unnuke_networkID_network_ROWID"')
  WHERE (SELECT networkID FROM unnuke WHERE networkID = OLD.ROWID) IS NOT NULL;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_info_releaseName_pre_releaseName
BEFORE INSERT ON [info]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "info" violates foreign key constraint "fki_info_releaseName_pre_releaseName"')
  WHERE (SELECT releaseName FROM pre WHERE releaseName = NEW.releaseName) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_info_releaseName_pre_releaseName
BEFORE UPDATE ON [info]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "info" violates foreign key constraint "fku_info_releaseName_pre_releaseName"')
      WHERE (SELECT releaseName FROM pre WHERE releaseName = NEW.releaseName) IS NULL;
END;

-- Cascading Delete
CREATE TRIGGER fkdc_info_releaseName_pre_releaseName
BEFORE DELETE ON pre
FOR EACH ROW BEGIN
    DELETE FROM info WHERE info.releaseName = OLD.releaseName;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_info_userID_user_ROWID
BEFORE INSERT ON [info]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "info" violates foreign key constraint "fki_info_userID_user_ROWID"')
  WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_info_userID_user_ROWID
BEFORE UPDATE ON [info]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "info" violates foreign key constraint "fku_info_userID_user_ROWID"')
      WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_info_userID_user_ROWID
BEFORE DELETE ON user
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "user" violates foreign key constraint "fkd_info_userID_user_ROWID"')
  WHERE (SELECT userID FROM info WHERE userID = OLD.ROWID) IS NOT NULL;
END;

-- Foreign Key Preventing insert
CREATE TRIGGER fki_info_networkID_network_ROWID
BEFORE INSERT ON [info]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "info" violates foreign key constraint "fki_info_networkID_network_ROWID"')
  WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_info_networkID_network_ROWID
BEFORE UPDATE ON [info]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "info" violates foreign key constraint "fku_info_networkID_network_ROWID"')
      WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_info_networkID_network_ROWID
BEFORE DELETE ON network
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "network" violates foreign key constraint "fkd_info_networkID_network_ROWID"')
  WHERE (SELECT networkID FROM info WHERE networkID = OLD.ROWID) IS NOT NULL;
END;

CREATE TRIGGER statsCounter
AFTER INSERT ON pre
BEGIN
  INSERT INTO stats (groupName) SELECT new.releaseGroup WHERE new.releaseGroup NOT IN (SELECT groupName FROM stats);
  UPDATE stats SET releaseCount = releaseCount+1 WHERE groupName = new.releaseGroup;
  UPDATE stats SET releaseCount = releaseCount+1 WHERE groupName = 'COMPLETE_DB';
  UPDATE category SET releaseCount = releaseCount+1 WHERE categoryName = new.category;
  UPDATE user SET releaseCount = releaseCount+1 WHERE ROWID = new.userID;
END;

CREATE TRIGGER flag_nuke 
AFTER INSERT ON nuke
BEGIN
  UPDATE pre SET nukeID = new.ROWID WHERE releaseName = new.releaseName;
  UPDATE stats SET nukecount = nukeCount+1 WHERE groupName = (SELECT releaseGroup FROM pre WHERE releaseName = new.releaseName LIMIT 1);
  UPDATE stats SET nukeCount = nukeCount+1 WHERE groupName = 'COMPLETE_DB';
  UPDATE category SET nukecount = nukeCount+1 WHERE categoryName = (SELECT category FROM pre WHERE releaseName = new.releaseName LIMIT 1);
END;

CREATE TRIGGER unflag_nuke 
AFTER INSERT ON unnuke
BEGIN
  UPDATE pre SET nukeID = NULL WHERE releaseName = new.releaseName;
  UPDATE stats SET nukeCount = nukeCount-1 WHERE groupName = 'COMPLETE_DB';
  UPDATE stats SET nukeCount = nukeCount-1 WHERE groupName = (SELECT releaseGroup FROM pre WHERE releaseName = new.releaseName LIMIT 1);
  END;

CREATE TRIGGER recycleBin 
BEFORE DELETE ON pre
BEGIN
  INSERT INTO delpre VALUES ( old.releaseName, old.category, old.pretime, old.releaseGroup, old.userID, old.networkID, old.nukeID );
  UPDATE stats SET releasecount = releaseCount-1 WHERE groupName = 'COMPLETE_DB';
END;