BCh@ddlZddlmZmZmZmZddlmZddlZddl Z ddlZddl m Z ddl m Z mZejeZGdde Zy)N)DictAnyOptionalList) current_app) AIModelBase)with_redis_query_cachewith_redis_explain_cacheceZdZdZdZfdZdeeefdefdZ e ddedeeefded e eeefdeeeff d Z e d eeefded edeeeffd ZxZS) ClaudeModelz6Claude model for SQL generation using direct API callsclaudect|tjj d|_tjj dd|_d|_d|_|j stjdy t|_ d|_tjd y#t$r+}tjd t|Yd}~yd}~wwxYw) z5Initialize the Claude model with direct HTTP requestsCLAUDE_3_OPUS_API_KEYCLAUDE_3_OPUS_MODELzclaude-3-opus-20240229z%https://api.anthropic.com/v1/messagesFz^Claude API key not configured. Please set CLAUDE_3_OPUS_API_KEY in your environment variables.NTzJClaude API integration initialized successfully using direct HTTP requestsz+Error initializing Claude API integration: )super__init__rconfiggetapi_keymodelapi_url initializedloggerwarningrequestsinfo Exceptionstr)selfe __class__s (/var/www/html/app/models/claude_model.pyrzClaudeModel.__init__s "))--.EF  ''++,AC[\ >  || NN{ |  S$DM#D  KKd e S NNHQQ R R Ss 'B22 C&;!C!!C& schema_datareturncg}|sy|jdg}t|tr|D]}|jdd}|jdg}|jd||jd|D]?}|jdd}|jdd } g} |jd |d | A|jd d j |S|jdij D]\}} | jdi}g} |j D]\}} | jdd } | jdd}| jdd}| jdd}g} |r| jd|r^| jdijd}| jdijd}|r|r| jd|d|d|s| jddj | }|r| jd |d | d|d| jd |d |  |jd||jd|j | d| vr| dr|jd| dj D]Y\}}dj |jdg}|jd d}|rd!nd"}|jd |d|d#|d[|jd d$|vr|d$r|jd%|d$D]y}|jd&}|jd'}|jd(}|jd)}|jd*d }|jd |d+|d,|d+|d|d {|jd d j |S)-z%Format the schema data for the promptzNo schema data availabletablesnameUnknowncolumnszTable: zColumns:dataTypeunknownz   data_type is_nullableTis_primary_keyFis_foreign_keyz PRIMARY KEY referencestablecolumnzFOREIGN KEY REFERENCES ()zNOT NULLz,  (indexeszIndexes: is_uniquez UNIQUE INDEXINDEXz on relationshipszRelationships: from_table from_columnto_table to_columntype.z -> )r isinstancelistappendjoinitemsextend)rr#formatted_schemar&r4 table_namer)r5 column_namer/ constraints table_infoformatted_columns column_infor0 is_primary is_foreign ref_table ref_columnconstraints_str index_name index_info columns_strr: index_typerelr=r>r?r@rel_types r"_format_schema_for_promptz%ClaudeModel._format_schema_for_prompt&s-2. fd # ,"YYvy9 ))Ir2 '''*(>? '' 3%LF"(**VY"?K & :y AI"$K$++b Qyk,JK L!''+ , 99-. .'2ooh&C&I&I&K( ( "J  nnY3G " ,3MMO M( ['OOKC )oomTB (__-=uE (__-=uE  &&}5 + b A E Eg NI!,r!B!F!Fx!PJ Z#**-DYKqQ[P\\]+^_"&&z2"&))K"8"%,,r+a {"_L]]^-_`%,,r+a {-KL+ M.  # #gj\$: ;  # #J /  # #$5 6J&:i+@ '' 3.8.C.I.I.K_*J "&))JNN9b,I"JK *{E BI3<'J$++b Bzl${m[\,]^ _  # #B 'Q( (V k )k/.J  # #$4 5"?3 p WW\2 !ggm4 77:.GGK0 77695 ''"ZL+d8*TUV_U``bckbllm(no p  # #B 'yy)**promptdb_type db_configc,|jsdd|jdS|j|}d}|r&d}|jD]\}}|d|d|dz }d |d |d } d |d |d|d|} d|jdd} |j d| dd| dgddd} |j j|j| | } | jdk(r| j}|jdg}|r#|djddjnd}|r@|jjds!d||jd |j d!dd"Sd|xsd#|jd |j d!dd$Sd%| jd&| j}t j#|d||jdd$S#t$$rH}t j#d't'|dd(t'||jdd$cYd)}~Sd)}~wwxYw)*z=Generate SQL query using Claude API with direct HTTP requestsF#Claude API not properly initialized)successerrorrr-zDatabase Configuration: z- z: r.z0You are an expert SQL developer specializing in z databases. Your task is to convert natural language requests into correct and efficient SQL queries. Follow these guidelines: 1. Generate only valid aj SQL syntax. 2. Focus on writing efficient queries with proper joins and conditions. 3. Return ONLY the SQL query without explanations or markdown formatting. 4. If you cannot generate a valid query, explain why. 5. Use appropriate table aliases for readability. 6. Consider performance implications of your query design. 7. Respect the database schema provided.Database Type: z Database Schema: z z2 Generate a SQL query for the following request: application/json 2023-06-01z Content-Typez x-api-keyzanthropic-version assistantrolecontentuser皙?rmessages max_tokens temperatureheadersjsonrkrtext)zi cannotz i'm sorryzsorry,Tr8r7)rb sql_queryr http_successzFailed to generate SQL query)rbrcrryClaude API error:  - z"Error generating SQL with Claude: zFailed to generate SQL query: N)r model_typer[rGrrrpostr status_coderurstriplower startswithrwrrcrr)rr]r#r^r_rIdb_config_infokeyvaluesystem_message user_messagertdataresponseresultrkrx error_messager s r" generate_sqlzClaudeModel.generate_sqls >  99+F 8N'oo/ 8 UBse2eWB"77 8NgYW y!) ,+7)4 /!- ZZ$@L9 3 }})) *H##s*!!**Y3BIGAJNN626<<>r Y__%6%A%ABf%g$(%.$(OO#4Btzzl!!D(, $)!*!L.L$(OO#4Btzzl!!D(, #5X5I5I4J#hmm_ ]  ]+$*!__$)    LL=c!fXF G 9#a&B %   s,CG$GAG H =HHH explain_datarxc|jsddgdStj|d}d|d}d|d |d |d }d |jd d}|jd|dd|dgddd} |j j |j||} | jdk(r| j} | jdg} | r#| djddjnd} g} d}| jd}d}d}|D]%jdjvrdvrtfdtd d!Drs jdd"j}|jD]=}|jd#js#t!|jd#}njj#d$rd%}|d%k(sr1djrd&dd'vr|r| j%|}|ss|d(zz }(|r| j%|| s| g} d)| | ||j&d*|jd+d,Sd-| jd.| j(}t*j-|d|gdS#YxYw#t.$r=}t*j-d/t1|dd0t1|gdcYd}~Sd}~wwxYw)1zJAnalyze database explain plan and provide recommendations using Claude APIFra)rbrcrecommendations)indentz?You are an expert database performance analyst specializing in aw databases. Your task is to analyze the execution plan of a SQL query and provide recommendations for optimization. Follow these guidelines: 1. Focus on identifying performance bottlenecks in the execution plan. 2. Provide specific, actionable recommendations to improve query performance. 3. CAUTION: Return ONLY the SQL query without explanations or markdown formatting. 4. Consider indexes, join methods, table scans, and other relevant factors. 5. Explain why each recommendation would improve performance. 6. Rate the overall efficiency of the query on a scale of 1-10. 7. Structure your analysis in a clear, organized manner.rdz SQL Query: z Execution Plan: z Please analyze this execution plan and provide: 1. A summary of how the query is being executed 2. Identification of any performance bottlenecks 3. Specific recommendations for optimization 4. An overall efficiency rating (1-10)rerfrgrhrirlrmrnrorsrvrkrrwr-r.Nrating:c38K|]}t|vywN)r).0ilines r" z3ClaudeModel.analyze_explain_plan..GsGm[\ARVGms z.,)recommendationr suggested optimizationrz. r,Tr8r7)rbperformance_analysisrefficiency_ratingrrzr{z*Error analyzing explain plan with Claude: z Failed to analyze explain plan: )rrudumpsrrrr}rr~rrsplitranyrangeisdigitintrrEr|rwrrcrr)rrr^rx explain_textrrrtrrrrk analysis_textrrlinescurrent_sectioncurrent_recommendation rating_textwordrr rs @r"analyze_explain_planz ClaudeModel.analyze_explain_plansd >#% zz,q9 ]]d\e f9 <+7)4  ' * /!- ZZ$@L9 X }})) *H##s*!!**Y3FM vr : @ @ BSU #%$%!&++D1"&)-&!AD::'J$&5J$J$!AJ$&6J$J!J$$ K*-2K%K*%K*r)__name__ __module__ __qualname____doc__r|rrrrr[r rrr r __classcell__)r!s@r"r r s@JS*X+T#s(^X+X+tn3nT#s(^ncn^fgkloqtltgu^vnCGHKMPHPCQnn`Pc3hP#PZ]PbfgjlogobpP Pr\r )loggingtypingrrrrflaskrrurapp.models.ai_model_baserapp.utils.redis_cacher r getLoggerrrr r\r"rs@,, 0R   8 $v+vr\