# Pincode Classification Centralization - Migration Documentation --- ## Overview The Pincode Classification Centralization migration successfully unified the system's previously scattered pincode tables (`approved_pin_codes` and `locations`) into a single, comprehensive `pincode_classifications` table. This centralization eliminates redundant logic, ensures a single source of truth, and introduces a more robust bulk upload process managed by Python backend services. ### High-Level Changes - **Unified Schema:** A single table now governs all pincode classifications using `type` values (`0` = Negative, `1` = Positive, `2` = Risky). - **Soft Deletes & Deduplication:** Soft deletes (`deleted_at`) are implemented. Duplicate pincodes are permitted at the database level to maintain historical records of bulk uploads. - **Postal API Removal:** External API calls (e.g., postal pincode APIs) were removed entirely. The system now strictly relies on the data provided in the master upload. - **Master Upload Functionality:** A new master upload capability allows administrators to replace the entire pincode dataset via an Excel file. - **Python Integration:** The actual Excel parsing, data integrity checks, soft-deleting of old records, and bulk inserting are offloaded to `cf-python-scripts`. To prevent table bloat over time without blocking API responses, a mathematically precise cleanup mechanism (grouped by an explicit `upload_batch_id`) runs in an asynchronous background daemon thread to hard-delete older soft-deleted copies. --- ## Architectural Additions ### Laravel Backend * **Migration:** `database/migrations/2026_07_18_120000_create_pincode_classifications_table.php` establishes the new table with columns for `id`, `upload_batch_id`, `pincode`, `type`, `state`, `city`, `area`, `location`, `comment`, `level`, `agency_name`, and `user_id`. It includes indexes for performance optimization but no unique constraints on the pincode column itself. * **Model:** `app/PincodeClassification.php` acts as the Eloquent representation, utilizing `SoftDeletes` and defining query scopes like `positive()`, `negative()`, `risky()`, and `pincode()`. * **Controller:** `app/Http/Controllers/Admin/PincodeController.php` exposes the `uploadMasterPincode` method, which proxies the Excel file to the Python service. It intelligently intercepts `400`/`422` validation errors, parsing them into an array (`pincode_errors`) to avoid massive unreadable strings, while cleanly obfuscating `500` server errors. ### User Interface (Blade) * **View:** `resources/views/admin/merchant/risk/edit.blade.php` was updated to include the file upload form and a link to the expected template. It features a custom `pincode_errors` UI block that renders multiple row-validation errors as a scrollable unordered list (max-height of 220px) to prevent massive validation failures from breaking the page layout. ### Python Microservice (`cf-python-scripts`) * **Endpoint:** `app.py` exposes `/api/process-pincode-master`. * **Processing Logic:** `scripts/pincode_master_processing.py` parses the file. It generates a UUIDv4 `upload_batch_id`, validates data (including explicit duplicate checks and POSITIVE type requirements), soft-deletes the current dataset, bulk inserts new records, and spins up a `threading.Thread` to execute batch cleanup. * **Database Queries:** `scripts/mysql_queries.py` houses the SQL (`insert_pincode_query`, `soft_delete_pincode_query`, and `cleanup_old_pincode_batches_query`). Insert query bindings were explicitly mapped using SQLAlchemy syntax (`:variable`) for seamless execution. --- ## Master Upload Implementation Details The master upload replaces the entire dataset on every upload. The process flow is as follows: 1. **User Action:** The admin uploads an Excel file via the Risk Based Pricing page. 2. **Laravel Proxy:** The `PincodeController` validates the file size and passes it via cURL to the Python service. 3. **Data Integrity Checks (Python):** - Normalizes columns. - Verifies the `Type` column correctly maps to `0`, `1`, or `2` (Negative, Positive, Risky). - Validates that no duplicate pincodes exist within the uploaded file itself. - Enforces that `AgencyName` is provided if the type is `POSITIVE` (or `1`). - Defaults the `level` of Negative pincodes to `1`. 4. **Database Operations:** - A unique UUIDv4 is generated for `upload_batch_id`. - A soft delete query (`UPDATE pincode_classifications SET deleted_at = NOW() WHERE deleted_at IS NULL`) moves the active dataset to a historical state. - The new rows are bulk inserted with the newly generated `upload_batch_id`. 5. **Asynchronous Data Cleanup:** - Immediately after insertion, a background daemon thread (`threading.Thread`) is launched. - It executes `cleanup_old_pincode_batches_query`, which mathematically groups rows by `upload_batch_id` and hard-deletes everything except the 3 most recently created batches (active + 2 historical), keeping the DB lean without blocking the API response. --- ## Impact & Affected Files ### Modified Files (Read Logic Switch) The core application read paths were updated to point to the new `PincodeClassification` model instead of the legacy tables: - `app/ConsumerApplication.php` - `app/Http/Controllers/Admin/ConsumerApplicationController.php` - `app/Http/Controllers/Grade/GradeController.php` - `app/Http/Controllers/Grade/AutoRejection.php` - `app/Http/Controllers/ConsumerAPI/ConsumerApiController.php` - `app/Http/Controllers/AnalyticsController.php` - `app/Http/Controllers/TestingController.php` - `app/Model/Prediction.php` ### Removed / Obsolete Files - **Models:** `app/ApprovedPincodeLists.php`, `app/NegativeArea.php` - **Views:** `resources/views/admin/approve-pincode/index.blade.php` (Legacy approved-only upload UI) - **Commands & Jobs:** `app/Console/Commands/PincodeImport.php`, `Jobs::startNegativeArea()` - **Events & Listeners:** `app/Events/V1/EventPincodeImport.php`, `app/Listeners/V1/ListenerPincodeImport.php` - **Imports:** Legacy `ExcelImport` and `TestImport` classes relating to the old `ApprovedPincodeLists` were stripped of this logic. ### Legacy Tables The old tables `approved_pin_codes` and `locations` remain in the database schema but are no longer written to or actively read by the primary application flows.