
    5j(w                       d Z ddlZddlZddlZddlZddlZddlZddlmZ ddlm	Z	  e	ej                  j                  d e	e      j                         j                  dz              Z e	ej                  j                  d e	e      j                         j                  dz              Z ej"                         Zd	Zd
ZddddddZi ddddddddddddddddddddddddddddddddd ddd!d!d!d"d#d$Z ej.                  d%      Zd&Zd'Zd( Zd) Zd* Zd+ Zd, Zd- Z d. Z!d/ Z"d0 Z#d1 Z$d2 Z%d3 Z&dd4Z'dd5Z(d6 Z)d7 Z*	 	 dd8Z+d9 Z,d: Z-d; Z.d< Z/d= Z0d> Z1d? Z2d@ Z3dA Z4dBdCdDdEdFZ5	 	 ddGZ6	 	 ddHZ7ddIZ8dJZ9dKZ:dL Z;ddMZ<dNZ=dOZ>dPZ?dQZ@dRZAdS ZBdT ZCddUZDdV ZEdW ZFdX ZGdY ZHddZZId[ ZJdd\ZKd] ZLd^ ZMd_ ZNd` ZOda ZPdb ZQdddZRddeZSdfZTdcg dgdhdiZUdj ZVdk ZWddlZXddmZYddnZZdo Z[dp Z\ddqZ]dr Z^ddsZ_	 	 ddtZ`du ZaddvZbdw Zcdx Zddy Zedz Zfd{ Zgd| Zhd}Zid~ Zjd ZkdZldZmd Znd Zod Zpd Zqd ZrdZsd Ztd Zud Zvd Zwd Zxd Zyd Zzd Z{d Z|ddZ}d Z~d Zy)uz  
assets_store.py — SQLite asset inventory store (DVI-1185 P1, board-approved
Option B: Togen is the source of truth for the Asset File data).

Holds the asset inventory previously maintained in the SharePoint "Asset File"
workbook (4 team sheets + Master Data controlled vocabulary). The importer
reads that workbook once (and on demand for re-import during the transition
window), flagging data-quality exceptions instead of silently fixing or
dropping rows. Search/filtering for the Equipment tool Assets tab and future
consumers (WO pickers, Oversight, export) read from here.

Flask-independent (mirrors scada_store.py) so app.py, scripts, and tests can
all import it. assets.db lives alongside the app's other state files.

Schema
------
assets(asset_tag UNIQUE, tag_num, asset_type, description, area, form_id,
       length, width, height, manufacturer, model_no, serial_no, notes,
       material, source_sheet, extra, sp_hash) — one row per asset. tag_num
    is the numeric form of the tag when it is all digits (sort key). extra
    keeps unmapped source columns as JSON so imports are lossless. sp_hash
    fingerprints the source row as of the last import/sync (P3 merge guard).
vocab(kind, value, sort) — controlled vocabulary from the Master Data sheet
    (kinds: asset_type, form_id, area), in sheet order.
meta(key, value) — import bookkeeping (last_import_at/source/report).
    N)datetime)PathASSETS_DB_FILEz	assets.dbASSETS_PHOTO_DIRasset_photos  )
asset_typeform_idarear	   r
   r   )zasset typesz	form id'szform idsr   areasassettag	asset_tag
assettagnoassettagnumber	assettypedescriptionformidformidslengthlengthinwidthwidthinheightheightinmanufacturermodelmodel_nomodelno	serial_nonotesmaterial)modelnumberserialserialnoserialnumberr    r!   z
^photo\d*$)r   r	   r   r   r
   r   r   r   r   r   r   r    r!   source_sheet)r   r   r   r   r   r
   r    c                      t        j                  t        t              d      } | j	                  d       | j	                  d       | S )N   )timeoutzPRAGMA journal_mode=WALzPRAGMA busy_timeout=10000)sqlite3connectstrr   executeconns    #/var/www/html/togen/assets_store.py_connectr1   k   s6    ??3~.;DLL*+LL,-K    c                     ||v ry	 | j                  d|d|       y# t        j                  $ r&}dt        |      j	                         vr Y d}~yd}~ww xY w)u  Additive ALTER TABLE assets ADD COLUMN, concurrency-safe.

    `_write_lock` is a threading.Lock — it only serializes threads within one
    process, not the several gunicorn worker processes that boot concurrently on
    a deploy restart. Each worker reads `cols` (a PRAGMA table_info snapshot),
    sees the column missing, and races on the ALTER; N-1 lose and would crash
    with `OperationalError: duplicate column name` (DVI-1488, seen DVI-1486).
    The ADD COLUMN is inherently idempotent, so swallowing exactly that error is
    the KISS fix: whichever worker wins adds the column, the losers no-op. Any
    other OperationalError still propagates.NzALTER TABLE assets ADD COLUMN  zduplicate column name)r-   r*   OperationalErrorr,   lower)r/   colscolumnddlexcs        r0   _add_column_if_missingr;   r   sU     ~fcJK## "#c(..*:: ;s    AAAc            	         t         5  t               5 } | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d	      D ch c]  }|d
   	 }}t        | |dd       dD ]  \  }}t        | |||        | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       ddd       ddd       yc c}w # 1 sw Y   xY w# 1 sw Y   yxY w)zECreate tables/indexes if missing. Idempotent; call at import/startup.aA  CREATE TABLE IF NOT EXISTS assets (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   asset_tag TEXT NOT NULL UNIQUE,
                   tag_num INTEGER,
                   asset_type TEXT NOT NULL DEFAULT '',
                   description TEXT NOT NULL DEFAULT '',
                   area TEXT NOT NULL DEFAULT '',
                   form_id TEXT NOT NULL DEFAULT '',
                   length TEXT NOT NULL DEFAULT '',
                   width TEXT NOT NULL DEFAULT '',
                   height TEXT NOT NULL DEFAULT '',
                   manufacturer TEXT NOT NULL DEFAULT '',
                   model_no TEXT NOT NULL DEFAULT '',
                   serial_no TEXT NOT NULL DEFAULT '',
                   notes TEXT NOT NULL DEFAULT '',
                   material TEXT NOT NULL DEFAULT '',
                   source_sheet TEXT NOT NULL DEFAULT '',
                   extra TEXT NOT NULL DEFAULT '',
                   created_at TEXT NOT NULL DEFAULT (datetime('now')),
                   updated_at TEXT NOT NULL DEFAULT (datetime('now'))
               )z@CREATE INDEX IF NOT EXISTS idx_assets_type ON assets(asset_type)z:CREATE INDEX IF NOT EXISTS idx_assets_area ON assets(area)z=CREATE INDEX IF NOT EXISTS idx_assets_form ON assets(form_id)z?CREATE INDEX IF NOT EXISTS idx_assets_tagnum ON assets(tag_num)zCREATE TABLE IF NOT EXISTS vocab (
                   kind TEXT NOT NULL,
                   value TEXT NOT NULL,
                   sort INTEGER NOT NULL DEFAULT 0,
                   UNIQUE(kind, value)
               )zBCREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT)zCREATE TABLE IF NOT EXISTS photos (
                   asset_tag TEXT NOT NULL,
                   filename TEXT NOT NULL,
                   sort INTEGER NOT NULL DEFAULT 0,
                   UNIQUE(asset_tag, filename)
               )zPRAGMA table_info(assets)   sp_hashTEXT NOT NULL DEFAULT ''))rts_requiredINTEGER NOT NULL DEFAULT 0)rts_departmentsr?   )include_in_oversightr?   )oversight_dev_euir?   )out_of_servicerA   )offsiterA   )equipment_created_atr?   )model_idINTEGERz?CREATE INDEX IF NOT EXISTS idx_assets_model ON assets(model_id)a  CREATE TABLE IF NOT EXISTS equipment_aliases (
                   norm_name TEXT NOT NULL UNIQUE,
                   name TEXT NOT NULL DEFAULT '',
                   asset_tag TEXT NOT NULL,
                   created_at TEXT NOT NULL DEFAULT (datetime('now'))
               )zTCREATE INDEX IF NOT EXISTS idx_equipment_aliases_tag ON equipment_aliases(asset_tag)a  CREATE TABLE IF NOT EXISTS asset_models (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   name TEXT NOT NULL,
                   manufacturer TEXT NOT NULL DEFAULT '',
                   model_no TEXT NOT NULL DEFAULT '',
                   description TEXT NOT NULL DEFAULT '',
                   created_at TEXT NOT NULL DEFAULT (datetime('now')),
                   updated_at TEXT NOT NULL DEFAULT (datetime('now'))
               )a  CREATE TABLE IF NOT EXISTS sub_assemblies (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   name TEXT NOT NULL,
                   parent_kind TEXT NOT NULL,
                   parent_ref TEXT NOT NULL,
                   description TEXT NOT NULL DEFAULT '',
                   sort INTEGER NOT NULL DEFAULT 0,
                   created_at TEXT NOT NULL DEFAULT (datetime('now')),
                   updated_at TEXT NOT NULL DEFAULT (datetime('now'))
               )zeCREATE INDEX IF NOT EXISTS idx_sub_assemblies_parent ON sub_assemblies(parent_kind, parent_ref, sort)a3  CREATE TABLE IF NOT EXISTS parts (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   part_no TEXT NOT NULL UNIQUE,
                   name TEXT NOT NULL DEFAULT '',
                   description TEXT NOT NULL DEFAULT '',
                   on_hand REAL NOT NULL DEFAULT 0,
                   min_qty REAL NOT NULL DEFAULT 0,
                   location TEXT NOT NULL DEFAULT '',
                   created_at TEXT NOT NULL DEFAULT (datetime('now')),
                   updated_at TEXT NOT NULL DEFAULT (datetime('now'))
               )a|  CREATE TABLE IF NOT EXISTS part_links (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   part_id INTEGER NOT NULL,
                   target_kind TEXT NOT NULL,
                   target_ref TEXT NOT NULL,
                   created_at TEXT NOT NULL DEFAULT (datetime('now')),
                   UNIQUE(part_id, target_kind, target_ref)
               )zWCREATE INDEX IF NOT EXISTS idx_part_links_target ON part_links(target_kind, target_ref)zECREATE INDEX IF NOT EXISTS idx_part_links_part ON part_links(part_id)a{  CREATE TABLE IF NOT EXISTS part_movements (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   part_id INTEGER NOT NULL,
                   delta REAL NOT NULL,
                   kind TEXT NOT NULL,
                   reason TEXT NOT NULL DEFAULT '',
                   wo_ref TEXT NOT NULL DEFAULT '',
                   wo_part_id TEXT NOT NULL DEFAULT '',
                   asset_tag TEXT NOT NULL DEFAULT '',
                   on_hand_after REAL NOT NULL DEFAULT 0,
                   actor TEXT NOT NULL DEFAULT '',
                   created_at TEXT NOT NULL DEFAULT (datetime('now'))
               )zQCREATE INDEX IF NOT EXISTS idx_part_movements_part ON part_movements(part_id, id)zpCREATE UNIQUE INDEX IF NOT EXISTS idx_part_movements_wopart ON part_movements(wo_part_id) WHERE wo_part_id != ''a  CREATE TABLE IF NOT EXISTS part_alerts (
                   part_id INTEGER PRIMARY KEY,
                   last_alert_at TEXT NOT NULL DEFAULT '',
                   on_hand_at_alert REAL NOT NULL DEFAULT 0,
                   min_qty_at_alert REAL NOT NULL DEFAULT 0
               )N)_write_lockr1   r-   r;   )r/   rr7   r8   r9   s        r0   init_assets_dbrL      s:   	 Jhj JD	, 	N	PH	JK	MM	O	 	P	R	 #ll+FGH!HH$	#=	?) 	<KFC* #4vs;+	<, 	M	O 		 	/	0 			 		
	 	@	A 	
	 		 	6	7 	&	' 		 	.	/ 	D	E 		IJ J Jd IeJ J J Js6   G2BG&.G!:DG&G2!G&&G/	+G22G;c           	          t         5  t               5 }|j                  d| |dn
t        |      f       d d d        d d d        y # 1 sw Y   xY w# 1 sw Y   y xY w)NzaINSERT INTO meta (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value )rJ   r1   r-   r,   )keyvaluer/   s      r0   set_metarQ   U  sY    	 8hj 8DE"3u:6	88 8 8 8 8 8s!   A"AAA	
AAc                     t               5 }|j                  d| f      j                         }d d d        r|d   S d S # 1 sw Y   xY w)Nz$SELECT value FROM meta WHERE key = ?r   r1   r-   fetchone)rO   r/   rows      r0   get_metarV   ]  sO    	 VtllAC6JSSUV3q6"d"V Vs	   ">Ac                     | yt        | t              r| j                         rt        |       } t	        |       j                  dd      j                         S )zCell -> trimmed text. Integers lose the trailing .0 float form; error
    values like #VALUE! are treated per-caller (photo cells vs data cells).rN       r4   )
isinstancefloat
is_integerintr,   replacestriprP   s    r0   _cleanr`   g  sJ     }%E$4$4$6E
u:fc*0022r2   c                 ^    t        j                  ddt        |       j                               S )Nz	[^a-z0-9]rN   )resubr`   r6   r_   s    r0   _norm_headerrd   q  s"    66,F5M$7$7$9::r2   c                 J    | j                  d      xr | j                  d      S )N#!)
startswithendswithtexts    r0   _is_error_valuerl   u  s    ??36DMM#$66r2   c                 "   t         D ci c]  }|g  }}d}| j                  d      D ]^  }t        |r|d   nd      }|st        j	                  |j                               }|r|}@|sC|||   vsK||   j                  |       ` |S c c}w )zMaster Data sheet -> {kind: [values]} preserving sheet order.

    The sheet is one column: a section header ("Asset Types", "Form ID's",
    "Area") followed by that section's values until the next header.NT)values_onlyr   )VOCAB_KINDS	iter_rowsr`   _MASTER_SECTIONSgetr6   append)wskvocabcurrentrU   rk   sections          r0   _parse_master_vocabry   y  s    
 ((qQU(E(G|||- 	(c!f."&&tzz|4Gt5>1'N!!$'	( L )s   
Bc                     dj                  t        D cg c]  }| |   	 c}| j                  dd      gz         }t        j                  |j                  d            j                         S c c}w )zFingerprint of a parsed source row (data fields + extra JSON). Stored
    per asset at import/sync time so the next sync can tell whether the
    source row changed (source wins) or only Togen did (Togen edit kept)._extrarN   zutf-8)joinASSET_FIELDSrr   hashlibsha256encode	hexdigest)recordfbasiss      r0   _sp_hashr     s^     KK()q)VZZ"-E,FFHE>>%,,w/0::<< 	*s   A-c                     t        | j                  ddd      d      }|sy|dd D cg c]  }t        |       }}dj                  d |D              S c c}w )	z>Row 1 is a team banner ("Team #1" | names). Best-effort label.r=   Tmin_rowmax_rowrn   NrN      u    — c              3   &   K   | ]	  }|s|  y wN ).0ps     r0   	<genexpr>z_sheet_team.<locals>.<genexpr>  s     .aA.s   )nextrp   r`   r}   )rt   firstcpartss       r0   _sheet_teamr     sZ    aEtLE %bq	*1VAY*E*<<.5... +s   Ac           
      ~   t        | j                  ddd      d      D ]  \  }}|D cg c]  }t        |       }}t        d |D              s1i }t        |      D ]N  \  }}|rt        j                  |      r
|rdnd||<   't        j                  |d	t        ||         z         ||<   P ||fc S  di fS c c}w )
zLocate the header row (contains "Asset Tag") within the first rows.

    Returns (row_index, {col_index: field_or_None}) or (None, {}) when the
    sheet has no recognizable asset table (Master Data / Totals).r=      Tr   startc              3   >   K   | ]  }|j                  d         yw)r   N)rh   )r   hs     r0   r   z#_find_header_row.<locals>.<genexpr>  s     8Aq||J'8s   _photoN	_unknown:)		enumeraterp   rd   any_PHOTO_HEADER_REmatch_HEADER_MAPrr   r`   )rt   idxrU   r   normedmappingcolr   s           r0   _find_header_rowr     s    
 bll1aTlRZ[\ 	 S+./a,q///888G#F+ RQ,2215/08dGCL*q+s3x@P2PQ	R
 <	  8O 0s   B:c                    ddl m}  || dd      }	 t        D ci c]  }|g  }}d|j                  v rt	        |d         }|j                         D ci c]%  \  }}||D ci c]  }|j                         | c}' }	}}}t        j                         j                  d      |xs t        | dd      xs t        |       g |j                         D ci c]  \  }}|t        |       c}}ddddi g d	
i }
g }d1fd	}fd}|j                  D ]  }|dv r	||   }t        |      \  }}|%d   j                  |t        |      ddddd       C|t        |      dddd}t!        |j#                  |dz   d      |dz         D ]W  \  }}t$        D ci c]  }|d
 }}||d<   i }d}|j                         D ]  \  }}||t        |      k\  rt'        ||         }|s(|dk(  r+t)        |      s |d|||j+                  dd
      |dd        Xt)        |      r |d|||j+                  dd
      |       d}|j-                  d      r|||j/                  dd      d   <   |||<    |s|dxx   dz  cc<   |dxx   dz  cc<   d xx   dz  cc<   |d   }|s |d!||d
|d"   dd#        ,||
v r |d$|||d%|
|   z         D||f|
|<   d&D ]  \  }}||   }|s |d'v r |d(|j1                  d)d*      z         -||	|   j3                         v rC|	|   j+                  |j                               }|r|||<   d+xx   dz  cc<   z|d,k7  s |d-|j1                  d)d*      z  ||||dd#         |rt5        j6                  |      nd
|d.<   |j9                         rt;        |      nd|d/<   |j                  |       |d0xx   dz  cc<   d0xx   dz  cc<   Z dxx   |d   z  cc<   d   j                  |        	 |j=                          ||fS c c}w c c}w c c}}}w c c}}w c c}w # |j=                          w xY w)2u  Stream the workbook into ``(vocab, report, prepared)`` without touching
    the database — shared by the full-replace importer and the merge sync.

    Exceptions are FLAGGED, never silently fixed or dropped (plan §2B):
    - rows missing an Asset Tag # or duplicating one are skipped + listed;
    - unknown Asset Type / Area values (no case-insensitive vocab match) are
      imported verbatim + listed;
    - case-drift vocab matches (e.g. "mh2") are canonicalized to the vocab
      casing and counted as normalized;
    - photo cells holding a real (non-#VALUE!) value are listed — photos are
      not imported until P2;
    - blank Asset Type / Area are tallied in counts only (226 Tools rows have
      no Area; listing each would drown real anomalies).
    r   )load_workbookT)	read_only	data_onlyMaster Data%Y-%m-%d %H:%M:%SnameN)
imported_atsource_namesheetsrv   
total_rowsimportedskipped_blank
normalizedexception_counts
exceptionsrN   c                     d   j                  | d      dz   d   | <   t        d         t        k  rd   j                  | ||||d       y y )Nr   r   r=   r   )issuesheetrU   tagrP   )rr   lenEXCEPTION_DETAIL_CAPrs   )r   r   rU   r   rP   reports        r0   flagz_parse_workbook.<locals>.flag  sk    )*..ua81< %&u-6,'(+??|$++"U3-  @r2   c                 B    d   j                  | d      dz   d   | <   y Nr   r   r=   rr   r   r   s    r0   tallyz_parse_workbook.<locals>.tally  s.    )*..ua81< %&u-r2   )r   Totals
All Assetsr   zno asset table found)r   team	data_rowsr   r   note)r   r   r   r   r   r=   )r   rn   r   r&   Fr   z+photo value present (not imported until P2)r   P   zformula error valuer   :r   r   r   zmissing asset tag (row skipped)r   <   z!duplicate asset tag (row skipped)zfirst seen on sheet %s row %s)r	   r	   )r   r   )r
   r
   )r	   r   zblank %s_r4   r   r
   zunknown %s (imported verbatim)r|   _tag_numr   rN   )openpyxlr   ro   
sheetnamesry   itemsr6   r   nowstrftimegetattrr,   r   r   rs   r   r   rp   r~   r`   rl   rr   rh   splitr]   valuesjsondumpsisdigitr\   close) pathr   r   wbru   rv   kindr   vvocab_ci	seen_tagspreparedr   r   
sheet_namert   
header_rowr   statsrow_idxrU   r   r   extra	any_valuer   fieldrk   r   val	canonicalr   s                                   @r0   _parse_workbookr     s     '	ttt	<B} +,1B,,BMM)'=(9:E !&
 
f 0A1779a<00
 
 $<<>223FG&R'$*ERT,1KKM:DAqaQi: "
 			> -- V	+J DDJB"22"6J!x ''&B!"A2) 
 'R"#QHE )LLaTLJ$q.!* D( *66A!R%66)3~&!	")--/ -JC}s3x !#c(+D (.t4 !N!+W!'K!<d3BiI !&t,2J#ZZR8$@  $I''48<ekk#q1!45(,u)-* !/*a/*k"a'"|$)$[):J "f]&;CR&@B)#<j #89S>IK ",g!6	#$< AKE4 !-C $::!*u}}S#/F"FG htn3355  ( 2 2399; ?I (1u|,1,)+ =$}}S#67'#s3BxA#A( 9>4::e#42x 14SXDz"'j!Q&!z"a'"ID(J ?#u_'==#8##E*mV	+p 	
&(""{ - 1
 ;V 7J 	
sa   Q 
P&2Q P0%P+=P0AQ P7/B.Q 
P='F3Q B5Q &Q +P00Q Qc                 >   t        | |      \  }}}t                t        j                         j	                  d      }t
        5  t               5 }|j                  d       |j                  d       |j                  d|D cg c]P  }|d   |d   |d   |d   |d	   |d
   |d   |d   |d   |d   |d   |d   |d   |d   |d   |d   t        |      ||fR c}       |j                  d|j                         D 	
cg c]  \  }}	t        |	      D ]
  \  }
}|||
f  c}}
}	}       ddd       ddd       t        d|d          t        d|d          t        dt        j                  |             |S c c}w c c}}
}	}w # 1 sw Y   ]xY w# 1 sw Y   axY w)u   Full-replace import of the Asset File workbook. Returns the report dict
    (also persisted to meta.last_import_report). Parse-level exceptions are
    flagged, never silently fixed or dropped — see _parse_workbook.r   zDELETE FROM assetszDELETE FROM vocabaY  INSERT INTO assets (asset_tag, tag_num, asset_type, description,
                   area, form_id, length, width, height, manufacturer,
                   model_no, serial_no, notes, material, source_sheet, extra,
                   sp_hash, created_at, updated_at)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)r   r   r	   r   r   r
   r   r   r   r   r   r   r    r!   r&   r|   z@INSERT OR IGNORE INTO vocab (kind, value, sort) VALUES (?, ?, ?)Nlast_import_atr   last_import_sourcer   last_import_report)r   rL   r   r   r   rJ   r1   r-   executemanyr   r   r   rQ   r   r   )r   r   rv   r   r   r   r/   rK   r   r   irP   s               r0   import_workbookr   D  s    .dK@E68
,,.
!
!"5
6C	 1hj 1D)*()S  
  na
mQ|_a>Ni9q{AgJ(:+'

mQ~.(Xa[3	  	! 	N!&0 0v&v.0E E1 0 0	11 1( vm45!6-#89!4::f#56M 0#1 1 1 1sC   F3FAE:(F?"E?!
F+F:FF	FFc                     t        t        ||             }|j                  dd      }	 |rt        j                  |      ni |d<   |j                  dd        |j                  dd        |S # t
        $ r i |d<   Y 6w xY w)Nr   rN   tag_numr>   )dictzippopr   loads
ValueError)rU   columnsassetr   s       r0   _row_to_assetr   i  s{    Wc"#EIIgr"E.3E*g 
IIi	IIiL	  gs   A+ +A<;A<c                    |D ]  }g |d<   	 |D ci c]  }|d   |
 }}|s|S dj                  dt        |      z        }| j                  d| dt        |            D ]  \  }}||   d   j	                  |        |S c c}w )Nphotosr   ,?z;SELECT asset_tag, filename FROM photos WHERE asset_tag IN (z) ORDER BY asset_tag, sort)r}   r   r-   listrs   )r/   assetsaby_tagplaceholdersr   filenames          r0   _attach_photosr
  u  s     ()/0Aana0F088C#f+-.L$$0>1KML /X 	sH$$X.	/
 M 1s   Bc                    g g }}| rrdt        |       j                         z   dz   }	|j                  ddj                  d t        D              z   dz          |j                  |	gt        t              z         d|fd|fd|fd	|ffD ]-  \  }
}|s	|j                  |
 d
       |j                  |       / |rddj                  |      z   nd}t        dt        t        |xs d      d            }t        dt        |xs d            }t               5 }|j                  d|z   |      j                         d   }|j                  d|z   dz   |||gz         }|j                  D cg c]  }|d   	 }}t        ||j                         D cg c]  }t!        ||       c}      }ddd       dS c c}w c c}w # 1 sw Y   xY w)zFiltered asset page: {"total": n, "assets": [...]} ordered by tag.

    ``q`` is a case-insensitive substring match across tag/description/
    manufacturer/model/serial/form id/notes; the other filters are exact.%( OR c              3   &   K   | ]	  }| d   ywz LIKE ? COLLATE NOCASENr   r   r   s     r0   r   z search_assets.<locals>.<genexpr>        'C-.qc'('C   )r	   r   r
   r&    = ? WHERE  AND rN   r=   2   r   r   SELECT COUNT(*) FROM assetszSELECT * FROM assetsz> ORDER BY tag_num IS NULL, tag_num, asset_tag LIMIT ? OFFSET ?N)totalr  )r,   r^   rs   r}   _SEARCH_COLUMNSextendr   maxminr\   r1   r-   rT   r   r
  fetchallr   )qr	   r   r
   r   limitoffsetwhereparamsliker8   rP   clauser/   r  curdr   rK   r  s                       r0   search_assetsr)    s    6ESV\\^#c)S6;; 'C2A'C C CEHI 	Jtfs?334'4vtn$g.0GI !LLF84)MM% 	!
 38i',,u--RF3s5;B'-.EC!$%F	 	Gt)F2F<<DHJqJll"V+LMeV_$& "%1A1Q411cllnE=G,EG	G f-- 2E	G 	Gs+   AF>'F43F>F9 F>4
F>>Gc           	      F   t               5 }|j                  dt        |       j                         f      }|j	                         }|s
	 d d d        y t        ||j                  D cg c]  }|d   	 c}      }t        ||g      d   cd d d        S c c}w # 1 sw Y   y xY w)N(SELECT * FROM assets WHERE asset_tag = ?r   )r1   r-   r,   r^   rT   r   r   r
  )r   r/   r'  rU   r(  r   s         r0   	get_assetr,    s    	 0tll6S9I8KMlln0 0 c#//#BQAaD#BCdUG,Q/0 0 $C0 0s#   >BB&B2BBB c                 0   t         5  t               5 }|j                  dt        |       f       |j	                  dt        |      D cg c]  \  }}t        |       ||f c}}       ddd       ddd       yc c}}w # 1 sw Y   xY w# 1 sw Y   yxY w)z@Replace the photo index rows for one asset (files live on disk).z&DELETE FROM photos WHERE asset_tag = ?IINSERT OR IGNORE INTO photos (asset_tag, filename, sort) VALUES (?, ?, ?)N)rJ   r1   r-   r,   r   r   )r   	filenamesr/   r   r   s        r0   
set_photosr0    s    	 Ghj GD=C{K 09)0DEWQc#ha E	GG G G
 FG G G Gs4   B7B 	A:!B )B:B  B		BBc                     t               5 }|j                  dt        |       f      D cg c]  \  }| c}cddd       S c c}w # 1 sw Y   yxY w)z&Ordered photo filenames for one asset.z=SELECT filename FROM photos WHERE asset_tag = ? ORDER BY sortNr1   r-   r,   )r   r/   r   s      r0   
photos_forr3    sQ    	 t"llKXK dq   s   AAAAAc                 ^   |syt         5  t               5 }|j                  dt        |       f      j	                         d   }|j                  dt        |      D cg c]  \  }}t        |       |||z   f c}}       ddd       ddd       yc c}}w # 1 sw Y   xY w# 1 sw Y   yxY w)z:Append photo index rows after the asset's existing photos.NzBSELECT COALESCE(MAX(sort), -1) + 1 FROM photos WHERE asset_tag = ?r   r.  )rJ   r1   r-   r,   rT   r   r   )r   r/  r/   baser   r   s         r0   
add_photosr6    s    	 Nhj ND||#%(XK119A? 	 7@7KLGAtc#hdQh'L	N	N N N MN N N Ns5   B#ABB8B B#BB 	B##B,c           	          t         5  t               5 }|j                  dt        |       t        |      f      }|j                  dkD  cddd       cddd       S # 1 sw Y   nxY wddd       y# 1 sw Y   yxY w)zDrop one photo index row; returns True when a row was removed.

    The caller owns deleting the file on disk (it may want to keep it).z7DELETE FROM photos WHERE asset_tag = ? AND filename = ?r   N)rJ   r1   r-   r,   rowcount)r   r	  r/   r'  s       r0   remove_photor9    sk     
  hj  DllEXs8}%' ||a	             s"   A/5A	A/A#	A//A8c                      t               5 } | j                  d      j                         d   cd d d        S # 1 sw Y   y xY w)NzSELECT COUNT(*) FROM photosr   rS   r.   s    r0   photo_countr;    =    	 It||9:CCEaHI I I	   "7A c                     t               5 }|j                  d| f      D cg c]  \  }| c}cddd       S c c}w # 1 sw Y   yxY w)z9Controlled vocabulary for one kind, in Master Data order.z4SELECT value FROM vocab WHERE kind = ? ORDER BY sortN)r1   r-   )r   r/   r   s      r0   vocab_valuesr?    sU    	 Nt"llBTGM Ndq NN NNN Ns   =8==Ac            	         i } t               5 }dD ]i  \  }}t        |      }|j                  d| d| d      D ch c]  \  }| }}t        |t	        |      z
        }|D cg c]	  }||v s| c}|z   | |<   k |j                  d      D cg c]  \  }| c}| d<   ddd       | S c c}w c c}w c c}w # 1 sw Y   | S xY w)zDistinct filter values for the UI: vocab order first, then any extra
    values present in the data (so imported-verbatim outliers stay findable).r   zSELECT DISTINCT z FROM assets WHERE z != ''zWSELECT DISTINCT source_sheet FROM assets WHERE source_sheet != '' ORDER BY source_sheetr   N)r1   r?  r-   sortedset)optionsr/   r   r8   orderedr   presentextrass           r0   filter_optionsrG    s     G	 
@t5 	JLD&"4(G%)\\"6(*=fXVL&N OTaq OG OGc'l23F(/@11<Q@6IGDM	J +/,,>+? @$1A @
@ NO A@
@ Ns:   .B9B*B9#	B/-B/1B9B4B9*B99Cc                      t               5 } | j                  d      j                         d   cd d d        S # 1 sw Y   y xY wNr  r   rS   r.   s    r0   asset_countrJ    r<  r=  TypeAreaTeamzForm ID)r	   r   r&   r
   c                    g g }}| rrdt        |       j                         z   dz   }|j                  ddj                  d t        D              z   dz          |j                  |gt        t              z         d|fd|fd|fd	|ffD ]-  \  }}	|	s	|j                  | d
       |j                  |	       / |rddj                  |      z   nd}
|
|fS )u   Shared WHERE builder for the reporting helpers — mirrors search_assets'
    case-insensitive substring ``q`` + exact type/area/form/sheet filters.
    Returns ``(clause, params)`` where clause is ``""`` or ``" WHERE ..."``.r  r  r  c              3   &   K   | ]	  }| d   ywr  r   r  s     r0   r   z'_asset_filter_clause.<locals>.<genexpr>  r  r  r  r	   r   r
   r&   r  r  r  rN   )r,   r^   rs   r}   r  r  r   )r   r	   r   r
   r   r#  r$  r%  r8   rP   r&  s              r0   _asset_filter_clauserP    s    
 6ESV\\^#c)S6;; 'C2A'C C CEHI 	Jtfs?334'4vtn$g.0GI !LLF84)MM% 	!
 38i',,u--RF6>r2   c                    | t         vrt        d|       t        |||||      \  }}t               5 }|j	                  d|z   |      j                         d   }	|j	                  dj                  | |      |      j                         }
ddd       
D cg c]
  \  }}||d }}}|j                  d        	|d	S # 1 sw Y   8xY wc c}}w )
zAsset counts grouped by one field (Type / Area / Team / Form ID),
    highest count first. Blank values are bucketed under "(blank)" so a data
    gap is visible rather than dropped. Returns
    ``{"total": n, "groups": [{"value", "count"}, ...]}``.zunknown group field: r  r   z^SELECT CASE WHEN {0} = '' THEN '(blank)' ELSE {0} END AS v, COUNT(*) FROM assets{1} GROUP BY vN)rP   countc                 4    | d    | d   j                         fS )NrR  rP   )r6   )gs    r0   <lambda>zgroup_counts.<locals>.<lambda>3  s    '
{AgJ,<,<,>? r2   )rO   )r  groups)	GROUP_FIELDSr   rP  r1   r-   rT   formatr  sort)r   r   r	   r   r
   r   r&  r$  r/   r  rowsr   r   rV  s                 r0   group_countsr[  "  s     L e=>>)!ZwNNFF	 t)F2F<<DHJqJ||228&2G HJ 	 48841aA&8F8
KK?K@f--  9s   ACCC
c                   	
 t        | ||||      \  }
||rdndz   t               5 		j                  d|z   
      j                         d   }	
fd} |d       |d       |d       |d	      d
}ddd       dS # 1 sw Y   xY w)zData-completeness scan: how many assets are missing key fields
    (Asset Type / Area / Serial # / photo). Flag, not fix. Returns
    ``{"total": n, "missing": {asset_type, area, serial_no, photo}}``.r  r  r  r   c                 V    j                  dz   | z         j                         d   S rI  )r-   rT   )condr/   r$  prefixs    r0   misszcompleteness.<locals>.missA  s2    <<-6= 
1& &r2   zasset_type = ''z	area = ''zserial_no = ''zHNOT EXISTS (SELECT 1 FROM photos p WHERE p.asset_tag = assets.asset_tag))r	   r   r   photoN)r  missing)rP  r1   r-   rT   )r   r	   r   r
   r   r&  r  r`  rb  r/   r$  r_  s            @@@r0   completenessrc  7  s     *!ZwNNFF&wi8F	 
t)F2F<<DHJqJ	& 01%./ C D	

  w//!
 
s   ABB)r   r	   r   r   r
   r   r   r   r   r   r   r    r!   zAdded in Togenc                     | dvs|s|S t        |       D ]'  }|j                         |j                         k(  s%|c S  |S )zCase-drift vocab match -> canonical casing (same rule as the importer);
    unknown values are kept verbatim, never rejected (flag-not-fix).)r	   r   r
   )r?  r6   )r   rP   r   s      r0   _canonical_vocabre  _  sH     55U%  779%H Lr2   c                 
   t                i }t        D ](  }|| v s| |   t        |t        | |               ||<   * d| v }d}|r!| j	                  d      }|dvr	 t        |      }|rt        |      nd}t        j                         j                  d      }t        5  t               5 }|rV|j                  d|f      }	|	j                         }
|
sddd|z  fcddd       cddd       S t        t!        |	j"                  D cg c]  }|d	   	 c}|
            }|j	                  d
|d
         }|s	 ddd       ddd       y||d
   k7  r;|j                  d|f      j                         rddd|z  fcddd       cddd       S i }|j%                         D ]  \  }}|||   k7  s||   |d||<   |||<     |r|j                  ddj'                  d t(        D              z   dz   t(        D cg c]  }||   	 c}|j+                         rt        |      nd||d   gz          ||k7  rX|j                  d||f       t,        |z  }t,        |z  }|j/                         r"|j1                         s	 |j3                  |       |rx||j	                  d      k7  rd|3|j                  d|f      j                         s	 ddd       ddd       y|j	                  d      |d|d<   |j                  d|||d   f       |}n|j	                  d
d      }|s	 ddd       ddd       y|j                  d|f      j                         rddd|z  fcddd       cddd       S t(        D ci c]  }|d }}|j7                  |       t8        |d<   |j                  ddj'                  t(              z   dz   dj'                  dt;        t(              z        z   dz   t(        D cg c]  }||   	 c}|j+                         rt        |      nd||gz          |j%                         D ci c]  \  }}|r|d|d }}}|rQ|O|j                  d|f      j                         s	 ddd       ddd       y|j                  d||f       d|d|d<   |}ddd       ddd       t=              dfS # t        t        f$ r Y yw xY wc c}w c c}w # t4        $ r Y Yw xY wc c}w c c}w c c}}w # 1 sw Y   ^xY w# 1 sw Y   bxY w)u  Create (``original_tag`` falsy) or update one asset.

    Only the EDITABLE_FIELDS present in ``fields`` are written (partial
    update); tag renames are allowed when the new tag is free. Returns
    ``(asset, changes, error)`` — ``changes`` maps field -> {"from", "to"}
    for the audit trail; on validation failure asset/changes are None and
    ``error`` is a user-facing message.
    NrH   )NrN   r   0)NNzInvalid asset model.rN   r   r+  u.   Asset %s no longer exists — reload the list.r   r   )NNzAsset Tag # is required.(SELECT 1 FROM assets WHERE asset_tag = ?zAsset tag %s is already in use.fromtozUPDATE assets SET z, c              3   &   K   | ]	  }d |z    yw)z%s = ?Nr   )r   r   s     r0   r   zsave_asset.<locals>.<genexpr>  s     AqhlAr  z*, tag_num = ?, updated_at = ? WHERE id = ?idz3UPDATE photos SET asset_tag = ? WHERE asset_tag = ?'SELECT 1 FROM asset_models WHERE id = ?)NNzUnknown asset model.z;UPDATE assets SET model_id = ?, updated_at = ? WHERE id = ?r&   zINSERT INTO assets (z2, tag_num, extra, created_at, updated_at) VALUES (r  z, ?, '', ?, ?)z2UPDATE assets SET model_id = ? WHERE asset_tag = ?)rL   EDITABLE_FIELDSre  r`   rr   r\   	TypeErrorr   r   r   r   rJ   r1   r-   rT   r   r   r   r   r}   r~   r   r   is_direxistsrenameOSErrorupdateCREATED_SHEETr   r,  )fieldsoriginal_tagcleanr   model_provided	model_valrawr   r/   r'  rU   r(  rw   new_tagchangesrP   r   old_dirnew_dir	final_tagr   r   r   s                          r0   
save_assetr  j  s    E  JF?ve}8+E6&-3HIE%LJ
  6)NIjj$((:H	 ,86,'RL
,,.
!
!"5
6C	 Phj PD,,:\OMC,,.CTD|SUP P P 3coo>!>DEGiiW[-ABG=P P P '+..4<<>J4  (
4+ T#Dw#NNP P P  G % +uGEN*.5enE%JGEN%*GEN+ (IIALAAB@A *66AWQZ6%,__%6S\D#T]$$	% l* LLM ,/1 /=G.8G~~'0@!#NN73 )w{{:/F"F(A"2&&.hj21 >]P P P^ 07{{:/F-6'8
#QWT]35  I))K,C=qP P Pr ||F F$$,HJ/T#Ds#JJwP P Px &22ae2F2MM% %2F>"LL&<)@@"iic,.?(?@ACST %11q1 [[]Sc3?@	A $);;=71aA 2Q// 7G 7)"7||A"&&.hj1 >WP P PX H$& 049&E
#IaP Pb Y$..m z* :9:  ?$ 7  ' ! !( 3 27KP P P Ps   R4 S?*0S3	S?-S3S	$S35S?0S36	S?	 S3*AS32S>A6S35S:S3 S?AS3S?*(S3	S?%S3-
S#7A.S3%S(1;S3,S-
>)S3'S?8S3S?4SS	
S3	S S3S  S33S<	8S??T)r   r   r   r   )r   r   part_nor   r   min_qtylocation)r   r   sub_assembly)r  r   r   r  c                     t               5 } | j                  d      D ci c]  \  }}||
 }}}| j                  d      }|j                  D cg c]  }|d   	 }}g }|j                         D ]@  }t	        t        ||            }	|j                  |	d   d      |	d<   |j                  |	       B 	 ddd       |S c c}}w c c}w # 1 sw Y   S xY w)zlAll models ordered by name, each with the count of assets using it (so
    the UI can warn before a delete).zRSELECT model_id, COUNT(*) FROM assets WHERE model_id IS NOT NULL GROUP BY model_idz;SELECT * FROM asset_models ORDER BY name COLLATE NOCASE, idr   rm  rJ  N)r1   r-   r   r  r   r   rr   rs   )
r/   midncountsr'  r(  r7   outrU   ms
             r0   list_asset_modelsr    s     
 t'+||<(= >VS!#q& > > llIK!oo.!..<<> 	CSs^$A%zz!D'15AmJJqM	 J>
 / Js(   CB="CCAC=CCc           
      L   	 t        |       } t               5 }|j	                  d| f      }|j                         }|s
	 d d d        y t        t        |j                  D cg c]  }|d   	 c}|            cd d d        S # t        t        f$ r Y y w xY wc c}w # 1 sw Y   y xY w)N'SELECT * FROM asset_models WHERE id = ?r   	r\   rp  r   r1   r-   rT   r   r   r   )rH   r/   r'  rU   r(  s        r0   get_asset_modelr    s    x= 
 ?tll5{Dlln? ? Cs7!17=>? ? z"  8? ?4   B  'BBB+B BBBB#c                    t                t        D ci c]  }|t        | j                  |d              }}|d   syt	        j
                         j                  d      }t        5  t               5 }|r|j                  dt        |      f      }|j                         }|s	 ddd       ddd       yt        t        |j                  D cg c]  }|d   	 c}|            }	i }
t        D ]$  }||   |	|   k7  s|	|   ||   d	|
|<   ||   |	|<   & |
r5|j                  d
t        D cg c]  }|	|   	 c}|t        |      gz          t        |      }na|j                  dt        D cg c]  }||   	 c}||gz         }|j                  }|j!                         D ci c]  \  }}|s	|d|d	 }
}}ddd       ddd       t#              
dfS c c}w c c}w c c}w c c}w c c}}w # 1 sw Y   9xY w# 1 sw Y   =xY w)u   Create (``model_id`` falsy) or update a model. Returns
    ``(model, changes, error)`` — ``changes`` maps field -> {from,to}.rN   r   )NNzModel name is required.r   r  N)NNu+   Model no longer exists — reload the list.r   ri  znUPDATE asset_models SET name = ?, manufacturer = ?, model_no = ?, description = ?, updated_at = ? WHERE id = ?zvINSERT INTO asset_models (name, manufacturer, model_no, description, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?))rL   _MODEL_FIELDSr`   rr   r   r   r   rJ   r1   r-   r\   rT   r   r   r   	lastrowidr   r  )rw  rH   r   ry  r   r/   r'  rU   r(  rw   r~  new_idr   s                r0   save_asset_modelr  	  s    3@AaQvzz!R())AEA=4
,,.
!
!"5
6C	 Phj PD,,9CM;KMC,,.CPP P P 3coo>!>DEGG" *8wqz)*1!*E!H!EGAJ!&qGAJ* R)67AWQZ73H:NNP ]F,,R#01aq1S#J>@C ]]F<AKKMODAqQq2Q//OGO3P P4 6"GT11= B  ? 8 2O3P P P Psw   #G'G<22G0$G<5G0G"G0<.G0*G 65G0+G%7+G0"
G*
-G*
5G08G<G00G9	5G<<Hc                 "   t                	 t        |       } t        5  t               5 }|j                  d| f      j                         s	 ddd       ddd       y|j                  d| f      j                         d   }|r"dd||dk(  rd	nd
fz  fcddd       cddd       S |j                  dt        |       f       |j                  d| f       ddd       ddd       y# t        t        f$ r Y yw xY w# 1 sw Y   'xY w# 1 sw Y   yxY w)u   Delete a model (and its model-scoped sub-assemblies). Refuses while any
    asset still references it (flag-not-fix) — returns ``(ok, error)``.)FzModel no longer exists.rn  Nz.SELECT COUNT(*) FROM assets WHERE model_id = ?r   Fu8   %d asset%s still use this model — reassign them first.r=   rN   szIDELETE FROM sub_assemblies WHERE parent_kind = 'model' AND parent_ref = ?z%DELETE FROM asset_models WHERE id = ?)TN)	rL   r\   rp  r   rJ   r1   r-   rT   r,   )rH   r/   r  s      r0   delete_asset_modelr  .  s4    0x= 
 Khj KD||9H;HHP
S3K K K LL<xk

(*Q JAFb,-.K K K 	"$'M#3	5 	<xkJK K # z" 0/0K K K sK   C$ D#C9D5C9	D$/C9D$C65C69D	>DDc           
      L   	 t        |       } t               5 }|j	                  d| f      }|j                         }|s
	 d d d        y t        t        |j                  D cg c]  }|d   	 c}|            cd d d        S # t        t        f$ r Y y w xY wc c}w # 1 sw Y   y xY w)N)SELECT * FROM sub_assemblies WHERE id = ?r   r  )sub_idr/   r'  rU   r(  s        r0   get_sub_assemblyr  H  s    V 
 ?tll7&Dlln? ? Cs7!17=>? ? z"  8? ?r  c           
      >   | t         vrg S t               5 }|j                  d| t        |      f      }|j                  D cg c]  }|d   	 }}|j                         D cg c]  }t        t        ||             c}cddd       S c c}w c c}w # 1 sw Y   yxY w)zGSub-assemblies for one parent (asset tag or model id), ordered by sort.zWSELECT * FROM sub_assemblies WHERE parent_kind = ? AND parent_ref = ? ORDER BY sort, idr   N)_SUB_ASSEMBLY_PARENTSr1   r-   r,   r   r  r   r   )parent_kind
parent_refr/   r'  r(  r7   rK   s          r0   list_sub_assembliesr  V  s    //		 <tll!#.J"@B "oo.!..,/LLN;qSq\";< < /;< <s)   ,BB	B!B=B	
BBc                    t        |       }|sg g ddS t        d|d         }|D ]  }d|d<   	 g d}}|j                  d      }|r?t        |      }|r2t        dt	        t        |                  }|D ]  }d	|d<   |d
   |d<    |||dS )z{own, inherited, model} for an asset detail (D7): its own sub-assemblies
    plus those inherited from its model (each flagged ``inherited``).N)own	inheritedr   r   r   Fr  rH   r   Tr   
model_name)r,  r  rr   r  r,   r\   )r   r   r  r  r  r   r  s          r0   sub_assemblies_for_assetr  b  s     cNET::
gu['9
:C +4uI
))J
C
$+GSS]CI 0!%+"'-,0 Y??r2   c                    t                t        | j                  dd            }t        | j                  dd            }|syd| v xr/ t        | j                  d      xs d      j	                         dk7  }d}|r	 t        | j                  d            }t        j                         j                  d      }t        5  t               5 }|r|j                  d	t        |      f      }|j                         }	|	s	 d
d
d
       d
d
d
       yt        t!        |j"                  D 
cg c]  }
|
d   	 c}
|	            }||d}|r||d<   i }|j%                         D ]  \  }}|||   k7  s||   |d||<   |||<     |r)|j                  d|d   |d   |d   |t        |      f       t        |      }n.| j                  d      }t        | j                  dd            }|t&        vs|s	 d
d
d
       d
d
d
       y|dk(  r3|j                  d|f      j                         sc	 d
d
d
       d
d
d
       y	 t        t        |            }|j                  dt        |      f      j                         s	 d
d
d
       d
d
d
       y|s%|j                  d||f      j                         d   }|j                  d|||||||f      }|j(                  }dd|di}|rd|d|d<   d
d
d
       d
d
d
       t+              d
fS # t        t        f$ r d}Y ~w xY wc c}
w # t        t        f$ r Y d
d
d
       d
d
d
       yw xY w# 1 sw Y   axY w# 1 sw Y   exY w)zCreate or update a sub-assembly. On create ``parent_kind``/``parent_ref``
    are required and validated; on update the parent is immutable. Returns
    ``(sub, changes, error)``.r   rN   r   )NNzSub-assembly name is required.rY  r   Fr   r  N)NNSub-assembly no longer exists.)r   r   ri  zZUPDATE sub_assemblies SET name = ?, description = ?, sort = ?, updated_at = ? WHERE id = ?r  r  )NNz2A sub-assembly must attach to an asset or a model.r   rh  )NNzAsset not found.)NNzModel not found.rn  z_SELECT COALESCE(MAX(sort), -1) + 1 FROM sub_assemblies WHERE parent_kind = ? AND parent_ref = ?zINSERT INTO sub_assemblies (name, parent_kind, parent_ref, description, sort, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?))rL   r`   rr   r,   r^   r\   rp  r   r   r   r   rJ   r1   r-   rT   r   r   r   r   r  r  r  )rw  r  r   descsort_providedsort_valr   r/   r'  rU   r(  rw   newr~  r   r   r  r  r  s                      r0   save_sub_assemblyr  w  s    &**VR()D&**]B/0D;f$TVZZ-?-E2)F)L)L)NRT)TMH	"6::f-.H ,,.
!
!"5
6C	 8Bhj 8BD,,;c&k^MC,,.CC8B 8B 8B 3coo>!>DEG5C&FG		 #1
?*1!*A!>GAJ!"GAJ# =V_gm&<gfo#f+'(
 [F **]3K

< <=J"77zJ78B 8B 8B: g%||B#''/xz2 :C8B 8B 8BF:!$S_!5J ||AZ*,,4HJ7 :U8B 8B 8BV ! LL@ *-/ 08xz!= ,,0 {JhSI	KC
 ]]F$78G24D)A&q8B 8Br F#Wd22y :& 	"!M	"  ?< ":. :9M8B 8B 8BJ:K8B 8B 8B 8Bs   >L M2M>MM'L38M,BM-M>'M%M7L+M6MAM$MLLMM.M/M MMM		MMc                    t                t        5  t               5 }|j                  dt	        |       f      }|j                         }|s	 ddd       ddd       yt        t        |j                  D cg c]  }|d   	 c}|            }|j                  dt	        |       f       ddd       ddd       dfS c c}w # 1 sw Y   xY w# 1 sw Y   dfS xY w)zlDelete one sub-assembly. Returns ``(record, error)`` (record is the
    deleted row, for the audit summary).r  N)Nr  r   z'DELETE FROM sub_assemblies WHERE id = ?	rL   rJ   r1   r-   r\   rT   r   r   r   )r  r/   r'  rU   r(  r   s         r0   delete_sub_assemblyr    s     	 Phj PDll7#f+Illn9P P P c9A1Q493?@>VOP P 4< :P P P 4<@   C0CCC5B=(C)C=CC	CCc                 >   | |S t        | t              r|S t        | t        t        f      r	 t        |       S t        j                  dt        |             }|s|S 	 t        |j                  d            S # t        t
        f$ r |cY S w xY w# t
        $ r |cY S w xY w)zParse a quantity to a float (signed). Blank/garbage -> default. Tolerant
    of stray text ('2 ea', ' 3.5 ') by taking the leading numeric token, so a
    WO part's free-text qty still decrements stock.z\s*(-?\d+(?:\.\d+)?)r=   )
rY   boolr\   rZ   rp  r   rb   r   r,   group)rP   defaultr  s      r0   _coerce_qtyr    s     }%%#u&	< 	(#e*5AQWWQZ   :& 	N	  s#   
A7 B 7B
BBBc                 ~    	 t        |       }|t        |      k(  rt        |      S |S # t        t        f$ r | cY S w xY w)zFREAL -> int when whole (so on_hand renders '5' not '5.0'), else float.)rZ   rp  r   r\   )rP   r   s     r0   _numr    sF    %L #a&[3q6'a' z" s   ( <<c                 v   t        t        ||             }t        |j                  dd            }t        |j                  dd            }||d<   ||d<   t	        |j                  d      xs d      dk  }t	        |xs d      dkD  xr t	        |xs d      t	        |      k  }||d<   ||d<   |xs ||d<   |S )u.  Shape a parts row: numeric on_hand/min_qty + below_min/negative flags.

    negative  = on_hand < 0 (over-consumed). below_min = a reorder point is set
    (min_qty > 0) and on_hand has reached/fallen below it. `low` = either — the
    digest/badge signal. Flags only; nothing here blocks a movement.on_handr   r  negative	below_minlow)r   r   r  rr   rZ   )rU   r7   r   r  r  r  r  s          r0   	_part_rowr    s     	Ss^A155A&'G155A&'GAiLAiLQUU9%*+a/Hgl#a'QE',Q,?5>,QIAjMAkN$9AeHHr2   c           
         t                t        | t              r| syt        j                         j                  d      }d}t        5  t               5 }|j                  d      D ch c]  }|d   	 }}| j                         D ]L  \  }}t        |      }|r||v r|j                  d|t        |      ||f       |j                  |       |dz  }N 	 ddd       ddd       |S c c}w # 1 sw Y   xY w# 1 sw Y   |S xY w)u  Seed the parts catalog from an external {part_no: name} map (the app's
    rpm_known_parts.json). Idempotent: inserts only part numbers not already
    present — never clobbers an edited name or existing stock. Returns the
    number of catalog rows added.r   r   zSELECT part_no FROM partszWINSERT OR IGNORE INTO parts (part_no, name, created_at, updated_at) VALUES (?, ?, ?, ?)r=   N)rL   rY   r   r   r   r   rJ   r1   r-   r   r`   add)	catalogr   addedr/   rK   existingr  r   pns	            r0   seed_parts_catalogr  	  s    
 gt$G
,,.
!
!"5
6CE	 
hj 
D"&,,/J"KLQAaDLL$]]_ 	MGTBxLL357sC4PR LLQJE	
 
 L M
 
 
 Ls7   	C9C-(C(4A!C-C9(C--C6	2C99Dc                      t               5 } | j                  d      j                         d   cd d d        S # 1 sw Y   y xY w)NSELECT COUNT(*) FROM partsr   rS   r.   s    r0   
part_countr  !  s=    	 Ht||89BBDQGH H Hr=  c           	      :   	 t        |       } t               5 }|j	                  d| f      }|j                         }|s
	 d d d        y t        ||j                  D cg c]  }|d   	 c}      cd d d        S # t        t        f$ r Y y w xY wc c}w # 1 sw Y   y xY w)N SELECT * FROM parts WHERE id = ?r   )r\   rp  r   r1   r-   rT   r  r   )part_idr/   r'  rU   r(  s        r0   get_partr  &  s    g, 
 ?tll=zJlln	? ?
 S__=qt=>? ? z"  >? ?s4   A7 'BBB'B7B	B	BBc           	         t        |       }|sy t               5 }|j                  d|f      }|j                         }|s
	 d d d        y t	        ||j
                  D cg c]  }|d   	 c}      cd d d        S c c}w # 1 sw Y   y xY w)Nz%SELECT * FROM parts WHERE part_no = ?r   )r`   r1   r-   rT   r  r   )r  r  r/   r'  rU   r(  s         r0   get_part_by_nor  3  s    	B	 ?tllBREJlln	? ?
 S__=qt=>? ?
 >? ?s#   'A>	A>A9)A>9A>>BFc           	      (   g g }}| rrdt        |       j                         z   dz   }|j                  ddj                  d t        D              z   dz          |j                  |gt        t              z         |r|j                  d       |rddj                  |      z   nd	}t        d
t        t        |xs d      d            }t        dt        |xs d            }t               5 }|j                  d|z   |      j                         d   }	|j                  d|z   dz   |||gz         }
|
j                  D cg c]  }|d   	 }}|
j                         D cg c]  }t        ||       }}ddd       	dS c c}w c c}w # 1 sw Y   xY w)zFiltered parts page: {"total": n, "parts": [...]}. `low_stock` restricts
    to parts flagged below-min or negative. Ordered by part_no.r  r  r  c              3   &   K   | ]	  }| d   ywr  r   r  s     r0   r   zlist_parts.<locals>.<genexpr>E  s      'H-.qc'('Hr  r  z5(on_hand < 0 OR (min_qty > 0 AND on_hand <= min_qty))r  r  rN   r=   d   r   r   r  zSELECT * FROM partsz1 ORDER BY part_no COLLATE NOCASE LIMIT ? OFFSET ?N)r  r   )r,   r^   rs   r}   _PART_SEARCH_COLUMNSr  r   r  r  r\   r1   r-   rT   r   r  r  )r   	low_stockr!  r"  r#  r$  r%  r&  r/   r  r'  r(  r7   rK   r   s                  r0   
list_partsr  ?  s    6ESV\\^#c)S6;; 'H2F'H H HJMN 	Otfs#7889LM27i',,u--RF3s5<C(#./EC!$%F	 =t(616;;C8:aIll!F*?@eV_$& "oo.!..-0\\^<1d#<<= U++ /<= =s+   *AF<E>FF/F>
FFc                    t               5 }|j                  dt        |       f      }|j                  D cg c]  }|d   	 }}|j	                         D cg c]  }t        ||       c}cddd       S c c}w c c}w # 1 sw Y   yxY w)u   Parts flagged below-min or negative, most-negative-margin first — the
    Assets low-stock digest + the P5 reorder-notification source.zSELECT * FROM parts WHERE on_hand < 0 OR (min_qty > 0 AND on_hand <= min_qty) ORDER BY (on_hand - min_qty), part_no COLLATE NOCASE LIMIT ?r   N)r1   r-   r\   r   r  r  )r!  r/   r'  r(  r7   rK   s         r0   low_stock_partsr  Y  s     
 <tllL ZM	
 "oo.!..,/LLN;q	!T";< < /;< <s(   +A?A5A?A:)A?5
A??Bparts_notify_configrN         8@enabledemailsteams_webhookcooldown_hoursc            	      B   t        t              } g | d<   t        t              }|r	 t	        j
                  |      }t        |t               rt        |j                  d            | d<   |j                  d      xs g D cg c]  }t        |t              r| c}| d<   t        |j                  dd            | d<   	 t        dt        |j                  dd                  | d<   | S | S # t        t        f$ r d}Y w xY wc c}w # t        t        f$ r	 d	| d<   Y | S w xY w)
zThe low-stock notification config (recipients, optional Teams webhook,
    per-part cooldown hours, enabled). Stored as a JSON meta row; a missing/bad
    value returns the defaults so the feature is off until an admin configures
    it.r  Nr  r  rN           r     r  )r   _PARTS_NOTIFY_DEFAULTSrV   _PARTS_NOTIFY_KEYr   r   r   rp  rY   r  rr   r,   r`   r  rZ   )cfgr|  dataes       r0   get_parts_notify_configr  s  s%   
 %
&CCM
$
%C
	::c?D dD!!$((9"56C	N)-();)Ar 41 *1c 2  4CM#)$((?B*G#HC -(+CHH-r224 )5$% J3J I& 	D	4 z* -(,$%J-s)   C*  D>(D *C>=C>DDc           	      &   t                g t               }}| j                  d      xs g D ]J  }t        |      j	                         }|sd|v s$||vs)|j                  |       |j                  |       L 	 t        dt        | j                  dd                  }t        | j                  d            |t        | j                  dd	            |d
}t        t        t        j                  |             |S # t        t        f$ r d}Y pw xY w)zPersist the low-stock notification config, sanitized. Emails are
    lowercased, deduped, and kept only if they look like addresses; the cooldown
    is clamped non-negative. Returns the cleaned config.r  @r  r  r  r  r  r  rN   r  )rL   rB  rr   r`   r6   rs   r  r  rZ   rp  r   r  rQ   r  r   r   )r  r  seenr  e2cooldownry  s          r0   save_parts_notify_configr    s     suDFggh%2 AY__#)$MM"HHRL	
sE#''*:B"?@A 	*+ <="	E 

5 12L z" s   :%C< <DDc                 $    dj                  |       S )zvSQL fragment: the row is low (negative or below a set reorder point).
    ``prefix`` qualifies the column (e.g. 'p.').zA({0}on_hand < 0 OR ({0}min_qty > 0 AND {0}on_hand <= {0}min_qty)))rX  )r_  s    r0   _low_predicater    s     PVF^r2   c                 4   t                t        dt        |            dz  }t               5 }|j	                  dt        d      z   dz   t        |       f      j                         }ddd       r|d   sy|d	   sy
|d   du xs |d   |k\  S # 1 sw Y   (xY w)u   True if the part is currently low AND due for a reorder alert — never
    alerted, or its last alert was at least ``cooldown_minutes`` ago. The
    per-part cooldown is what stops a below-min part re-alerting on every
    movement.r       @zSELECT p.z AS low, a.last_alert_at, (julianday('now', 'localtime') - julianday(a.last_alert_at)) AS age FROM parts p LEFT JOIN part_alerts a ON a.part_id = p.id WHERE p.id = ?Nr   Fr=   Tr   )rL   r  rZ   r1   r-   r  r\   rT   )r  cooldown_minutescd_daysr/   rU   s        r0   part_alert_duer    s    
 #u-./&8G	 ;tllt,, 0  !$G	0 19
	 	; c!fq6q6T>.SVw..; ;s   :BBc                 z   t                t        dt        |             dz  }t               5 }|j	                  dt        d      z   dz   |t        |      f      }|j                  D cg c]  }|d   	 }}|j                         D cg c]  }t        ||       c}cddd       S c c}w c c}w # 1 sw Y   yxY w)zLow-stock parts due for a reorder alert (never alerted or cooldown
    elapsed), most-negative-margin first. Feeds a digest / scheduled sweep.r  r  zJSELECT p.* FROM parts p LEFT JOIN part_alerts a ON a.part_id = p.id WHERE r  z AND (a.last_alert_at IS NULL OR a.last_alert_at = '' OR (julianday('now', 'localtime') - julianday(a.last_alert_at)) >= ?) ORDER BY (p.on_hand - p.min_qty), p.part_no COLLATE NOCASE LIMIT ?r   N)
rL   r  rZ   r1   r-   r  r\   r   r  r  )r  r!  r  r/   r'  r(  r7   rK   s           r0   low_stock_alert_candidatesr    s     #u-./&8G	 	<tll&t,-0
 !#e*-/ "oo.!..,/LLN;q	!T";	< 	< /;	< 	<s)   ;B1(B'4B1B,B1'
B11B:c           
         | syt                t        j                         j                  d      }t        5  t               5 }| D ]v  }|j                  dt        |      f      j                         }|r$t        |d   xs d      t        |d   xs d      fnd\  }}|j                  dt        |      |||f       x 	 ddd       ddd       y# 1 sw Y   xY w# 1 sw Y   yxY w)zOStamp the reorder-alert time (now) for each part id, starting its
    cooldown.Nr   z/SELECT on_hand, min_qty FROM parts WHERE id = ?r   r=   )r  r  a  INSERT INTO part_alerts (part_id, last_alert_at, on_hand_at_alert, min_qty_at_alert) VALUES (?, ?, ?, ?) ON CONFLICT(part_id) DO UPDATE SET last_alert_at = excluded.last_alert_at, on_hand_at_alert = excluded.on_hand_at_alert, min_qty_at_alert = excluded.min_qty_at_alert)
rL   r   r   r   rJ   r1   r-   r\   rT   rZ   )part_idsr   r/   pidrU   ohmqs          r0   record_low_stock_alertr    s     
,,.
!
!"5
6C	 )hj )D 	)C,,AS%XZ  BEeCFKa(%A!*<= BLL@ S3B')	)) ) ) ) ) )s$   CA<C?CC	CC%c                     t                t        5  t               5 }|j                  dt	        |       f       ddd       ddd       y# 1 sw Y   xY w# 1 sw Y   yxY w)u   Drop a part's cooldown stamp — called when it rises back above its
    reorder point so a future dip alerts immediately (no stale cooldown).z)DELETE FROM part_alerts WHERE part_id = ?N)rL   rJ   r1   r-   r\   )r  r/   s     r0   clear_low_stock_alertr    sZ     	 Shj SD@3w</RS S S S S Ss!   AA
A
A	AAc                    t                t        | j                  dd            }|sy|t        | j                  dd            t        | j                  dd            t        | j                  dd      d      t        | j                  d	d            d
}|d   dk  ryt	        j
                         j                  d      }t        5  t               5 }|j                  d|f      j                         }|r6|rt        |d         t        |      k7  rddd|z  fcddd       cddd       S |r|j                  dt        |      f      }|j                         }|s	 ddd       ddd       yt        t        |j                  D 	cg c]  }	|	d   	 c}	|            }
i }t        D ]O  }|dk(  rt!        |
|         n|
|   }||   }t#        |      t#        |      k7  s8||dk(  rt!        |      n|d||<   Q |r5|j                  dt        D cg c]  }||   	 c}|t        |      gz          t        |      }nY|j                  dt        D cg c]  }||   	 c}||gz         }|j$                  }t        D ci c]  }||   dvr	|d||   d }}ddd       ddd       t'              dfS c c}	w c c}w c c}w c c}w # 1 sw Y   3xY w# 1 sw Y   7xY w)u3  Create (``part_id`` falsy) or update a part's catalog fields (part_no,
    name, description, min_qty, location). on_hand is NEVER set here — stock only
    moves through adjust_stock / record_wo_part_usage so the ledger stays
    authoritative. Returns ``(part, changes, error)``; part_no must be unique.r  rN   )NNzPart number is required.r   r   r  r   r  r  r  )NNz$Minimum quantity cannot be negative.r   z&SELECT id FROM parts WHERE part_no = ?NzPart number %r already exists.r  )NNu*   Part no longer exists — reload the list.ri  zoUPDATE parts SET part_no = ?, name = ?, description = ?, min_qty = ?, location = ?, updated_at = ? WHERE id = ?zvINSERT INTO parts (part_no, name, description, min_qty, location, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?))rN   r   r  )rL   r`   rr   r  r   r   r   rJ   r1   r-   rT   r\   r   r   r   _PART_FIELDSr  r,   r  r  )rw  r  r  ry  r   r/   clashr'  rU   r(  rw   r~  r   cur_valnew_valr  s                   r0   	save_partr    s   
 VZZ	2./G5vzz&"-.fjj;<vzz)Q7=6::j"56E Y!A
,,.
!
!"5
6C	 8hj 8D4wjBBJ(* 	'Sq]c'l%B?'II	8 8 8
 ,,ACL?SC,,.CO8 8 8 3coo>!>DEGG! B./9n$wqz*'!*(w<3w</*1%&)^ :>g9@"BGAJ	B N'34!U1X4S\7JJL \F,,R#/0aq0C:=?C ]]F@L 81(,6 2U1X66 8G 8=8 8@ FWd**-  ? 5 18=8 8 8 8sz   KAK	K-2KK0KJ4AK 0KJ95KJ> K=KKK4KK	KKc                    t                t        |       }|syt        5  t               5 }|j	                  dt        |       f      j                         d   }|r"dd||dk(  rdndfz  fcddd       cddd       S |j	                  d	t        |       f       |j	                  d
t        |       f       ddd       ddd       |dfS # 1 sw Y   xY w# 1 sw Y   |dfS xY w)u   Delete a part. Refuses while a movement ledger exists for it (the ledger
    is the audit trail — flag-not-fix). Cascades its links. Returns
    ``(part, error)`` where part is the deleted row for the audit summary.NPart no longer exists.z5SELECT COUNT(*) FROM part_movements WHERE part_id = ?r   NuL   This part has %d stock movement%s — adjust it to zero instead of deleting.r=   rN   r  z(DELETE FROM part_links WHERE part_id = ?zDELETE FROM parts WHERE id = ?)rL   r  rJ   r1   r-   r\   rT   )r  partr/   r  s       r0   delete_partr  (  s     GD-	 	Hhj 	HDLLC\O%XZ+  #$AFb"<=>	H 	H 	H 	?#g,Q5GG	H 	H :	H 	H 	H :s.   C?C)	C<8C4CC	CC c                    t                t        |       }|syt        |t        t        f      st        |d      n
t	        |      }||dk(  ryt        j                         j                  d      }t        5  t               5 }|j                  dt        |       f      j                         }	t	        |	d   xs d      |z   }
|j                  d|
|t        |       f       |j                  dt        |       ||t        |      t        |      |
|f      j                  }ddd       ddd       t        |       t              dfS # 1 sw Y   (xY w# 1 sw Y   ,xY w)	zAppend a manual stock movement (signed ``delta``) and recompute on_hand.
    Below-min/negative are flags, never blocked. Returns ``(part, movement,
    error)``.)NNr
  Nr   )NNzEnter a non-zero adjustment.r   z&SELECT on_hand FROM parts WHERE id = ?9UPDATE parts SET on_hand = ?, updated_at = ? WHERE id = ?zxINSERT INTO part_movements (part_id, delta, kind, reason, actor, on_hand_after, created_at) VALUES (?, ?, ?, ?, ?, ?, ?))rL   r  rY   r\   rZ   r  r   r   r   rJ   r1   r-   rT   r`   r  get_part_movement)r  deltareasonactorr   r  r(  r   r/   r'  new_on_handr  s               r0   adjust_stockr  =  sF    GD3(253,(GE4 5\ yAF9
,,.
!
!"5
6C	 
hj 
Dll4s7|oGGOxz 	CFKa(1,P!3G5	7llG\1dF6NF5M; 9	 	
 
 G/4d::
 
 
 
s%   7EBEEE	
EEc                 v   t                t        |       }t        |      }t        |d      }	|	dk  r|rdnd}	|sddddS |	dk  rddddS t        j                         j                  d	      }
t        5  t               5 }|r9|j                  d
|f      j                         rddddcddd       cddd       S |j                  d|f      j                         }|,|j                  d|t        |      |
|
f      j                  }d}n t        |d         t        |d   xs d      }}||	z
  }|j                  d||
|f       |j                  d||	 t        |      |t        |      |t        |      |
f       ddd       ddd       t              }d||d   |d   ddS # 1 sw Y   *xY w# 1 sw Y   .xY w)u  Record a Work-Order part usage: resolve/create the catalog part by
    part_no, append a ``usage`` movement (delta = -qty), and decrement on_hand.

    IDEMPOTENT per ``wo_part_id`` (the stable RPM part id) — a WO's parts are
    read/re-saved often, but usage is recorded exactly once. below-min/negative
    only FLAG (never block). Returns a dict describing the outcome:
    ``{recorded, part, below_min, negative, reason}``. recorded=False (no stock
    change) when part_no is blank, qty<=0, or this wo_part_id was already logged.
    r  r   g      ?FN
no_part_no)recordedr  r  no_qtyr   z1SELECT 1 FROM part_movements WHERE wo_part_id = ?already_recordedz/SELECT id, on_hand FROM parts WHERE part_no = ?zMINSERT INTO parts (part_no, name, created_at, updated_at) VALUES (?, ?, ?, ?)r=   r  zINSERT INTO part_movements (part_id, delta, kind, reason, wo_ref, wo_part_id, asset_tag, on_hand_after, actor, created_at) VALUES (?, ?, 'usage', '', ?, ?, ?, ?, ?, ?)Tr  r  rN   )r  r  r  r  r  )rL   r`   r  r   r   r   rJ   r1   r-   rT   r  r\   rZ   r  )r  r   qtywo_ref
wo_part_idr   r  r  wpidr   r   r/   rU   r  r  r  r  s                    r0   record_wo_part_usager  X  s    	B*DCAAv C3!4<HHAv!48DD
,,.
!
!"5
6C	 "hj "DDLLC!$ !&t?QR	" " "
 ll=uFFNhj 	;,,')+VD\3(DFFOi  Gs1v;c!fk(:CkP!3,	.< 1"fVndF9,={E]C!		"#" ". C=Ddk9JZ(B8 81" " " "s1   4F/?)F#(	F/;B?F#:F/#F,	(F//F8c           
      b   t               5 }|j                  dt        |       f      }|j                         }|s
	 d d d        y t	        t        |j                  D cg c]  }|d   	 c}|            }d d d        t        d         |d<   t        |d         |d<   |S c c}w # 1 sw Y   2xY w)Nz)SELECT * FROM part_movements WHERE id = ?r   r  on_hand_after)r1   r-   r\   rT   r   r   r   r  )movement_idr/   r'  rU   r(  r  s         r0   r  r    s    	 <tll7#k:J9LNlln< < COO4qad4c:;< aj!AgJa01AoH 5< <s#   0B%B%B (B% B%%B.c                    t               5 }|j                  dt        |       t        |      f      }|j                  D cg c]  }|d   	 }}g }|j	                         D ]J  }t        t        ||            }t        |d         |d<   t        |d         |d<   |j                  |       L 	 ddd       |S c c}w # 1 sw Y   S xY w)z"Ledger for one part, newest first.zGSELECT * FROM part_movements WHERE part_id = ? ORDER BY id DESC LIMIT ?r   r  r   N)	r1   r-   r\   r   r  r   r   r  rs   )	r  r!  r/   r'  r(  r7   r  rK   r  s	            r0   list_part_movementsr#    s    	 
tll(*-g,E
)CE "oo.!.. 	ASq\"Aaj)AgJ!%a&8!9AoJJqM		
 J /	
 Js   5B= B8A!B=8B==Cc                 p   |dk(  r#| j                  d|f      j                         d uS |dk(  r-	 | j                  dt        |      f      j                         d uS |dk(  r-	 | j                  dt        |      f      j                         d uS y# t        t        f$ r Y yw xY w# t        t        f$ r Y yw xY w)Nr   rh  r   rn  Fr  z)SELECT 1 FROM sub_assemblies WHERE id = ?)r-   rT   r\   rp  r   )r/   target_kind
target_refs      r0   _link_target_existsr'    s    g||F'M++38:TB 	Bg	<< I!$Z 244<HJdK K n$	<< K!$Z 244<HJdK K  :& 		 :& 		s#   +B !+B# B B #B54B5c                    t                |t        vryt        |      }|dv r	 t        t	        |            }|syt        5  t               5 }|j                  dt	        |       f      j                         s	 ddd       ddd       yt        |||      s	 ddd       ddd       y|j                  d	t	        |       ||f       |j                  d
t	        |       ||f      j                         }ddd       ddd       d   |d   |d   |d   ddfS # t
        t        f$ r Y yw xY w# 1 sw Y   ;xY w# 1 sw Y   ?xY w)z`Associate a part with a model / asset / sub-assembly. Idempotent.
    Returns ``(link, error)``.)NzUnknown link target.r   r  )NzInvalid link target.)NzA link needs a target.z SELECT 1 FROM parts WHERE id = ?Nr	  )NzLink target not found.zTINSERT OR IGNORE INTO part_links (part_id, target_kind, target_ref) VALUES (?, ?, ?)ztSELECT id, part_id, target_kind, target_ref FROM part_links WHERE part_id = ? AND target_kind = ? AND target_ref = ?r   r=   r      )rm  r  r%  r&  )rL   _PART_LINK_KINDSr`   r,   r\   rp  r   rJ   r1   r-   rT   r'  )r  r%  r&  refr/   rU   s         r0   	link_partr-    se    **+

C//	0c#h-C -	 9hj 9D||> \O--5XZ819 9 9 #4c:19 9 9 	 "%g,S!A	C llH\;,. /7hj 	9 9 a&SVCFa&"#'( (# :& 	0/	09 9 9 9sN   D D8,D,8D8	D,D8(A
D,2D8D)(D),D5	1D88Ec                    t                t        5  t               5 }|j                  dt	        |       f      }|j                         }|s	 ddd       ddd       yt        t        |j                  D cg c]  }|d   	 c}|            }|j                  dt	        |       f       ddd       ddd       dfS c c}w # 1 sw Y   xY w# 1 sw Y   dfS xY w)z9Remove one part association. Returns ``(record, error)``.zHSELECT id, part_id, target_kind, target_ref FROM part_links WHERE id = ?N)NzLink no longer exists.r   z#DELETE FROM part_links WHERE id = ?r  )link_idr/   r'  rU   r(  recs         r0   unlink_partr1    s    	 Mhj MDll!'l_. lln1M M M 3coo6!6<=:S\OLM M 9 7M M M 9r  c                    | t         vrg S t        |      }| dv r	 t        t        |            }t               5 }|j                  d| |f      }|j                  D cg c]  }|d   	 }}g }|j                         D ]:  }t        ||      }	t        t        ||            d   |	d<   |j                  |	       < 	 ddd       |S # t        t
        f$ r g cY S w xY wc c}w # 1 sw Y   S xY w)zParts linked directly to one target (model / asset / sub_assembly), each
    carrying its stock + the link_id (so the UI can unlink).r)  zSELECT p.*, l.id AS link_id FROM part_links l JOIN parts p ON p.id = l.part_id WHERE l.target_kind = ? AND l.target_ref = ? ORDER BY p.part_no COLLATE NOCASEr   r/  N)r+  r`   r,   r\   rp  r   r1   r-   r   r  r  r   r   rs   )
r%  r&  r,  r/   r'  r(  r7   r  rK   r   s
             r0   parts_for_targetr3    s    **	

C//	c#h-C 
 tll1 4?2D	F
 "oo.!.. 	A!T"AD!-i8AiLJJqM	 J :& 	I	 / Js/   C #C C(AC CCC  C*c                 T   t               5 }|j                  dt        |       f      j                         }ddd       g }D ]W  \  }}}|}|dk(  rt	        |      }|r|d   nd|z  }n|dk(  rt        |      }	|	r|	d   nd|z  }|j                  ||||d       Y |S # 1 sw Y   ixY w)	u   The associations for one part, each resolved to a human label — feeds the
    part detail pane's link list. ``[{link_id, target_kind, target_ref, label}]``.zPSELECT id, target_kind, target_ref FROM part_links WHERE part_id = ? ORDER BY idNr   r   zmodel %sr  zsub-assembly %s)r/  r%  r&  label)r1   r-   r\   r  r  r  rs   )
r  r/   rZ  r  lidr   r,  r5  r  r  s
             r0   part_links_detailr7    s     
 Jt||-/27|o??Gxz 	J C 	%T37?$A!"AfIc)9E^# %A!"AfI):S)@E

s4s"$ 	%	% JJ Js   +BB'c           
         t        |       }|sdg iS g t               cfd} |t        d|d         dd       |j                  d      }|rt	        |      nd}|r) |t        dt        t        |                  d|d	          t        |       }|j                  d
g       |j                  dg       z   D ]%  } |t        dt        |d               d|d	          ' diS )a  Every part associated with an asset for its detail view: linked directly,
    inherited via its model, and via its own + model-inherited sub-assemblies.
    Returns a flat de-duplicated list (first source wins), each annotated with
    ``via`` (asset|model|sub_assembly) + ``via_label``.r   c                     | D ]9  }|d   v rj                  |d          ||d<   ||d<   j                  |       ; y )Nrm  via	via_label)r  rs   )r   r:  r;  r   r  r  s       r0   _addzparts_for_asset.<locals>._add#  sM     	Aw$HHQtWAeH&AkNJJqM	r2   r   r   rN   rH   Nr   r   r  r  r  rm  )r,  rB  r3  rr   r  r,   r\   r  )	r   r   r<  r  r   subsr  r  r  s	          @@r0   parts_for_assetr>    s    
 cNE}CEIC 		'5#5	6D
))J
C$'OC TEgs3s8}5wfN#C(Dhhub!DHH["$== nc!D'l;^vY	 S>r2   r   c                      t        d      } i }| r(	 t        j                  |       }|j                  di       }t        d      t        d      t               |dS # t        $ r Y ,w xY w)z;Import bookkeeping for the UI: when/what was last imported.r   r   r   r   )r   r   r  r   )rV   r   r   rr   r   rJ  )
report_rawr  r   s      r0   import_healthrA  @  ss    ./JF	ZZ
+FZZ 2B7F ##34&';<-"	   		s   'A 	A%$A%c                  h    t        d      } | sy 	 t        j                  |       S # t        $ r Y y w xY w)Nr   rV   r   r   r   r|  s    r0   r   r   R  s8    
'
(Czz#    % 	11zEquipment Registry)maintenance
operationssafetyqualityc                     dj                  | xs dj                         j                         j                               S )zRegistry key: lowercase + whitespace-collapsed. Mirrors the app's
    _normalize_equipment so aliases match the WO sidecars' free-text names.r4   rN   )r}   r^   r6   r   )r   s    r0   _norm_equipment_namerK  p  s2     88TZR&&(..066899r2   c                     | xs dj                         } | r| j                         dv ry| j                         dk(  ry| S )zStored include_in_oversight text -> the legacy value shape. '' -> False
    (off, factory default); 'true' -> True (legacy generic opt-in); otherwise
    the string ('stationary' | 'mobile').rN   )falserg  offnoneFtrueT)r^   r6   rj   s    r0   _oversight_from_storerQ  v  s@     JBD4::<#@@zz|vKr2   c                 z    | du ryt        | t              r&| j                         }|j                         dv rdS |S y)ztLegacy include_in_oversight value -> stored text. False/None/'' -> '';
    True -> 'true'; a string is kept trimmed.TrP  )rN   rM  rg  rN  rO  rN   )rY   r,   r^   r6   )rP   r  s     r0   _oversight_to_storerS    s?     }%KKMWWY"CCrJJr2   c                     | si S 	 t        j                  |       }t        |t              r6|j                         D ci c]  \  }}t        |      t        |       c}}S i S # t        $ r i cY S w xY wc c}}w r   )r   r   r   rY   r   r   r,   r  )r|  parsedru   r   s       r0   _departments_from_storerV    sm    	C <Ffd;S71CFDGO7[Y[[  	7s   A# A4#A10A1c                    | j                  d      xs | j                  d      xs d}| d   | j                  d      xs | j                  d      xs | d   |t        | j                  d            t        | j                  d	            t        | j                  d
            | j                  d      xs d| d   t        | j                  d            t        | j                  d            | d   dS )zBuild the legacy {asset_id, name, rts_*, include_in_oversight, ...}
    record from an (alias JOIN asset) row, so the app-side shim returns exactly
    what wor_equipment.json used to hold.rG   alias_created_atrN   	norm_name
alias_namer   r   r@   rB   rC   rD   rE   rF   )asset_idr   
created_atr@   rB   rC   rD   asset_norE   rF   r   )rr   r  rV  rQ  )rU   createds     r0   _equip_record_from_rowr_    s     gg,-R9K1LRPRG$%S)?S3{CSSWW^452377;L3MN 5cgg>T6U V WW%89?R$sww'789	*+% r2   a  SELECT al.norm_name AS norm_name, al.name AS alias_name, al.created_at AS alias_created_at, a.asset_tag AS asset_tag, a.description AS description, a.equipment_created_at AS equipment_created_at, a.rts_required AS rts_required, a.rts_departments AS rts_departments, a.include_in_oversight AS include_in_oversight, a.oversight_dev_eui AS oversight_dev_eui, a.out_of_service AS out_of_service, a.offsite AS offsite FROM equipment_aliases al JOIN assets a ON a.asset_tag = al.asset_tagc                  B   t                i } t               5 }|j                  t              }|j                  D cg c]  }|d   	 }}|j                         D ](  }t        t        ||            }t        |      | |d   <   * 	 ddd       | S c c}w # 1 sw Y   | S xY w)zReturn {norm_name: record} exactly like the old _load_wor_equipment().

    One entry per alias; ops fields come from the linked asset. Read half of
    the app-side compatibility shim.r   rY  N)	rL   r1   r-   _EQUIP_JOIN_SQLr   r  r   r   r_  )r  r/   r'  r(  r   rU   r0  s          r0   load_equipment_registryrb    s    
 
C	 @tll?+!$1A1Q411<<> 	@Cs7C()C$:3$?CK !	@@ J	 2@ Js   $BB=BBBc                     t        |      xs d}|}d}| j                  d|f      j                         r.d||fz  }|dz  }| j                  d|f      j                         r.|S )N	Equipmentr   rh  z%s (%d)r=   )r`   r-   rT   )r/   r5  r   r  s       r0   _unique_equipment_tagre    sz    $<&;D
C	A
,,Av'xz*4)#	Q ,,Av'xz* Jr2   c           
          t        |j                  d      xs d      }t        |j                  d      xs d      xs |}| j                  d||j                         rt	        |      nd|t
        |||f       y)zDCreate a new asset row for a registry record with no matching asset.r   rN   r\  zINSERT INTO assets (asset_tag, tag_num, description, source_sheet, equipment_created_at, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)N)r`   rr   r-   r   r\   EQUIPMENT_SHEET)r/   r   r   r   r   r^  s         r0   _insert_equipment_assetrh    sm    &**V$*+DVZZ-34;GLL	( 
#++-c#hT4	#s		r2   c                 J    | j                  d|t        |xs d      ||f       y )NdINSERT OR IGNORE INTO equipment_aliases (norm_name, name, asset_tag, created_at) VALUES (?, ?, ?, ?)rN   )r-   r`   )r/   rY  r   r   r^  s        r0   _link_aliasrk    s(    LL	+	F4:2&	7;=r2   c                    |j                  d      }t        |t              rt        j                  |      nd}| j                  d|j                  d      rdnd|t        |j                  d            t        |j                  d      xs d      |j                  d	      rdnd|j                  d
      rdnd||f       y)zBWrite the ops fields from a registry record onto its linked asset.rB   rN   zUPDATE assets SET rts_required = ?, rts_departments = ?, include_in_oversight = ?, oversight_dev_eui = ?, out_of_service = ?, offsite = ?, updated_at = ? WHERE asset_tag = ?r@   r=   r   rC   rD   rE   rF   N)rr   rY   r   r   r   r-   rS  r`   )r/   r   r   r   depts
depts_jsons         r0   _write_equipment_fieldsro    s    JJ()E&0&=E"2JLL	O jj(a	VZZ(>?	@	

./52	6jj)*jj#C		<	=r2   c                    t                t        j                         j                  d      }t        5  t               5 }|j                  d      D ci c]  }|d   |d    }}| j                         D ]  \  }}|s	|j                  |      }|t        |j                  d      xs d      }|r$|j                  d|f      j                         r|}n-t        ||j                  d	      xs |      }t        ||||       t        |||j                  d	      |t        |j                  d
      xs d      xs |       |||<   t        ||||        	 ddd       ddd       yc c}w # 1 sw Y   xY w# 1 sw Y   yxY w)a  Write half of the shim: upsert each {norm_name: record} entry into
    assets.db. An entry whose norm_name has no alias yet is the WO auto-seed
    path -> it links to an existing asset named by record['asset_no'] if that
    tag exists, else CREATES a new asset (source_sheet=EQUIPMENT_SHEET) + alias.
    Existing aliases update their linked asset's ops fields. Entries are never
    deleted here (flag-not-fix).r   z2SELECT norm_name, asset_tag FROM equipment_aliasesr   r=   Nr]  rN   rh  r   r\  )rL   r   r   r   rJ   r1   r-   r   rr   r`   rT   re  rh  rk  ro  )	registryr   r/   rK   	alias_maprY  r   r   	candidates	            r0   save_equipment_registryrt    s    
,,.
!
!"5
6C	 Bhj BD)-@*B CAQqT1Q4Z C	 C!)!1 	BIv!i0I "6::j#9#?R@	B""&&.hj"1 !*I 5fjj0=I!?I+D)VSID)VZZ-?"6::l#;#ArBIcK'0	)$#D)VSA#	BB B BCB B B Bs5   E8E,E'#C2E,E8'E,,E5	1E88Fc                     t               5 }|j                  dt        |       f      D cg c]
  \  }}||d c}}cddd       S c c}}w # 1 sw Y   yxY w)u   Ordered [{norm_name, name}] equipment aliases pointing at one asset —
    used by the Assets detail to gather WO history across every name variant.z`SELECT norm_name, name FROM equipment_aliases WHERE asset_tag = ? ORDER BY created_at, norm_name)rY  r   Nr2  )r   r/   r  nms       r0   equipment_aliases_forrw    s^     
  t<@LLB^=  !Ra,         s    AAAAAc           	      >   t               5 }|j                  t        dz   t        |       f      }|j                  D cg c]  }|d   	 }}|j                         }|s
	 ddd       yt        t        t        ||                  cddd       S c c}w # 1 sw Y   yxY w)zThe legacy registry record for one asset (first alias), or None when the
    asset carries no equipment alias. Lets the Assets detail render RtS/Service/
    Oversight without a second store.zD WHERE al.asset_tag = ? ORDER BY al.created_at, al.norm_name LIMIT 1r   N)	r1   r-   ra  r,   r   rT   r_  r   r   )r   r/   r'  r(  r   rU   s         r0   equipment_record_for_assetry    s     
 ?tll $ $&))n%68 "%1A1Q411lln? ? &d3w+<&=>? ? 2	? ?s"   2BB	B'BBBc                     t               5 }|j                  dt        |       f      j                         }ddd       r|d   S dS # 1 sw Y   xY w)zEResolve a normalized equipment name to its merged asset_tag, or None.;SELECT asset_tag FROM equipment_aliases WHERE norm_name = ?Nr   )r1   r-   rK  rT   )rY  r/   rU   s      r0   asset_tag_for_equipmentr|  -  sW    	 ;tllI!),.008
 	; 3q6"d"	; ;s   +AAc                    t        |       }t        5  t               5 }|j                  d|f      j	                         }|dd|z  fcddd       cddd       S |j                  d|f      j	                         }|r|d   dfcddd       cddd       S t        |      xs t        |d         xs |}t        |      }|s	 ddd       ddd       y|j                  d|f      j	                         }|r!|d   |k7  rdd|z  fcddd       cddd       S t        j                         j                  d	      }	|j                  d
||||	f       |j                  d|f      j	                         }
|
R|
d   xs dj                         s;|j                  dt        j                  t        D ci c]  }|d c}      |	|f       |dfcddd       cddd       S c c}w # 1 sw Y   nxY wddd       y# 1 sw Y   yxY w)ac  Link an EXISTING asset to an equipment alias so it enters the registry
    view (the consolidated Assets detail's "Track as equipment"). Never creates
    a new asset. Returns (norm_name, error): the primary alias norm_name for
    the asset, or an existing one if already tracked. Errors when the asset
    doesn't exist or no usable name can be derived.z2SELECT description FROM assets WHERE asset_tag = ?NzAsset %s no longer exists.zbSELECT norm_name FROM equipment_aliases WHERE asset_tag = ? ORDER BY created_at, norm_name LIMIT 1r   )Nz&Give the equipment a name to track it.r{  z1Another asset already uses the equipment name %r.r   rj  zLSELECT rts_departments, equipment_created_at FROM assets WHERE asset_tag = ?rN   z}UPDATE assets SET rts_departments = ?, equipment_created_at = COALESCE(NULLIF(equipment_created_at,''),?) WHERE asset_tag = ?T)r`   rJ   r1   r-   rT   rK  r   r   r   r^   r   r   _DEFAULT_RTS_DEPTS)r   r   r   r/   rU   r  displaynormr  r   r'  r(  s               r0   ensure_equipment_aliasr  6  s    
C	 $hj $Dll@3&JJR(* 	;5;;	$ $ $
 <<68;v??Gxz 	 A;$$ $ $ ,7&Q.7C#G,A$ $ $ IGXZ 	 U1X_M#$ %%$ $ $( lln%%&9::7C%	' ll#%(F,,4HJ 	 ?CFLb#7#7#9LL' .@AQWABCM	O
 TzI$ $ $F BG$ $ $ $ $se   G6*G!	G6)G!	G6-G!G61G!	G6BG!.
G8G!		G6G!!G*	&G66G?c                 ^   t                t        j                         j                  d      }|dddddg i dfd}t        5  t               5 }|j                  d      D ch c]  }|d   	 }}i }|j                  d      D ]L  \  }}t        |      t        |      hD ].  }	|	s|j                  |	t                     j                  |       0 N | j                         D ]  \  }
}|
s
dxx   dz  cc<   |
|v rd	xx   dz  cc<   )d
\  }}t        |j                  d      xs d      }|r&|j                  d|f      j                         r|d}}n|j                  |
      xs
 t               }t        |      dk(  rt!        t#        |            d}}nKt        |      dkD  r=d   j%                  |j                  d      xs |
t'        |      d        |d       |Xt)        ||j                  d      xs |
      }t+        ||||       d}|j                  |
t                     j                  |       t-        ||
|j                  d      |t        |j                  d      xs d      xs |       t/        ||||       |j                  |
       |dk(  rdxx   dz  cc<   |dk(  rdxx   dz  cc<   dxx   dz  cc<    	 ddd       ddd       t1        dt3        j4                               t1        d|       S c c}w # 1 sw Y   CxY w# 1 sw Y   GxY w)a  One-time idempotent merge of a legacy wor_equipment.json dict into
    assets.db (DVI-1472 P2 D3). For each record match to an asset by
    asset_no == asset_tag, else exact normalized-name match against
    tag/description; else create a new asset (source_sheet=EQUIPMENT_SHEET).
    Ambiguous (multiple) name matches are reported, NOT linked (flag-not-fix).
    Re-running is safe: a name already aliased is skipped (never re-clobbers
    admin edits). Returns + persists a report dict.r   r   )migrated_atr  already_linkedlinked_by_asset_nolinked_by_namer^  	ambiguousr   c                 B    d   j                  | d      dz   d   | <   y r   r   r   s    r0   r   z(migrate_equipment_registry.<locals>.flagt  s.    %&**5!4q8 	!"5)r2   z'SELECT norm_name FROM equipment_aliasesz)SELECT asset_tag, description FROM assetsr  r=   r  )NNr]  rN   rh  r   r  )r   
candidatesz!ambiguous name match (not linked)Nr^  r\  r  r  equipment_migration_reportequipment_migration_at)rL   r   r   r   rJ   r1   r-   rK  
setdefaultrB  r  r   r`   rr   rT   r   r   iterrs   rA  re  rh  rk  ro  rQ   r   r   )rq  r   r   r/   rK   r  
name_indexr   r  rO   rY  r   r   r   r]  candsr   s                   @r0   migrate_equipment_registryr  d  s)    
,,.
!
!"5
6CQ!1RF: 
 0'hj 0'D"&,,5#7 8QAaD 8 8
;= 	?IC,S13G3MN ?))#su599#>?	?
 "*!1 '	'Iv7Oq OH$'(A-((OItfjj4:;HDLL>K!!), #+J4	"y1:SUu:?&*4;&7tIZ!^;'.. &

6 2 ?i&,Um0  <= 1&**V,9	;	'iE %%i7;;IFiF);Yvzz,7=2>E#G#D)VSALL#z!+,1,'(A-(y!Q&!O'	'0' 0'b )4::f+=>%s+Me80' 0' 0' 0's=   L#L$L06L'H-LL#LL 	L##L,c                  h    t        d      } | sy 	 t        j                  |       S # t        $ r Y y w xY w)Nr  rC  rD  s    r0   r  r    s8    
/
0Czz# rE  r   )NNNNNr  r   )NNNNN)r  )NFr  r   )   r   )  )r  r  )rN   rN   
adjustment)rN   rN   )r  )__doc__r   r   osrb   r*   	threadingr   pathlibr   environrr   __file__resolveparentr   r   LockrJ   r   ro   rq   r   compiler   r~   r  r1   r;   rL   rQ   rV   r`   rd   rl   ry   r   r   r   r   r   r   r
  r)  r,  r0  r3  r6  r9  r;  r?  rG  rJ  rW  rP  r[  rc  ro  rv  re  r  r  r  r  r+  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r#  r'  r-  r1  r3  r7  r>  EXPORT_ALL_SHEETrA  r   rg  r~  rK  rQ  rS  rV  r_  ra  rb  re  rh  rk  ro  rt  rw  ry  r|  r  r  r  r   r2   r0   <module>r     sP  6   	 	    JJNN#T(^%;%;%=%D%D{%RSU
 JJNN%>))+22^CEF 
 inn  /   + k 	
 = F i y h  W w h  N  Z!" z#$ /4 2::m, (L^8#3;7(=/$P#fJ	 ?C/0.B0GN I
N$I 	 FJ#( EI.*0: !m/h D* 
 I5 E $?"2J4?	<@*J3Z&,(&0H

?	?,4
<" * 	 42/(<")0S3+l*;6 LN!18h
 &(>4*H   $. ' H :	\,M  	==B@ ?#+\GTr2   