from sqlalchemy import create_engine, func, text, or_, and_
from sqlalchemy.orm import sessionmaker, scoped_session, aliased, subqueryload
from sqlalchemy.sql import label
from sqlalchemy.exc import IntegrityError, SQLAlchemyError
from collections import OrderedDict
import traceback
import datetime

import models

if 'mysql' in models.SQLALCHEMY_URL.drivername:
    engine = create_engine(models.SQLALCHEMY_URL, pool_recycle=3600)
else:
    engine = create_engine(models.SQLALCHEMY_URL)

models.Base.metadata.bind = engine
DBSession = scoped_session(sessionmaker(bind=engine))

BUDGET_LIMIT = 5000000
PAGINATION_LIMIT = 10
SESSION_EXPIRED = 0
FCM_SERVER_KEY = 'AAAAYQ2139Q:APA91bGBhF2CQj-VE3eASKAVGfsbGKbu40ks-MX_Odb7zKIvsJzv_OpvaP6vo-EruR6P-AKshdFZWhFI1Lpdd-6FbSlSle5ZiDQyqw3j92Hze2qyvii1bn6p1I2bKTGCllUp-I8L2Tin'

def set_locale():
    import locale

    # Linux
    try:
        locale.setlocale(locale.LC_TIME, 'id_ID.utf8')
        return
    except:
        pass
    
    # Win Vista+ 
    try:
        locale.setlocale(locale.LC_TIME, 'id-ID') 
        return
    except:
        pass

    # Win XP
    try:  
        locale.setlocale(locale.LC_TIME, 'IN')
        return
    except:
        pass


def get_all_schedules(old=False):
    import locale
    session = DBSession()

    s = aliased(models.Schedule)
    sm = aliased(models.Schedule_Meta)

    q = session.query(s.uid, s.summary, s.start, sm.solution, sm.region, sm.pic, sm.bd, sm.attendee, sm.location, sm.description).join(sm, s.uid == sm.uid, isouter=True)

    if old:
        schedules = q.order_by(text('start DESC')).all()
    else:
        schedules = q.order_by(s.start).filter(s.start >= datetime.datetime.today()).all()

    set_locale()

    output = [
        {
            'uid' : row.uid,
            'summary' : row.summary,
            'start' : row.start.strftime('%a, %d %b %y %H:%M'),
            'solution' : row.solution if row.solution is not None else 'Type E/V',
            'region' : row.region,
            'pic' : row.pic,
            'bd' : row.bd,
            'attendee' : row.attendee,
            'location' : row.location,
            'description' : row.description
        } for row in schedules
    ]

    session.close()
    return output

def save_schedule(data):
    session = DBSession()

    # Create new schedule
    schedule = models.Schedule(**data)
    session.add(schedule)

    session.commit()
    session.close()

def save_schedule_batch(data_batch):
    session = DBSession()

    session.add_all([models.Schedule(**row) for row in data_batch])
    session.commit()
    session.close()


def save_schedule_batch_update(data_batch):
    session = DBSession()

    # Iterate each data
    for row in data_batch:
        schedule = session.query(models.Schedule).filter(models.Schedule.uid == row.uid).first()

        if schedule is not None:
            schedule.summary = row['summary']
            schedule.start = row['start']
        else:
            schedule = models.Schedule(**row)

        session.commit()
    
    session.close()

    
def save_schedule_additional_data(uid, data):
    session = DBSession()

    # Get particular schedule
    schedule = session.query(models.Schedule_Meta).filter(models.Schedule_Meta.uid == uid).first()

    if schedule is None:
        # Insert values
        data['uid'] = uid
        schedule = models.Schedule_Meta(**data)
        session.add(schedule)
    else:
        # Update values
        schedule.region = data['solution']
        schedule.region = data['region']
        schedule.bd = data['bd']
        schedule.attendee = data['attendee']
        schedule.pic = data['pic']
        schedule.location = data['location']
        schedule.description = data['description']

    session.commit()
    session.close()

def clear_schedule():
    session = DBSession()
    
    session.query(models.Schedule).delete()
    session.commit()

    session.close()