drop view sust.members_by_section;
drop view sust.members_active;

CREATE VIEW sust.members_active
AS SELECT id,
    member_surname,
    member_firstname,
    member_firstname_spouse,
    member_count,
    member_active,
    current_state_code,
    member_email
   FROM members m
  WHERE TRIM(BOTH FROM current_state_code) = 'A'::text AND member_count > 0 AND member_primary = true
  ORDER BY member_surname;

CREATE OR REPLACE VIEW sust.members_by_section
AS SELECT m.id,
    m.member_surname,
    m.member_firstname,
    m.member_firstname_spouse,
    m.member_active,
    m.member_email,
    s.section_name
   FROM members_active m
     JOIN member_sections ms ON ms.member_id = m.id
     JOIN sections s ON s.id = ms.section_id;

SELECT sum(member_count) FROM members_active

drop view sust.members_paid_years;
CREATE OR REPLACE VIEW sust.members_paid_years
AS SELECT id,
    member_surname,
    member_firstname,
    member_suffix,
    member_firstname_spouse,
    CONCAT_WS(' ', member_street, member_streetnumber, member_city) as member_address,
    member_active,
    current_state_code,
    member_email,
    member_membership_start,
        CASE
            WHEN member_membership_start > '2023-12-31'::date THEN 'N'::text
            WHEN (EXISTS ( SELECT 1
               FROM payments p
              WHERE p.member_id = m.id AND EXTRACT(year FROM p.payment_year) = 2023::numeric)) THEN 'X'::text
            ELSE NULL::text
        END AS paid_2023,
        CASE
            WHEN member_membership_start > '2024-12-31'::date THEN 'N'::text
            WHEN (EXISTS ( SELECT 1
               FROM payments p
              WHERE p.member_id = m.id AND EXTRACT(year FROM p.payment_year) = 2024::numeric)) THEN 'X'::text
            ELSE NULL::text
        END AS paid_2024,
        CASE
            WHEN member_membership_start > '2025-12-31'::date THEN 'N'::text
            WHEN (EXISTS ( SELECT 1
               FROM payments p
              WHERE p.member_id = m.id AND EXTRACT(year FROM p.payment_year) = 2025::numeric)) THEN 'X'::text
            ELSE NULL::text
        END AS paid_2025,
        CASE
            WHEN member_membership_start > '2026-12-31'::date THEN 'N'::text
            WHEN (EXISTS ( SELECT 1
               FROM payments p
              WHERE p.member_id = m.id AND EXTRACT(year FROM p.payment_year) = 2026::numeric)) THEN 'X'::text
            ELSE NULL::text
        END AS paid_2026
   FROM members m
  WHERE TRIM(BOTH FROM current_state_code) = 'A'::text AND member_count > 0 AND member_primary = true
  ORDER BY member_surname;