<?php
/**
 * Membership number helpers – prefixes (labels) and next number assignment
 * Format: prefix + number e.g. YMC125 = Youth Male Communicant #125
 */

/**
 * Ensure membership_number_prefixes table exists (for upgrades)
 */
function ensureMembershipNumberPrefixesTable($pdo) {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS membership_number_prefixes (
            id INT AUTO_INCREMENT PRIMARY KEY,
            code VARCHAR(20) NOT NULL UNIQUE,
            description VARCHAR(255) NULL,
            display_order INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ");
}

/**
 * Get all prefixes ordered by display_order, then code
 */
function getMembershipNumberPrefixes($pdo) {
    ensureMembershipNumberPrefixesTable($pdo);
    $stmt = $pdo->query("SELECT id, code, description, display_order FROM membership_number_prefixes ORDER BY display_order, code");
    return $stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : [];
}

/**
 * Get next available number for a prefix (e.g. YMC -> 126 if max is 125)
 */
function getNextMembershipNumber($pdo, $prefixCode) {
    $prefixCode = trim($prefixCode);
    if ($prefixCode === '') return null;
    $len = strlen($prefixCode);
    $stmt = $pdo->prepare("
        SELECT MAX(CAST(SUBSTRING(membership_number, ? + 1) AS UNSIGNED))
        FROM members
        WHERE membership_number LIKE ? AND membership_number REGEXP ?
    ");
    $like = $prefixCode . '%';
    $regex = '^' . preg_quote($prefixCode, '/') . '[0-9]+$';
    $stmt->execute([$len, $like, $regex]);
    $max = $stmt->fetchColumn();
    $next = $max !== null ? (int)$max + 1 : 1;
    return $prefixCode . $next;
}
