#!/usr/bin/python
# -*- coding: utf-8 -*-
import sqlite3 as lite
import sys
import os
from sh import du
from PIL import Image
import os
import re


directory = "/mnt/wave/fossils.kit.edu/"


con = lite.connect('fossil.db')
with con:
    cur = con.cursor()
    counter = 1
    for folder in sorted(os.listdir(directory)):

        if os.path.isfile( directory+folder+"/"+folder+".zip" ) == False:
            continue
        
        if os.path.isdir(directory+folder) == False:
            continue

        with open(directory+folder+'/'+folder+'.txt', 'r') as myfile:
            data=myfile.read().replace('\n', '')

        fsize = du(directory+folder+"/"+folder+".zip")
        fsize = fsize.stdout.split("\t")[0].strip()

        # path joining version for other paths
        left_dir = directory+folder+"/left/left0000.png"
        top_dir = directory+folder+"/top/top0000.png"
        front_dir = directory+folder+"/front/front0001.png"

        imLeft=Image.open(left_dir)
        left_width = imLeft.size[0] # (width,height) tuple
        
        imTop=Image.open(top_dir)
        top_width = imTop.size[0] # (width,height) tuple

        imFront=Image.open(front_dir)
        front_width = imFront.size[0] # (width,height) tuple
        
        # path joining version for other paths
        left_dir = directory+folder+"/left/"
        top_dir = directory+folder+"/top/"
        front_dir = directory+folder+"/front/"
       
        total_left = len([name for name in os.listdir(left_dir) if (os.path.join(left_dir, name)).endswith('.png')])
        total_top = len([name for name in os.listdir(top_dir) if (os.path.join(top_dir, name)).endswith('.png')])
        total_front = len([name for name in os.listdir(front_dir) if (os.path.join(front_dir, name)).endswith('.png')])
        
        #total_left = 0
        #total_top = 0
        #total_front = 0
 
        index = folder
        #index = re.findall(r'\d+', index)
        name = str(folder)
        desc = data
        size = fsize

        #print name, size, total_left

        cur.execute("INSERT INTO Fossil VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);", (name, desc, size, total_left, total_top, total_front, counter, left_width, top_width, front_width)) 
        counter += 1

"""
#CREATE TABLE
con = lite.connect('fossil.db')
with con:
    cur = con.cursor()    
    cur.execute("CREATE TABLE Fossil(Id INT, Name TEXT, Description TEXT, Size INT, Left INT, Top INT, Front INT)")
    #cur.execute("INSERT INTO Cars VALUES(1,'Audi',52642)")
    #cur.execute("INSERT INTO Cars VALUES(2,'Mercedes',57127)")
    #cur.execute("INSERT INTO Cars VALUES(3,'Skoda',9000)")
    #cur.execute("INSERT INTO Cars VALUES(4,'Volvo',29000)")
    #cur.execute("INSERT INTO Cars VALUES(5,'Bentley',350000)")
    #cur.execute("INSERT INTO Cars VALUES(6,'Citroen',21000)")
    #cur.execute("INSERT INTO Cars VALUES(7,'Hummer',41400)")
    #cur.execute("INSERT INTO Cars VALUES(8,'Volkswagen',21600)")
"""