defmodule Towerops.Gaiia do
  @moduledoc """
  Context for querying Gaiia integration data.
  """

  import Ecto.Query

  alias Towerops.Devices.Device
  alias Towerops.Gaiia.Account
  alias Towerops.Gaiia.BillingSubscription
  alias Towerops.Gaiia.InventoryItem
  alias Towerops.Gaiia.NetworkSite
  alias Towerops.Repo
  alias Towerops.Sites.Site

  # --- Accounts ---

  def list_accounts(organization_id) do
    Account
    |> where(organization_id: ^organization_id)
    |> order_by(:name)
    |> Repo.all()
  end

  def get_account(organization_id, gaiia_id) do
    Repo.get_by(Account, organization_id: organization_id, gaiia_id: gaiia_id)
  end

  def upsert_account(organization_id, attrs) do
    attrs = Map.put(attrs, :organization_id, organization_id)

    %Account{}
    |> Account.changeset(attrs)
    |> Repo.insert(
      on_conflict: {:replace_all_except, [:id, :organization_id, :gaiia_id, :inserted_at]},
      conflict_target: [:organization_id, :gaiia_id],
      returning: true
    )
  end

  # --- Network Sites ---

  def list_network_sites(organization_id) do
    NetworkSite
    |> where(organization_id: ^organization_id)
    |> order_by(:name)
    |> Repo.all()
  end

  def get_network_site(organization_id, gaiia_id) do
    Repo.get_by(NetworkSite, organization_id: organization_id, gaiia_id: gaiia_id)
  end

  def upsert_network_site(organization_id, attrs) do
    attrs = Map.put(attrs, :organization_id, organization_id)

    %NetworkSite{}
    |> NetworkSite.changeset(attrs)
    |> Repo.insert(
      on_conflict: {:replace_all_except, [:id, :organization_id, :gaiia_id, :site_id, :inserted_at]},
      conflict_target: [:organization_id, :gaiia_id],
      returning: true
    )
  end

  def update_network_site_mapping(%NetworkSite{} = network_site, attrs) do
    network_site
    |> NetworkSite.changeset(attrs)
    |> Repo.update()
  end

  # --- Inventory Items ---

  def list_inventory_items(organization_id) do
    InventoryItem
    |> where(organization_id: ^organization_id)
    |> order_by(:name)
    |> Repo.all()
  end

  def get_inventory_item(organization_id, gaiia_id) do
    Repo.get_by(InventoryItem, organization_id: organization_id, gaiia_id: gaiia_id)
  end

  def upsert_inventory_item(organization_id, attrs) do
    attrs = Map.put(attrs, :organization_id, organization_id)

    %InventoryItem{}
    |> InventoryItem.changeset(attrs)
    |> Repo.insert(
      on_conflict: {:replace_all_except, [:id, :organization_id, :gaiia_id, :device_id, :inserted_at]},
      conflict_target: [:organization_id, :gaiia_id],
      returning: true
    )
  end

  def update_inventory_item_mapping(%InventoryItem{} = inventory_item, attrs) do
    inventory_item
    |> InventoryItem.changeset(attrs)
    |> Repo.update()
  end

  # --- Billing Subscriptions ---

  def list_billing_subscriptions(organization_id) do
    BillingSubscription
    |> where(organization_id: ^organization_id)
    |> order_by(:product_name)
    |> Repo.all()
  end

  def list_billing_subscriptions_for_account(organization_id, account_gaiia_id) do
    BillingSubscription
    |> where(organization_id: ^organization_id, account_gaiia_id: ^account_gaiia_id)
    |> order_by(:product_name)
    |> Repo.all()
  end

  def get_billing_subscription(organization_id, gaiia_id) do
    Repo.get_by(BillingSubscription, organization_id: organization_id, gaiia_id: gaiia_id)
  end

  def upsert_billing_subscription(organization_id, attrs) do
    attrs = Map.put(attrs, :organization_id, organization_id)

    %BillingSubscription{}
    |> BillingSubscription.changeset(attrs)
    |> Repo.insert(
      on_conflict: {:replace_all_except, [:id, :organization_id, :gaiia_id, :inserted_at]},
      conflict_target: [:organization_id, :gaiia_id],
      returning: true
    )
  end

  # --- Reverse lookups (Towerops entity → Gaiia entity) ---

  @doc "Find the Gaiia inventory item mapped to a Towerops device."
  def get_inventory_item_for_device(device_id) do
    InventoryItem
    |> where(device_id: ^device_id)
    |> limit(1)
    |> Repo.one()
  end

  @doc "Find the Gaiia network site mapped to a Towerops site."
  def get_network_site_for_site(site_id) do
    NetworkSite
    |> where(site_id: ^site_id)
    |> limit(1)
    |> Repo.one()
  end

  # --- Match Suggestions ---

  @doc """
  Suggest Towerops Sites that might match a Gaiia Network Site.

  Matches by case-insensitive name containment (bidirectional).
  Returns a list of `%{entity: site, match_type: "name", confidence: :medium}`.
  """
  def suggest_site_matches(_organization_id, %NetworkSite{name: nil}), do: []
  def suggest_site_matches(_organization_id, %NetworkSite{name: ""}), do: []

  def suggest_site_matches(organization_id, %NetworkSite{name: gaiia_name}) do
    search_term = "%#{gaiia_name}%"

    # Find sites where either name contains the other (bidirectional)
    Site
    |> where(organization_id: ^organization_id)
    |> where([s], ilike(s.name, ^search_term) or ilike(^gaiia_name, fragment("'%' || ? || '%'", s.name)))
    |> Repo.all()
    |> Enum.map(fn site -> %{entity: site, match_type: "name", confidence: :medium} end)
  end

  @doc """
  Suggest Towerops Devices that might match a Gaiia Inventory Item.

  Matching strategies (in confidence order):
  - IP address exact match (:high confidence)
  - Name containment match (:low confidence)

  Returns a list of `%{entity: device, match_type: type, confidence: level}`,
  sorted by confidence (high first). Each device appears at most once per match type.
  """
  def suggest_device_matches(organization_id, %InventoryItem{} = item) do
    ip_matches = find_ip_matches(organization_id, item.ip_address)
    name_matches = find_name_matches(organization_id, item.name)

    (ip_matches ++ name_matches)
    |> Enum.uniq_by(fn %{entity: d, match_type: t} -> {d.id, t} end)
    |> Enum.sort_by(fn %{confidence: c} -> confidence_rank(c) end)
  end

  defp find_ip_matches(_organization_id, nil), do: []
  defp find_ip_matches(_organization_id, ""), do: []

  defp find_ip_matches(organization_id, ip_address) do
    Device
    |> where(organization_id: ^organization_id, ip_address: ^ip_address)
    |> Repo.all()
    |> Repo.preload(:site)
    |> Enum.map(fn device -> %{entity: device, match_type: "ip", confidence: :high} end)
  end

  defp find_name_matches(_organization_id, nil), do: []
  defp find_name_matches(_organization_id, ""), do: []

  defp find_name_matches(organization_id, name) do
    search_term = "%#{name}%"

    Device
    |> where(organization_id: ^organization_id)
    |> where([d], ilike(d.name, ^search_term) or ilike(^name, fragment("'%' || ? || '%'", d.name)))
    |> Repo.all()
    |> Repo.preload(:site)
    |> Enum.map(fn device -> %{entity: device, match_type: "name", confidence: :low} end)
  end

  defp confidence_rank(:high), do: 0
  defp confidence_rank(:medium), do: 1
  defp confidence_rank(:low), do: 2

  # --- Aggregate Queries ---

  @doc "Sum subscriber count and MRR across all Gaiia network sites for an organization."
  def get_org_subscriber_totals(organization_id) do
    result =
      NetworkSite
      |> where(organization_id: ^organization_id)
      |> select([ns], %{
        total_subscribers: coalesce(sum(ns.account_count), 0),
        total_mrr: coalesce(sum(ns.total_mrr), 0)
      })
      |> Repo.one()

    %{
      total_subscribers: result.total_subscribers,
      total_mrr: result.total_mrr
    }
  end

  @doc """
  Returns subscriber counts and MRR for multiple devices in a single batch query.

  Joins through `gaiia_inventory_items` (which map devices to Gaiia accounts)
  then counts distinct accounts and sums active MRR from `gaiia_billing_subscriptions`.

  Returns `%{device_id => %{subscriber_count: integer, mrr: Decimal.t()}}`.
  """
  def get_device_subscriber_counts(device_ids) when is_list(device_ids) do
    if device_ids == [] do
      %{}
    else
      # Count inventory items with assigned accounts per device as subscriber proxy
      InventoryItem
      |> where([ii], ii.device_id in ^device_ids and not is_nil(ii.assigned_account_gaiia_id))
      |> group_by([ii], ii.device_id)
      |> select([ii], %{
        device_id: ii.device_id,
        subscriber_count: count(ii.id, :distinct)
      })
      |> Repo.all()
      |> Map.new(fn row ->
        {row.device_id, %{subscriber_count: row.subscriber_count, mrr: nil}}
      end)
    end
  end

  @doc "Get subscriber count and MRR for a specific Towerops site via its mapped Gaiia network site."
  def get_site_subscriber_summary(site_id) do
    NetworkSite
    |> where(site_id: ^site_id)
    |> limit(1)
    |> select([ns], %{account_count: ns.account_count, total_mrr: ns.total_mrr})
    |> Repo.one()
  end
end
