from __future__ import annotations

from collections import Counter
from datetime import date
import inspect

from clickhouse_read_module.ch_client import execute_query, is_cache_hit
import clickhouse_read_module.direct_queries as direct_queries
from clickhouse_read_module.direct_queries import (
    _PLATFORM_MAP,
    _build_account_filter,
    _platform_tables,
)
from creative_module.models import (
    AdCreativeData,
    AdLabelConfig,
    AdLabelGroup,
    AdLabelTask,
    AdLabelV2,
    CreativeOverview,
    LabelConfigQueue,
)
from creative_module.services.data_service import (
    _get_queue_scope,
    _get_scoped_ids,
    calculate_effective_date_range,
    precompute_creative_scope,
    precompute_label_check_data,
)
from creative_module.tasks.label_v2_task import _get_ad_ids_for_date_range, _is_metadata_only
from organization_auth.models import Account


ACCOUNT_ID = "4a394592-e225-4b6d-850a-d68db3902bd3"
LABEL_CONFIG_ID = "f39d7c80-abb5-4e8a-b378-77ff69b70869"
START_DATE = date(2026, 4, 20)
END_DATE = date(2026, 4, 26)


def p(title: str):
    print(f"\n=== {title} ===")


def sample(values, n=10):
    values = list(values or [])
    suffix = " ..." if len(values) > n else ""
    return f"{values[:n]}{suffix} (count={len(values)})"


def build_active_ad_ids_query(account, start_date: str, end_date: str):
    child_accounts = account.get_child_accounts()
    platform_groups = _platform_tables(account, child_accounts or None)
    params = {
        "p_account_id": str(account.id),
        "p_start": start_date,
        "p_end": end_date,
    }

    union_legs = []
    for plat_key, children in platform_groups.items():
        mapped = _PLATFORM_MAP.get(plat_key)
        if mapped is None:
            continue
        table = f"{mapped}_daily_metric"
        child_ids = [str(c.id) for c in children]
        prefix = f"{plat_key.lower()}_"
        acct_filter = _build_account_filter(
            str(account.id),
            child_ids if child_accounts else [],
            params,
            table_alias="m",
            prefix=prefix,
        )
        union_legs.append(
            f"    SELECT DISTINCT m.ad_id AS ad_id\n"
            f"    FROM {table} AS m\n"
            f"    WHERE {acct_filter}\n"
            f"      AND m.date BETWEEN {{p_start:Date}} AND {{p_end:Date}}\n"
            f"      AND m.ad_id != ''"
        )

    inner_sql = "\n    UNION ALL\n".join(union_legs)
    sql = f"""
SELECT DISTINCT ad_id FROM (
{inner_sql}
)
"""
    return sql, params


def run_active_ad_query(account, start_date: str, end_date: str, *, skip_cache: bool):
    sql, params = build_active_ad_ids_query(account, start_date, end_date)
    rows, elapsed_ms = execute_query(sql, params, skip_cache=skip_cache)
    return [str(row["ad_id"]) for row in rows if row.get("ad_id")], elapsed_ms


account = Account.objects.get(id=ACCOUNT_ID)
label_config = AdLabelConfig.objects.get(id=LABEL_CONFIG_ID)
eff_start, eff_end = calculate_effective_date_range(START_DATE, END_DATE)
eff_key = f"{eff_start.isoformat()}..{eff_end.isoformat()}"

p("Loaded Code")
helper_source = inspect.getsource(direct_queries.fetch_active_ad_ids_for_date_range)
print("direct_queries file:", direct_queries.__file__)
print("helper has skip_cache=True:", "skip_cache=True" in helper_source)
for line in helper_source.splitlines():
    if "execute_query" in line:
        print("helper execute_query line:", line.strip())

p("Config")
print("account:", account.id, account.ad_account_name, "version=", account.version)
print("label_config:", label_config.id, label_config.title)
print("creative_type:", label_config.creative_type)
print("sync:", label_config.sync_status, label_config.sync_progress, label_config.error_message)
print("task_id:", label_config.task_id)
print("label_sources:", label_config.label_sources)
print("stage_timings:", label_config.stage_timings)
print("effective_range:", eff_key)
print("metadata_only:", _is_metadata_only(label_config))

p("Existing Work Rows")
print("groups:", AdLabelGroup.objects.filter(label_config=label_config).count())
print("label_v2 total:", AdLabelV2.objects.filter(label_config=label_config).count())
print(
    "label_v2 by status:",
    dict(Counter(AdLabelV2.objects.filter(label_config=label_config).values_list("sync_status", flat=True))),
)
print("queue rows:", LabelConfigQueue.objects.filter(label_config=label_config).count())
print("label tasks:", AdLabelTask.objects.filter(label_config=label_config).count())

p("ClickHouse Active Ads")
display_active_ids = direct_queries.fetch_active_ad_ids_for_date_range(
    account,
    START_DATE.isoformat(),
    END_DATE.isoformat(),
)
effective_active_ids = direct_queries.fetch_active_ad_ids_for_date_range(
    account,
    eff_start.isoformat(),
    eff_end.isoformat(),
)
print("display_active_ids:", sample(display_active_ids))
print("effective_active_ids:", sample(effective_active_ids))

p("Cache Bypass Proof For Effective Range")
cached_effective_ids, cached_elapsed = run_active_ad_query(
    account,
    eff_start.isoformat(),
    eff_end.isoformat(),
    skip_cache=False,
)
fresh_effective_ids, fresh_elapsed = run_active_ad_query(
    account,
    eff_start.isoformat(),
    eff_end.isoformat(),
    skip_cache=True,
)
helper_effective_ids = direct_queries.fetch_active_ad_ids_for_date_range(
    account,
    eff_start.isoformat(),
    eff_end.isoformat(),
)
print("cached effective ids:", sample(cached_effective_ids))
print("cached was cache hit:", is_cache_hit(cached_elapsed), "elapsed_ms=", cached_elapsed)
print("fresh effective ids:", sample(fresh_effective_ids))
print("fresh was cache hit:", is_cache_hit(fresh_elapsed), "elapsed_ms=", fresh_elapsed)
print("helper effective ids:", sample(helper_effective_ids))
print("helper matches fresh:", set(helper_effective_ids) == set(fresh_effective_ids))
print("helper differs from cached:", set(helper_effective_ids) != set(cached_effective_ids))

p("Current-Code Scoping")
display_scoped_no_cache = _get_ad_ids_for_date_range(
    label_config,
    display_active_ids,
    include_ads_without_creative=_is_metadata_only(label_config),
)
effective_scoped_no_cache = _get_ad_ids_for_date_range(
    label_config,
    effective_active_ids,
    include_ads_without_creative=_is_metadata_only(label_config),
)
print("_get_ad_ids_for_date_range display:", sample(display_scoped_no_cache))
print("_get_ad_ids_for_date_range effective:", sample(effective_scoped_no_cache))

creative_cache = precompute_creative_scope(display_active_ids)
display_scoped_fast = _get_scoped_ids(label_config, display_active_ids, _creative_cache=creative_cache)
queue_scoped_fast, queue_eff_start, queue_eff_end = _get_queue_scope(
    label_config,
    display_active_ids,
    START_DATE,
    END_DATE,
    account,
    _creative_cache=creative_cache,
)
print("_get_scoped_ids display fast:", sample(display_scoped_fast))
print("_get_queue_scope fast:", queue_eff_start, queue_eff_end, sample(queue_scoped_fast))

p("AdCreativeData Type Cross-Tab For Effective Active Ads")
type_rows = (
    AdCreativeData.objects
    .filter(account=account, ad_id__in=effective_active_ids)
    .values_list("creative_type", "creative__type")
)
print(dict(Counter(type_rows)))
print("adcreative rows for effective active ids:", len(type_rows))
print("active ids missing from AdCreativeData:", len(set(effective_active_ids) - {row[0] for row in AdCreativeData.objects.filter(account=account, ad_id__in=effective_active_ids).values_list("ad_id")}))

p("Demand Missing-Ad Decision Emulation")
already_processed = set(
    AdLabelV2.objects
    .filter(label_config=label_config, ad_id__in=effective_scoped_no_cache)
    .values_list("ad_id", flat=True)
    .distinct()
)
never_processed = [aid for aid in effective_scoped_no_cache if aid not in already_processed]
failed_ad_ids = list(
    AdLabelV2.objects
    .filter(label_config=label_config, ad_id__in=effective_scoped_no_cache, sync_status="failed")
    .values_list("ad_id", flat=True)
    .distinct()
)
missing = list(set(never_processed + failed_ad_ids))
if missing and not account.is_demo:
    ch_ready = set(
        AdCreativeData.objects
        .filter(ad_id__in=missing, ch_pushed_at__isnull=False)
        .values_list("ad_id", flat=True)
        .distinct()
    )
    pending_ch = [aid for aid in missing if aid not in ch_ready]
    missing_after_ch_ready = [aid for aid in missing if aid in ch_ready]
else:
    ch_ready = set(missing)
    pending_ch = []
    missing_after_ch_ready = missing
print("already_processed:", sample(already_processed))
print("never_processed:", sample(never_processed))
print("failed_ad_ids:", sample(failed_ad_ids))
print("missing_before_ch_ready:", sample(missing))
print("pending_ch:", sample(pending_ch))
print("missing_after_ch_ready:", sample(missing_after_ch_ready))
if not effective_active_ids:
    print("would_return: no_ads")
elif not effective_scoped_no_cache:
    print("would_return: no_matching_ads")
elif not missing_after_ch_ready:
    print("would_return: all_labeled")
else:
    print("would_return: found")

p("Overview State For Display-Scoped Ads")
display_creative_ids = list(
    AdCreativeData.objects
    .filter(account=account, ad_id__in=display_scoped_no_cache, creative_id__isnull=False)
    .values_list("creative_id", flat=True)
    .distinct()
)
print("display creative ids:", sample([str(cid) for cid in display_creative_ids]))
overview_counts = (
    CreativeOverview.objects
    .filter(creative_id__in=display_creative_ids)
    .values_list("overview_prompt__title", "overview_prompt__creative_type", "sync_status", "error_message")
)
summary = Counter(
    (
        title,
        prompt_type,
        status,
        (err or "")[:80],
    )
    for title, prompt_type, status, err in overview_counts
)
for key, count in sorted(summary.items()):
    print(count, key)

p("Relevant Rows")
print(
    "queue:",
    list(
        LabelConfigQueue.objects
        .filter(label_config=label_config)
        .values("id", "status", "effective_start_date", "effective_end_date", "ad_ids", "workflow_id", "error_message")
    ),
)
print(
    "tasks:",
    list(
        AdLabelTask.objects
        .filter(label_config=label_config)
        .values("id", "sync_status", "ad_ids", "error_message", "created_at", "started_at", "completed_at")
    ),
)