import pandas as pd
from typing import List, Dict, Any, BinaryIO
from datetime import datetime
from .base_parser import BaseBankStatementParser
import math

class VPBankExcelParser(BaseBankStatementParser):
    """
    Parser for VPBank Excel statements (usually old .xls format).
    Requires pandas and xlrd.
    """
    def _parse_amount(self, amt) -> float:
        if pd.isna(amt) or not amt:
            return 0.0
        try:
            return float(str(amt).replace(",", "").strip())
        except ValueError:
            return 0.0

    def parse(self, file_content: BinaryIO, filename: str) -> List[Dict[str, Any]]:
        df = pd.read_excel(file_content)
        
        transactions = []
        is_data_started = False
        
        for index, row in df.iterrows():
            stt = str(row.iloc[0]).strip()
            
            # Identify where data starts
            if stt == "1":
                is_data_started = True
                
            if not is_data_started:
                continue
                
            # If STT is NaN or 'Tổng số tiền', we have reached the end of transactions
            if pd.isna(row.iloc[0]) or 'Tổng số tiền' in str(row.iloc[1]):
                break
                
            date_str = str(row.iloc[2]).strip()
            time_str = str(row.iloc[7]).strip() if len(row) > 7 else ""
            
            try:
                # Value Date format: dd/mm/yyyy
                tx_date = datetime.strptime(date_str, "%d/%m/%Y")
                if time_str and time_str != "nan":
                    try:
                        dt = datetime.strptime(time_str, "%d/%m/%Y %H:%M")
                        tx_date = dt
                    except ValueError:
                        pass
                
                ref = str(row.iloc[1])
                credit = self._parse_amount(row.iloc[3])
                debit = self._parse_amount(row.iloc[4])
                desc = str(row.iloc[5])
                balance = self._parse_amount(row.iloc[6])
                
                tx = {
                    "transaction_date": tx_date,
                    "transaction_reference": ref,
                    "description": desc,
                    "debit_amount_vnd": debit,
                    "credit_amount_vnd": credit,
                    "running_balance_vnd": balance
                }
                transactions.append(tx)
            except Exception as e:
                # skip invalid row
                pass
                
        return transactions