<?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");
    }

    // Authorised signatory (director who signs) — varies per certificate
    if (!in_array('signatory_name', $column_names, true)) {
      db()->exec("ALTER TABLE certificates ADD COLUMN signatory_name VARCHAR(160) DEFAULT NULL AFTER description");
    }
    if (!in_array('signatory_title', $column_names, true)) {
      db()->exec("ALTER TABLE certificates ADD COLUMN signatory_title VARCHAR(160) DEFAULT NULL AFTER signatory_name");
    }
  } 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'] ?? '')),
    'signatory_name' => trim((string) ($data['signatory_name'] ?? '')) ?: 'Deepu Soman',
    'signatory_title' => trim((string) ($data['signatory_title'] ?? '')) ?: 'Managing Partner',
    'signature_image' => trim((string) ($data['signature_image'] ?? '')),
    'cert_id' => trim((string) ($data['cert_id'] ?? ''))
  ];
  // Resolve a stored signature path (relative to project root) to an absolute file
  if ($data['signature_image'] !== '' && !preg_match('#^/#', $data['signature_image'])) {
    $data['signature_image'] = dirname(__DIR__) . '/' . ltrim($data['signature_image'], '/');
  }

  // 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);
    $pdf->SetCreator('EAC CMS');
    $pdf->SetAuthor('ExpertAims Consultancy');
    $pdf->SetTitle('Certificate - ' . $data['internee_name']);
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);
    $pdf->SetMargins(0, 0, 0);
    $pdf->SetAutoPageBreak(false, 0);
    $pdf->AddPage();

    // Fonts (bundled): cinzel = display serif caps, greatvibes = script name,
    // ebgaramond = body serif. Fall back to core fonts if a face isn't installed.
    $fontDir = dirname(__DIR__) . '/vendor/tecnickcom/tcpdf/fonts/';
    $F_HEAD = is_file($fontDir . 'cinzel.php')     ? 'cinzel'     : 'times';
    $F_NAME = is_file($fontDir . 'greatvibes.php') ? 'greatvibes' : 'times';
    $F_BODY = is_file($fontDir . 'ebgaramond.php') ? 'ebgaramond' : 'times';

    // Brand assets
    $brand      = dirname(__DIR__) . '/assets/brand/';
    $logo_full  = $brand . 'logo-full.png';       // colour wordmark (white bg)
    $logo_e     = $brand . 'logo-e.png';          // e-mark WITH red dot (seal)
    $logo_e_lt  = $brand . 'logo-e-dark.png';     // light e-mark (dark sidebar)

    // Palette
    $red  = [196, 30, 58];   $redHex = '#C41E3A';
    $navy = [22, 27, 42];
    $gold = [190, 150, 70];
    $ink  = [30, 30, 30];    $inkHex = '#1E1E1E';
    $mute = [120, 120, 120];

    // ══ LEFT SIDEBAR (0–58mm) ══
    $sbW = 58;
    $pdf->SetFillColor($navy[0], $navy[1], $navy[2]);
    $pdf->Rect(0, 0, $sbW, 210, 'F');
    // red diagonal accent along the sidebar seam
    $pdf->SetFillColor($red[0], $red[1], $red[2]);
    $pdf->Polygon([$sbW - 6, 0, $sbW, 0, $sbW, 210, $sbW - 2, 210], 'F');
    $pdf->SetFillColor(150, 22, 40);
    $pdf->Polygon([$sbW - 9, 0, $sbW - 6, 0, $sbW - 2, 210, $sbW - 5, 210], 'F');

    // circular emblem badge with the light e-mark
    $emCX = 29; $emCY = 27;
    $pdf->SetDrawColor($red[0], $red[1], $red[2]); $pdf->SetLineWidth(0.7);
    $pdf->Circle($emCX, $emCY, 14, 0, 360, 'D');
    $pdf->SetLineWidth(0.3); $pdf->SetDrawColor(90, 96, 112);
    $pdf->Circle($emCX, $emCY, 15.5, 0, 360, 'D');
    if (is_file($logo_e_lt)) @$pdf->Image($logo_e_lt, $emCX - 6, $emCY - 10, 12, 0, '', '', '', false, 300, '', false, false, 0);
    $pdf->SetTextColor(255, 255, 255);
    $pdf->SetFont('helvetica', 'B', 12);
    $pdf->SetXY(2, 45); $pdf->Cell($sbW - 4, 5, 'EXPERTAIMS', 0, 1, 'C');
    $pdf->SetFont('helvetica', '', 7);
    $pdf->SetTextColor(180, 182, 192);
    $pdf->SetXY(2, 50.5); $pdf->Cell($sbW - 4, 3, chr(183) . '  S I N C E   2 0 1 2  ' . chr(183), 0, 1, 'C');

    // value pillars, each with a red icon disc
    $values = [
      ['INNOVATE', 'We imagine better solutions', 'bulb'],
      ['COLLABORATE', 'We work together for greater impact', 'people'],
      ['DELIVER', 'We build with excellence', 'target'],
      ['EMPOWER', 'We enable growth and transformation', 'shield'],
    ];
    $vy = 74;
    foreach ($values as $v) {
      $icx = 11; $icy = $vy + 3;
      $pdf->SetFillColor($red[0], $red[1], $red[2]);
      $pdf->Circle($icx, $icy, 4.4, 0, 360, 'F');
      $pdf->SetDrawColor(255, 255, 255); $pdf->SetLineWidth(0.35); $pdf->SetFillColor(255, 255, 255);
      // tiny white glyph inside the disc
      if ($v[2] === 'bulb') {
        $pdf->Circle($icx, $icy - 0.6, 1.7, 0, 360, 'D');
        $pdf->Line($icx - 1, $icy + 2.1, $icx + 1, $icy + 2.1);
      } elseif ($v[2] === 'people') {
        $pdf->Circle($icx - 1.3, $icy - 0.8, 1.0, 0, 360, 'DF');
        $pdf->Circle($icx + 1.3, $icy - 0.8, 1.0, 0, 360, 'DF');
        $pdf->Line($icx - 2.6, $icy + 2, $icx - 0.2, $icy + 0.4);
        $pdf->Line($icx + 2.6, $icy + 2, $icx + 0.2, $icy + 0.4);
      } elseif ($v[2] === 'target') {
        $pdf->Circle($icx, $icy, 2.1, 0, 360, 'D');
        $pdf->Circle($icx, $icy, 0.7, 0, 360, 'F');
      } else { // shield
        $pdf->Polygon([$icx, $icy - 2.3, $icx + 2, $icy - 1.3, $icx + 2, $icy + 0.6, $icx, $icy + 2.4, $icx - 2, $icy + 0.6, $icx - 2, $icy - 1.3], 'D');
      }
      $pdf->SetTextColor(255, 255, 255);
      $pdf->SetFont('helvetica', 'B', 9.5);
      $pdf->SetXY(18, $vy - 1.5); $pdf->Cell($sbW - 20, 5, $v[0], 0, 1, 'L');
      $pdf->SetTextColor(165, 168, 180);
      $pdf->SetFont('helvetica', '', 7);
      $pdf->SetXY(18, $vy + 3.2); $pdf->MultiCell($sbW - 21, 3, $v[1], 0, 'L');
      $vy += 30;
    }

    // ══ MAIN AREA (x: 58–297) ══
    $mx = 67;                 // content left edge
    $rEdge = 289;             // content right edge
    $mw = $rEdge - $mx;

    // top-right red corner triangle
    $pdf->SetFillColor($red[0], $red[1], $red[2]);
    $pdf->Polygon([297, 0, 297, 40, 258, 0], 'F');
    $pdf->SetFillColor(150, 22, 40);
    $pdf->Polygon([297, 0, 297, 22, 276, 0], 'F');

    // full-colour wordmark, top-left
    if (is_file($logo_full)) @$pdf->Image($logo_full, $mx, 13, 80, 0, '', '', '', false, 300, '', false, false, 0);

    // Certificate ID pill, top-right (below the corner)
    $pillW = 66; $pillX = $rEdge - $pillW; $pillY = 12;
    $pdf->SetFillColor(18, 18, 20);
    $pdf->RoundedRect($pillX, $pillY, $pillW, 8, 1.6, '1111', 'F');
    $pdf->SetTextColor(255, 255, 255);
    $pdf->SetFont('helvetica', '', 8.4);
    $pdf->SetXY($pillX, $pillY + 0.5);
    $pdf->Cell($pillW, 7, 'Certificate ID: ' . $data['cert_id'], 0, 0, 'C');

    // "BUILDING SOLUTIONS POWERING FUTURES" ribbon badge (right)
    $rbX = 248; $rbY = 30; $rbW = 41; $rbH = 20;
    $pdf->SetFillColor($red[0], $red[1], $red[2]);
    $pdf->RoundedRect($rbX, $rbY, $rbW, $rbH, 1.2, '1111', 'F');
    $pdf->Polygon([$rbX, $rbY + $rbH, $rbX + 6, $rbY + $rbH, $rbX, $rbY + $rbH + 4], 'F');
    $pdf->Polygon([$rbX + $rbW, $rbY + $rbH, $rbX + $rbW - 6, $rbY + $rbH, $rbX + $rbW, $rbY + $rbH + 4], 'F');
    $pdf->SetTextColor(255, 255, 255);
    $pdf->SetFont($F_HEAD, '', 8.2);
    $pdf->SetXY($rbX, $rbY + 2.6);  $pdf->Cell($rbW, 4, 'BUILDING', 0, 0, 'C');
    $pdf->SetXY($rbX, $rbY + 6.6);  $pdf->Cell($rbW, 4, 'SOLUTIONS POWERING', 0, 0, 'C');
    $pdf->SetXY($rbX, $rbY + 10.6); $pdf->Cell($rbW, 4, 'FUTURES', 0, 0, 'C');
    $pdf->SetFillColor(240, 200, 90); $pdf->SetDrawColor(240, 200, 90); $pdf->SetLineWidth(0.1);
    $pdf->StarPolygon($rbX + $rbW / 2, $rbY + 16.6, 1.9, 5, 2, 90, 'DF');

    // heading — two-tone, left aligned
    $hY = 46;
    $pdf->SetFont($F_HEAD, '', 27);
    $t1 = 'INTERNSHIP '; $t2 = 'CERTIFICATE';
    $w1 = $pdf->GetStringWidth($t1);
    $pdf->SetTextColor($ink[0], $ink[1], $ink[2]);
    $pdf->SetXY($mx, $hY); $pdf->Cell($w1, 12, $t1, 0, 0, 'L');
    $pdf->SetTextColor($red[0], $red[1], $red[2]);
    $pdf->SetXY($mx + $w1, $hY); $pdf->Cell(120, 12, $t2, 0, 0, 'L');

    // diamond divider under heading
    $dy = 62;
    $pdf->SetDrawColor($red[0], $red[1], $red[2]); $pdf->SetLineWidth(0.5);
    $pdf->Line($mx, $dy, $mx + 26, $dy);
    $pdf->SetFillColor($red[0], $red[1], $red[2]);
    $pdf->Polygon([$mx + 30, $dy - 1.6, $mx + 32.6, $dy, $mx + 30, $dy + 1.6, $mx + 27.4, $dy], 'F');

    // "THIS IS TO CERTIFY THAT"
    $pdf->SetFont('helvetica', '', 9);
    $pdf->SetTextColor($mute[0], $mute[1], $mute[2]);
    $pdf->SetXY($mx, 65.5);
    $pdf->Cell($mw, 5, 'T H I S   I S   T O   C E R T I F Y   T H A T', 0, 0, 'L');

    // internee name — script font, red, left aligned
    $pdf->SetFont($F_NAME, '', 46);
    $pdf->SetTextColor($red[0], $red[1], $red[2]);
    $pdf->SetXY($mx - 1, 70);
    $pdf->Cell($mw, 18, $data['internee_name'], 0, 0, 'L');
    $nameW = min($pdf->GetStringWidth($data['internee_name']) + 6, $mw);
    // gold underline under the name
    $pdf->SetDrawColor($gold[0], $gold[1], $gold[2]); $pdf->SetLineWidth(0.4);
    $pdf->Line($mx, 90.5, $mx + max($nameW, 60), 90.5);

    // education + institution/country (body serif, left aligned)
    $institution_line = $data['institution'];
    if (!empty($data['country'])) $institution_line .= ($institution_line !== '' ? ', ' : '') . $data['country'];
    $pdf->SetTextColor($ink[0], $ink[1], $ink[2]);
    $pdf->SetFont($F_BODY, 'B', 13);
    $pdf->SetXY($mx, 93); $pdf->Cell($mw, 6, $data['education'], 0, 0, 'L');
    $pdf->SetFont($F_BODY, '', 12.5);
    $pdf->SetXY($mx, 99.5); $pdf->Cell($mw, 6, $institution_line, 0, 0, 'L');

    // completion sentence with coloured keywords (rich text)
    $start = format_date_for_certificate($data['start_date']);
    $end   = format_date_for_certificate($data['end_date']);
    $em = " \xe2\x80\x94 ";
    $sentence = 'has successfully completed <font color="' . $redHex . '"><b>' . htmlspecialchars($data['role'], ENT_QUOTES)
      . '</b></font> at ' . htmlspecialchars($data['company'], ENT_QUOTES) . $em
      . '<font color="' . $redHex . '"><b>' . htmlspecialchars($data['location'], ENT_QUOTES) . '</b></font> during the period '
      . '<b>' . $start . '</b> to <b>' . $end . '</b>.';
    $pdf->SetFont($F_BODY, '', 12);
    $pdf->SetTextColor($ink[0], $ink[1], $ink[2]);
    $pdf->writeHTMLCell($mw - 6, 0, $mx, 109, $sentence, 0, 1, false, true, 'L', true);

    // description (italic)
    if (!empty($data['description'])) {
      $pdf->SetFont($F_BODY, 'I', 11);
      $pdf->SetTextColor(70, 70, 70);
      $pdf->writeHTMLCell($mw - 6, 0, $mx, 123, '<i>' . htmlspecialchars($data['description'], ENT_QUOTES) . '</i>', 0, 1, false, true, 'L', true);
    }

    // ══ BOTTOM BAND ══
    // signature block (left)
    $sigX = $mx; $sigW = 52; $sigLineY = 170;
    $sig = (string) ($data['signature_image'] ?? '');
    if ($sig !== '' && is_file($sig)) {
      @$pdf->Image($sig, $sigX + 6, $sigLineY - 16, 40, 15, '', '', '', false, 300, '', false, false, 0);
    }
    $pdf->SetDrawColor(50, 50, 50); $pdf->SetLineWidth(0.3);
    $pdf->Line($sigX, $sigLineY, $sigX + $sigW, $sigLineY);
    $pdf->SetTextColor($ink[0], $ink[1], $ink[2]);
    $pdf->SetFont('helvetica', 'B', 10);
    $pdf->SetXY($sigX, $sigLineY + 1.5); $pdf->Cell($sigW, 4.5, strtoupper($data['signatory_name']), 0, 1, 'C');
    $pdf->SetTextColor($mute[0], $mute[1], $mute[2]);
    $pdf->SetFont('helvetica', '', 8);
    $pdf->SetXY($sigX, $sigLineY + 6);   $pdf->Cell($sigW, 4, $data['signatory_title'], 0, 1, 'C');
    $pdf->SetXY($sigX, $sigLineY + 9.5); $pdf->Cell($sigW, 4, $data['company'], 0, 1, 'C');

    // company seal (built from the red-dot e-mark) — round blue stamp
    $sX = $mx + 74; $sY = 166; $sR = 13;
    $pdf->SetDrawColor(38, 84, 165); $pdf->SetLineWidth(0.7);
    $pdf->Circle($sX, $sY, $sR, 0, 360, 'D');
    $pdf->SetLineWidth(0.3);
    $pdf->Circle($sX, $sY, $sR - 2.4, 0, 360, 'D');
    // curved-ish label (top + bottom) approximated with straight text
    $pdf->SetTextColor(38, 84, 165);
    $pdf->SetFont('helvetica', 'B', 5.6);
    $pdf->SetXY($sX - $sR, $sY - $sR + 1.4); $pdf->Cell($sR * 2, 3, 'EXPERTAIMS CONSULTANCY', 0, 0, 'C');
    $pdf->SetXY($sX - $sR, $sY + $sR - 4.4); $pdf->Cell($sR * 2, 3, 'L L P', 0, 0, 'C');
    // issue date in the centre
    $pdf->SetFont('helvetica', 'B', 7);
    $pdf->SetXY($sX - $sR, $sY - 4.6); $pdf->Cell($sR * 2, 3, (new DateTime($data['issued_date']))->format('d-m-Y'), 0, 0, 'C');
    if (is_file($logo_e)) @$pdf->Image($logo_e, $sX - 4.5, $sY - 0.5, 9, 0, '', '', '', false, 300, '', false, false, 0);

    // ISO badges (three) — gold-ring black seals + labels
    $isos = [['9001', 'ISO 9001:2015', 'Quality Management'],
             ['20000-1', 'ISO/IEC 20000-1:2018', 'IT Service Management'],
             ['27001', 'ISO/IEC 27001:2022', 'Information Security']];
    $ibX = $mx + 104; $ibY = 163; $ibGap = 26;
    foreach ($isos as $iso) {
      $pdf->SetFillColor(24, 24, 26);
      $pdf->Circle($ibX, $ibY, 8.4, 0, 360, 'F');
      $pdf->SetDrawColor($gold[0], $gold[1], $gold[2]); $pdf->SetLineWidth(0.6);
      $pdf->Circle($ibX, $ibY, 8.4, 0, 360, 'D');
      $pdf->SetLineWidth(0.3);
      $pdf->Circle($ibX, $ibY, 6.6, 0, 360, 'D');
      $pdf->SetTextColor(235, 235, 235);
      $pdf->SetFont('helvetica', 'B', 4.6);
      $pdf->SetXY($ibX - 8, $ibY - 5.6); $pdf->Cell(16, 2.4, 'CERTIFIED', 0, 0, 'C');
      $pdf->SetTextColor($gold[0], $gold[1], $gold[2]);
      $pdf->SetFont('helvetica', 'B', 4.6);
      $pdf->SetXY($ibX - 8, $ibY - 3.2); $pdf->Cell(16, 2.4, 'ISO', 0, 0, 'C');
      $pdf->SetTextColor(255, 255, 255);
      $pdf->SetFont('helvetica', 'B', 6.4);
      $pdf->SetXY($ibX - 8, $ibY - 0.6); $pdf->Cell(16, 3, $iso[0], 0, 0, 'C');
      // labels below
      $pdf->SetTextColor($ink[0], $ink[1], $ink[2]);
      $pdf->SetFont('helvetica', 'B', 5.2);
      $pdf->SetXY($ibX - 12.5, $ibY + 9); $pdf->Cell(25, 2.6, $iso[1], 0, 0, 'C');
      $pdf->SetTextColor($mute[0], $mute[1], $mute[2]);
      $pdf->SetFont('helvetica', '', 5);
      $pdf->SetXY($ibX - 12.5, $ibY + 11.4); $pdf->Cell(25, 2.6, $iso[2], 0, 0, 'C');
      $ibX += $ibGap;
    }

    // date of issue (under the ISO row, centred on the middle badge)
    $issue_date = format_date_for_certificate($data['issued_date']);
    $diCx = $mx + 104 + $ibGap; $diW = 54;
    $pdf->SetTextColor($ink[0], $ink[1], $ink[2]);
    $pdf->SetFont('helvetica', 'B', 13);
    $pdf->SetXY($diCx - $diW / 2, 183); $pdf->Cell($diW, 6, $issue_date, 0, 0, 'C');
    $pdf->SetDrawColor($red[0], $red[1], $red[2]); $pdf->SetLineWidth(0.4);
    $pdf->Line($diCx - $diW / 2 + 5, 190, $diCx + $diW / 2 - 5, 190);
    $pdf->SetTextColor($red[0], $red[1], $red[2]);
    $pdf->SetFont('helvetica', 'B', 7.5);
    $pdf->SetXY($diCx - $diW / 2, 190.5); $pdf->Cell($diW, 4, 'D A T E   O F   I S S U E', 0, 0, 'C');

    // QR (far right)
    $qr_url = generate_qr_url($data['cert_id']);
    $qrX = $rEdge - 26; $qrY = 158;
    $pdf->write2DBarcode($qr_url, 'QRCODE,M', $qrX, $qrY, 26, 26, ['border' => false, 'padding' => 0, 'fgcolor' => [0, 0, 0], 'bgcolor' => false], 'N');
    $pdf->SetTextColor($ink[0], $ink[1], $ink[2]);
    $pdf->SetFont('helvetica', 'B', 7.2);
    $pdf->SetXY($qrX - 4, $qrY + 27); $pdf->Cell(34, 3.4, 'Scan to Verify', 0, 1, 'C');
    $pdf->SetFont('helvetica', '', 6.2);
    $pdf->SetTextColor($mute[0], $mute[1], $mute[2]);
    $pdf->SetXY($qrX - 4, $qrY + 30.4); $pdf->Cell(34, 3, 'expertaims.com/verify', 0, 1, 'C');

    // ══ FOOTER (full addresses) ══
    $fY = 196; $fH = 14;
    $pdf->SetFillColor($navy[0], $navy[1], $navy[2]);
    $pdf->Rect(0, $fY, 297, $fH, 'F');
    $pdf->SetFillColor($gold[0], $gold[1], $gold[2]);
    $pdf->Rect(0, $fY, 297, 0.9, 'F');
    $offices = [
      [6,  'INDIA',    "TC 14/651, Kalarickal, Mosque Lane, Kesavadasapuram,\nPattom P.O, Thiruvananthapuram \xe2\x80\x93 695004, Kerala."],
      [96, 'KUWAIT',   "Floor 19, Space Tower, Kuwait City.\n(In association with Mart Solution Consulting Co. W.L.L.)"],
      [163,'MALAYSIA', "Block A, 3A-02, Jalan Stadium SS 7/15, Kelana Jaya,\n47301 Petaling Jaya, Selangor."],
    ];
    foreach ($offices as $o) {
      $pdf->SetFillColor($red[0], $red[1], $red[2]);
      $pdf->Circle($o[0] + 1, $fY + 3.4, 1.1, 0, 360, 'F');
      $pdf->SetTextColor(245, 210, 120);
      $pdf->SetFont('helvetica', 'B', 6.6);
      $pdf->SetXY($o[0] + 3.5, $fY + 2); $pdf->Cell(40, 3, $o[1], 0, 0, 'L');
      $pdf->SetTextColor(210, 212, 220);
      $pdf->SetFont('helvetica', '', 5.2);
      $pdf->SetXY($o[0] + 3.5, $fY + 5.4); $pdf->MultiCell(60, 2.4, $o[2], 0, 'L');
    }
    // contact column
    $pdf->SetTextColor(220, 222, 230);
    $pdf->SetFont('helvetica', '', 5.6);
    $pdf->SetXY(232, $fY + 2.6);  $pdf->Cell(60, 2.6, 'www.expertaims.com', 0, 2, 'L');
    $pdf->SetXY(232, $fY + 5.8);  $pdf->Cell(60, 2.6, 'info@expertaims.com', 0, 2, 'L');
    $pdf->SetXY(232, $fY + 9);    $pdf->Cell(60, 2.6, '+91 471 291 0255  |  +965 9090 8625', 0, 0, 'L');

    // 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'] ?? ''));
  $signatory_name = mb_substr(trim((string) ($data['signatory_name'] ?? '')), 0, 160) ?: 'Deepu Soman';
  $signatory_title = mb_substr(trim((string) ($data['signatory_title'] ?? '')), 0, 160) ?: 'Managing Partner';
  $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, signatory_name, signatory_title, pdf_file_path, status)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ');

    $st->execute([
      $cert_id, $name, $email, $education, $institution, $country, $role, $company, $location,
      $start_date, $end_date, $issued_date, $description, $signatory_name, $signatory_title, $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()
    ];
  }
}
