fitme3070.pt/config/functions_events.php

<?php
// ─── Events CRUD helpers ───────────────────────────────────────────────────────
// Time-limited events that drive a homepage banner + a branded landing page at
// /evento/<slug>. Soft-delete via deleted_at (same shape as modalidades). An
// event is publicly visible only while is_active = 1 and NOW() is inside the
// [publish_from, publish_until] window. Each landing page can capture leads into
// the event_leads table. See database/migration_20260612_events.sql.

// All columns the create/update statements bind, with safe defaults. Keeps the
// view code and the two write paths in sync from one place.
function events_defaults(): array {
    return [
        'slug' => '', 'title' => '', 'subtitle' => '', 'summary' => '',
        'description' => '', 'hero_image' => '', 'cor' => '#C2D500',
        'location' => '', 'event_starts_at' => null, 'event_ends_at' => null,
        'publish_from' => '', 'publish_until' => '', 'is_active' => 1,
        'cta_label' => '', 'cta_url' => '', 'show_lead_form' => 1,
        'gym_id' => null,   // NULL = shown on every gym's homepage
    ];
}

// ─── Reads ─────────────────────────────────────────────────────────────────────

function db_events_list(PDO $pdo, bool $include_deleted = false): array {
    $sql = "SELECT * FROM events";
    if (!$include_deleted) $sql .= " WHERE deleted_at IS NULL";
    $sql .= " ORDER BY publish_from DESC, e_id DESC";
    return $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}

function db_events_get(PDO $pdo, int $id): ?array {
    $stmt = $pdo->prepare("SELECT * FROM events WHERE e_id = ? LIMIT 1");
    $stmt->execute([$id]);
    return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
}

function db_events_get_by_slug(PDO $pdo, string $slug): ?array {
    $stmt = $pdo->prepare("SELECT * FROM events WHERE slug = ? AND deleted_at IS NULL LIMIT 1");
    $stmt->execute([$slug]);
    return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
}

// Publicly visible events, in show order (soonest-ending first so the most
// urgent banner leads). Defensive: returns [] if the table doesn't exist yet.
function db_events_active(PDO $pdo): array {
    try {
        $stmt = $pdo->query(
            "SELECT * FROM events
             WHERE deleted_at IS NULL AND is_active = 1
               AND NOW() BETWEEN publish_from AND publish_until
             ORDER BY publish_until ASC, publish_from ASC"
        );
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        return [];
    }
}

// Lifecycle label for the backoffice badge.
function events_status(array $row): string {
    if (!empty($row['deleted_at']))            return 'deleted';
    if ((int) ($row['is_active'] ?? 0) !== 1)  return 'inactive';
    $now   = time();
    $from  = strtotime((string) ($row['publish_from']  ?? '')) ?: 0;
    $until = strtotime((string) ($row['publish_until'] ?? '')) ?: 0;
    if ($from  && $now < $from)  return 'scheduled';
    if ($until && $now > $until) return 'expired';
    return 'active';
}

// Is the event live for the public right now?
function events_is_live(array $row): bool {
    return events_status($row) === 'active';
}

// ─── Slug ──────────────────────────────────────────────────────────────────────

// Build a URL-safe, unique slug from a title. When editing, pass $ignoreId so a
// row doesn't collide with itself.
function events_make_slug(PDO $pdo, string $title, ?int $ignoreId = null): string {
    $base = strtolower(trim($title));
    // Best-effort transliteration of accented PT characters.
    if (function_exists('iconv')) {
        $t = @iconv('UTF-8', 'ASCII//TRANSLIT', $base);
        if ($t !== false) $base = $t;
    }
    $base = preg_replace('/[^a-z0-9]+/', '-', strtolower($base));
    $base = trim((string) $base, '-');
    if ($base === '') $base = 'evento';
    $base = substr($base, 0, 150);

    $slug = $base;
    $i = 2;
    while (true) {
        if ($ignoreId !== null) {
            $stmt = $pdo->prepare("SELECT 1 FROM events WHERE slug = ? AND e_id <> ? LIMIT 1");
            $stmt->execute([$slug, $ignoreId]);
        } else {
            $stmt = $pdo->prepare("SELECT 1 FROM events WHERE slug = ? LIMIT 1");
            $stmt->execute([$slug]);
        }
        if (!$stmt->fetchColumn()) return $slug;
        $slug = $base . '-' . $i++;
    }
}

// ─── Writes ────────────────────────────────────────────────────────────────────

// Coerce an empty datetime-local string to NULL (the optional event_* columns).
function events_nullable_dt($value): ?string {
    $value = trim((string) $value);
    return $value === '' ? null : str_replace('T', ' ', $value);
}

function db_events_create(PDO $pdo, array $data): int {
    $d = array_merge(events_defaults(), $data);
    $stmt = $pdo->prepare("
        INSERT INTO events
            (slug, title, subtitle, summary, description, hero_image, cor, location,
             event_starts_at, event_ends_at, publish_from, publish_until,
             is_active, cta_label, cta_url, show_lead_form, gym_id)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    ");
    $stmt->execute([
        $d['slug'], $d['title'], $d['subtitle'], $d['summary'], $d['description'],
        $d['hero_image'], $d['cor'], $d['location'],
        events_nullable_dt($d['event_starts_at']), events_nullable_dt($d['event_ends_at']),
        str_replace('T', ' ', (string) $d['publish_from']),
        str_replace('T', ' ', (string) $d['publish_until']),
        (int) $d['is_active'], $d['cta_label'], $d['cta_url'], (int) $d['show_lead_form'],
        $d['gym_id'] ? (int) $d['gym_id'] : null,
    ]);
    $__id = (int) $pdo->lastInsertId();
    audit_change($pdo, 'event', 'events', 'e_id', (int) $__id, 'create', null, 'title');
    return $__id;
}

function db_events_update(PDO $pdo, int $id, array $data): bool {
    $__before = audit_row($pdo, 'events', 'e_id', (int) $id);
    $d = array_merge(events_defaults(), $data);
    $stmt = $pdo->prepare("
        UPDATE events SET
            slug = ?, title = ?, subtitle = ?, summary = ?, description = ?,
            hero_image = ?, cor = ?, location = ?,
            event_starts_at = ?, event_ends_at = ?, publish_from = ?, publish_until = ?,
            is_active = ?, cta_label = ?, cta_url = ?, show_lead_form = ?, gym_id = ?
        WHERE e_id = ?
    ");
    $stmt->execute([
        $d['slug'], $d['title'], $d['subtitle'], $d['summary'], $d['description'],
        $d['hero_image'], $d['cor'], $d['location'],
        events_nullable_dt($d['event_starts_at']), events_nullable_dt($d['event_ends_at']),
        str_replace('T', ' ', (string) $d['publish_from']),
        str_replace('T', ' ', (string) $d['publish_until']),
        (int) $d['is_active'], $d['cta_label'], $d['cta_url'], (int) $d['show_lead_form'],
        $d['gym_id'] ? (int) $d['gym_id'] : null,
        $id,
    ]);
    $__r = $stmt->rowCount() > 0;
    audit_change($pdo, 'event', 'events', 'e_id', (int) $id, 'update', $__before, 'title');
    return $__r;
}

// Soft delete para a lixeira (config/functions_trash.php).
function db_events_delete(PDO $pdo, int $id): bool {
    return trash_delete($pdo, 'event', $id);
}

function db_events_restore(PDO $pdo, int $id): bool {
    return trash_restore($pdo, 'event', $id);
}

// ─── Hero image upload / removal ───────────────────────────────────────────────
// Stored in uploads/events/ as `upl_<slug>_<rand>.<ext>`. Mirrors the modalidades
// icon upload, but optimises to the larger IMG_MAX_EVENT cap (it's a full-bleed
// hero). events_image_remove() only ever unlinks files it created (`upl_` prefix).

function events_image_dir(): string {
    return __DIR__ . '/../uploads/events/';
}

function events_image_upload(array $file, ?string &$error = null): ?string {
    $error = null;
    if (($file['error'] ?? UPLOAD_ERR_NO_FILE) === UPLOAD_ERR_NO_FILE) {
        return null; // nothing uploaded — leave the existing image alone
    }
    if ($file['error'] !== UPLOAD_ERR_OK) {
        $error = 'Falha no upload da imagem (código ' . (int) $file['error'] . ').';
        return null;
    }
    if (($file['size'] ?? 0) > 6 * 1024 * 1024) {
        $error = 'A imagem excede o limite de 6 MB.';
        return null;
    }
    if (empty($file['tmp_name']) || !is_uploaded_file($file['tmp_name'])) {
        $error = 'Upload inválido.';
        return null;
    }
    $allowed = ['png', 'jpg', 'jpeg', 'gif', 'webp'];
    // Tipo decidido pelo conteúdo, não pelo nome; SVG só sem scripts
    // (config/functions_images.php).
    $ext = upload_image_ext($file, $allowed);
    if ($ext === null) {
        $error = 'Formato inválido ou ficheiro que não é uma imagem válida. Permitidos: ' . implode(', ', $allowed) . '.';
        return null;
    }

    $dir = events_image_dir();
    if (!is_dir($dir)) @mkdir($dir, 0775, true);

    $slug = preg_replace('/[^a-z0-9]+/', '-', strtolower(pathinfo($file['name'], PATHINFO_FILENAME)));
    $slug = substr(trim((string) $slug, '-'), 0, 40);
    if ($slug === '') $slug = 'hero';
    $filename = 'upl_' . $slug . '_' . bin2hex(random_bytes(4)) . '.' . $ext;

    if (!move_uploaded_file($file['tmp_name'], $dir . $filename)) {
        $error = 'Não foi possível guardar a imagem.';
        return null;
    }
    if (defined('IMG_MAX_EVENT') && function_exists('img_optimize')) {
        img_optimize($dir . $filename, IMG_MAX_EVENT);
    }
    return $filename;
}

function events_image_remove(?string $filename): void {
    $filename = basename((string) $filename);
    if ($filename === '' || strncmp($filename, 'upl_', 4) !== 0) return;
    $path = events_image_dir() . $filename;
    if (is_file($path)) @unlink($path);
    img_fallback_follow($path);
}

// ─── Leads ─────────────────────────────────────────────────────────────────────

function db_event_leads_list(PDO $pdo, int $eventId): array {
    $stmt = $pdo->prepare("SELECT * FROM event_leads WHERE e_id = ? AND deleted_at IS NULL ORDER BY created_at DESC, lead_id DESC");
    $stmt->execute([$eventId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function db_event_leads_count(PDO $pdo, int $eventId): int {
    try {
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM event_leads WHERE e_id = ? AND deleted_at IS NULL");
        $stmt->execute([$eventId]);
        return (int) $stmt->fetchColumn();
    } catch (Throwable $e) {
        return 0;
    }
}

function db_event_lead_create(PDO $pdo, int $eventId, array $data): int {
    $stmt = $pdo->prepare("
        INSERT INTO event_leads (e_id, name, email, phone, message, consent, consent_text, ip, user_agent)
        VALUES (?,?,?,?,?,?,?,?,?)
    ");
    $stmt->execute([
        $eventId,
        mb_substr(trim((string) ($data['name']    ?? '')), 0, 150),
        mb_substr(trim((string) ($data['email']   ?? '')), 0, 190),
        mb_substr(trim((string) ($data['phone']   ?? '')), 0, 40),
        mb_substr(trim((string) ($data['message'] ?? '')), 0, 1000),
        !empty($data['consent']) ? 1 : 0,
        mb_substr(trim((string) ($data['consent_text'] ?? '')), 0, 255),
        mb_substr(trim((string) ($data['ip']           ?? '')), 0, 45),
        mb_substr(trim((string) ($data['user_agent']   ?? '')), 0, 255),
    ]);
    return (int) $pdo->lastInsertId();
}

// A inscrição tem de pertencer ao evento aberto (não se apaga a de outro
// evento mudando o id). Vai para a lixeira.
function db_event_lead_delete(PDO $pdo, int $leadId, int $eventId): bool {
    $own = $pdo->prepare("SELECT 1 FROM event_leads WHERE lead_id = ? AND e_id = ?");
    $own->execute([$leadId, $eventId]);
    if (!$own->fetchColumn()) return false;
    return trash_delete($pdo, 'event_lead', $leadId);
}