# Phase Recon-0 — Foundation: `mw_recon` App + DB Schema

**Status:** ⬜ Pending
**Duration:** Weeks 1–2
**Goal:** New `mw_recon` umbrella app and all database tables exist. Nothing runs yet but the scaffold is complete and migrations pass.

---

## New Umbrella App: `apps/mw_recon/`

```
apps/mw_recon/
├── mix.exs
└── lib/
    ├── mw_recon.ex
    └── mw_recon/
        └── application.ex
```

### `apps/mw_recon/mix.exs`

```elixir
defmodule MwRecon.MixProject do
  use Mix.Project

  def project do
    [
      app: :mw_recon,
      version: "0.1.0",
      build_path: "../../_build",
      config_path: "../../config/config.exs",
      deps_path: "../../deps",
      lockfile: "../../mix.lock",
      elixir: "~> 1.14",
      elixirc_paths: elixirc_paths(Mix.env()),
      start_permanent: Mix.env() == :prod,
      deps: deps()
    ]
  end

  def application do
    [
      extra_applications: [:logger],
      mod: {MwRecon.Application, []}
    ]
  end

  defp elixirc_paths(:test), do: ["lib", "test/support"]
  defp elixirc_paths(_), do: ["lib"]

  defp deps do
    [
      {:infra_repo, in_umbrella: true},
      {:infra_queue, in_umbrella: true},
      {:adapter_cloudi, in_umbrella: true},
      {:mw_audit, in_umbrella: true},
      {:jason, "~> 1.2"}
    ]
  end
end
```

Add to umbrella `mix.exs` deps list:
```elixir
{:mw_recon, in_umbrella: true},
```

---

## Database Schema

### Migration: `20260619000001_create_recon_tables.exs`

```elixir
defmodule InfraRepo.Repo.Migrations.CreateReconTables do
  use Ecto.Migration

  def change do
    # ── recon_configs ─────────────────────────────────────────────────────────
    # One row per hospital + recon_type combination.
    # Stores column mappings, matching keys, and location-to-code map as JSON.
    create table(:recon_configs) do
      add :tenant_id,       references(:tenants, on_delete: :delete_all), null: false
      add :recon_type,      :string, null: false
      # e.g. "bank_card_vs_momentspay", "bank_upi_vs_momentspay",
      #      "his_bank_card", "his_bank_upi", "his_momentspay_card",
      #      "his_momentspay_upi", "amex"
      add :display_name,    :string, null: false
      add :enabled,         :boolean, default: true, null: false
      add :column_mappings, :text    # JSON: bank column aliases → canonical names
      add :location_map,    :text    # JSON: full location name → short code
      add :match_keys,      :text    # JSON: list of match key sets (4-key, 3-key fallback)
      add :payment_modes,   :text    # JSON: list of HIS payment mode filter values
      add :meta,            :text    # JSON: any extra config (e.g. summary_sheet_cell_offset)
      timestamps()
    end

    create unique_index(:recon_configs, [:tenant_id, :recon_type])
    create index(:recon_configs, [:tenant_id])

    # ── recon_sessions ────────────────────────────────────────────────────────
    # One row per reconciliation run initiated by a user.
    create table(:recon_sessions) do
      add :tenant_id,      references(:tenants, on_delete: :delete_all), null: false
      add :config_id,      references(:recon_configs, on_delete: :nilify_all)
      add :recon_type,     :string, null: false
      add :status,         :string, null: false, default: "pending"
      # pending | parsing | matching | reporting | completed | failed
      add :recon_date,     :date
      add :initiated_by,   references(:admin_users, on_delete: :nilify_all)
      add :job_id,         :string  # references async_jobs (string UUID)
      add :matched_count,  :integer
      add :unmatched_count,:integer
      add :total_count,    :integer
      add :matched_amount, :decimal, precision: 15, scale: 2
      add :unmatched_amount, :decimal, precision: 15, scale: 2
      add :error_reason,   :text
      add :report_data,    :longtext  # base64 XLSX — stored here for download
      timestamps()
    end

    create index(:recon_sessions, [:tenant_id])
    create index(:recon_sessions, [:tenant_id, :status])
    create index(:recon_sessions, [:tenant_id, :inserted_at])

    # ── recon_run_files ───────────────────────────────────────────────────────
    # Stores the uploaded files associated with a session.
    create table(:recon_run_files) do
      add :session_id,  references(:recon_sessions, on_delete: :delete_all), null: false
      add :role,        :string, null: false
      # "bank_card" | "bank_upi" | "momentspay" | "his" | "amex"
      add :filename,    :string, null: false
      add :file_format, :string  # "xlsx" | "csv"
      add :content,     :longtext, null: false  # base64 encoded
      add :row_count,   :integer
      add :columns,     :text    # JSON: list of column names detected
      timestamps()
    end

    create index(:recon_run_files, [:session_id])

    # ── recon_location_maps ───────────────────────────────────────────────────
    # Extracted into own table for easy admin editing (Phase 5).
    # Alternatively managed via recon_configs.location_map JSON — both approaches
    # are valid; this table allows per-row UI editing.
    create table(:recon_location_maps) do
      add :config_id,    references(:recon_configs, on_delete: :delete_all), null: false
      add :full_name,    :string, null: false   # e.g. "SHL Bibwewadi-Hospital"
      add :short_code,   :string, null: false   # e.g. "BBW"
      timestamps()
    end

    create unique_index(:recon_location_maps, [:config_id, :full_name])
    create index(:recon_location_maps, [:config_id])
  end
end
```

---

## Ecto Schemas

### `infra_repo/schemas/recon_config.ex`

```elixir
defmodule InfraRepo.Schemas.ReconConfig do
  use Ecto.Schema
  import Ecto.Changeset

  schema "recon_configs" do
    field :recon_type,      :string
    field :display_name,    :string
    field :enabled,         :boolean, default: true
    field :column_mappings, :string   # JSON string
    field :location_map,    :string
    field :match_keys,      :string
    field :payment_modes,   :string
    field :meta,            :string

    belongs_to :tenant,            InfraRepo.Schemas.Tenant
    has_many   :sessions,          InfraRepo.Schemas.ReconSession
    has_many   :location_maps,     InfraRepo.Schemas.ReconLocationMap
    timestamps()
  end

  def changeset(config, attrs) do
    config
    |> cast(attrs, [:tenant_id, :recon_type, :display_name, :enabled,
                    :column_mappings, :location_map, :match_keys, :payment_modes, :meta])
    |> validate_required([:tenant_id, :recon_type, :display_name])
    |> unique_constraint([:tenant_id, :recon_type])
    |> validate_inclusion(:recon_type, ~w(bank_card_vs_momentspay bank_upi_vs_momentspay
                                          his_bank_card his_bank_upi his_momentspay_card
                                          his_momentspay_upi amex))
  end
end
```

### `infra_repo/schemas/recon_session.ex`

```elixir
defmodule InfraRepo.Schemas.ReconSession do
  use Ecto.Schema
  import Ecto.Changeset

  @valid_statuses ~w(pending parsing matching reporting completed failed)

  schema "recon_sessions" do
    field :recon_type,       :string
    field :status,           :string, default: "pending"
    field :recon_date,       :date
    field :job_id,           :string
    field :matched_count,    :integer
    field :unmatched_count,  :integer
    field :total_count,      :integer
    field :matched_amount,   :decimal
    field :unmatched_amount, :decimal
    field :error_reason,     :string
    field :report_data,      :string

    belongs_to :tenant,       InfraRepo.Schemas.Tenant
    belongs_to :config,       InfraRepo.Schemas.ReconConfig
    belongs_to :initiated_by, InfraRepo.Schemas.AdminUser
    has_many   :run_files,    InfraRepo.Schemas.ReconRunFile
    timestamps()
  end

  def changeset(session, attrs) do
    session
    |> cast(attrs, [:tenant_id, :config_id, :recon_type, :status, :recon_date,
                    :initiated_by_id, :job_id, :matched_count, :unmatched_count,
                    :total_count, :matched_amount, :unmatched_amount, :error_reason,
                    :report_data])
    |> validate_required([:tenant_id, :recon_type])
    |> validate_inclusion(:status, @valid_statuses)
  end
end
```

---

## Context Modules

### `infra_repo/recon_sessions.ex`

```elixir
defmodule InfraRepo.ReconSessions do
  import Ecto.Query
  alias InfraRepo.Repo
  alias InfraRepo.Schemas.{ReconSession, ReconRunFile}

  def create_session(attrs), do: %ReconSession{} |> ReconSession.changeset(attrs) |> Repo.insert()

  def get_session(id), do: Repo.get(ReconSession, id)
  def get_session!(id), do: Repo.get!(ReconSession, id)

  def get_session_with_files(id) do
    Repo.get!(ReconSession, id) |> Repo.preload(:run_files)
  end

  def update_session(%ReconSession{} = session, attrs) do
    session |> ReconSession.changeset(attrs) |> Repo.update()
  end

  def set_status(session_id, status, extra_attrs \\ %{}) do
    get_session!(session_id)
    |> ReconSession.changeset(Map.merge(%{status: status}, extra_attrs))
    |> Repo.update()
  end

  def list_sessions_for_tenant(tenant_id, opts \\ []) do
    limit = Keyword.get(opts, :limit, 50)
    ReconSession
    |> where([s], s.tenant_id == ^tenant_id)
    |> order_by([s], desc: s.inserted_at)
    |> limit(^limit)
    |> Repo.all()
  end

  def add_run_file(attrs) do
    %ReconRunFile{} |> ReconRunFile.changeset(attrs) |> Repo.insert()
  end
end
```

---

## Acceptance Criteria

- [ ] `mix ecto.migrate` runs cleanly — 4 new tables created with no errors
- [ ] `InfraRepo.ReconSessions.create_session/1` returns `{:ok, %ReconSession{}}` in IEx
- [ ] `mw_recon` starts with the umbrella: `mix phx.server` shows no errors for the new app
- [ ] All existing tests pass: `mix test`