o
    =)jI                     @  s  U d 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 ddl	m
Z
mZmZmZmZ ddlZddlmZ eeZddd	d
dddddd	dddddddddd	dddddddddd	dd d!d"dd#d$d%d&d	d'd(dd)dd*d+ddd	gZd,ed-< G d.d/ d/ZdS )0z:CRUD for per-business sales propensity scoring parameters.    )annotationsN)contextmanager)AnyDictListOptionalTuple)
DictCursorIntentzBuying intent strength   z>Customer shows clear interest in purchasing or moving forward.hybridzNready to buy,book visit,send quotation,interested in,want to purchase,finalizez1not interested,wrong number,just browsing,no need   
   )	parameter_groupparameter_name	max_scorecheck_descriptiondetection_modepositive_keywordsnegative_keywordspositive_points_per_hitnegative_points_per_hitUrgencyzTimeline and urgency   z4Customer indicates a timeframe or urgency to decide.keywordz8this week,this month,urgent,asap,soon,immediately,withinz&later,not sure when,no hurry,next year   
CommercialzBudget readinessz@Budget, pricing, or payment willingness is discussed positively.zDbudget,afford,price is fine,payment plan,loan approved,within budgetz4too expensive,cannot afford,out of budget,overpriced	   RiskzObjection handling   z3Objections are raised and addressed constructively.z.that makes sense,understood,resolved,clear nowz8still concerned,not convinced,need to think,compare with      MomentumzNext step commitmentz,A concrete next step or follow-up is agreed.zFschedule,callback tomorrow,send details,visit site,meeting,whatsapp mez!will call you,maybe later,not nowList[Dict[str, Any]]DEFAULT_PROPENSITY_PARAMETERSc                   @  s   e Zd Zdd Zedd Zd:ddZd;d<ddZd=ddZd>ddZ	d?ddZ
d=ddZed@ddZedAd!d"ZedBdCd&d'ZdDd+d,Zd-d.dEd2d3ZdFd6d7ZdGd8d9Zd#S )HPropensityParametersHandlerc              	   C  sJ   || _ |dd|dd|dd|dd|d	d
dtdd| _d S )NDB_HOSTz	127.0.0.1DB_PORTi  DB_USERadminDB_PASSWORD DB_NAMEvoicebot_clusterutf8mb4T)hostportuserpassworddatabasecharsetcursorclass
autocommit)configgetr	   	db_config)selfr7    r;   H/home/aiteam/pcaa-dev/dashboard-backend/propensity_parameters_handler.py__init__M   s   




z$PropensityParametersHandler.__init__c                 c  sF    d }zt jdi | j}|V  W |r|  d S d S |r"|  w w )Nr;   )pymysqlconnectr9   close)r:   connr;   r;   r<   get_connectionZ   s   
z*PropensityParametersHandler.get_connectionreturnNonec                 C  s@   |   }| }|d W d    d S 1 sw   Y  d S )Na  
                CREATE TABLE IF NOT EXISTS sales_propensity_parameters (
                  id INT AUTO_INCREMENT PRIMARY KEY,
                  bid VARCHAR(50) NOT NULL,
                  parameter_group VARCHAR(255) NOT NULL,
                  parameter_name VARCHAR(255) NOT NULL,
                  check_description TEXT,
                  detailed_description TEXT,
                  max_score INT NOT NULL DEFAULT 10,
                  detection_mode VARCHAR(32) NOT NULL DEFAULT 'keyword',
                  positive_keywords TEXT,
                  negative_keywords TEXT,
                  positive_points_per_hit INT NOT NULL DEFAULT 5,
                  negative_points_per_hit INT NOT NULL DEFAULT 5,
                  max_keyword_hits INT NOT NULL DEFAULT 3,
                  status VARCHAR(50) DEFAULT 'Applicable',
                  sort_order INT NOT NULL DEFAULT 0,
                  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                  INDEX idx_bid (bid),
                  UNIQUE KEY unique_propensity_param (bid, parameter_group, parameter_name)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                )rB   cursorexecute)r:   rA   rE   r;   r;   r<   ensure_tabled   s   
"z(PropensityParametersHandler.ensure_tableTbidstractive_onlyboolr#   c                 C  sz   |    t|}|  '}| }d}|g}|r|d7 }|d7 }||| | p+g W  d    S 1 s6w   Y  d S )Nzv
                SELECT *
                FROM sales_propensity_parameters
                WHERE bid = %s
            z AND status = 'Applicable'zA ORDER BY sort_order ASC, parameter_group ASC, parameter_name ASC)rG   rI   rB   rE   rF   fetchall)r:   rH   rJ   rA   rE   queryparamsr;   r;   r<   get_parameters   s   

$z*PropensityParametersHandler.get_parametersintc                 C  sT   | j |dd}|rdS d}ttD ]\}}t|}||d< | || |d7 }q|S )NF)rJ   r   
sort_order   )rO   	enumerater$   dictsave_parameter)r:   rH   existinginsertedidxparampayloadr;   r;   r<   seed_defaults_if_empty   s   
z2PropensityParametersHandler.seed_defaults_if_emptyparameter_dataDict[str, Any]c                 C  s  |    t|}|  }| }|d|d|d|dtdt|dp*dt|dp3d	  |d
p>d|dpDdtdt|dpMdtdt|dpXdtdt|dpcd|dpkdt|dprdd}|dr|	d|d |d |d |d |d |d |d
 |d |d |d |d |d |d |d |f t|d W  d    S |	d||d |d |d |d |d |d |d
 |d |d |d |d |d |d f t|j
W  d    S 1 sw   Y  d S )Nr   r   r   detailed_descriptionrR   r   r   r   r   r   r+   r   r   r    r   max_keyword_hits   status
ApplicablerQ   r   )r   r   r   r^   r   r   r   r   r   r   r_   ra   rQ   ida
  
                    UPDATE sales_propensity_parameters
                    SET parameter_group=%s, parameter_name=%s, check_description=%s,
                        detailed_description=%s, max_score=%s, detection_mode=%s,
                        positive_keywords=%s, negative_keywords=%s,
                        positive_points_per_hit=%s, negative_points_per_hit=%s,
                        max_keyword_hits=%s, status=%s, sort_order=%s, updated_at=NOW()
                    WHERE id=%s AND bid=%s
                    a  
                INSERT INTO sales_propensity_parameters
                (bid, parameter_group, parameter_name, check_description, detailed_description,
                 max_score, detection_mode, positive_keywords, negative_keywords,
                 positive_points_per_hit, negative_points_per_hit, max_keyword_hits, status, sort_order)
                VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                )rG   rI   rB   rE   r8   maxrP   striplowerrF   	lastrowid)r:   rH   r\   rA   rE   fieldsr;   r;   r<   rU      sx   



1$z*PropensityParametersHandler.save_parameterparameter_idc                 C  s^   |    |  }| }|dt|t|f |jdkW  d    S 1 s(w   Y  d S )Nz>DELETE FROM sales_propensity_parameters WHERE id=%s AND bid=%sr   )rG   rB   rE   rF   rP   rI   rowcount)r:   rH   ri   rA   rE   r;   r;   r<   delete_parameter   s   
$z,PropensityParametersHandler.delete_parameterc                 C  s   |  |}tdd |D S )Nc                 s  s"    | ]}t |d pdV  qdS )r   r   N)rP   r8   ).0pr;   r;   r<   	<genexpr>   s     zMPropensityParametersHandler.calculate_total_possible_score.<locals>.<genexpr>)rO   sum)r:   rH   rN   r;   r;   r<   calculate_total_possible_score   s   
z:PropensityParametersHandler.calculate_total_possible_scorevaluec                 C  s$   d dd t| p	d  D S )Nr+   c                 s  s    | ]	}|  r|V  qd S N)isalnum)rl   chr;   r;   r<   rn      s    z@PropensityParametersHandler._normalize_header.<locals>.<genexpr>)joinrI   re   rf   rq   r;   r;   r<   _normalize_header   s   $z-PropensityParametersHandler._normalize_headerr   c                 C  s   | d u rdS t |  S Nr+   )rI   re   rv   r;   r;   r<   _clean_cell   s   z'PropensityParametersHandler._clean_cellNdefaultOptional[int]c              	   C  sB   t | pd }|s|S ztt|W S  ttfy    | Y S w rx   )rI   re   rP   float	TypeError
ValueError)rq   rz   textr;   r;   r<   
_parse_int  s   z&PropensityParametersHandler._parse_intfilenamecontentbytesc              
     s  t jt|pd d }|dkr{zddlm} W n ty+ } ztd|d }~ww |t	
|dd}|j}t|jdd	}|sDg S fd
d|d D  g }	|dd  D ]!rftfddD sgqW|	 fddtt D  qW|	S |dvrtdz|d}
W n ty   |d}
Y nw d}|
d d }zt j|dd}|j}W n7 tjy   |
 pdgd }|d}|d}|d}||kr|dkrd}n
||kr|dkrd}Y nw tjt	|
|d}fdd|D S )Nr+   rR   z.xlsxr   )load_workbookz)openpyxl is required to upload XLSX filesT)	data_only)values_onlyc                   s   g | ]}  |qS r;   ry   rl   cellr:   r;   r<   
<listcomp>  s    zAPropensityParametersHandler._read_upload_rows.<locals>.<listcomp>c                 3      | ]}  |V  qd S rr   r   r   r   r;   r<   rn         z@PropensityParametersHandler._read_upload_rows.<locals>.<genexpr>c                   s*   i | ]} | |t k r| nd qS )r+   )len)rl   rX   )headersrowr;   r<   
<dictcomp>!  s   * zAPropensityParametersHandler._read_upload_rows.<locals>.<dictcomp>>   .csvr+   z%Only CSV and XLSX files are supportedz	utf-8-sigzlatin-1,i    z,;	|)
delimiters;	)	delimiterc                   s.   g | ]}|rt  fd d| D r|qS )c                 3  r   rr   r   )rl   rq   r   r;   r<   rn   ;  r   zKPropensityParametersHandler._read_upload_rows.<locals>.<listcomp>.<genexpr>)anyvalues)rl   r   r   r;   r<   r   ;  s   . )ospathsplitextrI   rf   openpyxlr   ImportErrorr~   ioBytesIOactivelist	iter_rowsr   appendranger   decodeUnicodeDecodeErrorcsvSniffersniffr   Error
splitlinescount
DictReaderStringIO)r:   r   r   extr   excworkbooksheetr   rowsr   r   sampledialect
first_line
semicolonscommastabsreaderr;   )r   r   r:   r<   _read_upload_rows  s^   




	z-PropensityParametersHandler._read_upload_rowsr+   rz   normalized_rowDict[str, str]namesc                G  s,   |D ]}|  |}||v r||   S q|S rr   )rw   )r:   r   rz   r   namekeyr;   r;   r<   _row_get=  s   
z$PropensityParametersHandler._row_getr   .Tuple[Optional[Dict[str, Any]], Optional[str]]c                   sJ   fdd|  D } |ddd} |ddd} j |d	d
dd d}|r,|s.dS |d u s6|dk r8dS  j|dddd }|dvrId}|| |ddd |dd|| |dd |ddtd j |ddddpudtd j |d d!ddpdtd j |d"d#d$dpd$ j|d%d&d'dpd'd(d fS ))Nc                   s"   i | ]\}}  | |qS r;   )rw   ry   )rl   r   rq   r   r;   r<   r   E  s   " zAPropensityParametersHandler._row_to_parameter.<locals>.<dictcomp>zParameter Groupr   GroupzParameter Namer   Namez	Max Scorer   Scorer   )Nz/Parameter Group and Parameter Name are requiredrR   )Nz)Max Score must be a number greater than 0zDetection Moder   r   >   r   r   zCheck Descriptionr   DescriptionzDetailed Descriptionr^   zPositive Keywordsr   zNegative Keywordsr   zPositive Points Per Hitr   r    zNegative Points Per Hitr   zMax Keyword Hitsr_   r`   Statusra   rb   )r   r   r   r^   r   r   r   r   r   r   r_   ra   )itemsr   r   rf   rd   )r:   r   
normalizedr   r   r   r   r;   r   r<   _row_to_parameterD  sn   *z-PropensityParametersHandler._row_to_parameterc              	   C  s  t | }|std| ||}|std|   d}d}d}g }|  [}	|	 }
t|ddD ]H\}}| |\}}|rM|d7 }|	||d q3|

d||d	 |d
 f |
 }|rq|d |d< | || |d7 }q3| || |d7 }q3W d   n1 sw   Y  ||||dd d}|dkr|dkrd}|r|d| d7 }|r|d|d d  d|d d  7 }t||r|d7 }t||S )zCImport propensity parameters from CSV/XLSX; upsert by group + name.zUploaded file is emptyz(No parameter rows found in uploaded filer      )startrR   )r   errorz
                    SELECT id
                    FROM sales_propensity_parameters
                    WHERE bid = %s AND parameter_group = %s AND parameter_name = %s
                    LIMIT 1
                    r   r   rc   Nr   )rW   updatedskippederrorszNo rows were imported. z row(s) had errors.z Row r   z: r   zW Check column headers (Parameter Group, Parameter Name, Max Score) and file separators.)rI   re   r~   r   rG   rB   rE   rS   r   r   rF   fetchonerU   )r:   rH   r   r   r   rW   r   r   r   rA   rE   indexr   r\   r   rV   resultdetailr;   r;   r<   import_parameters_file  sf   


 
$z2PropensityParametersHandler.import_parameters_file)rC   rD   )T)rH   rI   rJ   rK   rC   r#   )rH   rI   rC   rP   )rH   rI   r\   r]   rC   rP   )rH   rI   ri   rP   rC   rK   )rq   rI   rC   rI   )rq   r   rC   rI   rr   )rq   r   rz   r{   rC   r{   )r   rI   r   r   rC   r#   )r   r   r   rI   rz   rI   rC   rI   )r   r]   rC   r   )rH   rI   r   rI   r   r   rC   r]   )__name__
__module____qualname__r=   r   rB   rG   rO   r[   rU   rk   rp   staticmethodrw   ry   r   r   r   r   r   r;   r;   r;   r<   r%   L   s(    

	


O


	.
;r%   )__doc__
__future__r   r   r   loggingr   
contextlibr   typingr   r   r   r   r   r>   pymysql.cursorsr	   	getLoggerr   loggerr$   __annotations__r%   r;   r;   r;   r<   <module>   s~    
;