psp_api/prisma/triggers/stored_procedures.sql

-- Stored Procedures equivalent to triggers

DELIMITER / /

-- Procedure for trg_bank_account_transaction_after_insert
CREATE PROCEDURE update_bank_balance(IN p_bankAccountId INT, IN p_type VARCHAR(20), IN p_amount DECIMAL(15,2))
BEGIN
  START TRANSACTION;
  IF p_type = 'DEPOSIT' THEN
    UPDATE Bank_Account_Balance SET balance = balance + p_amount WHERE bankAccountId = p_bankAccountId;
  ELSEIF p_type = 'WITHDRAWAL' THEN
    UPDATE Bank_Account_Balance SET balance = balance - p_amount WHERE bankAccountId = p_bankAccountId;
  END IF;
  COMMIT;
END //

-- Procedure for trg_bank_account_transaction_after_delete
CREATE PROCEDURE update_bank_balance_on_delete(IN p_bankAccountId INT, IN p_amount DECIMAL(15,2))
BEGIN
  START TRANSACTION;
  UPDATE Bank_Accounts SET balance = balance - p_amount WHERE id = p_bankAccountId;
  COMMIT;
END //

-- Procedure for trg_transfer_item_after_insert
CREATE PROCEDURE process_transfer_item(IN p_transferId INT, IN p_productId INT, IN p_count DECIMAL(10,2))
BEGIN
  START TRANSACTION;
  DECLARE fromInv INT;
  DECLARE toInv INT;
  DECLARE _avgCost DECIMAL(10,2);
  DECLARE latestQuantityInOrigin DECIMAL(10,2);
  DECLARE latestQuantityInDestination DECIMAL(10,2);

  SELECT fromInventoryId, toInventoryId INTO fromInv, toInv
  FROM Inventory_Transfers WHERE id = p_transferId;

  SELECT COALESCE(avgCost, 0), COALESCE(quantity, 0) INTO _avgCost, latestQuantityInOrigin FROM Stock_Balance
  WHERE ProductId = p_productId AND inventoryId = fromInv LIMIT 1;

  SELECT COALESCE(quantity, 0) INTO latestQuantityInDestination FROM Stock_Balance
  WHERE ProductId = p_productId AND inventoryId = toInv LIMIT 1;

  -- OUT from source
  INSERT INTO Stock_Movements
  (type, quantity, unitPrice, totalCost, avgCost, referenceType, referenceId, productId, inventoryId, counterInventoryId, createdAt, remainedInStock)
  VALUES
      ('OUT', p_count, _avgCost, _avgCost*p_count, _avgCost, 'INVENTORY_TRANSFER', p_transferId, p_productId, fromInv, toInv, NOW(), latestQuantityInOrigin-p_count);

  -- IN to destination
  INSERT INTO Stock_Movements
  (type, quantity, unitPrice, totalCost, avgCost, referenceType, referenceId, productId, inventoryId, counterInventoryId, createdAt, remainedInStock)
  VALUES
      ('IN', p_count, _avgCost, _avgCost*p_count, _avgCost, 'INVENTORY_TRANSFER', p_transferId, p_productId, toInv, fromInv, NOW(), latestQuantityInOrigin-p_count);
  COMMIT;
END //

-- Procedure for trg_order_item_after_insert
CREATE PROCEDURE update_stock_reservation_insert(IN p_orderId INT, IN p_productId INT, IN p_quantity DECIMAL(10,2))
BEGIN
  START TRANSACTION;
  UPDATE Stock_Reservations SET quantity = quantity + p_quantity
  WHERE orderId = p_orderId AND productId = p_productId;
  COMMIT;
END //

-- Procedure for trg_order_item_after_update
CREATE PROCEDURE update_stock_reservation_update(IN p_orderId INT, IN p_productId INT, IN p_old_quantity DECIMAL(10,2), IN p_new_quantity DECIMAL(10,2))
BEGIN
  START TRANSACTION;
  UPDATE Stock_Reservations
  SET quantity = quantity - p_old_quantity + p_new_quantity
  WHERE orderId = p_orderId AND productId = p_productId;
  COMMIT;
END //

-- Procedure for trg_order_item_after_delete
CREATE PROCEDURE update_stock_reservation_delete(IN p_orderId INT, IN p_productId INT, IN p_quantity DECIMAL(10,2))
BEGIN
  START TRANSACTION;
  UPDATE Stock_Reservations SET quantity = quantity - p_quantity
  WHERE orderId = p_orderId AND productId = p_productId;
  COMMIT;
END //

-- Procedure for trg_order_after_cancel
CREATE PROCEDURE cancel_order_stock(IN p_orderId INT, IN p_status VARCHAR(20))
BEGIN
  START TRANSACTION;
  IF p_status = 'CANCELED' OR p_status = 'REJECTED' OR p_status = 'DONE' THEN
    UPDATE Stock_Reservations sr SET quantity = 0
    WHERE sr.orderId = p_orderId;
  END IF;
  COMMIT;
END //

-- Procedure for trg_purchase_receipt_item_after_insert
CREATE PROCEDURE process_purchase_item(IN p_receiptId INT, IN p_productId INT, IN p_count DECIMAL(10,2), IN p_unitPrice DECIMAL(15,2), IN p_totalAmount DECIMAL(15,2))
BEGIN
  START TRANSACTION;
  DECLARE latestQuantity DECIMAL(10, 2) DEFAULT 0;
  DECLARE invId INT;
  DECLARE suppId INT;

  -- Get inventory & supplier from
  SELECT inventoryId, supplierId
  INTO invId, suppId
  FROM Purchase_Receipts
  WHERE id = p_receiptId;

  -- Get current stock quantity (if exists)
  SELECT COALESCE(quantity, 0)
  INTO latestQuantity
  FROM Stock_Balance sb
  WHERE sb.inventoryId = invId
    AND sb.productId = p_productId
  LIMIT 1;

  -- Insert stock movement
  INSERT INTO Stock_Movements (
      type,
      quantity,
      unitPrice,
      totalCost,
      referenceType,
      referenceId,
      productId,
      inventoryId,
      avgCost,
      supplierId,
      remainedInStock,
      createdAt
  )
  VALUES (
      'IN',
      p_count,
      p_unitPrice,
      p_totalAmount,
      'PURCHASE',
      p_receiptId,
      p_productId,
      invId,
      CASE
        WHEN p_count = 0 THEN 0
        ELSE p_totalAmount / p_count
      END,
      suppId,
      latestQuantity + p_count,
      NOW()
  );
  COMMIT;
END //

-- Procedure for trg_pr_payment_before_insert
CREATE PROCEDURE validate_payment_before_insert(IN p_receiptId INT, IN p_type VARCHAR(20), IN p_amount DECIMAL(15,2))
BEGIN
  START TRANSACTION;
  DECLARE receiptTotal DECIMAL(14,2);
  DECLARE paid DECIMAL(14,2);

  SELECT totalAmount, paidAmount
  INTO receiptTotal, paid
  FROM Purchase_Receipts
  WHERE id = p_receiptId
  FOR UPDATE;

  IF p_type = 'PAYMENT' AND paid + p_amount > receiptTotal THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'مجموع مبلغ پرداختی بیشتر از مبلغ فاکتور است.';
  END IF;
  COMMIT;
END //

-- Procedure for trg_purchase_payment_update_receipt
CREATE PROCEDURE update_receipt_payment(IN p_receiptId INT)
BEGIN
  START TRANSACTION;
  DECLARE paid DECIMAL(15,2);
  DECLARE total DECIMAL(15,2);

  SELECT
    COALESCE(SUM(
      CASE WHEN type = 'PAYMENT' THEN amount ELSE -amount END
    ),0)
  INTO paid
  FROM Purchase_Receipt_Payments
  WHERE receiptId = p_receiptId;

  SELECT totalAmount INTO total
  FROM Purchase_Receipts
  WHERE id = p_receiptId;

  UPDATE Purchase_Receipts
  SET
    paidAmount = paid,
    status = CASE
      WHEN paid = 0 THEN 'UNPAID'
      WHEN paid < total THEN 'PARTIALLY_PAID'
      ELSE 'PAID'
    END
  WHERE id = p_receiptId;
  COMMIT;
END //

-- Procedure for trg_purchase_payment_after_insert
CREATE PROCEDURE process_purchase_payment(IN p_bankAccountId INT, IN p_type VARCHAR(20), IN p_amount DECIMAL(15,2), IN p_id INT)
BEGIN
  START TRANSACTION;
  DECLARE currentBalance DECIMAL(15, 2);

  SELECT balance INTO currentBalance
  FROM Bank_Account_Balance
  WHERE bankAccountId = p_bankAccountId FOR UPDATE;

  IF currentBalance IS NULL THEN SET currentBalance = 0;
    INSERT INTO Bank_Account_Balance (bankAccountId, balance, updatedAt)
    VALUES (p_bankAccountId, 0, NOW());
  END IF;

  IF p_type = 'PAYMENT' THEN
    SET currentBalance = currentBalance - p_amount;
    INSERT INTO Bank_Account_Transactions (
        bankAccountId,
        type,
        amount,
        balanceAfter,
        referenceType,
        referenceId
    )
    VALUES (
        p_bankAccountId,
        'WITHDRAWAL',
        p_amount,
        currentBalance,
        'PURCHASE_PAYMENT',
        p_id
    );
  ELSE
    SET currentBalance = currentBalance + p_amount;
    INSERT INTO Bank_Account_Transactions (
        bankAccountId,
        type,
        amount,
        balanceAfter,
        referenceType,
        referenceId
    )
    VALUES (
        p_bankAccountId,
        'DEPOSIT',
        p_amount,
        currentBalance,
        'PURCHASE_REFUND',
        p_id
    );
  END IF;

  UPDATE Bank_Account_Balance
  SET balance = currentBalance
  WHERE bankAccountId = p_bankAccountId;
  COMMIT;
END //

-- Procedure for trg_pr_payment_after_insert
CREATE PROCEDURE update_supplier_ledger(IN p_receiptId INT, IN p_type VARCHAR(20), IN p_amount DECIMAL(15,2), IN p_id INT)
BEGIN
  START TRANSACTION;
  DECLARE receiptTotal DECIMAL(14,2) DEFAULT 0;
  DECLARE newPaid DECIMAL(14,2) DEFAULT 0;
  DECLARE _supplierId INT;
  DECLARE lastBalance DECIMAL(14,2) DEFAULT 0;

  -- Lock receipt row
  SELECT COALESCE(totalAmount, 0), COALESCE(paidAmount, 0), supplierId
  INTO receiptTotal, newPaid, _supplierId
  FROM Purchase_Receipts
  WHERE id = p_receiptId
  FOR UPDATE;

  -- Apply payment or refund
  IF p_type = 'PAYMENT' THEN
    SET newPaid = newPaid + p_amount;
  ELSE
    SET newPaid = newPaid - p_amount;
  END IF;

  -- Update receipt
  UPDATE Purchase_Receipts
  SET
    paidAmount = newPaid,
    status =
      CASE
        WHEN newPaid = 0 THEN 'UNPAID'
        WHEN newPaid < receiptTotal THEN 'PARTIALLY_PAID'
        ELSE 'PAID'
      END
  WHERE id = p_receiptId;

  -- Get last supplier balance
  SELECT IFNULL(balance, 0)
  INTO lastBalance
  FROM Supplier_Ledger
  WHERE supplierId = _supplierId
  ORDER BY id DESC
  LIMIT 1;

  -- Insert supplier ledger
  INSERT INTO Supplier_Ledger
  (
    supplierId,
    debit,
    credit,
    balance,
    sourceType,
    sourceId,
    createdAt
  )
  VALUES
  (
    _supplierId,
    IF(p_type = 'REFUND', p_amount, 0),
    IF(p_type = 'PAYMENT', p_amount, 0),
    lastBalance
      + IF(p_type = 'PAYMENT', p_amount, 0)
      - IF(p_type = 'REFUND', p_amount, 0),
    'PAYMENT',
    p_id,
    NOW()
  );
  COMMIT;
END //

-- Procedure for trg_pr_payment_after_delete
CREATE PROCEDURE update_receipt_on_payment_delete(IN p_receiptId INT, IN p_type VARCHAR(20), IN p_amount DECIMAL(15,2))
BEGIN
  DECLARE receiptTotal DECIMAL(14,2);
  DECLARE newPaid DECIMAL(14,2);

  SELECT totalAmount, paidAmount
  INTO receiptTotal, newPaid
  FROM Purchase_Receipts
  WHERE id = p_receiptId
  FOR UPDATE;

  IF p_type = 'PAYMENT' THEN
    SET newPaid = newPaid - p_amount;
  ELSE
    SET newPaid = newPaid + p_amount;
  END IF;

  UPDATE Purchase_Receipts
  SET
    paidAmount = newPaid,
    status =
      CASE
        WHEN newPaid = 0 THEN 'UNPAID'
        WHEN newPaid < receiptTotal THEN 'PARTIALLY_PAID'
        ELSE 'PAID'
      END
  WHERE id = p_receiptId;
END //

-- Procedure for trg_purchase_receipt_after_insert
CREATE PROCEDURE insert_supplier_ledger_purchase(IN p_supplierId INT, IN p_totalAmount DECIMAL(15,2), IN p_id INT)
BEGIN
  DECLARE lastBalance DECIMAL(14,2) DEFAULT 0;

  SELECT COALESCE(balance, 0)
  INTO lastBalance
  FROM Supplier_Ledger
  WHERE supplierId = p_supplierId
  ORDER BY id DESC
  LIMIT 1;

  INSERT INTO Supplier_Ledger
  (
    supplierId,
    debit,
    credit,
    balance,
    sourceType,
    sourceId,
    createdAt
  )
  VALUES
  (
    p_supplierId,
    p_totalAmount,
    0,
    lastBalance - p_totalAmount,
    'PURCHASE',
    p_id,
    NOW()
  );
END //

-- Procedure for trg_sales_invoice_items_before_insert
CREATE PROCEDURE validate_stock_before_sale(IN p_invoiceId INT, IN p_productId INT, IN p_count DECIMAL(10,2))
BEGIN
  DECLARE current_stock DECIMAL(10, 2);
  DECLARE inventory_id INT;

  SELECT pa.inventoryId INTO inventory_id
  FROM Pos_Accounts pa
  INNER JOIN Sales_Invoices si ON pa.id = si.posAccountId
  WHERE si.id = p_invoiceId;

  SELECT COALESCE(quantity, 0) INTO current_stock
  FROM Stock_Balance sb
  WHERE productId = p_productId AND sb.inventoryId = inventory_id
  LIMIT 1;

  IF p_count > current_stock THEN
      SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'موجودی کالا در انبار کافی نیست.';
  END IF;
END //

-- Procedure for trg_sales_invoice_items_after_insert
CREATE PROCEDURE process_sale_item(IN p_invoiceId INT, IN p_productId INT, IN p_count DECIMAL(10,2), IN p_unitPrice DECIMAL(15,2), IN p_totalAmount DECIMAL(15,2))
BEGIN
  DECLARE current_stock DECIMAL(10, 2);
  DECLARE inventory_id INT;
  DECLARE customer_id INT;
  DECLARE pos_id INT;

  SELECT posAccountId, customerId INTO pos_id, customer_id
  FROM Sales_Invoices si
  WHERE si.id = p_invoiceId
  LIMIT 1;

  SELECT pa.inventoryId INTO inventory_id
  FROM Pos_Accounts pa
  INNER JOIN Sales_Invoices si ON pa.id = si.posAccountId
  WHERE si.id = p_invoiceId;

  SELECT COALESCE(quantity, 0) INTO current_stock
  FROM Stock_Balance sb
  WHERE productId = p_productId AND sb.inventoryId = inventory_id
  LIMIT 1;

  INSERT INTO Stock_Movements (
      type,
      quantity,
      unitPrice,
      totalCost,
      referenceType,
      referenceId,
      productId,
      inventoryId,
      avgCost,
      remainedInStock,
      customerId,
      createdAt
  )
  VALUES (
      'OUT',
      p_count,
      p_unitPrice,
      p_totalAmount,
      'SALES',
      p_invoiceId,
      p_productId,
      inventory_id,
      CASE
        WHEN p_count = 0 THEN 0
        ELSE p_totalAmount / p_count
      END,
      current_stock - p_count,
      customer_id,
      NOW()
  );
END //

-- Procedure for trg_sales_invoice_payment_after_insert
CREATE PROCEDURE process_sale_payment(IN p_invoiceId INT, IN p_amount DECIMAL(15,2), IN p_id INT)
BEGIN
  DECLARE currentBalance DECIMAL(15,2);
  DECLARE bankAccountId INT;

  SELECT pa.bankAccountId INTO bankAccountId
  FROM Pos_Accounts pa
  INNER JOIN Sales_Invoices si ON pa.id = si.posAccountId
  WHERE si.id = p_invoiceId;

  SELECT balance INTO currentBalance
  FROM Bank_Account_Balance
  WHERE bankAccountId = bankAccountId
  FOR UPDATE;

  IF currentBalance IS NULL THEN
    SET currentBalance = 0;
    INSERT INTO Bank_Account_Balance (bankAccountId, balance, updatedAt)
    VALUES (bankAccountId, 0, NOW());
  END IF;

  SET currentBalance = currentBalance + p_amount;

  INSERT INTO Bank_Account_Transactions
  (bankAccountId, type, amount, balanceAfter, referenceType, referenceId)
  VALUES
  (bankAccountId, 'DEPOSIT', p_amount, currentBalance, 'POS_SALE', p_id);

  UPDATE Bank_Account_Balance
  SET balance = currentBalance
  WHERE bankAccountId = bankAccountId;
END //

-- Procedure for trg_pos_account_payment_after_insert
CREATE PROCEDURE process_pos_payment(IN p_invoiceId INT, IN p_paymentMethod VARCHAR(20), IN p_amount DECIMAL(15,2), IN p_id INT)
BEGIN
  DECLARE _bankAccountId INT;

  IF(p_paymentMethod != 'CASH') THEN
    SELECT cashBankAccountId INTO _bankAccountId
    FROM Pos_Accounts pa
    INNER JOIN Sales_Invoices si ON pa.id = si.posAccountId
    WHERE si.id = p_invoiceId;
  END IF;

  INSERT INTO Bank_Account_Transactions (
    bankAccountId,
    type,
    amount,
    balanceAfter,
    referenceType,
    referenceId
  )
  VALUES(
    _bankAccountId,
    'DEPOSIT',
    p_amount,
    0,
    'POS_SALE',
    p_id
  );
END //

-- Procedure for trg_stock_transfer
CREATE PROCEDURE update_stock_balance_transfer(IN p_productId INT, IN p_inventoryId INT, IN p_quantity DECIMAL(10,2), IN p_unitPrice DECIMAL(15,2), IN p_totalCost DECIMAL(15,2), IN p_type VARCHAR(10))
BEGIN
  IF p_type = 'IN' THEN
    INSERT INTO Stock_Balance (
        productId,
        inventoryId,
        quantity,
        totalCost,
        avgCost,
        updatedAt
    )
    VALUES (
        p_productId,
        p_inventoryId,
        p_quantity,
        p_totalCost,
        CASE
            WHEN p_quantity = 0 THEN 0
            ELSE p_totalCost / p_quantity
        END,
        NOW()
    )
    ON DUPLICATE KEY UPDATE
      quantity = quantity + p_quantity,
      totalCost = totalCost + p_totalCost,
      avgCost = CASE
          WHEN (quantity + p_quantity) = 0 THEN 0
          ELSE (totalCost + p_totalCost) / (quantity + p_quantity)
      END,
      updatedAt = NOW();
  END IF;

  IF p_type = 'OUT' THEN
    IF EXISTS (
      SELECT 1
      FROM Stock_Balance sb
      WHERE sb.productId = p_productId AND sb.inventoryId = p_inventoryId
    ) THEN
      UPDATE Stock_Balance sb
      SET
        sb.quantity = sb.quantity - p_quantity,
        sb.totalCost = sb.totalCost - (sb.avgCost * p_quantity),
        sb.updatedAt = NOW()
      WHERE
        sb.productId = p_productId
        AND sb.inventoryId = p_inventoryId;
    ELSE
      INSERT INTO Stock_Balance (
          productId,
          inventoryId,
          quantity,
          totalCost,
          avgCost,
          updatedAt
      )
      VALUES (
          p_productId,
          p_inventoryId,
          - p_quantity,
          - COALESCE(p_unitPrice, 0) * p_quantity,
          COALESCE(p_unitPrice, 0),
          NOW()
      );
    END IF;
  END IF;
END //

-- Procedure for trg_stock_purchase_insert
CREATE PROCEDURE update_stock_balance_purchase(IN p_productId INT, IN p_quantity DECIMAL(10,2), IN p_unitPrice DECIMAL(15,2), IN p_totalCost DECIMAL(15,2), IN p_inventoryId INT)
BEGIN
  INSERT INTO Stock_Balance (
      productId,
      quantity,
      avgCost,
      totalCost,
      inventoryId,
      updatedAt
  )
  VALUES (
      p_productId,
      p_quantity,
      p_unitPrice,
      p_totalCost,
      p_inventoryId,
      NOW()
  )
  ON DUPLICATE KEY UPDATE
    quantity = quantity + p_quantity,
    totalCost = totalCost + p_totalCost,
    avgCost = totalCost / quantity;
END //

-- Procedure for trg_stock_sale_insert
CREATE PROCEDURE update_stock_balance_sale(IN p_productId INT, IN p_quantity DECIMAL(10,2), IN p_unitPrice DECIMAL(15,2), IN p_totalCost DECIMAL(15,2), IN p_inventoryId INT)
BEGIN
  INSERT INTO Stock_Balance (
      productId,
      quantity,
      avgCost,
      totalCost,
      inventoryId,
      updatedAt
  )
  VALUES (
      p_productId,
      p_quantity,
      p_unitPrice,
      p_totalCost,
      p_inventoryId,
      NOW()
  )
  ON DUPLICATE KEY UPDATE
    quantity = quantity - p_quantity,
    totalCost = totalCost - p_totalCost,
    avgCost = totalCost / quantity;
END //

DELIMITER;