"""
SQL Query builders for invoice movement services.
Centralizes all SQL query construction logic.
"""


class TemporaryImportQueries:
    """SQL queries for temporary imports using PostgreSQL tables."""
    
    @staticmethod
    def build_aggregated_query(db_name: str, where_str: str) -> str:
        """Build optimized query for NORMAL mode (grouped by invoice with totals)."""
        # Note: db_name parameter kept for compatibility but not used in PostgreSQL
        return f"""
        SELECT 
            ih.invoice_number AS C1,
            COALESCE(ped.pedimento_number, '') AS C2,
            TO_CHAR(ih.invoice_date, 'YYYYMMDD') AS C3,
            CASE WHEN ih.status THEN 'AC' ELSE 'NA' END AS C4,
            COALESCE(ped.pedimento_code, '') AS C5,
            COALESCE(ped.regime, '') AS C10,
            COALESCE(TO_CHAR(pd.entry_date, 'YYYYMMDD'), '') AS C11,
            COALESCE(TO_CHAR(pd.end_date, 'YYYYMMDD'), TO_CHAR(pd.payment_date, 'YYYYMMDD'), '') AS C12,
            COALESCE(TO_CHAR(pd.payment_date, 'YYYYMMDD'), '') AS C13,
            COALESCE(cmp.remesa, 0) AS C14,
            COALESCE(fin.exchange_rate, 0) AS C15,
            COALESCE(cmp.provider_id::text, '') AS C16,
            COALESCE(cmp.sold_to_id::text, '') AS C17,
            COALESCE(cmp.customs_broker_id::text, '') AS C18,
            COALESCE(cmp.aduana, '') AS C38,
            ih.id AS C39,
            COALESCE(
                ped_r1.year || '-' || ped_r1.customs_office || '-' || ped_r1.license || '-' || ped_r1.pedimento_number,
                pro_origin.original_pedimento_year || '-' || pro_origin.original_customs_office || '-' || pro_origin.original_license || '-' || pro_origin.original_pedimento_number,
                ''
            ) AS C41,
            COALESCE(cmp.edocument, '') AS C42,
            COALESCE(cmp.vucem_operation_num, '') AS C43,
            COALESCE(fin.exchange_rate, 0) AS C50,
            COALESCE(TO_CHAR(ih.emission_date, 'YYYYMMDD'), '') AS C51,
            COALESCE(ih.capture_user, '') AS C52,
            COALESCE(ih.who_processed, '') AS C53,
            COALESCE(log.carrier_id, '') AS C54,
            COALESCE(NULLIF(CONCAT_WS(' ', log.transport_num, log.license_plate), ''), '') AS C55,
            '' AS C56,
            '' AS C57,
            '' AS C58,
            COALESCE(fin.value_me, 0) AS total_me,
            COALESCE(fin.value_mn, 0) AS total_mn,
            COALESCE(lf_agg.sum_value_mxn, 0) AS valor_comercial_mn,
            COALESCE(lf_agg.sum_value_temp_mxn, 0) AS valor_mp_temp_mn,
            COALESCE(lf_agg.sum_value_added_mxn, 0) AS valor_agre_mn,
            COALESCE(lf_agg.sum_value_temp_usd, 0) AS valor_mp_temp_usd,
            COALESCE(lf_agg.sum_value_usd, 0) AS sum_value_usd
        FROM a76.invoice_header ih
        LEFT JOIN a76.invoice_compliance_mx cmp ON cmp.invoice_id = ih.id
        LEFT JOIN a76.invoice_financials fin ON fin.invoice_id = ih.id
        LEFT JOIN a76.invoice_logistics log ON log.invoice_id = ih.id
        LEFT JOIN a76.pedimentos ped ON ped.id = cmp.pedimento_id
        LEFT JOIN a76.pedimento_rectification_origin pro_origin ON pro_origin.pedimento_id = ped.id
        LEFT JOIN a76.pedimento_rectification_origin pro_rect ON 
            pro_rect.original_pedimento_year = ped.year AND
            pro_rect.original_customs_office = ped.customs_office AND
            pro_rect.original_license = ped.license AND
            pro_rect.original_pedimento_number = ped.pedimento_number
        LEFT JOIN a76.pedimentos ped_r1 ON ped_r1.id = pro_rect.pedimento_id
        LEFT JOIN a76.pedimento_dates pd ON pd.pedimento_id = ped.id
        LEFT JOIN (
            SELECT il.invoice_id,
                   SUM(COALESCE(lf.value_mxn, 0)) AS sum_value_mxn,
                   SUM(COALESCE(lf.value_temp_material_mxn, 0)) AS sum_value_temp_mxn,
                   SUM(COALESCE(lf.value_added_mxn, 0)) AS sum_value_added_mxn,
                   SUM(COALESCE(lf.value_temp_material_usd, 0)) AS sum_value_temp_usd,
                   SUM(COALESCE(lf.value_usd, 0)) AS sum_value_usd
            FROM a76.item_lines il
            JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
            GROUP BY il.invoice_id
        ) lf_agg ON lf_agg.invoice_id = ih.id
        WHERE ih.operation_type = 'imp' 
            AND ih.invoice_type = 'TEM'
            AND {where_str}
        ORDER BY ih.invoice_number
        """

    
    @staticmethod
    def build_main_query(db_name: str, where_str: str) -> str:
        """Build main SQL query for DETAILED mode (all partidas) from PostgreSQL."""
        # Note: db_name parameter is kept for compatibility but not used in PostgreSQL
        return f"""
        SELECT 
            ih.invoice_number AS C1,
            COALESCE(ped.pedimento_number, '') AS C2,
            TO_CHAR(ih.invoice_date, 'YYYYMMDD') AS C3,
            CASE WHEN ih.status THEN 'AC' ELSE 'NA' END AS C4,
            COALESCE(ped.pedimento_code, '') AS C5,
            COALESCE(fin.value_me, 0) AS C6,
            COALESCE(fin.value_mn, 0) AS C7,
            COALESCE(prov.name, '') AS C8,
            COALESCE(client.name, '') AS C9,
            COALESCE(ped.regime, '') AS C10,
            COALESCE(TO_CHAR(pd.entry_date, 'YYYYMMDD'), '') AS C11,
            COALESCE(TO_CHAR(pd.end_date, 'YYYYMMDD'), TO_CHAR(pd.payment_date, 'YYYYMMDD'), '') AS C12,
            COALESCE(TO_CHAR(pd.payment_date, 'YYYYMMDD'), '') AS C13,
            COALESCE(cmp.remesa, 0) AS C14,
            COALESCE(fin.exchange_rate, 0) AS C15,
            COALESCE(cmp.provider_id::text, '') AS C16,
            COALESCE(cmp.sold_to_id::text, '') AS C17,
            COALESCE(cmp.customs_broker_id::text, '') AS C18,
            '' AS C19,
            COALESCE(il.class_id::text, '') AS C20,
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.description_spanish, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C21,
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.description_english, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C22,
            COALESCE(lq.quantity, 0) AS C23,
            COALESCE(il.unit_of_measure::text, '') AS C24,
            COALESCE(lf.value_mxn, 0) AS C25,
            COALESCE(lf.customs_value_mxn, 0) AS C26,
            COALESCE(lf.value_usd, 0) AS C27,
            COALESCE(lf.customs_value_usd, 0) AS C28,
            COALESCE(lq.net_weight, 0) AS C29,
            COALESCE(lq.gross_weight, 0) AS C30,
            COALESCE(il.order, ih.purchase_order, '') AS C31,
            COALESCE(lc.fraction, '') AS C32,
            COALESCE(lc.fraction_type, '') AS C33,
            COALESCE(lc.advalorem_numeric, 0) AS C34,
            COALESCE(NULLIF(lc.sector, ''), NULLIF(fap.sector, ''), '') AS C35,
            COALESCE(lf.igi_amount_usd, 0) AS C36,
            COALESCE(lc.origin_country, '') AS C37,
            COALESCE(cmp.aduana, '') AS C38,
            ih.id AS C39,
            COALESCE(il.material_type, 'P') AS C40,
            COALESCE(
                ped_r1.year || '-' || ped_r1.customs_office || '-' || ped_r1.license || '-' || ped_r1.pedimento_number,
                pro_origin.original_pedimento_year || '-' || pro_origin.original_customs_office || '-' || pro_origin.original_license || '-' || pro_origin.original_pedimento_number,
                ''
            ) AS C41,           -- [40] rectification_id
            cmp.edocument AS C42,           -- [41]
            cmp.vucem_operation_num AS C43, -- [42]
            COALESCE(il.line_number, 0) AS C44,
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.brand, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C45,
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.model, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C46,
            COALESCE(cls.us_fraction, '') AS C47,
            COALESCE(prt.eccn, fac.eccn_code, '') AS C48,
            COALESCE(prt.part_number::text, '') AS C49,
            COALESCE(fin.exchange_rate, 0) AS C50,
            COALESCE(TO_CHAR(ih.emission_date, 'YYYYMMDD'), '') AS C51,
            COALESCE(ih.capture_user, '') AS C52,
            COALESCE(ih.who_processed, '') AS C53,
            COALESCE(log.carrier_id, '') AS C54,
            COALESCE(NULLIF(CONCAT_WS(' ', log.transport_num, log.license_plate), ''), '') AS C55,
            '' AS C56,
            COALESCE(ld.lot, '') AS C57,
            '' AS C58,
            COALESCE(lf.value_temp_material_mxn, 0) AS C59,
            COALESCE(lf.value_temp_material_usd, 0) AS C60
        FROM a76.invoice_header ih
        LEFT JOIN a76.invoice_compliance_mx cmp ON cmp.invoice_id = ih.id
        LEFT JOIN a76.invoice_financials fin ON fin.invoice_id = ih.id
        LEFT JOIN a76.invoice_logistics log ON log.invoice_id = ih.id
        LEFT JOIN a76.pedimentos ped ON ped.id = cmp.pedimento_id
        LEFT JOIN a76.pedimento_rectification_origin pro_origin ON pro_origin.pedimento_id = ped.id
        LEFT JOIN a76.pedimento_rectification_origin pro_rect ON 
            pro_rect.original_pedimento_year = ped.year AND
            pro_rect.original_customs_office = ped.customs_office AND
            pro_rect.original_license = ped.license AND
            pro_rect.original_pedimento_number = ped.pedimento_number
        LEFT JOIN a76.pedimentos ped_r1 ON ped_r1.id = pro_rect.pedimento_id
        LEFT JOIN a76.pedimento_dates pd ON pd.pedimento_id = ped.id
        LEFT JOIN a76.clients_and_providers prov ON prov.id = cmp.provider_id
        LEFT JOIN a76.clients_and_providers client ON client.id = cmp.sold_to_id
        LEFT JOIN a76.item_lines il ON il.invoice_id = ih.id
        LEFT JOIN a76.item_line_descriptions ld ON ld.item_line_id = il.id
        LEFT JOIN a76.item_line_quantities lq ON lq.item_line_id = il.id
        LEFT JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
        LEFT JOIN a76.item_line_customs lc ON lc.item_line_id = il.id
        LEFT JOIN a76.classes cls ON cls.id = il.class_id
        LEFT JOIN a24.fa_classes fac ON fac.class_id = cls.id
        LEFT JOIN a76.parts prt ON prt.id = il.part_number_id
        LEFT JOIN a24.fa_partes fap ON fap.id = prt.id
        LEFT JOIN a76.units_of_measure um ON um.id = il.unit_of_measure
        WHERE ih.operation_type = 'imp' 
            AND ih.invoice_type = 'TEM'
            AND {where_str}
        ORDER BY ih.invoice_number, il.line_number
        """
    
    @staticmethod
    def build_totals_query(db_name: str) -> str:
        """Build query to get totals for an invoice."""
        return f"""
        SELECT 
            COALESCE(SUM(EqiPim.ValorImpoME), 0), 
            COALESCE(SUM(EqiPim.ValorImpoMN), 0) 
        FROM [{db_name}].dbo.QEqiMaq EqiPim
        WHERE EqiPim.Consecutivo = :consecutivo
            AND EqiPim.EsSubpartida = 'P'
        """
    
    @staticmethod
    def build_series_query(db_name: str) -> str:
        """Build query to get series information."""
        return f"""
        SELECT SerieImpo, ModeloImpo, ParteImpo 
        FROM [{db_name}].dbo.QSeriesImpo 
        WHERE Consecutivo = :consecutivo 
            AND LineaImpo = :linea
        ORDER BY RenImpo
        """
    
    @staticmethod
    def build_driver_badge_query(db_name: str) -> str:
        """Build query to get driver badge number."""
        return f"""
        SELECT TOP 1 NUMGAFETEUNICO 
        FROM [{db_name}].dbo.GConductor 
        LEFT JOIN [{db_name}].dbo.QFacImp 
            ON QFacImp.CONDUCTOR = GConductor.CONDUCTOR 
        WHERE FacturaImpo = :factura
        """


class DefinitiveImportQueries:
    """SQL queries for definitive imports (PostgreSQL schema)."""
    
    @staticmethod
    def build_aggregated_query(db_name: str, where_clause: str) -> str:
        """Build optimized query for NORMAL mode (grouped by invoice with totals)."""
        return f"""
        SELECT 
            ih.invoice_number AS C1,
            COALESCE(ped.pedimento_number, '') AS C2,
            TO_CHAR(ih.invoice_date, 'YYYYMMDD') AS C3,
            CASE WHEN ih.status THEN 'AC' ELSE 'NA' END AS C4,
            COALESCE(ped.pedimento_code, '') AS C5,
            COALESCE(ped.regime, '') AS C10,
            COALESCE(TO_CHAR(pd.entry_date, 'YYYYMMDD'), '') AS C11,
            COALESCE(TO_CHAR(pd.end_date, 'YYYYMMDD'), TO_CHAR(pd.payment_date, 'YYYYMMDD'), '') AS C12,
            COALESCE(TO_CHAR(pd.payment_date, 'YYYYMMDD'), '') AS C13,
            COALESCE(log.payment_receipt_num, '') AS C14,
            COALESCE(fin.exchange_rate, 0) AS C15,
            COALESCE(cmp.provider_id::text, '') AS C16,
            COALESCE(cmp.sold_to_id::text, '') AS C17,
            COALESCE(cmp.customs_broker_id::text, '') AS C18,
            COALESCE(ih.purchase_order, '') AS C31,
            COALESCE(cmp.aduana, '') AS C39,
            ih.id AS C35,
            COALESCE(
                ped_r1.year || '-' || ped_r1.customs_office || '-' || ped_r1.license || '-' || ped_r1.pedimento_number,
                pro_origin.original_pedimento_year || '-' || pro_origin.original_customs_office || '-' || pro_origin.original_license || '-' || pro_origin.original_pedimento_number,
                ''
            ) AS C42,
            COALESCE(cmp.edocument, '') AS C43,
            COALESCE(cmp.vucem_operation_num, '') AS C44,
            COALESCE(fin.exchange_rate, 0) AS C51,
            COALESCE(TO_CHAR(ih.emission_date, 'YYYYMMDD'), '') AS C52,
            COALESCE(ih.capture_user, '') AS C53,
            COALESCE(ih.who_processed, '') AS C54,
            COALESCE(NULLIF(CONCAT_WS(' ', log.transport_id, log.transport_num), ''), '') AS C56,
            '' AS C57,
            '' AS C58,
            '' AS C59,
            COALESCE(fin.value_me, 0) AS total_me,
            COALESCE(fin.value_mn, 0) AS total_mn,
            COALESCE(lf_agg.sum_value_usd, 0) AS sum_value_usd,
            COALESCE(lf_agg.sum_value_mxn, 0) AS sum_value_mxn
        FROM a76.invoice_header ih
        LEFT JOIN a76.invoice_compliance_mx cmp ON cmp.invoice_id = ih.id
        LEFT JOIN a76.invoice_financials fin ON fin.invoice_id = ih.id
        LEFT JOIN a76.invoice_logistics log ON log.invoice_id = ih.id
        LEFT JOIN a76.pedimentos ped ON ped.id = cmp.pedimento_id
        LEFT JOIN a76.pedimento_rectification_origin pro_origin ON pro_origin.pedimento_id = ped.id
        LEFT JOIN a76.pedimento_rectification_origin pro_rect ON 
            pro_rect.original_pedimento_year = ped.year AND
            pro_rect.original_customs_office = ped.customs_office AND
            pro_rect.original_license = ped.license AND
            pro_rect.original_pedimento_number = ped.pedimento_number
        LEFT JOIN a76.pedimentos ped_r1 ON ped_r1.id = pro_rect.pedimento_id
        LEFT JOIN a76.pedimento_dates pd ON pd.pedimento_id = ped.id
        LEFT JOIN (
            SELECT il.invoice_id,
                   SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE COALESCE(lf.value_mxn, 0) END) AS sum_value_mxn,
                   SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE COALESCE(lf.value_usd, 0) END) AS sum_value_usd
            FROM a76.item_lines il
            JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
            LEFT JOIN a24.fa_item_lines fil ON fil.id = il.id
            GROUP BY il.invoice_id
        ) lf_agg ON lf_agg.invoice_id = ih.id
        WHERE ih.operation_type = 'imp' 
            AND ih.invoice_type IN ('DEF', 'EXDEF', 'MATDE')
            AND {where_clause}
        ORDER BY ih.invoice_number
        """
    
    @staticmethod
    def build_main_query(db_name: str, where_clause: str) -> str:
        """Build main SQL query for DETAILED mode (all partidas) from PostgreSQL for definitive imports."""
        return f"""
        SELECT 
            ih.invoice_number AS C1,
            COALESCE(ped.pedimento_number, '') AS C2,
            TO_CHAR(ih.invoice_date, 'YYYYMMDD') AS C3,
            CASE WHEN ih.status THEN 'AC' ELSE 'NA' END AS C4,
            COALESCE(ped.pedimento_code, '') AS C5,
            COALESCE(fin.value_me, 0) AS C6,
            COALESCE(fin.value_mn, 0) AS C7,
            COALESCE(prov.name, '') AS C8,
            COALESCE(client.name, '') AS C9,
            COALESCE(ped.regime, '') AS C10,
            COALESCE(TO_CHAR(pd.entry_date, 'YYYYMMDD'), '') AS C11,
            COALESCE(TO_CHAR(pd.end_date, 'YYYYMMDD'), TO_CHAR(pd.payment_date, 'YYYYMMDD'), '') AS C12,
            COALESCE(TO_CHAR(pd.payment_date, 'YYYYMMDD'), '') AS C13,
            COALESCE(log.payment_receipt_num, '') AS C14,
            COALESCE(fin.exchange_rate, 0) AS C15,
            COALESCE(cmp.provider_id::text, '') AS C16,
            COALESCE(cmp.sold_to_id::text, '') AS C17,
            COALESCE(cmp.customs_broker_id::text, '') AS C18,
            '' AS C19,
            COALESCE(il.class_id::text, '') AS C20,
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.description_spanish, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C21,
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.description_english, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C22,
            COALESCE(lq.quantity, 0) AS C23,
            COALESCE(um.code, '') AS C24,
            COALESCE(lf.value_mxn, 0) AS C25,
            COALESCE(lf.customs_value_mxn, 0) AS C26,
            COALESCE(lf.value_usd, 0) AS C27,
            COALESCE(lf.customs_value_usd, 0) AS C28,
            COALESCE(lq.net_weight, 0) AS C29,
            COALESCE(lq.gross_weight, 0) AS C30,
            COALESCE(il.order, ih.purchase_order, '') AS C31,
            COALESCE(lc.fraction, '') AS C32,
            COALESCE(lc.fraction_type, '') AS C33,
            COALESCE(lc.advalorem_numeric, 0) AS C34,
            COALESCE(NULLIF(lc.sector, ''), NULLIF(fap.sector, ''), '') AS C35,
            COALESCE(lf.igi_amount_usd, 0) AS C36,
            COALESCE(lc.origin_country, '') AS C37,
            COALESCE(cmp.aduana, '') AS C38,
            ih.id AS C39,
            COALESCE(il.material_type, 'P') AS C40,
            COALESCE(
                ped_r1.year || '-' || ped_r1.customs_office || '-' || ped_r1.license || '-' || ped_r1.pedimento_number,
                pro_origin.original_pedimento_year || '-' || pro_origin.original_customs_office || '-' || pro_origin.original_license || '-' || pro_origin.original_pedimento_number,
                ''
            ) AS C41,
            cmp.edocument AS C42,
            cmp.vucem_operation_num AS C43,
            COALESCE(il.line_number, 0) AS C44,
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.brand, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C45,
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.model, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C46,
            COALESCE(cls.us_fraction, '') AS C47,
            COALESCE(prt.eccn, fac.eccn_code, '') AS C48,
            COALESCE(prt.part_number::text, '') AS C49,
            COALESCE(fin.exchange_rate, 0) AS C50,
            COALESCE(TO_CHAR(ih.emission_date, 'YYYYMMDD'), '') AS C51,
            COALESCE(ih.capture_user, '') AS C52,
            COALESCE(ih.who_processed, '') AS C53,
            COALESCE(log.carrier_id, '') AS C54,
            COALESCE(NULLIF(CONCAT_WS(' ', log.transport_id, log.transport_num), ''), '') AS C55,
            '' AS C56,
            COALESCE(ld.lot, '') AS C57,
            '' AS C58,
            0 AS C59,
            0 AS C60
        FROM a76.invoice_header ih
        LEFT JOIN a76.invoice_compliance_mx cmp ON cmp.invoice_id = ih.id
        LEFT JOIN a76.invoice_financials fin ON fin.invoice_id = ih.id
        LEFT JOIN a76.invoice_logistics log ON log.invoice_id = ih.id
        LEFT JOIN a76.pedimentos ped ON ped.id = cmp.pedimento_id
        LEFT JOIN a76.pedimento_rectification_origin pro_origin ON pro_origin.pedimento_id = ped.id
        LEFT JOIN a76.pedimento_rectification_origin pro_rect ON 
            pro_rect.original_pedimento_year = ped.year AND
            pro_rect.original_customs_office = ped.customs_office AND
            pro_rect.original_license = ped.license AND
            pro_rect.original_pedimento_number = ped.pedimento_number
        LEFT JOIN a76.pedimentos ped_r1 ON ped_r1.id = pro_rect.pedimento_id
        LEFT JOIN a76.pedimento_dates pd ON pd.pedimento_id = ped.id
        LEFT JOIN a76.clients_and_providers prov ON prov.id = cmp.provider_id
        LEFT JOIN a76.clients_and_providers client ON client.id = cmp.sold_to_id
        LEFT JOIN a76.item_lines il ON il.invoice_id = ih.id
        LEFT JOIN a76.item_line_descriptions ld ON ld.item_line_id = il.id
        LEFT JOIN a76.item_line_quantities lq ON lq.item_line_id = il.id
        LEFT JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
        LEFT JOIN a76.item_line_customs lc ON lc.item_line_id = il.id
        LEFT JOIN a76.classes cls ON cls.id = il.class_id
        LEFT JOIN a24.fa_classes fac ON fac.class_id = cls.id
        LEFT JOIN a76.parts prt ON prt.id = il.part_number_id
        LEFT JOIN a24.fa_partes fap ON fap.id = prt.id
        LEFT JOIN a76.units_of_measure um ON um.id = il.unit_of_measure
        WHERE {where_clause}
        ORDER BY ih.invoice_number, il.line_number
        """
    
    @staticmethod
    def build_totals_query(db_name: str) -> str:
        """Build query to get totals for a definitive import invoice."""
        return f"""
        SELECT 
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_usd END), 0), 
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_mxn END), 0) 
        FROM a76.item_line_financials lf
        INNER JOIN a76.item_lines il ON il.id = lf.item_line_id
        LEFT JOIN a24.fa_item_lines fil ON fil.id = il.id
        WHERE il.invoice_id = :consecutivo
            
        """
    
    @staticmethod
    def build_series_query(db_name: str) -> str:
        """Build query to get series information for definitive imports."""
        # TODO: QSeriesDef table not migrated to PostgreSQL yet
        return """
        SELECT '' as serie, '' as modelo, '' as parte 
        WHERE 1=0
        """
    
    @staticmethod
    def build_driver_badge_query(db_name: str) -> str:
        """Build query to get driver badge number for definitive imports."""
        # TODO: GConductor table not migrated to PostgreSQL yet
        return """
        SELECT '' as badge 
        WHERE 1=0
        """


class RepairImportQueries:
    """SQL queries for repair imports (PostgreSQL schema).
    
    Note: Repair imports are NOT identified by invoice_type, but by having
    cross-references (search_invoice field) that link them to export invoices.
    These are regular import invoices (TEM, DEF, etc.) that were imported for repair.
    """
    
    @staticmethod
    def build_aggregated_query(db_name: str, where_str: str, discharge_clause: str = "") -> str:
        """Build optimized query for NORMAL mode (grouped by invoice with totals)."""
        # Use fa_item_lines.discharge field (true = discharged, false = not discharged)
        if "SiDes" in discharge_clause:
            discharge_filter = "AND fil.discharge = true"
        elif "NoDes" in discharge_clause:
            discharge_filter = "AND fil.discharge = false"
        else:
            discharge_filter = ""

        return f"""
        SELECT 
            ih.invoice_number AS C2,
            COALESCE(ped.pedimento_number, '') AS C3,
            TO_CHAR(ih.invoice_date, 'YYYYMMDD') AS C4,
            CASE WHEN ih.status THEN 'AC' ELSE 'NA' END AS C5,
            COALESCE(ped.pedimento_code, '') AS C6,
            COALESCE(ped.regime, '') AS C7,
            COALESCE(TO_CHAR(pd.payment_date, 'YYYYMMDD'), '') AS C9,
            COALESCE(cmp.remesa::text, '') AS C10,
            COALESCE(fin.exchange_rate, 0) AS C11,
            COALESCE(cmp.provider_id::text, '') AS C12,
            COALESCE(cmp.sold_to_id::text, '') AS C13,
            COALESCE(cmp.customs_broker_id::text, '') AS C14,
            COALESCE(ih.purchase_order, '') AS C24,
            COALESCE(ped.customs_office, '') AS C29,
            ih.id AS C30,
            COALESCE(cmp.edocument, '') AS C33,
            COALESCE(cmp.vucem_operation_num, '') AS C34,
            COALESCE(fin.exchange_rate, 0) AS C40,
            COALESCE(TO_CHAR(ih.emission_date, 'YYYYMMDD'), '') AS C41,
            COALESCE(ih.capture_user, '') AS C42,
            COALESCE(ih.who_processed, '') AS C43,
            COALESCE(log.carrier_id, '') AS C44,
            COALESCE(log.transport_num, '') AS C45,
            COALESCE(ped.pedimento_code, '') AS C47,
            COALESCE(fin.value_me, 0) AS total_me,
            COALESCE(fin.value_mn, 0) AS total_mn,
            COALESCE(
                ped_r1.year || '-' || ped_r1.customs_office || '-' || ped_r1.license || '-' || ped_r1.pedimento_number,
                pro_origin.original_pedimento_year || '-' || pro_origin.original_customs_office || '-' || pro_origin.original_license || '-' || pro_origin.original_pedimento_number,
                ''
            ) AS C48,
            COALESCE(lf_agg.sum_value_usd, 0) AS sum_value_usd,
            COALESCE(lf_agg.sum_value_mxn, 0) AS sum_value_mxn
        FROM a76.invoice_header ih
        LEFT JOIN a76.invoice_compliance_mx cmp ON cmp.invoice_id = ih.id
        LEFT JOIN a76.invoice_financials fin ON fin.invoice_id = ih.id
        LEFT JOIN a76.invoice_logistics log ON log.invoice_id = ih.id
        LEFT JOIN a76.pedimentos ped ON ped.id = cmp.pedimento_id
        LEFT JOIN a76.pedimento_rectification_origin pro_origin ON pro_origin.pedimento_id = ped.id
        LEFT JOIN a76.pedimento_rectification_origin pro_rect ON 
            pro_rect.original_pedimento_year = ped.year AND
            pro_rect.original_customs_office = ped.customs_office AND
            pro_rect.original_license = ped.license AND
            pro_rect.original_pedimento_number = ped.pedimento_number
        LEFT JOIN a76.pedimentos ped_r1 ON ped_r1.id = pro_rect.pedimento_id
        LEFT JOIN a76.pedimento_dates pd ON pd.pedimento_id = ped.id
        LEFT JOIN (
            SELECT il.invoice_id,
                   SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE COALESCE(lf.value_mxn, 0) END) AS sum_value_mxn,
                   SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE COALESCE(lf.value_usd, 0) END) AS sum_value_usd
            FROM a76.item_lines il
            JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
            LEFT JOIN a24.fa_item_lines fil ON fil.id = il.id
            WHERE 1=1 {discharge_filter}
            GROUP BY il.invoice_id
        ) lf_agg ON lf_agg.invoice_id = ih.id
        WHERE ih.operation_type = 'imp' 
            AND COALESCE(cmp.is_regime_change, false) = false
            AND EXISTS (
                SELECT 1 FROM a76.item_lines il2
                INNER JOIN a24.fa_item_lines fil2 ON fil2.id = il2.id
                WHERE il2.invoice_id = ih.id AND fil2.search_invoice IS NOT NULL
                {discharge_filter}
            )
            {"AND " + where_str if where_str else ""}
        ORDER BY ih.invoice_number
        """
    
    @staticmethod
    def build_main_query(db_name: str, where_str: str, discharge_clause: str = "") -> str:
        """Build main SQL query for repair import data."""
        # Use fa_item_lines.discharge field (true = discharged, false = not discharged)
        if "SiDes" in discharge_clause:
            discharge_filter = "AND fil.discharge = true"
        elif "NoDes" in discharge_clause:
            discharge_filter = "AND fil.discharge = false"
        else:
            discharge_filter = ""
        return f"""
        SELECT 
            il.line_number,
            ih.invoice_number,
            COALESCE(ped.pedimento_number, ''),
            TO_CHAR(ih.invoice_date, 'YYYYMMDD'),
            CASE WHEN ih.status THEN 'AC' ELSE 'NA' END,
            COALESCE(ped.pedimento_code, ''),
            COALESCE(ped.regime, ''),
            COALESCE(TO_CHAR(pd.entry_date, 'YYYYMMDD'), ''),
            COALESCE(TO_CHAR(pd.end_date, 'YYYYMMDD'), TO_CHAR(pd.payment_date, 'YYYYMMDD'), ''),
            COALESCE(TO_CHAR(pd.payment_date, 'YYYYMMDD'), ''),
            COALESCE(cmp.remesa::text, ''),
            COALESCE(fin.exchange_rate, 0),
            COALESCE(cmp.provider_id::text, ''),
            COALESCE(cmp.sold_to_id::text, ''),
            COALESCE(cmp.customs_broker_id::text, ''),
            COALESCE(prt.part_number::text, ''),
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.description_spanish, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' '),
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.description_english, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' '),
            COALESCE(lq.quantity, 0),
            COALESCE(il.unit_of_measure, 0),
            COALESCE(lf.value_mxn, 0),
            COALESCE(lf.value_usd, 0),
            COALESCE(lq.net_weight, 0),
            COALESCE(lq.gross_weight, 0),
            COALESCE(il.order, ih.purchase_order, ''),
            COALESCE(lc.fraction, ''),
            '',
            COALESCE(NULLIF(lc.sector, ''), NULLIF(fap.sector, ''), ''),
            COALESCE(lc.origin_country, ''),
            COALESCE(ped.customs_office, ''),
            ih.id,
            COALESCE(il.material_type, 'P'),
            COALESCE(
                ped_r1.year || '-' || ped_r1.customs_office || '-' || ped_r1.license || '-' || ped_r1.pedimento_number,
                pro_origin.original_pedimento_year || '-' || pro_origin.original_customs_office || '-' || pro_origin.original_license || '-' || pro_origin.original_pedimento_number,
                ''
            ),
            COALESCE(cmp.edocument, ''),
            COALESCE(cmp.vucem_operation_num, ''),
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.brand, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' '),
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.model, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' '),
            COALESCE(lc.american_fraction, ''),
            COALESCE(prt.eccn, fac.eccn_code, ''),
            COALESCE(fin.exchange_rate, 0),
            COALESCE(TO_CHAR(ih.emission_date, 'YYYYMMDD'), ''),
            COALESCE(ih.capture_user, ''),
            COALESCE(ih.who_processed, ''),
            COALESCE(log.carrier_id, ''),
            COALESCE(log.transport_num, ''),
            '',
            COALESCE(ped.pedimento_code, '')
        FROM a76.invoice_header ih
        LEFT JOIN a76.invoice_compliance_mx cmp ON cmp.invoice_id = ih.id
        LEFT JOIN a76.invoice_financials fin ON fin.invoice_id = ih.id
        LEFT JOIN a76.invoice_logistics log ON log.invoice_id = ih.id
        LEFT JOIN a76.pedimentos ped ON ped.id = cmp.pedimento_id
        LEFT JOIN a76.pedimento_rectification_origin pro_origin ON pro_origin.pedimento_id = ped.id
        LEFT JOIN a76.pedimento_rectification_origin pro_rect ON 
            pro_rect.original_pedimento_year = ped.year AND
            pro_rect.original_customs_office = ped.customs_office AND
            pro_rect.original_license = ped.license AND
            pro_rect.original_pedimento_number = ped.pedimento_number
        LEFT JOIN a76.pedimentos ped_r1 ON ped_r1.id = pro_rect.pedimento_id
        LEFT JOIN a76.pedimento_dates pd ON pd.pedimento_id = ped.id
        LEFT JOIN a76.item_lines il ON il.invoice_id = ih.id
        LEFT JOIN a76.item_line_descriptions ld ON ld.item_line_id = il.id
        LEFT JOIN a76.item_line_quantities lq ON lq.item_line_id = il.id
        LEFT JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
        LEFT JOIN a76.item_line_customs lc ON lc.item_line_id = il.id
        LEFT JOIN a24.fa_item_lines fil ON fil.id = il.id
        LEFT JOIN a76.classes cls ON cls.id = il.class_id
        LEFT JOIN a24.fa_classes fac ON fac.class_id = cls.id
        LEFT JOIN a76.parts prt ON prt.id = il.part_number_id
        LEFT JOIN a24.fa_partes fap ON fap.id = prt.id
        LEFT JOIN a76.units_of_measure um ON um.id = il.unit_of_measure
        WHERE ih.operation_type = 'imp' 
            AND COALESCE(cmp.is_regime_change, false) = false
            AND fil.search_invoice IS NOT NULL
            {"AND " + where_str if where_str else ""}
            {discharge_filter}
        ORDER BY ih.invoice_number, il.line_number
        """
    
    @staticmethod
    def build_totals_query(db_name: str, discharge_clause: str = "") -> str:
        """Build query to get totals for a repair import invoice."""
        # Use fa_item_lines.discharge field (true = discharged, false = not discharged)
        if "SiDes" in discharge_clause:
            discharge_filter = "AND fil.discharge = true"
        elif "NoDes" in discharge_clause:
            discharge_filter = "AND fil.discharge = false"
        else:
            discharge_filter = ""
        return f"""
        SELECT 
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_usd END), 0), 
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_mxn END), 0)
        FROM a76.item_line_financials lf
        INNER JOIN a76.item_lines il ON il.id = lf.item_line_id
        LEFT JOIN a24.fa_item_lines fil ON fil.id = il.id
        WHERE il.invoice_id = :consecutivo
            
            {discharge_filter}
        """
    
    @staticmethod
    def build_series_query(db_name: str) -> str:
        """Build query to get series information for repair imports."""
        # TODO: QSeriesImpoRep table not migrated to PostgreSQL yet
        return """
        SELECT '' as serie, '' as modelo, '' as parte 
        WHERE 1=0
        """
    
    @staticmethod
    def build_driver_badge_query(db_name: str) -> str:
        """Build query to get driver badge number for repair imports."""
        # TODO: GConductor table not migrated to PostgreSQL yet
        return """
        SELECT '' as badge 
        WHERE 1=0
        """


class ExportQueries:
    """SQL queries for exports (PostgreSQL schema)."""
    
    @staticmethod
    def build_aggregated_query(db_name: str, where_clause: str) -> str:
        """
        Build optimized query for NORMAL mode (grouped by invoice with totals).
        
        Args:
            db_name: Database name (not used in PostgreSQL version)
            where_clause: Additional WHERE conditions (without WHERE keyword)
        """
        return f"""
        SELECT 
            ih.invoice_number AS C1,
            COALESCE(ped.pedimento_number, '') AS C2,
            TO_CHAR(ih.invoice_date, 'YYYYMMDD') AS C3,
            CASE WHEN ih.status THEN 'AC' ELSE 'NA' END AS C6,
            COALESCE(ped.pedimento_code, '') AS C7,
            COALESCE(ped.regime, '') AS C8,
            COALESCE(TO_CHAR(pd.end_date, 'YYYYMMDD'), TO_CHAR(pd.payment_date, 'YYYYMMDD'), '') AS C10,
            COALESCE(TO_CHAR(pd.payment_date, 'YYYYMMDD'), '') AS C11,
            COALESCE(log.payment_receipt_num, '') AS C12,
            COALESCE(cmp.provider_id::text, '') AS C14,
            COALESCE(cmp.sold_to_id::text, '') AS C15,
            COALESCE(cmp.customs_broker_id::text, '') AS C16,
            COALESCE(ih.purchase_order, '') AS C27,
            COALESCE(cmp.aduana, '') AS C33,
            COALESCE(ih.invoice_type, '') AS C34,
            ih.id AS C35,
            COALESCE(cmp.edocument, '') AS C40,
            COALESCE(cmp.vucem_operation_num, '') AS C41,
            COALESCE(fin.exchange_rate, 0) AS C48,
            COALESCE(TO_CHAR(ih.emission_date, 'YYYYMMDD'), '') AS C49,
            COALESCE(ih.capture_user, '') AS C50,
            COALESCE(ih.who_processed, '') AS C51,
            COALESCE(NULLIF(CONCAT_WS(' ', log.transport_id, log.transport_num), ''), '') AS C53,
            COALESCE(fin.value_me, 0) AS total_me,
            COALESCE(fin.value_mn, 0) AS total_mn,
            COALESCE(
                ped_r1.year || '-' || ped_r1.customs_office || '-' || ped_r1.license || '-' || ped_r1.pedimento_number,
                pro_origin.original_pedimento_year || '-' || pro_origin.original_customs_office || '-' || pro_origin.original_license || '-' || pro_origin.original_pedimento_number,
                ''
            ) AS C54,
            COALESCE(lf_agg.sum_value_usd, 0) AS sum_value_usd,
            COALESCE(lf_agg.sum_value_mxn, 0) AS sum_value_mxn
        FROM a76.invoice_header ih
        LEFT JOIN a76.invoice_compliance_mx cmp ON cmp.invoice_id = ih.id
        LEFT JOIN a76.invoice_financials fin ON fin.invoice_id = ih.id
        LEFT JOIN a76.invoice_logistics log ON log.invoice_id = ih.id
        LEFT JOIN a76.pedimentos ped ON ped.id = cmp.pedimento_id
        LEFT JOIN a76.pedimento_rectification_origin pro_origin ON pro_origin.pedimento_id = ped.id
        LEFT JOIN a76.pedimento_rectification_origin pro_rect ON 
            pro_rect.original_pedimento_year = ped.year AND
            pro_rect.original_customs_office = ped.customs_office AND
            pro_rect.original_license = ped.license AND
            pro_rect.original_pedimento_number = ped.pedimento_number
        LEFT JOIN a76.pedimentos ped_r1 ON ped_r1.id = pro_rect.pedimento_id
        LEFT JOIN a76.pedimento_dates pd ON pd.pedimento_id = ped.id
        LEFT JOIN (
            SELECT il.invoice_id,
                   SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE COALESCE(lf.value_mxn, 0) END) AS sum_value_mxn,
                   SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE COALESCE(lf.value_usd, 0) END) AS sum_value_usd
            FROM a76.item_lines il
            JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
            LEFT JOIN a24.fa_item_lines fil ON fil.id = il.id
            GROUP BY il.invoice_id
        ) lf_agg ON lf_agg.invoice_id = ih.id
        WHERE {where_clause}
        ORDER BY ih.invoice_number
        """
    
    @staticmethod
    def build_main_query(db_name: str, where_clause: str) -> str:
        return f"""
        SELECT 
            ih.invoice_number AS C1,        -- [0]
            ped.pedimento_number AS C2,     -- [1]
            ih.invoice_date AS C3,          -- [2]
            '' AS C4,                       -- [3]
            ped.pedimento_code AS C5,       -- [4]
            ped.regime AS C6,               -- [5]
            pd.entry_date AS C7,      -- [6]
            COALESCE(pd.end_date, pd.payment_date) AS C8,       -- [7]
            pd.payment_date AS C9,         -- [8]
            log.payment_receipt_num AS C12, -- [11]
            '' AS C13,                      -- [12]
            cmp.provider_id AS C14,         -- [13]
            cmp.sold_to_id AS C15,          -- [14]
            cmp.customs_broker_id AS C16,   -- [15]
            '' AS C17,                      -- [16]
            COALESCE(prt.part_number::text, '') AS C18, -- [17]
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.description_spanish, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C19,  -- [18]
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.description_english, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C20,  -- [19]
            lq.quantity AS C21,             -- [20]
            um.code AS C22,                 -- [21]
            '' AS C23,                      -- [22]
            '' AS C24,                      -- [23]
            COALESCE(lq.net_weight, 0) AS C25,           -- [24]
            COALESCE(lq.gross_weight, 0) AS C26,         -- [25]
            COALESCE(il.order, ih.purchase_order, '') AS C27,       -- [26]
            COALESCE(lc.fraction, '') AS C28,             -- [27]
            COALESCE(lc.fraction_type, '') AS C29,        -- [28]
            COALESCE(lc.advalorem_numeric, 0) AS C30,     -- [29]
            COALESCE(NULLIF(lc.sector, ''), NULLIF(fap.sector, ''), '') AS C31,               -- [30]
            COALESCE(lc.origin_country, '') AS C32,       -- [31]
            cmp.aduana AS C33,              -- [32]
            ih.invoice_type AS C34,         -- [33]
            ih.id AS C35,                   -- [34]
            lf.value_mxn AS C36,            -- [35]
            lf.value_usd AS C37,            -- [36]
            il.material_type AS C38,        -- [37]
            COALESCE(
                ped_r1.year || '-' || ped_r1.customs_office || '-' || ped_r1.license || '-' || ped_r1.pedimento_number,
                pro_origin.original_pedimento_year || '-' || pro_origin.original_customs_office || '-' || pro_origin.original_license || '-' || pro_origin.original_pedimento_number,
                ''
            ) AS C39,           -- [38] rectification_id
            cmp.edocument AS C40,           -- [39]
            cmp.vucem_operation_num AS C41, -- [40]
            il.line_number AS C42,          -- [41]
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.brand, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C43,  -- [42]
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.model, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C44,  -- [43]
            COALESCE(cls.us_fraction, '') AS C45,         -- [44]
            COALESCE(prt.eccn, fac.eccn_code, '') AS C46,                -- [45]
            prt.id AS C47,                  -- [46]
            fin.exchange_rate AS C48,       -- [47]
            ih.emission_date AS C49,        -- [48]
            ih.capture_user AS C50,         -- [49]
            ih.who_processed AS C51,          -- [50]
            '' AS C52,                      -- [51]
            COALESCE(NULLIF(CONCAT_WS(' ', log.transport_id, log.transport_num), ''), '') AS C53, -- [52] NumCaja
            '' AS C54,                      -- [53] Pedimento18
            COALESCE(ld.lot, '') AS C55,    -- [54] Lote
            '' AS C56,                      -- [55] TipoPedimentoTransporte
            '' AS C57,                      -- [56] 
            '' AS C58,                      -- [57] 
            '' AS C59,                      -- [58] 
            '' AS C60                       -- [59] Relleno final
        FROM a76.invoice_header ih
        LEFT JOIN a76.invoice_compliance_mx cmp ON cmp.invoice_id = ih.id
        LEFT JOIN a76.invoice_financials fin ON fin.invoice_id = ih.id
        LEFT JOIN a76.invoice_logistics log ON log.invoice_id = ih.id
        LEFT JOIN a76.pedimentos ped ON ped.id = cmp.pedimento_id
        LEFT JOIN a76.pedimento_rectification_origin pro_origin ON pro_origin.pedimento_id = ped.id
        LEFT JOIN a76.pedimento_rectification_origin pro_rect ON 
            pro_rect.original_pedimento_year = ped.year AND
            pro_rect.original_customs_office = ped.customs_office AND
            pro_rect.original_license = ped.license AND
            pro_rect.original_pedimento_number = ped.pedimento_number
        LEFT JOIN a76.pedimentos ped_r1 ON ped_r1.id = pro_rect.pedimento_id
        LEFT JOIN a76.pedimento_dates pd ON pd.pedimento_id = ped.id
        LEFT JOIN a76.item_lines il ON il.invoice_id = ih.id
        LEFT JOIN a76.item_line_descriptions ld ON ld.item_line_id = il.id
        LEFT JOIN a76.item_line_quantities lq ON lq.item_line_id = il.id
        LEFT JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
        LEFT JOIN a76.item_line_customs lc ON lc.item_line_id = il.id
        LEFT JOIN a76.classes cls ON cls.id = il.class_id
        LEFT JOIN a24.fa_classes fac ON fac.class_id = cls.id
        LEFT JOIN a76.parts prt ON prt.id = il.part_number_id
        LEFT JOIN a24.fa_partes fap ON fap.id = prt.id
        LEFT JOIN a76.units_of_measure um ON um.id = il.unit_of_measure
        WHERE {where_clause}
        ORDER BY ih.invoice_number, il.line_number
        """
    
    @staticmethod
    def build_totals_query(db_name: str, discharge_clause: str = "") -> str:
        """Build query to get totals for an export invoice.
        
        Only sums partidas where is_subitem is false (main partidas, not sub-items).
        """
        # Use fa_item_lines.discharge field (true = discharged, false = not discharged)
        if "SiDes" in discharge_clause:
            discharge_filter = "AND fil.discharge = true"
        elif "NoDes" in discharge_clause:
            discharge_filter = "AND fil.discharge = false"
        else:
            discharge_filter = ""
        return f"""
        SELECT 
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_usd END), 0), 
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_mxn END), 0) 
        FROM a76.item_line_financials lf
        INNER JOIN a76.item_lines il ON il.id = lf.item_line_id
        LEFT JOIN a24.fa_item_lines fil ON fil.id = il.id
        WHERE il.invoice_id = :consecutivo
            {discharge_filter}
        """
    
    @staticmethod
    def build_series_query(db_name: str) -> str:
        """Build query to get series information for exports."""
        # TODO: QSeriesExpo table not migrated to PostgreSQL yet
        return """
        SELECT '' as serie, '' as modelo, '' as parte 
        WHERE 1=0
        """
    
    @staticmethod
    def build_driver_badge_query(db_name: str) -> str:
        """Build query to get driver badge number for exports."""
        # TODO: GConductor table not migrated to PostgreSQL yet
        return """
        SELECT '' as badge 
        WHERE 1=0
        """


class ExportRepairQueries:
    """SQL queries for export repairs (PostgreSQL schema)."""
    
    @staticmethod
    def build_aggregated_query(db_name: str, where_str: str, discharge_clause: str = "") -> str:
        """Build optimized query for NORMAL mode (grouped by invoice with totals)."""
        # Use fa_item_lines.discharge field (true = discharged, false = not discharged)
        if "SiDes" in discharge_clause:
            discharge_filter = "AND fil.discharge = true"
        elif "NoDes" in discharge_clause:
            discharge_filter = "AND fil.discharge = false"
        else:
            discharge_filter = ""

        return f"""
        SELECT 
            ih.invoice_number AS C1,
            COALESCE(ped.pedimento_number, '') AS C2,
            TO_CHAR(ih.invoice_date, 'YYYYMMDD') AS C3,
            CASE WHEN ih.status THEN 'AC' ELSE 'NA' END AS C6,
            COALESCE(ped.pedimento_code, '') AS C7,
            COALESCE(ped.regime, '') AS C8,
            COALESCE(TO_CHAR(pd.payment_date, 'YYYYMMDD'), '') AS C11,
            COALESCE(cmp.remesa::text, '') AS C12,
            COALESCE(fin.exchange_rate, 0) AS C13,
            COALESCE(cmp.provider_id::text, '') AS C14,
            COALESCE(cmp.sold_to_id::text, '') AS C15,
            COALESCE(cmp.customs_broker_id::text, '') AS C16,
            COALESCE(ih.purchase_order, '') AS C27,
            COALESCE(ped.customs_office, '') AS C33,
            COALESCE(ih.document_type, '') AS C34,
            ih.id AS C35,
            COALESCE(cmp.edocument, '') AS C40,
            COALESCE(cmp.vucem_operation_num, '') AS C41,
            COALESCE(fin.exchange_rate, 0) AS C48,
            COALESCE(TO_CHAR(ih.invoice_date, 'YYYYMMDD'), '') AS C49,
            COALESCE(ih.capture_user, '') AS C50,
            COALESCE(ih.who_processed, '') AS C51,
            COALESCE(log.carrier_id, '') AS C52,
            COALESCE(NULLIF(CONCAT_WS(' ', log.transport_id, log.transport_num), ''), '') AS C53,
            COALESCE(fin.value_me, 0) AS total_me,
            COALESCE(fin.value_mn, 0) AS total_mn,
            COALESCE(
                ped_r1.year || '-' || ped_r1.customs_office || '-' || ped_r1.license || '-' || ped_r1.pedimento_number,
                pro_origin.original_pedimento_year || '-' || pro_origin.original_customs_office || '-' || pro_origin.original_license || '-' || pro_origin.original_pedimento_number,
                ''
            ) AS C54,
            COALESCE(lf_agg.sum_value_usd, 0) AS sum_value_usd,
            COALESCE(lf_agg.sum_value_mxn, 0) AS sum_value_mxn
        FROM a76.invoice_header ih
        LEFT JOIN a76.invoice_compliance_mx cmp ON cmp.invoice_id = ih.id
        LEFT JOIN a76.invoice_financials fin ON fin.invoice_id = ih.id
        LEFT JOIN a76.invoice_logistics log ON log.invoice_id = ih.id
        LEFT JOIN a76.pedimentos ped ON ped.id = cmp.pedimento_id
        LEFT JOIN a76.pedimento_rectification_origin pro_origin ON pro_origin.pedimento_id = ped.id
        LEFT JOIN a76.pedimento_rectification_origin pro_rect ON 
            pro_rect.original_pedimento_year = ped.year AND
            pro_rect.original_customs_office = ped.customs_office AND
            pro_rect.original_license = ped.license AND
            pro_rect.original_pedimento_number = ped.pedimento_number
        LEFT JOIN a76.pedimentos ped_r1 ON ped_r1.id = pro_rect.pedimento_id
        LEFT JOIN a76.pedimento_dates pd ON pd.pedimento_id = ped.id
        LEFT JOIN (
            SELECT il.invoice_id,
                   SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE COALESCE(lf.value_mxn, 0) END) AS sum_value_mxn,
                   SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE COALESCE(lf.value_usd, 0) END) AS sum_value_usd
            FROM a76.item_lines il
            JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
            LEFT JOIN a24.fa_item_lines fil ON fil.id = il.id
            WHERE 1=1 {discharge_filter}
            GROUP BY il.invoice_id
        ) lf_agg ON lf_agg.invoice_id = ih.id
        WHERE ih.operation_type = 'exp'
            AND ih.invoice_type = 'REPAR'
            {"AND " + where_str if where_str else ""}
        ORDER BY ih.invoice_number
        """
    
    @staticmethod
    def build_main_query(db_name: str, where_str: str) -> str:
        """Build main SQL query for export repair data."""
        return f"""
        SELECT 
            ih.invoice_number AS C1,
            COALESCE(ped.pedimento_number, '') AS C2,
            TO_CHAR(ih.invoice_date, 'YYYYMMDD') AS C3,
            COALESCE(fin.value_me, 0) AS C4,
            COALESCE(fin.value_mn, 0) AS C5,
            CASE WHEN ih.status THEN 'AC' ELSE 'NA' END AS C6,
            COALESCE(ped.pedimento_code, '') AS C7,
            COALESCE(ped.regime, '') AS C8,
            COALESCE(TO_CHAR(pd.entry_date, 'YYYYMMDD'), '') AS C9,
            COALESCE(TO_CHAR(pd.end_date, 'YYYYMMDD'), TO_CHAR(pd.payment_date, 'YYYYMMDD'), '') AS C10,
            COALESCE(TO_CHAR(pd.payment_date, 'YYYYMMDD'), '') AS C11,
            COALESCE(cmp.remesa::text, '') AS C12,
            COALESCE(fin.exchange_rate, 0) AS C13,
            COALESCE(cmp.provider_id::text, '') AS C14,
            COALESCE(cmp.sold_to_id::text, '') AS C15,
            COALESCE(cmp.customs_broker_id::text, '') AS C16,
            '' AS C17,
            COALESCE(cls.class_code, '') AS C18,
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.description_spanish, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C19,
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(cls.description_en, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C20,
            COALESCE(lq.quantity, 0) AS C21,
            COALESCE(il.unit_of_measure, 0) AS C22,
            COALESCE(lf.customs_value_mxn, 0) AS C23,
            COALESCE(lf.customs_value_usd, 0) AS C24,
            COALESCE(lq.net_weight, 0) AS C25,
            COALESCE(lq.gross_weight, 0) AS C26,
            COALESCE(il.order, ih.purchase_order, '') AS C27,
            COALESCE(lc.fraction, '') AS C28,
            COALESCE(lc.fraction_type, '') AS C29,
            COALESCE(lc.advalorem_numeric, 0) AS C30,
            COALESCE(NULLIF(lc.sector, ''), NULLIF(fap.sector, ''), '') AS C31,
            COALESCE(lc.origin_country, '') AS C32,
            COALESCE(ped.customs_office, '') AS C33,
            COALESCE(ih.document_type, '') AS C34,
            ih.id AS C35,
            COALESCE(lf.value_mxn, 0) AS C36,
            COALESCE(lf.value_usd, 0) AS C37,
            COALESCE(il.material_type, 'P') AS C38,
            COALESCE(
                ped_r1.year || '-' || ped_r1.customs_office || '-' || ped_r1.license || '-' || ped_r1.pedimento_number,
                pro_origin.original_pedimento_year || '-' || pro_origin.original_customs_office || '-' || pro_origin.original_license || '-' || pro_origin.original_pedimento_number,
                ''
            ) AS C39,
            COALESCE(cmp.edocument, '') AS C40,
            COALESCE(cmp.vucem_operation_num, '') AS C41,
            COALESCE(il.line_number, 0) AS C42,
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.brand, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C43,
            REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(ld.model, ''), CHR(44), ' '), CHR(9), ' '), CHR(10), ' '), CHR(13), ' ') AS C44,
            COALESCE(cls.us_fraction, '') AS C45,
            COALESCE(prt.eccn, fac.eccn_code, '') AS C46,
            COALESCE(prt.part_number::text, '') AS C47,
            COALESCE(fin.exchange_rate, 0) AS C48,
            COALESCE(TO_CHAR(ih.invoice_date, 'YYYYMMDD'), '') AS C49,
            COALESCE(ih.capture_user, '') AS C50,
            COALESCE(ih.who_processed, '') AS C51,
            COALESCE(log.carrier_id, '') AS C52,
            COALESCE(NULLIF(CONCAT_WS(' ', log.transport_id, log.transport_num), ''), '') AS C53,
            '' AS C54,
           COALESCE(ld.lot, '') AS C55,
            '' AS C56,
            '' AS C57,
            '' AS C58,
            '' AS C59
        FROM a76.invoice_header ih
        LEFT JOIN a76.invoice_compliance_mx cmp ON cmp.invoice_id = ih.id
        LEFT JOIN a76.invoice_financials fin ON fin.invoice_id = ih.id
        LEFT JOIN a76.invoice_logistics log ON log.invoice_id = ih.id
        LEFT JOIN a76.pedimentos ped ON ped.id = cmp.pedimento_id
        LEFT JOIN a76.pedimento_rectification_origin pro_origin ON pro_origin.pedimento_id = ped.id
        LEFT JOIN a76.pedimento_rectification_origin pro_rect ON 
            pro_rect.original_pedimento_year = ped.year AND
            pro_rect.original_customs_office = ped.customs_office AND
            pro_rect.original_license = ped.license AND
            pro_rect.original_pedimento_number = ped.pedimento_number
        LEFT JOIN a76.pedimentos ped_r1 ON ped_r1.id = pro_rect.pedimento_id
        LEFT JOIN a76.pedimento_dates pd ON pd.pedimento_id = ped.id
        LEFT JOIN a76.item_lines il ON il.invoice_id = ih.id
        LEFT JOIN a76.item_line_descriptions ld ON ld.item_line_id = il.id
        LEFT JOIN a76.item_line_quantities lq ON lq.item_line_id = il.id
        LEFT JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
        LEFT JOIN a76.item_line_customs lc ON lc.item_line_id = il.id
        LEFT JOIN a76.classes cls ON cls.id = il.class_id
        LEFT JOIN a24.fa_classes fac ON fac.class_id = cls.id
        LEFT JOIN a76.parts prt ON prt.id = il.part_number_id
        LEFT JOIN a24.fa_partes fap ON fap.id = prt.id
        LEFT JOIN a76.units_of_measure um ON um.id = il.unit_of_measure
       WHERE UPPER(ih.operation_type) IN ('EXP', 'TRA', 'RET') 
  AND UPPER(ih.invoice_type) IN ('DEF', 'REPAR', 'EXDEF', 'MATDE')
            AND {where_str}
        ORDER BY ih.invoice_number, il.line_number
        """ 
    
    @staticmethod
    def build_totals_query(db_name: str, discharge_clause: str = "") -> str:
        """Build query to get totals for an export repair invoice."""
        # Use fa_item_lines.discharge field (true = discharged, false = not discharged)
        if "SiDes" in discharge_clause:
            discharge_filter = "AND fil.discharge = true"
        elif "NoDes" in discharge_clause:
            discharge_filter = "AND fil.discharge = false"
        else:
            discharge_filter = ""
        return f"""
        SELECT 
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_usd END), 0), 
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_mxn END), 0) 
        FROM a76.item_line_financials lf
        INNER JOIN a76.item_lines il ON il.id = lf.item_line_id
        LEFT JOIN a24.fa_item_lines fil ON il.id = lf.item_line_id
        WHERE il.invoice_id = :consecutivo
            
            {discharge_filter}
        """
    
    @staticmethod
    def build_series_query(db_name: str) -> str:
        """Build query to get series information for export repairs."""
        # TODO: QSeriesExpoRep table not migrated to PostgreSQL yet
        return """
        SELECT '' as serie, '' as modelo, '' as parte 
        WHERE 1=0
        """
    
    @staticmethod
    def build_driver_badge_query(db_name: str) -> str:
        """Build query to get driver badge number for export repairs."""
        # TODO: GConductor table not migrated to PostgreSQL yet
        return """
        SELECT '' as badge 
        WHERE 1=0
        """
