"""
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.is_updated THEN 'AC' ELSE 'NA' END AS C4,
            COALESCE(ped.pedimento_code, '') AS C5,
            COALESCE(ped.regime, '') AS C10,
            COALESCE(TO_CHAR(log.entry_exit_date, 'YYYYMMDD'), '') AS C11,
            COALESCE(TO_CHAR(log.delivery_date, 'YYYYMMDD'), '') AS C12,
            COALESCE(TO_CHAR(log.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.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_updated, '') AS C53,
            COALESCE(log.carrier_id, '') AS C54,
            COALESCE(log.transport_num || ' ' || log.license_plate, '') AS C55,
            '' AS C56,
            '' AS C57,
            '' AS C58,
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_usd END), 0) AS total_me,
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_mxn END), 0) AS total_mn
        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.pedimentos ped_r1 ON ped_r1.id = cmp.pedimento_r1
        LEFT JOIN a76.items i ON i.invoice_id = ih.id
        LEFT JOIN a76.item_lines il ON il.item_id = i.id
        LEFT JOIN a24.fa_item_lines fil ON fil.id = il.id
        LEFT JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
        WHERE ih.operation_type = 'imp' 
            AND ih.invoice_type = 'TEM'
            AND {where_str}
        GROUP BY ih.id, ih.invoice_number, ped.pedimento_number, ped.pedimento_code, ped.regime,
                 log.entry_exit_date, log.delivery_date, log.payment_date, cmp.remesa, fin.exchange_rate,
                 cmp.provider_id, cmp.sold_to_id, cmp.customs_broker_id, cmp.aduana, ped_r1.pedimento_number,
                 cmp.edocument, cmp.vucem_operation_num, ih.emission_date, ih.capture_user, ih.who_updated,
                 log.carrier_id, log.transport_num, log.license_plate
        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.is_updated 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(cmp.provider_id::text, '') AS C8,
            COALESCE(cmp.sold_to_id::text, '') AS C9,
            COALESCE(ped.regime, '') AS C10,
            COALESCE(TO_CHAR(log.entry_exit_date, 'YYYYMMDD'), '') AS C11,
            COALESCE(TO_CHAR(log.delivery_date, 'YYYYMMDD'), '') AS C12,
            COALESCE(TO_CHAR(log.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(ih.purchase_order, '') AS C31,
            COALESCE(lc.fraction, '') AS C32,
            COALESCE(lc.fraction_type, '') AS C33,
            COALESCE(lc.advalorem_numeric, 0) AS C34,
            COALESCE(lc.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,
            FALSE AS C40,
            '' AS C41,
            COALESCE(cmp.edocument, '') AS C42,
            COALESCE(cmp.vucem_operation_num, '') AS C43,
            COALESCE(il.line_number, 0) AS C44,
            '' AS C45,
            '' AS C46,
            COALESCE(cls.us_fraction, '') AS C47,
            COALESCE(prt.eccn, '') AS C48,
            COALESCE(il.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_updated, '') AS C53,
            COALESCE(log.carrier_id, '') AS C54,
            COALESCE(log.transport_num || ' ' || log.license_plate, '') AS C55,
            '' AS C56,
            '' AS C57,
            '' AS C58
        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.item_lines il ON il.item_id = (
            SELECT id FROM a76.items WHERE invoice_id = ih.id LIMIT 1
        )
        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 a76.parts prt ON prt.id = il.part_number
        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}
        """
    
    @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.is_updated THEN 'AC' ELSE 'NA' END AS C4,
            COALESCE(ped.pedimento_code, '') AS C5,
            COALESCE(ped.regime, '') AS C10,
            COALESCE(TO_CHAR(log.entry_exit_date, 'YYYYMMDD'), '') AS C11,
            COALESCE(TO_CHAR(log.delivery_date, 'YYYYMMDD'), '') AS C12,
            COALESCE(TO_CHAR(log.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.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_updated, '') AS C54,
            COALESCE(log.transport_id || ' ' || log.transport_num, '') AS C56,
            '' AS C57,
            '' AS C58,
            '' AS C59,
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_usd END), 0) AS total_me,
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_mxn END), 0) AS total_mn
        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.pedimentos ped_r1 ON ped_r1.id = cmp.pedimento_r1
        LEFT JOIN a76.items i ON i.invoice_id = ih.id
        LEFT JOIN a76.item_lines il ON il.item_id = i.id
        LEFT JOIN a24.fa_item_lines fil ON fil.id = il.id
        LEFT JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
        WHERE ih.operation_type = 'imp' 
            AND ih.invoice_type IN ('DEF', 'EXDEF', 'MATDE')
            AND {where_clause}
        GROUP BY ih.id, ih.invoice_number, ped.pedimento_number, ped.pedimento_code, ped.regime,
                 log.entry_exit_date, log.delivery_date, log.payment_date, log.payment_receipt_num,
                 fin.exchange_rate, cmp.provider_id, cmp.sold_to_id, cmp.customs_broker_id, 
                 ih.purchase_order, cmp.aduana, ped_r1.pedimento_number, cmp.edocument, 
                 cmp.vucem_operation_num, ih.emission_date, ih.capture_user, ih.who_updated,
                 log.transport_id, log.transport_num
        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]
            ped.status AS C4,               -- [3]
            ped.pedimento_code AS C5,       -- [4]
            '' AS C6, '' AS C7, '' AS C8, '' AS C9, -- [5-8]
            ped.regime AS C10,              -- [9]
            log.entry_exit_date AS C11,     -- [10]
            log.delivery_date AS C12,       -- [11]
            log.payment_date AS C13,        -- [12]
            log.payment_receipt_num AS C14, -- [13]
            '' AS C15,                      -- [14]
            cmp.provider_id AS C16,         -- [15]
            cmp.sold_to_id AS C17,          -- [16]
            cmp.customs_broker_id AS C18,   -- [17]
            '' AS C19,                      -- [18]
            prt.part_number AS C20,         -- [19]
            ld.description_spanish AS C21,  -- [20]
            ld.description_english AS C22,  -- [21]
            lq.quantity AS C23,             -- [22]
            um.code AS C24,                 -- [23]
            lf.value_mxn AS C25,            -- [24]
            '' AS C26,                      -- [25]
            lf.value_usd AS C27,            -- [26]
            '' AS C28,                      -- [27]
            lq.net_weight AS C29,           -- [28]
            lq.gross_weight AS C30,         -- [29]
            ih.purchase_order AS C31,       -- [30]
            lc.fraction AS C32,             -- [31]
            '' AS C33, '' AS C34,           -- [32-33]
            ih.id AS C35,                   -- [34]
            '' AS C36, '' AS C37,           -- [35-36]
            lc.origin_country AS C38,       -- [37]
            cmp.aduana AS C39,              -- [38]
            il.material_type AS C40,        -- [39]
            il.id AS C41,                   -- [40]
            '' AS C42,                      -- [41] rectification_id
            cmp.edocument AS C43,           -- [42]
            cmp.vucem_operation_num AS C44, -- [43]
            il.line_number AS C45,          -- [44]
            ld.brand AS C46,                -- [45]
            ld.model AS C47,                -- [46]
            prt.us_fraction AS C48,         -- [47]
            prt.eccn AS C49,                -- [48]
            prt.id AS C50,                  -- [49]
            fin.exchange_rate AS C51,       -- [50]
            ih.emission_date AS C52,        -- [51]
            ih.capture_user AS C53,         -- [52]
            ih.who_updated AS C54,          -- [53]
            '' AS C55,                      -- [54]
            COALESCE(log.transport_id || ' ' || log.transport_num, '') AS C56, -- [55]
            '' AS C57,                      -- [56] Pedimento18 (row[56])
            COALESCE(ld.lot, '') AS C58,    -- [57] Lote (row[57])
            '' AS C59,                      -- [58] TipoPed (row[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.items itm ON itm.invoice_id = ih.id
        LEFT JOIN a76.item_lines il ON il.item_id = itm.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 a76.parts prt ON prt.id = il.part_number
        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
        INNER JOIN a76.items itm ON itm.id = il.item_id
        WHERE itm.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)."""
    
    @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)."""
        discharge_filter = ""  # Temporarily disabled until schema migration
        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.is_updated THEN 'AC' ELSE 'NA' END AS C5,
            COALESCE(ped.pedimento_code, '') AS C6,
            COALESCE(ped.regime, '') AS C7,
            COALESCE(TO_CHAR(log.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_updated, '') AS C43,
            COALESCE(log.carrier_id, '') AS C44,
            COALESCE(log.transport_num, '') AS C45,
            COALESCE(ped.pedimento_code, '') AS C47,
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_usd END), 0) AS total_me,
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_mxn END), 0) AS total_mn
        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.items i ON i.invoice_id = ih.id
        LEFT JOIN a76.item_lines il ON il.item_id = i.id
        LEFT JOIN a24.fa_item_lines fil ON fil.id = il.id
        LEFT JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
        WHERE ih.operation_type = 'imp' 
            AND ih.invoice_type = 'REP'
            AND COALESCE(cmp.is_regime_change, false) = false
            {"AND " + where_str if where_str else ""}
            {discharge_filter}
        GROUP BY ih.id, ih.invoice_number, ped.pedimento_number, ped.pedimento_code, ped.regime,
                 log.payment_date, cmp.remesa, fin.exchange_rate, cmp.provider_id, cmp.sold_to_id,
                 cmp.customs_broker_id, ih.purchase_order, ped.customs_office, cmp.edocument,
                 cmp.vucem_operation_num, ih.emission_date, ih.capture_user, ih.who_updated,
                 log.carrier_id, log.transport_num
        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."""
        # Note: is_discharged field not yet migrated to PostgreSQL schema
        # discharge_filter = "AND il.is_discharged = true" if "descargado" in discharge_clause.lower() else ""
        discharge_filter = ""  # Temporarily disabled until schema migration
        return f"""
        SELECT 
            il.line_number,
            ih.invoice_number,
            COALESCE(ped.pedimento_number, ''),
            TO_CHAR(ih.invoice_date, 'YYYYMMDD'),
            CASE WHEN ih.is_updated THEN 'AC' ELSE 'NA' END,
            COALESCE(ped.pedimento_code, ''),
            COALESCE(ped.regime, ''),
            '',
            COALESCE(TO_CHAR(log.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(il.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(ih.purchase_order, ''),
            COALESCE(lc.fraction, ''),
            '',
            COALESCE(lc.sector, ''),
            COALESCE(lc.origin_country, ''),
            COALESCE(ped.customs_office, ''),
            ih.id,
            'P',
            '',
            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, ''),
            COALESCE(fin.exchange_rate, 0),
            COALESCE(TO_CHAR(ih.emission_date, 'YYYYMMDD'), ''),
            COALESCE(ih.capture_user, ''),
            COALESCE(ih.who_updated, ''),
            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.items itm ON itm.invoice_id = ih.id
        LEFT JOIN a76.item_lines il ON il.item_id = itm.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.parts prt ON prt.id = il.part_number
        LEFT JOIN a76.units_of_measure um ON um.id = il.unit_of_measure
        WHERE ih.operation_type = 'imp' 
            AND ih.invoice_type = 'REP'
            AND COALESCE(cmp.is_regime_change, false) = false
            {"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."""
        # Note: is_discharged field not yet migrated to PostgreSQL schema
        # discharge_filter = "AND il.is_discharged = true" if "descargado" in discharge_clause.lower() else ""
        discharge_filter = ""  # Temporarily disabled until schema migration
        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
        INNER JOIN a76.items itm ON itm.id = il.item_id
        WHERE itm.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.is_updated THEN 'AC' ELSE 'NA' END AS C6,
            COALESCE(ped.pedimento_code, '') AS C7,
            COALESCE(ped.regime, '') AS C8,
            COALESCE(TO_CHAR(log.entry_exit_date, 'YYYYMMDD'), '') AS C9,
            COALESCE(TO_CHAR(log.delivery_date, 'YYYYMMDD'), '') AS C10,
            COALESCE(TO_CHAR(log.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_updated, '') AS C51,
            COALESCE(log.transport_id || ' ' || log.transport_num, '') AS C53,
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_usd END), 0) AS total_me,
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_mxn END), 0) AS total_mn
        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.items i ON i.invoice_id = ih.id
        LEFT JOIN a76.item_lines il ON il.item_id = i.id
        LEFT JOIN a24.fa_item_lines fil ON fil.id = il.id
        LEFT JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
        WHERE {where_clause}
        GROUP BY ih.id, ih.invoice_number, ih.is_updated, ped.pedimento_number, ped.pedimento_code, ped.regime,
                 log.entry_exit_date, log.delivery_date, log.payment_date, log.payment_receipt_num,
                 cmp.provider_id, cmp.sold_to_id, cmp.customs_broker_id, ih.purchase_order, cmp.aduana,
                 ih.invoice_type, cmp.edocument, cmp.vucem_operation_num, fin.exchange_rate,
                 ih.emission_date, ih.capture_user, ih.who_updated, log.transport_id, log.transport_num,
                 ih.invoice_date
        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]
            '' AS C5,                       -- [4]
            ped.status AS C6,               -- [5]
            ped.pedimento_code AS C7,       -- [6]
            ped.regime AS C8,               -- [7]
            log.entry_exit_date AS C9,      -- [8]
            log.delivery_date AS C10,       -- [9]
            log.payment_date AS C11,        -- [10]
            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]
            prt.part_number AS C18,         -- [17]
            ld.description_spanish AS C19,  -- [18]
            ld.description_english AS C20,  -- [19]
            lq.quantity AS C21,             -- [20]
            um.code AS C22,                 -- [21]
            '' AS C23,                      -- [22]
            '' AS C24,                      -- [23]
            lq.net_weight AS C25,           -- [24]
            lq.gross_weight AS C26,         -- [25]
            ih.purchase_order AS C27,       -- [26]
            lc.fraction AS C28,             -- [27]
            '' AS C29,                      -- [28]
            '' AS C30,                      -- [29]
            '' AS C31,                      -- [30]
            '' 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]
            '' AS C39,                      -- [38] rectification_id
            cmp.edocument AS C40,           -- [39]
            cmp.vucem_operation_num AS C41, -- [40]
            il.line_number AS C42,          -- [41]
            ld.brand AS C43,                -- [42]
            ld.model AS C44,                -- [43]
            prt.us_fraction AS C45,         -- [44]
            prt.eccn 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_updated AS C51,          -- [50]
            '' AS C52,                      -- [51]
            COALESCE(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.items itm ON itm.invoice_id = ih.id
        LEFT JOIN a76.item_lines il ON il.item_id = itm.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 a76.parts prt ON prt.id = il.part_number
        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).
        """
        discharge_filter = "AND il.is_discharged = true" if "descargado" in discharge_clause.lower() else ""
        return f"""
        SELECT 
            COALESCE(SUM(lf.value_usd), 0), 
            COALESCE(SUM(lf.value_mxn), 0) 
        FROM a76.item_line_financials lf
        INNER JOIN a76.item_lines il ON il.id = lf.item_line_id
        INNER JOIN a76.items itm ON itm.id = il.item_id
        WHERE itm.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)."""
        # Note: discharge_clause temporarily disabled until is_discharged field migrated
        discharge_filter = ""  # Will be: " AND il.is_discharged = true/false" when ready
        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.is_updated THEN 'AC' ELSE 'NA' END AS C6,
            COALESCE(ped.pedimento_code, '') AS C7,
            COALESCE(ped.regime, '') AS C8,
            COALESCE(TO_CHAR(log.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_updated, '') AS C51,
            COALESCE(log.carrier_id, '') AS C52,
            COALESCE(log.transport_id || ' ' || log.transport_num, '') AS C53,
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_usd END), 0) AS total_me,
            COALESCE(SUM(CASE WHEN COALESCE(fil.is_subitem, false) THEN 0 ELSE lf.value_mxn END), 0) AS total_mn
        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.items i ON i.invoice_id = ih.id
        LEFT JOIN a76.item_lines il ON il.item_id = i.id
        LEFT JOIN a24.fa_item_lines fil ON fil.id = il.id
        LEFT JOIN a76.item_line_financials lf ON lf.item_line_id = il.id
        WHERE ih.operation_type = 'exp'
            AND ih.invoice_type = 'REP'
            {"AND " + where_str if where_str else ""}
        GROUP BY ih.id, ih.invoice_number, ped.pedimento_number, ped.pedimento_code, ped.regime,
                 log.payment_date, cmp.remesa, fin.exchange_rate, cmp.provider_id, cmp.sold_to_id,
                 cmp.customs_broker_id, ih.purchase_order, ped.customs_office, ih.document_type,
                 cmp.edocument, cmp.vucem_operation_num, ih.invoice_date, ih.capture_user,
                 ih.who_updated, log.carrier_id, log.transport_id, log.transport_num
        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.is_updated THEN 'AC' ELSE 'NA' END AS C6,
            COALESCE(ped.pedimento_code, '') AS C7,
            COALESCE(ped.regime, '') AS C8,
            '' AS C9,
            '' AS C10,
            COALESCE(TO_CHAR(log.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(ih.purchase_order, '') AS C27,
            COALESCE(lc.fraction, '') AS C28,
            COALESCE(lc.fraction_type, '') AS C29,
            COALESCE(lc.advalorem_numeric, 0) AS C30,
            COALESCE(lc.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,
            'P' AS C38,
            '' 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, '') AS C46,
            COALESCE(il.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_updated, '') AS C51,
            COALESCE(log.carrier_id, '') AS C52,
            COALESCE(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.items itm ON itm.invoice_id = ih.id
        LEFT JOIN a76.item_lines il ON il.item_id = itm.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 a76.parts prt ON prt.id = il.part_number
        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', 'REP', '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."""
        discharge_filter = "AND il.is_discharged = true" if "descargado" in discharge_clause.lower() else ""
        return f"""
        SELECT 
            COALESCE(SUM(lf.value_usd), 0), 
            COALESCE(SUM(lf.value_mxn), 0) 
        FROM a76.item_line_financials lf
        INNER JOIN a76.item_lines il ON il.id = lf.item_line_id
        INNER JOIN a76.items itm ON itm.id = il.item_id
        WHERE itm.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
        """
