
    <|j                    T    d Z ddlmZ ddlZddlZddlZddlmZmZ dZ G d d      Z	y)u?   趋势数据库 - SQLite，存储每日价格用于趋势汇报    )annotationsN)datetime	timedeltaa  
CREATE TABLE IF NOT EXISTS daily_prices (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    trade_date TEXT NOT NULL,
    board_id INTEGER NOT NULL,
    board_type TEXT,
    topic_id INTEGER NOT NULL,
    topic_title TEXT,
    topic_url TEXT,
    author_name TEXT,
    name TEXT NOT NULL,
    ref_price TEXT,
    price_details TEXT,
    unit TEXT,
    quantity TEXT,
    quantity_unit TEXT,
    package_req TEXT,
    quality_req TEXT,
    other_notes TEXT,
    raw_text TEXT,
    fetched_at TEXT NOT NULL,
    UNIQUE(trade_date, board_id, topic_id, name, raw_text)
);
CREATE INDEX IF NOT EXISTS idx_dp_date_name ON daily_prices(trade_date, name);
CREATE INDEX IF NOT EXISTS idx_dp_name ON daily_prices(name);

CREATE TABLE IF NOT EXISTS crawl_runs (
    trade_date TEXT NOT NULL,
    board_id INTEGER NOT NULL,
    status TEXT,
    topic_count INTEGER DEFAULT 0,
    matched_count INTEGER DEFAULT 0,
    run_at TEXT NOT NULL,
    PRIMARY KEY(trade_date, board_id)
);

CREATE TABLE IF NOT EXISTS raw_posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    trade_date TEXT NOT NULL,
    board_id INTEGER NOT NULL,
    topic_id INTEGER NOT NULL,
    title TEXT,
    url TEXT,
    author_name TEXT,
    post_text TEXT,
    fetched_at TEXT NOT NULL,
    UNIQUE(trade_date, board_id, topic_id)
);
c                  t    e Zd ZddZddZddZddZddZ	 d	 	 	 	 	 ddZddZ	dddZ
dd	Zdd
ZddZy)Databasec                   t        j                  t         j                  j                  |      d       t	        j
                  |      | _        t        j                  | j                  _        | j                  j                  d       y )NT)exist_okzPRAGMA journal_mode=WAL)
osmakedirspathdirnamesqlite3connectconnRowrow_factoryexecute)selfdb_paths     /opt/daily-market/src/db.py__init__zDatabase.__init__@   sP    
BGGOOG,t<OOG,	 '				34    c                v    | j                   j                  t               | j                   j                          y N)r   executescript
SCHEMA_SQLcommitr   s    r   init_schemazDatabase.init_schemaF   s$    		
+		r   c                v    | j                   j                  d||f      j                         }|d uxr |d   dk(  S )Nz?SELECT status FROM crawl_runs WHERE trade_date=? AND board_id=?statusdone)r   r   fetchone)r   
trade_dateboard_idrows       r   is_board_crawledzDatabase.is_board_crawledJ   sG    iiM"
 (* 	 $:3x=F#::r   c                   | j                   j                  d|d   |d   |j                  d      |d   |j                  d      |j                  d      |j                  d      |d	   |j                  d
      t        j                  |j                  d      xs i d      |j                  d      |j                  d      |j                  d      |j                  d      |j                  d      |j                  d      |j                  d      |j                  dt        j                         j                               f       | j                   j                          y )Na[  INSERT OR IGNORE INTO daily_prices
               (trade_date, board_id, board_type, topic_id, topic_title, topic_url,
                author_name, name, ref_price, price_details, unit, quantity,
                quantity_unit, package_req, quality_req, other_notes, raw_text, fetched_at)
               VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)r$   r%   
board_typetopic_idtopic_title	topic_urlauthor_namename	ref_priceprice_detailsF)ensure_asciiunitquantityquantity_unitpackage_reqquality_reqother_notesraw_text
fetched_at)	r   r   getjsondumpsr   now	isoformatr   r   recs     r   save_price_recordzDatabase.save_price_recordQ   s   		? L!3z?CGGL4IJ!79M&FSWW[5I

377?39rN!4cggo6N&(>&
(;hlln&>&>&@A		
" 			r   c                j   | j                   j                  d|d   |d   |d   |j                  d      |j                  d      |j                  d      |j                  d      |j                  d	t        j                         j                               f       | j                   j                          y )
NzINSERT OR IGNORE INTO raw_posts
               (trade_date, board_id, topic_id, title, url, author_name, post_text, fetched_at)
               VALUES (?,?,?,?,?,?,?,?)r$   r%   r*   titleurlr-   	post_textr9   )r   r   r:   r   r=   r>   r   r?   s     r   save_raw_postzDatabase.save_raw_poste   s    		+ L!3z?C
O #''%.#''-2H$hlln&>&>&@A		
	
 			r   c           
         | j                   j                  d|||||t        j                         j	                         f       | j                   j                          y )NzINSERT OR REPLACE INTO crawl_runs
               (trade_date, board_id, status, topic_count, matched_count, run_at)
               VALUES (?,?,?,?,?,?))r   r   r   r=   r>   r   )r   r$   r%   r!   topic_countmatched_counts         r   record_crawl_runzDatabase.record_crawl_runs   sO    		' 6;\\^%%')		
 			r   c                X    | j                   j                  d|f      j                         S )NzESELECT * FROM daily_prices WHERE trade_date=? ORDER BY board_id, namer   r   fetchall)r   r$   s     r   fetch_prices_by_datezDatabase.fetch_prices_by_date~   s*    yy  SM
 (*	r   c                    t        j                         t        |      z
  j                  d      }| j                  j                  d||f      j                         S )N)daysz%Y-%m-%dzoSELECT * FROM daily_prices
               WHERE name=? AND trade_date>=?
               ORDER BY trade_date ASC)r   r=   r   strftimer   r   rM   )r   r.   rP   cutoffs       r   fetch_coin_historyzDatabase.fetch_coin_history   sP    ,,.9$#77AA*Myy  * 6N	

 (*	r   c                    | j                   j                  d      j                         }|D cg c]  }|d   s	|d    c}S c c}w )NzESELECT DISTINCT name FROM daily_prices WHERE name != '' ORDER BY namer.   rL   r   rowsrs      r   fetch_all_coin_nameszDatabase.fetch_all_coin_names   sC    yy  S

(* 	 $(5a1V9&	555s
   
AAc                    | j                   j                  d      j                         }|D cg c]  }|d   	 c}S c c}w )NzESELECT DISTINCT trade_date FROM daily_prices ORDER BY trade_date DESCr$   rL   rU   s      r   list_trade_dateszDatabase.list_trade_dates   s=    yy  S

(* 	 *..A,...s   =c                l    | j                   j                          | j                   j                          y r   )r   r   closer   s    r   r\   zDatabase.close   s     				r   N)r   strreturnNone)r^   r_   )r$   r]   r%   intr^   bool)r@   dictr^   r_   )r   r   )r$   r]   r%   r`   r!   r]   rH   r`   rI   r`   r^   r_   )r$   r]   r^   list[sqlite3.Row])   )r.   r]   rP   r`   r^   rc   )r^   z	list[str])__name__
__module____qualname__r   r   r'   rA   rF   rJ   rN   rS   rX   rZ   r\    r   r   r   r   ?   sX    5;( EF	&)	>A	JN	6/r   r   )
__doc__
__future__r   r;   r
   r   r   r   r   r   rh   r   r   <module>rk      s+    E "  	  (0
f\ \r   