import os
import pytest
import numpy as np
import pandas as pd
from datetime import datetime, timedelta
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from database import Base, DailyPrice, DailyYield, _ENGINE
import database
from core_engine import run_engine

@pytest.fixture(scope="module")
def temp_db_engine(tmp_path_factory):
    # Setup temporary SQLite database
    db_path = tmp_path_factory.mktemp("db") / "test_portfolio_db.sqlite3"
    engine = create_engine(f"sqlite:///{db_path}")
    Base.metadata.create_all(engine)
    
    # Mock database module engine function to return test engine
    original_engine = database._ENGINE
    database._ENGINE = engine
    
    # Generate mock data
    Session = sessionmaker(bind=engine)
    session = Session()
    
    tickers = ["SPY", "TLT", "GLD", "^TNX"]
    start_date = datetime.today() - timedelta(days=365*2)
    dates = [start_date + timedelta(days=i) for i in range(500)]
    
    for t in tickers:
        prices = np.cumprod(1 + np.random.normal(0.0001, 0.01, size=len(dates))) * 100
        for d, p in zip(dates, prices):
            session.add(DailyPrice(ticker=t, date=d.date(), close_price=float(p)))
            
    # Mock yield data for bond processing
    yields = np.random.uniform(0.01, 0.05, size=len(dates))
    for d, y in zip(dates, yields):
        session.add(DailyYield(ticker="TLT", date=d.date(), yield_pct=float(y)))
            
    session.commit()
    session.close()
    
    yield engine
    
    # Teardown
    database._ENGINE = original_engine

def test_db_end_to_end_pipeline(temp_db_engine):
    """
    Tests the full execution of the portfolio engine connecting 
    exclusively to the SQLAlchemy database layer.
    """
    try:
        results = run_engine(overrides={
            "tickers": ["SPY", "TLT", "GLD"],
            "capital": 100000.0,
            "risk_input": 5,
            "model": 1,
            "allocation_engine": 1
        })
        
        # Verify complete run
        assert results is not None
        assert "target_weights" in results
        assert "SPY" in results["target_weights"]
        assert sum(results["target_weights"].values()) > 0.99
    except Exception as e:
        pytest.fail(f"End-to-end DB integration test failed: {e}")