# TechTitans POS — IPTV Automatic MC ID Generation + Manual Entry

**Branch:** `techtitans/iptv-i2`. Adds concurrency-safe automatic MC ID generation (default) with optional
manual entry and strict duplicate prevention on the one-page New IPTV Connection form. Preserves all
existing I1/I2 functionality. **Not pushed.**

## Task 1 — Verified legacy format (read-only)

Source: `oldportal/techtitans_techdb.sql`, `iptv_details` (1623 rows), parsed read-only. Findings:

- **MC is TechTitans' OWN account reference**, not a provider username — it is the auto-suggested
  `user_login` (doc 04); the provider login is `portal`, the box is `macid`/`act_user`.
- **Format:** `MC` + number. Of 1623 logins: **1437 MC-prefixed, 186 non-MC** (e.g. `JM041`, `Zhd001`,
  `ALM001`, `Arul`), 0 empty. **794 are pure `MC<digits>`**; the rest carry historical **suffixes**
  (`MC403s2`, `MC434v`, `MC224a`) or **leading zeros** (`MC01`, `MC017`, `MC0000s2`), and a few are
  lowercase (`mc09`).
- **Highest pure `MC<digits>` number: 1603.**
- **Sequence scope:** the legacy portal is single-shop (single tenant/location) → the number is effectively
  **global per shop**. In the multi-tenant POS the correct domain is **per-tenant** (not provider- or
  location-specific — it is a customer-facing account ref).
- **Historical/disabled reserve ids:** legacy used an `is_active` soft-delete; ids stay in the table. The
  new system has no hard deletes (cancelled/suspended keep the row), so ids are reserved.

**New policy:** generate a clean `MC{n}` (no leading zeros, matching modern high-numbered records),
**tenant-scoped**, from a monotonic counter seeded above the tenant max; manual entry accepts any non-empty
identifier (MC or legacy non-MC), only normalizing the `MC` prefix case — no MC-only validation is imposed.

## Task 2 — Automatic generation (concurrency-safe)

- `UNIQUE(tenant_id, account_identifier)` on `iptv_subscriptions` is the authoritative duplicate guard (the
  correct business domain), so simultaneous creates can never reuse an id.
- `iptv_mc_sequences(tenant_id, last_number)` is a **per-tenant monotonic counter**. `IptvMcIdService`
  allocates inside the create transaction: `insertOrIgnore` seeds the row once from the tenant's highest
  existing MC number, then `SELECT … FOR UPDATE` + increment (serializing concurrent allocations), skipping
  any already-taken id. **No MAX+1 scan at allocation.** Previously-allocated numbers are never reused, and
  the seed excludes suffixed/non-MC ids.
- Auto is the default; the frontend shows a **provisional next id** and labels it *"Auto-assigned on save —
  the final ID may differ if another connection is created first."* The final id is allocated atomically on
  save and returned (the detail page shows the real id). Internal subscription UUIDs are unchanged.

## Task 3 — Manual entry

- A free-text id, **trimmed**, with the **`MC` prefix upper-cased** (case only); the rest (digits, legacy
  suffixes, or a non-MC provider login) is preserved — provider-login compatibility is kept, no MC-only
  validation. Blank input falls back to auto-generation.
- An authenticated availability endpoint powers inline **checking / available / duplicate / invalid** states.
- On save the duplicate is re-checked **transactionally** by the UNIQUE constraint; a collision returns a
  clear 422 without losing form data (the frontend keeps all fields).

## Task 4 — Sequence handling

A manual `MC{n}` **bumps the counter** (`last_number = max(last_number, n)`) so subsequent auto-generation
always follows above it and never collides (verified: manual `MC9000` → next auto `MC9001`). Concurrent
same-id creates resolve via the UNIQUE constraint (one wins, the other 422s). Create is **idempotent** per
`X-Request-Id` (a retry returns the same subscription + id, no second allocation). No frontend increment or
MAX+1-without-lock is used.

## Task 5 — UI (one page, no wizard)

The plain User Login input is replaced by an **MC ID / User login** control: an **Auto generate / Enter
manually** toggle, the suggested next id (auto), and a manual input with live availability feedback + inline
errors. Compact; other entered connection details are preserved when switching modes. The confirm summary
shows the MC id (manual value, or the provisional + "assigned on save").

## Task 6 — Compatibility

Existing I1/I2 subscriptions, CRM links, encrypted credentials, subscription references, renewal history,
provider relationships, pricing and discounts are all preserved (full IPTV suite 57 + MC suite 10 green;
no hard deletes; UNIQUE applied cleanly — dev DB had 0 duplicate account groups). Non-MC legacy logins
remain valid. The tenant-scoped UNIQUE + monotonic counter prepare for a future legacy import without MC
collisions (seed the counter from the imported max). No legacy data migrated in this task.

## Database / API changes

- Migration `2026_11_26_100001`: `UNIQUE(tenant_id, account_identifier)` + `iptv_mc_sequences` table.
- `GET iptv/mc-id/next` (provisional next id) and `GET iptv/mc-id/availability?account_identifier=…`
  (normalized + available + mc_format), both `iptv.manage`. `POST iptv/subscriptions` accepts
  `auto_generate` (and `account_identifier` becomes optional).

## Tests

- **Backend** `IptvMcIdTest` (10): sequential auto (MC1, MC2, next=MC3); manual valid + normalization
  ("  mc2000  " → MC2000); **manual duplicate → 422, no second row**; availability states incl. a non-MC
  legacy login; **manual-high-then-auto no collision**; legacy non-MC preserved + doesn't drive the
  sequence; **cancelled/historical id cannot be reused**; **idempotent per X-Request-Id**; **tenant
  isolation** (same manual id allowed in two tenants, independent sequences); save + reopen persists the id.
  Full IPTV + catalog suite **57 passed** (no regression).
- **Frontend** `IptvPages.test.tsx` (10): auto-generate default (sends `auto_generate`, no
  `account_identifier`, shows suggested id); **manual duplicate blocks submit**; the manual create path
  (availability → confirm → create). `tsc` ✓, `eslint` ✓.
- **Browser** (Playwright) `e2e/iptv-mc-id.spec.ts`: live suggested **MC5051** (one above the seeded
  MC5050), manual **MC5050 "already in use"** (Create disabled), **MC8000 "available"**, and an
  auto-generated create persisting a real sequential id on the detail page. Screenshots in
  `frontend/test-results/iptv-mc/` (`auto.png`, `manual-duplicate.png`, `manual-available.png`,
  `created-detail.png`).

## Concurrency & duplicate protection (summary)

Row-locked monotonic counter (no MAX+1 at allocation) + DB `UNIQUE(tenant, account_identifier)` backstop +
idempotency key. Manual high numbers bump the counter; historical/cancelled ids are retained and skipped.

## Known limitations

- The `MC` numbering policy is pure `MC<digits>`; historical suffix variants (`MC403s2`) are supported as
  manual ids and reserved by the UNIQUE constraint but do not advance the counter.
- Availability/normalization uses the column's case-insensitive collation; two logins differing only by
  case are treated as the same id (prevents confusing duplicates).

## Commit hashes (branch `techtitans/iptv-i2`, not pushed)

- Backend: **b7174cc** + the commit carrying this report.
- Frontend: see the latest `techtitans/iptv-i2` frontend commit.