pos_transaction_demo.py

"""
pos_transaction_demo.py

Interactive Point-of-Sale transaction demonstration for PostgreSQL,
built with psycopg2.

This script reproduces the workflow described in the lab manual:
    1. Open a database transaction.
    2. Create a new customer order (order_number is generated by the
       database using GENERATED ALWAYS AS IDENTITY, so we never supply
       it ourselves; we retrieve it with RETURNING).
    3. For each of three products:
         a. Create a SAVEPOINT before the product is inserted.
         b. Look up the product's selling price and current stock.
         c. Insert the order_detail row.
         d. Update the product's quantity_in_stock.
         e. Ask the user whether this item should be rolled back.
            If yes, ROLLBACK TO SAVEPOINT undoes only that item,
            leaving the rest of the transaction intact.
    4. Calculate the order total and, on confirmation, insert the
       payment row.
    5. Ask the user whether to COMMIT (make everything permanent) or
       ROLLBACK (discard the entire transaction).
    6. Print a receipt of whatever was actually committed.

Run with:
    python pos_transaction_demo.py
"""

import os
import sys
import getpass

import psycopg2
import psycopg2.extras

from dotenv import load_dotenv

# Load variables from a .env file in the current working directory
# into the process environment. This does nothing (and raises no
# error) if the file is absent, which is why the prompts below still
# work as a fallback.
load_dotenv()

# ---------------------------------------------------------------------------
# Connection handling
# ---------------------------------------------------------------------------

def get_connection():
    """
    Build a psycopg2 connection using credentials from a .env file
    where available, falling back to an interactive prompt for any
    value that is missing. Autocommit is left OFF (the default) so
    that we control the transaction boundary explicitly with
    COMMIT / ROLLBACK ourselves, rather than psycopg2 committing
    after every statement.
    """
    print("=== Database connection ===")

    host = os.getenv("DB_HOST") or input(
        "Host [The Ubuntu Server VM's enp0s8 IP Address]: "
    ).strip() or "localhost"

    port = os.getenv("DB_PORT") or input("Port [5432]: ").strip() or "5432"

    dbname = os.getenv("DB_NAME") or input(
        "Database name [siwaka_dishes]: "
    ).strip() or "siwaka_dishes"

    user = os.getenv("DB_APP_RUNTIME_USER") or input("User: ").strip()

    # Only fall back to an interactive, hidden prompt if the password
    # was not supplied via .env; never print it either way.
    password = os.getenv("DB_APP_RUNTIME_PASSWORD") or getpass.getpass("Password: ")

    try:
        conn = psycopg2.connect(
            host=host,
            port=port,
            dbname=dbname,
            user=user,
            password=password,
        )
    except psycopg2.OperationalError as exc:
        print(f"\nCould not connect to the database: {exc}")
        sys.exit(1)

    conn.autocommit = False
    return conn


# ---------------------------------------------------------------------------
# Small helpers
# ---------------------------------------------------------------------------

def ask_yes_no(prompt):
    """Ask a yes/no question and return True for yes, False for no."""
    while True:
        answer = input(f"{prompt} (y/n): ").strip().lower()
        if answer in ("y", "yes"):
            return True
        if answer in ("n", "no"):
            return False
        print("Please answer 'y' or 'n'.")


def ask_int(prompt, default=None):
    """Ask for an integer, optionally falling back to a default value."""
    while True:
        raw = input(prompt).strip()
        if not raw and default is not None:
            return default
        try:
            return int(raw)
        except ValueError:
            print("Please enter a whole number.")


def fetch_product(cursor, product_code):
    """
    Look up a product's selling price and quantity in stock.
    Returns a dictionary with keys 'selling_price' and 'quantity_in_stock',
    or None if the product code does not exist.
    """
    cursor.execute(
        """
        SELECT selling_price, quantity_in_stock
        FROM public.product
        WHERE product_code = %s;
        """,
        (product_code,),
    )
    row = cursor.fetchone()
    if row is None:
        return None
    return {"selling_price": row[0], "quantity_in_stock": row[1]}


# ---------------------------------------------------------------------------
# Step: create the order header
# ---------------------------------------------------------------------------

def create_order(cursor):
    """
    Insert the customer_order row and return the generated
    order_number. order_number is GENERATED ALWAYS AS IDENTITY, so we
    never supply it ourselves; PostgreSQL assigns it and we retrieve
    it via RETURNING.
    """
    print("\n=== Step: Create a new order ===")
    order_status_id = ask_int("Order status ID [3 == 'In Transit']: ", default=3)
    customer_number = ask_int("Customer number [264 == '[Business] Nyali Lodge']: ")
    branch_code = ask_int("Branch code [16 == 'City Mall, Nyali, Mombasa Branch']: ")

    cursor.execute(
        """
        INSERT INTO public.customer_order (
            order_date,
            required_date,
            dispatch_date,
            order_status_id,
            customer_number,
            branch_code
        )
        VALUES (
            CURRENT_TIMESTAMP,
            CURRENT_TIMESTAMP + INTERVAL '1 hour',
            CURRENT_TIMESTAMP + INTERVAL '45 minutes',
            %s,
            %s,
            %s
        )
        RETURNING order_number;
        """,
        (order_status_id, customer_number, branch_code),
    )
    order_number = cursor.fetchone()[0]
    print(f"Inserted order number: {order_number}")
    return order_number


# ---------------------------------------------------------------------------
# Step: insert one product line, guarded by its own SAVEPOINT
# ---------------------------------------------------------------------------

def add_order_item(cursor, order_number, item_label):
    """
    Handle a single order item:
        - create a named SAVEPOINT before touching the database,
        - prompt for product code and quantity,
        - insert the order_detail row,
        - update quantity_in_stock,
        - ask the user whether to roll this item back.

    Returns True if the item was kept, False if it was rolled back.
    The SAVEPOINT name is derived from item_label so that it stays unique
    and readable (e.g. 'before_product_1').
    """
    savepoint_name = f"before_{item_label}"

    print(f"\n=== Step: {item_label} ===")

    # A SAVEPOINT divides the transaction into a unit of work that can
    # be undone on its own, without discarding everything else already
    # done in this transaction.
    cursor.execute(f"SAVEPOINT {savepoint_name};")

    product_code = input("Enter product code (e.g. P018, P008, P002): ").strip()
    product = fetch_product(cursor, product_code)

    if product is None:
        print(f"Product code '{product_code}' was not found. Rolling this item back.")
        cursor.execute(f"ROLLBACK TO SAVEPOINT {savepoint_name};")
        return False

    print(f"Selling price: {product['selling_price']}")
    print(f"Quantity in stock: {product['quantity_in_stock']}")

    quantity_ordered = ask_int("Quantity ordered (e.g., 5, 100, 1): ")
    if quantity_ordered > product["quantity_in_stock"]:
        print("Requested quantity exceeds stock on hand. Rolling this item back.")
        cursor.execute(f"ROLLBACK TO SAVEPOINT {savepoint_name};")
        return False

    # Insert the order line. order_detail_number is also expected to be
    # an identity column, so it is omitted here, matching the pattern
    # used for order_number.
    cursor.execute(
        """
        INSERT INTO public.order_detail (
            order_number,
            product_code,
            quantity_ordered,
            price_each
        )
        VALUES (%s, %s, %s, %s);
        """,
        (order_number, product_code, quantity_ordered, product["selling_price"]),
    )

    # Update stock to reflect the sale.
    cursor.execute(
        """
        UPDATE public.product
        SET quantity_in_stock = quantity_in_stock - %s
        WHERE product_code = %s;
        """,
        (quantity_ordered, product_code),
    )

    # Confirm the updated stock level so that the user can see the effect.
    cursor.execute(
        "SELECT quantity_in_stock FROM public.product WHERE product_code = %s;",
        (product_code,),
    )
    updated_stock = cursor.fetchone()[0]
    print(f"Updated quantity in stock (not yet committed): {updated_stock}")

    # Ask whether to keep or roll back THIS item only. This mirrors the
    # supervisor-approved rollback scenario described in the lab manual,
    # where a single mistaken item is undone without discarding the
    # entire sale.
    if ask_yes_no(f"Roll back {item_label} (undo just this item)?"):
        cursor.execute(f"ROLLBACK TO SAVEPOINT {savepoint_name};")
        print(f"{item_label} has been rolled back; the rest of the order is unaffected.")
        return False

    # Releasing the savepoint discards it without undoing the work done
    # after it was created. This is optional (COMMIT/ROLLBACK of the
    # outer transaction would clean it up regardless), but it is good
    # practice in Point-of-Sale systems once we are sure the item is being kept.
    cursor.execute(f"RELEASE SAVEPOINT {savepoint_name};")
    return True


# ---------------------------------------------------------------------------
# Step: payment
# ---------------------------------------------------------------------------

def calculate_total(cursor, order_number):
    cursor.execute(
        """
        SELECT COALESCE(SUM(quantity_ordered * price_each), 0) AS total
        FROM public.order_detail
        WHERE order_number = %s;
        """,
        (order_number,),
    )
    return cursor.fetchone()[0]


def receive_payment(cursor, order_number):
    """
    Show the order total and, on confirmation, insert the payment row.
    Returns True if a payment row was inserted, False otherwise.
    """
    print("\n=== Step: Payment ===")
    total = calculate_total(cursor, order_number)
    print(f"Total amount due for order {order_number}: {total}")

    if total == 0:
        print("Total is zero (no items were kept); skipping payment.")
        return False

    if not ask_yes_no("Record payment for this amount now?"):
        print("Payment was not recorded.")
        return False

    payment_method_id = ask_int("Payment method ID [1 == Cash]: ", default=1)

    cursor.execute(
        """
        INSERT INTO public.payment (
            order_number,
            payment_date,
            amount,
            payment_method_id
        )
        VALUES (%s, CURRENT_TIMESTAMP, %s, %s);
        """,
        (order_number, total, payment_method_id),
    )
    print("Payment recorded.")
    return True


# ---------------------------------------------------------------------------
# Step: receipt
# ---------------------------------------------------------------------------

def print_receipt(cursor, order_number):
    print(f"\n=== Receipt for order {order_number} ===")
    cursor.execute(
        """
        SELECT
            od.product_code,
            p.product_name,
            od.quantity_ordered,
            od.price_each,
            (od.quantity_ordered * od.price_each) AS line_total
        FROM public.order_detail od
        INNER JOIN public.product p ON od.product_code = p.product_code
        WHERE od.order_number = %s
        ORDER BY od.product_code;
        """,
        (order_number,),
    )
    rows = cursor.fetchall()

    if not rows:
        print("No items were committed for this order.")
        return

    print(f"{'Code':<8}{'Product':<20}{'Qty':>6}{'Price':>12}{'Line total':>14}")
    grand_total = 0
    for code, name, qty, price, line_total in rows:
        print(f"{code:<8}{name:<20}{qty:>6}{price:>12}{line_total:>14}")
        grand_total += line_total
    print("-" * 60)
    print(f"{'Grand total':<46}{grand_total:>14}")


# ---------------------------------------------------------------------------
# Main workflow
# ---------------------------------------------------------------------------

def main():
    conn = get_connection()
    cursor = conn.cursor()

    try:
        # BEGIN is implicit in psycopg2: the first statement executed
        # after connecting (with autocommit=False) opens a transaction
        # automatically. There is no need to issue BEGIN explicitly.
        order_number = create_order(cursor)

        item_labels = ["product_1", "product_2", "product_3"]
        kept_items = []
        for label in item_labels:
            kept = add_order_item(cursor, order_number, label)
            kept_items.append((label, kept))

        print("\n=== Item summary ===")
        for label, kept in kept_items:
            status = "kept" if kept else "rolled back"
            print(f"  {label}: {status}")

        receive_payment(cursor, order_number)

        print("\n=== Step: Close the transaction ===")
        if ask_yes_no("COMMIT the transaction and make all changes permanent?"):
            conn.commit()
            print("Transaction committed.")
        else:
            conn.rollback()
            print("Transaction rolled back. No changes were saved.")
            return  # Nothing to show on the receipt if everything was undone.

        print_receipt(cursor, order_number)

    except psycopg2.Error as exc:
        # Any database error rolls back the whole transaction, since a
        # failed statement leaves the transaction unable to accept
        # further commands until it is rolled back.
        conn.rollback()
        print(f"\nA database error occurred; the transaction was rolled back: {exc}")

    finally:
        cursor.close()
        conn.close()


if __name__ == "__main__":
    main()