CREATE TABLE accounts ( account INTEGER PRIMARY KEY );
CREATE TABLE systems ( system INTEGER PRIMARY KEY );
CREATE TABLE policies ( policy INTEGER PRIMARY KEY );

CREATE TABLE policy_accounts (
	policy INTEGER NOT NULL REFERENCES policies,
	account INTEGER NOT NULL REFERENCES accounts
);
CREATE TABLE accounts_on_hold (
	account INTEGER NOT NULL REFERENCES accounts
);
CREATE TABLE policy_accounts_active (
	policy INTEGER NOT NULL REFERENCES policies,
	account INTEGER NOT NULL REFERENCES accounts
);
CREATE TABLE policy_hamlet_atoms (
	policy INTEGER NOT NULL REFERENCES policies,
	system INTEGER NOT NULL REFERENCES systems
);
CREATE TABLE account_instances (
	account INTEGER NOT NULL REFERENCES accounts,
	system INTEGER NOT NULL REFERENCES systems,
	last_atom_timestamp TIMESTAMP DEFAULT NULL
);

CREATE RULE executor_active AS
ON INSERT TO policy_accounts
DO INSERT INTO policy_accounts_active (account, policy) 
 SELECT account, policy FROM policy_accounts
  WHERE account = NEW.account
   AND account NOT IN 
    (SELECT account FROM accounts_on_hold WHERE account = NEW.account)
   AND (account, policy) NOT IN
    (SELECT account, policy FROM policy_accounts_active);

CREATE RULE executor_hamlet AS
ON INSERT TO policy_accounts_active
DO INSERT INTO account_instances (account, system)
 SELECT NEW.account, system
  FROM policy_hamlet_atoms
  WHERE policy = NEW.policy;

-- The actual test

INSERT INTO policies VALUES (1);
INSERT INTO accounts VALUES (11);
INSERT INTO systems VALUES (21);

INSERT INTO policy_hamlet_atoms VALUES (1, 21);
INSERT INTO policy_accounts VALUES (1, 11);

SELECT * FROM policy_accounts_active;
SELECT * FROM account_instances;

DELETE FROM policy_accounts_active;
INSERT INTO policy_accounts_active VALUES (1,11);

SELECT * FROM policy_accounts_active;
SELECT * FROM account_instances;
