# Database schema

## Source status

The Android/PHP source directories listed in the project specification (`android_user/` and
`php_user/user/`) are not present in this workspace. The PHP queries could therefore not be
used to introspect or confirm the production schema. The table and column names below were
transcribed from the schema outline supplied with the task. Types, defaults, indexes, and
foreign keys in `server/db/dev-schema.sql` are local-development assumptions, not a claim
that they match the existing production database.

Before pointing the application at an existing database, run `npm run db:inspect` with
credentials for that database and compare the resulting `SHOW TABLES` / `SHOW COLUMNS`
output with the PHP backend and this local-only schema. Do not run `npm run db:init` against
production.

## Tables and columns

| Table | Columns supplied in the task |
|---|---|
| `pranto_users` | `id`, `username`, `email`, `number`, `password`, `device_id`, `fcm_token`, `ip_address`, `session_id`, `leaderboard_name`, `join_date`, `avatar`, `status`, `balance`, `winning`, `total_play`, `total_winning`, `withdraw_limit`, `last_reset_time`, `name_change_date`, `next_spin` |
| `pranto_category` | `id`, `type`, `title`, `image_url`, `rules`, `status` |
| `pranto_match` | `id`, `category_id`, `matchtitle`, `input`, `date`, `time`, `totalprize`, `perkill`, `entryfee`, `matchmap`, `matchentry`, `playerneed`, `playerjoin`, `first_prize`, `second_prize`, `third_prize`, `fourth_prize`, `fiveth_prize`, `status`, `room_id`, `room_pass` |
| `pranto_join` | `id`, `user_id`, `match_id`, `slot`, `name`, `fcm_token`, `date`, `total_kill`, `total_winning` |
| `pranto_mymatch` | `id`, `user_id`, `match_id`, `joindate`, `jointime`, `winning` |
| `pranto_matchjoin_history` | `user_id`, `match_id`, `entry_fee`, `players`, `type`, `trxid`, `status`, `date` |
| `pranto_payment` | `user_id`, `username`, `email`, `trxid`, `method`, `amount`, `type`, `status`, `fcm_token`, `ip_address`, `date`, `created_at` |
| `transactions` | `id`, `transactionId`, `amount`, `source`, `status` |
| `pranto_winning_history` | `user_id`, `amount`, `winning_date` |
| `pranto_notification` | `id`, `type`, `title`, `message`, `date`, `user_id`, `created_at` |
| `pranto_otp` | `email`, `otp_code`, `ip_address`, `time`, `session_id` |
| `devhabib_setting` | `setting_key`, `setting_value` |
| `devhabib_slider` | `image_url`, `link_url`, `sort_order` |

The development schema adds surrogate `id` columns where the supplied list omitted one,
including payment/history/OTP/slider records, to provide stable local primary keys. It also
uses foreign keys and indexes to support the listed relations and query patterns. Confirm
these details against the real database before using or migrating it.

## Local setup

Use `docker compose up -d` for a disposable local MySQL instance, then `npm run db:init`
to create the development schema and seed records. `npm run db:inspect` prints every table
and column found in the configured database.
