-- Check for potential duplicates in assets import
WITH potential_assets AS (
    SELECT 
        t.`Sl NO` as air_compressor_serial_no,
        t.`Part NO` as air_compressor_part_no,
        STR_TO_DATE(t.`Date of Comm.`, '%d/%m/%Y') as commisioned_date,
        cc.id as client_id,
        p.id as air_compressor_id,
        t.`Customer Name`,
        t.Location,
        t.Machine,
        t.Model,
        COUNT(*) as duplicate_count
    FROM TEMPORARY_IMPORT_TABLE t
    JOIN contact_companies cc ON TRIM(SUBSTRING_INDEX(t.`Customer Name`, '(', 1)) = cc.company_name 
        AND t.Location = cc.company_address 
    LEFT JOIN products p ON t.Machine = p.name AND t.Model = p.model
    GROUP BY 
        t.`Sl NO`,
        t.`Part NO`,
        t.`Date of Comm.`,
        cc.id,
        p.id,
        t.`Customer Name`,
        t.Location,
        t.Machine,
        t.Model
    HAVING COUNT(*) > 1
)
SELECT * FROM potential_assets
ORDER BY duplicate_count DESC, `Customer Name`, Location;

-- Show total count of records vs unique records
SELECT 
    'Total Records' as metric,
    COUNT(*) as count
FROM TEMPORARY_IMPORT_TABLE t
JOIN contact_companies cc ON TRIM(SUBSTRING_INDEX(t.`Customer Name`, '(', 1)) = cc.company_name 
    AND t.Location = cc.company_address 
LEFT JOIN products p ON t.Machine = p.name AND t.Model = p.model
UNION ALL
SELECT 
    'Unique Records' as metric,
    COUNT(DISTINCT CONCAT(t.`Sl NO`, t.`Part NO`, t.`Date of Comm.`, cc.id, p.id)) as count
FROM TEMPORARY_IMPORT_TABLE t
JOIN contact_companies cc ON TRIM(SUBSTRING_INDEX(t.`Customer Name`, '(', 1)) = cc.company_name 
    AND t.Location = cc.company_address 
LEFT JOIN products p ON t.Machine = p.name AND t.Model = p.model;
