#!/usr/bin/env python

""" supplys the data needed to comunicate with the roundup-server,
and the sqlite database. Represents the types of errors used in roundup.

author: Sascha L. Teichmann <sascha.teichmann@intevation.de>
author: Bernhard Reiter <bernhard@intevation.de>
author: Sean Engelhardt <sean.engelhardt@intevation.de>

(c) 2010, 2015, 2018 by Intevation GmbH

This is Free Software unter the terms of the
GNU GENERAL PUBLIC LICENSE Version 3 or later.
See http://www.gnu.org/licenses/gpl-3.0.txt for details
"""

import os
from typing import List

COLUMNS= [
    "critical", "urgent", "bug", "feature", "wish",
]

data_dict = {
    "date": [],
    "critical": [],
    "urgent": [],
    "bug": [],
    "feature": [],
    "wish": []
}

#SQL

#DEMO System
SELECT_ALL = """
SELECT strftime("%Y-%m-%dT%H:%M:%S", timestamp),
    critical,
    urgent,
    bug,
    feature,
    wish
FROM issues
ORDER BY timestamp
"""

SELECT_WHERE = """
SELECT strftime("%Y-%m-%dT%H:%M:%S", timestamp),
    critical,
    urgent,
    bug,
    feature,
    wish
FROM issues
WHERE {}
ORDER BY timestamp
"""

CREATE_DB = """
CREATE TABLE issues (
    timestamp TIMESTAMP NOT NULL UNIQUE DEFAULT current_timestamp,
    critical INTEGER NOT NULL DEFAULT 0,
    urgent INTEGER NOT NULL DEFAULT 0,
    bug INTEGER NOT NULL DEFAULT 0,
    feature INTEGER NOT NULL DEFAULT 0,
    wish INTEGER NOT NULL DEFAULT 0
)
"""


INSERT_NEW = """
    INSERT INTO issues (critical, urgent, bug, feature, wish)
    VALUES (?, ?, ?, ?, ?)
"""


def build_sql_commands(list_of_columns: list):
    """Build sql commands for use with collect_issues.
    """
    create_db = ("""CREATE TABLE issues (
        timestamp TIMESTAMP NOT NULL UNIQUE DEFAULT current_timestamp,
        """ +
        ", ".join([c + " INTEGER NOT NULL DEFAULT 0"
                   for c in list_of_columns]) + ")")

    insert_new = ("INSERT INTO issues (" + ", ".join(list_of_columns) + ") " +
                  "VALUES (" + ("?, "*len(list_of_columns))[:-2]+ ")")

    select_tmpl = ("""SELECT strftime("%Y-%m-%dT%H:%M:%S", timestamp),""" +
                  ", ".join(list_of_columns))

    select_all = select_tmpl + "FROM issues ORDER BY timestamp"
    select_where = select_tmpl + "FROM issues WHERE {} ORDER BY timestamp"

    # print(select_all, select_where, create_db, insert_new)
    return select_all, select_where, create_db, insert_new