=== FILE LIST ===
/home/def/otb_billing/backend/app.py
/home/def/otb_billing/static/css/style.css
/home/def/otb_billing/templates/base.html
/home/def/otb_billing/templates/clients/edit.html
/home/def/otb_billing/templates/clients/list.html
/home/def/otb_billing/templates/clients/new.html
/home/def/otb_billing/templates/credits/add.html
/home/def/otb_billing/templates/credits/list.html
/home/def/otb_billing/templates/dashboard.html
/home/def/otb_billing/templates/footer.html
/home/def/otb_billing/templates/health.html
/home/def/otb_billing/templates/invoices/edit.html
/home/def/otb_billing/templates/invoices/list.html
/home/def/otb_billing/templates/invoices/new.html
/home/def/otb_billing/templates/invoices/print_batch.html
/home/def/otb_billing/templates/invoices/view.html
/home/def/otb_billing/templates/payments/edit.html
/home/def/otb_billing/templates/payments/list.html
/home/def/otb_billing/templates/payments/new.html
/home/def/otb_billing/templates/portal_dashboard.html
/home/def/otb_billing/templates/portal_forgot_password.html
/home/def/otb_billing/templates/portal_invoice_detail.html
/home/def/otb_billing/templates/portal_login.html
/home/def/otb_billing/templates/portal_set_password.html
/home/def/otb_billing/templates/reports/aging.html
/home/def/otb_billing/templates/reports/revenue.html
/home/def/otb_billing/templates/reports/revenue_print.html
/home/def/otb_billing/templates/services/edit.html
/home/def/otb_billing/templates/services/list.html
/home/def/otb_billing/templates/services/new.html
/home/def/otb_billing/templates/settings.html
/home/def/otb_billing/templates/subscriptions/list.html
/home/def/otb_billing/templates/subscriptions/new.html

=== APP.PY ===
import os
from flask import Flask, render_template, request, redirect, send_file, make_response, jsonify, session, Response
from db import get_db_connection
from utils import generate_client_code, generate_service_code
from datetime import datetime, timezone, date, timedelta
from zoneinfo import ZoneInfo
from decimal import Decimal, InvalidOperation
from pathlib import Path
from email.message import EmailMessage
from dateutil.relativedelta import relativedelta

from io import BytesIO, StringIO
import csv
import json
import hmac
import hashlib
import base64
import urllib.request
import urllib.error
import urllib.parse
import uuid
import re
import math
import zipfile
import smtplib
import secrets
import threading
import time
from reportlab.lib.pagesizes import letter
from reportlab.pdfgen import canvas
from reportlab.lib.utils import ImageReader
from werkzeug.security import generate_password_hash, check_password_hash
from health import register_health_routes

app = Flask(
    __name__,
    template_folder="../templates",
    static_folder="../static",
)
app.config["OTB_HEALTH_DB_CONNECTOR"] = get_db_connection

LOCAL_TZ = ZoneInfo("America/Toronto")

BASE_DIR = Path(__file__).resolve().parent.parent

app.secret_key = os.getenv("OTB_BILLING_SECRET_KEY", "otb-billing-dev-secret-change-me")
SQUARE_ACCESS_TOKEN = os.getenv("SQUARE_ACCESS_TOKEN", "")
SQUARE_WEBHOOK_SIGNATURE_KEY = os.getenv("SQUARE_WEBHOOK_SIGNATURE_KEY", "")
SQUARE_WEBHOOK_NOTIFICATION_URL = os.getenv("SQUARE_WEBHOOK_NOTIFICATION_URL", "")
SQUARE_API_BASE = "https://connect.squareup.com"
SQUARE_API_VERSION = "2026-01-22"
SQUARE_WEBHOOK_LOG = str(BASE_DIR / "logs" / "square_webhook_events.log")
ORACLE_BASE_URL = os.getenv("ORACLE_BASE_URL", "https://monitor.outsidethebox.top")
CRYPTO_EVM_PAYMENT_ADDRESS = os.getenv("OTB_BILLING_CRYPTO_EVM_ADDRESS", "0x44f6c44C42e6ae0392E7289F032384C0d37F56D5")
RPC_ETHEREUM_URL = os.getenv("OTB_BILLING_RPC_ETHEREUM", "https://ethereum-rpc.publicnode.com")
RPC_ETHEREUM_URL_2 = os.getenv("OTB_BILLING_RPC_ETHEREUM_2", "https://rpc.ankr.com/eth")
RPC_ETHEREUM_URL_3 = os.getenv("OTB_BILLING_RPC_ETHEREUM_3", "https://eth.drpc.org")

RPC_ARBITRUM_URL = os.getenv("OTB_BILLING_RPC_ARBITRUM", "https://arbitrum-one-rpc.publicnode.com")
RPC_ARBITRUM_URL_2 = os.getenv("OTB_BILLING_RPC_ARBITRUM_2", "https://rpc.ankr.com/arbitrum")
RPC_ARBITRUM_URL_3 = os.getenv("OTB_BILLING_RPC_ARBITRUM_3", "https://arb1.arbitrum.io/rpc")

RPC_ETICA_URL = os.getenv("OTB_BILLING_RPC_ETICA", "https://rpc.etica-stats.org")
RPC_ETICA_URL_2 = os.getenv("OTB_BILLING_RPC_ETICA_2", "https://eticamainnet.eticaprotocol.org")
RPC_ETHO_URL = os.getenv("OTB_BILLING_RPC_ETHO", "https://rpc.ethoprotocol.com")
RPC_ETHO_URL_2 = os.getenv("OTB_BILLING_RPC_ETHO_2", "https://rpc4.ethoprotocol.com")

CRYPTO_PROCESSING_TIMEOUT_SECONDS = int(os.getenv("OTB_BILLING_CRYPTO_PROCESSING_TIMEOUT_SECONDS", "180"))
CRYPTO_WATCH_INTERVAL_SECONDS = int(os.getenv("OTB_BILLING_CRYPTO_WATCH_INTERVAL_SECONDS", "30"))
CRYPTO_WATCHER_STARTED = False




def load_version():
    try:
        with open(BASE_DIR / "VERSION", "r") as f:
            return f.read().strip()
    except Exception:
        return "unknown"

APP_VERSION = load_version()

@app.context_processor
def inject_version():
    return {"app_version": APP_VERSION}

@app.context_processor
def inject_app_settings():
    return {"app_settings": get_app_settings()}

def fmt_local(dt_value):
    if not dt_value:
        return ""
    if isinstance(dt_value, str):
        try:
            dt_value = datetime.fromisoformat(dt_value)
        except ValueError:
            return str(dt_value)
    if dt_value.tzinfo is None:
        dt_value = dt_value.replace(tzinfo=timezone.utc)
    return dt_value.astimezone(LOCAL_TZ).strftime("%Y-%m-%d %I:%M:%S %p")

def to_decimal(value):
    if value is None or value == "":
        return Decimal("0")
    try:
        return Decimal(str(value))
    except (InvalidOperation, ValueError):
        return Decimal("0")

def fmt_money(value, currency_code="CAD"):
    amount = to_decimal(value)
    if currency_code == "CAD":
        return f"{amount:.2f}"
    return f"{amount:.8f}"

def payment_method_label(method, currency=None):
    method_key = str(method or "").strip().lower()
    currency_key = str(currency or "").strip().upper()

    if method_key == "square":
        return "Square"
    if method_key == "etransfer":
        return "e-Transfer"
    if method_key == "cash":
        return "Cash"
    if method_key == "other":
        if currency_key in {"ETH", "ETHO", "ETI", "USDC", "EGAZ", "ALT", "CAD"}:
            return currency_key
        return "Other"
    if method_key == "crypto_etho":
        return "ETHO"
    if method_key == "crypto_egaz":
        return "EGAZ"
    if method_key == "crypto_alt":
        return "ALT"

    if currency_key in {"ETH", "ETHO", "ETI", "USDC", "EGAZ", "ALT"}:
        return currency_key

    return method or "Unknown"

def get_invoice_payments(invoice_id):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
        SELECT
            id,
            payment_method,
            payment_currency,
            payment_amount,
            cad_value_at_payment,
            reference,
            sender_name,
            txid,
            wallet_address,
            payment_status,
            confirmations,
            confirmation_required,
            received_at,
            created_at,
            notes
        FROM payments
        WHERE invoice_id = %s
        ORDER BY COALESCE(received_at, created_at) ASC, id ASC
    """, (invoice_id,))
    rows = cursor.fetchall()
    conn.close()

    out = []
    for row in rows:
        item = dict(row)
        item["payment_method_label"] = payment_method_label(
            item.get("payment_method"),
            item.get("payment_currency"),
        )
        item["payment_amount_display"] = fmt_money(
            item.get("payment_amount"),
            item.get("payment_currency") or "CAD",
        )
        item["cad_value_display"] = fmt_money(item.get("cad_value_at_payment"), "CAD")
        item["received_at_local"] = fmt_local(item.get("received_at") or item.get("created_at"))
        out.append(item)
    return out

def normalize_oracle_datetime(value):
    if not value:
        return None
    try:
        text = str(value).replace("Z", "+00:00")
        dt = datetime.fromisoformat(text)
        if dt.tzinfo is None:
            dt = dt.replace(tzinfo=timezone.utc)
        return dt.astimezone(timezone.utc).strftime("%Y-%m-%d %H:%M:%S")
    except Exception:
        return None

def ensure_invoice_quote_columns():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute("""
        SELECT COLUMN_NAME
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'invoices'
    """)
    existing = {row["COLUMN_NAME"] for row in cursor.fetchall()}

    wanted = {
        "quote_fiat_amount": "ALTER TABLE invoices ADD COLUMN quote_fiat_amount DECIMAL(18,8) DEFAULT NULL AFTER status",
        "quote_fiat_currency": "ALTER TABLE invoices ADD COLUMN quote_fiat_currency VARCHAR(16) DEFAULT NULL AFTER quote_fiat_amount",
        "quote_expires_at": "ALTER TABLE invoices ADD COLUMN quote_expires_at DATETIME DEFAULT NULL AFTER quote_fiat_currency",
        "oracle_snapshot": "ALTER TABLE invoices ADD COLUMN oracle_snapshot LONGTEXT DEFAULT NULL AFTER quote_expires_at"
    }

    exec_cursor = conn.cursor()
    changed = False
    for column_name, ddl in wanted.items():
        if column_name not in existing:
            exec_cursor.execute(ddl)
            changed = True

    if changed:
        conn.commit()
    conn.close()

def fetch_oracle_quote_snapshot(currency_code, total_amount):
    if str(currency_code or "").upper() != "CAD":
        return None

    try:
        amount_value = Decimal(str(total_amount))
        if amount_value <= 0:
            return None
    except (InvalidOperation, ValueError):
        return None

    try:
        qs = urllib.parse.urlencode({
            "fiat": "CAD",
            "amount": format(amount_value, "f"),
        })
        req = urllib.request.Request(
            f"{ORACLE_BASE_URL.rstrip('/')}/api/oracle/quote?{qs}",
            headers={
                "Accept": "application/json",
                "User-Agent": "otb-billing-oracle/0.1"
            },
            method="GET"
        )
        with urllib.request.urlopen(req, timeout=15) as resp:
            data = json.loads(resp.read().decode("utf-8"))

        if not isinstance(data, dict) or not isinstance(data.get("quotes"), list):
            return None

        return {
            "oracle_url": ORACLE_BASE_URL.rstrip("/"),

=== templates/base.html ===
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>{{ page_title }}</title>
<link rel="stylesheet" href="/static/css/style.css">
    <link rel="icon" type="image/png" href="/static/favicon.png">
</head>

<body>

<header>
<h1>OTB Billing</h1>
</header>

<main>
<p>{{ content }}</p>
</main>

</body>
</html>
{% include "footer.html" %}

=== /home/def/otb_billing/static/css/style.css ===
body {
    font-family: Arial;
    background: #0f172a;
    color: #e5e7eb;
}

header {
    padding: 20px;
    background: #111827;
}
