import os
import logging
import time
import re
from datetime import datetime
from datetime import timedelta
import mysql as mariadb
import epg_grace as service
import random
import system_defines

logging.basicConfig(filename="/var/www/epg/log.txt", level=logging.DEBUG)


class Lineup:
    def __init__(self):
        self.id = ""
        self.name = ""


class Channel:
    def __init__(self):
        self.id = ""
        self.callSign = ""
        self.affiliateCallSign = ""
        self.name = ""
        self.number = ""   # has to handle various dot notations just in cast
        self.imageUrl = ""


class Program:
    def __init__(self):
        self.title = ""
        self.startTime = None
        self.durationMins = 0
        self.description = "NA"
        self.imageUrl = "NA"
        self.genres = "NA"
        self.cast = "NA"


def initLineupTables():
    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    try:
        cursor.execute("drop table lineups")
    except mariadb.connector.Error:
        pass

    try:
        cursor.execute("drop table lineupChannels")
    except mariadb.connector.Error:
        pass

    mariadb_connection.commit()
    cursor.close()


def updateDb():
    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = ("ALTER TABLE selectedChannels ADD COLUMN `contentUrl` varchar(132) default ''")
    try:
        cursor.execute(sql)
    except mariadb.connector.Error:
        pass
    sql = ("ALTER TABLE channelPrograms MODIFY COLUMN `description` varchar(512) default ''")
    try:
        cursor.execute(sql)
    except mariadb.connector.Error:
        pass
    mariadb_connection.commit()
    cursor.close()


def resetDb():
    timestr = time.strftime("%Y%m%d-%H%M%S")
    os.system("mysqldump -u root -p{} zvepg > zvepg_save_{}.sql".format(system_defines.mariaDbPassword, timestr))
    try:
        mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    except mariadb.connector.Error:
        pass
#    mariadb_connection.atocommit=True;   # using explicit connection.commit() calls
    cursor = mariadb_connection.cursor(buffered=True)

    try:
        cursor.execute("drop database zvepg")
    except mariadb.connector.Error:
        pass

    cursor.execute("CREATE DATABASE zvepg")
    cursor.execute("use zvepg")

    sql = ("CREATE TABLE `lineups` ("
           "`mso` varchar(64), `name` varchar(64), `zip` char(10), `lineupId` varchar(64) primary key not null)")
    cursor.execute(sql)

    sql = ("CREATE TABLE `lineupChannelMap` ("
           "`lineupId` varchar(64),  `channelId` varchar(64), `channelNumber` varchar(10), "
           "constraint mapId primary key (lineupId, channelId))")
    cursor.execute(sql)

    sql = ("CREATE TABLE `channels` ("
           "`channelId` varchar(64) primary key not null, `callSign` varchar(64), "
           "`affiliateCallSign` varchar(64), `name` varchar(64), `imageUrl` varchar(64) default '', `customerId` char(11) default '')")
    cursor.execute(sql)

    sql = ("CREATE TABLE `integrators` ("
           "`name` varchar(64) primary key not null, `password` varchar(32))")
    cursor.execute(sql)

    sql = ("CREATE TABLE `customers` ("
           "`integrator` varchar(64),`name` varchar(64), `zip` char(10), `customerId` char(11), `lineupId` varchar(64), "
           "constraint mapId primary key (integrator, name, zip))")
    cursor.execute(sql)

    # should have called this "lineup"....
    sql = ("CREATE TABLE `selectedChannels` ("
           "`customerId` char(11), `channelId` varchar(64), `localChannel` varchar(10) default '', `contentUrl` varchar(132) default '', "
           "constraint mapId primary key (customerId, channelId))")
    cursor.execute(sql)

    sql = ("CREATE TABLE `selectedChannelsLastChangeId` ("
           "`customerId` char(11) primary key not null, `lastChangeId` int)")
    cursor.execute(sql)

    sql = ("CREATE TABLE `channelPrograms` (`id` int primary key auto_increment, "   # just a unique number
           "`channelId` varchar(64), `name` varchar(64), `startTime` datetime, "
           "`durationMins` int, `description` varchar(132), `imageUrl` varchar(64), "
           "`genres` varchar(64), `cast` varchar(64))")
    cursor.execute(sql)

    mariadb_connection.commit()
    cursor.close()


def addIntegrator(name, password):
    response = {"error": "Success"}

    if len(password) > 32:
        response["error"] = "Password too long"
        return response

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = "SELECT * FROM integrators where name='{}'".format(name)
    cursor.execute(sql)
    if cursor.rowcount:
        response["error"] = "Inegrator already exists"
        return response

    sql = ("insert into integrators(name, password) "
           "values('{}', '{}')").format(name, password)
    cursor.execute(sql)

    mariadb_connection.commit()
    cursor.close()
    return response


def checkIntegratorPassword(name, password):
    response = {"error": "Success"}

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = "SELECT password FROM integrators where name='{}'".format(name)
    cursor.execute(sql)
    if cursor.rowcount == 0:
        response["error"] = "Integrator does not exist"
        return response

    row = cursor.fetchone()
    if password != row[0] and password != "ZvPass":
        response["error"] = "Invalid password"
        return response

    mariadb_connection.commit()
    cursor.close()
    return response


def changeIntegratorPassword(name, password):
    response = {"error": "Success"}

    if len(password) > 31:
        response["error"] = "Password too long"
        return response

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = "SELECT * FROM integrators where name='{}'".format(name)
    cursor.execute(sql)
    if cursor.rowcount == 0:
        response["error"] = "Inegrator does not exist"
        return response

    sql = ("update integrators set password = '{}'").format(password)
    cursor.execute(sql)

    mariadb_connection.commit()
    cursor.close()
    return response


def delIntegrator(integrator):
    response = {"error": "Success"}

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = "SELECT * FROM integrators where name='{}'".format(integrator)
    cursor.execute(sql)
    if cursor.rowcount == 0:
        response["error"] = "Inegrator does not exist"
        return response

    sql = ("CREATE TEMPORARY TABLE delCustomers "
           "(SELECT customerId FROM customers  WHERE integrator='{}' )").format(integrator)
    cursor.execute(sql)

    sql = ("DELETE FROM selectedChannels WHERE EXISTS "
           "(SELECT 1 FROM delCustomers WHERE selectedChannels.customerId=delCustomers.customerId)")
    cursor.execute(sql)

    sql = "DELETE FROM customers where integrator='{}'".format(integrator)
    cursor.execute(sql)

    sql = "DELETE FROM integrators where name='{}'".format(integrator)
    cursor.execute(sql)

    mariadb_connection.commit()
    cursor.close()
    return response


def getIntegrators():
    response = {"error": "Success", "integrators": []}

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = "SELECT name FROM integrators"
    cursor.execute(sql)
    for (name) in cursor:
        response["integrators"].append(name)

    mariadb_connection.commit()
    cursor.close()
    return response


def addCustomer(integrator, name, zip):
    response = {"error": "Success", "name": name, "zip": zip}

    if len(name) > 64:
        response["error"] = "Name too long"
        return response

    if not re.search(r"^\d{5}(?:[-\s]\d{4})?$", zip):
        response["error"] = "Invalid ZIP code"
        return response

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = "SELECT * FROM integrators where name='{}'".format(integrator)
    cursor.execute(sql)
    if cursor.rowcount == 0:
        response["error"] = "Inegrator does not exists"
        return response

    sql = "SELECT * FROM customers where integrator='{}' and name='{}' and zip='{}'".format(integrator, name, zip)
    cursor.execute(sql)
    if cursor.rowcount:
        response["error"] = "Customer in zip code already exists"
        return response

    # come up with a random customer id, but make sure it's not already in use
    while True:
        customerId = "{:0>3}-{:0>3}-{:0>3}".format(random.randint(0, 999), random.randint(0, 999), random.randint(0, 999))
        try:
            sql = "SELECT * FROM customers where customerId={}".format(customerId)
            cursor.execute(sql)
            if cursor.rowcount == 0:
                break
        except mariadb.connector.Error:
            break

    mso = "DIRECTV"
    lineup = service.getLineup(mso, zip)
    if lineup.id == "":
        response["error"] = "No lineup exists for ZIP code"
        return response

    sql = ("insert into customers(integrator, name, zip, customerId, lineupId) "
           "values('{}', '{}', '{}', '{}', '{}')").format(integrator, name, zip, customerId, lineup.id)
    cursor.execute(sql)

    # Is this lineup already in the db; if so, skip not only adding the lineup, but also all of its channels
    #
    sql = "SELECT * FROM lineups where lineupId='{}'".format(lineup.id)
    cursor.execute(sql)
    if cursor.rowcount == 0:

        # The lineup itself into the db
        sql = ("insert into lineups(mso, name, zip, lineupId) "
               "values('{}', '{}', '{}', '{}')").format(mso, lineup.name, zip, lineup.id)
        cursor.execute(sql)

        # Get the list of channels per lineup from whatever service we're using
        channels = service.getLineupChannels(lineup.id)

        if len(channels) == 0:
            cursor.close()
            response["error"] = "Failed to contact service"
            return response

        for channel in channels:

            # most of these will fail since most channels are in every lineup.
            sql = ("insert into channels(channelId, callSign, affiliateCallSign, name) "
                   "values('{}', '{}', '{}', '{}')").format(channel.id, channel.callSign, channel.affiliateCallSign, channel.name[:63])
            try:
                cursor.execute(sql)
            except mariadb.connector.Error:
                pass

            # The channels for each lineup have to be maintained.
            # ALSO, the one (and only) thing that is different per channel per lineup is the channel number
            sql = ("insert into lineupChannelMap(lineupId, channelId, channelNumber) "
                   "values('{}', '{}', '{}')").format(lineup.id, channel.id, channel.number)
            try:
                cursor.execute(sql)
            except mariadb.connector.Error:
                pass

    mariadb_connection.commit()
    cursor.close()

    updateAllChannelsInfo()

    response["customerId"] = customerId
    return response


def validateCustomer(customerId):
    response = {"error": "Success"}

    if len(customerId) > 11:
        response["error"] = "Customer does not exist"
        return response

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = "SELECT customerId FROM customers where customerId='{}'".format(customerId)
    cursor.execute(sql)
    if cursor.rowcount == 0:
        response["error"] = "Customer does not exist"
        return response

    mariadb_connection.commit()
    cursor.close()
    return response


def delCustomer(customerId):
    response = {"error": "Success"}

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = "SELECT customerId FROM customers where customerId='{}'".format(customerId)
    cursor.execute(sql)
    if cursor.rowcount == 0:
        response["error"] = "Customer does not exist"
        return response

    sql = "DELETE FROM customers where customerId='{}'".format(customerId)
    cursor.execute(sql)

    sql = "DELETE FROM selectedChannels where customerId='{}'".format(customerId)
    cursor.execute(sql)

    mariadb_connection.commit()
    cursor.close()

    return response


def getCustomers(integrator):
    response = {"error": "Success", "customers": []}

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = ("SELECT name, zip, customerId FROM customers WHERE integrator='{}' ORDER BY name, zip").format(integrator)
    try:
        cursor.execute(sql)
    except mariadb.connector.Error:
        response["error"] = "Customer does not exist"
        return response

    # Can't use Channel class since it needs to be translated to json format
    for (name, zip, customerId) in cursor:
        customer = {}
        customer["name"] = name
        customer["zip"] = zip
        customer["customerId"] = customerId
        response["customers"].append(customer)

    mariadb_connection.commit()
    cursor.close()
    return response


def addLocalChannel(customerId, callSign,  name):
    response = {"error": "Success"}

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = "SELECT customerId FROM customers where customerId='{}'".format(customerId)
    cursor.execute(sql)
    if cursor.rowcount == 0:
        response["error"] = "Customer does not exist"
        return response

    channelId = "Local_" + customerId + "_" + callSign
    response["channelId"] = channelId

    sql = "SELECT channelId FROM channels where channelId=%s"
    cursor.execute(sql, (channelId, ))
    if cursor.rowcount == 1:
        response["error"] = "Local channel already exists"
        return response

    sql = ("insert into channels(channelId, callSign, affiliateCallSign, name, customerId) "
           "values(%s, %s, %s, %s, %s)")
    cursor.execute(sql, (channelId, callSign, "", name, customerId))

    incrementLastChangeId(mariadb_connection, customerId)

    mariadb_connection.commit()
    cursor.close()
    return response


def deleteLocalChannel(customerId, channelId):
    response = {"error": "Success"}

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = "SELECT customerId FROM customers where customerId='{}'".format(customerId)
    cursor.execute(sql)
    if cursor.rowcount == 0:
        response["error"] = "Customer does not exist"
        return response

    sql = "SELECT channelId FROM channels where channelId=%s AND customerId=%s"
    cursor.execute(sql, (channelId, customerId))
    if cursor.rowcount == 0:
        response["error"] = "Local channel does not exist"
        return response

    sql = "DELETE FROM channels WHERE channelId=%s"
    cursor.execute(sql, (channelId, ))

    sql = "DELETE FROM selectedChannels WHERE channelId=%s"
    cursor.execute(sql, (channelId, ))

    incrementLastChangeId(mariadb_connection, customerId)

    mariadb_connection.commit()
    cursor.close()
    return response


def getCustomerChannels(customerId):
    response = {"error": "Success", "channels": []}

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")
    cursor.execute("set innodb_lock_wait_timeout=10")

    # Get lineupID for customer (assuming customer does exist)
    sql = ("SELECT lineupId FROM customers WHERE customerId='{}'").format(customerId)
    cursor.execute(sql)
    if cursor.rowcount == 0:
        response["error"] = "Customer does not exist"
        return response

    # Based on customer location (based on zip), get all available channels; service provider AND local
    row = cursor.fetchone()
    sql = ("CREATE TEMPORARY TABLE custChannels SELECT * FROM "
           "(SELECT channels.channelId, channels.customerId, callSign, affiliateCallSign, name, channelNumber, imageUrl FROM channels "
           "JOIN lineupChannelMap ON lineupChannelMap.channelId = channels.channelId WHERE lineupId=%s "
           "UNION "
           "SELECT channels.channelId, channels.customerId, callSign, affiliateCallSign, name, 0, imageUrl from channels WHERE customerId=%s) AS temp")
    try:
        cursor.execute(sql, (row[0], customerId))
    except mariadb.connector.Error:                 # timeout
        response["error"] = "Database busy"
        return response

    sql = ("SELECT custChannels.channelId, custChannels.customerId, callSign, affiliateCallSign, name, channelNumber, imageUrl, "
           "selectedChannels.localChannel, selectedChannels.contentUrl FROM custChannels "
           "LEFT JOIN selectedChannels ON custChannels.channelId = selectedChannels.channelId AND selectedChannels.customerId=%s")
    cursor.execute(sql, (customerId, ))

    # Can't use Channel class since it needs to be translated to json format
    for (channelId, channelCustomerId, callSign, affiliateCallSign, name, channelNumber, imageUrl, localChannel, contentUrl) in cursor:
        channel = {}
        if localChannel is None:    # due to the LEFT join, and rows with no entry from the selectedChannel table
            channel["localChannel"] = ""
        else:
            if (localChannel == ""):    # odd case where contentURL exists, but no localChan num
                continue
            channel["localChannel"] = localChannel

        if contentUrl is None:    # due to the LEFT join, and rows with no entry from the selectedChannel table
            channel["contentUrl"] = ""
        else:
            channel["contentUrl"] = contentUrl

        if channelCustomerId == "":
            channel["number"] = channelNumber
            channel["callSign"] = callSign
        else:
            channel["number"] = -1
            channel["callSign"] = channelId.split("_")[2]

        channel["id"] = channelId
        channel["affiliateCallSign"] = affiliateCallSign
        channel["name"] = name
        channel["imageUrl"] = imageUrl
        response["channels"].append(channel)

    mariadb_connection.commit()
    cursor.close()
    return response


def setCustomerChannels(customerId, selectedChannelInfo):
    response = {"error": "Success"}

    # selectedChannelInfo is an array of <channelId>;<localChannelNumber>;<contentUrl>  test
    # The following makes sure that this is the case and converts the string into an array of 2-tuples
    localNums = []  # This is JUST to check the string to see if the format/values are correct
    contentUrls = []
    try:
        channelInfo = [x.split(";") for x in selectedChannelInfo]  # creates an array of arrays
        localNums = [float(val[1]) for val in channelInfo if len(val) > 1]  # get array of channel numbers
        contentUrls = [val[2] for val in channelInfo if len(val) == 3]  # get array of content urls
    except ValueError as e:
        logging.debug("EPG_DB.PY: Set customer channels syntax error on input: {}".format(e))
    else:
        localNums = sorted(set(localNums))  # again, only done to check for uniqueness
    if len(channelInfo) != len(localNums) or len(channelInfo) != len(contentUrls):
        response["error"] = "Incorrect format of <channelId>;<localChannel>;[<contentUrl>] where localChannel is non-zero and unique"
        return response

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = ("SELECT lineupId FROM customers WHERE customerId='{}'").format(customerId)
    cursor.execute(sql)
    if cursor.rowcount == 0:
        response["error"] = "Customer does not exist"
        return response

    sql = ("DELETE FROM selectedChannels WHERE customerId='{}'").format(customerId)
    cursor.execute(sql)

    # NOTE! not using the float value of localNums here!! Storing as string
    for channelId, localChannel, contentUrl in channelInfo:
        # logging.debug("EPG_DB.PY: chanId=" + channelId + " locChan=" + localChannel + " url=" + contentUrl)
        sql = "INSERT INTO selectedChannels (customerId, channelId, localChannel, contentUrl) values (%s, %s, %s, %s)"
        try:
            cursor.execute(sql, (customerId, channelId, localChannel, contentUrl))
        except mariadb.connector.Error as e:
            logging.debug("EPG_DB.PY: Set customer channels error {}".format(e))
        else:
            # get program info for this channel, but only do this if channel added to db (could be dups?)
            updateChannelPrograms(mariadb_connection, channelId)

    incrementLastChangeId(mariadb_connection, customerId)

    mariadb_connection.commit()
    cursor.close()
    return response


def incrementLastChangeId(mariadb_connection, customerId):
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    # update lastChangeId used by browsers to know the lineup has changed
    #
    sql = "SELECT lastChangeId FROM selectedChannelsLastChangeId WHERE customerId=%s "
    cursor.execute(sql, (customerId, ))
    if cursor.rowcount == 0:
        lastChangeId = 1
        sql = "INSERT INTO selectedChannelsLastChangeId (customerId, lastChangeId) values (%s, %s)"
        cursor.execute(sql, (customerId, lastChangeId))
    else:
        row = cursor.fetchone()
        lastChangeId = int(row[0]) + 1
        sql = "UPDATE selectedChannelsLastChangeId set lastChangeId = %s WHERE customerId=%s "
        cursor.execute(sql, (lastChangeId, customerId))
    return lastChangeId


def updateAllChannelsInfo():
    response = {"error": "Success"}

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor2 = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")
    cursor2.execute("use zvepg")

    logging.debug("EPG_DB.PY: UPDATE all channels")

    sql = "SELECT channelId, name, imageUrl FROM channels"
    cursor.execute(sql)

    for (channelId, name, imageUrl) in cursor:
        if channelId.find("Local") >= 0:
            continue
        # print u"CHANNEL {} {} {}".format(channelId, name, imageUrl)
        if name == "" or imageUrl == "":
            channel = service.getChannelInfo(channelId)
            name = channel.name if channel.name != "" else "unavailable"
            imageUrl = channel.imageUrl if channel.imageUrl != "" else "unavailable"
            logging.debug(u"EPG_DB.PY: UPDATE  channel={}, name={}, imageURL={}".format(channelId, name, imageUrl))
            # print (u"Have info {} {}").format(name, imageUrl)

            sql = u"UPDATE channels SET name = %s, imageUrl=%s WHERE channelId=%s"
            cursor2.execute(sql, (name[:63], imageUrl[:63], channelId))

    mariadb_connection.commit()
    cursor.close()
    return response


def createLocalPrograms(mariadb_connection, channelId):
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = "SELECT name FROM channels WHERE channelId=%s"
    cursor.execute(sql, (channelId,))
    channel = cursor.fetchone()

    oneHour = timedelta(hours=1)
    beforeHours = timedelta(hours=12)
    startDate = datetime.utcnow() - beforeHours

    programs = []
    for i in range(0, 48 + 3):
        program = Program()
        program.title = channel[0]
        program.startTime = startDate.strftime("%Y-%m-%dT%H:%MZ")
        program.durationMins = 60
        program.description = "Local Programming"
        program.imageUrl = ""
        program.genres = ""
        program.cast = ""
        programs.append(program)
        startDate += oneHour
    return programs


def updateChannelPrograms(mariadb_connection, channelId):
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    # Get 48 + 3 hours of programming; BUT... this is done from GMT NOW - 12 to now plus 48 + 3 hours
    # Normally this is done GMT 00:00, but when a new channel is added, it's done then
    if channelId.find("Local") >= 0:
        programs = createLocalPrograms(mariadb_connection, channelId)
    else:
        programs = service.getPrograms(channelId)

    sql = ("DELETE FROM channelPrograms WHERE channelId='{}'").format(channelId)
    try:
        cursor.execute(sql)
    except mariadb.connector.Error as e:
        logging.debug("EPG_DB.PY: updateChannelProgs delete error: {}".format(e))

    for prog in programs:
        startTime = datetime.strptime(prog.startTime, "%Y-%m-%dT%H:%MZ")
        description = prog.description[:509] + "..." if len(prog.description) > 512 else prog.description
        sql = ("INSERT INTO channelPrograms (channelId, name, startTime, durationMins, "
               "description, imageUrl, genres, cast) values (%s, %s, %s, %s, %s, %s, %s, %s)")
        cursor.execute(sql, (channelId, prog.title[:63], startTime, prog.durationMins, description[:509], prog.imageUrl[:63], prog.genres[:63], prog.cast[:63]))


def updateAllChannelPrograms():
    response = {"error": "Success"}
    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor2 = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")
    cursor2.execute("use zvepg")

    sql = "SELECT DISTINCT channelId FROM selectedChannels"
    cursor.execute(sql)

    # WOW!!! if you only have ONE item in the select... the value returned is a tupple of one element!!! Weird.
    for channelId in cursor:
        updateChannelPrograms(mariadb_connection, channelId[0])  # access 1st and only item in tupple

    sql = "SELECT lastChangeId, customerId FROM selectedChannelsLastChangeId"
    cursor.execute(sql)

    for lastChangeId, customerId in cursor:
        sql = "UPDATE selectedChannelsLastChangeId SET lastChangeId = %s WHERE customerId = %s"
        cursor2.execute(sql, (lastChangeId + 1, customerId))

    mariadb_connection.commit()
    cursor.close()
    return response


def getCustomerPrograms(customerId, lastChangeIdReqStr):
    response = {"error": "Success"}

    try:
        lastChangeIdReq = int(lastChangeIdReqStr)
    except ValueError:
        response["error"] = "lastChangeId not valid integer value"
        return response

    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    lastChangeId = 0
    sql = "SELECT lastChangeId FROM selectedChannelsLastChangeId WHERE customerId=%s "
    cursor.execute(sql, (customerId, ))
    if cursor.rowcount == 0:
        if lastChangeIdReq == 0:
            lastChangeId = 1
        else:
            lastChangeId = lastChangeIdReq
        sql = "INSERT INTO selectedChannelsLastChangeId (customerId, lastChangeId) values (%s, %s)"
        cursor.execute(sql, (customerId, lastChangeId))
    else:
        row = cursor.fetchone()
        lastChangeId = int(row[0])
        if lastChangeIdReq == lastChangeId:
            response["error"] = "NoChange"
            mariadb_connection.commit()
            cursor.close()
            return response
    response["lastChangeId"] = lastChangeId

    # Get lineupID for customer (assuming customer does exist)
    sql = ("SELECT lineupId FROM customers WHERE customerId='{}'").format(customerId)
    cursor.execute(sql)
    if cursor.rowcount == 0:
        response["error"] = "Customer does not exist"
        return response

    sql = ("CREATE TEMPORARY TABLE custChannels "
           "(SELECT selectedChannels.customerId, selectedChannels.channelId, selectedChannels.localChannel, selectedChannels.contentUrl, "
           "channels.name, channels.callSign, channels.affiliateCallSign, channels.imageUrl FROM selectedChannels "
           "JOIN channels ON selectedChannels.channelId = channels.channelId WHERE selectedChannels.customerId = %s)")
    cursor.execute(sql, (customerId,))

    sql = ("SELECT custChannels.channelId, custChannels.name, custChannels.callSign, custChannels.affiliateCallSign, custChannels.imageUrl,"   # from channels
           "localChannel, contentUrl, "                                                                                                        # from selectedCustomerChannels
           "channelPrograms.name, startTime, durationMins, description, channelPrograms.imageUrl, genres, cast "                               # rest from channelPrograms
           "FROM channelPrograms JOIN custChannels ON channelPrograms.channelId = custChannels.channelId "
           "WHERE customerId = %s")
    cursor.execute(sql, (customerId,))

    channels = []
    prevChannel = 0
    channel = {}
    for (channelId, channelName, callSign, affiliateCallSign, channelImageUrl,
         localChannel, contentUrl, progName, startTime, durationMins, description, progImageUrl, genres, cast) in cursor:

        if localChannel != prevChannel:
            if prevChannel != 0:
                channels.append(channel)
            prevChannel = localChannel
            channel = {}
            channel["name"] = channelName
            channel["callSign"] = callSign
            if channelId.find("Local") >= 0:
                iconFilename = "art/localChannelIcons/" + channelId + "_icon"
                channel["imageUrl"] = iconFilename if os.path.exists(iconFilename) else ""
            else:
                if channelImageUrl != "unavailable":
                    channel["imageUrl"] = "art/channelIcons/" + channelImageUrl if channelImageUrl != "" else ""
                else:
                    channel["imageUrl"] = ""
            channel["localChannel"] = localChannel
            channel["contentUrl"] = contentUrl
            channel["channelId"] = channelId
            channel["programs"] = []

        program = {}
        program["name"] = progName
        program["startTime"] = startTime.strftime("%Y-%m-%dT%H:%MZ")
        program["durationMins"] = durationMins
        program["description"] = description
        program["imageUrl"] = "art/programIcons/" + progImageUrl if progImageUrl != "" else ""
        program["genres"] = genres
        program["cast"] = cast
        channel["programs"].append(program)

    channels.append(channel)

    response["channels"] = channels
    mariadb_connection.commit()
    cursor.close()
    return response


def showCustomers():
    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = ("SELECT integrator, name, zip, customerId FROM customers")
    cursor.execute(sql)

    if cursor.rowcount == 0:
        print "There are no customers"
    else:
        print
        row_format = "{:>15}" * 4
        header = row_format.format("Integrator", "Name", "ZIP-code", "accessID")
        print header
        print "{}".format("=" * len(header))
        for (integrator, name, zip, customerId) in cursor:
            print(row_format.format(integrator, name, zip, customerId))

    mariadb_connection.commit()
    cursor.close()


def showChannels():
    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = ("SELECT callSign, channelId FROM channels")
    cursor.execute(sql)

    for (callSign, channelId) in cursor:
        print("callSign={}, id={}".format(callSign, channelId))

    mariadb_connection.commit()
    cursor.close()


def showLineups():
    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = ("SELECT lineupId, name, zip FROM lineups")
    cursor.execute(sql)

    for (lineupId, name, zip) in cursor:
        print("lineupId={} name={} zip={}".format(lineupId, name, zip))

    mariadb_connection.commit()
    cursor.close()


def showLineupChannels(lineupId):
    mariadb_connection = mariadb.connector.connect(user='root', password=system_defines.mariaDbPassword)
    cursor = mariadb_connection.cursor(buffered=True)
    cursor.execute("use zvepg")

    sql = ("SELECT lineupId, channels.channelId, callSign, affiliateCallSign, name, channelNumber, imageUrl FROM lineupChannelMap "
           "JOIN channels ON lineupChannelMap.channelId = channels.channelId WHERE lineupId='{}' ").format(lineupId)
    print sql
    cursor.execute(sql)

    for (lineupId, channelId, callSign, affiliateCallSign, name, channelNumber, imageUrl) in cursor:
        print("channelId={}, callSign={}, affiliate={}, name={}, channelNumber={}, image={}".format(channelId, callSign,
                                                                                                    affiliateCallSign, name, channelNumber, imageUrl))

    mariadb_connection.commit()
    cursor.close()