-- psql -v ON_ERROR_STOP=1 -U postgres portal

-- Deletes everything to make this file re-runnable
\set QUIET true
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
\set QUIET false


-- Clients (_name_, email, phonenr)
CREATE TABLE Clients (
  name CHAR(20) PRIMARY KEY,
  email TEXT NOT NULL,
  phonenr INT NOT NULL CHECK (phonenr > 0));

-- Pets (_chipnr_, name, owner, type, born)
--   owner → Client.name
--   type ∈ {dog, cat, rabbit}
CREATE TABLE Pets (
  chipnr INT PRIMARY KEY,
  name TEXT NOT NULL,
  owner CHAR(20) NOT NULL REFERENCES Clients,
  type TEXT CHECK (type IN ('dog', 'cat', 'rabbit')),
  born INT NOT NULL
  CHECK (born >= 2000 AND born <= date_part('year', CURRENT_DATE)));

-- Vets (_id_, name, phonenr, specialisation)
--   specialisation ∈ {dog, cat, rabbit}
CREATE TABLE Vets (
  id INT PRIMARY KEY,
  name CHAR(20) NOT NULL,
  phonenr INT NOT NULL CHECK (phonenr > 0),
  specialisation TEXT 
  CHECK (specialisation IN ('dog', 'cat', 'rabbit')));

-- Bookings (emp, chipnr, bdate, time, length)
--   emp → Vets.id
--   chipnr → Pets.chipnr
--   time ∈ {9,10,11,13,14,15}
--   length ∈ {30,60}
--   UNIQUE (chipnr, bdate, time)
CREATE TABLE Bookings (
  emp INT REFERENCES Vets,
  chipnr INT REFERENCES Pets,
  bdate DATE,
  time INT NOT NULL CHECK (time in (9,10,11,13,14,15)),
  length INT NOT NULL DEFAULT 30 CHECK (length IN (30, 60)),
  PRIMARY KEY (emp,bdate,time),
  UNIQUE (chipnr, bdate, time) );


-- 3.a
CREATE OR REPLACE VIEW MultiplePets AS (
  SELECT Clients.name, phonenr
  FROM Clients, Pets
  WHERE Clients.name = owner
  GROUP BY Clients.name
  HAVING COUNT(Clients.name) > 1);


-- 3..b
CREATE OR REPLACE FUNCTION old_pets() RETURNS TRIGGER AS $$
DECLARE
  yborn INT;
  spec TEXT;
  ptype TEXT;
BEGIN
 yborn = (SELECT born FROM Pets WHERE chipnr = NEW.chipnr);
 spec = (SELECT specialisation FROM Vets WHERE id = NEW.emp);
 ptype = (SELECT type FROM Pets WHERE Pets.chipnr = NEW.chipnr);
 IF date_part('year', CURRENT_DATE) - yborn >= 5
 THEN IF spec = ptype
      THEN NEW.length = 60;
      ELSE RAISE EXCEPTION 'wong specialist';
      END IF;
 END IF;
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS book_old_pets ON Bookings;

CREATE TRIGGER book_old_pets
  BEFORE INSERT OR UPDATE ON Bookings
  FOR EACH ROW
  EXECUTE FUNCTION old_pets();


-- 3.c
CREATE OR REPLACE VIEW ThousandHours AS (
  SELECT name, specialisation
  FROM Vets, Bookings
  WHERE id = emp AND bdate <= CURRENT_DATE
  GROUP BY emp, name, specialisation
  HAVING SUM(length)/60 > 1000);
