
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
\set QUIET false


-- Ex 2

CREATE TABLE Customers (
  id INT PRIMARY KEY,
  name TEXT NOT NULL,
  isPrivate BOOLEAN NOT NULL,
  UNIQUE (id, isPrivate));                       -- because of being referenced


CREATE TABLE Subscriptions (
  phoneNumber CHAR(10) PRIMARY KEY,
  customer INT NOT NULL,
  isPrivate BOOLEAN NOT NULL,                                 -- added due to c
  plan TEXT CHECK (plan IN ('prepaid', 'flatrate', 'corporate')),       -- a
  fee INT CHECK (fee >= 0),                                             -- d
  balance INT NOT NULL,
  FOREIGN KEY (customer, isPrivate) REFERENCES Customers (id, isPrivate)
    ON DELETE CASCADE,                                                  -- e
  CHECK (plan = 'prepaid' OR balance = 0),                              -- b
  CHECK (NOT (isPrivate AND plan = 'corporate')));                      -- c
  -- alternative: CHECK (NOT isPrivate OR plan != 'corporate'),         -- alt c

/*
Note on solution to constraint c):

Because of the reference there is no harmful redundancy in the solution. 
For a given id, there is only one allowed isPrivate value so there can
be no update anomalies.

Achieving the same guarantee avoiding redundancy would require
multiple triggers since e.g. updating either of the two tables could
invalidate the constraint.
*/


-- d
CREATE OR REPLACE VIEW Billing AS
SELECT customer, name, SUM(fee) AS billing
FROM Subscriptions, Customers
WHERE id = customer
-- recall that what we select on should be a subset of what we group on
-- unless we use an agregation functions, hence we shuold also group by name
GROUP BY customer, name;


-- f
CREATE OR REPLACE FUNCTION delSubscription () RETURNS TRIGGER AS $$
BEGIN
  -- alternative
  -- IF 0 = (SELECT COUNT(*) FROM Subscriptions WHERE customer = OLD.customer)
  IF NOT EXISTS
     (SELECT * FROM Subscriptions WHERE customer = OLD.customer)
  THEN DELETE FROM Customers WHERE id = OLD.customer;
  END IF;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS deleteSubscription ON Subscriptions;

-- Will not work on BEFORE
CREATE TRIGGER deleteSubscription
AFTER DELETE ON Subscriptions
FOR EACH ROW
EXECUTE FUNCTION delSubscription ();


-- Some data
INSERT INTO Customers VALUES (1, 'Ana', true);
INSERT INTO Customers VALUES (2, 'Peter', false);
INSERT INTO Customers VALUES (3, 'Sanna', true);

INSERT INTO Subscriptions VALUES ('1234567890', 1, true, 'prepaid', 0, 10);
INSERT INTO Subscriptions VALUES ('1234567891', 1, true, 'flatrate', 20, 0);
INSERT INTO Subscriptions VALUES ('2345678901', 2, false, 'corporate', 30, 0);

-- Testing
SELECT * FROM Customers;
SELECT * FROM Billing;

-- Delete in Customers will delete in Subscriptions
DELETE FROM Customers WHERE id = 2;
SELECT * FROM Billing;

-- Deleting all subscription from a customer will delete de custumers as well
DELETE FROM Subscriptions WHERE phonenumber = '1234567891';
SELECT * FROM Customers;
SELECT * FROM Billing;
DELETE FROM Subscriptions WHERE phonenumber = '1234567890';
SELECT * FROM Customers;
