from datetime import datetime
from typing import Optional, List, Dict, Union
from Consultas_SQL.conexion import get_connection

# --- DTO para Oportunidad CRM unificada ---


class OpportunityAndCostingDTO:
    """DTO para la información unificada de Oportunidad y Encabezado de Costeo."""

    def __init__(self, costing_id: str, crm_opportunity_number: str, crm_contact_name: str,
                 crm_contact_type: str, crm_assigned_salesperson: str, crm_contact_adress: str,
                 crm_contact_colonia: str, crm_contact_city: str, crm_contact_number: str,
                 crm_contact_country: str, crm_contact_legal_identifier: str,
                 crm_contact_zip: str, crm_contact_state: str, crm_contact_email: str,
                 case_cost: str, sale_price_list: float, sale_price_min: float, discount_max_percent: float):
        self.CostingID = costing_id
        self.CRM_OpportunityNumber = crm_opportunity_number
        self.CRM_ContactName = crm_contact_name
        self.CRM_ContactType = crm_contact_type
        self.CRM_AssignedSalesperson = crm_assigned_salesperson
        self.CRM_ContactAdress = crm_contact_adress
        self.CRM_ContactColonia = crm_contact_colonia
        self.CRM_ContactCity = crm_contact_city
        self.CRM_ContactNumber = crm_contact_number
        self.CRM_ContactCountry = crm_contact_country
        self.CRM_ContactLegalIdentifier = crm_contact_legal_identifier
        self.CRM_ContactZip = crm_contact_zip
        self.CRM_ContactState = crm_contact_state
        self.CRM_ContactEmail = crm_contact_email
        self.CaseCost = case_cost
        self.SalePriceList = sale_price_list
        self.SalePriceMin = sale_price_min
        self.DiscountMaxPercent = discount_max_percent

    def to_dict(self) -> dict:
        """Convierte el objeto a un diccionario serializable."""
        return {
            "CostingID": self.CostingID,
            "CRM_OpportunityNumber": self.CRM_OpportunityNumber,
            "CRM_ContactName": self.CRM_ContactName,
            "CRM_ContactType": self.CRM_ContactType,
            "CRM_AssignedSalesperson": self.CRM_AssignedSalesperson,
            "CRM_ContactAdress": self.CRM_ContactAdress,
            "CRM_ContactColonia": self.CRM_ContactColonia,
            "CRM_ContactCity": self.CRM_ContactCity,
            "CRM_ContactNumber": self.CRM_ContactNumber,
            "CRM_ContactCountry": self.CRM_ContactCountry,
            "CRM_ContactLegalIdentifier": self.CRM_ContactLegalIdentifier,
            "CRM_ContactZip": self.CRM_ContactZip,
            "CRM_ContactState": self.CRM_ContactState,
            "CRM_ContactEmail": self.CRM_ContactEmail,
            "CaseCost": self.CaseCost,
            "SalePriceList": float(self.SalePriceList),
            "SalePriceMin": float(self.SalePriceMin),
            "DiscountMaxPercent": float(self.DiscountMaxPercent),
        }

# --- Clase de Servicio de Consulta Unificada ---


class Quote_Reception_Service:
    """Clase de servicio para manejar la lógica de recepción de datos de costeo y oportunidad."""

    @staticmethod
    def get_unified_data_by_costing_num(costing_num: int) -> Optional[OpportunityAndCostingDTO]:
        """
        Consulta unificada que obtiene los datos de Q_CostingHead y Q_OpportunityCRM
        en una sola llamada.
        """
        query = """
            SELECT TOP 1
                Q_CostingHead.CostingID,
                Q_OpportunityCRM.CRM_OpportunityNumber,
                Q_OpportunityCRM.CRM_ContactName,
                Q_OpportunityCRM.CRM_ContactType,
                Q_OpportunityCRM.CRM_AssignedSalesperson,
                Q_OpportunityCRM.CRM_ContactAdress,
                Q_OpportunityCRM.CRM_ContactColonia,
                Q_OpportunityCRM.CRM_ContactCity,
                Q_OpportunityCRM.CRM_ContactNumber,
                Q_OpportunityCRM.CRM_ContactCountry,
                Q_OpportunityCRM.CRM_ContactLegalIdentifier,
                Q_OpportunityCRM.CRM_ContactZip,
                Q_OpportunityCRM.CRM_ContactState,
                Q_OpportunityCRM.CRM_ContactEmail,
                Q_CostingHead.CaseCost,
                Q_CostingHead.SalePriceList,
                Q_CostingHead.SalePriceMin,
                Q_CostingHead.DiscountMaxPercent
            FROM
                Q_CostingHead
            INNER JOIN
                Q_OpportunityCRM ON Q_CostingHead.CRM_OpportunityID = Q_OpportunityCRM.CRM_OpportunityID
            WHERE
                Q_CostingHead.CostingNum = ?
            ORDER BY
                Q_CostingHead.Version DESC;
        """
        try:
            with get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(query, costing_num)
                    row = cursor.fetchone()
                if row:
                    return OpportunityAndCostingDTO(
                        costing_id=row[0], crm_opportunity_number=row[1], crm_contact_name=row[2],
                        crm_contact_type=row[3], crm_assigned_salesperson=row[4], crm_contact_adress=row[5],
                        crm_contact_colonia=row[6], crm_contact_city=row[7], crm_contact_number=row[8],
                        crm_contact_country=row[9], crm_contact_legal_identifier=row[10],
                        crm_contact_zip=row[11], crm_contact_state=row[12], crm_contact_email=row[13],
                        case_cost=row[14], sale_price_list=row[15], sale_price_min=row[16], discount_max_percent=row[17]
                    )
                return None
        except Exception as e:
            print(f"Error al obtener datos unificados: {e}")
            return None

    @staticmethod
    def get_taxes() -> List[Dict[str, Union[str, float]]]:
        """
        Consulta la tabla Q_TaxRate para obtener el código y la descripción de los impuestos.
        """
        query = "SELECT TaxCode, FrontES, TaxAmount, CurrencyCode    FROM Q_TaxRate WHERE Active = 1;"
        taxes = []
        try:
            with get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(query)
                    rows = cursor.fetchall()
                    for row in rows:
                        taxes.append({"TaxCode": row[0], "FrontES": row[1], "TaxAmount": float(
                            row[2]), "CurrencyCode": row[3]})
            return taxes
        except Exception as e:
            print(f"Error al obtener los impuestos: {e}")
            return []

    @staticmethod
    def get_currencies() -> List[Dict[str, str]]:
        """
        Consulta la tabla Q_Currency para obtener el código, el nombre y el símbolo de las monedas activas.
        """
        query = "SELECT CurrencyCode, FrontES, CurrSymbol FROM Q_Currency WHERE Active = 1;"
        currencies = []
        try:
            with get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(query)
                    rows = cursor.fetchall()
                    for row in rows:
                        currencies.append(
                            {"CurrencyCode": row[0], "FrontES": row[1], "CurrSymbol": row[2]})
            return currencies
        except Exception as e:
            print(f"Error al obtener las monedas: {e}")
            return []

    @staticmethod
    def get_costing_details(costing_num: int) -> List[Dict]:
        """
        Consulta Q_CostingDetail para obtener las líneas de costeo.
        """
        query = """
            SELECT 
                CostingLine,
                PartNum,
                PartDescription,
                Qty,
                UOMCode,
                UnitPrice,
                Amount
            FROM Q_CostingDetail
            WHERE CostingID = (
                SELECT TOP 1 CostingID 
                FROM Q_CostingHead 
                WHERE CostingNum = ? 
                ORDER BY Version DESC
            )
            ORDER BY CostingLine ASC;
        """
        details = []
        try:
            with get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(query, costing_num)
                    rows = cursor.fetchall()
                    for row in rows:
                        details.append({
                            "CostingLine": row[0],
                            "PartNum": row[1],
                            "PartDescription": row[2],
                            "Qty": float(row[3]),
                            "UOMCode": row[4],
                            "UnitPrice": float(row[5]),
                            "Amount": float(row[6])
                        })
            return details
        except Exception as e:
            print(f"Error al obtener detalles de costeo: {e}")
            return []

    # Al final de la clase Quote_Reception_Service, antes del método create_quotation

    @staticmethod
    def generate_quotation_preview(data: dict) -> str:
        """
        Genera HTML de vista previa de la cotización SIN guardar en BD.
        Retorna el HTML renderizado.
        """
        try:
            from flask import render_template
            from datetime import datetime
            
            costing_id = data.get('CostingID')
            
            # ✅ Query CORREGIDO: El vendedor es el UserID de la oportunidad
            query = """
                SELECT TOP 1
                    Q_CostingHead.CostingNum,
                    Q_CostingHead.Version,
                    
                    -- Cliente
                    Q_OpportunityCRM.CRM_ContactName,
                    Q_OpportunityCRM.CRM_ContactType,
                    Q_OpportunityCRM.CRM_OpportunityNumber,
                    Q_OpportunityCRM.CRM_ContactEmail,
                    Q_OpportunityCRM.CRM_ContactNumber,
                    Q_OpportunityCRM.CRM_ContactCity,
                    Q_OpportunityCRM.CRM_ContactState,
                    Q_OpportunityCRM.CRM_ContactCountry,
                    Q_OpportunityCRM.CRM_ContactAdress,
                    
                    -- ✅ El vendedor es el UserID de la oportunidad
                    TRIM(CONCAT(
                        ISNULL(Seller.FirstName, ''), ' ',
                        ISNULL(Seller.MiddleName, ''), ' ', 
                        ISNULL(Seller.LastName, ''), ' ',
                        ISNULL(Seller.SecondLastName, '')
                    )) AS VendedorNombre,
                    ISNULL(Seller.Email, '') AS VendedorEmail,
                    ISNULL(Seller.ContactPhone, '') AS VendedorTelefono
                    
                FROM Q_CostingHead
                
                INNER JOIN Q_OpportunityCRM 
                    ON Q_CostingHead.CRM_OpportunityID = Q_OpportunityCRM.CRM_OpportunityID
                
                -- ✅ JOIN directo: El vendedor es Q_OpportunityCRM.UserID
                LEFT JOIN Profiles AS Seller
                    ON Q_OpportunityCRM.UserID = Seller.UserID
                
                WHERE Q_CostingHead.CostingID = ?
            """
            
            with get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(query, costing_id)
                    row = cursor.fetchone()
                    
                    if not row:
                        print(f"❌ No se encontró CostingID: {costing_id}")
                        return None
                    
                    quotation_num = row[0]
                    current_version = row[1] if row[1] else 0
                    
                    # Verificar versión siguiente
                    cursor.execute("""
                        SELECT MAX(Version) FROM Q_QuotationHead WHERE QuotationNum = ?
                    """, quotation_num)
                    
                    version_result = cursor.fetchone()
                    next_version = 1 if not version_result[0] else version_result[0] + 1
                    
                                    
                    # ✅ NUEVO: Obtener el nombre legible de la moneda
                    currency_code = data.get('CurrencyCode')
                    currency_name = currency_code  # Por defecto, usar el código
                    
                    if currency_code:
                        cursor.execute("""
                            SELECT FrontES FROM Q_Currency WHERE CurrencyCode = ?
                        """, currency_code)
                        currency_result = cursor.fetchone()
                        if currency_result and currency_result[0]:
                            currency_name = currency_result[0]
                
                    print(f"💱 Moneda: {currency_code} -> {currency_name}")
                
                    
                    # ✅ DEBUG
                    print(f"🔍 DEBUG - VendedorNombre: '{row[11]}'")
                    print(f"🔍 DEBUG - VendedorEmail: '{row[12]}'")
                    print(f"🔍 DEBUG - VendedorTelefono: '{row[13]}'")
                    
                    # Limpiar espacios y validar
                    vendedor_nombre = row[11].strip() if row[11] else None
                    vendedor_email = row[12].strip() if row[12] else None
                    vendedor_telefono = row[13].strip() if row[13] else None
                    
                    
                    template_data = {
                        # Identificadores
                        'quotation_num': quotation_num,
                        'version': next_version,
                        'costing_id': costing_id,
                        'caso_costeo': data.get('CaseCost'),
                        
                        # Datos del cliente
                        'cliente_nombre': row[2],
                        'tipo_contacto': row[3],
                        'oportunidad_crm': row[4],
                        'cliente_email': row[5],
                        'cliente_telefono': row[6],
                        'cliente_ciudad': row[7],
                        'cliente_estado': row[8],
                        'cliente_pais': row[9],
                        'cliente_direccion': row[10],
                        
                        # ✅ Datos del vendedor (desde Q_OpportunityCRM.UserID → Profiles)
                        'vendedor_asignado': vendedor_nombre or 'No asignado',
                        'vendedor_email': vendedor_email or 'ventas@igsa.com',
                        'vendedor_telefono': vendedor_telefono or 'N/A',
                        
                        # Datos financieros
                        'precio_lista': data.get('SalePrice', 0),
                        'descuento_porcentaje': data.get('DiscountPercent', 0),
                        'precio_oferta': data.get('Amount', 0),
                        'impuesto_codigo': data.get('TaxCode'),
                        'precio_total': data.get('TotalAmount', 0),
                        # Usar el nombre legible de la moneda
                        'moneda': currency_name,  # "Peso Mexicano" en lugar de "MX"
                        'moneda_codigo': currency_code,  # Mantener el código para referencias
                        
                        
                        'factor_sobrecosto': data.get('OvercostFactor', 1),
                        
                        # Líneas de cotización
                        'lineas': data.get('QuotationLines', []),
                        'total_lineas': len(data.get('QuotationLines', [])),
                        
                        # Datos del sistema
                        'fecha_actual': datetime.now().strftime('%d/%m/%Y'),
                        'hora_actual': datetime.now().strftime('%H:%M'),
                        'año_actual': datetime.now().year,
                        'empresa': 'IGSA',
                        'empresa_completa': 'Integradora de Servicios Avanzados',
                        
                        # Proyecto
                        'proyecto_nombre': data.get('proyecto_nombre', 'Por definir'),
                        'proyecto_requerimientos': data.get('proyecto_requerimientos', 'Ninguno')
                    }
                    
                    print(f"✅ Vendedor final en template: {template_data['vendedor_asignado']}")
                    
                    html = render_template('Emails/Ventas/Cotiz/PreviewCotizacion.html', **template_data)
                    return html
                    
        except Exception as e:
            print(f"❌ Error al generar vista previa: {e}")
            import traceback
            traceback.print_exc()
            return None
            
        
        
    @staticmethod
    def create_quotation(data: dict) -> dict:
        """
        Crea una nueva cotización en Q_QuotationHead y Q_QuotationDetail.
        Envía notificación por correo al departamento de ingeniería.
        """
        try:
            with get_connection() as conn:
                with conn.cursor() as cursor:
                    costing_id = data.get('CostingID')
                    
                    # 1. Obtener QuotationNum del CostingHead
                    cursor.execute("""
                        SELECT CostingNum FROM Q_CostingHead WHERE CostingID = ?
                    """, costing_id)
                    
                    costing_result = cursor.fetchone()
                    if not costing_result:
                        return {"success": False, "error": "CostingID no encontrado"}
                    
                    quotation_num = costing_result[0]
                    
                    # 2. Verificar si ya existe una cotización con este número
                    cursor.execute("""
                        SELECT MAX(Version) FROM Q_QuotationHead WHERE QuotationNum = ?
                    """, quotation_num)
                    
                    version_result = cursor.fetchone()
                    version = 1 if not version_result[0] else version_result[0] + 1
                    quotation_id = f"{quotation_num}-{version}"
                    
                    print(f"Creando cotización: {quotation_id}")
                    
                    # 3. Insertar en Q_QuotationHead
                    insert_head_query = """
                        INSERT INTO Q_QuotationHead (
                            QuotationNum,
                            Version,
                            CaseCost,
                            SalePrice,
                            DiscountPercent,
                            OvercostFactor,
                            Amount,
                            TaxCode,
                            TotalAmount,
                            CurrencyCode,
                            Active
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
                    """
                    
                    cursor.execute(insert_head_query, (
                        quotation_num,
                        version,
                        data.get('CaseCost'),
                        data.get('SalePrice'),
                        data.get('DiscountPercent', 0),
                        data.get('OvercostFactor', 1),
                        data.get('Amount'),
                        data.get('TaxCode'),
                        data.get('TotalAmount'),
                        data.get('CurrencyCode')
                    ))
                    
                    # 4. Insertar líneas en Q_QuotationDetail
                    quotation_lines = data.get('QuotationLines', [])
                    
                    insert_detail_query = """
                        INSERT INTO Q_QuotationDetail (
                            QuotationID,
                            QuotationLine,
                            CostingLineID,
                            PartNum,
                            PartDescription,
                            Qty,
                            UOMCode,
                            UnitPrice,
                            Amount
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """
                    
                    for line in quotation_lines:
                        cursor.execute(insert_detail_query, (
                            quotation_id,
                            line.get('QuotationLine'),
                            line.get('CostingLineID'),
                            line.get('PartNum'),
                            line.get('PartDescription'),
                            line.get('Qty'),
                            line.get('UOMCode'),
                            line.get('UnitPrice'),
                            line.get('Amount')
                        ))
                    
                    conn.commit()
                    print(f"Cotización {quotation_id} creada exitosamente en BD")
                    
                # ✅ AGREGAR ESTE RETURN AQUÍ
                return {
                    "success": True,
                    "message": "Cotización creada exitosamente",
                    "QuotationID": quotation_id,
                    "Version": version
                } 
                    
        except Exception as e:
            print(f"Error al generar vista previa: {e}")
            return None
        
        
    @staticmethod
    def get_seller_info(costing_id: str) -> dict:
        """Obtiene información del vendedor"""
        try:
            from Consultas_SQL.conexion import get_connection
            
            with get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute("""
                        SELECT TOP 1
                            TRIM(CONCAT(
                                ISNULL(Seller.FirstName, ''), ' ',
                                ISNULL(Seller.MiddleName, ''), ' ', 
                                ISNULL(Seller.LastName, ''), ' ',
                                ISNULL(Seller.SecondLastName, '')
                            )) AS VendedorNombre,
                            ISNULL(Seller.Email, '') AS VendedorEmail,
                            ISNULL(Seller.ContactPhone, '') AS VendedorTelefono
                            
                        FROM Q_CostingHead
                        INNER JOIN Q_OpportunityCRM 
                            ON Q_CostingHead.CRM_OpportunityID = Q_OpportunityCRM.CRM_OpportunityID
                        LEFT JOIN Profiles AS Seller
                            ON Q_OpportunityCRM.UserID = Seller.UserID
                        WHERE Q_CostingHead.CostingID = ?
                    """, costing_id)
                    
                    row = cursor.fetchone()
                    if row:
                        return {
                            'nombre': row[0].strip() if row[0] else None,
                            'email': row[1].strip() if row[1] else None,
                            'telefono': row[2].strip() if row[2] else None
                        }
        except Exception as e:
            print(f"⚠️ Error al obtener info del vendedor: {e}")
        
        return {'nombre': None, 'email': None, 'telefono': None}