/*Procedures*/
CREATE FUNCTION CreateNewUser (	c_username varchar,c_email varchar , c_standing boolean, c_password varchar) RETURNS VOID AS $$
BEGIN
INSERT INTO mmo_user(username, email,standing, password) VALUES ( c_username,  c_email,c_standing, c_password);
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION a_create (a_name varchar, a_amount int, a_description varchar) RETURNS VOID AS $$
BEGIN
INSERT INTO achievement(name, point_amount, description) VALUES (a_name,  a_amount, a_description);
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION a_update (a_id int, a_name varchar, a_amount int, a_description varchar) RETURNS VOID AS $$
BEGIN
UPDATE achievement SET name = a_name, point_amount = a_amount, description = a_description WHERE achievement_id = a_id;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION ac_delete (ac_id int) RETURNS VOID AS $$
BEGIN
DELETE FROM achievement WHERE achievement_id=ac_id;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION profileGrab(p_user varchar) RETURNS VOID AS $$
BEGIN
SELECT * FROM mmo_user AS u, mmo_billing AS b, mmo_card_info AS c WHERE u.username = p_user AND b.billingno = u.uid AND  c.id = u.uid;
END;
$$ LANGUAGE plpgsql;

create function c_update(c_name varchar) returns void AS $$
BEGIN
UPDATE characters
SET character_attack = (SELECT SUM(attack)
FROM Equipment as E, Inventory as I
Where E.equipment_name = I.equipment_name and I.character_name = c_name and I.equipped = 't')
WHERE character_name = c_name;

UPDATE characters
SET character_hp = (SELECT SUM(hp)
FROM Equipment as E, Inventory as I
Where E.equipment_name = I.equipment_name and I.character_name = c_name and I.equipped = 't')
WHERE character_name = c_name;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION server_pop_update() RETURNS TRIGGER AS $$
BEGIN
UPDATE server SET population = (SELECT COUNT(native_server) FROM characters where native_server='Chattur''gha') WHERE server_name = 'Chattur''gha';

UPDATE server SET population = (SELECT COUNT(native_server) FROM characters where native_server='Ulyaoth') WHERE server_name = 'Ulyaoth';

UPDATE server SET population = (SELECT COUNT(native_server) FROM characters where native_server='Xel''lotath') WHERE server_name = 'Xel''lotath';
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

/*VIEWS*/
Create View Dagoth_Inventory AS
SELECT Equipment_Name
FROM inventory
WHERE Character_Name = 'Dagoth';

Create View Admins AS
SELECT username, email, F_name
FROM Admin, mmo_user
WHERE uid = admin_id;

Create View User_Characters_Equipment AS
SELECT u.username,c.character_name,i.equipment_name
FROM mmo_user AS u, characters AS c
LEFT JOIN inventory AS i
ON c.character_name = i.character_name AND equipped = 'true'
WHERE u.uid = c.owner_id;

Create View Achievements_earned AS
SELECT DISTINCT a.achievement_id, a.name, a.point_amount, a.description, a.status, a.date, a.a_uid, u.uid, c.owner_id, c.character_name 
FROM achievement AS a, mmo_user AS u, characters AS c
WHERE a.a_uid = u.uid AND u.uid = c.owner_id;
/*Triggers*/
CREATE OR REPLACE FUNCTION c_owner() 
RETURNS TRIGGER 
LANGUAGE PLPGSQL
AS 
$$
BEGIN
IF NEW.uid <> OLD.uid THEN
	UPDATE characters 
	SET owner_id = New.uid 
	WHERE owner_id = Old.uid;
END IF;
IF NEW.native_server <> OLD.native_server THEN
	UPDATE characters
	SET native_server = New.native_server
	WHERE native_server = Old.native_server;
END IF;
RETURN NEW;
END;
$$;

Create View c_log AS
SELECT uid, character_name, native_server
FROM characters, mmo_user
WHERE uid = owner_id;


CREATE TRIGGER c_view_instead
INSTEAD OF INSERT OR UPDATE
ON c_log
FOR EACH ROW
EXECUTE PROCEDURE c_owner();

CREATE TRIGGER pop_update
AFTER INSERT
ON characters
FOR EACH ROW
EXECUTE PROCEDURE server_pop_update();

INSERT INTO characters(character_name,last_logoff_location,character_attack,character_hp,owner_id,class,native_server) VALUES ('Dante','Cocytus',0,0,1,'Demon Hunter','Ulyaoth');