= Advanced Database Reports (SQL Queries and Stored Procedures) =

This page includes advanced SQL queries and stored procedures for generating reports from the **Legends of the Balkans** game database.

== 1. Player Progress Report ==
This query provides a summary of each player's progress—including their character details and any associated faction reputation.

{{{
SELECT p.PlayerID,
       p.Username,
       c.Name AS CharacterName,
       c.Health,
       c.EXP,
       c.Gold,
       f.Name AS FactionName,
       r.ReputationPoints,
       r.Rank
FROM Player p
JOIN Character c ON p.PlayerID = c.PlayerID
LEFT JOIN Reputation r ON c.CharacterID = r.CharacterID
LEFT JOIN Faction f ON r.FactionID = f.FactionID;
}}}

== 2. Faction Reputation Update After Quest Completion ==
The following stored procedure updates a character’s reputation for a given faction based on the EXP reward from a completed quest.

{{{
CREATE OR REPLACE FUNCTION UpdateFactionReputation(
    char_id INT,
    quest_id INT,
    faction_id INT
)
RETURNS VOID AS
$$
DECLARE
    reputation_change INT;
BEGIN
    -- Get the EXP reward from the quest
    SELECT EXP_Reward INTO reputation_change
    FROM Quest
    WHERE QuestID = quest_id;

    -- Update the Reputation record for the character and faction
    UPDATE Reputation
    SET ReputationPoints = ReputationPoints + reputation_change
    WHERE CharacterID = char_id AND FactionID = faction_id;
END;
$$ LANGUAGE plpgsql;
}}}

== 3. Completed Quests Report for a Specific Player ==
This query retrieves the list of quests completed by a specific player. (Note: The join table *Character_Quest* contains only the CharacterID and QuestID.)

{{{
SELECT p.Username,
       q.Name AS QuestName
FROM Player p
JOIN Character c ON p.PlayerID = c.PlayerID
JOIN Character_Quest cq ON c.CharacterID = cq.CharacterID
JOIN Quest q ON cq.QuestID = q.QuestID
WHERE p.PlayerID = ?;  -- Replace ? with the specific PlayerID
}}}

== 4. List of Players in a Specific Faction ==
This query lists all players whose characters have a reputation record with a specific faction. Replace the faction name as needed.

{{{
SELECT DISTINCT p.PlayerID,
       p.Username,
       c.Name AS CharacterName
FROM Player p
JOIN Character c ON p.PlayerID = c.PlayerID
JOIN Reputation r ON c.CharacterID = r.CharacterID
JOIN Faction f ON r.FactionID = f.FactionID
WHERE f.Name = 'Kingdom of Zeta';  -- Replace with the desired faction name
}}}

== 5. Market Listings for a Specific Player ==
This query retrieves all items listed on the market by a specific player.

{{{
SELECT m.MarketID,
       i.Name AS ItemName,
       m.Price
FROM Market m
JOIN Item i ON m.ItemID = i.ItemID
WHERE m.SellerID = ?;  -- Replace ? with the specific Seller's PlayerID
}}}

== 6. Top Players by Total Experience and Completed Quests ==
This query provides a list of top players, ordered by the total EXP of all their characters and the number of quests they have completed.

{{{
SELECT p.PlayerID,
       p.Username,
       SUM(c.EXP) AS TotalEXP,
       COUNT(cq.QuestID) AS CompletedQuests
FROM Player p
JOIN Character c ON p.PlayerID = c.PlayerID
LEFT JOIN Character_Quest cq ON c.CharacterID = cq.CharacterID
GROUP BY p.PlayerID, p.Username
ORDER BY TotalEXP DESC, CompletedQuests DESC;
}}}

== 7. Items Owned by a Specific Player ==
This query lists the items owned by a specific player's characters.

{{{
SELECT p.Username,
       i.Name AS ItemName
FROM Player p
JOIN Character c ON p.PlayerID = c.PlayerID
JOIN Character_Item ci ON c.CharacterID = ci.CharacterID
JOIN Item i ON ci.ItemID = i.ItemID
WHERE p.PlayerID = ?;  -- Replace ? with the specific PlayerID
}}}

== 8. Items Listed in the Market ==
This query retrieves all items currently listed on the market along with seller information.

{{{
SELECT m.MarketID,
       i.Name AS ItemName,
       m.Price,
       p.Username AS Seller
FROM Market m
JOIN Item i ON m.ItemID = i.ItemID
JOIN Player p ON m.SellerID = p.PlayerID;
}}}

== 9. List of Guilds and Their Leaders ==
This query lists all guilds along with the username of their leader.

{{{
SELECT g.GuildID,
       g.Name AS GuildName,
       p.Username AS LeaderName
FROM Guild g
JOIN Player p ON g.LeaderID = p.PlayerID;
}}}

== 10. Characters in Guilds ==
This query retrieves characters that belong to guilds, along with their guild name.

{{{
SELECT p.Username,
       c.Name AS CharacterName,
       g.Name AS GuildName
FROM Character_Guild cg
JOIN Character c ON cg.CharacterID = c.CharacterID
JOIN Player p ON c.PlayerID = p.PlayerID
JOIN Guild g ON cg.GuildID = g.GuildID;
}}}