from xmlrpc.client import DateTime
from api.core import Mixin
from .base import db
from api.core import create_response, serialize_list, logger
from sqlalchemy import inspect
from sqlalchemy import text
import psycopg2, json
import re 
from datetime import date,datetime

class DateTimeAwareEncoder(json.JSONEncoder):
    def default(self, o):
        if isinstance(o, datetime) | isinstance(o,date):
            return o.isoformat()

        return json.JSONEncoder.default(self, o)

# Note that we use sqlite for our tests, so you can't use Postgres Arrays
class Sentence(Mixin, db.Model):
    """Sentence Table."""
    __tablename__ = "sentence"
    id = db.Column(db.Integer, unique=True, primary_key=True)
    name = db.Column(db.String, nullable=False)
    sql_query = db.Column(db.String, nullable=False)
    visual_query_params = db.Column(db.JSON)
    transformation_steps = db.Column(db.JSON)
    connection_id = db.Column(db.Integer, db.ForeignKey("connection.id", ondelete="CASCADE"), nullable=False)
    comment = db.Column(db.String, nullable=True)
    visualizations = db.relationship('Visualization', backref='sentence', lazy=True, cascade="all, delete-orphan")

    def __init__(self, name:str, sql_query:str, connection_id:int, comment:str, visual_query_params:json, transformation_steps:json):
        self.name = name
        self.sql_query = sql_query
        self.connection_id = connection_id
        self.comment = comment
        self.visual_query_params = visual_query_params
        self.transformation_steps = transformation_steps

    def __repr__(self):
        return f"<Sentence {self.name}>"

    def default(self, o):
        if isinstance(o, datetime) | isinstance(o,date):
            return o.isoformat()
        else:
            return o

    def execute(self):        

        conn = self.connection.start_connection()
        cur = conn.cursor()
        cur.execute(self.sql_query)
        results= cur.fetchall()
        results_json = [dict(zip([key[0] for key in cur.description], row)) for row in results] #Googleada, ni idea cómo funciona pero it does #Esto funcionaría si no hiciéramos lo de los steps

        # Now, after the base query brings the results from the client's connection, we use those results and execute a query on our own database, adding transformation steps
        results_query = ''
        for row in results_json: ##### Using results_json instead of results. This is a test. 
            result_query_line = "SELECT "
            for key in row:
                if type(row[key]) == str: 
                    result_query_line += "\'" + str(row[key]) + "\'" + ' AS "'  + key + '", '
                else:
                    result_query_line += str(row[key]) + ' AS "'  + key + '", '

            result_query_line = result_query_line[:-2] # Removing the last comma of the line (with its space)
            results_query = results_query + result_query_line + " UNION "
        results_query = results_query[:-7] # removing the last " UNION "
        # Now, this is using our very own database engine
        full_query = 'WITH base_query AS (' + results_query + ' ORDER BY 1), '

        last_table = 'base_query' # We use this one for the FROM statement in each step
        for i, step in enumerate(self.transformation_steps):
            full_query = full_query + ' step_' + str(i+1) + ' AS ('
            if(step["type"] =='Limit rows'):
                step_query = 'SELECT * FROM ' + last_table + ' LIMIT ' + str(step['params']['limit']) + ' OFFSET ' + str(step['params']['limitOffset']) + '), '
            elif(step["type"] == 'Add column'):
                step_query = 'SELECT *, ' + str(step['params']['formula']) + ' AS "' + str(step['params']['name']) + '" FROM ' + last_table + '), ' 
            elif(step["type"] == 'Rename'):
                step_query = 'SELECT '
                for col in step['params']['columns']:
                    try:
                        step_query += '"' + col['oldLabel'] + '" AS "' + col['label'] + '", '
                    except:    
                        step_query += '"' + col['label'] + '", '
                step_query = step_query[:-2] +  ' FROM ' + last_table + '), ' # Removing the last comma + blank space, and finishing the subquery
            elif(step["type"] == 'Sort'):
                step_query = 'SELECT * FROM ' + last_table + ' ORDER BY '
                for col in step['params']['columns']:
                    step_query += '"' + col['label'] + '" '
                    try: 
                        step_query += col['sortMode']
                    except:
                        step_query = step_query
                step_query += '), '
            elif(step["type"] == 'Filter'):
                step_query = 'SELECT * FROM ' + last_table + ' WHERE ' + step['params']['filter'] + '), '
            elif(step["type"] == 'Select columns'):
                step_query = 'SELECT ' 
                for col in step['params']['columns']:
                    if col['checked'] == True: step_query += '"' + col['label'] + '", '
                step_query = step_query[:-2] +  ' FROM ' + last_table + '), ' # Removing the last comma + blank space, and finishing the subquery
                # return col['checked'] # hasattr(col,'checked')
            elif(step["type"] == 'Reorder'):
                step_query = 'SELECT ' 
                for col in step['params']['columns']:
                    step_query += '"' + col['label'] + '", '
                step_query = step_query[:-2] +  ' FROM ' + last_table + '), ' # Removing the last comma + blank space, and finishing the subquery
            

            full_query = full_query + step_query
            last_table = 'step_' + str(i+1)
        full_query = full_query[:-2] + ' SELECT * FROM ' + last_table
        try:
            cur = db.engine.execute(full_query)
            results = cur.fetchall()
            return [dict(row) for row in results] # This one works!!! Wooohoo
        except Exception as e:
            return str(e)