DROP TABLE Friend
;

DROP TABLE User
;

DROP TABLE Domain
;


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

CREATE TABLE User (
UserID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
UserName VARCHAR(256) NOT NULL,
Domain_DomainID INTEGER NOT NULL
)
;

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, 'doogle.invalid' );
INSERT INTO Domain (DomainID, DomainName) VALUES (2, 'kuumaposti.invalid' );
INSERT INTO Domain (DomainID, DomainName) VALUES (3, 'foobar.invalid' );
INSERT INTO Domain (DomainID, DomainName) VALUES (4, 'yliopisto.invalid' );


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

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

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

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

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

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

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (7, 'esko.mikkola', 2);

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

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

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

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

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (12, 'maija.meikäläinen', 3);

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

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

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

INSERT INTO User (UserID, UserName, Domain_DomainID) VALUES (16, 'elena.saavi', 4);