import time
import json
import logging
import re
from typing import List, Dict, Any, Optional
from google import genai

from ..config import DEFAULT_MODEL, gemini_api_key
from ..rate_limiter import execute_with_retry
from .csv_service import extract_year_int
from .extraction_verifier import parse_markdown_tables, parse_numeric_cell_value
from .gemini_json import generate_json, response_text, usage_tokens

logger = logging.getLogger("ocr.extraction_service")

# ---------------------------------------------------------------------------
# Compact output codes. The model answers with short codes; decode_compact_extraction()
# expands them into the grouped item shape stored by database.save_extracted_metrics.
# ---------------------------------------------------------------------------
STATEMENT_CODES = {
    "B": "BALANCE_SHEET", "I": "INCOME_STATEMENT", "C": "CASH_FLOW", "E": "CHANGES_IN_EQUITY",
    "N": "NOTES_AND_FOOTNOTES", "H": "FINANCIAL_HIGHLIGHTS", "U": "UNKNOWN",
}
SCOPE_CODES = {"C": "CONSOLIDATED", "S": "STANDALONE"}
LEVEL_CODES = {"L": "LINE_ITEM", "S": "SUBTOTAL", "G": "GRAND_TOTAL"}
TEMPORAL_CODES = {"F": "PERIOD_FLOW", "P": "POINT_IN_TIME", "O": "OPENING_BALANCE", "C": "CLOSING_BALANCE"}
ACTION_CODES = {"ID": "INCREASE_DECREASE", "CI": "CASH_INFLOW", "CO": "CASH_OUTFLOW", "PR": "PROCEEDS", "PA": "PAYMENTS"}
METRIC_TYPE_CODES = {"C": "CURRENCY", "S": "SHARES", "R": "PER_SHARE_RATIO", "P": "PERCENTAGE", "N": "COUNT"}
QUALIFIER_CODES = {"C": "CURRENT", "N": "NON_CURRENT", "D": "DOMESTIC", "F": "FOREIGN", "S": "SECURED", "U": "UNSECURED"}
TAG_CODES = {"F": "FINANCIAL_METRIC", "O": "OPERATIONAL_METRIC", "U": "UNKNOWN"}

# System instruction: the accounting rules are unchanged; the output is a compact table-oriented code format.
EXTRACTION_SYSTEM_INSTRUCTION = """You are an expert financial analyst and data extraction specialist.
Your task is to classify every financial metric row in the provided Markdown page of an annual report.
The tables are pre-annotated: each body row starts with `[Table X: Row Y]` and the header marks columns as `[Col C]`.
You do NOT copy labels, note numbers or values: the system reads them from the table cells at the coordinates you give.
Your output only says WHICH rows are metrics, WHICH columns hold which year, and HOW each row is classified.

### OUTPUT FORMAT (JSON following the response schema; keep it short):
{"t":[TABLE,...]}
If the page has no extractable financial figures or metric tables, return {"t":[]}.

TABLE object (one per annotated table that holds metric values; skip tables without metrics):
- "n": table number X from `[Table X: Row Y]`.
- "s": statement code: B=BALANCE_SHEET, I=INCOME_STATEMENT, C=CASH_FLOW, E=CHANGES_IN_EQUITY, N=NOTES_AND_FOOTNOTES, H=FINANCIAL_HIGHLIGHTS, U=UNKNOWN (see rule 6).
- "st": REQUIRED evidence for "s": copy VERBATIM (exact words as printed) the statement title or note heading this table belongs to,
  e.g. "Consolidated Statement of Financial Position", "Statement of Cash Flows", "Increase/(decrease) of other liabilities".
  On a continuation page without a printed title, copy the title from the REPORT CONTEXT list of this report's page titles.
- "sc": scope code: C=CONSOLIDATED (group: the company together with its subsidiaries), S=STANDALONE (the company alone) (see rule 7).
- "sh": REQUIRED evidence for "sc": copy VERBATIM the printed heading, sub-heading or column header that shows the scope,
  e.g. "Consolidated Statement of Financial Position", "Increase/(decrease) of other liabilities - The City Bank Limited", "Consolidated 2024".
  If the report has no consolidated statements, copy the page or table title.
- "u": unit for the table: currency code (e.g. "BDT", "USD") or "%"; omit if none.
- "m": scale multiplier from the table heading, ONLY one of 1 (normally), 1000 ("in thousands"), 100000 ("in lakh"),
  1000000 ("in millions"), 10000000 ("in crore"); never another number.
- "lc": column number C of the line item description (Particulars) column.
- "nc": column number C of the Note column, or 0 if there is none.
- "y": value columns, one object per column holding financial values, e.g. [{"c":3,"y":2024},{"c":4,"y":2023}]:
  "c" = column number C, "y" = REQUIRED on every column: the calendar year in which the column's period ends
  (for a balance sheet, the year of the "as at" date). Add "d" with that end date whenever a date is printed, e.g.
  "30 June 2023" or "30.06.2023" -> "y":2023,"d":"2023-06-30"; "31 December 2014" -> "y":2014,"d":"2014-12-31";
  "July 01, 2020 to June 30, 2021" -> "y":2021,"d":"2021-06-30"; "2023-24" or "FY 2023-24" -> "y":2024; "2024" -> "y":2024.
  Add keys only when the column differs from the table defaults: "sc" scope, "e" entity, "g" segment,
  "u" unit, "m" multiplier. Never list the Particulars or Note columns, never list a column twice.
  Columns that print the SAME year are different cells: give each the key that tells them apart, taken from its
  header ("g" for a segment such as "Conventional" / "Islamic" / "Offshore", "e" for an entity, "sc" for a scope);
  a "Total" column of those parts keeps no "g" / "e". List each printed row ONCE in "r": when the columns are the
  segments, never repeat a row per segment and never put the segment in the row's name.
  RESTATED COLUMNS are not segments: a column whose header says "Re-Stated" / "Restated" (for example "1st July 2022
  (Re-Stated)" printed next to "30 June 2022") is the restated figure of that same year. Give it the same "y" as the column
  it restates, its own printed date in "d" ("1st July 2022" -> "d":"2022-07-01"; the plain column keeps "d":"2022-06-30"),
  and "rs":1. Never give a restated column "g", "e" or "sc" to tell it apart, and never put a date, a year or "Re-Stated"
  in a row's name: the row is the same line, printed twice for that year.
- "tp": optional table default temporal code (see below) when every row shares it.
- "sec": the category of every metric row, as row ranges, e.g. [{"a":1,"b":8,"t":"Current Assets"},{"a":9,"b":15,"t":"Non-Current Assets"}]
  ("a" first row, "b" last row, "t" the section header or parent grouping, e.g. "Current Assets", "Operating Expenses",
  "Cash Flows from Operating Activities", "Revenue", "Equity"). REQUIRED: every row listed in "r" must fall inside one range.
  For rows under no printed heading, use the parent grouping they belong to (e.g. "Operating Income", "Profit Before Tax", "Appropriations").
  A range starts AT a printed section header row, including when that header row prints its own total (a bold row such as
  "Current Assets" or "Long-term Liabilities" with an amount): the header row is the FIRST row of its range and its printed name is the
  range's "t". Never fold such a row into the previous section just because it looks like a subtotal: a row that names a section and
  prints a total opens the section it names, and the rows below it belong to that section until the next printed section header.
  Give every section its own range: on a balance sheet one range each for non-current assets, current assets, equity, non-current
  liabilities and current liabilities (whatever the page calls them). EVERY row stays inside a range, including the grand-total rows
  (total assets, total equity and liabilities) and any line printed after them (for example a per-share line): such a row takes the range of
  the section it closes or follows, or its own range named by its printed label, but it is never left without a range.
  "t" is a printed section name, never the statement's grand-total label.
  Statements often print two levels of headings: a side or title heading (such as "ASSETS" or "EQUITY AND LIABILITIES") and, below it,
  the section headers. The range of a row is named after the NEAREST printed section header above it, never the side heading; a
  heading without amounts above its section headers is only a title and opens no range of its own. Before answering, list the printed
  section header rows of the table: there is exactly one range per such row, each ending where the next header row begins, so no range
  covers the rows of another section.
  Add "sc" to a range when its printed section heading shows a different scope than the table (rule 7), e.g.
  {"a":1,"b":6,"t":"Increase/(decrease) of other liabilities - The City Bank Limited","sc":"S"}.
- "k": REQUIRED breakdowns: which printed cells of this table add up to a total ([] only when the table has no total).
  One object per total, subtotal, net or formula row/column whose components the table prints:
  {"d":"R","t":9,"p":[7,8]} = row 9 is the sum of rows 7 and 8 in every value column
    (e.g. "Total balance with other banks" = "In Bangladesh" + "Outside Bangladesh");
  {"d":"C","t":6,"p":[3,4,5]} = column 6 is the sum of columns 3, 4 and 5 in each row
    (e.g. a "Total" column after "Conventional", "Islamic", "Offshore" columns); add "r":[rows] when only those rows add up.
    For "C", "t" and "p" are COLUMN numbers from `[Col C]`, never row numbers, and ONE object covers every row:
    never repeat it per row. A breakdown never replaces "y": the part columns (e.g. each segment) are value columns
    too and MUST all be listed in "y" with their "g" / "e", next to the total column.
  "n": [the parts that are deducted]. DECIDE "n" WITH THIS PROCEDURE, in this order, for every total (do the arithmetic; do not guess):
    STEP 1. ADD all the parts exactly as printed, once for EACH value column. A bracketed or minus-signed figure is a negative
        number and is simply added: "(104)" adds -104. If the sum equals the printed total in EVERY value column, "n" is
        EMPTY: write no "n" and stop. Most totals end here, including every total with a bracketed or sign-changing part.
    STEP 2. Only if STEP 1 fails in some column: test whether SUBTRACTING the printed size of ONE part makes the total
        right in EVERY value column. That part may go in "n" only if it is printed with NO brackets and NO minus sign in
        EVERY value column (the usual "Less: ..." line). One bracketed or negative cell in ANY column of that row (check
        the SECOND value column too, not only the first) means it is NOT in "n". If no such part fixes every column,
        leave "n" empty: never put a part in "n" to make one column fit.
    STEP 3. The label never decides. "Provision", "Write-down", "Impairment", "Diminution", "Allowance", "Loss",
        "Payment", "Decrease", "(income)/expense" on a line that STEP 1 shows is a plain part stay plain parts.
    A NEGATIVE number is never a subtraction sign. Do NOT put a row in "n" because it is bracketed, negative, an outflow,
    a payment, a decrease or an "(income)/expense": such a row is still a part, in "p", just not in "n".
    Example (STEP 1 ends it): a total row of 6,228 (second column) and 5 part rows printed
      row1 (25,622) / (7,932); row2 (104) / 553; row3 6,290 / 10,359; row4 14,127 / 5,414; row5 (1,566) / (2,165)
      with totals (6,875) / 6,228: every row is a plain part, "n" is empty: -7,932+553+10,359+5,414-2,165 = 6,228.
    A part goes in "n" only when STEP 1 failed and it is printed WITHOUT brackets in every value column (e.g. "Less:
    Depreciation" printed 300; "Interest paid" printed 600 under "Net interest income" = interest income - interest
    paid). Cash outflows in a cash-flow statement, "(income)/expense" lines, losses and any line whose sign changes
    between years are plain parts. Examples:
      receipts 900, payments (400), taxes paid (100), net cash 400 -> {"p":[receipts, payments, taxes paid]} (no "n");
      interest income 1,000, interest paid 600, net interest income 400 -> {"p":[income, paid],"n":[paid]};
      current tax 500, deferred tax (income)/expense (20) one year and 30 the other, total tax 480 / 530
        -> {"p":[current, deferred]} (no "n").
      a line whose label sounds like a reduction ("Provision for diminution in value", "Write-down", "Impairment",
        "Allowance") is still decided ONLY by the printed numbers (a real bank's "Total provision" page):
          Provision for loans and advances      1,200 /   900
          Provision for off-balance sheet items   (40) /    60
          Provision for diminution in value of investments  (350) / 1,480
          Other provisions                         90 /    40
          Total provision                         900 / 2,480
        STEP 1, column 1: 1,200 - 40 - 350 + 90 = 900; column 2: 900 + 60 + 1,480 + 40 = 2,480. Both columns equal the printed
        total, so "n" is EMPTY: {"p":[loans, off-balance, diminution, other]}. The diminution line is bracketed in one
        year and positive in the other, and its label sounds like a deduction, but neither decides: putting it in "n"
        would give 1,200-40+350+90 = 1,600 and 900+60-1,480+40 = -480: wrong in both columns.
        Test every "n" you write: STEP 1 must have failed, and the part must be printed WITHOUT brackets in EVERY value
        column.
  CALCULATION-STYLE STATEMENTS (running totals): a row that starts with "Less:" / "Add:" and prints an amount can have indented rows
    below it. Such a header row has TWO roles and needs TWO breakdowns, each its own object:
    (a) it is the total of the rows below it: {"d":"R","t":header,"p":[the indented rows]};
    (b) the next running total (the next "Gross profit", "Operating profit", ... row) is the previous running total plus or minus
        the header: {"d":"R","t":next total,"p":[previous running total, header],"n":[header]} for "Less:" (the header is printed
        without brackets, so it goes in "n"), the same without "n" for "Add:".
    Never put the header's own parts into the running total (they are already inside the header), never use the running total as a
    part of itself, and a "Less:" row with NO indented rows is simply a deducted part of the next total.
    Example: Revenue 100 / "Less: Costs" 60 / indented A 40 / indented B 20 / Gross profit 40
      -> {"t":Costs,"p":[A,B]} and {"t":Gross profit,"p":[Revenue,Costs],"n":[Costs]}  (100 - 60 = 40).
  "o": the operation, only when the total is a DIVISION, PERCENTAGE or PRODUCT (never for "+" or "-": a subtraction
    such as "C = A-B" or "E = C-D" is a plain sum with the subtracted part in "n" and NO "o"):
    A label with letters and "-" or "+" such as "(C = A-B)", "(E = C-D)", "(G = E-F)", "(A+B)" NEVER takes "o": it is a
    plain sum (the part after "-" goes in "n" when printed without brackets, otherwise nothing). "o" is ONLY for a
    label that prints a division "(i/ii)", "A/B", "per share", "ratio" or "%", or a product "(A x B)".
    "/" = the total is the first part divided by the second
    (e.g. "Earnings per share (i/ii)" = {"d":"R","t":9,"p":[7,8],"o":"/"} with row 7 profit and row 8 number of shares);
    "%" = the first part divided by the second, times 100 (a percentage); "*" = the parts multiplied. For "/" and "%"
    list exactly two parts, numerator first.
  HOW TO FIND THEM (follow every step):
  1. Every row you label "S" or "G" needs a "k" entry, and so does every ratio row computed from rows printed in the
     same table (e.g. earnings per share "(i/ii)" from "(i) Net profit" and "(ii) Number of shares": "o":"/"), unless the
     table does not print its components
     (e.g. an opening balance). Rows whose label prints a formula ALWAYS need one: "(C = A-B)", "(i+ii)", "(A+B+C)",
     "(D+E+F)", "(i/ii)". The letters and roman numerals name the rows printed with those labels, e.g. in
     "Profit before provision (C = A-B)" A is the row labelled "(A)" and B the row labelled "(B)". Every part written
     after a "-" in the formula goes in "n" when it is printed without brackets: "C = A-B" with B printed 14,644 is
     {"p":[A row, B row],"n":[B row]}.
     FORMULAS WITH LETTERS OR NUMERALS "(A+B+C)", "(i+ii)", "(D+E+F)": count the letters in the formula; the breakdown has
     EXACTLY that many parts, one per letter, in the same order. A letter or numeral names the row that is
     labelled with it ("(i)" and "(ii)" are rows whose label ends in "(i)" / "(ii)"), and when the row labelled "A)"
     is a section heading with NO figures, the letter stands for that section's own net or total row (the last
     total row printed before the next lettered heading). Never leave a letter out because its heading row is empty.
     Example (every figure is a made-up generic one):
       | A) Sales activities                 |            |   <- heading, no figures
       | Receipts from customers             | 900        |
       | Payments to suppliers               | (400)      |
       | Net cash from sales activities      | 500        |   <- this row is "A"
       | B) Asset activities                 |            |   <- heading, no figures
       | Purchase of assets                  | (200)      |
       | Net cash from asset activities      | (200)      |   <- this row is "B"
       | C) Net change in cash (A+B)         | 300        |   -> {"p":["Net cash from sales activities","Net cash from asset activities"]}: 2 letters, 2 parts, 500-200 = 300
       | Total of two parts (i+ii) where (i) is the row labelled "... (i)" and (ii) the row labelled "... (ii)": take those two rows.
     A subtotal line printed without a letter between the sections is NOT one of the letters.
  2. A row with an empty label but a bold or summed value directly under a group is a total too: find the rows it adds.
  3. The parts of a subtotal are the rows printed directly above it, back to the previous subtotal or heading, and
     NEVER that previous subtotal itself. A total such as "Total operating income" adds only its own items, not the
     "Net interest income" subtotal printed above them; a later row then adds the two subtotals. When a figure is
     split between owners and non-controlling interest, a later line that continues from the owners' share
     (e.g. retained surplus) uses the owners' row, not the total.
  4. List the DIRECT parts only: a grand total lists its subtotals, not the components of those subtotals.
  5. Before answering, add the printed numbers of the parts (subtracting the "n" parts) in EVERY value column and
     check they equal the printed total in that column. If one column does not add up, the parts or the "n" list are
     wrong: re-read the rows and fix them; never output a breakdown you have not checked.
  Row and column numbers are the same `[Table X: Row Y]` / `[Col C]` integers as everywhere else.
- "r": metric rows, one object per row: {"r":row,"c":"name","l":"level"} plus optional extras keys in the same object,
  e.g. {"r":2,"c":"Net Income","l":"L","e":"Parent Bank"}:
  * "r": row number Y from `[Table X: Row Y]`. Include every row that has at least one financial value; exclude pure heading rows.
  * "c": the concept name, a pure clean canonical accounting noun in Standard Title Case (e.g. "Revenue",
    "Intangible Assets", "Cash and Cash Equivalents"). NEVER include note numbers, note prefixes, or section numbering.
    Write "=" ONLY when the printed label alone identifies the line and has clean title casing.
    NEVER write "=" for a generic label ("Others", "Other", "Total", "Subtotal", "Miscellaneous", "In Bangladesh",
    a holder or a place): rule 8 requires the clean parent heading plus that label, so write that full name in "c".
  * "l": level: L=LINE_ITEM (individual component), S=SUBTOTAL (intermediate sum, e.g. "Total Operating Expenses"),
    G=GRAND_TOTAL (master total, e.g. "Total Assets", "Total Equity", "Net Income").
  * extras: include ONLY keys that differ from the defaults (omit every key whose value is the default):
    - "tp" temporal: F=PERIOD_FLOW, P=POINT_IN_TIME, O=OPENING_BALANCE, C=CLOSING_BALANCE.
      Default: P for balance sheet tables, F otherwise. Use O/C for beginning/end of period balances.
    - "a" action: ID=INCREASE_DECREASE (cash flow operating reconciliation movements), CI=CASH_INFLOW, CO=CASH_OUTFLOW, PR=PROCEEDS, PA=PAYMENTS. Default none.
    - "mt" metric type: C=CURRENCY, S=SHARES, R=PER_SHARE_RATIO, P=PERCENTAGE, N=COUNT. Default C.
    - "q" qualifier: C=CURRENT, N=NON_CURRENT, D=DOMESTIC, F=FOREIGN, S=SECURED, U=UNSECURED. Default none.
    - "tg" tag: F=FINANCIAL_METRIC, O=OPERATIONAL_METRIC, U=UNKNOWN. Default F.
    - "e" entity: named subsidiary or legal entity of this row when the table breaks down by entity. Default none (parent/group).
    - "g" segment: operating segment or division of this row (e.g. "Retail"). Default none.
    - "u" / "m": unit / multiplier of this row when it differs from the table (e.g. "u":"BDT" with "mt":"R" for EPS).
    - "s" / "sc": statement / scope code of this row when it differs from the table.
    - "f": discrepancy reason (see rule 3). Default none.
    - "x": 1 when this page does not show what the line is (rule 9). Default none.
    - "cf": integer 0-100, REQUIRED on every row whose name you built from a parent heading (rule 8): how sure you are
      that the heading you used is really the title of the table this row belongs to (rule 9). Omit it on rows named
      by their own printed label.

### RULES
1. CONCEPT NAMES: "name" must be a clean canonical accounting noun in STANDARD TITLE CASE (e.g. "Income Tax Expenses",
   "Finance Cost", "Other Income", "Cash and Cash Equivalents").
   - NEVER include note numbers, note prefixes, or section numbering (e.g. "32.", "32", "Note 32", "34.01", "46.a", "[32]").
     Strip any note prefix from headings before using them in concept names.
   - Do NOT include verbs ("Increase in"), aggregation words ("Total", except as rule 8 requires), legal company names,
     segment tags, formula references ("(A-B)", "[i/ii]"), footnote marks or bullet symbols.
   - When the printed label contains any of these or has non-standard casing, write the clean Standard Title Case name instead of "=".
   - An operator word printed before a label ("Less:", "Add:", "Deduct:", "Plus:", "(-)", "(+)") is a calculation step, never part
     of the name: "Less: Operating Expenses" is named "Operating Expenses". Copy the value with the sign it is PRINTED with; the
     operator only matters for the breakdown "k", never for the value.
2. COORDINATES: row numbers and column numbers MUST be the exact integers from `[Table X: Row Y]` and `[Col C]`.
   `[Table X: Row Y]` is not a column. Never guess or invent numbers.
3. DISCREPANCY FLAGGING: when a printed row label is a typo, misprint or copy-paste error that contradicts its
   parent section heading or the constituent math, put the true underlying concept in "name" and add "f" with a
   concise explanation for human review (e.g. "Printed row label contradicts parent section header; concept inferred from section context").
4. SCOPE AND ENTITIES: when columns are split by scope (Consolidated vs Standalone), entity or segment, express
   that with the column keys in "y". When rows are split by entity or segment, use the row extras "e" / "g".
   In a table that breaks a group down by subsidiary, the group total row is CONSOLIDATED.
5. Output only the JSON object, with no default-valued keys.
6. STATEMENT TYPE: B, I, C and E are ONLY for the primary statements themselves, i.e. tables under a title such as
   "Statement of Financial Position" / "Balance Sheet", "Statement of Profit or Loss (and Other Comprehensive Income)" /
   "Income Statement" / "Profit and Loss Account", "Statement of Cash Flows" / "Cash Flow Statement",
   "Statement of Changes in Equity". Everything else is N: notes, schedules, annexures, and notes or reconciliations
   that support a statement, e.g. "Increase/(decrease) of other assets", "Increase/(decrease) of other liabilities",
   "Reconciliation of net profit with cash flows from operating activities", "Earnings per share". Use H only for
   multi-year highlight or summary tables. A table never changes type because its rows resemble cash-flow items.
7. SCOPE: never guess and never default. Use C only when the printed heading, sub-heading or column header says
   "Consolidated" (or "Group"). Use S when it names the company itself (its own legal name, "Bank only", "Company",
   "Separate", "Standalone"), and ALWAYS use S when the REPORT CONTEXT says the report has no consolidated statements.
   A heading such as "... - <company's own name>" means the company alone (S), even inside a consolidated report.
   When the REPORT CONTEXT lists how the previous report labelled the same headings, keep the same labels unless
   this page's printed heading says otherwise. The page's first line "Source: <company> (<code>) | FY | Page" is added by
   the system: it is not printed on the page, so never quote it in "st" or "sh" and never use it to decide the scope.
   SCOPE IS DECIDED PER TABLE, from that table's own title:
   - A page can hold several statements. Decide "sc" and "sh" for each table separately; never copy the scope of the
     previous table or of the first title on the page.
   - A table's title can be a Markdown heading, a bold or plain line, a caption or the table's own header row, and it
     usually spans several lines (statement name, entity name, date line). Take the closest title above the table that
     belongs to it.
   - Text between two tables that names a different statement (for example a balance sheet after a cash flow
     statement, or notes after a primary statement) or a different entity is a NEW title: read its scope from its own
     words only.
   - A title that names only the company itself and never says Consolidated / Group means S, even when an earlier
     title on the same page said Consolidated.
   - Only a table with no title of its own (a continuation of the table above) keeps the scope of the table above.
   - "sh" quotes that table's own title, never an earlier one.
8. GENERIC ROW LABELS: when a row's printed label cannot identify the line on its own, i.e. a bare "Total" /
   "Subtotal" / "Others", a place or kind ("In Bangladesh", "Outside Bangladesh", "Domestic", "Foreign"), or a holder
   ("Equity holders of the bank", "Non-controlling interest"), its concept name is the parent heading it sits under
   plus that label, ALWAYS in Standard Title Case and with any note number stripped.
   - STRIP NOTE NUMBERS: when using a parent heading (e.g. "### 32. Income Tax Expenses" or "Note 30 Finance Cost"), ALWAYS strip the note number
     ("Income Tax Expenses", "Finance Cost"). NEVER output "32. Income Tax Expenses total" or "Note 30 Finance Cost total".
   - PARENT'S OWN TOTAL: the master/grand total row of a note or section takes the clean parent heading ITSELF (e.g. "Income Tax Expenses", "Finance Cost"),
     NOT the heading plus "total". Only when a section has multiple different generic sub-totals (e.g. "Operating Subtotal" vs "Non-Operating Subtotal")
     or "Others" should qualifying words be used, always in Standard Title Case.
   - Examples: a "Total" row under "Net profit after tax attributable to:" is "Net Profit after Tax Attributable to Total";
     an "In Bangladesh" row under "Balance with other banks and financial institutions" is "Balance with Other Banks and Financial Institutions in Bangladesh".
   Write such a name the same way for the same row in every report.
   A table can hold several generic rows under ONE heading, e.g. under "Increase/(decrease) of other items":
       | Item A | ... |   <- named from its own label
       | Item B | ... |
       | Others | ... |   <- "Increase / (Decrease) of Other Items Others"   (the heading + "Others")
       | Total  | ... |   <- "Increase / (Decrease) of Other Items Total"    (the heading + "Total")
   "Others" and "Total" are two different lines and MUST get two different names, each carrying its own word
   ("Others", "Total"). Never give "Others" the heading's name without "Others", and never reuse a name
   already given to another row of the same table. The same applies to "Subtotal", "Other", "Miscellaneous",
   "In Bangladesh" and every other generic label.
   - ROWS BROKEN DOWN BY SUBSIDIARY: when the rows of a note are the group's entities (company names under a
     heading such as "Increase/(decrease) of other assets"), put the entity in "e" and name the concept after the
     heading alone; never write "<entity> <heading>" as the concept name. The entity belongs in "e", so the same row
     is named the same way in every report.
9. UNIDENTIFIABLE LINES: a table can start on this page without its title or parent heading because they were printed
   on the previous page (a table that continues across pages). Take the heading ONLY from what is printed above that
   table on THIS page. NEVER borrow the heading or title of the next table, of another table, or of the REPORT CONTEXT
   to name its rows, and never guess it. A row whose printed label identifies the line by itself keeps its normal
   concept name. A row whose label does not (a bare "Total", "Subtotal", "Others", "In Bangladesh", a holder or a
   place, rule 8) cannot be named without that heading: write its printed label as "c" and add "x":1. The system keeps
   the figures of such a row but leaves the row out of the tables and marks it for a person to check; this is the
   correct answer when the page does not tell you the line. For the "sec" range of rows under no printed heading on
   this page use "t":"" (empty); never put another table's heading there.
   A heading belongs to the table printed BELOW it, never to the table printed above it. Example of a page:
       | Item A | 10 |            <- table 1: starts at the top, nothing printed above it
       | Total  | 10 |
       ### Note 7 Other receivables   <- the title of table 2, NOT of table 1
       | Item B | 4 |
       | Total  | 4 |
   Table 1 is the end of a table whose title was on the previous page: "Item A" keeps its own name, "Total" is
   marked "x":1. Table 2's "Total" is named from "Note 7 Other receivables". Never give both totals that name.
   CONFIDENCE ("cf", rule 8 rows): 90-100 the heading is printed directly above this table on this page;
   50-89 you had to infer it (e.g. the heading is far from the table or could belong to the neighbouring table);
   below 50 you are guessing. When unsure, give a low "cf" rather than a high one: the system leaves rows with a
   low "cf" out of the tables and marks them for a person to check.
"""


def _enum(codes: Dict[str, str]) -> Dict[str, Any]:
    return {"type": "STRING", "enum": list(codes)}


_EXTRAS_PROPS = {
    "tp": _enum(TEMPORAL_CODES),
    "a": _enum(ACTION_CODES),
    "mt": _enum(METRIC_TYPE_CODES),
    "q": _enum(QUALIFIER_CODES),
    "tg": _enum(TAG_CODES),
    "e": {"type": "STRING"},
    "g": {"type": "STRING"},
    "u": {"type": "STRING"},
    "m": {"type": "NUMBER"},
    "s": _enum(STATEMENT_CODES),
    "sc": _enum(SCOPE_CODES),
    "f": {"type": "STRING"},
    "x": {"type": "INTEGER"},
    "cf": {"type": "INTEGER"},
}

_BREAKDOWN_ITEM_SCHEMA: Dict[str, Any] = {
    "type": "OBJECT",
    "properties": {
        "d": {"type": "STRING", "enum": ["R", "C"]},
        "t": {"type": "INTEGER"},
        "p": {"type": "ARRAY", "items": {"type": "INTEGER"}},
        "n": {"type": "ARRAY", "items": {"type": "INTEGER"}},
        "r": {"type": "ARRAY", "items": {"type": "INTEGER"}},
        "o": {"type": "STRING", "enum": ["+", "/", "%", "*"]},
    },
    "required": ["d", "t", "p"],
    "propertyOrdering": ["d", "t", "p", "n", "r", "o"],
}

# Gemini structured output: guarantees syntactically valid JSON and valid enum codes.
EXTRACTION_RESPONSE_SCHEMA: Dict[str, Any] = {
    "type": "OBJECT",
    "properties": {
        "t": {
            "type": "ARRAY",
            "items": {
                "type": "OBJECT",
                "properties": {
                    "n": {"type": "INTEGER"},
                    "s": _enum(STATEMENT_CODES),
                    "st": {"type": "STRING"},
                    "sc": _enum(SCOPE_CODES),
                    "sh": {"type": "STRING"},
                    "u": {"type": "STRING"},
                    "m": {"type": "NUMBER"},
                    "lc": {"type": "INTEGER"},
                    "nc": {"type": "INTEGER"},
                    "tp": _enum(TEMPORAL_CODES),
                    "y": {
                        "type": "ARRAY",
                        "items": {
                            "type": "OBJECT",
                            "properties": {
                                "c": {"type": "INTEGER"},
                                "y": {"type": "INTEGER"},
                                "sc": _enum(SCOPE_CODES),
                                "e": {"type": "STRING"},
                                "g": {"type": "STRING"},
                                "d": {"type": "STRING"},
                                "rs": {"type": "INTEGER"},
                                "u": {"type": "STRING"},
                                "m": {"type": "NUMBER"},
                            },
                            "required": ["c", "y"],
                            "propertyOrdering": ["c", "y", "sc", "e", "g", "d", "rs", "u", "m"],
                        },
                    },
                    "sec": {
                        "type": "ARRAY",
                        "items": {
                            "type": "OBJECT",
                            "properties": {"a": {"type": "INTEGER"}, "b": {"type": "INTEGER"}, "t": {"type": "STRING"},
                                           "sc": _enum(SCOPE_CODES)},
                            "required": ["a", "b", "t"],
                            "propertyOrdering": ["a", "b", "t", "sc"],
                        },
                    },
                    "k": {"type": "ARRAY", "items": _BREAKDOWN_ITEM_SCHEMA},
                    "r": {
                        "type": "ARRAY",
                        "items": {
                            "type": "OBJECT",
                            "properties": {
                                "r": {"type": "INTEGER"},
                                "c": {"type": "STRING"},
                                "l": _enum(LEVEL_CODES),
                                **_EXTRAS_PROPS,
                            },
                            "required": ["r", "c", "l"],
                            "propertyOrdering": ["r", "c", "l", *_EXTRAS_PROPS],
                        },
                    },
                },
                "required": ["n", "s", "st", "sc", "sh", "lc", "nc", "y", "sec", "k", "r"],
                "propertyOrdering": ["n", "s", "st", "sc", "sh", "u", "m", "lc", "nc", "tp", "y", "sec", "k", "r"],
            },
        },
    },
    "required": ["t"],
}


def build_target_entity_context(company_name: str = "", security_code: str = "") -> str:
    """
    Builds the dynamic entity context block for the prompt if company details are provided.
    Keeps all guidelines completely generic and domain-agnostic.
    """
    context_lines = []
    if company_name:
        context_lines.append(f"- Target Company Name: {company_name}")
    if security_code:
        context_lines.append(f"- Security Code / Trading Symbol: {security_code}")

    if not context_lines:
        return ""

    return (
        "### TARGET ENTITY CONTEXT:\n"
        + "\n".join(context_lines)
        + "\n\nWhen a table presents breakdowns across constituent subsidiaries or operating segments:\n"
        + "- Identify the constituent entity with \"e\" and the operating segment with \"g\".\n"
        + "- Designate the master group total row as consolidated scope (\"sc\":\"C\").\n"
    )


def build_report_context_block(report_context: Optional[Dict[str, Any]]) -> str:
    """REPORT CONTEXT for the scope / statement decisions (see database.get_report_label_context):
    whether the report has consolidated statements, the titles printed on its pages, and how the
    previous report labelled the same headings."""
    if not report_context:
        return ""
    lines = [f"### REPORT CONTEXT (annual report {report_context.get('fiscal_year', '')}):"]
    if report_context.get("has_consolidated"):
        lines.append("- This report contains CONSOLIDATED statements (the group) and may also contain the company's own (STANDALONE) statements.")
    else:
        lines.append("- This report has NO consolidated statements: every table is STANDALONE (\"sc\":\"S\").")
    titles = [t for t in report_context.get("page_titles") or [] if t]
    if titles:
        lines.append("- Titles printed on this report's pages:")
        lines.extend(f"  - page {t['page']}: \"{t['title']}\"" for t in titles if isinstance(t, dict))
    prev = report_context.get("previous_labels") or []
    if prev:
        lines.append(f"- How the previous report ({report_context.get('previous_fiscal_year')}) labelled its headings "
                     "(reference for consistency; the printed heading of this page decides):")
        lines.extend(f"  - \"{p['heading']}\" -> s={p['statement']}, sc={p['scope']}" for p in prev)
    return "\n".join(lines)


def build_extraction_prompt(
    md_content: str,
    company_name: str = "",
    security_code: str = "",
    corrective_hint: Optional[str] = None,
    report_context: Optional[Dict[str, Any]] = None
) -> str:
    """
    Constructs the dynamic user prompt for the extraction model, combining target entity context,
    the report context, any corrective audit directives, and coordinate-annotated markdown.
    """
    blocks = []

    entity_block = build_target_entity_context(company_name=company_name, security_code=security_code)
    if entity_block:
        blocks.append(entity_block)

    report_block = build_report_context_block(report_context)
    if report_block:
        blocks.append(report_block)

    if corrective_hint:
        blocks.append(f"### CRITICAL EXTRACTION AUDIT DIRECTIVE:\n{corrective_hint}")

    annotated_md = annotate_markdown_tables_with_coordinates(md_content)
    blocks.append(
        f"Classify all financial metric rows on this page according to the system instructions:\n\n{annotated_md}"
    )

    return "\n\n".join(blocks)


def annotate_markdown_tables_with_coordinates(md_text: str) -> str:
    """
    Annotates every table found by extraction_verifier.parse_markdown_tables with:
    1. A column guide comment and header rows with [Col 1], [Col 2], [Col 3]...
    2. Body rows prefixed with [Table X: Row Y]
    Uses the same numbering as the grid the decoder reads, so coordinates always line up.
    Pipe blocks that are not valid tables are passed through unannotated.
    """
    if not md_text:
        return md_text

    lines = md_text.splitlines()
    tables = parse_markdown_tables(md_text)
    out: List[str] = []
    pos = 0
    for t in tables:
        out.extend(lines[pos:t["start"]])
        n = t["num"]
        out.append(f"\n<!-- TABLE {n} -->")
        if t["header_lines"]:
            guide = " | ".join(f"Col {i+1}: {c}" for i, c in enumerate(t["header_lines"][0]) if c)
            out.append(f"<!-- Table {n} Column Guide: {guide} -->")
        body_lines = {line_idx: r for r, line_idx in enumerate(t["row_line_idx"], start=1)}
        header_left = len(t["header_lines"])
        for i in range(t["start"], t["end"]):
            if i in body_lines:
                out.append(f"[Table {n}: Row {body_lines[i]}] {lines[i]}")
            elif header_left and not re.match(r"^\|?\s*[-:]+[-| :]*$", lines[i].strip()):
                cells = t["header_lines"][len(t["header_lines"]) - header_left]
                header_left -= 1
                hdr = [f"[Col {k+1}] {c}" if c else f"[Col {k+1}]" for k, c in enumerate(cells)]
                out.append(f"[Table {n} Header] | " + " | ".join(hdr) + " |")
            else:
                out.append(lines[i])
        pos = t["end"]
    out.extend(lines[pos:])
    return "\n".join(out)


# ---------------------------------------------------------------------------
# Decoding the compact output
# ---------------------------------------------------------------------------
def _code(table: Dict[str, str], value: Any, default: Optional[str] = None) -> Optional[str]:
    if value is None or value == "":
        return default
    key = str(value).strip().upper()
    if key in table:
        return table[key]
    if key in table.values():  # the model wrote the full enum name
        return key
    return default


def _int(value: Any) -> Optional[int]:
    try:
        return int(value)
    except (TypeError, ValueError):
        return None


def _num(value: Any, default: float) -> float:
    try:
        return float(value)
    except (TypeError, ValueError):
        return default


def _clean_label(text: str) -> str:
    """Printed label as raw_field: markdown emphasis and stray [Col N] tags removed, wording kept."""
    text = re.sub(r"^\[Col\s*\d+\]\s*", "", text or "")
    text = re.sub(r"(\*\*|__|\*)", "", text)
    return re.sub(r"\s+", " ", text).strip()


_LABEL_NOTE_RE = re.compile(r"\(\s*notes?\s+([0-9][0-9A-Za-z.]*?)\.?\s*\)", re.I)


def _split_notes(text: str) -> List[str]:
    text = _clean_label(text)
    if not text or text in ("-", "—", "–"):
        return []
    return [p.strip() for p in re.split(r"\s*[,&;/]\s*|\s+and\s+", text) if p.strip()]


def _column_specs(table: Dict[str, Any]) -> List[Dict[str, Any]]:
    """Value columns [{"c":3,"y":2024,...}] with "col" added, sorted, duplicates dropped."""
    specs, seen = [], set()
    for spec in table.get("y") or []:
        col = _int(spec.get("c"))
        if col is None or col in seen:
            continue
        seen.add(col)
        specs.append({**spec, "col": col})
    return sorted(specs, key=lambda s: s["col"])


def _sections(table: Dict[str, Any]) -> List[tuple]:
    """Section ranges as (first_row, last_row, category, scope code or None)."""
    out = []
    for sec in table.get("sec") or []:
        lo, hi, text = _int(sec.get("a")), _int(sec.get("b")), sec.get("t")
        if lo is not None and hi is not None and text:
            out.append((lo, hi, str(text), sec.get("sc")))
    return out


def _row_parts(entry: Dict[str, Any]) -> tuple:
    """(row, name, level code, extras) of a row object {"r","c","l",...extras}."""
    extras = {k: v for k, v in entry.items() if k not in ("r", "c", "l")}
    return entry.get("r"), entry.get("c"), entry.get("l"), extras


_HEADER_NOISE_RE = re.compile(r"\b(19|20)\d{2}\b|\b(taka|tk|bdt|usd|amount|in|figures)\b|[^A-Za-z0-9 &-]", re.I)


_SCALES = (1.0, 1_000.0, 100_000.0, 1_000_000.0, 10_000_000.0, 1_000_000_000.0)


def _scale(value: Any) -> Optional[float]:
    """A printed scale multiplier (1, thousand, lakh, million, crore, billion); anything else is not a scale."""
    m = _num(value, 0)
    return m if m in _SCALES else None


def _same_year_segments(col_specs: List[Dict[str, Any]], col_years: Dict[int, Optional[int]],
                        header_lines: List[List[str]]) -> Dict[int, str]:
    """When several value columns print the same year with nothing telling them apart (no "sc" / "e" / "g"),
    each is still its own printed cell: its segment is its printed column header without the year and
    currency words ("Conventional Taka 2013" -> "Conventional"); a "Total" column keeps no segment. A column the model marked restated ("rs") is the same line printed again and is never a segment."""
    groups: Dict[tuple, List[int]] = {}
    for spec in col_specs:
        if spec.get("rs"):          # a restated column is the same line printed again for that year, not a segment
            continue
        key = (col_years.get(spec["col"]), spec.get("sc"), spec.get("e"), spec.get("g"))
        groups.setdefault(key, []).append(spec["col"])
    out: Dict[int, str] = {}
    for (_, _, _, g), cols in groups.items():
        if len(cols) < 2 or g:
            continue
        for col in cols:
            text = " ".join(line[col - 1] for line in header_lines if 0 < col <= len(line) and line[col - 1])
            name = re.sub(r"\s+", " ", _HEADER_NOISE_RE.sub(" ", text.replace("*", ""))).strip()
            if name and name.lower() != "total":
                out[col] = name
    return out


def _column_year(spec: Dict[str, Any], header_lines: List[List[str]], report_end: Optional[int] = None) -> Optional[int]:
    """Year of a value column: "y", else the year of "d", else the period-end year in the column's
    printed header (e.g. "July 01, 2020 to June 30, 2021" -> 2021).

    With report_end (the end year of the report the page comes from), a column whose header prints
    no year is the report's own year, and a year the model gave outside [report_end - 6, report_end]
    is replaced by the year printed in the header (10-year summaries keep their old years), or by the
    report year when the header prints none (a guessed year cannot leak into another decade)."""
    col = spec["col"]
    header = " ".join(cells[col - 1] for cells in header_lines if 0 < col <= len(cells))
    header_year = extract_year_int(_clean_label(header))
    year = _int(spec.get("y"))
    if year is None:
        year = extract_year_int(spec.get("d"))
    if year is None:
        year = header_year
    if report_end:
        if year is None:
            year = report_end
        elif not (report_end - 6 <= year <= report_end):
            year = header_year or report_end
    return year


FIELD_UNIDENTIFIED = "FIELD_UNIDENTIFIED"
# A concept name that is only one of these words does not say which line it is (rule 8 needs the parent heading).
GENERIC_NAMES = frozenset({"total", "subtotal", "sub total", "grand total", "others", "other", "miscellaneous", "misc",
                           "balance", "net", "amount", "taka"})


def _is_generic_name(name: str) -> bool:
    return " ".join(re.sub(r"[^a-z ]+", " ", str(name or "").lower()).split()) in GENERIC_NAMES
MIN_NAME_CONFIDENCE = 50      # a line named from a heading the model is less sure of than this is treated as unidentified
UNIDENTIFIED_REASON = ("The page does not show which line this is (its title or parent heading is on another page); "
                       "the figures are kept but the line is left out of the CSV and the merge.")


def decode_compact_extraction(payload: Any, md_content: str, report_end: Optional[int] = None) -> List[Dict[str, Any]]:
    """
    Expands the compact model output into the grouped item shape used by save_extracted_metrics:
    {concept{name,statement,category,level}, scope{type,entity,segment},
     behavior{temporal,action,metric_type,qualifier}, tag, verification_flag, flag_reason,
     raw_field, notes, data[{year,date,numeric_value,unit,multiplier,table_num,table_row,table_col}]}

    raw_field, notes and values are read from the markdown cells at the coordinates the model gave.
    numeric_value is the number as printed; the scale stays in multiplier (csv_service multiplies).
    Columns with a different scope / entity / segment produce separate items.
    """
    tables_out = payload.get("t") or [] if isinstance(payload, dict) else []
    parsed = {t["num"]: t for t in parse_markdown_tables(md_content)}
    items: List[Dict[str, Any]] = []

    for table in tables_out:
        if not isinstance(table, dict):
            continue
        t_num = _int(table.get("n")) or 1
        grid_table = parsed.get(t_num)
        if grid_table is None:
            logger.warning("Compact extraction references missing table %s", t_num)
            continue
        rows = grid_table["rows"]

        t_stmt = _code(STATEMENT_CODES, table.get("s"), "UNKNOWN")
        t_scope = _code(SCOPE_CODES, table.get("sc"), "CONSOLIDATED")
        t_unit = table.get("u")
        t_mult = _scale(table.get("m")) or 1.0
        t_temporal = _code(TEMPORAL_CODES, table.get("tp"))
        label_col = _int(table.get("lc")) or 1
        note_col = _int(table.get("nc")) or 0
        col_specs = _column_specs(table)
        col_years = {spec["col"]: _column_year(spec, grid_table["header_lines"], report_end) for spec in col_specs}
        col_segment = _same_year_segments(col_specs, col_years, grid_table["header_lines"])
        sections = _sections(table)

        seen_rows: set = set()
        column_segments = bool(col_segment) or any(spec.get("g") for spec in col_specs)
        for row_entry in table.get("r") or []:
            row_ref, name_ref, level_ref, extras = _row_parts(row_entry)
            row = _int(row_ref)
            if row is None or not (1 <= row <= len(rows)):
                logger.warning("Compact extraction references missing row %s in table %s", row_ref, t_num)
                continue
            if row in seen_rows:   # a printed row is one line: a repeated row number adds nothing
                continue
            seen_rows.add(row)
            cells = rows[row - 1]

            raw_field = _clean_label(cells[label_col - 1]) if 0 < label_col <= len(cells) else ""
            name = str(name_ref).strip() if name_ref is not None else "="
            if not name or name == "=":
                name = raw_field
            level = _code(LEVEL_CODES, level_ref, "LINE_ITEM")
            if column_segments and extras.get("g"):
                # the columns name the segments: a row-level segment would give every column the same one
                name = re.sub(r"\s*[-(]\s*" + re.escape(str(extras["g"])) + r"\s*\)?\s*$", "", name, flags=re.I) or name
                extras = {k: v for k, v in extras.items() if k != "g"}
            notes = _split_notes(cells[note_col - 1]) if 0 < note_col <= len(cells) else []
            # the printed cell holding the note reference: the Note column, else a "(note 12.a)" in the label
            note_cell = {"table_num": t_num, "table_row": row, "table_col": note_col} if notes else None
            if not notes and 0 < label_col <= len(cells):
                m_note = _LABEL_NOTE_RE.search(cells[label_col - 1])
                if m_note:
                    notes = [m_note.group(1)]
                    note_cell = {"table_num": t_num, "table_row": row, "table_col": label_col}

            stmt = _code(STATEMENT_CODES, extras.get("s"), t_stmt)
            category = ""
            section_scope = None
            for lo, hi, text, sec_sc in sections:
                if lo <= row <= hi:
                    category = text
                    section_scope = sec_sc
            default_temporal = "POINT_IN_TIME" if stmt == "BALANCE_SHEET" else "PERIOD_FLOW"
            temporal = _code(TEMPORAL_CODES, extras.get("tp"), t_temporal or default_temporal)
            flag_reason = extras.get("f") or None
            confidence = _int(extras.get("cf"))
            generic = _is_generic_name(name)
            unidentified = bool(_int(extras.get("x"))) or generic or \
                (confidence is not None and confidence < MIN_NAME_CONFIDENCE)
            if unidentified:
                flag_reason = UNIDENTIFIED_REASON
                if generic and not _int(extras.get("x")):
                    # the bare word cannot name the line: offer the heading plus the word for a person to confirm
                    flag_reason = ("The line is named only \"%s\", which does not say which line it is; the suggested name "
                                   "is its heading plus that word. %s" % (name, UNIDENTIFIED_REASON))
                    if category and _norm_text(name) not in _norm_text(category):
                        name = f"{category} {name}".strip()
                elif confidence is not None and not _int(extras.get("x")):
                    flag_reason += f" The model was only {confidence}% sure of the heading it named this line from."

            # One item per (scope, entity, segment) combination of the value columns
            groups: Dict[tuple, List[Dict[str, Any]]] = {}
            for spec in col_specs:
                col = spec["col"]
                if not (0 < col <= len(cells)) or col in (label_col, note_col):
                    continue
                value = parse_numeric_cell_value(cells[col - 1])
                if value is None:
                    continue
                scope = _code(SCOPE_CODES, extras.get("sc") or spec.get("sc") or section_scope, t_scope)
                entity = extras.get("e") or spec.get("e") or None
                segment = spec.get("g") or col_segment.get(col) or extras.get("g") or None
                unit = extras.get("u") if "u" in extras else spec.get("u", t_unit)
                mult = _scale(extras.get("m")) or _scale(spec.get("m")) or t_mult
                groups.setdefault((scope, entity, segment), []).append({
                    "year": col_years[col],
                    "date": spec.get("d"),
                    "restated": 1 if spec.get("rs") else 0,
                    "numeric_value": value,
                    "unit": unit,
                    "multiplier": mult,
                    "table_num": t_num,
                    "table_row": row,
                    "table_col": col,
                })

            for (scope, entity, segment), data in groups.items():
                items.append({
                    "concept": {"name": name, "statement": stmt, "category": category, "level": level},
                    "scope": {"type": scope, "entity": entity, "segment": segment},
                    "behavior": {
                        "temporal": temporal,
                        "action": _code(ACTION_CODES, extras.get("a")),
                        "metric_type": _code(METRIC_TYPE_CODES, extras.get("mt"), "CURRENCY"),
                        "qualifier": _code(QUALIFIER_CODES, extras.get("q")),
                    },
                    "tag": _code(TAG_CODES, extras.get("tg"), "FINANCIAL_METRIC"),
                    "verification_flag": FIELD_UNIDENTIFIED if unidentified else ("INFERRED_DISCREPANCY" if flag_reason else None),
                    "flag_reason": flag_reason,
                    "raw_field": raw_field,
                    "notes": notes,
                    "note_cell": note_cell,
                    "data": data,
                })
    return items


def _decimals(cell: str) -> int:
    m = re.search(r"\.(\d+)", str(cell or "").replace(",", ""))
    return len(m.group(1)) if m else 0


COMPOSITION_OPERATIONS = {"+": "SUM", "/": "RATIO", "%": "PERCENT", "*": "PRODUCT"}


def _combine(operation: str, values: List[Optional[float]]) -> Optional[float]:
    """The value a non-sum breakdown predicts (None when a part is empty or the divisor is zero)."""
    if any(v is None for v in values):
        return None
    if operation in ("RATIO", "PERCENT"):
        if not values[1]:
            return None
        return values[0] / values[1] * (100.0 if operation == "PERCENT" else 1.0)
    out = 1.0
    for v in values:
        out *= v
    return out


def decode_note_cells(payload: Any, md_content: str) -> List[Dict[str, Any]]:
    """Every printed cell of the page that holds a note reference, whether or not its row is a metric
    (e.g. a heading row "Provision for taxation | 44" without figures): the cells of the Note column the
    model identified ("nc"), and "(note 12.a)" references inside a label. [{table_num, table_row, table_col, notes}]."""
    tables_out = payload.get("t") or [] if isinstance(payload, dict) else []
    parsed = {t["num"]: t for t in parse_markdown_tables(md_content)}
    out: List[Dict[str, Any]] = []
    for table in tables_out:
        t_num = _int(table.get("n")) or 1
        grid = parsed.get(t_num)
        if grid is None:
            continue
        label_col, note_col = _int(table.get("lc")) or 1, _int(table.get("nc")) or 0
        for row, cells in enumerate(grid["rows"], 1):
            notes = _split_notes(cells[note_col - 1]) if 0 < note_col <= len(cells) else []
            if notes:
                out.append({"table_num": t_num, "table_row": row, "table_col": note_col, "notes": notes})
            elif 0 < label_col <= len(cells):
                m = _LABEL_NOTE_RE.search(cells[label_col - 1])
                if m:
                    out.append({"table_num": t_num, "table_row": row, "table_col": label_col, "notes": [m.group(1)]})
    return out


def decode_compositions(payload: Any, md_content: str) -> List[Dict[str, Any]]:
    """The model's breakdowns ("k") as cell-level compositions, each checked against the printed numbers:
    [{"table_num", "direction" ("ROWS" / "COLUMNS"), "total": {"row", "col", "value"},
      "parts": [{"row", "col", "sign", "value"}], "parts_sum", "difference", "status"}].
    status: MATCH when the signed parts add up to the total within printed rounding, MISMATCH otherwise.
    A part with an empty cell counts as 0 (value None). Totals with no printed number are skipped."""
    tables_out = payload.get("t") or [] if isinstance(payload, dict) else []
    parsed = {t["num"]: t for t in parse_markdown_tables(md_content)}
    out: List[Dict[str, Any]] = []
    for table in tables_out:
        if not isinstance(table, dict) or not table.get("k"):
            continue
        t_num = _int(table.get("n")) or 1
        grid = parsed.get(t_num)
        if grid is None:
            continue
        rows = grid["rows"]
        label_col = _int(table.get("lc")) or 1
        note_col = _int(table.get("nc")) or 0
        value_cols = [spec["col"] for spec in _column_specs(table) if spec["col"] not in (label_col, note_col)]
        metric_rows = sorted({r for r in (_int(e.get("r")) for e in table.get("r") or []) if r})

        def cell(r: int, c: int) -> Optional[str]:
            if 1 <= r <= len(rows) and 1 <= c <= len(rows[r - 1]):
                return rows[r - 1][c - 1]
            return None

        seen = set()
        for k in table["k"]:
            if not isinstance(k, dict):
                continue
            direction = "COLUMNS" if str(k.get("d") or "R").upper() == "C" else "ROWS"
            total = _int(k.get("t"))
            minus = {x for x in (_int(v) for v in k.get("n") or []) if x}
            parts = [x for x in dict.fromkeys(_int(v) for v in k.get("p") or []) if x and x != total]
            if total is None or not parts:
                continue
            operation = COMPOSITION_OPERATIONS.get(str(k.get("o") or "+").strip(), "SUM")
            if operation in ("RATIO", "PERCENT") and len(parts) != 2:
                continue
            if direction == "ROWS":
                pairs = [((total, c), [(p_, c) for p_ in parts]) for c in value_cols]
            else:
                limit = {x for x in (_int(v) for v in k.get("r") or []) if x}
                pairs = [((r, total), [(r, p_) for p_ in parts]) for r in (sorted(limit) if limit else metric_rows)]
            for (tr, tc), part_cells in pairs:
                if (tr, tc) in seen:
                    continue
                t_text = cell(tr, tc)
                t_val = parse_numeric_cell_value(t_text) if t_text is not None else None
                if t_val is None:
                    continue
                part_out, total_sum, decimals = [], 0.0, _decimals(t_text)
                tolerance = 0.5 * 10 ** (-decimals)          # each printed figure is rounded to its OWN precision
                for pr, pc in part_cells:
                    p_text = cell(pr, pc)
                    if p_text is None:
                        break
                    p_val = parse_numeric_cell_value(p_text)
                    sign = -1 if (pc if direction == "COLUMNS" else pr) in minus else 1
                    part_out.append({"row": pr, "col": pc, "sign": sign, "value": p_val})
                    # a deducted part counts as a deduction whichever way it is printed: "Less: X  30" and
                    # "(30)" both subtract 30 (a part already printed negative must not be subtracted twice)
                    total_sum += (-abs(p_val or 0.0)) if sign < 0 else (p_val or 0.0)
                    decimals = max(decimals, _decimals(p_text))
                    tolerance += 0.5 * 10 ** (-_decimals(p_text))
                else:
                    seen.add((tr, tc))
                    if operation == "SUM":
                        computed = total_sum
                        ok = abs(computed - t_val) <= tolerance + 1e-9
                    else:
                        computed = _combine(operation, [p["value"] for p in part_out])
                        # the total is printed rounded and its inputs are rounded too: half a printed unit plus 0.1%
                        ok = computed is not None and abs(computed - t_val) <= \
                            0.5 * 10 ** (-_decimals(t_text)) + 0.001 * abs(t_val) + 1e-9
                    diff = None if computed is None else computed - t_val
                    out.append({"table_num": t_num, "direction": direction, "operation": operation,
                                "total": {"row": tr, "col": tc, "value": t_val},
                                "parts": part_out,
                                "parts_sum": None if computed is None else round(computed, 6),
                                "difference": None if diff is None else round(diff, 6),
                                "status": "MATCH" if ok else "MISMATCH"})
    return out


def _parse_json(text: str) -> Any:
    """The model's JSON answer (structured output guarantees the schema); None when unparseable."""
    try:
        return json.loads(text)
    except ValueError:
        logger.error("Extraction answer is not valid JSON: %s", text[:500])
        return None


# ---------------------------------------------------------------------------
# Scope / statement evidence check (the model's labels are verified, never rewritten)
# ---------------------------------------------------------------------------
LABEL_EVIDENCE_FAILED = "LABEL_EVIDENCE_FAILED"
_PRIMARY_TITLE_RE = {
    "BALANCE_SHEET": re.compile(r"financial\s+position|balance\s+sheet", re.I),
    "INCOME_STATEMENT": re.compile(r"profit|loss|income\s+statement|comprehensive\s+income|revenue\s+account", re.I),
    "CASH_FLOW": re.compile(r"cash\s+flows?", re.I),
    "CHANGES_IN_EQUITY": re.compile(r"changes\s+in\s+(shareholders'?\s+)?equity", re.I),
}
_CONSOLIDATED_WORD_RE = re.compile(r"\bconsolidat|\bgroup\b", re.I)
_OWN_ENTITY_WORD_RE = re.compile(r"\b(separate|standalone|stand-alone|solo|bank\s+only|company\s+only)\b", re.I)


def _norm_text(s: Any) -> str:
    s = re.sub(r"\[col\s*\d+\]", " ", str(s or ""), flags=re.I)
    s = re.sub(r"[\W_]+", " ", s.lower())   # punctuation-insensitive: "(Increase) / decrease" == "Increase/(decrease)"
    return re.sub(r"\s+", " ", s).strip()


def _quote_found(quote: str, haystacks: List[str]) -> bool:
    q = _norm_text(quote)
    return bool(q) and any(q in h for h in haystacks)


def _prints_heading(md: str, stmt: str) -> str:
    """The page's own heading line (not a table row) for this primary statement, or ""."""
    pat = _PRIMARY_TITLE_RE.get(stmt)
    if not pat:
        return ""
    for line in str(md or "").splitlines():
        text = re.sub(r"[#*_`]+", "", line).strip()
        if pat.search(text) and not line.lstrip().startswith("|") and 3 < len(text) <= 120:
            return text
    return ""


_SOURCE_LINE_RE = re.compile(r"^\s*Source:.*$", re.M)


def check_label_evidence(payload: Any, md_content: str, report_context: Optional[Dict[str, Any]]) -> Dict[int, str]:
    """{table number: reason} for every table whose scope ("sc"/"sh") or statement ("s"/"st") label is
    not backed by the printed page. Section-level scopes are checked against their own headings."""
    if not report_context or not isinstance(payload, dict):
        return {}
    # the "Source: <company> (<code>) | FY | Page" line is written by the OCR from system context: never evidence
    page = _norm_text(_SOURCE_LINE_RE.sub("", md_content))
    titles = [_norm_text(t.get("title") if isinstance(t, dict) else t) for t in report_context.get("page_titles") or []]
    has_cons = bool(report_context.get("has_consolidated"))
    out: Dict[int, str] = {}
    for table in payload.get("t") or []:
        if not isinstance(table, dict):
            continue
        n = _int(table.get("n")) or 1
        problems = []
        stmt = _code(STATEMENT_CODES, table.get("s"), "UNKNOWN")
        scope = _code(SCOPE_CODES, table.get("sc"), "CONSOLIDATED")
        sh, st = str(table.get("sh") or ""), str(table.get("st") or "")

        if not st:
            problems.append('statement evidence "st" is missing')
        elif not _quote_found(st, [page] + titles):
            problems.append(f'statement evidence "{st}" is not printed on the page or in the report titles')
        elif stmt in _PRIMARY_TITLE_RE and not _PRIMARY_TITLE_RE[stmt].search(st):
            problems.append(f'"{st}" is not a {stmt} title (use N for notes and reconciliations)')
        elif stmt == "NOTES_AND_FOOTNOTES" and any(p.search(st) for p in _PRIMARY_TITLE_RE.values()) \
                and re.match(r"^\s*(consolidated\s+)?(statements?\s+of|balance\s+sheet|income\s+statement|cash\s+flow\s+statement)", st, re.I):
            problems.append(f'"{st}" is a primary statement title but the table was labelled N')

        if not sh:
            problems.append('scope evidence "sh" is missing')
        elif not _quote_found(sh, [page] + titles):
            problems.append(f'scope evidence "{sh}" is not printed on the page')
        elif stmt in _PRIMARY_TITLE_RE and not _quote_found(sh, [page]) and _prints_heading(md_content, stmt):
            own = _prints_heading(md_content, stmt)
            problems.append(f'scope evidence "{sh}" is taken from another page, but this page prints its own heading '
                            f'"{own}": quote that heading, and a heading without Consolidated/Group means the table is STANDALONE')
        elif stmt in _PRIMARY_TITLE_RE and not _PRIMARY_TITLE_RE[stmt].search(sh)                 and any(p.search(sh) for k, p in _PRIMARY_TITLE_RE.items() if k != stmt):
            problems.append(f'scope evidence "{sh}" is the heading of a different statement than this {stmt} table; '
                            "quote the heading printed directly above this table")
        if scope == "CONSOLIDATED":
            if not has_cons:
                problems.append("labelled CONSOLIDATED but the report has no consolidated statements")
            elif sh and not _CONSOLIDATED_WORD_RE.search(sh):
                problems.append(f'labelled CONSOLIDATED but "{sh}" does not say Consolidated/Group')
        elif sh and _CONSOLIDATED_WORD_RE.search(sh) and not _OWN_ENTITY_WORD_RE.search(sh):
            problems.append(f'labelled STANDALONE but "{sh}" says Consolidated')

        for sec in table.get("sec") or []:
            if not isinstance(sec, dict) or not sec.get("sc"):
                continue
            text, sec_scope = str(sec.get("t") or ""), _code(SCOPE_CODES, sec.get("sc"), scope)
            if sec_scope == "CONSOLIDATED" and (not has_cons or not _CONSOLIDATED_WORD_RE.search(text)):
                problems.append(f'section "{text}" labelled CONSOLIDATED without saying Consolidated/Group')
            elif sec_scope == "STANDALONE" and _CONSOLIDATED_WORD_RE.search(text) and not _OWN_ENTITY_WORD_RE.search(text):
                problems.append(f'section "{text}" labelled STANDALONE but says Consolidated')
        if problems:
            out[n] = "; ".join(problems)
    return out


def _label_hint(failures: Dict[int, str]) -> str:
    return ("CRITICAL: the scope / statement labels of these tables are not backed by the printed page. "
            "Re-read the printed headings, copy them verbatim into \"sh\" and \"st\", and choose \"sc\" and \"s\" "
            "by rules 6 and 7:\n" + "\n".join(f"- Table {n}: {r}" for n, r in sorted(failures.items())))


def _flag_label_failures(items: List[Dict[str, Any]], failures: Dict[int, str]) -> None:
    for it in items:
        tables = {d.get("table_num") for d in it.get("data") or []}
        reasons = [failures[t] for t in tables if t in failures]
        if reasons:
            it["verification_flag"] = LABEL_EVIDENCE_FAILED
            prior = it.get("flag_reason")
            it["flag_reason"] = "; ".join([r for r in [prior] if r] + reasons)


def extract_financial_data_from_markdown(
    md_content: str,
    company_name: str = "",
    security_code: str = "",
    model_name: str = DEFAULT_MODEL,
    corrective_hint: Optional[str] = None,
    fiscal_year: Optional[str] = None,
    report_context: Optional[Dict[str, Any]] = None
) -> Dict[str, Any]:
    """
    Sends markdown to Gemini and decodes its compact table-oriented answer into structured metric items.
    Rate-limited and retry-safe. Dynamically provides company name and security code context.
    Supports corrective hint injection for automated recovery if initial extraction fails verification.
    fiscal_year (the report, e.g. "2017-18") bounds the column years (see _column_year).
    report_context (database.get_report_label_context) is given to the model; its scope / statement labels
    are stored as given (no evidence check, no retry).
    """
    report_end = extract_year_int(fiscal_year) if fiscal_year else None
    key = gemini_api_key("extract")
    if not key:
        raise ValueError("EXTRACT_API_KEY is not configured.")
    client = genai.Client(api_key=key)

    def _attempt(hint: Optional[str]) -> Dict[str, Any]:
        prompt = build_extraction_prompt(
            md_content=md_content,
            company_name=company_name,
            security_code=security_code,
            corrective_hint=hint,
            report_context=report_context,
        )

        def _call_api():
            start_time = time.time()
            response = generate_json(client, model_name, prompt, EXTRACTION_SYSTEM_INSTRUCTION,
                                     response_schema=EXTRACTION_RESPONSE_SCHEMA)
            duration_sec = time.time() - start_time
            usage = usage_tokens(response)

            text = response_text(response)
            data = _parse_json(text) if text else None
            items = decode_compact_extraction(data, md_content, report_end) if data is not None else []
            if text and not items and data != {"t": []}:
                logger.error("Extraction answer produced no items (%d output tokens): %s",
                             usage["candidates_tokens"], text[:500])

            return {
                "items": items,
                "payload": data,
                "tokens": usage["total_tokens"],
                "prompt_tokens": usage["prompt_tokens"],
                "candidates_tokens": usage["candidates_tokens"],
                "thoughts_tokens": usage["thoughts_tokens"],
                "duration_sec": round(duration_sec, 2),
                "model": model_name
            }

        return execute_with_retry(_call_api, "extract")

    result = _attempt(corrective_hint)

    # Breakdowns: identified by the model in this one answer; the code only checks the printed numbers
    payload = result.pop("payload", None)
    result["compositions"] = decode_compositions(payload, md_content) if payload is not None else []
    result["note_cells"] = decode_note_cells(payload, md_content) if payload is not None else []
    return result
