CREATE TABLE player (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  display_name TEXT,
  lvl INTEGER,
  coins INTEGER,
  current_location_id INTEGER,
  spawn_location_id INTEGER,
  FOREIGN KEY (current_location_id) REFERENCES location (id)
  FOREIGN KEY (spawn_location_id) REFERENCES location (id)
);


CREATE TABLE location (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  region TEXT CHECK (region IN ('Frostbite Tundra', 'Catacombia', 'Ethereal Reach', 'Pyreforge'))
);

CREATE TABLE quest (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  min_lvl INTEGER,
  location_id INTEGER,
  reward INTEGER,
  FOREIGN KEY (location_id) REFERENCES location (id)
);


CREATE TABLE quest_checkpoint (
  quest_id INTEGER,
  player_id INTEGER,
  checkpoint_type TEXT CHECK (checkpoint_type IN ('Start', 'Fail', 'Complete')),
  checkpoint_date DATE,
  PRIMARY KEY (quest_id, player_id, checkpoint_type),
  FOREIGN KEY (quest_id) REFERENCES quest (id),
  FOREIGN KEY (player_id) REFERENCES player (id)
);


CREATE TABLE shop (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  location_id INTEGER,
  FOREIGN KEY (location_id) REFERENCES location (id)
);


CREATE TABLE shop_catalogue (
  shop_id INTEGER,
  tool_id INTEGER,
  price INTEGER,
  PRIMARY KEY (shop_id, tool_id),
  FOREIGN KEY (shop_id) REFERENCES shop (id),
  FOREIGN KEY (tool_id) REFERENCES tool (id)
);


CREATE TABLE shop_transaction (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  transaction_date DATE,
  type TEXT CHECK (type IN ('Buy', 'Sell')),
  shop_id INTEGER,
  customer_id INTEGER,
  tool_purchased_id INTEGER,
  FOREIGN KEY (shop_id) REFERENCES shop (id),
  FOREIGN KEY (customer_id) REFERENCES player (id),
  FOREIGN KEY (tool_purchased_id) REFERENCES tool (id)
);


CREATE TABLE tool (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  type TEXT CHECK (type IN ('Weapon', 'Utility', 'Consumable')),
  damage INTEGER,
  health INTEGER
);

INSERT INTO player (display_name, lvl, coins, current_location_id, spawn_location_id)
VALUES
    ('shadow_striker99', 20, 500, 1, 1),
    ('MysticMagica', 15, 300, 2, 3),
    ('rogue_pirate', 25, 700, 9, 3),
    ('HolyClericX', 18, 400, 4, 4),
    ('IronKnight27', 22, 600, 2, 8),
    ('SwiftArcher13', 12, 200, 6, 6),
    ('SoulSorcerer86', 19, 450, 9, 7),
    ('DeadlyAssassinX', 28, 800, 8, 17),
    ('DivinePaladin7', 24, 550, 9, 9),
    ('NatureDruid99', 16, 350, 10, 10),
    ('MelodicBard22', 14, 250, 11, 11),
    ('DarkNecromancer666', 21, 420, 12, 18),
    ('MysticWarlock55', 17, 380, 13, 14),
    ('FierceBarbarian300', 23, 520, 13, 14),
    ('zen_champion', 19, 480, 15, 15),
    ('shaman_spirit', 11, 230, 16, 16),
    ('SilentHunter42', 13, 320, 14, 8),
    ('witch_tnr', 26, 650, 14, 18),
    ('SneakyThief007', 10, 190, 4, 19),
    ('wow_priest', 20, 500, 4, 20);

INSERT INTO location (name, region)
VALUES
    ('Frozenpeak Keep', 'Frostbite Tundra'),
    ('Icewind Citadel', 'Frostbite Tundra'),
    ('Permafrost Ruins', 'Frostbite Tundra'),
    ('Winters End', 'Frostbite Tundra'),
    ('Crypt of the Ancients', 'Catacombia'),
    ('Tomb of Lost Souls', 'Catacombia'),
    ('Eternal Rest', 'Catacombia'),
    ('Sanctum of Shadows', 'Ethereal Reach'),
    ('Spiritrealm Nexus', 'Ethereal Reach'),
    ('Mystic Gateway', 'Ethereal Reach'),
    ('Pyres Forge', 'Pyreforge'),
    ('Flameheart Citadel', 'Pyreforge'),
    ('Emberweld Armory', 'Pyreforge'),
    ('Blackforge Mines', 'Pyreforge'),
    ('Searing Peak', 'Pyreforge'),
    ('Ethereal Gardens', 'Ethereal Reach'),
    ('Veilwood Forest', 'Ethereal Reach'),
    ('Mystic Falls', 'Ethereal Reach'),
    ('Catacombias Heart', 'Catacombia'),
    ('Pyreforge Outpost', 'Pyreforge');

INSERT INTO quest (name, min_lvl, location_id, reward)
VALUES
    ('Frostbite Tundra Expedition', 15, 1, 500),
    ('Catacombia Crypt Exploration', 20, 5, 700),
    ('Ethereal Reach Mystery', 18, 9, 600),
    ('Pyreforge Forge Retrieval', 22, 13, 800),
    ('Mystic Gardens Quest', 12, 16, 400),
    ('Undead Army Eradication', 25, 4, 900),
    ('Ancient Ruins Investigation', 19, 10, 550),
    ('Lost City of Gold Search', 23, 7, 1000),
    ('Dragon Slayer Challenge', 30, 2, 1200),
    ('Shadow Realm Escape', 28, 14, 850),
    ('Sunken Temple Treasure Hunt', 21, 8, 750),
    ('Mystic Artifact Retrieval', 16, 11, 650),
    ('Corrupted Forest Purification', 14, 15, 500),
    ('Cursed Tomb Cleansing', 17, 6, 450),
    ('Sky Citadel Conquest', 24, 3, 950),
    ('Hidden Valley Exploration', 11, 12, 420),
    ('Ancient Library Discovery', 13, 17, 380),
    ('Mysterious Island Adventure', 10, 19, 350),
    ('Dark Forest Survival', 19, 18, 520),
    ('Haunted Mansion Investigation', 22, 20, 680),
    ('Frozen Peak Ascent', 15, 1, 480),
    ('Sunken City Salvage', 99, 7, 620);

INSERT INTO quest_checkpoint (quest_id, player_id, checkpoint_type, checkpoint_date)
VALUES
    (1, 9, 'Start', '2023-08-10'),
    (1, 17, 'Complete', '2023-08-15'),
    (2, 8, 'Start', '2023-08-12'),
    (2, 2, 'Fail', '2023-08-18'),
    (3, 4, 'Start', '2023-08-14'),
    (3, 3, 'Complete', '2023-08-22'),
    (4, 4, 'Start', '2023-08-16'),
    (3, 18, 'Complete', '2023-08-24'),
    (5, 5, 'Start', '2023-08-11'),
    (8, 6, 'Start', '2023-08-13'),
    (6, 6, 'Complete', '2023-08-20'),
    (7, 7, 'Start', '2023-08-17'),
    (8, 16, 'Start', '2023-08-19'),
    (4, 8, 'Complete', '2023-09-01'),
    (9, 4, 'Start', '2023-08-15'),
    (9, 14, 'Complete', '2023-08-23'),
    (10, 10, 'Start', '2023-08-12'),
    (10, 6, 'Complete', '2023-08-20'),
    (11, 11, 'Start', '2023-08-14'),
    (12, 12, 'Start', '2023-08-16'),
    (9, 12, 'Complete', '2023-08-24'),
    (13, 13, 'Start', '2023-08-18'),
    (13, 4, 'Complete', '2023-08-26'),
    (19, 14, 'Start', '2023-08-11'),
    (15, 15, 'Start', '2023-08-13'),
    (3, 15, 'Complete', '2023-08-20'),
    (16, 16, 'Start', '2023-08-17'),
    (1, 17, 'Start', '2023-08-12'),
    (17, 13, 'Fail', '2023-08-19'),
    (17, 12, 'Start', '2023-08-21'),
    (17, 11, 'Complete', '2023-08-28'),
    (18, 18, 'Start', '2023-08-15'),
    (18, 1, 'Complete', '2023-08-23'),
    (19, 2, 'Start', '2023-08-16'),
    (19, 19, 'Complete', '2023-08-24'),
    (20, 20, 'Start', '2023-08-11'),
    (20, 1, 'Start', '2023-08-11'),
    (20, 2, 'Start', '2023-08-11'),
    (20, 4, 'Start', '2023-08-11'),
    (4, 3, 'Fail', '2023-08-18'),
    (20, 3, 'Fail', '2023-08-18'),
    (20, 1, 'Fail', '2023-08-18'),
    (20, 9, 'Fail', '2023-08-18'),
    (20, 19, 'Start', '2023-08-20'),
    (20, 18, 'Start', '2023-08-20'),
    (20, 12, 'Start', '2023-08-20'),
    (9, 11, 'Start', '2023-08-20'),
    (13, 20, 'Complete', '2023-08-27'),
    (20, 10, 'Complete', '2023-08-27'),
    (20, 8, 'Complete', '2023-08-27'),
    (20, 9, 'Complete', '2023-08-27');

INSERT INTO shop_catalogue (shop_id, tool_id, price)
VALUES
    (1, 19, 1500),
    (2, 9, 1200),
    (3, 18, 1800),
    (4, 8, 2000),
    (5, 17, 1400),
    (6, 7, 900),
    (7, 16, 1600),
    (8, 6, 1100),
    (9, 15, 1300),
    (10, 5, 800),
    (11, 14, 1700),
    (12, 4, 1900),
    (13, 13, 1000),
    (14, 3, 1500),
    (15, 12, 2100),
    (16, 2, 700),
    (17, 11, 1200),
    (18, 1, 1300),
    (19, 10, 900);


INSERT INTO shop (name, location_id)
VALUES
    ('Frostbite Emporium', 1),
    ('Icewind Trading Post', 2),
    ('Permafrost Provisions', 3),
    ('Wintermarket', 4),
    ('Ancient Artifacts', 5),
    ('Lost Soul Relics', 6),
    ('Eternal Rest Funeral Home', 7),
    ('Shadowcraft Boutique', 8),
    ('Spiritrealm Supplies', 9),
    ('Mystic Gateway Portal', 10),
    ('Pyreforge Blacksmith', 11),
    ('Flameheart Armory', 12),
    ('Emberweld Weapons', 13),
    ('Blackforge Mining Co.', 14),
    ('Searing Summit', 15),
    ('Ethereal Botanicals', 16),
    ('Veilwood Traders', 17),
    ('Mystic Falls Apothecary', 18),
    ('Catacombs Antiquities', 19),
    ('Pyreforge Outpost Traders', 20);

INSERT INTO shop_transaction (transaction_date, type, shop_id, customer_id, tool_purchased_id)
VALUES
    ('2023-08-20', 'Buy', 1, 9, 19),
    ('2023-08-21', 'Sell', 2, 2, 9),
    ('2023-08-22', 'Buy', 3, 9, 18),
    ('2023-08-23', 'Sell', 4, 4, 8),
    ('2023-08-24', 'Buy', 5, 8, 17),
    ('2023-08-25', 'Sell', 6, 6, 7),
    ('2023-08-26', 'Buy', 7, 7, 16),
    ('2023-08-27', 'Sell', 8, 8, 6),
    ('2023-08-28', 'Buy', 9, 3, 15),
    ('2023-08-29', 'Sell', 10, 10, 5),
    ('2023-08-30', 'Buy', 11, 4, 14),
    ('2023-08-31', 'Sell', 12, 12, 4),
    ('2023-09-01', 'Buy', 13, 5, 13),
    ('2023-09-02', 'Sell', 14, 14, 3),
    ('2023-09-03', 'Buy', 15, 6, 12),
    ('2023-09-04', 'Sell', 16, 16, 2),
    ('2023-09-05', 'Buy', 17, 12, 11),
    ('2023-09-06', 'Sell', 18, 18, 1),
    ('2023-09-07', 'Buy', 19, 11, 10);

INSERT INTO tool (name, type, damage, health)
VALUES
    ('Frostbite Blade', 'Weapon', 20, 0),
    ('Icewind Bow', 'Weapon', 15, 0),
    ('Permafrost Pickaxe', 'Utility', 0, 10),
    ('Winters End Shovel', 'Utility', 0, 15),
    ('Ancient Scroll', 'Consumable', 0, 20),
    ('Soul Gem', 'Consumable', 0, 15),
    ('Eternal Rest Potion', 'Consumable', 0, 25),
    ('Shadowcraft Dagger', 'Weapon', 18, 0),
    ('Spiritrealm Staff', 'Weapon', 22, 0),
    ('Mystic Gateway Key', 'Utility', 0, 5),
    ('Pyreforge Hammer', 'Utility', 0, 20),
    ('Flameheart Sword', 'Weapon', 25, 0),
    ('Emberweld Armor', 'Utility', 0, 30),
    ('Blackforge Shield', 'Utility', 0, 25),
    ('Searing Spear', 'Weapon', 12, 0),
    ('Ethereal Elixir', 'Consumable', 0, 18),
    ('Veilwood Staff', 'Weapon', 10, 0),
    ('Mystic Healing Potion', 'Consumable', 0, 12),
    ('Catacombias Torch', 'Utility', 0, 8),
    ('Pyreforge Forge', 'Utility', 0, 15);


-- WITH QuestOutcomes AS (
--     SELECT
--         loc.region,
--         CASE
--             WHEN qcp.checkpoint_type = 'Complete' THEN 1
--             ELSE 0
--         END AS IsComplete,
--         CASE
--             WHEN qcp.checkpoint_type = 'Fail' THEN 1
--             ELSE 0
--         END AS IsFail,
--         p.lvl AS player_level
--     FROM quest_checkpoint qcp
--     JOIN quest q ON qcp.quest_id = q.id
--     JOIN location loc ON q.location_id = loc.id
--     JOIN player p ON qcp.player_id = p.id
-- ),
-- AggregatedData AS (
--     SELECT
--         region,
--         ROUND(SUM(IsComplete) * 100.0 / COUNT(*), 2) AS completion_rate,
--         ROUND(SUM(IsFail) * 100.0 / COUNT(*), 2) AS failure_rate,
--         ROUND(AVG(player_level), 2) AS avg_player_level
--     FROM QuestOutcomes
--     GROUP BY region
-- )
-- SELECT 
--     region, 
--     completion_rate, 
--     failure_rate, 
--     avg_player_level
-- FROM AggregatedData
-- ORDER BY failure_rate DESC
-- LIMIT 1;

WITH TransactionCounts AS (
    SELECT
        tool_purchased_id,
        SUM(CASE WHEN type = 'Buy' THEN 1 ELSE 0 END) AS BoughtCount,
        SUM(CASE WHEN type = 'Sell' THEN 1 ELSE 0 END) AS SoldCount
    FROM shop_transaction
    GROUP BY tool_purchased_id
),
ToolPrices AS (
    SELECT
        sc.tool_id,
        sc.price AS AvgPrice -- Assuming multiple shops might have different prices for the same tool
    FROM shop_catalogue sc
    GROUP BY sc.tool_id
)
SELECT
    t.name AS ToolName,
    t.type AS ToolType,
    tc.BoughtCount,
    tc.SoldCount,
    tp.AvgPrice
FROM TransactionCounts tc
JOIN ToolPrices tp ON tc.tool_purchased_id = tp.tool_id
JOIN tool t ON tc.tool_purchased_id = t.id
ORDER BY ToolName asc;