
    'it                       d Z ddlmZmZmZmZmZmZmZm	Z	m
Z
mZmZmZ ddlmZmZ ddlmZ ddlZddlmZ ddlZddlZddlZddlZddlmZmZmZmZmZmZm Z m!Z!m"Z" ddl#Z#ddl$Z$ddl%m&Z&  ee'      Z( ejR                  d	      e(_*        d
e(jV                  d<   de(jV                  d<   de(jV                  d<   h de(jV                  d<   d Z,d Z-ddZ.d Z/d Z0d Z1ddZ2d Z3d Z4ddZ5ddZ6d Z7d Z8e(js                  d d!d"g#      d$        Z:e(js                  d%      d&        Z;e(js                  d'      d(        Z<e(js                  d)      e7d*               Z=e(js                  d+      e7d,               Z>e(js                  d-      e7d.               Z?e(js                  d/d!d"g#      e7d0               Z@e(js                  d1d!d"g#      e7d2               ZAe(js                  d3d"g#      e7d4               ZBe(js                  d5      e7d6               ZCe(js                  d7      e7d8               ZDe(js                  d9d!d"g#      e7d:               ZEe(js                  d;d!d"g#      e7d<               ZFe(js                  d=d!d"g#      e7d>               ZGe(js                  d?d"g#      e7d@               ZHe(js                  dAd!g#      e7dB               ZIe(js                  dC      e7dD               ZJe(js                  dE      e7dF               ZKe(js                  dG      e7dH               ZLe(js                  dI      e7dJ               ZMe(js                  dKd!d"g#      e7dL               ZNe(js                  dMd!d"g#      e7dN               ZOe(js                  dOd"g#      e7dP               ZPe(js                  dQ      e7dR               ZQe(js                  dSd!d"g#      e7dT               ZRe(js                  dUd!d"g#      e7dV               ZSe(js                  dWd"g#      e7dX               ZTe(js                  dY      e7dZ               ZUe(js                  d[      e7d\               ZVe(js                  d]d!d"g#      e(js                  d^d!d"g#      e7dd_                     ZWe(js                  d`      e7da               ZXe(js                  dbd!d"g#      e7dc               ZYe(js                  ddd"g#      e7de               ZZe(js                  dfd"g#      e7dg               Z[e(js                  dh      e7di               Z\e(js                  djd"g#      e7dk               Z]e(js                  dld!d"g#      e7dm               Z^e(js                  dnd!d"g#      e7do               Z_e(js                  dpd"g#      e7dq               Z`e(js                  drd"g#      e7ds               Zae(js                  dt      e7du               Zbe(js                  dvd"g#      e7dw               Zce(js                  dx      e7dy               Zde(js                  dz      e7d{               Zee(js                  d|      e7d}               Zfe(js                  d~      e7d               Zge(js                  d      e7d               Zhe(js                  dd!d"g#      e7d               Zie(js                  dd"g#      e7d               Zje(js                  d      e7d               Zke(js                  d      e7d               Zle(js                  d      e7d               Zme(js                  dd"g#      e7d               Zne(js                  d      e7d               Zod Zpe(js                  d      e7d               Zqe(js                  d      e7d               Zre(js                  dd!d"g#      e7d               Zse(js                  dd"g#      e7d               Zte(js                  d      e7d               Zue(js                  dd!d"g#      e(js                  dd!d"g#      e7dd                     Zve(js                  d      e7d               Zwe(js                  dd!d"g#      e7d               Zxe(js                  dd"g#      e7d               Zye(js                  dd!d"g#      e7d               Zze(js                  dd!d"g#      e7d               Z{e(js                  dd"g#      e7d               Z|d Z}d Z~e(js                  dd"g#      e7d               Ze(js                  d      e7d               Ze(js                  d      e7d               Ze(js                  d      d        Ze(j                  d      d        Ze(j                  d      d        Ze(j                  d      d        Ze(j                  d      d        Ze(js                  dd"g#      e7d               Ze(js                  d      e7d               Ze(js                  dd!d"g#      e(js                  dd!d"g#      e(js                  dd!d"g#      e(js                  dd!d"g#      e7ddĄ                                   Ze(js                  dū      e7dƄ               Ze(js                  dd!d"g#      e7dȄ               Ze(js                  dd"g#      e7dʄ               Ze'dk(  re(j                  ddddͬΫ       yy)zT
Vera Medical CRM Application
Flask-based CRM for managing medical assessment cases
    )Flaskrender_templaterequestredirecturl_forflashjsonifysessionabortsend_from_directory	send_fileResponse)generate_password_hashcheck_password_hash)secure_filenameNdatetime)	sanitize_phonesanitize_postal_codesanitize_emailsanitize_namesanitize_company_namesanitize_form_datavalidate_form_datafuzzy_match_scorenormalize_for_comparison)EmailMessage   zdatabase/vera_medical.dbDATABASEuploads/reportsUPLOAD_FOLDERi   MAX_CONTENT_LENGTH>   docjpgpdfpngtxtdocxjpegALLOWED_EXTENSIONSc                 z    d| v xr6 | j                  dd      d   j                         t        j                  d   v S )z"Check if file extension is allowed.   r*   )rsplitlowerappconfig)filenames    app.pyallowed_filer4   $   s@    (? R??3"1%++-<P1QQR    c                     | dz  ddS )zConvert bytes to MB stringi   .2fz MB )
size_bytess    r3   get_file_size_mbr:   )   s    K(-S11r5   c                    |sy|xs d}t        |      xs d}|xs0 |j                  dd      j                  dd      j                         }t        j                         j                  d      }| d| }t        j                  j                  t        j                  d   |      }	t        j                  t        j                  d   d	
       t        |      }
t        |	dd      5 }|j                  |
       ddd       t        j                  j                  |	      }t!        d|dt        j                         j#                         |||d| ddd| d|xs d|||f      }|S # 1 sw Y   mxY w)z5Persist a generated form template as a report record.Nztemplate.html.html _ %Y%m%d_%H%M%Sr!   Texist_okwutf-8encodingaW  
        INSERT INTO reports (
            report_title, report_type, report_date, file_name,
            file_path, file_size, file_type, case_id, doctor_id,
            status, description, notes, uploaded_by, appointment_id,
            form_template, form_html_content
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    zForm TemplatehtmldraftzForm generated from template: zGenerated from form templateSystem)r   replacetitler   nowstrftimeospathjoinr0   r1   makedirswrap_html_documentopenwritegetsize
execute_dbdate)case_id	form_namehtml_contentreport_titleuploaded_byappointment_idsafe_template_name	timestampr2   filepathrendered_htmlf	file_sizeresults                 r3   save_form_template_reportre   -   s`   ,_I(3F[9#4#4Wb#A#I#I#s#S#Y#Y#[L''8IA012Hww||CJJ7BHKK

?+d;&|4M	hg	. !	 )I  	
(4
&x!
F2 M= s   )E''E0c                 j   | syt         j                  j                  |       r| S | j                  t        j
                  d         r.t         j                  j                  t        j                  |       S t         j                  j                  t        j                  t        j
                  d   |       S )z3Return absolute path for a stored report file path.Nr!   )rN   rO   isabs
startswithr0   r1   rP   	root_path)stored_paths    r3   resolve_report_full_pathrk   ]   sr    	ww}}[!cjj9:ww||CMM;7777<<szz/'BKPPr5   c                 X    | syddl }d}| }||k7  r|}|j                  |      }||k7  r|S )z>Unescape HTML entities so they render as HTML instead of text.r=   r   N)rG   unescape)contentrG   prev_content	unescapeds       r3   unescape_htmlrq   g   sD     LI
|
# MM),	 |
# r5   c                     | syddl }t        |       } d| v sd| v r|j                  |       } d| j                         v r| S d}d| d	|  d
S )zBEnsure saved HTML files render cleanly when downloaded or printed.r=   r   Nz&lt;z&gt;z</html>a%  body{font-family:'Segoe UI','Inter',sans-serif;padding:40px;background:#fff;color:#111;line-height:1.7;}h1,h2,h3{color:#1f2937;margin-top:1.5rem;}table{width:100%;border-collapse:collapse;margin-top:1rem;}th,td{border:1px solid #e5e7eb;padding:8px;text-align:left;}.signature{margin-top:2rem;}z8<!DOCTYPE html><html><head><meta charset='utf-8'><style>z</style></head><body>z</body></html>)rG   rq   rm   r/   )rn   rG   base_styless      r3   rR   rR   u   sk    G$GFg---(GMMO#	'  Fk]Rghogpp~r5   c                    |syt        d| fd      }|syt        |      }d|j                  d      xs |  }|j                  d      xs d	}|j                  d
      xs d	}|j                  d      xs |j                  d      xs d}|j                  d      xs d}	d|j                  d      xs d d|j                  d      xs dj                          d|j                  d      xs dj                          j                         d| d| d| d|	 dd|j                  d      xs d d|j                  d      xs d g	}
|r%|
j	                  d       |
j	                  d|        t        j                  d       }t        j                  d!      }t        j                  d"      }t        t        j                  d#d$            }t        j                  d%|xs d&      }|r|r|st        d'       y(t               }||d)<   ||d*<   ||d+<   |r||d,<   |j                  d-j                  |
             	 t        j                         }t        j                  |||.      5 }|j!                  ||       |j#                  |       d/d/d/       y0# 1 sw Y   y0xY w# t$        $ r}t        d1|        d2d3| fcY d/}~S d/}~ww xY w)4zMSend a confirmation email for an appointment if SMTP settings are configured.)Tz@Appointment saved without sending email (no recipient provided).a  
        SELECT a.id, a.appointment_date, a.appointment_time, a.location,
               a.assessment_type, a.service_code, a.notes,
               c.file_case_id, cl.first_name AS claimant_first_name,
               cl.last_name AS claimant_last_name, d.name AS doctor_name
        FROM appointments a
        LEFT JOIN cases c ON a.case_id = c.id
        LEFT JOIN claimants cl ON c.claimant_id = cl.id
        LEFT JOIN doctors d ON a.doctor_id = d.id
        WHERE a.id = ?
    T)one)Fz2Appointment email not sent: appointment not found.z!Appointment Confirmation - Case #file_case_idappointment_dateTBDappointment_timedoctor_namenoteszAssigned ProviderlocationzLocation TBDzCase: #N/Az
Claimant: claimant_first_namer=   r?   claimant_last_namezDate: zTime: z
Provider: z
Location: zAssessment Type: assessment_typezService Code: service_codezAdditional Notes:
	SMTP_HOSTSMTP_USERNAMESMTP_PASSWORD	SMTP_PORT465	SMTP_FROMzno-reply@vera-medical.localzI[send_appointment_email] SMTP settings are not configured; skipping send.)Tz0Email skipped: SMTP settings are not configured.SubjectFromToCc
)contextN)Tz1Appointment confirmation email sent successfully.z/[send_appointment_email] Failed to send email: FzEmail not sent: )query_dbdictgetstripappendrN   getenvintprintr   set_contentrP   sslcreate_default_contextsmtplibSMTP_SSLloginsend_message	Exception)r]   email_toemail_cc
extra_noteappointmentsubjectdate_strtime_str
doctor_strlocation_str
body_lines	smtp_host	smtp_user	smtp_pass	smtp_port	smtp_frommessager   serverexcs                       r3   send_appointment_emailr      s   W 
 	
&K J{#K1+//.2Q2cUc1deG12;eH12;eH/b;??73KbObJ??:.@.L +//.1:U;<
koo&;<BIIKLA{_sOtOzxz  OB  OB  OD  NE  	F  	L  	L  	N




ZL!
\N#

KOO,=>G%HI
8AEBC
J "/
|<=		+&I		/*I		/*IBIIk512I		+y'Q4QRI)	YZGnG GIGFOGDM 		*-./,,.iGD 	)LLI.(	) I	) I /?uEF(.../s<   ,J& -$JJ& J#J& #J& &	K/K	K	Kc                       y)zFEnsure v_appointments_full view exists - no-op, kept for compatibilityNr8   r8   r5   r3   ensure_appointments_viewr      s    r5   c                      t        j                  t        j                  d   d      } t         j                  | _        | j                  d       | j                  d       | S )zGet database connectionr   g      >@)timeoutzPRAGMA journal_mode=WALzPRAGMA busy_timeout=30000)sqlite3connectr0   r1   Rowrow_factoryexecute)conns    r3   get_dbr      sF    ??3::j14@D{{DLL*+LL,-Kr5   Fc                     t               }|j                  | |      }|j                         }|j                          |r	|r|d   S dS |S )zQuery database helperr   N)r   r   fetchallclose)queryargsru   r   currvs         r3   r   r      sE    8D
,,ud
#C	BJJL&)RBqE1T1r1r5   c                     t               }|j                  | |      }|j                          |j                  }|j	                          |S )z1Execute database command (INSERT, UPDATE, DELETE))r   r   commit	lastrowidr   )r   r   r   r   r   s        r3   rV   rV      s:    8D
,,ud
#CKKMIJJLr5   c                 B     t        j                          fd       }|S )z%Decorator to require login for routesc                  L    dt         vrt        t        d            S  | i |S )Nuser_idr   )r
   r   r   )r   kwargsrb   s     r3   decorated_functionz*login_required.<locals>.decorated_function   s+    G#GG,--$!&!!r5   )	functoolswraps)rb   r   s   ` r3   login_requiredr      s&    __Q" " r5   c                     t               } | j                  d       | j                  dd      j                         }|s|t        d      }| j                  dd|f       | j	                          t        d       t        d       t        d       t        d	       t        d
       t        d       t        d       | j                          y)z*Initialize users table if it doesn't existz
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    z'SELECT id FROM users WHERE username = ?)adminadmin123zO
            INSERT INTO users (username, password_hash) VALUES (?, ?)
        r   z<============================================================z!DEFAULT LOGIN CREDENTIALS CREATEDzUsername: adminz5Password: [Check documentation or change in database]u9   ⚠️  IMPORTANT: Change default password in production!N)r   r   fetchoner   r   r   r   )r   r   password_hashs      r3   init_users_tabler      s    8DLL  	 LLBJOXXZE.z: }%	' 	h12h EFIJhJJLr5   z/loginGETPOST)methodsc                     t         j                  dk(  rt         j                  j                  d      } t         j                  j                  d      }t	               }|j                  d| f      j                         }|j                          |rGt        |d   |      r8|d   t        d<   |d   t        d<   t        dd	       t        t        d
            S t        dd       t        d      S )z
Login pager   usernamepasswordz&SELECT * FROM users WHERE username = ?r   idr   zLogin successful!successindexzInvalid username or passworderrorz
login.html)r   methodformr   r   r   r   r   r   r
   r   r   r   r   )r   r   r   users       r3   r   r   !  s     ~~<<##J/<<##J/x||4xk

(* 	 	

'_(=xH!%dGI"&z"2GJ%y1GG,--0':<((r5   z/logoutc                  j    t        j                          t        dd       t        t	        d            S )LogoutzYou have been logged outr   r   )r
   clearr   r   r   r8   r5   r3   logoutr   8  s(     MMO	
$i0GG$%%r5   z/favicon.icoc                      t        d      S )zFHandle favicon requests - return 204 No Content to suppress 404 errors   )status)r   r8   r5   r3   faviconr   ?  s     3r5   /c                     t               } i }| j                  d      j                         d   |d<   | j                  d      j                         d   |d<   |d   |d   z
  |d<   | j                  d      j                         d   |d<   | j                  d	      j                         d   |d
<   | j                  d      j                         }| j                  d      j                         }| j	                          t        d|||      S )zDashboard home pagezSELECT COUNT(*) FROM casesr   total_caseszPSELECT COUNT(*) FROM cases WHERE file_close_date IS NULL OR file_close_date = ''active_casesclosed_caseszSELECT COUNT(*) FROM clientstotal_clientszSELECT COUNT(*) FROM claimantstotal_claimantsz^
        SELECT * FROM v_cases_full
        ORDER BY file_open_date DESC
        LIMIT 10
    z=
        SELECT * FROM v_cases_by_client
        LIMIT 5
    zdashboard.html)statsrecent_casestop_clients)r   r   r   r   r   r   )r   r   r   r   s       r3   r   r   H  s)    8D E  <<(DENNPQRSE- !LLZhjE.
 "-053HHE. "\\*HIRRTUVWE/  $||,LMVVXYZ[E
 << ! 	 
	  ,,   	 
 
 	JJL+$&2%02 2r5   z/casesc            	         t         j                  j                  ddt              } t         j                  j                  dd      }t         j                  j                  dd      }d}| dz
  |z  }t	               }d	}g }|r |d
z  }d| d}|j                  ||||g       |dk(  r|dz  }n
|dk(  r|dz  }d| d}	|j                  |	|      j                         d   }
|dz  }|j                  ||g       |j                  ||      j                         }|j                          |
|z   dz
  |z  }t        d|| ||
||      S )z(List all cases with filtering and searchpager-   typesearchr=   r   all2   $SELECT * FROM v_cases_full WHERE 1=1z AND (
            claimant_first_name LIKE ? OR 
            claimant_last_name LIKE ? OR
            file_case_id LIKE ? OR
            record_id LIKE ?
        )%active6 AND (file_close_date IS NULL OR file_close_date = '')closed: AND file_close_date IS NOT NULL AND file_close_date != ''zSELECT COUNT(*) FROM ()r   z. ORDER BY file_open_date DESC LIMIT ? OFFSET ?zcases_list.html)casesr   total_pagestotalr   r   )r   r   r   r   r   extendr   r   r   r   r   )r   r   r   per_pageoffsetr   r   paramssearch_termcount_queryr  r  r  s                r3   
cases_listr
  z  sq    <<FAC0D\\h+F\\h.FHQh("F8D 3EF  	 &m{KkJKII	8	MM +5'3KLLf-668;E 
==E
MM8V$%LL'002EJJL8#a'H4K,$"%0$ & &( (r5   z/cases/<int:case_id>c                    t         j                  j                  d      xs t         j                  xs t	        d      }t               }|j                  d| f      j                         }|s0t        dd       |j                          t        t	        d            S |j                  d| f      j                         }d}|d   r$|j                  d	|d   f      j                         }d}|d
   r$|j                  d|d
   f      j                         }d}|d   r$|j                  d|d   f      j                         }d}|d   r$|j                  d|d   f      j                         }d}	|d   r$|j                  d|d   f      j                         }	|j                  d| f      j                         }
|
D cg c]  }t        |       }
}|j                  d| f      j                         }|D cg c]  }t        |       }}|j                  d| f      j                         }|j                  d| f      j                         }|D cg c]  }t        |       }}|D cg c]  }t        |       }}g }|r<|j                  d|d   f      j                         }|D cg c]  }t        |       }}|j                          t        d||||||	||
||||      S c c}w c c}w c c}w c c}w c c}w )zView single case details	return_tor
  z5
        SELECT * FROM v_cases_full WHERE id = ?
    Case not foundr    SELECT * FROM cases WHERE id = ?Nclaimant_id$SELECT * FROM claimants WHERE id = ?lawyer_firm_id'SELECT * FROM lawyer_firms WHERE id = ?lawyer_contact_id*SELECT * FROM lawyer_contacts WHERE id = ?	client_id"SELECT * FROM clients WHERE id = ?client_contact_idz*SELECT * FROM client_contacts WHERE id = ?z
        SELECT * FROM appointments 
        WHERE case_id = ?
        ORDER BY appointment_date DESC, appointment_time DESC
    z}
        SELECT * FROM v_invoices_full
        WHERE case_id = ?
        ORDER BY invoice_date DESC, invoice_number DESC
    zq
        SELECT * FROM reports
        WHERE case_id = ?
        ORDER BY report_date DESC, uploaded_at DESC
    zv
        SELECT * FROM case_reports
        WHERE case_id = ?
        ORDER BY report_date DESC, uploaded_at DESC
    z
            SELECT * FROM claimant_name_history
            WHERE claimant_id = ?
            ORDER BY changed_date DESC, id DESC
        r   zcase_view.html)caseclaimantlawyer_firmlawyer_contactclientclient_contactinvoicesappointmentsreportscase_reportsname_historyr  )r   r   r   referrerr   r   r   r   r   r   r   r   r   r   )rX   r  r   r  case_detailsr  r  r  r  r  r  aptr  invr   r!  rcrr"  nhs                       r3   	case_viewr*    sR   
   -Z1A1AZW\EZI8D<< 
XZ 	 (

-.. << BWJOXXZLHM"<< F ,] ;=??Gxz 	 K$%ll#L$01A$B#DFFNhj 	 N'(&R'34G'H&JLLTHJ 	 FK B*;79;;C8: 	 N'(&R'34G'H&JLLTHJ 	 << ! 	 XZ	  *66#DI6L6 ||  	 XZ	  &..cS	.H. ll  	 XZ	  << ! 	 XZ	  !((1tAw(G('34DH4L4 L|| % tn	  !)
	 	
 ,88RR88JJL+*"*%0(6 &(6"*&2!(&2&2#,. .U 7 /" )4 9s   9K 2K%K*$K/$K4z
/cases/newc                  8"   t         j                  dk(  rt        t         j                  j	                               } t        |       \  }}|s,|D ]  }t        |d        t        t         j                        S 	 t               }| j                  d      }|s9|j                  d      j                         }|d   r|d   nd}t        |dz         }|j                  d      j                         }|d	   r|d	   nd}t        |dz         }		 |j                  d
|	f      j                         }
|
snt        t        |	      dz         }	<d}| j                  d      r| j                  d      rt        | j                  d            }t        | j                  d            }| j                  d      }|r^t        |      }| d| j!                         }t#        d|||| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d|f       |j                  d|f      j                         }|rB|j%                          t        d| d| d|d    dd       t        t'        d|d    !            S t        d"| d| d#d       n |j                  d$||f      j                         }|r|d    }t        d"| d| d       n| d| j!                         }t#        d%|||| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs df      }t        d&d'       d}| j                  d(      r/t)        | j                  d(            }|j                  d)|f      j                         }|r|d    }t        d*|d+    d       n|j                  d,      j+                         }d}d-}|D ]  }t-        ||d+         }||kD  s|}|} |r|d    }t        d.|d+    d       n}t#        d/|| j                  d0      xs d| j                  d1      xs d| j                  d2      xs d| j                  d3      xs d| j                  d4      xs df      }t        d5d'       d}| j                  d6      rKt)        | j                  d6            }|j                  d7|f      j                         }|r|d    }t        d8|d9    d       n|j                  d:      j+                         }d}d-}|D ]  }t-        ||d9         }||kD  s|}|} |r|d    }t        d;|d9    d       nt#        d<|| j                  d=      xs d| j                  d>      xs d| j                  d?      xs d| j                  d@      xs d| j                  dA      xs d| j                  dB      xs d| j                  dC      xs df      }d}| j                  dD      s| j                  dE      r]t#        dF| j                  dD      xs d| j                  dE      xs d| j                  dG      xs d|| j                  dH      xs df      }t#        dI|	|| j                  dJ      xs d| j                  dK      xs d| j                  dL      | j                  dM      xs d| j                  dN      xs d| j                  dO      xs d||||| j                  dP      xs ddQf      } t         j                  j/                  dR      }!|!r|!dS   rt         j                  j/                  dT      }"t         j                  j/                  dU      }#t         j                  j/                  dV      }$t         j                  j/                  dW      }%t         j                  j/                  dX      }&t         j                  j/                  dY      }'t         j                  j/                  dZ      }(t         j                  j/                  d[      })t         j                  j/                  d\      }*t         j                  j/                  d]      }+t         j                  j/                  d^      },t         j                  j/                  d_      }-t         j                  j/                  d`      }.t         j                  j/                  da      }/t         j                  j/                  db      }0t         j                  j/                  dc      }1t         j                  j/                  dd      }2t         j                  j/                  de      }3t         j                  j/                  df      }4t         j                  j/                  dg      }5t         j                  j/                  dh      }6t         j                  j/                  di      }7t         j                  j/                  dj      }8t         j                  j/                  dk      }9t         j                  j/                  dl      }:t         j                  j/                  dm      };t         j                  j/                  dn      }<t         j                  j/                  do      }=t         j                  j/                  dp      }>t         j                  j/                  dq      }?t         j                  j/                  dr      }@t1        |!      D ]  \  }A}B|Bs
t3               }Ct#        dsg | BAt5        |"      k  r|"A   ndAt5        |$      k  r|$A   ndAt5        |&      k  r|&A   ndAt5        |(      k  r|(A   ndAt5        |)      k  r|)A   ndAt5        |*      k  r|*A   ndAt5        |+      k  r|+A   ndAt5        |-      k  r|-A   ndAt5        |.      k  r|.A   nddCAt5        @      k  r@A   ndAt5        |,      k  r|,A   ndtdSAt5        |#      k  r|#A   nduAt5        |%      k  r|%A   ndAt5        |'      k  r|'A   ndAt5        |/      k  r
|/A   dvk(  rdndSAt5        |0      k  r|0A   ndAt5        |3      k  r|3A   ndAt5        |4      k  r|4A   ndAt5        |5      k  r
|5A   dvk(  rdndSAt5        |6      k  r|6A   ndAt5        |7      k  r
|7A   dvk(  rdndSAt5        |8      k  r|8A   ndAt5        |9      k  r
|9A   dvk(  rdndSAt5        |:      k  r
|:A   dvk(  rdndSAt5        |;      k  r|;A   ndAt5        |<      k  r|<A   ndAt5        |=      k  r|=A   rt7        |=A         ndAt5        |>      k  r|>A   ndAt5        |?      k  r|?A   ndAt5        |1      k  r
|1A   dvk(  rdndSAt5        |2      k  r|2A   nd        |j%                          t        dwd'       t        t'        d| !      dxz         S t               }|j                  d{      j+                         }F|j                  d|      j+                         }G|j                  d}      j                         }|d   r|d   nd}t        |dz         }H|j%                          g }I	 dSdl }JtC        d~dd      5 }KtE        |K       JjG                  |K      }L|LD ]m  }M|Mj                  dd      j!                         }N|Ns&Mj                  dd      xs Mj                  dd      }OIjI                  N|Oj!                         d       o 	 ddd       dSdl&m'}P |PjQ                         jS                         }Qd}Rd}StU        dddFGHI|Q|R|Sdd      S # t8        $ rf}Dt        dyt        D       d       	 dSdl}E|Ej=                          n# t8        $ r Y nw xY wdzt?               v rj%                         nd Y d}D~Dd}D~Dww xY w# 1 sw Y   xY w# t8        $ r}DtK        dD        Y d}D~Dd}D~Dww xY w)zCreate new caser   r   rv   a  
                    SELECT MAX(CAST(file_case_id AS INTEGER)) as max_case 
                    FROM cases 
                    WHERE file_case_id IS NOT NULL 
                    AND file_case_id != '' 
                    AND file_case_id GLOB '[0-9]*'
                max_casei@ r-   z
                SELECT MAX(CAST(record_id AS INTEGER)) as max_record_id 
                FROM cases 
                WHERE record_id IS NOT NULL 
                AND record_id != '' 
                AND record_id GLOB '[0-9]*'
            max_record_idz(SELECT id FROM cases WHERE record_id = ?Nr~   r   use_existing_claimant_idr?   a  
                        UPDATE claimants SET
                            first_name = ?, last_name = ?, full_name = ?,
                            date_of_birth = COALESCE(?, date_of_birth),
                            email = COALESCE(?, email),
                            address = COALESCE(?, address),
                            city = COALESCE(?, city),
                            province = COALESCE(?, province),
                            postal_code = COALESCE(?, postal_code),
                            home_phone = COALESCE(?, home_phone),
                            work_phone = COALESCE(?, work_phone),
                            cel_phone = COALESCE(?, cel_phone)
                        WHERE id = ?
                    claimant_date_of_birthclaimant_emailclaimant_addressclaimant_cityclaimant_provinceclaimant_postal_codeclaimant_home_phoneclaimant_work_phoneclaimant_cel_phonez
                        SELECT id, file_case_id FROM cases 
                        WHERE claimant_id = ? 
                        ORDER BY file_open_date DESC, id DESC
                        LIMIT 1
                    zA case already exists for z (Case #z ). Redirecting to existing case.infor*  r   rX   Using existing claimant: z. Updated claimant information.z
                        SELECT id FROM claimants 
                        WHERE LOWER(TRIM(first_name)) = LOWER(?) 
                        AND LOWER(TRIM(last_name)) = LOWER(?)
                    w  
                            INSERT INTO claimants (
                                first_name, last_name, full_name, date_of_birth, email,
                                address, city, province, postal_code,
                                home_phone, work_phone, cel_phone
                            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                        zNew claimant createdr   client_company_namez
                    SELECT id, company_name FROM clients 
                    WHERE LOWER(TRIM(company_name)) = LOWER(?)
                zUsing existing client: company_namezR
                        SELECT id, company_name FROM clients
                    g?zUsing similar existing client:   
                            INSERT INTO clients (
                                company_name, address, city, province, postal_code,
                                assistant_phone
                            ) VALUES (?, ?, ?, ?, ?, ?)
                        client_addressclient_cityclient_provinceclient_postal_codeclient_assistant_phonezNew client createdlawyer_firm_namez
                    SELECT id, firm_name FROM lawyer_firms 
                    WHERE LOWER(TRIM(firm_name)) = LOWER(?)
                zUsing existing law firm: 	firm_namezT
                        SELECT id, firm_name FROM lawyer_firms
                    z!Using similar existing law firm: z
                            INSERT INTO lawyer_firms (
                                firm_name, firm_alias, address, city, province, postal_code, phone, email
                            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                        
firm_aliasfirm_address	firm_cityfirm_provincefirm_postal_code
firm_phone
firm_emaillawyer_contact_first_namelawyer_contact_last_namez
                    INSERT INTO lawyer_contacts (
                        first_name, last_name, title, lawyer_firm_id, status
                    ) VALUES (?, ?, ?, ?, ?)
                lawyer_contact_titler   a  
                INSERT INTO cases (
                    record_id, file_case_id, assessment_type, case_notes,
                    file_open_date, file_close_date, date_of_loss,
                    service_location,
                    claimant_id, lawyer_firm_id, lawyer_contact_id,
                    client_id, client_file_number, created_by
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            r   
case_notesfile_open_datefile_close_datedate_of_lossservice_locationclient_file_numberrI   appointment_date[]r   appointment_time[]appointment_timezone[]appointment_doctor[] appointment_provider_specialty[]appointment_specialist_number[]appointment_clinic_location[]appointment_location[]appointment_service_code[]appointment_assessment_type[]appointment_description[]appointment_status[]appointment_date_confirmed[]appointment_date_received[]'appointment_pil_instructions_received[]"appointment_pil_instruction_type[]appointment_loi_received[]appointment_loi_received_date[]"appointment_report_deadline_date[]appointment_report_due_date[].appointment_confirmation_deposit_letter_sent[].appointment_confirmation_deposit_letter_date[])appointment_confirmation_sent_to_doctor[].appointment_confirmation_sent_to_doctor_date[]appointment_in_calendar[]appointment_retainer_required[]#appointment_retainer_requested_on[]"appointment_retainer_received_on[]appointment_retainer_amount[]appointment_atp_requested_on[]appointment_atp_received_on[]appointment_notes[]a  
                            INSERT INTO appointments (
                                case_id, appointment_date, appointment_time, doctor_name,
                                specialist_number, location, service_code, assessment_type,
                                description, date_confirmed, date_received, date_released,
                                reference, notes, status, invoiced,
                                timezone, provider_specialty, clinic_location,
                                pil_instructions_received, pil_instruction_type,
                                report_deadline_date, report_due_date,
                                confirmation_deposit_letter_sent, confirmation_deposit_letter_date,
                                confirmation_sent_to_doctor, confirmation_sent_to_doctor_date,
                                in_calendar, retainer_required, retainer_requested_on,
                                retainer_received_on, retainer_amount,
                                atp_requested_on, atp_received_on,
                                loi_received, loi_received_date
                            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                        	scheduledAmerica/Edmonton1zCase created successfully!#appointments-sectionzError creating case: r   BSELECT id, first_name, last_name FROM claimants ORDER BY last_name:SELECT id, company_name FROM clients ORDER BY company_namez
        SELECT MAX(CAST(file_case_id AS INTEGER)) as max_case 
        FROM cases 
        WHERE file_case_id IS NOT NULL 
        AND file_case_id != '' 
        AND file_case_id GLOB '[0-9]*'
    data/FIle maker & WMA codes.csvr'  	utf-8-sigrE   CODEr=   DESCRIPTION DESCRIPTIONcodedescriptionError loading service codes: rW   case_form.html)r  rX   	claimantsclientsnext_case_idservice_codes
today_dateappointment_reference_numberappointment_case_datar  r  )+r   r   r   r   to_dictr   r   r   urlr   r   r   r   strr   r   r   rV   r   r   r   r   r   getlist	enumerategenerate_invoice_numberlenfloatr   	traceback	print_exclocalscsvrS   next
DictReaderr   r   r   rW   today	isoformatr   )Tdatais_validerrorsr   r   rv   rd   r,  r-  	record_idexistingr  
first_name	last_nameuse_existing_claimant	full_nameexisting_caseexisting_claimantr  r=  existing_clientall_clients
best_match
best_scorer  scorer  rE  existing_firm	all_firmsfirmr  rX   appointment_datesappointment_timesappointment_timezonesappointment_doctors appointment_provider_specialtiesappointment_specialist_numbersappointment_clinic_locationsappointment_locationsappointment_service_codesappointment_assessment_typesappointment_descriptionsappointment_statusesappointment_date_confirmedappointment_date_received%appointment_pil_instructions_received!appointment_pil_instruction_typesappointment_loi_receivedappointment_loi_received_dates!appointment_report_deadline_datesappointment_report_due_dates,appointment_confirmation_deposit_letter_sent-appointment_confirmation_deposit_letter_dates'appointment_confirmation_sent_to_doctor-appointment_confirmation_sent_to_doctor_datesappointment_in_calendarappointment_retainer_required!appointment_retainer_requested_on appointment_retainer_received_onappointment_retainer_amountsappointment_atp_requested_onappointment_atp_received_onappointment_notes_listirw   	referenceer  r  r  r  r  r  rb   readerrowr  descrW   r  r  r  sT                                                                                       r3   case_newr    s    ~~!',,"6"6"89 .d3& &eW%&GKK(({	98D  88N3L '  XZ  28
1C6*-"8a<0 \\ #    8>o7NF?3TYMMA-.I <<(RU^T`ajjlI 23		  Kxx-.488<P3Q*4884I+JK
)$((3G*HI	 )-1K(L%("%&;"<K $.,a	{ ; A A CI   #!!!9:Bd!12:d!34<19T!45=!78@D!67?4!67?4!56>$#< %)LL 2
 &%)
 *2 " %

 ::,a	{RZ[hiw[xZy  zZ  [  ]c  d']SWEX(YZZ5j\9+Mlmouv )- 6 %i0	)2 3;(*	 & )&7&= 9*QykRTZ[ (2l!I;$?$E$E$G	&0 2 '%% HH%=>F$ HH%56>$ HH%78@D HH_5= HH%89AT HH%;<D HH%:;Ct HH%:;Ct HH%9:Bd'( 4i@ Ixx-.4TXX>S5TU #',, 0 #_#& '/hj  
 # / 5I3ON4S3TUW]^ #',, 0 #!   "&J!%J"- 0 1,~@V W :-).J)/J	0 "$.t$4	 ?
>@Z?[\^de %/ 0 ) HH%56>$ HH]3;t HH%67?4 HH%9:Bd HH%=>F$%	 2I> "Nxx*+1$((;M2NO	 !% .  \!# $,8: 
 !%24%8N5mK6P5QRTZ[ !% . !!  "&J!%J ) . 1)T+=N O :-).J)-J	. ")3D)9 A*[BYAZ[]cd *4 5
 & HH\2:d HH^4< HH[19T HH_5= HH%78@D HH\2:d HH\2:d		*  !%xx34A[8\$. 0
 HH89ATHH78@DHH34<"HHX&.$	
%! ! " *+3t&.$)**+3t(0D+,4!-.6$G4 !( 4 45I J %6q%9$+LL$8$89M$N!(/(<(<=U(V%&-ll&:&:;Q&R#3:<<3G3GHj3k0181E1EFg1h./6||/C/CDc/d,(/(<(<=U(V%,3LL,@,@A],^)/6||/C/CDc/d,+2<<+?+?@[+\('.||';';<R'S$-4\\-A-AB`-a*,3LL,@,@A^,_)8?8L8LMv8w54;LL4H4HIm4n1+2<<+?+?@\+](181E1EFg1h.4;LL4H4HIm4n1/6||/C/CDc/d,?F||?S?S  UE  @F<@G@T@T  VF  AG=:A,,:N:NOz:{7@G@T@T  VF  AG=*1,,*>*>?Z*['070D0DEf0g-4;LL4H4HIn4o13:<<3G3GHl3m0/6||/C/CDc/d,/6||/C/CDd/e,.5ll.B.BCb.c+)0)=)=>S)T&+45F+G :'A''$;$=	" $ %#%,% 56<M8N4N-a0TX% 78#>Q:R6R/2X\	%
 BCSIgEhAh:1=nr% 9:C@U<V8V1!4\`% =>D]@^<^5a8dh% @A3GcCd?d8;jn% <=sC[?\;\4Q7bf% >?E_A`=`6q9fj% =>D]@^<^5a8dh% !% &% :;SAW=X9X215^b% 893?S;T7T03Ze%  !%" 9:C@U<V8V1!4\n#%$ DEsKkGlCl<Q?rv%%& @A3GcCd?d8;jn'%( #$c*O&P"PUz{|U}  BE  VEA  LM)%* EFLmHnDn=a@tx+%, EFLmHnDn=a@tx-%. @A3GcCd?d8;jn/%0 #$c*V&W"W  ]I  JK  ]L  PS  ]SA  Z[1%2 QRTW  YF  UG  QGI!L  MQ3%4 #$c*Q&R"RW~  @A  XB  FI  XIA  PQ5%6 QRTW  YF  UG  QGI!L  MQ7%8 #$c*A&B"BG^_`GaehGhAop9%: #$c*G&H"HMjklMmqtMtA{|;%< EFLmHnDn=a@tx=%> DEsKkGlCl<Q?rv?%@ HI3OkKlGl  rN  OP  rQE">q"AB  X\A%B @A3GcCd?d8;jnC%D ?@#FaBb>b7:hlE%F #$c*B&C"CH`abHcgjHjAqrG%H BCSIgEhAh:1=nrI%!5:x JJL.	:GKAD[[\\ 8DabkkmIllWXaacG \\  	 
  &,J%7vj!UHx!|$LJJL M3S;O 	STG^^A&F wwvr*00277>26T#''-QS:TD!(( $'+zz|* 		" '')J $(  +"!%#,!(&2'4$.6R/D%)(,. .o  	9)#a&2G< ##% "fh.DJJLD	9>	 	  -aS12s   9JAA: <GAA: EAA: WAA: -LAA: AC8 #AAC,(A
AC,@3AC8 A:	AC)BAC$BAB1B0AC$B1	AB=B:AC$B<AB=B=!AC$C$AC)C,AC5C1AC8 C8	ADDADDADz/cases/<int:case_id>/editc                 "   t               }	 t        j                  dk(  r"t        j                  j	                         }t        |      }t        |      \  }}|s<|D ]  }t        |d        |j                          t        t        j                        S 	 |j                  d| f      j                         }|s0|j                          t        dd       t        t        d            S |r|d   nd}|j                  d      r-|j                  d	      r|j                  d
      }|r=t        |      }t        d|j                  d       d|j                  d	       d       n|r|d   r|j                  d|d   f      j                         }	|	r|	d   nd}
|j                  d	      }|j                  d       d|j                  d	       j!                         }t#        d|j                  d      |j                  d	      ||j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|d   f       |d   }|
rd|ra|
j!                         |j!                         k7  r?t#        d||
|t%        j                  dd      d|  df       n|j                  d       d|j                  d	       j!                         }t#        d|j                  d      |j                  d	      ||j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs df      }|r|d    nd}|j                  d!      r|r|d    rt#        d"|j                  d!      |j                  d#      xs d|j                  d$      xs d|j                  d%      xs d|j                  d&      xs d|j                  d'      xs d|d    f       |d    }nt#        d(|j                  d!      |j                  d#      xs d|j                  d$      xs d|j                  d%      xs d|j                  d&      xs d|j                  d'      xs df      }|j                  d)      }|s	|r|d)   nd}t#        d*||j                  d+      |j                  d,      |j                  d-      |j                  d.      xs d|j                  d/      xs d|j                  d0      xs d|||j                  d1      xs dd| f       t        j                  j'                  d2      }|r|d3   rt        j                  j'                  d4      }t        j                  j'                  d5      }t        j                  j'                  d6      }t        j                  j'                  d7      }t        j                  j'                  d8      }t        j                  j'                  d9      }t        j                  j'                  d:      }t        j                  j'                  d;      }t        j                  j'                  d<      }t        j                  j'                  d=      }t        j                  j'                  d>      }t        j                  j'                  d?      }t        j                  j'                  d@      }t        j                  j'                  dA      }t        j                  j'                  dB      }t        j                  j'                  dC      }t        j                  j'                  dD      } t        j                  j'                  dE      }!t        j                  j'                  dF      }"t        j                  j'                  dG      }#t        j                  j'                  dH      }$t        j                  j'                  dI      }%t        j                  j'                  dJ      }&t        j                  j'                  dK      }'t        j                  j'                  dL      }(t        j                  j'                  dM      })t        j                  j'                  dN      }*t        j                  j'                  dO      }+t        j                  j'                  dP      },t        j                  j'                  dQ      }-t        j                  j'                  dR      }.d3}/t)        |      D ]  \  }0}1|1s
t+               }2t#        dSg | |1|0t-        |      k  r||0   nd|0t-        |      k  r||0   nd|0t-        |      k  r||0   nd|0t-        |      k  r||0   nd|0t-        |      k  r||0   nd|0t-        |      k  r||0   nd|0t-        |      k  r||0   nd|0t-        |      k  r||0   nd|0t-        |      k  r||0   ndd|2|0t-        |.      k  r|.|0   nd|0t-        |      k  r||0   ndTd3|0t-        |      k  r||0   ndU|0t-        |      k  r||0   nd|0t-        |      k  r||0   nd|0t-        |      k  r
||0   dVk(  rdWnd3|0t-        |      k  r||0   nd|0t-        |!      k  r|!|0   nd|0t-        |"      k  r|"|0   nd|0t-        |#      k  r
|#|0   dVk(  rdWnd3|0t-        |$      k  r|$|0   nd|0t-        |%      k  r
|%|0   dVk(  rdWnd3|0t-        |&      k  r|&|0   nd|0t-        |'      k  r
|'|0   dVk(  rdWnd3|0t-        |(      k  r
|(|0   dVk(  rdWnd3|0t-        |)      k  r|)|0   nd|0t-        |*      k  r|*|0   nd|0t-        |+      k  r|+|0   rt/        |+|0         nd|0t-        |,      k  r|,|0   nd|0t-        |-      k  r|-|0   nd|0t-        |      k  r
||0   dVk(  rdWnd3|0t-        |       k  r| |0   nd       |/dWz  }/ |/d3kD  rt        dX|/ dYdZ       nt        d[dZ       nt        d[dZ       |j                          t        t        d\| ]      d^z         S 	 |j                  d| f      j                         }6|6s0t        dd       |j                          t        t        d            S t;        |6      }6d}7|6j                  d      r1|j                  dc|6d   f      j                         }8|8rt;        |8      }7d}9|6j                  d       r1|j                  dd|6d    f      j                         }:|:rt;        |:      }9d};|6j                  de      r1|j                  df|6de   f      j                         }<|<rt;        |<      };d}=|6j                  dg      r1|j                  dh|6dg   f      j                         }>|>rt;        |>      }=d}?|6j                  d)di      |7r|7j                  djdi      ndi|7r|7j                  ddi      ndi|9r|9j                  dkdi      ndidl}@|j                  dm      j=                         }A|j                  dn      j=                         }B|j                          g }C	 d3dl}DtA        dodpdqr      5 }EtC        |E       DjE                  |E      }F|FD ]m  }G|Gj                  dsdi      j!                         }H|Hs&Gj                  dtdi      xs Gj                  dudi      }ICjG                  H|Ij!                         dv       o 	 ddd       d3dxl%m&}J |JjO                         jQ                         }KtS        dy|6|6dz   |7|9|;|=ABC|K|?@{      S # t0        $ r`}3d3dl}4d3dl}5|4j7                  |5j8                  _       t        d`d       |j                          t        t        da| ]            cY d}3~3S d}3~3ww xY w# t0        $ r^}3d3dl}4d3dl}5|4j7                  |5j8                  _       t        dbd       |j                          t        t        d            cY d}3~3S d}3~3ww xY w# 1 sw Y   xY w# t0        $ r}3tI        dw|3        Y d}3~3?d}3~3ww xY w)|zEdit existing caser   r   r  r  r
  r  Nr~   r   r.  r:  r?   r8  z,SELECT last_name FROM claimants WHERE id = ?r  a  
                            UPDATE claimants SET
                                first_name = ?, last_name = ?, full_name = ?, date_of_birth = ?, email = ?,
                                address = ?, city = ?, province = ?, postal_code = ?,
                                home_phone = ?, work_phone = ?, cel_phone = ?
                            WHERE id = ?
                        r/  r0  r1  r2  r3  r4  r5  r6  r7  z
                                INSERT INTO claimant_name_history (
                                    claimant_id, old_last_name, new_last_name, changed_by, notes
                                ) VALUES (?, ?, ?, ?, ?)
                            r   rI   z"Name changed via case edit (Case #r   r;  r  r<  a  
                            UPDATE clients SET
                                company_name = ?, address = ?, city = ?, province = ?,
                                postal_code = ?, assistant_phone = ?
                            WHERE id = ?
                        r?  r@  rA  rB  rC  r>  rv   ag  
                    UPDATE cases SET
                        file_case_id = ?,
                        assessment_type = ?,
                        case_notes = ?,
                        file_open_date = ?,
                        file_close_date = ?,
                        date_of_loss = ?,
                        service_location = ?,
                        claimant_id = ?,
                        client_id = ?,
                        client_file_number = ?,
                        updated_by = ?,
                        update_date = CURRENT_TIMESTAMP
                    WHERE id = ?
                r   rP  rQ  rR  rS  rT  rU  rV  r   rW  rX  rY  rZ  r[  r\  r]  r^  r_  r`  ra  rb  rc  rd  re  rf  rg  rh  ri  rj  rk  rl  rm  rn  ro  rp  rq  rr  rs  rt  ru  aB  
                                INSERT INTO appointments (
                                    case_id, appointment_date, appointment_time, doctor_name,
                                    specialist_number, location, service_code, assessment_type,
                                    description, date_confirmed, date_received, date_released,
                                    reference, notes, status, invoiced,
                                    timezone, provider_specialty, clinic_location,
                                    pil_instructions_received, pil_instruction_type,
                                    report_deadline_date, report_due_date,
                                    confirmation_deposit_letter_sent, confirmation_deposit_letter_date,
                                    confirmation_sent_to_doctor, confirmation_sent_to_doctor_date,
                                    in_calendar, retainer_required, retainer_requested_on,
                                    retainer_received_on, retainer_amount,
                                    atp_requested_on, atp_received_on,
                                    loi_received, loi_received_date
                                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                            rv  rw  rx  r-   zCase updated successfully! z appointment(s) added.r   zCase updated successfully!r*  r9  ry  filez<An error occurred while updating the case. Please try again.	case_editz;An error occurred while loading the case. Please try again.r  r  r  r  r  r  r=   r  r=  )rv   r~   r   r<  rz  r{  r|  r'  r}  rE   r~  r  r  r  r  r  r  r   )r  rX   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   rV   r
   r  r  r  r  r  r   r  sysr  stderrr   r   r  rS   r  r  r   r   r   rW   r  r  r   )LrX   r   r  r  r  r   r  r  r  current_claimantold_last_namenew_last_namer  r  file_case_id_valuer  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  appointments_addedr  rw   r  r  r  r  r  r  claimant_rowr  
client_rowr  lawyer_firm_rowr  lawyer_contact_rowr  r  r  r  r  r  rb   r  r  r  r  rW   r  sL                                                                               r3   r  r    sT    8Do/>>V#<<'')D%d+D  2$7Hf# *E%)*

,,WG $-ORYQ[ \ e e g$JJL*G4#GL$9:: ?LmM:QU8812txx@T7U,0HH5O,P),&)*?&@ 9$((CX:Y9ZZ[\`\d\dey\z[{|  E  F&=+G ,0<<8f8Em8T7V,XX`X`Xb )IY(8(E_c(,1E(F (,xx0E'F&GqRfIgHh$i$o$o$q	" $ !HH%:; HH%9:% HH%=>F$ HH%56>$ HH%78@D HH_5= HH%89AT HH%;<D HH%:;Ct HH%:;Ct HH%9:Bd)-8* '4M&B )]}?R?R?TXeXkXkXm?m& ( 
 !, - - 'J A"DWIQ O"	
 (,xx0E'F&GqRfIgHh$i$o$o$q	&0 2 !HH%:; HH%9:% HH%=>F$ HH%56>$ HH%78@D HH_5= HH%89AT HH%;<D HH%:;Ct HH%:;Ct HH%9:Bd'. ;HM+6T	8812${)C" $ !HH%:; HH%56>$ HH]3;t HH%67?4 HH%9:Bd HH%=>F$)+6 %2+$>	 %/ 0 !HH%:; HH%56>$ HH]3;t HH%67?4 HH%9:Bd HH%=>F$%	  &*XXn%=")JW~)F]a&   'HH./HH\*HH-.HH./74HH^,4HH/08DHH12:d> %,LL$8$89M$N!$):1)=(/(<(<=Q(R%,3LL,@,@AY,Z)*1,,*>*>?U*V'7>||7K7KLn7o45<\\5I5IJk5l23:<<3G3GHg3h0,3LL,@,@AY,Z)070D0DEa0b-3:<<3G3GHg3h0/6||/C/CD_/`,+2<<+?+?@V+W(181E1EFd1e.070D0DEb0c-<CLL<P<PQz<{98?8L8LMq8r5/6||/C/CD`/a,5<\\5I5IJk5l28?8L8LMq8r53:<<3G3GHg3h0CJ<<CWCW  YI  DJ@DKLLDXDX  ZJ  EKA>Ell>R>RS~>;DKLLDXDX  ZJ  EKA.5ll.B.BC^._+4;LL4H4HIj4k18?8L8LMr8s57>||7K7KLp7q43:<<3G3GHg3h03:<<3G3GHh3i029,,2F2FGf2g/-4\\-A-ABW-X*)*&/89J/K ;4+++(?(AI& (  %" '%" 0%" 9:C@Q<R8R 1! 4X\%" ;<cBU>V:V 3A 6\`	%"
 FGMkIlEl >q Arv%" =>DY@Z<Z 5a 8`d%" ABCHaDb@b 9! <hl%" DEsKgGhCh <Q ?nr%" @A3G_C`?` 8 ;fj%" BCSIcEdAd :1 =jn%" ABCHaDb@b 9! <hl%" !%%" !*%" >?E[A\=\ 6q 9bf%" <=sCW?X;X 4Q 7^i%"  !"!%"" =>DY@Z<Z 5a 8`r#%"$ HI3OoKpGp @ Cvz%%"& DEsKgGhCh <Q ?nr'%"( '(#.S*T&TY~  @A  ZB  FI  ZI  PQ)%"* IJCPqLrHr A! Dx|+%", IJCPqLrHr A! Dx|-%". DEsKgGhCh <Q ?nr/%"0 '(#.Z*[&[  aM  NO  aP  TW  aW  ^_1%"2 UVX[  ]J  YK  UK Ma P  QU3%"4 '(#.U*V&V  \C  DE  \F  JM  \M  TU5%"6 UVX[  ]J  YK  UK Ma P  QU7%"8 '(#.E*F&FKbcdKeilKlst9%": '(#.K*L&LQnopQquxQx  @A;%"< IJCPqLrHr A! Dx|=%"> HI3OoKpGp @ Cvz?%"@ LMsSoOpKp  vR  ST  vU&B1&E F  \`A%"B DEsKgGhCh <Q ?nrC%"D CDcJeFfBf ;A >lpE%"F '(#.F*G&GLdefLgknLnuvG%"H FGMkIlEl >q ArvI%"!5l /!3.w;4z *A- ;<N;OOefhqr:IF6	B 

W EH_ _``u $b	 <<:WJGPPRD(

-.. :D Hxx||$JTR_M`LbcllnL)H Fxx\\"FkIZH\]ffh
*%F Kxx !,,'PSWXhSiRkluuw/K Nxx#$!\\*VY]^qYrXtu~~  A!"45N $( 4AIx||L"=r?Ghll;;RAGvzz."=R	 abkkmIllWXaacGJJL M3S;O 	STG^^A&F wwvr*00277>26T#''-QS:TD!(( $'+zz|* 		" '')J+"!%d"* &%0(6#,!('4$.6R/DF FC  G ###4TV]^

W EFFG  /,KWU

-../F	 	  -aS12s   BAB, AAA  )d AA  
L7AA  AD# )AAD.A
AD9AD# A 	AB)A	AAB$BAB)BAB, B$AB)B)AB, B,	ADB5AADDADDADDAD DAD# D#	AED,AE E AEz/cases/<int:case_id>/deletec                     	 t        d| f       t        dd       t	        t        d            S # t        $ r"}t        dt        |       d       Y d}~:d}~ww xY w)zDelete a casezDELETE FROM cases WHERE id = ?zCase deleted successfully!r   zError deleting case: r   Nr
  rV   r   r   r  r   r   )rX   r  s     r3   case_deleter  u  s\    93gZ@*I6 GL)**  9%c!fX.889   / 	AAAz/clientsc                      t               } | j                  d      j                         }| j                          t	        d|      S )z&List all clients (insurance companies)a  
        SELECT c.*, 
               COUNT(cs.id) as case_count,
               COALESCE(SUM(CASE 
                   WHEN cs.file_close_date IS NULL OR 
                        cs.file_close_date = '' OR 
                        TRIM(cs.file_close_date) = '' 
                   THEN 1 
                   ELSE 0 
               END), 0) as active_cases
        FROM clients c
        LEFT JOIN cases cs ON c.id = cs.client_id
        GROUP BY c.id
        ORDER BY case_count DESC, c.company_name
    zclients_list.html)r  )r   r   r   r   r   )r   r  s     r3   clients_listr    sC     8Dll  	 
   	JJL.@@r5   z/clients/<int:client_id>c                 x   t         j                  j                  dd      }t         j                  j                  ddt              }d}t	               }|j                  d| f      j                         }|s0t        dd	       |j                          t        t        d
            S t         j                  j                  ddt              }d}|j                  d| f      j                         }|r|d   nd}	|dz
  |z  }
|j                  d| ||
f      j                         }|	dkD  r|	|z   dz
  |z  nd}|dk(  rd}n
|dk(  rd}nd}|dz
  |z  }|j                  || ||f      j                         }|dk(  r%|j                  d| f      j                         d   }nN|dk(  r%|j                  d| f      j                         d   }n$|j                  d| f      j                         d   }|j                  d| f      j                         }||z   dz
  |z  }|j                          t        d||||||||||	      S )zView client detailsfilterr   r   r-   r      r  zClient not foundr   r  contact_page
   zO
        SELECT COUNT(*) as count FROM client_contacts WHERE client_id = ?
    countr   z
        SELECT * FROM client_contacts WHERE client_id = ?
        ORDER BY last_name, first_name
        LIMIT ? OFFSET ?
    r   z
            SELECT * FROM v_cases_full 
            WHERE client_id = ? AND (file_close_date IS NULL OR file_close_date = '')
            ORDER BY file_open_date DESC
            LIMIT ? OFFSET ?
        r   z
            SELECT * FROM v_cases_full 
            WHERE client_id = ? AND file_close_date IS NOT NULL AND file_close_date != ''
            ORDER BY file_open_date DESC
            LIMIT ? OFFSET ?
        z
            SELECT * FROM v_cases_full 
            WHERE client_id = ?
            ORDER BY file_open_date DESC
            LIMIT ? OFFSET ?
        z
            SELECT COUNT(*) FROM v_cases_full 
            WHERE client_id = ? AND (file_close_date IS NULL OR file_close_date = '')
        z
            SELECT COUNT(*) FROM v_cases_full 
            WHERE client_id = ? AND file_close_date IS NOT NULL AND file_close_date != ''
        zK
            SELECT COUNT(*) FROM v_cases_full WHERE client_id = ?
        zY
        SELECT * FROM v_cases_full WHERE client_id = ? ORDER BY file_open_date DESC
    zclient_view.html)
r  contactsr  	all_casescase_filterr   r  r  total_contact_pagestotal_contacts)r   r   r   r   r   r   r   r   r   r   r   r   r   )r  r  r   r  r   r  r  contacts_per_pagetotal_contacts_resultr  contact_offsetr  r  cases_queryr  r  r   r  r  s                      r3   client_viewr    s   
 ,,""8U3K<<FAC0DH8D\\>MVVXF '*

/00 <<##NAC#@L LL *
xz  8M*73RSN"Q&*;;N||  &	7	9 :B	  \jlm[m>,==AFWWst h 
	  Qh("FLLy(F&CDMMOE hll $ \ $8:a) 
	 ll $ \ $8:a)
 ll $\#8:a)
  
xz  )A-(:KJJL- &R[%0t&2H[(6	8 8r5   z/clients/<int:client_id>/editc                     t               }t        j                  dk(  rt        j                  j	                         }	 t        d|j                  d      |j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d	      xs d|j                  d
      xs d| f       |j                          t        dd       t        t        d|             S |j                  d| f      j                         }|j                          t        d|      S # t        $ r2}t        dt        |       d       |j                          Y d}~td}~ww xY w)zEdit client informationr   z
                UPDATE clients SET
                    company_name = ?, company_alias = ?, address = ?,
                    city = ?, province = ?, postal_code = ?, assistant_phone = ?
                WHERE id = ?
            r=  company_aliasNaddresscityprovincepostal_codeassistant_phonezClient updated successfully!r   r  r  zError updating client: r   r  zclient_form.html)r  r   r   r   r   r  rV   r   r   r   r   r   r   r  r   r   r   )r  r   r  r  r  s        r3   client_editr    sH    8D~~||##%	  ()1T#+t (D$,'/4*+3t	  JJL0)<GMYGHH \\>MVVXFJJL-f==  	+CF84g>JJLL	s   CE 	E=(E88E=z%/clients/<int:client_id>/contacts/newc           
         t         j                  dk(  rt         j                  j                         }	 t	        d| |j                  d      |j                  d      |j                  d      xs d|j                  d      xs df       t        dd	       t        t        d
|             S t        dd|       S # t        $ r"}t        dt        |       d       Y d}~4d}~ww xY w)zCreate new client contactr   z
                INSERT INTO client_contacts (
                    client_id, first_name, last_name, title, phone
                ) VALUES (?, ?, ?, ?, ?)
            r  r  rK   NphonezContact added successfully!r   r  r  zError adding contact: r   contact_form.htmlcontactr  )r   r   r   r  rV   r   r   r   r   r   r  r   )r  r  r  s      r3   contact_newr  $  s     ~~||##%	> 
 &%!)T!)T	
 /;GMYGHH
 .	RR  	>*3q6(3W==	>s   A6B8 8	C#CC#z7/clients/<int:client_id>/contacts/<int:contact_id>/editc           	         t               }t        j                  dk(  rt        j                  j	                         }	 t        d|j                  d      |j                  d      |j                  d      xs d|j                  d      xs d|| f       |j                          t        dd	       t        t        d
|             S |j                  d|| f      j                         }|s2t        dd       |j                          t        t        d
|             S |j                          t        d||       S # t        $ r"}t        dt        |       d       Y d}~d}~ww xY w)zEdit client contactr   z
                UPDATE client_contacts SET
                    first_name = ?, last_name = ?, title = ?, phone = ?
                WHERE id = ? AND client_id = ?
            r  r  rK   Nr  zContact updated successfully!r   r  r  zError updating contact: r   zJ
        SELECT * FROM client_contacts WHERE id = ? AND client_id = ?
    zContact not foundr	  r
  r  )r  
contact_idr   r  r  r  s         r3   contact_editr  @  sC    8D~~||##%	@ 
 &%!)T!)T	 JJL19=GMYGHH
 ll 
i	 ""*(*  !7+

CDDJJL.9UU  	@,SVH5w??	@s   BD9 9	E$EE$z9/clients/<int:client_id>/contacts/<int:contact_id>/deletec                     	 t        d|| f       t        dd       t	        t        d|             S # t        $ r"}t        dt        |       d       Y d}~<d}~ww xY w)	zDelete client contactzP
            DELETE FROM client_contacts WHERE id = ? AND client_id = ?
        zContact deleted successfullyr   zError deleting contact: r   Nr  r  r  )r  r  r  s      r3   contact_deleter  j  sh    < )$	& 	,i8 GMY?@@  <(Q17;;<s   2 	AAAz/api/check/claimantc                     t         j                  j                  dd      j                         } t         j                  j                  dd      j                         }| r|st	        dg d      S t               }|j                  d| |f      j                         }|j                          |r&|D cg c]  }t        |       }}t	        d|d      S t	        dg d      S c c}w )z5Check if a claimant with the same name already existsr  r=   r  F)existsr  aN  
        SELECT id, first_name, last_name, date_of_birth, email, 
               address, city, province, postal_code,
               home_phone, work_phone, cel_phone
        FROM claimants 
        WHERE LOWER(TRIM(first_name)) = LOWER(?) 
        AND LOWER(TRIM(last_name)) = LOWER(?)
        ORDER BY id DESC
        LIMIT 10
    T)
r   r   r   r   r	   r   r   r   r   r   )r  r  r   r  cclaimants_lists         r3   api_check_claimantr  x  s     !!,399;J  b1779IY%b9::8D  	 i	 	" #+(*  	JJL+45a$q'55'
  	
 
  	 6s   1C!z/api/search/clientsc                  x   t         j                  j                  dd      } | rt        |       dk  rt	        g       S t               }|j                  dd|  dd|  df      j                         }|D cg c]!  }|d   |d   |d   |d	   |d
   |d   |d   d# }}|j                          t	        |      S c c}w )zAPI endpoint to search clientsqr=      z
        SELECT id, company_name, address, city, province, postal_code, assistant_phone
        FROM clients
        WHERE company_name LIKE ? OR company_alias LIKE ?
        ORDER BY company_name
        LIMIT 10
    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  resultss        r3   search_clientsr    s     LLS"%ECJNr{8Dll  eWA,!E7!	%' (0xz     $iN+y>F
O=)&' G  	JJL7s   4&B7z/api/search/lawyersc            	      ^   t         j                  j                  dd      } | rt        |       dk  rt	        g       S t               }|j                  dd|  df      j                         }|D cg c]  }|d   |d   |d   |d	   |d
   d }}|j                          t	        |      S c c}w )zAPI endpoint to search lawyersr  r=   r  z
        SELECT DISTINCT lf.id, lf.firm_name, lf.address, lf.city, lf.phone
        FROM lawyer_firms lf
        WHERE lf.firm_name LIKE ?
        ORDER BY lf.firm_name
        LIMIT 10
    r   r   rE  r  r   r  )r   rE  r  r   r  r  )r   r   lawyersr  r  s        r3   search_lawyersr    s     LLS"%ECJNr{8Dll  eWA, #(*    $i%y>FW G  	JJL7s   /B*z/api/search/doctorsc                     t         j                  j                  dd      } | rt        |       dk  rt	        g       S t               }|j                  dd|  dd|  dd|  dd|  df      j                         }|D cg c]9  }|d   |d   |d   |d	   |d
   |d   |d   |d   |d   |d   |d   |d   |d   d; }}|j                          t	        |      S c c}w )zAPI endpoint to search doctorsr  r=   r  aU  
        SELECT id, name, first_name, last_name, prefix, specialty, 
               phone, cell_phone, email, clinic_city, clinic_province,
               clinic_address, clinic_postal_code
        FROM doctors
        WHERE name LIKE ? OR first_name LIKE ? OR last_name LIKE ? OR specialty LIKE ?
        ORDER BY name
        LIMIT 10
    r   r   namer  r  prefix	specialtyr  
cell_phoneemailclinic_cityclinic_provinceclinic_addressclinic_postal_code)r   r!  r  r  r"  r#  r  r$  r%  r   r  r(  r)  r  )r   r   doctorsr  r  s        r3   search_doctorsr+    s,    LLS"%ECJNr{8Dll  eWA,!E7!%lawaL	AC DL8: 0   $iF,'%h-%W,'WM")*./!"67 G   	JJL7#s   >>Cz/lawyersc                  n   t         j                  j                  dd      } t               }| r0|j	                  dd|  dd|  dd|  df      j                         }n|j	                  d      j                         }|D cg c]  }t        |       }}|j                          t        d||       S c c}w )zList all lawyersr   r=   a  
            SELECT lc.*, lf.firm_name, lf.city as firm_city, lf.phone as firm_phone,
                   lf.email as firm_email, lf.address as firm_address,
                   COUNT(c.id) as case_count
            FROM lawyer_contacts lc
            LEFT JOIN lawyer_firms lf ON lc.lawyer_firm_id = lf.id
            LEFT JOIN cases c ON lc.id = c.lawyer_contact_id
            WHERE lc.first_name LIKE ? OR lc.last_name LIKE ? OR lf.firm_name LIKE ?
            GROUP BY lc.id
            ORDER BY lc.last_name, lc.first_name
        r   a  
            SELECT lc.*, lf.firm_name, lf.city as firm_city, lf.phone as firm_phone,
                   lf.email as firm_email, lf.address as firm_address,
                   COUNT(c.id) as case_count
            FROM lawyer_contacts lc
            LEFT JOIN lawyer_firms lf ON lc.lawyer_firm_id = lf.id
            LEFT JOIN cases c ON lc.id = c.lawyer_contact_id
            GROUP BY lc.id
            ORDER BY lc.last_name, lc.first_name
        zlawyers_list.html)r  r   	r   r   r   r   r   r   r   r   r   )r   r   r  lawyers       r3   lawyers_listr/    s     \\h+F8D,, 
  &mq]axqM:
< =EHJ 	 ,, 	  	 XZ 	 +22tF|2G2JJL.OO	 3s    B2z/lawyers/newc                     t         j                  dk(  rt         j                  j                         } 	 t	               }d}| j                  d      r|j                  d| j                  d      | j                  dd      f      j                         }|r|d   }t        d| j                  d	      xs d| j                  d
      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d|f       nt        d| j                  d      | j                  d	      xs d| j                  d
      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs df      }t        d| j                  d      xs d| j                  d      | j                  d      | j                  d      xs d|| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs d| j                  d      xs df       |j                          t        dd       t        t        d            S t        d"d#      S # t        $ r2}t        d t        |       d!       j                          Y d}~Cd}~ww xY w)$zCreate new lawyerr   NrE  zb
                    SELECT id FROM lawyer_firms WHERE firm_name = ? AND city = ?
                rH  r=   r   a  
                        UPDATE lawyer_firms SET
                            firm_alias = COALESCE(?, firm_alias),
                            address = COALESCE(?, address),
                            city = COALESCE(?, city),
                            province = COALESCE(?, province),
                            postal_code = COALESCE(?, postal_code),
                            phone = COALESCE(?, phone),
                            email = COALESCE(?, email)
                        WHERE id = ?
                    rF  rG  rI  rJ  rK  rL  
                        INSERT INTO lawyer_firms (
                            firm_name, firm_alias, address, city, province, postal_code, phone, email
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                    aK  
                INSERT INTO lawyer_contacts (
                    salutation, first_name, last_name, title, lawyer_firm_id,
                    direct_number, ext, status, email, fax,
                    assistant_name, assistant_phone, assistant_email
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            
salutationr  r  rK   direct_numberextr   r%  faxassistant_namer  assistant_emailzLawyer created successfully!r   r/  zError creating lawyer: r   lawyer_form.htmlr.  )r   r   r   r  r   r   r   r   rV   r   r   r   r   r   r  r   )r  r   firm_idr  r  s        r3   
lawyer_newr;  )  s    ~~||##%O	8D Gxx$ $ .hh{+TXXk2-FG!IIQ  !+D1G 
  .6$08D-519T!34<.6$.6$	* ) *
 -.6$08D-519T!34<.6$.6$		G    &.$&%!)T)1T'4"*d!)T'4)*2d*+3t*+3t, JJL0)<GN344 -d;;	  	+CF84g>JJLL	s   KL 	M	(MM	z/lawyers/<int:lawyer_id>/editc                    t               }t        j                  dk(  rt        j                  j	                         }	 |j                  d      }|j                  d      rT|rt        d|j                  d      |j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d	      xs d|j                  d
      xs d|j                  d      xs d|j                  d      xs d|f	       nt        d|j                  d      |j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d	      xs d|j                  d
      xs d|j                  d      xs d|j                  d      xs df      }t        d|j                  d      xs d|j                  d      |j                  d      |j                  d      xs d||j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d| f       |j                          t        dd       t        t        d            S |j                  d | f      j                         }|s0t        d!d       |j                          t        t        d            S |j                          t        d"|#      S # t        $ r2}t        dt        |       d       |j                          Y d}~d}~ww xY w)$zEdit existing lawyerr   r:  rE  a(  
                        UPDATE lawyer_firms SET
                            firm_name = ?, firm_alias = ?, address = ?,
                            city = ?, province = ?, postal_code = ?,
                            phone = ?, email = ?
                        WHERE id = ?
                    rF  NrG  rH  rI  rJ  rK  rL  r1  al  
                UPDATE lawyer_contacts SET
                    salutation = ?, first_name = ?, last_name = ?, title = ?,
                    lawyer_firm_id = ?, direct_number = ?, ext = ?, status = ?,
                    email = ?, fax = ?,
                    assistant_name = ?, assistant_phone = ?, assistant_email = ?
                WHERE id = ?
            r2  r  r  rK   r3  r4  r   r%  r5  r6  r  r7  zLawyer updated successfully!r   r/  zError updating lawyer: r   a  
        SELECT lc.*, lf.id as firm_id, lf.firm_name, lf.firm_alias, lf.address as firm_address,
               lf.city as firm_city, lf.province as firm_province, lf.postal_code as firm_postal_code,
               lf.phone as firm_phone, lf.email as firm_email
        FROM lawyer_contacts lc
        LEFT JOIN lawyer_firms lf ON lc.lawyer_firm_id = lf.id
        WHERE lc.id = ?
    zLawyer not foundr8  r9  )r   r   r   r   r  r   rV   r   r   r   r   r   r  r   r   r   )	lawyer_idr   r  r:  r  r.  s         r3   lawyer_editr>    s#    8D~~||##%H	hhy)Gxx$   -.6$08D-519T!34<.6$.6$
& ) *
 -.6$08D-519T!34<.6$.6$		G    &.$&%!)T)1T'4"*d!)T'4)*2d*+3t*+3t0 JJL0)<GN344 \\    xz   '*

/00JJL-f==)  	+CF84g>JJLL	s   JM 	N(NNz/lawyers/<int:lawyer_id>/deletec                     	 t        d| f       t        dd       t	        t        d            S # t        $ r"}t        dt        |       d       Y d}~:d}~ww xY w)zDelete lawyerz(DELETE FROM lawyer_contacts WHERE id = ?zLawyer deleted successfullyr   zError deleting lawyer: r   Nr/  r  )r=  r  s     r3   lawyer_deleter@    s\    ;=	|L+Y7 GN+,,  ;'Ax0'::;r  z/doctorsc            	      x   t         j                  j                  dd      } t               }| r5|j	                  dd|  dd|  dd|  dd|  df      j                         }n|j	                  d      j                         }|D cg c]  }t        |       }}|j                          t        d||       S c c}w )zList all doctorsr   r=   z
            SELECT * FROM doctors
            WHERE name LIKE ? OR specialty LIKE ? OR city LIKE ? OR phone LIKE ?
            ORDER BY name ASC
        r   zI
            SELECT * FROM doctors
            ORDER BY name ASC
        zdoctors_list.html)r*  r   r-  )r   r   r*  doctors       r3   doctors_listrC    s     \\h+F8D ,,   &mq]axqMQvha=I	K LT8:	 	 ,,    XZ 	 +22tF|2G2JJL.OO	 3s   B7z/doctors/newc                  N   t         j                  dk(  r+t         j                  j                         } | j	                  d      }| j	                  d      }| j	                  d      }d}|r|r| d| j                         }	 d}d}| j	                  d      r	 t        | j	                  d            }| j	                  d	      r	 t        | j	                  d	            }t        d
||xs d||| j	                  d      xs d| j	                  d      xs d| j	                  d      xs d| j	                  d      xs d| j	                  d      xs d| j	                  d      xs d| j	                  d      xs d| j	                  d      xs d| j	                  d      xs d| j	                  d      xs d| j	                  d      xs d| j	                  d      xs d| j	                  d      xs d| j	                  d      xs d||| j	                  d      xs df       t        dd       t        t        d            S t        dd       S # t        t        f$ r Y w xY w# t        t        f$ r Y w xY w# t        $ r"}t        dt        |       d       Y d}~_d}~ww xY w)!zCreate new doctorr   r  r  r"  r=   r?   Nprovider_feetrial_fee_per_houra6  
                INSERT INTO doctors (name, prefix, first_name, last_name, specialty,
                                   mailing_address, mailing_city, mailing_province, mailing_postal_code,
                                   phone, cell_phone, email,
                                   clinic_address, clinic_city, clinic_province, clinic_postal_code,
                                   cancellation_fee, notes, provider_fee, trial_fee_per_hour, trial_fee_template)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            r#  mailing_addressmailing_citymailing_provincemailing_postal_coder  r$  r%  r(  r&  r'  r)  cancellation_feer{   trial_fee_templatezDoctor created successfully!r   rC  zError creating doctor: r   doctor_form.htmlrB  )r   r   r   r  r   r   r  
ValueError	TypeErrorrV   r   r   r   r   r  r   )r  r  r  r"  r  rE  rF  r  s           r3   
doctor_newrQ    s    ~~||##% XXl+
HH[)	(#	)%,a	{399;I2	?L!%xx'#(.)A#BL xx,-).txx8L/M)N&   $%-*+3t(0D+,4./74!)T&.$!)T)*2d'/4*+3t-.6$+,4!)T"-.6$+> 0)<GN344
 -d;;[ #I. 
 #I. J  	?+CF84g>>	?sa   I9 I 2I9 I# E!I9 I I9 I  I9 #I62I9 5I66I9 9	J$JJ$z/doctors/<int:doctor_id>/editc                 $   t               }t        j                  dk(  r,t        j                  j	                         }|j                  d      }|j                  d      }|j                  d      }d}|r|r| d| j                         }	 d}d}|j                  d      r	 t        |j                  d            }|j                  d	      r	 t        |j                  d	            }t        d
||xs d|||j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|||j                  d      xs d| f       t        dd       t        t        d            S |j!                  d| f      j#                         }
|
rt%        |
      }
|
s t        d d       t        t        d            S |j'                          t)        d!|
"      S # t        t        f$ r Y w xY w# t        t        f$ r Y w xY w# t        $ r"}	t        dt        |	       d       Y d}	~	d}	~	ww xY w)#zEdit existing doctorr   r  r  r"  r=   r?   NrE  rF  a6  
                UPDATE doctors 
                SET name = ?, prefix = ?, first_name = ?, last_name = ?, specialty = ?,
                    mailing_address = ?, mailing_city = ?, mailing_province = ?, mailing_postal_code = ?,
                    phone = ?, cell_phone = ?, email = ?,
                    clinic_address = ?, clinic_city = ?, clinic_province = ?, clinic_postal_code = ?,
                    cancellation_fee = ?, notes = ?,
                    provider_fee = ?, trial_fee_per_hour = ?, trial_fee_template = ?
                WHERE id = ?
            r#  rG  rH  rI  rJ  r  r$  r%  r(  r&  r'  r)  rK  r{   rL  zDoctor updated successfully!r   rC  zError updating doctor: r   z"SELECT * FROM doctors WHERE id = ?zDoctor not foundrM  rN  )r   r   r   r   r  r   r   r  rO  rP  rV   r   r   r   r   r  r   r   r   r   r   )	doctor_idr   r  r  r  r"  r  rE  rF  r  rB  s              r3   doctor_editrT  Z  s    8D~~||##% XXl+
HH[)	(#	)%,a	{399;I5	?L!%xx'#(.)A#BL xx,-).txx8L/M)N&  	 $%-*+3t(0D+,4./74!)T&.$!)T)*2d'/4*+3t-.6$+,4!)T"-.6$- D 0)<GN344
 \\>MVVXF f '*/00JJL-f==w #I. 
 #I. P  	?+CF84g>>	?sa   K$ "J8 <K$ K (E"K$ 8KK$ 
KK$ K!K$  K!!K$ $	L-L

Lz/doctors/<int:doctor_id>/deletec                     	 t        d| f       t        dd       t	        t        d            S # t        $ r"}t        dt        |       d       Y d}~:d}~ww xY w)zDelete a doctorz DELETE FROM doctors WHERE id = ?zDoctor deleted successfully!r   zError deleting doctor: r   NrC  r  )rS  r  s     r3   doctor_deleterV    s\    ;5	|D,i8 GN+,,  ;'Ax0'::;r  z/appointments/uninvoicedc                     ddl m} m} t               }| j	                          |d      z
  }|j                  d|f      j                         }|D cg c]  }t        |       }}|j                          t        d|      S c c}w )zAList appointments that are 30+ days old and haven't been invoicedr   rW   	timedelta   daysah  
        SELECT 
            a.*,
            c.file_case_id,
            cl.first_name as claimant_first_name, 
            cl.last_name as claimant_last_name,
            a.doctor_name,
            CAST(julianday('now') - julianday(a.appointment_date) AS INTEGER) AS days_since
        FROM appointments a
        LEFT JOIN invoices i ON a.id = i.appointment_id
        LEFT JOIN cases c ON a.case_id = c.id
        LEFT JOIN claimants cl ON c.claimant_id = cl.id
        WHERE i.id IS NULL 
        AND a.appointment_date < ?
        AND a.appointment_date IS NOT NULL
        ORDER BY a.appointment_date ASC
    zuninvoiced_appointments.html)r  )
r   rW   rY  r   r  r   r   r   r   r   )rW   rY  r   thirty_days_agor  r%  s         r3   uninvoiced_appointmentsr^    s     )8DjjlYB%77O<< !  	!  &XZ! & *66#DI6L6JJL9UU	 7s   Bz/appointmentsc            	         t         j                  j                  dd      } t         j                  j                  dd      }t         j                  j                  dd      }t               }d}g }| r |dz  }d|  d}|j	                  |gdz         |r|d	z  }|j                  |       |d
k(  r|dz  }n |dk(  r|dz  }n|dk(  r|dz  }n
|dk(  r|dz  }|dz  }|j                  ||      j                         }|D cg c]  }t        |       }}|j                          ddl
m}	 t        d|| |||	j                               S c c}w )z,List all appointments with search and filterr   r=   r   date_filtera  SELECT a.*,
               c.file_case_id,
               c.record_id,
               cl.first_name AS claimant_first_name,
               cl.last_name AS claimant_last_name,
               cl.full_name AS claimant_full_name,
               cli.company_name AS client_company_name,
               a.doctor_name AS doctor_full_name,
               COALESCE(al.letter_count, 0) AS letter_count
        FROM appointments a
        LEFT JOIN cases c ON a.case_id = c.id
        LEFT JOIN claimants cl ON c.claimant_id = cl.id
        LEFT JOIN clients cli ON c.client_id = cli.id
        LEFT JOIN (
            SELECT appointment_id, COUNT(*) AS letter_count
            FROM appointment_letters
            GROUP BY appointment_id
        ) al ON al.appointment_id = a.id
        WHERE 1=1a	   AND (
            c.file_case_id LIKE ? OR
            cl.first_name LIKE ? OR
            cl.last_name LIKE ? OR
            cli.company_name LIKE ? OR
            a.doctor_name LIKE ? OR
            a.location LIKE ? OR
            a.description LIKE ?
        )r      z AND a.status = ?upcomingz& AND a.appointment_date >= date('now')pastz% AND a.appointment_date < date('now')r  z% AND a.appointment_date = date('now')	this_weekzF AND a.appointment_date BETWEEN date('now') AND date('now', '+7 days')z 
        ORDER BY 
            CASE 
                WHEN a.appointment_date >= date('now') THEN 0 
                ELSE 1 
            END,
            ABS(julianday(a.appointment_date) - julianday('now')),
            a.appointment_time
    r   r  zappointments_list.html)r  r   status_filterr`  rW   )r   r   r   r   r  r   r   r   r   r   r   rW   r   r  )
r   re  r`  r   r   r  r  r  r%  rW   s
             r3   appointments_listrf    sp    \\h+FLL$$Xr2M,,""="5K8DE& F  	 &m{ma'($$m$j 99		88		88		#YY 
  E <<v.779L *66#DI6L6JJL3'3!'(5&1#zz|- - 7s   =Ez/appointments/newz/appointments/new/<int:case_id>c           	      F   | s%t         j                  j                  dt              } t         j                  dk(  r!t         j
                  j                         }t         j
                  j                  dd      }|j                  d      } | s%t        dd       t        t         j                        S 	 |j                  d      st               |d<   |j                  d	      d
k(  s|dk(  rdnd}t        dg t        |       |j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      |j                  d      xs d|j                  dd      ||j                  d      xs d|j                  d       xs d|j                  d!      xs d|j                  d"      d#k(  rdnd|j                  d$      xs d|j                  d%      xs d|j                  d&      xs d|j                  d'      d#k(  rdnd|j                  d(      xs d|j                  d)      d#k(  rdnd|j                  d*      xs d|j                  d+      d#k(  rdnd|j                  d,      d#k(  rdnd|j                  d-      xs d|j                  d.      xs d|j                  d/      rt        |j                  d/            nd|j                  d0      xs d|j                  d1      xs d|j                  d2      d#k(  rdnd|j                  d3      xs d      }t               }|j                  d4t        |       f      j                         }|r.|D ]  }|j                  d5||d6   f        |j!                          |j#                          |j                  d7      xs d}|j                  d8      xs d}	|j                  d9      xs d}
t%               j                  d:      }t'        |      r ||||	|
      \  }}n%t(        j*                  j-                  d;|       d<\  }}|r|rd=nd}t        ||       t        d>d=       |dk(  r"t        d?d@       t        t/        dA|B            S t        t/        dC|B            S t               }|j                  dE      j                         }|j#                          g }	 ddl}t7        dFdGdHI      5 }t9        |       |j;                  |      }|D ]m  }|j                  dJdK      j=                         }|s&|j                  dLdK      xs |j                  dMdK      }|j?                  ||j=                         dN       o 	 ddd       t               }tA        dP|        d}| r;t               }|j                  dQ| f      jC                         }|j#                          tE        dRd|||| |S      S # t0        $ r;}t        dDt3        |       d       t        t         j                        cY d}~S d}~ww xY w# 1 sw Y   xY w# t0        $ r}tA        dO|        Y d}~d}~ww xY w)TzCreate new appointmentrX   r   r   form_actionschedulez7Please select a case before scheduling the appointment.r   r  invoicedonschedule_invoicer-   r   aB  
                INSERT INTO appointments (
                    case_id, appointment_date, appointment_time, doctor_name,
                    specialist_number, location, service_code, assessment_type,
                    description, date_confirmed, date_received, date_released,
                    reference, notes, status, invoiced,
                    timezone, provider_specialty, clinic_location,
                    pil_instructions_received, pil_instruction_type,
                    report_deadline_date, report_due_date,
                    confirmation_deposit_letter_sent, confirmation_deposit_letter_date,
                    confirmation_sent_to_doctor, confirmation_sent_to_doctor_date,
                    in_calendar, retainer_required, retainer_requested_on,
                    retainer_received_on, retainer_amount,
                    atp_requested_on, atp_received_on,
                    loi_received, loi_received_date
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            rw   Nry   rz   specialist_numberr|   r   r   r  date_confirmeddate_receiveddate_releasedr{   r   rv  timezonerw  provider_specialtyclinic_locationpil_instructions_receivedrx  pil_instruction_typereport_deadline_datereport_due_date confirmation_deposit_letter_sent confirmation_deposit_letter_dateconfirmation_sent_to_doctor confirmation_sent_to_doctor_datein_calendarretainer_requiredretainer_requested_onretainer_received_onretainer_amountatp_requested_onatp_received_onloi_receivedloi_received_datezr
                SELECT id FROM reports 
                WHERE case_id = ? AND appointment_id IS NULL
            z`
                        UPDATE reports SET appointment_id = ? WHERE id = ?
                    r   r   r   
email_noter   zMsend_appointment_email helper missing; skipping email send for appointment %s)Fz>Email helper unavailable; appointment saved but no email sent.r   z!Appointment created successfully!3Invoice form opened with the same reference number.r8  invoice_newr]   appointment_viewzError creating appointment: j
        SELECT id, name, first_name, last_name, specialty
        FROM doctors
        ORDER BY name
    r|  r'  r}  rE   r~  r=   r  r  r  r  z7[appointment_new] Rendering form with reference_number=a  
            SELECT c.id, c.file_case_id, c.record_id,
                   cl.first_name as claimant_first_name, cl.last_name as claimant_last_name
            FROM cases c
            LEFT JOIN claimants cl ON c.claimant_id = cl.id
            WHERE c.id = ?
        appointment_form.htmlr   r*  r  reference_numberrX   	case_data)#r   r   r   r   r   r   r  r   r   r  r  rV   r  r   r   r   r   r   globalscallabler0   loggerr   r   r   r  r  rS   r  r  r   r   r   r   r   )rX   r  rh  invoiced_flagr]   r   unlinked_reportsreportr   r   r  send_email_fnokmsgcategoryr  r*  r  r  rb   r  r  r  r  r  r  s                             r3   appointment_newr  7	  s}    ,,""93"7~~||##%ll&&}jA((9%KWUGKK((g	)88K($;$=[!!%*!5!=PbAbAhiM ( ) %G%+,4% +,4% '/4	%
 ,-5% $,% (0D% *+3t% '/4% )*2d% )1T% )1T% %% !)T% ;/%  !%" $:(:#%$ -.6$%%& *+3t'%( XX9:cAq)%* /08D+%, /08D-%. *+3t/%0 XX@ASHa1%2 ;<D3%4 XX;<C5%6 ;<D7%8 XXm,39%: XX12c9q;%< 019T=%> /08D?%@ 7;hh?P6Qdhh012W[A%B +,4C%D *+3tE%F XXn-4!G%H ,-5I%!5Nr 8D#|| - g, " #+(* 
  . 9FLL "(&,799 JJL xx
+3tHxx
+3tH,/74J#IMM*BCM&'(JWC

  !p  sA  BaC(*9c8$5yA00KVTn UVVG$6~VWW 8Dll  	 
	 
 	JJL M3S;O 	STG^^A&F wwvr*00277>26T#''-QS:TD!(( $'+zz|* 		" /0	CDTCU
VW IxLL " Z " 	 	

#) e  	)0Q97CGKK((	)"	 	  -aS12s]   3RZ, Z, [? +A[30A
[3;[? ,	[050[+%[0+[03[<8[? ?	\ \\ z"/appointments/<int:appointment_id>c           	         t               }|j                  d| f      j                         }|s0t        dd       |j	                          t        t        d            S t        |      }|j                  d| f      j                         }|D cg c]  }t        |       }}|j                  d| f      j                         }|D cg c]  }t        |       }}g }	 |j                  d| f      j                         }|D cg c]  }t        |       }}g }		 |j                  d| f      j                         }	|	D 
cg c]  }
t        |
       }	}
g }	 |j                  d	| f      j                         }|D cg c]  }t        |       }}|j	                          t        d
|||||	|      S c c}w c c}w c c}w # t        $ r Y w xY wc c}
w # t        $ r Y w xY wc c}w # t        $ r Y dw xY w)zView appointment details'  
        SELECT a.*,
               c.file_case_id,
               c.record_id,
               cl.first_name AS claimant_first_name,
               cl.last_name AS claimant_last_name,
               cl.full_name AS claimant_full_name,
               cli.company_name AS client_company_name,
               a.doctor_name AS doctor_full_name
        FROM appointments a
        LEFT JOIN cases c ON a.case_id = c.id
        LEFT JOIN claimants cl ON c.claimant_id = cl.id
        LEFT JOIN clients cli ON c.client_id = cli.id
        WHERE a.id = ?
    Appointment not foundr   rf  j
        SELECT * FROM appointment_letters WHERE appointment_id = ?
        ORDER BY uploaded_at DESC
    z
        SELECT * FROM case_reports
        WHERE appointment_id = ? AND report_type = 'Confirmation Letter'
        ORDER BY report_date DESC, uploaded_at DESC
    z
            SELECT * FROM appointment_additional_services
            WHERE appointment_id = ?
            ORDER BY service_type, id
        z
            SELECT * FROM appointment_meds
            WHERE appointment_id = ?
            ORDER BY uploaded_at DESC
        z
            SELECT * FROM medical_documentation
            WHERE appointment_id = ?
            ORDER BY uploaded_at DESC
        zappointment_view.html)r   lettersconfirmation_lettersadditional_servicesmedsmedical_docs)r   r   r   r   r   r   r   r   r   r   r   )r]   r   r   r  letterr  clr  servicer  medr  r#   s                r3   r  r  	  sR    8D,,   	 %HJ   %w/

 3455 {#K ll  	 %HJ  +22tF|2G2  << ) 		 %HJ	 
 0DDDHDD 	"ll , 	  !)
	 	
 =PPtG}PP D	||  	  !)
	 	
 &**cS	** L	|| % 	  !)
	 	
 .::cS	::
 	JJL2%0!(.B-@"&24 4g 3 E Q  +  ; sx   F1F6%G  F;G  %G ?GG %G( ;G#G( ;G   	GGG 	G G #G( (	G43G4z'/appointments/<int:appointment_id>/editc           	         t               }t        j                  dk(  r	t        j                  j	                         }t        j                  j                  dd      }|j                  d      s%t        dd       t        t        j                        S 	 t        |j                  d            }|j                  d      dk(  s|d	k(  rd
nd}t        dg ||j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  dd      ||j                  d      xs d|j                  d      xs d|j                  d       xs d|j                  d!      d"k(  rd
nd|j                  d#      xs d|j                  d$      xs d|j                  d%      xs d|j                  d&      d"k(  rd
nd|j                  d'      xs d|j                  d(      d"k(  rd
nd|j                  d)      xs d|j                  d*      d"k(  rd
nd|j                  d+      d"k(  rd
nd|j                  d,      xs d|j                  d-      xs d|j                  d.      rt        |j                  d.            nd|j                  d/      xs d|j                  d0      xs d|j                  d1      d"k(  rd
nd|j                  d2      xs d|        |j                          t        d3d4       |d	k(  r"t        d5d6       t        t        d7| 8            S t        t        d9| 8            S |j!                  d;| f      j#                         }|s0t        d<d       |j                          t        t        d=            S d}|d   r$|j!                  d>|d   f      j#                         }|j!                  d?      j%                         }	|j                          g }
	 ddl}t)        d@dAdBC      5 }t+        |       |j-                  |      }|D ]m  }|j                  dDdE      j/                         }|s&|j                  dFdE      xs |j                  dGdE      }|
j1                  ||j/                         dH       o 	 ddd       |d   xs
 t5               }t3        dJ|  dK|        t7        dL||	|
||d   |M      S # t        $ rK}t        d:t        |       d       |j                          t        t        j                        cY d}~S d}~ww xY w# 1 sw Y   xY w# t        $ r}t3        dI|        Y d}~d}~ww xY w)NzEdit appointmentr   rh  ri  rX   z5Please select a case before updating the appointment.r   rj  rk  rl  r-   r   a  
                UPDATE appointments SET
                    case_id = ?, appointment_date = ?, appointment_time = ?,
                    doctor_name = ?, specialist_number = ?, location = ?,
                    service_code = ?, assessment_type = ?, description = ?,
                    date_confirmed = ?, date_received = ?, date_released = ?,
                    reference = ?, notes = ?, status = ?, invoiced = ?,
                    timezone = ?, provider_specialty = ?, clinic_location = ?,
                    pil_instructions_received = ?, pil_instruction_type = ?,
                    report_deadline_date = ?, report_due_date = ?,
                    confirmation_deposit_letter_sent = ?, confirmation_deposit_letter_date = ?,
                    confirmation_sent_to_doctor = ?, confirmation_sent_to_doctor_date = ?,
                    in_calendar = ?, retainer_required = ?, retainer_requested_on = ?,
                    retainer_received_on = ?, retainer_amount = ?,
                    atp_requested_on = ?, atp_received_on = ?,
                    loi_received = ?, loi_received_date = ?,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            rw   Nry   rz   rm  r|   r   r   r  rn  ro  rp  r  r{   r   rv  rq  rw  rr  rs  rt  rx  ru  rv  rw  rx  ry  rz  r{  r|  r}  r~  r  r  r  r  r  r  z!Appointment updated successfully!r   r  r8  r  r  r  zError updating appointment: z5
        SELECT * FROM appointments WHERE id = ?
    r  rf  ax  
            SELECT c.id, c.file_case_id,
                   cl.first_name as claimant_first_name, cl.last_name as claimant_last_name,
                   cli.company_name as client_company_name
            FROM cases c
            LEFT JOIN claimants cl ON c.claimant_id = cl.id
            LEFT JOIN clients cli ON c.client_id = cli.id
            WHERE c.id = ?
            r  r|  r'  r}  rE   r~  r=   r  r  r  r  z2[appointment_edit] Rendering form for appointment z with reference_number=r  r  )r   r   r   r   r  r   r   r   r  r   rV   r  r   r   r   r  r   r   r   r  rS   r  r  r   r   r   r  r   )r]   r   r  rh  case_id_intr  r  r   r  r*  r  r  rb   r  r  r  r  r  s                     r3   appointment_editr  G
  s    8D~~||##%ll&&}jAxx	"I7SGKK((I	)dhhy12K!%*!5!=PbAbAhiM $&&+,4& +,4& '/4	&
 ,-5& $,& (0D& *+3t& '/4& )*2d& )1T& )1T& %-& !)T& ;/&  !&" $:(:#&$ -.6$%&& *+3t'&( XX9:cAq)&* /08D+&, /08D-&. *+3t/&0 XX@ASHa1&2 ;<D3&4 XX;<C5&6 ;<D7&8 XXm,39&: XX12c9q;&< 019T=&> /08D?&@ 7;hh?P6Qdhh012W[A&B +,4C&D *+3tE&F XXn-4!G&H ,-5I&J K&%8t JJL5yA00KVTn UVVG$6~VWW ,,  
	$HJ  %w/

 3455 I9LL #%
 (* 	 ll  	 
	 
 	JJL M3S;O 	STG^^A&F wwvr*00277>26T#''-QS:TD!(( $'+zz|* 		" #;/L3J3L	>~>NNefvew
xy#)I& }  	)0Q97CJJLGKK((	)T	 	  -aS12s^   M=V9 V9 X *AX/A
X:X 9	XA XXXXX 	X=%X88X=z)/appointments/<int:appointment_id>/deletec                    	 t               }|j                  d| f      j                         }|D ]Z  }t        j                  j                  d|d         }t        j                  j                  |      sFt        j                  |       \ t        d| f       t        d| f       |j                          t        dd       t        t        d            S # t        $ r"}t        dt        |       d	       Y d
}~:d
}~ww xY w)zDelete appointmentzX
            SELECT file_path FROM appointment_letters WHERE appointment_id = ?
        uploads/appointment_letters	file_pathz8DELETE FROM appointment_letters WHERE appointment_id = ?z%DELETE FROM appointments WHERE id = ?z Appointment deleted successfullyr   zError deleting appointment: r   Nrf  )r   r   r   rN   rO   rP   r  removerV   r   r   r   r  r   r   )r]   r   r  r  r`   r  s         r3   appointment_deleter  
  s    @x ,,    (
 	  	$Fww||$A6+CVWHww~~h'		(#	$
 	MP^O`a:^<MN

0)< G/011  @,SVH5w??@s   A2C 5AC 	DC<<Dz0/appointments/<int:appointment_id>/upload_letterc                    dt         j                  vr"t        dd       t        t	        d|             S t         j                  d   }|j
                  dk(  r"t        dd       t        t	        d|             S t        |j
                        s"t        dd       t        t	        d|             S 	 t        |j
                        }t        j                         j                  d      }| d	| }t        j                  j                  d
|      }|j                  |       t        j                  j                  |      }|j!                  dd      d   j#                         }t         j$                  j'                         }t)        d| |||||j+                  d      xs d|j+                  d      xs dt-        j*                  dd      f       t        dd       t        t	        d|             S # t.        $ rV}	t        dt1        |	       d       t        j                  j3                        rt        j4                  |       Y d}	~	pd}	~	ww xY w)zUpload letter to appointmentletter_fileNo file selectedr   r  r  r=   Invalid file typer@   r>   r  r,   r-   z
            INSERT INTO appointment_letters (
                appointment_id, file_name, file_path, file_size, file_type,
                letter_type, description, uploaded_by
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        letter_typeotherr  Nr   rI   zLetter uploaded successfully!r   zError uploading letter: )r   filesr   r   r   r2   r4   r   r   rL   rM   rN   rO   rP   saverU   r.   r/   r   r  rV   r   r
   r   r  r  r  )
r]   r  original_filenamer_   r2   r`   rc   file_extensionr  r  s
             r3   appointment_upload_letterr  
  s    GMM) '* 2>RSS=='D}} '* 2>RSS&!7+ 2>RSS! +DMM:LLN++O<	["3!4577<< =xH		(GGOOH-	*11#q9!<BBD||##%  HH]#.wHH]#+tKK
H-	
	  	-y9 G.~NOO   (Q17;77>>(#IIh s   1D*G1 1	I:AIIz./appointments/letters/<int:letter_id>/downloadc                     t               }|j                  d| f      j                         }|j                          |st	        d       t        d|d   d|d         S )zDownload appointment letterzO
        SELECT file_path, file_name FROM appointment_letters WHERE id = ?
      r  r  T	file_nameas_attachmentdownload_namer   r   r   r   r   r   )	letter_idr   r  s      r3   appointment_letter_downloadr  7  se     8D\\ 
xz  	JJLc
%{[)	 r5   z,/appointments/letters/<int:letter_id>/deletec                 <   	 t               }|j                  d| f      j                         }|rvt        j                  j                  d|d         }t        j                  j                  |      rt        j                  |       t        d| f       |d   }t        dd       nt        dd	       d
}|j                          |rt        t        d|            S 	 t        t        d            S # t        $ r"}t        dt        |       d	       Y d
}~:d
}~ww xY w)zDelete appointment letterz\
            SELECT file_path, appointment_id FROM appointment_letters WHERE id = ?
        r  r  z,DELETE FROM appointment_letters WHERE id = ?r]   zLetter deleted successfullyr   zLetter not foundr   Nr  r  zError deleting letter: rf  r   r   r   rN   rO   rP   r  r  rV   r   r   r   r   r   r  )r  r   r  r`   r]   r  s         r3   appointment_letter_deleter  K  s    ;x \#8: 	 ww||$A6+CVWHww~~h'		(#E	|T#$45N/;$g.!N

G$6~VWW 
 G/011  ;'Ax0'::;   CC0 0	D9DDz6/appointments/<int:appointment_id>/additional_servicesc                    t               }|j                  d| f      j                         }|s0t        dd       |j	                          t        t        d            S t        j                  dk(  rt        j                  j                         }	 t        d| |j                  d      |j                  d      d	k(  rd
nd|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs df       |j	                          t        dd       t        t        d|             S |j                  d| f      j                         }|D cg c]  }t!        |       }}|j	                          t#        dt!        |      |      S # t        $ r2}t        dt        |       d       |j	                          Y d}~d}~ww xY wc c}w )z-Manage additional services for an appointmentz'SELECT * FROM appointments WHERE id = ?r  r   rf  r   z
                INSERT INTO appointment_additional_services (
                    appointment_id, service_type, present, name, company, language, travel_details
                ) VALUES (?, ?, ?, ?, ?, ?, ?)
            service_typepresentrx  r-   r   r!  Ncompanylanguagetravel_detailsz&Additional service added successfully!r   appointment_additional_servicesr  zError adding service: z~
        SELECT * FROM appointment_additional_services
        WHERE appointment_id = ?
        ORDER BY service_type, id
    z$appointment_additional_services.html)r   services)r   r   r   r   r   r   r   r   r   r   r  rV   r   r   r  r   r   r   )r]   r   r   r  r  r  r  s          r3   r  r  n  s    8D ,,H>J[\eegK%w/

 3455~~||##%	 
 (XXi(C/Q (D#+t$,)*2d	 JJL:IFG$EVdeff ||  		 %HJ	 
 .66'W6H6JJLA%)+%6"*, ,  	*3q6(3W=JJLL	 7s   B5F& +G$&	G!/(GG!z7/appointments/additional_services/<int:service_id>/editc                 ^   t               }|j                  d| f      j                         }|s0t        dd       |j	                          t        t        d            S |d   }t        j                  dk(  rt        j                  j                         }	 t        d|j                  d      |j                  d	      d
k(  rdnd|j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      xs d| f       |j	                          t        dd       t        t        d|            S |j	                          t        dt!        |            S # t        $ r2}t        dt        |       d       |j	                          Y d}~\d}~ww xY w)zEdit an additional servicez
        SELECT aas.*, a.id as appointment_id
        FROM appointment_additional_services aas
        JOIN appointments a ON aas.appointment_id = a.id
        WHERE aas.id = ?
    Service not foundr   rf  r]   r   z
                UPDATE appointment_additional_services SET
                    service_type = ?, present = ?, name = ?, company = ?,
                    language = ?, travel_details = ?
                WHERE id = ?
            r  r  rx  r-   r   r!  Nr  r  r  zService updated successfully!r   r  r  zError updating service: z(appointment_additional_service_form.html)r  )r   r   r   r   r   r   r   r   r   r   r  rV   r   r   r  r   r   )
service_idr   r  r]   r  r  s         r3   #appointment_additional_service_editr    s    8Dll 
 
 !
  !7+

 3455-.N~~||##%	  (XXi(C/Q (D#+t$,)*2d JJL19=G$EVdeff
 	JJLEtT[}]]  	,SVH5w?JJLL	s   B5E1 1	F,:(F''F,z9/appointments/additional_services/<int:service_id>/deletec                    t               }|j                  d| f      j                         }|s0t        dd       |j	                          t        t        d            S |d   }	 t        d| f       t        dd       |j	                          t        t        d|            S # t        $ r"}t        d	t        |       d       Y d
}~Ld
}~ww xY w)zDelete an additional servicezU
        SELECT appointment_id FROM appointment_additional_services WHERE id = ?
    r  r   rf  r]   z8DELETE FROM appointment_additional_services WHERE id = ?zService deleted successfully!r   zError deleting service: Nr  r  )
r   r   r   r   r   r   r   rV   r   r  )r  r   r  r]   r  s        r3   %appointment_additional_service_deleter    s     8Dll 
 
  !7+

 3455-.N<MPZ}]-y9 	JJLG=n]^^	  <(Q17;;<s   $B# #	C,C		Cz./appointments/<int:appointment_id>/meds/uploadc                    dt         j                  vr"t        dd       t        t	        d|             S t         j                  d   }|j
                  dk(  r"t        dd       t        t	        d|             S t        |j
                        s"t        dd       t        t	        d|             S 	 t        |j
                        }t        j                         j                  d      }| d	| }t        j                  j                  d
d      }t        j                  |d       t        j                  j                  ||      }|j                  |       t        j                  j!                  |      }d|v r#|j#                  dd      d   j%                         nd}t         j&                  j)                         }		 t+        d| |||||	j-                  d      xs dt/        j,                  dd      f       t        dd       t        t	        d|             S # t0        $ r= t+        d| |||||	j-                  d      xs dt/        j,                  dd      f       Y gw xY w# t0        $ rb}
t        dt3        |
       d       dt5               v r4t        j                  j7                        rt        j8                  |       Y d}
~
d}
~
ww xY w)z,Upload meds documentation for an appointment	meds_filer  r   r  r  r=   9Invalid file type. Allowed: PDF, DOC, DOCX, TXT, JPG, PNGr@   r>   uploadsappointment_medsTrA   r,   r-   z
                INSERT INTO appointment_meds (
                    appointment_id, file_name, file_path, file_size, file_type,
                    description, uploaded_by
                ) VALUES (?, ?, ?, ?, ?, ?, ?)
            r{   Nr   rI   z
                INSERT INTO appointment_meds (
                    appointment_id, file_name, file_path, file_size, file_type,
                    notes, uploaded_by
                ) VALUES (?, ?, ?, ?, ?, ?, ?)
            z)Meds documentation uploaded successfully!r   zError uploading meds: r`   )r   r  r   r   r   r2   r4   r   r   rL   rM   rN   rO   rP   rQ   r  rU   r.   r/   r   r  rV   r   r
   r   r  r  r  r  )r]   r  r  r_   r2   
upload_dirr`   rc   r  r  r  s              r3   appointment_meds_uploadr    sh    '--' '* 2>RSS==%D}} '* 2>RSS&I7S 2>RSS4 +DMM:LLN++O<	["3!45WW\\)-?@

J.77<<
H5		(GGOOH-	HKO`H`*11#q9!<BBDfh||##%	  !!)TJ1> 	99E G.~NOO/  	  !!)TJ1	$   &s1vh/9!bggnnX&>IIh s?   1DI$ ?:H 9I$ AI!I$  I!!I$ $	K-AK

Kz(/appointments/meds/<int:med_id>/downloadc                     t               }|j                  d| f      j                         }|j                          |st	        d       t        d|d   d|d         S )zDownload meds documentationzL
        SELECT file_path, file_name FROM appointment_meds WHERE id = ?
    r  uploads/appointment_medsr  Tr  r  r  )med_idr   r  s      r3   appointment_meds_downloadr  :  se     8D
,, 
HJ  	JJLc
"K+&	 r5   z&/appointments/meds/<int:med_id>/deletec                 <   	 t               }|j                  d| f      j                         }|rvt        j                  j                  d|d         }t        j                  j                  |      rt        j                  |       t        d| f       |d   }t        dd       nt        dd	       d
}|j                          |rt        t        d|            S 	 t        t        d            S # t        $ r"}t        dt        |       d	       Y d
}~:d
}~ww xY w)zDelete meds documentationzY
            SELECT file_path, appointment_id FROM appointment_meds WHERE id = ?
        r  r  z)DELETE FROM appointment_meds WHERE id = ?r]   z'Meds documentation deleted successfullyr   zMeds documentation not foundr   Nr  r  zError deleting meds: rf  r  )r  r   r  r`   r]   r  s         r3   appointment_meds_deleter  N  s    9xll Y 
 	 ww||$>K@PQHww~~h'		(#BVIN !12N;YG0':!N

G$6~VWW 
 G/011  9%c!fX.889r  z(/appointments/<int:appointment_id>/printc                 p   ddl m } t               }|j                  d| f      j                         }|j	                          |s t        dd       t        t        d            S t               }|j                  d| f      j                         }|j	                          t        d|||j                  	      S )
z"Print-friendly view of appointmentr   r   r  r  r   rf  r  zappointment_print.html)r   r  rL   )r   r   r   r   r   r   r   r   r   r   rL   )r]   r   r   r   r  s        r3   appointment_printr  m  s     "8D,,   	 %HJ  	JJL%w/ 3455 8Dll  	 %HJ  	JJL3V]ckcocoppr5   z/api/search/casesc                     t         j                  j                  dd      j                         } | rt	        |       dk  rt        g       S t               }|j                  dd|  dd|  dd|  dd|  df      j                         }|j                          g }|D ]N  }|j                  |d   |d   |d   |d	   xs d d
|d   xs d j                         |d   |d   |d   d       P t        |      S )z,Search cases by case number or claimant namer  r=   r  a  
        SELECT 
            c.id,
            c.file_case_id,
            c.client_file_number,
            cl.first_name AS claimant_first_name,
            cl.last_name AS claimant_last_name,
            cl.date_of_birth AS claimant_dob,
            cli.company_name AS client_company_name,
            c.file_open_date
        FROM cases c
        LEFT JOIN claimants cl ON c.claimant_id = cl.id
        LEFT JOIN clients cli ON c.client_id = cli.id
        WHERE 
            c.file_case_id LIKE ? OR
            cl.first_name LIKE ? OR
            cl.last_name LIKE ? OR
            (cl.first_name || ' ' || cl.last_name) LIKE ?
        ORDER BY c.file_case_id DESC
        LIMIT 20
    r   r   rv   rU  r~   r?   r   claimant_dobr<  rQ  )r   rv   rU  claimant_namer  client_companydate_opened)r   r   r   r   r  r	   r   r   r   r   r   )r   r   r  r  r  s        r3   search_casesr    s3    LLS"%++-ECJNr{8D ll ( eWA,!E7!%lawaL	A)C( DL8:) , 	JJL E 	d)/"%&:"; #$9 : @bA3G[C\Cb`bBcdjjl/!"78/0
 		 5>r5   z%/api/cases/<int:case_id>/appointmentsc                 *   t               }|j                  d| f      j                         }|j                          g }|D ]G  }|d   xs d}|d   rd|d    nd}|d   rd|d    nd}|j	                  |d   || | | d	       I t        |      S )
z(Get all appointments for a specific casez
        SELECT id, appointment_date, appointment_time, doctor_name, provider_specialty
        FROM appointments
        WHERE case_id = ?
        ORDER BY appointment_date DESC, appointment_time DESC
    rw   r=   ry   r?   rz   z - r   )r   rw   displayr   r   r   r   r   r	   )rX   r   r  r  r%  r   r   r   s           r3   api_case_appointmentsr    s     8D<< !
 
 XZ  	JJLG )*0b478J4KQs-./0QS36}3Es3}-./2
d) ("H:j\:
 		 7r5   z!/api/cases/<int:case_id>/invoicesc                 :   t               }|j                  d| f      j                         }|j                          g }|D ]O  }|d   xs d}|d   r	d|d   dnd}|d   r	d|d    d	nd}|j	                  |d
   |d   |d    | | d       Q t        |      S )z$Get all invoices for a specific casez
        SELECT id, invoice_number, invoice_date, total_amount, status
        FROM invoices
        WHERE case_id = ?
        ORDER BY invoice_date DESC, invoice_number DESC
    invoice_dater=   total_amountz - $r7   r    (r   r   invoice_number)r   r  r  r  )rX   r   r  r  r&  r   
amount_str
status_strs           r3   api_case_invoicesr    s     8D|| 
 
 XZ  	JJLG ~&,"9<^9LtC/45RT
.1(mr#h-*
d)!"23./0ZLI
 		 7r5   z/reportsc                  L   t         j                  j                  dd      } t         j                  j                  dd      }t         j                  j                  dd      }t               }d}g }| r |dz  }d|  d}|j	                  |gdz         |r|d	z  }|j                  |       |r|d
z  }|j                  |       |dz  }|j                  ||      j                         }|j                  d      j                         }|j                          t        d|| |||      S )z'List all reports with search and filterr   r=   r   report_typez&SELECT * FROM v_reports_full WHERE 1=1a;   AND (
            report_title LIKE ? OR
            file_case_id LIKE ? OR
            claimant_first_name LIKE ? OR
            claimant_last_name LIKE ? OR
            client_company_name LIKE ? OR
            lawyer_firm_name LIKE ? OR
            doctor_name LIKE ? OR
            description LIKE ?
        )r       AND status = ?z AND report_type = ?z ORDER BY uploaded_at DESCz
        SELECT DISTINCT report_type FROM reports 
        WHERE report_type IS NOT NULL AND report_type != ''
        ORDER BY report_type
    zreports_list.html)r   r   re  report_type_filterreport_types)
r   r   r   r   r  r   r   r   r   r   )	r   re  r  r   r   r  r  r   r  s	            r3   reports_listr    s2    \\h+FLL$$Xr2M ))-<8D 5EF 	 		 &m{ma'(""m$''()	))Ell5&)224G << ! 	 
	  	JJL.!( &'4,>&24 4r5   z/reports/newc                     t         j                  dk(  rdt         j                  vr%t        dd       t	        t         j
                        S t         j                  d   } | j                  dk(  r%t        dd       t	        t         j
                        S t        | j                        s%t        dd       t	        t         j
                        S 	 t        | j                        }t        j                         j                  d      }| d| }t        j                  j                  t        j                   d	   |      }| j#                  |       t        j                  j%                  |      }|j'                  d
d      d   j)                         }t         j*                  j-                         }t/               }t1        d|j3                  d      |j3                  d      xs d|j3                  d      xs d|||||j3                  d      xs d|j3                  d      xs d|j3                  dd      |j3                  d      xs d|j3                  d      xs dt5        j2                  dd      f      }	|j7                          t        dd       t	        t9        d|	            S t/               }|jC                  d      jE                         }|jC                  d      jE                         }|j7                          tG        d d||!      S # t:        $ rV}
t        dt=        |
       d       t        j                  j?                        rt        j@                  |       Y d}
~
d}
~
ww xY w)"zUpload new reportr   report_filer  r   r=   r  r@   r>   r!   r,   r-   a@  
                INSERT INTO reports (
                    report_title, report_type, report_date, file_name,
                    file_path, file_size, file_type, case_id, doctor_id,
                    status, description, notes, uploaded_by
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            r[   r  Nreport_daterX   rS  r   rH   r  r{   r   rI   Report uploaded successfully!r   report_view	report_idzError uploading report: z
        SELECT c.id, c.file_case_id, cl.first_name, cl.last_name
        FROM cases c
        LEFT JOIN claimants cl ON c.claimant_id = cl.id
        ORDER BY c.file_case_id DESC
        LIMIT 100
    r  zreport_form.html)r  r  r*  )$r   r   r  r   r   r  r2   r4   r   r   rL   rM   rN   rO   rP   r0   r1   r  rU   r.   r/   r   r  r   rV   r   r
   r   r   r   r  r  r  r   r   r   )r  r  r_   r2   r`   rc   r  r  r   r  r  r  r*  s                r3   
report_newr  9  s    ~~-$g.GKK((}}]+==B$g.GKK((DMM*MwWGKK((3	$ / > //@I#A&7%89H ww||CJJ$?JHIIh 1I.55c1=a@FFHN <<'')D8D # $ ('/4'/4!#+t%-7+'/4!)TJ1I, JJL19=GMYGHH 8DLL  	 
 
 ll  	 
	 
 	JJL-d%QXYY/  	$,SVH5w?ww~~h'		(#		$s   GL 	M/AM**M/z"/cases/<int:case_id>/report/uploadc                    t               }|j                  d| f      j                         }|s0|j                          t	        dd       t        t        d            S dt        j                  vr2|j                          t	        dd       t        t        d|             S t        j                  d   }|j                  d	k(  r2|j                          t	        dd       t        t        d|             S t        |j                        s2|j                          t	        d
d       t        t        d|             S 	 t        |j                        }t        j                         j                  d      }| d| }t        j                   j#                  t$        j&                  d   |      }t        j(                  t$        j&                  d   d       |j+                  |       t        j                   j-                  |      }|j/                  dd      d   j1                         }	t        j2                  j5                         }
t7        d|
j9                  d      xs ||
j9                  d      xs d|
j9                  d      xs d||||	| |
j9                  d      xs d|
j9                  dd      |
j9                  d      xs d|
j9                  d      xs dt;        j8                  dd      f      }|j                          t	        dd       t        t        d|       d z         S # t<        $ r}d!dl}d!dl }|jC                  |jD                  "       |j                          t	        d#d       d$tG               v r4t        j                   jI                        rt        jJ                  |       t        t        d|             cY d}~S d}~ww xY w)%z!Upload report for a specific case!SELECT id FROM cases WHERE id = ?r  r   r
  r  r  r*  r9  r=   r  r@   r>   r!   TrA   r,   r-   a(  
            INSERT INTO reports (
                report_title, report_type, report_date, file_name,
                file_path, file_size, file_type, case_id, doctor_id,
                status, description, notes, uploaded_by
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        r[   r  Nr  rS  r   rH   r  r{   r   rI   r   r   ry  r   r  z?An error occurred while uploading the report. Please try again.r`   )&r   r   r   r   r   r   r   r   r  r2   r4   r   r   rL   rM   rN   rO   rP   r0   r1   rQ   r  rU   r.   r/   r   r  rV   r   r
   r   r  r  r  r  r  r  r  )rX   r   r  r  r  r_   r2   r`   rc   r  r  r  r  r  r  s                  r3   case_report_uploadr    s	   
 8D<<;gZHQQSD

(-.. GMM)

 '*W=>>=='D}}

 '*W=>>&

I7SW=>>7?+DMM:LLN++O<	["3!45 77<<

? ;XF
CJJ/$?		( GGOOH-	*11#q9!<BBD ||##%    HH^$9(9HH]#+tHH]#+tHH[!)THHXw'HH]#+tHHW%KK
H-
	, 	

-y9W=@WWXX 	?,

OQXY!bggnnX&>IIhW=>>	?s    >G,L+ +	O4BO	OOz/reports/<int:report_id>c                    t               }|j                  d| f      j                         }|j                  d| f      j                         }|j                          |s t	        dd       t        t        d            S t        |      }|r|d   }||d<   |rt        |      nd|d<   n
d|d<   d|d<   t        d	|
      S )zView report details7
        SELECT * FROM v_reports_full WHERE id = ?
    z@
        SELECT form_html_content FROM reports WHERE id = ?
    Report not foundr   r  form_html_contentNform_html_content_previewzreport_view.htmlr  )
r   r   r   r   r   r   r   r   rq   r   )r  r   r  form_contentraw_htmls        r3   r  r    s     8D\\ 
xz  << !
xz  	JJL '*/00&\F 34&."#IQmH.EW[*+&*"#.2*+-f==r5   z/reports/<int:report_id>/editc                    t               }|j                  d| f      j                         }|j                          |s t	        dd       t        t        d            S t        |      }|j                  d      s"t	        dd       t        t        d|             S |j                  d	      xs |j                  d
      xs d|d	<   t        d|      S )z-Open editable view for template-based reportsz
        SELECT r.*, c.file_case_id
        FROM reports r
        LEFT JOIN cases c ON r.case_id = c.id
        WHERE r.id = ?
    r
  r   r  r  z4This report does not have editable template content.r  r  form_templater  report.htmlzreport_editor.htmlr  )
r   r   r   r   r   r   r   r   r   r   )r  r   r  s      r3   report_edit_popupr    s     8D\\ 
 
  xz  	JJL '*/00&\F::)*DgNCDD$jj9eVZZ=TeXeF?/??r5   z!/reports/<int:report_id>/downloadc                    t               }|j                  d| f      j                         }|j                          |st	        d       |d   dk(  rO|d   rJ|d   xs d|  d}|d   }t        |      }t        |      }t        |d	
      }d| d|j                  d<   |S t        |d         }|rt        j                  j                  |      st	        d       t        |d|d         S )zDownload report filezi
        SELECT file_path, file_name, file_type, form_html_content
        FROM reports WHERE id = ?
    r  	file_typerG   r  r  report_r<   ztext/html; charset=utf-8)mimetypezattachment; filename=""zContent-Dispositionr  Tr  )r   r   r   r   r   rq   rR   r   headersrk   rN   rO   r  r   )	r  r   r  r  raw_contentrn   ra   response	full_paths	            r3   report_downloadr  $  s    8D\\    xz  	JJLc
 kf$0C)D{+I5/I01,*73M4NO4KM?Z\2]./()<=IBGGNN95c
[) r5   z/reports/<int:report_id>/deletec                    	 t               }|j                  d| f      j                         }|r^t        |d         }|r4t        j
                  j                  |      rt	        j                  |       t        d| f       t        dd       nt        dd       |j                          t        t        d
            S # t        $ r"}t        dt        |       d       Y d	}~:d	}~ww xY w)zDelete reportz@
            SELECT file_path FROM reports WHERE id = ?
        r  z DELETE FROM reports WHERE id = ?zReport deleted successfullyr   r
  r   zError deleting report: Nr  )r   r   r   rk   rN   rO   r  r  rV   r   r   r   r  r   r   )r  r   r  r`   r  s        r3   report_deleter  H  s    ;x \#8: 	 /{0CDHBGGNN84		(# 9I<H/;$g.

 GN+,,  ;'Ax0'::;s   B'B= =	C(C##C(z/reports/<int:report_id>/printc                     t               }|j                  d| f      j                         }|j                          |s t	        dd       t        t        d            S ddlm} t        d||j                        S )	z&Print-friendly view of report metadatar	  r
  r   r  r   r   zreport_print.html)r  rL   )
r   r   r   r   r   r   r   r   r   rL   )r  r   r  r   s       r3   report_printr!  d  sm     8D\\ 
xz  	JJL '*/00!.v8<<PPr5   c                     t               } t        j                         j                  }d| d}d }| j	                  d|f      j                         }| j	                  d|f      j                         }| j                          d}|r|d   rt        | ||d               }|r|d   rt        | ||d               }|r|d	z   nd	}d| d
|dS )zLGenerate unique invoice/reference number shared by invoices and appointmentsINV-z-%c                 \    	 t        | j                  d      d         S # t        $ r Y yw xY w)N-r   )r   splitr   )values    r3   extract_suffixz/generate_invoice_number.<locals>.extract_suffix  s2    	u{{3'+,, 		s    	++z
        SELECT invoice_number FROM invoices 
        WHERE invoice_number LIKE ? 
        ORDER BY invoice_number DESC 
        LIMIT 1
    z
        SELECT reference FROM appointments
        WHERE reference LIKE ?
        ORDER BY reference DESC
        LIMIT 1
    r   r  r  r-   r%  04d)r   r   rL   yearr   r   r   max)r   current_yearpatternr)  last_invoicelast_referencelast_numnew_nums           r3   r  r  {  s    8D<<>&&L\N"%G << !
 
 XZ  \\ #
 
 XZ  	JJLH%56x=M0N!OP.5x{0K!LM&hlAG,q..r5   z/api/invoices/next_numberc                      	 t        dt               i      S # t        $ r"} t        dt        |       i      dfcY d} ~ S d} ~ ww xY w)z:Return the next invoice number without creating an invoicer  r     N)r	   r  r   r  )r  s    r3   api_next_invoice_numberr5    sE    1)+B+DFGG 1#a&*+S001s    	A=AAz/api/forms/listc                  N   ddl } d}g }| j                  j                  |      rvt         | j                  |            D ]X  }|j                  d      s|j                  dd      j                  dd      j                         }|j                  ||d       Z t        d	|i      S )
z!List all available form templatesr   N$forms/templates_bundle_cursor_prettyr<   r=   r>   r?   )r2   r!  forms)
rN   rO   r  sortedlistdirendswithrJ   rK   r   r	   )rN   	forms_dirr8  r2   r!  s        r3   api_forms_listr=    s     6IE	ww~~i zrzz)45 	H  )''4<<S#FLLN (  		 We%&&r5   z#/api/forms/<path:form_name>/previewc                 <   ddl }t        j                  j                  d      }t        j                  j                  d      }d}|rlt        j                  dk(  rYt               }|j                  d|f      j                         }|j                          |st        ddi      d	fS t        |      }n|rYt               }|j                  d
|f      j                         }|j                          |st        ddi      d	fS t        |      }nit        j                  dk(  rFt        j                  }|j                  d      ryi d|j                  d      xs dd|j                  d      xs dd|j                  d      xs dd|j                  d      xs dd|j                  d      xs dd|j                  d      xs dd|j                  d      xs dd|j                  d      xs dd|j                  d      xs dd|j                  d      xs dd|j                  d      xs dd|j                  d      xs |j                  d      xs dd|j                  d       xs dd!|j                  d!      xs dd"|j                  d"      xs dd#|j                  d#      xs dd$|j                  d%      xs d|j                  d&      xs dd|j                  d'      xs d|j                  d(      xs d|j                  d)      xs d|j                  d*      xs d|j                  d+      xs d|j                  d,      xs d|j                  d-      xs d|j                  d.      xs d|j                  d/      xs d|j                  d0      xs d|j                  d1      xs d|j                  d2      xs dd3}|j                  d1      rt               }|j                  d4|j                  d1      |j                  d1      f      j                         }|j                          |r|d5   |d6<   |d7   |d8<   |d9   |d:<   n|j                  d1d      j                         }	t        |	      dkD  r|	d;   |d8<   t        |	      d<kD  rd=j                  |	dd;       |d6<   n|j                  d      }|st        dd>i      d?fS t               }|j                  d@|f      j                         }|j                          |st        ddi      d	fS t        |      }|j                  d/      |d/<   |j                  d0      |d0<   |j                  d1      |d1<   |j                  d2      |d2<   |j                  d1      rt               }|j                  d4|j                  d1      |j                  d1      f      j                         }|j                          |r|d5   |d6<   |d7   |d8<   |d9   |d:<   nk|j                  d1d      j                         }	t        |	      dkD  r|	d;   |d8<   t        |	      d<kD  r'd=j                  |	dd;       |d6<   nt        ddAi      d?fS |j                  j                  dB|       }
|j                  j!                  |
      st        ddCi      d	fS t#        |
dDdEF      5 }|j%                         }ddd       i dG|j                  dH      xs ddI|j                  dJ      xs ddK|j                  d      xs ddL|j                  d!      xs ddM|j                  d"      xs ddN|j                  d#      xs ddO|j                  d$      xs ddPddQ|j                  d      xs ddR|j                  d      xs ddS|j                  d      xs ddT|j                  dU      xs ddV|j                  d(      xs ddW|j                  d)      xs ddX|j                  d*      xs ddY|j                  dZ      xs dd[dd\|j                  d6      xs d|j                  d8      xs |j                  d1      xs d|j                  d      xs |j                  d      xs d|j                  d/      xs ddd]}|j'                         D ]   \  }}j)                  |t+        |            }" ddl}d^ }d_j                  |j                  d"      xs |j                  d      |j                  d#      xs dfD cg c]  }|s|	 c}      j/                  d_      } ||j                  d      |j                  d             ||j                  d      |j                  d             ||j                  d.             ||j                  d.             ||j                  d/             ||j                  d`             ||j                  da             ||j                  db            |dc	fdd}|j1                  de|      }|j3                         D ]&  }|j)                  dfdg      }|j)                  |d      }( t        || |xs ddh      S # 1 sw Y   xY wc c}w )iz*Get form template with auto-populated datar   Nr]   rX   r   a  
            SELECT c.*, c.file_case_id, c.record_id,
                   cl.first_name as claimant_first_name, cl.last_name as claimant_last_name,
                   cl.title as claimant_title, cl.date_of_birth as claimant_dob,
                   cl.address as claimant_address, cl.city as claimant_city,
                   cl.province as claimant_province, cl.postal_code as claimant_postal,
                   cl.email as claimant_email, cl.home_phone as claimant_phone,
                   cli.company_name as client_company, cli.address as client_address,
                   cli.city as client_city, cli.province as client_province,
                   cli.postal_code as client_postal,
                   cc.first_name as client_contact_first, cc.last_name as client_contact_last,
                   lf.firm_name as lawyer_firm, lf.address as lawyer_address,
                   lf.city as lawyer_city, lf.province as lawyer_province,
                   lf.postal_code as lawyer_postal,
                   lc.first_name as lawyer_first, lc.last_name as lawyer_last
            FROM cases c
            LEFT JOIN claimants cl ON c.claimant_id = cl.id
            LEFT JOIN clients cli ON c.client_id = cli.id
            LEFT JOIN client_contacts cc ON c.client_contact_id = cc.id
            LEFT JOIN lawyer_firms lf ON c.lawyer_firm_id = lf.id
            LEFT JOIN lawyer_contacts lc ON c.lawyer_contact_id = lc.id
            WHERE c.id = ?
        r   r  r  a  
            SELECT a.*, c.file_case_id, c.record_id,
                   cl.first_name as claimant_first_name, cl.last_name as claimant_last_name,
                   cl.title as claimant_title, cl.date_of_birth as claimant_dob,
                   cl.address as claimant_address, cl.city as claimant_city,
                   cl.province as claimant_province, cl.postal_code as claimant_postal,
                   cl.email as claimant_email, cl.home_phone as claimant_phone,
                   cli.company_name as client_company, cli.address as client_address,
                   cli.city as client_city, cli.province as client_province,
                   cli.postal_code as client_postal,
                   cc.first_name as client_contact_first, cc.last_name as client_contact_last,
                   lf.firm_name as lawyer_firm, lf.address as lawyer_address,
                   lf.city as lawyer_city, lf.province as lawyer_province,
                   lf.postal_code as lawyer_postal,
                   lc.first_name as lawyer_first, lc.last_name as lawyer_last,
                   d.first_name as doctor_first, d.last_name as doctor_last,
                   d.specialty as doctor_specialty
            FROM appointments a
            LEFT JOIN cases c ON a.case_id = c.id
            LEFT JOIN claimants cl ON c.claimant_id = cl.id
            LEFT JOIN clients cli ON c.client_id = cli.id
            LEFT JOIN client_contacts cc ON c.client_contact_id = cc.id
            LEFT JOIN lawyer_firms lf ON c.lawyer_firm_id = lf.id
            LEFT JOIN lawyer_contacts lc ON c.lawyer_contact_id = lc.id
            LEFT JOIN doctors d ON a.doctor_name = d.name OR a.doctor_name = (d.first_name || ' ' || d.last_name)
            WHERE a.id = ?
        r  r   case_form_datarv   r=   r  r~   r   claimant_titler  r/  r1  r2  r3  claimant_postalr4  r0  claimant_phoner5  r7  r  r<  r?  r@  rA  client_postalrB  client_namerD  lawyer_addresslawyer_citylawyer_provincelawyer_postal_codelawyer_first_namelawyer_last_namerU  rw   ry   rz   r   )client_contact_firstclient_contact_lastr  rE  rF  rG  lawyer_postallawyer_firstlawyer_lastrU  rw   ry   rz   r   z
                    SELECT first_name, last_name, specialty
                    FROM doctors
                    WHERE name = ? OR (first_name || ' ' || last_name) = ?
                    LIMIT 1
                r  doctor_firstr  doctor_lastr#  doctor_specialtyr&  r-   r?   z(case_id is required for new appointments  a  
                SELECT c.*, c.file_case_id, c.record_id,
                       cl.first_name as claimant_first_name, cl.last_name as claimant_last_name,
                       cl.title as claimant_title, cl.date_of_birth as claimant_dob,
                       cl.address as claimant_address, cl.city as claimant_city,
                       cl.province as claimant_province, cl.postal_code as claimant_postal,
                       cl.email as claimant_email, cl.home_phone as claimant_phone,
                       cli.company_name as client_company, cli.address as client_address,
                       cli.city as client_city, cli.province as client_province,
                       cli.postal_code as client_postal,
                       cc.first_name as client_contact_first, cc.last_name as client_contact_last,
                       lf.firm_name as lawyer_firm, lf.address as lawyer_address,
                       lf.city as lawyer_city, lf.province as lawyer_province,
                       lf.postal_code as lawyer_postal,
                       lc.first_name as lawyer_first, lc.last_name as lawyer_last
                FROM cases c
                LEFT JOIN claimants cl ON c.claimant_id = cl.id
                LEFT JOIN clients cli ON c.client_id = cli.id
                LEFT JOIN client_contacts cc ON c.client_contact_id = cc.id
                LEFT JOIN lawyer_firms lf ON c.lawyer_firm_id = lf.id
                LEFT JOIN lawyer_contacts lc ON c.lawyer_contact_id = lc.id
                WHERE c.id = ?
            z'appointment_id or form data is requiredr7  zForm template not foundr'  rD   rE   z	<clfirst>rK  z<cllast>rL  z<clcompany>z<claddress>z<clcity>z<clprovince>z
<clpostal>z	<clemail>z	<cmtitle>z	<cmfirst>z<cmlast>z<lwcompany>r  z<lwaddress>z<lwcity>z<lwprovince>z
<lwpostal>rM  z	<lwemail>zDr.)z	<drtitle>z	<drfirst>z<drlast>z	<ourfile>z<lcdate>z<lcfee>c                  0    | D ]  }|st        |      c S  y)Nr=   )r  )valsvs     r3   pickzapi_form_preview.<locals>.pick  s"     	A1v	 r5   z / rp  rn  ro  )	zFILE NOzFILE NUMBERzCLAIM #zCLAIM NUMBERDATEz
REPORT DUEzDEADLINE DATEzLOI DATEz	CITY/PROVc                     | j                  d      j                         j                         }j                  || j                  d            S )Nr-   r   )groupr   upperr   )mkeybracket_maps     r3   replace_double_anglez.api_form_preview.<locals>.replace_double_angle  s;    ggaj &&(sAGGAJ//r5   z<<\s*([^>]+?)\s*>><z</)rG   rY   r]   )rN   r   r   r   r   r   r   r   r   r	   r   jsonr'  r  rP   rO   r  rS   readitemsrJ   r  rer   subkeys)rY   rN   r]   rX   r   r   r  	form_datarB  
name_parts	form_pathrb   	form_htmlreplacementsplaceholderr(  rd  rW  rV  	city_provr_  closing_tagr^  s                         @r3   api_form_previewro    s+    \\%%&67Nlly)GK7>>U*x|| , Z-, "- 	. 	

W&6893>>4j	xll $4 5 4 !)
5 	6 	

W&=?@#EE;'	6	!LL	 ==)* 	n = C Y]];7=2  &y}}5J'K'Qr  %imm4H&I&OR	 
 !)--0@"A"GR  	.F G M2  #IMM2D$E$K   !?!E2  $Y]]3F%G%M2  "9==1G#H#NB  !)--0@"A"GR  !)--0E"F"s)--XlJm"sqs  !)--0E"F"L"  !)--0@"A"GR  y}}];Ar   "9==1B#C#Ir! "  /C!D!J# $ )2m(D(J')(}}-?@FB"+--0@"A"GR(}}];Ar#,==1B#C#Ir!*/C!D!J ).A B Hb(}}-?@FB&/mm4H&I&OR$-MM2D$E$K$-MM2D$E$K(}}];Ar )n = C? KF }}]+x '
  mmM2IMM-4PQS
 T\S[S]  

282FK/171DK.6<[6IK 23 "+}b!A!G!G!IJ:*5?^M2:*69hhz#26ON3  mmI.G*TVWY\\\ 8D<< !, -, &XZ- . JJL*:<=sBB t*K.7mm<N.OK*+.7mm<N.OK*+)2})EK&*3--*GK' }}]+x '
  mmM2IMM-4PQS
 T\S[S]  

282FK/171DK.6<[6IK 23 "+}b!A!G!G!IJ:*5?^M2:*69hhz#26ON3"KMNPSSS CYOI77>>)$";=>CC	iw	/ 1FFH	[__%;<BKOO$9:@b 	{'78>B 	{'78>B	
 	KOOM28b 	(9:@b 	kooo6<" 	R 	[__%56<" 	[__%:;Ar 	KOO$89?R 	{}5; 	{'78>B 	KOOM28b 	(9:@b  	kooo6<"!" 	R#$  __^4:OOM2Zkoom6TZXZ __^4Z8TZXZOO$67=2/L6 +002 ?U%%k3u:>	? 
 

(F(j+//ZiJjlwl{l{  }N  mO  mU  SU  (V  \!  Z[A  \  ]  c  c  di  jI79UVKOON;[__[=YZ(<=>[__-ABC[__%789;???;<koo.>?@9:
K0 ,.BINI $((* 7!))#t4%%k26	7 (0D   R \s   j2j:jjz/api/forms/savec                  @   t         j                  } | j                  d      }| j                  d      xs d}| j                  d      }| j                  d      }| j                  d|j                  dd      j                  d	d
      j	                               }| j                  d      }|r|st        ddi      dfS 	 t               }|r|j                  d|f      j                         }|s|j                          t        ddi      dfS |d   }	|	}
|
sht        j                         j                  d      }t        |      xs d}t        j                  j!                  t"        j$                  d   | d	|       }
t'        |
      }t        j(                  t        j                  j+                  |      d       t-        |      }t/        |dd      5 }|j1                  |       ddd       t        j                  j3                  |      }|j                  d|t        j                         j5                         ||
|t7        j                  dd      |||f	       |j9                          |j                          t        ||dd      S d}|r8|j                  d|f      j                         }|r|d   }nUt        dd i      dfS |r5|j                  d!|f      j                         }|r|}nt        dd"i      dfS t        dd#i      dfS t;        ||||t7        j                  dd      |$      }|st        dd%i      dfS |j                          t        d|d&|d'      S # 1 sw Y   xY w# t<        $ r"}t        dt?        |      i      d(fcY d}~S d}~ww xY w))zSave completed form as reportrG   rY   r  r]   rX   r[   r<   r=   r>   r?   r  r   z+Missing required fields: html and form_namerS  zv
                SELECT id, file_path, file_name, form_template
                FROM reports WHERE id = ?
            r
  r  r  r@   r!   TrA   rC   rD   rE   Nz
                UPDATE reports
                SET report_title = ?, report_date = ?, file_name = ?, file_path = ?, 
                    file_size = ?, uploaded_by = ?, form_template = ?, form_html_content = ?
                WHERE id = ?
            r   rI   )r  r[   updatedz-SELECT case_id FROM appointments WHERE id = ?r  r  r  z,Either appointment_id or case_id is required)rX   rY   rZ   r[   r\   r]   zUnable to save reportzForm saved successfully)r   r  r   r[   r4  ) r   ra  r   rJ   rK   r	   r   r   r   r   r   rL   rM   r   rN   rO   rP   r0   r1   rk   rQ   dirnamerR   rS   rT   rU   rW   r
   r   re   r   r  )r  rj  rY   r]   rX   r[   r  r   r  rj   relative_pathr_   	safe_namer  ra   rb   rc   case_id_to_user   r  r  s                        r3   api_form_saverv    s~    <<D I%6IXX./Nhhy!G88NI,=,=gr,J,R,RSVX[,\,b,b,deL%II"OQRTWWWY1x|| %   (xz 
 

*<>?DD";/K'M $LLN33OD	+I6G-	 "SZZ-HYKWXYbXcJd e0?IKK	2TB.y9Miw7 '1&'	2ILL  ##%J1
" KKMJJL)\^bdee ,,'VYgXijssuK!,Y!7*ACDcII<< CgZPYY[D!(*:<=sBBW&TVWY\\\-""%J9)
	 W&=?@#EE

"0(	
  	u' 'B  1#a&*+S001s]   >AM2 C	M2 M%(B$M2 ;M2 	6M2  M2 5M2 M2 %M/*M2 2	N;NNNz	/invoicesc                     g } d}d}t         j                  j                  dd      }t         j                  j                  dd      }	 t                t	               }d}g }|r |dz  }d| d}|j                  |gdz         |r|d	z  }|j                  |       |d
z  }|j                  ||      j                         } | D 	cg c]  }	t        |	       } }		 |j                  d      j                         }
|
r|
d   nd}	 ddlm}m} |j%                          |d      z
  }|j                  d|f      j                         }|r|d   nd}|j'                          t1        d| ||||      S c c}	w # t        $ r*}t        d| t        j                         d}Y d}~d}~ww xY w# t        $ r*}t        d| t        j                         d}Y d}~d}~ww xY w# t        $ rd}t        d| t        j                         ddl}|j+                  t        j                         t-        dt/        |       d       Y d}~d}~ww xY w)z(List all invoices with search and filterr   r   r=   r   z'SELECT * FROM v_invoices_full WHERE 1=1a   AND (
                invoice_number LIKE ? OR
                file_case_id LIKE ? OR
                claimant_first_name LIKE ? OR
                claimant_last_name LIKE ? OR
                client_company_name LIKE ? OR
                specialist_name LIKE ?
            )r      r  z0 ORDER BY invoice_date DESC, invoice_number DESCz
                SELECT COUNT(*) as count FROM invoices 
                WHERE is_overdue = 1 AND status NOT IN ('paid', 'cancelled')
            r  zError getting overdue count: r  NrX  rZ  r[  a  
                SELECT COUNT(*) as count FROM appointments a
                LEFT JOIN invoices i ON a.id = i.appointment_id
                WHERE i.id IS NULL 
                AND a.appointment_date < ?
                AND a.appointment_date IS NOT NULL
            z Error getting uninvoiced count: zError in invoices_list: zError loading invoices: r   zinvoices_list.html)r  r   re  overdue_countuninvoiced_count)r   r   r   !check_and_update_overdue_invoicesr   r  r   r   r   r   r   r   r   r  r  r   rW   rY  r  r   r  r  r   r  r   )r  overdue_count_valueuninvoiced_count_valuer   re  r   r   r  r  r&  ry  r  rW   rY  r]  r^  r  s                    r3   invoices_listr~  F  s_   
 H\\h+FLL$$Xr2MA<)+x9  E fXQ-KMM;-!+,&&EMM-(CC<<v.779 *22#DI22	$ LL *    =J-"8q	'0"jjlYB-??O&*ll 4 "#'% &.XZ $ Ja%<W%Efg"
 	

 /"* &'4':*@B BK 3  	$1!5CJJG"#	$   	'4QC8szzJ%&"	'  <(,3::>,(Q17;;	<s{   A;G E.G (E3 AF) G .G 3	F&< F!G !F&&G )	G2 GG GG 	I(AIIz/invoices/newz"/invoices/new/<int:appointment_id>c                    t               }t        j                  dk(  rt        j                  j	                         }	 d}|j                  d      rst               }|j                  d|j                  d      f      j                         }|j                          |r'|d   r"t        |d         j                  d      r|d   }|s
t               }t        |j                  dd            }t        |j                  d	d
            }t        ||dz  z  d      }t        ||z   d      }	t        d||j                  d      |j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      |j                  d      |j                  d      ||||	|j                  dd      |j                  d      |j                  d      t        j
                  dd      f      }
|j                          t!        dd       t#        t%        d|
            S d}| r.|j                  d | f      j                         }|rt)        |      }g }	 ddl}t-        d!d"d#$      5 }t/        |       |j1                  |      }|D ]m  }|j                  d%d&      j3                         }|s&|j                  d'd&      xs |j                  d(d&      }|j5                  ||j3                         d)       o 	 ddd       |j                          dd+lm} |j=                         j?                  d,      }tA        d-d|g ||.      S # t&        $ r3}|j                          t!        dt        |       d       Y d}~\d}~ww xY w# 1 sw Y   xY w# t&        $ r}t7        d*|        Y d}~d}~ww xY w)/z0Create new invoice (optionally from appointment)r   Nr]   z/SELECT reference FROM appointments WHERE id = ?r  r#  feer   tax_rate      *@d   r  a  
                INSERT INTO invoices (
                    invoice_number, invoice_date, appointment_id, case_id, specialist_id,
                    service_code, service_description, service_date,
                    fee, tax_rate, tax_amount, total_amount,
                    status, notes, internal_notes, created_by
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            r  rX   specialist_idr   service_descriptionservice_dater   rH   r{   internal_notesr   rI   zInvoice created successfully!r   invoice_view
invoice_idzError creating invoice: r   a_  
            SELECT a.*,
                   c.file_case_id,
                   c.record_id,
                   cl.first_name AS claimant_first_name,
                   cl.last_name AS claimant_last_name,
                   cl.full_name AS claimant_full_name,
                   cli.company_name AS client_company_name,
                   a.doctor_name AS doctor_full_name
            FROM appointments a
            LEFT JOIN cases c ON a.case_id = c.id
            LEFT JOIN claimants cl ON c.claimant_id = cl.id
            LEFT JOIN clients cli ON c.client_id = cli.id
            WHERE a.id = ?
        r|  r'  r}  rE   r~  r=   r  r  r  r  r  %Y-%m-%dinvoice_form.html)invoicer   
line_itemsr  r  )!r   r   r   r   r  r   r   r   r   r  rh   r  r  roundrV   r
   r   r   r   r   r   r  rS   r  r  r   r   r   r   rW   r  rM   r   )r]   r   r  r  
conn_checkapptr  r  
tax_amountr  r  r  r   r  r  rb   r  r  r  r  rW   r  s                         r3   r  r    s   
 8D~~||##%4	@!Nxx()#X
!))*[^b^f^fgw^x]z{  E  E  G  "D-#d;6G2H2S2STZ2[%)+%6N!!8!: *+CTXXj$78Hshn5q9J z!115L $ % ()*2d#+t)1T(./(7+!)*J1!J4 JJL19=GNzJKK Kll $   !)
 	" {+K M3S;O 	STG^^A&F wwvr*00277>26T#''-QS:TD!(( $'+zz|* 		" 	JJLJJL!!*-E.!%%0$&'4$& &k  	@JJL,SVH5w??	@>	 	  -aS12sP   G-M !N 3AN8A
NN 	N(NNNN 	N=%N88N=z/invoices/<int:invoice_id>c                     t               }|j                  d| f      j                         }|rt        |      }|j	                          |s t        dd       t        t        d            S t        d|      S )zView invoice details8
        SELECT * FROM v_invoices_full WHERE id = ?
    Invoice not foundr   r~  zinvoice_view.htmlr  	r   r   r   r   r   r   r   r   r   r  r   r  s      r3   r  r    sp     8Dll 
 
 
 w-JJL!7+011.@@r5   z/invoices/<int:invoice_id>/editc                    t               }t        j                  dk(  rft        j                  j	                         }	 t        |j                  dd            }t        |j                  dd            }t        ||dz  z  d      }t        ||z   d      }t        d|j                  d	      |j                  d
      |j                  d      |j                  d      |||||j                  dd      |j                  d      xs d|j                  d      xs d|j                  d      xs d|j                  d      |j                  d      | f       |j                          t        dd       t        t        d|             S |j                  d| f      j!                         }|rt#        |      }|s0t        dd       |j                          t        t        d            S g }		 ddl}
t'        ddd !      5 }t)        |       |
j+                  |      }|D ]m  }|j                  d"d#      j-                         }|s&|j                  d$d#      xs |j                  d%d#      }|	j/                  ||j-                         d&       o 	 ddd       |j                          dd(lm} |j7                         j9                  d)      }t;        d*|d|	|+      S # t        $ r3}|j                          t        dt        |       d       Y d}~d}~ww xY w# 1 sw Y   xY w# t        $ r}t1        d'|        Y d}~d}~ww xY w),zEdit invoicer   r  r   r  r  r  r  a  
                UPDATE invoices SET
                    invoice_date = ?, service_code = ?, service_description = ?,
                    service_date = ?, fee = ?, tax_rate = ?, tax_amount = ?,
                    total_amount = ?, status = ?, payment_date = ?,
                    payment_method = ?, payment_reference = ?,
                    notes = ?, internal_notes = ?, updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            r  r   r  r  r   rH   payment_dateNpayment_methodpayment_referencer{   r  zInvoice updated successfully!r   r  r  zError updating invoice: r   z1
        SELECT * FROM invoices WHERE id = ?
    r  r~  r|  r'  r}  rE   r~  r=   r  r  r  r  r  r  r  )r  r   r  r  )r   r   r   r   r  r  r   r  rV   r   r   r   r   r   r  r   r   r   r  rS   r  r  r   r   r   r   rW   r  rM   r   )r  r   r  r  r  r  r  r  r  r  r  rb   r  r  r  r  rW   r  s                     r3   invoice_editr  )  s    8D~~||##%'	@*+CTXXj$78Hshn5q9J z!115L  ((./(7+(0D)*2d,-5!)*4 JJL19=GNzJKK ll 
 
 
 w-!7+

011 M3S;O 	STG^^A&F wwvr*00277>26T#''-QS:TD!(( $'+zz|* 		" 	JJLJJL!!*-E.!(%)'4$	& &W  	@JJL,SVH5w??	@*	 	  -aS12sP   EK (L" :AL?A
L
L" 	L (LLLL" "	M+L>>Mz!/invoices/<int:invoice_id>/deletec                     	 t        d| f       t        dd       t	        t        d            S # t        $ r"}t        dt        |       d       Y d}~:d}~ww xY w)zDelete invoicez!DELETE FROM invoices WHERE id = ?zInvoice deleted successfullyr   zError deleting invoice: r   Nr~  r  )r  r  s     r3   invoice_deleter    s\    <6F,i8 GO,--  <(Q17;;<r  z%/invoices/<int:invoice_id>/line_itemsc                 `
   t               }t        j                  dk(  rt        j                  j	                         }	 |j                  d      rq|j                  d| f      j                         }|rM|d   rG|j                  d|d   f      j                         }dddd	d
}d}|D ]  }|j                  |d   d      }|d   j                         }	|d   dk(  r|d   r|	d|d    z  }	n*|d   dk(  r|d   r|	d|d    dz  }	n|d   r|	d|d    z  }	|j                  d| |d|d    df      j                         }
|
rt        d| |d   |	|df       |dz  } |dkD  r+t        |        |j                          t        | dd       n|j                          t        dd       t        t        d|             S |j                  d      rt!        |j                  dd            nd}|j                  d      rt!        |j                  dd            nd}t#        ||z  d       }t        d!| |j                  d      xs d"|j                  d#      xs d"|j                  d$      xs d"|j                  d%      xs d"|dkD  r|nd"|dkD  r|nd"||j                  d&d'      f	       t        |        |j                          t        d(d       t        t        d|             S |j                  d+| f      j                         }|D cg c]  }t)        |       }}|j                  d,| f      j                         }|j                          |s t        d-d*       t        t        d.            S t)        |      }g }	 dd"l}t-        d/d0d12      5 }t/        |       |j1                  |      }|D ]m  }|j                  d3d4      j3                         }|s&|j                  d5d4      xs |j                  d6d4      }|j5                  ||j3                         d7       o 	 d"d"d"       g }|rg|j                  d      rVt               }|j                  d:|d   f      j                         }|D cg c]  }t)        |       }}|j                          t=        d;||||<      S # t$        $ r3}|j                          t        d)t'        |       d*       Y d"}~d"}~ww xY wc c}w # 1 sw Y   xY w# t$        $ r(}t7        d8| t8        j:                  9       Y d"}~d"}~ww xY wc c}w )=z Manage line items for an invoicer   pull_expensesz0SELECT appointment_id FROM invoices WHERE id = ?r]   z
                        SELECT * FROM appointment_additional_services 
                        WHERE appointment_id = ? AND present = 1
                    travelinterpretervideographernominee)r  r  r  r  r   r  r  z: r  r  r   r!  z
                            SELECT id FROM invoice_line_items 
                            WHERE invoice_id = ? AND expense_type = ? AND service_description LIKE ?
                        r   a)  
                                INSERT INTO invoice_line_items (
                                    invoice_id, appointment_id, service_description,
                                    expense_type, line_total
                                ) VALUES (?, ?, ?, ?, ?)
                            g        r-   z; expense(s) pulled from appointment! Please update amounts.r   z;No new expenses to pull. All expenses may already be added.r8  r  r  quantity
unit_pricer  a  
                INSERT INTO invoice_line_items (
                    invoice_id, appointment_id, service_code, service_description,
                    service_date, quantity, unit_price, line_total, expense_type
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            Nr   r  r  expense_typer  zLine item added successfully!zError adding line item: r   zO
        SELECT * FROM invoice_line_items WHERE invoice_id = ? ORDER BY id
    z#SELECT * FROM invoices WHERE id = ?r  r~  r|  r'  r}  rE   r~  r=   r  r  r  r  r  zy
            SELECT * FROM appointment_additional_services 
            WHERE appointment_id = ? AND present = 1
        zinvoice_line_items.html)r  r  r  r  )r   r   r   r   r  r   r   r   r   rK   rV   recalculate_invoice_totalsr   r   r   r   r  r  r   r  r   r  rS   r  r  r   r   r   r  r  r   )r  r   r  invoice_checkr  expense_type_mapitems_addedr  r  r  r  r  r  
line_totalr  r  itemr  r  r  rb   r  r  r  r  r  r  s                              r3   invoice_line_itemsr    s    8D~~||##%Z	@xx( $-_blan o x x z ]3C%D*.,, 8 ((89;+= >FXZ ( #+'4(6#,	($ #$K#6 -'7';';GN<SU]'^&-n&=&C&C&E">2h>7K[C\'R8H0I/J+KKK$^4E'R\J]'R
0C/DA+FFK$V_'R/@+AAK $(<< 1 ),!GN<S;TTU8VW$Y ZbYaYc !
  (& (  !+ -.> ? + , $" (1,K=-@ #Q2:>

-hiktu

[]cd#GNz$RSS :>*9MuTXXj!45STH=AXXl=Stxxa89YZJx*4a8J  )*2d(0D./74(0D$qLd(1n
$3
$ 'z2JJL19=GNzJKK  
 
 
 *44$t*4J4ll@:-PYY[GJJL!7+011 7mG MD3S;O 
	STG^^A&F wwvr*00277>26T#''-QS:TD!(( $'+zz|* 	
	 ')#7;;/0X
(00 2 &')+ ,48: 	
 I\*\W4=*\'*\4!($.'49X	Z Zi  	@JJL,SVH5w??	@ 5 
	 
	  D-aS1

CCD +]sj   DR' A<R' D&R' S&S7 AS+A
S+$S7 3T+'	S#0(SS#+S40S7 7	T( T##T(z,/invoices/line_items/<int:line_item_id>/editc                    t               }|j                  d| f      j                         }|s0|j                          t	        dd       t        t        d            S |d   }t        j                  dk(  r2t        j                  j                         }	 |j                  d      rt        |j                  dd            nd}|j                  d	      rt        |j                  d	d            nd}t        ||z  d
      }t        d|j                  d      xs d|j                  d      xs d|j                  d      xs d|dkD  r|nd|dkD  r|nd||j                  dd      | f       t        |       |j                          t	        dd       t        t        d|            S g }		 ddl}
t'        ddd      5 }t)        |       |
j+                  |      }|D ]m  }|j                  dd      j-                         }|s&|j                  dd      xs |j                  dd      }|	j/                  ||j-                         d       o 	 ddd       |j                          t3        d!||	"      S # t         $ r3}|j                          t	        dt#        |       d       Y d}~d}~ww xY w# 1 sw Y   fxY w# t         $ r}t1        d |        Y d}~d}~ww xY w)#zEdit a line itemz
        SELECT li.*, i.id as invoice_id 
        FROM invoice_line_items li
        JOIN invoices i ON li.invoice_id = i.id
        WHERE li.id = ?
    Line item not foundr   r~  r  r   r  r   r  r  z
                UPDATE invoice_line_items SET
                    service_code = ?, service_description = ?, service_date = ?,
                    quantity = ?, unit_price = ?, line_total = ?, expense_type = ?
                WHERE id = ?
            r   Nr  r  r  r  zLine item updated successfully!r   r  r  zError updating line item: r|  r'  r}  rE   r~  r=   r  r  r  r  zinvoice_line_item_form.html)	line_itemr  )r   r   r   r   r   r   r   r   r   r   r  r   r  r  rV   r  r   r  r  rS   r  r  r   r   r   r   )line_item_idr   r  r  r  r  r  r  r  r  r  rb   r  r  r  r  s                   r3   invoice_line_item_editr  0  s    8D 
 
 #(*  

#W-011<(J~~||##%	B9=*9MuTXXj!45STH=AXXl=Stxxa89YZJx*4a8J  (0D./74(0D$qLd(1n
$3	" 'z2JJL3Y?GNzJKK M33S;O 
	STG^^A&F wwvr*00277>26T#''-QS:TD!(( $'+zz|* 	
	 	JJL8I]jkk/  	BJJL.s1vh7AA	B
	 
	  3-aS1223sQ   DI4 ,J? >AJ3A
J3J? 4	J0=(J++J03J<8J? ?	K KK z./invoices/line_items/<int:line_item_id>/deletec                    t               }|j                  d| f      j                         }|s0|j                          t	        dd       t        t        d            S |d   }	 t        d| f       t        |       |j                          t	        dd       t        t        d|            S # t        $ r2}|j                          t	        d	t        |       d       Y d
}~Ld
}~ww xY w)zDelete a line itemzD
        SELECT invoice_id FROM invoice_line_items WHERE id = ?
    r  r   r~  r  z+DELETE FROM invoice_line_items WHERE id = ?zLine item deleted successfully!r   zError deleting line item: Nr  r  )r   r   r   r   r   r   r   rV   r  r   r  )r  r   r  r  r  s        r3   invoice_line_item_deleter  |  s     8D 
"(*  

#W-011<(J
>@</R 	#:.

/;
 GNzBCC	  >

*3q6(3W==>s   $4B. .	C)7(C$$C)c                    t               }|j                  d| f      j                         }|s|j                          y|d   xs d}|d   xs d}|j                  d| f      j                         }|r|d   nd}||z   }t	        ||d	z  z  d
      }t	        ||z   d
      }	|j                  d||	| f       |j                          |j                          y)z/Recalculate invoice totals including line itemsz/SELECT fee, tax_rate FROM invoices WHERE id = ?Nr  r   r  g      @zg
        SELECT COALESCE(SUM(line_total), 0) as total FROM invoice_line_items WHERE invoice_id = ?
    r  r  r  z
        UPDATE invoices SET
            tax_amount = ?, total_amount = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
    )r   r   r   r   r  r   )
r  r   r  base_feer  r  line_items_total	total_feer  r  s
             r3   r  r    s    8D llLzm\eegG

u~"Hz")cH  
 
  /9z'*a ++IyHsN3Q7JZ/3L 	LL  lJ	/	1 	KKMJJLr5   c                     ddl m} m } t               }| j                         }|j	                  d      j                         }d}|D ][  }|d   s		  |j                  |d   d      j                         }||kD  rdnd}|d   |k7  r|j	                  d|||d	   f       |dz  }] |j                          |j                          |S #  Y xY w)
z,Check all invoices and update overdue statusr   )rW   r   z
        SELECT id, due_date, status, is_overdue 
        FROM invoices 
        WHERE status NOT IN ('paid', 'cancelled') 
        AND due_date IS NOT NULL
    due_dater  r-   
is_overduea]  
                        UPDATE invoices SET
                            is_overdue = ?,
                            status = CASE 
                                WHEN ? = 1 AND status != 'cancelled' THEN 'overdue'
                                ELSE status
                            END
                        WHERE id = ?
                    r   )	r   rW   r   r  r   r   strptimer   r   )	rW   r   r   r  r  ry  r  r  r  s	            r3   r{  r{    s    '8DJJLE ||  	
 
  M :,8,,WZ-@*MRRT"'("2Q
 <(J6LL " %j'$-@B "Q&M#* 	KKMJJLs   ACC
z/invoices/check_overduec                  ^    t               } t        d|  dd       t        t        d            S )zManually trigger overdue checkzChecked invoices. z invoice(s) marked as overdue.r   r~  )r{  r   r   r   )r  s    r3   check_overdue_invoicesr    s1     ./E	ug%C
DiPGO,--r5   z /invoices/<int:invoice_id>/printc                     t               }|j                  d| f      j                         }|rt        |      }|j	                          |s t        dd       t        t        d            S t        d|      S )zPrint-friendly invoice viewr  r  r   r~  zinvoice_print.htmlr  r  r  s      r3   invoice_printr    sp     8Dll 
 
 
 w-JJL!7+011/AAr5   z/searchc                  0   t         j                  j                  dd      } t         j                  j                  dd      }t         j                  j                  dd      }t         j                  j                  dd      }t               }d}g }| r |dz  }d|  d}|j	                  |gd	z         |d
k(  r|dz  }n
|dk(  r|dz  }|r|dz  }|j                  |       |rddlm}m}	  |j                         j                         }
|dk(  r%|dz  }|j                  |
j                                n|dk(  r1|
 |	d      z
  }|dz  }|j                  |j                                nk|dk(  r1|
 |	d      z
  }|dz  }|j                  |j                                n5|dk(  r0|
 |	d      z
  }|dz  }|j                  |j                                |dz  }|j                  ||      j                         }|j                          t        d|| |||      S )zSearch across all fieldsr  r=   r   
assessment
date_ranger   a   AND (
            record_id LIKE ? OR 
            file_case_id LIKE ? OR
            claimant_first_name LIKE ? OR 
            claimant_last_name LIKE ? OR
            claimant_full_name LIKE ? OR
            client_company_name LIKE ? OR
            lawyer_firm_name LIKE ? OR
            lawyer_first_name LIKE ? OR
            lawyer_last_name LIKE ? OR
            case_notes LIKE ?
        )r   r  r   r   r   r   z AND assessment_type = ?r   )r   rY  r  z AND file_open_date = ?weekra  r[  z AND file_open_date >= ?monthrZ  r+  im  z' ORDER BY file_open_date DESC LIMIT 100zsearch_results.html)r  r   r   r  r  )r   r   r   r   r  r   r   rY  rL   rW   r  r   r   r   r   )r   r   r  r  r   sqlr  r  r   rY  r  week_ago	month_agoyear_agor  s                  r3   global_searchr  
  s    LLS"%E\\h+F!!,3J!!,3J8D 1CF   	 %l{mb() GG	8	KK ))j! 0##% ,,CMM%//+,6!ya00H--CMM(,,./7"	r 22I--CMM)--/06!yc22H--CMM(,,./44CLLf%..0EJJL0$$ &$.$.0 0r5   z/api/searchc                     t         j                  j                  dd      } t        |       dk  rt	        dg i      S t               }g }|j                  dd|  dd|  dd|  dd|  df      j                         }|D ]7  }|j                  dd|d	    |d
    d|d    t        d|d         d       9 |j                          t	        d|i      S )zGlobal search APIr  r=   r  r  a  
        SELECT id, record_id, file_case_id, claimant_first_name, claimant_last_name
        FROM v_cases_full
        WHERE record_id LIKE ? OR file_case_id LIKE ? OR 
              claimant_first_name LIKE ? OR claimant_last_name LIKE ?
        LIMIT 5
    r   r  zCase rv   r~   r?   r   r*  r   r9  )r   rK   subtitler  )r   r   r   r  r	   r   r   r   r   r   r   )r   r   r  r  r  s        r3   
api_searchr  Z  s    LLS"%E
5zA~	2''8DG LL  eWA,!E7!%lawaL	AC DL8: 
  T.123 567q>R9S8TU;T
;	
 	 	JJLIw'((r5   r  c                     t        d      dfS )Nz404.htmlr  )r   )r   s    r3   	not_foundr  ~  s    :&++r5   r4  c                     dd l }dd l} |j                         \  }}}|j                  ||||j                         t        d      dfS )Nr   r  z500.htmlr4  )r  r  exc_infoprint_exceptionr  r   )r   r  r  exc_type	exc_valueexc_tracebacks         r3   internal_errorr    sG    )5&Hih	=szzR:&++r5   format_datec                 |    | sy	 t        j                  t        |       d      }|j                  d      S #  | cY S xY w)zFormat date for displayr}   r  z	%B %d, %Y)r   r  r  rM   )date_stringdate_objs     r3   r  r    sD     $$S%5zB  --s   /5 ;format_phonec                     | sydj                  t        t        j                  t        |                   }t	        |      dk(  rd|dd  d|dd  d	|dd  S | S )
zFormat phone numberr}   r=   r  (N   z) rx  r%  )rP   r  r  isdigitr  )r  digitss     r3   r  r    sc     WWVCKKU45F
6{b6"1:,b!Qvabzl;;Lr5   z/appointments/temp_uploadc                     	 t         j                  j                  dt              } | s2t	        dd       t        t         j                  xs t        d            S dt         j                  vr2t	        dd       t        t         j                  xs t        d            S t         j                  d   }|j                  dk(  r2t	        dd       t        t         j                  xs t        d            S t        |j                        s2t	        d	d       t        t         j                  xs t        d            S t        |j                        }t        j                         j                  d
      }| d| }t        j                   j                  dd      }t#        j$                  |d       t"        j&                  j)                  ||      }|j+                  |       t"        j&                  j-                  |      }|j/                  dd      d   j1                         }t3        d|dt        j                         j5                         ||||| ddddt7        j                  dd      dddf       t	        dd       t        t         j                  xs t        d            S # t8        $ rH}	t	        dt;        |	       d       t        t         j                  xs t        d            cY d}	~	S d}	~	ww xY w)zcUpload attachment for a case before appointment exists. Stores in reports with appointment_id NULL.rX   r   z Case is required to upload filesr   rf  r  r  r=   r  r@   r>   r!   r    TrA   r,   r-   aw  
            INSERT INTO reports (
                report_title, report_type, report_date, file_name,
                file_path, file_size, file_type, case_id, doctor_id,
                status, description, notes, uploaded_by, appointment_id,
                form_template, form_html_content
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            
AttachmentNrH   z%Additional attachment for appointmentr   rI   zFile uploaded successfully!r   zError uploading file: )r   r   r   r   r   r   r#  r   r  r2   r4   r   r   rL   rM   r0   r1   rN   rQ   rO   rP   r  rU   r.   r/   rV   rW   r
   r   r  )
rX   r  r  r_   r2   r  r`   rc   r  r  s
             r3   appointment_temp_uploadr    si   :J,,""93"74g>G,,L8K0LMM-$g.G,,L8K0LMM}}]+==B$g.G,,L8K0LMMDMM*%w/G,,L8K0LMM+DMM:LLN++O<	["3!45ZZ^^O5FG

J.77<<
H5		(GGOOH-	*11#q9!<BBD "##%!7J1!	
8 	+Y7((HG4G,HII J&s1vh/9((HG4G,HIIJs:   AJ% AJ% AJ% 3AJ% :E*J% %	K6.=K1+K61K6z/tasksc                     t         j                  j                  dd      } t         j                  j                  dd      }t         j                  j                  dd      }t         j                  j                  dd      }t               }d}g }| r|dz  }|j	                  |        |r|dz  }|j	                  |       |r|d	z  }|j	                  |       |r|d
z  }d| d}|j                  ||g       |dz  }|j                  ||      j                         }|D 	cg c]  }	t        |	       }}		 |j                  d      j                         }
|j                  d      j                         }|
r|
d   nd}|r|d   nd}|j                          ddlm} |j#                         }t%        d|| ||||||	      S c c}	w # t        $ r,}ddl}t        d| |j                         d}d}Y d}~od}~ww xY w)z)List all Bring Forward tasks with filtersr   r=   priorityassignedr   aO  
        SELECT t.*, 
               c.file_case_id,
               a.appointment_date,
               i.invoice_number
        FROM bring_forward_tasks t
        LEFT JOIN cases c ON t.case_id = c.id
        LEFT JOIN appointments a ON t.appointment_id = a.id
        LEFT JOIN invoices i ON t.invoice_id = i.id
        WHERE 1=1
    z AND t.status = ?z AND t.priority = ?z AND t.assigned_to = ?z7 AND (t.task_title LIKE ? OR t.task_description LIKE ?)r   z< ORDER BY t.due_date ASC, t.priority DESC, t.created_at DESCzJSELECT COUNT(*) as count FROM bring_forward_tasks WHERE status = 'pending'z
            SELECT COUNT(*) as count FROM bring_forward_tasks 
            WHERE status = 'pending' AND due_date < date('now')
        r  r   NzError getting task counts: r  r  ztasks_list.html)tasksre  priority_filterassigned_filterr   pending_country  r  )r   r   r   r   r   r  r   r   r   r   r   r  r   r  r   r   rW   r  r   )re  r  r  r   r   r   r  r  r  taskr  ry  pending_count_valuer|  r  r  rW   r  s                     r3   
tasks_listr    s    LL$$Xr2Mll&&z26Oll&&z26O\\h+F8D
E F$$m$&&o&))o&JJ&m{K01	KKELL'002E %**DT$Z*E* %qr{{} &  XZ 	 9FmG418EmG41 	JJLJJLE,$'4)8)8 &':':$& &- +   +A3/cjjA	 s    F?5AG 	G9"G44G9z
/tasks/newz/tasks/new/<int:case_id>z+/tasks/new/appointment/<int:appointment_id>z#/tasks/new/invoice/<int:invoice_id>c                    t               }t        j                  dk(  rt        j                  j	                         }	 | xs- |j                  d      rt        |j                  d            nd} |xs- |j                  d      rt        |j                  d            nd}|xs- |j                  d      rt        |j                  d            nd}t        d| |||j                  d      |j                  d      xs d|j                  d	      xs d|j                  d
d      |j                  dd      |j                  d      xs dt        j
                  dd      |j                  d      xs df      }t        dd       | rt        t        d|             S |rt        t        d|            S |rt        t        d|            S t        t        d            S |j%                  d      j'                         }|j%                  d       j'                         }	|j%                  d!      j'                         }
i }| r3|j%                  d"| f      j)                         }|r|d#   |d<   |d$   |d%<   |r3|j%                  d&|f      j)                         }|r|d#   |d<   |d   |d<   |r?|j%                  d'|f      j)                         }|r|d#   |d<   |j                  d      |d<   |j+                          t-        d(d||	|
|)      S # t        $ rG}t        dt        |       d       ddl}|j                  t         j"                         Y d}~vd}~ww xY w)*zCreate new Bring Forward taskr   rX   Nr]   r  a4  
                INSERT INTO bring_forward_tasks (
                    case_id, appointment_id, invoice_id,
                    task_title, task_description, due_date,
                    priority, status, assigned_to, created_by, notes
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            
task_titletask_descriptionr  r  mediumr   pendingassigned_tor   rI   r{   zTask created successfully!r   r*  r9  r  r  r  r  r  zError creating task: r   r   r  GSELECT id, file_case_id FROM cases ORDER BY file_case_id DESC LIMIT 100
        SELECT id, appointment_date, case_id 
        FROM appointments 
        ORDER BY appointment_date DESC 
        LIMIT 100
    u
        SELECT id, invoice_number 
        FROM invoices 
        ORDER BY invoice_date DESC 
        LIMIT 100
    z/SELECT id, file_case_id FROM cases WHERE id = ?r   rv   case_file_idzCSELECT id, case_id, appointment_date FROM appointments WHERE id = ?z=SELECT id, case_id, invoice_number FROM invoices WHERE id = ?task_form.htmlr  r  r  r  prefill)r   r   r   r   r  r   r   rV   r
   r   r   r   r   r  r  r  r  r  r   r   r   r   r   )rX   r]   r  r   r  task_idr  r  r  r  r  r  r  r   r  s                  r3   task_newr  =  s=    8D~~||##%)	1\dhhy>Q#dhhy&9":W[G+xSWS[S[\lSmDHH=M4N0OswN#htxxP\G]DHH\,B(CcgJ  " &+,4$,X.9-'/4J1!)TG( .	: W EFF(:> Z[[: NOO 566 LLbcllnE<< ! 	
 
  ||  	
 
  G||MPWzZcce!%dGI&*>&:GN#ll#hkyj{|  F  F  H(3D(9G$%!,Y!7GI,,^ak`mnwwy$+DMGL!!(Y!7GIJJL+"$&2"*!(* *Q  	1)#a&2G<SZZ00	1s*   EL
 L
 3L
 L
 
	M<MMz/tasks/<int:task_id>c                    t               }|j                  d| f      j                         }|rt        |      }|j	                          |s t        dd       t        t        d            S ddlm	} |j                         }t        d||      S )	zView task detailsaT  
        SELECT t.*, 
               c.file_case_id,
               a.appointment_date,
               i.invoice_number
        FROM bring_forward_tasks t
        LEFT JOIN cases c ON t.case_id = c.id
        LEFT JOIN appointments a ON t.appointment_id = a.id
        LEFT JOIN invoices i ON t.invoice_id = i.id
        WHERE t.id = ?
    Task not foundr   r  r   r  ztask_view.html)r  r  )r   r   r   r   r   r   r   r   r   rW   r  r   )r  r   r  rW   r  s        r3   	task_viewr    s     8D<< 
 
 XZ 	 DzJJL(-..JJLE+$eDDr5   z/tasks/<int:task_id>/editc                    t               }t        j                  dk(  rt        j                  j	                         }	 |j                  d      dk(  r=t        d|j                  dd      |j                  dd      | f       t        dd       nt        d	|j                  d
      rt        |j                  d
            nd|j                  d      rt        |j                  d            nd|j                  d      rt        |j                  d            nd|j                  d      |j                  d      xs d|j                  d      xs d|j                  dd      |j                  dd      |j                  d      xs d|j                  d      xs d| f       t        dd       t        t        d|             S |j#                  d| f      j%                         }|rt'        |      }|s0|j)                          t        dd       t        t        d             S |j#                  d!      j+                         }|j#                  d"      j+                         }|j#                  d#      j+                         }|j)                          t-        d$||||i %      S # t        $ rG}t        dt        |       d       ddl}|j                  t        j                          Y d}~)d}~ww xY w)&zEdit Bring Forward taskr   actioncompletea  
                    UPDATE bring_forward_tasks SET
                        status = 'completed',
                        completed_at = CURRENT_TIMESTAMP,
                        notes = COALESCE(notes || '
' || ?, ?)
                    WHERE id = ?
                completion_notesr=   zTask marked as completed!r   a  
                    UPDATE bring_forward_tasks SET
                        case_id = ?,
                        appointment_id = ?,
                        invoice_id = ?,
                        task_title = ?,
                        task_description = ?,
                        due_date = ?,
                        priority = ?,
                        status = ?,
                        assigned_to = ?,
                        notes = ?
                    WHERE id = ?
                rX   Nr]   r  r  r  r  r  r  r   r  r  r{   zTask updated successfully!r  )r  zError updating task: r   r   r  z.SELECT * FROM bring_forward_tasks WHERE id = ?r   r  r  r  r  r  r  )r   r   r   r   r  r   rV   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  s	            r3   	task_editr    s    8D~~||##%1	1xx!Z/  HH/4HH/4
 19=  150CC+,7;xx@P7QC!123W[3788L3IC./tHH\*HH/08DHHZ(0DHHZ2HHXy1HH]+3tHHW%-4 2I>GKABB <<H7*U^^`D Dz

(-.. LLbcllnE<< ! 	
 
  ||  	
 
  	JJL+"$&2"*!#% %C  	1)#a&2G<SZZ00	1s   FJ" "	K2+<K--K2z/tasks/<int:task_id>/deletec                     	 t        d| f       t        dd       t	        t        d            S # t        $ r"}t        dt        |       d       Y d}~:d}~ww xY w)zDelete Bring Forward taskz,DELETE FROM bring_forward_tasks WHERE id = ?zTask deleted successfully!r   zError deleting task: r   Nr  r  )r  r  s     r3   task_deleter    s\    9AG:N*I6 GL)**  9%c!fX.889r  __main__z0.0.0.0i  )debuguse_reloaderhostport)NrI   N)NN)r8   F)r8   )N)NNN)__doc__flaskr   r   r   r   r   r   r	   r
   r   r   r   r   werkzeug.securityr   r   werkzeug.utilsr   r   r   rN   r   ra  r  utilsr   r   r   r   r   r   r   r   r   r   r   email.messager   __name__r0   urandom
secret_keyr1   r4   r:   re   rk   rq   rR   r   r   r   r   rV   r   r   router   r   r   r   r
  r*  r  r  r  r  r  r  r  r  r  r  r  r  r+  r/  r;  r>  r@  rC  rQ  rT  rV  r^  rf  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  r5  r=  ro  rv  r~  r  r  r  r  r  r  r  r  r{  r  r  r  r  errorhandlerr  r  template_filterr  r  r  r  r  r  r  r  runr8   r5   r3   <module>r     s  
 O  O  O  O I *   	   
  
  
 &HoB3

: /

? #3

  #V

  R
2-`Q@*D/T	2F 8eV_-) .), 9& & >    3*2  *2` 80(  0(d !"j.  #j.X <%1C.  2C.J &@NF  ANF` (6(;+  <+ :A  A0 %&Z8  'Z8x *UFOD#>  E#>J 2UFOLS  MS4 DuV\o^&V  _&VP FQWPXY
A  Z
A  5'2"  3"H  !  "8  !  "4  !"  ""P :#P  #PJ >E6?3V<  4V<p *UFODa>  Ea>F ,vh?-  @- :P  P4 >E6?3B<  4B<H *UFODR>  ER>h ,vh?-  @- %&V  'V@ ?P-  P-d 8,ufoFn  G 9n` /0Y4  1Y4v 4ufoNX  OXt 6I2  J22 =xP5P  Q5Pn ;<  =$ 9F8L2  M2B CeU[_]0,  ^0,d DuV\o^-^  _-^^ FQWPXY_  Z_6 ;fXNFP  OFPP 56  7$ 3fXF2  G2: 56!q  7!qF /   /b 23  44 ./  0< :44  44l >E6?3XZ  4XZt /&BS?  CS?j %&>  '>: *+@  ,@. ./   0 D ,vh?-  @-4 +,Q  -Q*#/J &'1  (1 '  '$ 05&/JW  KWr vh/f1  0f1P ;QB  QBf ?UFO4/%Is&  J 5s&j '(A  )A( ,ufoF[&  G[&z .A.  B. 2UFOLWZ  MWZr 9E6?SHl  THlT ;fXND  OD:!H'R $vh7.  8. -.B  /B0 9H0  H0\ =) )F #, , #, , ]# $ ^$ % &9<J  :<JF 8J&  J&X <%1%v?85&/R05&/JZ*  K S @ 2
Z*x !"E  #E> &@[%  A[%z (6(;+  <+ zGG%e)$GG r5   