"""Row-level Neo4j <-> Snowflake comparison helpers."""

import datetime as dt
import decimal
import logging

import config

log = logging.getLogger(config.LOGGER_NAME)


def run_row_level_check(check, driver, snowflake_executor, window_start, window_end):
    """Run a single row-level sync check between Neo4j and Snowflake.

    Builds queries, fetches rows from both stores, and compares them field
    by field.

    Args:
        check (dict): Check configuration dict from checks.CHECKS.
        driver (neo4j.Driver): Active Neo4j driver.
        snowflake_executor (SnowflakeSQLExecutor): Active Snowflake executor.
        window_start (datetime): Start of the comparison time window.
        window_end (datetime): End of the comparison time window.

    Returns:
        tuple[dict, dict]: summary dict (with 'shouldBeTrue' and row/mismatch
            counts) and comparison dict (with 'missing_in_snowflake',
            'missing_in_neo4j', 'field_mismatches').
    """
    key_specs, compare_specs = build_field_specs(check)
    all_specs = key_specs + compare_specs
    key_aliases = [spec['alias'] for spec in key_specs]
    compare_aliases = [spec['alias'] for spec in compare_specs]

    use_window = check_window_config(check)
    params = {'window_start': window_start, 'window_end': window_end} if use_window else {}

    check_name = check.get('name', 'unknown')
    log.info(f'Running check {check_name} (window={use_window})')

    neo4j_query = build_neo4j_query(check, all_specs, use_window)
    snowflake_query = build_snowflake_query(check, all_specs, use_window)

    neo4j_rows = fetch_neo4j_rows(
        driver,
        neo4j_query,
        params,
        key_aliases,
        compare_aliases,
    )
    log.info(f'Check {check_name}: fetched {len(neo4j_rows)} rows from Neo4j')

    snowflake_rows = fetch_snowflake_rows(
        snowflake_executor,
        snowflake_query,
        params,
        key_aliases,
        compare_aliases,
    )
    log.info(f'Check {check_name}: fetched {len(snowflake_rows)} rows from Snowflake')

    sample_number = check.get('sample_number', config.DEFAULT_SAMPLE_NUMBER)
    comparison = compare_row_maps(neo4j_rows, snowflake_rows, compare_specs, sample_number)
    mismatch_count = (
        len(comparison['missing_in_snowflake'])
        + len(comparison['missing_in_neo4j'])
        + len(comparison['field_mismatches'])
    )

    summary = {
        'shouldBeTrue': mismatch_count == 0,
        'missing_in_snowflake_count': len(comparison['missing_in_snowflake']),
        'missing_in_neo4j_count': len(comparison['missing_in_neo4j']),
        'field_mismatch_count': len(comparison['field_mismatches']),
        'neo4j_row_count': len(neo4j_rows),
        'snowflake_row_count': len(snowflake_rows),
    }

    if mismatch_count == 0:
        log.info(f'Check {check_name}: OK — no mismatches')
    else:
        log.warning(
            f'Check {check_name}: {mismatch_count} mismatches — '
            f'missing_in_snowflake={summary["missing_in_snowflake_count"]}, '
            f'missing_in_neo4j={summary["missing_in_neo4j_count"]}, '
            f'field_mismatches={summary["field_mismatch_count"]}'
        )

    return summary, comparison


def build_window():
    """Return the comparison time window as UTC datetimes.

    Returns:
        tuple[datetime, datetime]: window_start and window_end as timezone-aware
            UTC datetimes. window_start is WINDOW_START_HOURS ago, window_end
            is WINDOW_END_HOURS ago.
    """
    now = dt.datetime.now(dt.timezone.utc)
    window_start = now - dt.timedelta(hours=config.WINDOW_START_HOURS)
    window_end = now - dt.timedelta(hours=config.WINDOW_END_HOURS)
    log.info(f'Comparison window: {window_start.isoformat()} -> {window_end.isoformat()}')
    return window_start, window_end


def build_field_specs(check):
    """Parse key and compare field specs from a check config.

    Args:
        check (dict): Check configuration dict containing 'key_fields' and
            optionally 'compare_fields'.

    Returns:
        tuple[list[dict], list[dict]]: key_specs and compare_specs, each a
            list of field spec dicts.

    Raises:
        ValueError: If the check is missing key_fields.
    """
    key_fields = check.get('key_fields') or []
    if not key_fields:
        raise ValueError(f'Check {check.get("name")} is missing key_fields.')
    compare_fields = check.get('compare_fields') or []
    key_specs = _build_field_specs(key_fields, 'k')
    compare_specs = _build_field_specs(compare_fields, 'f')
    return key_specs, compare_specs


def check_window_config(check):
    """Validate and detect whether the check uses a time window.

    Args:
        check (dict): Check configuration dict.

    Returns:
        bool: True if the check uses a time window, False for a full-table scan.

    Raises:
        ValueError: If only one of window_property / snowflake_window_column
            is defined.
    """
    has_neo4j = bool(check.get('window_property'))
    has_snowflake = bool(check.get('snowflake_window_column'))
    if has_neo4j != has_snowflake:
        raise ValueError(
            f'Check {check.get("name")} must define both window_property and '
            'snowflake_window_column, or neither for a full scan.'
        )
    return has_neo4j


def build_neo4j_query(check, field_specs, use_window):
    """Build a Cypher MATCH query for the given check and field specs.

    Args:
        check (dict): Check configuration dict with 'cypher_pattern' and
            optionally 'window_property'.
        field_specs (list[dict]): Combined list of key and compare field specs.
        use_window (bool): Whether to include a time-window WHERE clause.

    Returns:
        str: The Cypher query string.
    """
    select_clause = ', '.join(
        f"{spec['neo4j']} AS {spec['alias']}" for spec in field_specs
    )
    if use_window:
        where_clause = (
            f"WHERE {check['window_property']} >= $window_start "
            f"AND {check['window_property']} < $window_end"
        )
        return f"MATCH {check['cypher_pattern']} {where_clause} RETURN {select_clause}"
    return f"MATCH {check['cypher_pattern']} RETURN {select_clause}"


def build_snowflake_query(check, field_specs, use_window):
    """Build a Snowflake SELECT query for the given check and field specs.

    Args:
        check (dict): Check configuration dict with 'snowflake_table' and
            optionally 'snowflake_window_column'.
        field_specs (list[dict]): Combined list of key and compare field specs.
        use_window (bool): Whether to include a time-window WHERE clause.

    Returns:
        str: The Snowflake SQL query string.
    """
    select_clause = ', '.join(
        f"{spec['snowflake']} AS {spec['alias']}" for spec in field_specs
    )
    table = check['snowflake_table']
    if '.' not in table:
        table = f"{config.SNOWFLAKE_DATABASE}.{config.SNOWFLAKE_SCHEMA}.{table}"
    if use_window:
        where_clause = (
            f"WHERE {check['snowflake_window_column']} >= %(window_start)s "
            f"AND {check['snowflake_window_column']} < %(window_end)s"
        )
        return f"SELECT {select_clause} FROM {table} {where_clause}"
    return f"SELECT {select_clause} FROM {table}"


def fetch_neo4j_rows(driver, query, params, key_aliases, compare_aliases):
    """Fetch rows from Neo4j and index them by composite key.

    Rows with any null key component, or duplicate keys, are skipped.

    Args:
        driver (neo4j.Driver): Active Neo4j driver.
        query (str): Cypher query to run.
        params (dict): Query parameters (e.g. window start/end).
        key_aliases (list[str]): Aliases identifying the composite key fields.
        compare_aliases (list[str]): Aliases for the fields to compare.

    Returns:
        dict: Mapping of key tuple to a dict of compare alias -> normalized value.
    """
    row_map = {}
    with driver.session(database=config.NEO4J_DATABASE_NAME) as session:
        result = session.run(query, params)
        for record in result:
            row = record.data()
            key = tuple(normalize_value(row.get(alias)) for alias in key_aliases)
            if any(value is None for value in key):
                continue
            if key in row_map:
                continue
            row_map[key] = {
                alias: normalize_value(row.get(alias))
                for alias in compare_aliases
            }
    return row_map


def fetch_snowflake_rows(executor, query, params, key_aliases, compare_aliases):
    """Fetch rows from Snowflake and index them by composite key.

    Rows with any null key component, or duplicate keys, are skipped.

    Args:
        executor (SnowflakeSQLExecutor): Active Snowflake executor.
        query (str): SQL query to run.
        params (dict): Query parameters (e.g. window start/end).
        key_aliases (list[str]): Aliases identifying the composite key fields.
        compare_aliases (list[str]): Aliases for the fields to compare.

    Returns:
        dict: Mapping of key tuple to a dict of compare alias -> normalized value.
    """
    row_map = {}
    rows = executor.fetchall(query, params, dict_cursor=True)
    for row_dict in rows:
        key = tuple(normalize_value(_row_value(row_dict, alias)) for alias in key_aliases)
        if any(value is None for value in key):
            continue
        if key in row_map:
            continue
        row_map[key] = {
            alias: normalize_value(_row_value(row_dict, alias))
            for alias in compare_aliases
        }
    return row_map


def compare_row_maps(neo4j_rows, snowflake_rows, compare_specs, sample_number=None):
    """Compare two row maps and collect discrepancies.

    Args:
        neo4j_rows (dict): Row map returned by fetch_neo4j_rows.
        snowflake_rows (dict): Row map returned by fetch_snowflake_rows.
        compare_specs (list[dict]): Field spec dicts for comparison fields.
        sample_number (int, optional): Max discrepancies to include per
            category. Defaults to None (unlimited).

    Returns:
        dict: With keys 'missing_in_snowflake', 'missing_in_neo4j', and
            'field_mismatches', each a list of discrepancy entries.
    """
    missing_in_snowflake = []
    missing_in_neo4j = []
    field_mismatches = []

    compare_aliases = [spec['alias'] for spec in compare_specs]
    compare_labels = {spec['alias']: spec['label'] for spec in compare_specs}

    for key, neo4j_values in neo4j_rows.items():
        snowflake_values = snowflake_rows.get(key)
        if snowflake_values is None:
            missing_in_snowflake.append(key)
            continue
        for alias in compare_aliases:
            neo4j_value = neo4j_values.get(alias)
            snowflake_value = snowflake_values.get(alias)
            if not values_equal(neo4j_value, snowflake_value):
                field_mismatches.append({
                    'key': key,
                    'field': compare_labels.get(alias, alias),
                    'neo4j': format_value(neo4j_value),
                    'snowflake': format_value(snowflake_value),
                })

    for key in snowflake_rows:
        if key not in neo4j_rows:
            missing_in_neo4j.append(key)

    if sample_number is not None:
        missing_in_snowflake = missing_in_snowflake[:sample_number]
        missing_in_neo4j = missing_in_neo4j[:sample_number]
        field_mismatches = field_mismatches[:sample_number]

    return {
        'missing_in_snowflake': missing_in_snowflake,
        'missing_in_neo4j': missing_in_neo4j,
        'field_mismatches': field_mismatches,
    }


def normalize_value(value):
    """Normalize a raw field value to a comparable Python type.

    Converts Neo4j native types via to_native(), empty containers to None,
    naive datetimes to UTC-aware, and Decimal to int or float.

    Args:
        value: The raw value to normalize.

    Returns:
        The normalized value, or None for empty containers.
    """
    if value is None:
        return None
    if hasattr(value, 'to_native'):
        try:
            value = value.to_native()
        except Exception:
            pass
    if isinstance(value, dict) and not value:
        return None
    if isinstance(value, (list, tuple)) and not value:
        return None
    if isinstance(value, dt.datetime):
        if value.tzinfo is None:
            value = value.replace(tzinfo=dt.timezone.utc)
        return value
    if isinstance(value, dt.date):
        return value
    if isinstance(value, decimal.Decimal):
        if value == value.to_integral_value():
            return int(value)
        return float(value)
    return value


def values_equal(left, right):
    """Compare two normalized field values for equality.

    Treats None and '' as equal. Coerces date/datetime pairs to UTC-aware
    datetimes before comparing.

    Args:
        left: First value to compare.
        right: Second value to compare.

    Returns:
        bool: True if the values are considered equal.
    """
    if _is_empty(left) and _is_empty(right):
        return True
    if _is_empty(left) or _is_empty(right):
        return False
    if isinstance(left, (dt.date, dt.datetime)) or isinstance(right, (dt.date, dt.datetime)):
        left_dt = _coerce_to_datetime(left)
        right_dt = _coerce_to_datetime(right)
        if left_dt is not None and right_dt is not None:
            return left_dt == right_dt
    return left == right


def format_value(value):
    """Format a value for human-readable output.

    Converts datetime and date objects to ISO 8601 strings.

    Args:
        value: The value to format.

    Returns:
        The formatted value, or None if value is None.
    """
    if value is None:
        return None
    if isinstance(value, dt.datetime):
        return value.isoformat()
    if isinstance(value, dt.date):
        return value.isoformat()
    return value


def _build_field_specs(fields, alias_prefix):
    """Build a list of field spec dicts with generated aliases.

    Args:
        fields (list[dict]): Field mappings with 'neo4j', 'snowflake', and
            optional 'label' keys.
        alias_prefix (str): Prefix for generated aliases (e.g. 'k' for key
            fields, 'f' for compare fields).

    Returns:
        list[dict]: Field spec dicts with 'alias', 'neo4j', 'snowflake', and
            'label' keys.
    """
    specs = []
    for index, field in enumerate(fields):
        alias = f'{alias_prefix}{index}'
        specs.append({
            'alias': alias,
            'neo4j': field['neo4j'],
            'snowflake': field['snowflake'],
            'label': field.get('label') or field['snowflake'],
        })
    return specs


def _row_value(row_dict, alias):
    """Look up a field value in a row dict, trying alias, UPPER, and lower variants.

    Args:
        row_dict (dict): A single result row from Snowflake.
        alias (str): The field alias to look up.

    Returns:
        The value for the alias, or None if not found.
    """
    if alias in row_dict:
        return row_dict[alias]
    alias_upper = alias.upper()
    if alias_upper in row_dict:
        return row_dict[alias_upper]
    alias_lower = alias.lower()
    return row_dict.get(alias_lower)


def _coerce_to_datetime(value):
    """Coerce a date or datetime to a UTC-aware datetime.

    Args:
        value: A datetime.datetime, datetime.date, or None.

    Returns:
        datetime.datetime: UTC-aware datetime, or None if value is None or
            cannot be coerced.
    """
    if value is None:
        return None
    if isinstance(value, dt.datetime):
        if value.tzinfo is None:
            return value.replace(tzinfo=dt.timezone.utc)
        return value
    if isinstance(value, dt.date):
        return dt.datetime(value.year, value.month, value.day, tzinfo=dt.timezone.utc)
    return None


def _is_empty(value):
    """Return True if value is None or an empty string."""
    return value is None or value == ''