
import os
import base64

import postgresql.driver


__RATE_LIMIT_PERIOD_MINS = 2

__READY_TO_SEND = 10
__EMAIL_SENDING = 11
__EMAIL_SENT = 12

__TOKEN_USED = 20
__TOKEN_MANUALLY_DELETED = 21
__TOKEN_REAPED = 22


def __attempt_update_email_token_state(email, token, prev, new):
    return len(__insert_email_token_state(email, token, prev, new)) > 0 

def __check_state_rows_any_in(state_rows, states):
    for s in state_rows:
        if s[0] in states:
            return True
    return False

def __check_state_rows_any_active(state_rows):
    return __check_state_rows_any_in(state_rows, (__READY_TO_SEND, __EMAIL_SENDING, __EMAIL_SENT))


def cget(config, sec, opt, default):
    if config.has_option(sec, opt):
        return config.get(sec, opt)
    return default

def init(config):
    global db
    global __ip_tokens_last_n_mins_current_states
    global __email_tokens_current_states
    global __create_confirm_token
    global __create_confirm_token_state
    global __check_email_token_in_state
    global __insert_email_token_state
    global __email_waiting_tokens_states

    user = cget(config, 'signupdb', 'user', 'signup')
    password = cget(config, 'signupdb', 'password', 'signup')
    host = cget(config, 'signupdb', 'host', 'localhost')
    port = int(cget(config, 'signupdb', 'port', '5432'))
    database = cget(config, 'signupdb', 'database', 'signup')

    db = postgresql.driver.connect(user=user, password=password, host=host, port=port, database=database)


    # (ip)
    __ip_tokens_last_n_mins_current_states = db.prepare("SELECT state_num FROM signup_state " +
                                                        "WHERE pkey IN (SELECT MAX(pkey) FROM signup_state " +
                                                        "WHERE signup IN (SELECT pkey FROM signup WHERE ip=$1) " +
                                                        "AND modified > (NOW() - INTERVAL '" + str(int(__RATE_LIMIT_PERIOD_MINS)) + " minutes') GROUP BY signup)")

    # (email)
    __email_tokens_current_states = db.prepare('SELECT state_num FROM signup_state ' +
                                               'WHERE pkey IN (SELECT MAX(pkey) FROM signup_state ' +
                                               'WHERE signup IN (SELECT pkey FROM signup WHERE email=$1) GROUP BY signup)')


    # (email, token, ip, ua)
    __create_confirm_token = db.prepare('INSERT INTO signup (email, token, ip, ua) VALUES ($1, $2, $3, $4) RETURNING pkey')

    # (signup, new_state)
    __create_confirm_token_state = db.prepare('INSERT INTO signup_state (signup, state_num) VALUES ($1, $2)')


    # (email, token, old_state)
    __check_email_token_in_state = db.prepare('SELECT 1 FROM signup_state ' +
                                              'WHERE pkey IN (SELECT MAX(pkey) FROM signup_state ' +
                                              'WHERE signup IN (SELECT pkey FROM signup WHERE token=$2 AND email=$1) GROUP BY signup) AND state_num=$3')

    # (email, token, old_state, new_state)
    __insert_email_token_state = db.prepare('INSERT INTO signup_state (signup, state_num) ' +
                                            'SELECT (SELECT pkey FROM signup WHERE email=$1 AND token=$2), $4 ' +
                                            'WHERE $3 IN (SELECT state_num FROM signup_state ' +
                                            'WHERE pkey IN (SELECT MAX(pkey) FROM signup_state ' +
                                            'WHERE signup IN (SELECT pkey FROM signup ' +
                                            'WHERE email=$1 AND token=$2) ' +
                                            'GROUP BY signup)) ' +
                                            'RETURNING pkey')

    __email_waiting_tokens_states = db.prepare('SELECT signup.email, signup.token, signup.pkey FROM signup_state INNER JOIN signup ON signup_state.signup=signup.pkey ' +
                                               'WHERE signup_state.state_num=' + str(int(__READY_TO_SEND)) + ' AND ' +
                                               'signup_state.pkey IN (SELECT MAX(pkey) FROM signup_state GROUP BY signup) LIMIT $1')


# does this IP have any unconfirmed requests within the last few mins?
# does this IP have too many unconfirmed requests?
# TODO: is it manually blacklisted?
def is_rate_limited(ip):
    return __check_state_rows_any_active(__ip_tokens_last_n_mins_current_states(ip))

# Currently limit each user to one valid token per expiry period
def can_send_a_token(email):
    return not __check_state_rows_any_active(__email_tokens_current_states(email))

def create_confirm_token(email, request_ip, user_agent):
    # transaction not strictly needed but saves cleanup
    token = base64.urlsafe_b64encode(os.urandom(15)).decode("ascii")
    signup_pkey = __create_confirm_token(email, token, request_ip, user_agent)[0][0]
    __create_confirm_token_state(signup_pkey, __READY_TO_SEND)
    return token

def check_confirm_token(email, token):
    return len(__check_email_token_in_state(email, token, __EMAIL_SENT)) > 0

# Must only be done at the request of an end-user - means they will be
# able to receive another confirmation email
def check_and_use_confirm_token(email, token):
    return __attempt_update_email_token_state(email, token, __EMAIL_SENT, __TOKEN_USED)

# Must only be done at the request of an end-user - means they will be
# able to receive another confirmation email
def check_and_manually_delete_confirm_token(email, token):
    return __attempt_update_email_token_state(email, token, __EMAIL_SENT, __TOKEN_MANUALLY_DELETED)

# Trade-offs, longer expire time is more user friendly except if they
# lose it and we have anti spam rule not to resend within expiry
# period (thought the spam period could be lower than expiry if we can
# allow multiple tokens).
# Lower is better for security in case user is too lazy to delete an
# unused token. But, if anti-spam period is also low, they could get
# lots of emails. If anti-spam period is higher, there will be some
# time where a user can have an expired token but not able to resend.
# I lean more towards anti-spam minimum of 3 or more days.
def expire_old_tokens():
    pass

def pickup_ready_emailtokens(n):
    return __email_waiting_tokens_states(n)

# generate a multi-insert query and execute() it, no prevstate chek needed
def update_statuses_sent(emailtokens):
    snip = ''
    for e in emailtokens:
        snip += ',(' + str(int(e[2])) + ',' + str(int(__EMAIL_SENT)) + ')'

    # take off leading comma
    snip = snip[1:]

    db.execute('INSERT INTO signup_state (signup, state_num) VALUES ' + snip)
