o
    ÑBjZC  ã                   @   sî   d dl Z d dlZd dl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	 d dl
mZmZ d dlmZ d dl
mZ e e¡ZdZdZd	Ze	jd
d�dd„ ƒZdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Z dS ) é    N)Údatetime)Ú
celery_app)ÚDB_PATHÚget_all_user_credentials)Úrefresh_sp_api_token)Úget_db_connectionz'https://sellingpartnerapi-na.amazon.comÚGET_MERCHANT_LISTINGS_ALL_DATAÚGET_FBA_MYI_ALL_INVENTORY_DATAz:keepa_deals.inventory_import.fetch_existing_inventory_task)Únamec                  C   s¼   t  d¡ tƒ } | st  d¡ dS | D ]H}|d }|d }t  d|› �¡ t|ƒ}|s5t  d|› d�¡ qzt||ƒ W q ty[ } zt jd	|› d
|› �dd� W Y d}~qd}~ww dS )zÈ
    Fetches existing inventory from Amazon via SP-API Reports API.
    Report Types:
    1. GET_MERCHANT_LISTINGS_ALL_DATA (Active Listings)
    2. GET_FBA_MYI_ALL_INVENTORY_DATA (FBA Inventory)
    zStarting inventory import task.z.No connected users found for inventory import.z	No users.Úuser_idÚrefresh_tokenz&Processing inventory import for user: z!Failed to refresh token for user z. Skipping.z!Error syncing inventory for user ú: T©Úexc_infoNzInventory import completed.)ÚloggerÚinfor   Úwarningr   ÚerrorÚ_sync_user_inventoryÚ	Exception)ÚusersÚuserr   r   Úaccess_tokenÚe© r   ú7/var/www/agentarbitrage/keepa_deals/inventory_import.pyÚfetch_existing_inventory_task   s(   

&€ÿr   c                 C   sB  t tg}|D ]˜}d}d}d}|s�||k r�|d7 }zJt d|› d|› d|› d�¡ t||ƒ}|s6t d	¡ W qt||ƒ}|sMt d
|› d�¡ t d¡ W qt	||ƒ\}	}
|	sXW qt
|	|
| |ƒ d}W n' ty‰ } ztjd|› d| › d|› �dd� t d¡ W Y d}~nd}~ww |s�||k s|sžt d|› d|› d�¡ qdS )z©
    Orchestrates the report request, download, and parsing for both Merchant and FBA reports.
    Includes retry logic for intermittent report generation failures.
    Fr   é   é   zRequesting report: z
 (Attempt ú/ú)é   úReport z failed/cancelled. Retrying...é   TzError processing report z
 for user r   r   é
   NzFailed to process report z after z
 attempts.)ÚREPORT_TYPE_MERCHANTÚREPORT_TYPE_FBAr   r   Ú_request_reportÚtimeÚsleepÚ_poll_report_statusr   Ú_get_report_document_urlÚ_download_and_process_reportr   r   )r   r   Úreport_typesÚreport_typeÚsuccessÚattemptsÚmax_attemptsÚ	report_idÚdocument_idÚurlÚcompressionr   r   r   r   r   9   sB   



 €þå €Úr   c                 C   sv   t › d�}| ddœ}|dgdœ}tj|||d�}|jdkr-| ¡ d }t d	|› �¡ |S t d
|› d|j› �¡ d S )Nz/reports/2021-06-30/reportszapplication/json)úx-amz-access-tokenzContent-TypeÚATVPDKIKX0DER)Ú
reportTypeÚmarketplaceIds)ÚheadersÚjsonéÊ   ÚreportIdzReport requested. ID: zFailed to request report r   )	ÚSP_API_BASE_URLÚrequestsÚpostÚstatus_coder;   r   r   r   Útext)r   r.   r4   r:   ÚpayloadÚrespr2   r   r   r   r'   h   s   
þþ
r'   c                 C   sÄ   t › d| › �}d|i}tdƒD ]K}t d¡ tj||d�}|jdkr.t d|j	› �¡  d S | 
¡ }|d }t d	| › d
|› �¡ |dkrK|d   S |dv rZt d|› �¡  d S qt d¡ d S )Nz/reports/2021-06-30/reports/r6   r$   r#   ©r:   éÈ   zFailed to check report status: ÚprocessingStatusr"   z	 status: ÚDONEÚreportDocumentId)Ú	CANCELLEDÚFATALz&Report processing failed with status: zReport processing timed out.)r>   Úranger(   r)   r?   ÚgetrA   r   r   rB   r;   r   )r2   r   r4   r:   Ú_rD   ÚdataÚstatusr   r   r   r*   |   s&   

þ
r*   c                 C   s^   t › d| › �}d|i}tj||d�}|jdkr$| ¡ }|d | d¡fS t d|j› �¡ dS )	Nz/reports/2021-06-30/documents/r6   rE   rF   r4   ÚcompressionAlgorithmzFailed to get document URL: )NN)r>   r?   rM   rA   r;   r   r   rB   )r3   r   r4   r:   rD   rO   r   r   r   r+   •   s   
r+   c              	   C   s.   z
| rt | ƒW S dW S  ttfy   Y dS w )z>Helper to robustly convert values to integer, defaulting to 0.r   )ÚintÚ
ValueErrorÚ	TypeError)Úvalr   r   r   Úsafe_int¡   s
   ÿrV   c                 C   sr  t jt | ¡dd�}|jrdd„ |jD ƒ|_z|j}t d|› d|› �¡ W n ty@ } zt d|› �¡ W Y d}~nd}~ww g }d	}d
}|D ]í}|d7 }||krat d|› d|› d|› �¡ |t	kr„| 
d¡}	| 
d¡}
| 
d¡}| 
d¡}| 
dd¡}|dk}nc|tkrÞ| 
d¡}	| 
d¡}
| 
d¡}t| 
d¡ƒ}t| 
d¡ƒ}t| 
d¡ƒ}t| 
d¡ƒ}|| | | }|d	krÛ||krÛt d|	› d|› d|› d |› d!|› d"|› d#�¡ d$}n	t d%|› �¡ qI|	rí|	 ¡ }	|
ró|
 ¡ }
|	söqIz
|rýt|ƒnd	}W n   d	}Y |�r$|d	k�r$||k�r$|tk�r$t d&|	› d'|› �¡ |
||	||d(d)t ¡ d*œ}| |¡ qI|S )+z_
    Parses the text content of an inventory report and returns a list of items to insert.
    ú	)Ú	delimiterc                 S   s   g | ]}|  ¡ ‘qS r   )Ústrip)Ú.0Úxr   r   r   Ú
<listcomp>°   s    z2parse_inventory_report_content.<locals>.<listcomp>zCSV Headers for r   zCould not log CSV headers: Nr   r!   r   zProcessing Row z (z): z
seller-skuÚasin1z	item-nameÚquantityzfulfillment-channelÚDEFAULTÚ	AMAZON_NAÚskuÚasinzproduct-namezafn-fulfillable-quantityzafn-inbound-working-quantityzafn-inbound-shipped-quantityzafn-inbound-receiving-quantityzFBA Qty Breakdown for z: Total=z (Fulfillable=z
, Working=z
, Shipped=z, Receiving=r    TzUnknown report type: zFound FBA Stock > 0: SKU=z, Qty=Ú	PURCHASEDÚImported)rb   Útitlera   r^   Úis_fbarP   ÚsourceÚpurchase_date)ÚcsvÚ
DictReaderÚioÚStringIOÚ
fieldnamesr   r   r   r   r%   rM   r&   rV   rY   rR   r   ÚutcnowÚappend)Útext_contentr.   Úreaderr:   r   Úitems_to_insertÚ	row_countÚMAX_DEBUG_ROWSÚrowra   rb   re   ÚqtyÚfulfillment_channelrf   ÚfulfillableÚinbound_workingÚinbound_shippedÚinbound_receivingÚitemr   r   r   Úparse_inventory_report_content¨   sv   €ÿ







0$ø
r}   c                 C   sâ  t  d|› d�¡ t | ¡}| ¡  |j}|dkr"dd l}| |¡}|jddd�}t	||ƒ}|s;t  d|› d	�¡ d S t  d
t
|ƒ› d|› d	�¡ ttƒ�š}	|	 ¡ }
|D ]†}|
 d|d f¡ |
 ¡ }|r¨|d }|d pnd}|d ptd}|}|}|tkr‰|d }t||d ƒ}n|tkrž|d s�|d }t||d ƒ}n	 |
 d|||f¡ qT|d rÌ|
 d|d |d |d |d |d |d |d d |d f	¡ qTt  d|d › d|› d�¡ qT|	 ¡  W d   ƒ d S 1 sêw   Y  d S )NzDownloading report z...ÚGZIPr   ú	utf-8-sigÚreplace)ÚerrorszNo items found in report Ú.zProcessing z items from report zUSELECT id, quantity_remaining, quantity_purchased FROM inventory_ledger WHERE sku = ?ra   r   é   r^   rf   z¥
                    UPDATE inventory_ledger
                    SET quantity_remaining = ?, quantity_purchased = ?
                    WHERE id = ?
                rb   zé
                        INSERT INTO inventory_ledger (asin, title, sku, quantity_purchased, quantity_remaining, status, source, buy_cost, purchase_date)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                    re   rP   rg   rh   z!Skipping new item insert for SKU z from report z due to missing ASIN.)r   r   r?   rM   Úraise_for_statusÚcontentÚgzipÚ
decompressÚdecoder}   Úlenr   r   ÚcursorÚexecuteÚfetchoner&   Úmaxr%   r   Úcommit)r4   r5   r   r.   rD   r…   r†   rp   rr   ÚconnrŠ   r|   ÚexistingÚexisting_idÚexisting_qty_remainingÚexisting_qty_purchasedÚnew_qty_remainingÚnew_qty_purchasedr   r   r   r,   
  sT   



ü4ý
"Ír,   c                 C   s.  zýt | tƒr|  d¡} t t | ¡¡}g }|D ]7}| d¡}| d¡}| d¡}|r,|s-qzt| 	dd¡ 	dd¡ƒ}W n	 t
yD   Y qw | |||dœ¡ q|sTW d	S d	}d
}d}	t|ƒD ]œ}
zWttdd��G}| ¡ }|D ].}d}|d g}|d r†|d7 }| |d ¡ |d7 }| |d ¡ | ||¡ ||j7 }qn| ¡  |W  d  ƒ W   W S 1 s±w   Y  W q^ tjyú } z6dt|ƒv rî|
|d k rçt d|	› d|
d › d|› d�¡ t |	¡ |	d9 }	n	t d¡ |‚|‚W Y d}~q^d}~ww |W S  t�y } ztjd|› �dd� |‚d}~ww ) z™
    Parses a CSV upload to update buy costs.
    Expected columns: SKU, Buy Cost, Purchase Date
    Includes retry logic for SQLite database locks.
    r   ÚSKUúBuy CostúPurchase Dateú$Ú ú,)ra   Úbuy_costrh   r   r!   g      ð?r$   )Útimeoutz(UPDATE inventory_ledger SET buy_cost = ?rœ   rh   z, purchase_date = ?z WHERE sku = ?ra   NÚlockedr   z0Database locked during cost upload. Retrying in zs... (Attempt r   r    rƒ   z9Max retries reached for database lock during cost upload.z#Error processing bulk cost upload: Tr   )Ú
isinstanceÚbytesrˆ   ri   rj   rk   rl   rM   Úfloatr€   rS   ro   rL   r   r   rŠ   r‹   ÚrowcountrŽ   Úsqlite3ÚOperationalErrorÚstrr   r   r(   r)   r   r   )Úcsv_contentrq   Úupdatesru   ra   rœ   rh   Úupdated_countÚmax_retriesÚretry_delayÚattemptr�   rŠ   ÚupdateÚsqlÚparamsr   r   r   r   Úprocess_bulk_cost_uploadV  sz   




ÿ
ý
,î"


û€û€þr¯   c               
   C   sô   zat  ¡ } t | ¡}| g d¢¡ ttƒ�@}| ¡ }| d¡ | 	¡ }|D ](}d}|d r=z|d  
d¡d }W n   Y | |d |d |d d|g¡ q%W d	  ƒ n1 sXw   Y  |  ¡ W S  tyy } ztjd
|› �dd� |‚d	}~ww )zy
    Generates a CSV string for items with missing buy costs.
    Columns: SKU, Title, ASIN, Buy Cost, Purchase Date
    )r–   ÚTitleÚASINr—   r˜   zÀ
                SELECT sku, title, asin, purchase_date
                FROM inventory_ledger
                WHERE buy_cost IS NULL OR buy_cost = 0
                ORDER BY title
            rš   r   Ú r   r   rƒ   Nz$Error generating missing costs CSV: Tr   )rk   rl   ri   ÚwriterÚwriterowr   r   rŠ   r‹   ÚfetchallÚsplitÚgetvaluer   r   r   )Úoutputr³   r�   rŠ   Úrowsru   Úexisting_dater   r   r   r   Úexport_missing_costs_csv©  s0   


"õõ
€þr»   )!Úosr(   r?   ri   rk   Úloggingr£   r   Úworkerr   Úkeepa_deals.db_utilsr   r   Úkeepa_deals.amazon_sp_apir   r   Ú	getLoggerÚ__name__r   r>   r%   r&   Útaskr   r   r'   r*   r+   rV   r}   r,   r¯   r»   r   r   r   r   Ú<module>   s6   


 /bLS