"""File parser service for card import."""
import csv
import json
from typing import List, Union
from pathlib import Path
import openpyxl
import pandas as pd


class FileParser:
    """Parse various file formats for card import."""

    SUPPORTED_FORMATS = ['xlsx', 'csv', 'json', 'ods']

    @staticmethod
    async def parse_file(file_path: Path) -> List[str]:
        """
        Parse a file and extract card names.
        
        Args:
            file_path: Path to the file to parse
            
        Returns:
            List of card names extracted from the file
            
        Raises:
            ValueError: If file format is not supported
            FileNotFoundError: If file does not exist
            Exception: If file cannot be parsed
        """
        file_type = file_path.suffix.lower().lstrip('.')

        if file_type not in FileParser.SUPPORTED_FORMATS:
            raise ValueError(f"Unsupported file format: {file_type}. Supported formats: {FileParser.SUPPORTED_FORMATS}")

        if not file_path.exists():
            raise FileNotFoundError(f"File not found: {file_path}")

        if file_type == 'csv':
            return FileParser._parse_csv(file_path)
        elif file_type == 'json':
            return FileParser._parse_json(file_path)
        elif file_type == 'xlsx':
            return FileParser._parse_xlsx(file_path)
        elif file_type == 'ods':
            return FileParser._parse_ods(file_path)

    @staticmethod
    def _parse_csv(file_path: Path) -> List[str]:
        """Parse CSV file and extract card names."""
        card_names = []
        with open(file_path, 'r', encoding='utf-8') as f:
            reader = csv.reader(f)
            for row in reader:
                # Take first non-empty column as card name
                for cell in row:
                    cell = cell.strip()
                    if cell:
                        card_names.append(cell)
                        break
        return card_names

    @staticmethod
    def _parse_json(file_path: Path) -> List[str]:
        """Parse JSON file and extract card names."""
        with open(file_path, 'r', encoding='utf-8') as f:
            data = json.load(f)

        if isinstance(data, list):
            return [str(item).strip() for item in data if str(item).strip()]
        elif isinstance(data, dict):
            # Try common keys
            for key in ['cards', 'card_names', 'cards_list', 'list']:
                if key in data and isinstance(data[key], list):
                    return [str(item).strip() for item in data[key] if str(item).strip()]
            # If no common key found, try first list value
            for value in data.values():
                if isinstance(value, list):
                    return [str(item).strip() for item in value if str(item).strip()]
        raise ValueError("Invalid JSON format: expected list or dict with card names")

    @staticmethod
    def _parse_xlsx(file_path: Path) -> List[str]:
        """Parse XLSX file and extract card names from first column."""
        card_names = []
        try:
            workbook = openpyxl.load_workbook(file_path, read_only=True)
            worksheet = workbook.active

            for row in worksheet.iter_rows(values_only=True):
                if row and row[0]:
                    cell_value = str(row[0]).strip()
                    if cell_value:
                        card_names.append(cell_value)
        finally:
            if 'workbook' in locals():
                workbook.close()
        return card_names

    @staticmethod
    def _parse_ods(file_path: Path) -> List[str]:
        """Parse ODS file and extract card names from first column."""
        try:
            df = pd.read_excel(file_path, engine='odf')
            card_names = df.iloc[:, 0].dropna().astype(str).str.strip().tolist()
            return [name for name in card_names if name]
        except ImportError:
            raise ImportError("pandas with odf engine required for ODS parsing. Install with: pip install pandas odfpy")