sheets_service.py

"""Google Sheets service using OAuth authentication"""
import gspread
from typing import List, Dict
import logging
from app.config import settings
from app.models.transaction import RawTransaction, EnrichedTransaction
from app.models.classification import TaxReference
from app.services.oauth_service import OAuthService

logger = logging.getLogger(__name__)


class SheetsService:
    """Service for Google Sheets operations using OAuth"""
    
    def __init__(self):
        """Initialize Google Sheets client with OAuth"""
        try:
            oauth_service = OAuthService()
            credentials = oauth_service.get_credentials()
            self.client = gspread.authorize(credentials)
            self.spreadsheet = self.client.open_by_key(settings.google_sheets_spreadsheet_id)
            logger.info("Google Sheets client initialized successfully with OAuth")
        except Exception as e:
            logger.error(f"Failed to initialize Google Sheets client: {e}")
            raise
    
    def read_raw_transactions(self) -> List[RawTransaction]:
        """Read raw transactions from Google Sheets"""
        try:
            sheet = self.spreadsheet.worksheet(settings.raw_transactions_sheet_name)
            records = sheet.get_all_records()
            
            transactions = []
            for record in records:
                try:
                    transaction = RawTransaction(
                        transaction_id=str(record.get('transaction_id', '')),
                        transaction_date=record.get('transaction_date') or None,
                        description=str(record.get('description', '')),
                        amount=float(record.get('amount', 0)),
                        cost_center=record.get('cost_center') or None,
                        location=record.get('location') or None,
                        vendor_name=record.get('vendor_name') or None,
                        tax_code=record.get('tax_code') or None,
                        tax_amount=float(record['tax_amount']) if record.get('tax_amount') else None
                    )
                    transactions.append(transaction)
                except Exception as e:
                    logger.warning(f"Failed to parse transaction {record.get('transaction_id')}: {e}")
                    continue
            
            logger.info(f"Read {len(transactions)} transactions from sheet")
            return transactions
        except Exception as e:
            logger.error(f"Failed to read raw transactions: {e}")
            raise
    
    def read_tax_reference(self) -> Dict[str, TaxReference]:
        """Read tax reference data and return as dictionary keyed by location"""
        try:
            sheet = self.spreadsheet.worksheet(settings.tax_reference_sheet_name)
            records = sheet.get_all_records()
            
            reference_data = {}
            for record in records:
                try:
                    location = str(record.get('location', '')).upper().strip()
                    if not location:
                        continue
                    
                    ref = TaxReference(
                        location=location,
                        jurisdiction_name=record.get('jurisdiction_name') or None,
                        expected_tax_rate=str(record.get('expected_tax_rate', '0%')),
                        jurisdiction_code=record.get('jurisdiction_code') or None,
                        applies_to=record.get('applies_to') or None,
                        effective_date=record.get('effective_date') or None,
                        notes=record.get('notes') or None
                    )
                    reference_data[location] = ref
                except Exception as e:
                    logger.warning(f"Failed to parse tax reference {record.get('location')}: {e}")
                    continue
            
            logger.info(f"Read {len(reference_data)} tax reference entries")
            return reference_data
        except Exception as e:
            logger.error(f"Failed to read tax reference: {e}")
            raise
    
    def write_clean_transactions(self, transactions: List[EnrichedTransaction]) -> None:
        """Write validated transactions to clean output sheet"""
        try:
            sheet = self.spreadsheet.worksheet(settings.clean_output_sheet_name)
            
            # Prepare headers if sheet is empty
            if len(sheet.get_all_values()) <= 1:
                headers = [
                    'transaction_id', 'description', 'amount', 'location',
                    'ai_classification', 'suggested_tax_rate', 'validation_status',
                    'confidence', 'ai_rationale', 'processing_timestamp'
                ]
                sheet.append_row(headers)
            
            # Prepare rows
            rows = []
            for tx in transactions:
                row = [
                    tx.transaction_id,
                    tx.description,
                    tx.amount,
                    tx.location,
                    tx.classification or '',
                    tx.suggested_tax_rate or '',
                    tx.validation_status or '',
                    tx.confidence or '',
                    tx.rationale or '',
                    tx.processing_timestamp
                ]
                rows.append(row)
            
            # Append all rows at once
            if rows:
                sheet.append_rows(rows)
                logger.info(f"Wrote {len(rows)} clean transactions to sheet")
        except Exception as e:
            logger.error(f"Failed to write clean transactions: {e}")
            raise
    
    def write_review_queue(self, transactions: List[EnrichedTransaction]) -> None:
        """Write flagged transactions to review queue sheet"""
        try:
            sheet = self.spreadsheet.worksheet(settings.review_queue_sheet_name)
            
            # Prepare headers if sheet is empty
            if  len(sheet.get_all_values()) <= 1:
                headers = [
                    'transaction_id', 'description', 'amount', 'location',
                    'ai_classification', 'suggested_tax_rate', 'confidence',
                    'anomaly_type', 'anomaly_reason', 'ai_rationale',
                    'validation_status', 'processing_timestamp'
                ]
                sheet.append_row(headers)
            
            # Prepare rows
            rows = []
            for tx in transactions:
                anomaly_types_str = ', '.join(tx.anomaly_types) if tx.anomaly_types else ''
                row = [
                    tx.transaction_id,
                    tx.description,
                    tx.amount,
                    tx.location,
                    tx.classification or '',
                    tx.suggested_tax_rate or '',
                    tx.confidence or '',
                    anomaly_types_str,
                    f"Anomaly count: {tx.anomaly_count}",
                    tx.rationale or '',
                    tx.validation_status or '',
                    tx.processing_timestamp
                ]
                rows.append(row)
            
            # Append all rows at once
            if rows:
                sheet.append_rows(rows)
                logger.info(f"Wrote {len(rows)} transactions to review queue")
        except Exception as e:
            logger.error(f"Failed to write review queue: {e}")
            raise