<?php
session_start(['read_and_close' => true]);
require_once 'db.php';
if (!isset($_SESSION['co_id'])) {
    echo json_encode(["status" => "error", "message" => "Unauthorized"]);
    exit;
}

//$coid  = $_SESSION['co_id'];

// Check if the form is submitted
if ($_SERVER["REQUEST_METHOD"] === "POST") {

    // Function to sanitize input
    function sanitize_input($data) {
        return htmlspecialchars(trim($data), ENT_QUOTES, 'UTF-8');
    }

    // Sanitize single-value form inputs
    $field1 = sanitize_input($_POST["field1"] ?? '');
    $field2 = sanitize_input($_POST["field2"] ?? '');
    $field3 = sanitize_input($_POST["field3"] ?? '');
    
    $field4 = sanitize_input($_POST["field4"] ?? '');
    $field5 = sanitize_input($_POST["field5"] ?? '');
    $user_location = sanitize_input($_POST["whm"] ?? '');
    $store_location = sanitize_input($_POST["store"] ?? '');
    $coid = sanitize_input($_POST["comp_id"] ?? '');

    // Sanitize array inputs (column values)
    $column1 = array_map('sanitize_input', $_POST["column1"] ?? []);
    $column2 = array_map('sanitize_input', $_POST["column2"] ?? []);
    $column7 = array_map('sanitize_input', $_POST["column7"] ?? []);
    $column8 = array_map('sanitize_input', $_POST["column8"] ?? []);
    $column9 = array_map('sanitize_input', $_POST["column9"] ?? []);
    
    $col33 = array_map('sanitize_input', $_POST["column33"] ?? []);
    $col44 = array_map('sanitize_input', $_POST["column44"] ?? []);
    $col55 = array_map('sanitize_input', $_POST["column55"] ?? []);
    $col66 = array_map('sanitize_input', $_POST["column66"] ?? []);
    $col77 = array_map('sanitize_input', $_POST["column77"] ?? []);
    $col100 = array_map('sanitize_input', $_POST["column100"] ?? []);
    $col1110 = array_map('sanitize_input', $_POST["column1110"] ?? []);
    
    

    // Constants
    $copy1 = "O";
    $copy2 = "D";
    $sub_date = date("Y-m-d H:i:s");
    $status = "Pending";
    
    //require_once "db.php"; // Ensure this initializes $pdo

    // Ensure column1 and column2 have the same length
    if (!is_array($column1) || !is_array($column2) || count($column1) !== count($column2)) {
        die("Invalid input data. Please check your form submission.");
    }
    
    if ($field4==='DEL') {

    try {
        

        // Begin transaction
        $pdo->beginTransaction();

        // Prepare the INSERT queries
        $stmt = $pdo->prepare("INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_credit, t_status, t_sub_date, t_comm, t_margin, t_sale_vat, t_prch_vat, t_sku, t_prch_px, t_entry_by) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?, ?)");

        $stmt2 = $pdo->prepare("INSERT INTO transactions 
            (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_debit, t_status, t_sub_date, t_entry_by) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            
        $stockStmt = $pdo->prepare("
        INSERT INTO stock_balance (co_id, item_id, warehouse_id, qty, last_update)
        VALUES (?, ?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE qty = qty - VALUES(qty), last_update = NOW() ");

        // Loop through items and check before inserting
       // Insert records into the database
       
               $rowCount = count($column1);

        for ($i = 0; $i < $rowCount; $i++) {
                $stmt->execute([
                    $field1, $coid, $user_location, $field2, $copy1, $field4, $field3, 
                    $field3, $column1[$i], $column2[$i], $col33[$i] ?? 0, 
                    $status, $sub_date, $col55[$i] ?? null, $col66[$i] ?? null, $col100[$i] ?? null, $col1110[$i] ?? null, $col77[$i] ?? null, $col44[$i] ?? null, $field5
                ]);
     

     
                $stmt2->execute([
                    $field1,$coid, $user_location, $field2, $copy2, $field4, $field3, 
                    $user_location, $column1[$i], $column2[$i], $col33[$i] ?? 0, 
                    $status, $sub_date, $field5
                ]);
                
                $stockStmt->execute([
                                        $coid,
                                        $column1[$i],     // item id
                                        $user_location,   // warehouse
                                        $column2[$i]      // quantity
                                    ]);
        }


       
        

        // Execute another commission-based transaction
       

        // Commit transaction
        $pdo->commit();

        //echo "Data inserted successfully.";
        echo json_encode(["status" => "success"]);

    } catch (PDOException $e) {
        $pdo->rollBack(); // Rollback in case of failure
        error_log("Database error: " . $e->getMessage()); // Log error
        echo "An error occurred while processing your request.";
    }
    }
    
if ($field4 === 'GR') {

    try {

        $pdo->beginTransaction();

        $stmt = $pdo->prepare("
        INSERT INTO transactions 
        (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, 
        t_item, t_qty, t_debit, t_status, t_sub_date, t_comm, t_margin, 
        t_sale_vat, t_prch_vat, t_sku, t_prch_px, t_entry_by) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");

        $stmt2 = $pdo->prepare("
        INSERT INTO transactions 
        (t_date, co_id, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, 
        t_item, t_qty, t_credit, t_status, t_sub_date, t_entry_by) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        
        $stockStmt = $pdo->prepare("
        INSERT INTO stock_balance (co_id, item_id, warehouse_id, qty, last_update)
        VALUES (?, ?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty), last_update = NOW() ");


        $rowCount = count($column1);

        for ($i = 0; $i < $rowCount; $i++) {

            $stmt->execute([
                $field1,
                $coid,
                $user_location,
                $field2,
                $copy1,
                $field4,
                $field3,
                $field3,
                $column1[$i],
                $column2[$i],
                $col33[$i] ?? 0,
                $status,
                $sub_date,
                $col55[$i] ?? null,
                $col66[$i] ?? null,
                $col100[$i] ?? null,
                $col1110[$i] ?? null,
                $col77[$i] ?? null,
                $col44[$i] ?? null,
                $field5
            ]);

            $stmt2->execute([
                $field1,
                $coid,
                $user_location,
                $field2,
                $copy2,
                $field4,
                $field3,
                $user_location,
                $column1[$i],
                $column2[$i],
                $col33[$i] ?? 0,
                $status,
                $sub_date,
                $field5
            ]);
            
            $stockStmt->execute([
                                $coid,
                                $column1[$i],     // item id
                                $user_location,   // warehouse
                                $column2[$i]      // quantity
                            ]);
        }

        $pdo->commit();

        echo "Data inserted successfully.";

    } catch (PDOException $e) {

        $pdo->rollBack();
        error_log("Database error: " . $e->getMessage());
        echo "An error occurred while processing your request.";
    }
}
    
    
        if ($field4==='STFR') {

    try {
        $field31 = sanitize_input($_POST["field31"] ?? '');
         $driver = sanitize_input($_POST["driver"] ?? '');

        // Begin transaction
        $pdo->beginTransaction();

        // Prepare the SELECT query to check if data exists
        $checkStmt = $pdo->prepare("SELECT COUNT(*) FROM transactions WHERE t_doc_no = ? AND t_cr = ?  AND t_type = 'STFR'");
         $checkStmt->execute([$field2,  $user_location]);
        $transactionExists = $checkStmt->fetchColumn();

        if ($transactionExists > 0) {
            die("Error: Transaction with Document No. $field2 already exists.");
        }

        // Prepare the INSERT queries
        $stmt = $pdo->prepare("INSERT INTO transactions 
            (t_date, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_credit, t_status, t_sub_date, t_comm, t_margin, t_entry_by, t_narration) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)");

        $stmt2 = $pdo->prepare("INSERT INTO transactions 
            (t_date, t_tt_doc, t_doc_no, t_copy, t_type, t_ct_det, t_cr, t_item, t_qty, t_debit, t_status, t_sub_date, t_entry_by, t_narration) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)");

        // Loop through items and check before inserting
       // Insert records into the database
       for ($i = 0; $i < count($column1); $i++) {
                $stmt->execute([
                    $field1, $user_location, $field2, $copy1, $field4, $user_location, 
                    $field31, $column1[$i], $column2[$i], $column7[$i] ?? 0, 
                    $status, $sub_date, $column8[$i] ?? null, $column9[$i] ?? null, $field5, $driver
                ]);
     }

        for ($i = 0; $i < count($column1); $i++) {
                $stmt2->execute([
                    $field1, $user_location, $field2, $copy2, $field4, $field31, 
                    $user_location, $column1[$i], $column2[$i], $column7[$i] ?? 0, 
                    $status, $sub_date, $field5, $driver
                ]);
        }

       // Execute the additional SQL statement 
        // Update locations  UPDATE transactions t JOIN accounts u ON u.acc_id=t.t_tt_doc SET t.co_id=u.co_id, t.t_tt_doc=u.br_id WHERE t.t_doc_no = ? AND t.t_type = ? 
        $stmt2_1 = $pdo->prepare("
             UPDATE transactions t JOIN accounts u ON u.acc_id=t.t_cr SET t.co_id=u.co_id, t.t_tt_doc=u.br_id WHERE t.t_doc_no = ? AND t.t_type = ? 
        ");
        $stmt2_1->execute([$field2, $field4]);
        
        // Update quantities
        $stmt3 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_credit=t.t_qty*p.p_sale_px
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='O'
        ");
       // $stmt3->execute([$field2, $field4]);
        
        $stmt4 = $pdo->prepare("
            UPDATE transactions t JOIN products p ON p.id=t.t_item SET t.t_debit=t.t_qty*p.p_sale_px
            WHERE t.t_doc_no = ? AND t.t_type = ? AND t.t_copy='D'
        ");
       // $stmt4->execute([$field2, $field4]);
       
        

        // Execute another commission-based transaction
       

        // Commit transaction
        $pdo->commit();

        echo "Data inserted successfully.";

    } catch (PDOException $e) {
        $pdo->rollBack(); // Rollback in case of failure
        error_log("Database error: " . $e->getMessage()); // Log error
        echo "An error occurred while processing your request.";
    }
    }
}
?>
