# Job Draft Status Troubleshooting Guide

## Overview

When recruiters report jobs going to "Draft" status instead of "Active", this guide helps diagnose and fix the issue.

## How Job Status Works

### Status Codes
| Code | Status | Description |
|------|--------|-------------|
| 2 | Active | Live, accepting applications |
| 3 | Inactive | Manually deactivated |
| 4 | Expired | Past job_expiry date |
| 5 | Deleted | Soft-deleted |
| 6 | Draft | Not published |
| 7 | Discard | Cancelled |

### New Recruiter (tools.kaabil.cc/recruiter)
- **File**: `public/add_job.php` line 237
- Jobs are inserted with **status=2 (Active)** directly
- **No draft state** — jobs go live immediately
- No subscription checks — any user with company access can post

### Org-Recruiter (kaabilprogram.org/recruiter)
- **File**: `org-recruiter/application/models/V2/CompanyModel.php` line 220
- Jobs are inserted with **status=6 (Draft)** first
- Auto-publishes to **status=2** ONLY if ALL conditions pass (see checklist below)
- Sub-admins (parent_id != 0) can NEVER auto-publish — "Please contact your company administrator"

## Auto-Publish Conditions Checklist (Org-Recruiter)

**File**: `org-recruiter/application/models/V2/CompanyModel.php` lines 904-908

ALL of these must be true for a job to auto-publish:

| # | Condition | Table/Column | Check |
|---|-----------|-------------|-------|
| 1 | Job type set | `company_jobs_types.job_type_master_id != 0` | Must have at least one job type |
| 2 | Location type set | `company_jobs.job_location_type_master_id != 0` | Remote/Onsite/Hybrid |
| 3 | Company set | `company_jobs.company_master_id != 0` | Must be linked to company |
| 4 | Profession set | `company_jobs.profession_master_id != 0` | Job role selected |
| 5 | Skills set | `company_jobs_skills` has records | At least one skill |
| 6 | Salary present | `company_jobs.is_salary_present != 0` | Salary info provided |
| 7 | Experience present | `company_jobs.is_exp_present != 0` | Experience info provided |
| 8 | Job completed flag | `job_completed == '1'` | Final step submitted |
| 9 | Subscription valid | `subscription_end_date > current_date` | Not expired |
| 10 | Job quota available | `pending_job_count > 0 OR unlimited_job_post == 1` | Has remaining job posts |
| 11 | User is admin | `users.parent_id == 0` | Sub-admins always stay draft |

### How Pending Job Count Works
```
user_companies.user_susbcription_order_id → user_susbcription_job_counting.total_count
pending_job_count = total_count - used_count
```
If `user_susbcription_order_id = 0` → no subscription order → `pending_job_count = 0` → **auto-publish fails**

### How Unlimited Job Post Works
```
user_companies.subscription_unlimited_end_date > current_date → unlimited_job_post = 1
```
If `subscription_unlimited_end_date IS NULL` → `unlimited_job_post = 0`

## Diagnostic SQL Queries

### Step 1: Find the company and user
```sql
SELECT u.id, u.name, u.mobile, u.parent_id, uc.role_type,
       uc.subscription_end_date, uc.subscription_unlimited_end_date,
       uc.user_susbcription_order_id,
       cm.name as company, cm.is_company_verified_by_admin
FROM users u
JOIN user_companies uc ON u.id = uc.user_id AND uc.status = 1
JOIN company_master cm ON uc.company_master_id = cm.id
WHERE cm.name LIKE '%COMPANY_NAME%';
```

### Step 2: Check their jobs
```sql
SELECT cj.id, cj.job_title, cj.status, cj.created_by, cj.created_date,
       cj.job_posted_date, cj.profession_master_id, cj.is_salary_present,
       cj.is_exp_present, cj.job_location_type_master_id
FROM company_jobs cj
WHERE cj.company_master_id = COMPANY_ID
ORDER BY cj.id DESC LIMIT 20;
```

### Step 3: Check job types exist
```sql
SELECT company_job_id, job_type_master_id
FROM company_jobs_types
WHERE company_job_id IN (JOB_IDS) AND status = 1;
```

### Step 4: Check skills exist
```sql
SELECT company_job_id, COUNT(*) as skill_count
FROM company_jobs_skills
WHERE company_job_id IN (JOB_IDS) AND status = 1
GROUP BY company_job_id;
```

### Step 5: Check subscription order
```sql
SELECT * FROM user_susbcription_job_counting
WHERE user_susbcription_order_id = ORDER_ID;
```

## Common Causes & Fixes

### Cause 1: No subscription order (MOST COMMON)
**Symptom**: `user_susbcription_order_id = 0`, `subscription_unlimited_end_date = NULL`
**Impact**: `pending_job_count = 0` AND `unlimited_job_post = 0` → auto-publish fails
**Fix**: Set unlimited job posting:
```sql
UPDATE user_companies
SET subscription_unlimited_end_date = '2045-12-31'
WHERE user_id = USER_ID AND status = 1;
```

### Cause 2: User is sub_admin
**Symptom**: `users.parent_id != 0`
**Impact**: Org-recruiter always returns "Job is in draft stage. Please contact your company administrator"
**Fix**: No fix possible in org-recruiter — this is by design. Use new recruiter instead.

### Cause 3: Missing required fields
**Symptom**: Job has empty skills, salary, or experience
**Fix**: Complete the job posting form, or manually activate:
```sql
UPDATE company_jobs
SET status = 2, job_expiry = DATE_ADD(CURDATE(), INTERVAL 30 DAY), job_posted_date = NOW()
WHERE id = JOB_ID AND status = 6;
```

### Cause 4: Subscription expired
**Symptom**: `subscription_end_date < current_date`
**Fix**: Extend subscription:
```sql
UPDATE user_companies
SET subscription_end_date = '2027-12-31'
WHERE user_id = USER_ID AND status = 1;
```

## Recommended Long-Term Fix

Redirect recruiters to the **new recruiter** (tools.kaabil.cc/recruiter) which:
- Sets status=2 (Active) directly
- No subscription checks
- No draft state for regular job postings
- Supports same multi-company architecture

Enable SSO redirect:
```sql
UPDATE ats_settings SET setting_value = '1' WHERE setting_key = 'enable_new_recruiter_redirect';
```

## Case Study: Ample (April 2026)

**Company**: Ample (id=494)
**Admin**: Anusuya Narayanaswamy (id=117090, parent_id=0, role=admin)
**Issue**: 6 draft jobs, 1 active job

**Root Cause**: `user_susbcription_order_id = 0` and `subscription_unlimited_end_date = NULL`
- `pending_job_count = 0` (no subscription order to count from)
- `unlimited_job_post = 0` (no unlimited end date set)
- Auto-publish condition `(pending_job_count > 0 || unlimited_job_post == 1)` → FALSE

**Why 1 job succeeded**: Job 1389 was posted via the new recruiter (add_job.php) which bypasses subscription checks entirely.

**Fix applied**: Set `subscription_unlimited_end_date = '2045-12-31'` for unlimited job posting, then manually activated the valid draft jobs.
