Email Indexing & Ingest + AI Search & Chatbot (PHP)

Libraries: phpoffice/phpword for DOCX, league/commonmark for MD, smalot/pdfparser for PDF. Fallback to strip_tags for HTML.

composer require phpoffice/phpword league/commonmark smalot/pdfparser openai-php/client

Policy: keep facts; remove salutations/signatures; redact PII; neutral tone; produce 3–6 bullet summary + 1 paragraph narrative. Store both original and paraphrase.

<?php
use OpenAI as OpenAIClient;

$client = OpenAIClient::client(getenv('OPENAI_API_KEY'));

function paraphrase(string $text): string {
    global $client;
    $prompt = "Rewrite the content into (1) 3-6 bullets, (2) one neutral paragraph. Remove salutations/signatures. Redact names/emails/phones.";
    $r = $client->chat()->create([
        'model' => 'gpt-4o-mini',
        'messages' => [
            ['role' => 'system', 'content' => 'You paraphrase EFMSA internal documents.'],
            ['role' => 'user', 'content' => $prompt . "\n\nCONTENT:\n" . $text]
        ],
        'temperature' => 0.2
    ]);
    return $r->choices[0]->message->content;
}

function embed(string $text): array {
    global $client;
    $e = $client->embeddings()->create([
        'model' => 'text-embedding-3-large',
        'input' => $text
    ]);
    return $e->data[0]->embedding; // array of floats
}

We keep a wiki_docs table with FULLTEXT over (title, body). Optional: store embedding JSON for future semantic search.

CREATE TABLE IF NOT EXISTS wiki_docs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  body LONGTEXT NOT NULL,
  tags VARCHAR(255) DEFAULT NULL,
  source ENUM('email','upload','wiki') NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  hidden TINYINT(1) NOT NULL DEFAULT 0,
  embedding JSON NULL
) ENGINE=InnoDB;

CREATE FULLTEXT INDEX ft_title_body ON wiki_docs (title, body);
CREATE INDEX idx_created ON wiki_docs (created_at);
CREATE INDEX idx_hidden ON wiki_docs (hidden);

<?php
$pdo = new PDO('mysql:host=localhost;dbname=efmsa;charset=utf8mb4','user','pass', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

function upsert_doc(PDO $pdo, string $title, string $body, string $tags, string $source, ?array $embedding = null) {
    $stmt = $pdo->prepare("INSERT INTO wiki_docs (title, body, tags, source, embedding) VALUES (?,?,?,?,?)");
    $stmt->execute([$title, $body, $tags, $source, $embedding ? json_encode($embedding) : null]);
    return $pdo->lastInsertId();
}

function search_docs(PDO $pdo, string $q): array {
    // Prefer FULLTEXT if terms look suitable; fallback to LIKE
    $stmt = $pdo->prepare("SELECT id, title, LEFT(body, 400) AS snippet, tags, created_at FROM wiki_docs
                           WHERE hidden = 0 AND MATCH(title, body) AGAINST(:q IN NATURAL LANGUAGE MODE)
                           ORDER BY MATCH(title, body) AGAINST(:q IN NATURAL LANGUAGE MODE) DESC LIMIT 20");
    $stmt->execute([':q' => $q]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if (!$rows) {
        $stmt2 = $pdo->prepare("SELECT id, title, LEFT(body, 400) AS snippet, tags, created_at FROM wiki_docs
                                WHERE hidden = 0 AND (title LIKE :l OR body LIKE :l) ORDER BY created_at DESC LIMIT 20");
        $stmt2->execute([':l' => '%'.$q.'%']);
        return $stmt2->fetchAll(PDO::FETCH_ASSOC);
    }
    return $rows;
}

For now we build answers from top FULLTEXT results. (Optional upgrade: vector DB for better semantic recall.) The bot cites titles and links to the source pages.

<?php
require __DIR__.'/vendor/autoload.php';
use OpenAI as OpenAIClient;

$pdo = new PDO('mysql:host=localhost;dbname=efmsa;charset=utf8mb4','user','pass', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$oi  = OpenAIClient::client(getenv('OPENAI_API_KEY'));

$q = $_POST['q'] ?? '';
$rows = search_docs($pdo, $q);

$ctx = "";
foreach ($rows as $r) {
  $ctx .= "Title: {$r['title']}\nSnippet: {$r['snippet']}\nLink: /wiki.php?id={$r['id']}\n--\n";
  if (strlen($ctx) > 6000) break;
}

$system = 'You answer questions for EFMSA using ONLY the provided context. Cite sources as [Title](/wiki.php?id=ID). If not supported, say you are unsure.';
$res = $oi->chat()->create([
  'model' => 'gpt-4o-mini',
  'messages' => [
    ['role' => 'system', 'content' => $system],
    ['role' => 'user', 'content' => "Question: $q\n\nContext:\n$ctx"]
  ],
  'temperature' => 0.2
]);

echo nl2br(htmlentities($res->choices[0]->message->content));

• Every 12 hours: discover new emails/files, (re)index changed items.
• Hourly: quick sweep for urgent uploads.
• Manual: “Index now” button queues an immediate run with CSRF protection.