import astropy
import pandas as pd
import numpy as np

from astropy.table import Table
from astropy.io import ascii
import sqlalchemy
from sqlalchemy import inspect
import psycopg2
import re

def queryidr3(Survey, *args):
    password = str(input("input password: "))
    engine = sqlalchemy.create_engine(f'postgresql://SPLUS_readonly:{password}@flaskpro.cm2v0pvygspl.sa-east-1.rds.amazonaws.com:5432/Splus')

    try:
        if args == ():
            query = f"""SELECT * FROM {Survey} """
        if args[0][0].split('=')[0].strip().lower() == 'field':
            query = f"""SELECT * FROM {Survey} WHERE "Field" = '{args[0][0].split('=')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)
        if args[0][0].split('==')[0].strip().lower() == 'field':
            query = f"""SELECT * FROM {Survey} WHERE "Field" = '{args[0][0].split('==')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)

        if args[0][0].split('=')[0].strip().lower() == 'id':
            query = f"""SELECT * FROM {Survey} WHERE "ID" = '{args[0][0].split('=')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)
        if args[0][0].split('==')[0].strip().lower() == 'id':
            query = f"""SELECT * FROM {Survey} WHERE "ID" = '{args[0][0].split('==')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)

        if args[0] != ():
            conditions = get_conditions(args[0])
            query = f"""SELECT * FROM "{Survey}" WHERE """
            for key, condition in enumerate(conditions):
                if key == 0:
                    query = str(query) + str(condition) + ' '
                if key != 0:
                    query = str(query) + 'and ' + str(condition) + ' '
            print('getting data...\n')
            print('it may take a minute sometimes\n')
        result = pd.read_sql_query(query, engine)
        print('Done!')
        return(result)


    except sqlalchemy.exc.ProgrammingError:
        print("Error while searching, check if the column exists or if it is typed correctly")
    else:
        print("Something is wrong!")

def get_columns():
    password = str(input("input password: "))
    engine = sqlalchemy.create_engine(f'postgresql://SPLUS_readonly:{password}@flask<EMAIL>.cm<EMAIL>pvygspl.sa-east-1.rds.amazonaws.com:5432/Splus')

    cols = []
    for x in inspect(engine).get_columns('main1'):
        cols.append(x['name'])

    return cols

def get_columns_return(engine):
    cols = []
    for x in inspect(engine).get_columns('main1'):
        cols.append(x['name'])

    return cols


def get_surveys():
    surveys = ['MAIN1']
    print(surveys)

def get_conditions(condits):
    conditions = []
    for condit in condits:
        if '<'in condit:
            operation = get_operation(condit)
            if operation != 'none':
                goal = replace_sym(condit)
                cond = f'"{goal[0].strip()}" {operation} "{goal[1].strip()}" < {goal[2].strip()}'
                conditions.append(cond)
            else:
                splits = condit.split('<')
                cond = f'"{splits[0].strip()}" < {(splits[1])}'
                conditions.append(cond)
        if '>'in condit:
            operation = get_operation(condit)
            if operation != 'none':
                goal = replace_sym(condit)
                cond = f'"{goal[0].strip()}" {operation} "{goal[1].strip()}" > {goal[2].strip()}'
                conditions.append(cond)
            else:
                splits = condit.split('>')
                cond = f'"{splits[0].strip()}" > {float(splits[1])}'
                conditions.append(cond)
        if '=' in condit:
            splits = condit.split('=')
            try:
                operation = get_operation(condit)
                if operation != 'none':
                    goal = replace_sym(condit)
                    cond = f'"{goal[0].strip()}" {operation} "{goal[1].strip()}" = {goal[2].strip()}'
                    conditions.append(cond)

                else:
                    float(splits[1])
                    cond = f'"{splits[0].strip()}" = {splits[1]}'
                    conditions.append(cond)
            except:
                pass

        if '==' in condit:
            splits = condit.split('==')
            try:
                operation = get_operation(condit)
                if operation != 'none':
                    goal = replace_sym(condit)
                    cond = f'"{goal[0].strip()}" {operation} "{goal[1].strip()}" = {goal[2].strip()}'
                    conditions.append(cond)
                else:
                    float(splits[1])
                    cond = f""""{splits[0].strip()}" = {splits[1]}' """
                    conditions.append(cond)
            except:
                pass

    return conditions

def replace_sym(string):
    rep = {"=": ";", "==": ";", "<": ";", ">": ";", "+": ";", "-": ";", "*": ";", "/": "<=", ">=": ";"}
    # string = string.replace(rep)

    rep = dict((re.escape(k), v) for k, v in rep.items())
    pattern = re.compile("|".join(rep.keys()))
    string = pattern.sub(lambda m: rep[re.escape(m.group(0))], string)

    string = string.split(";")
    return string

def get_operation(string):
    if '+' in string:
        return '+'
    if '-' in string:
        return '-'
    if '*' in string:
        return '*'
    if '/' in string:
        return '/'
    else:
        return 'none'


# In[52]:


def queryidr3_complex(Survey, *args):
    password = str(input("input password: "))
    engine = sqlalchemy.create_engine(f'postgresql://SPLUS_readonly:{password}@flask<EMAIL>.cm2v0pvygspl.sa-east-1.rds.amazonaws.com:5432/Splus')

    try:
        if args == ():
            query = f"""SELECT * FROM {Survey} """
        if args[0][0].split('=')[0].strip().lower() == 'field':
            query = f"""SELECT * FROM {Survey} WHERE "Field" = '{args[0][0].split('=')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)
        if args[0][0].split('==')[0].strip().lower() == 'field':
            query = f"""SELECT * FROM {Survey} WHERE "Field" = '{args[0][0].split('==')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)

        if args[0][0].split('=')[0].strip().lower() == 'id':
            query = f"""SELECT * FROM {Survey} WHERE "ID" = '{args[0][0].split('=')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)
        if args[0][0].split('==')[0].strip().lower() == 'id':
            query = f"""SELECT * FROM {Survey} WHERE "ID" = '{args[0][0].split('==')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)

        if args[0] != ():
            conditions = get_conditions_complex(args[0], engine)
            query = f"""SELECT * FROM "{Survey}" WHERE """
            for key, condition in enumerate(conditions):
                if key == 0:
                    query = str(query) + str(condition) + ' '
                if key != 0:
                    query = str(query) + 'and ' + str(condition) + ' '

            print('getting data...\n')
            print('it may take a minute sometimes\n')

        result = pd.read_sql_query(query, engine)
        print('Done!')
        return(result)


    except sqlalchemy.exc.ProgrammingError:
        print("Error while searching, check if the column exists or if it is typed correctly")
    else:
        print("Something is wrong!")


def get_conditions_complex(condits, engine):
    try:
        conditions = []
        for condit in condits:
            operation = get_operation(condit)
            if operation != 'none':
                cond = sql_ready(condit, engine)
                conditions.append(cond)
            else:
                cond = sql_ready(condit, engine)
                conditions.append(cond)

        return conditions
    except:
        print('Error with conditions')

def sql_ready(string, engine):
    try:
        cols = get_columns_return(engine)

        for key, col in enumerate(cols):
            if col != 'A' in string and key == 0:
                string = string.replace(col, f'"{col}"')
            if col != 'A' and col != 'CLASS_STAR' and col in string and key != 0:
                string = string.replace(col, f'"{col}"')

        return(string)
    except:
        print('Error with conditions')

import astropy
import pandas as pd
import numpy as np

from astropy.table import Table
from astropy.io import ascii
import sqlalchemy
from sqlalchemy import inspect
import psycopg2
import re

def queryidr3(Survey, *args):
    password = str(input("input password: "))
    engine = sqlalchemy.create_engine(f'postgresql://SPLUS_readonly:{password}@flaskpro.cm2v0pvygspl.sa-east-1.rds.amazonaws.com:5432/Splus')

    try:
        if args == ():
            query = f"""SELECT * FROM {Survey} """
        if args[0][0].split('=')[0].strip().lower() == 'field':
            query = f"""SELECT * FROM {Survey} WHERE "Field" = '{args[0][0].split('=')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)
        if args[0][0].split('==')[0].strip().lower() == 'field':
            query = f"""SELECT * FROM {Survey} WHERE "Field" = '{args[0][0].split('==')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)

        if args[0][0].split('=')[0].strip().lower() == 'id':
            query = f"""SELECT * FROM {Survey} WHERE "ID" = '{args[0][0].split('=')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)
        if args[0][0].split('==')[0].strip().lower() == 'id':
            query = f"""SELECT * FROM {Survey} WHERE "ID" = '{args[0][0].split('==')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)

        if args[0] != ():
            conditions = get_conditions(args[0])
            query = f"""SELECT * FROM "{Survey}" WHERE """
            for key, condition in enumerate(conditions):
                if key == 0:
                    query = str(query) + str(condition) + ' '
                if key != 0:
                    query = str(query) + 'and ' + str(condition) + ' '
            print('getting data...\n')
            print('it may take a minute sometimes\n')
        result = pd.read_sql_query(query, engine)
        print('Done!')
        return(result)


    except sqlalchemy.exc.ProgrammingError:
        print("Error while searching, check if the column exists or if it is typed correctly")
    else:
        print("Something is wrong!")

def get_columns():
    password = str(input("input password: "))
    engine = sqlalchemy.create_engine(f'postgresql://SPLUS_readonly:{password}@flask<EMAIL>.cm<EMAIL>pvygspl.sa-east-1.rds.amazonaws.com:5432/Splus')

    cols = []
    for x in inspect(engine).get_columns('main1'):
        cols.append(x['name'])

    return cols

def get_columns_return(engine):
    cols = []
    for x in inspect(engine).get_columns('main1'):
        cols.append(x['name'])

    return cols


def get_surveys():
    surveys = ['MAIN1']
    print(surveys)

def get_conditions(condits):
    conditions = []
    for condit in condits:
        if '<'in condit:
            operation = get_operation(condit)
            if operation != 'none':
                goal = replace_sym(condit)
                cond = f'"{goal[0].strip()}" {operation} "{goal[1].strip()}" < {goal[2].strip()}'
                conditions.append(cond)
            else:
                splits = condit.split('<')
                cond = f'"{splits[0].strip()}" < {(splits[1])}'
                conditions.append(cond)
        if '>'in condit:
            operation = get_operation(condit)
            if operation != 'none':
                goal = replace_sym(condit)
                cond = f'"{goal[0].strip()}" {operation} "{goal[1].strip()}" > {goal[2].strip()}'
                conditions.append(cond)
            else:
                splits = condit.split('>')
                cond = f'"{splits[0].strip()}" > {float(splits[1])}'
                conditions.append(cond)
        if '=' in condit:
            splits = condit.split('=')
            try:
                operation = get_operation(condit)
                if operation != 'none':
                    goal = replace_sym(condit)
                    cond = f'"{goal[0].strip()}" {operation} "{goal[1].strip()}" = {goal[2].strip()}'
                    conditions.append(cond)

                else:
                    float(splits[1])
                    cond = f'"{splits[0].strip()}" = {splits[1]}'
                    conditions.append(cond)
            except:
                pass

        if '==' in condit:
            splits = condit.split('==')
            try:
                operation = get_operation(condit)
                if operation != 'none':
                    goal = replace_sym(condit)
                    cond = f'"{goal[0].strip()}" {operation} "{goal[1].strip()}" = {goal[2].strip()}'
                    conditions.append(cond)
                else:
                    float(splits[1])
                    cond = f""""{splits[0].strip()}" = {splits[1]}' """
                    conditions.append(cond)
            except:
                pass

    return conditions

def replace_sym(string):
    rep = {"=": ";", "==": ";", "<": ";", ">": ";", "+": ";", "-": ";", "*": ";", "/": "<=", ">=": ";"}
    # string = string.replace(rep)

    rep = dict((re.escape(k), v) for k, v in rep.items())
    pattern = re.compile("|".join(rep.keys()))
    string = pattern.sub(lambda m: rep[re.escape(m.group(0))], string)

    string = string.split(";")
    return string

def get_operation(string):
    if '+' in string:
        return '+'
    if '-' in string:
        return '-'
    if '*' in string:
        return '*'
    if '/' in string:
        return '/'
    else:
        return 'none'


# In[52]:


def queryidr3_complex(Survey, *args):
    password = str(input("input password: "))
    engine = sqlalchemy.create_engine(f'postgresql://SPLUS_readonly:{password}@flask<EMAIL>.cm2v0pvygspl.sa-east-1.rds.amazonaws.com:5432/Splus')

    try:
        if args == ():
            query = f"""SELECT * FROM {Survey} """
        if args[0][0].split('=')[0].strip().lower() == 'field':
            query = f"""SELECT * FROM {Survey} WHERE "Field" = '{args[0][0].split('=')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)
        if args[0][0].split('==')[0].strip().lower() == 'field':
            query = f"""SELECT * FROM {Survey} WHERE "Field" = '{args[0][0].split('==')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)

        if args[0][0].split('=')[0].strip().lower() == 'id':
            query = f"""SELECT * FROM {Survey} WHERE "ID" = '{args[0][0].split('=')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)
        if args[0][0].split('==')[0].strip().lower() == 'id':
            query = f"""SELECT * FROM {Survey} WHERE "ID" = '{args[0][0].split('==')[1].strip()}'"""
            result = pd.read_sql_query(query, engine)
            print('Done!')
            return(result)

        if args[0] != ():
            conditions = get_conditions_complex(args[0], engine)
            query = f"""SELECT * FROM "{Survey}" WHERE """
            for key, condition in enumerate(conditions):
                if key == 0:
                    query = str(query) + str(condition) + ' '
                if key != 0:
                    query = str(query) + 'and ' + str(condition) + ' '

            print('getting data...\n')
            print('it may take a minute sometimes\n')

        result = pd.read_sql_query(query, engine)
        print('Done!')
        return(result)


    except sqlalchemy.exc.ProgrammingError:
        print("Error while searching, check if the column exists or if it is typed correctly")
    else:
        print("Something is wrong!")


def get_conditions_complex(condits, engine):
    try:
        conditions = []
        for condit in condits:
            operation = get_operation(condit)
            if operation != 'none':
                cond = sql_ready(condit, engine)
                conditions.append(cond)
            else:
                cond = sql_ready(condit, engine)
                conditions.append(cond)

        return conditions
    except:
        print('Error with conditions')

def sql_ready(string, engine):
    try:
        cols = get_columns_return(engine)

        for key, col in enumerate(cols):
            if col != 'A' in string and key == 0:
                string = string.replace(col, f'"{col}"')
            if col != 'A' and col != 'CLASS_STAR' and col in string and key != 0:
                string = string.replace(col, f'"{col}"')

        return(string)
    except:
        print('Error with conditions')

SELECT * FROM {Survey} SELECT * FROM {Survey} WHERE "Field" = '{args[0][0].split('=')[1].strip()}' SELECT * FROM {Survey} WHERE "Field" = '{args[0][0].split('==')[1].strip()}' SELECT * FROM {Survey} WHERE "ID" = '{args[0][0].split('=')[1].strip()}' SELECT * FROM {Survey} WHERE "ID" = '{args[0][0].split('==')[1].strip()}' SELECT * FROM "{Survey}" WHERE "{splits[0].strip()}" = {splits[1]}' # string = string.replace(rep) # In[52]: SELECT * FROM {Survey} SELECT * FROM {Survey} WHERE "Field" = '{args[0][0].split('=')[1].strip()}' SELECT * FROM {Survey} WHERE "Field" = '{args[0][0].split('==')[1].strip()}' SELECT * FROM {Survey} WHERE "ID" = '{args[0][0].split('=')[1].strip()}' SELECT * FROM {Survey} WHERE "ID" = '{args[0][0].split('==')[1].strip()}' SELECT * FROM "{Survey}" WHERE