Tank Level Alert V3 - Complete Email Sending Implementation

Overview

Complete the tank level alert email system by:

1. Implementing tank level calculation in the Edge Function
2. Sending aggregated emails via Microsoft Graph API
3. Adding configuration for alert recipients

Tasks

Task 1: Update send-tank-level-alert Edge Function

Add the following functionality:

1. Fetch flow meter assets for the alert asset IDs
2. Calculate tank levels (remaining litres, capacity, percentage)
3. Generate aggregated email data
4. Call send-email function or send directly via Graph API
5. Log the email send

Task 2: Add Tank Alert Email Configuration

Create a simple configuration table or use existing email_schedules:

• Recipients for tank level alerts
• Subject template
• Body template with {{summary_tank_level}}

Task 3: Add Cron Job Trigger

Configure daily trigger at 8:00 AM AWST.

---

Implementation Details

Edge Function Flow

1. Get today's date (AWST)
2. Query pending alerts (scheduled_send_date = today, not notified)
3. If no alerts → return early
4. Get asset IDs from alerts
5. Fetch flow meter assets for those IDs
6. Fetch tank capacities
7. Fetch latest corrections
8. Calculate current levels for each tank
9. Generate TankLevelAlertData
10. Get alert email config (recipients, template)
11. Replace {{summary_tank_level}} in template
12. Send email via Graph API
13. Mark alerts as notified
14. Log email send

Database Queries Needed

-- 1. Flow meter assets
SELECT asset_id, display_id, site, mine_site_id
FROM data_assets
WHERE asset_id IN (alert_asset_ids)
AND asset_type = 'flow_meter';

-- 2. Tank capacities
SELECT asset_id, capacity_litres, site
FROM ops_tank_capacities
WHERE asset_id IN (alert_asset_ids);

-- 3. Latest corrections (one per asset)
SELECT DISTINCT ON (asset_id)
  asset_id, remaining_litres, correction_datetime
FROM ops_tank_corrections
WHERE asset_id IN (alert_asset_ids)
ORDER BY asset_id, correction_datetime DESC;

-- 4. Refills since correction (per asset)
SELECT asset_id, SUM(litres_refilled) as total_refills
FROM ops_dustloc_refills
WHERE asset_id = ? AND refill_datetime > correction_datetime
GROUP BY asset_id;

-- 5. Usage since correction (per asset)
SELECT asset_id, SUM(litres_dispensed) as total_usage
FROM data_flow_meters
WHERE asset_id = ? AND datetime_dispensed > correction_datetime AND is_ignored = false
GROUP BY asset_id;

Email Configuration

Use cfg_app_settings table for simple config:

INSERT INTO cfg_app_settings (key, value) VALUES
('tank_alert_recipients', '["operations@dustac.com.au"]'),
('tank_alert_subject', '⚠️ Tank Level Low Warning - {{tank_count}} tank(s) require attention'),
('tank_alert_enabled', 'true');

Or create a dedicated email schedule that can be managed via UI.