U
    FiÙ  ã                   @   sè   d 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	 ddl
m
Z
 ddlZeeƒjjjd d Zdd	„ Zd
d„ Zeeef edœdd„Zeeeeef  dœdd„Zdeee	eeef  dœdd„Zeedœdd„Zedœdd„ZdS )z$Database utilities for shop storage.é    N)ÚPath)ÚOptionalÚDictÚAnyÚList)ÚdatetimeÚdatazshops.dbc                  C   s^   t jjddd t t ¡} |  ¡ }| d¡ | d¡ | d¡ | d¡ |  ¡  |  ¡  dS )z:Initialize database and create tables if they don't exist.T)ÚparentsÚexist_oka´  
        CREATE TABLE IF NOT EXISTS shops (
            shop_id TEXT PRIMARY KEY,
            name TEXT,
            url TEXT,
            description TEXT,
            logo TEXT,
            contact TEXT,  -- JSON string
            social TEXT,   -- JSON string
            platform TEXT, -- JSON string
            instagram_profile TEXT, -- JSON string
            facebook TEXT, -- JSON string
            website_info TEXT, -- JSON string
            additional_sources TEXT, -- JSON string
            raw_data TEXT, -- JSON string (full crawled data)
            crawled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    z>
        CREATE INDEX IF NOT EXISTS idx_url ON shops(url)
    zL
        CREATE INDEX IF NOT EXISTS idx_crawled_at ON shops(crawled_at)
    z@
        CREATE INDEX IF NOT EXISTS idx_name ON shops(name)
    N)	ÚDB_PATHÚparentÚmkdirÚsqlite3ÚconnectÚcursorÚexecuteÚcommitÚclose)Úconnr   © r   ú /apps/ss-crawler/api/shops/db.pyÚinit_db   s    




r   c                   C   s   t ƒ  t t¡S )zGet database connection.)r   r   r   r   r   r   r   r   Úget_db_connection8   s    r   )Ú	shop_dataÚreturnc                 C   s¤  t ƒ }| ¡ }|  d¡}|sn|  dd¡}|rLddl}d| | ¡ ¡ ¡  }n"|  dd¡}d| | ¡ ¡ ¡  }|  dd¡}|  dd¡}|  d	d¡}|  d
d¡}t |  di ¡¡}	t |  di ¡¡}
t |  di ¡¡}t |  di ¡¡}t |  di ¡¡}t |  di ¡¡}t |  di ¡¡}t | ¡}| 	d|f¡ | 
¡ }|rj| 	d|||||	|
|||||||f¡ n&| 	d||||||	|
||||||f¡ | ¡  | ¡  |S )z'Save or update a shop. Returns shop_id.Úshop_idÚurlÚ r   NZshop_ÚnameÚunknownÚdescriptionÚlogoÚcontactÚsocialÚplatformÚinstagram_profileÚfacebookÚwebsite_infoÚadditional_sourcesz+SELECT shop_id FROM shops WHERE shop_id = ?aå  
            UPDATE shops SET
                name = ?,
                url = ?,
                description = ?,
                logo = ?,
                contact = ?,
                social = ?,
                platform = ?,
                instagram_profile = ?,
                facebook = ?,
                website_info = ?,
                additional_sources = ?,
                raw_data = ?,
                updated_at = CURRENT_TIMESTAMP
            WHERE shop_id = ?
        a   
            INSERT INTO shops (
                shop_id, name, url, description, logo,
                contact, social, platform, instagram_profile,
                facebook, website_info, additional_sources, raw_data
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        )r   r   ÚgetÚhashlibZmd5ÚencodeZ	hexdigestÚjsonÚdumpsr   Úfetchoner   r   )r   r   r   r   r   r*   r   r    r!   r"   r#   r$   r%   r&   r'   r(   Úraw_dataÚexistsr   r   r   Ú	save_shop>   sx    

         üð          ýúr1   )r   r   c                 C   s†   t ƒ }tj|_| ¡ }| d| f¡ | ¡ }| ¡  |s<dS t|ƒ}dD ]8}|| rHzt	 
|| ¡||< W qH   i ||< Y qHX qH|S )zGet shop by ID.z%SELECT * FROM shops WHERE shop_id = ?N©r"   r#   r$   r%   r&   r'   r(   r/   )r   r   ÚRowÚrow_factoryr   r   r.   r   Údictr,   Úloads)r   r   r   ÚrowÚshopÚfieldr   r   r   Úget_shopŽ   s     r:   éd   )ÚlimitÚoffsetr   c           	      C   s˜   t ƒ }tj|_| ¡ }| d| |f¡ | ¡ }| ¡  g }|D ]T}t|ƒ}dD ]8}|| rNzt	 
|| ¡||< W qN   i ||< Y qNX qN| |¡ q>|S )z0List all shops, sorted by crawled_at descending.z[
        SELECT * FROM shops
        ORDER BY crawled_at DESC
        LIMIT ? OFFSET ?
    r2   )r   r   r3   r4   r   r   Zfetchallr   r5   r,   r6   Úappend)	r<   r=   r   r   ZrowsÚshopsr7   r8   r9   r   r   r   Ú
list_shops§   s&    ür@   c                 C   s:   t ƒ }| ¡ }| d| f¡ |jdk}| ¡  | ¡  |S )z?Delete shop by ID. Returns True if deleted, False if not found.z#DELETE FROM shops WHERE shop_id = ?r   )r   r   r   Zrowcountr   r   )r   r   r   Zdeletedr   r   r   Údelete_shopÅ   s    
rA   )r   c                  C   s0   t ƒ } |  ¡ }| d¡ | ¡ d }|  ¡  |S )zGet total count of shops.zSELECT COUNT(*) FROM shopsr   )r   r   r   r.   r   )r   r   Úcountr   r   r   Úcount_shopsÓ   s    
rC   )r;   r   )Ú__doc__r   ZuuidÚpathlibr   Útypingr   r   r   r   r   r,   Ú__file__r   r   r   r   Ústrr1   r:   Úintr@   ÚboolrA   rC   r   r   r   r   Ú<module>   s   +P 