"""A stand-in for your ERP's books, so the samples run end to end.

Replace these functions with your ERP's own calls (D365 services, SAP BAPIs, Odoo RPC ...).
It keeps sales orders and journals in STATE_DIR/erp.db. Accounts listed in ERP_ON_HOLD
(comma-separated) are refused, the way a credit department's hold refuses them.
"""
import os
import sqlite3

STATE_DIR = os.environ.get("STATE_DIR", "state")


def connect():
    os.makedirs(STATE_DIR, exist_ok=True)
    db = sqlite3.connect(os.path.join(STATE_DIR, "erp.db"))
    db.executescript(
        """
        create table if not exists sales_order (
            number integer primary key autoincrement,
            customer_reference text not null unique,   -- the Slflo order id
            account text not null,
            total text not null
        );
        create table if not exists journal (
            number integer primary key autoincrement,
            kind text not null,                       -- invoice, payment, return, payment-reversal
            source_id text not null,
            source_version integer not null,
            seq integer,
            amount text not null,
            unique (kind, source_id, source_version)
        );
        """
    )
    return db


def on_hold(account):
    return account in {a.strip() for a in os.environ.get("ERP_ON_HOLD", "").split(",") if a.strip()}


def find_sales_order(db, customer_reference):
    row = db.execute("select number from sales_order where customer_reference = ?", (customer_reference,)).fetchone()
    return f"SO-{row[0]:07d}" if row else None


def create_sales_order(db, customer_reference, account, total):
    cursor = db.execute(
        "insert into sales_order (customer_reference, account, total) values (?, ?, ?)",
        (customer_reference, account, total),
    )
    db.commit()
    return f"SO-{cursor.lastrowid:07d}"


def post_journal(db, kind, source_id, source_version, amount, seq=None):
    """One journal per document version; posting the same version again returns the first."""
    row = db.execute(
        "select number from journal where kind = ? and source_id = ? and source_version = ?",
        (kind, source_id, source_version),
    ).fetchone()
    if row is None:
        row = (
            db.execute(
                "insert into journal (kind, source_id, source_version, seq, amount) values (?, ?, ?, ?, ?)",
                (kind, source_id, source_version, seq, amount),
            ).lastrowid,
        )
        db.commit()
    return f"JRN-{row[0]:06d}"


def journal_of(db, kind, source_id):
    row = db.execute(
        "select number from journal where kind = ? and source_id = ? order by source_version limit 1",
        (kind, source_id),
    ).fetchone()
    return f"JRN-{row[0]:06d}" if row else None


def booked_seqs(db, kind):
    return [r[0] for r in db.execute("select distinct seq from journal where kind = ? and seq is not null order by seq", (kind,))]