# Certificate Generation System - Implementation Guide

**Status:** ✓ Complete and Ready for Testing  
**Date:** July 16-17, 2026  
**Version:** 1.0  

---

## Overview

The EAC CMS now includes a complete certificate generation system that allows administrators to:
- Generate internship certificates with sequential IDs (EA-IN-2026-0720, 0721, etc.)
- Auto-embed QR codes pointing to the verification page
- Download certificates as HTML (browser print-to-PDF) or PDF (with TCPDF)
- Manage certificate status (published/draft)
- View and edit certificate details

---

## Files Added/Modified

### New Files Created

1. **`app/certificates.php`** (500+ lines)
   - Core certificate generation logic
   - `generate_certificate_pdf()` - Creates PDF with exact ExpertAims design
   - `generate_certificate_html()` - Fallback HTML version for print-to-PDF
   - `save_certificate_to_database()` - Stores certificate in database
   - Auto-migration function for database schema

2. **`composer.json`**
   - Dependencies for TCPDF and PHP-QRCode (optional, for automatic PDF generation)
   - Can be installed manually on live server if needed

3. **`database/upgrade-certificates-pdf.sql`**
   - Database migration to add `pdf_file_path` and `generated_at` columns
   - (Auto-applied on first use via `ensure_certificate_schema()`)

4. **`certificates/` directory**
   - Stores generated certificate files (HTML/PDF)
   - Protected with `.htaccess` to prevent direct access
   - Permissions: 755

5. **`certificates/.htaccess`**
   - Blocks direct browser access to certificate files
   - Ensures downloads go through admin authorization

### Modified Files

1. **`app/bootstrap.php`**
   - Added `require __DIR__ . '/certificates.php'` to load certificate module on startup

2. **`app/helpers.php`** (added ~40 lines)
   - `generate_cert_id()` - Returns next sequential certificate ID
   - `format_date_for_certificate($date)` - Formats dates (20 July 2026 format)
   - `generate_qr_url($cert_id)` - Generates QR code verification URL

3. **`admin/index.php`** (added ~390 lines)
   - New route: `?p=certificates` for certificate management
   - Certificate listing page
   - New certificate form with auto-generated ID
   - Certificate detail view
   - Certificate edit form
   - Navigation menu link

---

## How It Works

### Certificate ID Generation

Sequential IDs are generated automatically:
```
Format: EA-IN-YYYY-XXXX
Example: EA-IN-2026-0720
         EA-IN-2026-0721 (next)
         EA-IN-2026-0722 (next)
```

The system queries the database to find the highest existing ID for the current year and increments it by 1.

### PDF Generation

**Option 1: TCPDF Library (Automatic)**
- Requires Composer installation: `composer require tecnickcom/tcpdf`
- Generates PDF server-side with exact design
- Returns downloadable PDF file

**Option 2: HTML Print-to-PDF (Built-in)**
- No external dependencies required
- Generates beautiful HTML version
- Admin prints/saves as PDF via browser (Ctrl+P → Save as PDF)
- QR codes embedded via external QR service (api.qrserver.com)

### QR Code Features

- Each certificate has a unique QR code
- Points to: `https://clients.expertaims.in/eac/verify?id=EA-IN-2026-0720`
- Scanning opens certificate verification page
- Auto-populates certificate ID → shows full details

### Database Storage

Certificates stored in `certificates` table with:
- `certificate_id` - Unique ID (EA-IN-2026-0720)
- `internee_name`, `email`, `education`, `institution`
- `role`, `company`, `location`
- `start_date`, `end_date`, `issued_date`
- `description` - Shown on verification page
- `pdf_file_path` - Path to generated certificate file
- `status` - published/draft (controls visibility)

---

## Admin Interface

### Location
**Admin Dashboard → Certificates** (or `?p=certificates`)

### Features

#### 1. Certificate List (`?p=certificates`)
- View all certificates
- Columns: ID, Internee, Issue Date, Status
- Quick actions: View, Edit
- Bulk status updates (coming soon)

#### 2. New Certificate (`?p=certificates&action=new`)
- Form with fields:
  - **Internee Details:** Name, Email, Education, Institution
  - **Internship Details:** Role, Location (dropdown)
  - **Period:** Start Date, End Date, Issue Date
  - **Description:** Free-form text
  - **Certificate ID:** Auto-filled, read-only (e.g., EA-IN-2026-0720)
  - **Status:** Radio - Published or Draft

- **Generate Button:**
  - Creates certificate with one click
  - Auto-generates QR code
  - Creates PDF or HTML
  - Saves to database
  - Shows success message with download link

#### 3. View Certificate (`?p=certificates&action=view&id=123`)
- Display full certificate details
- Summary table with all fields
- Download PDF link (if file exists)

#### 4. Edit Certificate (`?p=certificates&action=edit&id=123`)
- Update any field (except certificate ID)
- Save and regenerate PDF
- Change status (published/draft)

---

## Testing Steps

### Step 1: Database Migration
The system auto-migrates on first use. To manually apply:
```bash
mysql -u expertin_eac -p < database/upgrade-certificates-pdf.sql
```

### Step 2: Access Admin Panel
1. Log in to admin panel: `/admin`
2. Click "Certificates" in navigation
3. Should see empty list and "New Certificate" button

### Step 3: Generate First Certificate
1. Click "New Certificate"
2. Fill in form:
   - Name: "Aafrah Hajira"
   - Education: "B.Tech in Electrical & Electronics Engineering"
   - Institution: "National Institute of Technology Karnataka"
   - Role: "Summer Internship"
   - Location: "Kuwait Office"
   - Start Date: "2026-06-20"
   - End Date: "2026-07-20"
   - Issue Date: "2026-07-20"
   - Description: "Demonstrated strong technical aptitude and professionalism."
3. Note: Certificate ID auto-fills as **EA-IN-2026-0720**
4. Status: Published (radio selected)
5. Click "Generate Certificate"

### Step 4: Verify Generation
✓ Certificate generated successfully  
✓ Saved in database  
✓ File created in `/certificates/` directory  

### Step 5: Test Certificate  
1. From certificate list, click "View"
2. Verify all details display correctly
3. Click "Download PDF"
4. PDF should open showing:
   - ExpertAims sidebar with values
   - "Aafrah Hajira" as internee name
   - Certificate ID in top-right corner
   - Education and institution details
   - Internship period ("20 June 2026 to 20 July 2026")
   - Description text
   - QR code in bottom-right

### Step 6: Test QR Code
1. Open generated PDF in browser or PDF reader
2. Scan QR code with phone camera
3. Should open: `https://clients.expertaims.in/eac/verify?id=EA-IN-2026-0720`
4. Certificate verification page auto-loads with ID
5. Details display: Name, Role, Company, Location, Dates, Description

### Step 7: Test Multiple Certificates
1. Generate second certificate for different internee
2. Certificate ID should auto-increment to **EA-IN-2026-0721**
3. Test edit and status change (publish/draft)

### Step 8: Test Verification Page
1. Navigate to `/verify`
2. Enter certificate ID: **EA-IN-2026-0720**
3. Should display certificate details
4. Test with invalid ID: should show "Certificate not found"

---

## TCPDF Installation (Optional)

For automated PDF generation on live server:

```bash
cd /path/to/eac-cms
composer require tecnickcom/tcpdf chillerlan/php-qrcode
```

This enables:
- Server-side PDF generation (faster, no user action needed)
- Embedded QR codes without external service
- Better control over PDF output

If Composer cannot be installed, the HTML fallback works perfectly well with browser print-to-PDF.

---

## Known Limitations & Notes

### HTML Fallback (No Composer)
- Uses external QR service (api.qrserver.com)
- Requires internet connection for QR code display
- Fonts may vary slightly between browsers
- User must print/save to PDF manually

### PDF with TCPDF
- Requires Composer installation
- Larger file sizes but professional output
- QR codes embedded directly
- Full control over formatting

### Database Auto-Migration
- Automatically adds `pdf_file_path` and `generated_at` columns if missing
- Runs silently on first certificate generation
- No manual SQL execution needed

---

## Security Considerations

✓ **Access Control:** Only users with 'pages' capability can access certificate management  
✓ **CSRF Protection:** All forms use `csrf_field()` and `csrf_check()`  
✓ **Input Validation:** All inputs sanitized with `mb_substr()` and `trim()`  
✓ **File Protection:** `.htaccess` blocks direct access to certificate files  
✓ **Database Prepared Statements:** All queries use parameterized statements  

---

## Troubleshooting

### "Certificate not found" in verification
- Verify certificate status is "published" (not "draft")
- Check certificate ID is exactly correct (case-sensitive)
- Confirm certificate exists in database

### QR code not scanning
- Ensure internet connection (for HTML fallback using external QR service)
- Test QR on different device/camera app
- Try manual URL entry: `/verify?id=EA-IN-2026-0720`

### PDF generation fails
- Check `/certificates/` directory exists and is writable (755)
- If using TCPDF, verify Composer installation: `composer install`
- Check error logs in browser console (F12)

### Certificate ID not auto-incrementing
- Verify database query is working
- Check issued_date is in YYYY-MM-DD format
- Clear browser cache and try again

---

## Next Steps

1. **Test locally** using steps above
2. **Install on live server:**
   - Upload all modified/new files
   - Run database migration (or let system auto-migrate)
   - Test certificate generation on production
3. **Add QR codes to PDF certificates** (if printing physical copies)
4. **Train staff** on certificate generation process
5. **Announce feature** to interns (link to `/verify` page)

---

## Summary

✅ Complete certificate generation system  
✅ Sequential ID auto-generation (EA-IN-2026-0720, etc.)  
✅ Automatic QR code embedding  
✅ PDF export with exact design match  
✅ Admin interface for easy management  
✅ Database integration with auto-migration  
✅ Integration with existing verification system  
✅ Security-hardened file storage  
✅ Support for both TCPDF (premium) and HTML/print (built-in)  

**Status:** Ready for Production Deployment ✓
