"""Tests for admin dashboard ticket trends (UTC+8 calendar buckets)."""

from __future__ import annotations

import json
import uuid
from datetime import date, datetime, timezone

import pytest

from investibook.db import queries as dbq


@pytest.fixture
def db(monkeypatch, tmp_path):
    p = tmp_path / "investibook.db"
    monkeypatch.setattr(dbq, "DB_PATH", p)
    dbq._db_init()
    return p


def _iso_midday_utc(d: date) -> str:
    return datetime(d.year, d.month, d.day, 12, 0, 0, tzinfo=timezone.utc).isoformat()


def test_ticket_trends_and_drill_single_day(db):
    day = date(2026, 4, 12)
    bucket_e = dbq._china_date_midnight_epoch(day)
    noon = bucket_e + 12 * 3600
    master = str(uuid.uuid4())
    slave = str(uuid.uuid4())
    dos_ws = str(uuid.uuid4())
    ws_id = f"ws-{uuid.uuid4().hex[:10]}"

    payload = {
        "source_dossier_id": slave,
        "canonical_target_dossier_id": master,
        "issues_moved": 5,
        "issues_deduped": 0,
    }

    ticket_keys = ("TCOC-T1", "TCOC-T2", "TCOC-D", "TCOC-W", "TCOC-M", "TCOC-S")

    with dbq._db_lock:
        conn = dbq._db_connect()
        for did, title in ((master, "M"), (slave, "S"), (dos_ws, "WS day")):
            conn.execute(
                """
                INSERT INTO incident_dossiers (
                  id, title, status, merged_into_dossier_id, created_at, updated_at
                ) VALUES (?, ?, 'active', NULL, ?, ?)
                """,
                (did, title, noon, noon),
            )
        for key in ticket_keys:
            conn.execute(
                """
                INSERT INTO jira_tickets (
                  issue_key, project_key, summary,
                  jira_created_at, jira_updated_at,
                  first_seen_at, last_jira_sync_at, raw_payload_json
                ) VALUES (?, 'TCOC', 'x', ?, ?, ?, ?, '{}')
                """,
                (key, noon, noon, noon, noon),
            )
        conn.execute(
            """
            INSERT INTO incident_dossier_issues (
              dossier_id, issue_key, link_role, link_confidence, created_at
            ) VALUES (?, 'TCOC-D', 'primary', 'auto', ?)
            """,
            (dos_ws, noon),
        )
        conn.execute(
            """
            INSERT INTO incident_dossier_issues (
              dossier_id, issue_key, link_role, link_confidence, created_at
            ) VALUES (?, 'TCOC-W', 'primary', 'auto', ?)
            """,
            (dos_ws, noon),
        )
        conn.execute(
            """
            INSERT INTO incident_dossier_issues (
              dossier_id, issue_key, link_role, link_confidence, created_at
            ) VALUES (?, 'TCOC-M', 'primary', 'auto', ?)
            """,
            (master, noon),
        )
        conn.execute(
            """
            INSERT INTO incident_dossier_issues (
              dossier_id, issue_key, link_role, link_confidence, created_at
            ) VALUES (?, 'TCOC-S', 'primary', 'auto', ?)
            """,
            (slave, noon),
        )
        conn.execute(
            """
            INSERT INTO workspaces (
              id, user_id, name, description, is_example, is_shared, created_at, updated_at
            ) VALUES (?, 'u', 'W', '', 0, 0, ?, ?)
            """,
            (ws_id, dbq._utc_now(), dbq._utc_now()),
        )
        conn.execute(
            """
            INSERT INTO workspace_jira_links (
              id, workspace_id, issue_key, source_id, user_id, linked_at
            ) VALUES (?, ?, 'TCOC-W', '', 'u', ?)
            """,
            (str(uuid.uuid4()), ws_id, _iso_midday_utc(day)),
        )
        conn.execute(
            """
            INSERT INTO dossier_audit_log (
              id, created_at, actor_type, actor_user_id, action, payload_json
            ) VALUES (?, ?, 'user', 'u', 'merge_incident_dossiers', ?)
            """,
            (str(uuid.uuid4()), noon, json.dumps(payload)),
        )
        extra_marked = str(uuid.uuid4())
        conn.execute(
            """
            INSERT INTO incident_dossiers (
              id, title, status, merged_into_dossier_id, created_at, updated_at,
              manual_not_incident, manual_not_incident_at, manual_not_incident_by
            ) VALUES (?, 'Marked only', 'active', NULL, ?, ?, 1, ?, 'u')
            """,
            (extra_marked, noon, noon, noon),
        )
        conn.execute(
            """
            INSERT INTO jira_tickets (
              issue_key, project_key, summary,
              jira_created_at, jira_updated_at,
              first_seen_at, last_jira_sync_at, raw_payload_json
            ) VALUES ('TCOC-MARK', 'TCOC', 'x', ?, ?, ?, ?, '{}')
            """,
            (noon, noon, noon, noon),
        )
        conn.execute(
            """
            INSERT INTO incident_dossier_issues (
              dossier_id, issue_key, link_role, link_confidence, created_at
            ) VALUES (?, 'TCOC-MARK', 'primary', 'auto', ?)
            """,
            (extra_marked, noon),
        )
        conn.commit()
        conn.close()

    out = dbq._db_admin_dashboard_ticket_trends(
        start_day=day, end_day=day, project="TCOC", granularity="day"
    )
    b0 = out["buckets"][0]
    assert b0["day"] == day.isoformat()
    assert b0["total_tickets"] == 7
    assert b0["dossiers_created"] == 3
    assert b0["merged_issues_moved"] == 5
    mtot = out["manual_activity_range_totals"]
    assert mtot["merge_events"] == 1
    assert mtot["incidents_created"] == 0
    assert mtot["ticket_links_manual"] == 0
    assert mtot["workspace_links_manual"] == 0
    assert mtot["not_incident_marks"] == 1
    assert b0["dossiers_workspace_linked_new"] == 1
    assert b0["incidents_analyzed"] == 1
    assert b0["incidents_not_analyzed"] == 2
    assert b0["incident_analysis_rate_pct"] == round(100.0 * 1 / 3, 1)
    assert b0["disposition_total"] == 4
    assert b0["disposition_not_incident"] == 1
    assert b0["disposition_analyzed"] == 1
    assert b0["disposition_not_analyzed"] == 2
    assert b0["disposition_merged"] == 0
    assert b0["disposition_unsupported"] == 0
    assert b0["workspace_penetration_pct"] == b0["incident_analysis_rate_pct"]

    dd_t = dbq._db_admin_dashboard_ticket_trends_drill_down(day=day, metric="total_tickets", project="TCOC")
    assert len(dd_t["items"]) == 7

    dd_d = dbq._db_admin_dashboard_ticket_trends_drill_down(day=day, metric="dossiers_created", project="TCOC")
    assert len(dd_d["items"]) == 3
    for item in dd_d["items"]:
        assert str(item.get("primary_issue_key") or "").startswith("TCOC-")

    dd_m = dbq._db_admin_dashboard_ticket_trends_drill_down(day=day, metric="merged_issues_moved", project="TCOC")
    assert len(dd_m["items"]) == 1
    assert dd_m["items"][0]["issues_moved"] == 5

    dd_w = dbq._db_admin_dashboard_ticket_trends_drill_down(
        day=day, metric="dossiers_workspace_linked_new", project="TCOC"
    )
    assert len(dd_w["items"]) == 1
    assert dd_w["items"][0]["dossier_id"] == dos_ws
    assert dd_w["items"][0]["primary_issue_key"] == "TCOC-D"

    dd_all = dbq._db_admin_dashboard_ticket_trends_drill_down(
        day=day, metric="disposition_total", project="TCOC"
    )
    assert len(dd_all["items"]) == 4

    dd_ni = dbq._db_admin_dashboard_ticket_trends_drill_down(
        day=day, metric="disposition_not_incident", project="TCOC"
    )
    assert len(dd_ni["items"]) == 1
    assert dd_ni["items"][0]["dossier_id"] == extra_marked


def test_dossiers_linked_workspace_bucketed_by_creation_day_not_link_day(db):
    """Workspace link on day N+1 still counts toward dossiers created on day N."""
    day_create = date(2026, 5, 13)
    day_link = date(2026, 5, 14)
    b_create = dbq._china_date_midnight_epoch(day_create)
    b_link = dbq._china_date_midnight_epoch(day_link)
    noon_create = b_create + 12 * 3600
    noon_link = b_link + 12 * 3600
    dossier_id = "93bb8422-acab-42ff-8d3a-afb0647696a7"
    ws_id = f"ws-{uuid.uuid4().hex[:10]}"

    with dbq._db_lock:
        conn = dbq._db_connect()
        conn.execute(
            """
            INSERT INTO incident_dossiers (
              id, title, status, merged_into_dossier_id, created_at, updated_at
            ) VALUES (?, 'Late link', 'active', NULL, ?, ?)
            """,
            (dossier_id, noon_create, noon_create),
        )
        conn.execute(
            """
            INSERT INTO jira_tickets (
              issue_key, project_key, summary,
              jira_created_at, jira_updated_at,
              first_seen_at, last_jira_sync_at, raw_payload_json
            ) VALUES ('TCOC-LATE', 'TCOC', 'x', ?, ?, ?, ?, '{}')
            """,
            (noon_create, noon_create, noon_create, noon_create),
        )
        conn.execute(
            """
            INSERT INTO incident_dossier_issues (
              dossier_id, issue_key, link_role, link_confidence, created_at
            ) VALUES (?, 'TCOC-LATE', 'primary', 'auto', ?)
            """,
            (dossier_id, noon_create),
        )
        conn.execute(
            """
            INSERT INTO workspaces (
              id, user_id, name, description, is_example, is_shared, created_at, updated_at
            ) VALUES (?, 'u', 'W', '', 0, 0, ?, ?)
            """,
            (ws_id, dbq._utc_now(), dbq._utc_now()),
        )
        conn.execute(
            """
            INSERT INTO workspace_jira_links (
              id, workspace_id, issue_key, source_id, user_id, linked_at
            ) VALUES (?, ?, 'TCOC-LATE', '', 'u', ?)
            """,
            (str(uuid.uuid4()), ws_id, _iso_midday_utc(day_link)),
        )
        conn.commit()
        conn.close()

    out_create = dbq._db_admin_dashboard_ticket_trends(
        start_day=day_create, end_day=day_create, project="TCOC", granularity="day"
    )
    b = out_create["buckets"][0]
    assert b["dossiers_created"] == 1
    assert b["dossiers_workspace_linked_new"] == 1
    assert b["incidents_analyzed"] == 1
    assert b["incidents_not_analyzed"] == 0
    assert b["incident_analysis_rate_pct"] == 100.0

    out_link = dbq._db_admin_dashboard_ticket_trends(
        start_day=day_link, end_day=day_link, project="TCOC", granularity="day"
    )
    b2 = out_link["buckets"][0]
    assert b2["dossiers_created"] == 0
    assert b2["dossiers_workspace_linked_new"] == 0

    dd = dbq._db_admin_dashboard_ticket_trends_drill_down(
        day=day_create, metric="dossiers_workspace_linked_new", project="TCOC"
    )
    assert len(dd["items"]) == 1
    assert dd["items"][0]["dossier_id"] == dossier_id


def test_merge_issues_bucketed_by_source_creation_day_not_merge_audit_day(db):
    """issues_moved counts toward source dossier's creation UTC+8 day, not merge audit day."""
    day_slave = date(2026, 5, 13)
    day_merge = date(2026, 5, 14)
    b_slave = dbq._china_date_midnight_epoch(day_slave)
    b_merge = dbq._china_date_midnight_epoch(day_merge)
    noon_slave = b_slave + 12 * 3600
    noon_merge = b_merge + 12 * 3600
    noon_master = b_merge + 3600
    master = str(uuid.uuid4())
    slave = str(uuid.uuid4())
    payload = {
        "source_dossier_id": slave,
        "canonical_target_dossier_id": master,
        "issues_moved": 8,
        "issues_deduped": 0,
    }

    with dbq._db_lock:
        conn = dbq._db_connect()
        conn.execute(
            """
            INSERT INTO incident_dossiers (
              id, title, status, merged_into_dossier_id, created_at, updated_at
            ) VALUES (?, 'Slave old', 'active', NULL, ?, ?)
            """,
            (slave, noon_slave, noon_slave),
        )
        conn.execute(
            """
            INSERT INTO incident_dossiers (
              id, title, status, merged_into_dossier_id, created_at, updated_at
            ) VALUES (?, 'Master new', 'active', NULL, ?, ?)
            """,
            (master, noon_master, noon_master),
        )
        for key in ("TCOC-MRG-M", "TCOC-MRG-S"):
            conn.execute(
                """
                INSERT INTO jira_tickets (
                  issue_key, project_key, summary,
                  jira_created_at, jira_updated_at,
                  first_seen_at, last_jira_sync_at, raw_payload_json
                ) VALUES (?, 'TCOC', 'x', ?, ?, ?, ?, '{}')
                """,
                (key, noon_slave, noon_slave, noon_slave, noon_slave),
            )
        conn.execute(
            """
            INSERT INTO incident_dossier_issues (
              dossier_id, issue_key, link_role, link_confidence, created_at
            ) VALUES (?, 'TCOC-MRG-M', 'primary', 'auto', ?)
            """,
            (master, noon_slave),
        )
        conn.execute(
            """
            INSERT INTO incident_dossier_issues (
              dossier_id, issue_key, link_role, link_confidence, created_at
            ) VALUES (?, 'TCOC-MRG-S', 'primary', 'auto', ?)
            """,
            (slave, noon_slave),
        )
        conn.execute(
            """
            INSERT INTO dossier_audit_log (
              id, created_at, actor_type, actor_user_id, action, payload_json
            ) VALUES (?, ?, 'user', 'u', 'merge_incident_dossiers', ?)
            """,
            (str(uuid.uuid4()), noon_merge, json.dumps(payload)),
        )
        conn.commit()
        conn.close()

    out_slave = dbq._db_admin_dashboard_ticket_trends(
        start_day=day_slave, end_day=day_slave, project="TCOC", granularity="day"
    )
    assert out_slave["buckets"][0]["merged_issues_moved"] == 8

    out_merge_day = dbq._db_admin_dashboard_ticket_trends(
        start_day=day_merge, end_day=day_merge, project="TCOC", granularity="day"
    )
    assert out_merge_day["buckets"][0]["merged_issues_moved"] == 0

    dd = dbq._db_admin_dashboard_ticket_trends_drill_down(
        day=day_slave, metric="merged_issues_moved", project="TCOC"
    )
    assert len(dd["items"]) == 1
    assert dd["items"][0]["issues_moved"] == 8
    assert dd["items"][0]["source_dossier_id"] == slave


def test_manual_dossier_workspace_counts_for_linked_kpi(db):
    """Manual incident_dossier_workspaces rows count toward linked-workspace KPI (no wjl)."""
    day = date(2026, 6, 1)
    bucket_e = dbq._china_date_midnight_epoch(day)
    noon = bucket_e + 12 * 3600
    dossier_id = str(uuid.uuid4())
    ws_id = f"ws-{uuid.uuid4().hex[:10]}"

    with dbq._db_lock:
        conn = dbq._db_connect()
        conn.execute(
            """
            INSERT INTO incident_dossiers (
              id, title, status, merged_into_dossier_id, created_at, updated_at
            ) VALUES (?, 'Manual WS only', 'active', NULL, ?, ?)
            """,
            (dossier_id, noon, noon),
        )
        conn.execute(
            """
            INSERT INTO jira_tickets (
              issue_key, project_key, summary,
              jira_created_at, jira_updated_at,
              first_seen_at, last_jira_sync_at, raw_payload_json
            ) VALUES ('TCOC-MAN', 'TCOC', 'x', ?, ?, ?, ?, '{}')
            """,
            (noon, noon, noon, noon),
        )
        conn.execute(
            """
            INSERT INTO incident_dossier_issues (
              dossier_id, issue_key, link_role, link_confidence, created_at
            ) VALUES (?, 'TCOC-MAN', 'primary', 'auto', ?)
            """,
            (dossier_id, noon),
        )
        conn.execute(
            """
            INSERT INTO workspaces (
              id, user_id, name, description, is_example, is_shared, created_at, updated_at
            ) VALUES (?, 'u', 'W', '', 0, 0, ?, ?)
            """,
            (ws_id, dbq._utc_now(), dbq._utc_now()),
        )
        conn.execute(
            """
            INSERT INTO incident_dossier_workspaces (
              dossier_id, workspace_id, created_at, created_by
            ) VALUES (?, ?, ?, 'admin')
            """,
            (dossier_id, ws_id, noon),
        )
        conn.commit()
        conn.close()

    out = dbq._db_admin_dashboard_ticket_trends(
        start_day=day, end_day=day, project="TCOC", granularity="day"
    )
    b = out["buckets"][0]
    assert b["dossiers_created"] == 1
    assert b["dossiers_workspace_linked_new"] == 1
    assert b["incidents_analyzed"] == 1
    assert b["incidents_not_analyzed"] == 0
    assert b["incident_analysis_rate_pct"] == 100.0

    dd = dbq._db_admin_dashboard_ticket_trends_drill_down(
        day=day, metric="dossiers_workspace_linked_new", project="TCOC"
    )
    assert len(dd["items"]) == 1
    assert dd["items"][0]["dossier_id"] == dossier_id


def test_manual_not_incident_dossier_excluded_from_incident_kpis(db):
    """Dossiers with manual_not_incident do not count toward dossiers_created KPI or drill-down."""
    day = date(2026, 7, 2)
    bucket_e = dbq._china_date_midnight_epoch(day)
    noon = bucket_e + 12 * 3600
    dossier_ok = str(uuid.uuid4())
    dossier_ni = str(uuid.uuid4())

    with dbq._db_lock:
        conn = dbq._db_connect()
        for did, title in ((dossier_ok, "Real incident"), (dossier_ni, "Not incident")):
            conn.execute(
                """
                INSERT INTO incident_dossiers (
                  id, title, status, merged_into_dossier_id, created_at, updated_at
                ) VALUES (?, ?, 'active', NULL, ?, ?)
                """,
                (did, title, noon, noon),
            )
        conn.execute(
            """
            UPDATE incident_dossiers SET manual_not_incident = 1,
                   manual_not_incident_at = ?, manual_not_incident_by = 'u1'
             WHERE id = ?
            """,
            (noon, dossier_ni),
        )
        for key in ("TCOC-KPI-A", "TCOC-KPI-B"):
            conn.execute(
                """
                INSERT INTO jira_tickets (
                  issue_key, project_key, summary,
                  jira_created_at, jira_updated_at,
                  first_seen_at, last_jira_sync_at, raw_payload_json
                ) VALUES (?, 'TCOC', 'x', ?, ?, ?, ?, '{}')
                """,
                (key, noon, noon, noon, noon),
            )
        conn.execute(
            """
            INSERT INTO incident_dossier_issues (
              dossier_id, issue_key, link_role, link_confidence, created_at
            ) VALUES (?, 'TCOC-KPI-A', 'primary', 'auto', ?)
            """,
            (dossier_ok, noon),
        )
        conn.execute(
            """
            INSERT INTO incident_dossier_issues (
              dossier_id, issue_key, link_role, link_confidence, created_at
            ) VALUES (?, 'TCOC-KPI-B', 'primary', 'auto', ?)
            """,
            (dossier_ni, noon),
        )
        conn.commit()
        conn.close()

    out = dbq._db_admin_dashboard_ticket_trends(
        start_day=day, end_day=day, project="TCOC", granularity="day"
    )
    b = out["buckets"][0]
    assert b["dossiers_created"] == 1

    dd = dbq._db_admin_dashboard_ticket_trends_drill_down(
        day=day, metric="dossiers_created", project="TCOC"
    )
    assert len(dd["items"]) == 1
    assert dd["items"][0]["dossier_id"] == dossier_ok


def test_ticket_trends_project_filter_excludes_other_project(db):
    day = date(2026, 5, 1)
    bucket_e = dbq._china_date_midnight_epoch(day)
    noon = bucket_e + 3600
    with dbq._db_lock:
        conn = dbq._db_connect()
        conn.execute(
            """
            INSERT INTO jira_tickets (
              issue_key, project_key, summary,
              jira_created_at, jira_updated_at,
              first_seen_at, last_jira_sync_at, raw_payload_json
            ) VALUES ('O11Y-1', 'O11Y', 'x', ?, ?, ?, ?, '{}')
            """,
            (noon, noon, noon, noon),
        )
        conn.commit()
        conn.close()

    out = dbq._db_admin_dashboard_ticket_trends(
        start_day=day, end_day=day, project="TCOC", granularity="day"
    )
    assert out["buckets"][0]["total_tickets"] == 0

    out_all = dbq._db_admin_dashboard_ticket_trends(
        start_day=day, end_day=day, project="ALL", granularity="day"
    )
    assert out_all["buckets"][0]["total_tickets"] == 1