import re
import csv
import io
import logging
from typing import List, Dict, Any, Optional
from collections import Counter

logger = logging.getLogger("ocr.csv_service")

# Spreadsheet apps run a cell starting with one of these as a formula (CSV injection).
_FORMULA_PREFIXES = ("=", "+", "-", "@", "\t", "\r")


def _is_number(s: str) -> bool:
    try:
        float(s)
        return True
    except ValueError:
        return False


def csv_safe_cell(value: Any) -> Any:
    """Prefixes a text cell that starts with = + - @ tab or CR with "'"; numbers (incl. negatives) are kept."""
    if isinstance(value, str) and value.startswith(_FORMULA_PREFIXES) and not _is_number(value):
        return "'" + value
    return value


def csv_safe_row(values: List[Any]) -> List[Any]:
    return [csv_safe_cell(v) for v in values]


def csv_unescape_cell(value: str) -> str:
    """Reverses csv_safe_cell: strips the "'" only where csv_safe_cell would have added it."""
    if value.startswith("'") and csv_safe_cell(value[1:]) == value:
        return value[1:]
    return value

GENERIC_LEGAL_SUFFIXES = [
    (re.compile(r"\b(public\s+limited\s+company|plc)\b", re.I), "plc"),
    (re.compile(r"\b(limited|ltd)\b", re.I), "ltd"),
    (re.compile(r"\b(private\s+limited|pvt\s+ltd|pvt)\b", re.I), "pvt ltd"),
    (re.compile(r"\b(incorporated|inc)\b", re.I), "inc"),
    (re.compile(r"\b(corporation|corp)\b", re.I), "corp"),
    (re.compile(r"\b(sdn\.?\s*bhd\.?)\b", re.I), "sdn bhd"),
    (re.compile(r"\b(llc|gmbh|sa|bv)\b", re.I), "llc"),
    (re.compile(r"\b(co\.|company)\b", re.I), "co"),
]

def normalize_entity_name(text: Optional[str]) -> str:
    """Normalizes an entity name for canonical entity matching across reports."""
    if not text:
        return ""
    s = re.sub(r"^the\s+", "", str(text).lower().strip())
    s = re.sub(r"[\.,\(\)\[\]]", " ", s)
    for pat, _ in GENERIC_LEGAL_SUFFIXES:
        s = pat.sub(" ", s)
    s = re.sub(r"\b(bank|plc|ltd|limited|company|co|corp|inc)\b", " ", s)
    return re.sub(r"\s+", " ", s).strip()

def is_distinct_subsidiary_entity(entity: Optional[str], parent_candidates: Optional[Any] = None) -> bool:
    """
    Determines if an entity represents a distinct subsidiary or non-parent legal entity.
    Returns True for separate legal entities/subsidiaries (e.g. City Brokerage Ltd, CBL Money Transfer),
    and False for the parent company (e.g. The City Bank Ltd, City Bank PLC) or unassigned/group metrics.
    """
    if not entity:
        return False
    e_str = str(entity).strip()
    if not e_str:
        return False
    e_lower = e_str.lower()
    if e_lower in ("subsidiary", "subsidiaries"):
        return True
    
    norm_e = normalize_entity_name(e_str)
    if not norm_e:
        return False

    if parent_candidates:
        norm_parents = {normalize_entity_name(p) for p in parent_candidates if p}
        for np in norm_parents:
            if np and norm_e == np:
                return False

    return True

STATEMENT_TYPE_ORDER = [
    "BALANCE_SHEET",
    "INCOME_STATEMENT",
    "CASH_FLOW",
    "CHANGES_IN_EQUITY",
    "FINANCIAL_HIGHLIGHTS",
    "NOTES_AND_FOOTNOTES",
    "UNKNOWN"
]

def normalize_statement_type(stmt: Optional[str]) -> str:
    if not stmt:
        return "UNKNOWN"
    s = str(stmt).strip().upper()
    if "APPROPRIAT" in s:
        return "CHANGES_IN_EQUITY"
    if "BALANCE" in s or "POSITION" in s:
        return "BALANCE_SHEET"
    if "INCOME" in s or "PROFIT" in s or "LOSS" in s or "COMPREHENSIVE" in s:
        return "INCOME_STATEMENT"
    if "CASH" in s or "FLOW" in s:
        return "CASH_FLOW"
    if "EQUITY" in s:
        return "CHANGES_IN_EQUITY"
    if "NOTE" in s or "FOOTNOTE" in s or "SCHEDULE" in s or "DISCLOSURE" in s:
        return "NOTES_AND_FOOTNOTES"
    if "HIGHLIGHT" in s:
        return "FINANCIAL_HIGHLIGHTS"
    if s in STATEMENT_TYPE_ORDER:
        return s
    return "UNKNOWN"

def normalize_field_name(field: str) -> str:
    cleaned = field.strip().lower().replace("&", "and")
    cleaned = re.sub(r"[\(\)\[\]\,\-\/\:]", " ", cleaned)
    return re.sub(r"\s+", " ", cleaned).strip()

def normalize_unit(unit_str: Optional[str], field_name: str = "") -> str:
    if not unit_str:
        if "ratio" in field_name.lower():
            return "Times"
        return "BDT"

    cleaned = unit_str.strip()
    u_lower = cleaned.lower().rstrip(".")

    if u_lower in ("bdt", "tk", "taka"):
        return "BDT"
    if u_lower in ("usd", "$"):
        return "USD"
    if u_lower in ("eur", "euro"):
        return "EUR"
    if u_lower in ("%", "percent", "percentage"):
        return "%"
    if u_lower.startswith("sft") or u_lower in ("sqft", "sq.ft", "square feet"):
        return "Sft."
    if u_lower in ("nos", "number", "numbers", "persons", "shares", "ordinary shares", "shareholders"):
        return "Nos."
    if u_lower in ("days", "day"):
        return "Days"
    if u_lower in ("mt", "metric ton", "metric tons"):
        return "MT"
    if u_lower in ("times", "ratio"):
        return "Times"

    return cleaned

def format_numeric_value(val: Optional[float]) -> str:
    if val is None:
        return ""
    if isinstance(val, (int, float)):
        if isinstance(val, float) and val.is_integer():
            return str(int(val))
        return str(val)
    return str(val)

_ISO_DATE_RE = re.compile(r"\b((?:19|20)\d{2})-\d{1,2}-\d{1,2}\b")
_DMY_DATE_RE = re.compile(r"\b\d{1,2}[./-]\d{1,2}[./-]((?:19|20)\d{2})\b")
_FY_RANGE_RE = re.compile(r"\b((?:19|20)\d{2})\s*[-/–]\s*(\d{4}|\d{2})\b")
_YEAR_RE = re.compile(r"\b((?:19|20)\d{2})\b")


def extract_year_int(val: Any) -> Optional[int]:
    """Year of a value, date or column header; the period-end year when several are printed.

    Dates are read before fiscal-year ranges, so "2023-06-30" is 2023 (never "2023-06" -> 2006).
    "2023-24" / "2023-2024" -> 2024; "July 01, 2020 to June 30, 2021" -> 2021; "30.06.2023" -> 2023.
    """
    if val is None or isinstance(val, bool):
        return None
    if isinstance(val, int):
        return val
    val_str = str(val).strip()
    dates = _ISO_DATE_RE.findall(val_str) + _DMY_DATE_RE.findall(val_str)
    if dates:
        return max(int(y) for y in dates)
    for m in _FY_RANGE_RE.finditer(val_str):
        y1, y2 = int(m.group(1)), m.group(2)
        end = int(y2) if len(y2) == 4 else (y1 // 100) * 100 + int(y2)
        if len(y2) == 2 and end < y1:
            end += 100  # "1999-00"
        if end == y1 + 1:
            return end
    years = _YEAR_RE.findall(val_str)
    if years:
        return max(int(y) for y in years)
    return None


def build_raw_csv_from_metrics(
    metrics: List[Dict[str, Any]],
    company_name: Optional[str] = None,
    instrument_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    Transforms extracted metrics into consolidated multi-year financial_data.csv and stating_year.csv.
    Adheres strictly to the logic established in csv_creation_code.py.
    Partitions distinct subsidiary / non-parent legal entity schedules into their own distinct rows.
    """
    grouped_rows = {}
    years_set = set()
    raw_columns_set = set()
    remaining_data = []

    # Identify parent reporting entity candidates across the metrics set
    parent_candidates = set()
    if company_name:
        parent_candidates.add(company_name)
    if instrument_code:
        parent_candidates.add(instrument_code)

    entity_counts = Counter()
    for m in metrics:
        ent = (m.get("scope") or {}).get("entity") if isinstance(m.get("scope"), dict) else m.get("entity")
        if ent:
            entity_counts[str(ent).strip()] += 1
        inst = m.get("instrument_code")
        if inst:
            parent_candidates.add(inst)

    # Names that two lines of one report share under different headings ("Total" of note 45 and of note 46):
    # those keys carry the category, in every report, so the lines are never one row.
    cats_by_name: Dict[tuple, set] = {}
    for metric in metrics:
        if metric.get("verification_flag") == "FIELD_UNIDENTIFIED":
            continue
        sc = metric.get("scope") or {}
        sc_raw = str(sc.get("type") if isinstance(sc, dict) else sc or "CONSOLIDATED").strip().upper()
        ent = sc.get("entity") if isinstance(sc, dict) else metric.get("entity")
        cat = (metric.get("concept") or {}).get("category") or metric.get("category") or ""
        cats_by_name.setdefault((normalize_statement_type(metric.get("statement_type", "UNKNOWN")),
                                 normalize_field_name(metric.get("field") or metric.get("raw_field") or ""),
                                 "STANDALONE" if sc_raw == "STANDALONE" else "CONSOLIDATED", str(ent or "").lower(),
                                 str(metric.get("fiscal_year"))), set()).add(normalize_field_name(cat))
    colliding = {k[:4] for k, v in cats_by_name.items() if len(v) > 1}

    # A report that prints a year more than once with a restated column ("1st July 2022 (Re-Stated)" next to
    # "30 June 2022"): each of its columns for that year is its own raw column, named by its printed date.
    restated_report_years = set()
    for metric in metrics:
        for obs in metric.get("data", []):
            if obs.get("restated"):
                y_obs = extract_year_int(obs.get("year")) or extract_year_int(obs.get("date"))
                rep_obs = str(metric.get("fiscal_year") or "").strip()
                rep_obs = rep_obs[2:].strip() if rep_obs.upper().startswith("FY") else rep_obs
                if y_obs is not None and rep_obs:
                    restated_report_years.add((rep_obs, y_obs))

    for metric in metrics:
        if metric.get("verification_flag") == "FIELD_UNIDENTIFIED":
            continue      # the page does not say what the line is: kept in the database, not used in the tables
        stmt = normalize_statement_type(metric.get("statement_type", "UNKNOWN"))
        raw_f = metric.get("raw_field", "")
        f_name = metric.get("field", raw_f)
        tag = metric.get("tag", "FINANCIAL_METRIC")
        page_num = metric.get("page_number", 1)
        reporting_fy = extract_year_int(metric.get("fiscal_year"))

        # Extract 6 Canonical Dimensions
        concept = metric.get("concept") or {}
        scope_obj = metric.get("scope") or {}
        behavior = metric.get("behavior") or {}

        c_name = concept.get("name") or metric.get("concept_name") or f_name or raw_f
        category = concept.get("category") or metric.get("category") or ""
        agg_level = concept.get("level") or metric.get("aggregation_level") or "LINE_ITEM"

        entity = scope_obj.get("entity") if isinstance(scope_obj, dict) else metric.get("entity")
        segment = scope_obj.get("segment") if isinstance(scope_obj, dict) else metric.get("segment")

        temporal = behavior.get("temporal") or metric.get("temporal_state") or "PERIOD_FLOW"
        act_val = behavior.get("action") or metric.get("action")
        m_type = behavior.get("metric_type") or metric.get("metric_type") or "CURRENCY"
        qual = behavior.get("qualifier") or metric.get("qualifier")

        is_sub = is_distinct_subsidiary_entity(entity, parent_candidates)
        display_f = f_name
        norm_ent = ""
        if is_sub and entity:
            ent_str = str(entity).strip()
            if ent_str.lower() not in f_name.lower():
                display_f = f"{f_name} ({ent_str})"
            norm_ent = normalize_entity_name(ent_str)
        if segment and str(segment).strip().lower() not in display_f.lower():
            display_f = f"{display_f} ({str(segment).strip()})"   # each segment column is its own row

        # Observations
        for obs in metric.get("data", []):
            obs_year = extract_year_int(obs.get("year"))
            if obs_year is None and obs.get("date"):
                obs_year = extract_year_int(obs.get("date"))

            num_val = obs.get("numeric_value")
            if obs_year is None or num_val is None:
                remaining_data.append({
                    "metric": display_f,
                    "statement": stmt,
                    "obs": obs,
                    "reason": "Missing observation year or numeric value"
                })
                continue

            years_set.add(obs_year)
            multiplier = float(obs.get("multiplier", 1.0) or 1.0)
            effective_val = float(num_val) * multiplier
            unit = normalize_unit(obs.get("unit"), display_f)

            # Determine publication report fiscal year label & clean identifier
            rep_fy_raw = str(metric.get("fiscal_year") or (f"{reporting_fy-1}-{str(reporting_fy)[-2:]}" if reporting_fy else "") or "").strip()
            if rep_fy_raw.upper().startswith("FY"):
                rep_fy_clean = rep_fy_raw[2:].strip()
            else:
                rep_fy_clean = rep_fy_raw
            if not rep_fy_clean and reporting_fy:
                rep_fy_clean = str(reporting_fy)
            if not rep_fy_clean and obs_year:
                rep_fy_clean = str(obs_year)

            obs_restated = bool(obs.get("restated"))
            obs_date = str(obs.get("date") or "").strip() or None
            if (rep_fy_clean, obs_year) in restated_report_years:
                suffix = obs_date or ("restated" if obs_restated else "stated")
                col_key = f"{rep_fy_clean}__{obs_year}@{suffix}"
                raw_columns_set.add((rep_fy_clean, obs_year, suffix, obs_date, obs_restated))
            else:
                col_key = f"{rep_fy_clean}__{obs_year}"
                raw_columns_set.add((rep_fy_clean, obs_year, None, None, False))

            src_raw = str(scope_obj.get("type") if isinstance(scope_obj, dict) else metric.get("scope", "CONSOLIDATED")).strip().upper()
            src_norm = "STANDALONE" if src_raw == "STANDALONE" else "CONSOLIDATED"

            # Unique key by Statement, Normalized Field, Unit, Tag, Scope, and Entity Qualifier
            name_key = (stmt, normalize_field_name(f_name), src_norm, str(entity or "").lower())
            row_key = (stmt, normalize_field_name(display_f), unit, tag, src_norm, norm_ent,
                       normalize_field_name(category) if name_key in colliding else "")

            if row_key not in grouped_rows:
                grouped_rows[row_key] = {
                    "field": display_f,
                    "concept_name": c_name,
                    "concept": {
                        "name": c_name,
                        "statement": stmt,
                        "category": category,
                        "level": agg_level
                    },
                    "scope_obj": {
                        "type": src_norm,
                        "entity": entity,
                        "segment": segment
                    },
                    "behavior": {
                        "temporal": temporal,
                        "action": act_val,
                        "metric_type": m_type,
                        "qualifier": qual
                    },
                    "category": category,
                    "aggregation_level": agg_level,
                    "entity": entity,
                    "segment": segment,
                    "temporal_state": temporal,
                    "action": act_val,
                    "metric_type": m_type,
                    "qualifier": qual,
                    "unit": unit,
                    "tag": tag,
                    "statement_type": stmt,
                    "scope": src_norm,
                    "values": {},          # col_key -> effective_val AND year -> effective_val
                    "cells": {},           # col_key -> cell_info
                    "stating_years": {},    # year -> reporting_fy
                    "page_numbers": {},     # year -> page_num
                    "page_number": page_num,
                    "report_year": metric.get("fiscal_year") or (str(reporting_fy) if reporting_fy else None),
                    "table_num": obs.get("table_num") or 1,
                    "table_row": obs.get("table_row"),
                    "table_col": obs.get("table_col"),
                    "raw_field": raw_f or f_name,
                    "provenance": {
                        "report_year": metric.get("fiscal_year") or (str(reporting_fy) if reporting_fy else None),
                        "page_number": page_num,
                        "table_num": obs.get("table_num") or 1,
                        "table_row": obs.get("table_row"),
                        "table_col": obs.get("table_col"),
                        "raw_field": raw_f or f_name
                    },
                    "all_observations": {},
                    "table_nums": {},
                    "raw_fields": {},
                    "reporting_years": set(),
                    "publication_pages": set()
                }

            row_entry = grouped_rows[row_key]
            curr_table = obs.get("table_num") or 1
            curr_row = obs.get("table_row")
            curr_col = obs.get("table_col")

            cell_info = {
                "value": effective_val,
                "numeric_value": num_val,
                "effective_val": effective_val,
                "multiplier": multiplier,
                "unit": unit,
                "report_fiscal_year": rep_fy_clean,
                "observation_year": obs_year,
                "page_number": page_num,
                "table_num": curr_table,
                "table_row": curr_row,
                "table_col": curr_col,
                "raw_field": raw_f or display_f,
                "notes": metric.get("notes") or []
            }

            if col_key not in row_entry["cells"]:
                row_entry["cells"][col_key] = cell_info
                row_entry["values"][col_key] = effective_val
            else:
                existing_c = row_entry["cells"][col_key]
                if existing_c.get("effective_val") == 0.0 and effective_val != 0.0:
                    row_entry["cells"][col_key] = cell_info
                    row_entry["values"][col_key] = effective_val

            rep_y_str = str(metric.get("fiscal_year") or reporting_fy or "").strip()
            if rep_y_str:
                row_entry["reporting_years"].add(rep_y_str)
            if reporting_fy:
                row_entry["reporting_years"].add(str(reporting_fy))
            if page_num and rep_y_str:
                row_entry["publication_pages"].add(f"{rep_y_str}:p{page_num}")

            if obs_year not in row_entry["all_observations"]:
                row_entry["all_observations"][obs_year] = []
            row_entry["all_observations"][obs_year].append({
                "reporting_fy": reporting_fy or obs_year,
                "report_year": metric.get("fiscal_year") or (str(reporting_fy) if reporting_fy else None),
                "document_id": metric.get("document_id"),
                "page_num": page_num,
                "table_num": curr_table,
                "table_row": obs.get("table_row"),
                "table_col": obs.get("table_col"),
                "raw_field": raw_f or f_name,
                "field": f_name,
                "effective_val": effective_val
            })

            existing_val = row_entry["values"].get(obs_year)
            existing_stating = row_entry["stating_years"].get(obs_year, 0)
            curr_reporting = reporting_fy or obs_year
            restated_years = row_entry.setdefault("restated_years", {})
            should_replace = (existing_val is None or curr_reporting > existing_stating
                              or (curr_reporting == existing_stating and existing_val == 0.0 and effective_val != 0.0)
                              or (curr_reporting == existing_stating and obs_restated and not restated_years.get(obs_year)))

            if should_replace:
                restated_years[obs_year] = obs_restated or (restated_years.get(obs_year, False)
                                                           and curr_reporting == existing_stating)
                row_entry["values"][obs_year] = effective_val
                row_entry["stating_years"][obs_year] = curr_reporting
                row_entry["page_numbers"][obs_year] = page_num
                row_entry.setdefault("table_nums", {})[obs_year] = curr_table
                row_entry.setdefault("raw_fields", {})[obs_year] = raw_f or display_f
                row_entry["page_number"] = page_num
                row_entry["report_year"] = metric.get("fiscal_year") or (str(reporting_fy) if reporting_fy else None)
                row_entry["table_num"] = curr_table
                row_entry["table_row"] = obs.get("table_row") or row_entry.get("table_row")
                row_entry["table_col"] = obs.get("table_col") or row_entry.get("table_col")
                row_entry["raw_field"] = raw_f or display_f
                row_entry["provenance"] = {
                    "report_year": metric.get("fiscal_year") or (str(reporting_fy) if reporting_fy else None),
                    "page_number": page_num,
                    "table_num": curr_table,
                    "table_row": obs.get("table_row") or row_entry.get("table_row"),
                    "table_col": obs.get("table_col") or row_entry.get("table_col"),
                    "raw_field": raw_f or display_f
                }

    # Sort years ascending
    sorted_years = sorted(list(years_set))
    if not sorted_years:
        sorted_years = [2024, 2025]

    # Build and sort subcategorized raw columns: (report_fiscal_year, observation_year)
    def _parse_start_year(fy_str: str) -> int:
        m = re.search(r"(\d{4})", str(fy_str))
        return int(m.group(1)) if m else 0

    sorted_raw_cols_meta = sorted(
        list(raw_columns_set),
        key=lambda col: (_parse_start_year(col[0]), col[0], col[1], col[3] or "", col[4])
    )
    raw_columns = []
    for rep_fy, o_year, suffix, o_date, o_restated in sorted_raw_cols_meta:
        raw_columns.append({
            "key": f"{rep_fy}__{o_year}" + (f"@{suffix}" if suffix else ""),
            "report_fiscal_year": rep_fy,
            "report_year_label": f"FY {rep_fy}",
            "observation_year": o_year,
            # a year the report prints more than once is headed by each column's printed date
            "header": (o_date or str(o_year)) if suffix else str(o_year),
            "observation_date": o_date,
            "restated": o_restated,
            "group_header": f"FY {rep_fy}"
        })

    # Sort rows by statement type order then field name then source
    def sort_key(item):
        k = item[0]
        stmt = k[0]
        norm_f = k[1]
        src_norm = k[4] if len(k) > 4 else ""
        try:
            stmt_idx = STATEMENT_TYPE_ORDER.index(stmt)
        except ValueError:
            stmt_idx = 999
        return (stmt_idx, norm_f, src_norm)

    sorted_items = sorted(grouped_rows.items(), key=sort_key)

    # Build CSV Strings
    out_csv = io.StringIO()
    writer_csv = csv.writer(out_csv)
    out_stating = io.StringIO()
    writer_stating = csv.writer(out_stating)

    if raw_columns:
        headers_group = ["field", "unit", "tag", "statement_type"] + [c["group_header"] for c in raw_columns]
        headers_sub = ["", "", "", ""] + [c["header"] for c in raw_columns]
        writer_csv.writerow(headers_group)
        writer_csv.writerow(headers_sub)
        writer_stating.writerow(headers_group)
        writer_stating.writerow(headers_sub)
        headers = headers_group
    else:
        headers = ["field", "unit", "tag", "statement_type"] + [str(y) for y in sorted_years]
        writer_csv.writerow(headers)
        writer_stating.writerow(headers)

    table_rows = []
    for item_key, row in sorted_items:
        if raw_columns:
            row_vals = [row["field"], row["unit"], row["tag"], row["statement_type"]]
            row_stating_vals = [row["field"], row["unit"], row["tag"], row["statement_type"]]
            for c in raw_columns:
                k = c["key"]
                cell_item = row.get("cells", {}).get(k)
                val = cell_item.get("value") if cell_item else None
                row_vals.append(format_numeric_value(val))
                row_stating_vals.append(str(c["report_fiscal_year"]))
            writer_csv.writerow(csv_safe_row(row_vals))
            writer_stating.writerow(csv_safe_row(row_stating_vals))
        else:
            row_vals = [row["field"], row["unit"], row["tag"], row["statement_type"]]
            row_stating_vals = [row["field"], row["unit"], row["tag"], row["statement_type"]]
            for y in sorted_years:
                val = row["values"].get(y)
                st_year = row["stating_years"].get(y)
                row_vals.append(format_numeric_value(val))
                row_stating_vals.append(str(st_year) if st_year else "")
            writer_csv.writerow(csv_safe_row(row_vals))
            writer_stating.writerow(csv_safe_row(row_stating_vals))

        values_dict = {}
        for c in raw_columns:
            k = c["key"]
            cell_item = row.get("cells", {}).get(k)
            values_dict[k] = cell_item.get("value") if cell_item else None

        for y in sorted_years:
            val = row["values"].get(y)
            values_dict[str(y)] = val

        restated_variance_years = []
        restatement_variances = {}
        for y, obs_list in row.get("all_observations", {}).items():
            if len(obs_list) > 1:
                # Group observations by unique reporting publication instance: (reporting_fy, page_num, table_num)
                inst_map = {}
                for o in obs_list:
                    ikey = (o.get("reporting_fy", 0), o.get("page_num", 0), o.get("table_num", 1))
                    if ikey not in inst_map:
                        inst_map[ikey] = []
                    inst_map[ikey].append(o)

                # Pick representative observation for each distinct reporting publication instance
                unique_reps = []
                for ikey in sorted(inst_map.keys()):
                    siblings = inst_map[ikey]
                    if len(siblings) == 1:
                        unique_reps.append(siblings[0])
                    else:
                        # Intra-table siblings: prefer summary/total row, or first non-zero/parent
                        chosen = siblings[0]
                        for s in siblings:
                            s_raw = f"{s.get('raw_field', '')} {s.get('field', '')}".lower()
                            if re.search(r"\[total\b|total\s+consolidated|\btotal\b", s_raw):
                                chosen = s
                                break
                            elif chosen.get("effective_val") == 0.0 and s.get("effective_val") != 0.0:
                                chosen = s
                        unique_reps.append(chosen)

                # Only evaluate chronological restatement across distinct reporting fiscal years (different annual reports):
                rep_fys = set(r.get("reporting_fy", 0) for r in unique_reps)
                if len(unique_reps) > 1 and len(rep_fys) > 1:
                    orig_val = unique_reps[0]["effective_val"]
                    latest_val = unique_reps[-1]["effective_val"]
                    diff = latest_val - orig_val
                    if abs(diff) > 1e-4:
                        y_str = str(y)
                        restated_variance_years.append(y_str)
                        pct = (diff / orig_val * 100) if orig_val != 0 else None     # None = n/a (zero base)
                        restatement_variances[y_str] = {
                            "original_value": orig_val,
                            "restated_value": latest_val,
                            "difference": diff,
                            "percent_change": None if pct is None else round(pct, 2)
                        }

        row_scope = row.get("scope", "CONSOLIDATED")
        if isinstance(row_scope, dict):
            row_scope = str(row_scope.get("type") or "CONSOLIDATED").strip().upper()
        else:
            row_scope = str(row_scope or "CONSOLIDATED").strip().upper()
        table_rows.append({
            "field": row["field"],
            "concept_name": row.get("concept_name") or row["field"],
            "concept": row.get("concept") or {
                "name": row.get("concept_name") or row["field"],
                "statement": row["statement_type"],
                "category": row.get("category", ""),
                "level": row.get("aggregation_level", "LINE_ITEM")
            },
            "scope_obj": row.get("scope_obj") or {
                "type": row_scope,
                "entity": row.get("entity"),
                "segment": row.get("segment")
            },
            "behavior": row.get("behavior") or {
                "temporal": row.get("temporal_state", "PERIOD_FLOW"),
                "action": row.get("action"),
                "metric_type": row.get("metric_type", "CURRENCY"),
                "qualifier": row.get("qualifier")
            },
            "category": row.get("category", ""),
            "aggregation_level": row.get("aggregation_level", "LINE_ITEM"),
            "entity": row.get("entity"),
            "segment": row.get("segment"),
            "temporal_state": row.get("temporal_state", "PERIOD_FLOW"),
            "action": row.get("action"),
            "metric_type": row.get("metric_type", "CURRENCY"),
            "qualifier": row.get("qualifier"),
            "unit": row["unit"],
            "tag": row["tag"],
            "statement_type": row["statement_type"],
            "scope": row_scope,
            "values": values_dict,
            "cells": row.get("cells", {}),
            "stating_years": {str(k): v for k, v in row["stating_years"].items()},
            "restated_variance_years": restated_variance_years,
            "restatement_variances": restatement_variances,
            "restatement_dots": {},
            "duplicate_in_report_years": [],
            "page_number": row.get("page_number"),
            "report_year": row.get("report_year"),
            "reporting_years": sorted(list(row.get("reporting_years", set()))),
            "publication_pages": sorted(list(row.get("publication_pages", set()))),
            "table_num": row.get("table_num"),
            "table_row": row.get("table_row"),
            "table_col": row.get("table_col"),
            "raw_field": row.get("raw_field"),
            "provenance": row.get("provenance")
        })

    return {
        "headers": headers,
        "raw_columns": raw_columns,
        "years": sorted_years,
        "rows": table_rows,
        "csv_text": out_csv.getvalue(),
        "stating_csv_text": out_stating.getvalue(),
        "remaining_data": remaining_data
    }

def parse_csv_text_to_rows(csv_text: str) -> List[Dict[str, Any]]:
    """Quickly parses financial CSV text into row objects without executing any model evaluation."""
    if not csv_text or not csv_text.strip():
        return []
    try:
        reader = csv.reader(io.StringIO(csv_text))
        all_lines = list(reader)
        if not all_lines or len(all_lines) < 2:
            return []

        # Check if line 1 is a second-tier header (first 4 columns blank, remaining non-empty)
        is_two_tier = False
        if len(all_lines) >= 3:
            first_data = all_lines[1]
            if len(first_data) >= 4 and not any(first_data[:4]) and any(first_data[4:]):
                is_two_tier = True

        if is_two_tier:
            group_headers = all_lines[0]
            sub_headers = all_lines[1]
            data_lines = all_lines[2:]
            year_cols = []
            for i in range(4, max(len(group_headers), len(sub_headers))):
                grp = group_headers[i].strip() if i < len(group_headers) else ""
                sub = sub_headers[i].strip() if i < len(sub_headers) else ""
                year_cols.append({"group": grp, "sub": sub, "raw": f"{grp} | {sub}" if grp else sub})
        else:
            headers = all_lines[0]
            data_lines = all_lines[1:]
            year_cols = [{"group": "", "sub": h.strip(), "raw": h.strip()} for h in headers[4:]]

        rows = []
        for line in data_lines:
            if not line or len(line) < 4:
                continue
            line = [csv_unescape_cell(cell) for cell in line]
            field = line[0]
            if not field.strip() and not any(line[4:]):
                continue
            unit = line[1]
            tag = line[2]
            stmt = line[3]
            vals = {}
            for i, col_meta in enumerate(year_cols):
                col_idx = 4 + i
                raw_v = line[col_idx].strip() if col_idx < len(line) else ""
                parsed_val = None
                if raw_v != "":
                    try:
                        parsed_val = float(raw_v)
                    except ValueError:
                        parsed_val = raw_v

                raw_hdr = col_meta["raw"]
                sub_hdr = col_meta["sub"]
                grp_hdr = col_meta["group"]

                vals[raw_hdr] = parsed_val
                if sub_hdr:
                    vals[sub_hdr] = parsed_val
                    y_int = extract_year_int(sub_hdr)
                    if y_int is not None:
                        vals[str(y_int)] = parsed_val
                if grp_hdr and sub_hdr:
                    clean_grp = grp_hdr.replace("FY ", "").strip()
                    vals[f"{clean_grp}__{sub_hdr}"] = parsed_val

            rows.append({
                "field": field,
                "unit": unit,
                "tag": tag,
                "statement_type": stmt,
                "values": vals
            })
        return rows
    except Exception as e:
        logger.warning(f"Error parsing CSV text to rows: {e}")
        return []
