# Database migrations

This repo runs migrations automatically via a git post-merge hook. Every `*.sql`
file dropped in `sql/` is applied to `res_erp` the next time someone runs
`git pull` — once each, tracked in a `_migrations` table.

## First-time setup (per clone, server or laptop)

```bash
bash scripts/install-hooks.sh
```

That sets `git config core.hooksPath .githooks`, which activates
`.githooks/post-merge`. Without this step the hook is dormant and
migrations stay manual.

## Day-to-day usage

1. Author writes a new SQL file in `sql/`, commits it.
2. Server runs `git pull`.
3. Post-merge hook fires → `php scripts/migrate.php` → new files applied,
   already-applied files skipped. Output is printed to the pull terminal.
4. If a migration fails, the merge still succeeds. Fix the SQL and re-run:
   ```bash
   php scripts/migrate.php
   ```

## Writing a migration

- Filename order = run order. Use a numeric prefix if order matters
  (`002_add_user_index.sql`, `003_drop_legacy_table.sql`). Filenames already
  in `_migrations` are never re-run, so once committed, treat them as
  immutable.
- Use idempotent SQL where you can: `CREATE TABLE IF NOT EXISTS`,
  `INSERT IGNORE`, `ON DUPLICATE KEY UPDATE`. That way the migration is
  safe if it's accidentally re-applied on a freshly-restored DB.
- Multi-statement files are fine — the runner uses `multi_query()`.

## Manual commands

```bash
# Apply any pending migrations now (same thing the hook runs).
php scripts/migrate.php

# Just show what would run, without applying.
php scripts/migrate.php --status

# Force a dev laptop to point at production DB (rarely a good idea):
MIGRATE_PROD=1 php scripts/migrate.php
```

## How the runner decides which DB to use

`scripts/migrate.php` includes `configs/connection.php`, which auto-detects
local vs production from `$_SERVER['HTTP_HOST']`. CLI invocations have
no HTTP_HOST, so the runner sets it to `apis.resindia.co.in` **only when
running CLI on Linux** (production = Linux, dev = Windows in this project).
That preserves the existing connection rules without modifying
`configs/connection.php`.

## How tracking works

The runner creates one extra table on first run:

```sql
CREATE TABLE `_migrations` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `filename` VARCHAR(255) NOT NULL UNIQUE,
  `applied_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

Each successful migration inserts one row. Re-running the migrator skips
any filename already present.

## Adopting an already-applied SQL file

If you add an SQL file that has *already* been applied manually on a
server (e.g. you ran `create_battery_codes.sql` by hand months ago and
now want it tracked), the runner will see it as pending and try to apply
it again. As long as the SQL uses `CREATE TABLE IF NOT EXISTS` and
`INSERT … ON DUPLICATE KEY UPDATE` / `INSERT IGNORE`, this is harmless —
the SQL no-ops and the file gets recorded as applied. Otherwise, insert
the filename into `_migrations` by hand first:

```sql
INSERT IGNORE INTO `_migrations` (`filename`) VALUES ('the_old_file.sql');
```