def strategic_stock_system_prompt() -> str:
    return """
You are extracting a SCANNED Strategic Stock spreadsheet.

This is a wide table containing MANY columns and MANY rows.

Your task is STRICT DATA EXTRACTION.

ABSOLUTE REQUIREMENTS
1. NO COLUMN may be omitted.
2. NO ROW may be omitted.
3. Every warehouse column must appear in the JSON.
4. Each row must contain values for ALL detected columns.
5. Empty cells must appear as null.

-----------------------------------------------------

STEP 1 — DETECT TABLE STRUCTURE

Before extracting data you MUST identify:

A) ALL COLUMN HEADERS
B) TOTAL NUMBER OF ROWS

Read the header row carefully.

Columns appear in this EXACT order:

SL
Item Code
Barcode
Item Name
Batch
Pro
Expiry
Blend
Grain Type
Var/type
Process type
COO
Unit
[WAREHOUSE COLUMNS]
Total Bags
Total MT
Shipment No
Quality Report No

Everything between Unit and Total Bags are WAREHOUSE columns.

-----------------------------------------------------

STEP 2 — DETECT ALL WAREHOUSE COLUMNS

Read ALL warehouse column headers exactly as written.

Examples from this sheet include:

Abu Dhabi Musaffah
Al Ain Meyzad
Al Ain Meyzad (Strategic)
Al Ain Muwaiji (Acc)
Al Ain Sanaya Block - A
Al Ain Sanaya B Block - B
Al Ain Sanaya C Block - D
Maxpeed 6 months count
DIC 1
DIC 2
DIC 3
DIC 4
DIC 5
DIC 6
DIC 7
DIC 8
DIC 9
DIC 9 (Strategic)
DIC 10
DIC 11
Sharja Sajaa Block C

Return ALL warehouse headers inside:

"warehouses": []

Even if some rows have empty values.

-----------------------------------------------------

STEP 3 — DETECT TOTAL ROW COUNT

Find the SL column.

Read the serial numbers.

Example:

1
2
3
...
43

The highest number determines total rows.

If highest SL = 43
then line_items MUST contain 43 objects.

Skipping rows is NOT allowed.

-----------------------------------------------------

STEP 4 — EXTRACT ROW DATA

For each SL row extract ALL columns IN ORDER:

COLUMN ORDER IS FIXED:
1. SL → n
2. Item Code → ic
3. Barcode → bc
4. Item Name → in
5. Batch → bn
6. Pro → pr
7. Expiry → ed
8. Blend → bl
9. Grain Type → gt
10. Var/type → vt
11. Process type → pt
12. COO → co
13. Unit → u
14. [warehouse columns] → wb
15. Total Bags → tb
16. Total MT → tm
17. Shipment No → sn
18. Quality Report No → qr

CRITICAL: Read each cell by its column position, not by guessing content.
Do NOT shift columns. The 7th column is always Expiry, the 8th is always Blend, etc.

Empty cells must be null.

-----------------------------------------------------

STEP 5 — WAREHOUSE VALUES

Warehouse columns must appear in wb object.

Example:

"wb": {
  "Abu Dhabi Musaffah": 30,
  "Al Ain Meyzad": 3877,
  "DIC 1": 4251
}

If empty:

"wb": {
  "Abu Dhabi Musaffah": null
}

-----------------------------------------------------

STEP 6 — TOTAL ROW

After the last SL row there is a TOTAL row.

Extract:

total_bags_all
total_mt_all

Do NOT include totals in line_items.

-----------------------------------------------------

STEP 7 — SIGNATURES

At the bottom of the document there are signatures:

Prepared By
Reviewed By
Approved By

Count visible signatures.

signature_count = number of signatures.

-----------------------------------------------------

STEP 8 — VALIDATION

Before returning JSON verify:

1. ALL columns detected
2. ALL warehouses included
3. ALL rows extracted
4. SL sequence complete
5. No skipped numbers

Example valid SL sequence:

1,2,3,4,...,43

-----------------------------------------------------

OUTPUT JSON STRUCTURE

Return ONLY JSON.

{
  "document_type": "strategic_stock_report",
  "company_name": null,
  "report_title": null,
  "stock_report_for": null,
  "document_date": null,
  "document_number": null,
  "reference_number": null,
  "prepared_by": null,
  "reviewed_by": null,
  "approved_by": null,
  "signature_count": 0,
  "warehouses": [],
  "warehouse_name": null,
  "item_name": null,
  "item_code": null,
  "batch_number": null,
  "expiry_date": null,
  "unit": null,
  "quantity_mt": null,
  "total_bags_all": null,
  "total_mt_all": null,
  "all_batches": null,
  "line_items": [
    {
      "n":1,
      "ic":null,
      "bc":null,
      "in":null,
      "bn":null,
      "pr":null,
      "ed":null,
      "bl":null,
      "gt":null,
      "vt":null,
      "pt":null,
      "co":null,
      "u":null,
      "tb":null,
      "tm":null,
      "sn":null,
      "qr":null,
      "wb":{}
    }
  ]
}
"""


def strategic_stock_document_prompt() -> str:
    """Pass 1 — extract only document header, warehouse list, totals and signatures."""
    return """
You are reading the HEADER and FOOTER of a Strategic Stock spreadsheet.

YOUR ONLY TASK: Extract document-level fields. Do NOT extract row data.

Extract:
1. company_name — company name at the top
2. report_title — title of the report
3. stock_report_for — the period/date the report covers
4. document_date — date of the document (YYYY-MM-DD)
5. document_number — document reference number
6. reference_number — any PO or reference number
7. prepared_by — name in Prepared By field
8. reviewed_by — name in Reviewed By field
9. approved_by — name in Approved By field
10. signature_count — count of visible signatures (0-3)
11. warehouses — list of ALL warehouse column headers in exact order as they appear left to right
12. total_bags_all — value from the TOTAL row in Total Bags column
13. total_mt_all — value from the TOTAL row in Total MT column

WAREHOUSE DETECTION:
- Warehouse columns appear BETWEEN the "Unit" column and the "Total Bags" column
- Read every column header between those two columns
- Return them in left-to-right order

Return ONLY this JSON:
{
  "document_type": "strategic_stock_report",
  "company_name": null,
  "report_title": null,
  "stock_report_for": null,
  "document_date": null,
  "document_number": null,
  "reference_number": null,
  "prepared_by": null,
  "reviewed_by": null,
  "approved_by": null,
  "signature_count": 0,
  "warehouses": [],
  "total_bags_all": null,
  "total_mt_all": null
}
""".strip()


def strategic_stock_chunk_prompt(warehouse_headers: list[str]) -> str:
    """Pass 2 — extract rows from a chunk, with known column order injected."""

    warehouse_list = "\n".join(f"{i+1}. {w}" for i, w in enumerate(warehouse_headers)) if warehouse_headers else "(detect from header row)"

    col_position = 14  # warehouse columns start at position 14
    wb_positions = ""
    for i, w in enumerate(warehouse_headers):
        wb_positions += f"  Column {col_position + i}: {w}\n"

    total_bags_col = col_position + len(warehouse_headers)
    total_mt_col = total_bags_col + 1
    shipment_col = total_mt_col + 1
    qr_col = shipment_col + 1

    return f"""
You are extracting ROW DATA from a chunk of a Strategic Stock spreadsheet.

THE COLUMN ORDER IS FIXED AND MUST BE FOLLOWED EXACTLY:

Column 1:  SL (serial number) → n
Column 2:  Item Code → ic
Column 3:  Barcode → bc
Column 4:  Item Name → in
Column 5:  Batch → bn
Column 6:  Pro (production date) → pr
Column 7:  Expiry → ed
Column 8:  Blend → bl
Column 9:  Grain Type → gt
Column 10: Var/type → vt
Column 11: Process type → pt
Column 12: COO → co
Column 13: Unit → u
{wb_positions}Column {total_bags_col}: Total Bags → tb
Column {total_mt_col}: Total MT → tm
Column {shipment_col}: Shipment No → sn
Column {qr_col}: Quality Report No → qr

WAREHOUSE COLUMNS (in order, keys for wb object):
{warehouse_list}

CRITICAL RULES:
- Read each cell strictly by its column position (left to right)
- Column 7 is ALWAYS Expiry — do NOT put item names or blend values there
- Column 8 is ALWAYS Blend — do NOT put expiry dates there
- The wb object keys must match the warehouse names above exactly
- Total Bags is the column AFTER the last warehouse column
- Total MT is the column AFTER Total Bags
- Do NOT include the TOTAL row in line_items

For each data row return:
{{
  "n": <SL number as integer>,
  "ic": <item code or null>,
  "bc": <barcode or null>,
  "in": <item name or null>,
  "bn": <batch number or null>,
  "pr": <production date or null>,
  "ed": <expiry date or null>,
  "bl": <blend or null>,
  "gt": <grain type or null>,
  "vt": <variety type or null>,
  "pt": <process type or null>,
  "co": <COO or null>,
  "u": <unit or null>,
  "tb": <total bags as number or null>,
  "tm": <total MT as number or null>,
  "sn": <shipment no or null>,
  "qr": <quality report no or null>,
  "wb": {{ <warehouse_name>: <bags as number or null>, ... }}
}}

Return ONLY this JSON:
{{
  "line_items": [ ... ]
}}
""".strip()


def strategic_stock_parse_compact(parsed: dict) -> dict:
    """
    Expand compact line item keys back to full field names.
    Compact keys: n, ic, bc, in, bn, pr, ed, bl, gt, vt, pt, co, u, tb, tm, sn, qr, wb
    """
    if not isinstance(parsed, dict):
        return parsed

    key_map = {
        "n": "line_number",
        "ic": "item_code",
        "bc": "barcode",
        "in": "item_name",
        "bn": "batch_number",
        "pr": "production",
        "ed": "expiry_date",
        "bl": "blend",
        "gt": "grain_type",
        "vt": "variety_type",
        "pt": "process_type",
        "co": "coo",
        "u": "unit",
        "tb": "total_bags",
        "tm": "total_mt",
        "sn": "shipment_no",
        "qr": "quality_report_no",
        "wb": "warehouse_bags",
    }

    line_items = parsed.get("line_items", [])
    if not isinstance(line_items, list):
        return parsed

    expanded = []
    for li in line_items:
        if not isinstance(li, dict):
            expanded.append(li)
            continue
        item = {}
        for k, v in li.items():
            full_key = key_map.get(k, k)
            item[full_key] = v
        expanded.append(item)

    parsed["line_items"] = expanded
    return parsed