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

  def change do
    # Create settings table for dynamic platform configuration
    create table(:platform_settings) do
      add :category, :string, null: false, size: 50
      add :key, :string, null: false, size: 100
      add :value, :text
      add :value_type, :string, default: "string", size: 20
      add :description, :text
      add :is_encrypted, :boolean, default: false, null: false
      add :is_active, :boolean, default: true, null: false
      
      timestamps(type: :utc_datetime)
    end

    create unique_index(:platform_settings, [:category, :key])
    create index(:platform_settings, [:category])
    create index(:platform_settings, [:is_active])

    # Create settlements table for financial reconciliation
    create table(:settlements) do
      add :reference_id, :string, null: false
      add :batch_id, :string
      add :type, :string, null: false # "net", "merchant", "partner"
      add :status, :string, default: "pending", null: false
      add :frequency, :string, default: "daily" # "daily", "weekly", "monthly"
      add :priority, :string, default: "normal" # "low", "normal", "high", "urgent"
      
      # Amount details
      add :settlement_amount, :decimal, precision: 15, scale: 2, null: false
      add :fee_amount, :decimal, precision: 15, scale: 2, default: 0
      add :tax_amount, :decimal, precision: 15, scale: 2, default: 0
      add :net_amount, :decimal, precision: 15, scale: 2, null: false
      add :transaction_count, :integer, default: 0
      add :currency, :string, default: "INR", size: 3
      
      # Partner/merchant details
      add :partner_id, references(:partners, type: :binary_id, on_delete: :nothing)
      add :merchant_id, references(:merchants, on_delete: :nothing)
      add :partner_account_number, :string
      add :partner_ifsc, :string
      add :partner_bank_name, :string
      
      # Timing details
      add :scheduled_at, :utc_datetime
      add :started_at, :utc_datetime
      add :approved_at, :utc_datetime
      add :bank_processed_at, :utc_datetime
      add :completed_at, :utc_datetime
      add :settlement_window_start, :utc_datetime
      add :settlement_window_end, :utc_datetime
      
      # Error handling
      add :failure_code, :string
      add :failure_reason, :text
      add :retry_count, :integer, default: 0
      
      # Reconciliation
      add :reconciliation_status, :string, default: "pending"
      add :reconciled_at, :utc_datetime
      add :reconciliation_reference, :string
      
      timestamps(type: :utc_datetime)
    end

    create index(:settlements, [:status])
    create index(:settlements, [:type])
    create index(:settlements, [:partner_id])
    create index(:settlements, [:merchant_id])
    create index(:settlements, [:scheduled_at])
    create unique_index(:settlements, [:reference_id])

    # Create API request logs table for audit trail
    create table(:api_request_logs) do
      add :request_id, :string, null: false
      add :method, :string, null: false
      add :path, :string, null: false
      add :query_params, :text
      add :headers, :text
      add :body, :text
      add :response_status, :integer
      add :response_body, :text
      add :response_headers, :text
      add :duration_ms, :integer
      add :ip_address, :string
      add :user_agent, :string
      add :user_id, references(:users, on_delete: :nothing)
      add :partner_id, references(:partners, type: :binary_id, on_delete: :nothing)
      add :error_details, :text
      
      timestamps(type: :utc_datetime)
    end

    create index(:api_request_logs, [:request_id])
    create index(:api_request_logs, [:method, :path])
    create index(:api_request_logs, [:response_status])
    create index(:api_request_logs, [:user_id])
    create index(:api_request_logs, [:partner_id])
    create index(:api_request_logs, [:inserted_at])

    # Add any missing fields to transactions table for better international support
    alter table(:transactions) do
      # Add settlement tracking
      add :settlement_id, references(:settlements, on_delete: :nilify_all)
      add :settlement_status, :string, default: "pending"
      
      # Add audit fields
      add :api_request_log_id, references(:api_request_logs, on_delete: :nilify_all)
      
      # Add performance tracking fields
      add :processing_duration_ms, :integer
      add :database_duration_ms, :integer
      add :external_api_duration_ms, :integer
    end

    create index(:transactions, [:settlement_id])
    create index(:transactions, [:settlement_status])
    create index(:transactions, [:api_request_log_id])
  end
end