# CWSN Portal — PHP/MySQL Conversion (Phase 1: Foundation)

This is a from-scratch rewrite of the Firebase/React CWSN Portal onto plain
PHP + MySQL, aimed at XAMPP (matching your other PHP/MySQL and PHP/SQLite
projects) but portable to any standard PHP+MySQL host.

**This is not a drop-in replacement yet.** Converting the whole app means
re-implementing every piece on a completely different architecture — no
Cloud Functions, no Firestore, no client SDK, session-based auth instead of
Firebase Auth tokens, SQL instead of NoSQL documents. Attempting all of that
in one pass risks a shallow, broken result, so it's built in phases. This
phase delivers the foundation everything else sits on:

- Complete MySQL schema (`schema.sql`) — every table from the Firestore
  version, translated to relational form with proper foreign keys and the
  same status conventions (`cwsn.status`: 0=complete, 1=deleted,
  2=incomplete draft)
- Session-based authentication — Admin + CLRC logins, same hardcoded
  defaults as before (`Admin@123#` / `admin123`), forced password change
  on first login, `password_hash()`/`password_verify()` instead of Firebase
  Auth
- CLRC data scoping (`clrcScopeSql()`) — the same "CLRC only sees their own
  records, Admin sees everything" rule, enforced in PHP on every query
  instead of Firestore security rules
- Shared page chrome (sidebar, topbar, profile menu) matching the current
  visual design
- A working Dashboard with live counts, proving the full stack (PHP → PDO
  → MySQL → rendered page) works end to end

## Setup (XAMPP)

1. Create the database and import the schema:
   ```bash
   mysql -u root -p -e "CREATE DATABASE cwsn_portal CHARACTER SET utf8mb4"
   mysql -u root -p cwsn_portal < schema.sql
   ```
2. Copy the config and fill in your local DB credentials:
   ```bash
   cp config/config.example.php config/config.php
   ```
3. Import the seed data, in this order (each is safe to re-run except
   `10_cwsn.sql`'s `class` rows — see the note inside that file):
   ```bash
   mysql -u root -p cwsn_portal < scripts/seed_data/07_reference_data.sql
   mysql -u root -p cwsn_portal < scripts/seed_data/07b_users.sql
   mysql -u root -p cwsn_portal < scripts/seed_data/08_school.sql
   mysql -u root -p cwsn_portal < scripts/seed_data/09_udise_enrollment.sql
   mysql -u root -p cwsn_portal < scripts/seed_data/10_cwsn.sql
   mysql -u root -p cwsn_portal < scripts/seed_data/11_appliance_bank_block_awc.sql
   mysql -u root -p cwsn_portal < scripts/seed_data/12_fix_zero_placeholders.sql
   ```
   `07b_users.sql` already creates the Admin + CLRC logins with the same
   default passwords `scripts/create_admin.php` would, so step 4 below is
   only needed if you want to reset them later.
4. Bootstrap (or reset) the Admin login and all CLRC logins:
   ```bash
   php scripts/create_admin.php
   ```
5. Point your webroot (e.g. `htdocs/cwsn-php`) at this folder and visit
   `login.php`.

## What's deliberately stubbed for now

`reports.php`, `masters.php`, and `users.php` exist as placeholder pages (so
the nav doesn't 404) but hold no real functionality yet.

## Known remaining data gap

30 CWSN records (0.16%) have an `awc_no` that doesn't match anything in the
`awc` master table — not the same `'0'`-placeholder pattern as bank/appliance
below, but informal local numbers (e.g. `"119"`, `"5/83"`) that don't follow
the official AWC code format at all. Unlike the bank/appliance case, there's
no unambiguous "this means nothing" value here, so I left this data as-is
rather than guessing — editing and saving one of these 30 specific records
will still hit a foreign key error until that AWC reference is corrected or
cleared by hand. Run this to see exactly which ones:
```sql
SELECT cwsn_code, cwsn_name, awc_no FROM cwsn c
LEFT JOIN awc a ON a.awc_no = c.awc_no
WHERE c.awc_no IS NOT NULL AND a.awc_no IS NULL;
```

## Phase 2 (delivered)

- **CWSN Records list** (`cwsn_list.php`): pagination, search, status/CLRC/
  disability filters, soft delete/restore, CSV export — CLRC-scoped access
  enforced server-side (verified: a cross-CLRC delete attempt is rejected)
- **CWSN Add/Edit wizard** (`cwsn_form.php` + `assets/js/cwsn_form.js`): the
  same 7 steps as the original app (Location, Personal, Address, Disability,
  Education, Bank, Review), client-side step navigation with no page reload,
  the same Aadhaar masking-while-typing behavior, searchable dropdowns for
  School/AWC/IFSC backed by `api/search.php`, the UDISE+ Match Check panel
  (Pull/Overwrite/Matched states) backed by `api/udise_check.php`, and
  Complete/Incomplete status computed server-side on every save
  (`api/save_cwsn.php`) — saving is never blocked by incomplete fields,
  matching the original app's draft-saving design
- **Server-side validation** (`includes/cwsn_validate.php`): ported
  field-for-field from the original rules — required fields, patterns
  (name/address/PIN/contact/Aadhaar), DOB age range, the NOT APPLICABLE
  sentinel logic for conditional sub-answers, bank/account-holder consistency

All of the above was built and verified against the real ~19,000-row
dataset — not just linted. Along the way this caught and fixed several
real bugs: a `schema.sql` NOT NULL constraint that conflicted with the
Incomplete-draft design, missing IFSC/Branch pre-fill when editing an
existing record, `api/*.php` endpoints redirecting to a broken URL when a
session had expired (fixed by having AJAX endpoints return a proper 401
instead of an HTTP redirect), and the CLRC/Block school-search UI not
appearing when "School" was selected mid-step rather than on step entry.

## Phase 3+ (tell me the order you want)

- **Master Data**: CRUD for all 12 reference tables (CLRC, Block, Cluster,
  School, AWC, Disability, Appliance, Bank, Bank Branch, Special Educator,
  etc.) — all now have seed data loaded (see below), so this is just the
  admin UI for managing them going forward
- **CLRC Logins**: Admin screen to create/reset CLRC passwords
- **Reports**: the 7 aggregate report types with Excel export
- **Dashboard**: the full analytical version (field-wise Yes/No breakdowns,
  UDISE+ cross-source comparison, the inconsistency scanner)
- **PDF export**: the one-page printable summary (the Review step's data is
  already structured for this — just needs the print CSS + trigger wired up,
  same approach as the Firebase version)
- **CSV import scripts**: PHP equivalents of the Node.js import scripts, for
  refreshing master data or the CWSN bulk import going forward

Each of these is a substantial, self-contained piece of work. Let me know
which to build next (or the order to do them all in) and I'll continue.
