import os
import sys
import pyodbc
import csv
from configparser import ConfigParser
import paramiko
import time
import re
import html

# Load configuration from a file (e.g., config.ini)

config = ConfigParser()
config.read('config.ini')

groupingIds = config['Clients']['GroupIDs'].split(',')
groupingIds = [int(id) for id in groupingIds]
groupingIds_str = ",".join(map(str, groupingIds))
cliCount = 0
server = config['Database']['Server']
database = config['Database']['Database']
username = config['Database']['Username']
password = config['Database']['Password']
output_csv = config['Files']['output']
SFTPAddress = config['SFTP']['address']
SFTPPort = config['SFTP']['port']
SFTPUsername= config['SFTP']['username']
SFTPPassword = config['SFTP']['password']
SFTPDestination = config['SFTP']['destination']

connection_string = (
    f"DRIVER={{ODBC Driver 18 for SQL Server}};"
    f"SERVER={server};"
    f"DATABASE={database};"
    f"UID={username};"
    f"PWD={password};"
    f"TrustServerCertificate=yes;"
)

def sanitize_field(value):
    if isinstance(value, str):
        value = html.unescape(value)
        value = value.replace('\n', ' ').replace('\r', ' ').replace('\t', ' ')
        value = value.replace(';', ',')
        value = value.replace('"', '')
        value = re.sub(r'\s+', ' ', value)
        value = value.strip()
        return value
    return value

try:
    with pyodbc.connect(connection_string) as connection:
        with connection.cursor() as cursor:
            clients_query = f"""
            SELECT DISTINCT
                m.Client_No,
                m.ClientID,
                m.Name,
                m.Location,
                m.Loc2,
                m.OKPassword,
                pt.Name,
                ac.ControlName,
                REPLACE(REPLACE(REPLACE(REPLACE(CAST(m.SpecRequest AS VARCHAR(MAX)), CHAR(13), '<br>'), CHAR(10), '<br>'), CHAR(9), '<br>'), ';', '') AS SpecRequest,
                REPLACE(REPLACE(REPLACE(REPLACE(CAST(m.SP_Page AS VARCHAR(MAX)), CHAR(13), '<br>'), CHAR(10), '<br>'), CHAR(9), '<br>'), ';', '') AS SP_page,
                m.DecommissionDate,
                REPLACE(REPLACE(REPLACE(REPLACE(CAST(m.DecommissionReason AS VARCHAR(MAX)), CHAR(13), '<br>'), CHAR(10), '<br>'), CHAR(9), '<br>'), ';', '') AS DecommissionReason
            FROM
                Memalarm m
            JOIN
                ClientGroupingAssign cg ON m.Client_No = cg.Client_No
            LEFT JOIN
                AccessControlNames ac ON m.Client_No = ac.ClientNo
            LEFT JOIN
                PanelTypes pt ON m.PanelTypeId = pt.IDPanelTypes
            WHERE
                cg.ClientGroupingID IN ({groupingIds_str})
                AND (m.TemplateType = '0' OR m.TemplateType IS NULL)
            """

            # Other queries remain the same
            users_query = """
            SELECT
                u.UserId,
                u.User_Name,
                u.PhoneNo3,
                u.Email
            FROM
                Muser u
            JOIN
                UserToClient uc ON u.UserId = uc.UserId
            WHERE
                u.Type IN ('U','Z','E')
                AND uc.ClientNo = ?;
            """

            zones_query = """
            SELECT
                z.Zone_No,
                z.Zone_area
            FROM
                Mzone z
            WHERE
                z.Client_No = ?;
            """

            key_query = """
            SELECT k.Name
                ,REPLACE(REPLACE(REPLACE(CAST(k.Description AS VARCHAR(MAX)), CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' ') AS Description
            FROM
                [Patriot].[dbo].[Key] k
            WHERE
                k.ClientNo = ?;
            """

            # Execute the clients query
            cursor.execute(clients_query)
            clients = cursor.fetchall()
            with open(output_csv, mode='w', newline='', encoding='latin-1', errors='replace') as csvfile:
                csv_writer = csv.writer(csvfile, delimiter=';')

                for client in clients:
                    cliCount += 1
                    csv_writer.writerow(['Client_No', 'ClientID', 'Name', 'Location', 'Loc2', 'OKPassword', 'PanelType', 'DeviceRef', 'SpecRequest', 'ExtraInfo', 'DecommissionDate', 'DecommissionReason'])

                    csv_writer.writerow([sanitize_field(field) for field in client])

                    csv_writer.writerow(['UserID', 'User_Name', 'PhoneNo3', 'Email'])

                    cursor.execute(users_query, client.Client_No)
                    users = cursor.fetchall()

                    for user in users:
                        csv_writer.writerow([sanitize_field(field) for field in user])

                    csv_writer.writerow(['Zone_No', 'Zone_area'])

                    cursor.execute(zones_query, client.Client_No)
                    zones = cursor.fetchall()

                    for zone in zones:
                        csv_writer.writerow([sanitize_field(field) for field in zone])

                    csv_writer.writerow(['Key_Name', 'Key_Desc'])

                    cursor.execute(key_query, client.Client_No)
                    keys = cursor.fetchall()

                    for key in keys:
                        csv_writer.writerow([sanitize_field(field) for field in key])

                    print(f"{cliCount} Fetched: {client.Client_No}, {client[2]}")
                    time.sleep(0.1)


            print(f"{cliCount} clients exported successfully to {output_csv}")

            output_csv_path = f"{output_csv}"  # Path to the CSV file on the Windows server

            try:
                # Check if the file exists on the Windows server
                if not os.path.exists(output_csv_path):
                    raise FileNotFoundError(f"File {output_csv_path} does not exist")

                # Create an SSH client and set the policy to automatically add host keys
                ssh_client = paramiko.SSHClient()
                ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())

                # Connect to the Ubuntu server using SSH with username and password
                print(f"Connecting to {SFTPAddress}...")
                ssh_client.connect(SFTPAddress, username=SFTPUsername, password=SFTPPassword)
                print("Connected successfully.")

                # Define the remote destination path on the Ubuntu server
                print(f"Transfering file from {output_csv_path} to {SFTPDestination}{output_csv}...")

                # Transfer the file from the Windows server to the Ubuntu server
                sftp = ssh_client.open_sftp()
                try:
                    sftp.put(output_csv_path, os.path.join(SFTPDestination, output_csv))
                    print("File transfer completed successfully.")
                except Exception as e:
                    print(f"Error transferring file: {e}")

                # Close the SFTP connection and SSH client
                sftp.close()
                ssh_client.close()
                print("Disconnected.")

            except FileNotFoundError as e:
                print(e)
            except Exception as e:
                print(f"An error occurred: {e}")


except pyodbc.Error as e:
    import sys
    print(f"An error occurred: {e}", file=sys.stderr)