from datetime import datetime
import sqlite3, os
from sqlite3 import Error

## Create HTML files for MASTER Database Table Data.
## [0]  Unplayed Tracks Total.
## [1]  Last_Run_Additions.
## [2]  Last_Run_Deletions.
## [3]  New_Dupe_Tracks.
## [4]  URI_Change.
## [5a] Last_Played_Tracks (EPM) - Present in the MASTER database.
## [5b] Last_Played_Tracks (ETX) - Not yet part of the EPM System.
## [6]  Queue Errors. (from Queue_check Table).
## [7]  Index Page with Interactive Queue Icons

## NOTE: IMPORTANT ##
## Add your own database name in Line: 38 ##
## (no need for full path if running "htmlbuilder.py" in same folder as database). ##

## Set your prefered file path & folder name for Web files in Line: 41 ##
## N.B. For this early version, please create the folder manually before running this script. ##

## NOTE: HTML BUILDER Creates HTML files from MASTER Database Tables. (11/11/22)
print('HTML BUILDER v7.0.0')
now = datetime.now()
date_time = now.strftime('%d-%m-%y @ %H:%M')
yearis = now.strftime('%Y')
tablebody1 = ""
indrow = 0
indrowitem = 0
indrowcount = 0
inditemcount = 0
indurilist = []
indnamelist = []
indpiclist = []
indaudlist = []
conn = None
##filename = "MASTER.db"
##filename = "J:\HGMS\Endless Feed.db"  ##
filename = "J:\Spotty\secrets_and_database\Enormous Feed.db"

## Set your prefered "Web" contents folder name and path here: ##
file_path = r'J:\HGMS\VIKING-WEB'

##==========================================================================

try:
    conn = sqlite3.connect(filename)

    ## NOTE: [0] Unplayed Tracks Total.
    sql = ("SELECT COUNT (*) FROM Track_List WHERE QUEUED = 0")
    curr = conn.cursor()
    curr.execute(sql)
    row = curr.fetchone()
    unplayed = str(row[0])

    ##==========================================================================

    header1 = '<header><style> #intro {background-image: url("viking-wallpaper.jpg"); height: 120vh;} @media (min-width: 992px) {#intro {margin-top: -1px;}} .navbar .nav-link {color: #fff !important;} h1 {color: rgb(0, 0, 0); text-align: center;}audio {filter: sepia(20%) saturate(70%) grayscale(1) contrast(99%) invert(12%);width: 100px;height: 20px;}</style>'
    header2 = '<nav class = "navbar navbar-expand-lg" style = "background-color: #64b8c0"><div class = "container-fluid"><a class = "navbar-brand" href = "index.html"> Viking Utilities ~ &copy; 2022 </a><button class = "navbar-toggler" type = "button" data-bs-toggle = "collapse" data-bs-target = "#navbarSupportedContent" aria-controls = "navbarSupportedContent" aria-expanded = "false" aria-label = "Toggle navigation"><span class = "navbar-toggler-icon"></span></button><div class = "collapse navbar-collapse" id = "navbarSupportedContent"><ul class = "navbar-nav me-auto mb-2 mb-lg-0"><li class = "nav-item py-1 col-12 col-lg-auto"><div class = "vr d-none d-lg-flex h-100 mx-lg-2 text-black"></div><hr class = "d-lg-none text-black-50"></li><li class = "nav-item"><a class = "nav-link active" aria-current = "page" href = "enorindex.html"> Main </a></li><li class = "nav-item"><a class = "nav-link active" aria-current = "page" href = "enorqueue-errors.html"> Q-Errors </a></li><li class = "nav-item"><a class = "nav-link active" aria-current = "page" href = "enorurichanges.html"> URI Changes </a></li><li class = "nav-item"><a class = "nav-link active" aria-current = "page" href = "enorduplicates.html"> Duplicates </a></li><li class = "nav-item dropdown"><a class = "nav-link dropdown-toggle" role = "button" data-bs-toggle = "dropdown" aria-expanded = "false"> Violet Updates</a><ul class = "dropdown-menu"><li><a class = "dropdown-item" href = "enoradditions.html"> Additions </a></li><li><a class = "dropdown-item" href = "enordeletions.html"> Deletions </a></li></ul></li><li class = "nav-item dropdown"><a class = "nav-link dropdown-toggle" aria-current = "page" role = "button" data-bs-toggle = "dropdown"<aria-expanded = "false" > Last Played </a><ul class = "dropdown-menu"><li><a class = "dropdown-item" href = "enorplayed-epm.html"> Played[EPM] </a> </li><li><a class = "dropdown-item" href = "enorplayed-ext.html"> Played[EXT] </a></li></ul></li><li class = "nav-item py-1 col-12 col-lg-auto"><div class = "vr d-none d-lg-flex h-100 mx-lg-2 text-black"></div><hr class = "d-lg-none text-black-50"><li class = "nav-item"><a class = "nav-link active" aria-current = "page" href = "https://www.python.org" target = "_blank"> PYTHON </a></li><li class = "nav-item"><a class = "nav-link active" aria-current = "page" href = "https://www.sqlite.org" target = "_blank"> SQLITE3 </a></li><li class = "nav-item"><a class = "nav-link active" aria-current = "page" href = "https://developer.spotify.com/documentation/web-api/reference/#/" target = "_blank"> SPOTIFY WEB API </a></li><li class = "nav-item"><a class = "nav-link active" aria-current = "page" href = "https://html.com/" target = "_blank"> HTML </a></li><li class = "nav-item py-1 col-12 col-lg-auto"><div class = "vr d-none d-lg-flex h-100 mx-lg-2 text-black"></div><hr class = "d-lg-none text-black-50"></li ><li class = "nav-item"><span class = "navbar-text"><h3><i> E. P. M. S. </i></h3></span></li></ul></div></div></nav></header>'
    head = "<head><meta charset='UTF-8'><title>Endless Playlist Management System</title><link href='https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/css/bootstrap.min.css' rel='stylesheet' integrity='sha384-Zenh87qX5JnK2Jl0vWa8Ck2rdkQ2Bzep5IDxbcnCeuOxjzrPF/et3URy9Bv1WTRi' crossorigin='anonymous'><script src='https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/js/bootstrap.bundle.min.js' integrity='sha384-OERcA2EqjJCMA+/3y+gxIOqMEjwtxJY7qPCqsdltbNJuaOe923+mo//f6V8Qbsw3' crossorigin='anonymous'></script></head>"

    main = head + header1 + header2

    ## NOTE: [1] Last Run Additions.
    sql = ("SELECT * FROM Last_Run_Additions")    
    curr = conn.cursor()
    curr.execute(sql)
    rows = curr.fetchall()
    
    additiondata = "Last Run Additions: " + date_time
    body1 = '<body><div id = "intro" class = "bg-image shadow-2-strong"><div class = "container"><table class = "table" ><table border = "0" align = "center" cellpadding = "50" cellspacing = "15"><tr><td><table border = "1" bgcolor = "64b8c0" align = "center" cellpadding = "3" cellspacing = "3"><tr>'
    body2 = '<td><b> Remaining Unplayed Tracks = ' + unplayed + '</b></td></tr></table></td></tr></table></table>'
    body3 = '<table class = "table"><table border = "0" align="center" cellpadding="3" cellspacing="15"><tr><td>'
    body4 = '<table border = "1" bgcolor="64b8c0" align="center" cellpadding="3" cellspacing="3"><tr><td><b>' + additiondata + '</b></td></tr></table></td></tr></table></table>'
    body5 = '<table class = "table table-bordered"><thead><tr bgcolor = "64b8c0"><th> # </th><th> URI</th><th> TRACK</th><th> ARTIST</th><th> ALBUM</th></tr></thead><tbody>'
    
    tablebody1 = ""
    tr = 1
    for row in rows:
        if tr & 1:
            bgcolor = "#ffffff"
        else:
            bgcolor = "#dfdfdf"
        tablebody1 = tablebody1 + '<tr bgcolor=' + bgcolor + '><td>' + str(tr) + '</td><td><a href=' + str(row[0]) + '>' + str(row[0]) + '</a></td><td>' + str(row[1]) + '</td><td>' + str(row[2]) + '</td><td>' + str(row[3]) + '</td></tr>'
        tr = tr + 1
    tablebody2 = '</tbody></table></div></div></body>'
    html = main + body1 + body2 + body3 + body4 + body5 + tablebody1 + tablebody2

    file_name = "enoradditions.html"
    with open(os.path.join(file_path, file_name), 'w') as fp:        
        fp.write(html)

    ##==========================================================================

    ## NOTE: [2] Last Run Deletions.
    sql = ("SELECT * FROM Last_Run_Deletions")    
    curr = conn.cursor()
    curr.execute(sql)
    rows = curr.fetchall()

    deletiondata = "Last Run Deletions: " + date_time
    body4 = '<table border = "1" bgcolor="64b8c0" align="center" cellpadding="3" cellspacing="3"><tr><td><b>' + deletiondata + '</b></td></tr></table></td></tr></table></table>'
    tablebody1 = ""
    tr = 1
    for row in rows:
        if tr & 1:
            bgcolor = "#ffffff"
        else:
            bgcolor = "#dfdfdf"
        tablebody1 = tablebody1 + '<tr bgcolor=' + bgcolor + '><td>' + str(tr) + '</td><td><a href=' + str(row[0]) + '>' + str(row[0]) + '</a></td><td>' + str(row[1]) + '</td><td>' + str(row[2]) + '</td><td>' + str(row[3]) + '</td></tr>'
        tr = tr + 1
    tablebody2 = '</tbody></table></div></div></body>'
    html = main + body1 + body2 + body3 + body4 + body5 + tablebody1 + tablebody2

    file_name = "enordeletions.html"
    with open(os.path.join(file_path, file_name), 'w') as fp:        
        fp.write(html)

    ##==========================================================================

    ## NOTE: [3] Duplicated tracks.
    sql = ("SELECT * FROM New_Dupe_Tracks WHERE REVIEWED = 0")    
    curr = conn.cursor()
    curr.execute(sql)
    rows = curr.fetchall()

    dupedata = "Duplicated Tracks: " + date_time
    body4 = '<table border = "1" bgcolor="64b8c0" align="center" cellpadding="3" cellspacing="3"><tr><td><b>' + dupedata + '</b></td></tr></table></td></tr></table></table>'
    body5 = '<table class = "table table-bordered"><thead><tr bgcolor = "64b8c0"><th> # </th><th> URI</th><th> TRACK</th><th> ARTIST</th><th> REVIEWED</th></tr></thead><tbody>'
    tablebody1 = ""
    tr = 1
    for row in rows:
        if tr & 1:
            bgcolor = "#ffffff"
        else:
            bgcolor = "#dfdfdf"
        tablebody1 = tablebody1 + '<tr bgcolor=' + bgcolor + '><td>' + str(tr) + '</td><td><a href=' + str(row[0]) + '>' + str(row[0]) + '</a></td><td>' + str(row[1]) + '</td><td>' + str(row[2]) + '</td><td>' + str(row[3]) + '</td></tr>'
        tr = tr + 1
    tablebody2 = '</tbody></table></div></div></body>'
    html = main + body1 + body2 + body3 + body4 + body5 + tablebody1 + tablebody2

    file_name = "enorduplicates.html"
    with open(os.path.join(file_path, file_name), 'w') as fp:        
        fp.write(html)

    ##==========================================================================

    ## NOTE: [4] URI Changes.
    sql = ("SELECT * FROM URI_Change WHERE DATE_OF_CHANGE LIKE '%" + yearis + "%' ORDER by DATE_OF_CHANGE DESC LIMIT 100")
    curr = conn.cursor()
    curr.execute(sql)
    rows = curr.fetchall()

    uridata = "URI Changes: " + date_time
    body4 = '<table border = "1" bgcolor="64b8c0" align="center" cellpadding="3" cellspacing="3"><tr><td><b>' + uridata + '</b></td></tr></table></td></tr></table></table>'
    body5 = '<table class = "table table-bordered"><thead><tr bgcolor = "64b8c0"><th> # </th><th> DATE</th><th> OLD URI</th><th> NEW URI</th><th> TRACK</th><th> ARTIST</th></tr></thead><tbody>'
    tablebody1 = ""
    tr = 1
    for row in rows:
        if tr & 1:
            bgcolor = "#ffffff"
        else:
            bgcolor = "#dfdfdf"
        tablebody1 = tablebody1 + '<tr bgcolor=' + bgcolor + '><td>' + str(tr) + '</td><td nowrap>' + str(row[5]) + '</td><td><a href=' + str(row[1]) + '>' + str(row[1]) + '</a></td><td><a href=' + str(row[2]) + '>' + str(row[2]) + '</a></td><td>' + str(row[3]) + '</td><td>' + str(row[4]) + '</td></tr>'
        tr = tr + 1
    tablebody2 = '</tbody></table></div></div></body>'
    html = main + body1 + body2 + body3 + body4 + body5 + tablebody1 + tablebody2

    file_name = "enorurichanges.html"
    with open(os.path.join(file_path, file_name), 'w') as fp:        
        fp.write(html)

    ##==========================================================================

    ## NOTE: [5a] Last_Played_Tracks (EPM)
    sql = ("SELECT * FROM Last_Played_Tracks WHERE PLAY_COUNT != -100 AND LAST_PLAYED LIKE '%" + yearis + "%' ORDER BY LAST_PLAYED DESC LIMIT 100")
    curr = conn.cursor()
    curr.execute(sql)
    rows = curr.fetchall()

    epmdata = "Last Played Tracks (EPM): " + date_time
    body4 = '<table border = "1" bgcolor="64b8c0" align="center" cellpadding="3" cellspacing="3"><tr><td><b>' + epmdata + '</b></td></tr></table></td></tr></table></table>'
    body5 = '<table class = "table table-bordered"><thead><tr bgcolor = "64b8c0"><th> # </th><th> URI</th><th> TRACK</th><th> ARTIST</th><th> PLAYED</th></tr></thead><tbody>'
    tablebody1 = ""
    tr = 1
    for row in rows:
        if tr & 1:
            bgcolor = "#ffffff"
        else:
            bgcolor = "#dfdfdf"
        tablebody1 = tablebody1 + '<tr bgcolor=' + bgcolor + '><td>' + str(tr) + '</td><td><a href=' + str(row[0]) + '>' + str(row[0]) + '</a></td><td>' + str(row[1]) + '</td><td>' + str(row[2]) + '</td><td nowrap>' + str(row[5]) + '</td></tr>'
        tr = tr + 1
    tablebody2 = '</tbody></table></div></div></body>'
    html = main + body1 + body2 + body3 + body4 + body5 + tablebody1 + tablebody2

    file_name = "enorplayed-epm.html"
    with open(os.path.join(file_path, file_name), 'w') as fp:        
        fp.write(html)

    ##==========================================================================

    ## NOTE: [5b] Last_Played_Tracks (EXT)
    sql = ("SELECT * FROM Last_Played_Tracks WHERE PLAY_COUNT = -100 AND LAST_PLAYED LIKE '%" + yearis + "%' ORDER BY LAST_PLAYED DESC LIMIT 100")
    curr = conn.cursor()
    curr.execute(sql)
    rows = curr.fetchall()

    extdata = "Last Played Tracks (EXT): " + date_time
    body4 = '<table border = "1" bgcolor="64b8c0" align="center" cellpadding="3" cellspacing="3"><tr><td><b>' + extdata + '</b></td></tr></table></td></tr></table></table>'
    body5 = '<table class = "table table-bordered"><thead><tr bgcolor = "64b8c0"><th> # </th><th> URI</th><th> TRACK</th><th> ARTIST</th><th> PLAYED</th></tr></thead><tbody>'
    tablebody1 = ""
    tr = 1
    for row in rows:
        if tr & 1:
            bgcolor = "#ffffff"
        else:
            bgcolor = "#dfdfdf"
        tablebody1 = tablebody1 + '<tr bgcolor=' + bgcolor + '><td>' + str(tr) + '</td><td><a href=' + str(row[0]) + '>' + str(row[0]) + '</a></td><td>' + str(row[1]) + '</td><td>' + str(row[2]) + '</td><td nowrap>' + str(row[5]) + '</td></tr>'
        tr = tr + 1
    tablebody2 = '</tbody></table></div></div></body>'
    html = main + body1 + body2 + body3 + body4 + body5 + tablebody1 + tablebody2

    file_name = "enorplayed-ext.html"
    with open(os.path.join(file_path, file_name), 'w') as fp:        
        fp.write(html)

    ##==========================================================================

    ## NOTE: [6] Queue Check.
    sql = ("SELECT * FROM Queue_check")  
    curr = conn.cursor()
    curr.execute(sql)
    rows = curr.fetchall()
 
    queuedata = "Queue Errors: " + date_time
    body4 = '<table border = "1" bgcolor="64b8c0" align="center" cellpadding="3" cellspacing="3"><tr><td><b>' + queuedata + '</b></td></tr></table></td></tr></table></table>'
    body5 = '<table class = "table table-bordered"><thead><tr bgcolor = "64b8c0"><th> # </th><th> URI</th><th> TRACK</th><th> ARTIST</th><th> ALBUM</th></tr></thead><tbody>'
    tablebody1 = ""
    tr = 1
    for row in rows:
        if tr & 1:
            bgcolor = "#ffffff"
        else:
            bgcolor = "#dfdfdf"
        tablebody1 = tablebody1 + '<tr bgcolor=' + bgcolor + '><td>' + str(tr) + '</td><td><a href=' + str(row[0]) + '>' + str(row[0]) + '</a></td><td>' + str(row[1]) + '</td><td>' + str(row[2]) + '</td><td>' + str(row[3]) + '</td></tr>'
        tr = tr + 1
    tablebody3 = '</tbody></table></div></div></body>'
    html = main + body1 + body2 + body3 + body4 + body5 + tablebody1 + tablebody2

    file_name = "enorqueue-errors.html"
    with open(os.path.join(file_path, file_name), 'w') as fp:        
        fp.write(html)

##==========================================================================

    ## NOTE: [7] Index
    indexpage = ""
    sql = ("SELECT * FROM Queue_preview")
    curr = conn.cursor()
    curr.execute(sql)
    rows = curr.fetchall()

    lastrun = "Latest Run: " + date_time
    body6 = '<div id="intro" class="bg-image shadow-2-strong"><div class = "container-fluid"><h2 style = "text-align:center;">ENDLESS PLAYLIST MANAGEMENT SYSTEM</h2><h3>Endless Queue - 50 Upcoming Tunes</h3><h5><i>' + lastrun + '</i></h5>'

    html = main + body6

    for row in rows:
        induri = str(row[0])
        indname = str(row[1])
        indpic = str(row[2])
        indaud = str(row[3])
        indurilist.append(induri)
        indnamelist.append(indname)
        indpiclist.append(indpic)
        indaudlist.append(indaud)

    while indrow < 5:
        indexpage = indexpage + '<div class="row mb-1">'
        while indrowitem < 10:
            indrowcount = indrowitem + inditemcount
            indexpage = indexpage + '<div class="col-sm" title="' + indnamelist[indrowcount] + '"><img src="'
            indexpage = indexpage + indpiclist[indrowcount] + '" width="100" height="100" class="rounded mb-1" alt="Image2"><audio controls><source src="' + indaudlist[indrowcount] + '"></audio></div>'
            indrowitem+=1
        indrow+=1
        inditemcount +=10
        indrowitem = 0
        indexpage = indexpage + '</div>'
        indend = '</div></div></div>'

    html = html + indexpage + indend

    file_name = "enorindex.html"
    with open(os.path.join(file_path, file_name), 'w') as fp:
        fp.write(html)

except Error as e:       
    print(e)  
finally:
    if conn:
        conn.close()
print('Completed.')