defmodule Odinsea.Database.Context do
  @moduledoc """
  Database context module for Odinsea.
  Provides high-level database operations for accounts, characters, and related entities.

  Ported from Java UnifiedDB.java and MapleClient.java login functionality.
  """

  require Logger

  import Ecto.Query

  alias Odinsea.Repo

  alias Odinsea.Database.Schema.{
    Account,
    Character,
    CharacterSlot,
    InventoryItem,
    Buddy,
    Guild,
    Skill,
    QuestStatus,
    QuestStatusMob,
    QuestInfo,
    Gift,
    NxCode,
    IpBan,
    MacBan
  }

  alias Odinsea.Game.InventoryType
  alias Odinsea.Net.Cipher.LoginCrypto

  # ==================================================================================================
  # Account Operations
  # ==================================================================================================

  @doc """
  Authenticates a user with username and password.

  Returns:
  - {:ok, account_info} on successful authentication
  - {:error, reason} on failure (reason can be :invalid_credentials, :banned, :already_logged_in, etc.)

  Ported from MapleClient.java login() method
  """
  def authenticate_user(username, password, ip_address \\ "") do
    # Filter username (sanitization)
    username = sanitize_username(username)

    case Repo.get_by(Account, name: username) do
      nil ->
        Logger.warning("Login attempt for non-existent account: #{username}")
        {:error, :account_not_found}

      account ->
        check_account_login(account, password, ip_address)
    end
  end

  @doc """
  Gets an account by name.
  Returns nil if not found.
  """
  def get_account_by_name(name) do
    Repo.get_by(Account, name: name)
  end

  @doc """
  Gets an account by ID.
  Returns nil if not found.
  """
  def get_account(account_id) do
    Repo.get(Account, account_id)
  end

  @doc """
  Updates all accounts to logged out state.
  Used during server startup/shutdown.
  """
  def set_all_accounts_logged_off do
    Repo.update_all(Account, set: [loggedin: 0])
    :ok
  end

  @doc """
  Updates account login state.

  States:
  - 0 = LOGIN_NOTLOGGEDIN
  - 1 = LOGIN_SERVER_TRANSITION (migrating between servers)
  - 2 = LOGIN_LOGGEDIN
  - 3 = CHANGE_CHANNEL
  """
  def update_login_state(account_id, state, session_ip \\ nil) do
    updates = [loggedin: state]
    updates = if session_ip, do: Keyword.put(updates, :session_ip, session_ip), else: updates

    Repo.update_all(
      from(a in Account, where: a.id == ^account_id),
      set: updates
    )

    :ok
  end

  @doc """
  Updates account logged in status.
  """
  def update_logged_in_status(account_id, status) do
    Repo.update_all(
      from(a in Account, where: a.id == ^account_id),
      set: [loggedin: status]
    )

    :ok
  end

  @doc """
  Gets the current login state for an account.
  """
  def get_login_state(account_id) do
    case Repo.get(Account, account_id) do
      nil -> 0
      account -> account.loggedin
    end
  end

  @doc """
  Updates account last login time.
  """
  def update_last_login(account_id) do
    now = NaiveDateTime.utc_now() |> NaiveDateTime.truncate(:second)

    Repo.update_all(
      from(a in Account, where: a.id == ^account_id),
      set: [lastlogin: now]
    )

    :ok
  end

  @doc """
  Bans an account.

  Options:
  - :banned - ban status (1 = banned, 2 = auto-banned)
  - :banreason - reason for ban
  - :tempban - temporary ban expiry datetime
  - :greason - GM reason code
  """
  def ban_account(account_id, attrs \\ %{}) do
    case Repo.get(Account, account_id) do
      nil ->
        {:error, :not_found}

      account ->
        account
        |> Account.ban_changeset(attrs)
        |> Repo.update()
    end
  end

  @doc """
  Bans an account with explicit reason and duration (in days).
  For permanent ban, set duration to nil or 0.
  """
  def ban_account(account_id, reason, duration) do
    attrs = %{
      banned: 1,
      banreason: reason
    }

    attrs =
      if duration && duration > 0 do
        tempban =
          NaiveDateTime.utc_now()
          |> NaiveDateTime.add(duration * 86400, :second)
          |> NaiveDateTime.truncate(:second)

        Map.put(attrs, :tempban, tempban)
      else
        attrs
      end

    ban_account(account_id, attrs)
  end

  @doc """
  Checks if an IP address is banned.
  Returns true if the IP is in the ipbans table.
  """
  def ip_banned?(ip_address) do
    IpBan
    |> where([i], i.ip == ^ip_address)
    |> Repo.exists?()
  end

  @doc """
  Checks if a MAC address is banned.
  Returns true if the MAC is in the macbans table.
  """
  def mac_banned?(mac_address) do
    MacBan
    |> where([m], m.mac == ^mac_address)
    |> Repo.exists?()
  end

  @doc """
  Alias for log_ip_address/2.
  Also known as ban_ip_address in some contexts.
  """
  def ban_ip_address(ip_address, _reason \\ "", _is_mac \\ false, _times \\ 1) do
    # For enforcement, we insert into ipbans table
    # The original Java code logs and potentially bans
    %IpBan{}
    |> IpBan.changeset(%{ip: ip_address})
    |> Repo.insert()
    |> case do
      {:ok, _} -> :ok
      {:error, _} -> :error
    end
  end

  @doc """
  Updates the second password (SPW) for an account.
  Input: account_id, hashed_password
  """
  def update_second_password(account_id, hashed_password) do
    Repo.update_all(
      from(a in Account, where: a.id == ^account_id),
      set: [second_password: hashed_password]
    )

    :ok
  end

  @doc """
  Records an IP log entry for audit purposes.
  """
  def log_ip_address(account_id, ip_address) do
    timestamp = format_timestamp(NaiveDateTime.utc_now())

    # Using raw SQL since iplog may not have an Ecto schema yet
    sql = "INSERT INTO iplog (accid, ip, time) VALUES (?, ?, ?)"

    case Ecto.Adapters.SQL.query(Repo, sql, [account_id, ip_address, timestamp]) do
      {:ok, _} ->
        :ok

      {:error, err} ->
        Logger.error("Failed to log IP: #{inspect(err)}")
        :error
    end
  end

  @doc """
  Checks if an account is banned.
  Returns {:ok, account} if not banned, {:error, :banned} if banned.
  """
  def check_ban_status(account_id) do
    case Repo.get(Account, account_id) do
      nil ->
        {:error, :not_found}

      account ->
        if account.banned > 0 do
          {:error, :banned}
        else
          {:ok, account}
        end
    end
  end

  @doc """
  Gets temporary ban information if account is temp banned.
  """
  def get_temp_ban_info(account_id) do
    case Repo.get(Account, account_id) do
      nil ->
        nil

      account ->
        if account.tempban &&
             NaiveDateTime.compare(account.tempban, NaiveDateTime.utc_now()) == :gt do
          %{reason: account.banreason, expires: account.tempban}
        else
          nil
        end
    end
  end

  @doc """
  Updates account NX cash (ACash) and Maple Points.
  """
  def update_account_cash(account_id, acash, mpoints, points \\ nil, vpoints \\ nil) do
    updates = [acash: acash, mpoints: mpoints]
    updates = if points, do: Keyword.put(updates, :points, points), else: updates
    updates = if vpoints, do: Keyword.put(updates, :vpoints, vpoints), else: updates

    Repo.update_all(
      from(a in Account, where: a.id == ^account_id),
      set: updates
    )

    :ok
  end

  # ==================================================================================================
  # Cash Shop Operations
  # ==================================================================================================

  @doc """
  Gets coupon information by code.
  Input: coupon_code string
  Output: {:ok, coupon} or {:error, reason}
  """
  def get_coupon_info(coupon_code) do
    case Repo.get(NxCode, coupon_code) do
      nil -> {:error, :not_found}
      coupon -> {:ok, coupon}
    end
  end

  @doc """
  Marks a coupon as used by an account.
  Input: coupon_id (code string), account_id
  Output: {:ok, _} or {:error, _}
  """
  def mark_coupon_used(coupon_id, account_id) do
    case Repo.get(NxCode, coupon_id) do
      nil ->
        {:error, :not_found}

      coupon ->
        # Mark as invalid (0) and set user to account name
        # We need to look up the account name first
        account = Repo.get(Account, account_id)
        user_name = if account, do: account.name, else: ""

        coupon
        |> NxCode.changeset(%{valid: 0, user: user_name})
        |> Repo.update()
    end
  end

  @doc """
  Increments character slots for an account in a world.
  Input: account_id, amount (default 1)
  Output: {:ok, new_slot_count} or {:error, _}
  """
  def increment_character_slots(account_id, amount \\ 1, world_id \\ 0) do
    case CharacterSlot
         |> where([c], c.accid == ^account_id and c.worldid == ^world_id)
         |> Repo.one() do
      nil ->
        # Create new slot record with default + amount
        %CharacterSlot{}
        |> CharacterSlot.changeset(%{
          accid: account_id,
          worldid: world_id,
          charslots: 6 + amount
        })
        |> Repo.insert()
        |> case do
          {:ok, slot} -> {:ok, slot.charslots}
          error -> error
        end

      slot ->
        # Update existing
        new_count = slot.charslots + amount

        slot
        |> CharacterSlot.changeset(%{charslots: new_count})
        |> Repo.update()
        |> case do
          {:ok, _} -> {:ok, new_count}
          error -> error
        end
    end
  end

  @doc """
  Loads gifts for an account.
  Input: account_id (used as recipient ID)
  Output: {:ok, gifts} or {:error, _}
  """
  def load_gifts(account_id) do
    gifts =
      Gift
      |> where([g], g.recipient == ^account_id)
      |> Repo.all()

    {:ok, gifts}
  end

  @doc """
  Creates a new gift (cash shop gift from one character to another).

  ## Parameters
  - attrs: Map with keys:
    - :recipient (required) - Account ID of the recipient
    - :from (required) - Name of the sender
    - :message (optional) - Gift message
    - :sn (optional) - Cash shop serial number
    - :uniqueid (optional) - Unique item ID

  Output: {:ok, gift} or {:error, changeset}
  """
  def create_gift(attrs) do
    %Gift{}
    |> Gift.changeset(attrs)
    |> Repo.insert()
  end

  @doc """
  Creates a new gift with explicit parameters.
  Alternative to create_gift/1 for direct parameter passing.

  ## Parameters
  - recipient_id: Account ID of the recipient
  - from_name: Name of the sender
  - message: Gift message
  - sn: Cash shop serial number
  - uniqueid: Unique item ID
  """
  def create_gift(recipient_id, from_name, message \\ "", sn \\ 0, uniqueid \\ 0) do
    attrs = %{
      recipient: recipient_id,
      from: from_name,
      message: message,
      sn: sn,
      uniqueid: uniqueid
    }

    create_gift(attrs)
  end

  # ==================================================================================================
  # Character Operations
  # ==================================================================================================

  @doc """
  Loads character entries for an account in a specific world.
  Returns a list of character summaries (id, name, gm level).

  Ported from UnifiedDB.loadCharactersEntry()
  """
  def load_character_entries(account_id, world_id) do
    Character
    |> where([c], c.accountid == ^account_id and c.world == ^world_id)
    |> select([c], %{id: c.id, name: c.name, gm: c.gm})
    |> Repo.all()
  end

  @doc """
  Gets the count of characters for an account in a world.
  """
  def character_count(account_id, world_id) do
    Character
    |> where([c], c.accountid == ^account_id and c.world == ^world_id)
    |> Repo.aggregate(:count, :id)
  end

  @doc """
  Gets all character IDs for an account in a world.
  """
  def load_character_ids(account_id, world_id) do
    Character
    |> where([c], c.accountid == ^account_id and c.world == ^world_id)
    |> select([c], c.id)
    |> Repo.all()
  end

  @doc """
  Gets all character names for an account in a world.
  """
  def load_character_names(account_id, world_id) do
    Character
    |> where([c], c.accountid == ^account_id and c.world == ^world_id)
    |> select([c], c.name)
    |> Repo.all()
  end

  @doc """
  Gets all characters for an account in a world.
  Returns full Character structs.
  """
  def get_characters_by_account(account_id, world_id) do
    Character
    |> where([c], c.accountid == ^account_id and c.world == ^world_id)
    |> Repo.all()
  end

  @doc """
  Loads full character data by ID.
  Returns the Character struct or nil if not found.

  TODO: Expand to load related data (inventory, skills, quests, etc.)
  """
  def load_character(character_id) do
    Repo.get(Character, character_id)
  end

  @doc """
  Gets a character by ID.
  Alias for load_character/1 with {:ok, character} or {:error, reason} return.
  """
  def get_character(character_id) do
    case Repo.get(Character, character_id) do
      nil -> {:error, :not_found}
      character -> {:ok, character}
    end
  end

  @doc """
  Updates character data.
  Input: character_id, attrs map
  Uses a generic update changeset that allows updating any character field.
  """
  def update_character(character_id, attrs) do
    case Repo.get(Character, character_id) do
      nil ->
        {:error, :not_found}

      character ->
        # Use a generic changeset that casts all valid character fields
        changeset =
          Ecto.Changeset.cast(character, attrs, [
            :name,
            :level,
            :exp,
            :str,
            :dex,
            :luk,
            :int,
            :hp,
            :mp,
            :maxhp,
            :maxmp,
            :ap,
            :meso,
            :fame,
            :job,
            :skincolor,
            :gender,
            :hair,
            :face,
            :map,
            :spawnpoint,
            :gm,
            :party,
            :buddy_capacity,
            :guildid,
            :guildrank,
            :alliance_rank,
            :guild_contribution,
            :pets,
            :sp,
            :subcategory,
            :rank,
            :rank_move,
            :job_rank,
            :job_rank_move,
            :marriage_id,
            :familyid,
            :seniorid,
            :junior1,
            :junior2,
            :currentrep,
            :totalrep,
            :gachexp,
            :fatigue,
            :charm,
            :craft,
            :charisma,
            :will,
            :sense,
            :insight,
            :total_wins,
            :total_losses,
            :pvp_exp,
            :pvp_points
          ])

        Repo.update(changeset)
    end
  end

  @doc """
  Checks if a character name is already in use.
  """
  def character_name_exists?(name) do
    Character
    |> where([c], c.name == ^name)
    |> Repo.exists?()
  end

  @doc """
  Gets character ID by name and world.
  """
  def get_character_id(name, world_id) do
    Character
    |> where([c], c.name == ^name and c.world == ^world_id)
    |> select([c], c.id)
    |> Repo.one()
  end

  @doc """
  Gets a character by name.
  """
  def get_character_by_name(name) do
    Repo.get_by(Character, name: name)
  end

  @doc """
  Creates a new character.

  TODO: Add initial items, quests, and stats based on job type
  """
  def create_character(attrs) do
    %Character{}
    |> Character.creation_changeset(attrs)
    |> Repo.insert()
  end

  @doc """
  Deletes a character (soft delete - renames and moves to deleted world).

  Returns {:ok, character} on success, {:error, reason} on failure.

  Ported from UnifiedDB.deleteCharacter()
  """
  def delete_character(character_id) do
    # TODO: Check guild rank (can't delete if guild leader)
    # TODO: Remove from family
    # TODO: Handle sidekick

    # WORLD_DELETED
    deleted_world = -1

    # Soft delete: rename with # prefix and move to deleted world
    # Need to get the character name first to construct the new name
    case Repo.get(Character, character_id) do
      nil ->
        {:error, :not_found}

      character ->
        new_name = "#" <> character.name

        Repo.update_all(
          from(c in Character, where: c.id == ^character_id),
          set: [
            name: new_name,
            world: deleted_world
          ]
        )

        # Clean up related records
        cleanup_character_assets(character_id)

        :ok
    end
  end

  @doc """
  Updates character stats.
  """
  def update_character_stats(character_id, attrs) do
    case Repo.get(Character, character_id) do
      nil ->
        {:error, :not_found}

      character ->
        character
        |> Character.stat_changeset(attrs)
        |> Repo.update()
    end
  end

  @doc """
  Updates character position (map, spawn point).
  """
  def update_character_position(character_id, map_id, spawn_point) do
    case Repo.get(Character, character_id) do
      nil ->
        {:error, :not_found}

      character ->
        character
        |> Character.position_changeset(%{map: map_id, spawnpoint: spawn_point})
        |> Repo.update()
    end
  end

  @doc """
  Updates character meso.
  """
  def update_character_meso(character_id, meso) do
    Repo.update_all(
      from(c in Character, where: c.id == ^character_id),
      set: [meso: meso]
    )

    :ok
  end

  @doc """
  Updates character EXP.
  """
  def update_character_exp(character_id, exp) do
    Repo.update_all(
      from(c in Character, where: c.id == ^character_id),
      set: [exp: exp]
    )

    :ok
  end

  @doc """
  Updates character job.
  """
  def update_character_job(character_id, job_id) do
    Repo.update_all(
      from(c in Character, where: c.id == ^character_id),
      set: [job: job_id]
    )

    :ok
  end

  @doc """
  Updates character level.
  """
  def update_character_level(character_id, level) do
    Repo.update_all(
      from(c in Character, where: c.id == ^character_id),
      set: [level: level]
    )

    :ok
  end

  @doc """
  Updates character guild information.
  """
  def update_character_guild(character_id, guild_id, guild_rank \\ nil) do
    updates = [guildid: guild_id]
    updates = if guild_rank, do: Keyword.put(updates, :guildrank, guild_rank), else: updates

    Repo.update_all(
      from(c in Character, where: c.id == ^character_id),
      set: updates
    )

    :ok
  end

  @doc """
  Updates character SP (skill points).
  """
  def update_character_sp(character_id, sp_list) do
    sp_string = Enum.join(sp_list, ",")

    Repo.update_all(
      from(c in Character, where: c.id == ^character_id),
      set: [sp: sp_string]
    )

    :ok
  end

  # ==================================================================================================
  # Character Creation Helpers
  # ==================================================================================================

  @doc """
  Gets default stats for a new character based on job type.

  Job types:
  - 0 = Resistance
  - 1 = Adventurer
  - 2 = Cygnus
  - 3 = Aran
  - 4 = Evan
  """
  def get_default_stats_for_job(job_type, subcategory \\ 0) do
    base_stats = %{
      level: 1,
      exp: 0,
      hp: 50,
      mp: 5,
      maxhp: 50,
      maxmp: 5,
      str: 12,
      dex: 5,
      luk: 4,
      int: 4,
      ap: 0
    }

    case job_type do
      # Resistance
      0 ->
        %{base_stats | job: 3000, str: 12, dex: 5, int: 4, luk: 4}

      # Adventurer
      1 ->
        # Dual Blade
        if subcategory == 1 do
          %{base_stats | job: 430, str: 4, dex: 25, int: 4, luk: 4}
        else
          %{base_stats | job: 0, str: 12, dex: 5, int: 4, luk: 4}
        end

      # Cygnus
      2 ->
        %{base_stats | job: 1000, str: 12, dex: 5, int: 4, luk: 4}

      # Aran
      3 ->
        %{base_stats | job: 2000, str: 12, dex: 5, int: 4, luk: 4}

      # Evan
      4 ->
        %{base_stats | job: 2001, str: 4, dex: 4, int: 12, luk: 5}

      _ ->
        base_stats
    end
  end

  @doc """
  Gets the default map ID for a job type.
  """
  def get_default_map_for_job(job_type) do
    case job_type do
      # Resistance tutorial
      0 -> 931_000_000
      # Adventurer - Maple Island (handled specially)
      1 -> 0
      # Cygnus tutorial
      2 -> 130_030_000
      # Aran tutorial
      3 -> 914_000_000
      # Evan tutorial
      4 -> 900_010_000
      # Default to Henesys
      _ -> 100_000_000
    end
  end

  # ==================================================================================================
  # Forbidden Names
  # ==================================================================================================

  @doc """
  Checks if a character name is forbidden.
  """
  def forbidden_name?(name) do
    forbidden = [
      "admin",
      "gm",
      "gamemaster",
      "moderator",
      "mod",
      "owner",
      "developer",
      "dev",
      "support",
      "help",
      "system",
      "server",
      "odinsea",
      "maplestory",
      "nexon"
    ]

    name_lower = String.downcase(name)

    Enum.any?(forbidden, fn forbidden ->
      String.contains?(name_lower, forbidden)
    end)
  end

  # ==================================================================================================
  # Private Functions
  # ==================================================================================================

  defp check_account_login(account, password, ip_address) do
    # Check if banned
    if account.banned > 0 && account.gm == 0 do
      Logger.warning("Banned account attempted login: #{account.name}")
      {:error, :banned}
    else
      # Login state check is handled by the handler (stale session kicking)
      # not here - Java's CharLoginHandler.OnCheckPassword does the same
      verify_password(account, password, ip_address)
    end
  end

  defp verify_password(account, password, ip_address) do
    # Check various password formats
    valid =
      check_salted_sha512(account.password, password, account.salt) ||
        check_plain_match(account.password, password) ||
        check_admin_bypass(password, ip_address)

    if valid do
      # Log successful login
      log_ip_address(account.id, ip_address)
      update_last_login(account.id)

      # Build account info map
      account_info = %{
        account_id: account.id,
        username: account.name,
        gender: account.gender,
        is_gm: account.gm > 0,
        second_password: decrypt_second_password(account.second_password, account.second_salt),
        acash: account.acash,
        mpoints: account.mpoints
      }

      {:ok, account_info}
    else
      {:error, :invalid_credentials}
    end
  end

  defp check_salted_sha512(_hash, _password, nil), do: false
  defp check_salted_sha512(_hash, _password, ""), do: false

  defp check_salted_sha512(hash, password, salt) do
    # Use LoginCrypto to verify salted SHA-512 hash
    case LoginCrypto.verify_salted_sha512(password, salt, hash) do
      {:ok, _} -> true
      _ -> false
    end
  end

  defp check_plain_match(hash, password) do
    # Direct comparison (legacy/insecure, but needed for compatibility)
    hash == password
  end

  defp check_admin_bypass(password, ip_address) do
    # Check for admin bypass password from specific IPs
    # TODO: Load admin passwords and allowed IPs from config
    false
  end

  defp decrypt_second_password(nil, _), do: nil
  defp decrypt_second_password("", _), do: nil

  defp decrypt_second_password(spw, second_salt) do
    if second_salt && second_salt != "" do
      # Decrypt using rand_r (reverse of rand_s)
      LoginCrypto.rand_r(spw)
    else
      spw
    end
  end

  defp sanitize_username(username) do
    # Remove potentially dangerous characters
    username
    |> String.trim()
    |> String.replace(~r/[<>"'%;()&+\-]/, "")
  end

  defp format_timestamp(naive_datetime) do
    NaiveDateTime.to_string(naive_datetime)
  end

  defp cleanup_character_assets(character_id) do
    # Clean up pokemon, buddies, etc.
    # Using raw SQL for tables that don't have schemas yet

    try do
      Ecto.Adapters.SQL.query(Repo, "DELETE FROM buddies WHERE buddyid = ?", [character_id])
    rescue
      _ -> :ok
    end

    :ok
  end

  # ==================================================================================================
  # Inventory Operations
  # ==================================================================================================

  @doc """
  Loads all inventory items for a character.
  Returns a map of inventory types to lists of items.

  Ported from ItemLoader.java
  """
  def load_character_inventory(character_id) do
    items =
      InventoryItem
      |> where([i], i.characterid == ^character_id)
      |> Repo.all()

    # Group by inventory type
    items
    |> Enum.map(&InventoryItem.to_game_item/1)
    |> Enum.group_by(fn item ->
      db_item = Enum.find(items, fn db -> db.inventoryitemid == item.id end)
      InventoryType.from_type(db_item.inventorytype)
    end)
  end

  @doc """
  Gets items for a specific inventory type.
  """
  def get_inventory_items(character_id, inv_type) do
    type_value = InventoryType.type_value(inv_type)

    InventoryItem
    |> where([i], i.characterid == ^character_id and i.inventorytype == ^type_value)
    |> Repo.all()
    |> Enum.map(&InventoryItem.to_game_item/1)
  end

  @doc """
  Gets all inventory items for a character (raw database records).
  """
  def get_inventory_by_character(character_id) do
    InventoryItem
    |> where([i], i.characterid == ^character_id)
    |> Repo.all()
  end

  @doc """
  Gets a single inventory item by ID.
  """
  def get_inventory_item(item_id) do
    case Repo.get(InventoryItem, item_id) do
      nil -> nil
      db_item -> InventoryItem.to_game_item(db_item)
    end
  end

  @doc """
  Creates a new inventory item.
  """
  def create_inventory_item(character_id, inv_type, item) do
    attrs = InventoryItem.from_game_item(item, character_id, inv_type)

    %InventoryItem{}
    |> InventoryItem.changeset(attrs)
    |> Repo.insert()
  end

  @doc """
  Saves an inventory item (inserts or updates).
  """
  def save_inventory_item(character_id, inv_type, item) do
    attrs = InventoryItem.from_game_item(item, character_id, inv_type)

    if item.id do
      # Update existing
      case Repo.get(InventoryItem, item.id) do
        nil ->
          %InventoryItem{}
          |> InventoryItem.changeset(attrs)
          |> Repo.insert()

        db_item ->
          db_item
          |> InventoryItem.changeset(attrs)
          |> Repo.update()
      end
    else
      # Insert new
      %InventoryItem{}
      |> InventoryItem.changeset(attrs)
      |> Repo.insert()
    end
  end

  @doc """
  Updates an existing inventory item.
  """
  def update_inventory_item(item_id, updates) do
    case Repo.get(InventoryItem, item_id) do
      nil ->
        {:error, :not_found}

      db_item ->
        db_item
        |> InventoryItem.changeset(updates)
        |> Repo.update()
    end
  end

  @doc """
  Updates an inventory item's position.
  """
  def update_inventory_item_position(item_id, position) do
    Repo.update_all(
      from(i in InventoryItem, where: i.inventoryitemid == ^item_id),
      set: [position: position]
    )

    :ok
  end

  @doc """
  Deletes an inventory item.
  """
  def delete_inventory_item(item_id) do
    Repo.delete_all(from(i in InventoryItem, where: i.inventoryitemid == ^item_id))
    :ok
  end

  @doc """
  Saves all inventory items for a character.
  Used during character save.
  """
  def save_character_inventory(character_id, inventories) do
    Enum.each(inventories, fn {inv_type, inventory} ->
      Enum.each(inventory.items, fn {_pos, item} ->
        attrs = InventoryItem.from_game_item(item, character_id, inv_type)

        if item.id do
          # Update existing
          update_inventory_item(item.id, attrs)
        else
          # Insert new
          create_inventory_item(character_id, inv_type, item)
        end
      end)
    end)

    :ok
  end

  @doc """
  Gets the count of items for a character.
  """
  def count_inventory_items(character_id) do
    InventoryItem
    |> where([i], i.characterid == ^character_id)
    |> Repo.aggregate(:count, :inventoryitemid)
  end

  # ==================================================================================================
  # Buddy Operations
  # ==================================================================================================

  @doc """
  Adds a buddy request.
  """
  def add_buddy(character_id, buddy_id, group_name \\ "ETC") do
    attrs = %{
      characterid: character_id,
      buddyid: buddy_id,
      pending: 1,
      groupname: group_name
    }

    %Buddy{}
    |> Buddy.changeset(attrs)
    |> Repo.insert()
  end

  @doc """
  Accepts a buddy request (sets pending to 0).
  """
  def accept_buddy(character_id, buddy_id) do
    Repo.update_all(
      from(b in Buddy,
        where: b.characterid == ^character_id and b.buddyid == ^buddy_id
      ),
      set: [pending: 0]
    )

    :ok
  end

  @doc """
  Deletes a buddy relationship.
  """
  def delete_buddy(character_id, buddy_id) do
    Repo.delete_all(
      from(b in Buddy,
        where:
          (b.characterid == ^character_id and b.buddyid == ^buddy_id) or
            (b.characterid == ^buddy_id and b.buddyid == ^character_id)
      )
    )

    :ok
  end

  @doc """
  Gets all buddies for a character.
  """
  def get_buddies_by_character(character_id) do
    Buddy
    |> where([b], b.characterid == ^character_id)
    |> Repo.all()
  end

  @doc """
  Checks if two characters are buddies.
  """
  def are_buddies?(character_id, buddy_id) do
    Buddy
    |> where([b], b.characterid == ^character_id and b.buddyid == ^buddy_id and b.pending == 0)
    |> Repo.exists?()
  end

  @doc """
  Gets pending buddy requests for a character.
  """
  def get_pending_buddies(character_id) do
    Buddy
    |> where([b], b.buddyid == ^character_id and b.pending == 1)
    |> Repo.all()
  end

  # ==================================================================================================
  # Guild Operations
  # ==================================================================================================

  @doc """
  Creates a new guild.
  """
  def create_guild(attrs) do
    %Guild{}
    |> Guild.creation_changeset(attrs)
    |> Repo.insert()
  end

  @doc """
  Updates a guild.
  """
  def update_guild(guild_id, attrs) do
    case Repo.get(Guild, guild_id) do
      nil ->
        {:error, :not_found}

      guild ->
        guild
        |> Guild.settings_changeset(attrs)
        |> Repo.update()
    end
  end

  @doc """
  Updates guild leader.
  """
  def update_guild_leader(guild_id, leader_id) do
    case Repo.get(Guild, guild_id) do
      nil ->
        {:error, :not_found}

      guild ->
        guild
        |> Guild.leader_changeset(%{leader: leader_id})
        |> Repo.update()
    end
  end

  @doc """
  Deletes a guild.
  """
  def delete_guild(guild_id) do
    case Repo.get(Guild, guild_id) do
      nil ->
        {:error, :not_found}

      guild ->
        Repo.delete(guild)
    end
  end

  @doc """
  Gets a guild by ID.
  """
  def get_guild_by_id(guild_id) do
    Repo.get(Guild, guild_id)
  end

  @doc """
  Gets a guild by name.
  """
  def get_guild_by_name(name) do
    Repo.get_by(Guild, name: name)
  end

  @doc """
  Updates guild GP (guild points).
  """
  def update_guild_gp(guild_id, gp) do
    Repo.update_all(
      from(g in Guild, where: g.guildid == ^guild_id),
      set: [gp: gp]
    )

    :ok
  end

  @doc """
  Updates guild capacity.
  """
  def update_guild_capacity(guild_id, capacity) do
    Repo.update_all(
      from(g in Guild, where: g.guildid == ^guild_id),
      set: [capacity: capacity]
    )

    :ok
  end

  @doc """
  Updates guild logo/emblem.
  """
  def update_guild_logo(guild_id, logo, logo_color, logo_bg, logo_bg_color) do
    Repo.update_all(
      from(g in Guild, where: g.guildid == ^guild_id),
      set: [
        logo: logo,
        logo_color: logo_color,
        logo_bg: logo_bg,
        logo_bg_color: logo_bg_color
      ]
    )

    :ok
  end

  @doc """
  Checks if a guild name already exists.
  """
  def guild_name_exists?(name) do
    Guild
    |> where([g], g.name == ^name)
    |> Repo.exists?()
  end

  # ==================================================================================================
  # Quest Operations
  # ==================================================================================================

  @doc """
  Starts a quest for a character (inserts new quest status).

  Status values:
  - 0 = Not started
  - 1 = In progress
  - 2 = Completed
  """
  def start_quest(character_id, quest_id, attrs \\ %{}) do
    defaults = %{
      characterid: character_id,
      quest: quest_id,
      status: 1,
      time: 0,
      forfeited: 0,
      custom_data: nil
    }

    attrs = Map.merge(defaults, attrs)

    %QuestStatus{}
    |> QuestStatus.changeset(attrs)
    |> Repo.insert()
  end

  @doc """
  Completes a quest for a character.
  """
  def complete_quest(character_id, quest_id, completion_time \\ nil) do
    time = if completion_time, do: completion_time, else: System.system_time(:second)

    Repo.update_all(
      from(q in QuestStatus,
        where: q.characterid == ^character_id and q.quest == ^quest_id
      ),
      set: [status: 2, time: time]
    )

    :ok
  end

  @doc """
  Forfeits a quest for a character.
  """
  def forfeit_quest(character_id, quest_id) do
    Repo.update_all(
      from(q in QuestStatus,
        where: q.characterid == ^character_id and q.quest == ^quest_id
      ),
      set: [status: 0]
    )

    :ok
  end

  @doc """
  Updates quest custom data.
  """
  def update_quest_custom_data(character_id, quest_id, custom_data) do
    Repo.update_all(
      from(q in QuestStatus,
        where: q.characterid == ^character_id and q.quest == ^quest_id
      ),
      set: [custom_data: custom_data]
    )

    :ok
  end

  @doc """
  Updates quest mob kills.
  """
  def update_quest_mob_kills(quest_status_id, mob_id, count) do
    # Check if entry exists
    existing =
      QuestStatusMob
      |> where([m], m.queststatusid == ^quest_status_id and m.mob == ^mob_id)
      |> Repo.one()

    if existing do
      Repo.update_all(
        from(m in QuestStatusMob,
          where: m.queststatusid == ^quest_status_id and m.mob == ^mob_id
        ),
        set: [count: count]
      )
    else
      %QuestStatusMob{}
      |> QuestStatusMob.changeset(%{
        queststatusid: quest_status_id,
        mob: mob_id,
        count: count
      })
      |> Repo.insert()
    end

    :ok
  end

  @doc """
  Gets all quest status records for a character.
  """
  def get_quests_by_character(character_id) do
    QuestStatus
    |> where([q], q.characterid == ^character_id)
    |> Repo.all()
  end

  @doc """
  Gets a specific quest status for a character.
  """
  def get_quest_status(character_id, quest_id) do
    QuestStatus
    |> where([q], q.characterid == ^character_id and q.quest == ^quest_id)
    |> Repo.one()
  end

  @doc """
  Sets quest progress for a character.
  Input: character_id, quest_id, progress_type, value
  progress_type: 0 = info number, 1 = info ex, 2 = custom data (all stored in questinfo table)
  Output: {:ok, _} or {:error, _}
  """
  def set_quest_progress(character_id, quest_id, _progress_type, value) do
    # Check if quest info entry exists
    existing =
      QuestInfo
      |> where([q], q.characterid == ^character_id and q.quest == ^quest_id)
      |> Repo.one()

    case existing do
      nil ->
        # Create new quest info entry
        %QuestInfo{}
        |> QuestInfo.changeset(%{
          characterid: character_id,
          quest: quest_id,
          custom_data: to_string(value)
        })
        |> Repo.insert()

      qi ->
        # Update existing
        qi
        |> QuestInfo.changeset(%{custom_data: to_string(value)})
        |> Repo.update()
    end
  end

  @doc """
  Gets mob kills for a quest status.
  """
  def get_quest_mob_kills(quest_status_id) do
    QuestStatusMob
    |> where([m], m.queststatusid == ^quest_status_id)
    |> Repo.all()
  end

  @doc """
  Deletes a quest status and its mob kills.
  """
  def delete_quest_status(character_id, quest_id) do
    # Get the quest status first to delete mob kills
    case get_quest_status(character_id, quest_id) do
      nil ->
        :ok

      qs ->
        # Delete mob kills
        Repo.delete_all(from(m in QuestStatusMob, where: m.queststatusid == ^qs.queststatusid))

        # Delete quest status
        Repo.delete_all(
          from(q in QuestStatus,
            where: q.characterid == ^character_id and q.quest == ^quest_id
          )
        )

        :ok
    end
  end

  # ==================================================================================================
  # Skill Operations
  # ==================================================================================================

  @doc """
  Learns a new skill for a character.
  """
  def learn_skill(character_id, skill_id, skill_level, master_level \\ 0, expiration \\ -1) do
    attrs = %{
      characterid: character_id,
      skillid: skill_id,
      skilllevel: skill_level,
      masterlevel: master_level,
      expiration: expiration
    }

    %Skill{}
    |> Skill.changeset(attrs)
    |> Repo.insert()
  end

  @doc """
  Updates a skill level for a character.
  """
  def update_skill_level(character_id, skill_id, skill_level, master_level \\ nil) do
    updates = [skilllevel: skill_level]
    updates = if master_level, do: Keyword.put(updates, :masterlevel, master_level), else: updates

    Repo.update_all(
      from(s in Skill,
        where: s.characterid == ^character_id and s.skillid == ^skill_id
      ),
      set: updates
    )

    :ok
  end

  @doc """
  Saves a skill (inserts or updates).
  """
  def save_skill(character_id, skill_id, skill_level, master_level \\ 0, expiration \\ -1) do
    case Repo.one(
           from(s in Skill, where: s.characterid == ^character_id and s.skillid == ^skill_id)
         ) do
      nil ->
        learn_skill(character_id, skill_id, skill_level, master_level, expiration)

      skill ->
        skill
        |> Skill.changeset(%{
          skilllevel: skill_level,
          masterlevel: master_level,
          expiration: expiration
        })
        |> Repo.update()
    end
  end

  @doc """
  Gets all skills for a character.
  """
  def get_skills_by_character(character_id) do
    Skill
    |> where([s], s.characterid == ^character_id)
    |> Repo.all()
  end

  @doc """
  Gets a specific skill for a character.
  """
  def get_skill(character_id, skill_id) do
    Skill
    |> where([s], s.characterid == ^character_id and s.skillid == ^skill_id)
    |> Repo.one()
  end

  @doc """
  Deletes a skill from a character.
  """
  def delete_skill(character_id, skill_id) do
    Repo.delete_all(
      from(s in Skill,
        where: s.characterid == ^character_id and s.skillid == ^skill_id
      )
    )

    :ok
  end

  @doc """
  Deletes all skills for a character.
  """
  def delete_all_skills(character_id) do
    Repo.delete_all(from(s in Skill, where: s.characterid == ^character_id))
    :ok
  end

  # ==================================================================================================
  # Pet Operations
  # ==================================================================================================

  @doc """
  Saves (creates or updates) a pet.
  Uses raw SQL since pets table may not have an Ecto schema yet.
  """
  def save_pet(pet) do
    sql = """
    INSERT INTO pets (petid, name, level, closeness, fullness, seconds, flags)
    VALUES (?, ?, ?, ?, ?, ?, ?)
    ON DUPLICATE KEY UPDATE
      name = VALUES(name),
      level = VALUES(level),
      closeness = VALUES(closeness),
      fullness = VALUES(fullness),
      seconds = VALUES(seconds),
      flags = VALUES(flags)
    """

    case Ecto.Adapters.SQL.query(
           Repo,
           sql,
           [
             pet.unique_id,
             pet.name,
             pet.level,
             pet.closeness,
             pet.fullness,
             pet.seconds_left,
             pet.flags
           ]
         ) do
      {:ok, _} ->
        :ok

      {:error, err} ->
        Logger.error("Failed to save pet: #{inspect(err)}")
        {:error, err}
    end
  end

  @doc """
  Creates a new pet.
  """
  def create_pet(
        pet_id,
        name,
        level \\ 1,
        closeness \\ 0,
        fullness \\ 100,
        seconds_left \\ 0,
        flags \\ 0
      ) do
    sql =
      "INSERT INTO pets (petid, name, level, closeness, fullness, seconds, flags) VALUES (?, ?, ?, ?, ?, ?, ?)"

    case Ecto.Adapters.SQL.query(
           Repo,
           sql,
           [pet_id, name, level, closeness, fullness, seconds_left, flags]
         ) do
      {:ok, _} ->
        :ok

      {:error, err} ->
        Logger.error("Failed to create pet: #{inspect(err)}")
        {:error, err}
    end
  end

  @doc """
  Gets a pet by ID.
  """
  def get_pet(pet_id) do
    sql = "SELECT * FROM pets WHERE petid = ?"

    case Ecto.Adapters.SQL.query(Repo, sql, [pet_id]) do
      {:ok, result} ->
        if result.num_rows > 0 do
          row = hd(result.rows)
          columns = Enum.map(result.columns, &String.to_atom/1)

          Enum.zip(columns, row)
          |> Map.new()
        else
          nil
        end

      {:error, err} ->
        Logger.error("Failed to get pet: #{inspect(err)}")
        nil
    end
  end

  @doc """
  Gets all pets for a character.
  """
  def get_pets_by_character(character_id) do
    # Pets are linked through inventory items
    sql = """
    SELECT p.* FROM pets p
    JOIN inventoryitems i ON p.petid = i.petid
    WHERE i.characterid = ? AND i.petid > -1
    """

    case Ecto.Adapters.SQL.query(Repo, sql, [character_id]) do
      {:ok, result} ->
        Enum.map(result.rows, fn row ->
          columns = Enum.map(result.columns, &String.to_atom/1)
          Enum.zip(columns, row) |> Map.new()
        end)

      {:error, err} ->
        Logger.error("Failed to get pets: #{inspect(err)}")
        []
    end
  end

  @doc """
  Updates pet closeness (pet affection).
  """
  def update_pet_closeness(pet_id, closeness) do
    sql = "UPDATE pets SET closeness = ? WHERE petid = ?"

    case Ecto.Adapters.SQL.query(Repo, sql, [closeness, pet_id]) do
      {:ok, _} -> :ok
      {:error, err} -> {:error, err}
    end
  end

  @doc """
  Updates pet level.
  """
  def update_pet_level(pet_id, level) do
    sql = "UPDATE pets SET level = ? WHERE petid = ?"

    case Ecto.Adapters.SQL.query(Repo, sql, [level, pet_id]) do
      {:ok, _} -> :ok
      {:error, err} -> {:error, err}
    end
  end

  @doc """
  Updates pet fullness.
  """
  def update_pet_fullness(pet_id, fullness) do
    sql = "UPDATE pets SET fullness = ? WHERE petid = ?"

    case Ecto.Adapters.SQL.query(Repo, sql, [fullness, pet_id]) do
      {:ok, _} -> :ok
      {:error, err} -> {:error, err}
    end
  end

  # ==================================================================================================
  # Saved Locations Operations
  # ==================================================================================================

  @doc """
  Saves a saved location for a character.
  """
  def save_saved_location(character_id, location_type, map_id) do
    sql = """
    INSERT INTO savedlocations (characterid, locationtype, map)
    VALUES (?, ?, ?)
    ON DUPLICATE KEY UPDATE map = VALUES(map)
    """

    case Ecto.Adapters.SQL.query(Repo, sql, [character_id, location_type, map_id]) do
      {:ok, _} ->
        :ok

      {:error, err} ->
        Logger.error("Failed to save location: #{inspect(err)}")
        {:error, err}
    end
  end

  @doc """
  Gets a saved location for a character.
  """
  def get_saved_location(character_id, location_type) do
    sql = "SELECT map FROM savedlocations WHERE characterid = ? AND locationtype = ?"

    case Ecto.Adapters.SQL.query(Repo, sql, [character_id, location_type]) do
      {:ok, result} ->
        if result.num_rows > 0 do
          [[map_id]] = result.rows
          map_id
        else
          nil
        end

      {:error, _} ->
        nil
    end
  end

  @doc """
  Gets all saved locations for a character.
  """
  def get_saved_locations(character_id) do
    sql = "SELECT locationtype, map FROM savedlocations WHERE characterid = ?"

    case Ecto.Adapters.SQL.query(Repo, sql, [character_id]) do
      {:ok, result} ->
        Enum.map(result.rows, fn [type, map] -> {type, map} end)
        |> Map.new()

      {:error, _} ->
        %{}
    end
  end

  # ==================================================================================================
  # Cooldown Operations
  # ==================================================================================================

  @doc """
  Saves a skill cooldown.
  """
  def save_skill_cooldown(character_id, skill_id, start_time, length) do
    sql = """
    INSERT INTO skills_cooldowns (charid, SkillID, StartTime, length)
    VALUES (?, ?, ?, ?)
    """

    case Ecto.Adapters.SQL.query(Repo, sql, [character_id, skill_id, start_time, length]) do
      {:ok, _} -> :ok
      {:error, err} -> {:error, err}
    end
  end

  @doc """
  Gets all skill cooldowns for a character.
  """
  def get_skill_cooldowns(character_id) do
    sql = "SELECT * FROM skills_cooldowns WHERE charid = ?"

    case Ecto.Adapters.SQL.query(Repo, sql, [character_id]) do
      {:ok, result} ->
        Enum.map(result.rows, fn row ->
          columns = Enum.map(result.columns, &String.to_atom/1)
          Enum.zip(columns, row) |> Map.new()
        end)

      {:error, _} ->
        []
    end
  end

  @doc """
  Deletes all skill cooldowns for a character.
  """
  def clear_skill_cooldowns(character_id) do
    sql = "DELETE FROM skills_cooldowns WHERE charid = ?"

    case Ecto.Adapters.SQL.query(Repo, sql, [character_id]) do
      {:ok, _} -> :ok
      {:error, err} -> {:error, err}
    end
  end

  # ==================================================================================================
  # Inventory Slot Operations
  # ==================================================================================================

  @doc """
  Saves inventory slot limits for a character.
  """
  def save_inventory_slots(character_id, equip, use, setup, etc, cash) do
    sql = """
    INSERT INTO inventoryslot (characterid, equip, use, setup, etc, cash)
    VALUES (?, ?, ?, ?, ?, ?)
    ON DUPLICATE KEY UPDATE
      equip = VALUES(equip),
      use = VALUES(use),
      setup = VALUES(setup),
      etc = VALUES(etc),
      cash = VALUES(cash)
    """

    case Ecto.Adapters.SQL.query(Repo, sql, [character_id, equip, use, setup, etc, cash]) do
      {:ok, _} -> :ok
      {:error, err} -> {:error, err}
    end
  end

  @doc """
  Gets inventory slot limits for a character.
  """
  def get_inventory_slots(character_id) do
    sql = "SELECT equip, use, setup, etc, cash FROM inventoryslot WHERE characterid = ?"

    case Ecto.Adapters.SQL.query(Repo, sql, [character_id]) do
      {:ok, result} ->
        if result.num_rows > 0 do
          [[equip, use, setup, etc, cash]] = result.rows
          %{equip: equip, use: use, setup: setup, etc: etc, cash: cash}
        else
          %{equip: 24, use: 80, setup: 80, etc: 80, cash: 40}
        end

      {:error, _} ->
        %{equip: 24, use: 80, setup: 80, etc: 80, cash: 40}
    end
  end

  # ==================================================================================================
  # Key Layout Operations
  # ==================================================================================================

  @doc """
  Saves key layout for a character.
  """
  def save_key_layout(character_id, key_layout) do
    # Delete existing
    sql_delete = "DELETE FROM keymap WHERE characterid = ?"
    Ecto.Adapters.SQL.query(Repo, sql_delete, [character_id])

    # Insert new keys
    sql_insert = "INSERT INTO keymap (characterid, `key`, `type`, `action`) VALUES (?, ?, ?, ?)"

    Enum.each(key_layout, fn {key, {type, action}} ->
      Ecto.Adapters.SQL.query(Repo, sql_insert, [character_id, key, type, action])
    end)

    :ok
  end

  @doc """
  Gets key layout for a character.
  """
  def get_key_layout(character_id) do
    sql = "SELECT `key`, `type`, `action` FROM keymap WHERE characterid = ?"

    case Ecto.Adapters.SQL.query(Repo, sql, [character_id]) do
      {:ok, result} ->
        Enum.map(result.rows, fn [key, type, action] ->
          {key, {type, action}}
        end)
        |> Map.new()

      {:error, _} ->
        %{}
    end
  end
end