# Elasticsearch Job Sync — Knowledge Transfer

This document captures critical knowledge about how job postings sync to Elasticsearch and appear on the frontend. Share this with any AI agent working on the Kaabil codebase.

## Architecture

```
Job Posted (new recruiter ATS or org-recruiter)
    → INSERT/UPDATE company_jobs table (MySQL)
    → Push to RabbitMQ queue "updateJob" with {job_id: ID}
    → Node.js consumer (elasticsearch-jobs service) reads queue
    → Consumer queries MySQL for job data (WHERE status=2 AND company_master.status=1)
    → Consumer indexes document to Elasticsearch
    → Frontend (Next.js at kaabilprogram.org) queries ES via feed API
```

## Key URLs

| Environment | Feed API | Frontend |
|-------------|----------|----------|
| PROD | `feed.kaabilprogram.org` | `kaabilprogram.org/jobs` |
| UAT | `feed.meuat.kaam.com` | `kaabiljobs.kaam.com/jobs` |

## CRITICAL: job_expiry Field

The `company_jobs.job_expiry` column is `varchar(255)` in MySQL but mapped as `date` type in Elasticsearch.

**ONLY `YYYY-MM-DD` format works.** These cause SILENT indexing failure (job disappears from frontend with no error):
- Empty string `''`
- NULL
- Datetime `YYYY-MM-DD HH:MM:SS`
- `0000-00-00`

**Rule:** Always ensure `job_expiry` is set to a valid `YYYY-MM-DD` date when posting or editing jobs. Default: `date('Y-m-d', strtotime('+29 days'))`.

## When Jobs Don't Appear on Frontend

Checklist (in order):
1. **DB status:** `company_jobs.status` must be `2` (Active)
2. **Company status:** `company_master.status` must be `1`
3. **job_expiry:** Must be non-empty, valid `YYYY-MM-DD` format
4. **RabbitMQ:** Job update was pushed to queue after save
5. **ES consumer:** Message was consumed and processed
6. **ES index:** Document exists in Elasticsearch

## How to Verify

**Query PROD Elasticsearch directly:**
```bash
curl -s -X POST "https://feed.kaabilprogram.org/api/job/list?page=1&pageLength=50&userId=0&slug=" \
  -H "Content-Type: application/json" \
  -d '{"company_id_filter":["COMPANY_ID"],"sort":3,"recommendate":false, \
       "soft_skill_filter":[],"skill_filter":[],"job_location_types_filter":[], \
       "industries_filter":[],"location_filter":[],"benefits_filter":[], \
       "job_types_filter":[],"experience_filter":[],"min_salary":null,"max_salary":null,"search":""}'
```

**Compare DB count vs ES count:**
```sql
-- Active jobs in DB
SELECT COUNT(*) FROM company_jobs WHERE company_master_id = X AND status = 2;

-- Then compare with ES total from the API response above
```

## How to Fix Missing Jobs

1. Fix the data issue (usually empty `job_expiry`):
```sql
UPDATE company_jobs SET job_expiry = DATE_ADD(CURDATE(), INTERVAL 29 DAY) WHERE id = X;
```

2. Push to RabbitMQ to re-index:
```php
require_once 'app/Services/RabbitMQService.php';
$rmq = new RabbitMQService();
$rmq->pushJobUpdate($jobId);
```

3. Or full re-index via sysadmin: Job Reports → "Sync to ES" button

## RabbitMQ Details

| Environment | Host | Port | User | Queues |
|-------------|------|------|------|--------|
| PROD | 13.203.37.197 | 5672 | (encrypted in RabbitMQService.php) | updateJob, syncJobs, user_auto_match |
| UAT | 65.0.113.221 | 5672 | guest/guest | Same queues |

Consumer service: Node.js at `resume-builder-v1/services/elasticsearch-jobs/`
Consumer server: 13.200.132.142

## Past Incidents

1. **Job 993 (UAT):** Posted with empty job_expiry → not in ES. Fixed by setting date-only format.
2. **Jobs 1395-1398 (PROD, Shoppers Stop):** 4 of 6 active jobs missing from frontend. All had empty job_expiry. Fixed by setting valid dates.
3. **Job_expiry datetime format:** Jobs posted with `YYYY-MM-DD HH:MM:SS` (from `DATE_ADD` SQL function) failed to index. Must use date-only.

## Case Study: Shoppers Stop (Company 533) — Full Issue & Resolution

### Problem 1: Jobs not appearing on kaabilprogram.org/jobs

**Symptom:** Company had 6 active jobs in DB but only 2 showed on `kaabilprogram.org/jobs?company=Shoppers+Stop&cmp_id=533`.

**Investigation:**
```bash
# Step 1: Check DB — 6 active jobs
SELECT id, status, job_expiry FROM company_jobs WHERE company_master_id = 533 AND status = 2;
# Result: 1367 (expiry: 2026-03-31), 1387 (2026-03-31), 1395 (empty), 1396 (empty), 1397 (empty), 1398 (empty)

# Step 2: Check PROD ES — only 2 jobs
curl -X POST "https://feed.kaabilprogram.org/api/job/list?page=1&pageLength=50&userId=0&slug=" \
  -d '{"company_id_filter":["533"],...}'
# Result: total: 2 (only 1367 and 1387)
```

**Root cause:** Jobs 1395-1398 had **empty `job_expiry`**. ES mapping rejects documents with empty date fields silently.

**Fix:**
```sql
UPDATE company_jobs SET job_expiry = '2026-04-22' WHERE id IN (1395, 1396, 1397, 1398);
```
Then push to RabbitMQ to re-index, or use sysadmin "Sync to ES" button.

**Prevention:** `add_job.php` now defaults `job_expiry` to `date('Y-m-d', strtotime('+29 days'))` when not provided.

### Problem 2: "Subscription has expired" when superadmin tries to repost

**Symptom:** On `kaabilprogram.org/superadmin`, clicking Repost on Shoppers Stop jobs showed "Subscription has expired" popup.

**Investigation:**
The superadmin repost controller (`Jobposting.php` line 1567-1570) runs:
```php
$user_data = $this->db->select("uc.user_id")
    ->where(['uc.is_legal_company_verified_by_admin' => 1,
             'uc.subscription_end_date >' => date('Y-m-d'),
             'uc.company_master_id' => $company_master_id])
    ->get('user_companies uc')->row_array();
```
If `$user_data` is empty → `checkUserJobPack()` returns `"Subscription has expired"` (line 1622).

For company 533, ALL `user_companies` rows had:
- `subscription_end_date = NULL`
- `user_susbcription_order_id = 0`

**Why:** Jobs were posted from new recruiter ATS which doesn't enforce subscriptions. Org-recruiter sets subscription data during recruiter onboarding/payment, but companies onboarded via new recruiter ATS skip this.

**Fix (DB — per company):**
```sql
-- 1. Set subscription dates for the company's admin user
UPDATE user_companies
SET subscription_end_date = '2045-12-31',
    subscription_unlimited_end_date = '2045-12-31'
WHERE company_master_id = 533 AND user_id = 121715 AND status = 1;

-- 2. Create subscription order record (superadmin checks this)
INSERT INTO user_susbcription_order (
    user_id, company_master_id, legal_company_master_id,
    gstin_no, transaction_id, subscription_package_type_id,
    gross_amount, discount, sub_total, mrp,
    cgst, sgst, igst, total,
    state_name, state_code, address_line_1, address_line_2,
    is_coupon_applied, coupon_master_id, payment_mode,
    status, payment_mode_type, created_date, created_by, updated_by
) VALUES (
    121715, 533, 465,
    '', 'ats-recruiter-533', 1,
    0, 0, 0, 0, 0, 0, 0, 0,
    '', '', '', '',
    0, 0, 0, 1, 1, NOW(), 121715, 121715
);

-- 3. Link order to user_companies
SET @oid = LAST_INSERT_ID();
UPDATE user_companies SET user_susbcription_order_id = @oid
WHERE company_master_id = 533 AND user_id = 121715 AND status = 1;
```

**Prevention:** `add_job.php` now auto-ensures subscription data when posting a job for a company that lacks it. Sets far-future subscription date and creates a zero-cost order record.

**How to find other affected companies:**
```sql
SELECT DISTINCT cj.company_master_id, cm.name
FROM company_jobs cj
JOIN company_master cm ON cm.id = cj.company_master_id
WHERE cj.status IN (2, 4)
AND cj.company_master_id NOT IN (
    SELECT company_master_id FROM user_companies
    WHERE subscription_end_date > NOW() AND is_legal_company_verified_by_admin = 1 AND status = 1
);
```

### Problem 3: "35 jobs for you" count mismatch

**Symptom:** Page shows "35 jobs for you" but only 6 job cards visible.

**Root cause:** Frontend (`kaabil-frontend/src/components/JobList/index.tsx` line 425) displays `total_vacancies` (sum of all `no_of_positions_available` across jobs) with the label "jobs for you". The 6 jobs have a combined 35 open positions.

**This is a frontend label bug** — should either show `total` (6, actual job count) or change label to "vacancies".

**File to fix:** `mock-interview-v1/kaabil-frontend/src/components/JobList/index.tsx` line 425:
```jsx
// Current (wrong):
{totalJobsVacancy} jobs for you
// Fix option 1 — show job count:
{totalJobs} jobs for you
// Fix option 2 — accurate label:
{totalJobsVacancy} vacancies for you
```

## Subscription Requirement for Superadmin Repost

Org-recruiter superadmin checks `user_companies.subscription_end_date > NOW()` before allowing job repost. Jobs posted from new recruiter ATS may lack subscription data. Fix: ensure the company's admin user in `user_companies` has `subscription_end_date = '2045-12-31'` and a valid `user_susbcription_order` record.

## Key Files

| File | Purpose |
|------|---------|
| `recruiter/app/Services/RabbitMQService.php` | Pushes to RabbitMQ (env-aware) |
| `recruiter/public/add_job.php` | Job posting with default job_expiry + auto-subscription |
| `recruiter/app/Services/JobActionService.php` | Repost/activate (updates job_expiry) |
| `resume-builder-v1/services/elasticsearch-jobs/src/service/JobService.js` | ES consumer |
| `mock-interview-v1/kaabil-frontend/src/components/JobList/index.tsx` | Frontend job listing (has vacancies count bug) |
| `org-recruiter/application/controllers/superadmin/Jobposting.php` | Superadmin repost with subscription check (lines 1567-1622) |
| `org-recruiter/application/models/V2/CompanyModel.php` | createJob with subscription validation (line 905) |
