"""
StagedTransactionModel — operates against a SEPARATE staging database.

Architecture note (Epic 9):
  - staging_pipeline.py opens instance/import_staging.db with a separate SQLAlchemy
    engine bound to this model's StagingBase (not the main db).
  - This model is intentionally NOT imported in app/models/__init__.py — it must NOT
    appear in the main Flask-Migrate migrations.
  - staged_txn_to_model() in staging_pipeline.py converts the StagedTransaction
    dataclass to StagedTransactionModel for persistence in the staging DB.
  - On successful user commit: rows are promoted to Transaction (main DB).
  - On abandon or error: staging DB is wiped; no main DB debris.
"""
from sqlalchemy import Column, Integer, Text, Numeric, Boolean, Float, DateTime, String
from sqlalchemy.orm import declarative_base

StagingBase = declarative_base()


class StagedTransactionModel(StagingBase):
    __tablename__ = 'staged_transactions'

    id = Column(Integer, primary_key=True)
    date = Column(Text, nullable=False)                  # ISO 8601: '2026-05-01'
    merchant_raw = Column(Text, nullable=False)
    merchant_normalized = Column(Text, nullable=False)
    amount = Column(Numeric(10, 2), nullable=False)
    is_credit = Column(Boolean, nullable=False, default=False)
    issuer = Column(String(100), nullable=True)
    confidence_score = Column(Float, nullable=True)       # 0.0–1.0; < 0.7 flagged for review
    dedup_hash = Column(String(64), nullable=True)
    raw_text = Column(Text, nullable=True)

    # No FK constraints — staging DB has no foreign key relationship to main DB
    import_batch_id = Column(Integer, nullable=True)
    category_id = Column(Integer, nullable=True)          # resolved by user during staged review
    account_id = Column(Integer, nullable=True)           # resolved by user during staged review

    committed_at = Column(DateTime, nullable=True)        # set when promoted to main Transaction
    status = Column(String(20), nullable=False, default='pending')  # pending / accepted / rejected