db_path = "./drstats.db"
import dataset
from drstats.duration import Duration


def get_db():
    db = dataset.connect(f"sqlite:///{db_path}")

    db.query(
        """
        DROP VIEW IF EXISTS score_ignored_most_last_7_days
        """
    )
    db.query(
        """
        CREATE VIEW score_ignored_most_last_7_days AS SELECT 
            user_username AS username, 
            COUNT(score) AS userscore
        FROM comments 
        WHERE score = 0 
            AND created >= DATE('now', '-7 days') 
        GROUP BY username 
        ORDER BY userscore DESC
        """
    )

    db.query("DROP VIEW IF EXISTS score_last_7_days")
    db.query(
        """
        CREATE VIEW score_last_7_days AS SELECT 
            user_username AS username, 
            SUM(score) AS userscore
        FROM comments 
        GROUP BY user_username 
        ORDER BY userscore DESC
        """
    )

    db.query("DROP VIEW IF EXISTS rant_stats_per_day")
    db.query(
        """
        CREATE VIEW rant_stats_per_day AS SELECT 
            COUNT(0) AS count, 
            DATE(created) AS created_date,
            CASE strftime('%w', DATE(created))
                WHEN '0' THEN 'Sunday'
                WHEN '1' THEN 'Monday'
                WHEN '2' THEN 'Tuesday'
                WHEN '3' THEN 'Wednesday'
                WHEN '4' THEN 'Thursday'
                WHEN '5' THEN 'Friday'
                WHEN '6' THEN 'Saturday'
            END AS weekday
        FROM rants 
        GROUP BY created_date 
        ORDER BY created_date
    """
    )

    db.query("DROP VIEW IF EXISTS comment_stats_per_day")
    db.query(
        """
        CREATE VIEW comment_stats_per_day AS SELECT 
            COUNT(0) AS count, 
            DATE(created) AS created_date,
            CASE strftime('%w', DATE(created))
                WHEN '0' THEN 'Sunday'
                WHEN '1' THEN 'Monday'
                WHEN '2' THEN 'Tuesday'
                WHEN '3' THEN 'Wednesday'
                WHEN '4' THEN 'Thursday'
                WHEN '5' THEN 'Friday'
                WHEN '6' THEN 'Saturday'
            END AS weekday
        FROM comments 
        GROUP BY created_date 
        ORDER BY created_date
    """
    )

    db.query("DROP VIEW IF EXISTS rant_stats_per_weekday")
    db.query(
        """
        CREATE VIEW rant_stats_per_weekday AS SELECT 
            COUNT(0) AS count, 
            DATE(created) AS created_date,
            CASE strftime('%w', DATE(created))
                WHEN '0' THEN 'Sunday'
                WHEN '1' THEN 'Monday'
                WHEN '2' THEN 'Tuesday'
                WHEN '3' THEN 'Wednesday'
                WHEN '4' THEN 'Thursday'
                WHEN '5' THEN 'Friday'
                WHEN '6' THEN 'Saturday'
            END AS weekday
        FROM rants 
        GROUP BY weekday 
        ORDER BY created_date
    """
    )

    db.query("DROP VIEW IF EXISTS comment_stats_per_weekday")
    db.query(
        """
        CREATE VIEW comment_stats_per_weekday AS SELECT 
            COUNT(0) AS count, 
            DATE(created) AS created_date,
            CASE strftime('%w', DATE(created))
                WHEN '0' THEN 'Sunday'
                WHEN '1' THEN 'Monday'
                WHEN '2' THEN 'Tuesday'
                WHEN '3' THEN 'Wednesday'
                WHEN '4' THEN 'Thursday'
                WHEN '5' THEN 'Friday'
                WHEN '6' THEN 'Saturday'
            END AS weekday
        FROM comments 
        GROUP BY weekday 
        ORDER BY created_date
    """
    )

    db.query("DROP VIEW IF EXISTS comment_stats_per_hour")
    db.query(
        """
        CREATE VIEW comment_stats_per_hour AS SELECT 
            COUNT(0) AS count, 
            strftime('%H', created) AS hour
        FROM comments 
        GROUP BY hour 
        ORDER BY hour
        """
    )

    db.query("DROP VIEW IF EXISTS rant_stats_per_hour")
    db.query(
        """
        CREATE VIEW rant_stats_per_hour AS SELECT 
            COUNT(0) AS count, 
            strftime('%H', created) AS hour
        FROM rants 
        GROUP BY hour 
        ORDER BY hour
        """
    )

    db.query(
        """
    DROP VIEW IF EXISTS user_stats
    """
    )

    db.query(
        """
        CREATE VIEW user_stats AS
        SELECT
            user_username AS username,
            COUNT(0) AS post_count,
            (
                select count(0) from rants where 
                    rants.id = comments.rant_id 
                    and date(rants.created) = date(comments.created)
            ) as rant_count,
            DATE(comments.created) AS date,
            (SELECT COUNT(0)
            FROM comments AS comments2
            WHERE comments2.user_username = comments.user_username
            AND comments2.score = 0 and date(comments2.created) = date(comments.created)) AS ignore_count,
            (SELECT COUNT(0)
            FROM comments AS comments2
            WHERE comments2.user_username = comments.user_username
            AND comments2.score > 0 and date(comments2.created) = date(comments.created)) AS upvote_times,
            (SELECT SUM(score)
            FROM comments AS comments2
            WHERE comments2.user_username = comments.user_username
            AND comments2.score > 0 and date(comments2.created) = date(comments.created)) AS upvote_total
        FROM comments
        GROUP BY username, DATE(comments.created)
        ORDER BY username ASC, date ASC;
    """
    )
    db.query("DROP  VIEW IF EXISTS contributions")
    db.query(
        """
        CREATE VIEW contributions AS
            select distinct user_username as username, count(0) as contributions,sum(score) as upvotes,avg(length(text)) as post_length_average, sum(length(text)) as content_length from rants
            union
            select distinct user_username as username, count(0) as contributions,sum(score) as upvotes, sum(length(body)) / count(0) as post_length_average, sum(length(body)) as content_length from comments
            group by username
            order by contributions desc, username asc
        """
    )
    db.query("DROP VIEW IF EXISTS contributions_extended")
    db.query(
        """
        CREATE VIEW contributions_extended as SELECT username, contributions,ROUND(CAST(contributions AS REAL) / CAST((select contributions from contributions) AS REAL),2) as ownership, upvotes, ROUND(CAST(upvotes AS REAL) / CAST((SELECT SUM(upvotes) from contributions) AS REAL),2) upvotes_ownership, ROUND(CAST(upvotes AS REAL) / CAST(contributions AS REAL),2) as upvotes_per_post_on_average,content_length as post_length_total, ROUND(CAST(content_length AS REAL) / CAST((SELECT SUM(content_length) from contributions) AS REAL)) as ownership_content,post_length_average    
        FROM contributions
        """
    )
    db.query("DROP VIEW IF EXISTS rants_of_user")
    db.query(
        "CREATE VIEW rants_of_user as SELECT user_username as username, GROUP_CONCAT(text) as text FROM rants GROUP BY username"
    )
    db.query("DROP VIEW IF EXISTS posts_of_user")
    db.query(
        "CREATE VIEW posts_of_user AS SELECT user_username as username, GROUP_CONCAT(body) as text FROM comments GROUP BY username"
    )
    db.query("DROP VIEW IF EXISTS contributions_extended_ranked")
    db.query(
        "CREATE VIEW contributions_extended_ranked AS SELECT ROW_NUMBER() OVER (ORDER BY upvotes_per_post_on_average DESC) as rank_by_appreciation_based_on_upvotes_per_message, * FROM contributions_extended ORDER BY upvotes_per_post_on_average DESC"
    )
    db.query(
        "CREATE VIEW IF NOT EXISTS views AS SELECT sql, name FROM sqlite_schema WHERE type='view' AND name != 'views';"
    )

    return db


class Db:

    def __init__(self):
        self.db = None

    def __enter__(self):
        self.db = get_db()
        return self

    def query(self, str):
        with Duration("DB Query {}".format(str[:80])):
            return self.db.query(str)

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.db.close()
        self.db = None

    async def __aenter__(self):
        self.db = get_db()
        return self

    def query(self, str):
        with Duration("Query {}".format(str[:80])):
            return self.db.query(str)

    async def __aexit__(self, exc_type, exc_val, exc_tb):
        self.db.close()
        self.db = None


def get_views():
    with Db() as db:
        return list(db.query("SELECT * FROM views;"))


def get_contributions():
    with Db() as db:
        contributions = db.query("SELECT * FROM contributions_extended_ranked")
        return list(contributions)


def get_upvote_average():
    return avg(
        contribution["upvotes_per_post_on_average"]
        for contribution in get_contributions()
    )


def get_users():
    with Db() as db:
        return [
            user["username"]
            for user in db.query(
                "SELECT DISTINCT username FROM contributions ORDER BY username"
            )
        ]


def get_user_count():
    return len(get_users())


def get_contribution_count():
    return sum(user["contributions"] for user in get_contributions())


def get_contribution_average_per_user():
    return round(get_contribution_count() / get_user_count(), 2)


def get_all_rants_of_user(username):
    with Db() as _db:
        try:
            return list(_db.db["rants_of_user"].find(username=username))[0]["text"]
        except IndexError:
            return ""


def get_all_posts_of_user(username):
    with Db() as _db:
        try:
            return list(_db.db["posts_of_user"].find(username=username))[0]["text"]
        except IndexError:
            return ""