/*
Created: 2017-01-06
Modified: 2017-02-03
Model: MuOnline
Database: PostgreSQL 9.5
*/

-- Create roles section -------------------------------------------------

CREATE ROLE Guest
;

CREATE ROLE Uzytkownik LOGIN
;

CREATE ROLE GameMaster LOGIN
;

CREATE ROLE Administrator LOGIN
;

-- Create tables section -------------------------------------------------

-- Table Weapons

CREATE TABLE Weapons(
 ID Bigint NOT NULL,
 ilvl Smallint NOT NULL,
 type Smallint NOT NULL,
 mindmg Bigint NOT NULL,
 maxdmg Bigint NOT NULL,
 durability Bigint NOT NULL,
 reqlvl Smallint NOT NULL,
 speed Double precision NOT NULL,
 name Text NOT NULL
)
;

-- Add keys for table Weapons

ALTER TABLE Weapons ADD CONSTRAINT ID PRIMARY KEY (ID)
;

-- Table Armor

CREATE TABLE Armor(
 ID Bigint NOT NULL,
 ilvl Smallint NOT NULL,
 type Smallint NOT NULL,
 armour Bigint NOT NULL,
 reqlvl Smallint NOT NULL,
 class Smallint NOT NULL,
 name Text NOT NULL
)
;

-- Add keys for table Armor

ALTER TABLE Armor ADD CONSTRAINT Key1 PRIMARY KEY (ID)
;

-- Table Jewellery

CREATE TABLE Jewellery(
 ID Bigint NOT NULL,
 ilvl Smallint NOT NULL,
 type Smallint NOT NULL,
 reqlvl Smallint NOT NULL,
 power Bigint NOT NULL,
 name Text NOT NULL
)
;

-- Add keys for table Jewellery

ALTER TABLE Jewellery ADD CONSTRAINT Key2 PRIMARY KEY (ID)
;

-- Table CharacterEquipment

CREATE TABLE CharacterEquipment(
 ID Bigint NOT NULL,
 Weapon1 Bigint,
 Weapon2 Bigint,
 Helmet Bigint,
 Chest Bigint,
 Gloves Bigint,
 Belt Bigint,
 Boots Bigint,
 Necklace Bigint,
 Ring1 Bigint,
 Ring2 Bigint
)
;

-- Create indexes for table CharacterEquipment

CREATE INDEX IX_Relationship10 ON CharacterEquipment (Weapon1)
;

CREATE INDEX IX_Relationship11 ON CharacterEquipment (Weapon2)
;

CREATE INDEX IX_Relationship12 ON CharacterEquipment (Helmet)
;

CREATE INDEX IX_Relationship17 ON CharacterEquipment (Chest)
;

CREATE INDEX IX_Relationship18 ON CharacterEquipment (Gloves)
;

CREATE INDEX IX_Relationship19 ON CharacterEquipment (Belt)
;

CREATE INDEX IX_Relationship20 ON CharacterEquipment (Boots)
;

CREATE INDEX IX_Relationship21 ON CharacterEquipment (Necklace)
;

CREATE INDEX IX_Relationship22 ON CharacterEquipment (Ring1)
;

CREATE INDEX IX_Relationship23 ON CharacterEquipment (Ring2)
;

-- Add keys for table CharacterEquipment

ALTER TABLE CharacterEquipment ADD CONSTRAINT Key4 PRIMARY KEY (ID)
;

-- Table Account

CREATE TABLE Account(
 login Varchar NOT NULL,
 password Varchar NOT NULL,
 team Varchar DEFAULT user NOT NULL,
 email Text NOT NULL,
 regdate Date NOT NULL,
 status Boolean NOT NULL,
 warnings Bigint NOT NULL
)
;

-- Add keys for table Account

ALTER TABLE Account ADD CONSTRAINT Key5 PRIMARY KEY (login)
;

ALTER TABLE Account ADD CONSTRAINT Attribute1 UNIQUE (login)
;

-- Table CharacterList

CREATE TABLE CharacterList(
 ID Bigint NOT NULL,
 login Varchar NOT NULL,
 class Bigint NOT NULL,
 credate Date NOT NULL,
 locationID Bigint
)
;

-- Create indexes for table CharacterList

CREATE INDEX IX_Relationship29 ON CharacterList (login)
;

CREATE INDEX IX_Relationship7 ON CharacterList (locationID)
;

-- Add keys for table CharacterList

ALTER TABLE CharacterList ADD CONSTRAINT Key6 PRIMARY KEY (ID)
;

-- Table CharacterStats

CREATE TABLE CharacterStats(
 ID Bigint NOT NULL,
 Strenght Bigint NOT NULL,
 Dexterity Bigint NOT NULL,
 Stamina Bigint NOT NULL,
 Inteligence Bigint NOT NULL,
 Health Bigint NOT NULL,
 Mana Bigint NOT NULL,
 Armor Bigint NOT NULL,
 Resists Bigint NOT NULL,
 Dodge Bigint NOT NULL,
 Speed Bigint NOT NULL,
 MovementSpeed Bigint NOT NULL,
 MinDamage Bigint NOT NULL,
 MaxDamage Bigint NOT NULL,
 DPS Bigint NOT NULL
)
;

-- Add keys for table CharacterStats

ALTER TABLE CharacterStats ADD CONSTRAINT Key7 PRIMARY KEY (ID)
;

-- Table CharacterAchievements

CREATE TABLE CharacterAchievements(
 ID Bigint NOT NULL,
 date Date NOT NULL,
 achivID Bigint NOT NULL,
 status Bigint NOT NULL
)
;

-- Create indexes for table CharacterAchievements

CREATE INDEX IX_Relationship3 ON CharacterAchievements (achivID)
;

CREATE INDEX ON CharacterAchievements (ID)
;

-- Add keys for table CharacterAchievements

ALTER TABLE CharacterAchievements ADD CONSTRAINT Key8 PRIMARY KEY (ID)
;

-- Table Achievements

CREATE TABLE Achievements(
 achivID Bigint NOT NULL,
 Info Text NOT NULL
)
;

-- Add keys for table Achievements

ALTER TABLE Achievements ADD CONSTRAINT Key9 PRIMARY KEY (achivID)
;

-- Table CharacterQuests

CREATE TABLE CharacterQuests(
 ID Bigint NOT NULL,
 date Date NOT NULL,
 questID Bigint NOT NULL,
 status Bigint NOT NULL,
 achivID Bigint
)
;

-- Create indexes for table CharacterQuests

CREATE INDEX IX_Relationship2 ON CharacterQuests (achivID)
;

CREATE INDEX IX_Relationship4 ON CharacterQuests (questID)
;

-- Add keys for table CharacterQuests

ALTER TABLE CharacterQuests ADD CONSTRAINT Key10 PRIMARY KEY (ID)
;

-- Table Quests

CREATE TABLE Quests(
 questID Bigint NOT NULL,
 info Text NOT NULL
)
;

-- Add keys for table Quests

ALTER TABLE Quests ADD CONSTRAINT Key11 PRIMARY KEY (questID)
;

-- Table LocationLists

CREATE TABLE LocationLists(
 locationID Bigint NOT NULL,
 type Bigint NOT NULL,
 name Text NOT NULL,
 unlocked Bigint NOT NULL
)
;

-- Add keys for table LocationLists

ALTER TABLE LocationLists ADD CONSTRAINT Key12 PRIMARY KEY (locationID)
;

-- Table MonstersList

CREATE TABLE MonstersList(
 monsterID Bigint NOT NULL,
 locationID Bigint NOT NULL,
 name Text NOT NULL,
 type Bigint NOT NULL
)
;

-- Create indexes for table MonstersList

CREATE INDEX IX_Relationship8 ON MonstersList (locationID)
;

-- Add keys for table MonstersList

ALTER TABLE MonstersList ADD CONSTRAINT Key13 PRIMARY KEY (monsterID)
;

-- Table Monsters

CREATE TABLE Monsters(
 monsterID Bigint NOT NULL,
 life Bigint NOT NULL,
 damage Bigint NOT NULL,
 defence Bigint NOT NULL
)
;

-- Create indexes for table Monsters

CREATE INDEX IX_Relationship9 ON Monsters (monsterID)
;

-- Create views section -------------------------------------------------

CREATE VIEW Postac AS
SELECT CharacterList.ID, class, Weapon1, Weapon2, Helmet, Chest, Gloves, Belt, Boots, Necklace, Ring1, Ring2
FROM CharacterList, CharacterEquipment
WHERE CharacterList.ID = CharacterEquipment.ID
;

CREATE VIEW Statystyki AS
SELECT CharacterList.ID, Strenght, Dexterity, Stamina, Inteligence, Health, Mana, Armor, Resists, Dodge, Speed, MovementSpeed, MinDamage, MaxDamage, DPS
FROM CharacterList, CharacterStats
WHERE CharacterList.ID = CharacterStats.ID
;

CREATE VIEW Osiagniecia AS
SELECT CharacterList.ID, date, achivID, status
FROM CharacterList, CharacterAchievements
WHERE CharacterList.ID = CharacterAchievements.ID
;

CREATE VIEW Zadania AS
SELECT CharacterList.ID, date, questID, status
FROM CharacterList, CharacterQuests
WHERE CharacterList.ID = CharacterQuests.ID
;

CREATE VIEW Postaci AS
SELECT Account.login, ID, CharacterList.login, class, credate, locationID
FROM Account, CharacterList
WHERE Account.login = CharacterList.login
;

-- Create relationships section -------------------------------------------------

ALTER TABLE CharacterEquipment ADD FOREIGN KEY (Weapon1) REFERENCES Weapons (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterEquipment ADD FOREIGN KEY (Weapon2) REFERENCES Weapons (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterEquipment ADD FOREIGN KEY (Helmet) REFERENCES Armor (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterEquipment ADD FOREIGN KEY (Chest) REFERENCES Armor (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterEquipment ADD FOREIGN KEY (Gloves) REFERENCES Armor (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterEquipment ADD FOREIGN KEY (Belt) REFERENCES Armor (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterEquipment ADD FOREIGN KEY (Boots) REFERENCES Armor (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterEquipment ADD FOREIGN KEY (Necklace) REFERENCES Jewellery (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterEquipment ADD FOREIGN KEY (Ring1) REFERENCES Jewellery (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterEquipment ADD FOREIGN KEY (Ring2) REFERENCES Jewellery (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterList ADD FOREIGN KEY (login) REFERENCES Account (login) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterQuests ADD FOREIGN KEY (achivID) REFERENCES Achievements (achivID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterAchievements ADD FOREIGN KEY (achivID) REFERENCES Achievements (achivID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterQuests ADD FOREIGN KEY (questID) REFERENCES Quests (questID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterList ADD FOREIGN KEY (locationID) REFERENCES LocationLists (locationID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE MonstersList ADD FOREIGN KEY (locationID) REFERENCES LocationLists (locationID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE Monsters ADD FOREIGN KEY (monsterID) REFERENCES MonstersList (monsterID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterEquipment ADD FOREIGN KEY (ID) REFERENCES CharacterList (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterAchievements ADD FOREIGN KEY (ID) REFERENCES CharacterList (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterQuests ADD CONSTRAINT Relationship3 FOREIGN KEY (ID) REFERENCES CharacterList (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
;

ALTER TABLE CharacterStats ADD FOREIGN KEY (ID) REFERENCES CharacterList (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
;



-- Grant permissions section -------------------------------------------------

GRANT Guest TO Administrator
;
GRANT Guest TO GameMaster
;
GRANT Guest TO Uzytkownik
;
GRANT Uzytkownik TO Administrator
;
GRANT Uzytkownik TO GameMaster
;
GRANT GameMaster TO Administrator
;

GRANT SELECT ON Postac TO Uzytkownik
;
GRANT SELECT ON Postac TO GameMaster
;
GRANT INSERT ON Postac TO GameMaster
;
GRANT UPDATE ON Postac TO GameMaster
;
GRANT SELECT ON Postac TO Administrator
;
GRANT INSERT ON Postac TO Administrator
;
GRANT UPDATE ON Postac TO Administrator
;
GRANT DELETE ON Postac TO Administrator
;
GRANT REFERENCES ON Postac TO Administrator
;
GRANT TRIGGER ON Postac TO Administrator
;
GRANT SELECT ON Statystyki TO Uzytkownik
;
GRANT SELECT ON Statystyki TO GameMaster
;
GRANT INSERT ON Statystyki TO GameMaster
;
GRANT UPDATE ON Statystyki TO GameMaster
;
GRANT TRIGGER ON Statystyki TO Administrator
;
GRANT REFERENCES ON Statystyki TO Administrator
;
GRANT DELETE ON Statystyki TO Administrator
;
GRANT UPDATE ON Statystyki TO Administrator
;
GRANT INSERT ON Statystyki TO Administrator
;
GRANT SELECT ON Statystyki TO Administrator
;
GRANT SELECT ON Osiagniecia TO Uzytkownik
;
GRANT SELECT ON Osiagniecia TO GameMaster
;
GRANT INSERT ON Osiagniecia TO GameMaster
;
GRANT UPDATE ON Osiagniecia TO GameMaster
;
GRANT SELECT ON Osiagniecia TO Administrator
;
GRANT INSERT ON Osiagniecia TO Administrator
;
GRANT UPDATE ON Osiagniecia TO Administrator
;
GRANT DELETE ON Osiagniecia TO Administrator
;
GRANT REFERENCES ON Osiagniecia TO Administrator
;
GRANT SELECT ON Zadania TO Uzytkownik
;
GRANT SELECT ON Zadania TO GameMaster
;
GRANT INSERT ON Zadania TO GameMaster
;
GRANT UPDATE ON Zadania TO GameMaster
;
GRANT SELECT ON Zadania TO Administrator
;
GRANT INSERT ON Zadania TO Administrator
;
GRANT UPDATE ON Zadania TO Administrator
;
GRANT DELETE ON Zadania TO Administrator
;
GRANT REFERENCES ON Zadania TO Administrator
;
GRANT TRIGGER ON Zadania TO Administrator
;
GRANT SELECT ON Postaci TO Uzytkownik
;
GRANT SELECT ON Postaci TO GameMaster
;
GRANT INSERT ON Postaci TO GameMaster
;
GRANT UPDATE ON Postaci TO GameMaster
;
GRANT TRIGGER ON Postaci TO Administrator
;
GRANT REFERENCES ON Postaci TO Administrator
;
GRANT DELETE ON Postaci TO Administrator
;
GRANT UPDATE ON Postaci TO Administrator
;
GRANT INSERT ON Postaci TO Administrator
;