from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

import models

# table_seq = ['Role', 'Division', 'Employment', 'Account_Group', 'Account', 'Account_Meta', 'Account_Image', 'Status', 'Order', 'Order_Lines', 'Inbox', 'Approval', 'Session', 'Device', 'Token']
table_seq = ['Role', 'Division', 'Employment', 'Account_Group', 'Account', 'Account_Meta', 'Account_Image', 'Status', 'Order', 'Order_Lines', 'Inbox', 'Approval', 'Session', 'Device', 'Token', 'Order_Number']
commit_count = 0

if __name__ == '__main__':
    def make_session(conn_string):
        engine = create_engine(conn_string)
        Session = sessionmaker(bind=engine)
        return Session(), engine

    from sys import argv

    if len(argv) <= 1:
        print 'No argument. Use: init, backup or restore'
        exit(1)

    command = argv[1].lower()

    # INIT: Load seed data
    if command == 'init':
        import seeds

        session, engine = make_session(models.SQLALCHEMY_URL)
        models.Base.metadata.bind = engine

        for table in table_seq:
            if table in seeds.DATA:
                print "Insert into table %s" % table.lower()

                session.add_all([getattr(models, table)(**row) for row in seeds.DATA[table]])
                session.commit()

                # For postgresql sequence only
                try:
                    tname = table.lower()
                    sql = "SELECT setval('%s_id_seq', COALESCE((SELECT MAX(id)+1 FROM \"%s\"), 1), false);" % (tname, tname)
                    session.execute(sql)
                except:
                    pass
                finally:
                    session.close()

        session.close()
        print "Finished!"

    # BACKUP or RESTORE
    elif command in ['backup', 'restore']:
        from datetime import datetime

        current = datetime.now().strftime('%Y%m%d%H%M%S')

        print 'Creating sessions...'
        if command == 'backup':
            ssession, sengine = make_session(models.SQLALCHEMY_URL)
            dsession, dengine = make_session('sqlite:///backup_%s.sqlite' % (current))
        else:
            ssession, sengine = make_session('sqlite:///backup.sqlite')
            dsession, dengine = make_session(models.SQLALCHEMY_URL)

        print 'Preparing destination database...'
        models.Base.metadata.create_all(dengine)

        for table in table_seq:
            print "%s table %s" % ('Backing up' if command=='backup' else 'Restoring', table.lower())

            count = 0
            for record in ssession.query(getattr(models, table)).all():
                # Partial commit when data size is large
                if commit_count > 0 and count > 0 and count % commit_count == 0:
                    dsession.commit()

                dsession.merge(record)
                count += 1

            dsession.commit()

            # For postgresql sequence only
            if command == 'restore':
                try:
                    tname = table.lower()
                    sql = "SELECT setval('%s_id_seq', COALESCE((SELECT MAX(id)+1 FROM \"%s\"), 1), false);" % (tname, tname)
                    dsession.execute(sql)
                except:
                    pass
                finally:
                    dsession.close()

        ssession.close()
        dsession.close()

        print 'Finished!'

    else:
        print 'Undefined command. Use: init, backup or restore'