from app.extensions import db


class Transaction(db.Model):
    __tablename__ = 'transactions'
    __table_args__ = (
        db.Index('idx_transactions_date', 'date'),
        db.Index('idx_transactions_category', 'category_id'),
        db.Index('idx_transactions_account', 'account_id'),
    )

    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.Text, nullable=False)              # ISO 8601: '2026-05-01'
    merchant_normalized = db.Column(db.String(200), nullable=False)
    amount = db.Column(db.Numeric(10, 2), nullable=False)
    is_credit = db.Column(db.Boolean, nullable=False, default=False)
    is_manual = db.Column(db.Boolean, nullable=False, default=True)
    notes = db.Column(db.Text, nullable=True)
    created_at = db.Column(db.DateTime, server_default=db.func.now(), nullable=False)

    # FKs
    account_id = db.Column(db.Integer, db.ForeignKey('accounts.id', ondelete='RESTRICT'), nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id', ondelete='RESTRICT'), nullable=True)

    # Phase 2 — do not remove these columns
    dedup_hash = db.Column(db.String(64), nullable=True)
    issuer = db.Column(db.String(100), nullable=True)
    merchant_raw = db.Column(db.Text, nullable=True)
    confidence_score = db.Column(db.Float, nullable=True)
    import_batch_id = db.Column(
        db.Integer,
        db.ForeignKey('import_batches.id', ondelete='SET NULL'),
        nullable=True,
    )

    # Relationships
    account = db.relationship('Account', back_populates='transactions')
    category = db.relationship('Category', back_populates='transactions')
    import_batch = db.relationship('ImportBatch', back_populates='transactions')