from flask import Flask, request, render_template, redirect, url_for, send_from_directory
from models import db, ModelForm, BondDeal, Counterparties
from sqlalchemy.sql import text
from sqlalchemy import create_engine
from sqlalchemy.exc import IntegrityError
import pandas as pd
import os
import datetime

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

def cp_choices():
    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('/trades/<int:tradeid>', methods=['GET', 'POST'])
@app.route('/trades/', defaults={'tradeid': None}, methods=['GET', 'POST'])
def trade_manage(tradeid):
    if tradeid:
        trade = BondDeal.query.get(tradeid)
        bond_form = BondForm(obj = BondDeal.query.get(tradeid))
        #we change the default to update
        bond_form.action.data = 'UPDATE'
    else:
        trade = BondDeal()
        bond_form = BondForm()
    bond_form.cp_code.choices = cp_choices()
    if bond_form.is_submitted():
        if bond_form.validate():
            if tradeid:
                old_ticket_name = trade.ticket
            bond_form.populate_obj(trade)
            session = bond_form.get_session()
            if tradeid and trade.ticket.filename == '':
                trade.ticket = old_ticket_name
            else:
                ticket_name =  "{0} {1}.pdf".format(trade.description,
                                                  str(datetime.datetime.now()))
                trade.ticket.save(os.path.join(app.config['UPLOAD_FOLDER'],
                                               ticket_name))
                trade.ticket = ticket_name
                if not tradeid:
                    session.add(trade)
            try:
                session.commit()
            except IntegrityError:
                print("TODO: fix this")
            return redirect(url_for('list_trades'))
        else:
            return str(bond_form.errors)
    return render_template('trade_entry.html', form=bond_form, trade_id=tradeid)

@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('/tickets/<path:filename>')
def download_ticket(filename):
    return send_from_directory(app.config['UPLOAD_FOLDER'],
                               filename, as_attachment=True)

@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 = Counterparties.query.get(cpcode)
    cp_form = CounterpartyForm(obj = cp)
    if cp_form.is_submitted():
        if cp_form.validate():
            cp_form.populate_obj(cp)
            session = cp_form.get_session()
            session.commit()
            return redirect(url_for('list_counterparties'))
    return render_template('edit_cp.html', form=cp_form, code=cpcode)

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)