"""Definitions for database construction and access.

Attributes:
    TABLE_STEAM_REVIEWS (str): Reviews table name.
    TABLE_USERS (str): Users table name.
    TABLE_STEAM_REVIEW_ISSUES (str): Issues table name.
    TABLE_STEAM_PLAYER_COUNT (str): Player count table name.
    TABLE_STEAM_LANGUAGES (str): Language table name.
    TABLE_EVENTS (str): Events table name.
    TABLE_STEAM_GAMES (str): Games table name.

    COLUMNS_STEAM_REVIEWS (str): Steam reviews columns.
    CREATE_STEAM_REVIEWS (str): Create steam reviews table.
    COLUMNS_USERS (str): Steam users columns.
    CREATE_USERS (str): Create steam users table.
    COLUMNS_STEAM_REVIEW_ISSUES (str): Steam reviews issues columns.
    CREATE_STEAM_REVIEW_ISSUES (str): Create steam review issues table.
    COLUMNS_STEAM_PLAYER_COUNT (str): Player count columns.
    CREATE_STEAM_PLAYER_COUNT (str): Create steam player count table.
    COLUMNS_STEAM_LANGUAGES (str): Languages columns.
    CREATE_STEAM_LANGUAGES (str): Create languages table.
    COLUMNS_EVENTS (str): Events columns.
    CREATE_EVENTS (str): Create events table.
    COLUMNS_STEAM_GAMES (str): Steam games columns.
    CREATE_STEAM_GAMES (str): Create steam games table.

    ORDER_MODES (str): Available ordering options using db functions.
"""

from collections import OrderedDict


TABLE_STEAM_REVIEWS = "stats_steam_reviews"
TABLE_USERS = "stats_users"
TABLE_STEAM_REVIEW_ISSUES = "stats_steam_review_issues"
TABLE_STEAM_PLAYER_COUNT = "stats_steam_player_count"
TABLE_STEAM_LANGUAGES = "stats_steam_languages"
TABLE_EVENTS = "stats_events"
TABLE_STEAM_GAMES = "stats_steam_games"


COLUMNS_STEAM_REVIEWS = OrderedDict([
    ("id", "INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT"),
    ("steam_appid", "bigint"),
    ("recommended", "boolean NOT NULL"),
    ("user_name", "character varying"),
    ("content", "character varying"),
    ("hours_played", "numeric"),
    ("review_url", "character varying NOT NULL"),
    ("date_posted", "timestamp without time zone NOT NULL"),
    ("date_updated", "timestamp without time zone"),
    ("helpful_amount", "integer"),
    ("helpful_total", "integer"),
    ("games_owned", "integer"),
    ("responded_by", "bigint"),
    ("responded_date", "timestamp without time zone"),
    ("lang_key", "NUMERIC"),
    ("received_compensation", "boolean"),
    ("entry_updated", "integer"),
    ("can_be_turned", "boolean NOT NULL DEFAULT 0"),
    ("issue_list", "bigintlst"),
])


COLUMNS_USERS = OrderedDict([
    ("id", "INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT"),
    ("user_names", "character varying NOT NULL"),
    ("steam_url", "character varying"),
])


COLUMNS_STEAM_REVIEW_ISSUES = OrderedDict([
    ("id", "INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT"),
    ("name", "character varying NOT NULL"),
    ("resolved_status", "bigint NOT NULL DEFAULT 0")
])


COLUMNS_STEAM_PLAYER_COUNT = OrderedDict([
    ("player_count", "integer NOT NULL"),
    ("time_stamp", "timestamp without time zone NOT NULL"),
    ("steam_appid", "bigint NOT NULL"),
])


COLUMNS_STEAM_LANGUAGES = OrderedDict([
    ("lang_key", "character varying NOT NULL"),
    ("name", "character varying NOT NULL"),
    ("steam_key", "character varying NOT NULL"),
])


COLUMNS_EVENTS = OrderedDict([
    ("name", "character varying NOT NULL"),
    ("description", "character varying NOT NULL"),
    ("time_stamp", "timestamp without time zone NOT NULL"),
    ("id", "INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT"),
    ("type", "stats_event_type NOT NULL DEFAULT 'Patch'"),
    ("steam_appid", "bigint NOT NULL")
])


COLUMNS_STEAM_GAMES = OrderedDict([
    ("steam_appid", "bigint NOT NULL"),
    ("display_name", "character varying NOT NULL"),
])


CREATE_TABLE_STEAM_REVIEWS = """CREATE TABLE "{0}" ({1},
    FOREIGN KEY("lang_key") REFERENCES "stats_steam_languages"("lang_key")
);""".format(TABLE_STEAM_REVIEWS, ",".join("'{0}' {1}".format(*t) for t in COLUMNS_STEAM_REVIEWS.items()))


CREATE_TABLE_USERS = """CREATE TABLE "{0}" ({1});""".format(
    TABLE_USERS, ",".join("'{0}' {1}".format(*t) for t in COLUMNS_USERS.items()))


CREATE_TABLE_STEAM_REVIEW_ISSUES = """CREATE TABLE "{0}" ({1});""".format(
    TABLE_STEAM_REVIEW_ISSUES,
    ",".join("'{0}' {1}".format(*t) for t in COLUMNS_STEAM_REVIEW_ISSUES.items()))


CREATE_TABLE_STEAM_PLAYER_COUNT = """CREATE TABLE "{0}" ({1});""".format(
    TABLE_STEAM_PLAYER_COUNT,
    ",".join("'{0}' {1}".format(*t) for t in COLUMNS_STEAM_PLAYER_COUNT.items()))


CREATE_TABLE_STEAM_LANGUAGES = """CREATE TABLE "{0}" ({1}, PRIMARY KEY("lang_key"));""".format(
        TABLE_STEAM_LANGUAGES,
        ",".join("'{0}' {1}".format(*t) for t in COLUMNS_STEAM_LANGUAGES.items()))


CREATE_TABLE_EVENTS = """CREATE TABLE "{0}" ({1});""".format(
    TABLE_EVENTS, ",".join("'{0}' {1}".format(*t) for t in COLUMNS_EVENTS.items()))


CREATE_TABLE_STEAM_GAMES = """CREATE TABLE "{0}" ({1}, PRIMARY KEY("steam_appid"));""".format(
    TABLE_STEAM_GAMES, ",".join("'{0}' {1}".format(*t) for t in COLUMNS_STEAM_GAMES.items()))


ORDER_MODES = (
    "desc",
    "asc"
)