import threading
import os
import pandas as pd
from watchdog.observers import Observer
from watchdog.events import FileSystemEventHandler
from time import sleep
from clickhouse_driver import Client
import logging
import re
import csv

# Configure logging
logging.basicConfig(filename='fffilenew.log', level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

# Constants
DATA_DIR = 'test'
CLICKHOUSE_HOST = '192.168.9.144'
CLICKHOUSE_PORT = 9000
CLUSTER_NAME = 'company_cluster'
CLICKHOUSE_PASSWORD = ''

def create_client():
    return Client(host=CLICKHOUSE_HOST, port=CLICKHOUSE_PORT, password=CLICKHOUSE_PASSWORD)

def create_database(client, database_name):
    try:
        client.execute(f'CREATE DATABASE IF NOT EXISTS {database_name} ON CLUSTER {CLUSTER_NAME}')
        logging.info(f"Created/Verified database: {database_name}")
    except Exception as e:
        logging.error(f'Error creating database: {database_name}: {e}')

def create_table(client, database_name, table_name, schema, order_by_column):
    try:
        query = f'CREATE TABLE IF NOT EXISTS {database_name}.{table_name} ON CLUSTER {CLUSTER_NAME} ({schema}) ENGINE = ReplicatedMergeTree() ORDER BY {order_by_column}'
        client.execute(query)
        logging.info(f"Created/Verified table: {table_name}")
    except Exception as e:
        logging.error(f'Error creating table: {table_name}: {e}')

def process_column_name(column_name):
    if ' ' in column_name:
        return re.sub(r'\s', '_', column_name)
    else:
        return column_name

def process_file(file_path, client):
    try:
        file = pd.read_csv(file_path, na_values=['NULL'], encoding='cp1251')
        file = file.rename(columns={"Item Type": "ProductType"})
        file.columns = [process_column_name(col) for col in file.columns]
        schema = ','.join([f'{col} String' for col in file.columns])
        order_by_column = file.columns[0]  # Use the first column as the ORDER BY column
    except Exception as e:
        logging.error(f"Error processing file {file_path}: {str(e)}")
        return None, None, None

    return file, schema, order_by_column

def process_data(client, file, database_name, name_of_table, schema, order_by_column):
    try:
        file = file.astype(str)
        data = file.to_dict(orient='records')
        query = f'INSERT INTO {database_name}.{name_of_table} ({",".join(file.columns)}) VALUES'
        client.execute(query, data)
        logging.info(f"Inserted data into {name_of_table}")
    except Exception as e:
        logging.error(f'Error inserting data into {name_of_table}: {e}')

def main():
    client = create_client()
    observer = Observer()
    observer.schedule(Handler(client), path=DATA_DIR, recursive=True)
    observer.start()
    print('start')
    try:
        while True:
            sleep(1)
    except KeyboardInterrupt:
        observer.stop()
    observer.join()

class Handler(FileSystemEventHandler):

    def __init__(self, client):
        self.client = client

    def process_created_file(self, event):
        if not hasattr(event, 'is_threaded'):
            event.is_threaded = True
            delimiter = self.detect_delimiter(event.src_path)
            t = threading.Thread(target=self.process_file, args=(event, delimiter))
            t.start()

    def detect_delimiter(self, file_path):
        with open(file_path, 'r', encoding='cp1251') as file:
            dialect = csv.Sniffer().sniff(file.read(2048))
            print(dialect.delimiter)
            return dialect.delimiter

    def process_file(self, event):
        file_path = event.src_path
        for bd in os.listdir(DATA_DIR):
            if bd.endswith('.csv'):
                file_path = os.path.join(DATA_DIR, bd)
                a = 0
                b = 1
                while a != b:
                    a = os.path.getsize(file_path)
                    print(a)
                    sleep(5)
                    b = os.path.getsize(file_path)
                    print(b)
                if os.path.getsize(file_path) == 0:
                    logging.warning(f"File is empty: {file_path}")
                    return

                try:
                    with open(file_path, 'r', encoding='cp1251') as file:
                        dialect = csv.Sniffer().sniff(file.read(2048))
                        delimiter = dialect.delimiter
                        print(delimiter)
                    file, schema, order_by_column = process_file(file_path, self.client, delimiter)
                    if file is None:
                        return

                    name_of_table = bd.replace('.csv', '')
                    database_name = bd.replace('.csv', '')
                    create_database(self.client, database_name)
                    create_table(self.client, database_name, name_of_table, schema, order_by_column)

                    process_data(self.client, file, database_name, name_of_table, schema, order_by_column)
                    print('success')

                    os.remove(file_path)
                except Exception as e:
                    logging.error(f"Error processing file {file_path}: {str(e)}")

    def on_created(self, event):
        self.process_created_file(event)

    def on_deleted(self, event):
        logging.info(f"File deleted: {event.src_path}")

    def on_moved(self, event):
        logging.info(f"File moved: {event.src_path} -> {event.dest_path}")

if __name__ == '__main__':
    main()