silo/pkg/calc/pythonpath/silo_calc/sheet_format.py

"""BOM and Items sheet column layouts, constants, and detection helpers.

This module defines the column structure that matches the engineer's working
BOM format.  Hidden property columns and sync-tracking columns are appended
to the right.
"""

from typing import Dict, List, Optional, Tuple

# ---------------------------------------------------------------------------
# Column indices -- BOM sheet
# ---------------------------------------------------------------------------

# Visible core columns (always shown)
BOM_VISIBLE_HEADERS: List[str] = [
    "Item",  # A  - assembly label / section header
    "Level",  # B  - depth in expanded BOM
    "Source",  # C  - sourcing_type (M/P)
    "PN",  # D  - part_number
    "Description",  # E  - item description
    "Seller Description",  # F  - metadata.seller_description
    "Unit Cost",  # G  - standard_cost / metadata.unit_cost
    "QTY",  # H  - quantity on relationship
    "Ext Cost",  # I  - formula =G*H
    "Sourcing Link",  # J  - sourcing_link
    "Schema",  # K  - schema name
]

# Hidden property columns (collapsed group, available when needed)
BOM_PROPERTY_HEADERS: List[str] = [
    "Manufacturer",  # L
    "Manufacturer PN",  # M
    "Supplier",  # N
    "Supplier PN",  # O
    "Lead Time (days)",  # P
    "Min Order Qty",  # Q
    "Lifecycle Status",  # R
    "RoHS Compliant",  # S
    "Country of Origin",  # T
    "Material",  # U
    "Finish",  # V
    "Notes",  # W
    "Long Description",  # X
]

# Hidden sync columns (never shown to user)
BOM_SYNC_HEADERS: List[str] = [
    "_silo_row_hash",  # Y  - SHA256 of row data at pull time
    "_silo_row_status",  # Z  - synced/modified/new/error
    "_silo_updated_at",  # AA - server timestamp
    "_silo_parent_pn",  # AB - parent assembly PN for this BOM entry
]

# All headers in order
BOM_ALL_HEADERS: List[str] = (
    BOM_VISIBLE_HEADERS + BOM_PROPERTY_HEADERS + BOM_SYNC_HEADERS
)

# Index constants for quick access
COL_ITEM = 0
COL_LEVEL = 1
COL_SOURCE = 2
COL_PN = 3
COL_DESCRIPTION = 4
COL_SELLER_DESC = 5
COL_UNIT_COST = 6
COL_QTY = 7
COL_EXT_COST = 8
COL_SOURCING_LINK = 9
COL_SCHEMA = 10

# Property column range
COL_PROP_START = len(BOM_VISIBLE_HEADERS)  # 11
COL_PROP_END = COL_PROP_START + len(BOM_PROPERTY_HEADERS)  # 24

# Sync column range
COL_SYNC_START = COL_PROP_END  # 24
COL_ROW_HASH = COL_SYNC_START  # 24
COL_ROW_STATUS = COL_SYNC_START + 1  # 25
COL_UPDATED_AT = COL_SYNC_START + 2  # 26
COL_PARENT_PN = COL_SYNC_START + 3  # 27

# Total column count
BOM_TOTAL_COLS = len(BOM_ALL_HEADERS)

# ---------------------------------------------------------------------------
# Items sheet columns (flat list of all items for a project)
# ---------------------------------------------------------------------------

ITEMS_HEADERS: List[str] = [
    "PN",
    "Description",
    "Type",
    "Source",
    "Schema",
    "Standard Cost",
    "Sourcing Link",
    "Long Description",
    "Manufacturer",
    "Manufacturer PN",
    "Supplier",
    "Supplier PN",
    "Lead Time (days)",
    "Min Order Qty",
    "Lifecycle Status",
    "RoHS Compliant",
    "Country of Origin",
    "Material",
    "Finish",
    "Notes",
    "Created",
    "Updated",
]

# ---------------------------------------------------------------------------
# Property key mapping (header name -> DB field path)
# ---------------------------------------------------------------------------

PROPERTY_KEY_MAP: Dict[str, str] = {
    "Manufacturer": "manufacturer",
    "Manufacturer PN": "manufacturer_pn",
    "Supplier": "supplier",
    "Supplier PN": "supplier_pn",
    "Lead Time (days)": "lead_time_days",
    "Min Order Qty": "minimum_order_qty",
    "Lifecycle Status": "lifecycle_status",
    "RoHS Compliant": "rohs_compliant",
    "Country of Origin": "country_of_origin",
    "Material": "material",
    "Finish": "finish",
    "Notes": "notes",
}

# Reverse map
DB_FIELD_TO_HEADER: Dict[str, str] = {v: k for k, v in PROPERTY_KEY_MAP.items()}

# ---------------------------------------------------------------------------
# Row status colours (RGB tuples, 0-255)
# ---------------------------------------------------------------------------

STATUS_COLORS: Dict[str, Tuple[int, int, int]] = {
    "synced": (198, 239, 206),  # light green  #C6EFCE
    "modified": (255, 235, 156),  # light yellow #FFEB9C
    "new": (189, 215, 238),  # light blue   #BDD7EE
    "error": (255, 199, 206),  # light red    #FFC7CE
    "conflict": (244, 176, 132),  # orange       #F4B084
}

# ---------------------------------------------------------------------------
# Sheet type detection
# ---------------------------------------------------------------------------


def detect_sheet_type(headers: List[str]) -> Optional[str]:
    """Detect sheet type from the first row of headers.

    Returns ``"bom"``, ``"items"``, or ``None`` if unrecognised.
    """
    if not headers:
        return None
    # Normalise for comparison
    norm = [h.strip().lower() for h in headers]
    if "item" in norm and "level" in norm and "qty" in norm:
        return "bom"
    if "pn" in norm and "type" in norm:
        return "items"
    return None


def col_letter(index: int) -> str:
    """Convert 0-based column index to spreadsheet letter (A, B, ..., AA, AB)."""
    result = ""
    while True:
        result = chr(65 + index % 26) + result
        index = index // 26 - 1
        if index < 0:
            break
    return result