3i-/dZddlZddlZddlmZddlmZmZmZmZddl m Z ddl Z eje ZGddZy)zz Database Manager for Portfolio Manager. Handles SQLite database operations for holdings, transactions, and price cache. N)datetime)ListDictOptionalTuple)contextmanagerceZdZdZd"defdZd#dZedZd#dZ d ed ed ed e d e ddf dZ d eddfdZ d eddfdZ deefdZd edeefdZ d$d ededede d ee dee deddfdZ d%d eedeedeefdZdeddfdZd edeee effdZd ede ddfdZdeefdZd&deedefd Zdeeeffd!Zy)' DBManagerz5Manages SQLite database operations for the portfolio.db_pathcR||_|j|jy)zo Initialize database manager. Args: db_path: Path to SQLite database file N)r _ensure_data_directoryinitialize_database)selfr s A/mnt/ssd/data/python-lab/portfolio-manager/src/data/db_manager.py__init__zDBManager.__init__s#  ##%   "returnNctjj|j}|rNtjj |s.tj |t jd|yyy)zEnsure data directory exists.zCreated data directory: N)ospathdirnamer existsmakedirsloggerinfo)rdb_dirs rr z DBManager._ensure_data_directorysQ. "''..0 KK  KK26(; <16rc#bKtj|j}tj|_ ||j  |jy#t $r0}|jtjd|dd}~wwxYw#|jwxYww)z Context manager for database connections. Yields: sqlite3.Connection: Database connection zDatabase error: T)exc_infoN) sqlite3connectr Row row_factorycommit Exceptionrollbackrerrorclose)rconnes rget_connectionzDBManager.get_connection&st||,";; J KKM JJL   MMO LL+A3/$L ?   JJLs45B/A B/ B'+BBBB,,B/c|j5}|j}|jd|jd|jdtj d|j dddy#1swYyxYw)z+Create database tables if they don't exist.a CREATE TABLE IF NOT EXISTS holdings ( id INTEGER PRIMARY KEY AUTOINCREMENT, ticker TEXT NOT NULL UNIQUE, name TEXT, asset_type TEXT, quantity REAL NOT NULL, avg_price REAL NOT NULL, current_price REAL, current_value REAL, weight_pct REAL, last_updated TIMESTAMP ) a CREATE TABLE IF NOT EXISTS transactions ( id INTEGER PRIMARY KEY AUTOINCREMENT, ticker TEXT NOT NULL, transaction_type TEXT NOT NULL, date DATE NOT NULL, quantity REAL, price REAL, amount REAL NOT NULL, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) z CREATE TABLE IF NOT EXISTS price_cache ( ticker TEXT PRIMARY KEY, price REAL NOT NULL, last_updated TIMESTAMP NOT NULL ) zDatabase initialized at N)r*cursorexecuterrr )rr(r,s rrzDBManager.initialize_database:s  "+ Cd[[]F NN   NN   NN  KK24<<.A BW+ C+ C+ Cs A&BB tickername asset_typequantity avg_pricec |j5}|j}|jd|||||tjft j d|d|d|ddddy#1swYyxYw)a4 Add a new holding to the portfolio. Args: ticker: Stock ticker symbol (e.g., 'VWCE.MI') name: Full name of the asset asset_type: 'ETF', 'Stock', or 'Cash' quantity: Number of shares/units avg_price: Average purchase price z INSERT INTO holdings (ticker, name, asset_type, quantity, avg_price, last_updated) VALUES (?, ?, ?, ?, ?, ?) zAdded holding: z ( @ )N)r*r,r-rnowrr)rr.r/r0r1r2r(r,s r add_holdingzDBManager.add_holdingks$ " Od[[]F NN$ HiP R KK/&H:S 1M N  O O Os AA44A=c |sydj|jDcgc]}|d c}}t|jt j |gz}|j 5}|j}|jd|d|tjd|d|dddycc}w#1swYyxYw)z Update holding fields. Args: ticker: Ticker to update **kwargs: Fields to update (quantity, avg_price, current_price, etc.) Nz, z = ?z5 UPDATE holdings SET z@, last_updated = ? WHERE ticker = ? zUpdated holding z: ) joinkeyslistvaluesrr6r*r,r-rdebug)rr.kwargskey set_clauser<r(r,s rupdate_holdingzDBManager.update_holdings YY F3%t FG fmmo&(,,.&)AA  " @d[[]F NN L!    LL+F82fX> ? @ @ G @ @s B?4ACC c|j5}|j}|jd|ftj d|dddy#1swYyxYw)zj Delete a holding from the portfolio. Args: ticker: Ticker to delete z%DELETE FROM holdings WHERE ticker = ?zDeleted holding: Nr*r,r-rr)rr.r(r,s rdelete_holdingzDBManager.delete_holdingsW " 6d[[]F NNBVI N KK+F84 5 6 6 6 2BC D E E ErEc|j5}|j}|jd|f|j}|r&|dt j |dfcdddS dddy#1swYyxYw)z Get cached price for a ticker. Args: ticker: Stock ticker Returns: Tuple of (price, last_updated) or None if not cached z