defmodule DaProductApp.TerminalManagement.TerminalGroupMembership do
  use Ecto.Schema
  import Ecto.Changeset
   import Ecto.Query

  @derive {Jason.Encoder, only: [
    :id, :terminal_id, :group_id, :assignment_type, :assigned_by, :assigned_at,
    :is_active, :rule_id, :inserted_at, :updated_at
  ]}

  schema "terminal_group_memberships" do
    field :assignment_type, :string  # manual, rule_based, imported, system
    field :assigned_by, :string      # User ID or system process
    field :assigned_at, :utc_datetime
    field :is_active, :boolean, default: true
    
    belongs_to :terminal, DaProductApp.TerminalManagement.TmsTerminal, type: :integer
    belongs_to :group, DaProductApp.TerminalManagement.TerminalGroup
    belongs_to :rule, DaProductApp.TerminalManagement.TerminalGroupRule, foreign_key: :rule_id
    
    timestamps()
  end

  @required_fields [:terminal_id, :group_id, :assignment_type, :assigned_at]
  @optional_fields [:assigned_by, :is_active, :rule_id]
  @all_fields @required_fields ++ @optional_fields

  @valid_assignment_types ["manual", "rule_based", "imported", "system"]

  def changeset(membership, attrs) do
    membership
    |> cast(attrs, @all_fields)
    |> validate_required(@required_fields)
    |> validate_inclusion(:assignment_type, @valid_assignment_types)
    |> validate_unique_active_membership()
    |> foreign_key_constraint(:terminal_id)
    |> foreign_key_constraint(:group_id)
    |> foreign_key_constraint(:rule_id)
  end

  # Custom validation for MySQL compatibility (since MySQL doesn't support WHERE in unique indexes)
  defp validate_unique_active_membership(changeset) do
    terminal_id = get_change(changeset, :terminal_id)
    group_id = get_change(changeset, :group_id)
    is_active = get_change(changeset, :is_active) || get_field(changeset, :is_active)
    
    # Only validate if we're creating an active membership
    if terminal_id && group_id && is_active do
      import Ecto.Query
      alias DaProductApp.Repo
      
      try do
        existing = Repo.one(
          from m in __MODULE__,
          where: m.terminal_id == ^terminal_id and 
                 m.group_id == ^group_id and 
                 m.is_active == true and
                 m.id != ^(get_field(changeset, :id) || 0)
        )
        
        if existing do
          add_error(changeset, :terminal_id, "Terminal is already active in this group")
        else
          changeset
        end
      rescue
        # If repo is not available (e.g., in tests), skip validation
        _error -> changeset
      end
    else
      changeset
    end
  end

  # Helper functions for different assignment types
  def manual_assignment(terminal_id, group_id, assigned_by) do
    %{
      terminal_id: terminal_id,
      group_id: group_id,
      assignment_type: "manual",
      assigned_by: assigned_by,
      assigned_at: DateTime.utc_now(),
      is_active: true
    }
  end

  def rule_based_assignment(terminal_id, group_id, rule_id) do
    %{
      terminal_id: terminal_id,
      group_id: group_id,
      assignment_type: "rule_based",
      assigned_by: "system",
      assigned_at: DateTime.utc_now(),
      is_active: true,
      rule_id: rule_id
    }
  end

  def system_assignment(terminal_id, group_id) do
    %{
      terminal_id: terminal_id,
      group_id: group_id,
      assignment_type: "system",
      assigned_by: "system",
      assigned_at: DateTime.utc_now(),
      is_active: true
    }
  end

  # Scopes
  def active_memberships(query \\ __MODULE__) do
    from m in query, where: m.is_active == true
  end

  def by_assignment_type(query \\ __MODULE__, type) do
    from m in query, where: m.assignment_type == ^type
  end

  def by_terminal(query \\ __MODULE__, terminal_id) do
    from m in query, where: m.terminal_id == ^terminal_id
  end

  def by_group(query \\ __MODULE__, group_id) do
    from m in query, where: m.group_id == ^group_id
  end

  def recent_assignments(query \\ __MODULE__, days \\ 7) do
    cutoff_date = DateTime.add(DateTime.utc_now(), -days * 24 * 60 * 60, :second)
    from m in query, where: m.assigned_at >= ^cutoff_date
  end
end