from uuid import UUID
from typing import Optional
from app.modules.banking.domain.entities import BankTransaction

class BankTransactionRepository:
    def __init__(self, conn):
        self._conn = conn

    def ensure_schema(self):
        with self._conn.cursor() as cur:
            cur.execute("""
                CREATE SCHEMA IF NOT EXISTS banking;
                CREATE TABLE IF NOT EXISTS banking.transactions (
                    id UUID PRIMARY KEY,
                    bank_account_no TEXT NOT NULL,
                    amount NUMERIC(18, 4) NOT NULL,
                    transaction_date TIMESTAMP WITH TIME ZONE NOT NULL,
                    description TEXT NOT NULL,
                    reconciled BOOLEAN DEFAULT FALSE,
                    reconciled_invoice_id UUID,
                    created_at TIMESTAMP WITH TIME ZONE
                );
            """)

    def save(self, txn: BankTransaction) -> None:
        self.ensure_schema()
        with self._conn.cursor() as cur:
            cur.execute("""
                INSERT INTO banking.transactions 
                (id, bank_account_no, amount, transaction_date, description, reconciled, reconciled_invoice_id, created_at)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                ON CONFLICT (id) DO UPDATE SET
                    reconciled = EXCLUDED.reconciled,
                    reconciled_invoice_id = EXCLUDED.reconciled_invoice_id;
            """, (
                str(txn.id),
                txn.bank_account_no,
                txn.amount,
                txn.transaction_date,
                txn.description,
                txn.reconciled,
                str(txn.reconciled_invoice_id) if txn.reconciled_invoice_id else None,
                txn.created_at
            ))

    def get_by_id(self, txn_id: UUID) -> Optional[BankTransaction]:
        with self._conn.cursor() as cur:
            cur.execute("SELECT * FROM banking.transactions WHERE id = %s", (str(txn_id),))
            row = cur.fetchone()
            if row:
                return BankTransaction(**row)
        return None