#encoding=utf-8
import re
import time
import md5
import csv,codecs
from datetime import datetime
from time import strptime ,mktime
from trac.util.datefmt import utc, to_timestamp

from genshi.builder import tag

from trac.core import *
from trac.env import IEnvironmentSetupParticipant
from trac.web import IRequestHandler
from trac.web.chrome import INavigationContributor, ITemplateProvider, add_stylesheet, add_script
from trac.perm import IPermissionRequestor
from trac.config import Configuration

#from enquete_answer import *
#from enquete_chart import *
from listedit import *
from listtitle import *

class ListManagePlugin(Component):
    implements(IEnvironmentSetupParticipant,
                    INavigationContributor, IRequestHandler, ITemplateProvider,IPermissionRequestor)

    # INavigationContributor methods
    def get_active_navigation_item(self, req):
        return 'ListManage'

    def get_navigation_items(self, req):
        if req.perm.has_permission('LIST_MANAGE_VIEW'):
            yield ('mainnav', 'listmanage',
                   tag.a('一覧管理', href=req.href.listmanage()))

    # IRequestHandler methods
    def match_request(self, req):
        match = re.match('/listmanage(?:/([^/]+))?(?:/(.*)$)?', req.path_info)
        if match:
            req.args['module'] = match.group(1)
            req.args['arguments'] = match.group(2)
            return True
        else:
            return False

    #IPermissionRequestor methods
    def get_permission_actions(self):
        return ['LIST_MANAGE_VIEW','LIST_MANAGE_EDIT']

    def process_request(self, req):
        req.perm.assert_permission('LIST_MANAGE_VIEW')

        db = self.env.get_db_cnx()
        actionName = req.args.get('actionName')
        list_id = req.args.get('list_id')
        id = req.args.get('id')
        error =''

        add_stylesheet(req, 'listmanage/css/listmanage.css')
        add_stylesheet(req, 'listmanage/css/calendar.css')
        add_stylesheet(req, 'listmanage/css/logger.css')
        add_stylesheet(req, 'listmanage/css/container-skin.css')

        add_script(req,'listmanage/js/util.js')
        add_script(req,'listmanage/js/yahoo-dom-event.js')
        add_script(req,'listmanage/js/calendar.js')

        #utilities.jsをインクルードするかしないかの判断
        yui_flag = Configuration.get(self.config, 'listmanage', 'yui_utilities', default='')
        if yui_flag.lower()!='false':
            add_script(req,'listmanage/js/utilities.js')

        #モジュールの判定
        module = req.args.get('module', None)
        if module:
            if module =='edittitle':
                #タイトル編集
                return edittitle(self,req)
            elif module == 'editelement':
                #要素編集
                return editelement(self,req)
            elif module == 'editelementdetail':
                #要素明細編集
                return editelementdetail(self,req)
            elif module == 'edit':
                #リスト編集
                return edit(self,req)
            elif module == 'editdetail':
                #リスト明細編集
                return editdetail(self,req)

        data = {}

        when = datetime.now()
        when_ts = time.mktime(when.timetuple())
        #when_ts = to_timestamp(when)

        cursor = db.cursor()
        if actionName:
            #削除
            if actionName=='delete':
                req.perm.assert_permission('LIST_MANAGE_EDIT')

                try:
                    #list_idにリンクする全てのデータを削除する
                    sql = [
                         ("""delete from list_title where list_id = '%s'""")  % (list_id),
                         ("""delete from list_element where list_id = '%s'""") % (list_id),
                         ("""delete from list_value where value_id in (select value_id from list_value left join list_row on list_value.row_id = list_row.row_id where list_id ='%s')""") % (list_id),
                         ("""delete from list_row where list_id = '%s'""") % (list_id)
                    ]
                    cursor = db.cursor()
                    for s in sql:
                        try:
                            self.log.debug('%s' % s)
                            cursor.execute(s)
                        except Exception,e:
                            self.log.debug('%s' % e)
                            None

                except Exception,e:
                    self.log.debug('%s' % e)
                    raise e

                db.commit()

            elif actionName == 'import':
                #csvファイルインポート
                form = req.args.get('importfile')
                if not form.filename.endswith('.csv'):
                    error = 'ファイル形式がcsvファイルではありません。'
                else:
                    fn = tempfile.mkstemp()

                    fp = file(fn[1],"w")
                    fp.write(form.value)
                    fp.close()
                    os.close(fn[0])
                    fname = form.filename

                    result = self.importCSV(req,db,fn[1],fname[0:fname.rindex('.')])
                    if result !='SUCCESS':
                        error = result

        #一覧検索
        try:
            sql = ("""SELECT t.list_id as list_id,t.title as title,strftime('%Y-%m-%d %H:%M:%f',m.mx) as update_date,m.cnt as cnt,t.description as description,t.order_no as order_no,""")
            sql = sql + ("""t.parent_list_id as parent_list_id,t.wiki_name as wiki_name,strftime('%Y-%m-%d %H:%M:%f',t.release_date) as release_date,w.wiki_version""")
            sql = sql + (""" FROM list_title t""")
            sql = sql + (""" left outer join (select lr.list_id as lid , max(update_date) as mx,count(list_id) as cnt from list_row lr group by list_id) m on m.lid = list_id""")
            sql = sql + (""" left outer join (select name,max(version) as wiki_version from wiki group by name) w on w.name = wiki_name order by order_no""")
            self.log.debug('%s' % sql)
            cursor.execute(sql)

        except Exception,e:
            self.log.debug('%s' % e)
            raise e

        list_title=[]

        for list_id, title,update_date, cnt, description, order_no, parent_list_id, wiki_name, release_date, wiki_version in cursor:
            status = ''
            if release_date == None or update_date > release_date:
                status = '編集中'
            else:
                status = 'リリース'
            list = {'list_id':list_id ,'title':title,'update_date':update_date,'cnt':cnt, 'description':description, 'status':status,
                    'order_no':order_no, 'parent_list_id':parent_list_id, 'wiki_name':wiki_name, 'release_date':release_date,'wiki_version':wiki_version }
            list_title.append(list)

        data = {'list_title': list_title,'error':error}

        db.close()

        return 'listmanage.html', data, None

    # ITemplateProvider methods
    def get_templates_dirs(self):
        from pkg_resources import resource_filename
        return [resource_filename(__name__, 'templates')]

    def get_htdocs_dirs(self):
        from pkg_resources import resource_filename
        return [('listmanage',resource_filename(__name__, 'htdocs'))]

    # IEnvironmentSetupParticipant methods
    def environment_created(self):
        self.log.debug('ListManagePlugin environment_created')
        pass

    def environment_needs_upgrade(self, db):
        self.log.debug('ListManagePlugin environment_needs_upgrade')
        cursor = db.cursor()
        try:
            sql = 'SELECT list_id FROM list_title'
            self.log.debug('%s' % sql)
            cursor.execute(sql)

            sql = 'SELECT hidden FROM list_element'
            self.log.debug('%s' % sql)
            cursor.execute(sql)
        except Exception,e:
            self.log.debug('%s' % e)
            return True

        return False

    def upgrade_environment(self, db):
        self.log.debug('ListManagePlugin upgrade_environment')

        sql = [
"""CREATE TABLE list_element ( list_id TEXT NOT NULL, element_id TEXT NOT NULL , element_name TEXT , element_type INTEGER , element_detail TEXT , default_value TEXT , column_no INTEGER , length TEXT , PRIMARY KEY (list_id,element_id))""",
"""CREATE TABLE list_value ( value_id INTEGER NOT NULL , row_id INTEGER , element_id TEXT , value TEXT , PRIMARY KEY (value_id))""",
"""CREATE TABLE list_row ( row_id INTEGER NOT NULL , list_id TEXT , parent_row_id INTEGER , row_no INTEGER , update_user TEXT , update_date DATE , PRIMARY KEY (row_id))""",
"""CREATE TABLE list_title ( list_id TEXT NOT NULL , title TEXT , description TEXT , order_no INTEGER , parent_list_id INTEGER , wiki_name TEXT , release_date DATE , PRIMARY KEY (list_id))""",
"""ALTER TABLE list_element ADD COLUMN hidden INTEGER""",
"""VACUUM"""

]
        cursor = db.cursor()
        for s in sql:
            try:
                self.log.debug('%s' % s)
                cursor.execute(s)
            except Exception,e:
                self.log.debug('%s' % e)
                None


    def importCSV(self,req,db,path,title):
        try:
            csvreader = csv.reader(codecs.open(path, "r", "Shift_JIS"))
            cursor = db.cursor()

            #title
            sql = (("""insert into list_title values('%s','%s','%s',ifnull((select max(order_no)+1 from list_title),1),'%s','%s',null)""")
                    % (title, title, '', '', title))
            self.log.debug('%s' % sql)
            cursor.execute(sql)

            #element,column
            elements = []
            values = []

            when = datetime.now()
            for idx0,row in enumerate(csvreader):
                sql = "select max(row_id)+1 as mx from list_row"
                cursor.execute(sql)
                mx = cursor.fetchone()
                row_id = mx[0]
                when_ts = row_id
                if idx0 == 0:
                    elements = row
                    for idx,element in enumerate(elements):
                        element_id = element
                        element_name = element
                        element_type = 1
                        default_value = ''
                        column_no = idx
                        element_length = ''
                        element_hidden = '0'
                        element_detail = ''
                        sql = (("""insert into list_element values('%s','%s','%s',%d,'%s','%s' ,ifnull((select max(column_no) + 1 from list_element where list_id = '%s'),1),'%s',%d)""" )
                               % (title ,element_id,element_name,int(element_type),element_detail,default_value,title,element_length,int(element_hidden)))
                        self.log.debug('%s' % sql)
                        cursor.execute(sql)
                else:
                    values = row
                    sql = (("""insert into list_row values(%d,'%s',%d,ifnull((select max(row_no) + 1 from list_row where list_id = '%s'),1),'%s','%s')""" )
                            % (row_id , title, 0, title, req.authname, when))

                    self.log.debug('%s' % sql)
                    cursor.execute(sql)

                    for idx,value in enumerate(row):
                        when_ts = when_ts + 1
                        sql = ("""insert into list_value values(ifnull((select max(value_id) + 1 from list_value),%d),%d,'%s','%s')""" ) % (int(when_ts) , row_id, elements[idx], value)
                        self.log.debug('%s' % sql)
                        cursor.execute(sql)

                    #value

            db.commit()

            message = "SUCCESS"

        except Exception, e:
            self.log.debug('%s' % e)
            #raise e
            message = e

        return message
