Select inventory at Ragnarok Online (rAthena)

.rs

use crate::error::AppError;
use serde::{Deserialize, Serialize};
use sqlx::{MySql, Pool, Row, FromRow};

// Helper struct to handle database type conversions
#[derive(Debug, FromRow, Clone, Serialize, Deserialize)]
pub struct RawInventoryRow {
    pub account_id: u32,
    pub account: String,
    pub source: String,
    pub hero_id: Option<u32>,
    pub hero: Option<String>,
    pub item_id: u32,
    pub item: String,
    pub item_type: Option<String>,
    pub item_subtype: Option<String>,
    pub slots: u32,
    pub refine: u32,
    pub amount: u32,
}

// Get user items splitted by groups (storage, inventory, cart, etc.)
pub async fn select_items(&self, user_email: &str) -> Result<Vec<RawInventoryRow>, AppError> {
  let rows = sqlx::query(
      r#"..."#,
  )
  .bind(user_email)
  .try_map(|row: sqlx::mysql::MySqlRow| {
      let source = row.try_get::<String, _>("source").unwrap_or_default();
      let has_hero = source == "inventory" || source == "cart" || source == "zeny";
      Ok(RawInventoryRow {
          account_id: row.try_get::<u32, _>("account_id").unwrap_or(0),
          account: row.try_get("account").unwrap_or_default(),
          source: row.try_get("source").unwrap_or_default(),
          hero_id: if has_hero {
              row.try_get::<Option<u32>, _>("hero_id").unwrap_or(None)
          } else {
              None
          },
          hero: if has_hero {
              row.try_get("hero").unwrap_or(None)
          } else {
              None
          },
          item_id: row.try_get::<u32, _>("item_id").unwrap_or(0),
          item: row.try_get("item").unwrap_or_default(),
          item_type: row.try_get("item_type").unwrap_or(None),
          item_subtype: row.try_get("item_subtype").unwrap_or(None),
          slots: row.try_get::<u32, _>("slots").unwrap_or(0),
          refine: row.try_get::<u32, _>("refine").unwrap_or(0),
          amount: row.try_get::<u32, _>("amount").unwrap_or(0),
      })
  })
  .fetch_all(&self.pool)
  .await?;

  if rows.is_empty() {
      return Ok(Vec::new()); // Return empty Vec if no items found
  }

  return Ok(rows);
}

inventory.sql

-- SQL query to select account items from various sources
WITH
  accounts AS (
    SELECT
      lgn.account_id AS account_id,
      lgn.userid     AS account
    FROM `login` AS lgn
    WHERE lgn.email = ?
  ),

  vip_sum AS (
    SELECT
      acc.account_id                             AS account_id,
      acc.account                                AS account,
      vip.nameid                                 AS nameid,
      vip.refine                                 AS refine,
      SUM(CONVERT(vip.amount, UNSIGNED INTEGER)) AS amount
    FROM `vip_storage` AS vip
    INNER JOIN accounts AS acc
      ON vip.account_id = acc.account_id
    WHERE vip.amount > 0
    GROUP BY acc.account_id, acc.account, vip.nameid, vip.refine
  ),

  storage_sum AS (
    SELECT
      acc.account_id                             AS account_id,
      acc.account                                AS account,
      str.nameid                                 AS nameid,
      str.refine                                 AS refine,
      SUM(CONVERT(str.amount, UNSIGNED INTEGER)) AS amount
    FROM `storage` AS str
    INNER JOIN accounts AS acc
      ON str.account_id = acc.account_id
    WHERE str.amount > 0
    GROUP BY acc.account_id, acc.account, str.nameid, str.refine
  ),

  inv_sum AS (
    SELECT
      acc.account_id                             AS account_id,
      acc.account                                AS account,
      chr.char_id                                AS hero_id,
      chr.name                                   AS hero,
      inv.nameid                                 AS nameid,
      inv.refine                                 AS refine,
      SUM(CONVERT(inv.amount, UNSIGNED INTEGER)) AS amount
    FROM `inventory` AS inv
    INNER JOIN `char` AS chr
      ON inv.char_id = chr.char_id
    INNER JOIN accounts AS acc
      ON chr.account_id = acc.account_id
    WHERE inv.amount > 0
    GROUP BY acc.account_id, acc.account, chr.char_id, chr.name, inv.nameid, inv.refine
  ),

  cart_sum AS (
    SELECT
      acc.account_id                             AS account_id,
      acc.account                                AS account,
      chr.char_id                                AS hero_id,
      chr.name                                   AS hero,
      crt.nameid                                 AS nameid,
      crt.refine                                 AS refine,
      SUM(CONVERT(crt.amount, UNSIGNED INTEGER)) AS amount
    FROM `cart_inventory` AS crt
    INNER JOIN `char` AS chr
      ON crt.char_id = chr.char_id
    INNER JOIN accounts AS acc
      ON chr.account_id = acc.account_id
    WHERE crt.amount > 0
    GROUP BY acc.account_id, acc.account, chr.char_id, chr.name, crt.nameid, crt.refine
  )

-- VIP Storage
SELECT
  CONVERT(vip.account_id, UNSIGNED INTEGER)           AS account_id,
  vip.account                                         AS account,
  'vip'                                               AS source,
  CONVERT(0, UNSIGNED INTEGER)                        AS hero_id,
  ''                                                  AS hero,
  COALESCE(itm.type, 'Unknown')                       AS item_type,
  itm.subtype                                         AS item_subtype,
  CONVERT(vip.nameid, UNSIGNED INTEGER)               AS item_id,
  COALESCE(itm.name_english, CONCAT('#', vip.nameid)) AS item,
  CAST(IFNULL(itm.slots, 0) AS UNSIGNED)              AS slots,
  CAST(vip.refine AS UNSIGNED)                        AS refine,
  CONVERT(vip.amount, UNSIGNED INTEGER)               AS amount
FROM vip_sum AS vip
LEFT JOIN `item_db` AS itm
  ON vip.nameid = itm.id

UNION ALL

-- Storage
SELECT
  CONVERT(str.account_id, UNSIGNED INTEGER)           AS account_id,
  str.account                                         AS account,
  'storage'                                           AS source,
  CONVERT(0, UNSIGNED INTEGER)                        AS hero_id,
  ''                                                  AS hero,
  COALESCE(itm.type, 'Unknown')                       AS item_type,
  itm.subtype                                         AS item_subtype,
  CONVERT(str.nameid, UNSIGNED INTEGER)               AS item_id,
  COALESCE(itm.name_english, CONCAT('#', str.nameid)) AS item,
  CAST(IFNULL(itm.slots, 0) AS UNSIGNED)              AS slots,
  CAST(str.refine AS UNSIGNED)                        AS refine,
  CONVERT(str.amount, UNSIGNED INTEGER)               AS amount
FROM storage_sum AS str
LEFT JOIN `item_db` AS itm
  ON str.nameid = itm.id

UNION ALL

-- Zeny
SELECT
  CONVERT(acc.account_id, UNSIGNED INTEGER)           AS account_id,
  acc.account                                         AS account,
  'zeny'                                              AS source,
  CONVERT(chr.char_id, UNSIGNED INTEGER)              AS hero_id,
  chr.name                                            AS hero,
  'Zeny'                                              AS item_type,
  null                                                AS item_subtype,
  CONVERT(0, UNSIGNED INTEGER)                        AS item_id,
  'Zeny'                                              AS item,
  CONVERT(0, UNSIGNED INTEGER)                        AS slots,
  CONVERT(0, UNSIGNED INTEGER)                        AS refine,
  CONVERT(chr.zeny, UNSIGNED INTEGER)                 AS amount
FROM accounts AS acc
INNER JOIN `char` AS chr
  ON acc.account_id = chr.account_id
WHERE chr.zeny > 0

UNION ALL

-- Character Inventory
SELECT
  CONVERT(inv.account_id, UNSIGNED INTEGER)           AS account_id,
  inv.account                                         AS account,
  'inventory'                                         AS source,
  CONVERT(inv.hero_id, UNSIGNED INTEGER)              AS hero_id,
  inv.hero                                            AS hero,
  COALESCE(itm.type, 'Unknown')                       AS item_type,
  itm.subtype                                         AS item_subtype,
  CONVERT(inv.nameid, UNSIGNED INTEGER)               AS item_id,
  COALESCE(itm.name_english, CONCAT('#', inv.nameid)) AS item,
  CAST(IFNULL(itm.slots, 0) AS UNSIGNED)              AS slots,
  CAST(inv.refine AS UNSIGNED)                        AS refine,
  CONVERT(inv.amount, UNSIGNED INTEGER)               AS amount
FROM inv_sum AS inv
LEFT JOIN `item_db` AS itm
  ON inv.nameid = itm.id

UNION ALL

-- Cart Inventory
SELECT
  CONVERT(crt.account_id, UNSIGNED INTEGER)           AS account_id,
  crt.account                                         AS account,
  'cart'                                              AS source,
  CONVERT(crt.hero_id, UNSIGNED INTEGER)              AS hero_id,
  crt.hero                                            AS hero,
  COALESCE(itm.type, 'Unknown')                       AS item_type,
  itm.subtype                                         AS item_subtype,
  CONVERT(crt.nameid, UNSIGNED INTEGER)               AS item_id,
  COALESCE(itm.name_english, CONCAT('#', crt.nameid)) AS item,
  CAST(IFNULL(itm.slots, 0) AS UNSIGNED)              AS slots,
  CAST(crt.refine AS UNSIGNED)                        AS refine,
  CONVERT(crt.amount, UNSIGNED INTEGER)               AS amount
FROM cart_sum AS crt
LEFT JOIN `item_db` AS itm
  ON crt.nameid = itm.id

ORDER BY
  account_id ASC,
  source ASC