refresh_data.py

import requests
import json
import sqlite3
import pandas as pd
from datetime import datetime
import os
import time
import json
from google.oauth2 import service_account
from googleapiclient.discovery import build
from googleapiclient.http import MediaFileUpload, MediaIoBaseDownload
import io
from googleapiclient.errors import HttpError

# Database path
DB_PATH = "stocks.db"

# Google Drive sync credentials and folder (use Secrets in production)
GOOGLE_SERVICE_ACCOUNT_JSON = os.environ.get("GOOGLE_SERVICE_ACCOUNT_JSON", "YOUR_GOOGLE_SERVICE_ACCOUNT_JSON")
GOOGLE_DRIVE_FOLDER_ID = os.environ.get("GOOGLE_DRIVE_FOLDER_ID", "YOUR_GOOGLE_DRIVE_FOLDER_ID")

# Alpha Vantage API key (set as environment variable or hardcode temporarily for testing, update for Spaces)
ALPHA_VANTAGE_API_KEY = os.environ.get("ALPHA_VANTAGE_API_KEY", "YOUR_ALPHA_VANTAGE_API_KEY")

# List of tickers to refresh
popular_stocks = ["AAPL", "GOOGL", "GOOG", "MSFT", "AMZN", "TSLA", "FB", "NVDA", "IBM", "INTC", "ORCL", "META"]

def fetch_alpha_vantage_quote(ticker, api_key):
    url = f"https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol={ticker}&apikey={api_key}"
    try:
        response = requests.get(url, timeout=10)
        response.raise_for_status()
        data = response.json()
        if "Global Quote" in data:
            return data["Global Quote"]
        return None
    except requests.exceptions.RequestException as e:
        print(f"Error fetching quote for {ticker}: {str(e)}")
        return None

def fetch_alpha_vantage_history(ticker, api_key):
    url = f"https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol={ticker}&outputsize=compact&apikey={api_key}"
    try:
        response = requests.get(url, timeout=10)
        response.raise_for_status()
        data = response.json()
        if "Time Series (Daily)" in data:
            daily_data = data["Time Series (Daily)"]
            df = pd.DataFrame.from_dict(daily_data, orient='index')
            df = df.astype(float)
            df.index = pd.to_datetime(df.index)
            df = df.sort_index(ascending=True)
            df.columns = ['Open', 'High', 'Low', 'Close', 'Volume']
            return df[['Close', 'Volume']].to_json()
        return None
    except requests.exceptions.RequestException as e:
        print(f"Error fetching history for {ticker}: {str(e)}")
        return None

def fetch_alpha_vantage_overview(ticker, api_key):
    url = f"https://www.alphavantage.co/query?function=OVERVIEW&symbol={ticker}&apikey={api_key}"
    try:
        response = requests.get(url, timeout=10)
        response.raise_for_status()
        data = response.json()
        return data
    except requests.exceptions.RequestException as e:
        print(f"Error fetching overview for {ticker}: {str(e)}")
        return None

def sync_database_to_drive():
    try:
        # Parse service account JSON from Secrets
        service_account_info = json.loads(GOOGLE_SERVICE_ACCOUNT_JSON)
        credentials = service_account.Credentials.from_service_account_info(service_account_info)
        drive_service = build("drive", "v3", credentials=credentials)

        # Upload stocks.db to Google Drive
        file_metadata = {
            "name": "stocks.db",
            "parents": [GOOGLE_DRIVE_FOLDER_ID]
        }
        media = MediaFileUpload(DB_PATH, mimetype="application/octet-stream")
        file = drive_service.files().create(body=file_metadata, media_body=media, fields="id").execute()
        print(f"Successfully synced SQLite database to Google Drive (File ID: {file.get('id')})")
    except HttpError as e:
        if e.resp.status == 404:
            print("Google Drive folder not found. Check GOOGLE_DRIVE_FOLDER_ID and permissions.")
            # Optionally, create a new file or folder, but this requires additional logic
        else:
            print(f"Error syncing database to Google Drive: {str(e)}")
    except Exception as e:
        print(f"Error syncing database to Google Drive: {str(e)}")

def sync_database_from_drive():
    try:
        # Parse service account JSON from Secrets
        service_account_info = json.loads(GOOGLE_SERVICE_ACCOUNT_JSON)
        credentials = service_account.Credentials.from_service_account_info(service_account_info)
        drive_service = build("drive", "v3", credentials=credentials)

        # Download stocks.db from Google Drive
        file_metadata = drive_service.files().list(q=f"'{GOOGLE_DRIVE_FOLDER_ID}' in parents and name='stocks.db'").execute()
        files = file_metadata.get("files", [])
        if files:
            request = drive_service.files().get_media(fileId=files[0]["id"])
            fh = io.BytesIO()
            downloader = MediaIoBaseDownload(fh, request)
            done = False
            while not done:
                status, done = downloader.next_chunk()
            with open(DB_PATH, "wb") as f:
                f.write(fh.getvalue())
            print("Successfully synced SQLite database from Google Drive.")
        else:
            print("No stocks.db found in Google Drive. Creating a new database.")
            conn = sqlite3.connect(DB_PATH)
            conn.close()
    except HttpError as e:
        if e.resp.status == 404:
            print("stocks.db not found in Google Drive. Creating a new local database.")
            conn = sqlite3.connect(DB_PATH)
            conn.close()
        else:
            print(f"Error syncing database from Google Drive: {str(e)}")
    except Exception as e:
        print(f"Error syncing database from Google Drive: {str(e)}")

def update_database():
    # Sync database from Google Drive before updating
    sync_database_from_drive()

    # Connect to SQLite database
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    # Create table if it doesn't exist
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS stocks (
            ticker TEXT PRIMARY KEY,
            quote TEXT,
            history TEXT,
            overview TEXT,
            last_updated TEXT
        )
    """)

    # Fetch and store data for each ticker, with delays to respect rate limits
    for ticker in popular_stocks:
        quote = fetch_alpha_vantage_quote(ticker, ALPHA_VANTAGE_API_KEY)
        history = fetch_alpha_vantage_history(ticker, ALPHA_VANTAGE_API_KEY)
        overview = fetch_alpha_vantage_overview(ticker, ALPHA_VANTAGE_API_KEY)

        if quote and history and overview:
            cursor.execute("""
                REPLACE INTO stocks (ticker, quote, history, overview, last_updated)
                VALUES (?, ?, ?, ?, ?)
            """, (ticker, json.dumps(quote), history, json.dumps(overview), datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
        else:
            print(f"Skipping {ticker} due to missing data")
        
        # Delay to respect 5 requests/minute limit (12 seconds per request for 5 requests/min)
        time.sleep(12)

    conn.commit()
    conn.close()

    # Sync updated database to Google Drive
    sync_database_to_drive()

if __name__ == "__main__":
    update_database()