<?php
/**
 * Certificate Generation Module
 * Handles PDF generation, QR code embedding, and database storage
 */

// Auto-migrate database schema if needed
function ensure_certificate_schema(){
  try {
    // Check if pdf_file_path column exists
    $columns = db()->query("DESCRIBE certificates")->fetchAll(PDO::FETCH_ASSOC);
    $column_names = array_column($columns, 'Field');

    if (!in_array('pdf_file_path', $column_names, true)) {
      db()->exec("ALTER TABLE certificates ADD COLUMN pdf_file_path VARCHAR(255) DEFAULT NULL AFTER description");
    }

    if (!in_array('generated_at', $column_names, true)) {
      db()->exec("ALTER TABLE certificates ADD COLUMN generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER pdf_file_path");
    }

    // Home country of the intern (they come from many countries)
    if (!in_array('country', $column_names, true)) {
      db()->exec("ALTER TABLE certificates ADD COLUMN country VARCHAR(120) DEFAULT NULL AFTER institution");
    }
  } catch (\Throwable $e) {
    // Silent fail - column might already exist or table might be missing
  }
}

// Run schema check on load
ensure_certificate_schema();

function generate_certificate_pdf($data){
  /**
   * Generates a certificate PDF with the ExpertAims design
   *
   * Expected $data array:
   *   - internee_name: Full name of internee
   *   - education: Education details (e.g., "B.Tech in Electrical & Electronics Engineering")
   *   - institution: Institution name
   *   - role: Internship role (e.g., "Summer Internship")
   *   - location: Office location (e.g., "Kuwait Office")
   *   - start_date: YYYY-MM-DD format
   *   - end_date: YYYY-MM-DD format
   *   - issued_date: YYYY-MM-DD format
   *   - description: Additional text
   *   - cert_id: Certificate ID (auto-generated)
   *
   * Returns: ['success' => bool, 'path' => string (file path), 'message' => string]
   */

  $data = [
    'internee_name' => trim((string) ($data['internee_name'] ?? '')),
    'education' => trim((string) ($data['education'] ?? '')),
    'institution' => trim((string) ($data['institution'] ?? '')),
    'country' => trim((string) ($data['country'] ?? '')),
    'role' => trim((string) ($data['role'] ?? '')),
    'location' => trim((string) ($data['location'] ?? '')),
    'company' => 'ExpertAims Consultancy LLP',
    'start_date' => trim((string) ($data['start_date'] ?? '')),
    'end_date' => trim((string) ($data['end_date'] ?? '')),
    'issued_date' => trim((string) ($data['issued_date'] ?? '')),
    'description' => trim((string) ($data['description'] ?? '')),
    'cert_id' => trim((string) ($data['cert_id'] ?? ''))
  ];

  // Validate required fields
  if (empty($data['internee_name']) || empty($data['cert_id'])) {
    return ['success' => false, 'message' => 'Missing required fields: internee_name or cert_id'];
  }

  // Create certificates directory if it doesn't exist
  $cert_dir = dirname(__DIR__) . '/certificates';
  if (!is_dir($cert_dir)) {
    @mkdir($cert_dir, 0755, true);
  }

  // Generate filename
  $safe_name = preg_replace('/[^a-z0-9-]/i', '', str_replace(' ', '-', $data['internee_name']));
  $pdf_filename = $data['cert_id'] . '-' . $safe_name . '.pdf';
  $pdf_path = $cert_dir . '/' . $pdf_filename;

  // Try TCPDF first (if installed), otherwise fall back to HTML
  if (class_exists('TCPDF')) {
    return generate_certificate_pdf_tcpdf($data, $pdf_path);
  } else {
    // Fallback: Generate HTML version for browser print-to-PDF
    return generate_certificate_html($data, $pdf_path);
  }
}

function generate_certificate_pdf_tcpdf($data, $pdf_path){
  /**
   * Generates PDF using TCPDF library
   * Requires: tecnickcom/tcpdf installed via Composer
   */
  try {
    // Landscape A4 (297 x 210 mm) to match the ExpertAims certificate design.
    $pdf = new TCPDF('L', 'mm', 'A4', true, 'UTF-8', false);

    // Set document properties
    $pdf->SetCreator('EAC CMS');
    $pdf->SetAuthor('ExpertAims Consultancy');
    $pdf->SetTitle('Certificate - ' . $data['internee_name']);

    // Remove default header/footer + page margins, disable auto page breaks
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);
    $pdf->SetMargins(0, 0, 0);
    $pdf->SetAutoPageBreak(false, 0);
    $pdf->AddPage();

    // Brand asset paths (rendered only if the file exists)
    $brand = dirname(__DIR__) . '/assets/brand/';
    $logo_full = $brand . 'logo-full.png';      // colour wordmark (white bg)
    $logo_e    = $brand . 'logo-e-dark.png';    // light e-mark (dark bg)

    // Palette
    $red   = [196, 30, 58];   // #C41E3A
    $navy  = [26, 31, 46];    // #1A1F2E
    $gold  = [193, 155, 74];  // ISO badge ring
    $ink   = [34, 34, 34];
    $muteC = [120, 120, 120];

    // ── LEFT SIDEBAR (0–62mm, full height) ──
    $sbW = 62;
    $pdf->SetFillColor($navy[0], $navy[1], $navy[2]);
    $pdf->Rect(0, 0, $sbW, 210, 'F');
    // thin red seam on the sidebar's inner edge
    $pdf->SetFillColor($red[0], $red[1], $red[2]);
    $pdf->Rect($sbW - 1.2, 0, 1.2, 210, 'F');

    // sidebar emblem
    if (is_file($logo_e)) {
      @$pdf->Image($logo_e, ($sbW - 20) / 2, 12, 20, 0, '', '', '', false, 300, '', false, false, 0);
    }
    $pdf->SetTextColor(255, 255, 255);
    $pdf->SetFont('helvetica', 'B', 11);
    $pdf->SetXY(2, 44);
    $pdf->Cell($sbW - 4, 5, 'EXPERTAIMS', 0, 1, 'C');
    $pdf->SetFont('helvetica', '', 7);
    $pdf->SetXY(2, 49.5);
    $pdf->SetTextColor(180, 180, 190);
    $pdf->Cell($sbW - 4, 3, 'S I N C E   2 0 1 2', 0, 1, 'C');

    // sidebar value pillars
    $values = [
      'INNOVATE'    => 'We imagine better solutions',
      'COLLABORATE' => 'We work together for greater impact',
      'DELIVER'     => 'We build with excellence',
      'EMPOWER'     => 'We enable growth and transformation',
    ];
    $y = 66;
    foreach ($values as $title => $subtitle) {
      // red bullet dot
      $pdf->SetFillColor($red[0], $red[1], $red[2]);
      $pdf->Circle(9, $y + 2.2, 1.6, 0, 360, 'F');
      $pdf->SetTextColor(255, 255, 255);
      $pdf->SetFont('helvetica', 'B', 9.5);
      $pdf->SetXY(13, $y);
      $pdf->Cell($sbW - 15, 5, $title, 0, 1, 'L');
      $pdf->SetTextColor(170, 172, 182);
      $pdf->SetFont('helvetica', '', 7);
      $pdf->SetXY(13, $y + 5);
      $pdf->MultiCell($sbW - 16, 3, $subtitle, 0, 'L');
      $y += 32;
    }

    // ── MAIN CONTENT AREA (x: 62–297) ──
    $mx = $sbW + 8;             // left text edge
    $mw = 297 - $mx - 10;       // usable width
    $cx = $mx;                  // cell x
    $cw = $mw;                  // cell width (for centred cells)

    // Full colour logo, top-centre of the main area
    if (is_file($logo_full)) {
      $lw = 74;
      @$pdf->Image($logo_full, $mx + ($mw - $lw) / 2, 12, $lw, 0, '', '', '', false, 300, '', false, false, 0);
    }

    // Certificate ID pill, top-right
    $pillW = 66; $pillX = 297 - 10 - $pillW; $pillY = 12;
    $pdf->SetFillColor(20, 20, 20);
    $pdf->RoundedRect($pillX, $pillY, $pillW, 8, 1.5, '1111', 'F');
    $pdf->SetTextColor(255, 255, 255);
    $pdf->SetFont('helvetica', 'B', 8.5);
    $pdf->SetXY($pillX, $pillY + 0.6);
    $pdf->Cell($pillW, 7, 'Certificate ID: ' . $data['cert_id'], 0, 0, 'C');

    // Heading
    $pdf->SetXY($cx, 40);
    $pdf->SetFont('times', 'B', 30);
    $pdf->SetTextColor($red[0], $red[1], $red[2]);
    $pdf->Cell($cw, 12, 'INTERNSHIP CERTIFICATE', 0, 1, 'C');
    // underline accent
    $pdf->SetDrawColor($red[0], $red[1], $red[2]);
    $pdf->SetLineWidth(0.5);
    $pdf->Line($mx + $mw / 2 - 22, 54, $mx + $mw / 2 + 22, 54);

    // "THIS IS TO CERTIFY THAT"
    $pdf->SetFont('helvetica', '', 10);
    $pdf->SetTextColor($muteC[0], $muteC[1], $muteC[2]);
    $pdf->SetXY($cx, 58);
    $pdf->Cell($cw, 5, 'T H I S   I S   T O   C E R T I F Y   T H A T', 0, 1, 'C');

    // Internee name (large italic serif — stands in for the script hand)
    $pdf->SetFont('times', 'BI', 40);
    $pdf->SetTextColor($red[0], $red[1], $red[2]);
    $pdf->SetXY($cx, 64);
    $pdf->Cell($cw, 18, $data['internee_name'], 0, 1, 'C');

    // Education + institution (+ country)
    $pdf->SetFont('times', '', 12);
    $pdf->SetTextColor($ink[0], $ink[1], $ink[2]);
    $pdf->SetXY($cx, 86);
    $pdf->Cell($cw, 5, $data['education'], 0, 1, 'C');
    $institution_line = $data['institution'];
    if (!empty($data['country'])) {
      $institution_line .= ($institution_line !== '' ? ', ' : '') . $data['country'];
    }
    $pdf->SetXY($cx, 91.5);
    $pdf->Cell($cw, 5, $institution_line, 0, 1, 'C');

    // Completion sentence
    $start = format_date_for_certificate($data['start_date']);
    $end   = format_date_for_certificate($data['end_date']);
    $sentence = 'has successfully completed ' . $data['role'] . ' at ' . $data['company']
              . " \xe2\x80\x94 " . $data['location'] . ' during the period ' . $start . ' to ' . $end . '.';
    $pdf->SetFont('times', '', 11.5);
    $pdf->SetXY($mx + 12, 101);
    $pdf->MultiCell($mw - 24, 5.2, $sentence, 0, 'C');

    // Description
    if (!empty($data['description'])) {
      $pdf->SetFont('times', 'I', 10.5);
      $pdf->SetTextColor(70, 70, 70);
      $pdf->SetXY($mx + 16, 116);
      $pdf->MultiCell($mw - 32, 5, $data['description'], 0, 'C');
    }

    // ── BOTTOM BAND: signature | seal | ISO badges | date | QR ──
    $bandY = 168;

    // Signature (left)
    $sigX = $mx + 4; $sigW = 46;
    $pdf->SetDrawColor(60, 60, 60);
    $pdf->SetLineWidth(0.3);
    $pdf->Line($sigX, $bandY, $sigX + $sigW, $bandY);
    $pdf->SetFont('helvetica', 'B', 9.5);
    $pdf->SetTextColor($ink[0], $ink[1], $ink[2]);
    $pdf->SetXY($sigX, $bandY + 1);
    $pdf->Cell($sigW, 4, 'DEEPU SOMAN', 0, 1, 'C');
    $pdf->SetFont('helvetica', '', 7.5);
    $pdf->SetTextColor($muteC[0], $muteC[1], $muteC[2]);
    $pdf->SetXY($sigX, $bandY + 5);
    $pdf->Cell($sigW, 3.5, 'Managing Partner', 0, 1, 'C');
    $pdf->SetXY($sigX, $bandY + 8.5);
    $pdf->Cell($sigW, 3.5, 'ExpertAims Consultancy LLP', 0, 1, 'C');

    // Company seal (centre) — drawn stamp
    $sealX = $mx + 78; $sealCY = $bandY + 2;
    $pdf->SetDrawColor(40, 90, 170);
    $pdf->SetLineWidth(0.6);
    $pdf->Circle($sealX, $sealCY, 12, 0, 360, 'D');
    $pdf->SetLineWidth(0.3);
    $pdf->Circle($sealX, $sealCY, 9.5, 0, 360, 'D');
    $pdf->SetTextColor(40, 90, 170);
    $pdf->SetFont('helvetica', 'B', 5.5);
    $pdf->SetXY($sealX - 12, $sealCY - 11);
    $pdf->Cell(24, 3, 'EXPERTAIMS CONSULTANCY', 0, 0, 'C');
    $pdf->SetFont('helvetica', 'B', 6.5);
    $pdf->SetXY($sealX - 12, $sealCY - 1.5);
    $pdf->Cell(24, 3, (new DateTime($data['issued_date']))->format('d-m-Y'), 0, 0, 'C');
    $pdf->SetFont('helvetica', '', 5);
    $pdf->SetXY($sealX - 12, $sealCY + 6.5);
    $pdf->Cell(24, 3, 'L L P', 0, 0, 'C');

    // ISO badges (three gold rings)
    $isos = ['ISO 9001', 'ISO 20000-1', 'ISO 27001'];
    $isoX = $mx + 108;
    foreach ($isos as $iso) {
      $pdf->SetDrawColor($gold[0], $gold[1], $gold[2]);
      $pdf->SetLineWidth(0.5);
      $pdf->Circle($isoX, $sealCY, 8, 0, 360, 'D');
      $pdf->SetFillColor(20, 20, 20);
      $pdf->Circle($isoX, $sealCY, 6.2, 0, 360, 'F');
      $pdf->SetTextColor($gold[0], $gold[1], $gold[2]);
      $pdf->SetFont('helvetica', 'B', 5);
      $pdf->SetXY($isoX - 7, $sealCY - 1.5);
      $pdf->Cell(14, 3, $iso, 0, 0, 'C');
      $isoX += 19;
    }

    // Date of issue
    $issue_date = format_date_for_certificate($data['issued_date']);
    $diX = $mx + 108; $diW = 55;
    $pdf->SetTextColor($ink[0], $ink[1], $ink[2]);
    $pdf->SetFont('helvetica', 'B', 13);
    $pdf->SetXY($diX, $bandY + 16);
    $pdf->Cell($diW, 6, $issue_date, 0, 1, 'C');
    $pdf->SetDrawColor($red[0], $red[1], $red[2]);
    $pdf->SetLineWidth(0.4);
    $pdf->Line($diX + 6, $bandY + 23, $diX + $diW - 6, $bandY + 23);
    $pdf->SetFont('helvetica', 'B', 7.5);
    $pdf->SetTextColor($red[0], $red[1], $red[2]);
    $pdf->SetXY($diX, $bandY + 23.5);
    $pdf->Cell($diW, 4, 'D A T E   O F   I S S U E', 0, 1, 'C');

    // QR code (far right)
    $qr_url = generate_qr_url($data['cert_id']);
    $qrX = 297 - 10 - 26; $qrY = $bandY - 4;
    $style = ['border' => false, 'padding' => 0, 'fgcolor' => [0, 0, 0], 'bgcolor' => false];
    $pdf->write2DBarcode($qr_url, 'QRCODE,M', $qrX, $qrY, 26, 26, $style, 'N');
    $pdf->SetTextColor($ink[0], $ink[1], $ink[2]);
    $pdf->SetFont('helvetica', 'B', 7);
    $pdf->SetXY($qrX - 4, $qrY + 27);
    $pdf->Cell(34, 3.2, 'Scan to Verify', 0, 1, 'C');
    $pdf->SetFont('helvetica', '', 6);
    $pdf->SetTextColor($muteC[0], $muteC[1], $muteC[2]);
    $pdf->SetXY($qrX - 4, $qrY + 30);
    $pdf->Cell(34, 3, 'expertaims.com/verify', 0, 1, 'C');

    // ── FOOTER BAR (offices) ──
    $fY = 200;
    $pdf->SetFillColor($navy[0], $navy[1], $navy[2]);
    $pdf->Rect(0, $fY, 297, 10, 'F');
    $pdf->SetFillColor($red[0], $red[1], $red[2]);
    $pdf->Rect(0, $fY, 297, 0.8, 'F');
    $pdf->SetTextColor(235, 235, 240);
    $pdf->SetFont('helvetica', '', 6.5);
    $footer_text = 'INDIA  ·  Thiruvananthapuram, Kerala      KUWAIT  ·  Kuwait City      MALAYSIA  ·  Petaling Jaya, Selangor      www.expertaims.com   ·   info@expertaims.com';
    $pdf->SetXY($sbW, $fY + 3);
    $pdf->Cell(297 - $sbW - 4, 4, $footer_text, 0, 0, 'C');

    // Output PDF
    $pdf->Output($pdf_path, 'F');

    return [
      'success' => true,
      'path' => $pdf_path,
      'filename' => basename($pdf_path),
      'message' => 'Certificate PDF generated successfully'
    ];

  } catch (\Throwable $e) {
    return [
      'success' => false,
      'message' => 'PDF generation error: ' . $e->getMessage()
    ];
  }
}

function generate_certificate_html($data, $pdf_path){
  /**
   * Fallback: Generate HTML certificate for browser print-to-PDF
   * Returns path to HTML file instead of PDF
   * Admins can save via browser's print dialog
   */

  $html_path = preg_replace('/\.pdf$/', '.html', $pdf_path);

  $qr_url = generate_qr_url($data['cert_id']);
  $qr_encoded = urlencode($qr_url);
  $qr_img_url = 'https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=' . $qr_encoded;

  $start_date = format_date_for_certificate($data['start_date']);
  $end_date = format_date_for_certificate($data['end_date']);
  $issue_date = format_date_for_certificate($data['issued_date']);

  $html = <<<HTML
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Certificate - {$data['internee_name']}</title>
  <style>
    * { margin: 0; padding: 0; }
    @page { size: A4; margin: 0; }
    @media print {
      body { margin: 0; padding: 0; }
      .certificate { page-break-after: always; }
    }
    body {
      font-family: 'Times New Roman', Times, serif;
      background: #f5f5f5;
      padding: 20px;
    }
    .certificate {
      width: 210mm;
      height: 297mm;
      background: white;
      margin: 0 auto 20px;
      display: grid;
      grid-template-columns: 50mm 1fr;
      position: relative;
      box-shadow: 0 2px 10px rgba(0,0,0,0.1);
      overflow: hidden;
    }
    .sidebar {
      background: #1A1F2E;
      color: white;
      padding: 30mm 5mm;
      text-align: center;
      font-size: 11px;
      display: flex;
      flex-direction: column;
      justify-content: space-around;
    }
    .sidebar-section {
      margin: 20px 0;
    }
    .sidebar-title {
      font-weight: bold;
      font-size: 12px;
      margin-bottom: 5px;
    }
    .sidebar-text {
      font-size: 8px;
      line-height: 1.3;
    }
    .main-content {
      padding: 30mm 30px 50mm 30px;
      display: flex;
      flex-direction: column;
      justify-content: space-between;
      position: relative;
    }
    .cert-badge {
      position: absolute;
      top: 15mm;
      right: 20px;
      background: black;
      color: white;
      padding: 5px 10px;
      font-size: 10px;
      font-weight: bold;
    }
    .cert-title {
      text-align: center;
      color: #C41E3A;
      font-size: 36px;
      font-weight: bold;
      margin: 20px 0 10px;
      letter-spacing: 2px;
    }
    .cert-subtitle {
      text-align: center;
      color: #999;
      font-size: 12px;
      margin-bottom: 20px;
    }
    .cert-name {
      text-align: center;
      color: #C41E3A;
      font-size: 44px;
      font-style: italic;
      margin: 20px 0 20px;
      font-family: 'Brush Script MT', cursive;
    }
    .cert-education {
      text-align: center;
      font-size: 11px;
      margin-bottom: 5px;
    }
    .cert-body {
      text-align: center;
      font-size: 10px;
      font-style: italic;
      line-height: 1.6;
      margin: 20px 0;
    }
    .cert-description {
      text-align: center;
      font-size: 10px;
      font-style: italic;
      line-height: 1.5;
      margin: 20px 0;
      color: #333;
    }
    .cert-footer {
      display: grid;
      grid-template-columns: 1fr 1fr 1fr;
      gap: 20px;
      margin-top: 40px;
      text-align: center;
    }
    .signature-area {
      text-align: center;
    }
    .signature-line {
      border-top: 1px solid #333;
      margin-bottom: 5px;
      height: 30px;
    }
    .signature-name {
      font-size: 9px;
      font-weight: bold;
    }
    .issue-date {
      text-align: center;
    }
    .issue-date-big {
      font-size: 14px;
      font-weight: bold;
      margin-bottom: 5px;
    }
    .issue-date-label {
      font-size: 8px;
      font-weight: bold;
    }
    .qr-area {
      text-align: center;
    }
    .qr-code {
      width: 80px;
      height: 80px;
      margin: 0 auto 5px;
    }
    .qr-text {
      font-size: 7px;
      color: #999;
      line-height: 1.3;
    }
    .bottom-bar {
      position: absolute;
      bottom: 0;
      left: 0;
      right: 0;
      background: #C41E3A;
      color: white;
      padding: 5px 10px;
      font-size: 8px;
      text-align: center;
      height: 15mm;
      display: flex;
      align-items: center;
      justify-content: center;
    }
    .print-note {
      background: #fff3cd;
      border: 1px solid #ffc107;
      padding: 15px;
      margin-bottom: 20px;
      border-radius: 4px;
      font-family: Arial, sans-serif;
    }
    .print-note b { font-weight: bold; }
  </style>
</head>
<body>
  <div class="print-note">
    <b>Print Instructions:</b> Use your browser's Print dialog (Ctrl+P or Cmd+P) →
    Save as PDF. Ensure margins are set to "None" and "Background graphics" is enabled for best results.
  </div>

  <div class="certificate">
    <div class="sidebar">
      <div>
        <div style="font-size: 16px; font-weight: bold; margin-bottom: 10px;">EXPERTAIMS</div>
        <div style="font-size: 10px; color: #ccc;">SINCE 2012</div>
      </div>

      <div class="sidebar-section">
        <div class="sidebar-title">●  INNOVATE</div>
        <div class="sidebar-text">We imagine better solutions</div>
      </div>

      <div class="sidebar-section">
        <div class="sidebar-title">●  COLLABORATE</div>
        <div class="sidebar-text">We work together for greater impact</div>
      </div>

      <div class="sidebar-section">
        <div class="sidebar-title">●  DELIVER</div>
        <div class="sidebar-text">We build with excellence</div>
      </div>

      <div class="sidebar-section">
        <div class="sidebar-title">●  EMPOWER</div>
        <div class="sidebar-text">We enable growth and transformation</div>
      </div>
    </div>

    <div class="main-content">
      <div class="cert-badge">Certificate ID: {$data['cert_id']}</div>

      <div class="cert-title">INTERNSHIP CERTIFICATE</div>

      <div class="cert-subtitle">THIS IS TO CERTIFY THAT</div>

      <div class="cert-name">{$data['internee_name']}</div>

      <div>
        <div class="cert-education">{$data['education']}</div>
        <div class="cert-education">{$data['institution']}</div>
      </div>

      <div class="cert-body">
        has successfully completed her {$data['role']} at {$data['company']} — {$data['location']} during the period {$start_date} to {$end_date}.
      </div>

      <div class="cert-description">
        {$data['description']}
      </div>

      <div class="cert-footer">
        <div class="signature-area">
          <div class="signature-line"></div>
          <div class="signature-name">Deepu Soman</div>
          <div style="font-size: 8px;">Managing Partner</div>
        </div>

        <div class="issue-date">
          <div class="issue-date-big">{$issue_date}</div>
          <div class="issue-date-label">DATE OF ISSUE</div>
        </div>

        <div class="qr-area">
          <img src="{$qr_img_url}" alt="QR Code" class="qr-code">
          <div class="qr-text">
            Scan to Verify<br>
            expertaims.com/verify
          </div>
        </div>
      </div>

      <div class="bottom-bar">
        ExpertAims Consultancy LLP | Kuwait | India | Malaysia | www.expertaims.com
      </div>
    </div>
  </div>
</body>
</html>
HTML;

  file_put_contents($html_path, $html);

  return [
    'success' => true,
    'path' => $html_path,
    'filename' => basename($html_path),
    'message' => 'Certificate HTML generated. Open in browser and print to PDF for best results.'
  ];
}

function save_certificate_to_database($data, $pdf_path){
  /**
   * Saves certificate to database
   *
   * Expected $data keys (same as for PDF generation)
   * Returns: ['success' => bool, 'cert_id' => string, 'message' => string]
   */

  $data = array_merge([
    'email' => null,
    'status' => 'published'
  ], $data);

  // Sanitize inputs
  $cert_id = mb_substr(trim((string) ($data['cert_id'] ?? '')), 0, 50);
  $name = mb_substr(trim((string) ($data['internee_name'] ?? '')), 0, 160);
  $email = mb_substr(trim((string) ($data['email'] ?? '')), 0, 190);
  $education = mb_substr(trim((string) ($data['education'] ?? '')), 0, 255);
  $institution = mb_substr(trim((string) ($data['institution'] ?? '')), 0, 255);
  $country = mb_substr(trim((string) ($data['country'] ?? '')), 0, 120);
  $role = mb_substr(trim((string) ($data['role'] ?? '')), 0, 160);
  $company = mb_substr(trim((string) ($data['company'] ?? 'ExpertAims Consultancy LLP')), 0, 255);
  $location = mb_substr(trim((string) ($data['location'] ?? '')), 0, 160);
  $description = trim((string) ($data['description'] ?? ''));
  $status = in_array($data['status'], ['published', 'draft']) ? $data['status'] : 'published';
  $start_date = trim((string) ($data['start_date'] ?? ''));
  $end_date = trim((string) ($data['end_date'] ?? ''));
  $issued_date = trim((string) ($data['issued_date'] ?? date('Y-m-d')));
  $pdf_file_path = basename($pdf_path);

  // Validate dates
  try {
    new DateTime($start_date);
    new DateTime($end_date);
    new DateTime($issued_date);
  } catch (\Throwable $e) {
    return ['success' => false, 'message' => 'Invalid date format'];
  }

  try {
    $st = db()->prepare('
      INSERT INTO certificates
      (certificate_id, internee_name, email, education, institution, country, role, company, location, start_date, end_date, issued_date, description, pdf_file_path, status)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ');

    $st->execute([
      $cert_id, $name, $email, $education, $institution, $country, $role, $company, $location,
      $start_date, $end_date, $issued_date, $description, $pdf_file_path, $status
    ]);

    return [
      'success' => true,
      'cert_id' => $cert_id,
      'message' => 'Certificate saved to database successfully'
    ];

  } catch (\Throwable $e) {
    return [
      'success' => false,
      'message' => 'Database error: ' . $e->getMessage()
    ];
  }
}
