-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('FEHDE', 'edw', 'effort', 'Flag', 'Flag records where the maximum daily effort is out of range');
DROP TABLE IF EXISTS groomed.max_daily_effort;
CREATE TABLE groomed.max_daily_effort (
method TEXT,
events INT,
hook_num INT,
net_length NUMERIC
);
INSERT INTO groomed.max_daily_effort VALUES
('BLL', 25, 70000, 0),
('BPT', 25, 0, 0),
('BT', 25, 0, 0),
('MW', 25, 0, 0),
('PS', 25, 0, 10000),
('SLL', 25, 16000, 0),
('SN', 250, 0, 20000);
DROP TABLE IF EXISTS groomed.high_daily_effort;
CREATE TABLE groomed.high_daily_effort AS
WITH methods AS (
SELECT unnest(ARRAY['MW', 'BT', 'BPT', 'SN', 'SLL', 'BLL', 'PS']) AS method
), daily_effort AS (
SELECT method, key, day,
count(*) AS records,
SUM(coalesce(effort_num, 0)) AS effort,
SUM(coalesce(total_hook_num, 0)) AS hook_num,
SUM(coalesce(total_net_length, 0)) AS net_length
FROM methods
LEFT JOIN (
SELECT primary_method,
COALESCE(vessel_key, -event_key) AS key,
start_datetime::DATE AS day,
effort_num,
total_hook_num,
total_net_length
FROM groomed.effort
) x ON x.primary_method = methods.method
GROUP BY method, key, day
)
SELECT d.*,
CASE WHEN d.effort > a.events THEN 'effort_num'
WHEN d.hook_num > a.hook_num THEN 'total_hook_num'
WHEN d.net_length > a.net_length THEN 'total_net_length'
ELSE NULL
END AS too_high
FROM daily_effort d
JOIN groomed.max_daily_effort a ON d.method = a.method
WHERE d.effort > a.events
OR d.hook_num > a.hook_num
OR d.net_length > a.net_length
ORDER BY d.method, key, day;
CREATE OR REPLACE FUNCTION FEHDE() RETURNS VOID AS $$
INSERT INTO groomed.checks (code, "table", "column", id)
SELECT 'FEHDE', 'effort', h.too_high, e.id
FROM groomed.effort e
JOIN groomed.high_daily_effort h ON e.vessel_key = h.key
AND e.start_datetime::DATE = h.day
AND e.primary_method = h.method;
UPDATE groomed.effort
SET checks = checks || '{FEHDE}'
FROM groomed.checks u
WHERE u.code = 'FEHDE'
AND groomed.effort.id = u.id;
$$ LANGUAGE SQL;
SELECT FEHDE();
