import os
import sqlite3
from sqlite3 import Error

class alpha_date:
    def __init__(self):
        self.here_i_am = os.path.dirname(os.path.abspath(__file__))
        self.database = self.here_i_am + \
            r"\\All Playlists Tracks for daftjoe.db"
        self.decade = [
            "Decade_Classical_or_Older_Dates_1940_1949", "Decade_1950_1959", "Decade_1960_1969", "Decade_1970_1979", "Decade_1980_1989", "Decade_1990_1999", "Decade_2000_2009", "Decade_2010_2019", "Decade_2020_2029",]
        self.alphabet = ["ALPHA", "BRAVO", "CHARLIE", "DELTA", "ECHO", "FOXTROT", "GOLF", "HOTEL", "INDIA", "JULIET", "KILO", "LIMA", "MIKE", "NOVEMBER", "OSCAR", "PAPA",
                          "QUEBEC", "ROMEO", "SIERRA", "TANGO", "UNIFORM", "VICTOR", "WHISKEY", "X_RAY", "YANKEE", "ZULU", ]
        
    def do_the_business(self):
        letter = 0
        while letter < 26:
            self.playlist = self.alphabet[letter]
            conn = self.create_connection(self.database)
            print("Working on " + self.playlist)
            self.select_all_alpha_rows(conn)
            letter = letter + 1

    def select_all_alpha_rows(self, conn):
        cur = conn.cursor()
        try:
            cur.execute("SELECT * FROM '" + self.playlist + "'")
        except:
            print("Error in Alpha Rows")
            return
        arows = cur.fetchall()
        for arow in arows:
            self.uri = arow[0]
            self.decad = 0
            while self.decad < 9:
                self.declist = self.decade[self.decad]
                self.select_all_dec_rows(conn)
        return

    def select_all_dec_rows(self, conn):
        cur = conn.cursor()
        try:
            cur.execute("SELECT * FROM '" + self.declist + "'")
        except:
            print("Error in Dec Rows")
            return
        drows = cur.fetchall()
        for drow in drows:
            if drow[0] == self.uri:
                self.year = drow[3]
                self.update_alpha(conn)
                self.decad = 9
        self.decad = self.decad + 1
        return

    def update_alpha(self, conn):
        cur = conn.cursor()
        try:
            cur.execute("UPDATE '" + self.playlist + "' SET DECADE = '" + str(self.year) + "' WHERE URI = '" + self.uri + "'")
            conn.commit()
        except Error as e:
            print(e)
        return          

    def create_connection(self, db_file):
        conn = None
        try:
            conn = sqlite3.connect(db_file)
            return conn
        except Error as e1:
            print("E1")
            print(e1)
        return conn

a = alpha_date()
a.do_the_business()