Optimizing PostgreSQL Performance with Historical Data Archiving: Is Creating a History Table a Good Solution?

const { app } = require('@azure/functions');
const { Client } = require('pg');
const XLSX = require('xlsx');

function normalizeDate(dateStr) {
    if (!dateStr || dateStr === 'keine Lieferung') {
        return null; // Return null for undefined, empty, or specific non-date text
    }

    // Split the date string by newlines or any non-alphanumeric character (like space, tab, etc.)
    const dates = dateStr.split(/[ns,;]+/);

    // Take the first date for processing
    let dateToNormalize = dates[0];

    let parts;
    if (dateToNormalize.includes('/')) {
        // Handle MM/DD/YY or MM/DD/YYYY format
        parts = dateToNormalize.split('/');
        if (parts.length === 3) {
            if (parts[2].length === 2) {
                // Assuming the year is given as two digits
                return `20${parts[2]}-${parts[0].padStart(2, '0')}-${parts[1].padStart(2, '0')}`; // Convert to YYYY-MM-DD
            } else {
                return `${parts[2]}-${parts[0].padStart(2, '0')}-${parts[1].padStart(2, '0')}`; // Convert to YYYY-MM-DD
            }
        }
    } else if (dateToNormalize.includes('.')) {
        // Handle DD.MM.YYYY format
        parts = dateToNormalize.split('.');
        if (parts.length === 3) {
            return `${parts[2]}-${parts[1].padStart(2, '0')}-${parts[0].padStart(2, '0')}`; // Convert to YYYY-MM-DD
        }
    }

    return null; // Return null if the format is unexpected
}

app.storageBlob('bdbos', {
    path: 'excelfiles/{name}',
    connection: 'MyStorageConnectionString',
    handler: async (blob, context) => {
        const fileName = `${context.triggerMetadata.name}`      
        const connection = new Client({
        *******
        }); 

        try {
            await connection.connect();
            await connection.query('BEGIN');

            const workbook = XLSX.read(blob, { type: 'buffer' });
            let sheetIndex = 0; // Default to the first sheet
            let rangeOption = '1'; // Default range to skip header row

            if (fileName.startsWith('BDBOS')) {
                sheetIndex = 1; // For 'BDBOS', the data is in the second sheet
                rangeOption = ''; // Do not skip header for 'BDBOS'
            }
            if(fileName.startsWith('Materialbestellung')) {
                sheetIndex = 0;
                rangeOption = ''; 
            }

            const sheetName = workbook.SheetNames[sheetIndex];
            const sheet = workbook.Sheets[sheetName];
            let headerDate;

            // Adjust range if header should be skipped
            const range = XLSX.utils.decode_range(sheet['!ref']);
            if (rangeOption === '1') {
                range.s.r = 1; // Skip the first row (header) if not 'BDBOS'
            }            

            // Process for 'Stock' - read the date from the header
            if (fileName.startsWith('Stock')) {
                const rawHeaderValue = sheet['A1'].v; // Reads the A1 cell value
                const dateString = rawHeaderValue.split('|')[0].trim(); // Extracts the date string '28.04.2024'
                // Format the date to 'YYYY-MM-DD'
                const dateParts = dateString.split('.');
                headerDate = `${dateParts[2]}-${dateParts[1]}-${dateParts[0]}`; // Reformat to '2024-04-28'
            }
            const adjustedRange = XLSX.utils.encode_range(range);
            const data = XLSX.utils.sheet_to_json(sheet, { raw: false, range: adjustedRange });
            const now = new Date();
            const formattedDate = `${now.getFullYear()}.${String(now.getMonth() + 1).padStart(2, '0')}.${String(now.getDate()).padStart(2, '0')}`; // Formats to 'YYYY.MM.DD'
            
            // condition for choosing a function according to the file being uploaded
            if (fileName.startsWith('Stock')) {
                await processStock(data, connection, headerDate);
                context.log(`${fileName} is processing`)
            } else if (fileName.startsWith('BDBOS')) {
                await processBdbos(data, connection, formattedDate, context);
                context.log(`${fileName} is processing`)
            } else if (fileName.startsWith('Goods')) {
                await processGoods(data, connection);
                context.log(`${fileName} is processing`)
            } else if(fileName.startsWith('Materialbestellung')) {
                await processMaterialOrders(data, connection, context);
                context.log(`${fileName} is processing`)

            }else {
                throw new Error(`Unsupported file type: ${fileName}`);
            }

            await connection.query('COMMIT');
            context.log(`Data processed and committed for file: ${fileName}`);
        } catch (error) {
            await connection.query('ROLLBACK');
            context.log(`Error in processing data from ${fileName}: ${error}`);
        } finally {
            connection.end();
        }
    }
});
//function for BDBOS file
async function processBdbos(data, connection, formattedDate, context) {

    const deleteResult = await connection.query('DELETE FROM flm_master.bdbos_hws'); //deleting existing data/rows
    context.log(`Deleted ${deleteResult.rowCount} rows from flm_master.bdbos_hws`);
    for (const row of data) {
        await connection.query(`
        INSERT INTO flm_master.bdbos_hws (
            rma_no,
            customer_reference,
            creation_datetime,
            sold_to_party,
            sold_to_party_description,
            cancellation_reason,
            service_level_description,
            service_level_lead_time,
            service_level_lead_time_unit,
            material_version_code_ordered,
            material_code_ordered,
            material_code_description_ordered,
            material_version_code_shipped,
            material_code_shipped,
            serial_number_shipped,
            requested_delivery_datetime,
            goods_issue_datetime,
            ship_to_party_name1,
            ship_to_party_name2,
            street_address,
            postal_code,
            city,
            delivery_swh,
            km_driven,
            pod_datetime,
            delivery_status,
            specials,
            receive_date_return,
            tracking_number_return,
            delivery_status_return,
            valuation_return,
            material_version_code_return,
            material_code_return,
            serial_number_return,
            repair_vendor_rma,
            import_date
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36)`,
        [
            row['RMA No.'], 
            row['Customer Reference'], 
            row['Creation Date and Time RMA No.'], 
            row['Sold-to Party'], 
            row['Sold-to Party Description'], 
            row['Cancellation Reason'], 
            row['Service Level Description'], 
            row['Service Level Lead Time'],
            row['Service Level Lead Time Unit'], 
            row['Material Version Code ordered'], 
            row['Material Code ordered'], 
            row['Material Code Description ordered'], 
            row['Material Version Code shipped'], 
            row['Material Code shipped'], 
            row['Serial Number shipped'], 
            row['Requested Delivery Date and Time'], 
            row['Goods Issue Date and Time'], 
            row['Ship-to Party Name 1'], 
            row['Ship-to Party Name 2'], 
            row['Street address'], 
            row['Postal Code'], 
            row['City'], 
            row['Delivery SWH'], 
            row['km driven'],
            row['POD Date and Time'], 
            row['Delivery Status'], 
            row['Specials (e.g. traffic jam)'], 
            row['Receive Date Return'], 
            row['Tracking Number Return'], 
            row['Delivery Status Return'], 
            row['Valuation Return'], 
            row['Material Version Code Return'], 
            row['Material Code Return'], 
            row['Serial Number Return'], 
            row['Repair Vendor RMA'],
            formattedDate
        ]
    );
    }
}
//function for stock file
async function processStock(data, connection, headerDate) {

    for (const row of data) {
        await connection.query(`INSERT INTO flm_master.stock (
            project_prefix,
            material_code,
            producer_item_code,
            description,
            serial_number,
            total_stock,
            unit,
            warehouse,
            report_date
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
        [
            row['Project Prefix'],
            row['Material Code'],
            row['Producer Item Code'],
            row['Description'],
            row['Serial Numbers'],
            row['Total Stock'], 
            row['Unit'],
            row['Warehouse'],
            headerDate
        ]);
    }
}

//function for goods_receipt
async function processGoods(data, connection) {

    row['Material Code'] = parseInt(row['Material Code']);
        const dateParts = row['Posting date'].split('.'); // Assuming date is in 'DD.MM.YYYY' format
        row['Posting date'] = `${dateParts[2]}-${dateParts[1]}-${dateParts[0]}`; // Convert to 'YYYY-MM-DD'

    for (const row of data) {
        await connection.query(`INSERT INTO flm_master.goods_receipt (
            posting_date,
            delivery_note,
            order_reference,
            carrier,
            tracking_number,
            prefix,
            material_code,
            producer_item_code,
            material_description,
            serial,
            qty,
            unit
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)`,
        [
            row['Posting date'],
            row['Delivery note'],
            row['Order Reference'],
            row['Carrier'],
            row['Tracking Number'],
            row['Prefix'], 
            row['Material Code'],
            row['Producer Item Code'],
            row['Material Description'],
            row['Serial'],
            row['Qty'],
            row['Unit']
        ]);
    }
}

async function processMaterialOrders(data, connection, context) {
    context.log('Processing Material Orders');
    const bestellungIdKey = 'Bestellung-IDrn(Ingress)';
    const bpPartKey = 'BP Part / rnSRM  Code / Order No.';
    const csoCartKey = 'CSO / Shopping Cart No.';
    for (const row of data) {
        context.log(`Processing row: ${JSON.stringify(row)}`);

        const bestellungsdatum = row['Bestellungsdatum'] ? normalizeDate(row['Bestellungsdatum']) : null;
        await connection.query(`
            INSERT INTO flm_master.material_orders (
                bestellung_id,
                nokia_material_code,
                hersteller,
                producer_material_code,
                materialbeschreibung,
                quantity,
                anforderung,
                bestellungsdatum,
                bp_srm_amazon,
                bp_part_srm_code_order_no,
                cso_shopping_cart_no,
                po_no,
                lieferung_bestaetigung,
                besonderheiten
            ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)`,
            [
                row[bestellungIdKey],
                row['Nokia Material Code'],
                row['Hersteller'],
                row['Producer Material Code'],
                row['Materialbeschreibung'],
                row['Quantity'],
                row['Anforderung'],
                bestellungsdatum,
                row['BP / SRM / Amazon / …'],
                row[bpPartKey],
                row[csoCartKey],
                row['PO No.'],
                row['Lieferung Bestätigung'],
                row['Besonderheiten']
            ]
        );
        context.log(`Ìnserted data for Bestellung-ID: ${row[bestellungIdKey]}`);
    }
}