Страна за трансакции

Креирање договор

Првенствено, за креирање на договор имавме

pool.query(
            "INSERT INTO agreement (A_Id, Price, Status, Datum, Tax_Nr, VIN, EMBG) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *",
            [A_Id, Price, Status, Datum, Tax_Nr, VIN, EMBG],
            (error, results) => {
                if (error) {
                    reject(new Error("Not able to create agreement."));
                }
                if (results && results.rows) {
                    // Update vehicle status to sold
                    pool.query(
                        "UPDATE vehicle SET status = false WHERE vin = $1",
                        [VIN],
                        (updateError, updateResults) => {
                            if (updateError) {
                                console.error("Error updating vehicle status:", updateError);
                            }
                        }
                    );
                    resolve(`Agreement created successfully.`);
                } else {
                    reject(new Error("Not able to create agreement."));
                }
            }
        );

Меѓутоа сега со трансакции за да се осигураме дека возилото постои, возилото е достапно, и да се постави како продадено и тргне од продажба доколку е креиран договорот успешно имаме:

pool.connect((err, client, done) => {
            if (err) {
                console.error('Error getting client from pool:', err);
                reject(err);
                return;
            }

            client.query('BEGIN', (beginError) => {
                if (beginError) {
                    console.error('Begin transaction error:', beginError);
                    done();
                    reject(beginError);
                    return;
                }

                // First, validate that the vehicle exists and is available
                client.query(
                    "SELECT vin, status, price FROM vehicle WHERE vin = $1 AND tax_nr = $2",
                    [VIN, Tax_Nr],
                    (validateError, validateResults) => {
                        if (validateError) {
                            console.error('Vehicle validation error:', validateError);
                            client.query('ROLLBACK', () => {
                                done();
                            });
                            reject(new Error("Error validating vehicle."));
                            return;
                        }

                        if (!validateResults.rows || validateResults.rows.length === 0) {
                            console.error('Vehicle not found or not owned by dealership');
                            client.query('ROLLBACK', () => {
                                done();
                            });
                            reject(new Error("Vehicle not found or you don't have permission to sell it."));
                            return;
                        }

                        const vehicle = validateResults.rows[0];
                        if (vehicle.status === false) {
                            console.error('Vehicle is already sold');
                            client.query('ROLLBACK', () => {
                                done();
                            });
                            reject(new Error("Vehicle is already sold."));
                            return;
                        }

                        // Validate that the agreement price matches vehicle price
                        if (parseFloat(Price) !== parseFloat(vehicle.price)) {
                            console.error('Agreement price does not match vehicle price');
                            client.query('ROLLBACK', () => {
                                done();
                            });
                            reject(new Error("Agreement price must match vehicle price."));
                            return;
                        }

                        // Insert agreement
                        client.query(
                            "INSERT INTO agreement (A_Id, Price, Status, Datum, Tax_Nr, VIN, EMBG) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *",
                            [A_Id, Price, Status, Datum, Tax_Nr, VIN, EMBG],
                            (error, results) => {
                                if (error) {
                                    console.error('Agreement insert error:', error);
                                    client.query('ROLLBACK', () => {
                                        done();
                                    });
                                    reject(new Error("Not able to create agreement."));
                                    return;
                                }

                                if (results && results.rows) {
                                    console.log('Agreement inserted successfully');
                                    
                                    // Update vehicle status to sold
                                    client.query(
                                        "UPDATE vehicle SET status = false WHERE vin = $1",
                                        [VIN],
                                        (updateError, updateResults) => {
                                            if (updateError) {
                                                console.error('Vehicle update error:', updateError);
                                                client.query('ROLLBACK', () => {
                                                    done();
                                                });
                                                reject(new Error("Error updating vehicle status."));
                                                return;
                                            }

                                            console.log('Vehicle status updated successfully');
                                            
                                            // Commit the transaction
                                            client.query('COMMIT', (commitError) => {
                                                done();
                                                if (commitError) {
                                                    console.error('Commit error:', commitError);
                                                    reject(commitError);
                                                } else {
                                                    console.log('Agreement transaction committed successfully');
                                                    resolve(`Agreement created successfully.`);
                                                }
                                            });
                                        }
                                    );
                                } else {
                                    client.query('ROLLBACK', () => {
                                        done();
                                    });
                                    reject(new Error("Not able to create agreement."));
                                }
                            }
                        );
                    }
                );
            });
        });

Уплата

Исто како и за Договор, претходно во Уплатата имавме:

pool.query(
            "INSERT INTO payment (Bank, IBAN, Amount, EMBG, A_Id) VALUES ($1, $2, $3, $4, $5) RETURNING *",
            [Bank, IBAN, Amount, EMBG, A_Id],
            (error, results) => {
                if (error) {
                    reject(new Error("Not able to create payment."));
                }
                if (results && results.rows) {
                    resolve(`Payment created successfully.`);
                } else {
                    reject(new Error("Not able to create payment."));
                }
            }
        );

Сега со трансакции се осигуруваме дали постои договор и дали точниот клиент е асоциран со него, дали е валиден договорот, проверуваме да не случано веќе е извршено уплаќање за него, и се означува како платен доколку е успешно извршена уплата.

pool.connect((err, client, done) => {
            if (err) {
                console.error('Error getting client from pool:', err);
                reject(err);
                return;
            }

            client.query('BEGIN', (beginError) => {
                if (beginError) {
                    console.error('Begin transaction error:', beginError);
                    done();
                    reject(beginError);
                    return;
                }

                // First, validate that the agreement exists and belongs to the client
                client.query(
                    `SELECT a.a_id, a.price as agreement_price, a.status as agreement_status,
                            v.vin, v.make, v.model, v.p_year, v.color,
                            c.c_name, c.embg
                     FROM agreement a
                     JOIN vehicle v ON a.vin = v.vin
                     JOIN client c ON a.embg = c.embg
                     WHERE a.a_id = $1 AND a.embg = $2`,
                    [A_Id, EMBG],
                    (validateError, validateResults) => {
                        if (validateError) {
                            console.error('Agreement validation error:', validateError);
                            client.query('ROLLBACK', () => {
                                done();
                            });
                            reject(new Error("Error validating agreement."));
                            return;
                        }

                        if (!validateResults.rows || validateResults.rows.length === 0) {
                            console.error('Agreement not found or not owned by client');
                            client.query('ROLLBACK', () => {
                                done();
                            });
                            reject(new Error("Agreement not found or you don't have permission to pay for it."));
                            return;
                        }

                        const agreement = validateResults.rows[0];
                        
                        // Validate that the agreement is active
                        if (agreement.agreement_status !== true) {
                            console.error('Agreement is not active');
                            client.query('ROLLBACK', () => {
                                done();
                            });
                            reject(new Error("Agreement is not active."));
                            return;
                        }

                        // Validate that the payment amount matches agreement price
                        if (parseFloat(Amount) !== parseFloat(agreement.agreement_price)) {
                            console.error('Payment amount does not match agreement price');
                            client.query('ROLLBACK', () => {
                                done();
                            });
                            reject(new Error("Payment amount must match agreement price."));
                            return;
                        }

                        // Check if payment already exists for this agreement
                        client.query(
                            "SELECT p_id FROM payment WHERE a_id = $1",
                            [A_Id],
                            (checkError, checkResults) => {
                                if (checkError) {
                                    console.error('Payment check error:', checkError);
                                    client.query('ROLLBACK', () => {
                                        done();
                                    });
                                    reject(new Error("Error checking existing payments."));
                                    return;
                                }

                                if (checkResults.rows && checkResults.rows.length > 0) {
                                    console.error('Payment already exists for this agreement');
                                    client.query('ROLLBACK', () => {
                                        done();
                                    });
                                    reject(new Error("Payment already exists for this agreement."));
                                    return;
                                }

                                // Insert payment
                                client.query(
                                    "INSERT INTO payment (Bank, IBAN, Amount, EMBG, A_Id) VALUES ($1, $2, $3, $4, $5) RETURNING *",
                                    [Bank, IBAN, Amount, EMBG, A_Id],
                                    (error, results) => {
                                        if (error) {
                                            console.error('Payment insert error:', error);
                                            client.query('ROLLBACK', () => {
                                                done();
                                            });
                                            reject(new Error("Not able to create payment."));
                                            return;
                                        }

                                        if (results && results.rows) {
                                            console.log('Payment inserted successfully');
                                            
                                            // Optionally update agreement status to paid
                                            client.query(
                                                "UPDATE agreement SET status = false WHERE a_id = $1",
                                                [A_Id],
                                                (updateError, updateResults) => {
                                                    if (updateError) {
                                                        console.error('Agreement status update error:', updateError);
                                                        // Don't fail the transaction for this, just log it
                                                        console.log('Payment created but agreement status not updated');
                                                    } else {
                                                        console.log('Agreement status updated to paid');
                                                    }

                                                    // Commit the transaction
                                                    client.query('COMMIT', (commitError) => {
                                                        done();
                                                        if (commitError) {
                                                            console.error('Commit error:', commitError);
                                                            reject(commitError);
                                                        } else {
                                                            console.log('Payment transaction committed successfully');
                                                            resolve(`Payment created successfully.`);
                                                        }
                                                    });
                                                }
                                            );
                                        } else {
                                            client.query('ROLLBACK', () => {
                                                done();
                                            });
                                            reject(new Error("Not able to create payment."));
                                        }
                                    }
                                );
                            }
                        );
                    }
                );
            });
        });