# utils.py
import operator
from itertools import product, permutations
import ast
import sqlite3
from sqlite3 import Connection
import pandas as pd
import datetime

def mydiv(n, d):
    if d==0:
        r = 99999
    else:
        r = operator.truediv(n,d)
    return r

def solve24(nums):
    syms = [operator.add, operator.sub, operator.mul, mydiv]
    op = {sym: ch for sym, ch in zip(syms, '+-*/')}
    sols = [];
    for x, y, z in product(syms, repeat=3):
        for a, b, c, d in permutations(nums):
            if round(x(y(a,b),z(c,d)),5) == 24:
                sols.append(f"({a}{op[y]}{b}){op[x]}({c}{op[z]}{d})")
            if round(x(a,y(b,z(c,d))),5) == 24:
                sols.append(f"{a}{op[x]}({b}{op[y]}({c}{op[z]}{d}))")
            if round(x(y(z(c,d),b),a),5) == 24:
                sols.append(f"(({c}{op[z]}{d}){op[y]}{b}){op[x]}{a}")
            if round(x(y(b,z(c,d)),a),5) == 24:
                sols.append(f"({b}{op[y]}({c}{op[z]}{d})){op[x]}{a}")
            if round(x(a,y(z(c,d),b)),5) == 24:
                sols.append(f"{a}{op[x]}(({c}{op[z]}{d}){op[y]}{b})")
            
    return sols

def check_extra(sol):
    if eval(sol) == 24 and sol.count(")")==2:
        return True
    else:
        return False

def get_connection(path: str):
    return sqlite3.connect(path, check_same_thread=False)

def insert_value_user (conn, value:tuple):
    q = f"INSERT INTO user (id, name) VALUES {value}"
    conn.execute(q)
    conn.commit()

def check_value_user (conn, id:int):
    q = f"SELECT EXISTS(SELECT 1 FROM user WHERE id={id}) LIMIT 1;"
    return pd.read_sql(q, con=conn).values[0][0] == 1

def insert_value_games (conn, value:tuple):
    q = f"INSERT OR IGNORE INTO games (date, num, suit, sols, num_sols) VALUES {value}"
    conn.execute(q)
    conn.commit()
    
def insert_value_plays (conn, value:tuple):
    q = f"INSERT INTO plays (id, date, num, suit, sol) VALUES {value}"
    conn.execute(q)
    conn.commit() 
    
def update_value_plays(conn, id, date, num, value):
    q = f"""
    UPDATE plays 
    SET sol = '{value}' 
    WHERE id = {id} 
    AND date='{date}' 
    AND num='{num}' 
    ;
    """
    conn.execute(q)
    conn.commit()
    
def check_plays_user (conn, id, date, num):
    q = f"SELECT EXISTS(SELECT 1 FROM plays WHERE id={id} AND date = '{date}' AND num = '{num}') LIMIT 1;"
    return pd.read_sql(q, con=conn).values[0][0] == 1

def get_plays_answer(conn, id, date, num):
    q = f"""
    SELECT sol
    FROM plays
    WHERE id = {id}
    AND date = '{date}'
    and num = '{num}';
    """
    return pd.read_sql(q, con=conn)

def get_top_board(conn):
    q = """
    SELECT plays.sol as ans,
        plays.date as Date,
        user.name as name
        FROM plays
        INNER JOIN user ON plays.id = user.id
        ;
    """
    return pd.read_sql(q, con=conn)
    
def top_board_df(df):
    df['scores'] = df.ans.apply(ast.literal_eval).apply(len)
    new = df.groupby('name')['scores'].sum().reset_index()
    return new.head(10)