# TA-ATS — Migrations & Seeders (both databases)

TA-ATS uses **two PostgreSQL databases**, seeded differently:

| Database      | Owned by        | Schema mechanism                 | Seed mechanism                          |
|---------------|-----------------|----------------------------------|-----------------------------------------|
| `ats_main`    | Django backend  | **Django migrations** (`migrate`)| `seed_*.py` scripts + menu scripts      |
| `ats_mcp_db`  | MCP auth service| **`ensure_schema()`** (raw SQL)  | seeded inside `ensure_schema()` (upsert)|

> Default login after seeding: **ats@admin.com / ats@2468** (all six demo roles share
> the password `ats@2468`).

---

## A. `ats_mcp_db` — MCP auth service

The MCP has **no migration framework**. Its schema and seed data both live in one
function, `ensure_schema()` in `auth_mcp/db.py`:
- Creates tables `mfa_users` (MFA/OTP enrollment) and `mcp_users` (login credentials).
- Seeds `mcp_users` with the admin + the 6 demo users (idempotent **upsert**, so it is
  safe to run any number of times).

It runs **automatically when the MCP server starts** (`server.py` calls it), or run it
on demand:

**Linux**
```bash
cd /home/indovisionconsul/public_html/auth_mcp
./venv/bin/python -c "import db; db.ensure_schema()"
```
**Windows**
```powershell
cd E:\xampp\htdocs\python\auth_mcp
.\venv\Scripts\python.exe -c "import db; db.ensure_schema()"
```

**Connection settings** come from env vars (defaults in parentheses):
`MCP_DB_HOST (localhost)`, `MCP_DB_PORT (5432)`, `MCP_DB_NAME (ats_mcp_db)`,
`MCP_DB_USER (postgres)`, `MCP_DB_PASSWORD (root)`.

**Verify it worked**
```bash
# from the MCP venv
./venv/bin/python -c "import db;
c=db.get_conn();cur=c.cursor();cur.execute('SELECT email, role FROM mcp_users ORDER BY email');
[print(' ',*r) for r in cur.fetchall()]"
```

---

## B. `ats_main` — Django backend

### B.1 Migrations
```bash
# Linux (production) — export prod settings first
cd /home/indovisionconsul/public_html/ats.indovisionconsultancy.in/backend
export DJANGO_SETTINGS_MODULE=config.settings.prod
./venv/bin/python manage.py migrate
```
```powershell
# Windows (dev)
cd E:\xampp\htdocs\python\TA-ATS-interns\backend
.\venv\Scripts\python.exe manage.py migrate
```

Check status:
```bash
./venv/bin/python manage.py showmigrations | grep '\[ \]'   # any unapplied?
./venv/bin/python manage.py showmigrations notifications pipeline candidates jobs users
```

### B.2 Seeders (run in this order)
Each script is standalone and (mostly) idempotent. On **Linux/production** you must
`export DJANGO_SETTINGS_MODULE=config.settings.prod` first, because the scripts default
to `config.settings.dev` internally.

| Order | Script                             | Seeds                                            |
|-------|------------------------------------|--------------------------------------------------|
| 1     | `seed_demo_users.py`               | 6 role groups + 6 demo users (password ats@2468) |
| 2     | `update_permissions.py`            | group → permission grants                        |
| 3     | `add_master_data_menus.py`         | sidebar: Master Data (Designation/Country/State/City) |
| 4     | `add_master_skills_menu.py`        | sidebar: Skills                                  |
| 5     | `add_reports_menu.py`              | sidebar: Reports                                 |
| 6     | `add_notification_templates_menu.py`| sidebar: Notification Templates + default/OTP templates |
| 7     | `seed_india_data.py`               | Country/State/City — India                       |
| 8     | `seed_world_data.py`               | Country/State/City — world                       |
| 9     | `seed_master_defaults.py`          | pipeline stages, key skills, designations        |

**Linux (production) — full run**
```bash
cd /home/indovisionconsul/public_html/ats.indovisionconsultancy.in/backend
export DJANGO_SETTINGS_MODULE=config.settings.prod
PY=./venv/bin/python
$PY manage.py migrate
$PY seed_demo_users.py
$PY update_permissions.py
$PY add_master_data_menus.py
$PY add_master_skills_menu.py
$PY add_reports_menu.py
[ -f add_notification_templates_menu.py ] && $PY add_notification_templates_menu.py
$PY seed_india_data.py
$PY seed_world_data.py
$PY seed_master_defaults.py
```

**Linux — one command** (does BOTH databases + frontend + restarts):
```bash
cd /home/indovisionconsul/public_html/ats.indovisionconsultancy.in
bash deploy_server.sh
```

**Windows (dev) — one command** (all migrations + seeders for `ats_main`):
```powershell
cd E:\xampp\htdocs\python\TA-ATS-interns\database
.\ats_seeder_akn.bat
```
Then seed the MCP store separately (section A).

---

## Known seeder gotcha (pipeline stages)

`seed_master_defaults.py` may fail with:
```
duplicate key value violates unique constraint "pipeline_stages_name_key"
```
**Cause:** the seeder looked up stages by `code`, but existing rows had a different
`code`, so it tried to insert a duplicate `name`. The repo version now uses
`update_or_create(name=...)` and is idempotent.

If you hit it on an **already-seeded** DB with the old script, align the codes first:
```bash
export DJANGO_SETTINGS_MODULE=config.settings.prod
./venv/bin/python manage.py shell <<'PYEOF'
from apps.pipeline.models import PipelineStage
codes = {"Contacted":"contacted","Candidate Responded":"candidate-responded","Screening":"screening",
 "Interview Scheduled":"interview-scheduled","Interviewed":"interviewed","Offer Extended":"offer-extended",
 "Offer Accepted":"offer-accepted","Joined":"joined","Rejected":"rejected","Candidate Declined":"candidate-declined"}
for n,c in codes.items():
    PipelineStage.objects.filter(name=n).update(code=c)
print("pipeline stage codes aligned")
PYEOF
./venv/bin/python seed_master_defaults.py
```

---

## Fresh-database checklist

1. Create both DBs: `ats_main`, `ats_mcp_db`.
2. `ats_main`: `manage.py migrate` → run seeders 1–9.
3. `ats_mcp_db`: `ensure_schema()`.
4. Start services (MCP, backend, frontend).
5. Log in: **ats@admin.com / ats@2468**.