# ============================================================
# ADDITIVE: eSSL real-time sync + PDF import - Admin / HR API
# ------------------------------------------------------------
# Mounted under /api/attendance-hub/essl/   (see People/att_urls.py)
# These endpoints touch database credentials and write attendance for many people, so they need
# can_manage_attendance (or Company Admin); read-only listing needs can_view_attendance.
#
#   CRUD   essl/sources/                    connection to the eSSL MySQL (password is write-only, stored encrypted)
#   POST   essl/sources/test/               test a config WITHOUT saving it
#   POST   essl/sources/<id>/test/          test a saved source (lists the DeviceLogs_<m>_<y> tables)
#   POST   essl/sources/<id>/sync/          one sync pass now   (?drain=true repeats until nothing is left)
#   POST   essl/sources/sync-all/           every enabled source
#   POST   essl/sources/<id>/reset-cursor/  {mode: latest | start | set, month?, log_id?}
#   GET    essl/runs/  essl/runs/<id>/      sync / import history          DELETE essl/runs/<id>/
#   POST   essl/import/pdf/                 multipart: file=<eSSL Monthly Status Report PDF>, dry_run=true|false, ...
#   (cron)  python manage.py essl_sync --once       / --loop --interval 10 --max-seconds 55
# ============================================================
import json
from datetime import date

from django.db import IntegrityError, transaction
from django.utils import timezone
from rest_framework.decorators import action
from rest_framework.parsers import FormParser, JSONParser, MultiPartParser
from rest_framework.permissions import BasePermission
from rest_framework.viewsets import ViewSet

from . import att_engine as en
from . import essl_pdf, essl_sync as es
from .att_models import EsslSource, EsslSyncRun
from .common import err, iso, ok, paginate, to_bool, to_date, to_int
from .views_att import AttCrud, AttMixin, AttView, attendance_allows

MAX_PDF_BYTES = 25 * 1024 * 1024


class IsAttendanceManager(BasePermission):
    """Read = attendance view; everything else = attendance MANAGE (or Company Admin)."""
    message = "Managing the eSSL sync needs the 'manage attendance' permission."

    def has_permission(self, request, view):
        return attendance_allows(request.user, "view" if request.method in ("GET", "HEAD", "OPTIONS") else "manage")


class ManageMixin(AttMixin):
    permission_classes = [IsAttendanceManager]


def source_row(s, request=None):
    return {"id": s.id, "name": s.name, "source_type": s.source_type, "host": s.host, "port": s.port, "database": s.database,
            "username": s.username, "password_set": bool(s.password_enc), "table_pattern": s.table_pattern,
            "id_column": s.id_column, "user_column": s.user_column, "time_column": s.time_column, "device_column": s.device_column,
            "direction_column": s.direction_column, "device_map": s.device_map, "start_from_date": iso(s.start_from_date),
            "batch_size": s.batch_size, "poll_interval_seconds": s.poll_interval_seconds, "min_gap_minutes": s.min_gap_minutes,
            "auto_enroll": s.auto_enroll, "enabled": s.enabled, "cursor": s.cursor, "last_status": s.last_status,
            "last_sync_at": iso(s.last_sync_at), "last_sync_label": en.ago(s.last_sync_at), "last_error": s.last_error,
            "total_rows_synced": s.total_rows_synced, "created_at": iso(s.created_at), "updated_at": iso(s.updated_at)}


def run_row(r):
    return {"id": r.id, "source_id": r.source_id, "source": r.source.name if r.source_id else None, "kind": r.kind, "trigger": r.trigger,
            "status": r.status, "started_at": iso(r.started_at), "finished_at": iso(r.finished_at),
            "duration_seconds": round((r.finished_at - r.started_at).total_seconds(), 2) if r.finished_at else None,
            "rows_fetched": r.rows_fetched, "accepted": r.accepted, "reconciled": r.reconciled, "unmatched": r.unmatched,
            "duplicates": r.duplicates, "failed": r.failed, "file_name": r.file_name, "error": r.error, "detail": r.detail}


class EsslSourceViewSet(ManageMixin, AttCrud):
    """CRUD essl/sources/   {name, source_type?, host, port?, database, username, password, table_pattern?, id_column?, user_column?,
                             time_column?, device_column?, direction_column?, device_map?, start_from_date?, batch_size?,
                             poll_interval_seconds?, min_gap_minutes?, auto_enroll?, enabled?}"""
    model = EsslSource
    code = "ESSL_SOURCE"
    soft_delete = False
    status_field = "last_status"
    search_fields = ("name", "host", "database")
    dup_message = "A source with this name already exists."
    permission_action_map = {}

    def build(self, s):
        return source_row(s, self.request)

    def apply(self, s, d, creating):
        co = s.company
        if creating or "name" in d:
            name = str(d.get("name") or "").strip()
            if not name:
                raise ValueError("name is required.")
            if EsslSource.objects.filter(company=co, name__iexact=name).exclude(pk=s.pk).exists():
                raise IntegrityError
            s.name = name
        if "source_type" in d:
            if d["source_type"] not in dict(EsslSource.TYPES):
                raise ValueError("source_type must be mysql (or sqlite for development).")
            s.source_type = d["source_type"]
        if creating or "host" in d:
            if not str(d.get("host") or "").strip():
                raise ValueError("host is required.")
            s.host = str(d["host"]).strip()[:255]
        if "port" in d:
            p = to_int(d["port"])
            if p is None or not (1 <= p <= 65535):
                raise ValueError("port must be between 1 and 65535.")
            s.port = p
        for f in ("database", "username"):
            if f in d:
                setattr(s, f, d[f] or None)
        if "password" in d:
            es.set_password(s, d["password"])
        if "table_pattern" in d:
            s.table_pattern = es.check_pattern(d["table_pattern"])
        for f in ("id_column", "user_column", "time_column"):
            if f in d:
                setattr(s, f, es.check_identifier(d[f], f))
        for f in ("device_column", "direction_column"):
            if f in d:
                setattr(s, f, es.check_identifier(d[f], f) if d[f] else None)
        if "device_map" in d:
            dm = d["device_map"] or {}
            if not isinstance(dm, dict) or not all(isinstance(k, (str, int)) and isinstance(v, str) for k, v in dm.items()):
                raise ValueError("device_map must be an object like {\"1\": \"BIO-DEV-01\"}.")
            s.device_map = {str(k): v for k, v in dm.items()}
        if "start_from_date" in d:
            s.start_from_date = to_date(d["start_from_date"])
        for f, lo, hi in (("batch_size", 1, es.MAX_BATCH), ("poll_interval_seconds", 5, 3600), ("min_gap_minutes", 0, 60)):
            if f in d:
                v = to_int(d[f])
                if v is None or not (lo <= v <= hi):
                    raise ValueError(f"{f} must be between {lo} and {hi}.")
                setattr(s, f, v)
        for f in ("auto_enroll", "enabled"):
            if f in d:
                setattr(s, f, to_bool(d[f]))
        if creating:
            es.check_pattern(s.table_pattern)
            for f in ("id_column", "user_column", "time_column"):
                es.check_identifier(getattr(s, f), f)

    def _src(self, pk):
        return self.scope().filter(pk=pk, is_active=True).first() if str(pk).isdigit() else None

    @staticmethod
    def _test(src):
        try:
            return {**es.test_connection(src)}
        except Exception as e:
            return {"connected": False, "tables": [], "message": str(e)[:300]}

    @action(detail=False, methods=["post"], url_path="test")
    def test_new(self, request):
        from .att_models import EsslSource as M
        s = M(company=self.company())
        try:
            self.apply(s, request.data, True)
        except (ValueError, IntegrityError) as e:
            return err("VALIDATION", str(e))
        return ok("ESSL_SOURCE_TEST", "Connection test.", self._test(s))

    @action(detail=True, methods=["post"], url_path="test")
    def test_saved(self, request, pk=None):
        s = self._src(pk)
        if s is None:
            return err("NOT_FOUND", "Source not found.", 404)
        return ok("ESSL_SOURCE_TEST", "Connection test.", self._test(s))

    @action(detail=True, methods=["post"])
    def sync(self, request, pk=None):
        s = self._src(pk)
        if s is None:
            return err("NOT_FOUND", "Source not found.", 404)
        if not s.enabled:
            return err("CONFLICT", "This source is disabled.", 409)
        if to_bool(request.query_params.get("drain")):
            run, total = es.sync_until_empty(s, trigger="api")
        else:
            run, total = es.sync_source(s, trigger="api", kind="manual"), None
        s.refresh_from_db()
        return ok("ESSL_SYNC_DONE" if run.status != "Error" else "ESSL_SYNC_FAILED", f"Sync {run.status.lower()}.",
                  {"run": run_row(run), "totals": total, "source": source_row(s)}, 200)

    @action(detail=False, methods=["post"], url_path="sync-all")
    def sync_all(self, request):
        rows = [run_row(es.sync_source(s, trigger="api", kind="manual")) for s in self.scope().filter(is_active=True, enabled=True)]
        return ok("ESSL_SYNC_ALL", "All enabled sources synced.", {"sources": len(rows), "runs": rows})

    @action(detail=True, methods=["post"], url_path="reset-cursor")
    def reset_cursor(self, request, pk=None):
        """mode=latest: start AFTER everything already in eSSL | start: re-read the month(s) from the first row | set: month + log_id"""
        s = self._src(pk)
        if s is None:
            return err("NOT_FOUND", "Source not found.", 404)
        mode = request.data.get("mode")
        try:
            if mode == "start":
                s.cursor = {}
            elif mode == "set":
                y, m = [int(x) for x in str(request.data.get("month")).split("-")]
                lid = to_int(request.data.get("log_id"))
                if lid is None or lid < 0 or not (1 <= m <= 12):
                    raise ValueError("send month (YYYY-M) and log_id >= 0.")
                s.cursor = {**(s.cursor or {}), f"{y}_{m}": lid}
            elif mode == "latest":
                today, cursor = timezone.localdate(), {}
                conn, ph = es.connect(s)
                try:
                    for y, m in es.month_span(s.start_from_date or today.replace(day=1), today):
                        cur = conn.cursor()
                        try:
                            cur.execute(f"SELECT MAX({es.check_identifier(s.id_column, 'id_column')}) FROM {es.table_for(s, y, m)}")
                            cursor[f"{y}_{m}"] = cur.fetchone()[0] or 0
                        except Exception as e:
                            if not es.is_missing_table(e):
                                raise
                        finally:
                            cur.close()
                finally:
                    conn.close()
                s.cursor = cursor
            else:
                raise ValueError("mode must be latest, start or set.")
        except (ValueError, TypeError) as e:
            return err("VALIDATION", str(e))
        except Exception as e:
            return err("CONNECTION_FAILED", str(e)[:300], 502)
        s.updated_by = self.actor()
        s.save()
        return ok("ESSL_CURSOR_RESET", "Cursor updated.", source_row(s))


class EsslRunViewSet(ManageMixin, ViewSet):
    """GET essl/runs/ ?source_id= ?status= ?kind=   GET essl/runs/<id>/   DELETE essl/runs/<id>/"""
    permission_action_map = {}

    def _qs(self):
        return EsslSyncRun.objects.filter(company=self.company(), is_active=True).select_related("source")

    def list(self, request):
        qs, p = self._qs(), request.query_params
        for f, lk in (("source_id", "source_id"), ("status", "status"), ("kind", "kind")):
            if p.get(f):
                qs = qs.filter(**{lk: p[f]})
        rows, meta = paginate(request, qs, run_row)
        return ok("ESSL_RUN_LIST", "Sync history.", rows, meta=meta)

    def retrieve(self, request, pk=None):
        r = self._qs().filter(pk=pk).first() if str(pk).isdigit() else None
        return ok("ESSL_RUN_DETAIL", "Sync run.", run_row(r)) if r else err("NOT_FOUND", "Record not found.", 404)

    def destroy(self, request, pk=None):
        r = self._qs().filter(pk=pk).first() if str(pk).isdigit() else None
        if r is None:
            return err("NOT_FOUND", "Record not found.", 404)
        r.delete()
        return ok("ESSL_RUN_DELETED", "Deleted.", {"id": int(pk)})


class EsslPdfImportView(ManageMixin, AttView):
    """
    POST /api/attendance-hub/essl/import/pdf/   multipart/form-data
      file                 the eSSL "Monthly Status Report (Basic Work Duration)" PDF            (required)
      dry_run              true (default) = parse + match only, nothing is saved; false = import
      employee_map         JSON {"4": "EMP0504", "320": "EMP0320"}  eSSL Emp. Code -> HRMS employee_code
      save_enrollments     true = remember the mapping as biometric enrollments (so live sync recognises them)
      auto_enroll          true = an eSSL code that equals an HRMS employee_code is matched automatically
      day_status           none | present_only (default: P, WOP, half days) | all (also A / WO)   -> existing AttendanceEmp rows
      overwrite_day_status true = replace existing AttendanceEmp rows of those days (default false)
      include_codes        comma list of eSSL codes to import (default: all)
    Safe to repeat: punches are unique, so a second import only adds what is new.
    """
    parser_classes = [MultiPartParser, FormParser, JSONParser]
    permission_action_map = {}

    def post(self, request):
        f = request.FILES.get("file")
        if f is None:
            return err("VALIDATION", "Upload the PDF in the 'file' field (multipart/form-data).", errors={"file": ["This field is required."]})
        if f.size > MAX_PDF_BYTES:
            return err("VALIDATION", "The PDF is larger than 25 MB.")
        head = f.read(5)
        f.seek(0)
        if head != b"%PDF-":
            return err("VALIDATION", "The uploaded file is not a PDF.")
        d = request.data
        try:
            emap = d.get("employee_map") or {}
            if isinstance(emap, str):
                emap = json.loads(emap) if emap.strip() else {}
            if not isinstance(emap, dict):
                raise ValueError("employee_map must be an object.")
            codes = d.get("include_codes")
            codes = [c.strip() for c in codes.split(",") if c.strip()] if isinstance(codes, str) else (codes or None)
            parsed = essl_pdf.parse_report(f)
            result, run = es.import_report(
                self.company(), parsed, file_name=f.name, dry_run=to_bool(d.get("dry_run", "true")), employee_map=emap,
                save_enrollments=to_bool(d.get("save_enrollments")), auto_enroll=to_bool(d.get("auto_enroll")),
                day_status=d.get("day_status") or "present_only", overwrite_day_status=to_bool(d.get("overwrite_day_status")),
                include_codes=codes, actor=self.actor())
        except (ValueError, json.JSONDecodeError) as e:
            return err("VALIDATION", str(e))
        result["report"] = {**essl_pdf.summarize(parsed), "company": parsed["company"], "printed_on": parsed["printed_on"], "file_name": f.name}
        result["run_id"] = run.id if run else None
        return ok("ESSL_PDF_PREVIEW" if result["dry_run"] else "ESSL_PDF_IMPORTED",
                  "Report parsed (nothing saved)." if result["dry_run"] else "Report imported.", result, 200 if result["dry_run"] else 201)
