import csv
import io
from typing import BinaryIO

from email_validator import EmailNotValidError
from email_validator import validate_email as _validate_email
from snowflake.snowpark import Session

from backend.dtos import BulkDeleteResult
from backend.queries import insert_deleted_profiles

MAX_EMAILS = 100_000


def parse_emails_from_csv(file: BinaryIO) -> tuple[list[str], list[str]]:
    raw_content = file.read()
    if isinstance(raw_content, bytes):
        content = raw_content.decode("utf-8-sig")
    else:
        content = raw_content.lstrip("")

    reader = csv.reader(io.StringIO(content))
    rows = list(reader)

    if not rows:
        return [], ["CSV file is empty."]

    if len(rows) < 2:
        return [], ["CSV file contains only a header row with no data."]

    email_col = 0

    emails: list[str] = []
    errors: list[str] = []

    for row_num, row in enumerate(rows[1:], start=2):
        if not row or all(cell.strip() == "" for cell in row):
            continue

        if len(row) <= email_col:
            errors.append(f"Row {row_num}: missing email column.")
            continue

        raw_email = row[email_col].strip().lower()

        if not raw_email:
            errors.append(f"Row {row_num}: email is empty.")
            continue

        try:
            _validate_email(raw_email, check_deliverability=False)
        except EmailNotValidError:
            errors.append(f"Row {row_num}: invalid email '{raw_email}'.")
            continue

        emails.append(raw_email)

    deduped = list(dict.fromkeys(emails))

    if len(deduped) > MAX_EMAILS:
        return [], [
            f"CSV contains {len(deduped):,} unique emails, which exceeds the limit of {MAX_EMAILS:,}."
        ]

    return deduped, errors


def bulk_delete_emails_from_csv(
    session: Session, file: BinaryIO, user: str = "UNKNOWN", env: str = "QA"
) -> BulkDeleteResult:
    emails, errors = parse_emails_from_csv(file)

    if errors:
        return BulkDeleteResult(inserted_count=0, errors=errors)

    if not emails:
        return BulkDeleteResult(inserted_count=0, errors=[])

    inserted_count = insert_deleted_profiles(session, emails, user, env)

    return BulkDeleteResult(inserted_count=inserted_count, errors=[])