o
    i!C                     @   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 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_tokenz'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|st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   8   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-   r3   r9   payloadrespr1   r   r   r   r&   g   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/r5   r#   r"   r9      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>   getr@   r   r   rA   r:   r   )r1   r   r3   r9   _rC   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/r5   rD   rE   r3   compressionAlgorithmzFailed to get document URL: )NN)r=   r>   rL   r@   r:   r   r   rA   )r2   r   r3   r9   rC   rN   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
   rU   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rtd|	 d| d| d | d!| d"| d# d$}n	td%|  qI|	r|	 }	|
r|
 }
|	sqIz
|rt|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)ra   titler`   r]   is_fbarO   sourcepurchase_date)csv
DictReaderioStringIO
fieldnamesr   r   r   r   r$   rL   r%   rU   rX   rQ   r   utcnowappend)text_contentr-   readerr9   r   items_to_insert	row_countMAX_DEBUG_ROWSrowr`   ra   rd   qtyfulfillment_channelre   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 pod}|d pud}|}|}|tkr|d }t||d }n|tkr|d s|d }t||d }n	 |
d|||f qU|d r|
d|d |d |d |d |d |d |d d |d f	 qUt d|d  d| d qU|	  W d    d S 1 sw   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 = ?r`   r      r]   re   z
                    UPDATE inventory_ledger
                    SET quantity_remaining = ?, quantity_purchased = ?
                    WHERE id = ?
                ra   z
                        INSERT INTO inventory_ledger (asin, title, sku, quantity_purchased, quantity_remaining, status, source, buy_cost, purchase_date)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                    rd   rO   rf   rg   z!Skipping new item insert for SKU z from report z due to missing ASIN.)r   r   r>   rL   raise_for_statuscontentgzip
decompressdecoder|   lensqlite3connectr   cursorexecutefetchoner%   maxr$   r   commit)r3   r4   r
   r-   rC   r   r   ro   rq   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   s0  zt | 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 ]}
zXtjtddG}| }|D ].}d}|d g}|d r|d7 }||d  |d7 }||d  ||| ||j7 }qo|  |W  d   W   W S 1 sw   Y  W q^ tjy } z6dt|v r|
|d k rt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$ ,)r`   buy_costrg   r   r    g      ?r#   )timeoutz(UPDATE inventory_ledger SET buy_cost = ?r   rg   z, purchase_date = ?z WHERE sku = ?r`   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   rh   ri   rj   rk   rL   floatr   rR   rn   rK   r   r   r   r   r   rowcountr   OperationalErrorstrr   r   r'   r(   r   r   )csv_contentrp   updatesrt   r`   r   rg   updated_countmax_retriesretry_delayattemptr   r   updatesqlparamsr   r   r   r   process_bulk_cost_uploadU  sz   






,"


r   c               
   C   s   zbt  } 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 sYw   Y  |  W S  tyz } 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   )rj   rk   rh   writerwriterowr   r   r   r   r   fetchallsplitgetvaluer   r   r   )outputr   r   r   rowsrt   existing_dater   r   r   r   export_missing_costs_csv  s0   

"
r   ) osr'   r>   rh   rj   loggingr   r   workerr   keepa_deals.db_utilsr   r   keepa_deals.amazon_sp_apir   	getLogger__name__r   r=   r$   r%   taskr   r   r&   r)   r*   rU   r|   r+   r   r   r   r   r   r   <module>   s4   


 /bLS