<?php
// ─────────────────────────────────────────────────────────────
//  lib.php — Core library: DB, auth, SMTP, PDF, helpers
// ─────────────────────────────────────────────────────────────
if (!defined('DB_HOST')) require_once __DIR__ . '/config.php';
if (file_exists(__DIR__ . '/vendor/autoload.php')) require_once __DIR__ . '/vendor/autoload.php';

date_default_timezone_set(defined('APP_TIMEZONE') ? APP_TIMEZONE : 'UTC');

// ── PDO singleton ─────────────────────────────────────────────
function db(): PDO {
    static $pdo = null;
    if ($pdo) return $pdo;
    $pdo = new PDO(
        'mysql:host='.DB_HOST.';port='.DB_PORT.';dbname='.DB_NAME.';charset=utf8mb4',
        DB_USER, DB_PASS,
        [PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES=>false]
    );
    return $pdo;
}

// ── Schema init ───────────────────────────────────────────────
function init_db(): void {
    $pdo = db();
    $pdo->exec("CREATE TABLE IF NOT EXISTS fb_forms (
        id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        slug        VARCHAR(80)  NOT NULL UNIQUE,
        title       VARCHAR(200) NOT NULL,
        description TEXT,
        fields      LONGTEXT     NOT NULL,
        is_public   TINYINT(1)   DEFAULT 1,
        form_password VARCHAR(120) DEFAULT NULL,
        submit_label VARCHAR(80) DEFAULT 'Submit',
        success_url  VARCHAR(255) DEFAULT NULL,
        notify_email VARCHAR(200) DEFAULT NULL,
        submission_limit INT UNSIGNED DEFAULT NULL,
        is_active   TINYINT(1)   DEFAULT 1,
        created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
        updated_at  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    // Add submission_limit column if upgrading from older version
    try { $pdo->exec("ALTER TABLE fb_forms ADD COLUMN IF NOT EXISTS submission_limit INT UNSIGNED DEFAULT NULL"); } catch(\Throwable $e) {}
    // read/unread tracking
    try { $pdo->exec("ALTER TABLE fb_submissions ADD COLUMN read_at DATETIME DEFAULT NULL"); } catch(\Throwable $e) {}
    try { $pdo->exec("ALTER TABLE fb_submissions ADD INDEX idx_read (read_at)"); } catch(\Throwable $e) {}

    $pdo->exec("CREATE TABLE IF NOT EXISTS fb_submissions (
        id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        form_id     INT UNSIGNED NOT NULL,
        ref         VARCHAR(40)  NOT NULL UNIQUE,
        data        LONGTEXT     NOT NULL,
        files       LONGTEXT,
        pdf_path    VARCHAR(255) DEFAULT NULL,
        ip          VARCHAR(60),
        user_agent  VARCHAR(255),
        email_sent  TINYINT(1)   DEFAULT 0,
        email_error TEXT,
        created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_form (form_id),
        INDEX idx_ref  (ref),
        INDEX idx_created (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
}

// ── Booking schema init ───────────────────────────────────────
function init_booking_db(): void {
    $pdo = db();

    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_plans (
        id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        name          VARCHAR(120) NOT NULL,
        description   TEXT,
        price         DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        duration_days INT UNSIGNED NOT NULL DEFAULT 30,
        color         VARCHAR(20)  DEFAULT '#e09500',
        is_active     TINYINT(1)   DEFAULT 1,
        created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_services (
        id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        name          VARCHAR(120) NOT NULL,
        description   TEXT,
        duration_min  INT UNSIGNED NOT NULL DEFAULT 60,
        color         VARCHAR(20)  DEFAULT '#e09500',
        is_restricted TINYINT(1)   DEFAULT 1,
        is_active     TINYINT(1)   DEFAULT 1,
        created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_plan_services (
        plan_id    INT UNSIGNED NOT NULL,
        service_id INT UNSIGNED NOT NULL,
        PRIMARY KEY (plan_id, service_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_providers (
        id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        name         VARCHAR(120) NOT NULL,
        branch_name  VARCHAR(120) DEFAULT NULL,
        email        VARCHAR(200) DEFAULT NULL,
        phone        VARCHAR(40)  DEFAULT NULL,
        bio          TEXT,
        avatar_url   VARCHAR(255) DEFAULT NULL,
        timezone     VARCHAR(60)  DEFAULT 'UTC',
        is_active    TINYINT(1)   DEFAULT 1,
        created_at   DATETIME     DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_provider_services (
        provider_id       INT UNSIGNED NOT NULL,
        service_id        INT UNSIGNED NOT NULL,
        price             DECIMAL(10,2) DEFAULT NULL,
        capacity_per_slot INT UNSIGNED  DEFAULT 1,
        PRIMARY KEY (provider_id, service_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_provider_hours (
        id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        provider_id  INT UNSIGNED NOT NULL,
        day_of_week  TINYINT UNSIGNED NOT NULL COMMENT '0=Sun,6=Sat',
        start_time   TIME NOT NULL DEFAULT '09:00:00',
        end_time     TIME NOT NULL DEFAULT '17:00:00',
        is_active    TINYINT(1) DEFAULT 1,
        UNIQUE KEY uk_prov_day (provider_id, day_of_week)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_provider_breaks (
        id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        provider_id INT UNSIGNED NOT NULL,
        break_date  DATE NOT NULL,
        start_time  TIME NOT NULL,
        end_time    TIME NOT NULL,
        label       VARCHAR(80) DEFAULT 'Break',
        INDEX idx_prov_date (provider_id, break_date)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_customers (
        id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        email      VARCHAR(200) NOT NULL UNIQUE,
        name       VARCHAR(120) DEFAULT '',
        phone      VARCHAR(40)  DEFAULT NULL,
        created_at DATETIME     DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_memberships (
        id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        customer_id        INT UNSIGNED NOT NULL,
        plan_id            INT UNSIGNED NOT NULL,
        starts_at          DATETIME     NOT NULL,
        expires_at         DATETIME     NOT NULL,
        status             ENUM('active','expired','cancelled') DEFAULT 'active',
        stripe_payment_id  VARCHAR(120) DEFAULT NULL,
        stripe_session_id  VARCHAR(120) DEFAULT NULL,
        activated_by       VARCHAR(30)  DEFAULT 'manual',
        notes              TEXT,
        created_at         DATETIME     DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_cust (customer_id),
        INDEX idx_plan (plan_id),
        INDEX idx_exp  (expires_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_appointments (
        id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        ref           VARCHAR(20)  NOT NULL UNIQUE,
        customer_id   INT UNSIGNED NOT NULL,
        provider_id   INT UNSIGNED NOT NULL,
        service_id    INT UNSIGNED NOT NULL,
        membership_id INT UNSIGNED DEFAULT NULL,
        start_datetime DATETIME    NOT NULL,
        end_datetime   DATETIME    NOT NULL,
        notes          TEXT,
        status         ENUM('pending','confirmed','cancelled','no_show','completed') DEFAULT 'pending',
        email_sent     TINYINT(1)  DEFAULT 0,
        created_at     DATETIME    DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_cust  (customer_id),
        INDEX idx_prov  (provider_id),
        INDEX idx_start (start_datetime)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_waitlist (
        id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        customer_email  VARCHAR(200) NOT NULL,
        customer_name   VARCHAR(120) DEFAULT '',
        provider_id     INT UNSIGNED NOT NULL,
        service_id      INT UNSIGNED NOT NULL,
        slot_datetime   DATETIME     NOT NULL,
        created_at      DATETIME     DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    // ── bk_users: customer auth accounts ────────────────────
    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_users (
        id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        email               VARCHAR(200) NOT NULL UNIQUE,
        password_hash       VARCHAR(255) NOT NULL,
        email_verified_at   DATETIME     DEFAULT NULL,
        remember_token      VARCHAR(100) DEFAULT NULL,
        reset_token         VARCHAR(100) DEFAULT NULL,
        reset_token_expires DATETIME     DEFAULT NULL,
        created_at          DATETIME     DEFAULT CURRENT_TIMESTAMP,
        updated_at          DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_email (email),
        INDEX idx_reset (reset_token)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    // Add user_id FK to bk_customers (idempotent)
    try { $pdo->exec("ALTER TABLE bk_customers ADD COLUMN IF NOT EXISTS user_id INT UNSIGNED DEFAULT NULL AFTER id"); } catch (\Throwable $e) {}
    try { $pdo->exec("ALTER TABLE bk_customers ADD CONSTRAINT fk_customer_user FOREIGN KEY (user_id) REFERENCES bk_users(id) ON DELETE SET NULL"); } catch (\Throwable $e) {}

    // ── UPGRADE: Promo / discount codes ─────────────────────
    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_promo_codes (
        id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        code           VARCHAR(40)  NOT NULL UNIQUE,
        description    VARCHAR(200) DEFAULT NULL,
        discount_type  ENUM('percent','fixed') NOT NULL DEFAULT 'percent',
        discount_value DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        max_uses       INT UNSIGNED DEFAULT NULL COMMENT 'NULL = unlimited',
        uses_count     INT UNSIGNED DEFAULT 0,
        valid_from     DATETIME     DEFAULT NULL,
        valid_until    DATETIME     DEFAULT NULL,
        plan_ids       TEXT         DEFAULT NULL COMMENT 'JSON array of allowed plan IDs, NULL = all',
        is_active      TINYINT(1)   DEFAULT 1,
        created_at     DATETIME     DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_code (code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    // ── UPGRADE: Appointment reminders tracking ──────────────
    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_reminders (
        id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        appointment_id INT UNSIGNED NOT NULL,
        type           ENUM('24h','1h','custom') DEFAULT '24h',
        sent_at        DATETIME     DEFAULT NULL,
        failed         TINYINT(1)   DEFAULT 0,
        error_msg      TEXT         DEFAULT NULL,
        created_at     DATETIME     DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uk_appt_type (appointment_id, type),
        INDEX idx_sent (sent_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    // ── UPGRADE: Provider ratings & reviews ─────────────────
    $pdo->exec("CREATE TABLE IF NOT EXISTS bk_ratings (
        id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        appointment_id INT UNSIGNED NOT NULL UNIQUE,
        customer_id    INT UNSIGNED NOT NULL,
        provider_id    INT UNSIGNED NOT NULL,
        service_id     INT UNSIGNED NOT NULL,
        rating         TINYINT UNSIGNED NOT NULL DEFAULT 5 COMMENT '1-5',
        comment        TEXT         DEFAULT NULL,
        is_public      TINYINT(1)   DEFAULT 1,
        created_at     DATETIME     DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_provider (provider_id),
        INDEX idx_service  (service_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    // ── UPGRADE: Missing columns (MySQL 5.7 compatible, no IF NOT EXISTS) ──────
    $db_name_up = DB_NAME;
    $up_cols = [];
    foreach ($pdo->query("
        SELECT CONCAT(TABLE_NAME,'.',COLUMN_NAME) as tc
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = '{$db_name_up}'
          AND TABLE_NAME IN ('bk_plans','bk_services','bk_promo_codes','sys_feature_toggles')
          AND COLUMN_NAME IN ('description','is_enabled','updated_at')
    ")->fetchAll(PDO::FETCH_COLUMN) as $tc) {
        $up_cols[$tc] = true;
    }
    // bk_plans
    if (empty($up_cols['bk_plans.description']))
        try { $pdo->exec("ALTER TABLE bk_plans ADD COLUMN description TEXT DEFAULT NULL AFTER name"); } catch (\Throwable $e) {}
    // bk_services
    if (empty($up_cols['bk_services.description']))
        try { $pdo->exec("ALTER TABLE bk_services ADD COLUMN description TEXT DEFAULT NULL AFTER name"); } catch (\Throwable $e) {}
    // bk_promo_codes
    if (empty($up_cols['bk_promo_codes.description']))
        try { $pdo->exec("ALTER TABLE bk_promo_codes ADD COLUMN description VARCHAR(200) DEFAULT NULL AFTER code"); } catch (\Throwable $e) {}
    // sys_feature_toggles — add any columns the original table may be missing
    if (empty($up_cols['sys_feature_toggles.description']))
        try { $pdo->exec("ALTER TABLE sys_feature_toggles ADD COLUMN description TEXT DEFAULT NULL AFTER label"); } catch (\Throwable $e) {}
    if (empty($up_cols['sys_feature_toggles.is_enabled']))
        try { $pdo->exec("ALTER TABLE sys_feature_toggles ADD COLUMN is_enabled TINYINT(1) NOT NULL DEFAULT 1"); } catch (\Throwable $e) {}
    if (empty($up_cols['sys_feature_toggles.updated_at']))
        try { $pdo->exec("ALTER TABLE sys_feature_toggles ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"); } catch (\Throwable $e) {}

    // ── UPGRADE: Membership freeze/pause ────────────────────
    try { $pdo->exec("ALTER TABLE bk_memberships ADD COLUMN IF NOT EXISTS frozen_at DATETIME DEFAULT NULL AFTER notes"); } catch (\Throwable $e) {}
    try { $pdo->exec("ALTER TABLE bk_memberships ADD COLUMN IF NOT EXISTS frozen_days INT UNSIGNED DEFAULT 0 AFTER frozen_at"); } catch (\Throwable $e) {}
    try { $pdo->exec("ALTER TABLE bk_memberships ADD COLUMN IF NOT EXISTS promo_code VARCHAR(40) DEFAULT NULL AFTER notes"); } catch (\Throwable $e) {}
    try { $pdo->exec("ALTER TABLE bk_memberships ADD COLUMN IF NOT EXISTS discount_amount DECIMAL(10,2) DEFAULT 0.00 AFTER promo_code"); } catch (\Throwable $e) {}

    // ── UPGRADE: Add reminder_sent columns to appointments ───
    try { $pdo->exec("ALTER TABLE bk_appointments ADD COLUMN IF NOT EXISTS reminder_24h_sent TINYINT(1) DEFAULT 0 AFTER email_sent"); } catch (\Throwable $e) {}
    try { $pdo->exec("ALTER TABLE bk_appointments ADD COLUMN IF NOT EXISTS reminder_1h_sent TINYINT(1) DEFAULT 0 AFTER reminder_24h_sent"); } catch (\Throwable $e) {}
    try { $pdo->exec("ALTER TABLE bk_appointments ADD COLUMN IF NOT EXISTS cancellation_reason TEXT DEFAULT NULL AFTER notes"); } catch (\Throwable $e) {}

    // ── UPGRADE: Add loyalty_points to customers ─────────────
    try { $pdo->exec("ALTER TABLE bk_customers ADD COLUMN IF NOT EXISTS loyalty_points INT UNSIGNED DEFAULT 0 AFTER phone"); } catch (\Throwable $e) {}
    try { $pdo->exec("ALTER TABLE bk_customers ADD COLUMN IF NOT EXISTS total_bookings INT UNSIGNED DEFAULT 0 AFTER loyalty_points"); } catch (\Throwable $e) {}

    // ── PHASE 0: Module toggle table ─────────────────────────
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS sys_feature_toggles (
            `key`        VARCHAR(64)  NOT NULL,
            label        VARCHAR(128) NOT NULL,
            description  TEXT,
            is_enabled   TINYINT(1)   NOT NULL DEFAULT 1,
            updated_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
                         ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`key`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    // DELETE stale rows with no label (from failed earlier migrations)
    $pdo->exec("DELETE FROM sys_feature_toggles WHERE (label IS NULL OR label = '') AND `key` NOT IN ('module_forms','module_booking','module_membership')");
    // Use ON DUPLICATE KEY UPDATE so existing rows get correct label/description
    $pdo->exec("
        INSERT INTO sys_feature_toggles (`key`, label, description, is_enabled) VALUES
        ('module_forms',      'Form Builder',           'Custom forms, submissions, file uploads, e-signatures.', 1),
        ('module_booking',    'Appointment Booking',    'Services, providers, schedules and appointments.',       1),
        ('module_membership', 'Membership & Sessions',  'Stripe plans, session limits, QR check-in.',             1)
        ON DUPLICATE KEY UPDATE
            label       = VALUES(label),
            description = VALUES(description)
    ");

    // ── PHASE 1: Session-limited membership columns (MySQL 5.7 compatible) ──
    // Use information_schema check instead of IF NOT EXISTS (not supported on MySQL 5.7)
    $db_name = DB_NAME;
    $existing_cols = [];
    foreach ($pdo->query("SELECT CONCAT(TABLE_NAME,'.',COLUMN_NAME) as tc FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = '{$db_name}'
        AND TABLE_NAME IN ('bk_plans','bk_memberships','bk_checkins')
        AND COLUMN_NAME IN ('session_limit','sessions_remaining')")->fetchAll(PDO::FETCH_COLUMN) as $tc) {
        $existing_cols[$tc] = true;
    }
    if (empty($existing_cols['bk_plans.session_limit']))
        try { $pdo->exec("ALTER TABLE bk_plans ADD COLUMN session_limit INT NOT NULL DEFAULT 0 COMMENT '0=unlimited' AFTER duration_days"); } catch (\Throwable $e) {}
    if (empty($existing_cols['bk_memberships.sessions_remaining']))
        try { $pdo->exec("ALTER TABLE bk_memberships ADD COLUMN sessions_remaining INT DEFAULT NULL COMMENT 'NULL=unlimited' AFTER discount_amount"); } catch (\Throwable $e) {}
    if (empty($existing_cols['bk_checkins.sessions_remaining']))
        try { $pdo->exec("ALTER TABLE bk_checkins ADD COLUMN sessions_remaining INT DEFAULT NULL COMMENT 'snapshot after check-in' AFTER customer_id"); } catch (\Throwable $e) {}

    // ── PHASE 1: Check-in log ─────────────────────────────────
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS bk_checkins (
            id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            appointment_id  INT UNSIGNED     DEFAULT NULL,
            membership_id   INT UNSIGNED     NOT NULL,
            customer_id     INT UNSIGNED     NOT NULL,
            sessions_remaining INT           DEFAULT NULL COMMENT 'snapshot after check-in',
            checked_in_at   DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
            scanned_by      INT UNSIGNED     DEFAULT NULL COMMENT 'admin user id',
            method          ENUM('qr','manual') NOT NULL DEFAULT 'qr',
            INDEX idx_mem  (membership_id),
            INDEX idx_cust (customer_id),
            INDEX idx_date (checked_in_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // ── PHASE 3: Membership adjustment audit log ──────────────
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS bk_membership_log (
            id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            membership_id   INT UNSIGNED NOT NULL,
            admin_id        INT UNSIGNED DEFAULT NULL,
            action          ENUM('adjust','reset','freeze','unfreeze') NOT NULL,
            sessions_delta  INT          DEFAULT NULL,
            note            TEXT,
            created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_mem (membership_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // ── UPGRADE: Missing indexes on hot query columns ─────────
    $db_idx = DB_NAME;
    $existing_idx = [];
    foreach ($pdo->query("
        SELECT CONCAT(TABLE_NAME,'.',INDEX_NAME) as ti
        FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = '{$db_idx}'
          AND TABLE_NAME IN ('bk_customers','bk_appointments')
          AND INDEX_NAME IN ('idx_user_id','idx_service','idx_membership')
    ")->fetchAll(PDO::FETCH_COLUMN) as $ti) {
        $existing_idx[$ti] = true;
    }
    if (empty($existing_idx['bk_customers.idx_user_id']))
        try { $pdo->exec("ALTER TABLE bk_customers ADD INDEX idx_user_id (user_id)"); } catch (\Throwable $e) {}
    if (empty($existing_idx['bk_appointments.idx_service']))
        try { $pdo->exec("ALTER TABLE bk_appointments ADD INDEX idx_service (service_id)"); } catch (\Throwable $e) {}
    if (empty($existing_idx['bk_appointments.idx_membership']))
        try { $pdo->exec("ALTER TABLE bk_appointments ADD INDEX idx_membership (membership_id)"); } catch (\Throwable $e) {}
}

// ── Auth ──────────────────────────────────────────────────────
function session_start_safe(): void {
    if (session_status() === PHP_SESSION_NONE) {
        session_set_cookie_params([
            'path'     => '/',
            'httponly' => true,
            'samesite' => 'Lax',
            'secure'   => isset($_SERVER['HTTPS']),
        ]);
        session_start();
    }
}

/** Return (or generate) the CSRF token for the current admin session. */
function csrf_token(): string {
    session_start_safe();
    if (empty($_SESSION['csrf_token'])) {
        $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
    }
    return $_SESSION['csrf_token'];
}

function admin_logged_in(): bool {
    session_start_safe();
    if (empty($_SESSION['admin_authed']) || empty($_SESSION['admin_time'])) return false;
    if (time() - $_SESSION['admin_time'] > ADMIN_SESSION_TTL) {
        session_destroy(); return false;
    }
    $_SESSION['admin_time'] = time();
    return true;
}

function require_admin(): void {
    if (!admin_logged_in()) { http_response_code(401); json_out(['ok'=>false,'error'=>'Unauthorized']); }
    // CSRF check for all state-changing requests
    $method = $_SERVER['REQUEST_METHOD'] ?? 'GET';
    if (in_array($method, ['POST','PUT','PATCH','DELETE'], true)) {
        $token = $_SERVER['HTTP_X_CSRF_TOKEN'] ?? '';
        if (!$token || !hash_equals(csrf_token(), $token)) {
            http_response_code(403);
            json_out(['ok'=>false,'error'=>'Invalid or missing CSRF token']);
        }
    }
}

function api_require_admin(): void { require_admin(); }

// ── Helpers ───────────────────────────────────────────────────
function json_out(array $d, int $code = 200): void {
    http_response_code($code);
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode($d, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}

function cors(): void {
    $o = defined('ALLOWED_ORIGIN') ? ALLOWED_ORIGIN : '*';
    header('Access-Control-Allow-Origin: '.$o);
    header('Access-Control-Allow-Methods: GET,POST,PUT,DELETE,OPTIONS');
    header('Access-Control-Allow-Headers: Content-Type,X-Requested-With,X-CSRF-Token');
    header('Access-Control-Allow-Credentials: true');
    header('X-Content-Type-Options: nosniff');
    header('X-Frame-Options: SAMEORIGIN');
    header('Referrer-Policy: strict-origin-when-cross-origin');
    header("Content-Security-Policy: default-src 'self'; script-src 'self' https://cdnjs.cloudflare.com https://cdn.jsdelivr.net https://accounts.google.com 'unsafe-inline'; style-src 'self' https://fonts.googleapis.com 'unsafe-inline'; font-src 'self' https://fonts.gstatic.com; img-src 'self' data: https:; connect-src 'self'; frame-ancestors 'none'");
    if ($_SERVER['REQUEST_METHOD']==='OPTIONS') { http_response_code(204); exit; }
}

function body(): array {
    static $cached = null;
    if ($cached !== null) return $cached;
    $ct = $_SERVER['CONTENT_TYPE'] ?? '';
    if (str_contains($ct, 'application/json')) {
        $cached = json_decode(file_get_contents('php://input'), true) ?: [];
    } else {
        $cached = $_POST;
    }
    return $cached;
}

function client_ip(): string {
    return trim(explode(',', $_SERVER['HTTP_X_FORWARDED_FOR'] ?? '')[0])
        ?: ($_SERVER['REMOTE_ADDR'] ?? '');
}

function esc(?string $v): string {
    if ($v === null || $v === '') return '—';
    return htmlspecialchars($v, ENT_QUOTES|ENT_SUBSTITUTE, 'UTF-8');
}

function slugify(string $s): string {
    $s = mb_strtolower(trim($s));
    $s = preg_replace('/[^a-z0-9]+/', '-', $s);
    return trim($s, '-') ?: 'form';
}

function unique_slug(string $base): string {
    $slug = slugify($base);
    $orig = $slug; $i = 2;
    while (db()->query("SELECT id FROM fb_forms WHERE slug=".db()->quote($slug))->fetchColumn()) {
        $slug = $orig.'-'.$i++;
    }
    return $slug;
}

function gen_ref(): string {
    return 'FB-' . strtoupper(base_convert((string)time(), 10, 36)) . '-' . strtoupper(substr(bin2hex(random_bytes(3)), 0, 6));
}

function check_rate(string $ip): bool {
    $f = sys_get_temp_dir().'/fb_rl_'.md5($ip).'.json';
    $now = time(); $d = ['count'=>0,'start'=>$now];
    if (file_exists($f)) { $r = json_decode(file_get_contents($f), true) ?: $d; if ($now-$r['start'] < RATE_LIMIT_WINDOW) $d=$r; }
    if ($d['count'] >= RATE_LIMIT_MAX) return false;
    $d['count']++; file_put_contents($f, json_encode($d), LOCK_EX);
    return true;
}

// ── PDF generation (pure PHP, no dependencies) ───────────────
function generate_pdf(array $form, array $fields, array $data, array $files, string $ref, string $submitted_at): ?string {
    // Use FPDF if available via composer, else build minimal PDF manually
    if (class_exists('FPDF')) return generate_pdf_fpdf($form, $fields, $data, $files, $ref, $submitted_at);
    return generate_pdf_raw($form, $fields, $data, $files, $ref, $submitted_at);
}

function generate_pdf_raw(array $form, array $fields, array $data, array $files, string $ref, string $submitted_at): ?string {
    // Minimal hand-crafted PDF — works without any library
    $title  = $form['title'] ?? 'Form Submission';
    $lines  = [];
    $lines[] = $title;
    $lines[] = str_repeat('=', min(60, strlen($title)));
    $lines[] = 'Reference: ' . $ref;
    $lines[] = 'Submitted: ' . $submitted_at;
    $lines[] = '';
    foreach ($fields as $field) {
        $key   = $field['id'] ?? '';
        $label = $field['label'] ?? $key;
        $type  = $field['type'] ?? 'text';
        if ($type === 'signature') {
            $lines[] = $label . ': [Signature captured]';
        } elseif ($type === 'file') {
            $flist = [];
            foreach (($files[$key] ?? []) as $fi) $flist[] = $fi['original_name'] ?? '';
            $lines[] = $label . ': ' . (implode(', ', $flist) ?: '—');
        } else {
            $val = $data[$key] ?? '';
            if (is_array($val)) $val = implode(', ', $val);
            $lines[] = $label . ': ' . ($val ?: '—');
        }
    }
    $lines[] = '';
    $lines[] = 'Generated by ' . (defined('APP_NAME') ? APP_NAME : 'FormBuilder');

    // Build a real (minimal) PDF structure
    $text = implode("\n", $lines);
    // Escape special PDF chars
    // Also transliterate to windows-1252 so non-ASCII chars (accented letters,
    // em-dashes, etc.) don't produce malformed PDF streams in Acrobat.
    $text = iconv('UTF-8', 'windows-1252//TRANSLIT//IGNORE', $text);
    $text = str_replace(['\\','(',')'], ['\\\\','\\(','\\)'], $text);

    $objects = [];
    // Object 1: catalog
    $objects[1] = "1 0 obj\n<< /Type /Catalog /Pages 2 0 R >>\nendobj";
    // Object 2: pages
    $objects[2] = "2 0 obj\n<< /Type /Pages /Kids [3 0 R] /Count 1 >>\nendobj";
    // Object 4: font
    $objects[4] = "4 0 obj\n<< /Type /Font /Subtype /Type1 /BaseFont /Helvetica /Encoding /WinAnsiEncoding >>\nendobj";

    // Build content stream
    $stream = "BT\n/F1 10 Tf\n14 TL\n50 780 Td\n";
    $linesArr = explode("\n", $text);
    foreach ($linesArr as $i => $line) {
        if ($i === 0) {
            $stream .= "/F1 14 Tf\n(" . $line . ") Tj\nT*\n/F1 10 Tf\n";
        } else {
            $stream .= "(" . $line . ") Tj\nT*\n";
        }
    }
    $stream .= "ET";
    $streamLen = strlen($stream);

    // Object 5: content
    $objects[5] = "5 0 obj\n<< /Length $streamLen >>\nstream\n$stream\nendstream\nendobj";
    // Object 3: page
    $objects[3] = "3 0 obj\n<< /Type /Page /Parent 2 0 R /MediaBox [0 0 612 792]\n   /Contents 5 0 R /Resources << /Font << /F1 4 0 R >> >> >>\nendobj";

    // Build xref
    $body_parts = ["%PDF-1.4\n"];
    $offsets = [];
    $pos = strlen($body_parts[0]);
    foreach ([1,2,3,4,5] as $n) {
        $offsets[$n] = $pos;
        $body_parts[] = $objects[$n] . "\n";
        $pos += strlen(end($body_parts));
    }
    $xref_pos = $pos;
    $xref = "xref\n0 6\n0000000000 65535 f \n";
    foreach ([1,2,3,4,5] as $n) $xref .= str_pad($offsets[$n], 10, '0', STR_PAD_LEFT) . " 00000 n \n";
    $trailer = "trailer\n<< /Size 6 /Root 1 0 R >>\nstartxref\n$xref_pos\n%%EOF";

    $pdf_content = implode('', $body_parts) . $xref . $trailer;

    $dir = __DIR__ . '/data/pdfs';
    if (!is_dir($dir)) @mkdir($dir, 0755, true);
    $safe = preg_replace('/[^A-Za-z0-9\-]/', '', $ref);
    $path = $dir . '/' . $safe . '.pdf';
    if (file_put_contents($path, $pdf_content) !== false) return $path;
    return null;
}

function generate_pdf_fpdf(array $form, array $fields, array $data, array $files, string $ref, string $submitted_at): ?string {
    try {
        $pdf = new FPDF();
        $pdf->AddPage();
        $pdf->SetMargins(15, 15, 15);
        $pdf->SetAutoPageBreak(true, 20);

        // Header bar
        $pdf->SetFillColor(240, 165, 0);
        $pdf->Rect(0, 0, 210, 20, 'F');
        $pdf->SetFont('Helvetica', 'B', 14);
        $pdf->SetTextColor(255, 255, 255);
        $pdf->SetXY(15, 5);
        $pdf->Cell(0, 10, iconv('UTF-8','windows-1252//TRANSLIT', $form['title'] ?? 'Form Submission'), 0, 1);

        $pdf->SetFont('Helvetica', '', 8);
        $pdf->SetTextColor(255, 240, 200);
        $pdf->SetX(15);
        $pdf->Cell(0, 5, 'Ref: '.$ref.'   Submitted: '.$submitted_at, 0, 1);

        $pdf->Ln(4);
        $labelW = 55; $valW = 120;

        foreach ($fields as $field) {
            if ($pdf->GetY() > 260) $pdf->AddPage();
            $key   = $field['id'] ?? '';
            $label = $field['label'] ?? $key;
            $type  = $field['type'] ?? 'text';

            $pdf->SetFont('Helvetica','B', 8);
            $pdf->SetTextColor(192, 127, 0);
            $pdf->SetFillColor(248, 250, 252);
            $safeLabel = iconv('UTF-8','windows-1252//TRANSLIT', strtoupper($label));
            $pdf->Cell($labelW, 7, $safeLabel, 0, 0, 'L', false);

            $pdf->SetFont('Helvetica','', 9);
            $pdf->SetTextColor(10, 22, 40);
            if ($type === 'signature') {
                $sig = $data[$key] ?? '';
                if ($sig && str_starts_with($sig, 'data:image')) {
                    try {
                        $imgData = base64_decode(explode(',', $sig)[1] ?? '');
                        $tmp = tempnam(sys_get_temp_dir(), 'sig_') . '.png';
                        file_put_contents($tmp, $imgData);
                        $x = $pdf->GetX(); $y = $pdf->GetY();
                        $pdf->Image($tmp, $x, $y, 60, 15);
                        @unlink($tmp);
                        $pdf->Ln(18);
                    } catch (\Throwable $_) {
                        $pdf->Cell($valW, 7, '[Signature captured]', 0, 1);
                    }
                } else {
                    $pdf->Cell($valW, 7, '[No signature]', 0, 1);
                }
            } elseif ($type === 'file') {
                $flist = [];
                foreach (($files[$key] ?? []) as $fi) $flist[] = $fi['original_name'] ?? '';
                $val = implode(', ', $flist) ?: '—';
                $pdf->MultiCell($valW, 7, iconv('UTF-8','windows-1252//TRANSLIT', $val), 0, 'L');
            } else {
                $val = $data[$key] ?? '';
                if (is_array($val)) $val = implode(', ', $val);
                $safe = iconv('UTF-8','windows-1252//TRANSLIT', $val ?: '—');
                $pdf->MultiCell($valW, 7, $safe, 0, 'L');
            }
            // divider
            $pdf->SetDrawColor(226, 232, 240);
            $pdf->Line(15, $pdf->GetY(), 195, $pdf->GetY());
            $pdf->Ln(1);
        }

        // Footer
        $pdf->SetY(-20);
        $pdf->SetFont('Helvetica','',7);
        $pdf->SetTextColor(148,163,184);
        $pdf->Cell(0,5,defined('APP_NAME')?APP_NAME:'FormBuilder',0,0,'L');
        $pdf->Cell(0,5,$ref,0,0,'R');

        $dir = __DIR__ . '/data/pdfs';
        if (!is_dir($dir)) @mkdir($dir, 0755, true);
        $safe = preg_replace('/[^A-Za-z0-9\-]/', '', $ref);
        $path = $dir . '/' . $safe . '.pdf';
        $pdf->Output('F', $path);
        return $path;
    } catch (\Throwable $e) {
        error_log('FPDF error: '.$e->getMessage());
        return generate_pdf_raw($form, $fields, $data, $files, $ref, $submitted_at);
    }
}

// ── SMTP mailer ───────────────────────────────────────────────
function send_mail(string $to, string $to_name, string $subject, string $html, string $text, array $attachments = []): bool {
    if (class_exists('PHPMailer\PHPMailer\PHPMailer')) {
        return _send_phpmailer($to, $to_name, $subject, $html, $text, $attachments);
    }
    return _send_raw_smtp($to, $to_name, $subject, $html, $text, $attachments);
}

function _send_phpmailer(string $to, string $to_name, string $subject, string $html, string $text, array $attachments): bool {
    try {
        $m = new PHPMailer\PHPMailer\PHPMailer(true);
        $m->isSMTP();
        $m->Host       = SMTP_HOST; $m->SMTPAuth = true;
        $m->Username   = SMTP_USER; $m->Password = SMTP_PASS;
        $m->SMTPSecure = SMTP_SECURE==='ssl' ? PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_SMTPS : PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_STARTTLS;
        $m->Port       = SMTP_PORT; $m->CharSet  = 'UTF-8';
        $m->setFrom(FROM_EMAIL, FROM_NAME);
        $m->addAddress($to, $to_name);
        $m->addReplyTo(ADMIN_EMAIL);
        $m->Subject = $subject; $m->isHTML(true);
        $m->Body = $html; $m->AltBody = $text;
        foreach ($attachments as $att) {
            if (file_exists($att['path'])) $m->addAttachment($att['path'], $att['name']);
        }
        $m->send(); return true;
    } catch (\Throwable $e) { error_log('PHPMailer: '.$e->getMessage()); return false; }
}

function _send_raw_smtp(string $to, string $to_name, string $subject, string $html, string $text, array $attachments): bool {
    $from  = FROM_EMAIL; $secure = SMTP_SECURE;
    $altB  = '==ALT'.md5(uniqid('',true)); $mixB = '==MIX'.md5(uniqid('',true));
    $alt   = "--$altB\r\nContent-Type: text/plain; charset=UTF-8\r\n\r\n$text\r\n\r\n--$altB\r\nContent-Type: text/html; charset=UTF-8\r\n\r\n$html\r\n\r\n--$altB--";
    $hasAtt = array_filter($attachments, fn($a)=>file_exists($a['path']));
    if ($hasAtt) {
        $body = "--$mixB\r\nContent-Type: multipart/alternative; boundary=\"$altB\"\r\n\r\n$alt\r\n\r\n";
        foreach ($hasAtt as $a) {
            $b64 = chunk_split(base64_encode(file_get_contents($a['path'])));
            $n   = $a['name'];
            $body .= "--$mixB\r\nContent-Type: application/octet-stream; name=\"$n\"\r\nContent-Transfer-Encoding: base64\r\nContent-Disposition: attachment; filename=\"$n\"\r\n\r\n$b64\r\n";
        }
        $body .= "--$mixB--"; $ct = "multipart/mixed; boundary=\"$mixB\"";
    } else { $body = $alt; $ct = "multipart/alternative; boundary=\"$altB\""; }
    $hdrs = "MIME-Version: 1.0\r\nContent-Type: $ct\r\nFrom: ".FROM_NAME." <$from>\r\nReply-To: ".ADMIN_EMAIL."\r\nSubject: =?UTF-8?B?".base64_encode($subject)."?=";
    $ctx  = stream_context_create(['ssl'=>['verify_peer'=>false,'verify_peer_name'=>false]]);
    $pfx  = $secure==='ssl' ? 'ssl://' : '';
    try {
        $sock = stream_socket_client($pfx.SMTP_HOST.':'.SMTP_PORT,$errno,$errstr,15,STREAM_CLIENT_CONNECT,$ctx);
        if (!$sock) throw new \RuntimeException("$errstr ($errno)");
        $r = function() use ($sock) { $s=''; while($l=fgets($sock,512)){$s.=$l;if(isset($s[3]) && $s[3]===' ')break;} return $s; };
        $s = function($c) use ($sock) { return fwrite($sock,"$c\r\n"); };
        $r(); $s('EHLO '.gethostname()); $r();
        if ($secure==='tls'){$s('STARTTLS');$r();stream_socket_enable_crypto($sock,true,STREAM_CRYPTO_METHOD_TLS_CLIENT);$s('EHLO '.gethostname());$r();}
        $s('AUTH LOGIN');$r();$s(base64_encode(SMTP_USER));$r();$s(base64_encode(SMTP_PASS));$r();
        $s("MAIL FROM:<$from>");$r();$s("RCPT TO:<$to>");$r();$s('DATA');$r();
        $s("$hdrs\r\n\r\n$body\r\n.");$r();$s('QUIT');fclose($sock); return true;
    } catch (\Throwable $e) { error_log('SMTP: '.$e->getMessage()); return false; }
}

// ── Email templates ───────────────────────────────────────────
function email_html_wrapper(string $title, string $subtitle, string $inner, string $ref = ''): string {
    $app = defined('APP_NAME') ? APP_NAME : 'FormBuilder';
    return <<<HTML
<!DOCTYPE html><html><head><meta charset="UTF-8"/></head>
<body style="margin:0;padding:0;background:#f1f5f9;font-family:'DM Sans',Arial,sans-serif">
<table width="100%" cellpadding="0" cellspacing="0" style="background:#f1f5f9;padding:32px 16px"><tr><td align="center">
<table width="600" cellpadding="0" cellspacing="0" style="background:#fff;border-radius:16px;overflow:hidden;box-shadow:0 4px 24px rgba(10,22,40,.1)">
  <tr><td style="background:linear-gradient(135deg,#f0a500,#c47f00);padding:28px 32px">
    <p style="margin:0 0 4px;color:rgba(255,255,255,.7);font-size:11px;font-weight:700;letter-spacing:.12em;text-transform:uppercase">$app</p>
    <h1 style="margin:0 0 4px;color:#fff;font-size:20px;font-weight:800">$title</h1>
    <p style="margin:0;color:rgba(255,255,255,.75);font-size:13px">$subtitle</p>
  </td></tr>
  <tr><td style="padding:24px 32px">$inner</td></tr>
  <tr><td style="padding:14px 32px;background:#f8fafc;border-top:1px solid #e2e8f0;text-align:center">
    <p style="margin:0;font-size:11px;color:#94a3b8">$app · Ref: $ref</p>
  </td></tr>
</table></td></tr></table></body></html>
HTML;
}

function build_email_rows(array $fields, array $data, array $files): string {
    $rows = '';
    $TL = 'padding:9px 12px;color:#64748b;font-size:12px;font-weight:600;text-transform:uppercase;letter-spacing:.05em;width:38%';
    $TR = 'padding:9px 12px;font-size:13px;color:#0a1628';
    $BB = 'border-bottom:1px solid #f1f5f9';
    $ALT = 'background:#f8fafc';
    $i = 0;
    foreach ($fields as $field) {
        $key = $field['id'] ?? ''; $type = $field['type'] ?? 'text';
        $label = esc($field['label'] ?? $key);
        $bg = $i % 2 === 0 ? '' : "style=\"$ALT\"";
        if ($type === 'signature') {
            $sig = $data[$key] ?? '';
            $val = $sig ? "<img src=\"$sig\" style=\"max-height:60px;display:block\"/>" : '<em style="color:#94a3b8">No signature</em>';
        } elseif ($type === 'file') {
            $flist = [];
            foreach (($files[$key] ?? []) as $fi) {
                $n = esc($fi['original_name'] ?? ''); $url = esc($fi['url'] ?? '');
                $flist[] = $url ? "<a href=\"$url\" style=\"color:#f0a500\">$n</a>" : $n;
            }
            $val = implode('<br/>', $flist) ?: '—';
        } else {
            $v = $data[$key] ?? ''; if (is_array($v)) $v = implode(', ', $v); $val = esc($v) ?: '—';
        }
        $rows .= "<tr $bg><td style=\"$TL;$BB\">$label</td><td style=\"$TR;$BB\">$val</td></tr>";
        $i++;
    }
    return $rows;
}

function send_submission_emails(array $form, array $fields, array $submission): void {
    $data  = json_decode($submission['data']  ?? '{}', true) ?: [];
    $files = json_decode($submission['files'] ?? '[]', true) ?: [];
    $ref   = $submission['ref'];
    $at    = $submission['created_at'] ?? date('Y-m-d H:i:s');
    $pdf   = $submission['pdf_path']   ?? null;

    // Build attachment list — PDF + uploaded files (if under size limit)
    $attachments = [];
    if ($pdf && file_exists($pdf)) $attachments[] = ['path'=>$pdf,'name'=>basename($pdf)];
    $total = $pdf && file_exists($pdf) ? filesize($pdf) : 0;
    $max   = (defined('EMAIL_ATTACH_MAX_MB') ? EMAIL_ATTACH_MAX_MB : 8) * 1024 * 1024;
    foreach (array_values($files) as $group) {
        foreach ((array)$group as $fi) {
            if (!empty($fi['path']) && file_exists($fi['path'])) {
                $sz = filesize($fi['path']); $total += $sz;
                if ($total <= $max) $attachments[] = ['path'=>$fi['path'],'name'=>$fi['original_name']??basename($fi['path'])];
            }
        }
    }

    $rows   = build_email_rows($fields, $data, is_array(reset($files)) ? $files : []);
    $tw     = 'border:1px solid #e2e8f0;border-radius:10px;overflow:hidden;width:100%;margin-bottom:18px';
    $table  = "<table cellpadding=\"0\" cellspacing=\"0\" style=\"$tw\">$rows</table>";

    // Admin email
    $admin_to = $form['notify_email'] ?: ADMIN_EMAIL;
    $inner = "<p style=\"margin:0 0 14px;font-size:14px;color:#334155\">A new submission was received for <strong>".esc($form['title'])."</strong>.</p>$table";
    if ($pdf) $inner .= '<p style="font-size:12px;color:#64748b">The filled form PDF is attached.</p>';
    $html = email_html_wrapper(esc($form['title']), 'New submission · Ref: '.$ref, $inner, $ref);
    $text = "New submission for {$form['title']}\nRef: $ref\nSubmitted: $at\n";
    send_mail($admin_to, 'Admin', 'New Submission — '.strip_tags($form['title']).' ('.$ref.')', $html, $text, $attachments);

    // Find submitter email field
    $sub_email = ''; $sub_name = '';
    foreach ($fields as $f) {
        if (($f['type']??'') === 'email' || strtolower($f['label']??'') === 'email') { $sub_email = $data[$f['id']??''] ?? ''; }
        if (str_contains(strtolower($f['label']??''), 'name') && !$sub_name)           { $sub_name  = $data[$f['id']??''] ?? ''; }
    }
    if (!$sub_email || !filter_var($sub_email, FILTER_VALIDATE_EMAIL)) return;

    $first = $sub_name ? explode(' ', $sub_name)[0] : 'there';
    $inner2 = "<div style=\"background:#ecfdf5;border:1px solid #a7f3d0;border-radius:10px;padding:14px 18px;margin-bottom:18px\">
        <p style=\"margin:0;color:#065f46;font-size:14px;font-weight:700\">&#x2705; Submission confirmed &middot; Ref: <strong>$ref</strong></p>
        <p style=\"margin:6px 0 0;color:#047857;font-size:13px\">We've received your form and will be in touch soon.</p>
      </div>
      <p style=\"margin:0 0 14px;font-size:14px;color:#334155\">Hi $first, here's a summary of what you submitted:</p>$table";
    $html2 = email_html_wrapper('Submission Confirmed', esc($form['title']), $inner2, $ref);
    $text2 = "Hi $first,\nYour submission has been received.\nRef: $ref\n";
    send_mail($sub_email, $sub_name ?: 'Submitter', 'Submission Confirmed — '.$ref, $html2, $text2, $attachments);
}

// ── Customer auth helpers ─────────────────────────────────────
function customer_logged_in(): bool {
    session_start_safe();
    if (empty($_SESSION['bk_user_id']) || empty($_SESSION['bk_user_time'])) return false;
    if (time() - $_SESSION['bk_user_time'] > 86400 * 2) { // 48-hour idle timeout
        session_destroy(); return false;
    }
    $_SESSION['bk_user_time'] = time();
    return true;
}

function require_customer_auth(string $returnTo = ''): void {
    if (!customer_logged_in()) {
        $url = rtrim(APP_URL, '/') . '/login.php';
        if ($returnTo) $url .= '?redirect=' . urlencode($returnTo);
        header('Location: ' . $url); exit;
    }
}
