# -*- coding: utf-8 -*-

import json
import paho.mqtt.client as mqtt
import ssl
import time
import logging
import os
import sys

# Setup Django environment
sys.path.append('/home/devuser/fomin/mqttuser/mqtt_project')  # Path to your Django project
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'mqtt_project.settings')  # Your Django settings module

# Initialize Django
import django
django.setup()

# Now import Django models and DB
from django.db import connection
from django.utils import timezone

# Logging setup
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

# MQTT settings
MQTT_BROKER = "localhost"
MQTT_PORT = 8883
MQTT_USERNAME = 'fomin_a'
MQTT_PASSWORD = 'Hs6#2vG#%8bxsKZf4'
MQTT_CLIENT_ID = "calculation_service"

# Topics
INPUT_TOPIC = "grand_beton/requestMaterialConsumption/input"
OUTPUT_TOPIC = "grand_beton/requestMaterialConsumption/output"

def ensure_connection():
    """Check and restore Django database connection"""
    try:
        if connection.connection and not connection.is_usable():
            connection.close()
    except Exception as e:
        logger.warning(f"Error checking connection: {e}")

def create_placeholders(count):
    """Create SQL placeholders for query"""
    return ','.join(['%s'] * count)

def process_calculation(request_params):
    """Main calculation logic based on request parameters"""
    ensure_connection()
    
    # Extract parameters
    id_param = request_params.get('id')
    bsu_code = request_params.get('bsu')
    id_plant = request_params.get('plant')
    dispencer = request_params.get('dispencer')
    time_start = request_params.get('timeStart')
    time_end = request_params.get('timeEnd')
    car = request_params.get('car')
    driver = request_params.get('driver')
    id_ttn = request_params.get('idTtn')
    order_id = request_params.get('order')
    time_start_exact = request_params.get('timeStartExact')
    
    # Convert datetime format if needed (from ISO to MySQL format)
    if time_start:
        time_start = time_start.replace('T', ' ')
    if time_end:
        time_end = time_end.replace('T', ' ')
    if time_start_exact:
        time_start_exact = time_start_exact.replace('T', ' ')
    
    logger.info(f"Processing request: id={id_param}, bsu={bsu_code}, plant={id_plant}, dispencer={dispencer}, "
                f"timeStart={time_start}, timeEnd={time_end}, car={car}, driver={driver}, "
                f"idTtn={id_ttn}, order={order_id}, timeStartExact={time_start_exact}")
    
    all_bsu_codes = []
    
    # Get BSU codes by plant
    if id_plant:
        with connection.cursor() as cursor:
            cursor.execute("SELECT code FROM BSU WHERE codePlant = %s", [id_plant])
            for row in cursor.fetchall():
                all_bsu_codes.append(row[0])
    
    # Add specific BSU code
    if bsu_code:
        all_bsu_codes.append(bsu_code)
    
    # Get BSU codes by dispencer
    if dispencer:
        with connection.cursor() as cursor:
            cursor.execute("SELECT codeBSU FROM silCem WHERE code = %s", [dispencer])
            for row in cursor.fetchall():
                all_bsu_codes.append(row[0])
    
    # Get TTN IDs by order
    ttn_ids = []
    if order_id:
        with connection.cursor() as cursor:
            cursor.execute("SELECT id FROM ttn WHERE idOrder = %s", [order_id])
            for row in cursor.fetchall():
                ttn_ids.append(row[0])
    
    # Remove duplicates
    all_bsu_codes = list(set(all_bsu_codes))
    logger.info(f"BSU codes to process: {all_bsu_codes}")
    logger.info(f"TTN IDs to process: {ttn_ids}")
    
    # Get silos mapping (dispencer -> silos code by BSU code)
    #  PHP: êëþ÷îì ÿâëÿåòñÿ codeBSU (êîä ÁÑÓ)
    silos_mapping = {}
    if all_bsu_codes:
        placeholders = create_placeholders(len(all_bsu_codes))
        query = f"""
            SELECT batcher, code, codeBSU 
            FROM silCem 
            WHERE codeBSU IN ({placeholders})
        """
        with connection.cursor() as cursor:
            cursor.execute(query, all_bsu_codes)
            for row in cursor.fetchall():
                batcher = row[0]      # dispencer
                code = row[1]         # silos code
                code_bsu = row[2]     # codeBSU (êîä ÁÑÓ)
                
                # Ïðèâîäèì ê ñòðîêå äëÿ åäèíîîáðàçèÿ
                code_bsu_str = str(code_bsu)
                batcher_str = str(batcher)
                
                if code_bsu_str not in silos_mapping:
                    silos_mapping[code_bsu_str] = {}
                silos_mapping[code_bsu_str][batcher_str] = code
    
    logger.info(f"Silos mapping built for BSU codes: {list(silos_mapping.keys())}")
    
    # Build query for weightManual data
    weight_manual_query = """
        SELECT wm.*, p.id as productId
        FROM weightManual wm
        JOIN product p ON wm.idProduct = p.id
        WHERE 1=1
    """
    weight_manual_params = []
    
    if id_param:
        weight_manual_query += " AND p.id = %s"
        weight_manual_params.append(id_param)
    
    if all_bsu_codes:
        placeholders = create_placeholders(len(all_bsu_codes))
        weight_manual_query += f" AND p.idPlant IN ({placeholders})"
        weight_manual_params.extend(all_bsu_codes)
    
    if order_id and ttn_ids:
        placeholders = create_placeholders(len(ttn_ids))
        weight_manual_query += f" AND p.idTtn IN ({placeholders})"
        weight_manual_params.extend(ttn_ids)
    
    if time_start and time_end:
        weight_manual_query += " AND p.timeStart BETWEEN %s AND %s"
        weight_manual_params.extend([time_start, time_end])
    elif time_start:
        weight_manual_query += " AND p.timeStart >= %s"
        weight_manual_params.append(time_start)
    
    if time_start_exact:
        weight_manual_query += " AND p.timeStart = %s"
        weight_manual_params.append(time_start_exact)
    
    if car:
        weight_manual_query += " AND p.car = %s"
        weight_manual_params.append(car)
    
    if driver:
        weight_manual_query += " AND p.driver = %s"
        weight_manual_params.append(driver)
    
    if id_ttn:
        weight_manual_query += " AND p.idTtn = %s"
        weight_manual_params.append(id_ttn)
    
    # Execute weightManual query
    weight_manual_data = {}
    if weight_manual_params:
        with connection.cursor() as cursor:
            cursor.execute(weight_manual_query, weight_manual_params)
            columns = [col[0] for col in cursor.description]
            for row in cursor.fetchall():
                row_dict = dict(zip(columns, row))
                product_id = row_dict['productId']
                code = int(row_dict['code'])
                weight = float(row_dict['weight'])
                if product_id not in weight_manual_data:
                    weight_manual_data[product_id] = {}
                weight_manual_data[product_id][code] = weight
    
    # Build query for reportCurrentLoop data
    report_query = """
        SELECT rcl.*, p.idPlant
        FROM reportCurrentLoop rcl
        JOIN product p ON rcl.idProduct = p.id
        WHERE 1=1
    """
    report_params = []
    
    if id_param:
        report_query += " AND p.id = %s"
        report_params.append(id_param)
    
    if all_bsu_codes:
        placeholders = create_placeholders(len(all_bsu_codes))
        report_query += f" AND p.idPlant IN ({placeholders})"
        report_params.extend(all_bsu_codes)
    
    if order_id and ttn_ids:
        placeholders = create_placeholders(len(ttn_ids))
        report_query += f" AND p.idTtn IN ({placeholders})"
        report_params.extend(ttn_ids)
    
    if time_start and time_end:
        report_query += " AND p.timeStart BETWEEN %s AND %s"
        report_params.extend([time_start, time_end])
    elif time_start:
        report_query += " AND p.timeStart >= %s"
        report_params.append(time_start)
    
    if time_start_exact:
        report_query += " AND p.timeStart = %s"
        report_params.append(time_start_exact)
    
    if car:
        report_query += " AND p.car = %s"
        report_params.append(car)
    
    if driver:
        report_query += " AND p.driver = %s"
        report_params.append(driver)
    
    if id_ttn:
        report_query += " AND p.idTtn = %s"
        report_params.append(id_ttn)
    
    # Execute report query
    calculations_by_dispencer = {}
    
    # Ïåðåìåííûå äëÿ ðàñ÷åòà totalVProduct è totalVProductExpected
    total_v_product = 0
    total_v_product_expected = 0
    unique_v_loops_by_product = {}
    
    if report_params:
        with connection.cursor() as cursor:
            cursor.execute(report_query, report_params)
            columns = [col[0] for col in cursor.description]
            for row in cursor.fetchall():
                row_dict = dict(zip(columns, row))
                
                code = int(row_dict['code'])
                weight_fact_loop = float(row_dict['weightFactLoop'] or 0)
                weight_recipe_loop = float(row_dict['weightRecipeLoop'] or 0)
                humidity_korr = float(row_dict.get('humidityKorr') or 0)
                id_product = int(row_dict['idProduct'])
                dispencer_value = str(row_dict['dispencer'] or '')
                id_plant_value = int(row_dict['idPlant'])
                
                # Ñîáèðàåì óíèêàëüíûå êîìáèíàöèè vLoop è loopNumber äëÿ ðàñ÷åòà totalVProductExpected
                v_loop_value = float(row_dict.get('vLoop') or 0)
                loop_number_value = int(row_dict.get('loopNumber') or 0)
                combination_key = f"{v_loop_value}|{loop_number_value}"
                
                if id_product not in unique_v_loops_by_product:
                    unique_v_loops_by_product[id_product] = {}
                
                if combination_key not in unique_v_loops_by_product[id_product]:
                    unique_v_loops_by_product[id_product][combination_key] = v_loop_value
                
                # Ê recipeSum ïðèáàâëÿåì humidityKorr
                adjusted_recipe_loop = weight_recipe_loop + humidity_korr
                
                # Adjust fact loop with manual weight
                adjusted_fact_loop = weight_fact_loop
                if id_product in weight_manual_data and code in weight_manual_data[id_product]:
                    adjusted_fact_loop += weight_manual_data[id_product][code]
                
                if code not in calculations_by_dispencer:
                    calculations_by_dispencer[code] = {}
                
                key = f"{dispencer_value}|{id_plant_value}"
                if key not in calculations_by_dispencer[code]:
                    calculations_by_dispencer[code][key] = {
                        'recipeSum': 0,
                        'factSum': 0,
                        'dispencer': dispencer_value,
                        'idPlant': id_plant_value
                    }
                
                # Èñïîëüçóåì adjusted_recipe_loop âìåñòî weight_recipe_loop
                calculations_by_dispencer[code][key]['recipeSum'] += adjusted_recipe_loop
                calculations_by_dispencer[code][key]['factSum'] += adjusted_fact_loop
    
    # Ïîëó÷àåì çíà÷åíèÿ vProduct èç òàáëèöû product äëÿ totalVProduct
    # Build query for product data
    product_query = """
        SELECT p.id, p.vProduct
        FROM product p
        WHERE 1=1
    """
    product_params = []
    
    if id_param:
        product_query += " AND p.id = %s"
        product_params.append(id_param)
    
    if all_bsu_codes:
        placeholders = create_placeholders(len(all_bsu_codes))
        product_query += f" AND p.idPlant IN ({placeholders})"
        product_params.extend(all_bsu_codes)
    
    if order_id and ttn_ids:
        placeholders = create_placeholders(len(ttn_ids))
        product_query += f" AND p.idTtn IN ({placeholders})"
        product_params.extend(ttn_ids)
    
    if time_start and time_end:
        product_query += " AND p.timeStart BETWEEN %s AND %s"
        product_params.extend([time_start, time_end])
    elif time_start:
        product_query += " AND p.timeStart >= %s"
        product_params.append(time_start)
    
    if time_start_exact:
        product_query += " AND p.timeStart = %s"
        product_params.append(time_start_exact)
    
    if car:
        product_query += " AND p.car = %s"
        product_params.append(car)
    
    if driver:
        product_query += " AND p.driver = %s"
        product_params.append(driver)
    
    if id_ttn:
        product_query += " AND p.idTtn = %s"
        product_params.append(id_ttn)
    
    # Execute product query to get totalVProduct
    if product_params:
        with connection.cursor() as cursor:
            cursor.execute(product_query, product_params)
            for row in cursor.fetchall():
                v_product = float(row[1] or 0)
                total_v_product += v_product
    
    # Ðàññ÷èòûâàåì totalVProductExpected êàê ñóììó óíèêàëüíûõ vLoop ïî êàæäîìó ïðîäóêòó
    for product_id, unique_combinations in unique_v_loops_by_product.items():
        total_v_product_expected += sum(unique_combinations.values())
    
    logger.info(f"Total V Product: {total_v_product}")
    logger.info(f"Total V Product Expected: {total_v_product_expected}")
    
    # Prepare calculate array
    calculate_array = []
    dispencer_array = []
    
    # Get component names
    component_names = {}
    with connection.cursor() as cursor:
        cursor.execute("SELECT code, name FROM comp ORDER BY code")
        for row in cursor.fetchall():
            code_int = int(row[0])
            code_str = str(row[0])
            component_names[code_int] = row[1]
            component_names[code_str] = row[1]
    
    for code, dispencer_groups in calculations_by_dispencer.items():
        total_recipe_sum = 0
        total_fact_sum = 0
        
        for group in dispencer_groups.values():
            total_recipe_sum += group['recipeSum']
            total_fact_sum += group['factSum']
        
        comp_name = component_names.get(code, '')
        if not comp_name:
            comp_name = component_names.get(str(code), '')
        
        recipe_sum_rounded = round(total_recipe_sum, 1)
        fact_sum_rounded = round(total_fact_sum, 1)
        
        # Calculate error
        if total_recipe_sum != 0:
            error_percent = ((total_fact_sum - total_recipe_sum) / total_recipe_sum) * 100
        else:
            error_percent = 0
        
        error_percent_rounded = round(error_percent, 1)
        error_kg = total_fact_sum - total_recipe_sum
        error_kg_rounded = round(error_kg, 1)
        
        calculate_array.append({
            'code': code,
            'recipeSum': str(recipe_sum_rounded),
            'factSum': fact_sum_rounded,
            'name': comp_name,
            'errorPercent': error_percent_rounded,
            'errorKg': error_kg_rounded
        })
        
        # Process dispencer details for codes >= 4000 and < 5000
        if 4000 <= code < 5000:
            for group in dispencer_groups.values():
                dispencer_recipe_sum = round(group['recipeSum'], 1)
                dispencer_fact_sum = round(group['factSum'], 1)
                
                if group['recipeSum'] != 0:
                    dispencer_error_percent = ((group['factSum'] - group['recipeSum']) / group['recipeSum']) * 100
                else:
                    dispencer_error_percent = 0
                
                dispencer_error_percent_rounded = round(dispencer_error_percent, 1)
                dispencer_error_kg = group['factSum'] - group['recipeSum']
                dispencer_error_kg_rounded = round(dispencer_error_kg, 1)
                
                # Get silos - èñïîëüçóåì idPlant (êîä ÁÑÓ) êàê êëþ÷, êàê â PHP
                silos = ''
                # group['idPlant'] - ýòî êîä ÁÑÓ (êàê è â PHP)
                bsu_key = str(group['idPlant'])  # ýòî êîä ÁÑÓ
                dispencer_key = str(group['dispencer'])
                
                if bsu_key in silos_mapping and dispencer_key in silos_mapping[bsu_key]:
                    silos = silos_mapping[bsu_key][dispencer_key]
                
                dispencer_array.append({
                    'code': code,
                    'factSum': dispencer_fact_sum,
                    'name': comp_name,
                    'dispencer': group['dispencer'],
                    'silos': silos
                })
    
    # Sort arrays
    calculate_array.sort(key=lambda x: x['code'])
    dispencer_array.sort(key=lambda x: (x['code'], x['dispencer']))
    
    # Get all components
    components = []
    with connection.cursor() as cursor:
        cursor.execute("SELECT code, name FROM comp ORDER BY code")
        for row in cursor.fetchall():
            components.append({
                'code': int(row[0]),
                'name': row[1]
            })
    
    # Prepare info
    info = {}
    if bsu_code:
        info['bsu'] = bsu_code
    if id_plant:
        info['plant'] = id_plant
    if dispencer:
        info['dispencer'] = dispencer
    if time_start:
        info['timeStart'] = time_start
    if time_end:
        info['timeEnd'] = time_end
    
    # Final result with totalV object
    result = {
        'params': info,
        'totalV': {
            'recipeTotalV': round(total_v_product, 2),
            'factTotalV': round(total_v_product_expected, 2)
        },
        'calculate': calculate_array,
        'dispencer': dispencer_array,
        'component': components
    }
    
    return result

def on_connect(client, userdata, flags, rc):
    """Callback when connecting to broker"""
    if rc == 0:
        logger.info(f"Successfully connected to MQTT broker (code {rc})")
        client.subscribe(INPUT_TOPIC)
        logger.info(f"Subscribed to topic: {INPUT_TOPIC}")
        logger.info(f"Ready to process messages from {INPUT_TOPIC}")
    else:
        logger.error(f"Failed to connect to MQTT broker, code: {rc}")

def on_message(client, userdata, msg):
    """Callback when receiving message from subscribed topic"""
    try:
        # Decode payload
        payload = msg.payload.decode('utf-8')
        logger.info(f"Received message from topic: {msg.topic}")
        logger.info(f"Message content: {payload}")
        
        # Parse JSON
        try:
            data = json.loads(payload)
        except json.JSONDecodeError as e:
            logger.error(f"Invalid JSON format: {e}")
            error_response = {
                'error': 'Invalid JSON format',
                'message': str(e)
            }
            client.publish(OUTPUT_TOPIC, json.dumps(error_response, ensure_ascii=False), qos=0)
            return
        
        # Extract requestParams
        request_params = data.get('requestParams')
        if not request_params:
            logger.error("Missing 'requestParams' in message")
            error_response = {
                'error': 'Missing requestParams',
                'message': 'Request must contain requestParams object'
            }
            client.publish(OUTPUT_TOPIC, json.dumps(error_response, ensure_ascii=False), qos=0)
            return
        
        # Process calculation
        result = process_calculation(request_params)
        
        # Send result to output topic
        result_json = json.dumps(result, ensure_ascii=False, indent=2)
        client.publish(OUTPUT_TOPIC, result_json, qos=0, retain=False)
        logger.info(f"Calculation result sent to topic: {OUTPUT_TOPIC}")
        
    except Exception as e:
        logger.error(f"Error processing message: {e}", exc_info=True)
        error_response = {
            'error': 'Processing error',
            'message': str(e)
        }
        try:
            client.publish(OUTPUT_TOPIC, json.dumps(error_response, ensure_ascii=False), qos=0)
        except:
            pass

def on_disconnect(client, userdata, rc):
    """Callback when disconnecting from broker"""
    if rc != 0:
        logger.warning(f"Unexpected disconnection from MQTT broker, code: {rc}")
    else:
        logger.info("Disconnected from MQTT broker")

def start_calculation_service():
    """Start MQTT calculation service"""
    try:
        client = mqtt.Client(client_id=MQTT_CLIENT_ID, protocol=mqtt.MQTTv311)
        
        client.on_connect = on_connect
        client.on_message = on_message
        client.on_disconnect = on_disconnect
        
        client.username_pw_set(MQTT_USERNAME, MQTT_PASSWORD)
        
        client.tls_set(ca_certs=None, cert_reqs=ssl.CERT_NONE, tls_version=ssl.PROTOCOL_TLS_CLIENT)
        client.tls_insecure_set(True)
        
        logger.info(f"Connecting to MQTT broker at {MQTT_BROKER}:{MQTT_PORT}...")
        client.connect(MQTT_BROKER, MQTT_PORT, 60)
        
        client.loop_start()
        
        return client
        
    except Exception as e:
        logger.error(f"Failed to start MQTT client: {e}")
        return None

def stop_calculation_service(client):
    """Stop calculation service"""
    if client:
        client.loop_stop()
        client.disconnect()
        logger.info("Calculation service stopped")

if __name__ == "__main__":
    client = start_calculation_service()
    
    if client:
        try:
            logger.info("=" * 60)
            logger.info("CALCULATION SERVICE IS RUNNING")
            logger.info(f"Listening on: {INPUT_TOPIC}")
            logger.info(f"Responding on: {OUTPUT_TOPIC}")
            logger.info("Press Ctrl+C to stop...")
            logger.info("=" * 60)
            
            while True:
                time.sleep(1)
                
        except KeyboardInterrupt:
            logger.info("\nStopping calculation service...")
            stop_calculation_service(client)
            logger.info("Calculation service stopped")