o
    *j                      @   s   d Z ddlZddlZddlmZmZ dddddd	Zd
d Z	de
fddZdZdd ZdefddZde
defddZdefddZde
defddZdefd d!Zde
defd"d#Zed$krde  dS dS )%uF   
MySQL 저장 모듈
- 업체 정보, 리뷰, 순위 데이터 저장
    N)datetimedate	localhost
place_userzPlacePass1!naver_place_monitorutf8mb4)hostuserpassworddatabasecharsetc                   C   s   t jjdi tS )N )mysql	connectorconnect	DB_CONFIGr   r   r    /var/www/html/bicorn/place/db.pyget_conn   s   r   date_strc                 C   s   | sdS t dt| }|sdS t|dt|d}}t }|j}||jks5||jkr9||j	kr9|d8 }zt|||W S  t
yJ   Y dS w )uV   '6.10.수' 형태의 네이버 날짜 → date 객체 (연도는 오늘 기준 추정)Nz(\d+)\.(\d+)      )_rematchstrintgroup_datetodayyearmonthday	Exception)r   mr   r    r   r   r   r   r   parse_naver_date   s   r#   u	  
-- 고객사 업체 목록
CREATE TABLE IF NOT EXISTS places (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    place_id    VARCHAR(20) NOT NULL UNIQUE COMMENT '네이버 플레이스 ID',
    client_name VARCHAR(100) COMMENT '고객사명',
    name        VARCHAR(200) COMMENT '업체명',
    category    VARCHAR(100),
    address     VARCHAR(300),
    phone       VARCHAR(30),
    keywords    TEXT COMMENT '모니터링 키워드 (쉼표 구분)',
    is_active   TINYINT DEFAULT 1,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 일별 통계 스냅샷
CREATE TABLE IF NOT EXISTS place_stats (
    id                   INT AUTO_INCREMENT PRIMARY KEY,
    place_id             VARCHAR(20) NOT NULL,
    rating               FLOAT,
    visitor_review_count INT DEFAULT 0,
    blog_review_count    INT DEFAULT 0,
    collected_at         DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_place_id (place_id),
    INDEX idx_collected_at (collected_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 리뷰 저장
CREATE TABLE IF NOT EXISTS place_reviews (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    place_id            VARCHAR(20) NOT NULL,
    review_id           VARCHAR(50) UNIQUE,
    author              VARCHAR(100),
    rating              FLOAT,
    content             TEXT,
    visit_count         INT DEFAULT 0,
    review_date         VARCHAR(30),
    review_date_parsed  DATE NULL,
    collected_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_place_id (place_id),
    INDEX idx_review_date_parsed (review_date_parsed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 검색 순위 기록
CREATE TABLE IF NOT EXISTS place_ranks (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    place_id     VARCHAR(20) NOT NULL,
    keyword      VARCHAR(200) NOT NULL,
    my_rank      INT COMMENT 'NULL이면 순위권 밖',
    total_found  INT,
    searched_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_place_id (place_id),
    INDEX idx_keyword (keyword),
    INDEX idx_searched_at (searched_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 리뷰 키워드 분석
CREATE TABLE IF NOT EXISTS review_keywords (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    place_id     VARCHAR(20) NOT NULL,
    keyword      VARCHAR(100) NOT NULL,
    count        INT DEFAULT 0,
    analyzed_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_place_id (place_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
c                  C   s\   t  } |  }t dD ]}| }|r|| q|   |  |   td dS )u   DB 및 테이블 초기화;u   ✅ DB 초기화 완료N)	r   cursorCREATE_TABLES_SQLstripsplitexecutecommitcloseprint)connr%   sqlr   r   r   init_dbt   s   
r/   summaryc              
   C   sn   | sdS t  }| }d}||| d | dd| dd| dd| df |  |  |  dS )	u   업체 통계 저장Nz
        INSERT INTO place_stats
            (place_id, rating, visitor_review_count, blog_review_count, collected_at)
        VALUES (%s, %s, %s, %s, %s)
    place_idratingr   visitor_review_countblog_review_countcollected_atr   r%   r)   getr*   r+   )r0   r-   r%   r.   r   r   r   
save_stats   s   


r8   r1   reviewsc                    sZ   |sdS t  }| }d} fdd|D }||| |  |j}|  |  |S )u   리뷰 저장 (중복 무시)Nz
        INSERT IGNORE INTO place_reviews
            (place_id, review_id, author, rating, content, visit_count, review_date, review_date_parsed)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    c                    sN   g | ]#} | d | d| d| d| d| dt| dfqS )	review_idauthorr2   contentvisit_count
created_at)r7   r#   ).0rr1   r   r   
<listcomp>   s    z save_reviews.<locals>.<listcomp>)r   r%   executemanyr*   rowcountr+   )r1   r9   r-   r%   r.   rows	new_countr   rA   r   save_reviews   s   
rG   	rank_datac              
   C   s   | sdS t  }| }d}tdd | dg D i }|dd}|||| d | d	| d
| df |  |  |  dS )u   순위 저장Nz
        INSERT INTO place_ranks (place_id, keyword, my_rank, total_found, searched_at)
        VALUES (%s, %s, %s, %s, %s)
    c                 s   s    | ]	}|d  r|V  qdS )is_my_placeNr   )r?   cr   r   r   	<genexpr>   s    zsave_rank.<locals>.<genexpr>competitorsr1    keywordmy_ranktotal_foundsearched_at)r   r%   nextr7   r)   r*   r+   )rH   r-   r%   r.   my_placemy_place_idr   r   r   	save_rank   s&   rU   keywordsc                    sb   |sdS t  }| }|d f d} fdd|D }||| |  |  |  dS )u   키워드 분석 결과 저장NzMDELETE FROM review_keywords WHERE place_id=%s AND DATE(analyzed_at)=CURDATE()zJINSERT INTO review_keywords (place_id, keyword, count) VALUES (%s, %s, %s)c                    s   g | ]} |d  |d fqS )rN   countr   )r?   kwrA   r   r   rB      s    z!save_keywords.<locals>.<listcomp>)r   r%   r)   rC   r*   r+   )r1   rV   r-   r%   r.   rE   r   rA   r   save_keywords   s   rY   returnc                  C   s8   t  } | jdd}|d | }|  |   |S )u   활성 업체 목록 조회T)
dictionaryz&SELECT * FROM places WHERE is_active=1)r   r%   r)   fetchallr+   )r-   r%   rE   r   r   r   get_active_places   s   
r]   c              
   C   s`   t  }| }|d|dd|dd|dd|dd| f |  |  |  dS )u:   크롤링한 기본정보로 places 테이블 업데이트zNUPDATE places SET name=%s, category=%s, address=%s, phone=%s WHERE place_id=%snamerM   categoryaddressphoneNr6   )r1   r0   r-   r%   r   r   r   update_place_info   s   




rb   __main__)__doc__rer   mysql.connectorr   r   r   r   r   r   r   r#   r&   r/   dictr8   listrG   rU   rY   r]   rb   __name__r   r   r   r   <module>   s.    	D 
