import sqlite3
from datetime import datetime, timezone


# ==================================================
# DATABASE CONFIGURATION
# ==================================================

DB_FILE = "qotd.db"


def get_connection():
    """
    Create a connection to the SQLite database.
    """

    connection = sqlite3.connect(DB_FILE)

    connection.row_factory = sqlite3.Row

    return connection


# ==================================================
# DATABASE INITIALIZATION
# ==================================================

def init_database():
    """
    Create all required database tables.

    This is safe to run every time the bot starts.
    """

    with get_connection() as db:

        # ------------------------------------------
        # SERVER SETTINGS
        # ------------------------------------------

        db.execute("""
            CREATE TABLE IF NOT EXISTS guilds (
                guild_id INTEGER PRIMARY KEY,
                channel_id INTEGER,
                post_hour INTEGER DEFAULT 9,
                post_minute INTEGER DEFAULT 0,
                timezone TEXT DEFAULT 'UTC'
            )
        """)

        # ------------------------------------------
        # APPROVED QUESTION POOL
        # ------------------------------------------

        db.execute("""
            CREATE TABLE IF NOT EXISTS questions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                question TEXT NOT NULL,
                category TEXT DEFAULT 'General',
                active INTEGER DEFAULT 1,
                created_by INTEGER,
                created_at TEXT NOT NULL
            )
        """)

        # ------------------------------------------
        # DAILY QUESTIONS
        #
        # Each guild gets one question per date.
        #
        # posted = 0
        #     Question selected but not posted yet.
        #
        # posted = 1
        #     Question successfully posted.
        # ------------------------------------------

        db.execute("""
            CREATE TABLE IF NOT EXISTS daily_questions (
                guild_id INTEGER NOT NULL,
                date TEXT NOT NULL,
                question_id INTEGER NOT NULL,
                posted INTEGER DEFAULT 0,
                message_id INTEGER,
                posted_at TEXT,
                PRIMARY KEY (guild_id, date)
            )
        """)

        # ------------------------------------------
        # MEMBER SUGGESTIONS
        # ------------------------------------------

        db.execute("""
            CREATE TABLE IF NOT EXISTS suggestions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                guild_id INTEGER NOT NULL,
                user_id INTEGER NOT NULL,
                username TEXT NOT NULL,
                question TEXT NOT NULL,
                category TEXT DEFAULT 'General',
                status TEXT DEFAULT 'pending',
                created_at TEXT NOT NULL,
                reviewed_at TEXT
            )
        """)

        # ------------------------------------------
        # ADD DEFAULT QUESTIONS IF DATABASE IS EMPTY
        # ------------------------------------------

        count = db.execute(
            """
            SELECT COUNT(*) AS count
            FROM questions
            """
        ).fetchone()["count"]

        if count == 0:

            default_questions = [

                (
                    "What's a skill you'd love to learn?",
                    "Personal"
                ),

                (
                    "What's your favorite movie of all time?",
                    "Entertainment"
                ),

                (
                    "If you could travel anywhere tomorrow, "
                    "where would you go?",
                    "Travel"
                ),

                (
                    "What's the best meal you've ever had?",
                    "Food"
                ),

                (
                    "What's a hobby you'd recommend to everyone?",
                    "Hobbies"
                ),

                (
                    "If you could meet anyone from history, "
                    "who would it be?",
                    "History"
                ),

                (
                    "What's one thing you're looking forward to?",
                    "Personal"
                ),

                (
                    "If you won $1 million, "
                    "what would you do first?",
                    "Fun"
                ),

                (
                    "What's your favorite childhood memory?",
                    "Personal"
                ),

                (
                    "What's a game you could play forever?",
                    "Gaming"
                ),

                (
                    "What's something you could talk about "
                    "for hours?",
                    "Conversation"
                ),

                (
                    "What's the best advice you've ever received?",
                    "Advice"
                ),

                (
                    "What's a small thing that always makes "
                    "your day better?",
                    "Personal"
                ),

                (
                    "If you could instantly master one skill, "
                    "what would it be?",
                    "Fun"
                ),

                (
                    "What's your favorite season and why?",
                    "General"
                )

            ]

            now = datetime.now(
                timezone.utc
            ).isoformat()

            db.executemany(
                """
                INSERT INTO questions
                (
                    question,
                    category,
                    active,
                    created_at
                )
                VALUES (?, ?, 1, ?)
                """,
                [
                    (
                        question,
                        category,
                        now
                    )
                    for question, category
                    in default_questions
                ]
            )


# ==================================================
# GUILD SETTINGS
# ==================================================

def create_guild_if_missing(guild_id):
    """
    Create default settings for a Discord server
    if it doesn't already exist.
    """

    with get_connection() as db:

        db.execute(
            """
            INSERT OR IGNORE INTO guilds
            (
                guild_id,
                post_hour,
                post_minute,
                timezone
            )
            VALUES (?, 9, 0, 'UTC')
            """,
            (guild_id,)
        )


def get_guild(guild_id):
    """
    Return the settings for a Discord server.
    """

    create_guild_if_missing(guild_id)

    with get_connection() as db:

        return db.execute(
            """
            SELECT *
            FROM guilds
            WHERE guild_id = ?
            """,
            (guild_id,)
        ).fetchone()


def update_guild_settings(
    guild_id,
    channel_id=None,
    hour=None,
    minute=None,
    timezone_name=None
):
    """
    Update one or more server settings.
    """

    create_guild_if_missing(guild_id)

    with get_connection() as db:

        if channel_id is not None:

            db.execute(
                """
                UPDATE guilds
                SET channel_id = ?
                WHERE guild_id = ?
                """,
                (
                    channel_id,
                    guild_id
                )
            )

        if hour is not None:

            db.execute(
                """
                UPDATE guilds
                SET post_hour = ?
                WHERE guild_id = ?
                """,
                (
                    hour,
                    guild_id
                )
            )

        if minute is not None:

            db.execute(
                """
                UPDATE guilds
                SET post_minute = ?
                WHERE guild_id = ?
                """,
                (
                    minute,
                    guild_id
                )
            )

        if timezone_name is not None:

            db.execute(
                """
                UPDATE guilds
                SET timezone = ?
                WHERE guild_id = ?
                """,
                (
                    timezone_name,
                    guild_id
                )
            )


# ==================================================
# QUESTIONS
# ==================================================

def get_random_question():
    """
    Return a random active question.
    """

    with get_connection() as db:

        return db.execute(
            """
            SELECT *
            FROM questions
            WHERE active = 1
            ORDER BY RANDOM()
            LIMIT 1
            """
        ).fetchone()


def add_question(
    question,
    category,
    created_by
):
    """
    Add a new question directly to the question pool.
    """

    now = datetime.now(
        timezone.utc
    ).isoformat()

    with get_connection() as db:

        cursor = db.execute(
            """
            INSERT INTO questions
            (
                question,
                category,
                active,
                created_by,
                created_at
            )
            VALUES (?, ?, 1, ?, ?)
            """,
            (
                question,
                category,
                created_by,
                now
            )
        )

        return cursor.lastrowid


def remove_question(question_id):
    """
    Disable a question without permanently deleting it.
    """

    with get_connection() as db:

        question = db.execute(
            """
            SELECT *
            FROM questions
            WHERE id = ?
            """,
            (question_id,)
        ).fetchone()

        if not question:
            return False

        db.execute(
            """
            UPDATE questions
            SET active = 0
            WHERE id = ?
            """,
            (question_id,)
        )

        return True


def get_questions(limit=20):
    """
    Return active questions.
    """

    with get_connection() as db:

        return db.execute(
            """
            SELECT *
            FROM questions
            WHERE active = 1
            ORDER BY id DESC
            LIMIT ?
            """,
            (limit,)
        ).fetchall()


# ==================================================
# DAILY QUESTIONS
# ==================================================

def get_daily_question(
    guild_id,
    date
):
    """
    Get the question assigned to a guild for a
    particular date.

    If one doesn't exist yet, randomly select one
    and save it.

    This ensures everyone sees the same QOTD.
    """

    with get_connection() as db:

        # ------------------------------------------
        # Check whether today's question already exists
        # ------------------------------------------

        existing = db.execute(
            """
            SELECT
                dq.*,
                q.question,
                q.category
            FROM daily_questions dq
            JOIN questions q
                ON q.id = dq.question_id
            WHERE dq.guild_id = ?
              AND dq.date = ?
            """,
            (
                guild_id,
                date
            )
        ).fetchone()

        if existing:
            return existing

        # ------------------------------------------
        # Pick a new question
        # ------------------------------------------

        question = get_random_question()

        if not question:
            return None

        # ------------------------------------------
        # Save today's question
        # ------------------------------------------

        db.execute(
            """
            INSERT INTO daily_questions
            (
                guild_id,
                date,
                question_id,
                posted
            )
            VALUES (?, ?, ?, 0)
            """,
            (
                guild_id,
                date,
                question["id"]
            )
        )

        # ------------------------------------------
        # Return the saved question
        # ------------------------------------------

        return db.execute(
            """
            SELECT
                dq.*,
                q.question,
                q.category
            FROM daily_questions dq
            JOIN questions q
                ON q.id = dq.question_id
            WHERE dq.guild_id = ?
              AND dq.date = ?
            """,
            (
                guild_id,
                date
            )
        ).fetchone()


def mark_question_posted(
    guild_id,
    date,
    message_id
):
    """
    Mark today's question as successfully posted.

    This is what makes the scheduler restart-safe.
    """

    with get_connection() as db:

        db.execute(
            """
            UPDATE daily_questions
            SET
                posted = 1,
                message_id = ?,
                posted_at = ?
            WHERE guild_id = ?
              AND date = ?
            """,
            (
                message_id,
                datetime.now(
                    timezone.utc
                ).isoformat(),
                guild_id,
                date
            )
        )


def was_question_posted(
    guild_id,
    date
):
    """
    Check whether today's question has already
    been successfully posted.
    """

    with get_connection() as db:

        row = db.execute(
            """
            SELECT posted
            FROM daily_questions
            WHERE guild_id = ?
              AND date = ?
            """,
            (
                guild_id,
                date
            )
        ).fetchone()

        if not row:
            return False

        return bool(row["posted"])


# ==================================================
# SUGGESTIONS
# ==================================================

def create_suggestion(
    guild_id,
    user_id,
    username,
    question,
    category
):
    """
    Save a member's question suggestion.
    """

    now = datetime.now(
        timezone.utc
    ).isoformat()

    with get_connection() as db:

        cursor = db.execute(
            """
            INSERT INTO suggestions
            (
                guild_id,
                user_id,
                username,
                question,
                category,
                status,
                created_at
            )
            VALUES (?, ?, ?, ?, ?, 'pending', ?)
            """,
            (
                guild_id,
                user_id,
                username,
                question,
                category,
                now
            )
        )

        return cursor.lastrowid


def get_pending_suggestions(
    guild_id
):
    """
    Get all pending suggestions for a server.
    """

    with get_connection() as db:

        return db.execute(
            """
            SELECT *
            FROM suggestions
            WHERE guild_id = ?
              AND status = 'pending'
            ORDER BY created_at ASC
            """,
            (
                guild_id,
            )
        ).fetchall()


def get_suggestion(
    suggestion_id
):
    """
    Get a suggestion by its ID.
    """

    with get_connection() as db:

        return db.execute(
            """
            SELECT *
            FROM suggestions
            WHERE id = ?
            """,
            (
                suggestion_id,
            )
        ).fetchone()


def approve_suggestion(
    suggestion_id
):
    """
    Approve a pending suggestion and add it
    to the active question pool.
    """

    with get_connection() as db:

        suggestion = db.execute(
            """
            SELECT *
            FROM suggestions
            WHERE id = ?
              AND status = 'pending'
            """,
            (
                suggestion_id,
            )
        ).fetchone()

        if not suggestion:
            return None

        now = datetime.now(
            timezone.utc
        ).isoformat()

        # ------------------------------------------
        # Mark suggestion as approved
        # ------------------------------------------

        db.execute(
            """
            UPDATE suggestions
            SET
                status = 'approved',
                reviewed_at = ?
            WHERE id = ?
            """,
            (
                now,
                suggestion_id
            )
        )

        # ------------------------------------------
        # Add question to question pool
        # ------------------------------------------

        db.execute(
            """
            INSERT INTO questions
            (
                question,
                category,
                active,
                created_by,
                created_at
            )
            VALUES (?, ?, 1, ?, ?)
            """,
            (
                suggestion["question"],
                suggestion["category"],
                suggestion["user_id"],
                now
            )
        )

        return suggestion


def reject_suggestion(
    suggestion_id
):
    """
    Reject a pending suggestion.
    """

    with get_connection() as db:

        suggestion = db.execute(
            """
            SELECT *
            FROM suggestions
            WHERE id = ?
              AND status = 'pending'
            """,
            (
                suggestion_id,
            )
        ).fetchone()

        if not suggestion:
            return None

        now = datetime.now(
            timezone.utc
        ).isoformat()

        db.execute(
            """
            UPDATE suggestions
            SET
                status = 'rejected',
                reviewed_at = ?
            WHERE id = ?
            """,
            (
                now,
                suggestion_id
            )
        )

        return suggestion