# To make a table, we first need column names. On the start of a screen, every table must be blank.
# In Queries.py, inside the load() method of your screen, make a simple SELECT query and get all of the columns you need.
# Don't worry about filtering data for now, we just need the column names:
"""
cursor.execute(f"SELECT CONCAT(FirstName, ' ', LastName) AS Name, COUNT(*) As NumShifts FROM "
               f"assignto JOIN user on assignto.StaffUsername = user.Username GROUP BY Name")
staff = cursor.fetchall()
"""

# cursor.fetchall() returns a list of dictionaries. For this example, the variable staff would look like:
# [{'Name': 'James Smith', 'NumShifts': 3}, {'Name': 'Staff 1', 'NumShifts': 2} {'Name': 'Staff 2', 'NumShifts': 5}]

# However, we want only the column names. To do this, I simply replace all of the
# values in each dictionary with empty strings.
"""
for i in staff:
    for key in i:
        i[key] = ""
"""

# In order for the table to accept data, it needs to be in the form of
# {1: {ColName: Value, ColName2: Value2}, 2: {ColName: Value, ColName2: Value2}}   where 1, 2, 3... are the numbers of the rows.

# And, in this case, Value1, Value2, Value3... will always be an empty string.
# We only need one row to fill the table with (since all the values are empty and we only need column names),
# and we need it in the form (like I said above) {RowNum: {ColName: Value}}, so we do the following line:

"""
staff = {1: staff[0]}
"""

# And now the variable staff looks like : {1: {'Name': '', 'NumShifts': ''}}.
# We return this, along with anything else we need (for instance, a list of sites for a dropdown, etc).

# Now, back in Beltline.py,
# You make a table this this, inside the display() method of your screen.
"""
staff = self.SQL.load()
self.resultTable = TableCanvas(self, editable=True, data=staff,
                               read_only=True, rowheaderwidth=15, maxcellwidth=200, cellwidth=150,
                               rows=len(staff), thefont=('Helvetica', 10), autoresizecols=1,
                               width=150 * len(list(staff.values())[0]), height=25 * 7)
self.resultTable.show()
"""
# Excellent, now we have a blank table. Whenever the user presses the filter button, inside the filter() method,
# we will get all of the stuff to filter with (i.e. price, date range, etc) from all of the boxes/dropdowns on the screen.

# This is inside the filter() method in Beltline.py.
# Here, we get all of the info the user can input.
"""
site, fname, lname, d1, d2 = self.site.get(), self.fname.get(), self.lname.get(), self.d1.get(), self.d2.get()
"""
#Next, you'd want to check to make sure all of these are the right kinds of values.
#For instance, check if dates are actually dates, check if prices are actually floats, etc etc


# Then, we want to convert any blank values, or any dropdowns with the value 'Any', to None. This is important because,
# in Queries.py, if a value is None, then we do not include it in a WHERE clause.
"""
conv = {'': None, 'Any': None}
site, fname, lname, d1, d2 = conv.get(site, site), conv.get(fname, fname), conv.get(lname, lname), conv.get(d1, d1), conv.get(d2, d2)
"""
# .get() is a method for dictionaries. Basically, we look up a value in the dict, and if it isn't there then we return a default.
# Like this: .get(ItemToFind, Value to Return if Item Isn't In There).
# So, for example, we conv.get(site, site), which looks for an entry in conv. If site is '' or 'Any', it returns None,
# but if site is anything else, then we return the default value, site.

# Now, all of our values are either None, which implies the user didn't input anything, or they are something that the user input.

# By default, the table should be sorted by the first sortable column, so if sort wasn't defined, we make it 'Name',
# which in this case is the first sortable column.
"""
if sort is None:
    sort = 'Name'
"""
# Now, we call self.SQL.filter() with all of the data, some of which is None, and some of which is actual user input.
"""
staff = self.SQL.filter(site, fname, lname, d1, d2, sort)
"""

# Inside Queries.py, in the filter() method for your screen, you'd do the following:
"""
query = "SELECT CONCAT(FirstName, ' ', LastName) AS Name, COUNT(*) As NumShifts FROM " \
                "assignto JOIN user on assignto.StaffUsername = user.Username WHERE 1=1 "

        if site:
            query += f"AND SiteName = '{site}'"
        if fname:
            query += f"AND FirstName LIKE '%{fname}%' "
        if lname:
            query += f"AND LastName LIKE '%{lname}%' "
        if d1 and d2:
            query += f"AND StartDate BETWEEN {d1} AND {d2} "
        elif d1:
            query += f"AND StartDate >= {d1} "
        elif d2:
            query += f"AND StartDate <= {d2} "

        query += f"GROUP BY Name ORDER BY {sort}"
"""
# This is why it was important to change the values to None, because we can easily say
# if Value:
#   query += WHERE CLAUSE

# At the end of constructing our where statement, we will add a GROUP BY clause if necessary, and ORDER BY whatever sort we passed in.
# Then, we execute the query and do .fetchall()
"""
staff = cursor.fetchall()

for i in staff:
    for key in i:
        i[key] = str(i[key])
"""
# The for loop here converts everything to a String, which might not be necessary, but I do it just to be safe
# Since I don't know how the Table will display certain values.

# Remember, the Table accepts data in the format {RowNum: {ColName: Value}},
# and fetchall() returns a list of dictionaries. So, let's fix that.
"""
staff = {i + 1: staff[i] for i in range(len(staff))}

return staff
"""
# Now, back in Beltline.py, we have a list of staff. We clear the table completely, and reimport the data.
"""
self.resultTable.model.deleteRows(range(0, self.resultTable.model.getRowCount()))
self.resultTable.model.importDict(staff)
self.resultTable.redraw()
"""


# Okay, perfect, the table works. What if we want to select a row from the data (for example, stuff like Event/Site Detail)?
# Just do something the following:
"""
row = self.resultTable.model.getData()[self.resultTable.getSelectedRow() + 1]
    user_name, user_type, status = row['Username'], row['UserType'], row['Status']

    if any([user_name == '', user_type == '', status == '']):
        messagebox.showwarning('Error', 'No user selected. Make sure to click on the non-empty '
                                        'row number to select which transit you are taking.')
        return
"""