o GY)j hã@s`dZddlZddlmZddlZddlZddlZddlZddlm Z e  e ¡Z Gdd„dƒZ dS)z] Quality Parameters Handler Manages CRUD operations for business-specific quality parameters éN)Ú DictCursor)Úcontextmanagerc@sÖeZdZdZdd„Zedd„ƒZdd„Zedd „ƒZ ed d „ƒZ ed d „ƒZ ed0dd„ƒZ dd„Z ddœdd„Zdd„Zdd„Zdd„Zd1dd„Zd d!„Zd"d#„Zd$d%„Zd&d'„Zd(d)„Zd*d+„Zd,d-„Zd.d/„ZdS)2ÚQualityParametersHandlerz'Handle all quality parameter operationsc CsJ||_| dd¡| dd¡| dd¡| dd¡| d d ¡d td d œ|_dS)NÚDB_HOSTz 127.0.0.1ÚDB_PORTiê ÚDB_USERÚadminÚ DB_PASSWORDzmcube@admin123ÚDB_NAMEÚvoicebot_clusterÚutf8mb4T)ÚhostÚportÚuserÚpasswordÚdatabaseÚcharsetÚ cursorclassÚ autocommit)ÚconfigÚgetrÚ db_config)Úselfr©rúE/home/aiteam/pcaa-dev/dashboard-backend/quality_parameters_handler.pyÚ__init__s      øz!QualityParametersHandler.__init__c csxd}z0ztjdi|j¤Ž}|VWntjy(}z t d|›¡‚d}~wwW|r2| ¡dSdS|r;| ¡ww)z(Context manager for database connectionsNzDatabase connection error: r)ÚpymysqlÚconnectrÚErrorÚloggerÚerrorÚclose)rÚconnÚerrrÚget_connection s € €þÿ ÿ ÿz'QualityParametersHandler.get_connectioncCsR| ¡}| ¡}d}| ||f¡| ¡}|WdƒS1s"wYdS)z2Get all quality parameters for a specific businessz¦ SELECT * FROM quality_parameters WHERE bid = %s ORDER BY parameter_group, parameter_name N©r$ÚcursorÚexecuteÚfetchall©rÚbidr"r&ÚqueryÚresultsrrrÚget_parameters.s $óz'QualityParametersHandler.get_parameterscCs$d dd„t|p dƒ ¡ ¡Dƒ¡S)NÚcss|] }| ¡r|VqdS©N)Úisalnum)Ú.0ÚchrrrÚ As€z=QualityParametersHandler._normalize_header..)ÚjoinÚstrÚstripÚlower©ÚvaluerrrÚ_normalize_header?s$z*QualityParametersHandler._normalize_headercCs|durdSt|ƒ ¡S©Nr.)r5r6r8rrrÚ _clean_cellCs z$QualityParametersHandler._clean_cellcCs*t|tƒr|St|p dƒ ¡ ¡}|dvS)Nr.>Ú1ÚyÚyesÚtrueÚenabledÚ applicable)Ú isinstanceÚboolr5r6r7)r9ÚtextrrrÚ _parse_boolIs z$QualityParametersHandler._parse_boolNc CsBt|pdƒ ¡}|s |Sztt|ƒƒWSttfy |YSwr;)r5r6ÚintÚfloatÚ TypeErrorÚ ValueError)r9ÚdefaultrErrrÚ _parse_intPsÿz#QualityParametersHandler._parse_intc sôtj t|pdƒ ¡¡d}|dkr{zddlm}Wnty+}ztdƒ|‚d}~ww|t   |¡dd}|j }t |j dd ƒ}|sDgS‡fd d „|dDƒ‰g} |dd…D]!‰ˆrft‡fd d „ˆDƒƒsgqW|  ‡‡fdd„ttˆƒƒDƒ¡qW| S|dvrƒtdƒ‚z| d¡} Wnty˜| d¡} Ynwd} | dd…} zt ¡j| dd} | j} Wn7tjyæ|  ¡p½dgd}| d¡}| d¡}| d¡}||krÚ|dkrÚd} n ||krä|dkräd} Ynwtjt  | ¡| d}‡fdd „|DƒS)Nr.éz.xlsxr)Ú load_workbookz)openpyxl is required to upload XLSX filesT)Ú data_only)Ú values_onlycsg|]}ˆ |¡‘qSr©r<©r1Úcell©rrrÚ fsz>QualityParametersHandler._read_upload_rows..c3ó|]}ˆ |¡VqdSr/rQrRrTrrr3ió€z=QualityParametersHandler._read_upload_rows..cs*i|]}ˆ||tˆƒkrˆ|nd“qS)r.)Úlen)r1Úidx)ÚheadersÚrowrrÚ ks*z>QualityParametersHandler._read_upload_rows..>ú.csvr.z%Only CSV and XLSX files are supportedz utf-8-sigzlatin-1ú,i z,; |)Ú delimitersú;ú )Ú delimitercs.g|]}|rt‡fdd„| ¡Dƒƒr|‘qS)c3rVr/rQ)r1r9rTrrr3„rWzHQualityParametersHandler._read_upload_rows...)ÚanyÚvalues©r1r[rTrrrU„s.)ÚosÚpathÚsplitextr5r7ÚopenpyxlrNÚ ImportErrorrJÚioÚBytesIOÚactiveÚlistÚ iter_rowsrcÚappendÚrangerXÚdecodeÚUnicodeDecodeErrorÚcsvÚSnifferÚsniffrbrÚ splitlinesÚcountÚ DictReaderÚStringIO)rÚfilenameÚcontentÚextrNÚexcÚworkbookÚsheetrdÚrowsrErbÚsampleÚdialectÚ first_lineÚ semicolonsÚcommasÚtabsÚreaderr)rZr[rrÚ_read_upload_rowsZsZ €ÿ$ ÿ     €ø z*QualityParametersHandler._read_upload_rowsr.©rKcGs,|D]}| |¡}||vr||Sq|Sr/)r:)rÚnormalized_rowrKÚnamesÚnameÚkeyrrrÚ_row_get†s   ÿz!QualityParametersHandler._row_getcsN‡fdd„| ¡Dƒ}ˆ |ddd¡}ˆ |ddd¡}ˆjˆ |d d d ¡dd }|r,|s.d S|dus6|dkr8dSˆ |ddd¡}||ˆj|dddd pLdˆ |ddd¡ˆ |ddd¡|ˆ |dd¡|ˆ ˆ |dd¡¡ˆ ˆ |d d!¡¡ˆ ˆ |d"d#¡¡ˆ ˆ |d$d%¡¡ˆ ˆ |d&d'¡¡ˆ ˆ |d(d)¡¡ˆj|d*d+d,d p¢d,d-œdfS).Ncs"i|] \}}ˆ |¡ˆ |¡“qSr)r:r<)r1rŽr9rTrrr\Žs"z>QualityParametersHandler._row_to_parameter..zParameter GroupÚparameter_groupÚGroupzParameter NameÚparameter_nameÚNameÚScorez Max ScoreÚ max_scorerŠ)Nz/Parameter Group and Parameter Name are requiredrM)Nz%Score must be a number greater than 0z"When is this check not applicable?zNot Applicable ConditionÚapplicability_conditionzParameter TypeÚparameter_typeÚRequiredzWhat do you want to check on?zCheck DescriptionÚcheck_descriptionÚ DescriptionzDetailed DescriptionÚdetailed_descriptionzSample UtterancesÚsample_utteranceszAuto Detect N/AÚauto_detect_naz Default N/AÚ default_nazApplicable Once Per CustomerÚapplicable_once_per_customerzApplicable ThresholdÚapplicable_thresholdÚFatalÚis_fatalzSubjective MarkingÚenable_subjective_markingÚStatusÚstatusÚ Applicable)rr’r—r™r›r•rœr–rržrŸr r¢r£r¥)ÚitemsrrLrF)rr[Ú normalizedrr’ÚscoreÚ na_conditionrrTrÚ_row_to_parametersJüü ìëz*QualityParametersHandler._row_to_parametercCs²t|ƒ ¡}|s tdƒ‚| ||¡}|stdƒ‚d}d}d}g}| ¡ä} |  ¡} t|ddD]Í\} } | | ¡\} }|rI|d7}| | |dœ¡q/|   d|| d | d f¡|   ¡}|r­|d | d <d }|   ||   d ¡|   d¡|   d¡|   d¡|   d¡|   d¡|   dd¡|   dd¡|   dd¡|   d¡|   dd¡|   dd¡|   dd¡|d |f¡|d7}q/d}|   |||   d ¡|   d ¡|   d ¡|   d¡|   d¡|   d¡|   d¡|   d¡|   dd¡|   dd¡|   dd¡|   d¡|   dd¡|   dd¡|   dd¡f¡|d7}q/|   ¡Wdƒn 1s wY||||dd…dœ}|dkrW|dkrWd }|r3|d!|›d"7}|rL|d#|dd$›d%|dd&›7}t|ƒ‚|rS|d'7}t|ƒ‚|S)(zJImport quality parameters from a CSV/XLSX upload and upsert by group/name.zUploaded file is emptyz(No parameter rows found in uploaded fileré)ÚstartrM)r[r zÏ SELECT id FROM quality_parameters WHERE bid = %s AND parameter_group = %s AND parameter_name = %s LIMIT 1 rr’Úida_ UPDATE quality_parameters SET parameter_type = %s, check_description = %s, detailed_description = %s, max_score = %s, sample_utterances = %s, applicability_condition = %s, auto_detect_na = %s, default_na = %s, applicable_once_per_customer = %s, applicable_threshold = %s, is_fatal = %s, enable_subjective_marking = %s, status = %s, updated_at = NOW() WHERE id = %s AND bid = %s r—r™r›r•rœr–rFržrŸr r¢r£r¥r¦á INSERT INTO quality_parameters (bid, parameter_group, parameter_name, parameter_type, check_description, detailed_description, max_score, sample_utterances, applicability_condition, auto_detect_na, default_na, applicable_once_per_customer, applicable_threshold, is_fatal, enable_subjective_marking, status) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) Né)ÚinsertedÚupdatedÚskippedÚerrorszNo rows were imported.ú z row(s) had errors.z Row r[z: r zx Check that the file uses comma or semicolon separators and includes Parameter Group, Parameter Name, and Score columns.) r5r6rJr‰r$r&Ú enumerater«rpr'ÚfetchonerÚcommit)rr*r{r|rr±r²r³r´r"r&Úindexr[Úparameter_datar Úexistingr+ÚresultÚdetailrrrÚimport_parameters_file¶s¦   ù        ñ      ð  §\ ü$þz/QualityParametersHandler.import_parameters_filecCs@| ¡}| ¡}| d¡WdƒdS1swYdS)z7Create storage for reusable system parameter templates.as CREATE TABLE IF NOT EXISTS quality_parameter_templates ( id INT AUTO_INCREMENT PRIMARY KEY, template_key VARCHAR(100) NOT NULL DEFAULT 'default', parameter_group VARCHAR(255) NOT NULL, parameter_name VARCHAR(255) NOT NULL, parameter_type VARCHAR(100), check_description TEXT, detailed_description TEXT, max_score INT NOT NULL, sample_utterances TEXT, applicability_condition VARCHAR(255), auto_detect_na BOOLEAN DEFAULT FALSE, default_na BOOLEAN DEFAULT FALSE, applicable_once_per_customer BOOLEAN DEFAULT FALSE, applicable_threshold INT, is_fatal BOOLEAN DEFAULT FALSE, enable_subjective_marking BOOLEAN DEFAULT FALSE, status VARCHAR(50) DEFAULT 'Applicable', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_template_key (template_key), UNIQUE KEY unique_template_param (template_key, parameter_group, parameter_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci N)r$r&r')rr"r&rrrÚensure_template_table1s  "þz.QualityParametersHandler.ensure_template_tablerKc CsÐ| ¡t|pdƒ}| ¡}| ¡}| d|f¡| ¡}Wdƒn1s(wY|sf|rf| t|ƒ¡}|rf|D]$}t|ƒ}| dd¡| dd¡| dd¡| dd¡|  ||¡q<|  |¡S|S)zOGet reusable template parameters, optionally seeding from an existing BID once.rKz¼ SELECT * FROM quality_parameter_templates WHERE template_key = %s ORDER BY parameter_group, parameter_name Nr®r*Ú created_atÚ updated_at) r¿r5r$r&r'r(r-ÚdictÚpopÚsave_template_parameterÚget_template_parameters) rÚ template_keyÚ seed_from_bidr"r&r,ÚsourceÚparamÚitemrrrrÅOs,  ù õ      z0QualityParametersHandler.get_template_parameterscCs¦| ¡t|pdƒ}| ¡»}| ¡}d|vrt|drtd}| || d¡| d¡| d¡| d¡| d¡| d ¡| d ¡| d ¡| d d ¡| dd ¡| dd ¡| d¡| dd ¡| dd ¡| dd¡|d|f¡|dWdƒSd}| ||| d¡| d¡| d¡| d¡| d¡| d ¡| d ¡| d ¡| d d ¡| dd ¡| dd ¡| d¡| dd ¡| dd ¡| dd¡f¡|jWdƒS1sÌwYdS)z-Save or update a reusable template parameter.rKr®a„ UPDATE quality_parameter_templates SET parameter_group = %s, parameter_name = %s, parameter_type = %s, check_description = %s, detailed_description = %s, max_score = %s, sample_utterances = %s, applicability_condition = %s, auto_detect_na = %s, default_na = %s, applicable_once_per_customer = %s, applicable_threshold = %s, is_fatal = %s, enable_subjective_marking = %s, status = %s, updated_at = NOW() WHERE id = %s AND template_key = %s rr’r—r™r›r•rœr–rFržrŸr r¢r£r¥r¦Naý INSERT INTO quality_parameter_templates (template_key, parameter_group, parameter_name, parameter_type, check_description, detailed_description, max_score, sample_utterances, applicability_condition, auto_detect_na, default_na, applicable_once_per_customer, applicable_threshold, is_fatal, enable_subjective_marking, status) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) )r¿r5r$r&r'rÚ lastrowid)rrÆrºr"r&r+rrrrÄns`        ïÔ.      ð$¸z0QualityParametersHandler.save_template_parametercCs^| ¡| ¡}| ¡}| dt|pdƒ|f¡|jdkWdƒS1s(wYdS)z%Delete a reusable template parameter.z} DELETE FROM quality_parameter_templates WHERE template_key = %s AND id = %s rKrN)r¿r$r&r'r5Úrowcount)rrÆÚ parameter_idr"r&rrrÚdelete_template_parameter¼s û$÷z2QualityParametersHandler.delete_template_parametercCóT| ¡}| ¡}d}| |||f¡| ¡}|WdƒS1s#wYdS)z&Get a specific quality parameter by IDzy SELECT * FROM quality_parameters WHERE bid = %s AND id = %s N©r$r&r'r·)rr*rÍr"r&r+r¼rrrÚget_parameter_by_idÊs $ôz,QualityParametersHandler.get_parameter_by_idcCs’| ¡»}| ¡}d|vrj|drjd}| || d¡| d¡| d¡| d¡| d¡| d¡| d ¡| d ¡| d d ¡| d d ¡| dd ¡| d¡| dd ¡| dd ¡| dd¡|d|f¡|dWdƒSd}| ||| d¡| d¡| d¡| d¡| d¡| d¡| d ¡| d ¡| d d ¡| d d ¡| dd ¡| d¡| dd ¡| dd ¡| dd¡f¡|jWdƒS1sÂwYdS)z"Save or update a quality parameterr®ar UPDATE quality_parameters SET parameter_group = %s, parameter_name = %s, parameter_type = %s, check_description = %s, detailed_description = %s, max_score = %s, sample_utterances = %s, applicability_condition = %s, auto_detect_na = %s, default_na = %s, applicable_once_per_customer = %s, applicable_threshold = %s, is_fatal = %s, enable_subjective_marking = %s, status = %s, updated_at = NOW() WHERE id = %s AND bid = %s rr’r—r™r›r•rœr–rFržrŸr r¢r£r¥r¦Nr¯)r$r&r'rrË)rr*rºr"r&r+rrrÚsave_parameterÚs\       ïÐ4       ð$°z'QualityParametersHandler.save_parametercCsR| ¡}| ¡}d}| |||f¡|jdkWdƒS1s"wYdS)zDelete a quality parameterzg DELETE FROM quality_parameters WHERE bid = %s AND id = %s rN)r$r&r'rÌ)rr*rÍr"r&r+rrrÚdelete_parameter.s $öz)QualityParametersHandler.delete_parametercCsf| ¡%}| ¡}d}| ||f¡| ¡}|r |dr |dndWdƒS1s,wYdS)z1Calculate total possible score for all parametersz  SELECT SUM(max_score) as total_score FROM quality_parameters WHERE bid = %s AND default_na = FALSE Ú total_scorerNrÐ)rr*r"r&r+r¼rrrÚcalculate_total_possible_score<s $ôz7QualityParametersHandler.calculate_total_possible_scorecCrÏ)z'Get all parameters for a specific groupz® SELECT * FROM quality_parameters WHERE bid = %s AND parameter_group = %s ORDER BY parameter_name Nr%)rr*rr"r&r+r,rrrÚget_parameters_by_groupLs $óz0QualityParametersHandler.get_parameters_by_groupcCs\| ¡ }| ¡}d}| ||f¡| ¡}dd„|DƒWdƒS1s'wYdS)z/Get list of all parameter groups for a businessz­ SELECT DISTINCT parameter_group FROM quality_parameters WHERE bid = %s ORDER BY parameter_group cSsg|]}|d‘qS)rrrerrrrUlszAQualityParametersHandler.get_parameter_groups..Nr%r)rrrÚget_parameter_groups]s  $óz-QualityParametersHandler.get_parameter_groupsr/)rKN)Ú__name__Ú __module__Ú __qualname__Ú__doc__rrr$r-Ú staticmethodr:r<rFrLr‰rr«r¾r¿rÅrÄrÎrÑrÒrÓrÕrÖr×rrrrrs8       ,){ NT r)rÛrÚpymysql.cursorsrÚloggingrtrkrfÚ contextlibrÚ getLoggerrØrrrrrrÚs