ghOddlmZmZmZddlmZmZmZmZddl Z ddl Z ddl Z ddl m Z ddl mZddlmZddlmZmZmZddlmZmZmZmZdd lmZdd lmZmZdd lm Z e jBe"Z#dd l$m%Z%e%Z&e#jOd ejPddge dZ)d5de*de+de,fdZ-ejPddgdZ.ejPddgdZ/ejPdddgdZ0ejPddge dZ1ejPdd ge d!Z2ejPd"d ge d#Z3ejPd$dge d%Z4ejPd&ddge d'Z5ejPd(d dge d)Z6ejPd*ddge d+Z7ejPd,gd-e d.Z8ejPd/ddge d0Z9ejPd1dge d2Z:ejPd3dge d4Z;y)6)requestjsonify current_app)login_required current_user login_user logout_userNdatetime)db)api_bp)DatabaseConnectionDatabaseSchema QueryHistory) SubscriptionUserSubscriptionPlan QueryUsage)AIModelFactory)encrypt_passworddecrypt_password)token_required)DatabaseConnectorz9Database connector initialized successfully in API routesz/models/availableGET)methodsc@tj}ddddg}tjj |j j }|std|dStjj |jj }|r |jstd|dS|j}|jd d k(r|jd d d d|jdd k(r|jddddtd|dS)z9Get available AI models based on user's subscription plandeepseekz DeepSeek R1z-DeepSeek SQL model for general SQL generation)idname descriptionuser_idT)successmodelsplan_id openai_modelsYesopenaiz OpenAI GPT-4z0OpenAI's GPT-4 model for advanced SQL generation claude_modelsclaudezClaude 3z6Anthropic's Claude 3 model for advanced SQL generation) rrrquery filter_byrfirstrrr&featuresgetappend)ravailable_models subscriptionplanr/s /var/www/html/app/api/routes.pyget_available_modelsr6s8 ''L !J  %%// /HNNPL &    ! ! + +L4H4H + I O O QD t}}&   }}H||O$-"M!  ||O$-S!  " message status_codereturnc Hd|d}|j|t||fS)aHelper function to create standardized error responses Args: message: Error message status_code: HTTP status code **kwargs: Additional key-value pairs to include in the response Returns: Tuple with JSON response and status code Fr#r8)updater)r8r9kwargsresponses r5error_responser@Ts/H  OOF 8 k ))r7z /auth/logoutPOSTctjjd}|rz|jdri|j dd}t j j|j}|r%d|_ tjjtjr tt!ddd S) zAPI endpoint for user logout AuthorizationzBearer  ) auth_tokenNTzLogged out successfullyr<)rheadersr0 startswithsplitrr,r-r.rFr sessioncommitris_authenticatedr r) auth_headertokenusers r5 api_logoutrPls//%%o6K{--i8!!#&q)zz##u#5;;= "DO JJ   $$ , r7z/auth/registerc ddlm}tj}|s t dSgd}|D]}||vst dd|ddfcS|j j|d  jrt dd ddfS|j j|d  jrt ddddfS||d |d |jdd|jdd|jdd|jdd}|j|dtjj|tjjt dddS)zRegister a new userrrNo data provided)emailusernamepasswordFMissing required field: r<rTrTzEmail already registeredrU)rUzUsername already taken first_name last_namecompany job_title)rTrUrZr\r]r^rVTzRegistration successful)app.auth.modelsrrget_jsonr@rr,r-r.r0 set_passwordr rJaddrK)rdatarequired_fieldsfieldrOs r5 auth_registerrfs%    D 0118O    5eW=  zz$w-06681      zzT*%56<<>/     7mj!88L"-((;+B'((;+  D d:&'JJNN4JJ , r7z /auth/loginOPTIONSctjdk(ryddlm}ddl}tj }|s t dSddg}|D]}||vstd d |d d fcS|jj|d j}|r|j|dstd dd dfS|jstd dd dfS|jd}||_tj |_t$j&j)t+||j-dd t.j1d|j2d|j4dt6j8ddlm}|jj|j2j}tdd||j2|j4|j<|j>xsd|j@xsd|jBxsd|jDxsd|jF|r |jHnd|r |jJnd|r |jLnddd d} | S)z Login a userrgr[rrRNrSrTrVFrWr<rXrYzInvalid email or passwordz1Your account is inactive. Please contact support. remember)rmzUser z (z') logged in successfully. Auth status: )rr!TzLogin successfulr[r&status is_annual) rrTrUrZr\r]r^is_adminr3)r#r8rNrO)'rmethodr_rsecretsr`r@rr,r-r.check_password is_active token_hexrFr utcnow last_loginr rJrKrr0loggerinforrTrrLrrUrZr\r]r^rqr&rorp) rrsrcrdrerOrNrr3r?s r5 auth_loginr{sU~~"$    D 011 +O    5eW=  ::  d7m  4 : : >J       b !EDOoo'DOJJtdhhz59: KK%y4::,.UVbVsVsUtuv-%%///@FFHL%''ZZ ///R-2||)r-2 3?<//T1=,--47C\33 H, Or7cddlm}tjr)dt_t j jttdddS)zLogout the current userrrRNTzLogout successfulr<) r_rrrLrFr rJrKr rrRs r5 auth_logoutr} sI%$$"&  M & r7z /auth/profilePUTc @tj}|s tdSd|vr|dt_d|vr|dt_d|vr|dt_d|vr|dt_tjjtddtjtjtjtjxsdtj xsdtj xsdtjxsdtjd d S) Update user profilerSrZr\r]r^TProfile updated successfullyr[)rrTrUrZr\r]r^rqr#r8rO)rr`r@rrZr\r]r^r rJrKrrrTrUrq)rcs r5update_profilers    D 011t"&|"4 d!%k!2 D#I d!%k!2 JJ 1//!''$--&117R%//52#++1r%//52$--     r7z/auth/passwordctj}|s tdSgd}|D]}||vstdd|ddfcSt j |dstddddfS|d |d k7rtdd ddfSt j |d tjjtd d dS)zChange user passwordrS)current_password new_passwordconfirm_passwordFrWr<rXrCurrent password is incorrectrrzNew passwords do not matchTzPassword changed successfully) rr`r@rrrtrar rJrK)rcrdres r5change_passwordr@s    D 011OO    5eW=   & &t,>'? @6      Nt$6773     d>23JJ 2 r7z/history/countcttdt}tjj |j j}td|dS)z/Get total count of queries for the current userrr!T)r#count) getattrrrrr,r-rrr)rOrs r5get_query_countrksR 7NL 9D    ( ( ( 9 ? ? AE  r7z/historyctjdk(ryttdt}|r t |dst ddddfStj jd d t }tj jd d t }tj jdt }tj jdt }tj jdd}tjj|j}|r|j|}||j|}|red|d}|jtj tj"j%|tj&j%|}|j)tj*j-}|j/}|dkDr ||zd z |znd } || kDrt dg||||| ddS |j1||} g} | j:D]} | j=| j| j>| j"| j&| j@| jB| jD| jF| jH| jJ| j*jMd t d| | jN| jP| jR| jN| jTddS#t2$r@} t4j7dt9| t dg|d ||| ddcYd} ~ Sd} ~ wwxYw)z"Get query history for current userrgrirrFAuthentication requiredr<rkpagerE)typeper_page connection_id is_favoritesearchr[r!)rN)r%rT)rrtotalpages)r#history total_count pagination)rrzPagination error: ) rrnatural_language_query generated_sqlexecution_time is_successful error_messager model_typecredit_consumed created_at)+rrrrrhasattrrargsr0intboolrr,r-rfilterr or_rilikerorder_byrdescrpaginate Exceptionryerrorstritemsr1rrrrrrr isoformatrrrr)rOrrrrrr, search_termr total_pagesreritems r5get_query_historyrzs> ~~" 7NL 9D wtT*0     <<  FAC  0D|| BS9HLL$$_3$?M,,""=t"| }t dBdC|j|j |j"|j|j|j$|j&|jVddddDdE dFS#t$r$}t dt|d}Yd}~&d}~wwxYw#1swYaxYw#t$r[}t d?t|t8j:jUt dd@t|ddAfcYd}~Sd}~wwxYw)GrrgrirrFrr<rkzRequest headers:zRequest method:zRequest content type:z Request data:zParsed JSON data:zError parsing JSON: Nz No data provided or invalid JSONrXzUpdating profile for user ID: z , email: zCurrent user data: first_name='z', last_name='z ', company='z', job_title=''zUpdate data received: z&Before update - User data in database:z ID: z First name: 'z Last name: 'z Company: 'z Job title: 'rrDrErr[zUpdated name to: first_name='r]zUpdated company to: 'r^zUpdated job_title to: 'z%After update - User data to be saved:currentPassword newPasswordr)r)textzMarking user object as modified) flag_modifiedrZr\zCommitting changes to databasezDatabase commit successfulz)Executing direct SQL update as a fallbackzUPDATE user SET first_name = :first_name, last_name = :last_name, company = :company, job_title = :job_title WHERE id = :user_id)rZr\r]r^r"zDirect SQL update affected z rowszExpiring all objects in sessionzReloading user with ID: z'After commit (fresh user): first_name='z+ERROR: Could not reload user from database!z&Error committing changes to database: zDatabase error: Trrn) rrZr\rTrUr]r^rqr3r),rrrrrrrprintdictrG content_typercr`rrrrTrZr\r]r^rIlenrtraflaskr sqlalchemyrr rJrbsqlalchemy.orm.attributesrrKengineconnectexecuterowcount expire_allrr,r0rollbackrq) rOrcr name_partsrrrr connectionsqlresult fresh_users r5update_user_profilers\ ~~" 7NL 9D wtT*0      d7??34 W^^, !7#7#78 /7<<(! !4(  019      *477)9TZZL IJ +DOO+>JZZfgkgsgsftuCDHDRDRCSST UV "4& )* 23 F477)  ODOO,A ./ N4>>*! ,- La () N4>>*! ,- ~&\''Q/ +5*Q-2*-j/A*=A2 -doo->nT^^L\\]^_DI  %dll^156dk* ''7q9: 12 F477)  ODOO,A ./ N4>>*! ,- La () N4>>*! ,-D ]d%:""4(9#:; :  $}-.A%# /0 t <dL)dK(dI&dK( ./  *+ 9: ^^  HQC  ''"&//!%#||!%#ww  F     //@F G) H. /0  ( 23ZZ^^DGG,  ;Jc &tjdk(ryttdt}|r t |dst ddddfSt jj||j j}|st dd dd fSt d |j|j|j|j|j|j|j |j"|j$j'd dS)zGet a specific query by IDrgrirrFrr<rkrr"Query not foundT) rrrrrrrrr)r#r,)rrrrrrrrr,r-rr.rrrrrrrrrquery_idrOr,s r5get_query_by_idrs ~~" 7NL 9D wtT*0        ( (Hdgg ( F L L NE (     (("00&+&B&B"00#22"00"00 ,,**446     r7z/history//delete)rADELETErgctjdk(ryttdt}|r t |dst ddddfSt jj||j j}|st dd dd fS tjj|tjjt d d dS#t$re}tjj!t"j%dt'|t ddt'|ddfcYd}~Sd}~wwxYw)z!Delete a query history item by IDrgrirrFrr<rkrrrTzQuery deleted successfullyzError deleting query: zFailed to delete query: rN)rrrrrrrrr,r-rr.r rJdeleterKrrryrr)rrOr,rs r5delete_query_by_idrsH ~~" 7NL 9D wtT*0        ( (Hdgg ( F L L NE (      %  3      -c!fX671#a&:    s A C E (AEE E z /history//favoritectjdk(ryttdt}|r t |dst ddddfSt jj||j j}|st dd dd fS|j |_ tjjt d |jd S)z"Toggle favorite status for a queryrgrirrFrr<rkrrrT)r#r)rrrrrrrrr,r-rr.rr rJrKrs r5toggle_favoriters ~~" 7NL 9D wtT*0        ( ( TWW )  eg  (     "---EJJ (( r7z/explainct tj}|s tdS|jd}|jd}|jdd}t d|t ||gs tdSt jj|tjj}|s td d St|j}|j||jd |j |j"d }|j$j'd k(r@t)|dr4|j*r(|j*|d<t d|j*d|t,j.d<ddlm}m}m} ddlm} m} | j<| dz } |jj?|j@tjk(|jB|k(|jDdk(|jF| k\j} | sm|jjtjdj}| j<}| |jH|jJd}|jj?|j@tjk(|jF|k\jM}d}|r| jj|jNj}|r[|jP}tS|tTr d|vr|d}n5|jNdk(rd}n#|jNd k(rd!}n|jNd"k(rd#}|d#k7r||k\rtWd$d%|d&d'd(fS tXj[|j$|||)}| sud*|vrq|d*rl|tj| j<|d+d!|dd$,}t\j^ja|t\j^jcn6| r4| j<| _#t\j^jctW|S#td$rG}tfjid-tk|tWd$d-tk|d.d!fcYd+}~Sd+}~wwxYw)/z9Get the explain plan for a SQL query without executing itrSr sql_querymodelr)z+[API] Getting explain plan with model_type=Missing required parametersrDatabase connection not foundr:rUrVhostPort databaseNameoracle service_name serviceName* [API ROUTES] Adding Oracle service_name: z for explain query LAST_CONNECTION_CONFIGr)rrr)r timedeltarE)hoursexplainactive)r"rorr% query_limitbasicd professionalr enterpriseFz-You have reached your monthly query limit of z3. Please upgrade your plan to execute more queries.r<i)db_typeconnection_configrrperformance_analysisNr"r query_textr query_type is_cachedzError getting explain plan: )r#r)6rr`r@r0rallrr,r-rrr.rrVrUhostport database_namerlowerrrrconfigr_rrrr rrwrr"rrryearmonthrr&r/ isinstancerr connector explain_queryr rJrbrKrryrr)rcrrrrrVrrrrr r one_hour_ago cached_queryr3today month_start query_countrr4 plan_featuresexplain_result query_usagers r5r r st    D 011HH_-M%I'8,J 7 | DE  y) *;<<$))33 ,//4 eg =sCC 3 34H''!'q(9:"00 !X-'*n2UZdZqZq+5+B+B-( ;JK||w.&) 7&) 5&(  "  !; J;-XKL  !"00&&/! 1  6. H^\rMs$$.x0$Tc?+$ K JJNN; ' JJ    *9(//*;L ' JJ   ~&&  3CF8<=3CF8<    s CQ'' R70jA| j2| } | jddr>| jdi}d|vr(tBjEd|jddt>jG| j2| |||}t=j<}|| z }tIt j"|d|||d|ds|jd dnd!}tJjLjO||drN |t j"|jP|d d"|d#d$}tJjLjO|tJjLjY||d&<t[|S#ttf$rd }d }YwxYw#tR$r+}tBjUd%tW|Yd }~wd }~wwxYw)'z#Execute a SQL query on the databaserSrranalyze_performanceFr)rr rrE page_sizerNirrrrrrrrrrz for execute query r#rsql_modezDatabase SQL Mode: r[)rrrlimitoffsetr)r"rrrrrrrrrzError tracking query usage: r).rr`r@r0r_rr rrr ValueError TypeErrorrrr,r-rrr.rrVrUrrrrrrrrtimer get_db_configryrz execute_queryrr rJrbrwrrrrKr)rcrrrrr rrrrrVr start_timedb_config_result db_configrend_timer query_historyrrs r5rrs    D 011HH_-M%I((#8%@+!xx"HH[#.  dD !fn88FA&D ,s4y! i &;)+C! k37I&/&;C N  ax1} D( Qh) #F  y) *;<<$))33 ,//4 eg =sCC 3 34H''!'q(9:"00 !X-'*n2UZdZqZq+5+B+B-( ;J!O99O>)rX)r1s//MM PPLL%C)   8 $8    GH !E73646r*C*c*e*0nvh/0,1020dmfi%89P:Pdnvh/0"ow/ 0 D0%1%R0 1 j5)"45s6sjj5)"45j6jX'%1CD&E&P.8UV(W(T069:MN"O"Hj6(+G,GRj6(+C,Cr7