defmodule DaProductApp.Repo.Migrations.AlignSettlementMisItemsSchema do
  use Ecto.Migration

  @moduledoc """
  Aligns the settlement_mis_items table with the SettlementMisItem schema.

  The original migration created per-transaction detail columns, but the
  SettlementMisGenerator produces grouped aggregate rows:
    one row per (merchant_mid × card_type_code) per MIS date.

  This migration:
    1. Makes core_transaction_id nullable (aggregate rows have no single txn id)
    2. Adds the aggregate columns the schema & generator require
    3. Existing per-transaction columns are retained for backwards compatibility
  """

  def up do
    # Make core_transaction_id optional for aggregate rows
    alter table(:settlement_mis_items) do
      modify :core_transaction_id, :bigint, null: true
    end
    
    # Add aggregate fields - check existence first for idempotency
    add_column_if_not_exists(:settlement_mis_items, :settlement_date, "date")
    add_column_if_not_exists(:settlement_mis_items, :merchant_id, "bigint")
    add_column_if_not_exists(:settlement_mis_items, :transaction_count, "integer DEFAULT 0")
    add_column_if_not_exists(:settlement_mis_items, :total_gross_amount, "decimal(14,2)")
    add_column_if_not_exists(:settlement_mis_items, :total_mdr_amount, "decimal(14,2)")
    add_column_if_not_exists(:settlement_mis_items, :total_vat_amount, "decimal(14,2)")
    add_column_if_not_exists(:settlement_mis_items, :total_interchange_fee, "decimal(14,2)")
    add_column_if_not_exists(:settlement_mis_items, :total_net_settlement_amount, "decimal(14,2)")

    # Drop the strict unique on (settlement_mis_id, core_transaction_id)
    # since aggregate rows will have null core_transaction_id
    drop_index_if_exists(:settlement_mis_items, :mis_items_unique)

    # New unique on (settlement_mis_id, merchant_mid, card_type_code) for aggregates
    create_index_if_not_exists(:settlement_mis_items, :mis_items_merchant_card_unique,
      [:settlement_mis_id, :merchant_mid, :card_type_code], unique: true)
  end

  def down do
    drop_index_if_exists(:settlement_mis_items, :mis_items_merchant_card_unique)

    # Restore the old unique index
    create_index_if_not_exists(:settlement_mis_items, :mis_items_unique,
      [:settlement_mis_id, :core_transaction_id], unique: true)

    # Remove added columns
    alter table(:settlement_mis_items) do
      remove :total_net_settlement_amount
      remove :total_interchange_fee
      remove :total_vat_amount
      remove :total_mdr_amount
      remove :total_gross_amount
      remove :transaction_count
      remove :merchant_id
      remove :settlement_date
      
      # Restore core_transaction_id to NOT NULL
      modify :core_transaction_id, :bigint, null: false
    end
  end

  # Helper to add column only if it doesn't exist
  defp add_column_if_not_exists(table, column, type) do
    # Use MySQL INFORMATION_SCHEMA to check if column exists
    result = repo().query!("""
      SELECT COUNT(*) as cnt
      FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = '#{table}'
        AND COLUMN_NAME = '#{column}'
    """)
    
    col_exists = result.rows |> List.first() |> List.first() |> Kernel.>(0)
    
    unless col_exists do
      execute "ALTER TABLE #{table} ADD COLUMN #{column} #{type}"
    end
  end

  # Helper to drop index only if it exists
  defp drop_index_if_exists(table, index_name) do
    result = repo().query!("""
      SELECT COUNT(*) as cnt
      FROM information_schema.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = '#{table}'
        AND INDEX_NAME = '#{index_name}'
    """)
    
    index_exists = result.rows |> List.first() |> List.first() |> Kernel.>(0)
    
    if index_exists do
      execute "DROP INDEX #{index_name} ON #{table}"
    end
  end

  # Helper to create index only if it doesn't exist
  defp create_index_if_not_exists(table, index_name, columns, opts \\ []) do
    result = repo().query!("""
      SELECT COUNT(*) as cnt
      FROM information_schema.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = '#{table}'
        AND INDEX_NAME = '#{index_name}'
    """)
    
    index_exists = result.rows |> List.first() |> List.first() |> Kernel.>(0)
    
    unless index_exists do
      unique = if opts[:unique], do: "UNIQUE ", else: ""
      columns_str = Enum.join(columns, ", ")
      execute "CREATE #{unique}INDEX #{index_name} ON #{table} (#{columns_str})"
    end
  end
end