import re
from datetime import datetime
import glob
import os
import mysql.connector
import chardet

def detect_encoding(file_path):
    """Detecta o encoding do arquivo."""
    with open(file_path, 'rb') as file:
        raw_data = file.read()
        result = chardet.detect(raw_data)
        return result['encoding']

def create_tables(db_cursor):
    """Cria as tabelas no banco de dados, caso não existam."""
    db_cursor.execute("""
    CREATE TABLE IF NOT EXISTS logs_cabecalhos (
        id INT AUTO_INCREMENT PRIMARY KEY,
        callsign VARCHAR(50) NOT NULL,
        contest VARCHAR(50) NOT NULL,
        contest_id INT DEFAULT 1,
        category_operator VARCHAR(50),
        category_assisted VARCHAR(50),
        category_band VARCHAR(50),
        category_power VARCHAR(50),
        category_mode VARCHAR(50),
        category_transmitter VARCHAR(50),
        category_overlay VARCHAR(50),
        claimed_score INT DEFAULT 0,
        final_score INT DEFAULT 0,
        name VARCHAR(100),
        location VARCHAR(30),
        address TEXT,
        address_city VARCHAR(100),
        address_state_province VARCHAR(100),
        address_postalcode VARCHAR(50),
        address_country VARCHAR(100),
        operators TEXT,
        email VARCHAR(100),
        club VARCHAR(100),
        certificate VARCHAR(10),
        created_by VARCHAR(50),
        x_source VARCHAR(50),
        UNIQUE (callsign, contest)
    );
    """)

    db_cursor.execute("""
    CREATE TABLE IF NOT EXISTS logs_qsos (
        id INT AUTO_INCREMENT PRIMARY KEY,
        log_id INT NOT NULL,
        frequency VARCHAR(50),
        mode VARCHAR(50),
        date_time DATETIME NOT NULL,
        call_sent VARCHAR(50),
        rst_sent VARCHAR(10),
        exch_sent VARCHAR(50),
        call_rcvd VARCHAR(50),
        rst_rcvd VARCHAR(10),
        exch_rcvd VARCHAR(50),
        FOREIGN KEY (log_id) REFERENCES logs_cabecalhos(id) ON DELETE CASCADE,
        UNIQUE (log_id, call_sent, date_time)
    );
    """)

def parse_log_file(file_path):
    """Lê e analisa o arquivo de log, retornando cabeçalho e contatos."""
    encoding = detect_encoding(file_path)
    print(f"Lendo arquivo {file_path} com encoding {encoding}...")
    with open(file_path, 'r', encoding=encoding, errors='replace') as file:
        lines = file.readlines()
    
    header = {}
    contacts = []
    in_contacts_section = False

    for line in lines:
        line = line.strip()
        if not line:
            continue

        if line.startswith("QSO:"):
            in_contacts_section = True
            contact_data = line.split()
            if len(contact_data) >= 9:
                try:
                    datetime_str = f"{contact_data[3]} {contact_data[4]}"
                    timestamp = datetime.strptime(datetime_str, "%Y-%m-%d %H%M")
                    contact = {
                        "frequency": contact_data[1],
                        "mode": contact_data[2],
                        "date_time": timestamp,
                        "call_sent": contact_data[5],
                        "rst_sent": contact_data[6],
                        "exch_sent": contact_data[7],
                        "call_rcvd": contact_data[8],
                        "rst_rcvd": contact_data[9],
                        "exch_rcvd": contact_data[10] if len(contact_data) > 10 else None
                    }
                    contacts.append(contact)
                except ValueError as e:
                    print(f"Erro ao analisar data/hora: {datetime_str} - {e}")
            else:
                print(f"Contato mal formatado: {contact_data}")
        elif ": " in line and not in_contacts_section:
            key, value = line.split(": ", 1)
            key = key.lower().replace('-', '_')
            header[key] = value.strip()

    # Adiciona valores padrão se campos estiverem ausentes
    header['contest_id'] = int(header.get('contest_id', 1))  # Valor padrão 1

    # Truncar valores longos para evitar erros de inserção
    if 'location' in header and len(header['location']) > 30:
        header['location'] = header['location'][:30]
    if 'created_by' in header and len(header['created_by']) > 50:
        header['created_by'] = header['created_by'][:50]
    if 'x_source' in header and len(header['x_source']) > 50:
        header['x_source'] = header['x_source'][:50]        

    return header, contacts

def insert_data_to_db(header, contacts, db_cursor):
    """Insere o cabeçalho e os contatos no banco de dados."""
    expected_fields = {
        'callsign': '',
        'contest': '',
        'contest_id': 1,
        'category_operator': '',
        'category_assisted': '',
        'category_band': '',
        'category_power': '',
        'category_mode': '',
        'category_transmitter': '',
        'category_overlay': '',
        'claimed_score': 0,
        'final_score': 0,
        'name': '',
        'location': '',
        'address': '',
        'address_city': '',
        'address_state_province': '',
        'address_postalcode': '',
        'address_country': '',
        'operators': '',
        'email': '',
        'club': '',
        'certificate': '',
        'created_by': '',
        'x_source': ''
    }

    for field, default_value in expected_fields.items():
        if field not in header:
            header[field] = default_value

    log_insert_query = """
    INSERT INTO logs_cabecalhos (
        callsign, contest, contest_id, category_operator, category_assisted, 
        category_band, category_power, category_mode, category_transmitter, 
        category_overlay, claimed_score, final_score, name, location, address, 
        address_city, address_state_province, address_postalcode, 
        address_country, operators, email, club, certificate, created_by, x_source
    ) VALUES (
        %(callsign)s, %(contest)s, %(contest_id)s, %(category_operator)s, %(category_assisted)s,
        %(category_band)s, %(category_power)s, %(category_mode)s, %(category_transmitter)s,
        %(category_overlay)s, %(claimed_score)s, %(final_score)s, %(name)s, %(location)s, %(address)s,
        %(address_city)s, %(address_state_province)s, %(address_postalcode)s,
        %(address_country)s, %(operators)s, %(email)s, %(club)s, %(certificate)s,
        %(created_by)s, %(x_source)s
    ) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);
    """
    db_cursor.execute(log_insert_query, header)
    log_id = db_cursor.lastrowid

    contact_insert_query = """
    INSERT INTO logs_qsos (
        log_id, frequency, mode, date_time, call_sent, rst_sent, exch_sent,
        call_rcvd, rst_rcvd, exch_rcvd
    ) VALUES (
        %(log_id)s, %(frequency)s, %(mode)s, %(date_time)s, %(call_sent)s,
        %(rst_sent)s, %(exch_sent)s, %(call_rcvd)s, %(rst_rcvd)s, %(exch_rcvd)s
    ) ON DUPLICATE KEY UPDATE
        rst_sent = VALUES(rst_sent),
        rst_rcvd = VALUES(rst_rcvd);
    """
    for contact in contacts:
        contact['log_id'] = log_id
        db_cursor.execute(contact_insert_query, contact)

# Configurações do banco
db_connection = mysql.connector.connect(
    host='localhost',
    database='radio_log',
    user='root',
    password='root',
    port=3306
)
db_cursor = db_connection.cursor()

try:
    create_tables(db_cursor)
    directory_path = r'C:\commbox\web\projetos\contest\pages\logs'
    log_files = glob.glob(os.path.join(directory_path, "*.log"))
    print("Arquivos encontrados.")

    if not log_files:
        print(f"Nenhum arquivo .log encontrado no diretório: {directory_path}")
    else:
        for file_path in log_files:
            print(f"Processando: {file_path}")
            header, contacts = parse_log_file(file_path)
            insert_data_to_db(header, contacts, db_cursor)
        db_connection.commit()
        print("Dados inseridos com sucesso!")

except Exception as e:
    db_connection.rollback()
    print(f"Erro durante a execução: {e}")

finally:
    db_cursor.close()
    db_connection.close()