#!-*- coding:utf-8 -*-"

from datetime import date, datetime
from decimal import Decimal

from trac.util.datefmt import utc, to_datetime, format_date, parse_date

from utils import to_cc_list, get_est_field
from dateutils import to_tsint, prev_monthtop, next_monthtop, today, format_utcepoch, handle_current_date

def execute_update(com, sql, *params):
    u"""更新系SQLを実行する。コミットも行う。
    """
    db = com.env.get_db_cnx()
    cur = db.cursor()
    try:
        cur.execute(sql, params)
        db.commit()
    except Exception, e:
        _handle_sql_error(com, db, sql, params, e)

def select_first_row(com, sql, *params):
    u"""検索結果の1行目を取得する。"""
    db = com.env.get_db_cnx()
    cur = db.cursor()
    data = None;
    try:
        cur.execute(sql, params)
        data = cur.fetchone();
        db.commit();
    except Exception, e:
        _handle_sql_error(com, db, sql, params, e)
    return data;

def select_single_value(com, sql, col=0, *params):
    u"""検索結果の1行目の特定カラムのデータだけ取得する。"""
    data = select_first_row(com, sql, *params);
    if data:
        return data[col]
    else:
        return None;

def get_daily_estimated_hours(env, from_date=today(),
                              to_date=today()):
    est_field = get_est_field(env)
    
    db = env.get_db_cnx()
    cursor = db.cursor()
    
    def date_condition(col):
        if from_date and to_date:
            return "(%s BETWEEN %s AND %s) AND" % \
                    (col,
                     to_tsint(from_date.replace(day=1)),
                     to_tsint(next_monthtop(to_date, 1)))
        return ''
    
    #ticket_changeに登録されていない見積り時間(Ticket作成時に入力されたもの)
    #をまずは取得する
    sql = """
    SELECT DISTINCT t.id, t.time,
        tc1.value AS esthours
      FROM ticket_change tc, ticket t
      LEFT OUTER JOIN ticket_custom tc1
         ON tc1.ticket = t.id AND tc1.name = %s
      WHERE
        %s
        t.id NOT IN
          (SELECT DISTINCT tc.ticket
            FROM ticket_change tc
            WHERE tc.field = %s
          )
      ORDER BY t.time asc, t.id
    """ % ('%s', date_condition('t.time'), '%s')
    
    env.log.debug('get_daily_estimated_hours, 1st phase SQL: %s', sql)
    
    cursor.execute(sql, (est_field, est_field))
    tmp = []
    for ticket_id, date, est in cursor:
        tmp.append((format_utcepoch(date), ticket_id, est))
    
    #ticket_changeテーブルから、更新された日付時の見積り時間を取得する。
    sql = """
    SELECT t.id, tc.time,
       tc.newvalue AS esthours
     FROM ticket t, ticket_change tc
     WHERE
       %s
       tc.field = %s AND t.id = tc.ticket
     ORDER BY tc.time desc, t.id
    """ % (date_condition('tc.time'), '%s')
    cursor.execute(sql, (est_field,))
    
    before_date = None
    before_ticket_id = None
    
    for ticket_id, date, est in cursor:
        #同一日付、チケットの見積りは先頭(その日付で最後に更新されたもの)の値のみを採用する
        if before_date == date and before_ticket_id == ticket_id:
            continue
        tmp.append((format_utcepoch(date), ticket_id, est))
        
        before_date = date
        before_ticket_id = ticket_id
    
    #2つの見積り時間をマージしたので日付でソートし直す
    tmp.sort(lambda x, y: cmp(x[0], y[0]))
    
    env.log.debug('tmp_list=%s' % str(tmp))
    
    #上記で取得した値をもとに、日付ごとの見積り時間合計を算出する
    results = {}
    before_date = None
    latest_est = {}
    before_ids = []
    
    for date, ticket_id, est in tmp:
        if before_date is not None and before_date != date:
            for k, v in latest_est.iteritems():
                if k not in before_ids:
                    #前回の日付で登録されていないので追加する
                    results.update({before_date: results[before_date] + v})
            before_ids = []
            
        sum = results.get(date, Decimal(0))
        if not est:
            est = 0
        sum += Decimal(str(est))
        results[date] = sum
        
        latest_est[ticket_id] = Decimal(str(est))
        before_ids.append(ticket_id)
        before_date = date
        
    for k, v in latest_est.iteritems():
        if k not in before_ids:
            #前回の日付で登録されていないので追加する
            results.update({before_date: results[before_date] + v})
    
    return results

def get_worktime_sum_daily(env, from_date=today(),
                           to_date=today()):
    db = env.get_db_cnx()
    cursor = db.cursor()
  
    def date_condition():
        if from_date and to_date:
            return "WHERE substr(Date, 1, 7) BETWEEN '%s' AND '%s'" % (format_date(parse_date(from_date.isoformat()), '%Y-%m'),
                                          format_date(parse_date(to_date.isoformat()), '%Y-%m'))
        return ''  
    
    sql = """
    SELECT tw.date AS Date, sum(tw.hours_worked)
      FROM ticket_worktime tw
      %s
      GROUP BY Date
      ORDER BY Date asc
    """ % date_condition()
    
    cursor.execute(sql)
    results = {}
    for date, sum in cursor:
        results[date] = Decimal(str(sum))
    return results

def get_worktime_value(com, ticket_id, worker, date):
    return select_single_value(com, "SELECT ticket FROM ticket_worktime WHERE ticket=%s AND worker=%s AND date=%s", 0, ticket_id, worker, date)

def update_worktime(com, ticket_id, worker, submitter, date, hours, comments=''):
    if get_worktime_value(com, ticket_id, worker, date):
        if hours == '0':
            execute_update(com, "DELETE FROM ticket_worktime WHERE ticket=%s AND worker=%s AND date=%s",
                              ticket_id, worker, date)
        else:
            execute_update(com, "UPDATE ticket_worktime SET submitter=%s, hours_worked=%s, comments=%s WHERE ticket=%s AND worker=%s AND date=%s",
                          submitter, hours, comments, ticket_id, worker, date)
    else:
        if hours == '0':
            return
        execute_update(com, "INSERT INTO ticket_worktime (ticket, worker, submitter, date, hours_worked, comments) VALUES (%s, %s, %s, %s, %s, %s)",
             ticket_id, worker, submitter, date, hours, comments)
        
def get_holidays(com, req, cday=None):
    holidays = {}
    try:
        sql = "SELECT date, description from holiday"
        if cday:
            sql += " WHERE substr(date, 1, 7) = %s" 
        db = com.env.get_db_cnx()
        cursor = db.cursor()
        if cday:
            cursor.execute(sql, (cday.strftime('%Y/%m'), ))
        else:
            cursor.execute(sql)
        for hol_date, hol_desc in cursor:
            holidays[hol_date.replace('/', '-')] = hol_desc
    except:
        pass
    
    return holidays

TICKET_COLUMNS = ['id', 'summary', 'reporter', 'owner', 'description', 'type', 'status',
                  'priority', 'milestone', 'component', 'version', 'resolution',
                  'keywords', 'cc', 'time', 'changetime']

def get_ticket_column_for_sql(custom_ticket_columns, prefix='t'):
    sql = 't.' + ', t.'.join(TICKET_COLUMNS)
    if len(custom_ticket_columns) > 0:
        sql += ', ' + ', '.join(['tc%s.value' % x for x in range(0, len(custom_ticket_columns))])
    return sql


def get_tickets(env, req, worker, group_fields,
             base_day=None, prev_month=0, hide_closed_ticket=False, show_cc_ticket=True,
             selected_created_month=2):
    
    if worker is None:
        worker = req.authname 
    
    db = env.get_db_cnx()
    cursor = db.cursor()
    
    custom_ticket_columns = [x for x in group_fields if x not in TICKET_COLUMNS]
    
    sql = """
    SELECT %s, tc_est.value AS est
        FROM ticket t 
        LEFT OUTER JOIN ticket_custom tc_est
        ON tc_est.ticket = t.id AND tc_est.name = '%s'
    """ % (get_ticket_column_for_sql(custom_ticket_columns), get_est_field(env))
    custom_order_map = {}
    for index, group_field in enumerate(custom_ticket_columns):
        sql += """
        LEFT OUTER JOIN ticket_custom tc%s
        ON tc%s.ticket = t.id AND tc%s.name = '%s'
        """ % (index, index, index, group_field)
        custom_order_map[group_field] = 'tc%s.value' % index
        
    sql += " WHERE (t.owner = %s "
    
    if show_cc_ticket:
        cc_ids = _get_cc_tickets(env, db, req, worker)
        if len(cc_ids) > 0:
           sql += ' OR t.id IN (%s)' % ', '.join(str(x) for x in cc_ids)
    
    if base_day:
        closed_ids = _get_closed_tickets(env, db, req, worker, base_day, prev_month)
        if len(closed_ids) > 0:
            sql += ") AND t.id IN (%s)" % ", ".join(closed_ids)
        else:
            sql += ") "
    else:
        if hide_closed_ticket:
            sql += ") AND (t.resolution IS NULL OR t.resolution = '') "
        else:
            sql += ") "
    
    if not base_day:
        base_day = handle_current_date(req)
    
    if selected_created_month > 0:
        sql += " AND t.time < %s " % to_tsint(next_monthtop(base_day, selected_created_month))
    
    sql += " ORDER BY "
    if len(group_fields) > 0:
        orders = []
        for group_field in group_fields:
            # summaryでソートはしない
            if group_field == 'summary':
                continue
            
            if group_field in TICKET_COLUMNS:
                orders.append('t.%s' % group_field)
            else:
                orders.append(custom_order_map[group_field])
        orders.append('t.id')
        sql += ', '.join(orders)
    else:
        sql += " t.id"
    
    env.log.debug("get_tickets:SQL=%s", sql)
    
    cursor.execute(sql, (worker,))
        
    tickets = []
    for row in cursor:
        ticket = to_ticket(custom_ticket_columns, row)
        tickets.append(ticket)
        
    ticket_ids = [x['id'] for x in tickets]
    
    #このチケットの、日付ごとの作業時間を取得してTicketに設定する
    worktime_by_date, date_sum, ticket_sum = get_worktime_group_by_date_and_sum(env, ticket_ids, worker, base_day)
    
    #このチケットの、作業時間合計を取得。他のworkerの時間も含めた値。
    worktime_sums = get_worktime_sum_group_by_ticket(env, ticket_ids)
    
    #取得した上記の値をチケットに設定していく
    for ticket in tickets:
        ticket['worktimes'] = worktime_by_date.get(ticket['id'], {})
        ticket['worktime_sum'] = worktime_sums.get(ticket['id'], 0)
        
    return tickets, date_sum, ticket_sum



def _get_closed_tickets(env, db, req, worker, base_day, prev_month):
    u"""クローズ済みチケットの取得を行う。base_dayよりprev_month月分取得する。
    """
    close_sql = u"""
    SELECT DISTINCT t.id
        FROM ticket t, ticket_change tc
        WHERE
            %s
            AND
            (
             (t.resolution IS NULL OR t.resolution = '') /*チケット未解決*/
             OR
             (
              (t.resolution IS NOT NULL OR t.resolution <> '') /*チケット解決済*/
              AND tc.ticket = t.id AND tc.field = 'resolution'
              AND
              (tc.newvalue IS NOT NULL OR tc.newvalue <> '')
              AND tc.time > %s /*チケット解決した日時*/
             )
            )
    """ % (_where_owner_or_cc(env, db, req, worker), '%s')
    
    env.log.debug('_get_closed_tickets_sql=' + close_sql)
    
    cursor = db.cursor()
    cursor.execute(close_sql, (to_tsint(prev_monthtop(base_day, prev_month)),))
    
    close_ticket_ids = [str(x[0]) for x in cursor]
    return close_ticket_ids
    
def _where_owner_or_cc(env, db, req, worker, prefix='t'):
    rtn = "(%s.owner = '%s' " % (prefix, worker)
    cc_ids = _get_cc_tickets(env, db, req, worker)
    if len(cc_ids) > 0:
       rtn += ' OR t.id IN (%s)' % ', '.join(str(x) for x in cc_ids)
    return rtn + ')'
    
def _get_cc_tickets(env, db, req, worker):
    if hasattr(req, '_worktime_cc'):
        env.log.debug('_get_cc_ticket: return cached cc.')
        return req._worktime_cc
    
    cursor = db.cursor()
    search_cc_sql = "SELECT id, cc FROM ticket WHERE cc %s" % db.like() # % db.like_escape(worker)
    
    env.log.debug('_get_cc_tickets_sql=%s' % search_cc_sql)
    
    cursor.execute(search_cc_sql, ('%' + db.like_escape(worker) + '%',))
    ids = []
    for id, cc in cursor:
        if worker in to_cc_list(cc):
            ids.append(id)
            
    req._worktime_cc = ids
    return ids
    
def to_ticket(custom_ticket_columns, row):
    """検索結果のrowをTicketの辞書に変換する。
    """
    ticket = {}
    for index, ticket_column in enumerate(TICKET_COLUMNS):
        ticket[ticket_column] = row[index]
    #カスタムフィールド
    for index, ticket_column in enumerate(custom_ticket_columns):
        ticket[ticket_column] = row[len(TICKET_COLUMNS) + index]
        
    try:
        ticket['estimatedhours'] = float(row[len(TICKET_COLUMNS) + len(custom_ticket_columns)])
    except:
        ticket['estimatedhours'] = 0
        
    return ticket
    
def get_worktime_sum_group_by_ticket(env, ticket_ids):
    """指定したチケットごとの作業時間合計を取得する。
    チケットIDをキーとした辞書型で返す。
    """
    
    if len(ticket_ids) == 0:
        return {}
    
    db = env.get_db_cnx()
    cursor = db.cursor()
    
    sql = """
    SELECT ticket, sum(hours_worked) AS worktime
        FROM ticket_worktime
        WHERE ticket IN (%s)
        GROUP BY ticket
        ORDER BY ticket
    """ % ('%s,' * len(ticket_ids))[0:-1]
    
    cursor.execute(sql, ticket_ids)
    
    results = {}
    for ticket, worktime_sum in cursor:
        results[ticket] = Decimal(str(float(worktime_sum)))
        
    return results
    
def get_worktime_sum_by_ticket(env, ticket_id):
    """指定したチケットの作業時間合計を取得する。
    """
    db = env.get_db_cnx()
    cursor = db.cursor()
    
    sql = """
    SELECT sum(hours_worked) AS worktime
        FROM ticket_worktime
        WHERE ticket = %s
    """ 
    cursor.execute(sql, (ticket_id,))
    row = cursor.fetchone()
    if row:
        if row[0] is None:
            return 0
        return Decimal(str(float(row[0])))
    else:
        return 0

def is_related_ticket(env, req, ticket_id, worker):
    """指定したチケットが関連しているかチェックする。
    """
    ids = get_related_ticket_ids(env, req, worker)
    return int(ticket_id) in ids
    
def get_related_ticket_ids(env, req, worker):
    """関連チケットのIDリストを返す。
    """
    if hasattr(req, '_worktime_related_ticket'):
        env.log.debug('get_related_ticket_ids: return cached ids.')
        return req._worktime_related_ticket
    
    db = env.get_db_cnx()
    
    sql = """
      SELECT t.id, t.owner, t.cc
        FROM ticket t
        WHERE
          t.owner = %s OR cc %s
    """ % ('%s', db.like())
    
    cursor = db.cursor()
    
    env.log.debug('get_related_ticket_ids=%s' % sql)
    
    cursor.execute(sql, (worker, '%' + db.like_escape(worker) + '%',))
    
    ids = []
    for id, owner, cc in cursor:
        if owner == worker or worker in to_cc_list(cc):
            ids.append(id)
            
    req._worktime_related_ticket = ids
    
    return ids 


def get_worktime_group_by_date_and_sum(env, ticket_ids, worker, cday):
    """特定月における、日付ごと/チケットごとの作業時間合計と、日付ごとの作業時間合計、チケットごとの作業時間を取得する。
    """
    if len(ticket_ids) == 0:
        return {}, {}, {}
    
    db = env.get_db_cnx()
    cursor = db.cursor()
    
    sql = """
    SELECT ticket, date, sum(hours_worked) AS worktime
        FROM ticket_worktime
        WHERE ticket IN (%s) AND worker = %s AND substr(date, 1, 7) = %s 
        GROUP BY date, ticket
        ORDER BY date, ticket
    """ % (('%s,' * len(ticket_ids))[0:-1], '%s', '%s')
    
    env.log.debug('get_worktime_by_date SQL: %s', sql)
    cursor.execute(sql, ticket_ids + [worker, cday.strftime('%Y-%m')])
    
    results = {}
    date_sum = {}
    ticket_sum = {}
    for ticket, date, worktime in cursor:
        worktime = Decimal(str(float(worktime)))
        worktimes = results.get(ticket)
        if not worktimes:
            worktimes = {}
        worktimes[date] = worktime
        results[ticket] = worktimes
        
        #日付ごとの合計時間を算出
        if not date_sum.get(date):
            date_sum[date] = worktime
        else:
            date_sum[date] += worktime
            
        if not ticket_sum.get(ticket):
            ticket_sum[ticket] = worktime
        else:
            ticket_sum[ticket] += worktime
        
    env.log.debug('get_worktime_by_date: %s', results)
    return results, date_sum, ticket_sum

def _handle_sql_error(com, db, sql, params, exception):
    com.log.error('SQL ERROR:%s \nparams:%s \nException:%s' % (sql, params, exception));
    db.rollback();
