"""Authorized, replay-safe PChome sales report acquisition and import."""

from __future__ import annotations

import json
import logging
import os
import uuid
from datetime import datetime
from pathlib import Path
from typing import Dict, List, Optional

import pytz
from sqlalchemy import text

from database.import_models import SalesAcquisitionReceipt
from services.google_drive_service import drive_service
from services.import_service import Session, humanize_import_error, import_service
from services.pchome_sales_acquisition_providers import (
    AcquisitionCandidate,
    AuthorizedSalesProviders,
    ProviderError,
    env_bool,
    env_int,
)
from services.pchome_sales_source_reconciliation import build_source_reconciliation
from services.pchome_sales_freshness_policy import evaluate_pchome_sales_freshness


logger = logging.getLogger(__name__)
TAIPEI_TZ = pytz.timezone("Asia/Taipei")
WORK_ITEM_ID = "P0-PCHOME-SALES-AUTHORIZED-ACQUISITION"


class PChomeSalesAcquisitionService:
    """Acquire authorized reports and close the import loop without manual review."""

    def __init__(self, providers: Optional[AuthorizedSalesProviders] = None):
        self.providers = providers or AuthorizedSalesProviders()

    def _freshness(self) -> Dict[str, object]:
        latest = None
        try:
            session = Session()
            try:
                latest = session.execute(
                    text("SELECT MAX(snapshot_date) FROM daily_sales_snapshot")
                ).scalar()
            finally:
                session.close()
        except Exception:
            logger.warning("Unable to read PChome sales freshness", exc_info=True)

        return evaluate_pchome_sales_freshness(
            latest,
            now=datetime.now(TAIPEI_TZ),
        )

    def _latest_receipt(self) -> Optional[Dict[str, object]]:
        session = Session()
        try:
            receipt = session.query(SalesAcquisitionReceipt).order_by(
                SalesAcquisitionReceipt.started_at.desc()
            ).first()
            return receipt.to_public_dict() if receipt else None
        except Exception:
            logger.warning("Unable to read latest sales acquisition receipt", exc_info=True)
            return None
        finally:
            session.close()

    def _recent_receipts(self, limit: int = 48) -> List[Dict[str, object]]:
        session = Session()
        try:
            receipts = session.query(SalesAcquisitionReceipt).order_by(
                SalesAcquisitionReceipt.started_at.desc()
            ).limit(max(1, min(limit, 336))).all()
            return [receipt.to_public_dict() for receipt in receipts]
        except Exception:
            logger.warning("Unable to read sales acquisition history", exc_info=True)
            return []
        finally:
            session.close()

    def _source_inventory(self) -> List[Dict[str, object]]:
        try:
            drive = drive_service.check_auth_readiness(refresh_expired=False)
            drive_ready = bool(drive.get("ready"))
            drive_status = drive.get("kind") or "unknown"
        except Exception:
            drive_ready = False
            drive_status = "probe_failed"

        http_enabled = env_bool("PCHOME_SALES_HTTP_ENABLED")
        http_ready = bool(
            http_enabled
            and os.getenv("PCHOME_SALES_HTTP_URL", "").strip()
            and os.getenv("PCHOME_SALES_HTTP_ALLOWED_HOSTS", "").strip()
        )
        imap_enabled = env_bool("PCHOME_SALES_IMAP_ENABLED")
        imap_ready = bool(
            imap_enabled
            and os.getenv("PCHOME_SALES_IMAP_HOST", "").strip()
            and os.getenv("PCHOME_SALES_IMAP_USER", "").strip()
            and os.getenv("PCHOME_SALES_IMAP_PASSWORD", "").strip()
        )
        local_path = os.getenv("PCHOME_SALES_LOCAL_DROP_DIR", "").strip()
        local_ready = bool(local_path and Path(local_path).is_dir())
        return [
            {"id": "google_drive", "enabled": True, "ready": drive_ready, "status": drive_status},
            {
                "id": "authorized_https",
                "enabled": http_enabled,
                "ready": http_ready,
                "status": "ready" if http_ready else "disabled" if not http_enabled else "incomplete",
            },
            {
                "id": "authorized_imap",
                "enabled": imap_enabled,
                "ready": imap_ready,
                "status": "ready" if imap_ready else "disabled" if not imap_enabled else "incomplete",
            },
            {
                "id": "controlled_local_drop",
                "enabled": bool(local_path),
                "ready": local_ready,
                "status": "ready" if local_ready else "disabled" if not local_path else "path_missing",
            },
        ]

    def readiness(self) -> Dict[str, object]:
        sources = self._source_inventory()
        ready_count = sum(1 for source in sources if source["ready"])
        freshness = self._freshness()
        last_receipt = self._latest_receipt()
        last_stages = (last_receipt or {}).get("stages") or {}
        return {
            "success": True,
            "automation_mode": "controlled_apply",
            "manual_review_required": False,
            "risk_level": "medium",
            "source_priority": [source["id"] for source in sources],
            "sources": sources,
            "asset_coverage": {
                "ready": ready_count,
                "total": len(sources),
                "percent": round(ready_count / len(sources) * 100, 1),
            },
            "freshness": freshness,
            "runtime_closure": "closed" if freshness["decision_ready"] else "blocked_by_upstream_freshness",
            "source_reconciliation": last_stages.get("source_reconciliation"),
            "last_receipt": last_receipt,
        }

    def _persist_receipt(
        self,
        *,
        receipt_id: str,
        trace_id: str,
        span_id: str,
        run_id: str,
        status: str,
        source_type: str,
        candidate: Optional[AcquisitionCandidate],
        decision: str,
        before: Dict[str, object],
        stages: Dict[str, object],
        verifier: Dict[str, object],
        import_job_id: Optional[int],
        rows_imported: int,
        error_kind: Optional[str],
        message: str,
        started_at: datetime,
    ) -> bool:
        session = Session()
        try:
            session.add(SalesAcquisitionReceipt(
                receipt_id=receipt_id,
                trace_id=trace_id,
                span_id=span_id,
                run_id=run_id,
                work_item_id=WORK_ITEM_ID,
                status=status,
                source_type=source_type,
                source_fingerprint=candidate.fingerprint if candidate else None,
                source_ref_hash=candidate.source_ref_hash if candidate else None,
                source_file_name=candidate.file_name if candidate else None,
                risk_level="medium",
                policy_decision="controlled_apply_allowed",
                decision=decision,
                import_job_id=import_job_id,
                rows_imported=rows_imported,
                before_freshness_json=json.dumps(before, ensure_ascii=False),
                stage_receipts_json=json.dumps(stages, ensure_ascii=False),
                verifier_json=json.dumps(verifier, ensure_ascii=False),
                error_kind=error_kind,
                public_message=message,
                started_at=started_at.replace(tzinfo=None),
                completed_at=datetime.now(TAIPEI_TZ).replace(tzinfo=None),
            ))
            session.commit()
            return True
        except Exception:
            session.rollback()
            logger.error("Unable to persist PChome sales acquisition receipt", exc_info=True)
            return False
        finally:
            session.close()

    def run(self, *, trigger: str = "scheduler") -> Dict[str, object]:
        started_at = datetime.now(TAIPEI_TZ)
        receipt_id = uuid.uuid4().hex
        trace_id = uuid.uuid4().hex
        span_id = uuid.uuid4().hex[:16]
        run_id = uuid.uuid4().hex
        before = self._freshness()
        source_inventory = self._source_inventory()
        source_by_id = {str(source["id"]): source for source in source_inventory}
        prior_receipts = self._recent_receipts()
        stages: Dict[str, object] = {
            "sensor": {"status": "started", "trigger": trigger, "trace_id": trace_id, "span_id": span_id},
            "normalize": {"asset_id": "pchome.daily_sales.authorized_report"},
            "source_diff": {"before": before},
            "ai_decision": {"decision": "probe_authorized_sources_in_policy_order"},
            "risk_policy": {"risk": "medium", "decision": "controlled_apply_allowed"},
            "check_mode": {"status": "passed", "max_files": env_int("PCHOME_SALES_MAX_FILES_PER_RUN", 5, 1, 25)},
            "providers": [],
        }
        candidates: List[AcquisitionCandidate] = []
        successful_results: List[Dict[str, object]] = []
        failed_results: List[Dict[str, object]] = []
        duplicate_results: List[Dict[str, object]] = []
        finalization_failures: List[Dict[str, object]] = []
        selected_candidate = None
        error_kind = None

        try:
            drive_result = import_service.auto_import_from_drive(send_stale_alert=False)
            stages["providers"].append({
                "source": "google_drive",
                "enabled": True,
                "ready": bool(source_by_id.get("google_drive", {}).get("ready")),
                "status": drive_result.get("status") or ("completed" if drive_result.get("success") else "failed"),
                "file_count": drive_result.get("file_count", 0),
                "imported_count": drive_result.get("imported_count", 0),
                "error_kind": drive_result.get("error_kind"),
            })
            if drive_result.get("imported_count", 0) > 0:
                successful_results.append({
                    "source_type": "google_drive",
                    "job_id": None,
                    "rows": int(drive_result.get("total_rows") or 0),
                    "date_range": drive_result.get("date_range"),
                    "result": drive_result,
                })
                if not drive_result.get("source_finalize_ok", True):
                    finalization_failures.append({
                        "source_type": "google_drive",
                        "error_kind": "source_finalize_failed",
                    })
            elif not drive_result.get("success"):
                failed_results.append({
                    "source_type": "google_drive",
                    "error_kind": drive_result.get("error_kind") or "drive_acquisition_failed",
                    "message": drive_result.get("message") or "Google Drive 授權來源目前無法取得報表。",
                })

            if not successful_results:
                for source_name, provider in (
                    ("authorized_https", self.providers.http_candidates),
                    ("authorized_imap", self.providers.imap_candidates),
                    ("controlled_local_drop", self.providers.local_candidates),
                ):
                    source_state = source_by_id.get(source_name) or {}
                    if not source_state.get("enabled"):
                        stages["providers"].append({
                            "source": source_name,
                            "enabled": False,
                            "ready": False,
                            "status": "disabled",
                            "candidate_count": 0,
                        })
                        continue
                    if not source_state.get("ready"):
                        failed_results.append({
                            "source_type": source_name,
                            "error_kind": "provider_not_ready",
                            "message": "授權來源設定尚未達到自動執行條件。",
                        })
                        stages["providers"].append({
                            "source": source_name,
                            "enabled": True,
                            "ready": False,
                            "status": "not_ready",
                            "error_kind": "provider_not_ready",
                        })
                        continue
                    try:
                        provider_candidates = provider()
                        candidates.extend(provider_candidates)
                        stages["providers"].append({
                            "source": source_name,
                            "enabled": True,
                            "ready": True,
                            "status": "candidate_found" if provider_candidates else "no_candidate",
                            "candidate_count": len(provider_candidates),
                        })
                    except ProviderError as exc:
                        failed_results.append({"source_type": source_name, "error_kind": exc.kind, "message": exc.public_message})
                        stages["providers"].append({
                            "source": source_name,
                            "enabled": True,
                            "ready": True,
                            "status": "failed",
                            "error_kind": exc.kind,
                        })

                max_files = env_int("PCHOME_SALES_MAX_FILES_PER_RUN", 5, 1, 25)
                for candidate in candidates[:max_files]:
                    selected_candidate = candidate
                    try:
                        result = import_service.import_local_daily_sales(
                            candidate.file_path,
                            source_type=candidate.source_type,
                            source_fingerprint=candidate.fingerprint,
                            source_file_name=candidate.file_name,
                        )
                        job = result.get("job") or {}
                        summary = job.get("import_summary") or {}
                        record = {
                            "source_type": candidate.source_type,
                            "job_id": result.get("job_id"),
                            "rows": int(summary.get("imported_count") or 0),
                            "date_range": (
                                {"min": summary.get("date_min"), "max": summary.get("date_max")}
                                if summary.get("date_min") and summary.get("date_max")
                                else None
                            ),
                            "status": result.get("status"),
                        }
                        if result.get("duplicate"):
                            duplicate_results.append(record)
                            try:
                                candidate.finalize_success()
                            except Exception:
                                logger.error("Duplicate source finalization failed", exc_info=True)
                                finalization_failures.append({
                                    "source_type": candidate.source_type,
                                    "error_kind": "source_finalize_failed",
                                })
                        elif result.get("success"):
                            successful_results.append(record)
                            try:
                                candidate.finalize_success()
                            except Exception:
                                logger.error("Imported source finalization failed", exc_info=True)
                                finalization_failures.append({
                                    "source_type": candidate.source_type,
                                    "error_kind": "source_finalize_failed",
                                })
                        else:
                            current_step = str(job.get("current_step") or "")
                            if "欄位" in current_step or "日期" in current_step:
                                try:
                                    candidate.finalize_rejected()
                                except Exception:
                                    logger.error("Rejected source finalization failed", exc_info=True)
                                    finalization_failures.append({
                                        "source_type": candidate.source_type,
                                        "error_kind": "source_finalize_failed",
                                    })
                            failed_results.append({**record, "error_kind": result.get("error_kind") or "import_failed_rolled_back"})
                    finally:
                        candidate.cleanup()
        except Exception as exc:
            logger.error("PChome sales acquisition run failed", exc_info=True)
            error_kind = "acquisition_runtime_error"
            failed_results.append({
                "source_type": selected_candidate.source_type if selected_candidate else "orchestrator",
                "error_kind": error_kind,
                "message": humanize_import_error(exc),
            })
        finally:
            for candidate in candidates:
                try:
                    candidate.cleanup()
                except Exception:
                    logger.warning("Unable to clean sales acquisition temp file", exc_info=True)

        for provider_stage in stages["providers"]:
            provider_source = provider_stage.get("source")
            imported_for_source = [
                item for item in successful_results
                if item.get("source_type") == provider_source
            ]
            duplicates_for_source = [
                item for item in duplicate_results
                if item.get("source_type") == provider_source
            ]
            failures_for_source = [
                item for item in failed_results
                if item.get("source_type") == provider_source
            ]
            if imported_for_source:
                provider_stage["status"] = "completed"
                provider_stage["imported_count"] = len(imported_for_source)
                provider_stage["rows_imported"] = sum(
                    int(item.get("rows") or 0) for item in imported_for_source
                )
            elif duplicates_for_source:
                provider_stage["status"] = "duplicate"
                provider_stage["duplicate_count"] = len(duplicates_for_source)
            elif failures_for_source and provider_stage.get("status") != "disabled":
                provider_stage["status"] = "failed"
                provider_stage["error_kind"] = (
                    provider_stage.get("error_kind")
                    or failures_for_source[0].get("error_kind")
                )

        verifier = self._freshness()
        rows_imported = sum(int(item.get("rows") or 0) for item in successful_results)
        import_job_id = next((item.get("job_id") for item in reversed(successful_results) if item.get("job_id")), None)
        source_types = sorted({
            item.get("source_type")
            for item in successful_results + duplicate_results + failed_results
            if item.get("source_type")
        })
        source_type = source_types[0] if len(source_types) == 1 else "multiple" if source_types else "none"
        date_mins = [
            item["date_range"]["min"]
            for item in successful_results
            if item.get("date_range") and item["date_range"].get("min")
        ]
        date_maxes = [
            item["date_range"]["max"]
            for item in successful_results
            if item.get("date_range") and item["date_range"].get("max")
        ]
        date_range = (
            {"min": min(date_mins), "max": max(date_maxes)}
            if date_mins and date_maxes
            else None
        )

        if finalization_failures and (successful_results or duplicate_results):
            status = "partial"
            decision = "runtime_verified_source_finalize_failed"
            error_kind = "source_finalize_failed"
            message = "業績資料已通過正式驗證，但來源封存回讀失敗，系統將自動重試收尾。"
        elif successful_results and verifier["decision_ready"]:
            status = "completed"
            decision = "imported_and_verified"
            message = f"已自動匯入 {rows_imported} 筆業績資料，雙表與新鮮度驗證通過。"
        elif successful_results:
            status = "partial"
            decision = "imported_but_freshness_not_closed"
            message = "業績檔已匯入並通過雙表驗證，但資料日期仍未達決策新鮮度。"
        elif duplicate_results and verifier["decision_ready"]:
            status = "completed_no_write"
            decision = "duplicate_verified_no_write"
            message = "來源內容已匯入過，冪等略過寫入且正式資料仍通過新鮮度驗證。"
        elif not failed_results and verifier["decision_ready"]:
            status = "completed_no_write"
            decision = "no_candidate_fresh_no_write"
            message = "目前沒有新業績檔，正式資料仍通過新鮮度驗證，本輪無需寫入。"
        elif failed_results:
            error_kind = error_kind or failed_results[0].get("error_kind")
            import_failed = any(
                str(item.get("error_kind") or "") in {
                    "import_failed_rolled_back",
                    "acquisition_runtime_error",
                }
                for item in failed_results
            )
            status = "failed_rolled_back" if import_failed else "degraded_no_write"
            decision = "import_failed_rolled_back" if import_failed else "provider_failed_no_write"
            message = (
                "匯入驗證未通過，正式資料已回滾。"
                if import_failed
                else "授權來源目前無法取得報表，系統將自動重試。"
            )
        else:
            status = "blocked_with_safe_next_action"
            decision = "no_authorized_candidate"
            message = "所有已啟用授權來源目前都沒有新業績檔，正式資料未變更。"

        source_reconciliation = build_source_reconciliation(
            sources=source_inventory,
            provider_attempts=stages["providers"],
            prior_receipts=prior_receipts,
            freshness=verifier,
            now=datetime.now(TAIPEI_TZ),
            retry_interval_minutes=env_int(
                "PCHOME_SALES_RETRY_INTERVAL_MINUTES", 30, 1, 1440
            ),
            max_failure_backoff_minutes=env_int(
                "PCHOME_SALES_MAX_FAILURE_BACKOFF_MINUTES", 240, 30, 1440
            ),
            alert_cooldown_minutes=env_int(
                "PCHOME_SALES_ALERT_COOLDOWN_MINUTES", 360, 30, 1440
            ),
        )
        stages["source_reconciliation"] = source_reconciliation

        stages["execution"] = {
            "status": status,
            "successful_files": len(successful_results),
            "duplicate_files": len(duplicate_results),
            "failed_files": len(failed_results),
            "source_finalize_failed": len(finalization_failures),
            "rows_imported": rows_imported,
            "idempotent": True,
            "bounded": True,
        }
        stages["post_verifier"] = verifier
        stages["terminal"] = {
            "status": status,
            "rollback": status == "failed_rolled_back",
            "no_write": status in {
                "completed_no_write",
                "blocked_with_safe_next_action",
                "failed_rolled_back",
                "degraded_no_write",
            },
        }
        stages["learning_writeback"] = {
            "target": "pchome_sales_acquisition_receipts",
            "ack": "committed_with_receipt",
            "km_rag_payload": "not_applicable_for_raw_sales_payload",
        }
        persisted = self._persist_receipt(
            receipt_id=receipt_id,
            trace_id=trace_id,
            span_id=span_id,
            run_id=run_id,
            status=status,
            source_type=source_type,
            candidate=selected_candidate,
            decision=decision,
            before=before,
            stages=stages,
            verifier=verifier,
            import_job_id=import_job_id,
            rows_imported=rows_imported,
            error_kind=error_kind,
            message=message,
            started_at=started_at,
        )
        if not persisted and status in {"completed", "completed_no_write"}:
            status = "partial"
            decision = "runtime_verified_receipt_write_failed"
            message = "業績資料已通過驗證，但治理 receipt 寫入失敗，閉環尚未完成。"

        if error_kind == "source_finalize_failed":
            safe_next_action = "retry_source_finalization"
        elif status == "failed_rolled_back":
            safe_next_action = "replay_transaction_after_rollback"
        else:
            safe_next_action = source_reconciliation.get("safe_next_action") or "none"

        return {
            "success": status in {"completed", "completed_no_write"},
            "status": status,
            "message": message,
            "trace_id": trace_id,
            "span_id": span_id,
            "run_id": run_id,
            "work_item_id": WORK_ITEM_ID,
            "receipt_id": receipt_id,
            "receipt_persisted": persisted,
            "source_type": source_type,
            "file_count": len(successful_results) + len(duplicate_results) + len(failed_results),
            "imported_count": len(successful_results),
            "failed_count": len(failed_results),
            "duplicate_count": len(duplicate_results),
            "source_finalize_failed_count": len(finalization_failures),
            "total_rows": rows_imported,
            "date_range": date_range,
            "latest_sales_date": verifier.get("latest_sales_date"),
            "data_lag_days": verifier.get("data_lag_days"),
            "sla_lag_days": verifier.get("sla_lag_days"),
            "freshness_status": verifier.get("freshness_status"),
            "expected_latest_sales_date": verifier.get("expected_latest_sales_date"),
            "grace_period_active": verifier.get("grace_period_active"),
            "report_cutoff_hour": verifier.get("report_cutoff_hour"),
            "report_due_at": verifier.get("report_due_at"),
            "freshness_contract": verifier.get("contract_version"),
            "decision_ready": verifier.get("decision_ready"),
            "requires_upstream_acquisition": not bool(verifier.get("decision_ready")),
            "manual_review_required": False,
            "safe_next_action": safe_next_action,
            "next_retry_at": source_reconciliation.get("next_retry_at"),
            "notification_policy": source_reconciliation.get("notification_policy"),
            "source_reconciliation": source_reconciliation,
            "providers": stages["providers"],
            "verifier": verifier,
        }


pchome_sales_acquisition_service = PChomeSalesAcquisitionService()
