from sqlalchemy.sql import text
from sqlalchemy import create_engine
import os

def create_trigger_function(db):
    # need to escape % with %%
    auto_dealid = db.DDL("""
    CREATE OR REPLACE FUNCTION auto_dealid()
    RETURNS TRIGGER AS $$
    DECLARE stub text;
            sqlstr text;
    BEGIN
      sqlstr:= 'UPDATE '|| TG_TABLE_NAME ||' SET %%s WHERE id = %%L AND dealid is NULL';

       IF (TG_TABLE_NAME = 'bonds') THEN
          stub := 'SC_';
          sqlstr := format(sqlstr, 'dealid = $1||upper(left(asset_class::text,3))||id,
identifier = COALESCE(identifier, cusip, isin)', NEW.id);
       ELSIF (TG_TABLE_NAME = 'cds') THEN
          stub := 'SCCDS';
          sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
       ELSIF (TG_TABLE_NAME = 'repo') THEN
          stub := 'SC_REP';
          sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
       ELSIF (TG_TABLE_NAME = 'swaptions') THEN
          stub := 'SWPTN';
          sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
       ELSIF (TG_TABLE_NAME = 'futures') THEN
          stub := 'SCFUT';
          sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
       ELSIF (TG_TABLE_NAME = 'wires') THEN
          stub := 'SCCSH';
          sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id);
       END IF;
       EXECUTE sqlstr USING stub;
       RETURN NEW;
    END;
    $$ language plpgsql""")
    db.event.listen(db.metadata, 'before_create', auto_dealid)

def create_triggers(db):
    trigger = db.DDL("""CREATE TRIGGER dealid
    AFTER INSERT ON %(fullname)s
    FOR EACH ROW
    EXECUTE PROCEDURE auto_dealid()""")
    for name, tb in db.metadata.tables.items():
        if not name.endswith('counterparties'):
            db.event.listen(tb, 'after_create', trigger)

if __name__ == "__main__":
    """ This script will create the tables and triggers for the Dawn app.
    If schema is not None, it will create it under a specific schema"""
    #conn.execute("CREATE SCHEMA IF NOT EXISTS {}".format(schema))
    #conn.execute("SET search_path TO %s", (schema,))
    os.environ['CONF'] = 'config.ini'
    from Dawn import db
    create_trigger_function(db)
    create_triggers(db)
    db.create_all()