<?php
if ($_SERVER["REQUEST_METHOD"] === "POST") {

    if (isset($_POST['data'])) {
        // Initialize variables
        $db2Path = 'database/erfmapping.db';

        // Get the data as a concatenated string
        $dataString = $_POST['data'];
        $dataParts = explode('«', $dataString);

        // Extract individual values from the array
        $erfno = $dataParts[0];
        
        $allotment = $dataParts[1];
        $allotment = str_replace('|', ' ', $allotment);

        $projectname = $dataParts[2];
        $projectname = str_replace('|', ' ', $projectname);
        
        
        $TAG_X = $dataParts[3];
        $TAG_Y = $dataParts[4];

        try {
            // Connect to the erfmapping.db
            $db2 = new PDO("sqlite:" . $db2Path);
            $db2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            // Prepare a query to check if the entry already exists in erfnumbers
            $queryCheck = "SELECT COUNT(*) FROM erfnumbers WHERE TAG_X = :TAG_X AND TAG_Y = :TAG_Y";
            $stmtCheck = $db2->prepare($queryCheck);
            $stmtCheck->bindParam(':TAG_X', $TAG_X);
            $stmtCheck->bindParam(':TAG_Y', $TAG_Y);
            $stmtCheck->execute();
            $exists = $stmtCheck->fetchColumn();

            // If the entry does not exist, insert the new entry
            if ($exists == 0) {
                $queryInsert = "INSERT INTO erfnumbers (erfno, allotment, projectname, TAG_X, TAG_Y) VALUES (:erfno, :allotment, :projectname, :TAG_X, :TAG_Y)";
                $stmtInsert = $db2->prepare($queryInsert);
                $stmtInsert->bindParam(':erfno', $erfno);
                $stmtInsert->bindParam(':allotment', $allotment);
                $stmtInsert->bindParam(':projectname', $projectname);
                $stmtInsert->bindParam(':TAG_X', $TAG_X);
                $stmtInsert->bindParam(':TAG_Y', $TAG_Y);
                $stmtInsert->execute();

                // Return success response
                echo json_encode(['status' => 'success', 'message' => 'New entry added successfully.']);
            } else {
                // Return entry already exists response
                echo json_encode(['status' => 'exists', 'message' => 'Entry already exists for Erfno: ' . $erfno . ' and Allotment: ' . $allotment]);
            }

        } catch (PDOException $e) {
            // Return error response in case of database error
            echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
        }
    }
}
?>