import  os ,mutagen 
import sqlite3

# update form dog_log

class makeDB():
    def __init__( self):
        self.fname=os.path.join(os.path.dirname(__file__) , 'watchdog.log')
        if not os.path.exists(self.fname):
            print('missing watchdog.log')
            input()
            quit()
        self.db=os.path.join(os.path.dirname(__file__) , 'database.db')
        if not os.path.exists(self.db):
            print('missing database.db')
            input()
            quit()
        conn = sqlite3.connect(self.db)
        c = conn.cursor()
        c.execute('create table IF NOT EXISTS musics(path PRIMARY KEY,mdate,flag,artist,album,title)')
        self.count={'new':0 , 'same':0,'mod':0, 'update':0 }
        self.first(c) # ログを開いてDBのチェック
        # quit()
        self.second(c) # フラグがNoneのものを更新
        c.execute('DELETE FROM musics WHERE flag=4') # フラグが４のものを削除
        
        c.close()
        conn.commit()
        conn.close()
        print(self.count)
        os.remove(self.fname)
        input('')
  
  
    def first(self,c):
        # フルパスをフラグNoneで新規挿入、成功したらそのまま
        # 失敗したらmdate比較して　同じならフラグ１　違ってたらフラグNone
        # フルパスが存在しなければFBから探してあったらフラグ４

        with open(self.fname,'r') as f:
            flist=f.readlines()
            flist_u = list(set(flist)) # リストにして重複行を削除
        # print(flist_u)

        for p in flist_u:
            p = p.replace('\n','')
            p = p.replace('\r','')
            # print('p',p)
            if os.path.exists(p):
                # print('f',p)
                mtime=os.path.getmtime(p)
                try:
                    c.execute( "INSERT INTO musics (path,mdate,flag) VALUES (?,?,?)" , (p,mtime,None ))
                    self.count['new']+=1
                except:
                    c.execute('select  mdate from musics where path = ?',(p, ))
                    res=c.fetchone()
                    if res :
                        if res[0]==mtime : # 同じなら flag=1
                            c.execute('update  musics set flag = ? where path = ? ',( 1, p ))
                            self.count['same']+=1
                        else : # 違ったら flag=None
                            c.execute('update  musics set mdate=?,flag = ? where path = ? ',( mtime,None, p ))
                            self.count['mod']+=1
            else : # でーた削除の場合
                c.execute('select  mdate from musics where path = ?',(p, ))
                res=c.fetchone()
                if res : # 存在したらフラグを４とする
                    c.execute('update  musics set flag = ? where path = ? ',( 4, p ))

    def second(self,c):
        c.execute('select path from musics where flag IS NULL')
        for p in  c.fetchall():
            # print(p)
            try:
                m = mutagen.File(p[0], easy=True) # m は辞書で要素はリスト、取り出し方が難しい
                tup=1,
            except:
                print('error-- ' + p[0])
                c.execute('update  musics set flag = ? where path = ? ',( 4, p[0] ))
                continue

            try:
                tup += m['albumartist'][0],
            except:
                try :
                    tup += m['artist'][0],
                except:
                    tup += '?',

            try :
                date=m['date'][0]
            except:
                date='?'
            try :
                album=m['album'][0]
            except:
                album='?'
            if date=='?' and album=='?' :
                tup += '?',
            else:
                tup += '['+ date + ']' + album ,

            title=''
            sl=''
            try :
                for i in m['discnumber'][0]:
                    if i == '/':
                        break
                    else:
                        sl += i
                title+='{:01}.'.format(int(sl))
            except:
                pass
            ti=''
            try:
                for i in m['tracknumber'][0]:
                    if i == '/':
                        break
                    else:
                        ti += i
                title+='{:02} '.format(int(ti))
            except:
                pass
            try:
                title += m['title'][0] 
            except:
                title='?'

            tup +=title,
            tup += p[0],
            c.execute('update musics set flag=?,artist=?,album=?,title=? where path=?' , tup )
            self.count['update']+=1

makeDB()