import logging as log

import calc
import random
import time
import os
import glob
import datetime

exclude=[]

def delete(db, applicationfolder):
    """ delete all tables in crm database except auth and those in the exclude list."""

    log.info("deletion starting "+str(datetime.datetime.today()))
    # delete tables and related web2py migration files
    for tablename in db.tables[:]:
        if tablename in exclude or tablename[0:4]=="auth":
            log.info("keep "+tablename)
        else:
            table=db[tablename]
            try:
                log.info("delete "+tablename)
                table.drop()
            except Exception as e:
                log.warning("failed to drop %s\n%s"%(tablename, e))
            try:
                 os.remove("%s/databases/%s_%s.table"%(applicationfolder, db._uri_hash, tablename))
            except Exception as e:
                log.warning("failed to delete %s\n%s"%(tablename, e))
        db.commit()
  
    # print SQL requests and timings
    for elem in db._timings:
        log.debug(elem)
    
    # delete uploaded images
    filelist=glob.glob('%s/uploads/*.*'%applicationfolder)
    for file in filelist:
        log.info("deleting file "+ file) 
        os.remove(file)

def populate(db, web, testimages):
    """ populates database with test data.
    Product data/images from opencart demo. Rest generated by web2py populate program.
    Note first run deletedata if you want to delete existing tables """
    t0 = time.time()

    # get categories
    c=web.oc_category
    cd=web.oc_category_description
    cats = web(c.category_id == cd.category_id).select(c.parent_id, c.category_id, cd.name)
    for cat in cats:
        if cat.oc_category.parent_id != 0:
            p = web(cd.category_id == cat.oc_category.parent_id).select().first().name
        else:
            p=""
        log.info(db.category.fields)
        log.info(p)
        log.info(cat.oc_category_description.name)
        try:
            db.category.insert(category1=p, category2=cat.oc_category_description.name)
        except:
            log.exception("oh no")

############# populate product data and images from opencart demo ############################

    for prod in web(web.oc_product).select():
        log.info("loading "+str(prod.product_id))

        # randomize price on web
        prod.update_record(price=random.random()*10)

        # get categories for product
        catstring = []
        for webcat in web(
            (web.oc_product_to_category.product_id==prod.product_id) & 
            (web.oc_product_to_category.category_id==c.category_id) &
            (cd.category_id==c.category_id) &
            (cd.language_id==1)
            ).select():
            catstring.append(webcat.oc_category_description.name)

        # copy web to crm product
        desc=web((web.oc_product_description.product_id==prod.product_id) & \
                    (web.oc_product_description.language_id==1) ).select().first()
        log.info(desc)

        log.info(desc.description)
        id=db.product.insert(name=desc.name,
                          price=prod.price, 
                          quantity=prod.quantity,
                          categories=",".join(catstring),
                          description=desc.description)

        # web reference to crm
        prod.update_record(model=id)

        # images from web
        for pi in [prod] + list(web(web.oc_product_image.product_id==prod.product_id).select()):
            if pi.image is None:
                continue
            try:
                name=pi.image.split("/")[-1]
                log.info("found image "+name)
                stream = open(os.path.join(testimages, name),'rb')
                db.plugin_attachment_attachment.insert(
                                parentname = "product",
                                parentid=id,
                                file1=db.plugin_attachment_attachment.file1.store(stream, name),
                                filename=name)
            except Exception as e:
                log.warning("failed to add image\n%s"%e)
                pass

###### populate remaining db tables ################################################
    from gluon.contrib import populate
    for table in ['customer','event','salesorder','orderline']:
        n=30
        # populate transactional tables with greater volume
        if table in ['salesorder','orderline']: n=3*n
        try:
            log.info('adding '+str(n)+' records to '+ table)
            populate.populate(db[table],n)
        except:
            log.exception('failed to populate' + table)
    
    log.info('populating seconds='+str(time.time()-t0))
    t0 = time.time()

    # update orderlines
    for orderline in db(db.orderline).select():
        product=db.product[orderline.product]
        orderline.update_record(price=product.price)
        calc.calc_orderline(db, orderline)

    # update salesorder
    for salesorder in db(db.salesorder).select():
        calc.calc_salesorder(db, salesorder)
        
    log.info('updating seconds='+str(time.time()-t0))