# ============================================================
# ADDITIVE: eSSL real-time sync (MySQL -> HRMS) and eSSL PDF report import
# ------------------------------------------------------------
# eSSL eTimeTrack stores every punch of every device in a monthly table
#   DeviceLogs_<month>_<year>   (DeviceLogId, DeviceId, UserId, LogDate, Direction ...)
# sync_source() reads ONLY the rows after the saved cursor (read-only SELECT), turns each row into a
# BiometricPunch and reconciles it into the EXISTING EMP_check_in_check_out records - the same path the
# device push API uses. Re-running is always safe (punches are unique).
#   * run it every minute:  python manage.py essl_sync --once          (cron)
#   * or as a poller:        python manage.py essl_sync --loop --interval 10 --max-seconds 55
#   * or on demand:          POST /api/attendance-hub/essl/sources/<id>/sync/
# import_report() loads the eSSL "Monthly Status Report (Basic Work Duration)" PDF the same way.
# ============================================================
import ipaddress
import re
import socket
import sqlite3
from datetime import datetime, time, timedelta

from django.conf import settings
from django.db import IntegrityError, transaction
from django.utils import timezone

from AdminCenter.common import decrypt_secret, encrypt_secret
from Employee.models import AttendanceEmp, EmployeeDT

from . import att_engine as en
from .att_models import BiometricDevice, BiometricEnrollment, BiometricPunch, EsslSource, EsslSyncRun

IDENT = re.compile(r"^[A-Za-z_][A-Za-z0-9_]{0,63}$")
PATTERN_OK = re.compile(r"^[A-Za-z_][A-Za-z0-9_{}]{0,63}$")
STALE_RUN_MINUTES = 10
MAX_BATCH = 5000


# ------------------------------------------------------------
# config validation / safety
# ------------------------------------------------------------

def check_identifier(value, label):
    if not IDENT.match(str(value or "")):
        raise ValueError(f"{label} must be a plain SQL name (letters, digits, underscore).")
    return value


def check_pattern(pattern):
    if not PATTERN_OK.match(str(pattern or "")) or "{year}" not in pattern:
        raise ValueError("table_pattern must be a plain name containing {year} (and {month} or {month2}), "
                         "e.g. DeviceLogs_{month}_{year}.")
    try:
        pattern.format(month=1, year=2026, month2="01")
    except (KeyError, IndexError, ValueError):
        raise ValueError("table_pattern may only use {month}, {month2} and {year}.")
    return pattern


def host_allowed(host):
    """Block link-local / metadata / multicast targets (and loopback unless ESSL_ALLOW_LOOPBACK); private LAN is fine."""
    try:
        infos = socket.getaddrinfo(host, None)
    except socket.gaierror:
        raise ValueError(f"Cannot resolve host '{host}'.")
    for info in infos:
        ip = ipaddress.ip_address(info[4][0])
        if ip.is_link_local or ip.is_multicast or ip.is_unspecified or ip.is_reserved:
            raise ValueError(f"Host '{host}' resolves to a blocked address ({ip}).")
        if ip.is_loopback and not getattr(settings, "ESSL_ALLOW_LOOPBACK", False):
            raise ValueError("Loopback hosts are blocked (set settings.ESSL_ALLOW_LOOPBACK = True to allow).")
    return True


def set_password(src, plain):
    src.password_enc = encrypt_secret(plain) if plain else None


def get_password(src):
    return decrypt_secret(src.password_enc) if src.password_enc else None


# ------------------------------------------------------------
# reader
# ------------------------------------------------------------

def connect(src):
    """-> (connection, placeholder). MySQL via PyMySQL / mysqlclient, SQLite only for dev & tests."""
    if src.source_type == "sqlite":
        if not getattr(settings, "ESSL_ALLOW_SQLITE_SOURCE", False):
            raise ValueError("SQLite sources are disabled (settings.ESSL_ALLOW_SQLITE_SOURCE).")
        return sqlite3.connect(f"file:{src.host}?mode=ro", uri=True, timeout=5), "?"
    host_allowed(src.host)
    pwd = get_password(src)
    try:
        import pymysql
        return pymysql.connect(host=src.host, port=src.port, user=src.username, password=pwd or "", database=src.database,
                               connect_timeout=5, read_timeout=20, write_timeout=5, charset="utf8mb4"), "%s"
    except ImportError:
        try:
            import MySQLdb
            return MySQLdb.connect(host=src.host, port=src.port, user=src.username, passwd=pwd or "", db=src.database,
                                   connect_timeout=5), "%s"
        except ImportError:
            raise ValueError("Install 'pymysql' (pip install pymysql) to read the eSSL MySQL database.")


def table_for(src, year, month):
    return check_identifier(check_pattern(src.table_pattern).format(month=month, year=year, month2=f"{month:02d}"), "table name")


def month_span(first, last):
    y, m = first.year, first.month
    while (y, m) <= (last.year, last.month):
        yield y, m
        m += 1
        if m == 13:
            y, m = y + 1, 1


def _columns(src):
    cols = [check_identifier(src.id_column, "id_column"), check_identifier(src.user_column, "user_column"),
            check_identifier(src.time_column, "time_column")]
    cols.append(check_identifier(src.device_column, "device_column") if src.device_column else "NULL")
    cols.append(check_identifier(src.direction_column, "direction_column") if src.direction_column else "NULL")
    return cols


def is_missing_table(exc):
    t = str(exc).lower()
    return "doesn't exist" in t or "no such table" in t or "unknown table" in t or "1146" in t


def fetch_rows(conn, ph, src, table, last_id, limit):
    c = _columns(src)
    cur = conn.cursor()
    try:
        cur.execute(f"SELECT {c[0]}, {c[1]}, {c[2]}, {c[3]}, {c[4]} FROM {table} WHERE {c[0]} > {ph} ORDER BY {c[0]} ASC LIMIT {int(limit)}",
                    (last_id,))
        return cur.fetchall()
    finally:
        cur.close()


def to_aware(v):
    if isinstance(v, str):
        v = v.replace("T", " ")[:19]
        v = datetime.strptime(v, "%Y-%m-%d %H:%M:%S")
    if isinstance(v, datetime) and timezone.is_naive(v):
        v = timezone.make_aware(v)
    return v


def parse_direction(v):
    t = str(v or "").strip().lower()
    if t.startswith("i"):
        return "IN"
    if t.startswith("o"):
        return "OUT"
    return None


def test_connection(src):
    """Connect, list the monthly tables that exist and count their rows. Never writes."""
    conn, ph = connect(src)
    try:
        today = timezone.localdate()
        first = src.start_from_date or today.replace(day=1)
        tables = []
        for y, m in month_span(first, today):
            t = table_for(src, y, m)
            cur = conn.cursor()
            try:
                cur.execute(f"SELECT COUNT(*), MAX({check_identifier(src.id_column, 'id_column')}) FROM {t}")
                n, mx = cur.fetchone()
                tables.append({"table": t, "exists": True, "rows": n, "max_id": mx})
            except Exception as e:
                if not is_missing_table(e):
                    raise
                tables.append({"table": t, "exists": False, "rows": 0, "max_id": None})
            finally:
                cur.close()
        return {"connected": True, "tables": tables, "message": f"Connected. {sum(1 for t in tables if t['exists'])} table(s) found."}
    finally:
        conn.close()


# ------------------------------------------------------------
# punch -> HRMS
# ------------------------------------------------------------

def ingest_punch(company, *, biometric_id, when, direction, device, device_code, verification, raw, source, actor, min_gap=0,
                 auto_enroll=False, employee=None):
    """Create (once) and reconcile one BiometricPunch. direction=None -> decided from the open check-in."""
    q = BiometricPunch.objects.filter(company=company, device_code=device_code, biometric_id=biometric_id, punch_time=when)
    if direction:
        q = q.filter(direction=direction)
    old = q.first()
    if old:
        return old, False
    if auto_enroll and not en.resolve_employee(company, biometric_id):
        emp = EmployeeDT.objects.filter(company=company, employee_code=biometric_id).first()
        if emp:
            BiometricEnrollment.objects.get_or_create(company=company, biometric_id=biometric_id, defaults={
                "employee": emp, "verification_mode": "Other", "created_by": actor, "updated_by": actor})
    payload = dict(raw or {})
    if direction is None:
        payload.update(auto_direction=True, min_gap=min_gap)
    p = BiometricPunch.objects.create(company=company, device=device, device_code=device_code, biometric_id=biometric_id, employee=employee,
                                      punch_time=when, direction=direction or "IN", verification_mode=verification,
                                      raw_payload=payload, source=source, created_by=actor, updated_by=actor)
    en.reconcile(p, actor)
    return p, True


def _count(stats, p):
    stats["accepted"] += 1
    key = {"Reconciled": "reconciled", "Unmatched": "unmatched", "Duplicate": "duplicates", "Failed": "failed"}.get(p.sync_status)
    if key:
        stats[key] += 1


# ------------------------------------------------------------
# live sync
# ------------------------------------------------------------

def _claim(src):
    """Only one run per source at a time (cron + API can overlap)."""
    with transaction.atomic():
        s = EsslSource.objects.select_for_update().get(pk=src.pk)
        if s.last_status == "Running" and s.running_since and timezone.now() - s.running_since < timedelta(minutes=STALE_RUN_MINUTES):
            return None
        s.last_status, s.running_since = "Running", timezone.now()
        s.save(update_fields=["last_status", "running_since"])
        return s


def sync_source(src, trigger="command", kind="live", max_rows=None):
    """One sync pass over the new rows. Returns the EsslSyncRun (status Success / Partial / Error)."""
    s = _claim(src)
    run = EsslSyncRun.objects.create(company=src.company, source=src, kind=kind, trigger=trigger,
                                     created_by=f"essl:{trigger}", updated_by=f"essl:{trigger}")
    if s is None:
        run.status, run.finished_at, run.error = "Error", timezone.now(), "Another sync of this source is already running."
        run.save()
        return run
    src = s
    stats = {"rows_fetched": 0, "accepted": 0, "reconciled": 0, "unmatched": 0, "duplicates": 0, "failed": 0}
    detail = {"cursor_before": dict(src.cursor or {}), "tables": {}, "unmatched_ids": []}
    status, error, conn = "Success", None, None
    try:
        conn, ph = connect(src)
        limit = min(int(max_rows or src.batch_size or 500), MAX_BATCH)
        devmap = {str(k): v for k, v in (src.device_map or {}).items()}
        devices = {d.device_code: d for d in BiometricDevice.objects.filter(company=src.company)}
        today = timezone.localdate()
        first = src.start_from_date or today.replace(day=1)
        cursor = dict(src.cursor or {})
        remaining = limit
        for y, m in month_span(first, today):
            if remaining <= 0:
                break
            key, table = f"{y}_{m}", table_for(src, y, m)
            try:
                rows = fetch_rows(conn, ph, src, table, int(cursor.get(key, 0)), remaining)
            except Exception as e:
                if is_missing_table(e):
                    detail["tables"][table] = "missing"
                    continue
                raise
            detail["tables"][table] = len(rows)
            for rid, uid, when, dev_id, direction in rows:
                stats["rows_fetched"] += 1
                remaining -= 1
                cursor[key] = max(int(cursor.get(key, 0)), int(rid))
                when = to_aware(when)
                if when is None or (src.start_from_date and when.date() < src.start_from_date) or when > timezone.now() + timedelta(minutes=5):
                    continue
                code = devmap.get(str(dev_id)) or (f"ESSL-{dev_id}" if dev_id is not None else "ESSL")
                p, created = ingest_punch(
                    src.company, biometric_id=str(uid).strip(), when=when, direction=parse_direction(direction),
                    device=devices.get(code), device_code=code, verification="eSSL", raw={"essl_log_id": int(rid), "essl_device_id": dev_id},
                    source="device", actor=f"essl:{src.name}"[:150], min_gap=src.min_gap_minutes, auto_enroll=src.auto_enroll)
                if created:
                    _count(stats, p)
                    if p.sync_status == "Unmatched" and len(detail["unmatched_ids"]) < 20 and str(uid) not in detail["unmatched_ids"]:
                        detail["unmatched_ids"].append(str(uid))
        src.cursor = cursor
        detail["cursor_after"] = cursor
        src.total_rows_synced += stats["rows_fetched"]
        BiometricDevice.objects.filter(company=src.company, device_code__in=set(devmap.values()), status="Enabled").update(
            last_seen_at=timezone.now(), last_sync_at=timezone.now())
        if stats["failed"]:
            status = "Partial"
    except Exception as e:                                         # connection problems must not crash the poller
        status, error = "Error", str(e)[:1000]
    finally:
        if conn is not None:
            try:
                conn.close()
            except Exception:
                pass
    now = timezone.now()
    src.last_status = "Success" if status in ("Success", "Partial") else "Error"
    src.last_error = error
    src.running_since = None
    if status != "Error":
        src.last_sync_at = now
    src.save()
    for k, v in stats.items():
        setattr(run, k, v)
    run.status, run.finished_at, run.error, run.detail = status, now, error, detail
    run.save()
    return run


def sync_until_empty(src, trigger="command", max_batches=20):
    """Drain a backlog: repeat passes until a pass fetches nothing (or max_batches)."""
    total = {"rows_fetched": 0, "reconciled": 0, "unmatched": 0, "duplicates": 0, "failed": 0}
    last = None
    for _ in range(max_batches):
        last = sync_source(src, trigger=trigger)
        if last.status == "Error":
            break
        for k in total:
            total[k] += getattr(last, k)
        src.refresh_from_db()
        if last.rows_fetched < (src.batch_size or 500):
            break
    return last, total


# ------------------------------------------------------------
# PDF import
# ------------------------------------------------------------

DAY_STATUS_MAP = {"P": "Present", "½P": "Half Day", "WOP": "Present", "WO½P": "Half Day"}
EXTRA_STATUS_MAP = {"A": "Absent", "WO": "Holiday"}


def _local(d, hhmm, plus_day=0):
    h, m = [int(x) for x in hhmm.split(":")]
    return timezone.make_aware(datetime.combine(d + timedelta(days=plus_day), time(h, m)))


def import_report(company, parsed, *, file_name=None, dry_run=True, employee_map=None, save_enrollments=False, auto_enroll=False,
                  day_status="present_only", overwrite_day_status=False, include_codes=None, actor="essl:pdf-import"):
    """Load a parsed eSSL monthly PDF. dry_run=True parses and matches only (nothing is written)."""
    if day_status not in ("none", "present_only", "all"):
        raise ValueError("day_status must be none, present_only or all.")
    period = parsed.get("period")
    if not period:
        raise ValueError("The report period (Aug 01 2026 To Aug 31 2026) could not be read from the PDF.")
    start = period[0]
    employee_map = {str(k): str(v) for k, v in (employee_map or {}).items()}
    wanted = {str(c) for c in include_codes} if include_codes else None
    stats = {"rows_fetched": 0, "accepted": 0, "reconciled": 0, "unmatched": 0, "duplicates": 0, "failed": 0}
    res = {"period": {"from": str(period[0]), "to": str(period[1])}, "company": parsed.get("company"), "dry_run": dry_run,
           "matched_employees": [], "unmatched_employees": [], "enrollments_created": [], "enrollment_errors": [],
           "punches_planned": 0, "already_received": 0, "day_statuses_written": 0, "day_statuses_skipped_existing": 0}
    run = None
    if not dry_run:
        run = EsslSyncRun.objects.create(company=company, kind="pdf-import", trigger="import", file_name=file_name,
                                         created_by=actor, updated_by=actor)
    plan = []
    for e in parsed["employees"]:
        code = e["code"]
        if wanted is not None and code not in wanted:
            continue
        punch_days = sum(1 for d in e["days"].values() if d["in"])
        emp = None
        if code in employee_map:
            emp = EmployeeDT.objects.filter(company=company, employee_code=employee_map[code]).first()
            if emp is None:
                res["enrollment_errors"].append({"essl_code": code, "error": f"Employee {employee_map[code]} not found."})
        if emp is None:
            emp = en.resolve_employee(company, code)
        if emp is None and auto_enroll:
            emp = EmployeeDT.objects.filter(company=company, employee_code=code).first()
        if emp is None:
            if punch_days:
                res["unmatched_employees"].append({"essl_code": code, "name": e["name"], "punch_days": punch_days})
            continue
        if not en.resolve_employee(company, code) and (code in employee_map or auto_enroll) and save_enrollments and not dry_run:
            try:
                with transaction.atomic():
                    BiometricEnrollment.objects.create(company=company, employee=emp, biometric_id=code, verification_mode="Other",
                                                       created_by=actor, updated_by=actor)
                res["enrollments_created"].append({"essl_code": code, "employee_code": emp.employee_code})
            except IntegrityError:
                res["enrollment_errors"].append({"essl_code": code, "error": "This biometric id is already enrolled for someone else."})
        res["matched_employees"].append({"essl_code": code, "name": e["name"], "employee_code": emp.employee_code, "punch_days": punch_days})
        for d, cell in sorted(e["days"].items()):
            day = start.replace(day=d)
            if cell["in"]:
                plan.append((code, emp, day, "IN", cell["in"], cell))
                if cell["out"]:
                    nxt = 1 if cell["out"] < cell["in"] else 0
                    plan.append((code, emp, day, "OUT", cell["out"], cell, nxt))
    res["punches_planned"] = len(plan)
    if dry_run:
        res["status_counts"] = {}
        for e in parsed["employees"]:
            for c in e["days"].values():
                res["status_counts"][c["status"]] = res["status_counts"].get(c["status"], 0) + 1
        return res, None
    plan.sort(key=lambda x: (x[2] if len(x) < 7 else x[2] + timedelta(days=x[6]), x[4], x[0]))
    for item in plan:
        code, emp, day, direction, hhmm, cell = item[:6]
        when = _local(day, hhmm, item[6] if len(item) > 6 else 0)
        p, created = ingest_punch(company, biometric_id=code, when=when, direction=direction, device=None, device_code="ESSL-PDF",
                                  verification="eSSL Monthly Report", raw={"status": cell["status"], "total": cell["total"],
                                                                         "report": file_name}, source="import", actor=actor, employee=emp)
        stats["rows_fetched"] += 1
        if created:
            _count(stats, p)
        else:
            res["already_received"] += 1
    # day status for payroll (existing AttendanceEmp table; the scheduler only fills yesterday)
    if day_status != "none":
        mapping = dict(DAY_STATUS_MAP)
        if day_status == "all":
            mapping.update(EXTRA_STATUS_MAP)
        for e in parsed["employees"]:
            emp = next((m for m in res["matched_employees"] if m["essl_code"] == e["code"]), None)
            if not emp:
                continue
            obj = EmployeeDT.objects.get(company=company, employee_code=emp["employee_code"])
            for d, cell in e["days"].items():
                st = mapping.get(cell["status"])
                day = start.replace(day=d)
                if st is None or day >= timezone.localdate():
                    continue
                row = AttendanceEmp.objects.filter(company=company, Employee_ID=obj.employee_code, date=day).first()
                hours = en._hours(day, cell["in"] + ":00", day, cell["out"] + ":00") if (cell["in"] and cell["out"] and cell["out"] > cell["in"]) else None
                rec = en.EMP_check_in_check_out.objects.filter(company=company, Employee_ID=obj.employee_code, login_date=day, is_active=True).order_by("id").first()
                if row is None:
                    AttendanceEmp.objects.create(Employee_ID=obj.employee_code, company=company, Name=obj.name, date=day, status=st,
                                                 total_working_hours=hours, comment=f"eSSL report {cell['status']}", check_in_id=rec,
                                                 created_by=actor, updated_by=actor)
                    res["day_statuses_written"] += 1
                elif overwrite_day_status:
                    row.status, row.total_working_hours, row.check_in_id, row.updated_by = st, hours, rec, actor
                    row.comment = f"eSSL report {cell['status']}"
                    row.save()
                    res["day_statuses_written"] += 1
                else:
                    res["day_statuses_skipped_existing"] += 1
    for k, v in stats.items():
        setattr(run, k, v)
    run.status = "Partial" if stats["failed"] or stats["unmatched"] else "Success"
    run.finished_at = timezone.now()
    run.detail = {k: res[k] for k in ("period", "matched_employees", "unmatched_employees", "enrollments_created", "day_statuses_written")}
    run.save()
    res["stats"] = stats
    return res, run
