refactoring as part of https://github.com/vainglorygame/vainsocial.com/issues/17

3 files changed, 28 insertions, 46 deletions

diff --git a/queries/match_participation.sql b/queries/match_participation.sql
deleted file mode 100644
index 43785c6..0000000
--- a/queries/match_participation.sql
+++ /dev/null
@@ -1,24 +0,0 @@
-SELECT
-
-relationships->'player'->'data'->>'id' AS player_id,
-0 AS "match_id",
-0 AS "rosterId",
-0 AS "fk_player_id",
-0 AS "fk_match_results_id",
-attributes->>'actor' AS "hero",
-COALESCE(NULLIF(attributes->'stats'->>'kills', ''), '0')::int AS "kills",
-(attributes->'stats'->>'deaths')::int AS "deaths",
-(attributes->'stats'->>'assists')::int AS "assists",
-0.0 AS "KD",
-0.0 AS "KDA",
-0 AS "killParticipation",
-COALESCE(NULLIF(attributes->'stats'->>'nonJungleMinionKills', ''), '0')::int AS "laneMinionsSlayed",
-COALESCE(NULLIF(attributes->'stats'->>'minionKills', ''), '0')::int AS "jungleMinionsSlayed",
-COALESCE(NULLIF(attributes->'stats'->>'turretCaptures', ''), '0')::int AS "turretsDestroyed",
-COALESCE(NULLIF(attributes->'stats'->>'krakenCaptures', ''), '0')::int AS "krakenCaptures",
-COALESCE(NULLIF(attributes->'stats'->>'goldMineCaptures', ''), '0')::int AS "goldMineCaptures",
-COALESCE(NULLIF(attributes->'stats'->>'crystalMineCaptures', ''), '0')::int AS "crystalMineCaptures",
-(attributes->'stats'->>'wentAfk')::bool::int AS "wentAfk",
-FALSE AS "perfectGame"
-
-FROM participant
diff --git a/queries/match_results.sql b/queries/match_results.sql
deleted file mode 100644
index c493d5c..0000000
--- a/queries/match_results.sql
+++ /dev/null
@@ -1,22 +0,0 @@
-SELECT
-
-attributes->'stats'->>'side' AS "team",
-FALSE AS "winner",
-FALSE AS "surrender",
-0 AS "teamSize",
-'' AS "hero_1",
-'' AS "hero_2",
-'' AS "hero_3",
-0 AS "laneMinionsSlayed",
-0 AS "jungleMinionsSlayed",
-(attributes->'stats'->>'turretKills')::int AS "turretsDestroyed",
-(attributes->'stats'->>'heroKills')::int AS "kills",
-0 AS "assists",
-0 AS "deaths",
-(attributes->'stats'->>'krakenCaptures')::int AS "krakenCaptures",
-0 AS "goldMineCaptures",
-0 AS "crystalMineCaptures",
-0 AS "afkCount",
-0 AS "afkTime"
-
-FROM roster
diff --git a/queries/participant.sql b/queries/participant.sql
new file mode 100644
index 0000000..a84274d
--- /dev/null
+++ b/queries/participant.sql
@@ -0,0 +1,28 @@
+SELECT
+
+id AS "apiId",
+relationships->'player'->'data'->>'id' AS "playerId",
+
+attributes->>'actor' AS "hero",
+(attributes->'stats'->>'assists')::int AS "assists",
+COALESCE(NULLIF(attributes->'stats'->>'crystalMineCaptures', ''), '0')::int AS "crystalMineCaptures",
+(attributes->'stats'->>'deaths')::int AS "deaths",
+(attributes->'stats'->>'farm')::float AS "farm",
+(attributes->'stats'->>'firstAfkTime')::float AS "firstAfkTime",
+COALESCE(NULLIF(attributes->'stats'->>'goldMineCaptures', ''), '0')::int AS "goldMineCaptures",
+COALESCE(NULLIF(attributes->'stats'->>'jungleKills', ''), '0')::int AS "jungleKills",
+COALESCE(NULLIF(attributes->'stats'->>'karmaLevel', ''), '0')::int AS "karmaLevel",
+COALESCE(NULLIF(attributes->'stats'->>'kills', ''), '0')::int AS "kills",
+COALESCE(NULLIF(attributes->'stats'->>'krakenCaptures', ''), '0')::int AS "krakenCaptures",
+COALESCE(NULLIF(attributes->'stats'->>'level', ''), '0')::int AS "level",
+COALESCE(NULLIF(attributes->'stats'->>'minionKills', ''), '0')::int AS "minionKills",
+COALESCE(NULLIF(attributes->'stats'->>'skillTier', ''), '0')::int AS "skillTier",
+COALESCE(NULLIF(attributes->'stats'->>'skinKey', ''), '0') AS "skinKey",
+(attributes->'stats'->>'wentAfk')::bool::bool AS "wentAfk",
+(attributes->'stats'->>'winner')::bool::bool AS "winner",
+attributes->'stats'->'itemGrants' AS "itemGrants",
+attributes->'stats'->'itemSells' AS "itemSells",
+attributes->'stats'->'itemUses' AS "itemUses",
+attributes->'stats'->'items' AS "items"
+
+FROM participant