DROP TABLE Friend
;

DROP TABLE User
;

DROP TABLE Domain
;


CREATE TABLE Domain (
DomainID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
DomainName VARCHAR(100) NOT NULL UNIQUE
)
;

CREATE TABLE User (
UserID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
UserName VARCHAR(100) NOT NULL,
Domain_DomainID INTEGER NOT NULL,
CONSTRAINT user_uniq UNIQUE(UserName, Domain_DomainID)
)
;

CREATE TABLE Friend (
UserID INTEGER NOT NULL,
FriendID INTEGER NOT NULL,
PRIMARY KEY (UserID, FriendID)
)
;

-- Koska SQLite ei toteuta viite-eheyksiä toteutetaan ne itse
-- triggereiden avulla
CREATE TRIGGER fki_user_domain_id
BEFORE INSERT ON User
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT DomainID FROM Domain WHERE DomainID = NEW.Domain_DomainID) IS NULL)
     THEN RAISE(ABORT, 'INSERT on table "User" violates foreign key constraint "fki_user_domain_id"')
  END;
END;

CREATE TRIGGER fki_friend_userid
BEFORE INSERT ON Friend
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT UserID FROM User WHERE UserID = NEW.UserID) IS NULL)
     THEN RAISE(ABORT, 'INSERT on table "Friend" violates foreign key constraint "fki_friend_userid"')
  END;
END;

CREATE TRIGGER fki_friend_friendid
BEFORE INSERT ON Friend
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT UserID FROM User WHERE UserID = NEW.FriendID) IS NULL)
     THEN RAISE(ABORT, 'INSERT on table "Friend" violates foreign key constraint "fki_friend_friendid"')
  END;
END;

CREATE TRIGGER fku_friend_userid
BEFORE UPDATE ON Friend
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT UserID FROM User WHERE UserID = NEW.UserID) IS NULL)
     THEN RAISE(ABORT, 'UPDATE on table "Friend" violates foreign key constraint "fku_friend_userid"')
  END;
END;

CREATE TRIGGER fku_friend_friendid
BEFORE UPDATE ON Friend
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT UserID FROM User WHERE UserID = NEW.FriendID) IS NULL)
     THEN RAISE(ABORT, 'UPDATE on table "Friend" violates foreign key constraint "fku_friend_friendid"')
  END;
END;

CREATE TRIGGER fku_user_domain_id
BEFORE UPDATE ON User
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT DomainID FROM Domain WHERE DomainID = NEW.Domain_DomainID) IS NULL)
     THEN RAISE(ABORT, 'UPDATE on table "User" violates foreign key constraint "fku_user_domain_id"')
  END;
END;

CREATE TRIGGER fkd_domain_user_id
BEFORE DELETE ON Domain
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT Domain_DomainID FROM User WHERE Domain_DomainID = OLD.DomainID) IS NOT NULL)
     THEN RAISE(ABORT, 'DELETE on table "Domain" violates foreign key constraint "fkd_domain_user_id"')
  END;
END;

INSERT INTO Domain (DomainID, DomainName) VALUES (1, 'kuukkeli.invalid' );
INSERT INTO Domain (DomainID, DomainName) VALUES (2, 'viileamaili.invalid' );
INSERT INTO Domain (DomainID, DomainName) VALUES (3, 'teinix.invalid' );
INSERT INTO Domain (DomainID, DomainName) VALUES (4, 'muuh.invalid' );


INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (1, 'je_maria89', 1);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (2, 'aakoistinen', 1);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (3, 'smtp15', 1);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (4, 'sam_wise', 1);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (5, 'einari.makelainen', 1);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (6, 'mikkomm', 2);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (7, 'mikko.eskola', 2);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (8, 'essi-maaria90', 2);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (9, 'esa.kivela', 2);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (10, 'rami.wartsila', 2);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (11, 'ulla_p', 3);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (12, 'matti.kiviniemi', 3);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (13, 'gerhard.lahtinen', 3);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (14, 'xiao88', 3);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (15, 'helvi.kuusisto', 4);

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (16, 'helena.saukko', 4);