import wx ,sqlite3, mutagen , os
from watchdog.events import FileSystemEventHandler
from watchdog.observers import Observer
import sqlviewer5_ui

# incremental search     watchDOG

class fileDT(wx.FileDropTarget):
    def __init__( self, window ):
        super().__init__( )
        self.window=window  # 親を登録

    def  OnDropFiles(self, x, y, filenames):     # override
        self.window.dropoperation(x,y,filenames)   # 親に投げる

class ChangeHandler(FileSystemEventHandler):
    def on_created(self, event):
        filepath = event.src_path
        filename = os.path.basename(filepath)
        print('%sができました' % filename)

    def on_modified(self, event):
        filepath = event.src_path
        filename = os.path.basename(filepath)
        print('%sを変更しました' % filename)

    def on_deleted(self, event):
        filepath = event.src_path
        filename = os.path.basename(filepath)
        print('%sを削除しました' % filename)

class MyFrame( sqlviewer5_ui.MyFrame1 ):
    def __init__( self, parent ):
        sqlviewer5_ui.MyFrame1.__init__( self, parent )
        self.db=<pass>
        self.btn_refresh(0)
        line='select artist ,album,title ,path from  musics order by artist ,album,title'
        self.m_textCtrl5.SetValue(line)
        self.sqlexecute(line)

        tr=fileDT(self)
        self.SetDropTarget(tr)

        target_dir='i:\\Music\\'
        event_handler = ChangeHandler()
        self.observer = Observer()
        self.observer.schedule(event_handler, target_dir, recursive=True)
        self.observer.start()

    def dropoperation(self,x,y,fname):
        self.m_listBox1.Clear()                      # Clear
        m = mutagen.File(fname[0], easy=True) # m は辞書で要素はリスト、取り出し方が難しい
        for n in m:
            self.m_listBox1.Append(n + ' : ' + m[n][0])

    def btn_refresh( self, event ):
        conn = sqlite3.connect( self.db )
        c = conn.cursor()
        c.execute("select * from sqlite_master where type='table'")
        table=c.fetchone()[1] # 最初のテーブル名
        des=c.execute('select * from {}'.format(table) ).description
        colis=[item[0] for item in des]
        i=0
        nonull=[]
        null=[]
        for col in colis :
            res=c.execute( 'select count(*) from {} where {} IS NULL'.format(table,colis[i]) )
            null.append(res.fetchall()[0][0])
            res=c.execute( 'select count(*) from {} where {} IS NOT NULL'.format(table,colis[i]) )
            nonull.append(res.fetchall()[0][0])
            i+=1

        self.m_staticText1.SetLabel('DATABASE -- {}'.format(self.db))
        self.m_staticText2.SetLabel('   Table -- {}'.format(table))

        all=[colis,nonull,null]
        line=''
        for i in all:
            for ii in i :
                line += '{:>10}'.format(ii)
            line += '\r\n'
        self.m_textCtrl1.SetValue(line)

        self.table=table  # global に使うことにした
        self.colis=colis
        c.close()
        conn.close()

    def onsearch_a( self, e ): # ここがサーチ
        st="'%"+self.m_textCtrl2.GetValue()+"%'"             # 取得
        line='select artist ,album,title ,path from  musics  where \
            artist like {}  or path like {} or title like {}\
                order by artist ,album,title'.format(st,st,st)
        self.sqlexecute(line)

    def btn_go2( self, event ): 
        line=self.m_textCtrl5.GetValue()
        self.sqlexecute(line)

    def sqlexecute( self, line ):
        # print(line)
        self.m_treeCtrl1.DeleteAllItems()
        conn = sqlite3.connect( self.db )
        c = conn.cursor()
        # albumartist ,date,album,tracknumber , title,fullpath
        item=['×','×']
        tree=[None,None,None]
        tree[0] = self.m_treeCtrl1.AddRoot('root', -1, -1, -1)    # rootとして挿入

        for l in  c.execute(line) :
            # print(l)
            if item[0] != l[0] :      # 1 a-artist
                tree[1] = self.m_treeCtrl1.AppendItem(tree[0],l[0], -1, -1 )
                item[0]=l[0]
            if item[1] != l[1] :      # 1 date album
                tree[2] = self.m_treeCtrl1.AppendItem(tree[1],l[1], -1, -1 )
                item[1]=l[1]
            self.m_treeCtrl1.AppendItem(tree[2],l[2], -1, -1 ,l[3])
 
        c.close()
        conn.close()

    def treeclick(self,e):
        # i=self.m_treeCtrl1.GetItemData(self.m_treeCtrl1.GetSelection())
        i=self.m_treeCtrl1.GetItemData(e.GetItem())
        self.m_staticText6.SetLabel(i)
        self.m_listBox1.Clear()                      # Clear
        m = mutagen.File(i, easy=True) # m は辞書で要素はリスト、取り出し方が難しい
        for n in m:
            self.m_listBox1.Append(n + ' : ' + m[n][0])

    def btn_clear(self,e):
        self.m_treeCtrl1.DeleteAllItems()

    def btn_bye( self, event ):
        self.observer.stop()
        quit()

if __name__ == '__main__':
    app = wx.App(False)
    frame = MyFrame(None)
    frame.Show(True)
    app.MainLoop()