
    5j                        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mZ ddlmZ  eej                  j                  d ee      j                         j                  dz              Z ej                         Z ej"                  d      Z ej"                  d      Zd	Zd
ZdZeD  ch c]  } | d   	 c} ZeD  ch c]  } | d   	 c} ZdZd Zd Zd Zd Zd Zd Zd Z d Z!d Z"d Z#d Z$d Z%dPdZ&d Z'd Z(	 dQdZ)d Z*dRdZ+d Z,dQd Z-d! Z.dRd"Z/d# Z0dQd$Z1d% Z2d& Z3dRd'Z4d( Z5dSd)Z6d* Z7d+Z8d,Z9 ej"                  d-      Z:d. Z;d/ Z<d0 Z=d1 Z>d2 Z?d3 Z@d4 ZAdTd5ZBd6 ZCd7 ZDd8 ZEdUd9ZFdRd:ZGd; ZHd< ZI	 dSd=ZJ	 dSd>ZKdSd?ZLdSd@ZMdSdAZNdB ZOdC ZPdD ZQdE ZR G dF dGeS      ZTdVdHZUdI ZVdJ ZWdK ZXdRdLZYdRdMZZdQdNZ[dO Z\yc c} w c c} w )Wu	  
logistics_store.py — SQLite store for the Togen Logistics tool (DVI-1387 P1
[DVI-1397], board-approved plan rev b8a5ca65).

Backs the Logistics coordinator: Resources (drivers/trucks/trailers) and the
Settings → Events catalog now; Loads / assignments / event timelines land in
P2 (their tables are created here so the schema is in place, but P1 exercises
only the resource + event-type + availability tables).

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

Design decisions (from the approved DVI-1387 plan + board answers 2026-08-12):
- **Drivers are a designated subset of Personnel (D7).** A driver row is keyed
  by the Personnel record id (`per_*`) — name/email live only on the Personnel
  record (personnel.json), never duplicated here. This table holds the
  logistics-only fields: max hours/day + hours/week planning knobs and the
  availability calendar. Removing a driver deletes the row (app.py also clears
  the `driver` designation) but never the Personnel record.
- **Availability is normalized (D2 groundwork).** Driver time-off and truck /
  trailer out-of-service dates are rows in `availability_windows` keyed by
  (resource_type, resource_id). One table = one place for P2's overlap engine
  to query "is this resource free for this window?". P1 stores + displays them;
  the conflict engine arrives in P2.
- **Trucks / trailers are soft-deleted** so a future load that referenced one
  keeps a stable id; drivers hard-delete the logistics row (the person stays in
  Personnel).
- **Hours are a planning aid, NOT legal HOS** (D3) — surfaced in the UI.

Schema
------
drivers(person_id PK [per_*], max_hours_day, max_hours_week, active,
        created_at/by, updated_at) — logistics fields for a designated person.
trucks(name, truck_number, asset_number, in_service, gross_weight,
        features JSON, active, deleted, created_at/by, updated_at) — a truck.
trailers(...) — same shape (gross_weight = weight it can carry; features []).
event_types(name, typical_minutes, sort, active, created_at, updated_at) —
        the reusable load-timeline events (Settings → Events).
availability_windows(resource_type [driver|truck|trailer], resource_id, kind
        [unavailable|out_of_service], start_date, end_date, note) — a blocked
        date range for one resource.
loads / load_assignments / load_events — created for P2 (empty in P1).
meta(key, value) — bookkeeping.
    N)datetime)PathLOGISTICS_DB_FILEzlogistics.dbz^\d{4}-\d{2}-\d{2}$z^per_[0-9a-f]{6,}$)drivertrucktrailer))extending_bucket_boomzExtending Bucket Boom ))zHook Trailer   )Load-   )Fuel   )Drive<   )Unloadr   c                      t        j                  t        t              d      } | j	                  d       | j	                  d       | j	                  d       t         j
                  | _        | S )N   )timeoutzPRAGMA journal_mode=WALzPRAGMA busy_timeout=10000zPRAGMA foreign_keys=ON)sqlite3connectstrr   executeRowrow_factoryconns    &/var/www/html/togen/logistics_store.py_connectr   T   sP    ??3012>DLL*+LL,-LL)*{{DK    c                  H    t        j                         j                  d      S )Nz%Y-%m-%d %H:%M:%S)r   utcnowstrftimer
   r    r   _nowr$   ]   s    ??%%&9::r    c                  $   t         5  t               5 } | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d       | j                  d	       | j                  d
       | j                  d       t        |        | j	                          ddd       ddd       y# 1 sw Y   xY w# 1 sw Y   yxY w)z?Create tables/indexes if missing and seed defaults. Idempotent.aF  CREATE TABLE IF NOT EXISTS drivers (
                   person_id TEXT PRIMARY KEY,
                   max_hours_day REAL NOT NULL DEFAULT 11,
                   max_hours_week REAL NOT NULL DEFAULT 60,
                   active INTEGER NOT NULL DEFAULT 1,
                   created_at TEXT, created_by TEXT, updated_at TEXT)aB  CREATE TABLE IF NOT EXISTS trucks (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   name TEXT NOT NULL,
                   truck_number TEXT NOT NULL DEFAULT '',
                   asset_number TEXT NOT NULL DEFAULT '',
                   in_service INTEGER NOT NULL DEFAULT 1,
                   gross_weight REAL,
                   features TEXT NOT NULL DEFAULT '[]',
                   active INTEGER NOT NULL DEFAULT 1,
                   deleted INTEGER NOT NULL DEFAULT 0,
                   created_at TEXT, created_by TEXT, updated_at TEXT)aF  CREATE TABLE IF NOT EXISTS trailers (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   name TEXT NOT NULL,
                   trailer_number TEXT NOT NULL DEFAULT '',
                   asset_number TEXT NOT NULL DEFAULT '',
                   in_service INTEGER NOT NULL DEFAULT 1,
                   gross_weight REAL,
                   features TEXT NOT NULL DEFAULT '[]',
                   active INTEGER NOT NULL DEFAULT 1,
                   deleted INTEGER NOT NULL DEFAULT 0,
                   created_at TEXT, created_by TEXT, updated_at TEXT)af  CREATE TABLE IF NOT EXISTS event_types (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   name TEXT NOT NULL,
                   typical_minutes INTEGER NOT NULL DEFAULT 0,
                   sort INTEGER NOT NULL DEFAULT 0,
                   active INTEGER NOT NULL DEFAULT 1,
                   created_at TEXT, updated_at TEXT)a  CREATE TABLE IF NOT EXISTS availability_windows (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   resource_type TEXT NOT NULL,
                   resource_id TEXT NOT NULL,
                   kind TEXT NOT NULL DEFAULT 'unavailable',
                   start_date TEXT NOT NULL,
                   end_date TEXT NOT NULL,
                   note TEXT NOT NULL DEFAULT '',
                   created_at TEXT)ztCREATE INDEX IF NOT EXISTS idx_avail_resource
                   ON availability_windows(resource_type, resource_id)a  CREATE TABLE IF NOT EXISTS loads (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   name TEXT NOT NULL DEFAULT '',
                   ltl_load_id INTEGER,
                   status TEXT NOT NULL DEFAULT 'planned',
                   start_date TEXT, start_time TEXT,
                   end_date TEXT, end_time TEXT,
                   destination TEXT NOT NULL DEFAULT '',
                   miles REAL,
                   budget_amount REAL,
                   cost_notes TEXT NOT NULL DEFAULT '',
                   travel_minutes INTEGER NOT NULL DEFAULT 0,
                   notes TEXT NOT NULL DEFAULT '',
                   deleted INTEGER NOT NULL DEFAULT 0,
                   created_at TEXT, created_by TEXT, updated_at TEXT)zCREATE TABLE IF NOT EXISTS load_assignments (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   load_id INTEGER NOT NULL,
                   resource_type TEXT NOT NULL,
                   resource_id TEXT NOT NULL)aU  CREATE TABLE IF NOT EXISTS load_events (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   load_id INTEGER NOT NULL,
                   event_type_id INTEGER,
                   name TEXT NOT NULL DEFAULT '',
                   minutes INTEGER NOT NULL DEFAULT 0,
                   sort INTEGER NOT NULL DEFAULT 0)aA  CREATE TABLE IF NOT EXISTS ltl_loads (
                   id INTEGER PRIMARY KEY AUTOINCREMENT,
                   name TEXT NOT NULL DEFAULT '',
                   notes TEXT NOT NULL DEFAULT '',
                   deleted INTEGER NOT NULL DEFAULT 0,
                   created_at TEXT, created_by TEXT, updated_at TEXT)zVCREATE TABLE IF NOT EXISTS meta (
                   key TEXT PRIMARY KEY, value TEXT)N)_write_lockr   r   _seed_defaultscommitr   s    r   init_logistics_dbr)   a   s,   	 _hj _DI	J 	
I	J 	
I	J 	8	9 	'		( 	J	K 	I	J  	1	2 	7	8 	I	J 	8	9 	t_ _ _ _ _ _s#   DCC:)D:D	?DDc           	          t               }| j                  d      j                         d   dk(  r8t        t              D ]%  \  }\  }}| j                  d|||dz   dz  ||f       ' y y )Nz SELECT COUNT(*) FROM event_typesr   INSERT INTO event_types(name, typical_minutes, sort, active,
                       created_at, updated_at) VALUES(?,?,?,1,?,?)   
   )r$   r   fetchone	enumerate_SEED_EVENTS)r   nowinameminutess        r   r'   r'      st    
&C||67@@B1EJ"+L"9 	9AgLLFwQ"c379	9 Kr    c                 f    | | dk(  ry	 t        |       }|dk\  r|S dS # t        t        f$ r Y yw xY w)zCCoerce to a non-negative float, else None (blank clears the field).N r   )float	TypeError
ValueError)valuens     r   _num_or_noner<      sJ    }%L Q1 D  z" s    00c                     t        | t        t        f      sg S g }| D ]:  }t        |xs d      j	                         }||v s%||vs*|j                  |       < |S Nr6   )
isinstancelisttupler   stripappend)rawallowedoutfkeys        r   _sanitize_featuresrI      s^    cD%=)	
C !'rl  "'>cnJJsO Jr    c           
         t        | t        t        f      sg S g }| D ]  }t        |t              st	        |j                  d      xs d      j                         }t	        |j                  d      xs d      j                         xs |}t        j                  |      rt        j                  |      s||k  r||}}|j                  ||t	        |j                  d      xs d      j                         dd d        |S )zNormalize a list of {start_date,end_date,note} blocks. Drops entries
    without two valid dates; swaps a reversed range; caps note length.
start_dater6   end_datenoteN   )rK   rL   rM   )
r?   r@   rA   dictr   getrB   _DATE_REmatchrC   )rD   rF   wstartends        r   _sanitize_windowsrV      s     cD%=)	
C 
E!T"AEE,'-2.446!%%
#)r*002;e~~e$HNN3,?;e3E

%Sf 34::<TcBD 	E
E Jr    c                     t               5 }|j                  d| t        |      f      j                         }d d d        D cg c]  }t	        |       c}S # 1 sw Y   "xY wc c}w )NzSELECT start_date, end_date, note FROM availability_windows
                   WHERE resource_type=? AND resource_id=?
                   ORDER BY start_date, end_date)r   r   r   fetchallrO   )resource_typeresource_idr   rowsrs        r   list_windowsr]     sg    	 :t||4 C,-	/ 08xz	 	: ""DG"": : #s   ,AA$A!c                     | j                  d|t        |      f       t               }t        |      D ]-  }| j                  d|t        |      ||d   |d   |d   |f       / y )NzHDELETE FROM availability_windows WHERE resource_type=? AND resource_id=?zINSERT INTO availability_windows(resource_type, resource_id, kind,
                   start_date, end_date, note, created_at)
                   VALUES(?,?,?,?,?,?,?)rK   rL   rM   )r   r   r$   rV   )r   rY   rZ   kindwindowsr1   rS   s          r   _set_windowsra     st    LLR	K()+ &Cw' -, C,dAlOz]AfIs,		--r    c                     t        |      }t        |j                  dd            |d<   | j                  d|d   f      j	                         D cg c]  }t        |       c}|d<   |S c c}w )Nactiver,   zSELECT start_date, end_date, note FROM availability_windows
               WHERE resource_type='driver' AND resource_id=?
               ORDER BY start_date, end_date	person_idr`   )rO   boolrP   r   rX   )r   rowdrS   s       r   _driver_rowrh     ss    S	AquuXq)*AhK%)\\	023K.1B&D EMHJPDG PAiL H	Ps   A*c                      t               5 } | j                  d      j                         }|D cg c]  }t        | |       c}cddd       S c c}w # 1 sw Y   yxY w)zvAll logistics driver rows (person_id + logistics fields + windows).
    The caller joins these to Personnel for names.(SELECT * FROM drivers ORDER BY person_idN)r   r   rX   rh   )r   r[   r\   s      r   list_driversrk   '  sZ     
 4t||688@
 	.23D!$34 4 44 4s   $AAAAAc                     t               5 }|j                  d| f      j                         }|rt        ||      nd cd d d        S # 1 sw Y   y xY w)N'SELECT * FROM drivers WHERE person_id=?)r   r   r.   rh   )rd   r   rf   s      r   
get_driverrn   0  sL    	 7tll5	|EEMXZ 	),{4%$7 7 7s   1AAc                    t        | xs d      j                         } t        j                  |       sy|xs i }t	        |j                  d            }t	        |j                  d            }t               }t        5  t               5 }|j                  d| f      j                         }|rg g }	}d|v r&|j                  d       |	j                  ||nd       d|v r&|j                  d	       |	j                  ||nd
       d|v r5|j                  d       |	j                  |j                  d      rdnd       |j                  d       |	j                  |       |	j                  |        |j                  ddj                  |      z  |	       n |j                  d| ||nd||nd
|||f       d|v rt        |d| d|j                  d             |j                          |j                  d| f      j                         }
t        ||
      df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)zCreate or update a driver row (keyed by Personnel `per_*` id). Returns
    (driver, error). `windows` in fields replaces the driver's time-off blocks.r6   )Nz!A valid personnel id is required.max_hours_daymax_hours_weekz/SELECT person_id FROM drivers WHERE person_id=?zmax_hours_day=?N   zmax_hours_week=?r   rc   zactive=?r,   r   zupdated_at=?z'UPDATE drivers SET %s WHERE person_id=?z, zINSERT INTO drivers(person_id, max_hours_day, max_hours_week,
                       active, created_at, created_by, updated_at)
                       VALUES(?,?,?,1,?,?,?)r`   r   unavailablerm   )r   rB   _PERSON_ID_RErR   r<   rP   r$   r&   r   r   r.   rC   joinra   r(   rh   )rd   fieldsactormax_daymax_weekr1   r   existingsetsparamsrf   s              r   upsert_driverr}   7  s#    IO$**,Iy)8\rF6::o67GFJJ'789H
&C	 !,hj !,D<<=L"(* 	 r&D&(-.)<g"E6)./(*>hBG6!J'6::h#7aQ?KK'MM#MM)$LLB99T?+,24 LL0 w':G%1r3sL	M xMI.0ll5	|EEMXZ 	4%t+C!, !, !, !, !, !, !,s%   >H>	FH)	H>)H2	.H>>Ic                    t         5  t               5 }|j                  d| f      }|j                  d| f       |j                          |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)zDelete the logistics driver row + its windows. Returns True if a row was
    removed. The Personnel record is untouched here (app.py clears the
    designation).z%DELETE FROM drivers WHERE person_id=?zODELETE FROM availability_windows WHERE resource_type='driver' AND resource_id=?r   Nr&   r   r   r(   rowcount)rd   r   curs      r   remove_driverr   e  sz     
  hj  DllBYLQ]L	 	||a             s#   A?AA*	A?*A3	/A??Bc                 
   t        |      }t        |j                  dd            |d<   t        |j                  dd            |d<   t        |j                  dd            |d<   	 t        j                  |j                  d      xs d      |d<   | j                  d|t        |d	         f      j                         D cg c]  }t        |       c}|d
<   |S # t        j
                  t        f$ r g |d<   Y jw xY wc c}w )N
in_servicer,   rc   deletedr   featuresz[]zSELECT start_date, end_date, note FROM availability_windows
               WHERE resource_type=? AND resource_id=?
               ORDER BY start_date, end_dateidr`   )
rO   re   rP   jsonloadsJSONDecodeErrorr8   r   r   rX   )r   rf   rY   number_fieldvrS   s         r   _vehicle_rowr   v  s    S	A155q12AlOquuXq)*AhKi+,AiL

155#4#<=* &*\\	0 
AdG%	&' (0xz	3DG 3AiL
 H   ), *3s   '+C D C=<C=c                 V   |xs i }t        |j                  d      xs d      j                         }|syt        |j                  |      xs d      j                         }t        |j                  d      xs d      j                         }	|j                  dd      rdnd}
t        |j                  d	            }t	        j
                  t        |j                  d
      |            }t               }t        5  t               5 }|rZ|j                  d| z  |f      j                         }|s	 d d d        d d d        y|j                  d| d|d|||	|
||||f       n.|j                  d| d|d|||	|
|||||f	      }|j                  }d|v rt        |||d|j                  d             |j                          |j                  d| z  |f      j                         }t        ||||      d f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)Nr3   r6   NzA name is required.asset_numberr   Tr,   r   gross_weightr   z*SELECT id FROM %s WHERE id=? AND deleted=0Nz
Not found.zUPDATE z SET name=?, zl=?, asset_number=?, in_service=?,
                       gross_weight=?, features=?, updated_at=? WHERE id=?zINSERT INTO z(name, z, asset_number, in_service,
                       gross_weight, features, active, deleted, created_at,
                       created_by, updated_at) VALUES(?,?,?,?,?,?,1,0,?,?,?)r`   out_of_servicezSELECT * FROM %s WHERE id=?)r   rP   rB   r<   r   dumpsrI   r$   r&   r   r   r.   	lastrowidra   r(   r   )tablerY   r   feature_idsrv   vidrw   r3   numberr   r   grossr   r1   r   rf   r   s                    r   _save_vehicler     s   \rFvzz&!'R(..0D*L)/R0668Fvzz.17R8>>@Ljjt4!JN34Ezz,VZZ
-C[QRH
&C	 Jhj JD,,<uD 
  )J J J LL ,( v|Z#	 ,, ,( v|Z#C --C}c3CI.0ll)E1C6;;C8: 	D#}lCTI9J J J J J J Js1   <H*H
1HB5H
7	H
H	HH(c                    t         5  t               5 }|j                  d| z  t               |f      }|j	                          |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)Nz>UPDATE %s SET deleted=1, updated_at=? WHERE id=? AND deleted=0r   r&   r   r   r$   r(   r   )r   r   r   r   s       r   _delete_vehicler     ss    	  hj  DllLfc]$ 	||a             s"   A8>A#	A8#A,	(A88Bc           
          t               5 }d}| s|dz  }|dz  }|j                  |      j                         D cg c]  }t        ||dd       c}cd d d        S c c}w # 1 sw Y   y xY w)NzSELECT * FROM trucks WHERE deleted=0 ORDER BY name COLLATE NOCASEr   truck_numberr   r   rX   r   include_deletedr   qr\   s       r   list_trucksr     sx    	 5t"##A	,,a1135 T1g~> 55 5
55 5   0A!AA!A!!A*c                     t               5 }|j                  d| f      j                         }|rt        ||dd      nd cd d d        S # 1 sw Y   y xY w)NzSELECT * FROM trucks WHERE id=?r   r   r   r   r.   r   r   r   rf   s      r   	get_truckr     sP    	 Qtll<sfENNPCF|D#w?DQ Q Q   3AAc           	      ,    t        dddt        | ||      S )Ntrucksr   r   )r   _TRUCK_FEATURE_IDSrv   r   rw   s      r   
save_truckr     s    7N<Ne- -r    c                     t        d|       S )Nr   r   r   s    r   delete_truckr     s    8S))r    c           
          t               5 }d}| s|dz  }|dz  }|j                  |      j                         D cg c]  }t        ||dd       c}cd d d        S c c}w # 1 sw Y   y xY w)NzSELECT * FROM trailersr   r   r   trailer_numberr   r   s       r   list_trailersr     sy    	 5t$##A	,,a1135 T1i1AB 55 5
55 5r   c                     t               5 }|j                  d| f      j                         }|rt        ||dd      nd cd d d        S # 1 sw Y   y xY w)Nz!SELECT * FROM trailers WHERE id=?r   r   r   r   s      r   get_trailerr     sV    	 "tll/#99A 	  T3	3CD 	" " "r   c           	      ,    t        dddt        | ||      S )Ntrailersr   r   )r   _TRAILER_FEATURE_IDSr   s      r   save_trailerr     s!    Y0@-vsEC Cr    c                     t        d|       S )Nr   r   r   s    r   delete_trailerr     s    :s++r    c                 X    t        |       }t        |j                  dd            |d<   |S )Nrc   r,   rO   re   rP   )rf   es     r   
_event_rowr     s)    S	AquuXq)*AhKHr    c                     t               5 }d}| s|dz  }|dz  }|j                  |      j                         D cg c]  }t        |       c}cd d d        S c c}w # 1 sw Y   y xY w)NzSELECT * FROM event_typesz WHERE active=1z# ORDER BY sort, name COLLATE NOCASE)r   r   rX   r   )include_inactiver   r   r\   s       r   list_event_typesr     so    	 Ct'""A	22'+||A'?'?'AB!
1BC C
 CC C   0AAAAA'c                     t               5 }|j                  d| f      j                         }|rt        |      nd cd d d        S # 1 sw Y   y xY w)N$SELECT * FROM event_types WHERE id=?)r   r   r.   r   )eidr   rf   s      r   get_event_typer     sH    	 0tll2SF<<DHJ 	"%z#40 0 0s   0AAc           
      :   | xs i } t        | j                  d      xs d      j                         }|sy	 t        t	        | j                  d      xs d            }|dk  rd}t               }t        5  t               5 }|rL|j                  d|f      j                         }|s	 d d d        d d d        y|j                  d||||f       nE|j                  d	      j                         d   }|j                  d
|||||f      }|j                  }|j                          |j                  d|f      j                         }t        |      d fcd d d        cd d d        S # t
        t        f$ r d}Y w xY w# 1 sw Y   nxY wd d d        y # 1 sw Y   y xY w)Nr3   r6   r   typical_minutesr   z%SELECT id FROM event_types WHERE id=?r   z`UPDATE event_types SET name=?, typical_minutes=?,
                       updated_at=? WHERE id=?z0SELECT COALESCE(MAX(sort),0)+10 FROM event_typesr+   r   )r   rP   rB   intr7   r8   r9   r$   r&   r   r   r.   r   r(   r   )	rv   r   r3   r4   r1   r   rf   nxtr   s	            r   save_event_typer   	  s   \rFvzz&!'R(..0D*eFJJ'89>Q?@ {
&C	 %hj %D,,7#AAI )% % % LL248'33LN ,,BDDLHJqRC,,FwS#.0C --Cll2SF<<DHJ 	#$)% % % z" 
% % % % %sB   'E$ 5F 'E<'F8BE<	F$E98E9<F	FFc                     t         5  t               5 }|j                  d| f      }|j                          |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)zHard delete an event type. A load snapshots its events (name+minutes) at
    save time, so deleting a type never rewrites a planned load's timeline.z"DELETE FROM event_types WHERE id=?r   Nr   )r   r   r   s      r   delete_event_typer   ,  sd     
  hj  Dll?#H||a             s"   A,2A	A,A 	A,,A5)planned	scheduled	completed	cancelled)r   r   r   z^\d{2}:\d{2}$c                    t        | xs d      j                         }t        j                  |      sy|j	                  d      \  }}	 dt        |      cxk  rdk  rn ydt        |      cxk  rdk  r|S  yy# t        $ r Y yw xY w)zEReturn a valid HH:MM (24h) or '' (blank = an open end of the window).r6   :r      ;   )r   rB   _TIME_RErR   splitr   r9   )r:   shhmms       r   _sanitize_timer   P  s    EKR A>>!WWS\FBB2 	 #$s2w"4""4H #5   s   	A9  A9 6A9 9	BBc                     | r| S dS )Nz00:00r
   ts    r   _win_lor   ^      1wr    c                     | r| S dS )Nz23:59r
   r   s    r   _win_hir   b  r   r    c                    t        | j                  d      xs d      j                         }t        j	                  |      syt        | j                  d      xs d      j                         }t        j	                  |      r||k  r|}t        | j                  d            }t        | j                  d            }|dz   t        |      z   |dz   t        |      z   fS )z(start_dt, end_dt) as sortable 'YYYY-MM-DD HH:MM' strings, or None when
    the load has no start date. A blank start time floors to 00:00, a blank end
    time ceils to 23:59 (whole-window occupancy).rK   r6   NrL   
start_timeend_time )r   rP   rB   rQ   rR   r   r   r   )loadsdedstets        r   load_windowr   f  s     
TXXl#)r	*	0	0	2B>>"	TXXj!'R	(	.	.	0B>>"b	.	/B	,	-BHwr{"BHwr{$:;;r    c                     t        | j                  d      xs d      j                         }t        | j                  d      xs d      j                         }t        j	                  |      r|r||k  r|}||fS )NrK   r6   rL   )r   rP   rB   rQ   rR   )r   r   r   s      r   _load_date_ranger   u  sf    	TXXl#)r	*	0	0	2B	TXXj!'R	(	.	.	0B>>""br6Mr    c                     | |k  xr ||k  S )zFInclusive interval overlap (works for both datetime and date strings).r
   )a_starta_endb_startb_ends       r   	_overlapsr   }  s    e05 00r    c                     | j                  d      xs g }t        d |D              }|t        | j                  d            z   S )zFComputed total = sum of the load's event minutes + manual travel time.eventsc              3   P   K   | ]  }t        |j                  d                yw)r4   N)_intrP   ).0r   s     r   	<genexpr>z(load_duration_minutes.<locals>.<genexpr>  s     71QUU9%&7s   $&travel_minutes)rP   sumr   )r   r   totals      r   load_duration_minutesr    s?    XXh%2F777E4!12333r    c                 Z    	 t        t        |             S # t        t        f$ r |cY S w xY wN)r   r7   r8   r9   )r:   defaults     r   r   r     s/    5<  z" s    **c                 |    | j                  d|f      j                         D cg c]  }t        |       c}S c c}w )NzbSELECT resource_type, resource_id FROM load_assignments
               WHERE load_id=? ORDER BY idr   rX   rO   r   load_idr\   s      r   _load_assignmentsr    s@    !\\	.07z;;C8:GDG G G G   9c                 |    | j                  d|f      j                         D cg c]  }t        |       c}S c c}w )NzoSELECT id, event_type_id, name, minutes, sort FROM load_events
               WHERE load_id=? ORDER BY sort, idr
  r  s      r   _load_eventsr    sA    !\\	46=ZAAIMDG M M Mr  c                     t        |      }t        |j                  dd            |d<   t        | |d         |d<   t	        | |d         |d<   t        |      |d<   |S )Nr   r   r   assignmentsr   duration_minutes)rO   re   rP   r  r  r  )r   rf   rg   s      r   	_load_rowr    sb    S	Ai+,AiL(qw7AmtQtW-AhK1!4AHr    c                     t               5 }d}|s|dz  }| s|dz  }|dz  }|j                  |      j                         D cg c]  }t        ||       c}cd d d        S c c}w # 1 sw Y   y xY w)NzSELECT * FROM loads WHERE 1=1 AND deleted=0z AND status!='cancelled'zC ORDER BY COALESCE(start_date,''), COALESCE(start_time,''), id DESC)r   r   rX   r  )include_cancelledr   r   r   r\   s        r   
list_loadsr    s    	 Ht+!!A ++A	RR,0LLO,D,D,FGq	$"GH H HH Hs   7A&A!A&!A&&A/c                     t               5 }d}|s|dz  }|j                  || f      j                         }|rt        ||      nd cd d d        S # 1 sw Y   y xY w)NSELECT * FROM loads WHERE id=?r  )r   r   r.   r  )r  r   r   r   rf   s        r   get_loadr    sZ    	 5t,!!All1wj)224'*ys#5 5 5s   :AAc                    |dk(  r<| j                  dt        |      f      j                         }|dddS |d   sdd	dS y|d
k(  rdnd}| j                  d|z  |f      j                         }||d   rdd|z  dS |d   sdddS y)z(b): a vehicle flagged not-in-service / a deleted or inactive resource
    is unavailable for every window. Returns a conflict dict or None.r   z,SELECT active FROM drivers WHERE person_id=?Nmissingzdriver not found)r_   detailrc   inactivezdriver inactiver   r   r   z-SELECT in_service, deleted FROM %s WHERE id=?r   z%s not foundr   out_of_service_flagzmarked not in service)r   r   r.   )r   rY   rZ   rf   r   s        r   !_resource_is_globally_unavailabler!    s      ll:!!) 	 ;%1CDD8}&2CDD%0HjE
,,7%?	 
  {c)n!^m-KLL|-13 	3r    c                     g }| j                  d|t        |      f      j                         D ]9  }t        |||d   |d         s|j	                  |d   |d   |d   |d   d       ; |S )zH(a): out-of-service / time-off windows overlapping [sd, ed] (date-only).z|SELECT kind, start_date, end_date, note FROM availability_windows
                   WHERE resource_type=? AND resource_id=?rK   rL   r_   rM   )r_   rK   rL   rM   )r   r   rX   r   rC   )r   rY   rZ   r   r   rF   r\   s          r   _window_conflictsr#    s    
C\\>C,-/ 08xzG RQ|_a
m<JJ&	<$%jM1V9F GG Jr    c           	      2   g }| j                  d|t        |      f      j                         D ]f  }|r	|d   |k(  rt        t	        |            }|s%t        |||d   |d         s:|j                  d|d   |d   xs d|d   z  |d   |d	   d
       h |S )zD(c): other occupying loads whose window overlaps [start_dt, end_dt].aM  SELECT l.id, l.name, l.status, l.start_date, l.start_time,
                          l.end_date, l.end_time
                   FROM load_assignments a JOIN loads l ON l.id=a.load_id
                   WHERE a.resource_type=? AND a.resource_id=? AND l.deleted=0
                     AND l.status IN ('planned','scheduled','completed')r   r   r,   r   r3   zLoad #%drK   rL   )r_   r  	load_namerK   rL   )r   r   rX   r   rO   r   rC   )	r   rY   rZ   start_dtend_dtexclude_load_idrF   r\   rS   s	            r   _load_conflictsr)    s     C\\L
 C,-/ 08xz4 qw/9Q 8VQqT1Q48JJ1T7%&vY%H:$3G&'o$%jM3 44 Jr    c           	          g }t        | ||      }|r|j                  |       |\  }}	|t        | ||||	      z  }|t        | |||d   |d   |      z  }|S )Nr   r,   )r!  rC   r#  r)  )
r   rY   rZ   	window_dt
date_ranger(  	conflictsgr   r   s
             r   _conflicts_forr/    su    I)${KAFB"4RLLI}k!*1y|_N NIr    c           	          t        |      }|sdg fS t        |      }t               5 }t        || t	        |      |||      }ddd        |fS # 1 sw Y   xY w)zsReturn (available, conflicts) for one resource against a window dict
    {start_date,start_time,end_date,end_time}.TN)r   r   r   r/  r   )rY   rZ   windowr(  r+  r,  r   r-  s           r   check_availabilityr2    sp     F#IRx!&)J	 Kt"4K8H#,j/K	K MI%%K Ks   AAc           
         t        |      }t        |      }g }t               5 }| dk(  r;|j                  d      j	                         }|D cg c]  }t        ||       }	}d}
n~| dk(  r=|j                  d      j	                         }|D cg c]  }t        ||dd       }	}d}
n<|j                  d      j	                         }|D cg c]  }t        ||d	d
       }	}d}
|	D ]J  }||
   }|sg nt        || t        |      |||      }| |d<   ||d<   | |d<   ||d<   |j                  |       L 	 ddd       |S c c}w c c}w c c}w # 1 sw Y   |S xY w)zEvery non-deleted resource of a type + its availability for `window`.
    Driver rows carry person_id + hour knobs; vehicle rows carry name/number so
    the caller can render them (drivers still need the Personnel join for a
    display name).r   rj   rd   r   zASELECT * FROM trucks WHERE deleted=0 ORDER BY name COLLATE NOCASEr   r   zCSELECT * FROM trailers WHERE deleted=0 ORDER BY name COLLATE NOCASEr   r   rY   rZ   	availabler-  N)
r   r   r   r   rX   rh   r   r/  r   rC   )rY   r1  r(  r+  r,  rF   r   r[   r\   baserH   resridr-  s                 r   resources_availabilityr8    s   
 F#I!&)J
C	 tH$<<:<<DHJ 267QKa(7D7Cg%<<Shj  MQQqLq'>BQDQC<<Uhj  "# !q)5EF #D #C 		Cc(C#,'mSX(1:P  $1C !$C#,}C(CJJsO		': J3 8 R#!: Js6   )ED<-EE!(E	EAE<EEc                    d}d}t        |      }t               5 }|j                  dt        |       f      j	                         D ]q  }|r	|d   |k(  rt        |d         |j                  d|d   f      j                         d   xs dz   }|d   |k(  r||z  }|d   s[t        |d         |k(  sm||z  }s 	 ddd       ||dS # 1 sw Y   xY w)	z(D3 planning aid) sum of computed load durations already assigned to this
    driver on `date` (day) and in the same ISO week (week). Cancelled/deleted
    loads excluded. Returns {day, week}.r   a9  SELECT l.id, l.start_date, l.travel_minutes
                       FROM load_assignments a JOIN loads l ON l.id=a.load_id
                       WHERE a.resource_type='driver' AND a.resource_id=?
                         AND l.deleted=0
                         AND l.status IN ('planned','scheduled','completed')r   r  z@SELECT COALESCE(SUM(minutes),0) FROM load_events WHERE load_id=?rK   N)dayweek)	_iso_weekr   r   r   rX   r   r.   )	rd   dater(  r:  r;  target_weekr   r\   durs	            r   driver_assigned_minutesr@  :  s     CDD/K	 tP
 Y!# $,8:	A 1T7o#=q)*+t||R4
0$HJq0* 0/-.0C $&s
9Q|_#=#L	" %%# s   BC C2CCc                     t         j                  t        | xs d            sy	 d | j                  d      D        \  }}}t	        |||      j                         }|d   |d   fS # t        t        f$ r Y yw xY w)zm(year, week) for a YYYY-MM-DD string; '' for a bad date. Uses date math,
    not epoch, so no timezone drift.r6   c              3   2   K   | ]  }t        |        y wr  )r   )r   xs     r   r  z_iso_week.<locals>.<genexpr>[  s     3a3q63s   -r   r,   )rQ   rR   r   r   r   isocalendarr9   r8   )r=  ymrg   isos        r   r<  r<  U  sy     >>#djb/*34::c?31aq!Q++-AA	" s   A A& &A87A8c                 p    t        | xs d      j                         j                         }|t        v r|S dS )Nr6   r   )r   rB   lowerLOAD_STATUSES)r:   r   s     r   _sanitize_statusrL  d  s4    EKR &&(A]"11	1r    c                 <   t        | t        t        f      sg S t               g }}| D ]  }t        |t              st        |j                  d      xs d      j                         }|t        vrI|j                  d      }|dk(  r3t        |xs d      j                         }t        j                  |      s	 t        |      }|t        |      f}||v r|j                  |       |j                  |t        |      d        |S # t        t        f$ r Y w xY w)u   Normalize [{resource_type, resource_id}] — one per (type,id), types
    limited to driver/truck/trailer. Driver ids stay strings (per_*); vehicle
    ids coerce to int.rY   r6   rZ   r   )rY   rZ   )r?   r@   rA   setrO   r   rP   rB   RESOURCE_TYPESrt   rR   r   r8   r9   addrC   )rD   seenrF   artyperid_rawr7  keyts           r   _sanitize_assignmentsrV  i  s    cD%=)	r#D F!T"AEE/*0b1779&%%&Hgm$**,C &&s+'l s3x 4<

U3s8DE)F* J z* s   :D		DDc                    t        | t        t        f      sg S g }t        |       D ]  \  }}t        |t              st        |j                  d      xs d      j                         dd }|j                  d      }	 |dvrt        |      nd}t        |j                  d            }|dk  rd}|s||j                  ||||d	z   d
z  d        |S # t        t        f$ r d}Y Yw xY w)zNormalize a load's ordered event timeline. Each entry snapshots a
    name+minutes (from a picked event_type or hand-entered) so later Settings
    edits never rewrite a planned load.r3   r6   Nx   event_type_idr>   r4   r   r,   r-   )rY  r3   r4   sort)r?   r@   rA   r/   rO   r   rP   rB   r   r8   r9   r   rC   )rD   rF   r2   r   r3   etidr4   s          r   _sanitize_load_eventsr\    s     cD%=)	
C# +1!T"155=&B'--/5uu_%	 $J 63t9DD quuY'(Q;G

T4GUbL* 	++  J :& 	D	s   >CC)(C)c                   "     e Zd ZdZ fdZ xZS )LoadConflictErrorz=Raised when a save would double-book an unavailable resource.c                 2    t         |   d       || _        y )Nzresource conflict)super__init__r-  )selfr-  	__class__s     r   ra  zLoadConflictError.__init__  s    ,-"r    )__name__
__module____qualname____doc__ra  __classcell__)rc  s   @r   r^  r^    s    G# #r    r^  c                    | xs i } t        | j                  d      xs d      j                         dd }t        | j                  d      xs d      j                         }t        j	                  |      syt        | j                  d      xs d      j                         }t        j	                  |      r||k  r|}t        | j                  d            }t        | j                  d	            }t        | j                  d
            }	t        | j                  d      xs d      j                         dd }
t        | j                  d      xs d      j                         dd }t        | j                  d      xs d      j                         dd }t        | j                  d            }t        | j                  d            }t        | j                  d            }|dk  rd}| j                  d      }	 |dvrt        |      nd}t        | j                  d            }t        | j                  d            }||||d}|rS|rQ|	dk7  rLg }|D ]8  }t        |d   |d   ||      \  }}|r|j                  |d   |d   |d       : |rt!        |      t#               }t$        5  t'               5 }|rW|j)                  d|f      j+                         }|s	 ddd       ddd       y |j)                  d!|||	|||||
|||||||f       n.|j)                  d"|||	|||||
||||||||f      }|j,                  }|j)                  d#|f       |D ]  }|j)                  d$||d   |d   f        |j)                  d%|f       |D ]%  }|j)                  d&||d'   |d   |d(   |d)   f       ' |j/                          |j)                  d*|f      j+                         }t1        ||      dfcddd       cddd       S # t        t        f$ r d}Y 
w xY w# 1 sw Y   nxY wddd       y# 1 sw Y   yxY w)+a^  Create or update a load (header + ordered assignments + events). On save,
    re-validate every assigned resource against the load's window (the
    double-booking race guard, D2) unless revalidate=False. Returns
    (load, error). Raises LoadConflictError with a list of
    {resource_type, resource_id, conflicts} when an assignment is unavailable.r3   r6   NrN   rK   )NzA start date is required.rL   r   r   statusdestinationi,  
cost_notesi  notesi  milesbudget_amountr  r   ltl_load_idr>   r  r   )rK   r   rL   r   r   rY   rZ   )r(  )rY   rZ   r-  z-SELECT id FROM loads WHERE id=? AND deleted=0r   a  UPDATE loads SET name=?, ltl_load_id=?, status=?,
                       start_date=?, start_time=?, end_date=?, end_time=?,
                       destination=?, miles=?, budget_amount=?, cost_notes=?,
                       travel_minutes=?, notes=?, updated_at=? WHERE id=?aQ  INSERT INTO loads(name, ltl_load_id, status, start_date,
                       start_time, end_date, end_time, destination, miles,
                       budget_amount, cost_notes, travel_minutes, notes,
                       deleted, created_at, created_by, updated_at)
                       VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,0,?,?,?)z,DELETE FROM load_assignments WHERE load_id=?zfINSERT INTO load_assignments(load_id, resource_type,
                       resource_id) VALUES(?,?,?)z'DELETE FROM load_events WHERE load_id=?zmINSERT INTO load_events(load_id, event_type_id, name,
                       minutes, sort) VALUES(?,?,?,?,?)rY  r4   rZ  r  )r   rP   rB   rQ   rR   r   rL  r<   r   r   r8   r9   rV  r\  r2  rC   r^  r$   r&   r   r   r.   r   r(   r  )rv   r  rw   
revalidater3   rK   rL   r   r   rj  rk  rl  rm  rn  budgettravelltl_idr  r   r1  blockedrR  okr-  r1   r   rf   r   r   s                                r   	save_loadrw    sc    \rFvzz&!'R(..0#6DVZZ-34::<J>>*%06::j)/R0668H>>(#x*'<

< 89Jfjj45Hfjj23Ffjj/526<<>tDKVZZ-34::<UdCJ

7#)r*0025D9EG,-E&**_56F&**-./FzZZ&F &j 8Vd (

=(ABK"6::h#78F&j":F
 kf&; 	9A./"Am$4f ')MB	 ?1C/0/?-6 8 9	9 #G,,
&C	 )*hj )*D,,?
$HJ  ))* )* )* LLM vvz:x;vz6W&' ,,D
 vvz:x;vz6UC)*C mmGCgZP 	AALL5!O,a.>?A	A
 	>
K 	ALL;!O,ai96	 	ll;gZHQQSs#T)S)* )* )*/ z" .)* )* )* )* )*sC   P	 3P6>'P!%P66D P!6	P6	PP!P*	&P66P?c                 T   t        |      }t        5  t               5 }|j                  d|t	               | f      }|j                          |j                  dk(  r	 d d d        d d d        y t        |       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)Nz@UPDATE loads SET status=?, updated_at=? WHERE id=? AND deleted=0r   )rL  r&   r   r   r$   r(   r   r  )r  rj  r   r   s       r   set_load_statusry  	  s    f%F	 !hj !DllNTVW%' 	<<1! ! !  ! ! ! ! ! ! !s.   B>B	B,
B	6	B	B	BB'c                    t         5  t               5 }|j                  dt               | f      }|j	                          |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)NzAUPDATE loads SET deleted=1, updated_at=? WHERE id=? AND deleted=0r   r   )r  r   r   s      r   delete_loadr{    sl    	  hj  DllOVW 	||a             "   A5;A 	A5 A)	%A55A>c                 X    t        |       }t        |j                  dd            |d<   |S )Nr   r   r   )rf   rg   s     r   _ltl_rowr~  $  s)    S	Ai+,AiLHr    c                     t               5 }d}| s|dz  }|dz  }|j                  |      j                         D cg c]  }t        |       c}cd d d        S c c}w # 1 sw Y   y xY w)NzSELECT * FROM ltl_loadsr   z ORDER BY LOWER(name), id DESC)r   r   rX   r~  r   s       r   list_ltl_loadsr  *  so    	 At%##A	--%)\\!_%=%=%?@@A A
 AA Ar   c                     t               5 }d}|s|dz  }|j                  || f      j                         }|rt        |      nd cd d d        S # 1 sw Y   y xY w)N"SELECT * FROM ltl_loads WHERE id=?r  )r   r   r.   r~  )rt  r   r   r   rf   s        r   get_ltl_loadr  3  sW    	 .t0!!All1vi(113 #x}. . .s   9AAc           
         | xs i } t        | j                  d      xs d      j                         dd }|syt        | j                  d      xs d      j                         dd }t               }t        5  t               5 }|rL|j                  d|f      j                         }|s	 ddd       ddd       y	|j                  d
||||f       n#|j                  d|||||f      }|j                  }|j                          |j                  d|f      j                         }t        |      df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)z9Create or update a built load. Returns (ltl_load, error).r3   r6   NrN   )NzA load name is required.rm  i  z1SELECT id FROM ltl_loads WHERE id=? AND deleted=0r   z=UPDATE ltl_loads SET name=?, notes=?, updated_at=? WHERE id=?zzINSERT INTO ltl_loads(name, notes, deleted, created_at,
                       created_by, updated_at) VALUES(?,?,0,?,?,?)r  )r   rP   rB   r$   r&   r   r   r.   r   r(   r~  )	rv   rt  rw   r3   rm  r1   r   rf   r   s	            r   save_ltl_loadr  <  sZ   \rFvzz&!'R(..0#6D/

7#)r*0025D9E
&C	 #hj #D,,C	#8:  )# # # LLOuc6*, ,,Fuc5#.0C ]]Fll06)==EXZ 	}d"'# # # # # # #s1   8E'E*E;A7E2	EE	
EE#c                    t         5  t               5 }|j                  dt               | f      }|j	                          |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)u   Soft-delete a built load. Loads that already reference it keep their
    ltl_load_id (the name is resolved via get_ltl_load(..., include_deleted=True)
    for display) — deleting a built load never rewrites a planned load.zEUPDATE ltl_loads SET deleted=1, updated_at=? WHERE id=? AND deleted=0r   Nr   )rt  r   r   s      r   delete_ltl_loadr  Z  sn     
  hj  DllSVV 	||a             r|  )r6   r>   )Fr  )r   )TF)Nr6   T)]rg  r   osrer   	threadingr   pathlibr   environrP   __file__resolveparentr   Lockr&   compilerQ   rt   rO  TRUCK_FEATURESTRAILER_FEATURESr   r   r0   r   r$   r)   r'   r<   rI   rV   r]   ra   rh   rk   rn   r}   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   rK  _OCCUPYING_STATUSESr   r   r   r   r   r   r   r  r   r  r  r  r  r  r!  r#  r)  r/  r2  r8  r@  r<  rL  rV  r\  	Exceptionr^  rw  ry  r{  r~  r  r  r  r  )rG   s   0r   <module>r     s^  ,\  	 	    JJNN&>))+22^CEF  inn2::,-

01/ G $23qad3 &67!7 ;aH9!0#-$47+,\
 "$ #%(JV 5Q-
*5"C
,C0 %F 8 C < 2::&'<1
4GMH50
 %). $(

&%P&6
2
>4#	 #\*~	! A.#<	 i  47s   %F<8G