from decouple import config as load_data

from lib.Connection import Connection

from sqlalchemy import Integer, String, update, func, and_, or_, case, Date, cast, literal_column, text
from sqlalchemy.exc import SQLAlchemyError
import hashlib

from lib.Stech import Stech

def get_access_user(username, password):

    try:

        # conexion a base de datos
        session = Connection.get_session(load_data('ENVIRONMENTS'))

        #print (f"session: {session}")

        def cifrar_contrasenna(password: str) -> str:
            md5_hash = hashlib.md5()
            md5_hash.update(password.encode('utf-8'))
            return md5_hash.hexdigest()
        
        password_cifrada = cifrar_contrasenna(password)


        # query compuesta
        query = text("""
            SELECT id,email FROM gs_users WHERE username = :username AND password = :password
        """)
        response_query = session.execute(query, {'username': username, 'password': password_cifrada}).fetchall()
        session.close()

        json_response = Stech.object_to_json(response_query)
        
        #print(f"json_response: {json_response}")
        if json_response:
            return json_response
        else:
            return False
        
    except SQLAlchemyError as ex:
        print(f"Error1: {str(ex)}")
        raise False
    except Exception as ex:
        print(f"Error2: {ex}")
        raise False

def insert_integraTAG(timestamp, tipo_negocio, ppu, marca_vehiculo, modelo_vehiculo, anio_vehiculo, color_vehiculo, imei_gps, usuario_solicitante, tipo_actividad):
    try:
        session = Connection.get_session(load_data('ENVIRONMENTS'))

        query = text("""
            INSERT INTO st_integra_tag (timestamp, tipo_negocio, ppu, marca_vehiculo, modelo_vehiculo, anio_vehiculo, color_vehiculo, imei_gps, usuario_solicitante, tipo_actividad)
            VALUES (:timestamp, :tipo_negocio, :ppu, :marca_vehiculo, :modelo_vehiculo, :anio_vehiculo, :color_vehiculo, :imei_gps, :usuario_solicitante, :tipo_actividad)
        """)
        session.execute(query, {'timestamp': timestamp, 'tipo_negocio': tipo_negocio, 'ppu': ppu, 'marca_vehiculo': marca_vehiculo, 'modelo_vehiculo': modelo_vehiculo, 'anio_vehiculo': anio_vehiculo, 'color_vehiculo' : color_vehiculo, 'imei_gps': imei_gps, 'usuario_solicitante': usuario_solicitante, 'tipo_actividad': tipo_actividad})
        session.commit()
        session.close()

        return True
        
    except SQLAlchemyError as ex:
        print(f"Error: {str(ex)}")
        return False

def buscar_imei(imei):
    try:
        session = Connection.get_session(load_data('ENVIRONMENTS'))
        int_imei = int(imei)
        query = text("""
            SELECT * FROM gs_objects WHERE imei = :imei
        """)
        response_query = session.execute(query, {'imei': int_imei}).fetchall()
        session.close()

        json_response = Stech.object_to_json(response_query)
            
        if json_response:
            return json_response
        else:
            return False
    except SQLAlchemyError as ex:
        print(f"Error: {str(ex)}")
        return False

def buscar_existe_gs_user_object(user_id, imei):
    try:
        session = Connection.get_session(load_data('ENVIRONMENTS'))

        query = text("""
            SELECT object_id FROM gs_user_objects where user_id = :user_id and imei = :imei
        """)
        response_query = session.execute(query, {'user_id': user_id, 'imei': imei} ).fetchall()
        session.close()

        json_response = Stech.object_to_json(response_query)
            
        if json_response:
            return json_response
        else:
            return False
    except SQLAlchemyError as ex:
        print(f"Error: {str(ex)}")
        return False

def update_group_id(object_id):
    try:
        session = Connection.get_session(load_data('ENVIRONMENTS'))

        query = text("""
            UPDATE gs_user_objects SET group_id = :group_id WHERE object_id = :object_id
        """)
        session.execute(query, {'object_id': object_id, 'group_id': load_data('GROUP_ID')})
        session.commit()
        session.close()

        return True
    except SQLAlchemyError as ex:
        print(f"Error: {str(ex)}")
        return False

def insert_gs_user_object(user_id, imei):
    try:
        session = Connection.get_session(load_data('ENVIRONMENTS'))

        query = text("""
            INSERT INTO gs_user_objects (user_id, imei, group_id, driver_id, trailer_id, fav) VALUES (:user_id, :imei, :group_id, driver_id, trailer_id, fav)
        """)
        session.execute(query, {'user_id': user_id, 'imei': imei, 'group_id': load_data('GROUP_ID'), 'driver_id': 0, 'trailer_id': 0, 'fav': 0})
        session.commit()
        session.close()

        return True
    except SQLAlchemyError as ex:
        print(f"Error: {str(ex)}")
        return False

def sacar_grupo_smartreport(user_id, imei):
    try:
        session = Connection.get_session(load_data('ENVIRONMENTS'))

        query = text("""
            UPDATE gs_user_objects SET group_id = :group_id WHERE imei = :imei and user_id = :user_id
        """)
        session.execute(query, {'user_id': user_id, 'imei': imei, 'group_id': 0})
        session.commit()
        session.close()

        return True
    except SQLAlchemyError as ex:
        print(f"Error: {str(ex)}")
        return False