import importlib
import json
import os
from datetime import datetime

import pandas as pd
import pytz
from flask import Flask
from sqlalchemy import text


TAIPEI_TZ = pytz.timezone("Asia/Taipei")


def _load_services(monkeypatch, database_url):
    os.environ.setdefault("MOMO_ALLOW_INSECURE_CONFIG_FOR_TESTS", "true")
    import config

    monkeypatch.setattr(config, "DATABASE_PATH", database_url)
    import services.import_service as import_service

    import_service = importlib.reload(import_service)
    import services.pchome_sales_acquisition_service as acquisition

    acquisition = importlib.reload(acquisition)
    return import_service, acquisition


def _prepare_tables(import_service):
    import_service.Base.metadata.create_all(import_service.engine)
    with import_service.engine.begin() as conn:
        conn.execute(text("DROP TABLE IF EXISTS daily_sales_snapshot"))
        conn.execute(text("DROP TABLE IF EXISTS realtime_sales_monthly"))
        conn.execute(text("""
            CREATE TABLE daily_sales_snapshot (
                "日期" TEXT,
                "商品ID" TEXT,
                "商品名稱" TEXT,
                "銷售金額" INTEGER,
                snapshot_date TEXT
            )
        """))
        conn.execute(text("""
            CREATE TABLE realtime_sales_monthly (
                "日期" TEXT,
                "商品ID" TEXT,
                "商品名稱" TEXT,
                "銷售金額" INTEGER
            )
        """))


class EmptyDrive:
    last_error_kind = None
    last_error = None

    def list_files_in_folder(self, _folder_path, _file_pattern):
        return []


class ReadyDrive(EmptyDrive):
    def check_auth_readiness(self, refresh_expired=False):
        return {"ready": True, "kind": "ready"}


class FailedDrive(EmptyDrive):
    last_error_kind = "authentication_failed"
    last_error = "credentials missing"


def _write_sales_file(path):
    today = datetime.now(TAIPEI_TZ).strftime("%Y-%m-%d")
    pd.DataFrame([{
        "日期": today,
        "商品ID": "P001",
        "商品名稱": "測試商品",
        "銷售金額": 1680,
    }]).to_excel(path, index=False, sheet_name="即時業績明細")


def _disable_remote_sources(monkeypatch):
    monkeypatch.setenv("PCHOME_SALES_HTTP_ENABLED", "false")
    monkeypatch.setenv("PCHOME_SALES_IMAP_ENABLED", "false")


def test_local_drop_closes_import_and_archives_only_after_verification(monkeypatch, tmp_path):
    import_service, acquisition = _load_services(monkeypatch, f"sqlite:///{tmp_path / 'momo.db'}")
    _prepare_tables(import_service)
    _disable_remote_sources(monkeypatch)
    drop_dir = tmp_path / "drop"
    drop_dir.mkdir()
    source = drop_dir / "即時業績_當日.xlsx"
    _write_sales_file(source)
    monkeypatch.setenv("PCHOME_SALES_LOCAL_DROP_DIR", str(drop_dir))
    monkeypatch.setattr(import_service, "drive_service", EmptyDrive())

    result = acquisition.PChomeSalesAcquisitionService().run(trigger="test")

    assert result["success"] is True
    assert result["status"] == "completed"
    assert result["source_type"] == "controlled_local_drop"
    assert result["total_rows"] == 1
    assert result["decision_ready"] is True
    assert result["freshness_contract"] == "pchome_sales_freshness_sla_v1"
    assert result["sla_lag_days"] <= 0
    assert result["report_cutoff_hour"] == 20
    assert result["receipt_persisted"] is True
    assert len(result["trace_id"]) == 32
    assert len(result["span_id"]) == 16
    assert result["trace_id"] == result["trace_id"].lower()
    assert result["span_id"] == result["span_id"].lower()
    assert not source.exists()
    assert (drop_dir / "archive" / source.name).exists()

    with import_service.engine.connect() as conn:
        assert conn.execute(text("SELECT COUNT(*) FROM daily_sales_snapshot")).scalar() == 1
        assert conn.execute(text("SELECT COUNT(*) FROM realtime_sales_monthly")).scalar() == 1
        assert conn.execute(text("SELECT COUNT(*) FROM pchome_sales_acquisition_receipts")).scalar() == 1


def test_same_content_is_idempotent_and_does_not_duplicate_rows(monkeypatch, tmp_path):
    import_service, acquisition = _load_services(monkeypatch, f"sqlite:///{tmp_path / 'momo.db'}")
    _prepare_tables(import_service)
    _disable_remote_sources(monkeypatch)
    drop_dir = tmp_path / "drop"
    drop_dir.mkdir()
    source = drop_dir / "即時業績_當日.xlsx"
    _write_sales_file(source)
    original_bytes = source.read_bytes()
    monkeypatch.setenv("PCHOME_SALES_LOCAL_DROP_DIR", str(drop_dir))
    monkeypatch.setattr(import_service, "drive_service", EmptyDrive())
    service = acquisition.PChomeSalesAcquisitionService()

    first = service.run(trigger="test")
    source.write_bytes(original_bytes)
    second = service.run(trigger="test")

    assert first["status"] == "completed"
    assert second["status"] == "completed_no_write"
    assert second["duplicate_count"] == 1
    assert second["imported_count"] == 0
    with import_service.engine.connect() as conn:
        assert conn.execute(text("SELECT COUNT(*) FROM daily_sales_snapshot")).scalar() == 1
        assert conn.execute(text("SELECT COUNT(*) FROM realtime_sales_monthly")).scalar() == 1


def test_verified_import_is_partial_when_source_archive_ack_fails(monkeypatch, tmp_path):
    import_service, acquisition = _load_services(monkeypatch, f"sqlite:///{tmp_path / 'momo.db'}")
    import services.pchome_sales_acquisition_providers as providers

    _prepare_tables(import_service)
    _disable_remote_sources(monkeypatch)
    drop_dir = tmp_path / "drop"
    drop_dir.mkdir()
    source = drop_dir / "即時業績_當日.xlsx"
    _write_sales_file(source)
    monkeypatch.setenv("PCHOME_SALES_LOCAL_DROP_DIR", str(drop_dir))
    monkeypatch.setattr(import_service, "drive_service", EmptyDrive())
    monkeypatch.setattr(
        providers,
        "_archive_file",
        lambda *_args, **_kwargs: (_ for _ in ()).throw(PermissionError("archive denied")),
    )

    result = acquisition.PChomeSalesAcquisitionService().run(trigger="test")

    assert result["success"] is False
    assert result["status"] == "partial"
    assert result["source_finalize_failed_count"] == 1
    assert result["safe_next_action"] == "retry_source_finalization"
    assert source.exists()
    with import_service.engine.connect() as conn:
        assert conn.execute(text("SELECT COUNT(*) FROM daily_sales_snapshot")).scalar() == 1
        assert conn.execute(text("SELECT COUNT(*) FROM realtime_sales_monthly")).scalar() == 1


def test_no_candidate_with_missing_freshness_is_blocked_and_receipted(monkeypatch, tmp_path):
    import_service, acquisition = _load_services(monkeypatch, f"sqlite:///{tmp_path / 'momo.db'}")
    _prepare_tables(import_service)
    _disable_remote_sources(monkeypatch)
    monkeypatch.delenv("PCHOME_SALES_LOCAL_DROP_DIR", raising=False)
    monkeypatch.setattr(import_service, "drive_service", EmptyDrive())
    monkeypatch.setattr(acquisition, "drive_service", ReadyDrive())

    result = acquisition.PChomeSalesAcquisitionService().run(trigger="test")

    assert result["success"] is False
    assert result["status"] == "blocked_with_safe_next_action"
    assert result["safe_next_action"] == "scheduled_retry_and_enabled_fallback_probe"
    assert result["next_retry_at"]
    assert result["source_reconciliation"]["contract"] == "pchome_sales_source_reconciliation_v1"
    assert result["source_reconciliation"]["manual_review_required"] is False
    assert result["providers"][1]["status"] == "disabled"
    assert result["providers"][2]["status"] == "disabled"
    assert result["providers"][3]["status"] == "disabled"
    assert result["receipt_persisted"] is True
    with import_service.engine.connect() as conn:
        receipt = conn.execute(text(
            "SELECT status, decision FROM pchome_sales_acquisition_receipts"
        )).one()
    assert receipt.status == "blocked_with_safe_next_action"
    assert receipt.decision == "no_authorized_candidate"


def test_provider_failure_keeps_product_message_compact(monkeypatch, tmp_path):
    import_service, acquisition = _load_services(monkeypatch, f"sqlite:///{tmp_path / 'momo.db'}")
    _prepare_tables(import_service)
    _disable_remote_sources(monkeypatch)
    monkeypatch.delenv("PCHOME_SALES_LOCAL_DROP_DIR", raising=False)
    monkeypatch.setattr(import_service, "drive_service", FailedDrive())

    result = acquisition.PChomeSalesAcquisitionService().run(trigger="test")

    assert result["success"] is False
    assert result["status"] == "degraded_no_write"
    assert result["message"] == "授權來源目前無法取得報表，系統將自動重試。"
    assert result["providers"][0]["error_kind"] == "authentication_failed"


def test_readiness_never_exposes_provider_credentials_or_urls(monkeypatch, tmp_path):
    import_service, acquisition = _load_services(monkeypatch, f"sqlite:///{tmp_path / 'momo.db'}")
    _prepare_tables(import_service)
    monkeypatch.setattr(acquisition, "drive_service", ReadyDrive())
    monkeypatch.setenv("PCHOME_SALES_HTTP_ENABLED", "true")
    monkeypatch.setenv("PCHOME_SALES_HTTP_URL", "https://reports.example.test/private/report.xlsx")
    monkeypatch.setenv("PCHOME_SALES_HTTP_ALLOWED_HOSTS", "reports.example.test")
    monkeypatch.setenv("PCHOME_SALES_HTTP_BEARER_TOKEN", "top-secret-http-token")
    monkeypatch.setenv("PCHOME_SALES_IMAP_ENABLED", "true")
    monkeypatch.setenv("PCHOME_SALES_IMAP_HOST", "mail.example.test")
    monkeypatch.setenv("PCHOME_SALES_IMAP_USER", "private-user@example.test")
    monkeypatch.setenv("PCHOME_SALES_IMAP_PASSWORD", "top-secret-imap-password")

    payload = acquisition.PChomeSalesAcquisitionService().readiness()
    public_text = json.dumps(payload, ensure_ascii=False)

    assert payload["manual_review_required"] is False
    assert payload["asset_coverage"]["ready"] == 3
    assert "top-secret" not in public_text
    assert "reports.example.test" not in public_text
    assert "mail.example.test" not in public_text
    assert "private-user" not in public_text


def test_https_provider_rejects_non_https_and_non_allowlisted_hosts(monkeypatch):
    import services.pchome_sales_acquisition_service as acquisition
    import services.pchome_sales_acquisition_providers as provider_module

    service = acquisition.PChomeSalesAcquisitionService()
    monkeypatch.setenv("PCHOME_SALES_HTTP_ALLOWED_HOSTS", "reports.example.test")

    try:
        service.providers.validate_http_url("http://reports.example.test/report.xlsx")
        assert False, "HTTP URL should be rejected"
    except acquisition.ProviderError as exc:
        assert exc.kind == "http_url_rejected"

    try:
        service.providers.validate_http_url("https://untrusted.example.test/report.xlsx")
        assert False, "Non-allowlisted host should be rejected"
    except acquisition.ProviderError as exc:
        assert exc.kind == "http_host_not_allowed"

    monkeypatch.setenv("PCHOME_SALES_HTTP_ALLOWED_HOSTS", "reports.example.test")
    try:
        service.providers.validate_http_url("https://reports.example.test:8443/report.xlsx")
        assert False, "Non-allowlisted port should be rejected"
    except acquisition.ProviderError as exc:
        assert exc.kind == "http_port_rejected"

    monkeypatch.setattr(
        provider_module.socket,
        "getaddrinfo",
        lambda *_args, **_kwargs: [(2, 1, 6, "", ("127.0.0.1", 443))],
    )
    try:
        service.providers.validate_http_url("https://reports.example.test/report.xlsx")
        assert False, "Private resolved address should be rejected"
    except acquisition.ProviderError as exc:
        assert exc.kind == "http_private_address_rejected"


def test_auto_import_api_uses_governed_acquisition_service(monkeypatch):
    import routes.auto_import_routes as routes

    class FakeAcquisitionService:
        def readiness(self):
            return {"success": True, "runtime_closure": "blocked_by_upstream_freshness"}

        def run(self, *, trigger):
            return {
                "success": False,
                "status": "blocked_with_safe_next_action",
                "trigger": trigger,
                "receipt_persisted": True,
            }

    monkeypatch.setattr(routes, "pchome_sales_acquisition_service", FakeAcquisitionService())
    app = Flask(__name__)
    app.register_blueprint(routes.auto_import_bp)
    client = app.test_client()

    readiness = client.get("/api/auto_import/readiness")
    run = client.post("/api/auto_import/run")
    legacy = client.post("/api/manual_import")

    assert readiness.status_code == 200
    assert readiness.get_json()["runtime_closure"] == "blocked_by_upstream_freshness"
    assert run.get_json()["status"] == "blocked_with_safe_next_action"
    assert run.get_json()["trigger"] == "operator_command"
    assert legacy.get_json()["trigger"] == "operator_command"


def test_scheduler_continues_when_legacy_hitl_pause_is_set(monkeypatch):
    import scheduler
    import services.agent_actions as agent_actions
    import services.notification_manager as notification_manager
    import services.pchome_sales_acquisition_service as acquisition

    calls = []
    saved = {}
    monkeypatch.setattr(agent_actions, "is_task_paused", lambda _name: True)
    monkeypatch.setattr(
        acquisition.pchome_sales_acquisition_service,
        "run",
        lambda *, trigger: calls.append(trigger) or {
            "success": False,
            "status": "blocked_with_safe_next_action",
            "message": "no candidate",
            "trace_id": "trace-test",
            "receipt_id": "receipt-test",
            "receipt_persisted": True,
            "latest_sales_date": "2026-07-10",
            "data_lag_days": 1,
            "sla_lag_days": 0,
            "freshness_status": "fresh",
            "expected_latest_sales_date": "2026-07-10",
            "grace_period_active": False,
            "report_cutoff_hour": 20,
            "report_due_at": "2026-07-14T20:00:00+08:00",
            "freshness_contract": "pchome_sales_freshness_sla_v1",
            "decision_ready": True,
            "safe_next_action": "scheduler_retry_authorized_sources",
            "next_retry_at": "2026-07-14T19:00:00+08:00",
            "notification_policy": {
                "send": False,
                "reason": "duplicate_within_cooldown",
                "fingerprint": "same-state",
            },
        },
    )
    monkeypatch.setattr(
        notification_manager,
        "NotificationManager",
        lambda: (_ for _ in ()).throw(AssertionError("suppressed notification must not send")),
    )
    monkeypatch.setattr(scheduler, "_save_stats", lambda name, stats: saved.update({"name": name, "stats": stats}))

    scheduler.run_auto_import_task()

    assert calls == ["scheduler"]
    assert saved["name"] == "auto_import_task"
    assert saved["stats"]["status"] == "Degraded"
    assert saved["stats"]["receipt_persisted"] is True
    assert saved["stats"]["next_retry_at"] == "2026-07-14T19:00:00+08:00"
    assert saved["stats"]["notification_policy"]["send"] is False
    assert saved["stats"]["freshness_contract"] == "pchome_sales_freshness_sla_v1"
    assert saved["stats"]["sla_lag_days"] == 0
    assert saved["stats"]["report_cutoff_hour"] == 20
