import mysql.connector
from mysql.connector import Error
import os
import re
import requests
from bs4 import BeautifulSoup
from requests.exceptions import HTTPError, Timeout, RequestException

# Import the get_token function from the utilities module
from utilities.get_token import get_token

# Database connection details
DB_HOST = 'mariadb'
DB_USER = 'root'
DB_PASSWORD = os.getenv('MYSQL_ROOT_PASSWORD', 'S3raph1n!')
DB_NAME = 'fhem'
TABLE_NAME = 'devices'

# FHEM server URL
FHEM_URL_BASE = "https://fhem.auwiesen2.de/fhem"

# Path to the log file created by the first script
LOG_FILE_PATH = 'fhem_script.log'

# Session to handle requests
session = requests.Session()

def connect_to_db():
    """Establish a connection to the MySQL database."""
    connection = None
    try:
        connection = mysql.connector.connect(
            host=DB_HOST,
            user=DB_USER,
            password=DB_PASSWORD,
            charset='utf8mb4',
            collation='utf8mb4_unicode_ci'
        )
        if connection.is_connected():
            print(f"Connected to MySQL Server.")
            return connection
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
    return connection

def create_database(connection):
    """Create the fhem database if it doesn't exist."""
    try:
        cursor = connection.cursor()
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {DB_NAME} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;")
        print(f"Database '{DB_NAME}' created or already exists with utf8mb4_unicode_ci collation.")
    except Error as e:
        print(f"Error creating database: {e}")

def create_table(connection):
    """Create the devices table in the fhem database."""
    try:
        cursor = connection.cursor()
        cursor.execute(f"USE {DB_NAME};")
        create_table_query = f"""
        CREATE TABLE IF NOT EXISTS {TABLE_NAME} (
            Device_id VARCHAR(255) PRIMARY KEY,
            Room VARCHAR(255),
            alias VARCHAR(255) NOT NULL
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
        """
        cursor.execute(create_table_query)
        print(f"Table '{TABLE_NAME}' created or already exists in database '{DB_NAME}'.")
    except Error as e:
        print(f"Error creating table: {e}")

def insert_device_data(connection, device_id, room, alias):
    """Insert a device's data into the devices table."""
    try:
        cursor = connection.cursor()
        insert_query = f"""
        INSERT INTO {TABLE_NAME} (Device_id, Room, alias)
        VALUES (%s, %s, %s)
        ON DUPLICATE KEY UPDATE Room = VALUES(Room), alias = VALUES(alias);
        """
        cursor.execute(insert_query, (device_id, room, alias))
        connection.commit()
        print(f"Inserted/Updated Device: {device_id}, Room: {room}, Alias: {alias}")
    except Error as e:
        print(f"Error inserting data: {e}")

def get_device_details(device_name, csrf_token):
    """Fetch the room and alias attributes for a given device from FHEM."""
    try:
        detail_url = f"{FHEM_URL_BASE}?detail={device_name}"
        headers = {
            "X-FHEM-csrfToken": csrf_token,
            "Content-Type": "application/x-www-form-urlencoded",
            "Accept": "application/json"
        }
        response = session.get(detail_url, headers=headers, timeout=10)
        response.raise_for_status()

        soup = BeautifulSoup(response.text, 'html.parser')
        attributes_table = soup.find('table', class_='block wide attributes')

        alias = None
        room = None
        if attributes_table:
            rows = attributes_table.find_all('tr')
            for row in rows:
                name_div = row.find('div', class_='dname')
                value_div = row.find('div', class_='dval')
                if name_div and value_div:
                    attr_name = name_div.text.strip()
                    attr_value = value_div.text.strip()
                    if attr_name == "alias":
                        alias = attr_value
                    elif attr_name == "room":
                        room = attr_value
        
        return alias, room
    
    except (HTTPError, Timeout) as err:
        print(f"Error fetching details for device {device_name}: {err}")
        return None, None
    except RequestException as req_err:
        print(f"An error occurred: {req_err}")
        return None, None

def read_log_and_insert_into_db(connection, csrf_token):
    """Read the log file, fetch device details, and insert them into the database."""
    try:
        with open(LOG_FILE_PATH, 'r') as log_file:
            for line in log_file:
                match = re.search(r'Device: (MA_[a-zA-Z0-9]+), State:', line)
                if match:
                    device_name = match.group(1)
                    alias, room = get_device_details(device_name, csrf_token)
                    if alias and room:
                        insert_device_data(connection, device_name, room, alias)
                    elif alias:  # In case room is not defined
                        insert_device_data(connection, device_name, None, alias)
    
    except FileNotFoundError:
        print(f"Log file '{LOG_FILE_PATH}' not found.")

def main():