microcap_scrapping/database.py

"""
Database setup for Stock Intelligence System
SQLite database with all required tables
"""

import sqlite3
import os
from datetime import datetime
import json


class StockDatabase:
    def __init__(self, db_path="data/stocks.db"):
        self.db_path = db_path
        os.makedirs(os.path.dirname(db_path), exist_ok=True)
        self.conn = sqlite3.connect(db_path)
        self.cursor = self.conn.cursor()
        self.create_tables()
    
    def create_tables(self):
        """Create all database tables"""
        
        # Main stocks master table
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS stocks_master (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                symbol TEXT NOT NULL UNIQUE,
                company_name TEXT NOT NULL,
                exchange TEXT NOT NULL,
                sector TEXT,
                industry TEXT,
                country TEXT,
                listing_date TEXT,
                status TEXT DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # Financial statements table
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS financial_statements (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                stock_id INTEGER NOT NULL,
                year INTEGER NOT NULL,
                quarter TEXT,
                statement_type TEXT NOT NULL,
                revenue REAL,
                cogs REAL,
                gross_profit REAL,
                operating_income REAL,
                net_income REAL,
                eps REAL,
                ebit REAL,
                ebitda REAL,
                total_assets REAL,
                total_liabilities REAL,
                total_debt REAL,
                shareholders_equity REAL,
                cash REAL,
                operating_cash_flow REAL,
                investing_cash_flow REAL,
                financing_cash_flow REAL,
                free_cash_flow REAL,
                is_ttm BOOLEAN DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (stock_id) REFERENCES stocks_master(id),
                UNIQUE(stock_id, year, quarter, statement_type)
            )
        """)
        
        # Financial metrics table
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS financial_metrics (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                stock_id INTEGER NOT NULL,
                year INTEGER NOT NULL,
                quarter TEXT,
                is_ttm BOOLEAN DEFAULT 0,
                
                -- Valuation Ratios
                pe_ratio REAL,
                peg_ratio REAL,
                pb_ratio REAL,
                ps_ratio REAL,
                price_to_cash_flow REAL,
                ev_ebitda REAL,
                ev_ebit REAL,
                dividend_yield REAL,
                price_to_fcf REAL,
                ev_to_sales REAL,
                
                -- Profitability Ratios
                gross_margin REAL,
                operating_margin REAL,
                net_margin REAL,
                roe REAL,
                roa REAL,
                roce REAL,
                roic REAL,
                ebitda_margin REAL,
                
                -- Leverage Ratios
                debt_to_equity REAL,
                debt_to_assets REAL,
                interest_coverage REAL,
                financial_leverage REAL,
                
                -- Liquidity Ratios
                current_ratio REAL,
                quick_ratio REAL,
                cash_ratio REAL,
                working_capital_ratio REAL,
                
                -- Efficiency Ratios
                inventory_turnover REAL,
                asset_turnover REAL,
                receivables_turnover REAL,
                payables_turnover REAL,
                days_sales_outstanding REAL,
                days_inventory_outstanding REAL,
                days_payable_outstanding REAL,
                
                -- Growth Metrics
                revenue_growth_yoy REAL,
                eps_growth_yoy REAL,
                net_income_growth_yoy REAL,
                book_value_growth_yoy REAL,
                
                -- Cash Flow Metrics
                fcf_yield REAL,
                operating_cf_ratio REAL,
                capex_ratio REAL,
                
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (stock_id) REFERENCES stocks_master(id),
                UNIQUE(stock_id, year, quarter)
            )
        """)
        
        # News articles table
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS news_articles (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                stock_id INTEGER NOT NULL,
                title TEXT NOT NULL,
                source TEXT,
                published_date TEXT,
                url TEXT,
                snippet TEXT,
                full_text TEXT,
                sentiment TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (stock_id) REFERENCES stocks_master(id)
            )
        """)
        
        # Press releases table
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS press_releases (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                stock_id INTEGER NOT NULL,
                title TEXT NOT NULL,
                source TEXT NOT NULL,
                published_date TEXT,
                url TEXT,
                summary TEXT,
                full_text TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (stock_id) REFERENCES stocks_master(id)
            )
        """)
        
        # Regulatory filings table
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS filings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                stock_id INTEGER NOT NULL,
                filing_date TEXT NOT NULL,
                filing_type TEXT NOT NULL,
                title TEXT,
                document_url TEXT,
                source TEXT,
                filing_text TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (stock_id) REFERENCES stocks_master(id)
            )
        """)
        
        # AGM information table
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS agm_info (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                stock_id INTEGER NOT NULL,
                agm_date TEXT,
                agm_time TEXT,
                agm_location TEXT,
                agm_agenda TEXT,
                document_url TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (stock_id) REFERENCES stocks_master(id)
            )
        """)
        
        # Tax disclosures table
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS tax_disclosures (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                stock_id INTEGER NOT NULL,
                year INTEGER NOT NULL,
                income_tax_expense REAL,
                deferred_tax_assets REAL,
                deferred_tax_liabilities REAL,
                effective_tax_rate REAL,
                tax_loss_carryforwards REAL,
                tax_jurisdictions TEXT,
                notes TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (stock_id) REFERENCES stocks_master(id),
                UNIQUE(stock_id, year)
            )
        """)
        
        # Stock quotes table (real-time price data)
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS stock_quotes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                stock_id INTEGER NOT NULL,
                quote_date TEXT,
                quote_time TEXT,
                open_price REAL,
                high_price REAL,
                low_price REAL,
                close_price REAL,
                volume TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (stock_id) REFERENCES stocks_master(id)
            )
        """)
        
        # Coverage tracking table
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS coverage_report (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                ticker TEXT NOT NULL UNIQUE,
                exchange TEXT NOT NULL,
                has_financials BOOLEAN DEFAULT 0,
                has_ttm BOOLEAN DEFAULT 0,
                has_news BOOLEAN DEFAULT 0,
                has_press_releases BOOLEAN DEFAULT 0,
                has_filings BOOLEAN DEFAULT 0,
                has_tax_disclosures BOOLEAN DEFAULT 0,
                has_agm_info BOOLEAN DEFAULT 0,
                last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                error_log TEXT,
                FOREIGN KEY (ticker) REFERENCES stocks_master(symbol)
            )
        """)
        
        self.conn.commit()
        print("✅ Database tables created successfully")
    
    def add_stock(self, symbol, company_name, exchange, sector=None, industry=None, country=None, listing_date=None):
        """Add a stock to the master table"""
        try:
            self.cursor.execute("""
                INSERT OR IGNORE INTO stocks_master 
                (symbol, company_name, exchange, sector, industry, country, listing_date)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (symbol, company_name, exchange, sector, industry, country, listing_date))
            self.conn.commit()
            return self.cursor.lastrowid
        except Exception as e:
            print(f"Error adding stock {symbol}: {e}")
            return None
    
    def import_listings_from_json(self, json_file):
        """Import stock listings from JSON file"""
        print(f"\n📥 Importing listings from {json_file}...")
        
        with open(json_file, 'r', encoding='utf-8') as f:
            listings = json.load(f)
        
        imported = 0
        for stock in listings:
            stock_id = self.add_stock(
                symbol=stock.get('symbol'),
                company_name=stock.get('name'),
                exchange=stock.get('exchange'),
                sector=stock.get('sector'),
                industry=stock.get('industry'),
                country=stock.get('country', 'Canada')
            )
            if stock_id:
                imported += 1
                
                # Also add to coverage report
                self.cursor.execute("""
                    INSERT OR IGNORE INTO coverage_report (ticker, exchange)
                    VALUES (?, ?)
                """, (stock.get('symbol'), stock.get('exchange')))
        
        self.conn.commit()
        print(f"✅ Imported {imported} stocks")
        return imported
    
    def get_all_stocks(self):
        """Get all stocks from database"""
        self.cursor.execute("SELECT * FROM stocks_master")
        return self.cursor.fetchall()
    
    def get_coverage_report(self):
        """Get coverage report for all stocks"""
        self.cursor.execute("""
            SELECT ticker, exchange, 
                   has_financials, has_ttm, has_news, has_press_releases,
                   has_filings, has_tax_disclosures, has_agm_info,
                   last_updated
            FROM coverage_report
            ORDER BY ticker
        """)
        return self.cursor.fetchall()
    
    def update_coverage(self, ticker, **kwargs):
        """Update coverage flags for a stock"""
        fields = []
        values = []
        for key, value in kwargs.items():
            fields.append(f"{key} = ?")
            values.append(value)
        
        if fields:
            query = f"UPDATE coverage_report SET {', '.join(fields)}, last_updated = ? WHERE ticker = ?"
            values.extend([datetime.now().isoformat(), ticker])
            self.cursor.execute(query, values)
            self.conn.commit()
    
    def get_stock_id(self, ticker):
        """Get stock ID from ticker"""
        self.cursor.execute("SELECT id FROM stocks_master WHERE symbol = ?", (ticker,))
        result = self.cursor.fetchone()
        return result[0] if result else None
    
    def insert_financial_metrics(self, ticker, year, metrics_dict, is_ttm=False, quarter=None):
        """Insert calculated financial metrics into database"""
        stock_id = self.get_stock_id(ticker)
        if not stock_id:
            return False
        
        try:
            self.cursor.execute("""
                INSERT OR REPLACE INTO financial_metrics (
                    stock_id, year, quarter, is_ttm,
                    pe_ratio, peg_ratio, pb_ratio, ps_ratio, price_to_cash_flow,
                    ev_ebitda, ev_ebit, dividend_yield, price_to_fcf, ev_to_sales,
                    gross_margin, operating_margin, net_margin, roe, roa, roce, roic, ebitda_margin,
                    debt_to_equity, debt_to_assets, interest_coverage, financial_leverage,
                    current_ratio, quick_ratio, cash_ratio, working_capital_ratio,
                    inventory_turnover, asset_turnover, receivables_turnover, payables_turnover,
                    days_sales_outstanding, days_inventory_outstanding, days_payable_outstanding,
                    revenue_growth_yoy, eps_growth_yoy, net_income_growth_yoy, book_value_growth_yoy,
                    fcf_yield, operating_cf_ratio, capex_ratio
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                stock_id, year, quarter, is_ttm,
                metrics_dict.get('pe_ratio'), metrics_dict.get('peg_ratio'), metrics_dict.get('pb_ratio'),
                metrics_dict.get('ps_ratio'), metrics_dict.get('price_to_cash_flow'),
                metrics_dict.get('ev_ebitda'), metrics_dict.get('ev_ebit'), metrics_dict.get('dividend_yield'),
                metrics_dict.get('price_to_fcf'), metrics_dict.get('ev_to_sales'),
                metrics_dict.get('gross_margin'), metrics_dict.get('operating_margin'), metrics_dict.get('net_margin'),
                metrics_dict.get('roe'), metrics_dict.get('roa'), metrics_dict.get('roce'),
                metrics_dict.get('roic'), metrics_dict.get('ebitda_margin'),
                metrics_dict.get('debt_to_equity'), metrics_dict.get('debt_to_assets'),
                metrics_dict.get('interest_coverage'), metrics_dict.get('financial_leverage'),
                metrics_dict.get('current_ratio'), metrics_dict.get('quick_ratio'), metrics_dict.get('cash_ratio'),
                metrics_dict.get('working_capital_ratio'),
                metrics_dict.get('inventory_turnover'), metrics_dict.get('asset_turnover'),
                metrics_dict.get('receivables_turnover'), metrics_dict.get('payables_turnover'),
                metrics_dict.get('days_sales_outstanding'), metrics_dict.get('days_inventory_outstanding'),
                metrics_dict.get('days_payable_outstanding'),
                metrics_dict.get('revenue_growth_yoy'), metrics_dict.get('eps_growth_yoy'),
                metrics_dict.get('net_income_growth_yoy'), metrics_dict.get('book_value_growth_yoy'),
                metrics_dict.get('fcf_yield'), metrics_dict.get('operating_cf_ratio'), metrics_dict.get('capex_ratio')
            ))
            self.conn.commit()
            return True
        except Exception as e:
            print(f"Error inserting metrics for {ticker}: {e}")
            return False
    
    def insert_news_article(self, ticker, title, source, published_date, url, snippet=None):
        """Insert news article into database"""
        stock_id = self.get_stock_id(ticker)
        if not stock_id:
            return False
        
        try:
            self.cursor.execute("""
                INSERT OR IGNORE INTO news_articles (stock_id, title, source, published_date, url, snippet)
                VALUES (?, ?, ?, ?, ?, ?)
            """, (stock_id, title, source, published_date, url, snippet))
            self.conn.commit()
            return True
        except Exception as e:
            print(f"Error inserting news for {ticker}: {e}")
            return False
    
    def insert_filing(self, ticker, filing_date, filing_type, title, document_url, source):
        """Insert regulatory filing into database"""
        stock_id = self.get_stock_id(ticker)
        if not stock_id:
            return False
        
        try:
            self.cursor.execute("""
                INSERT OR IGNORE INTO filings (stock_id, filing_date, filing_type, title, document_url, source)
                VALUES (?, ?, ?, ?, ?, ?)
            """, (stock_id, filing_date, filing_type, title, document_url, source))
            self.conn.commit()
            return True
        except Exception as e:
            print(f"Error inserting filing for {ticker}: {e}")
            return False
    
    def insert_stock_quote(self, ticker, quote_data):
        """Insert stock quote data into database"""
        stock_id = self.get_stock_id(ticker)
        if not stock_id:
            return False
        
        try:
            # Parse price values (remove commas and convert to float)
            def parse_price(value):
                if not value:
                    return None
                try:
                    return float(str(value).replace(',', ''))
                except:
                    return None
            
            open_price = parse_price(quote_data.get('open'))
            high_price = parse_price(quote_data.get('high'))
            low_price = parse_price(quote_data.get('low'))
            close_price = parse_price(quote_data.get('close'))
            volume = quote_data.get('volume', '')
            quote_date = quote_data.get('date', '')
            
            self.cursor.execute("""
                INSERT INTO stock_quotes 
                (stock_id, quote_date, open_price, high_price, low_price, close_price, volume)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (stock_id, quote_date, open_price, high_price, low_price, close_price, volume))
            self.conn.commit()
            return True
        except Exception as e:
            print(f"Error inserting quote for {ticker}: {e}")
            return False
    
    def close(self):
        """Close database connection"""
        self.conn.close()


def main():
    """Initialize database and import listings if available"""
    db = StockDatabase()
    
    # Check if we have listings to import
    listings_file = "data/listings/all_listings_combined.json"
    if os.path.exists(listings_file):
        db.import_listings_from_json(listings_file)
        
        # Show stats
        stocks = db.get_all_stocks()
        print(f"\n📊 Database Statistics:")
        print(f"   Total stocks: {len(stocks)}")
        
        # Group by exchange
        exchanges = {}
        for stock in stocks:
            exchange = stock[3]  # exchange column
            exchanges[exchange] = exchanges.get(exchange, 0) + 1
        
        for exchange, count in exchanges.items():
            print(f"   {exchange}: {count} stocks")
    else:
        print(f"⚠️  No listings file found at {listings_file}")
        print("   Run extract_listings.py first to get stock data")
    
    db.close()


if __name__ == "__main__":
    main()