from sqlalchemy import exc
from sqlalchemy.sql.expression import func
from models import Watchlist, Portfolio, Activity
from app import db
import metric


def buy_stock(ticker, units):
    unit_price = metric.get_price(ticker)
    total_price = units * unit_price
    max_id = db.session.query(func.max(Activity.activity_id)).scalar()
    
    if max_id is None:
        old_buying_power = 100000
    else:
        old_buying_power = Activity.query.filter(Activity.activity_id == max_id).all()[0].buying_power
    
    new_buying_power = old_buying_power - total_price

    if new_buying_power > 0:
        try:
            db.session.add( Activity(ticker=ticker, 
                units=units, order_type= "b", unit_price=unit_price, total_price=total_price, buying_power=new_buying_power) )
            update_portfolio_buy(ticker, units, total_price)
            db.session.commit()
            return { 'status': True, 'error': None }
        except exc.SQLAlchemyError:
            return { 'status': False, 'error': 'database error' }
    else:
        return { 'status': False, 'error': 'Insufficient Funds' }

def sell_stock(ticker, units):
    unit_price = metric.get_price(ticker)
    row = Portfolio.query.filter(Portfolio.ticker == ticker).all()
    if len(row):
        available_units = int(row[0].total_units)
        units = min(available_units, units) if units >= 1 else int(available_units*units)
        total_price = units * unit_price

        max_id = db.session.query(func.max(Activity.activity_id)).scalar()
        old_buying_power = Activity.query.filter(Activity.activity_id == max_id).all()[0].buying_power
        new_buying_power = old_buying_power + total_price

        try:
            db.session.add( Activity(ticker=ticker, 
                units=units, order_type= "s", unit_price=unit_price, total_price=total_price, buying_power=new_buying_power) )
            update_portfolio_sell(ticker, units, total_price)
            db.session.commit()
            return { 'status': True, 'amount': units, 'error': None }
        except exc.SQLAlchemyError:
            return { 'status': False, 'error': 'database error' }
    else:
        return { 'status': False, 'error': 'No Stock by this name' }

def update_portfolio_buy(ticker, units, total_price):
    row = Portfolio.query.filter(Portfolio.ticker == ticker).all()
    if len(row):
        row[0].total_units = int(row[0].total_units) + units
        row[0].total_invested = int(row[0].total_invested) + total_price
    else:
        db.session.add( Portfolio(ticker=ticker, total_units=units, total_invested=total_price) )

def update_portfolio_sell(ticker, units, total_price):
    row = Portfolio.query.filter(Portfolio.ticker == ticker).all()
    if len(row):
        row[0].total_invested = int(row[0].total_invested) - ((int(row[0].total_invested)/int(row[0].total_units)) * units)
        row[0].total_units = int(row[0].total_units) - units
    
    Portfolio.query.filter(Portfolio.total_units == 0).delete()

def get_watchlist():
    rows = Watchlist.query.all()
    if len(rows):
        watchlist = [row.ticker for row in rows]
    else:
        watchlist = []

    return watchlist

def get_portfolio():
    rows = Portfolio.query.all()
    portfolio = [{'ticker':row.ticker, 'total_units':row.total_units, 'total_invested':row.total_invested} for row in rows]
    return portfolio

def is_stock_in_watchlist(ticker):
    rows = Watchlist.query.filter(Watchlist.ticker == ticker).all()
    return True if len(rows) else False

def add_to_watchlist(ticker):
    industry = metric.get_company(ticker)["industry"]
    try:
        db.session.add( Watchlist(ticker=ticker, industry=industry) )
        db.session.commit()
        return True
    except exc.SQLAlchemyError:
        return False

def remove_from_watchlist(ticker):
    try:
        Watchlist.query.filter(Watchlist.ticker == ticker).delete()
        db.session.commit()
        return True
    except exc.SQLAlchemyError:
        return False






[((202, 226), 'metric.get_price', 'metric.get_price', (['ticker'], {}), '(ticker)\n', (218, 226), False, 'import metric\n'), ((1165, 1189), 'metric.get_price', 'metric.get_price', (['ticker'], {}), '(ticker)\n', (1181, 1189), False, 'import metric\n'), ((3039, 3060), 'models.Watchlist.query.all', 'Watchlist.query.all', ([], {}), '()\n', (3058, 3060), False, 'from models import Watchlist, Portfolio, Activity\n'), ((3216, 3237), 'models.Portfolio.query.all', 'Portfolio.query.all', ([], {}), '()\n', (3235, 3237), False, 'from models import Watchlist, Portfolio, Activity\n'), ((3573, 3599), 'metric.get_company', 'metric.get_company', (['ticker'], {}), '(ticker)\n', (3591, 3599), False, 'import metric\n'), ((3699, 3718), 'app.db.session.commit', 'db.session.commit', ([], {}), '()\n', (3716, 3718), False, 'from app import db\n'), ((3913, 3932), 'app.db.session.commit', 'db.session.commit', ([], {}), '()\n', (3930, 3932), False, 'from app import db\n'), ((865, 884), 'app.db.session.commit', 'db.session.commit', ([], {}), '()\n', (882, 884), False, 'from app import db\n'), ((1200, 1250), 'models.Portfolio.query.filter', 'Portfolio.query.filter', (['(Portfolio.ticker == ticker)'], {}), '(Portfolio.ticker == ticker)\n', (1222, 1250), False, 'from models import Watchlist, Portfolio, Activity\n'), ((1960, 1979), 'app.db.session.commit', 'db.session.commit', ([], {}), '()\n', (1977, 1979), False, 'from app import db\n'), ((2296, 2346), 'models.Portfolio.query.filter', 'Portfolio.query.filter', (['(Portfolio.ticker == ticker)'], {}), '(Portfolio.ticker == ticker)\n', (2318, 2346), False, 'from models import Watchlist, Portfolio, Activity\n'), ((2538, 2609), 'models.Portfolio', 'Portfolio', ([], {'ticker': 'ticker', 'total_units': 'units', 'total_invested': 'total_price'}), '(ticker=ticker, total_units=units, total_invested=total_price)\n', (2547, 2609), False, 'from models import Watchlist, Portfolio, Activity\n'), ((2678, 2728), 'models.Portfolio.query.filter', 'Portfolio.query.filter', (['(Portfolio.ticker == ticker)'], {}), '(Portfolio.ticker == ticker)\n', (2700, 2728), False, 'from models import Watchlist, Portfolio, Activity\n'), ((2946, 2996), 'models.Portfolio.query.filter', 'Portfolio.query.filter', (['(Portfolio.total_units == 0)'], {}), '(Portfolio.total_units == 0)\n', (2968, 2996), False, 'from models import Watchlist, Portfolio, Activity\n'), ((3430, 3480), 'models.Watchlist.query.filter', 'Watchlist.query.filter', (['(Watchlist.ticker == ticker)'], {}), '(Watchlist.ticker == ticker)\n', (3452, 3480), False, 'from models import Watchlist, Portfolio, Activity\n'), ((3645, 3688), 'models.Watchlist', 'Watchlist', ([], {'ticker': 'ticker', 'industry': 'industry'}), '(ticker=ticker, industry=industry)\n', (3654, 3688), False, 'from models import Watchlist, Portfolio, Activity\n'), ((294, 324), 'sqlalchemy.sql.expression.func.max', 'func.max', (['Activity.activity_id'], {}), '(Activity.activity_id)\n', (302, 324), False, 'from sqlalchemy.sql.expression import func\n'), ((640, 775), 'models.Activity', 'Activity', ([], {'ticker': 'ticker', 'units': 'units', 'order_type': '"""b"""', 'unit_price': 'unit_price', 'total_price': 'total_price', 'buying_power': 'new_buying_power'}), "(ticker=ticker, units=units, order_type='b', unit_price=unit_price,\n    total_price=total_price, buying_power=new_buying_power)\n", (648, 775), False, 'from models import Watchlist, Portfolio, Activity\n'), ((1734, 1869), 'models.Activity', 'Activity', ([], {'ticker': 'ticker', 'units': 'units', 'order_type': '"""s"""', 'unit_price': 'unit_price', 'total_price': 'total_price', 'buying_power': 'new_buying_power'}), "(ticker=ticker, units=units, order_type='s', unit_price=unit_price,\n    total_price=total_price, buying_power=new_buying_power)\n", (1742, 1869), False, 'from models import Watchlist, Portfolio, Activity\n'), ((3845, 3895), 'models.Watchlist.query.filter', 'Watchlist.query.filter', (['(Watchlist.ticker == ticker)'], {}), '(Watchlist.ticker == ticker)\n', (3867, 3895), False, 'from models import Watchlist, Portfolio, Activity\n'), ((1490, 1520), 'sqlalchemy.sql.expression.func.max', 'func.max', (['Activity.activity_id'], {}), '(Activity.activity_id)\n', (1498, 1520), False, 'from sqlalchemy.sql.expression import func\n'), ((434, 487), 'models.Activity.query.filter', 'Activity.query.filter', (['(Activity.activity_id == max_id)'], {}), '(Activity.activity_id == max_id)\n', (455, 487), False, 'from models import Watchlist, Portfolio, Activity\n'), ((1558, 1611), 'models.Activity.query.filter', 'Activity.query.filter', (['(Activity.activity_id == max_id)'], {}), '(Activity.activity_id == max_id)\n', (1579, 1611), False, 'from models import Watchlist, Portfolio, Activity\n')]