# Certificate Verification System

ExpertAims Certificate Verification System allows interns and course participants to verify their certificates using a unique Certificate ID. This document explains how to implement and manage the verification system.

## Overview

The verification system consists of:
1. **Database table** (`certificates`) — stores certificate data
2. **API endpoint** (`/verify?id=...`) — JSON API for certificate lookups
3. **Public verification page** (`/verify-certificate`) — HTML form for users to verify certificates
4. **Admin interface** — manage certificates in the admin panel

## Setup Instructions

### Step 1: Import Database Schema

Import the certificate table schema into your MySQL database:

1. Go to **cPanel → phpMyAdmin**
2. Select your database
3. Click **Import**
4. Upload or paste the SQL from `database/upgrade-certificates.sql`
5. Click **Go**

The schema creates:
- `certificates` table with fields for certificate data
- Sample certificate from Aafrah Hajira (EA-DEMO-2026-0001) for testing

### Step 2: Add Verification Page

Import the verify-certificate page:

1. In **cPanel → phpMyAdmin**
2. Upload `database/upgrade-verify-page.sql`
3. The page will appear at `/verify-certificate` on your site

Or manually add it in the admin panel:
1. Go to **Admin → Content → Pages**
2. Click **Add Page**
3. Set slug to `verify-certificate`
4. Set title to "Verify Certificate"
5. Add content using the template below

### Step 3: Customize the Verification Page

The template HTML is available in `/certificate-verify-template.html`. Add this content to your verification page:

1. Go to **Admin → Content → Pages**
2. Find **verify-certificate** page
3. Add a richtext section with the verification form HTML
4. Or use the built-in endpoint directly with a custom form

## Database Schema

### certificates table

```sql
CREATE TABLE certificates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  certificate_id VARCHAR(50) NOT NULL UNIQUE,     -- e.g., "EA-DEMO-2026-0001"
  internee_name VARCHAR(160) NOT NULL,             -- Full name
  email VARCHAR(190) DEFAULT NULL,                 -- Optional email
  education VARCHAR(255) DEFAULT NULL,             -- Degree/program
  institution VARCHAR(255) DEFAULT NULL,           -- College/University
  role VARCHAR(160) NOT NULL,                      -- e.g., "Summer Internship"
  company VARCHAR(255) NOT NULL,                   -- e.g., "ExpertAims Consultancy LLP"
  location VARCHAR(160) DEFAULT NULL,              -- e.g., "Kuwait Office"
  start_date DATE NOT NULL,                        -- Program start date
  end_date DATE NOT NULL,                          -- Program end date
  issued_date DATE NOT NULL,                       -- Certificate issue date
  description TEXT DEFAULT NULL,                   -- Optional notes/remarks
  status ENUM('published','draft') NOT NULL,       -- Only 'published' certs are verified
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_cert_id (certificate_id),
  KEY idx_status (status)
);
```

## API Usage

### Verify Certificate (JSON API)

**Endpoint:** `/verify?id=CERTIFICATE_ID`  
**Method:** GET  
**Response:** JSON

#### Request

```
GET /verify?id=EA-DEMO-2026-0001
```

#### Success Response (200)

```json
{
  "ok": true,
  "certificate": {
    "id": "EA-DEMO-2026-0001",
    "name": "Aafrah Hajira",
    "role": "Summer Internship",
    "company": "ExpertAims Consultancy LLP",
    "location": "Kuwait Office",
    "start_date": "2026-06-20",
    "end_date": "2026-07-20",
    "issued_date": "2026-07-20",
    "education": "Fourth Semester, B.Tech in Electrical & Electronics Engineering",
    "institution": "National Institute of Technology Karnataka (NITK), Surathkal – 575025",
    "description": "Sincere, hardworking and inquisitive, demonstrated strong technical aptitude and professionalism."
  }
}
```

#### Error Response (404)

```json
{
  "ok": false,
  "error": "not_found"
}
```

## Managing Certificates

### Adding a New Certificate

**Via SQL:**
```sql
INSERT INTO certificates (
  certificate_id, internee_name, email, education, institution,
  role, company, location, start_date, end_date, issued_date,
  description, status
) VALUES (
  'EA-IN-2026-0721',
  'Jane Doe',
  'jane@example.com',
  'B.Tech in Computer Science',
  'NIT Karnataka',
  'Summer Internship',
  'ExpertAims Consultancy LLP',
  'Kuwait Office',
  '2026-06-20',
  '2026-07-20',
  '2026-07-20',
  'Excellent performance and dedication.',
  'published'
);
```

**Via Admin Panel (when implemented):**
1. Go to **Admin → Content → Certificates** (or similar)
2. Click **Add Certificate**
3. Fill in all required fields
4. Set status to "Published"
5. Click **Save**

### Bulk Import

To import multiple certificates from a CSV file:

1. Export/prepare CSV with columns:
   - certificate_id
   - internee_name
   - email
   - education
   - institution
   - role
   - company
   - location
   - start_date (YYYY-MM-DD)
   - end_date (YYYY-MM-DD)
   - issued_date (YYYY-MM-DD)
   - description

2. Use phpMyAdmin or a script to import

## Verification Page Features

The public verification page includes:

- **Search box** — enter certificate ID to verify
- **Instant lookup** — real-time verification via API
- **Full details display** — shows all certificate information
- **Error handling** — friendly messages for invalid IDs
- **QR code integration** — certificate PDF can link to `/verify-certificate?cert=EA-DEMO-2026-0001`
- **Mobile responsive** — works on all devices
- **FAQ section** — explains how to use the tool

### URL Shortcuts

Users can verify directly using URL parameters:

```
/verify-certificate?cert=EA-DEMO-2026-0001
```

The form will auto-fill and verify immediately.

## QR Code Integration

Print QR codes on certificates that link to the verification page:

```
https://www.expertaims.com/verify-certificate?cert=EA-DEMO-2026-0001
```

When scanned, users can instantly verify the certificate on their phone.

## Security & Privacy

- **Published only** — only certificates with status "published" are verifiable
- **No PII leakage** — verification shows only intended public information
- **Rate limiting** — add if needed to prevent enumeration attacks
- **HTTPS only** — always use HTTPS in production
- **No sensitive data** — never store personal IDs, passport numbers, etc.

## File Locations

```
eac-cms/
├── app/
│   └── helpers.php              (certificate helper functions)
├── index.php                    (API endpoint & verification page route)
├── database/
│   ├── schema.sql               (base tables)
│   ├── upgrade-certificates.sql (certificate table + sample data)
│   └── upgrade-verify-page.sql  (verification page content)
└── CERTIFICATE-VERIFICATION.md  (this file)
```

## Functions Added to helpers.php

### find_certificate($cert_id)
```php
// Find a published certificate by ID
$cert = find_certificate('EA-DEMO-2026-0001');
if ($cert) {
  echo $cert['internee_name'];
}
```

### all_certificates()
```php
// Get all published certificates (for bulk display, admin list)
$certs = all_certificates();
foreach ($certs as $c) {
  echo $c['internee_name'];
}
```

## Routes Added to index.php

1. **`/verify?id=...`** — JSON verification API
2. **`/verify-certificate`** — HTML verification page
3. **`/verify-certificate?cert=...`** — direct verification with auto-fill

## Testing

### Test the API

```bash
# Test successful verification
curl "https://www.expertaims.com/verify?id=EA-DEMO-2026-0001"

# Test invalid ID
curl "https://www.expertaims.com/verify?id=INVALID"

# Test missing ID
curl "https://www.expertaims.com/verify"
```

### Test the Page

1. Visit `/verify-certificate`
2. Enter `EA-DEMO-2026-0001` (the sample certificate)
3. Verify it displays correctly
4. Try invalid IDs to test error handling

## Deployment

1. **Local testing** — import SQL, test routes
2. **Push to GitHub** — commit new files
3. **Deploy to cPanel**:
   - Upload `index.php` (updated)
   - Upload `app/helpers.php` (updated)
   - Upload `database/upgrade-*.sql` files
   - Import SQL files in phpMyAdmin
   - Test verification page and API

## Future Enhancements

- [ ] Admin panel to manage certificates
- [ ] Bulk CSV import feature
- [ ] Email notifications on certificate creation
- [ ] Digital signature verification
- [ ] Certificate analytics (view counts, verification frequency)
- [ ] Batch certificate generation tool
- [ ] QR code generator in admin panel
- [ ] Certificate template editor
- [ ] Expiration/revocation support

## Troubleshooting

### Certificates not found

**Check:**
- Is the certificate status set to "published"?
- Is the certificate_id exactly correct? (case-sensitive)
- Did you import the SQL correctly?
- Check database: `SELECT * FROM certificates;`

### API returns 404

- Verify the certificate exists: `SELECT * FROM certificates WHERE certificate_id = 'YOUR-ID';`
- Check the certificate status is "published"
- Clear any CDN/caching layers

### Page not found

- Did you import `upgrade-verify-page.sql`?
- Check the page exists: `SELECT * FROM pages WHERE slug = 'verify-certificate';`
- Verify the page status is "published"

## Support

For issues or questions:
- Email: info@expertaims.com
- Phone: +965 9090 8625

## Sample Certificate Data

A sample certificate is pre-loaded:

| Field | Value |
|-------|-------|
| Certificate ID | EA-DEMO-2026-0001 |
| Name | Aafrah Hajira |
| Role | Summer Internship |
| Company | ExpertAims Consultancy LLP |
| Location | Kuwait Office |
| Duration | June 20, 2026 – July 20, 2026 |
| Education | Fourth Semester, B.Tech in Electrical & Electronics Engineering |
| Institution | National Institute of Technology Karnataka (NITK), Surathkal – 575025 |
| Status | Published (active) |

Use this to test the verification system.
