-- 1 Truncate Table
TRUNCATE TABLE client_report;

-- 1a Truncate client/site report table
TRUNCATE TABLE client_site_report;

-- 1b Insert unique client/site pairs from jobs with nightly counts
INSERT INTO client_site_report (client_id, site_id, active_assets, open_estimates, open_installs, open_repairs, open_services, ppm_percent, rag_red, rag_amber, rag_green, last_visit_date)
SELECT
    csr.client_id,
    csr.site_id,
    COALESCE(a.active_assets, 0) AS active_assets,
    COALESCE(e.open_estimates, 0) AS open_estimates,
    COALESCE(i.open_installs, 0) AS open_installs,
    COALESCE(r.open_repairs, 0) AS open_repairs,
    COALESCE(sv.open_services, 0) AS open_services,
    CASE
        WHEN COALESCE(a.active_assets, 0) > 0 THEN ROUND((COALESCE(a.active_ppm_assets, 0) / a.active_assets) * 100, 1)
        ELSE 0
    END AS ppm_percent,
    COALESCE(a.rag_red, 0) AS rag_red,
    COALESCE(a.rag_amber, 0) AS rag_amber,
    COALESCE(a.rag_green, 0) AS rag_green,
    NULLIF(lv.last_visit_date, '1000-01-01') AS last_visit_date
FROM (
    SELECT estimate_client AS client_id, estimate_site AS site_id
    FROM estimates
    WHERE estimate_client IS NOT NULL
      AND estimate_site IS NOT NULL
      AND estimate_job_ref IS NOT NULL
    UNION
    SELECT repair_client AS client_id, repair_site AS site_id
    FROM repairs
    WHERE repair_client IS NOT NULL
      AND repair_site IS NOT NULL
    UNION
    SELECT service_client AS client_id, service_site AS site_id
    FROM services
    WHERE service_client IS NOT NULL
      AND service_site IS NOT NULL
    UNION
    SELECT rem_client AS client_id, rem_site AS site_id
    FROM remedial
    WHERE rem_client IS NOT NULL
      AND rem_site IS NOT NULL
) AS csr
LEFT JOIN (
    SELECT
        asset_site,
        COUNT(*) AS active_assets,
        SUM(CASE WHEN mo.mo_status = 'Accepted' THEN 1 ELSE 0 END) AS active_ppm_assets,
        SUM(CASE WHEN asset_working = 2 OR asset_risk = 3 THEN 1 ELSE 0 END) AS rag_red,
        SUM(CASE WHEN asset_risk = 2 AND (asset_working IS NULL OR asset_working <> 2) THEN 1 ELSE 0 END) AS rag_amber,
        SUM(CASE WHEN (asset_risk IS NULL OR asset_risk NOT IN (2,3))
                   AND (asset_working IS NULL OR asset_working <> 2) THEN 1 ELSE 0 END) AS rag_green
    FROM assets
    LEFT JOIN maintenance_orders mo ON mo.mo_id = assets.asset_mo
    WHERE asset_status = 'Active'
    GROUP BY asset_site
) AS a ON a.asset_site = csr.site_id
LEFT JOIN (
    SELECT estimate_site, COUNT(*) AS open_estimates
    FROM estimates
    WHERE estimate_status NOT IN ('Cancelled','Closed','Declined','Lost','Won')
    GROUP BY estimate_site
) AS e ON e.estimate_site = csr.site_id
LEFT JOIN (
    SELECT estimate_site, COUNT(*) AS open_installs
    FROM estimates
    WHERE estimate_job_ref IS NOT NULL
      AND estimate_job_status NOT IN ('Cancelled','Closed','Complete','Completed')
    GROUP BY estimate_site
) AS i ON i.estimate_site = csr.site_id
LEFT JOIN (
    SELECT repair_site, COUNT(*) AS open_repairs
    FROM repairs
    WHERE repair_status NOT IN ('Cancelled','Closed','Complete','Completed','Invoiced')
    GROUP BY repair_site
) AS r ON r.repair_site = csr.site_id
LEFT JOIN (
    SELECT service_site, COUNT(*) AS open_services
    FROM services
    WHERE service_status NOT IN ('Cancelled','Closed','Complete','Completed','Invoiced')
    GROUP BY service_site
) AS sv ON sv.service_site = csr.site_id
LEFT JOIN (
    SELECT
        s.site_id,
        GREATEST(
            COALESCE(MAX(CASE WHEN r.repair_req_date <= CURDATE() THEN r.repair_req_date END), '1000-01-01'),
            COALESCE(MAX(CASE WHEN sv.service_req_date <= CURDATE() THEN sv.service_req_date END), '1000-01-01'),
            COALESCE(MAX(CASE WHEN js.status_scheduled <= CURDATE() THEN js.status_scheduled END), '1000-01-01')
        ) AS last_visit_date
    FROM sites s
    LEFT JOIN repairs r ON r.repair_site = s.site_id
    LEFT JOIN services sv ON sv.service_site = s.site_id
    LEFT JOIN estimates e ON e.estimate_site = s.site_id AND e.estimate_job_ref IS NOT NULL
    LEFT JOIN job_status js ON js.status_job = e.estimate_job_ref
    GROUP BY s.site_id
) AS lv ON lv.site_id = csr.site_id;

-- 2 Insert unique client_id
INSERT INTO client_report (cr_client_id)
SELECT e.estimate_client
FROM estimates e
JOIN companies c ON c.company_id = e.estimate_client
WHERE e.estimate_client IS NOT NULL
  AND c.company_archive IS NULL
UNION
SELECT r.repair_client
FROM repairs r
JOIN companies c ON c.company_id = r.repair_client
WHERE r.repair_client IS NOT NULL
  AND c.company_archive IS NULL
UNION
SELECT s.service_client
FROM services s
JOIN companies c ON c.company_id = s.service_client
WHERE s.service_client IS NOT NULL
  AND c.company_archive IS NULL
UNION
SELECT rm.rem_client
FROM remedial rm
JOIN companies c ON c.company_id = rm.rem_client
WHERE rm.rem_client IS NOT NULL
  AND c.company_archive IS NULL;

-- Financial year boundaries (Apr 1 to Apr 1)
SET @today := CURDATE();
SET @fy_start := IF(MONTH(@today) >= 4,
    DATE_FORMAT(@today, '%Y-04-01'),
    DATE_FORMAT(DATE_SUB(@today, INTERVAL 1 YEAR), '%Y-04-01')
);
SET @fy_end := DATE_ADD(@fy_start, INTERVAL 1 YEAR);
SET @fy_prev_start := DATE_SUB(@fy_start, INTERVAL 1 YEAR);
SET @fy_prev_end := @fy_start;

-- Update client name and sector
UPDATE
    client_report cr
JOIN
    companies c ON cr.cr_client_id = c.company_id
SET
    cr.cr_client_name = c.company_name,
    cr.cr_sector = c.company_category;

-- Add client ID to invoice table
UPDATE invoices
JOIN estimates ON invoices.invoice_job = estimates.estimate_job_ref
SET invoices.invoice_client = estimates.estimate_client
WHERE invoice_client IS NULL;

UPDATE invoices
JOIN repairs ON invoices.invoice_repair = repairs.repair_id
SET invoices.invoice_client = repairs.repair_client
WHERE invoice_client IS NULL;

UPDATE invoices
JOIN services ON invoices.invoice_service = services.service_id
SET invoices.invoice_client = services.service_client
WHERE invoice_client IS NULL;

UPDATE invoices
JOIN remedial ON invoices.invoice_remedial = remedial.rem_id
SET invoices.invoice_client = remedial.rem_client
WHERE invoice_client IS NULL;

-- Update table with number of estimates Won/Lost
UPDATE
    client_report AS cr
LEFT JOIN
    (
        SELECT
            estimate_client,
            SUM(estimate_status = 'Won') AS total_won,
            SUM(estimate_status IN ('Lost', 'Declined')) AS total_lost
        FROM
            estimates
        WHERE
            estimate_client IS NOT NULL
        GROUP BY
            estimate_client
    ) AS estimate_counts ON cr.cr_client_id = estimate_counts.estimate_client
SET
    cr.cr_estimates_won = COALESCE(estimate_counts.total_won, 0),
    cr.cr_estimates_lost = COALESCE(estimate_counts.total_lost, 0);

-- Work out conversion ratio of won estimates

UPDATE client_report SET cr_estimate_conversion = CASE WHEN (COALESCE(cr_estimates_won, 0) + COALESCE(cr_estimates_lost, 0)) > 0 THEN (COALESCE(cr_estimates_won, 0) / (COALESCE(cr_estimates_won, 0) + COALESCE(cr_estimates_lost, 0))) * 100 ELSE 0 END;

-- Set turnover (overall/current/previous) in one scan
UPDATE
    client_report cr
LEFT JOIN (
    SELECT
        invoice_client,
        SUM(CASE
            WHEN invoice_status IN ('Approved', 'Created', 'Paid') THEN invoice_value
            ELSE 0
        END) AS total_overall,
        SUM(CASE
            WHEN invoice_status IN ('Approved', 'Created', 'Paid')
                AND invoice_date >= @fy_start
                AND invoice_date < @fy_end
            THEN invoice_value
            ELSE 0
        END) AS total_current,
        SUM(CASE
            WHEN invoice_status IN ('Approved', 'Created', 'Paid')
                AND invoice_date >= @fy_prev_start
                AND invoice_date < @fy_prev_end
            THEN invoice_value
            ELSE 0
        END) AS total_prev
    FROM
        invoices
    GROUP BY
        invoice_client
) AS invoice_totals ON cr.cr_client_id = invoice_totals.invoice_client
SET
    cr.cr_turnover_overall = COALESCE(invoice_totals.total_overall, 0),
    cr.cr_turnover_current = COALESCE(invoice_totals.total_current, 0),
    cr.cr_turnover_previous = COALESCE(invoice_totals.total_prev, 0);

-- Install Volume
UPDATE client_report cr
LEFT JOIN (
    SELECT
        estimate_client,
        COUNT(*) AS total_installs
    FROM
        estimates
    WHERE
        estimate_job_ref IS NOT NULL
        AND estimate_job_status <> 'Cancelled'
	AND estimate_category IN ('Supply/Fit','Supply Only')
    GROUP BY
        estimate_client
) AS install_counts ON cr.cr_client_id = install_counts.estimate_client
SET
    cr.cr_install_volume = COALESCE(install_counts.total_installs, 0);



-- Install Value
UPDATE client_report cr
LEFT JOIN (
    SELECT 
        e.estimate_client, 
        SUM(i.invoice_value) AS total_value
    FROM 
        invoices i
    JOIN estimates e ON i.invoice_job = e.estimate_job_ref
    WHERE
        i.invoice_status IN ('Approved', 'Created', 'Paid')
    GROUP BY 
        e.estimate_client
) AS e_summary ON cr.cr_client_id = e_summary.estimate_client
SET 
    cr.cr_install_value = IFNULL(e_summary.total_value, 0);

-- Repair Volume
UPDATE client_report cr
LEFT JOIN (
    SELECT 
        repair_client, 
        COUNT(*) AS total_repairs
    FROM 
        repairs
    WHERE 
        repair_status = 'Completed'
    GROUP BY 
        repair_client
) AS r_summary ON cr.cr_client_id = r_summary.repair_client
SET 
    cr.cr_repair_volume = IFNULL(r_summary.total_repairs, 0);

-- Repair Value
UPDATE client_report cr
LEFT JOIN (
    SELECT 
        r.repair_client, 
        SUM(i.invoice_value) AS total_value
    FROM 
        invoices i
    JOIN repairs r ON i.invoice_repair = r.repair_id
    WHERE
        r.repair_status != 'Cancelled'
        AND i.invoice_status IN ('Approved', 'Created', 'Paid')
    GROUP BY 
        r.repair_client
) AS e_summary ON cr.cr_client_id = e_summary.repair_client
SET 
    cr.cr_repair_value = IFNULL(e_summary.total_value, 0);

-- Service Volume
UPDATE client_report cr
LEFT JOIN (
    SELECT 
        service_client, 
        COUNT(*) AS total_services
    FROM 
        services
    WHERE 
        service_status = 'Completed'
    GROUP BY 
        service_client
) AS r_summary ON cr.cr_client_id = r_summary.service_client
SET 
    cr.cr_service_volume = IFNULL(r_summary.total_services, 0);

-- Service Value
UPDATE client_report cr
LEFT JOIN (
    SELECT 
        s.service_client, 
        SUM(i.invoice_value) AS total_value
    FROM 
        invoices i
    JOIN services s ON i.invoice_service = s.service_id
    WHERE
        s.service_status != 'Cancelled'
        AND i.invoice_status IN ('Approved', 'Created', 'Paid')
    GROUP BY 
        s.service_client
) AS e_summary ON cr.cr_client_id = e_summary.service_client
SET 
    cr.cr_service_value = IFNULL(e_summary.total_value, 0);

-- Set Overall Ranking
UPDATE
    client_report cr
JOIN (
    SELECT
        cr_id,
        @current_rank := IF(
            @prev_turnover = cr_turnover_overall, 
            @current_rank, 
            @row_count
        ) AS overall_rank,
        @row_count := @row_count + 1,
        @prev_turnover := cr_turnover_overall
    FROM
        client_report,
        (SELECT @current_rank := 0, @prev_turnover := NULL, @row_count := 1) AS vars
    ORDER BY
        cr_turnover_overall DESC
) AS ClientRanks ON cr.cr_id = ClientRanks.cr_id
SET
    cr.cr_ranking_overall = ClientRanks.overall_rank;



-- Set Current Year Ranking
UPDATE
    client_report cr
JOIN (
    SELECT
        cr_id,
        @current_rank := IF(
            @prev_turnover = cr_turnover_current,
            @current_rank,
            @row_count
        ) AS current_rank,
        @row_count := @row_count + 1,
        @prev_turnover := cr_turnover_current
    FROM
        client_report,
        (SELECT @current_rank := 0, @prev_turnover := NULL, @row_count := 1) AS vars
    ORDER BY
        cr_turnover_current DESC
) AS CurrentRanks ON cr.cr_id = CurrentRanks.cr_id
SET
    cr.cr_ranking_current = CurrentRanks.current_rank;


-- Set Previous Year Ranking
-- We need three variables to simulate RANK()
SET @current_rank = 0;
SET @row_num = 0;
SET @previous_turnover = NULL;

UPDATE
    client_report cr
JOIN (
    SELECT
        cr_id,
        @row_num := @row_num + 1,
        @current_rank := IF(
            -- If the turnover is the same as the previous row...
            @previous_turnover = cr_turnover_previous,
            -- ...keep the same rank.
            @current_rank,
            -- Otherwise, the new rank is the current row number.
            @row_num
        ) AS previous_rank,
        -- Store this row's turnover to compare against the next row
        @previous_turnover := cr_turnover_previous
    FROM
        client_report,
        -- Initialize variables (this is just a trick to make sure they are set)
        (SELECT @current_rank := 0, @row_num := 0, @previous_turnover := NULL) AS vars
    ORDER BY
        -- This ORDER BY is crucial!
        cr_turnover_previous DESC
) AS PreviousRanks ON cr.cr_id = PreviousRanks.cr_id
SET
    cr.cr_ranking_previous = PreviousRanks.previous_rank;


-- Set Sector Ranking
UPDATE
    client_report cr
JOIN
    (
        -- The extra wrapper to bypass the error
        SELECT * FROM (
            SELECT
                cr_a.cr_id,  -- <-- REPLACE THIS
                (
                    SELECT
                        1 + COUNT(*)
                    FROM
                        client_report cr_b
                    WHERE
                        cr_b.cr_sector = cr_a.cr_sector
                        AND cr_b.cr_turnover_overall > cr_a.cr_turnover_overall
                ) AS new_rank
            FROM
                client_report cr_a
        ) AS temp_ranks
    ) AS ranks ON cr.cr_id = ranks.cr_id -- <-- AND REPLACE THIS
SET
    cr.cr_ranking_sector = ranks.new_rank;


-- Active Assets (worked-on assets, matches dashboard KPI)
UPDATE
    client_report cr
LEFT JOIN (
    SELECT
        job_assets.client_id,
        COUNT(DISTINCT job_assets.asset_id) AS asset_count
    FROM (
        SELECT
            repairs.repair_client AS client_id,
            repair_products.rp_asset AS asset_id
        FROM
            repair_products
        JOIN repairs ON repairs.repair_id = repair_products.rp_repair_id
        WHERE
            repairs.repair_client IS NOT NULL
            AND repair_products.rp_asset IS NOT NULL
        UNION
        SELECT
            services.service_client AS client_id,
            service_products.sp_asset AS asset_id
        FROM
            service_products
        JOIN services ON services.service_id = service_products.sp_service_id
        WHERE
            services.service_client IS NOT NULL
            AND service_products.sp_asset IS NOT NULL
        UNION
        SELECT
            remedial.rem_client AS client_id,
            remedial_products.rmp_asset AS asset_id
        FROM
            remedial_products
        JOIN remedial ON remedial.rem_id = remedial_products.rmp_remedial_id
        WHERE
            remedial.rem_client IS NOT NULL
            AND remedial_products.rmp_asset IS NOT NULL
        UNION
        SELECT
            estimates.estimate_client AS client_id,
            assets.asset_id AS asset_id
        FROM
            assets
        JOIN estimate_products ON assets.asset_est_product = estimate_products.est_prod_id
        JOIN estimates ON estimates.estimate_ref = estimate_products.est_prod_estimate
            AND estimates.estimate_revision = estimate_products.est_prod_est_revision
        WHERE
            estimates.estimate_client IS NOT NULL
            AND estimates.estimate_job_ref IS NOT NULL
    ) AS job_assets
    JOIN assets ON assets.asset_id = job_assets.asset_id
    WHERE
        assets.asset_status = 'Active'
    GROUP BY
        job_assets.client_id
) AS summary ON cr.cr_client_id = summary.client_id
SET
    cr.cr_installed_assets = IFNULL(summary.asset_count, 0);


-- Active MC
UPDATE
    client_report cr
LEFT JOIN (
    SELECT
        mo.mo_client,
        COUNT(DISTINCT mo.mo_id) AS active_mc_count
    FROM
        maintenance_orders mo
    WHERE
        mo.mo_status = 'Accepted'
        AND EXISTS (
            SELECT 1
            FROM mo_history mh
            JOIN services s ON s.service_id = mh.moh_service
            WHERE mh.moh_mo = mo.mo_id
              AND COALESCE(s.service_status, '') <> 'Cancelled'
              AND s.service_req_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
        )
    GROUP BY
        mo.mo_client
) AS summary ON cr.cr_client_id = summary.mo_client
SET
    cr.cr_active_mc = IFNULL(summary.active_mc_count, 0);

-- Relapsed MC (Accepted MOs with no active service in last 3 months/future)
UPDATE
    client_report cr
LEFT JOIN (
    SELECT
        mo.mo_client,
        COUNT(DISTINCT mo.mo_id) AS relapsed_mc_count
    FROM
        maintenance_orders mo
    WHERE
        mo.mo_status = 'Accepted'
        AND NOT EXISTS (
            SELECT 1
            FROM mo_history mh
            JOIN services s ON s.service_id = mh.moh_service
            WHERE mh.moh_mo = mo.mo_id
              AND COALESCE(s.service_status, '') <> 'Cancelled'
              AND s.service_req_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
        )
    GROUP BY
        mo.mo_client
) AS summary ON cr.cr_client_id = summary.mo_client
SET
    cr.cr_mc_relapsed = IFNULL(summary.relapsed_mc_count, 0);

-- Active MC - Previous
-- cr_active_mc_previous is now “previous FY only” (between @fy_prev_start and @fy_start) instead of “everything before current FY.”
UPDATE
    client_report cr
LEFT JOIN (
    SELECT
        mo_client,
        COUNT(mo_id) AS active_mc_count
    FROM
        maintenance_orders
    WHERE
        mo_status = 'Accepted'
        AND mo_date >= @fy_prev_start
        AND mo_date < @fy_start
    GROUP BY
        mo_client
) AS summary ON cr.cr_client_id = summary.mo_client
SET
    cr.cr_active_mc_previous = IFNULL(summary.active_mc_count, 0);

-- Active MC - Older

UPDATE
    client_report cr
LEFT JOIN (
    SELECT
        mo_client,
        COUNT(mo_id) AS active_mc_count
    FROM
        maintenance_orders
    WHERE
        mo_status = 'Accepted'
        AND mo_date < @fy_prev_start
    GROUP BY
        mo_client
) AS summary ON cr.cr_client_id = summary.mo_client
SET
    cr.cr_active_mc_older = IFNULL(summary.active_mc_count, 0);


-- Service Visits - Current
UPDATE
    client_report cr
LEFT JOIN (
    SELECT
        s.service_client,
        COUNT(DISTINCT s.service_id) AS current_visits
    FROM
        services s
    WHERE
        s.service_req_date >= @fy_start
        AND s.service_req_date < @fy_end
        AND s.service_status = 'Completed'
        AND EXISTS (
            SELECT 1
            FROM mo_history mh
            WHERE mh.moh_service = s.service_id
        )
    GROUP BY
        s.service_client
) AS calculated_visits
ON
    cr.cr_client_id = calculated_visits.service_client
SET
    cr.cr_service_visits_current = COALESCE(calculated_visits.current_visits, 0);

-- Service Visits - Previous
UPDATE
    client_report cr
LEFT JOIN (
    SELECT
        s.service_client,
        COUNT(DISTINCT s.service_id) AS current_visits
    FROM
        services s
    WHERE
        s.service_req_date >= @fy_prev_start
        AND s.service_req_date < @fy_prev_end
        AND s.service_status = 'Completed'
        AND EXISTS (
            SELECT 1
            FROM mo_history mh
            WHERE mh.moh_service = s.service_id
        )
    GROUP BY
        s.service_client
) AS calculated_visits
ON
    cr.cr_client_id = calculated_visits.service_client
SET
    cr.cr_service_visits_previous = COALESCE(calculated_visits.current_visits, 0);

-- Service Visits - Older
UPDATE
    client_report cr
LEFT JOIN (
    SELECT
        s.service_client,
        COUNT(DISTINCT s.service_id) AS current_visits
    FROM
        services s
    WHERE
        s.service_req_date < @fy_prev_start
        AND s.service_status = 'Completed'
        AND EXISTS (
            SELECT 1
            FROM mo_history mh
            WHERE mh.moh_service = s.service_id
        )
    GROUP BY
        s.service_client
) AS calculated_visits
ON
    cr.cr_client_id = calculated_visits.service_client
SET
    cr.cr_service_visits_older = COALESCE(calculated_visits.current_visits, 0);


-- Client Summary
-- Installs - Quoted estimate volume current year
UPDATE client_report
LEFT JOIN (
  -- 1. Pre-calculate counts for ALL clients in one pass
  SELECT
    estimate_client,
    COUNT(*) AS client_quote_count
  FROM
    estimates
  WHERE
    estimate_status != 'Cancelled'
    AND estimate_category IN ('Supply Only', 'Supply/Fit')
    AND estimate_date BETWEEN '2025-04-01' AND '2026-03-31'
  GROUP BY
    estimate_client
) AS counts ON client_report.cr_client_id = counts.estimate_client
-- 2. Set the value
SET
  cr_install_quoted_volume_current = COALESCE(counts.client_quote_count, 0);


-- Installs - Quoted estimate volume previous year

UPDATE client_report
LEFT JOIN (
  -- 1. Pre-calculate counts for ALL clients in one pass
  SELECT
    estimate_client,
    COUNT(*) AS client_quote_count
  FROM
    estimates
  WHERE
    estimate_status != 'Cancelled'
    AND estimate_category IN ('Supply Only', 'Supply/Fit')
    AND estimate_date BETWEEN '2024-04-01' AND '2025-03-31'
  GROUP BY
    estimate_client
) AS counts ON client_report.cr_client_id = counts.estimate_client
-- 2. Set the value
SET
  cr_install_quoted_volume_previous = COALESCE(counts.client_quote_count, 0);

-- Installs - Quoted estimate volume older

UPDATE client_report
LEFT JOIN (
  -- 1. Pre-calculate counts for ALL clients in one pass
  SELECT
    estimate_client,
    COUNT(*) AS client_quote_count
  FROM
    estimates
  WHERE
    estimate_status != 'Cancelled'
    AND estimate_category IN ('Supply Only', 'Supply/Fit')
    AND estimate_date < '2024-04-01'
  GROUP BY
    estimate_client
) AS counts ON client_report.cr_client_id = counts.estimate_client
-- 2. Set the value
SET
  cr_install_quoted_volume_older = COALESCE(counts.client_quote_count, 0);
-- Installs - Quoted estimate value current year
UPDATE client_report cr
LEFT JOIN (
  SELECT
    estimate_client,
    SUM(estimate_value) AS total_value
  FROM
    estimates
  WHERE
    estimate_status != 'Cancelled'
    AND estimate_category IN ('Supply Only', 'Supply/Fit')
    AND estimate_date BETWEEN '2025-04-01' AND '2026-03-31'
  GROUP BY
    estimate_client
) AS ct ON cr.cr_client_id = ct.estimate_client
SET
  cr.cr_install_quoted_value_current = COALESCE(ct.total_value, 0);

-- Installs - Quoted estimate value previous year

UPDATE client_report cr
LEFT JOIN (
  SELECT
    estimate_client,
    SUM(estimate_value) AS total_value
  FROM
    estimates
  WHERE
    estimate_status != 'Cancelled'
    AND estimate_category IN ('Supply Only', 'Supply/Fit')
    AND estimate_date BETWEEN '2024-04-01' AND '2025-03-31'
  GROUP BY
    estimate_client
) AS ct ON cr.cr_client_id = ct.estimate_client
SET
  cr.cr_install_quoted_value_previous = COALESCE(ct.total_value, 0);

-- Installs - Quoted estimate value older

UPDATE client_report cr
LEFT JOIN (
  SELECT
    estimate_client,
    SUM(estimate_value) AS total_value
  FROM
    estimates
  WHERE
    estimate_status != 'Cancelled'
    AND estimate_category IN ('Supply Only', 'Supply/Fit')
    AND estimate_date < '2024-04-01'
  GROUP BY
    estimate_client
) AS ct ON cr.cr_client_id = ct.estimate_client
SET
  cr.cr_install_quoted_value_older = COALESCE(ct.total_value, 0);


-- Installs - Won estimate volume current year
UPDATE client_report
SET 
  cr_install_won_volume_current = (
    SELECT 
      COUNT(*) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status = 'Won'
      AND estimates.estimate_category IN ('Supply Only', 'Supply/Fit') 
      AND estimates.estimate_date BETWEEN '2025-04-01' AND '2026-03-31'
  );


-- Installs - Won estimate volume previous year
UPDATE client_report
SET 
  cr_install_won_volume_previous = (
    SELECT 
      COUNT(*) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status = 'Won' 
      AND estimates.estimate_category IN ('Supply Only', 'Supply/Fit') 
      AND estimates.estimate_date BETWEEN '2024-04-01' AND '2025-03-31'
  );

-- Installs - Won estimate volume older
UPDATE client_report
SET 
  cr_install_won_volume_older = (
    SELECT 
      COUNT(*) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status = 'Won'  
      AND estimates.estimate_category IN ('Supply Only', 'Supply/Fit') 
      AND estimates.estimate_date < '2024-04-01'  );


-- Installs - Won estimate value current year
UPDATE client_report
SET 
  cr_install_won_value_current = (
    SELECT 
      SUM(estimate_value) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status = 'Won' 
      AND estimates.estimate_category IN ('Supply Only', 'Supply/Fit') 
      AND estimates.estimate_date BETWEEN '2025-04-01' AND '2026-03-31'
  );

-- Installs - Won estimate value previous year
UPDATE client_report
SET 
  cr_install_won_value_previous = (
    SELECT 
      SUM(estimate_value) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status = 'Won' 
      AND estimates.estimate_category IN ('Supply Only', 'Supply/Fit') 
      AND estimates.estimate_date BETWEEN '2024-04-01' AND '2025-03-31'
  );

-- Installs - Won estimate value older
UPDATE client_report
SET 
  cr_install_won_value_older = (
    SELECT 
      SUM(estimate_value) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status = 'Won' 
      AND estimates.estimate_category IN ('Supply Only', 'Supply/Fit') 
      AND estimates.estimate_date < '2024-04-01'  );

-- Assets installed - Current Year

UPDATE client_report AS cr
LEFT JOIN (
  SELECT
    e.estimate_client,
    COUNT(ep.est_prod_id) AS total_asset_count
  FROM
    estimate_products AS ep
    JOIN estimates AS e ON ep.est_prod_estimate = e.estimate_ref
    JOIN products AS p ON ep.est_prod_product = p.product_id
  WHERE
    p.product_asset = 1
    AND e.estimate_job_ref IS NOT NULL
    AND e.estimate_date BETWEEN '2025-04-01' AND '2026-03-31'
  GROUP BY
    e.estimate_client
) AS cac ON cr.cr_client_id = cac.estimate_client

SET
  cr.cr_install_assets_installed_current = COALESCE(cac.total_asset_count, 0);


-- Assets installed - Previous Year

UPDATE client_report AS cr
LEFT JOIN (
  SELECT
    e.estimate_client,
    COUNT(ep.est_prod_id) AS total_asset_count
  FROM
    estimate_products AS ep
    JOIN estimates AS e ON ep.est_prod_estimate = e.estimate_ref
    JOIN products AS p ON ep.est_prod_product = p.product_id
  WHERE
    p.product_asset = 1
    AND e.estimate_job_ref IS NOT NULL
    AND e.estimate_date BETWEEN '2024-04-01' AND '2025-03-31'
  GROUP BY
    e.estimate_client
) AS cac ON cr.cr_client_id = cac.estimate_client

SET
  cr.cr_install_assets_installed_previous = COALESCE(cac.total_asset_count, 0);

-- Assets installed - Older

UPDATE client_report AS cr
LEFT JOIN (
  SELECT
    e.estimate_client,
    COUNT(ep.est_prod_id) AS total_asset_count
  FROM
    estimate_products AS ep
    JOIN estimates AS e ON ep.est_prod_estimate = e.estimate_ref
    JOIN products AS p ON ep.est_prod_product = p.product_id
  WHERE
    p.product_asset = 1
    AND e.estimate_job_ref IS NOT NULL
    AND e.estimate_date < '2024-04-01'
  GROUP BY
    e.estimate_client
) AS cac ON cr.cr_client_id = cac.estimate_client

SET
  cr.cr_install_assets_installed_older = COALESCE(cac.total_asset_count, 0);

-- Installed Assets Under Contract - Current year
UPDATE 
    client_report AS cr
LEFT JOIN (
    -- 1. This subquery calculates all counts in a single pass
    SELECT
        e.estimate_client,
        COUNT(ep.est_prod_id) AS ContractCount
    FROM
        estimate_products AS ep
        JOIN estimates AS e ON ep.est_prod_estimate = e.estimate_ref
        JOIN products AS p ON ep.est_prod_product = p.product_id
        JOIN assets AS a ON ep.est_prod_id = a.asset_est_product
        JOIN maintenance_orders AS mo ON a.asset_mo = mo.mo_id
    WHERE
        p.product_asset = 1
        AND e.estimate_job_ref IS NOT NULL
        AND mo.mo_status = 'Accepted'
        AND e.estimate_date BETWEEN '2025-04-01' AND '2026-03-31'
    GROUP BY 
        e.estimate_client
) AS ccc ON cr.cr_client_id = ccc.estimate_client
-- 2. The SET clause updates the target table
SET
    -- Use COALESCE to set the count to 0 for clients who have no contracts
    cr.cr_install_under_contract_current = COALESCE(ccc.ContractCount, 0);

-- Installed Assets Under Contract - Previous year

UPDATE 
    client_report AS cr
LEFT JOIN (
    -- 1. This subquery calculates all counts in a single pass
    SELECT
        e.estimate_client,
        COUNT(ep.est_prod_id) AS ContractCount
    FROM
        estimate_products AS ep
        JOIN estimates AS e ON ep.est_prod_estimate = e.estimate_ref
        JOIN products AS p ON ep.est_prod_product = p.product_id
        JOIN assets AS a ON ep.est_prod_id = a.asset_est_product
        JOIN maintenance_orders AS mo ON a.asset_mo = mo.mo_id
    WHERE
        p.product_asset = 1
        AND e.estimate_job_ref IS NOT NULL
        AND mo.mo_status = 'Accepted'
        AND e.estimate_date BETWEEN '2024-04-01' AND '2025-03-31'
    GROUP BY 
        e.estimate_client
) AS ccc ON cr.cr_client_id = ccc.estimate_client
-- 2. The SET clause updates the target table
SET
    -- Use COALESCE to set the count to 0 for clients who have no contracts
    cr.cr_install_under_contract_previous = COALESCE(ccc.ContractCount, 0);
-- Installed Assets Under Contract - Other
UPDATE 
    client_report AS cr
LEFT JOIN (
    -- 1. This subquery calculates all counts in a single pass
    SELECT
        e.estimate_client,
        COUNT(ep.est_prod_id) AS ContractCount
    FROM
        estimate_products AS ep
        JOIN estimates AS e ON ep.est_prod_estimate = e.estimate_ref
        JOIN products AS p ON ep.est_prod_product = p.product_id
        JOIN assets AS a ON ep.est_prod_id = a.asset_est_product
        JOIN maintenance_orders AS mo ON a.asset_mo = mo.mo_id
    WHERE
        p.product_asset = 1
        AND e.estimate_job_ref IS NOT NULL
        AND mo.mo_status = 'Accepted'
        AND e.estimate_date < '2024-04-01'
    GROUP BY 
        e.estimate_client
) AS ccc ON cr.cr_client_id = ccc.estimate_client
-- 2. The SET clause updates the target table
SET
    -- Use COALESCE to set the count to 0 for clients who have no contracts
    cr.cr_install_under_contract_older = COALESCE(ccc.ContractCount, 0);

-- Install Remedials Volume - Current Year
UPDATE client_report
SET 
  cr_install_remedial_volume_current = (
    SELECT 
      COUNT(*) 
    FROM 
      remedial 
    WHERE 
      remedial.rem_client = client_report.cr_client_id 
      AND remedial.rem_job IS NOT NULL 
      AND remedial.rem_date BETWEEN '2025-04-01' AND '2026-03-31'
  );

-- Install Remedials Volume - Previous Year
UPDATE client_report
SET 
  cr_install_remedial_volume_previous = (
    SELECT 
      COUNT(*) 
    FROM 
      remedial 
    WHERE 
      remedial.rem_client = client_report.cr_client_id 
      AND remedial.rem_job IS NOT NULL 
      AND remedial.rem_date BETWEEN '2024-04-01' AND '2025-03-31'
  );

-- Install Remedials Volume - Other
UPDATE client_report
SET 
  cr_install_remedial_volume_older = (
    SELECT 
      COUNT(*) 
    FROM 
      remedial 
    WHERE 
      remedial.rem_client = client_report.cr_client_id 
      AND remedial.rem_job IS NOT NULL 
      AND remedial.rem_date < '2024-04-01'
  );

-- Install Remedials Volume - Current Year
UPDATE client_report
SET 
  cr_install_remedial_volume_current = (
    SELECT 
      COUNT(*) 
    FROM 
      remedial 
    WHERE 
      remedial.rem_client = client_report.cr_client_id 
      AND remedial.rem_job IS NOT NULL 
      AND remedial.rem_date BETWEEN '2025-04-01' AND '2026-03-31'
  );

-- Install Remedials Volume - Previous Year
UPDATE client_report
SET 
  cr_install_remedial_volume_previous = (
    SELECT 
      COUNT(*) 
    FROM 
      remedial 
    WHERE 
      remedial.rem_client = client_report.cr_client_id 
      AND remedial.rem_job IS NOT NULL 
      AND remedial.rem_date BETWEEN '2024-04-01' AND '2025-03-31'
  );

-- Install Remedials Volume - Other
UPDATE client_report
SET 
  cr_install_remedial_volume_older = (
    SELECT 
      COUNT(*) 
    FROM 
      remedial 
    WHERE 
      remedial.rem_client = client_report.cr_client_id 
      AND remedial.rem_job IS NOT NULL 
      AND remedial.rem_date < '2024-04-01'
  );

-- Install Remedial Value - Current Year

UPDATE client_report AS cr
LEFT JOIN (
  SELECT
    repair_client,
    SUM(repair_value) AS total_remedial_value
  FROM
    repairs
  WHERE
    repair_remedial IS NOT NULL
    AND repair_date BETWEEN '2025-04-01' AND '2026-03-31'
  GROUP BY
    repair_client
) AS rs ON cr.cr_client_id = rs.repair_client
-- The COALESCE sets clients with no matching repairs to 0
SET
  cr.cr_install_remedial_value_current = COALESCE(rs.total_remedial_value, 0);


-- Install Remedial Value - Previous Year

UPDATE client_report AS cr
LEFT JOIN (
  SELECT
    repair_client,
    SUM(repair_value) AS total_remedial_value
  FROM
    repairs
  WHERE
    repair_remedial IS NOT NULL
    AND repair_date BETWEEN '2024-04-01' AND '2025-03-31'
  GROUP BY
    repair_client
) AS rs ON cr.cr_client_id = rs.repair_client
-- The COALESCE sets clients with no matching repairs to 0
SET
  cr.cr_install_remedial_value_previous = COALESCE(rs.total_remedial_value, 0);
-- Install Remedial Value - Other

UPDATE client_report AS cr
LEFT JOIN (
  SELECT
    repair_client,
    SUM(repair_value) AS total_remedial_value
  FROM
    repairs
  WHERE
    repair_remedial IS NOT NULL
    AND repair_date < '2024-04-01'
  GROUP BY
    repair_client
) AS rs ON cr.cr_client_id = rs.repair_client
-- The COALESCE sets clients with no matching repairs to 0
SET
  cr.cr_install_remedial_value_older = COALESCE(rs.total_remedial_value, 0);

-- Repairs - Quoted estimate volume current year
UPDATE client_report
SET 
  cr_repair_quoted_volume_current = (
    SELECT 
      COUNT(*) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status != 'Cancelled'
      AND estimates.estimate_category = 'Repair'
      AND estimates.estimate_date BETWEEN '2025-04-01' AND '2026-03-31'
  );

-- Repairs - Quoted estimate volume previous year
UPDATE client_report
SET 
  cr_repair_quoted_volume_previous = (
    SELECT 
      COUNT(*) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status != 'Cancelled' 
      AND estimates.estimate_category = 'Repair' 
      AND estimates.estimate_date BETWEEN '2024-04-01' AND '2025-03-31'
  );

-- Repairs - Quoted estimate volume older
UPDATE client_report
SET 
  cr_repair_quoted_volume_older = (
    SELECT 
      COUNT(*) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status != 'Cancelled' 
      AND estimates.estimate_category = 'Repair'
      AND estimates.estimate_date < '2024-04-01'  );

-- Repairs - Quoted estimate value current year
UPDATE client_report
SET 
  cr_repair_quoted_value_current = (
    SELECT 
      SUM(estimate_value) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status != 'Cancelled' 
      AND estimates.estimate_category = 'Repair'
      AND estimates.estimate_date BETWEEN '2025-04-01' AND '2026-03-31'
  );

-- Repairs - Quoted estimate value previous year
UPDATE client_report
SET 
  cr_repair_quoted_value_previous = (
    SELECT 
      SUM(estimate_value) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status != 'Cancelled' 
      AND estimates.estimate_category = 'Repair'
      AND estimates.estimate_date BETWEEN '2024-04-01' AND '2025-03-31'
  );

-- Repairs - Quoted estimate value older
UPDATE client_report
SET 
  cr_repair_quoted_value_older = (
    SELECT 
      SUM(estimate_value) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status != 'Cancelled' 
      AND estimates.estimate_category = 'Repair'
      AND estimates.estimate_date < '2024-04-01'  );

-- Repairs - Won estimate volume current year
UPDATE client_report
SET 
  cr_repair_won_volume_current = (
    SELECT 
      COUNT(*) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status = 'Won'
      AND estimates.estimate_category = 'Repair'
      AND estimates.estimate_date BETWEEN '2025-04-01' AND '2026-03-31'
  );


-- Repairs - Won estimate volume previous year
UPDATE client_report
SET 
  cr_repair_won_volume_previous = (
    SELECT 
      COUNT(*) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status = 'Won' 
      AND estimates.estimate_category = 'Repair'
      AND estimates.estimate_date BETWEEN '2024-04-01' AND '2025-03-31'
  );

-- Repairs - Won estimate volume older
UPDATE client_report
SET 
  cr_repair_won_volume_older = (
    SELECT 
      COUNT(*) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status = 'Won'  
      AND estimates.estimate_category = 'Repair'
      AND estimates.estimate_date < '2024-04-01'  );


-- Repairs - Won estimate value current year
UPDATE client_report
SET 
  cr_repair_won_value_current = (
    SELECT 
      SUM(estimate_value) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status = 'Won' 
      AND estimates.estimate_category = 'Repair'
      AND estimates.estimate_date BETWEEN '2025-04-01' AND '2026-03-31'
  );

-- Repairs - Won estimate value previous year
UPDATE client_report
SET 
  cr_repair_won_value_previous = (
    SELECT 
      SUM(estimate_value) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status = 'Won' 
      AND estimates.estimate_category = 'Repair'
      AND estimates.estimate_date BETWEEN '2024-04-01' AND '2025-03-31'
  );

-- Repairs - Won estimate value older
UPDATE client_report
SET 
  cr_repair_won_value_older = (
    SELECT 
      SUM(estimate_value) 
    FROM 
      estimates 
    WHERE 
      estimates.estimate_client = client_report.cr_client_id 
      AND estimates.estimate_status = 'Won' 
      AND estimates.estimate_category = 'Repair'
      AND estimates.estimate_date < '2024-04-01'  );
