-- 0

CREATE TABLE Sunken_City
(Name VARCHAR(50),
 Country VARCHAR(4),
 Province VARCHAR(50),
 Population DECIMAL CONSTRAINT CityPop
   CHECK (Population >= 0),
 Latitude DECIMAL CONSTRAINT CityLat
   CHECK ((Latitude >= -90) AND (Latitude <= 90)) ,
 Longitude DECIMAL CONSTRAINT CityLon
   CHECK ((Longitude >= -180) AND (Longitude <= 180)) ,
 Elevation DECIMAL ,
 Sinking_Date DATE,
 CONSTRAINT SunkenCityKey PRIMARY KEY (Name, Country, Province));

-- 1

CREATE TABLE Params (Key VARCHAR(50) PRIMARY KEY, Val INT);
INSERT INTO Params VALUES ('Sea level', 0);

CREATE FUNCTION sea_level(INT) RETURNS INT AS $$
INSERT INTO Sunken_City
SELECT City.*, NOW() -- ewentualnie z jakimś extract
  FROM City WHERE Elevation < $1;
DELETE FROM City WHERE Elevation < $1;
DELETE FROM Airport WHERE Elevation < $1;
UPDATE Airport SET City = NULL, Province = NULL, Country = NULL
  WHERE (City, Province, Country) NOT IN (SELECT Name, Province, Country FROM City);
UPDATE Params SET Val = $1 WHERE Key = 'Sea level';
SELECT $1; 
$$ LANGUAGE sql;

-- 2

CREATE FUNCTION utop_nowe() RETURNS TRIGGER AS $$
BEGIN
IF NEW.elevation < (SELECT Val FROM Params WHERE Key = 'Sea level') THEN
  INSERT INTO Sunken_City VALUES
    (NEW.Name, New.Country, New.Province, New.Population, New.Latitude, New.Longitude, New.elevation, NOW());
  RETURN NULL;
ELSE
  RETURN NEW;
END IF;
END
$$ LANGUAGE plpgsql;

CREATE FUNCTION utop_stare() RETURNS TRIGGER AS $$
BEGIN
IF NEW.elevation < (SELECT Val FROM Params WHERE Key = 'Sea level') THEN
  INSERT INTO Sunken_City VALUES
    (NEW.Name, New.Country, New.Province, New.Population, New.Latitude, New.Longitude, New.elevation, NOW());
  DELETE FROM City WHERE (Name, Province, Country) = (OLD.Name, OLD.Province, OLD.Country);
  RETURN NULL;
ELSE
  RETURN NEW;
END IF;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER utop_nowe_miasto BEFORE INSERT ON City FOR EACH ROW
EXECUTE PROCEDURE utop_nowe();
CREATE TRIGGER utop_stare_miasto BEFORE UPDATE ON City FOR EACH ROW
EXECUTE PROCEDURE utop_stare();