import openpyxl
from typing import List, Dict, Any, BinaryIO
from datetime import datetime
from .base_parser import BaseBankStatementParser
import re

class ACBExcelParser(BaseBankStatementParser):
    """
    Parser for ACB Excel statements.
    """
    def _parse_amount(self, amt) -> float:
        if 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]]:
        wb = openpyxl.load_workbook(file_content, data_only=True)
        sheet = wb.active
        
        transactions = []
        is_data_started = False
        
        for row in sheet.iter_rows(values_only=True):
            if not row or row[0] is None:
                continue
                
            # Check for header row
            if str(row[0]).strip() == "Ngày hiệu lực":
                is_data_started = True
                continue
                
            if not is_data_started:
                continue
                
            # Check if Date is valid
            date_str = str(row[0]).strip()
            if not re.match(r'\d{2}/\d{2}/\d{4}', date_str):
                continue
                
            try:
                tx_date = datetime.strptime(date_str[:10], "%d/%m/%Y")
                ref = str(row[2]) if row[2] else None
                desc = str(row[3]).strip() if row[3] else ""
                
                debit = self._parse_amount(row[4])
                credit = self._parse_amount(row[5])
                balance = self._parse_amount(row[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 rows
                pass
                
        return transactions