Ring sequence page

[wheresrhys]

For each ring sequence (rings with the same string length and beginning with the same 3 alphanumeric string) it will be interesting to see

• How many rings have been used
• The earliest date a ring was used and the latest
• Which species the rings went on and when

I suggest a UI on a new page /ring-sequences (accessible via the main nav) which contains nested accordions/tabs/tables as follows

Top level accordion: Ring sequence e.g. AYL, RKK, SH5 - sorted by latest date ring used, earliest date ring used, both DESC. Shows data: number used, earliest, latest
When expanded show

• if there are any discontinuities in the ring sequence e.g. ones ending 6,7,9 exist in the data, but not 8, list these as Unused: {list of ring numbers you'd have expected to see}
• a table of Species. Data: count of rings used. Expanding a species shows the individual rings

Query to get sequences could be something like:

WITH sequences as (SELECT 
  LEFT(ring_no, 3) as sequence,
  ring_no
FROM "Birds")
select
  sequence, count(*) as number
FROM sequences
GROUP BY sequence
ORDER BY SEQUENCE

Retrap data should always be ignored

As this page will use data for ALL rings I suggest writing a supabase view to obtain the summary data (Or maybe a RPC would be better as it allows passing in group id) - count, earliest and latest date. Then the detail can be lazily fetched when the accordion is expanded using a next.js action