import MySQLdb
import re
from freepost import random, settings

db = MySQLdb.connect (
    host       = settings['mysql']['host'],
    port       = settings['mysql']['port'],
    db         = settings['mysql']['schema'],
    user       = settings['mysql']['username'],
    passwd     = settings['mysql']['password'],
    autocommit = True)

# Since the "db" object is reused for the entire life of website,
# this ping should avoid MySQL timing out if no requests are sent in a while.
db.ping (True)

# Store a new session_id for a user that has logged in
# The session token is stored in the user cookies during login, here
# we store the hash value of that token.
def new_session (user_id, session_token):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        UPDATE user
        SET session = SHA2(%(session)s, 512)
        WHERE id = %(user)s
        """,
        {
            'user': user_id,
            'session': session_token
        }
    )

# Delete user session token on logout
def delete_session (user_id):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        UPDATE user
        SET session = NULL
        WHERE id = %(user)s
        """,
        {
            'user': user_id
        }
    )

# Check user login credentials
# 
# @return None if bad credentials, otherwise return the user
def check_user_credentials (username, password):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        SELECT *
        FROM user
        WHERE
            username = %(username)s AND
            password = SHA2(CONCAT(%(password)s, salt), 512) AND
            isActive = 1
        """,
        {
            'username': username,
            'password': password
        }
    )
    
    return cursor.fetchone ()

# Check if username exists
def username_exists (username):
    return get_user_by_username (username) is not None

# Create new user account
def new_user (username, password):
    # Create a hash_id for the new post
    hash_id = random.alphanumeric_string (10)
    
    # Create a salt for user's password
    salt = random.ascii_string (16)
    
    # Add user to database
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        INSERT INTO user (hashId, isActive, password, registered, salt, username)
        VALUES (%(hash_id)s, 1, SHA2(CONCAT(%(password)s, %(salt)s), 512), NOW(), %(salt)s, %(username)s)
        """,
        {
            'hash_id': hash_id,
            'password': password,
            'salt': salt,
            'username': username
        }
    )

# Check if session token exists
def is_valid_session (token):
    return get_user_by_session_token (token) is not None

# Return the number of unread replies
def count_unread_messages (user_id):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        SELECT COUNT(1) as new_messages
        FROM comment
        WHERE parentUserId = %(user)s AND userId != %(user)s AND `read` = 0
        """,
        {
            'user': user_id
        }
    )
        
    return cursor.fetchone ()['new_messages']

# Retrieve a user
def get_user_by_username (username):
    if not username:
        return None
    
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        SELECT *
        FROM user
        WHERE username = %(username)s
        """,
        {
            'username': username
        }
    )
        
    return cursor.fetchone ()

# Retrieve a user from a session cookie
def get_user_by_session_token (session_token):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        SELECT *
        FROM user
        WHERE session = SHA2(%(session)s, 512)
        """,
        {
            'session': session_token
        }
    )
        
    return cursor.fetchone ()

# Get posts by date (for homepage)
def get_new_posts (page = 0, session_user_id = None):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        SELECT
            P.*,
            U.username,
            V.vote AS user_vote,
            GROUP_CONCAT(DISTINCT T.name ORDER BY T.name SEPARATOR " ") AS topics
        FROM post AS P
        JOIN user AS U ON P.userId = U.id
        LEFT JOIN vote_post as V ON V.postId = P.id AND V.userId = %(user)s
        LEFT JOIN topic as T ON T.post_id = P.id
        GROUP BY P.id
        ORDER BY P.created DESC
        LIMIT %(limit)s
        OFFSET %(offset)s
        """,
        {
            'user': session_user_id,
            'limit': settings['defaults']['items_per_page'],
            'offset': page * settings['defaults']['items_per_page']
        }
    )
    
    return cursor.fetchall ()

# Get posts by rating (for homepage)
def get_hot_posts (page = 0, session_user_id = None):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        SELECT
            P.*,
            U.username,
            V.vote AS user_vote,
            GROUP_CONCAT(DISTINCT T.name ORDER BY T.name SEPARATOR " ") AS topics
        FROM post AS P
        JOIN user AS U ON P.userId = U.id
        LEFT JOIN vote_post as V ON V.postId = P.id AND V.userId = %(user)s
        LEFT JOIN topic as T ON T.post_id = P.id
        GROUP BY P.id
        ORDER BY P.dateCreated DESC, P.vote DESC, P.commentsCount DESC
        LIMIT %(limit)s
        OFFSET %(offset)s
        """,
        {
            'user': session_user_id,
            'limit': settings['defaults']['items_per_page'],
            'offset': page * settings['defaults']['items_per_page']
        }
    )
    
    return cursor.fetchall ()

# Retrieve user's own posts
def get_user_posts (user_id):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        SELECT *
        FROM post
        WHERE userId = %(user)s
        ORDER BY created DESC
        LIMIT 50
        """,
        {
            'user': user_id
        }
    )
    
    return cursor.fetchall ()

# Retrieve user's own comments
def get_user_comments (user_id):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        SELECT
            C.*,
            P.title AS postTitle,
            P.hashId AS postHashId
        FROM comment AS C
        JOIN post AS P ON P.id = C.postId
        WHERE C.userId = %(user)s
        ORDER BY C.created DESC
        LIMIT 50
        """,
        {
            'user': user_id
        }
    )
    
    return cursor.fetchall ()

# Retrieve user's own replies to other people
def get_user_replies (user_id):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        SELECT
            C.*,
            P.title AS postTitle,
            P.hashId AS postHashId,
            U.username AS username
        FROM comment AS C
        JOIN post AS P ON P.id = C.postId
        JOIN user AS U ON U.id = C.userId
        WHERE C.parentUserId = %(user)s AND C.userId != %(user)s
        ORDER BY C.created DESC
        LIMIT 50
        """,
        {
            'user': user_id
        }
    )
    
    return cursor.fetchall ()

# Update user information
def update_user (user_id, about, email, email_notifications):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    # Update user info, but not email address
    cursor.execute (
        """
        UPDATE user
        SET about = %(about)s,
            email_notifications = %(notifications)s
        WHERE id = %(user)s
        """,
        {
            'about': about,
            'notifications': email_notifications,
            'user': user_id
        }
    )
        
    # Update email address
    # IGNORE update if the email address is already specified. This is
    # necessary to avoid an "duplicate key" exception when updating value.
    cursor.execute (
        """
        UPDATE IGNORE user
        SET email = %(email)s
        WHERE id = %(user)s
        """,
        {
            'email': email,
            'user': user_id
        }
    )

# Set user replies as read
def set_replies_as_read (user_id):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        UPDATE comment
        SET `read` = 1
        WHERE parentUserId = %(user)s AND `read` = 0
        """,
        {
            'user': user_id
        }
    )

# Submit a new post/link
def new_post (title, link, text, user_id):
    # Create a hash_id for the new post
    hash_id = random.alphanumeric_string (10)
    
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        INSERT INTO post (hashId, created, dateCreated, title,
                          link, text, vote, commentsCount, userId)
        VALUES (%(hash_id)s, NOW(), CURDATE(), %(title)s, %(link)s,
                %(text)s, 0, 0, %(user)s)
        """,
        {
            'hash_id': hash_id,
            'title': title,
            'link': link,
            'text': text,
            'user': user_id
        }
    )
    
    return hash_id

# Set topics post. Deletes existing ones.
def replace_post_topics (post_id, topics = ''):
    if not topics:
        return
    
    # Normalize topics
    # 1. Split topics by space
    # 2. Remove empty strings
    # 3. Lower case topic name
    topics = [ topic.lower () for topic in topics.split (' ') if topic ]
    
    if len (topics) == 0:
        return
    
    # Remove extra topics if the list is too long
    topics = topics[:settings['defaults']['topics_per_post']]
    
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    # First we delete the existing topics
    cursor.execute (
        """
        DELETE FROM topic WHERE post_id = %(post)s
        """,
        {
            'post': post_id
        }
    )
    
    # Now insert the new topics
    cursor.executemany (
        """
        INSERT INTO topic (post_id, name)
        VALUES (%s, %s)
        """,
        [ (post_id, topic) for topic in topics ]
    )

# Retrieve a post
def get_post (hash, session_user_id = None):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        SELECT P.*, U.username, V.vote AS user_vote
        FROM post AS P
        JOIN user AS U ON P.userId = U.id
        LEFT JOIN vote_post as V ON V.postId = P.id AND V.userId = %(user)s
        WHERE P.hashId = %(post)s
        """,
        {
            'user': session_user_id,
            'post': hash
        }
    )
    
    return cursor.fetchone ()

# Update a post
def update_post (title, link, text, post_hash_id, user_id):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        UPDATE post
        SET title = %(title)s,
            link  = %(link)s,
            text  = %(text)s
        WHERE hashId = %(hash_id)s AND userId = %(user)s
        """,
        {
            'title': title,
            'link': link,
            'text': text,
            'hash_id': post_hash_id,
            'user': user_id
        }
    )

# Retrieve all comments for a specific post
def get_post_comments (post_id, session_user_id = None):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        SELECT C.*, U.username, V.vote AS user_vote
        FROM comment AS C
        JOIN user AS U ON C.userId = U.id
        LEFT JOIN vote_comment as V ON V.commentId = C.id AND V.userId = %(user)s
        WHERE C.postId = %(post)s
        ORDER BY C.vote DESC, C.created ASC
        """,
        {
            'user': session_user_id,
            'post': post_id
        }
    )
    
    return cursor.fetchall ()

# Submit a new comment to a post
def new_comment (comment_text, post_hash_id, user_id, parent_comment_id = None):
    # Create a hash_id for the new comment
    hash_id = random.alphanumeric_string (10)
    
    # Retrieve post
    post = get_post (post_hash_id)
    
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        INSERT INTO comment (hashId, created, dateCreated, `read`, `text`, `vote`,
                             parentId, parentUserId, postId, userId)
        VALUES (%(hash_id)s, NOW(), CURDATE(), 0, %(text)s, 0, %(parent_id)s,
                %(parent_user_id)s, %(post_id)s, %(user)s)
        """,
        {
            'hash_id': hash_id,
            'text': comment_text,
            'parent_id': parent_comment_id,
            'parent_user_id': post['userId'],
            'post_id': post['id'],
            'user': user_id
        }
    )
    
    # Increase comments count for post
    cursor.execute (
        """
        UPDATE post
        SET commentsCount = commentsCount + 1
        WHERE id = %(post)s
        """,
        {
            'post': post['id']
        }
    )
    
    return hash_id

# Retrieve a single comment
def get_comment (hash_id, session_user_id = None):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        SELECT
            C.*,
            P.hashId AS postHashId,
            P.title AS postTitle,
            U.username,
            V.vote AS user_vote
        FROM comment AS C
        JOIN user AS U ON C.userId = U.id
        JOIN post AS P ON P.id = C.postId
        LEFT JOIN vote_comment as V ON V.commentId = C.id AND V.userId = %(user)s
        WHERE C.hashId = %(comment)s
        """,
        {
            'user': session_user_id,
            'comment': hash_id
        }
    )
    
    return cursor.fetchone ()

# Update a comment
def update_comment (text, comment_hash_id, user_id):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        UPDATE comment
        SET text = %(text)s
        WHERE hashId = %(comment)s AND userId = %(user)s
        """,
        {
            'text': text,
            'comment': comment_hash_id,
            'user': user_id
        }
    )

# Add or update vote to a post
def vote_post (post_id, user_id, vote):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    # Insert or update the user vote
    cursor.execute (
        """
        INSERT INTO vote_post (vote, datetime, postId, userId)
        VALUES (%(vote)s, NOW(), %(post)s, %(user)s)
        ON DUPLICATE KEY UPDATE
            vote = vote + %(vote)s,
            datetime = NOW()
        """,
        {
            'vote': vote,
            'post': post_id,
            'user': user_id
        }
    )
    
    # Update vote counter for post
    cursor.execute (
        """
        UPDATE post
        SET vote = vote + %(vote)s
        WHERE id = %(post)s
        """,
        {
            'vote': vote,
            'post': post_id
        }
    )

# Add or update vote to a comment
def vote_comment (comment_id, user_id, vote):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    # Insert or update the user vote
    cursor.execute (
        """
        INSERT INTO vote_comment (vote, datetime, commentId, userId)
        VALUES (%(vote)s, NOW(), %(comment)s, %(user)s)
        ON DUPLICATE KEY UPDATE
            vote = vote + %(vote)s,
            datetime = NOW()
        """,
        {
            'vote': vote,
            'comment': comment_id,
            'user': user_id
        }
    )
    
    # Update vote counter for comment
    cursor.execute (
        """
        UPDATE comment
        SET vote = vote + %(vote)s
        WHERE id = %(comment)s
        """,
        {
            'vote': vote,
            'comment': comment_id
        }
    )

# Search posts
def search (query, page = 0, order = 'newest'):
    if not query:
        return None
    
    # Remove multiple white spaces and replace with '|' (for query REGEXP)
    query = re.sub (' +', '|', query.strip ())
    
    if len (query) == 0:
        return None
    
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    if order == 'newest':
        order = 'P.created DESC'
    if order == 'points':
        order = 'P.vote DESC'
    
    cursor.execute (
        """
        SELECT P.*, U.username
        FROM post AS P
        JOIN user AS U ON P.userId = U.id
        WHERE P.title REGEXP %(query)s
        ORDER BY {order}
        LIMIT %(limit)s
        OFFSET %(offset)s
        """.format (order=order),
        {
            'query': query,
            'limit': settings['defaults']['search_results_per_page'],
            'offset': page * settings['defaults']['search_results_per_page']
        }
    )
    
    return cursor.fetchall ()

# Set reset token for user email
def set_password_reset_token (user_id = None, token = None):
    if not user_id or not token:
        return
    
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        UPDATE user
        SET passwordResetToken = SHA2(%(token)s, 512),
            passwordResetTokenExpire = NOW() + INTERVAL 1 HOUR
        WHERE id = %(user)s
        """,
        {
            'user': user_id,
            'token': token
        }
    )

# Delete the password reset token for a user
def delete_password_reset_token (user_id = None):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        UPDATE user
        SET passwordResetToken = NULL,
            passwordResetTokenExpire = NULL
        WHERE id = %(user)s
        """,
        {
            'user': user_id
        }
    )

# Check if a reset token has expired.
def is_password_reset_token_valid (user_id = None):
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        SELECT COUNT(1) AS valid
        FROM user
        WHERE id = %(user)s AND
              passwordResetToken IS NOT NULL AND
              passwordResetTokenExpire IS NOT NULL AND
              passwordResetTokenExpire > NOW()
        """,
        {
            'user': user_id
        }
    )
    
    return cursor.fetchone ()['valid'] == 1

# Reset user password
def reset_password (username = None, email = None, new_password = None, secret_token = None):
    if not new_password:
        return
    
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    
    cursor.execute (
        """
        UPDATE user
        SET password = SHA2(CONCAT(%(password)s, `salt`), 512),
            passwordResetToken = NULL,
            passwordResetTokenExpire = NULL
        WHERE username = %(user)s AND
              email = %(email)s AND
              passwordResetToken = SHA2(%(token)s, 512) AND
              passwordResetTokenExpire > NOW()
        """,
        {
            'password': new_password,
            'user': username,
            'email': email,
            'token': secret_token
        }
    )