import sys
import os
import time
import datetime
import csv
import logging
import traceback
import mysql.connector
import win32serviceutil
import win32service
import win32event
import servicemanager

class AbakusMysqlService(win32serviceutil.ServiceFramework):
    _svc_name_ = "AbakusMysqlReceiver"
    _svc_display_name_ = "AbakusMysqlReceiver"
    _svc_description_ = "AbakusMysqlReceiver"

    def __init__(self, args):
        win32serviceutil.ServiceFramework.__init__(self, args)
        self.hWaitStop = win32event.CreateEvent(None, 0, 0, None)
        self.running = True
        
        # Find the base directory where the script is running
        try:
            if getattr(sys, 'frozen', False):
                self.base_path = os.path.dirname(sys.executable)
            else:
                self.base_path = os.path.dirname(os.path.abspath(__file__))
                
            # Create the logs directory
            log_dir = os.path.join(self.base_path, 'logs')
            if not os.path.exists(log_dir):
                os.makedirs(log_dir)
                
            # DEBUG LEVEL LOGGING CONFIGURATION
            logging.basicConfig(
                filename=os.path.join(log_dir, 'service.log'),
                level=logging.DEBUG,
                format='%(asctime)s - %(levelname)s - %(message)s',
                encoding='utf-8'
            )
            logging.debug(f"Service initialized. Base path: {self.base_path}")
        except Exception as e:
            # If logging setup fails, write to an emergency file in C drive
            with open('C:\\abakus_emergency_error.txt', 'w') as f:
                f.write(f"Init error: {str(e)}\n")

    def SvcStop(self):
        self.ReportServiceStatus(win32service.SERVICE_STOP_PENDING)
        win32event.SetEvent(self.hWaitStop)
        self.running = False
        logging.info("Service is stopping...")

    def SvcDoRun(self):
        try:
            logging.info("Service started.")
            
            while self.running:
                logging.debug("Starting a new cycle...")
                try:
                    conf = self.read_conf()
                    if conf:
                        logging.debug("Configuration read successfully. Processing data...")
                        self.process_data(conf)
                    else:
                        logging.error("conf.txt could not be read or is missing!")
                except Exception as e:
                    # Log the exact line of error in the main loop
                    logging.error(f"Main loop error: {e}", exc_info=True)
                
                # Wait period
                period_min = int(conf.get('get_period', 5)) if 'conf' in locals() and conf else 5
                logging.debug(f"Waiting for {period_min} minutes until next cycle...")
                
                rc = win32event.WaitForSingleObject(self.hWaitStop, period_min * 60 * 1000)
                if rc == win32event.WAIT_OBJECT_0:
                    logging.debug("Stop signal received during wait period.")
                    break

            logging.info("Service loop exited naturally.")
            
        except Exception as critical_error:
            # Catching fatal errors that crash the whole service
            logging.critical(f"FATAL ERROR causing service to stop: {critical_error}", exc_info=True)

    def read_conf(self):
        conf = {}
        conf_path = os.path.join(self.base_path, 'conf.txt')
        logging.debug(f"Reading configuration from: {conf_path}")
        
        if not os.path.exists(conf_path):
            logging.error(f"Configuration file not found at {conf_path}")
            return None
            
        try:
            with open(conf_path, 'r', encoding='utf-8') as f:
                for line in f:
                    line = line.strip()
                    if '=' in line and not line.startswith('#'):
                        key, value = line.split('=', 1)
                        conf[key.strip()] = value.strip()
            return conf
        except Exception as e:
            logging.error(f"Configuration read error: {e}", exc_info=True)
            return None

    def get_last_id(self):
        path = os.path.join(self.base_path, 'last_id.txt')
        logging.debug(f"Checking last_id from: {path}")
        if os.path.exists(path):
            try:
                with open(path, 'r', encoding='utf-8') as f:
                    content = f.read().strip()
                    last_id = int(content) if content else 0
                    logging.debug(f"Last ID read as: {last_id}")
                    return last_id
            except ValueError as ve:
                logging.error(f"ValueError while reading last_id: {ve}", exc_info=True)
                return 0
        logging.debug("last_id.txt not found. Returning 0.")
        return 0

    def set_last_id(self, last_id):
        path = os.path.join(self.base_path, 'last_id.txt')
        try:
            with open(path, 'w', encoding='utf-8') as f:
                f.write(str(last_id))
            logging.debug(f"Last ID updated successfully to: {last_id}")
        except Exception as e:
            logging.error(f"Error writing last_id: {e}", exc_info=True)

    def process_data(self, conf):
        columns_str = conf['table_columns']
        columns_list = [c.strip() for c in columns_str.split(',')]
        unique_id_col = conf['unique_id']
        max_line = int(conf['max_line'])
        
        try:
            logging.debug(f"Connecting to MySQL Database at {conf['mysql_server_ip']}:{conf['mysql_server_port']}...")
            conn = mysql.connector.connect(
                host=conf['mysql_server_ip'],
                port=int(conf['mysql_server_port']),
                user=conf['db_user'],
                password=conf['db_pass'],
                database=conf['database_name'],
                connection_timeout=10,
                use_pure=True
            )
            cursor = conn.cursor(dictionary=True)
            logging.debug("Database connection successful.")
            
            while self.running:
                last_id = self.get_last_id()
                
                query = f"""
                    SELECT {columns_str} 
                    FROM {conf['table_name']} 
                    WHERE {unique_id_col} > %s 
                    ORDER BY {unique_id_col} ASC 
                    LIMIT %s
                """
                logging.debug(f"Executing query with last_id: {last_id}, limit: {max_line}")
                
                cursor.execute(query, (last_id, max_line))
                rows = cursor.fetchall()

                if not rows:
                    logging.info("No new records found in this cycle.")
                    break
                
                logging.debug(f"Fetched {len(rows)} rows. Creating CSV file...")
                timestamp = datetime.datetime.now().strftime("%Y_%m_%d_%H%M%S")
                filename = f"cdr_{timestamp}.csv"
                file_full_path = os.path.join(conf['cdr_file_path'], filename)
                
                with open(file_full_path, 'w', newline='', encoding='utf-8') as f:
                    writer = csv.DictWriter(f, fieldnames=columns_list)
                    writer.writeheader()
                    writer.writerows(rows)
                
                new_last_id = rows[-1][unique_id_col]
                self.set_last_id(new_last_id)
                logging.info(f"{filename} created. {len(rows)} records processed. New last_id: {new_last_id}")
                
                if len(rows) < max_line:
                    logging.debug("Fetched rows are less than max_line. Stopping chunk process for this cycle.")
                    break
                
                logging.debug("Wait 1 second before next chunk...")
                time.sleep(1)

        except mysql.connector.Error as err:
            logging.error(f"Database error: {err}", exc_info=True)
        except Exception as e:
            logging.error(f"Data processing error: {e}", exc_info=True)
        finally:
            logging.debug("Closing database connections...")
            if 'cursor' in locals() and cursor:
                cursor.close()
            if 'conn' in locals() and conn.is_connected():
                conn.close()

if __name__ == '__main__':
    if len(sys.argv) == 1:
        servicemanager.Initialize()
        servicemanager.PrepareToHostSingle(AbakusMysqlService)
        servicemanager.StartServiceCtrlDispatcher()
    else:
        win32serviceutil.HandleCommandLine(AbakusMysqlService)