# -*- coding: utf-8 -*-

import os
import sqlite3
import ymapjnx

def main():
    con = sqlite3.connect(ymapjnx.DBNAME_PNG)
    count = 0
    try:
        cur = con.cursor()
        finished = False
        try:
            cur.execute('CREATE TABLE IF NOT EXISTS tbl (\
                id INTEGER PRIMARY KEY, \
                enable INTEGER CHECK (enable IN (0, 1) OR enable IS NULL) DEFAULT NULL, \
                pngdata BLOB)')
            modes = os.listdir("files")
            for mode in modes:
                #print mode
                scales = os.listdir(os.path.join("files", mode))
                for scale in scales:
                    #print scale
                    xval = os.listdir(os.path.join("files", mode, scale))
                    for x in xval:
                        #print x
                        yval = os.listdir(os.path.join("files", mode, scale, x))
                        for y in yval:
                            #print y
                            ysplited = y.split(".")
                            name = ysplited[0]
                            ext = ysplited[1]
                            #print(name)
                            #print(ext)
                            if ext == "png":
                                target_id = ymapjnx.getKey(mode, int(scale), int(x), int(name))
                                #print(target_id)
                                row_exist = False
                                for row in cur.execute('SELECT * FROM tbl WHERE id=?', (target_id,)):
                                    row_exist = True
                                if not row_exist:
                                    path = os.path.join("files", mode, scale, x, y)
                                    #print(path)
                                    with open(path, 'rb') as f:
                                        data = f.read()
                                        size = os.path.getsize(path)
                                        #print(size)
                                        if size == 0:
                                            enable = 0
                                            data = None
                                        else:
                                            enable = 1
                                            try:
                                                data = buffer(data)
                                            except NameError:
                                                data = bytes(data)
                                        finished = False
                                        cur.execute('INSERT INTO tbl VALUES (?, ?, ?)', (target_id, enable, data,))
                                        finished = True
                                        con.commit()
                                        count = count + 1
                                        if count == 1000:
                                            count = 0
                                            print(path)
        except:
            if not finished:
                con.rollback()
            raise
        finally:
            cur.close()
    finally:
        con.close()

if __name__ == "__main__":
    print("start")
    main()
    print("finish")