CREATE TABLE invoke.Users
(
  UsersID BIGSERIAL,
  UserName character varying(100),
  UsersPassword character varying(100),
  UsersEmail character varying(120),
  UsersCountry character varying(2000),
  UsersGender character varying(50),
  /*UserDateOfBirth int tuka date da sa dodede koja igra e kupena od koja godina na korisnici */

  CONSTRAINT pk_UsersID PRIMARY KEY (UsersID)
);


CREATE TABLE invoke.Costomers
(
  UsersID bigint references invoke.Users(UsersID),
  CostomersUserName character varying(120),

  constraint pkCostomers primary key (UsersID,CostomersUserName)
);

CREATE TABLE invoke.Admins
(
  UsersID bigint references invoke.Users(UsersID),
  AdminUserName character varying(120),
  AdminsName character varying(100),
  AdminsSurName character varying(100),
  AdminsPhone bigint,

  constraint pkAdmins primary key (UsersID,AdminsUserName)
);

CREATE TABLE invoke.ItemArtist
(
  UsersID bigint references invoke.Users(UsersID),
  ItemArtistUserName character varying(120),
  ItemArtistName character varying(120),
  ItemArtistSurName character varying(120),

  constraint pkItemArtist primary key (UsersID,ItemArtistUserName)
);

CREATE TABLE invoke.CreditCards
(
  CardsID BIGSERIAL,
  UsersID bigint references invoke.Users(UsersID),
  CustomerUserName character varying(120),
  CardsNumber character varying(120),
  CardsCVCode integer,
  CardsCardholderName character varying(120),
  CardsCardholderSurName character varying(120),
  CardsExpirationDate date,
  CardsType character varying(120),

  CONSTRAINT pk_CardsID PRIMARY KEY (CardsID),

  CONSTRAINT "FK_UsersID" FOREIGN KEY (UsersID)

  REFERENCES invoke.Users(UsersID) MATCH SIMPLE

  ON UPDATE NO ACTION ON DELETE NO ACTION
);


CREATE TABLE invoke.Games
(
  GamesName character varying(150),
  GamesDescription character varying(2000),
  GamesPrice integer NOT NULL,
  GamesReleaseDate date,
  GamesDeveloper character varying(120),
  GamesGenre character varying(120),


  CONSTRAINT pk_GamesName PRIMARY KEY (GamesName)
);



CREATE TABLE invoke.WorkShops
(
  WorkShopsID BIGSERIAL primary key,
  WorkShopsName character varying(200) unique,
  GamesName character varying(150) references invoke.Games(GamesName)
);
CREATE TABLE invoke.Items
(
  ItemsID BIGSERIAL primary key,
  ItemsName character varying(120),
  ItemsType character varying(150),
  ItemsDescription character varying(2000),
  ItemsPrice integer NOT NULL,
  GamesName character varying(150) references invoke.Games(GamesName),
  UsersID bigint,
  ItemArtistUserName character varying(150),
  WorkShopsID bigint references invoke.WorkShops(WorkShopsID),

  constraint fkItemArtist foreign key (UsersID,ItemArtistUserName)
  references invoke.ItemArtist(UsersID,ItemArtistUserName)
);
CREATE TABLE invoke.Inventorys
(
  InventorysID BIGSERIAL primary key,
  UsersID bigint references invoke.Users(UsersID),
  ItemsID bigint references invoke.Items(ItemsID),
  InventorysDateWithTime timestamptz

);
CREATE TABLE invoke.Forums
(
  ForumsID BIGSERIAL primary key,
  ForumsName character varying(200) unique,
  ForumsDateWithTime timestamptz,
  GamesName character varying(150) references invoke.Games(GamesName)
);

CREATE TABLE invoke.Topics
(
  TopicsID BIGSERIAL primary key,
  UsersID bigint references invoke.Users(UsersID),
  ForumsID bigint references invoke.Forums(ForumsID),
  TopicsName character varying(200),
  TopicsDescription character varying(2000),
  TopicsDateWithTime timestamptz
);
CREATE TABLE invoke.Comments (
  CommentsID BIGSERIAL primary key,
  CommentsContent character varying(10000),
  CommentsDateWithTime timestamptz,
  UsersID bigint references invoke.Users(UsersID),
  TopicsID bigint references invoke.Topics(TopicsID)
);


CREATE TABLE invoke.Achievements
(
  AchievementsID BIGSERIAL primary key,
  UsersID bigint references invoke.Users(UsersID),
  AchievementsTitle character varying(200),
  GamesName character varying(150) references invoke.Games(GamesName)
);

  CREATE TABLE invoke.Friendship
(
  FriendshipID BIGSERIAL primary key,
  UsersID1 bigint references invoke.Users(UsersID),
  UsersID2 bigint references invoke.Users(UsersID),
  DateStared date
);
ALTER TABLE invoke.Friendship
  ADD COLUMN "FriendshipAccepted" BOOLEAN NOT NULL DEFAULT FALSE;



CREATE TABLE invoke.BoughtGames
(
  BoughtGamesID BIGSERIAL primary key,
  UsersID bigint references invoke.Users(UsersID),
  GamesName character varying(150) references invoke.Games(GamesName),
  UNIQUE(UsersID,GamesName)
);
CREATE TABLE invoke.PurchaseGames
(
  PurchaseGamesID BIGSERIAL primary key,
  CardsID bigint references invoke.CreditCards(CardsID),
  GamesName character varying(150) references invoke.Games(GamesName),
  DateOfPurchaseGames timestamptz,
  UNIQUE(CardsID,GamesName,DateOfPurchaseGames)
);
CREATE TABLE invoke.PurchaseItems
(
  PurchaseItemsID BIGSERIAL primary key,
  CardsID bigint references invoke.CreditCards(CardsID),
  GamesName character varying(150) references invoke.Games(GamesName),
  ItemsID bigint references invoke.Items(ItemsID),
  DateOfPurchaseItems timestamptz,
  UNIQUE(CardsID,ItemsID,DateOfPurchaseItems)
);
CREATE TABLE invoke.SetsGames
(
  SetsGamesID BIGSERIAL primary key,
  UsersID bigint,
  AdminsUserName character varying(150),
  GamesName character varying(150) references invoke.Games(GamesName),
  SetGamesDate date,

  constraint fkAdmins foreign key (UsersID,AdminsUserName)
  references invoke.Admins(UsersID,AdminsUserName)
);

CREATE TABLE invoke.VoteForItem
(
  WorkShopsID bigint references invoke.WorkShops(WorkShopsID),
  UsersID bigint references invoke.Users(UsersID),
  ItemsID bigint references invoke.Items(ItemsID),

  constraint pkVoteForItem primary key (WorkShopsID,UsersID,ItemsID)
);

CREATE TABLE invoke.SellingItems /*Ova e Marketut*/
(
  SellingItemsID BIGSERIAL primary key,
  ItemsID bigint references invoke.Items(ItemsID),
  GamesName character varying(150) references invoke.Games(GamesName),
  ItemsQuantity int
);


CREATE TABLE invoke.EarnedAchievements
(
  UsersID bigint references invoke.Users(UsersID),
  AchievementsID bigint references invoke.Achievements(AchievementsID),
  DateEarned date,
  constraint pkEarnedAchievements primary key (UsersID,AchievementsID,DateEarned)
);

/*Users*/

INSERT INTO invoke.Users (UserName,UsersPassword,UsersEmail,UsersCountry,UsersGender)
  VALUES ('IgorNikolov','IgorPassword1','igor.nikoloov@gmail.com','Makedonija','M');
INSERT INTO invoke.Users (UserName,UsersPassword,UsersEmail,UsersCountry,UsersGender)
  VALUES ('IgorNikolovvv','IgorPassword2','igor.nikoloov@gmail.com','Makedonija','M');
INSERT INTO invoke.Users (UserName,UsersPassword,UsersEmail,UsersCountry,UsersGender)
  VALUES ('PavlinkaUser','PavlinkaPassword2','pavlinka@gmail.com','Makedonija','F');
INSERT INTO invoke.Users (UserName,UsersPassword,UsersEmail,UsersCountry,UsersGender)
  VALUES ('RistePopovski','RistePass','riste.popovski@gmail.com','Makedonija','M');

/*Costomers*/
INSERT INTO invoke.Costomers (UsersID,CostomersUserName) VALUES (1,'IgorNikolov');
INSERT INTO invoke.Costomers (UsersID,CostomersUserName) VALUES (2,'IgorNikolovvv');
INSERT INTO invoke.Costomers (UsersID,CostomersUserName) VALUES (4,'RistePopovski');

/*Admins*/
INSERT INTO invoke.Admins (UsersID,AdminsUserName,AdminsName,AdminsSurName,AdminsPhone)
VALUES (1,'IgorNikolov','Igor','Nikolov','072525010');

/*ItemArtist*/
INSERT INTO invoke.ItemArtist (UsersID,ItemArtistUserName,ItemArtistName,ItemArtistSurName) VALUES (2,'IgorNikolovvv','Igor','Nikolov');
INSERT INTO invoke.ItemArtist (UsersID,ItemArtistUserName,ItemArtistName,ItemArtistSurName) VALUES (3,'PavlinkaUser','Pavlinka','Atanasova');

/*CreditCards*/
INSERT INTO invoke.creditcards
(cardsid, usersid, customerusername, cardsnumber, cardscvcode, cardscardholdername, cardscardholdersurname, cardsexpirationdate, cardstype)
VALUES(nextval('invoke.creditcards_cardsid_seq'::regclass), 1, 'IgorNikolov', '123456', 1234, 'Igor', 'Nikolov', to_date('20.11.2016','DD.MM.YYYY'), 'Maestro');

INSERT INTO invoke.creditcards
(cardsid, usersid, customerusername, cardsnumber, cardscvcode, cardscardholdername, cardscardholdersurname, cardsexpirationdate, cardstype)
VALUES(nextval('invoke.creditcards_cardsid_seq'::regclass), 2, 'IgorNikolovvv', '1234567', 1234, 'Igor', 'Nikolovvv', to_date('16.11.2016','DD.MM.YYYY'), 'Visa');

INSERT INTO invoke.creditcards
(cardsid, usersid, customerusername, cardsnumber, cardscvcode, cardscardholdername, cardscardholdersurname, cardsexpirationdate, cardstype)
VALUES(nextval('invoke.creditcards_cardsid_seq'::regclass), 3, 'PavlinkaAtanasova', '12345678', 1235, 'Pavlinka', 'Atanasova', to_date('20.11.2016','DD.MM.YYYY'), 'Visa');

/*Games*/
INSERT INTO invoke.games
(gamesname, gamesdescription, gamesprice, gamesreleasedate, gamesdeveloper, gamesgenre)
VALUES('Dota2', 'Dota2 > League Of Legends', 10, to_date('16.11.2010','DD.MM.YYYY'), 'Valve', 'MOBA');

INSERT INTO invoke.games
(gamesname, gamesdescription, gamesprice, gamesreleasedate, gamesdeveloper, gamesgenre)
VALUES('League Of Legends', 'League Of Legends < Dota2', 10, to_date('16.11.2008','DD.MM.YYYY'), 'RIOT', 'MOBA');

INSERT INTO invoke.games
(gamesname, gamesdescription, gamesprice, gamesreleasedate, gamesdeveloper, gamesgenre)
VALUES('CSGO', 'Test test', 5, to_date('16.11.2010','DD.MM.YYYY'), 'Valve', 'FPS');

INSERT INTO invoke.games
(gamesname, gamesdescription, gamesprice, gamesreleasedate, gamesdeveloper, gamesgenre)
VALUES('HON', 'Test Test', 5, to_date('16.11.2010','DD.MM.YYYY'),'S2 Games', 'MOBA');

/*WorkShops*/
INSERT INTO invoke.workshops
(workshopsid, workshopsname, gamesname)
VALUES(nextval('invoke.workshops_workshopsid_seq'::regclass), 'Dota2Badges', 'Dota2');
INSERT INTO invoke.workshops
(workshopsid, workshopsname, gamesname)
VALUES(nextval('invoke.workshops_workshopsid_seq'::regclass), 'Dota2 Tshirt', 'Dota2');
INSERT INTO invoke.workshops
(workshopsid, workshopsname, gamesname)
VALUES(nextval('invoke.workshops_workshopsid_seq'::regclass), 'CSGO Guns', 'CSGO');

/*Items*/
INSERT INTO invoke.items
(itemsid, itemsname, itemstype, itemsdescription, itemsprice, gamesname, usersid, itemartistusername, workshopsid)
VALUES(nextval('invoke.items_itemsid_seq'::regclass), 'AWP', 'Rare', 'Test', 3, 'CSGO', 3, 'PavlinkaUser', 3);
INSERT INTO invoke.items
(itemsid, itemsname, itemstype, itemsdescription, itemsprice, gamesname, usersid, itemartistusername, workshopsid)
VALUES(nextval('invoke.items_itemsid_seq'::regclass), 'AWP2', 'Epic', 'TestTest', 3, 'CSGO',3, 'PavlinkaUser', 3);
INSERT INTO invoke.items
(itemsid, itemsname, itemstype, itemsdescription, itemsprice, gamesname, usersid, itemartistusername, workshopsid)
VALUES(nextval('invoke.items_itemsid_seq'::regclass), 'Invoker Head', 'Rare', 'blabla', 3, 'Dota2', 2, 'IgorNikolovvv', 1);

INSERT INTO invoke.items
(itemsid, itemsname, itemstype, itemsdescription, itemsprice, gamesname, usersid, itemartistusername, workshopsid)
VALUES(nextval('invoke.items_itemsid_seq'::regclass), 'Invoker Chest', 'Rare', 'blabla', 3, 'Dota2', 2, 'IgorNikolovvv', 1);

/*Inventorys*/
INSERT INTO invoke.inventorys
(inventorysid, usersid, itemsid, inventorysdatewithtime)
VALUES(nextval('invoke.inventorys_inventorysid_seq'::regclass), 1, 1, NOW());
INSERT INTO invoke.inventorys
(inventorysid, usersid, itemsid, inventorysdatewithtime)
VALUES(nextval('invoke.inventorys_inventorysid_seq'::regclass), 1, 2, NOW());




/*Forums*/
-- i tuka ne sa znae
INSERT INTO invoke.forums
(forumsid, forumsname, forumsdatewithtime, gamesname)
VALUES(nextval('invoke.forums_forumsid_seq'::regclass), 'Dota2 General Discussions', NOW(), 'Dota2');
INSERT INTO invoke.forums
(forumsid, forumsname, forumsdatewithtime, gamesname)
VALUES(nextval('invoke.forums_forumsid_seq'::regclass), 'Dota2 Bug Reports', NOW(), 'Dota2');



/*Topics*/

INSERT INTO invoke.topics
(topicsid, usersid, forumsid, topicsname, topicsdescription, topicsdatewithtime)
VALUES(nextval('invoke.topics_topicsid_seq'::regclass), 1, 1,'Dota2 General Discussions', 'Voa e za se PogChamp', NOW());
INSERT INTO invoke.topics
(topicsid, usersid, forumsid, topicsname, topicsdescription, topicsdatewithtime)
VALUES(nextval('invoke.topics_topicsid_seq'::regclass), 1, 1,'CSGO General Discussions', 'TestTest PogChamp', NOW());
INSERT INTO invoke.topics
(topicsid, usersid, forumsid, topicsname, topicsdescription, topicsdatewithtime)
VALUES(nextval('invoke.topics_topicsid_seq'::regclass), 1, 1,'Topic3', 'Voa e za se PogChamp', NOW());
INSERT INTO invoke.topics
(topicsid, usersid, forumsid, topicsname, topicsdescription, topicsdatewithtime)
VALUES(nextval('invoke.topics_topicsid_seq'::regclass), 2, 2,'Topic3', 'Voa e za se PogChamp', NOW());


/*Comments*/
INSERT INTO invoke.comments
(commentsid, commentscontent, commentsdatewithtime, usersid, topicsid)
VALUES(nextval('invoke.comments_commentsid_seq'::regclass), 'Dali Ognen e Noob?', NOW(), 1, 1);
INSERT INTO invoke.comments
(commentsid, commentscontent, commentsdatewithtime, usersid, topicsid)
VALUES(nextval('invoke.comments_commentsid_seq'::regclass), 'Test Test Test', NOW(), 2, 2);
INSERT INTO invoke.comments
(commentsid, commentscontent, commentsdatewithtime, usersid, topicsid)
VALUES(nextval('invoke.comments_commentsid_seq'::regclass), 'commenttest', NOW(), 2, 4);
INSERT INTO invoke.comments
(commentsid, commentscontent, commentsdatewithtime, usersid, topicsid)
VALUES(nextval('invoke.comments_commentsid_seq'::regclass), 'commenttest', NOW(), 1, 1);


/*Achievements*/
INSERT INTO invoke.achievements
(achievementsid, usersid, achievementstitle, gamesname)
VALUES(nextval('invoke.achievements_achievementsid_seq'::regclass), 1, 'Kappa', 'Dota2');
INSERT INTO invoke.achievements
(achievementsid, usersid, achievementstitle, gamesname)
VALUES(nextval('invoke.achievements_achievementsid_seq'::regclass), 1, 'You can play Dota2 now!', 'League Of Legends');


/*Friendship*/
INSERT INTO invoke.friendship
(friendshipid,usersid1, usersid2, datestared, "FriendshipAccepted")
VALUES(nextval('invoke.friendship_friendshipid_seq'::regclass), 1, 2, NOW(), false);
INSERT INTO invoke.friendship
(friendshipid,usersid1, usersid2, datestared, "FriendshipAccepted")
VALUES(nextval('invoke.friendship_friendshipid_seq'::regclass), 1, 3, NOW(), true);
INSERT INTO invoke.friendship
(friendshipid,usersid1, usersid2, datestared, "FriendshipAccepted")
VALUES(nextval('invoke.friendship_friendshipid_seq'::regclass), 2, 3, NOW(), true);

/*BoughtGames*/
INSERT INTO invoke.boughtgames
(boughtgamesid,usersid, gamesname)
VALUES(nextval('invoke.boughtgames_boughtgamesid_seq'::regclass) ,1, 'Dota2');
INSERT INTO invoke.boughtgames
(boughtgamesid,usersid, gamesname)
VALUES(nextval('invoke.boughtgames_boughtgamesid_seq'::regclass) ,1, 'CSGO');
INSERT INTO invoke.boughtgames
(boughtgamesid,usersid, gamesname)
VALUES(nextval('invoke.boughtgames_boughtgamesid_seq'::regclass) ,2, 'Dota2');
INSERT INTO invoke.boughtgames
(boughtgamesid,usersid, gamesname)
VALUES(nextval('invoke.boughtgames_boughtgamesid_seq'::regclass) ,1, 'League Of Legends');
INSERT INTO invoke.boughtgames
(boughtgamesid,usersid, gamesname)
VALUES(nextval('invoke.boughtgames_boughtgamesid_seq'::regclass) ,3, 'Dota2');
INSERT INTO invoke.boughtgames
(boughtgamesid,usersid, gamesname)
VALUES(nextval('invoke.boughtgames_boughtgamesid_seq'::regclass) ,4, 'Dota2');


/*PurchaseGames*/
INSERT INTO invoke.purchasegames
(purchasegamesid, cardsid, gamesname, dateofpurchasegames)
VALUES(nextval('invoke.purchasegames_purchasegamesid_seq'::regclass), 1, 'Dota2', NOW());
INSERT INTO invoke.purchasegames
(purchasegamesid, cardsid, gamesname, dateofpurchasegames)
VALUES(nextval('invoke.purchasegames_purchasegamesid_seq'::regclass), 1, 'CSGO', NOW());
INSERT INTO invoke.purchasegames
(purchasegamesid, cardsid, gamesname, dateofpurchasegames)
VALUES(nextval('invoke.purchasegames_purchasegamesid_seq'::regclass), 4, 'Dota2', NOW());

/*PurchaseItems*/
INSERT INTO invoke.purchaseitems
(purchaseitemsid, cardsid, gamesname, itemsid, dateofpurchaseitems)
VALUES(nextval('invoke.purchaseitems_purchaseitemsid_seq'::regclass), 1, 'Dota2', 1, NOW());
INSERT INTO invoke.purchaseitems
(purchaseitemsid, cardsid, gamesname, itemsid, dateofpurchaseitems)
VALUES(nextval('invoke.purchaseitems_purchaseitemsid_seq'::regclass), 1, 'Dota2', 2, NOW());
INSERT INTO invoke.purchaseitems
(purchaseitemsid, cardsid, gamesname, itemsid, dateofpurchaseitems)
VALUES(nextval('invoke.purchaseitems_purchaseitemsid_seq'::regclass), 4, 'CSGO', 4, NOW());

/*SetsGames*/
INSERT INTO invoke.setsgames
(setsgamesid, usersid, adminsusername, gamesname, setgamesdate)
VALUES(nextval('invoke.setsgames_setsgamesid_seq'::regclass), 1, 'IgorNikolov', 'Dota2', NOW());
INSERT INTO invoke.setsgames
(setsgamesid, usersid, adminsusername, gamesname, setgamesdate)
VALUES(nextval('invoke.setsgames_setsgamesid_seq'::regclass), 1, 'IgorNikolov', 'CSGO', NOW());


/*VoteForItem*/
INSERT INTO invoke.voteforitem
(workshopsid, usersid, itemsid)
VALUES(1, 1, 1);
INSERT INTO invoke.voteforitem
(workshopsid, usersid, itemsid)
VALUES(1, 2, 1);
INSERT INTO invoke.voteforitem
(workshopsid, usersid, itemsid)
VALUES(1, 3, 1);
INSERT INTO invoke.voteforitem
(workshopsid, usersid, itemsid)
VALUES(1, 1, 2);

/*SellingItems*//*Voa e Marketut*/
INSERT INTO invoke.sellingitems
(sellingitemsid, itemsid, gamesname, itemsquantity)
VALUES(nextval('invoke.sellingitems_sellingitemsid_seq'::regclass), 1, 'Dota2', 10);
INSERT INTO invoke.sellingitems
(sellingitemsid, itemsid, gamesname, itemsquantity)
VALUES(nextval('invoke.sellingitems_sellingitemsid_seq'::regclass), 2, 'Dota2', 10);
INSERT INTO invoke.sellingitems
(sellingitemsid, itemsid, gamesname, itemsquantity)
VALUES(nextval('invoke.sellingitems_sellingitemsid_seq'::regclass), 3, 'Dota2', 10);


/*EarnedAchievements*/
INSERT INTO invoke.earnedachievements
(usersid, achievementsid, dateearned)
VALUES(1, 1, NOW());