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

// Enable error reporting for development (comment out for production)
ini_set('display_errors', 1);
error_reporting(E_ALL);

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    try {
        $db = DatabaseConnection::getInstance();
        $conn = $db->getConnection();

        // ✅ 1. Sanitize all POST data
        $clean = [];
        foreach ($_POST as $key => $value) {
            $clean[$key] = htmlspecialchars(trim($value), ENT_QUOTES, 'UTF-8');
        }

        // ✅ 2. Check if `contact_person_nrc` already exists
        $nrc = isset($clean['contact_person_nrc']) ? $clean['contact_person_nrc'] : '';  // Check if NRC exists
        
        if (empty($nrc)) {
            echo json_encode(['status' => 'error', 'message' => 'NRC is required.']);
            exit;
        }

        // Prepare query to check if the NRC exists
        $checkQuery = "SELECT COUNT(*) FROM grants WHERE contact_person_nrc = ?";
        $stmt = $conn->prepare($checkQuery);
        if (!$stmt) {
            throw new Exception("Prepare failed: " . $conn->error);
        }

        // Bind the NRC parameter and execute
        $stmt->bind_param("s", $nrc);
        $stmt->execute();
        
        // Fetch the result and free the result set to avoid "commands out of sync" error
        $stmt->bind_result($count);
        $stmt->fetch();
        $stmt->free_result(); // Free the result set after fetching
        
        if ($count > 0) {
            // If NRC already exists, throw an error message
            echo json_encode(['status' => 'error', 'message' => 'This NRC already exists in the database. Please check the NRC and try again.']);
            $stmt->close();
            $conn->close();
            exit;  // Stop the script if the NRC exists
        }

        // ✅ 3. Prepare Insert into `grants` table
        $status = "Pending"; // Default value
        $sql = "INSERT INTO grants (
            application_number, name_of_group, type_of_group, male_members, female_members, youth_members, total_members,
            differently_abled_male, differently_abled_female, registration_body, registration_no, province, district,
            constituency, ward, zone, contact_person_name, contact_person_nrc, contact_person_phone_primary,
            type_of_venture, sector, jobs_pledged, year_of_funding, amount_requested, account,
            amount_recommended_by_cdfc, amount_approved_by_plgo, date_of_approval, disbursement_date, remarks, status
        ) VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )";

        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception("Prepare failed: " . $conn->error);
        }

        // ✅ 4. Bind parameters for the prepared statement
        $stmt->bind_param(
            "sssssssssssssssssssssssssssssss",
            $clean['application_number'],
            $clean['name_of_group'],
            $clean['type_of_group'],
            $clean['male_members'],
            $clean['female_members'],
            $clean['youth_members'],
            $clean['total_members'],
            $clean['differently_abled_male'],
            $clean['differently_abled_female'],
            $clean['registration_body'],
            $clean['registration_no'],
            $clean['province'],
            $clean['district'],
            $clean['constituency'],
            $clean['ward'],
            $clean['zone'],
            $clean['contact_person_name'],
            $clean['contact_person_nrc'],
            $clean['contact_person_phone_primary'],
            $clean['type_of_venture'],
            $clean['sector'],
            $clean['jobs_pledged'],
            $clean['year_of_funding'],
            $clean['amount_requested'],
            $clean['account'],
            $clean['amount_recommended_by_cdfc'],
            $clean['amount_approved_by_plgo'],
            $clean['date_of_approval'],
            $clean['disbursement_date'],
            $clean['remarks'],
            $status
        );

        // ✅ 5. Execute the insert statement
        if (!$stmt->execute()) {
            throw new Exception("Execution failed: " . $stmt->error);
        }

        // ✅ 6. Check if the insertion was successful
        echo json_encode(['status' => 'success', 'message' => 'Grant saved successfully!']);
        
        // ✅ 7. Close the statement and connection
        $stmt->close();
        $conn->close();

    } catch (Exception $e) {
        // Log the error to the PHP error log
        error_log('Error: ' . $e->getMessage()); // Logs to the PHP error log

        // Return a response indicating the error occurred
        http_response_code(500); // Internal Server Error
        echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
    }
} else {
    echo json_encode(['status' => 'error', 'message' => 'Invalid request method']);
}
?>
