import psycopg2 as pg
from sshtunnel import SSHTunnelForwarder
from config import host, user, password, db_name, jid, jidpassword, port, SSH_H
from multiprocessing import connection
from dateutil import parser
import datetime, time
import xmpp
#Проверка соединения и само соединение
try:
    print('Connecting to the PostgreSQL Database...')
    ssh_tunnel = SSHTunnelForwarder(
    (SSH_H, 334),
    ssh_username = "artem_valiakhmetov",
    ssh_private_key = 'D:/keys/home/.ssh/id_rsa ',
    ssh_private_key_password = '',
    remote_bind_address = ("localhost", 5432)
    )
    ssh_tunnel.start()
    print("Tunnel start!")
    conn = pg.connect(
        host = "localhost",
        port = ssh_tunnel.local_bind_port,
        user = "postgres",
        password = "",
        database = "sharixdrive"
    )
    print("Success ssh connect!")
    print("Successfully connection...")
except Exception as ex:
    print("Error connection to database...")
    print(ex)

    def add_client_to_metaservice(user_id, ticket_status, id_metaservice):
        with conn.cursor() as cursor:
            insert_into_client_to_metaservice = "INSERT INTO client (user_id, requirements, status, ticket_status, id_metaservice, is_global, is_visible) VALUES (%s,'sgGshHz','A',%s,%s,'t','f')"
            val = [user_id, ticket_status, id_metaservice]
            cursor.execute(insert_into_client_to_metaservice)
            conn.commit()
    def change_client_visibility(user_id, id_metaservice, is_visible):
        with conn.cursor() as cursor:
            update_client_visibility = "UPDATE client SET is_visible = %s WHERE user_id = %s AND id_metaservice = %s"
            val = [is_visible, user_id, id_metaservice]
            cursor.execute(update_client_visibility)
            conn.commit()
    def change_client_globality(user_id, id_metaservice, is_global):
        with conn.cursor() as cursor:
            update_client_globality = "UPDATE client SET is_global = %s WHERE user_id = %s AND id_metaservice = %s"
            val = [is_global, user_id, id_metaservice]
            cursor.execute(update_client_globality)
            conn.commit()
    def check_client_status():

    def find_client_tickets():

    def add_provider_to_metaservice(id, id_metaservice):
        with conn.cursor() as cursor:
            insert_into_provider_to_metaservice = "INSERT INTO provider (type, id_company, user_id, id_metaservice, requirements, status, ticket_status, is_global, is_visible) VALUES (%s, %s, %s, %s, %s, status, ticket_status, is_global, is_visible)"
            val = [type, id_company, user_id, id_metaservice, requirements, status, ticket_status, is_global, is_visible]
            cursor.execute(insert_into_provider_to_metaservice)
            conn.commit()
    def change_provider_visibility(id, id_metaservice, is_visible):
        with conn.cursor() as cursor:
            update_provider_visibility = "UPDATE provider SET is_visible = %s WHERE id = %s AND id_metaservice = %s"
            val = [is_visible, id, id_metaservice]
            cursor.execute(update_provider_visibility)
            conn.commit()
    def change_provider_globality(id, id_metaservice, is_global):
        with conn.cursor() as cursor:
            update_provider_visibility = "UPDATE provider SET is_global = %s WHERE id = %s AND id_metaservice = %s"
            val = [is_global, id, id_metaservice]
            cursor.execute(update_provider_visibility)
            conn.commit()