visit.waterschurch.org/public/api/lib/Db.php

<?php
declare(strict_types=1);

/**
 * Lead persistence. SQLite by default (available on virtually every cPanel PHP
 * build via pdo_sqlite), stored in the private data directory. Set
 * DATABASE_DSN / DATABASE_USER / DATABASE_PASSWORD to use cPanel MySQL instead.
 *
 * Leads are ALWAYS written here before any third-party call is made.
 */
final class Db
{
    private static ?PDO $pdo = null;

    public static function pdo(): PDO
    {
        if (self::$pdo instanceof PDO) {
            return self::$pdo;
        }
        $dsn = Config::get('DATABASE_DSN');
        if ($dsn) {
            $pdo = new PDO($dsn, Config::get('DATABASE_USER', ''), Config::get('DATABASE_PASSWORD', ''), [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_TIMEOUT => 5,
            ]);
        } else {
            $file = Config::dataDir() . '/leads.sqlite';
            $isNew = !file_exists($file);
            $pdo = new PDO('sqlite:' . $file, null, null, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_TIMEOUT => 5,
            ]);
            $pdo->exec('PRAGMA journal_mode=WAL');
            $pdo->exec('PRAGMA busy_timeout=5000');
            $pdo->exec('PRAGMA foreign_keys=ON');
            if ($isNew) {
                @chmod($file, 0640);
            }
        }
        self::$pdo = $pdo;
        self::migrate($pdo);
        return $pdo;
    }

    public static function isSqlite(): bool
    {
        return self::pdo()->getAttribute(PDO::ATTR_DRIVER_NAME) === 'sqlite';
    }

    private static function migrate(PDO $pdo): void
    {
        $sqlite = $pdo->getAttribute(PDO::ATTR_DRIVER_NAME) === 'sqlite';
        $text = $sqlite ? 'TEXT' : 'TEXT';
        $json = $sqlite ? 'TEXT' : 'JSON';
        $pk = $sqlite ? 'INTEGER PRIMARY KEY AUTOINCREMENT' : 'BIGINT AUTO_INCREMENT PRIMARY KEY';
        $ts = $sqlite ? "TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))" : 'DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP';

        $pdo->exec("CREATE TABLE IF NOT EXISTS leads (
            id $pk,
            submission_id VARCHAR(36) NOT NULL UNIQUE,
            created_at $ts,
            updated_at $ts,
            first_name VARCHAR(80) NOT NULL,
            last_name VARCHAR(80) NULL,
            email VARCHAR(254) NOT NULL,
            phone_e164 VARCHAR(20) NULL,
            campus_id VARCHAR(40) NOT NULL,
            visit_date VARCHAR(10) NULL,
            service_time VARCHAR(80) NULL,
            adults SMALLINT NULL,
            children SMALLINT NULL,
            child_age_ranges VARCHAR(80) NULL,
            questions $text NULL,
            email_consent TINYINT NOT NULL DEFAULT 0,
            sms_consent TINYINT NOT NULL DEFAULT 0,
            consent_version VARCHAR(40) NOT NULL,
            consent_at VARCHAR(30) NOT NULL,
            consent_ip_hash VARCHAR(64) NULL,
            attribution $json NULL,
            variant VARCHAR(40) NULL,
            referrer $text NULL,
            landing_url $text NULL,
            user_agent VARCHAR(300) NULL,
            mailchimp_status VARCHAR(20) NOT NULL DEFAULT 'pending',
            mailchimp_detail $text NULL,
            mailchimp_attempts SMALLINT NOT NULL DEFAULT 0,
            sms_status VARCHAR(20) NOT NULL DEFAULT 'pending',
            sms_detail $text NULL,
            sms_message_id VARCHAR(80) NULL,
            sms_attempts SMALLINT NOT NULL DEFAULT 0,
            next_retry_at VARCHAR(30) NULL
        )");
        $pdo->exec("CREATE INDEX IF NOT EXISTS idx_leads_retry ON leads (next_retry_at)");
        $pdo->exec("CREATE INDEX IF NOT EXISTS idx_leads_created ON leads (created_at)");

        $pdo->exec("CREATE TABLE IF NOT EXISTS rate_limits (
            bucket VARCHAR(120) NOT NULL PRIMARY KEY,
            hits INT NOT NULL DEFAULT 0,
            window_start INT NOT NULL
        )");

        $pdo->exec("CREATE TABLE IF NOT EXISTS cache (
            cache_key VARCHAR(120) NOT NULL PRIMARY KEY,
            value $text NOT NULL,
            expires_at INT NOT NULL
        )");
    }

    /** Simple cache helpers (used for geolocation lookups). */
    public static function cacheGet(string $key): ?string
    {
        $st = self::pdo()->prepare('SELECT value FROM cache WHERE cache_key = ? AND expires_at > ?');
        $st->execute([$key, time()]);
        $v = $st->fetchColumn();
        return $v === false ? null : (string)$v;
    }

    public static function cacheSet(string $key, string $value, int $ttlSeconds): void
    {
        $pdo = self::pdo();
        if (self::isSqlite()) {
            $st = $pdo->prepare('INSERT OR REPLACE INTO cache (cache_key, value, expires_at) VALUES (?, ?, ?)');
        } else {
            $st = $pdo->prepare('REPLACE INTO cache (cache_key, value, expires_at) VALUES (?, ?, ?)');
        }
        $st->execute([$key, $value, time() + $ttlSeconds]);
        if (random_int(1, 50) === 1) {
            $pdo->prepare('DELETE FROM cache WHERE expires_at < ?')->execute([time()]);
        }
    }
}