from sqlalchemy import Column, Integer, String, Float, Text, DateTime, ForeignKey
from sqlalchemy.orm import relationship
from datetime import datetime
from app.db.database import Base

class User(Base):
    __tablename__ = "users"

    user_id = Column(String, primary_key=True, index=True)
    organization_name = Column(String, nullable=False)
    email = Column(String, unique=True, index=True, nullable=False)
    hashed_password = Column(String, nullable=True)
    country = Column(String, nullable=True)
    business_type = Column(String, nullable=True)
    monthly_transactions = Column(String, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)

    journal_entries = relationship("JournalEntry", back_populates="user", cascade="all, delete-orphan")
    accounts = relationship("Account", back_populates="user", cascade="all, delete-orphan")

class Account(Base):
    __tablename__ = "accounts"
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.user_id", ondelete="CASCADE"), index=True, nullable=False)
    
    name = Column(String, nullable=False) # e.g., "Software Subscriptions"
    account_type = Column(String, nullable=False) # ASSET, LIABILITY, EQUITY, REVENUE, EXPENSE
    description = Column(String, nullable=True)
    
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # Relationships
    user = relationship("User")
    line_items = relationship("LineItem", back_populates="account")

class MerchantCache(Base):
    __tablename__ = "merchant_cache"
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.user_id", ondelete="CASCADE"), index=True, nullable=False)
    merchant_key = Column(String, index=True)  # "STARBUCKS" (the cleaned name, used for lookup)
    clean_name = Column(String)                              # "Starbucks Corporation" (human-readable)
    category = Column(String)                                # "Food & Dining" (from ML model)
    irs_line = Column(String, nullable=True)                 # "Line 24b - Meals" (from IRS mapping)
    confidence = Column(Float, nullable=True)                # 0.97 (how sure the ML model was)
    source = Column(String, default="ml_model")              # "ml_model", "llm", or "human_override"
    created_at = Column(DateTime, default=datetime.utcnow)


class JournalEntry(Base):
    __tablename__ = "journal_entries"
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.user_id", ondelete="CASCADE"), index=True, nullable=False)
    
    tx_hash = Column(String, unique=True, index=True) # Deterministic hash for deduplication
    date = Column(String, index=True)
    description = Column(String) # "AWS CLOUD SERVICES"
    
    status = Column(String, default="pending") # 'pending', 'reconciled', 'flagged'
    ambiguity_reason = Column(String, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # Relationships
    user = relationship("User")
    line_items = relationship("LineItem", back_populates="journal_entry", cascade="all, delete-orphan")

class LineItem(Base):
    __tablename__ = "line_items"
    
    id = Column(Integer, primary_key=True, index=True)
    journal_entry_id = Column(Integer, ForeignKey("journal_entries.id", ondelete="CASCADE"), index=True, nullable=False)
    account_id = Column(Integer, ForeignKey("accounts.id", ondelete="CASCADE"), index=True, nullable=False)
    
    amount = Column(Float, nullable=False) # Always positive!
    entry_type = Column(String, nullable=False) # "DEBIT" or "CREDIT"
    
    # Relationships
    journal_entry = relationship("JournalEntry", back_populates="line_items")
    account = relationship("Account", back_populates="line_items")

class TaxLine(Base):
    __tablename__ = "tax_lines"
    
    id = Column(Integer, primary_key=True, index=True)
    form_name = Column(String, nullable=False) # "Form 1120"
    line_number = Column(String, nullable=False) # "Line 26"
    description = Column(String, nullable=False) # "Other Deductions"

class AccountTaxMapping(Base):
    __tablename__ = "account_tax_mappings"
    
    id = Column(Integer, primary_key=True, index=True)
    account_id = Column(Integer, ForeignKey("accounts.id"), unique=True, nullable=False)
    tax_line_id = Column(Integer, ForeignKey("tax_lines.id"), nullable=False)
    tax_year = Column(Integer, nullable=False) # e.g., 2026
    
    # Relationships
    account = relationship("Account")
    tax_line = relationship("TaxLine")

class ClosedPeriod(Base):
    __tablename__ = "closed_periods"
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(String, ForeignKey("users.user_id", ondelete="CASCADE"), index=True, nullable=False)
    period = Column(String, nullable=False)  # e.g. "2026-06"
    closed_at = Column(DateTime, default=datetime.utcnow)
    
    user = relationship("User")