<?php
// assets/includes/submit_cdfgrant.php
session_start();
require_once('../config/dbConnection.php');

header('Content-Type: application/json; charset=utf-8');

// DEBUG — set to 0 in production
ini_set('display_errors', 1);
error_reporting(E_ALL);

$DEV_TEMPLATE_LOCAL = '/mnt/data/YOUTH, WOMEN AND COMMUNITY EMPOWERMENT APPLICATION FORM.pdf';

function respond($data, $httpCode = 200) {
    http_response_code($httpCode);
    echo json_encode($data);
    exit;
}

function log_error($text) {
    $logDir = __DIR__ . '/../../logs';
    if (!is_dir($logDir)) @mkdir($logDir, 0775, true);
    $logFile = $logDir . '/grant_errors.log';
    @file_put_contents($logFile, '['.date('Y-m-d H:i:s')."] ".$text.PHP_EOL, FILE_APPEND);
}

try {
    // Initialize DB
    $db = DatabaseConnection::getInstance();
    $conn = $db->getConnection();
    if (!$conn || !($conn instanceof mysqli)) throw new Exception('DB connection required (mysqli)');

    // Confirm cdfgrants exists
    $tblOk = false;
    try {
        $tblRes = $conn->query("SHOW TABLES LIKE 'cdfgrants'");
        if ($tblRes && $tblRes->num_rows) $tblOk = true;
    } catch (Throwable $t) { /* ignore */ }
    if (!$tblOk) {
        $msg = "Table `cdfgrants` not found in current connection.";
        log_error($msg);
        respond(['status'=>'error','message'=>$msg], 500);
    }

    // ---------- Robust primary key discovery ----------
    $primaryKey = null;
    $primaryIsInt = false;
    try {
        // Try to find declared PRIMARY KEY first
        $pkRes = $conn->query("SHOW KEYS FROM cdfgrants WHERE Key_name = 'PRIMARY'");
        if ($pkRes && $pkRes->num_rows) {
            $pkRow = $pkRes->fetch_assoc();
            if (!empty($pkRow['Column_name'])) {
                $primaryKey = $pkRow['Column_name'];
            }
        }
    } catch (Throwable $t) { /* ignore */ }

    // If no PRIMARY found, inspect available columns and pick a safe fallback (prefer integer-like columns)
    if (empty($primaryKey)) {
        try {
            $colRes = $conn->query("SHOW COLUMNS FROM cdfgrants");
            $cols = [];
            $intCandidates = [];
            $firstAppNumber = false;
            if ($colRes) {
                while ($cr = $colRes->fetch_assoc()) {
                    $cols[] = $cr;
                    $field = $cr['Field'];
                    $type = strtolower($cr['Type']);
                    if (strpos($type, 'int') !== false || strpos($type, 'bigint') !== false || preg_match('/^int|^bigint|^tinyint|^smallint/', $type)) {
                        $intCandidates[] = $field;
                    }
                    if ($field === 'application_number') $firstAppNumber = true;
                }
            }
            // preferred fallbacks
            if (in_array('id', array_column($cols, 'Field'), true)) {
                $primaryKey = 'id';
            } elseif (!empty($intCandidates)) {
                // choose first integer-like column
                $primaryKey = $intCandidates[0];
            } elseif ($firstAppNumber) {
                $primaryKey = 'application_number';
            } elseif (!empty($cols)) {
                // choose the first column if nothing else
                $primaryKey = $cols[0]['Field'];
            } else {
                $primaryKey = 'application_number';
            }

            // determine if chosen PK is integer type
            foreach ($cols as $c) {
                if ($c['Field'] === $primaryKey) {
                    $t = strtolower($c['Type']);
                    if (strpos($t, 'int') !== false || strpos($t, 'bigint') !== false) $primaryIsInt = true;
                    break;
                }
            }
        } catch (Throwable $t) {
            $primaryKey = 'application_number';
            $primaryIsInt = false;
        }
    } else {
        // We found an explicit PRIMARY - determine if it's integer type
        try {
            $colRes = $conn->query("SHOW COLUMNS FROM cdfgrants LIKE '{$primaryKey}'");
            if ($colRes && $row = $colRes->fetch_assoc()) {
                $t = strtolower($row['Type']);
                if (strpos($t, 'int') !== false || strpos($t, 'bigint') !== false) $primaryIsInt = true;
            }
        } catch (Throwable $t) { /* ignore */ }
    }
    $primaryKey = (string)$primaryKey;
    log_error("Detected primary key for cdfgrants: {$primaryKey} (is_int: ".($primaryIsInt ? 'yes' : 'no').")");

    // helper
    $clean_input = function($s) {
        return is_string($s) ? trim(htmlspecialchars($s, ENT_QUOTES, 'UTF-8')) : $s;
    };

    // resolve grant id by id or application_number
    $resolveGrantId = function($grant_id, $application_number) use ($conn, $clean_input, $primaryKey, $primaryIsInt) {
        // If numeric grant_id provided, return it (only safe if PK is integer)
        $grant_id = (int)$grant_id;
        $application_number = trim((string)$application_number);
        if ($grant_id > 0 && $primaryIsInt) return $grant_id;
        if ($application_number === '') return 0;

        // Attempt to lookup the primary key value using application_number
        // We expect to return the primary key value (int if primaryIsInt, otherwise string)
        $sql = "SELECT `{$primaryKey}` FROM cdfgrants WHERE application_number = ? LIMIT 1";
        $q = $conn->prepare($sql);
        if (!$q) {
            throw new Exception('DB prepare failed in resolveGrantId: ' . $conn->error . ' SQL: ' . $sql);
        }
        $app = $clean_input($application_number);
        $q->bind_param('s', $app);
        if (!$q->execute()) {
            $err = $q->error;
            $q->close();
            throw new Exception('Execute failed in resolveGrantId: ' . $err . ' SQL: ' . $sql);
        }
        $q->bind_result($found);
        $q->fetch();
        $q->close();
        if ($found === null) return 0;
        return $primaryIsInt ? (int)$found : $found;
    };

    // generic upload helper
    $upload_file = function($fileField, $destDirName) {
        if (!isset($_FILES[$fileField]) || $_FILES[$fileField]['error'] === UPLOAD_ERR_NO_FILE) return null;
        $f = $_FILES[$fileField];
        if ($f['error'] !== UPLOAD_ERR_OK) return null;
        $base = realpath(__DIR__ . '/../../uploads/' . $destDirName);
        if ($base === false) {
            if (!mkdir(__DIR__ . "/../../uploads/{$destDirName}", 0775, true)) throw new Exception('Could not create upload folder');
            $base = realpath(__DIR__ . "/../../uploads/{$destDirName}");
        }
        $safe = time() . '_' . preg_replace('/[^A-Za-z0-9_\-\.]/', '_', basename($f['name']));
        $target = $base . DIRECTORY_SEPARATOR . $safe;
        if (!move_uploaded_file($f['tmp_name'], $target)) throw new Exception('Failed to move uploaded file');
        return 'uploads/' . $destDirName . '/' . $safe;
    };

    // ---------- MEMBER endpoints (list/add/delete) ----------
    $member_action = isset($_REQUEST['member_action']) ? strtolower(trim((string)$_REQUEST['member_action'])) : null;
    if ($member_action) {
        // LIST members
        if ($member_action === 'list') {
            $grant_id = isset($_REQUEST['grant_id']) ? (int)$_REQUEST['grant_id'] : 0;
            $appNo = isset($_REQUEST['application_number']) ? $clean_input($_REQUEST['application_number']) : '';
            $grant_id = $resolveGrantId($grant_id, $appNo);
            if (!$grant_id) respond(['status'=>'error','message'=>'grant_id or application_number required'], 400);

            // alias member_id -> id so front-end can use m.id
            $sql = "SELECT member_id AS id, grant_id, member_no, name, position, age, sex, nrc, person_with_disability, signature, created_at FROM grant_members WHERE grant_id = ? ORDER BY member_id ASC";
            $q = $conn->prepare($sql);
            if (!$q) {
                log_error("List prepare failed: ".$conn->error." SQL: ".$sql);
                respond(['status'=>'error','message'=>'DB prepare failed: '.$conn->error], 500);
            }
            // bind grant_id appropriately (most likely integer)
            if ($primaryIsInt) {
                $gid = (int)$grant_id;
                $q->bind_param('i', $gid);
            } else {
                // fallback: bind as string if for some reason grant_id is non-integer key
                $gid = (string)$grant_id;
                $q->bind_param('s', $gid);
            }
            $q->execute();
            $res = $q->get_result();
            $members = [];
            while ($r = $res->fetch_assoc()) $members[] = $r;
            $q->close();
            respond(['status'=>'success','members'=>$members]);
        }

        // ADD member
        if ($member_action === 'add') {
            if ($_SERVER['REQUEST_METHOD'] !== 'POST') respond(['status'=>'error','message'=>'POST required for add'], 405);

            try {
                $grant_id_in = isset($_POST['grant_id']) ? (int)$_POST['grant_id'] : 0;
                $appNo = isset($_POST['application_number']) ? $clean_input($_POST['application_number']) : '';
                $grant_id = $resolveGrantId($grant_id_in, $appNo);
                if (!$grant_id) respond(['status'=>'error','message'=>'grant_id or application_number required'], 400);

                $member_no = isset($_POST['member_no']) ? $clean_input($_POST['member_no']) : null;
                // convert member_no to int if numeric
                if ($member_no !== null && $member_no !== '') {
                    $member_no = is_numeric($member_no) ? (int)$member_no : null;
                } else {
                    $member_no = null;
                }
                $name = isset($_POST['name']) ? $clean_input($_POST['name']) : null;
                $position = isset($_POST['position']) ? $clean_input($_POST['position']) : null;
                $age = isset($_POST['age']) && $_POST['age'] !== '' ? (int)$_POST['age'] : null;
                $sex = isset($_POST['sex']) ? $clean_input($_POST['sex']) : null;
                $nrc = isset($_POST['nrc']) ? $clean_input($_POST['nrc']) : null;
                $pwd = (isset($_POST['person_with_disability']) && ($_POST['person_with_disability'] === '1' || $_POST['person_with_disability'] === 1 || $_POST['person_with_disability'] === 'on')) ? 1 : 0;

                if (!$name) respond(['status'=>'error','message'=>'Member name is required'], 400);

                $signaturePath = null;
                if (isset($_FILES['signature']) && $_FILES['signature']['error'] !== UPLOAD_ERR_NO_FILE) {
                    if ($_FILES['signature']['error'] !== UPLOAD_ERR_OK) {
                        $errMap = [
                            UPLOAD_ERR_INI_SIZE => 'The uploaded file exceeds the upload_max_filesize directive.',
                            UPLOAD_ERR_FORM_SIZE => 'The uploaded file exceeds the MAX_FILE_SIZE directive.',
                            UPLOAD_ERR_PARTIAL => 'The uploaded file was only partially uploaded.',
                            UPLOAD_ERR_NO_TMP_DIR => 'Missing a temporary folder on server.',
                            UPLOAD_ERR_CANT_WRITE => 'Failed to write file to disk.',
                            UPLOAD_ERR_EXTENSION => 'A PHP extension stopped the file upload.'
                        ];
                        $code = $_FILES['signature']['error'];
                        $msg = isset($errMap[$code]) ? $errMap[$code] : 'Unknown upload error code ' . $code;
                        respond(['status'=>'error','message'=>'Signature upload error: '.$msg], 400);
                    }
                    if ($_FILES['signature']['size'] > 5 * 1024 * 1024) {
                        respond(['status'=>'error','message'=>'Signature file too large (max 5MB).'], 400);
                    }
                    $signaturePath = $upload_file('signature', 'grant_members');
                }

                // Insert — note correct bind types and columns; member_id is auto-increment
                $sql = "INSERT INTO grant_members (grant_id, member_no, name, position, age, sex, nrc, person_with_disability, signature)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
                $stmt = $conn->prepare($sql);
                if (!$stmt) {
                    log_error("Member insert prepare failed: ".$conn->error." SQL: ".$sql);
                    respond(['status'=>'error','message'=>'DB prepare failed: '.$conn->error], 500);
                }

                // Determine bind types dynamically: grant_id might not be integer in some schemas, but we prefer integer.
                $grantBindType = $primaryIsInt ? 'i' : 's';
                // rest types: member_no (i), name (s), position (s), age (i), sex (s), nrc (s), pwd (i), signature (s)
                $types = $grantBindType . 'ississis'; // 9 parameters total
                $params = [
                    $grant_id,
                    $member_no,
                    $name,
                    $position,
                    $age,
                    $sex,
                    $nrc,
                    $pwd,
                    $signaturePath
                ];
                // ensure nulls remain null (bind_param requires references)
                foreach ($params as $k => $v) if ($v === null) $params[$k] = null;

                $refs = [];
                $refs[] = & $types;
                for ($i = 0; $i < count($params); $i++) $refs[] = & $params[$i];

                if (!call_user_func_array([$stmt, 'bind_param'], $refs)) {
                    log_error("Member bind_param failed: ".$stmt->error." SQL: ".$sql);
                    respond(['status'=>'error','message'=>'bind_param failed: '.$stmt->error], 500);
                }
                if (!$stmt->execute()) {
                    log_error("Member execute failed: ".$stmt->error." SQL: ".$sql);
                    respond(['status'=>'error','message'=>'Execute failed: '.$stmt->error], 500);
                }

                $newId = $stmt->insert_id; // this is member_id
                $stmt->close();

                // fetch inserted row and alias member_id -> id
                $q = $conn->prepare("SELECT member_id AS id, grant_id, member_no, name, position, age, sex, nrc, person_with_disability, signature, created_at FROM grant_members WHERE member_id = ? LIMIT 1");
                if (!$q) {
                    log_error("Member select after insert prepare failed: ".$conn->error);
                    respond(['status'=>'error','message'=>'DB prepare failed after insert: '.$conn->error], 500);
                }
                $q->bind_param('i', $newId);
                $q->execute();
                $res = $q->get_result();
                $member = $res->fetch_assoc();
                $q->close();

                // include application_number for convenience (lookup using cdfgrants PK)
                $appNo = '';
                $r = $conn->prepare("SELECT application_number FROM cdfgrants WHERE `{$primaryKey}` = ? LIMIT 1");
                if ($r) {
                    if ($primaryIsInt) {
                        $gidFetch = (int)$grant_id;
                        $r->bind_param('i', $gidFetch);
                    } else {
                        $gidFetch = (string)$grant_id;
                        $r->bind_param('s', $gidFetch);
                    }
                    $r->execute();
                    $r->bind_result($appNo);
                    $r->fetch();
                    $r->close();
                }

                respond(['status'=>'success','member'=>$member,'application_number'=>$appNo]);
            } catch (Exception $eAdd) {
                log_error('member_add_exception: '.$eAdd->getMessage());
                respond(['status'=>'error','message'=>'Member add failed: '.$eAdd->getMessage()], 400);
            }
        }

        // DELETE member
        if ($member_action === 'delete') {
            if ($_SERVER['REQUEST_METHOD'] !== 'POST') respond(['status'=>'error','message'=>'POST required for delete'], 405);
            $id = isset($_POST['id']) ? (int)$_POST['id'] : 0;
            if (!$id) respond(['status'=>'error','message'=>'id required'], 400);

            $sigPath = null;
            $q = $conn->prepare("SELECT signature FROM grant_members WHERE member_id = ? LIMIT 1");
            if ($q) {
                $q->bind_param('i', $id);
                $q->execute();
                $q->bind_result($sigPath);
                $q->fetch();
                $q->close();
            }

            $stmt = $conn->prepare("DELETE FROM grant_members WHERE member_id = ? LIMIT 1");
            if (!$stmt) {
                log_error("Member delete prepare failed: ".$conn->error);
                respond(['status'=>'error','message'=>'DB prepare failed: '.$conn->error], 500);
            }
            $stmt->bind_param('i', $id);
            if (!$stmt->execute()) {
                log_error("Member delete execute failed: ".$stmt->error);
                respond(['status'=>'error','message'=>'Delete failed: '.$stmt->error], 500);
            }
            $stmt->close();

            if (!empty($sigPath) && strpos($sigPath, 'uploads/grant_members/') === 0) {
                $fs = __DIR__ . '/../../' . $sigPath;
                if (is_file($fs)) @unlink($fs);
            }

            respond(['status'=>'success']);
        }

        respond(['status'=>'error','message'=>'Unknown member_action'], 400);
    }

    // ---------- No member_action: proceed with main grant submit logic ----------
    if ($_SERVER['REQUEST_METHOD'] !== 'POST') respond(['status'=>'error','message'=>'Invalid request method'], 405);

    // sanitize POST
    $clean = [];
    foreach ($_POST as $k => $v) {
        $clean[$k] = is_string($v) ? trim(htmlspecialchars($v, ENT_QUOTES, 'UTF-8')) : $v;
    }

    $saveDraft = (isset($clean['save_draft']) && ($clean['save_draft'] == '1' || $clean['save_draft'] === 1)) ? true : false;
    $clean['status'] = $saveDraft ? 'Draft' : 'Pending';

    if (empty($clean['application_number'])) {
        $clean['application_number'] = 'CDF-' . time() . '-' . substr(md5(uniqid('', true)), 0, 6);
    }
    $application_number = $clean['application_number'];

    // prepare upload folder for this application
    $uploadsBase = realpath(__DIR__ . '/../../uploads/cdfgrants');
    if ($uploadsBase === false) {
        if (!mkdir(__DIR__ . '/../../uploads/cdfgrants', 0775, true)) throw new Exception('Could not create uploads base');
        $uploadsBase = realpath(__DIR__ . '/../../uploads/cdfgrants');
    }
    $safeFolder = preg_replace('/[^A-Za-z0-9_\-]/', '_', $application_number);
    $appFolder = $uploadsBase . DIRECTORY_SEPARATOR . $safeFolder;
    if (!is_dir($appFolder) && !mkdir($appFolder, 0775, true)) throw new Exception('Could not create app folder');

    // grant attachment uploader
    $uploadGrantFile = function($field, $destDir) {
        if (!isset($_FILES[$field]) || $_FILES[$field]['error'] === UPLOAD_ERR_NO_FILE) return null;
        $f = $_FILES[$field];
        if ($f['error'] !== UPLOAD_ERR_OK) return null;
        $safe = time() . '_' . preg_replace('/[^A-Za-z0-9_\-\.]/', '_', basename($f['name']));
        $target = rtrim($destDir, DIRECTORY_SEPARATOR) . DIRECTORY_SEPARATOR . $safe;
        if (!move_uploaded_file($f['tmp_name'], $target)) return null;
        return 'uploads/cdfgrants/' . basename($destDir) . '/' . $safe;
    };

    $attachmentCols = [
        'attachment_registration_certificate',
        'attachment_nrcs_zip',
        'attachment_bank_proof',
        'attachment_tpIN_copy',
        'attachment_constitution',
        'attachment_minutes',
        'attachment_property_proof',
        'attachment_project_proposal'
    ];

    foreach ($attachmentCols as $col) {
        $uploaded = $uploadGrantFile($col, $appFolder);
        if ($uploaded !== null) $clean[$col] = $uploaded;
        else if (!array_key_exists($col, $clean)) $clean[$col] = null;
    }

    if (file_exists($DEV_TEMPLATE_LOCAL) && empty($clean['attachment_project_proposal'])) {
        $tplDest = $appFolder . DIRECTORY_SEPARATOR . 'form_template.pdf';
        @copy($DEV_TEMPLATE_LOCAL, $tplDest);
        if (file_exists($tplDest)) $clean['attachment_project_proposal'] = 'uploads/cdfgrants/' . basename($appFolder) . '/form_template.pdf';
    }

    // Columns exactly in DB order (exclude id, created_at, updated_at)
    $columns = [
        'application_number','date_of_application','province','district','constituency','ward','zone',
        'physical_address','gps_coordinates','land_tenure','group_name','registration_date',
        'registration_number','has_experience','experience_details','type_of_venture','sector',
        'project_identification_source','problem_statement','number_of_people_employed',
        'cross_cutting_issues','sustainability_plan','grant_amount_applied',
        'received_financial_assistance','assistance_institution','assistance_amount',
        'assistance_status','trained_entrepreneurship','trained_vocational','trained_savings',
        'trained_functional_literacy','trained_financial_literacy','training_members_count',
        'training_organisation','training_duration','bank_name','bank_branch','sort_branch_code',
        'swift_code','account_number','tpIN','mobile_money_wallet','ward_recommendation',
        'ward_reasons','ward_chairperson_name','ward_recommendation_date','cdf_decision',
        'cdf_reasons','cdf_chairperson_name','cdf_decision_date',
        'attachment_registration_certificate','attachment_nrcs_zip','attachment_bank_proof',
        'attachment_tpIN_copy','attachment_constitution','attachment_minutes',
        'attachment_property_proof','attachment_project_proposal','status'
    ];

    $values = [];
    foreach ($columns as $col) {
        $values[$col] = array_key_exists($col, $clean) ? $clean[$col] : null;
        if ($values[$col] === '') $values[$col] = null;
    }

    $intFields = [
        'has_experience','number_of_people_employed','received_financial_assistance',
        'trained_entrepreneurship','trained_vocational','trained_savings',
        'trained_functional_literacy','trained_financial_literacy','training_members_count'
    ];
    $decimalFields = ['grant_amount_applied','assistance_amount'];

    $types = '';
    $bindVals = [];
    foreach ($columns as $col) {
        $val = $values[$col];
        if (in_array($col, $intFields, true)) {
            $types .= 'i';
            $bindVals[] = ($val === null) ? null : (int)$val;
        } elseif (in_array($col, $decimalFields, true)) {
            $types .= 'd';
            $bindVals[] = ($val === null) ? null : (float)$val;
        } else {
            $types .= 's';
            $bindVals[] = ($val === null) ? null : (string)$val;
        }
    }

    // check if row exists for this application_number (use discovered primary key)
    $checkSql = "SELECT `{$primaryKey}` FROM cdfgrants WHERE application_number = ? LIMIT 1";
    $chkStmt = $conn->prepare($checkSql);
    if (!$chkStmt) {
        log_error("Check prepare failed: ".$conn->error." SQL: ".$checkSql);
        respond(['status'=>'error','message'=>'DB prepare failed: '.$conn->error], 500);
    }
    $chkStmt->bind_param('s', $application_number);
    if (!$chkStmt->execute()) {
        log_error("Check execute failed: ".$chkStmt->error);
        respond(['status'=>'error','message'=>'DB execute failed: '.$chkStmt->error], 500);
    }
    $chkStmt->bind_result($existingId);
    $chkStmt->fetch();
    $chkStmt->close();

    // Determine PK bind type for updates/inserts
    $pkType = $primaryIsInt ? 'i' : 's';

    if ($existingId && !$saveDraft) {
        $sets = [];
        foreach ($columns as $col) $sets[] = "`{$col}` = ?";
        $sql = "UPDATE cdfgrants SET " . implode(',', $sets) . " WHERE `{$primaryKey}` = ?";
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            log_error("Update prepare failed: ".$conn->error." SQL: ".$sql);
            respond(['status'=>'error','message'=>'DB prepare failed: '.$conn->error], 500);
        }

        $updateTypes = $types . $pkType;
        $bindParams = [];
        foreach ($bindVals as $v) $bindParams[] = $v;
        // append existingId, cast if integer PK expected
        $bindParams[] = $primaryIsInt ? (int)$existingId : (string)$existingId;

        $refs = [];
        $refs[] = & $updateTypes;
        for ($i = 0; $i < count($bindParams); $i++) $refs[] = & $bindParams[$i];

        if (!call_user_func_array([$stmt, 'bind_param'], $refs)) {
            log_error("Update bind_param failed: ".$stmt->error);
            respond(['status'=>'error','message'=>'bind_param failed: '.$stmt->error], 500);
        }
        if (!$stmt->execute()) {
            log_error("Update execute failed: ".$stmt->error);
            respond(['status'=>'error','message'=>'Execute failed: '.$stmt->error], 500);
        }
        $stmt->close();

        respond(['status'=>'success','message'=>'Draft finalized (updated).','id'=>$existingId,'application_number'=>$application_number]);
    }

    if ($existingId && $saveDraft) {
        $sets = [];
        foreach ($columns as $col) $sets[] = "`{$col}` = ?";
        $sql = "UPDATE cdfgrants SET " . implode(',', $sets) . " WHERE `{$primaryKey}` = ?";
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            log_error("Draft update prepare failed: ".$conn->error);
            respond(['status'=>'error','message'=>'DB prepare failed: '.$conn->error], 500);
        }

        $updateTypes = $types . $pkType;
        $bindParams = [];
        foreach ($bindVals as $v) $bindParams[] = $v;
        $bindParams[] = $primaryIsInt ? (int)$existingId : (string)$existingId;

        $refs = [];
        $refs[] = & $updateTypes;
        for ($i = 0; $i < count($bindParams); $i++) $refs[] = & $bindParams[$i];

        if (!call_user_func_array([$stmt, 'bind_param'], $refs)) {
            log_error("Draft bind_param failed: ".$stmt->error);
            respond(['status'=>'error','message'=>'bind_param failed: '.$stmt->error], 500);
        }
        if (!$stmt->execute()) {
            log_error("Draft execute failed: ".$stmt->error);
            respond(['status'=>'error','message'=>'Execute failed: '.$stmt->error], 500);
        }
        $stmt->close();

        respond(['status'=>'success','message'=>'Draft saved.','id'=>$existingId,'application_number'=>$application_number]);
    }

    if (!$existingId) {
        $placeholders = implode(',', array_fill(0, count($columns), '?'));
        $colList = implode(',', array_map(function($c){ return "`{$c}`"; }, $columns));
        $sql = "INSERT INTO cdfgrants ({$colList}) VALUES ({$placeholders})";
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            log_error("Insert prepare failed: ".$conn->error." SQL: ".$sql);
            respond(['status'=>'error','message'=>'Prepare failed: '.$conn->error], 500);
        }

        $refs = [];
        $refs[] = & $types;
        for ($i = 0; $i < count($bindVals); $i++) $refs[] = & $bindVals[$i];

        if (!call_user_func_array([$stmt, 'bind_param'], $refs)) {
            log_error("Insert bind_param failed: ".$stmt->error);
            respond(['status'=>'error','message'=>'bind_param failed: '.$stmt->error], 500);
        }

        if (!$stmt->execute()) {
            log_error("Insert execute failed: ".$stmt->error);
            respond(['status'=>'error','message'=>'Execute failed: '.$stmt->error], 500);
        }

        $newId = $stmt->insert_id;
        $stmt->close();

        respond(['status'=>'success','message'=> $saveDraft ? 'Draft created.' : 'Application saved.', 'id'=>$newId,'application_number'=>$application_number]);
    }

    respond(['status'=>'error','message'=>'Unhandled path'], 500);

} catch (Exception $ex) {
    log_error('exception: '.$ex->getMessage());
    respond(['status'=>'error','message'=>$ex->getMessage()], 500);
}
