collective-lists/supabase/seed.sql

-- seed.sql — development seed data
-- UUIDs are fixed and match both keycloak/realm-export.json and auth.users below.
-- Password for all Keycloak test users: test1234

-- ── GoTrue auth.users — pre-seed so auth.uid() returns the same UUIDs ────────
-- GoTrue normally generates its own UUIDs on first OIDC login. By pre-seeding
-- auth.users + auth.identities with the known Keycloak sub UUIDs, GoTrue finds
-- the existing identity on login and reuses the same UUID. This keeps auth.uid()
-- consistent with all foreign keys in public.* tables.
INSERT INTO auth.users (
    instance_id, id, aud, role, email,
    email_confirmed_at,
    -- Token fields must be '' not NULL; GoTrue's Go struct uses string (not *string)
    confirmation_token, recovery_token, email_change_token_new, email_change,
    raw_app_meta_data, raw_user_meta_data,
    is_super_admin, created_at, updated_at
)
VALUES
  ('00000000-0000-0000-0000-000000000000', '11111111-1111-1111-1111-111111111111', 'authenticated', 'authenticated', 'ana@dev.local',
   now(), '', '', '', '',
   '{"provider":"keycloak","providers":["keycloak"]}',
   '{"full_name":"Ana García","email":"ana@dev.local","email_verified":true}',
   false, now(), now()),
  ('00000000-0000-0000-0000-000000000000', '22222222-2222-2222-2222-222222222222', 'authenticated', 'authenticated', 'borja@dev.local',
   now(), '', '', '', '',
   '{"provider":"keycloak","providers":["keycloak"]}',
   '{"full_name":"Borja López","email":"borja@dev.local","email_verified":true}',
   false, now(), now()),
  ('00000000-0000-0000-0000-000000000000', '33333333-3333-3333-3333-333333333333', 'authenticated', 'authenticated', 'carmen@dev.local',
   now(), '', '', '', '',
   '{"provider":"keycloak","providers":["keycloak"]}',
   '{"full_name":"Carmen Martínez","email":"carmen@dev.local","email_verified":true}',
   false, now(), now()),
  ('00000000-0000-0000-0000-000000000000', '44444444-4444-4444-4444-444444444444', 'authenticated', 'authenticated', 'david@dev.local',
   now(), '', '', '', '',
   '{"provider":"keycloak","providers":["keycloak"]}',
   '{"full_name":"David Fernández","email":"david@dev.local","email_verified":true}',
   false, now(), now()),
  ('00000000-0000-0000-0000-000000000000', '55555555-5555-5555-5555-555555555555', 'authenticated', 'authenticated', 'eva@dev.local',
   now(), '', '', '', '',
   '{"provider":"keycloak","providers":["keycloak"]}',
   '{"full_name":"Eva Ruiz","email":"eva@dev.local","email_verified":true}',
   false, now(), now())
ON CONFLICT (id) DO NOTHING;

-- ── GoTrue auth.identities — link Keycloak provider_id to the pre-seeded UUIDs ─
INSERT INTO auth.identities (
    provider_id, user_id, identity_data, provider,
    created_at, updated_at
)
VALUES
  ('11111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111',
   '{"sub":"11111111-1111-1111-1111-111111111111","iss":"http://keycloak:8080/realms/colectivo","email":"ana@dev.local","full_name":"Ana García","email_verified":true}',
   'keycloak', now(), now()),
  ('22222222-2222-2222-2222-222222222222', '22222222-2222-2222-2222-222222222222',
   '{"sub":"22222222-2222-2222-2222-222222222222","iss":"http://keycloak:8080/realms/colectivo","email":"borja@dev.local","full_name":"Borja López","email_verified":true}',
   'keycloak', now(), now()),
  ('33333333-3333-3333-3333-333333333333', '33333333-3333-3333-3333-333333333333',
   '{"sub":"33333333-3333-3333-3333-333333333333","iss":"http://keycloak:8080/realms/colectivo","email":"carmen@dev.local","full_name":"Carmen Martínez","email_verified":true}',
   'keycloak', now(), now()),
  ('44444444-4444-4444-4444-444444444444', '44444444-4444-4444-4444-444444444444',
   '{"sub":"44444444-4444-4444-4444-444444444444","iss":"http://keycloak:8080/realms/colectivo","email":"david@dev.local","full_name":"David Fernández","email_verified":true}',
   'keycloak', now(), now()),
  ('55555555-5555-5555-5555-555555555555', '55555555-5555-5555-5555-555555555555',
   '{"sub":"55555555-5555-5555-5555-555555555555","iss":"http://keycloak:8080/realms/colectivo","email":"eva@dev.local","full_name":"Eva Ruiz","email_verified":true}',
   'keycloak', now(), now())
ON CONFLICT (provider_id, provider) DO NOTHING;

-- ── public.users — synced from auth.users via trigger on login ────────────────
-- Pre-seeded here too so the data is available before first login.
INSERT INTO public.users (id, email, display_name, language, avatar_type)
VALUES
  ('11111111-1111-1111-1111-111111111111', 'ana@dev.local',    'Ana García',      'es', 'initials'),
  ('22222222-2222-2222-2222-222222222222', 'borja@dev.local',  'Borja López',     'es', 'initials'),
  ('33333333-3333-3333-3333-333333333333', 'carmen@dev.local', 'Carmen Martínez', 'es', 'initials'),
  ('44444444-4444-4444-4444-444444444444', 'david@dev.local',  'David Fernández', 'es', 'initials'),
  ('55555555-5555-5555-5555-555555555555', 'eva@dev.local',    'Eva Ruiz',        'es', 'initials')
ON CONFLICT (id) DO NOTHING;

-- ── Sample collective ──────────────────────────────────────────────────────────
INSERT INTO public.collectives (id, name, emoji, created_by)
VALUES ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'Casa García-López', '🏠', '11111111-1111-1111-1111-111111111111')
ON CONFLICT (id) DO NOTHING;

-- ── Collective members ─────────────────────────────────────────────────────────
-- Ana: admin, Borja: member, Carmen: member, David: guest
-- Eva has no collective (for onboarding testing)
INSERT INTO public.collective_members (collective_id, user_id, role)
VALUES
  ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '11111111-1111-1111-1111-111111111111', 'admin'),
  ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '22222222-2222-2222-2222-222222222222', 'member'),
  ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '33333333-3333-3333-3333-333333333333', 'member'),
  ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '44444444-4444-4444-4444-444444444444', 'guest')
ON CONFLICT (collective_id, user_id) DO NOTHING;

-- ── Phase 2a seed data (shopping lists, items, frequency) ─────────────────────
-- Guarded: only inserted when the tables exist (Phase 2a migrations applied).
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables
             WHERE table_schema = 'public' AND table_name = 'shopping_lists') THEN
    INSERT INTO public.shopping_lists (id, collective_id, name, status, created_by)
    VALUES ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'Weekly shop', 'active', '11111111-1111-1111-1111-111111111111')
    ON CONFLICT (id) DO NOTHING;

    INSERT INTO public.shopping_items (list_id, name, quantity, sort_order, created_by)
    VALUES
      ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'Milk 1L',      2, 1, '11111111-1111-1111-1111-111111111111'),
      ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'Bread',        1, 2, '22222222-2222-2222-2222-222222222222'),
      ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'Eggs 1 doz',   1, 3, '11111111-1111-1111-1111-111111111111'),
      ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'Coffee',       1, 4, '33333333-3333-3333-3333-333333333333'),
      ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'Apples',       6, 5, '22222222-2222-2222-2222-222222222222')
    ON CONFLICT DO NOTHING;
  END IF;

  IF EXISTS (SELECT 1 FROM information_schema.tables
             WHERE table_schema = 'public' AND table_name = 'item_frequency') THEN
    INSERT INTO public.item_frequency (collective_id, name, use_count, last_used_at)
    VALUES
      ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'milk',   5, now()),
      ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'bread',  4, now()),
      ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'eggs',   3, now()),
      ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'coffee', 3, now()),
      ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'apples', 2, now()),
      ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'butter', 2, now()),
      ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'yogurt', 1, now())
    ON CONFLICT (collective_id, name) DO UPDATE SET
      use_count = EXCLUDED.use_count,
      last_used_at = EXCLUDED.last_used_at;
  END IF;
END $$;

-- ── Fase 13 server admins ─────────────────────────────────────────────────────
-- In dev we promote Ana directly so the integration + e2e suites have a known
-- server admin without depending on `docker-entrypoint-initdb.d` (which only
-- fires on a fresh volume). On prod the same role is bootstrapped by
-- infra/db-init/10-server-admin-seed.sh reading SERVER_ADMIN_EMAIL.
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables
             WHERE table_schema = 'public' AND table_name = 'server_admins') THEN
    INSERT INTO public.server_admins (user_id)
    VALUES ('11111111-1111-1111-1111-111111111111')  -- Ana
    ON CONFLICT (user_id) DO NOTHING;
  END IF;
END $$;