# File: /Users/bpa/ForesixaQuant/log_payments.py
import sqlite3
import os
from datetime import datetime, timezone

class QzoticTransactionLedger:
    def __init__(self, db_path="/Users/bpa/ForesixaQuant/trading_live.db"):
        self.db_path = db_path
        self._initialize_transaction_ledger_table()

    def _initialize_transaction_ledger_table(self):
        """Initializes a strict transaction auditing ledger completely isolated from trading rules."""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        cursor.execute("PRAGMA foreign_keys = ON;")
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS customer_payments_ledger (
                transaction_id TEXT PRIMARY KEY NOT NULL,
                customer_scope TEXT NOT NULL,
                amount_cents INTEGER NOT NULL CHECK(amount_cents >= 0),
                currency_marker TEXT NOT NULL CHECK(length(currency_marker) = 3),
                payment_status TEXT NOT NULL,
                logged_at_utc TEXT NOT NULL
            );
        """)
        conn.commit()
        conn.close()

    def commit_verified_payment_record(self, transaction_id, customer_scope, amount_cents, currency, status):
        """Transactionally writes an immutable client payment block into local storage."""
        conn = sqlite3.connect(self.db_path, timeout=10.0)
        cursor = conn.cursor()
        try:
            cursor.execute("BEGIN IMMEDIATE TRANSACTION;")
            cursor.execute("""
                INSERT INTO customer_payments_ledger (transaction_id, customer_scope, amount_cents, currency_marker, payment_status, logged_at_utc)
                VALUES (?, ?, ?, ?, ?, ?);
            """, (transaction_id, customer_scope, int(amount_cents), currency.upper(), status, datetime.now(timezone.utc).isoformat()))
            conn.commit()
            print(f"   • [LEDGER SUCCESS] Committed transaction row {transaction_id} cleanly to disk.")
            return True
        except sqlite3.IntegrityError as ie:
            conn.rollback()
            err_msg = str(ie).lower()
            if "unique" in err_msg or "primary key" in err_msg:
                print(f"   • [LEDGER REJECTED] Filtered transaction duplication event: {transaction_id}")
            else:
                print(f"   • [LEDGER FORMAT REJECT] Table integrity check failed: {err_msg}")
            return False
        except Exception as e:
            conn.rollback()
            print(f"   • [LEDGER ABORTED] Database pipeline failure: {str(e)}")
            return False
        finally:
            cursor.close()
            conn.close()

if __name__ == "__main__":
    ledger = QzoticTransactionLedger()
    print("[SUCCESS] Isolated customer payments ledger initialised and verified.")