import io

# from src.apps.wine.wine_duplication.models.wine_duplication import WineDuplication
import unicodedata
from typing import List, Optional

import chardet
import polars as pl
import sqlalchemy
from fastapi import FastAPI, File, HTTPException, UploadFile, status
from fastapi.responses import JSONResponse
from sqlalchemy.orm import Session

from src.apps.keyword_generator.schemas.keyword_generator import KeywordSave
from src.apps.wine.producer.models.producer import Producer, ProducerKeyword
from src.apps.wine.wine.models.wine import (
    Wine,
    WineDb,
    WineDuplication,
    WineKeyword,
    WineNoise,
)
from src.apps.wine.wine_addition.models.wine_addition import WineAddition
from src.apps.wine.wine_addition.schemas.wine_addition import WineAdditionSchema
from src.core.exceptions import APIException
from src.utils.enums import SourceEnum
from src.utils.helpers.functions import join_pattern


def detect_encoding(file: UploadFile) -> str:
    raw = file.file.read(1024)
    file.file.seek(0)
    result = chardet.detect(raw)
    return result["encoding"] or "utf-8"


async def upload_for_keyword_generator(db: Session, file: UploadFile = File(...)):
    if not file:
        raise APIException(
            module=__name__,
            error={},
            status_code=status.HTTP_400_BAD_REQUEST,
            message="Please select file to upload.",
        )

    encoding = detect_encoding(file)
    content = await file.read()
    buffer = io.BytesIO(content)

    # Determine file type by extension
    filename = file.filename.lower()
    if filename.endswith(".csv"):
        df = pl.read_csv(buffer, encoding=encoding)
        lines = df.select(df.columns[0]).to_series().to_list()
    elif filename.endswith(".txt"):
        buffer.seek(0)
        text = buffer.read().decode(encoding)
        lines = [line.strip() for line in text.splitlines() if line.strip()]
    else:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST, detail="Unsupported file type. Please upload a .csv or .txt file."
        )

    if len(lines) <= 1:
        return JSONResponse(content={"success": True, "data": []})

    wine_ids = lines[1:]  # skip header line

    # Fetch wines and associated data
    wines = (
        db.query(
            WineDb.id,
            WineDb.literal,
            WineDb.name,
            WineDb.color,
            WineDb.producer_id,
            Producer.name.label("producer_show"),
            ProducerKeyword.keyword.label("producer_keyword"),
            Producer.name_show,
            # WineDb.producer.name_show,
            # WineDb.producer.producer_keyword.keyword
        )
        .join(Producer, WineDb.producer_id == Producer.id)
        .join(ProducerKeyword, Producer.id == ProducerKeyword.producer_id)
        .filter(WineDb.literal.in_(wine_ids))
        .all()
    )

    wine_ids = [wine[0] for wine in wines]

    noises = db.query(WineNoise.noise, WineNoise.wine_db_id).filter(WineNoise.wine_db_id.in_(wine_ids)).all()

    # Group noises by wine_id
    noise_map = {}
    for noise, wine_id in noises:
        noise_map.setdefault(wine_id, []).append(noise)

    # Build the response
    wines_data = []
    for wine in wines:
        wine_id = wine[0]
        wine_noises = sorted(noise_map.get(wine_id, []), key=lambda x: len(x), reverse=True)
        wines_data.append(
            {
                "wineDbId": wine[1],
                "wineDbName": wine[2],
                "color": wine[3],
                "producerShow": wine[7],
                "producerKeyword": wine[6],
                "noises": wine_noises,
            }
        )

    return wines_data


async def save_keywords_to_db(db: Session, keywords: List[KeywordSave]):
    if not keywords:
        return
    for keyword in keywords:
        wine_literal = keyword.wine_literal
        if not wine_literal:
            continue

        # Validate wine_id exists in the database
        wine = db.query(WineDb).filter(WineDb.literal == wine_literal).first()
        if not wine:
            raise APIException(
                module=__name__,
                error={},
                status_code=status.HTTP_404_NOT_FOUND,
                message=f"Wine with ID {wine_literal} not found.",
            )

        wine_id = wine.id

        existing_keywords: WineKeyword = db.query(WineKeyword).filter(WineKeyword.wine_db_id == wine_id).first()
        if existing_keywords:
            existing_keywords.producer_keyword = keyword.producer_keyword
            existing_keywords.base_keyword = keyword.base_keyword
            existing_keywords.appellation_keyword = keyword.appellation_keyword
            existing_keywords.color_keyword = keyword.color_keyword
            existing_keywords.modifiers_keyword = keyword.modifiers_keyword
            existing_keywords.global_not_keyword = keyword.global_not_keyword
            existing_keywords.common_modifiers_keyword = keyword.common_modifiers_keyword
            # Update pattern based on the new values
            pattern = join_pattern(existing_keywords)
            existing_keywords.pattern = pattern if pattern else None
            existing_keywords.source = SourceEnum.KEYWORD_GENERATOR.value
        else:
            new_keyword = WineKeyword(
                wine_db_id=wine_id,
                producer_keyword=keyword.producer_keyword,
                base_keyword=keyword.base_keyword,
                appellation_keyword=keyword.appellation_keyword,
                color_keyword=keyword.color_keyword,
                modifiers_keyword=keyword.modifiers_keyword,
                global_not_keyword=keyword.global_not_keyword,
                common_modifiers_keyword=keyword.common_modifiers_keyword,
            )
            pattern = join_pattern(new_keyword)
            new_keyword.pattern = pattern if pattern else None
            new_keyword.source = SourceEnum.KEYWORD_GENERATOR.value
            db.add(new_keyword)
        db.commit()

    return {"message": "Keywords saved successfully."}


def strip_accents(text: Optional[str]) -> Optional[str]:
    if not text:
        return text
    return "".join(c for c in unicodedata.normalize("NFD", text) if unicodedata.category(c) != "Mn")


async def upload_wines_addition(db: Session, file: UploadFile = File(...)):
    if not file:
        raise APIException(
            module=__name__,
            error={},
            status_code=status.HTTP_400_BAD_REQUEST,
            message="Please select file to upload.",
        )

    encoding = detect_encoding(file)
    content = await file.read()
    buffer = io.BytesIO(content)

    # Determine file type by extension
    filename = file.filename.lower()
    if filename.endswith(".csv"):
        df = pl.read_csv(buffer, encoding=encoding)
        lines = df.select(df.columns[0]).to_series().to_list()
    elif filename.endswith(".txt"):
        buffer.seek(0)
        text = buffer.read().decode(encoding)
        lines = [line.strip() for line in text.splitlines() if line.strip()]
    else:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST, detail="Unsupported file type. Please upload a .csv or .txt file."
        )

    if len(lines) <= 1:
        return JSONResponse(content={"success": True, "data": []})

    # Parse each line into WineAddition
    wine_additions = []
    for idx, line in enumerate(lines):
        if idx == 0:  # Skip header
            continue

        parts = line.split("\t")
        if not parts or len(parts) < 4:
            continue  # Skip malformed or empty rows

        vintage = parts[3].strip()
        if vintage.lower() == "n.v.":
            vintage = "NV"
        wine_addition = WineAddition(
            literal=parts[0].strip() if len(parts) > 0 else None,
            wine_vinid=parts[1].strip() if len(parts) > 1 else None,
            region_code=parts[2].strip() if len(parts) > 2 else None,
            vintage=vintage,
            variety=strip_accents(parts[4].strip()) if len(parts) > 4 else None,
            label=strip_accents(parts[5].strip()) if len(parts) > 5 else None,
            producer_name=strip_accents(parts[6].strip()) if len(parts) > 6 else None,
            producer_name_show=strip_accents(parts[7].strip()) if len(parts) > 7 else None,
            country=strip_accents(parts[8].strip()) if len(parts) > 8 else None,
            region=strip_accents(parts[9].strip()) if len(parts) > 9 else None,
            location=strip_accents(parts[10].strip()) if len(parts) > 10 else None,
            locale=strip_accents(parts[11].strip()) if len(parts) > 11 else None,
            site=strip_accents(parts[12].strip()) if len(parts) > 12 else None,
            sweetness=parts[13].strip() if len(parts) > 13 else None,
            color=strip_accents(parts[14].strip()) if len(parts) > 14 else None,
            wine_type=parts[15].strip() if len(parts) > 15 else None,
        )
        db.add(wine_addition)
        db.commit()
        db.refresh(wine_addition)
        wine_additions.append(WineAdditionSchema.model_validate(wine_addition))

    return wine_additions


def check_addition_matched(db: Session, literal: Optional[str] = None):
    if not literal:
        return {"id": "", "description": ""}

    # Look up in Wine
    wine = db.query(Wine).filter(Wine.literal.like(f"%{literal}%")).first()
    if wine:
        return {"id": str(wine.id), "description": wine.wineDb.description() if wine.wineDb else ""}

    # Look up in WineDuplication
    duplication = db.query(WineDuplication).filter(WineDuplication.literal.like(f"%{literal}%")).first()
    if duplication and duplication.wine:
        return {
            "id": str(duplication.wine.id),
            "description": duplication.wine.wineDb.description() if duplication.wine.wineDb else "",
        }

    # Nothing found
    return {"id": "", "description": ""}