Finance Profit & Loss Generator

import sqlite3, json

DB_PATH = "finance.db"

def fetch_trial_balance(month):
    conn = sqlite3.connect(DB_PATH)
    cur = conn.cursor()
    cur.execute("SELECT account, amount FROM trial_balance WHERE month=?", (month,))
    return {r[0]: r[1] for r in cur.fetchall()}

def classify_pnl(accounts):
    revenue = sum(v for k,v in accounts.items() if k.startswith('REV'))
    cogs = sum(v for k,v in accounts.items() if k.startswith('COGS'))
    expenses = sum(v for k,v in accounts.items() if k.startswith('EXP'))
    gross = revenue - cogs
    net = gross - expenses
    return {"Revenue": revenue, "COGS": cogs, "Gross Profit": gross, "Expenses": expenses, "Net Profit": net}

def calculate_variance(current, previous):
    return {k: round((v - previous.get(k,0))/previous.get(k,1)*100, 1) for k,v in current.items()}

pl = classify_pnl(fetch_trial_balance("2026-06"))
prev = classify_pnl(fetch_trial_balance("2026-05"))
variance = calculate_variance(pl, prev)
print(json.dumps({"pl": pl, "variance": variance}))