"""
Extraction Verifier Service
----------------------------
Deterministic verification module that audits financial table extraction results
by comparing the count of extracted items and observation data points in the JSON
response against the actual count of numeric value cells discovered in the OCR Markdown.

Usage:
    from app.ocr.services.extraction_verifier import verify_extraction, analyze_markdown_tables
    result = verify_extraction(md_content, extracted_items)
    # result['is_success'], result['missing_cells'], result['status'], etc.
"""

import re
import logging
from typing import List, Dict, Any, Tuple, Optional

logger = logging.getLogger("ocr.extraction_verifier")

NIL_MARKERS = {"-", "—", "–", "nil", "Nil", "NIL", "n/a", "N/A", "none", "None", ".."}
NOTE_COLUMN_NAMES = {"note", "notes", "ref", "schedule", "sl", "sl.", "no", "no.", "item", "item no."}
LABEL_COLUMN_NAMES = {"particulars", "particular", "description", "descriptions", "item", "items"}


def clean_cell_text(text: str) -> str:
    """Removes markdown formatting markers like bold, italics, brackets, and leading/trailing whitespace."""
    if not text:
        return ""
    # Strip markdown bold/italic markers (*, _) and link/annotation brackets
    cleaned = re.sub(r"[\*_\[\]]", "", text).strip()
    return cleaned


def is_numeric_value_cell(cell_str: str) -> bool:
    """
    Checks if a markdown table cell contains an extractable financial numeric amount.
    Handles:
    - Normal integers and decimals: '483,560,259', '19024532', '12.50'
    - Parenthesized negatives: '(537,888,686)', '(10,787,799)', '[5,000]'
    - Decimals & percentages: '12.5%', '0.75'
    - Currency prefixes/suffixes: 'BDT 12,000', '৳ 500', 'Tk. 1,000', '$ 50'
    - Explicit nil/zero indicators: '-', '—', 'Nil', 'nil', 'N/A'
    """
    s = clean_cell_text(cell_str)
    if not s:
        return False
    if s in NIL_MARKERS:
        return True

    # Strip currency symbols, commas, percent, spaces
    clean = re.sub(r"[,\$%\s৳]", "", s)
    # Strip BDT / Tk / TK / USD words if present
    clean = re.sub(r"^(BDT|USD|Tk\.?|TK\.?|EUR|GBP)", "", clean, flags=re.IGNORECASE).strip()

    # Handle parenthesized negative: (123) or ( 123 )
    if clean.startswith("(") and clean.endswith(")"):
        clean = "-" + clean[1:-1]
    elif clean.startswith("[") and clean.endswith("]"):
        clean = "-" + clean[1:-1]

    # Try parsing as float
    try:
        float(clean)
        return True
    except ValueError:
        return False


def parse_numeric_cell_value(cell_str: str) -> Optional[float]:
    """
    Parses a markdown table cell into a float numeric value.
    Returns 0.0 for nil markers ('-', 'nil', 'N/A', etc.), float for valid numbers, or None if unparseable.
    Handles parentheses negatives, currency signs (BDT, USD, Tk), commas, and percentages.
    """
    s = clean_cell_text(cell_str)
    if not s:
        return None
    if s in NIL_MARKERS:
        return 0.0

    clean = re.sub(r"[,\$%\s৳]", "", s)
    clean = re.sub(r"^(BDT|USD|Tk\.?|TK\.?|EUR|GBP)", "", clean, flags=re.IGNORECASE).strip()

    if (clean.startswith("(") and clean.endswith(")")) or (clean.startswith("[") and clean.endswith("]")):
        clean = "-" + clean[1:-1].strip()

    try:
        return float(clean)
    except ValueError:
        return None


_DIVIDER_RE = re.compile(r"^\|?\s*[-:]+[-| :]*$")


def _split_cells(line: str) -> List[str]:
    return [c.strip() for c in line.split("|")[1:-1]]


def parse_markdown_tables(md_text: str) -> List[Dict[str, Any]]:
    """
    Single source of truth for markdown table numbering, shared by the extraction prompt annotator
    and every grid lookup, so (table_num, table_row, table_col) always means the same cell.

    A table is a run of consecutive lines that start and end with '|', containing a divider line
    (|---|---|) and at least one body row. Every line before the first divider is a header line;
    body rows are the non-divider lines after it, numbered from 1. Columns are the cells between
    pipes, numbered from 1.

    Returns [{"num", "start", "end", "header_lines": [[cells]], "rows": [[cells]],
              "row_line_idx": [line index of each body row]}]; start/end are line indices (end exclusive).
    """
    if not md_text:
        return []
    lines = md_text.splitlines()
    blocks = []
    start = None
    for i, line in enumerate(lines):
        stripped = line.strip()
        if stripped.startswith("|") and stripped.endswith("|") and len(stripped) > 1:
            if start is None:
                start = i
        elif start is not None:
            blocks.append((start, i))
            start = None
    if start is not None:
        blocks.append((start, len(lines)))

    tables = []
    for b_start, b_end in blocks:
        block = [lines[i].strip() for i in range(b_start, b_end)]
        div_idx = next((k for k, s in enumerate(block) if _DIVIDER_RE.match(s)), None)
        if div_idx is None:
            continue
        header_lines = [_split_cells(s) for s in block[:div_idx]]
        rows, row_line_idx = [], []
        for k in range(div_idx + 1, len(block)):
            if _DIVIDER_RE.match(block[k]):
                continue
            rows.append(_split_cells(block[k]))
            row_line_idx.append(b_start + k)
        if not rows:
            continue
        tables.append({
            "num": len(tables) + 1,
            "start": b_start,
            "end": b_end,
            "header_lines": header_lines,
            "rows": rows,
            "row_line_idx": row_line_idx,
        })
    return tables


def extract_markdown_table_grid(md_text: str) -> Dict[Tuple[int, int, int], str]:
    """
    Builds a lookup grid: (table_num, table_row, table_col) -> cell_raw_text, all 1-indexed,
    using the numbering of parse_markdown_tables.
    """
    grid = {}
    for t in parse_markdown_tables(md_text):
        for r_idx, cells in enumerate(t["rows"], start=1):
            for c_idx, cell_str in enumerate(cells, start=1):
                grid[(t["num"], r_idx, c_idx)] = cell_str
    return grid


def verify_cell_values(
    md_text: str,
    cells: List[Dict[str, Any]]
) -> Dict[str, Any]:
    """
    Performs deterministic cell-level value verification by matching LLM extracted values
    against the actual values present in the markdown table grid at (table_num, table_row, table_col).
    
    Returns verification summary and list of mismatched cells.
    """
    grid = extract_markdown_table_grid(md_text)
    annotated = []
    matched = 0
    mismatched = 0
    mismatches = []
    checked = 0

    for item in cells:
        cell_copy = dict(item)
        t_num = cell_copy.get("table_num") or 1
        r_num = cell_copy.get("table_row")
        c_num = cell_copy.get("table_col")
        ext_val = cell_copy.get("numeric_value")
        mult = cell_copy.get("multiplier") or 1.0

        if r_num is None or c_num is None:
            cell_copy["is_value_match"] = None
            cell_copy["mismatch_reason"] = "Missing table coordinates"
            annotated.append(cell_copy)
            continue

        cell_raw = grid.get((t_num, r_num, c_num))
        if cell_raw is None:
            cell_copy["is_value_match"] = False
            cell_copy["mismatch_reason"] = f"Cell coordinates [T{t_num}: R{r_num}, C{c_num}] out of bounds in markdown table"
            mismatched += 1
            checked += 1
            mismatches.append(cell_copy)
            annotated.append(cell_copy)
            continue

        actual_val = parse_numeric_cell_value(cell_raw)
        cell_copy["cell_raw_text"] = cell_raw
        cell_copy["actual_parsed_val"] = actual_val

        if actual_val is None or ext_val is None:
            cell_copy["is_value_match"] = None
            cell_copy["mismatch_reason"] = "Non-numeric cell or null value"
            annotated.append(cell_copy)
            continue

        checked += 1
        diff_direct = abs(float(ext_val) - float(actual_val))
        diff_scaled = abs(float(ext_val) - (float(actual_val) * float(mult)))
        tol = max(0.01, abs(float(actual_val)) * 0.0001)

        if diff_direct <= tol or diff_scaled <= tol:
            matched += 1
            cell_copy["is_value_match"] = True
            cell_copy["mismatch_reason"] = None
        else:
            mismatched += 1
            cell_copy["is_value_match"] = False
            reason = f"Extracted {float(ext_val):,} does not match cell text '{cell_raw}' (parsed: {float(actual_val):,})"
            cell_copy["mismatch_reason"] = reason
            mismatches.append(cell_copy)

        annotated.append(cell_copy)

    match_pct = round((matched / max(1, checked)) * 100, 1) if checked > 0 else 100.0
    return {
        "total_checked": checked,
        "matched": matched,
        "mismatched": mismatched,
        "match_pct": match_pct,
        "mismatches": mismatches,
        "annotated_cells": annotated
    }


def analyze_markdown_tables(md_text: str) -> Dict[str, Any]:
    """
    Parses OCR markdown to detect tables and count expected rows and value cells.
    Distinguishes description columns and note reference columns from actual numeric data cells.
    
    Returns:
    - total_data_rows: count of rows containing at least one numeric financial cell
    - total_value_cells: count of cells containing numeric values in data rows
    - tables: breakdown per markdown table
    """
    if not md_text or not md_text.strip():
        return {
            "total_data_rows": 0,
            "total_value_cells": 0,
            "tables": []
        }

    total_data_rows = 0
    total_value_cells = 0
    table_summaries = []

    for table in parse_markdown_tables(md_text):
        t_idx = table["num"]
        raw_header = table["header_lines"][0] if table["header_lines"] else []

        # Identify note/schedule and label columns by header name (any header line) to exclude them
        # from numeric counts
        note_col_indices = set()
        label_col_idx = None
        for header_cells in table["header_lines"]:
            for c_idx, col_name in enumerate(header_cells):
                c_clean = clean_cell_text(col_name).lower()
                if c_clean in LABEL_COLUMN_NAMES and label_col_idx is None:
                    label_col_idx = c_idx
                elif c_clean in NOTE_COLUMN_NAMES:
                    note_col_indices.add(c_idx)
        # Without a named label column, a table with a Note column has its labels in the first
        # non-note column. Either way, number-like labels ("50.1", "2024") are not values.
        if label_col_idx is None and note_col_indices:
            label_col_idx = 1 if 0 in note_col_indices else 0
        if label_col_idx is not None:
            note_col_indices.add(label_col_idx)

        data_rows = table["rows"]
        t_data_rows = 0
        t_value_cells = 0

        for cells in data_rows:
            if not cells:
                continue

            row_num_count = 0
            for c_idx, cell_str in enumerate(cells):
                # Skip identified note/schedule and label columns
                if c_idx in note_col_indices:
                    continue
                # No label column identified: skip column 0 if it's purely a textual line item description
                if c_idx == 0 and not is_numeric_value_cell(cell_str):
                    continue

                if is_numeric_value_cell(cell_str):
                    row_num_count += 1

            if row_num_count > 0:
                t_data_rows += 1
                t_value_cells += row_num_count

        total_data_rows += t_data_rows
        total_value_cells += t_value_cells
        table_summaries.append({
            "table_index": t_idx,
            "header": raw_header,
            "total_rows": len(data_rows),
            "data_rows": t_data_rows,
            "value_cells": t_value_cells
        })

    return {
        "total_data_rows": total_data_rows,
        "total_value_cells": total_value_cells,
        "tables": table_summaries
    }


def count_extracted_cells(extracted_items: List[Dict[str, Any]]) -> Tuple[int, int]:
    """
    Returns (item_count, observation_cell_count) from extracted JSON array.
    item_count = number of line items
    observation_cell_count = sum of data observations (years/values) across all items
    """
    if not extracted_items or not isinstance(extracted_items, list):
        return 0, 0
    item_count = len(extracted_items)
    cell_count = sum(len(item.get("data", [])) for item in extracted_items if isinstance(item, dict))
    return item_count, cell_count


def verify_extraction(
    md_content: str,
    extracted_items: Optional[List[Dict[str, Any]]] = None,
    tolerance_pct: float = 85.0,
    item_count: Optional[int] = None,
    cell_count: Optional[int] = None
) -> Dict[str, Any]:
    """
    Compares the count of extracted items and observation data points
    against the expected rows and value cells discovered in the OCR markdown.
    """
    analysis = analyze_markdown_tables(md_content)
    expected_rows = analysis["total_data_rows"]
    expected_cells = analysis["total_value_cells"]

    if item_count is not None and cell_count is not None:
        extracted_items_count = item_count
        extracted_cells_count = cell_count
    else:
        extracted_items_count, extracted_cells_count = count_extracted_cells(extracted_items or [])

    missing_cells = max(0, expected_cells - extracted_cells_count)
    missing_items = max(0, expected_rows - extracted_items_count)

    # 1. Page with no financial tables (text only, notes narrative, etc.)
    if expected_cells == 0:
        return {
            "is_success": True,
            "status": "NO_DATA_PAGE",
            "failure_type": None,
            "expected_rows": 0,
            "expected_cells": 0,
            "extracted_items": extracted_items_count,
            "extracted_cells": extracted_cells_count,
            "missing_cells": 0,
            "missing_items": 0,
            "completeness_pct": 100.0,
            "message": "Page contains text narrative or notes with no quantifiable financial table cells.",
            "table_count": len(analysis["tables"])
        }

    # 2. Catastrophic failure: Table exists with value cells, but LLM returned 0 items
    if extracted_items_count == 0 or extracted_cells_count == 0:
        return {
            "is_success": False,
            "status": "FAILED",
            "failure_type": "ZERO_EXTRACTION",
            "expected_rows": expected_rows,
            "expected_cells": expected_cells,
            "extracted_items": 0,
            "extracted_cells": 0,
            "missing_cells": expected_cells,
            "missing_items": expected_rows,
            "completeness_pct": 0.0,
            "message": (
                f"Extraction failed: Markdown table contains {expected_cells} value cells "
                f"across {expected_rows} rows, but extraction returned 0 items."
            ),
            "table_count": len(analysis["tables"])
        }

    # 3. Completeness ratio check
    completeness_pct = round((extracted_cells_count / expected_cells) * 100, 1)

    # In financial tables, slight variance (+/- 1-4 cells) can happen when footnote numbers
    # or duplicated section headers exist in markdown. We consider it successful if >= tolerance_pct.
    is_success = (completeness_pct >= tolerance_pct) or (abs(expected_cells - extracted_cells_count) <= 4)
    
    if is_success:
        status = "VERIFIED"
        failure_type = None
        message = (
            f"Extraction verified: {extracted_cells_count}/{expected_cells} cells captured "
            f"({completeness_pct}%) across {extracted_items_count} metrics."
        )
    else:
        status = "FAILED"
        failure_type = "INCOMPLETE_EXTRACTION"
        message = (
            f"Extraction failed: Only {extracted_cells_count}/{expected_cells} cells captured "
            f"({completeness_pct}%). Missing {missing_cells} cells across {expected_rows} expected rows."
        )

    # 4. Level-2 Verification: Numerical cell value audit
    value_ver = None
    if extracted_items and isinstance(extracted_items, list):
        data_cells = []
        for it in extracted_items:
            if not isinstance(it, dict):
                continue
            c_name = it.get("concept", {}).get("name") if isinstance(it.get("concept"), dict) else it.get("concept_name")
            rf = it.get("raw_field")
            for d in it.get("data", []):
                if isinstance(d, dict):
                    data_cells.append({
                        "concept_name": c_name,
                        "raw_field": rf,
                        "table_num": d.get("table_num") or 1,
                        "table_row": d.get("table_row"),
                        "table_col": d.get("table_col"),
                        "numeric_value": d.get("numeric_value"),
                        "year": d.get("year"),
                        "multiplier": d.get("multiplier", 1.0)
                    })
        if data_cells:
            value_ver = verify_cell_values(md_content, data_cells)

    return {
        "is_success": is_success,
        "status": status,
        "failure_type": failure_type,
        "expected_rows": expected_rows,
        "expected_cells": expected_cells,
        "extracted_items": extracted_items_count,
        "extracted_cells": extracted_cells_count,
        "missing_cells": missing_cells,
        "missing_items": missing_items,
        "completeness_pct": completeness_pct,
        "message": message,
        "table_count": len(analysis["tables"]),
        "value_verification": value_ver
    }


def audit_page_in_db(
    conn,
    instrument_code: str,
    fiscal_year: str,
    page_number: int
) -> Dict[str, Any]:
    """Audits a single stored page against its metrics in the database."""
    cursor = conn.cursor()
    cursor.execute("""
        SELECT ocr_markdown FROM pages
        WHERE instrument_code = ? AND fiscal_year = ? AND page_number = ?
    """, (instrument_code, fiscal_year, page_number))
    page_row = cursor.fetchone()
    if not page_row or not page_row[0]:
        return {"error": "Page or markdown not found"}

    md_content = page_row[0]

    # Query metrics and data points for this page
    cursor.execute("""
        SELECT m.id, d.numeric_value, d.year
        FROM financial_metrics m
        LEFT JOIN financial_metric_data d ON d.metric_id = m.id
        WHERE m.instrument_code = ? AND m.fiscal_year = ? AND m.page_number = ?
    """, (instrument_code, fiscal_year, page_number))
    metric_rows = cursor.fetchall()

    # Reconstruct item list structure
    items_map = {}
    for r in metric_rows:
        m_id = r[0]
        if m_id not in items_map:
            items_map[m_id] = {"id": m_id, "data": []}
        if r[1] is not None:
            items_map[m_id]["data"].append({"numeric_value": r[1], "year": r[2]})

    return verify_extraction(md_content, list(items_map.values()))


def backfill_all_pages_verification(db_path: str = "data/financial_ocr.db") -> List[Dict[str, Any]]:
    """
    Backfills expected_cells, extracted_cells, missing_cells, completeness_pct,
    and verification_status into SQLite for all existing pages.
    """
    import sqlite3
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    # Schema natively defines expected_cells, extracted_cells, missing_cells, completeness_pct, verification_status


    cursor.execute("""
        SELECT id, instrument_code, fiscal_year, page_number, ocr_markdown
        FROM pages
        WHERE ocr_markdown IS NOT NULL AND length(ocr_markdown) > 0
        ORDER BY fiscal_year ASC, page_number ASC
    """)
    pages = cursor.fetchall()
    results = []

    for p in pages:
        p_id = p["id"]
        code = p["instrument_code"]
        fy = p["fiscal_year"]
        num = p["page_number"]
        md = p["ocr_markdown"]

        # Get extracted data count
        cursor.execute("""
            SELECT COUNT(DISTINCT m.id) as item_cnt, COUNT(d.id) as cell_cnt
            FROM financial_metrics m
            LEFT JOIN financial_metric_data d ON d.metric_id = m.id
            WHERE m.instrument_code = ? AND m.fiscal_year = ? AND m.page_number = ?
        """, (code, fy, num))
        stats = cursor.fetchone()
        item_cnt = stats["item_cnt"] or 0
        cell_cnt = stats["cell_cnt"] or 0

        audit = verify_extraction(md, item_count=item_cnt, cell_count=cell_cnt)

        cursor.execute("""
            UPDATE pages
            SET expected_cells = ?,
                extracted_cells = ?,
                missing_cells = ?,
                completeness_pct = ?,
                verification_status = ?
            WHERE id = ?
        """, (
            audit["expected_cells"],
            audit["extracted_cells"],
            audit["missing_cells"],
            audit["completeness_pct"],
            audit["status"],
            p_id
        ))

        results.append({
            "instrument_code": code,
            "fiscal_year": fy,
            "page_number": num,
            "expected_cells": audit["expected_cells"],
            "extracted_cells": audit["extracted_cells"],
            "missing_cells": audit["missing_cells"],
            "completeness_pct": audit["completeness_pct"],
            "status": audit["status"],
            "is_success": audit["is_success"]
        })

def audit_company_extractions(
    instrument_code: str,
    fiscal_year: Optional[str] = None,
    db_path: str = "data/financial_ocr.db"
) -> Dict[str, Any]:
    """
    Deterministically audits and returns verification results for all pages of an instrument.
    """
    import sqlite3
    code = instrument_code.upper().strip()
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    query = """
        SELECT p.id, p.instrument_code, p.fiscal_year, p.page_number, p.title, p.category,
               p.ocr_markdown, p.extraction_status, p.expected_cells, p.extracted_cells,
               p.missing_cells, p.completeness_pct, p.verification_status,
               COUNT(DISTINCT m.id) as metric_count, COUNT(d.id) as live_cell_count
        FROM pages p
        LEFT JOIN financial_metrics m ON m.fiscal_year = p.fiscal_year AND m.page_number = p.page_number AND m.instrument_code = p.instrument_code
        LEFT JOIN financial_metric_data d ON d.metric_id = m.id
        WHERE p.instrument_code = ?
    """
    params = [code]
    if fiscal_year:
        query += " AND p.fiscal_year = ?"
        params.append(fiscal_year)
    query += " GROUP BY p.id ORDER BY p.fiscal_year ASC, p.page_number ASC"

    cursor.execute(query, params)
    rows = cursor.fetchall()

    pages_audit = []
    total_expected = 0
    total_extracted = 0
    total_missing = 0
    verified_cnt = 0
    failed_cnt = 0

    for r in rows:
        md = r["ocr_markdown"] or ""
        cell_cnt = r["live_cell_count"] or 0
        item_cnt = r["metric_count"] or 0

        audit = verify_extraction(md, item_count=item_cnt, cell_count=cell_cnt)

        total_expected += audit["expected_cells"]
        total_extracted += audit["extracted_cells"]
        total_missing += audit["missing_cells"]
        if audit["status"] in ("VERIFIED", "NO_DATA_PAGE"):
            verified_cnt += 1
        else:
            failed_cnt += 1

        pages_audit.append({
            "page_number": r["page_number"],
            "fiscal_year": r["fiscal_year"],
            "title": r["title"] or f"Page {r['page_number']}",
            "category": r["category"] or "Financial Statement",
            "is_success": audit["is_success"],
            "status": audit["status"],
            "failure_type": audit["failure_type"],
            "expected_cells": audit["expected_cells"],
            "extracted_cells": audit["extracted_cells"],
            "missing_cells": audit["missing_cells"],
            "completeness_pct": audit["completeness_pct"],
            "message": audit["message"]
        })

    conn.close()

    return {
        "instrument_code": code,
        "fiscal_year_filter": fiscal_year,
        "total_pages": len(pages_audit),
        "verified_pages": verified_cnt,
        "failed_pages": failed_cnt,
        "is_all_verified": (failed_cnt == 0 and len(pages_audit) > 0),
        "total_expected_cells": total_expected,
        "total_extracted_cells": total_extracted,
        "total_missing_cells": total_missing,
        "overall_completeness_pct": round((total_extracted / total_expected * 100), 1) if total_expected > 0 else 100.0,
        "pages": pages_audit
    }


if __name__ == "__main__":
    # Run as: python -m app.ocr.services.extraction_verifier
    from ..config import DB_PATH
    db_path = str(DB_PATH)

    print(f"Running Extraction Verifier on {db_path}...")
    audits = backfill_all_pages_verification(db_path)
    print(f"\n--- AUDIT SUMMARY ({len(audits)} Pages) ---")
    verified_count = sum(1 for a in audits if a["status"] == "VERIFIED")
    failed_count = sum(1 for a in audits if a["status"] == "FAILED")
    print(f"Verified Pages: {verified_count}/{len(audits)}")
    print(f"Failed Pages:   {failed_count}/{len(audits)}")

    print(f"\n{'FY':<10} {'Page':<6} {'MD Cells':<10} {'Ext Cells':<10} {'Missing':<10} {'Match %':<10} {'Status':<10}")
    print("-" * 72)
    for a in audits:
        flag = "[OK]  " if a["status"] == "VERIFIED" else "[FAIL]"
        print(f"{a['fiscal_year']:<10} P-{a['page_number']:<4} {a['expected_cells']:<10} {a['extracted_cells']:<10} {a['missing_cells']:<10} {a['completeness_pct']:<9}% {flag} {a['status']}")
