40161-vm/includes/leads.php

<?php
declare(strict_types=1);

require_once __DIR__ . '/../db/config.php';

function e(?string $value): string
{
    return htmlspecialchars((string)$value, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}

function ensure_leads_table(): void
{
    $sql = "CREATE TABLE IF NOT EXISTS agency_leads (
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(120) NOT NULL,
        email VARCHAR(190) NOT NULL,
        phone VARCHAR(60) DEFAULT NULL,
        company VARCHAR(140) DEFAULT NULL,
        service VARCHAR(120) NOT NULL,
        budget VARCHAR(80) DEFAULT NULL,
        timeline VARCHAR(80) DEFAULT NULL,
        message TEXT NOT NULL,
        status VARCHAR(40) NOT NULL DEFAULT 'new',
        admin_notes TEXT DEFAULT NULL,
        source VARCHAR(120) DEFAULT 'website',
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_status_created (status, created_at),
        INDEX idx_email (email)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    db()->exec($sql);
}

function lead_statuses(): array
{
    return [
        'new' => 'New',
        'reviewing' => 'Reviewing',
        'contacted' => 'Contacted',
        'proposal' => 'Proposal sent',
        'won' => 'Won',
        'lost' => 'Lost',
    ];
}

function sanitize_text(string $value, int $maxLength): string
{
    $value = trim(preg_replace('/\s+/', ' ', $value) ?? '');
    if (strlen($value) > $maxLength) {
        $value = substr($value, 0, $maxLength);
    }
    return $value;
}

function validate_lead_payload(array $input): array
{
    $data = [
        'name' => sanitize_text((string)($input['name'] ?? ''), 120),
        'email' => sanitize_text((string)($input['email'] ?? ''), 190),
        'phone' => sanitize_text((string)($input['phone'] ?? ''), 60),
        'company' => sanitize_text((string)($input['company'] ?? ''), 140),
        'service' => sanitize_text((string)($input['service'] ?? ''), 120),
        'budget' => sanitize_text((string)($input['budget'] ?? ''), 80),
        'timeline' => sanitize_text((string)($input['timeline'] ?? ''), 80),
        'message' => trim((string)($input['message'] ?? '')),
        'source' => sanitize_text((string)($input['source'] ?? 'website'), 120),
    ];

    if (strlen($data['message']) > 3000) {
        $data['message'] = substr($data['message'], 0, 3000);
    }

    $errors = [];
    if ($data['name'] === '') {
        $errors['name'] = 'Please enter your name.';
    }
    if (!filter_var($data['email'], FILTER_VALIDATE_EMAIL)) {
        $errors['email'] = 'Please enter a valid email address.';
    }
    if ($data['service'] === '') {
        $errors['service'] = 'Please choose a service.';
    }
    if (strlen($data['message']) < 20) {
        $errors['message'] = 'Please share at least 20 characters about the project.';
    }

    return [$data, $errors];
}

function create_lead(array $data): int
{
    ensure_leads_table();
    $stmt = db()->prepare('INSERT INTO agency_leads (name, email, phone, company, service, budget, timeline, message, source) VALUES (:name, :email, :phone, :company, :service, :budget, :timeline, :message, :source)');
    $stmt->execute([
        ':name' => $data['name'],
        ':email' => $data['email'],
        ':phone' => $data['phone'] !== '' ? $data['phone'] : null,
        ':company' => $data['company'] !== '' ? $data['company'] : null,
        ':service' => $data['service'],
        ':budget' => $data['budget'] !== '' ? $data['budget'] : null,
        ':timeline' => $data['timeline'] !== '' ? $data['timeline'] : null,
        ':message' => $data['message'],
        ':source' => $data['source'],
    ]);
    return (int)db()->lastInsertId();
}

function list_leads(?string $status = null, int $limit = 100): array
{
    ensure_leads_table();
    $statuses = lead_statuses();
    if ($status && isset($statuses[$status])) {
        $stmt = db()->prepare('SELECT * FROM agency_leads WHERE status = :status ORDER BY created_at DESC LIMIT :limit');
        $stmt->bindValue(':status', $status, PDO::PARAM_STR);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }
    $stmt = db()->prepare('SELECT * FROM agency_leads ORDER BY created_at DESC LIMIT :limit');
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll();
}

function get_lead(int $id): ?array
{
    ensure_leads_table();
    $stmt = db()->prepare('SELECT * FROM agency_leads WHERE id = :id LIMIT 1');
    $stmt->bindValue(':id', $id, PDO::PARAM_INT);
    $stmt->execute();
    $lead = $stmt->fetch();
    return $lead ?: null;
}

function update_lead(int $id, string $status, string $notes): void
{
    $statuses = lead_statuses();
    if (!isset($statuses[$status])) {
        $status = 'new';
    }
    $notes = trim($notes);
    if (strlen($notes) > 3000) {
        $notes = substr($notes, 0, 3000);
    }
    ensure_leads_table();
    $stmt = db()->prepare('UPDATE agency_leads SET status = :status, admin_notes = :notes WHERE id = :id');
    $stmt->bindValue(':status', $status, PDO::PARAM_STR);
    $stmt->bindValue(':notes', $notes !== '' ? $notes : null, $notes !== '' ? PDO::PARAM_STR : PDO::PARAM_NULL);
    $stmt->bindValue(':id', $id, PDO::PARAM_INT);
    $stmt->execute();
}

function lead_counts(): array
{
    ensure_leads_table();
    $counts = array_fill_keys(array_keys(lead_statuses()), 0);
    $stmt = db()->query('SELECT status, COUNT(*) AS total FROM agency_leads GROUP BY status');
    foreach ($stmt->fetchAll() as $row) {
        if (isset($counts[$row['status']])) {
            $counts[$row['status']] = (int)$row['total'];
        }
    }
    return $counts;
}