"""
CHECK_FILE upload deduplication helpers.

Strip leading filename timestamps, find fuzzy duplicates in the same folder/subfolder,
and decide proceed / skip / replace based on freshness.
"""
from __future__ import annotations

import os
import re
from datetime import datetime
from typing import Any, Dict, List, Literal, Optional, Tuple

UploadAction = Literal["proceed", "skip", "replace"]

# DB file tuple: original_filename=2, file_path=4, relative_path=6, upload_date=7
_IDX_ORIGINAL = 2
_IDX_FILE_PATH = 4
_IDX_UPLOAD_DATE = 7

_COMPACT_DATE = re.compile(r"^\d{8}$")
_COMPACT_DATETIME = re.compile(r"^\d{14}$")
_ISO_DATE = re.compile(r"^\d{4}-\d{2}-\d{2}$")
_ISO_DATETIME = re.compile(r"^\d{4}-\d{2}-\d{2}[ T]\d{2}:\d{2}(:\d{2})?$")
# Training uploads: Unix seconds prefix + underscore, e.g. 01776717100_report.pdf
_UNIX_PREFIX_UNDERSCORE = re.compile(r"^(\d{10,11})_(.+)$")
_UNIX_TOKEN = re.compile(r"^\d{10,11}$")
# Plausible Unix seconds for document uploads (2000-01-01 .. ~2099)
_UNIX_MIN = 946684800
_UNIX_MAX = 4102444800


def _truthy(val: Optional[str]) -> bool:
    s = (val or "").strip().lower()
    return s in ("true", "1", "yes", "y", "on")


def check_file_enabled_from_env_or_settings(settings: Any) -> bool:
    """True when CHECK_FILE is enabled via env, initial_config.txt, or Settings."""
    import os

    env = os.environ.get("CHECK_FILE", "").strip()
    if env:
        return _truthy(env)
    try:
        from app.core.config import get_initial_config

        cfg = get_initial_config()
        raw = (cfg.get("CHECK_FILE") or cfg.get("check_file") or "").strip()
        if raw:
            return _truthy(raw)
    except Exception:
        pass
    return bool(getattr(settings, "CHECK_FILE", False))


_LIKE_ESCAPE_CHAR = "!"


def escape_like_pattern(s: str) -> str:
    """Escape % and _ for SQL LIKE (use with ``ESCAPE '!'`` in SQLite)."""
    e = _LIKE_ESCAPE_CHAR
    return s.replace(e, e + e).replace("%", e + "%").replace("_", e + "_")


def sql_like_escape_char() -> str:
    """Single-character LIKE escape for SQL ``ESCAPE`` clauses."""
    return _LIKE_ESCAPE_CHAR


def _parse_unix_timestamp_token(token: str) -> Optional[datetime]:
    """Parse 10–11 digit Unix epoch seconds (optional leading zero), e.g. 01776717100."""
    t = (token or "").strip()
    if not _UNIX_TOKEN.match(t):
        return None
    try:
        secs = int(t)
    except ValueError:
        return None
    if secs < _UNIX_MIN or secs > _UNIX_MAX:
        return None
    try:
        return datetime.fromtimestamp(secs)
    except (OSError, ValueError, OverflowError):
        return None


def _parse_timestamp_token(token: str) -> Optional[datetime]:
    t = (token or "").strip()
    if not t:
        return None
    unix_dt = _parse_unix_timestamp_token(t)
    if unix_dt is not None:
        return unix_dt
    if _COMPACT_DATETIME.match(t):
        try:
            return datetime.strptime(t, "%Y%m%d%H%M%S")
        except ValueError:
            return None
    if _COMPACT_DATE.match(t):
        try:
            return datetime.strptime(t, "%Y%m%d")
        except ValueError:
            return None
    if _ISO_DATETIME.match(t):
        for fmt in ("%Y-%m-%d %H:%M:%S", "%Y-%m-%d %H:%M", "%Y-%m-%dT%H:%M:%S", "%Y-%m-%dT%H:%M"):
            try:
                return datetime.strptime(t.replace("T", " "), fmt)
            except ValueError:
                continue
        return None
    if _ISO_DATE.match(t):
        try:
            return datetime.strptime(t, "%Y-%m-%d")
        except ValueError:
            return None
    return None


def strip_leading_timestamp(basename: str) -> Tuple[str, Optional[datetime]]:
    """
    If a leading timestamp prefix is present, return (rest, parsed_dt).

    Supported prefixes (only when parse succeeds):
    - Unix seconds + underscore: ``01776717100_2026-27 Report.pdf``
    - ISO date/time + space, compact ``YYYYMMDD[HHMMSS]``, or ``YYYYMMDD`` + space
    """
    name = (basename or "").strip()
    if not name:
        return name, None

    unix_m = _UNIX_PREFIX_UNDERSCORE.match(name)
    if unix_m:
        parsed = _parse_unix_timestamp_token(unix_m.group(1))
        if parsed is not None:
            return unix_m.group(2).strip(), parsed

    iso_dt = re.match(
        r"^(\d{4}-\d{2}-\d{2}[ T]\d{2}:\d{2}(?::\d{2})?)\s+(.+)$",
        name,
    )
    if iso_dt:
        parsed = _parse_timestamp_token(iso_dt.group(1).replace("T", " "))
        if parsed is not None:
            return iso_dt.group(2).strip(), parsed

    iso_date = re.match(r"^(\d{4}-\d{2}-\d{2})\s+(.+)$", name)
    if iso_date:
        parsed = _parse_timestamp_token(iso_date.group(1))
        if parsed is not None:
            return iso_date.group(2).strip(), parsed

    if " " not in name:
        return name, None
    first, rest = name.split(" ", 1)
    parsed = _parse_timestamp_token(first)
    if parsed is None:
        return name, None
    rest = rest.strip()
    return (rest if rest else name, parsed)


def _parse_upload_date(value: Optional[str]) -> Optional[datetime]:
    if not value:
        return None
    raw = str(value).strip()
    if not raw:
        return None
    try:
        if raw.endswith("Z"):
            raw = raw[:-1] + "+00:00"
        return datetime.fromisoformat(raw)
    except ValueError:
        pass
    for fmt in ("%Y-%m-%d %H:%M:%S", "%Y-%m-%d %H:%M", "%Y-%m-%d"):
        try:
            return datetime.strptime(raw, fmt)
        except ValueError:
            continue
    return None


def file_freshness_dt(
    filename: str,
    file_path: Optional[str],
    upload_date: Optional[str],
    filename_ts: Optional[datetime] = None,
) -> Optional[datetime]:
    """Resolve a single comparable datetime for a file (priority: name ts, mtime, upload_date)."""
    if filename_ts is not None:
        return filename_ts
    _, ts = strip_leading_timestamp(os.path.basename(filename or ""))
    if ts is not None:
        return ts
    path = (file_path or "").strip()
    if path and os.path.isfile(path):
        try:
            return datetime.fromtimestamp(os.path.getmtime(path))
        except OSError:
            pass
    return _parse_upload_date(upload_date)


def pick_best_candidate(candidates: List[Tuple], search_name: str) -> Optional[Tuple]:
    """Longest original_filename containing search_name; tie-break newest upload_date."""
    if not candidates:
        return None
    sn = (search_name or "").strip().lower()
    if not sn:
        return candidates[0]

    def sort_key(row: Tuple) -> Tuple:
        orig = (row[_IDX_ORIGINAL] if len(row) > _IDX_ORIGINAL else "") or ""
        orig_l = orig.lower()
        contains = sn in orig_l
        length = len(orig_l) if contains else 0
        upload = _parse_upload_date(row[_IDX_UPLOAD_DATE] if len(row) > _IDX_UPLOAD_DATE else None)
        upload_ord = upload or datetime.min
        return (contains, length, upload_ord)

    return max(candidates, key=sort_key)


def compare_incoming_vs_existing(
    incoming_basename: str,
    incoming_ts: Optional[datetime],
    incoming_mtime: Optional[float],
    existing_row: Tuple,
) -> int:
    """
    Return 1 if incoming is strictly newer, -1 if older, 0 if tie/unknown.
    """
    existing_name = (existing_row[_IDX_ORIGINAL] if len(existing_row) > _IDX_ORIGINAL else "") or ""
    existing_path = (existing_row[_IDX_FILE_PATH] if len(existing_row) > _IDX_FILE_PATH else "") or ""
    existing_upload = existing_row[_IDX_UPLOAD_DATE] if len(existing_row) > _IDX_UPLOAD_DATE else None

    incoming_dt = file_freshness_dt(
        incoming_basename,
        None,
        None,
        filename_ts=incoming_ts,
    )
    if incoming_dt is None and incoming_mtime is not None:
        try:
            incoming_dt = datetime.fromtimestamp(incoming_mtime)
        except (OSError, ValueError):
            incoming_dt = None

    existing_dt = file_freshness_dt(existing_name, existing_path, existing_upload)

    if incoming_dt is None or existing_dt is None:
        return 0
    if incoming_dt > existing_dt:
        return 1
    if incoming_dt < existing_dt:
        return -1
    return 0


def resolve_upload_action(
    search_name: str,
    incoming_basename: str,
    incoming_ts: Optional[datetime],
    incoming_mtime: Optional[float],
    candidates: List[Tuple],
) -> Tuple[UploadAction, Optional[str], Optional[str]]:
    """
    Decide proceed / skip / replace for CHECK_FILE dedup.

    Returns (action, existing_file_id, reason).
    """
    if not candidates:
        return "proceed", None, None

    target = pick_best_candidate(candidates, search_name)
    if not target:
        return "proceed", None, None

    cmp = compare_incoming_vs_existing(
        incoming_basename, incoming_ts, incoming_mtime, target
    )
    file_id = target[0] if target else None

    if cmp > 0:
        return "replace", file_id, "check_file_replaced"
    if cmp < 0:
        return "skip", file_id, "check_file_older"
    return "skip", file_id, "check_file_older"


def evaluate_check_file_upload(
    db_service: Any,
    folder_path: str,
    relative_path: str,
    source_path: Optional[str] = None,
    agent_id: Any = None,
) -> Dict[str, Any]:
    """
    Run CHECK_FILE logic for one incoming file.

    Returns dict with keys: action, existing_file_id, reason, search_name.
    """
    rel = (relative_path or "").replace("\\", "/").strip()
    basename = os.path.basename(rel) if rel else ""
    rel_dir = os.path.dirname(rel).replace("\\", "/") if rel else ""

    search_name, incoming_ts = strip_leading_timestamp(basename)
    if not search_name:
        return {
            "action": "proceed",
            "existing_file_id": None,
            "reason": None,
            "search_name": basename,
        }

    candidates = db_service.find_files_by_name_in_subfolder(
        folder_path, rel_dir, search_name, agent_id=agent_id
    )

    incoming_mtime: Optional[float] = None
    if source_path and os.path.isfile(source_path):
        try:
            incoming_mtime = os.path.getmtime(source_path)
        except OSError:
            incoming_mtime = None

    action, existing_id, reason = resolve_upload_action(
        search_name,
        basename,
        incoming_ts,
        incoming_mtime,
        candidates,
    )
    return {
        "action": action,
        "existing_file_id": existing_id,
        "reason": reason,
        "search_name": search_name,
    }
