from flask import Flask, request, render_template
from models import db, ModelForm, BondDeal, Counterparties
from sqlalchemy.sql import text
from sqlalchemy import create_engine
import pandas as pd

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://dawn_user@debian/dawndb'
app.config['SECRET_KEY'] = 'papa'
app.config['UPLOAD_FOLDER'] = 'tickets'

def list_counterparties():
    return Counterparties.query.order_by('name').\
        with_entities(Counterparties.code, Counterparties.name)

def load_counterparties():
    counterparties = pd.read_csv("/home/share/CorpCDOs/data/counterparties.csv")
    counterparties[['city', 'state']] = counterparties.Location.str.split(", ", expand=True)
    counterparties.drop(['Location', 'Valuation Contact4', 'Valuation Contact4 Email'], axis=1, inplace=True)
    counterparties.rename(columns ={'CODE': 'code',
                                    'DTC Number': 'dtc_number',
                                    'Email1': 'sales_email',
                                    'FIRM': 'name',
                                    'Phone': 'sales_phone',
                                    'Sales Contact': 'sales_contact',
                                    'Valuation Contact1': 'valuation_contact1',
                                    'Valuation Contact1 Email': 'valuation_email1',
                                    'Valuation Contact2': 'valuation_contact2',
                                    'Valuation Contact2 Email': 'valuation_email2',
                                    'Valuation Contact3': 'valuation_contact3',
                                    'Valuation Contact3 Email': 'valuation_email3',
                                    'Valuation Note': 'notes'}, inplace=True)
    engine = create_engine('postgresql://dawn_user@debian/dawndb')
    counterparties.to_sql('counterparties', engine, if_exists='append', index=False)

def add_triggers():
    engine = create_engine('postgresql://dawn_user@debian/dawndb')
    engine.execute(text("""
    CREATE OR REPLACE FUNCTION mydealid()
    RETURNS TRIGGER AS $$
    BEGIN
       UPDATE bonds SET dealid = 'SC_' ||left(asset_class::text,3)||id WHERE bonds.id = NEW.id;
       RETURN NEW;
    END;
    $$ language plpgsql"""))
    engine.execute(text("""CREATE TRIGGER dealid
       AFTER INSERT ON bonds
       FOR EACH ROW
       EXECUTE PROCEDURE mydealid()"""))

class BondForm(ModelForm):
    class Meta:
        model = BondDeal
        include_foreign_keys = True
        exclude = ['dealid', 'lastupdate'] #we generate it with a trigger at the server level

class CounterpartyForm(ModelForm):
    class Meta:
        model = Counterparties

@app.route('/', methods=['GET', 'POST'])
def trade_entry():
    bond_form = BondForm()
    bond_form.counterparty.choices = list_counterparties()
    if bond_form.is_submitted():
        if bond_form.validate():
            bond = BondDeal()
            bond_form.populate_obj(bond)
            bond.ticket.save('test.pdf')
            bond.ticket='test.pdf'
            bond_form.get_session().add(bond)
            bond_form.get_session().commit()
            return "Success!"
        else:
            print(bond_form.errors)
    return render_template("trade_entry.html", form=bond_form)

@app.route('/trade/<int:tradeid>', methods=['GET', 'POST'])
def trade_update(tradeid):
    trade = BondDeal()
    bond_form = BondForm(obj = BondDeal.query.get(tradeid))
    bond_form.counterparty.choices = list_counterparties()
    if bond_form.is_submitted():
        if bond_form.validate():
            bond_form.populate_obj(trade)
            return redirect(url_for('list_trades'))
        else:
            return "something wrong!"
    return render_template("trade_entry.html", form=bond_form)

@app.route('/blotter/')
def list_trades():
    trade_list = BondDeal.query.order_by(BondDeal.trade_date)
    return render_template('blotter.html', trades=trade_list.all())

@app.route('/counterparties/')
def list_counterparties():
    cp_list = Counterparties.query.order_by(Counterparties.name)
    return render_template('counterparties.html', counterparties = cp_list.all())

@app.route('/edit_cp/<cpcode>', methods=['GET', 'POST'])
def edit_counterparty(cpcode):
    cp_form = CounterpartyForm(obj = Counterparties.query.get(cpcode))
    if cp_form.is_submitted():
        if cp_form.validate():
            cp = Counterparties()
            cp_form.populate_obj(cp)
            return
    return render_template('edit_cp.html', form=cp_form)

if __name__=="__main__":
    db.init_app(app)
    db.drop_all(app=app)
    db.create_all(app=app)
    add_triggers()
    load_counterparties()
    app.run(debug=True)