-- 2. Migrate spouse data to new member records
INSERT INTO sust.members (
    member_count, member_firstname, member_surname, member_suffix,
    member_street, member_streetnumber, member_email, member_zip, 
    member_city, member_active, member_gender, member_died, member_printed,
    member_coordinates, member_geo_latitude, member_geo_longitude, 
    member_membership_start, member_phone, current_state_code, 
    current_state_changed_at, spouse_id
)
SELECT 
    1, member_firstname_spouse, member_surname, member_suffix,
    member_street, member_streetnumber, 
    member_email || '_partner' as member_email,
    member_zip, member_city, member_active, 'female', member_died, 
    member_printed, member_coordinates, member_geo_latitude, 
    member_geo_longitude, member_membership_start, 
    member_phone || '_partner' as member_phone, 
    current_state_code, current_state_changed_at, id
FROM sust.members 
WHERE member_firstname_spouse IS NOT NULL AND member_firstname_spouse != '' and members.id = 93
RETURNING id, spouse_id;

-- 3. Copy section memberships to new spouse members
INSERT INTO sust.member_sections (member_id, section_id)
SELECT 
    new_spouse.id, ms.section_id
FROM sust.member_sections ms
JOIN sust.members orig ON ms.member_id = orig.id
JOIN sust.members new_spouse ON new_spouse.spouse_id = orig.id
WHERE new_spouse.spouse_id IS NOT NULL
ON CONFLICT (member_id, section_id) DO NOTHING;

-- 5. Update primary members with spouse links
UPDATE sust.members m1
SET spouse_id = m2.id
FROM sust.members m2
WHERE m2.spouse_id = m1.id and m2.id = 93;

-- Add ONLY spouses of members ALREADY in event_attends for this event
INSERT INTO sust.event_attends (event_id, member_id, notes)
SELECT 1, m.spouse_id, 'Spouse of event attendee - all 3 days (Sylvia)'
FROM sust.members m
JOIN sust.event_attends ea ON m.id = ea.member_id
WHERE m.spouse_id IS NOT NULL
  AND ea.event_id = 1
  AND NOT EXISTS (
      SELECT 1 FROM sust.event_attends ea2 
      WHERE ea2.event_id = 1 AND ea2.member_id = m.spouse_id
  );

-- Check primary vs spouse attendees for event
SELECT 
    ea.notes,
    COUNT(*) as count,
    string_agg(m.member_firstname || ' ' || m.member_surname, ', ' ORDER BY m.member_surname) as names
FROM sust.event_attends ea
JOIN sust.members m ON ea.member_id = m.id
WHERE ea.event_id = 1
GROUP BY ea.notes
ORDER BY ea.notes;

-- Add birthday column to members table
ALTER TABLE sust.members 
ADD COLUMN member_birthday date NULL;

-- Add index for birthday queries
CREATE INDEX ix_members_member_birthday ON sust.members USING btree (member_birthday);

-- Optional: Add check constraint for valid dates
ALTER TABLE sust.members 
ADD CONSTRAINT members_birthday_check 
CHECK (member_birthday IS NULL OR member_birthday < CURRENT_DATE);

-- Much simpler: List households with attendee count + email
SELECT 
    MIN(m.member_firstname || ' ' || m.member_surname) AS household_representative,
    MIN(m.member_surname) AS surname,
    COUNT(DISTINCT ea.member_id) AS attendees,
    STRING_AGG(
        DISTINCT 
        CASE 
            WHEN m.member_birthday IS NOT NULL 
            THEN m.member_firstname || ' (' || TO_CHAR(m.member_birthday, 'DD.MM.YYYY') || ')'
            ELSE m.member_firstname 
        END,
        ', '
    ) AS attendees_birthdays,
    MIN(
        TRIM(
            COALESCE(m.member_street, '') || ' ' || COALESCE(m.member_streetnumber, '') || 
            ', ' || COALESCE(m.member_zip::text, '') || ' ' || COALESCE(m.member_city, '')
        )
    ) AS address,
    MIN(m.member_email) AS email  -- household email (one per household)
FROM sust.event_attends ea
JOIN sust.members m ON ea.member_id = m.id
WHERE ea.event_id = 1
GROUP BY 
    LEAST(
        m.id,
        COALESCE(
            (SELECT spouse_id FROM sust.members WHERE id = m.id),
            m.id
        )
    ),
    m.member_surname
ORDER BY m.member_surname;

update members set member_primary = false where id in (217,221);

select * from payments where member_id = 119