supabase-dummy-data.sql

-- =====================================================
-- DUMMY DATA FOR TESTING
-- =====================================================
-- This script populates the database with sample data
-- Run this after the main schema is set up
-- =====================================================

-- First, create a test user profile (if not exists)
-- Note: This requires an auth user first, or we can create profiles directly
-- For testing, we'll create profiles that can be linked to auth users later

-- Create test profiles (these would normally be created via Supabase Auth)
-- We'll use a workaround: create profiles with placeholder IDs
-- In production, profiles are created automatically when users sign up

-- =====================================================
-- 1. CREATE TEST PROFILES
-- =====================================================
-- Note: In production, profiles are created via Supabase Auth triggers
-- For testing, we'll insert directly (you may need to adjust based on your setup)

DO $$
DECLARE
  test_user_id_1 uuid := '00000000-0000-0000-0000-000000000001';
  test_user_id_2 uuid := '00000000-0000-0000-0000-000000000002';
  test_user_id_3 uuid := '00000000-0000-0000-0000-000000000003';
BEGIN
  -- Insert test profiles (only if they don't exist)
  INSERT INTO profiles (id, email, full_name, role, created_at)
  VALUES
    (test_user_id_1, 'max.mustermann@example.com', 'Max Mustermann', 'business', now()),
    (test_user_id_2, 'anna.schmidt@example.com', 'Anna Schmidt', 'business', now()),
    (test_user_id_3, 'peter.weber@example.com', 'Peter Weber', 'business', now())
  ON CONFLICT (id) DO NOTHING;
END $$;

-- =====================================================
-- 2. CREATE SAMPLE LISTINGS
-- =====================================================

-- Get category IDs (assuming categories from main schema exist)
DO $$
DECLARE
  elektriker_id uuid;
  klempner_id uuid;
  maler_id uuid;
  schreiner_id uuid;
  dachdecker_id uuid;
  heizung_id uuid;
  bau_id uuid;
  allgemein_id uuid;
  
  user_1_id uuid := '00000000-0000-0000-0000-000000000001';
  user_2_id uuid := '00000000-0000-0000-0000-000000000002';
  user_3_id uuid := '00000000-0000-0000-0000-000000000003';
BEGIN
  -- Get category IDs
  SELECT id INTO elektriker_id FROM categories WHERE slug = 'elektriker' LIMIT 1;
  SELECT id INTO klempner_id FROM categories WHERE slug = 'klempner-sanitar' LIMIT 1;
  SELECT id INTO maler_id FROM categories WHERE slug = 'maler' LIMIT 1;
  SELECT id INTO schreiner_id FROM categories WHERE slug = 'schreiner-tischler' LIMIT 1;
  SELECT id INTO dachdecker_id FROM categories WHERE slug = 'dachdecker' LIMIT 1;
  SELECT id INTO heizung_id FROM categories WHERE slug = 'heizungsbauer' LIMIT 1;
  SELECT id INTO bau_id FROM categories WHERE slug = 'bauunternehmen' LIMIT 1;
  SELECT id INTO allgemein_id FROM categories WHERE slug = 'allgemein' LIMIT 1;

  -- Insert sample listings
  INSERT INTO listings (
    user_id, business_name, slug, category_id, description, short_description,
    street, city, zip_code, state, latitude, longitude,
    phone, email, website, logo_url, images,
    opening_hours, is_verified, subscription_tier, status, views_count, leads_count,
    rating_average, rating_count
  ) VALUES
    -- Elektriker 1
    (
      user_1_id,
      'Münchner Elektro-Service',
      'munchner-elektro-service',
      elektriker_id,
      'Ihr zuverlässiger Elektriker in München und Umgebung. Wir bieten professionelle Elektroinstallationen, Reparaturen und Smart Home Lösungen. Über 20 Jahre Erfahrung im Handwerk. Notdienst verfügbar!',
      'Professionelle Elektroinstallationen und Reparaturen in München',
      'Marienplatz 1',
      'München',
      '80331',
      'Bayern',
      48.1374,
      11.5755,
      '+49 89 12345678',
      'info@munchner-elektro.de',
      'https://www.munchner-elektro.de',
      'https://images.unsplash.com/photo-1621905251918-48416bd8575a?w=200',
      ARRAY[
        'https://images.unsplash.com/photo-1621905251918-48416bd8575a?w=800',
        'https://images.unsplash.com/photo-1558618666-fcd25c85cd64?w=800',
        'https://images.unsplash.com/photo-1581092160562-40aa08e78837?w=800'
      ],
      '{"monday": "08:00-18:00", "tuesday": "08:00-18:00", "wednesday": "08:00-18:00", "thursday": "08:00-18:00", "friday": "08:00-18:00", "saturday": "09:00-14:00", "sunday": "closed"}'::jsonb,
      true,
      'premium',
      'active',
      245,
      12,
      4.8,
      15
    ),
    -- Klempner 1
    (
      user_2_id,
      'Bayern Sanitär & Heizung',
      'bayern-sanitar-heizung',
      klempner_id,
      'Komplettservice für Sanitär, Heizung und Bad. Von der Reparatur bis zur kompletten Badsanierung. Kostenlose Beratung vor Ort. 24h Notdienst!',
      'Sanitär, Heizung und Badkomplettservice in ganz Bayern',
      'Maximilianstraße 15',
      'München',
      '80539',
      'Bayern',
      48.1392,
      11.5782,
      '+49 89 98765432',
      'kontakt@bayern-sanitaer.de',
      'https://www.bayern-sanitaer.de',
      'https://images.unsplash.com/photo-1625246333195-78d9c38ad449?w=200',
      ARRAY[
        'https://images.unsplash.com/photo-1625246333195-78d9c38ad449?w=800',
        'https://images.unsplash.com/photo-1584622650111-993a426fbf0a?w=800'
      ],
      '{"monday": "07:00-19:00", "tuesday": "07:00-19:00", "wednesday": "07:00-19:00", "thursday": "07:00-19:00", "friday": "07:00-19:00", "saturday": "08:00-16:00", "sunday": "closed"}'::jsonb,
      true,
      'premium_plus',
      'active',
      189,
      8,
      4.9,
      22
    ),
    -- Maler 1
    (
      user_1_id,
      'Farbenreich Malerbetrieb',
      'farbenreich-malerbetrieb',
      maler_id,
      'Professionelle Malerarbeiten innen und außen. Tapezieren, Lackieren, Fassadenarbeiten. Kostenlose Farbberatung. Umweltfreundliche Farben verfügbar.',
      'Malerarbeiten innen und außen - Ihr Maler in München',
      'Sendlinger Straße 42',
      'München',
      '80331',
      'Bayern',
      48.1351,
      11.5720,
      '+49 89 55512345',
      'info@farbenreich.de',
      NULL,
      'https://images.unsplash.com/photo-1589939705384-5185137a7f0f?w=200',
      ARRAY[
        'https://images.unsplash.com/photo-1589939705384-5185137a7f0f?w=800',
        'https://images.unsplash.com/photo-1563453392212-326f5e854473?w=800'
      ],
      '{"monday": "08:00-17:00", "tuesday": "08:00-17:00", "wednesday": "08:00-17:00", "thursday": "08:00-17:00", "friday": "08:00-17:00", "saturday": "closed", "sunday": "closed"}'::jsonb,
      true,
      'basic',
      'active',
      156,
      5,
      4.6,
      8
    ),
    -- Schreiner 1
    (
      user_3_id,
      'Holzwerkstatt München',
      'holzwerkstatt-munchen',
      schreiner_id,
      'Meisterbetrieb für Möbelbau, Küchen, Türen und Fenster. Individuelle Lösungen nach Maß. Moderne und klassische Designs. Kostenlose Beratung.',
      'Schreinerarbeiten nach Maß - Möbel, Küchen, Türen',
      'Nymphenburger Straße 86',
      'München',
      '80636',
      'Bayern',
      48.1500,
      11.5300,
      '+49 89 44456789',
      'info@holzwerkstatt-muenchen.de',
      'https://www.holzwerkstatt-muenchen.de',
      'https://images.unsplash.com/photo-1504148455328-c376907d081c?w=200',
      ARRAY[
        'https://images.unsplash.com/photo-1504148455328-c376907d081c?w=800',
        'https://images.unsplash.com/photo-1556911220-bff31c812dba?w=800',
        'https://images.unsplash.com/photo-1586023492125-27b2c045efd7?w=800',
        'https://images.unsplash.com/photo-1556228578-0d85b1a4d571?w=800'
      ],
      '{"monday": "07:30-17:30", "tuesday": "07:30-17:30", "wednesday": "07:30-17:30", "thursday": "07:30-17:30", "friday": "07:30-17:30", "saturday": "09:00-13:00", "sunday": "closed"}'::jsonb,
      true,
      'premium',
      'active',
      312,
      18,
      4.7,
      14
    ),
    -- Dachdecker 1
    (
      user_2_id,
      'Dach & Fassade Bayern',
      'dach-fassade-bayern',
      dachdecker_id,
      'Dachdeckerarbeiten aller Art. Neue Dächer, Reparaturen, Dämmung, Fassadenarbeiten. Kostenlose Besichtigung und Kostenvoranschlag.',
      'Professionelle Dachdeckerarbeiten in ganz Bayern',
      'Prinzregentenstraße 58',
      'München',
      '80538',
      'Bayern',
      48.1412,
      11.5920,
      '+49 89 33398765',
      'info@dach-fassade-bayern.de',
      NULL,
      'https://images.unsplash.com/photo-1503387762-592deb58ef4e?w=200',
      ARRAY[
        'https://images.unsplash.com/photo-1503387762-592deb58ef4e?w=800',
        'https://images.unsplash.com/photo-1486406146926-c627a92ad1ab?w=800'
      ],
      '{"monday": "07:00-18:00", "tuesday": "07:00-18:00", "wednesday": "07:00-18:00", "thursday": "07:00-18:00", "friday": "07:00-18:00", "saturday": "closed", "sunday": "closed"}'::jsonb,
      true,
      'basic',
      'active',
      98,
      3,
      4.5,
      6
    ),
    -- Heizungsbauer 1
    (
      user_1_id,
      'Wärme & Energie München',
      'warme-energie-munchen',
      heizung_id,
      'Heizungsinstallation, Wartung und Reparatur. Gas, Öl, Wärmepumpen, Solarthermie. Energieberatung und Förderung. 24h Notdienst.',
      'Heizungsbau und -wartung - Ihr Heizungsexperte',
      'Leopoldstraße 45',
      'München',
      '80802',
      'Bayern',
      48.1633,
      11.5850,
      '+49 89 22234567',
      'kontakt@waerme-energie.de',
      'https://www.waerme-energie.de',
      'https://images.unsplash.com/photo-1621905252507-b35492cc74b4?w=200',
      ARRAY[
        'https://images.unsplash.com/photo-1621905252507-b35492cc74b4?w=800',
        'https://images.unsplash.com/photo-1558618047-3c8c76ca7d13?w=800'
      ],
      '{"monday": "08:00-18:00", "tuesday": "08:00-18:00", "wednesday": "08:00-18:00", "thursday": "08:00-18:00", "friday": "08:00-18:00", "saturday": "09:00-15:00", "sunday": "closed"}'::jsonb,
      true,
      'premium',
      'active',
      201,
      11,
      4.8,
      19
    ),
    -- Bauunternehmen 1
    (
      user_3_id,
      'Bauwerk München GmbH',
      'bauwerk-munchen-gmbh',
      bau_id,
      'Komplettservice für Bau, Renovierung und Sanierung. Von der Planung bis zur Schlüsselübergabe. Hochwertige Ausführung zu fairen Preisen.',
      'Bauunternehmen für Neubau, Renovierung und Sanierung',
      'Schwanthalerstraße 40',
      'München',
      '80336',
      'Bayern',
      48.1332,
      11.5500,
      '+49 89 11122334',
      'info@bauwerk-muenchen.de',
      'https://www.bauwerk-muenchen.de',
      'https://images.unsplash.com/photo-1486406146926-c627a92ad1ab?w=200',
      ARRAY[
        'https://images.unsplash.com/photo-1486406146926-c627a92ad1ab?w=800',
        'https://images.unsplash.com/photo-1504307651254-35680f356dfd?w=800'
      ],
      '{"monday": "07:00-17:00", "tuesday": "07:00-17:00", "wednesday": "07:00-17:00", "thursday": "07:00-17:00", "friday": "07:00-17:00", "saturday": "closed", "sunday": "closed"}'::jsonb,
      true,
      'premium_plus',
      'active',
      278,
      15,
      4.9,
      24
    ),
    -- More listings in different cities
    (
      user_2_id,
      'Nürnberg Elektro',
      'nurnberg-elektro',
      elektriker_id,
      'Ihr Elektriker in Nürnberg. Schnelle Hilfe bei allen Elektroproblemen. Installation, Reparatur, Smart Home.',
      'Elektriker in Nürnberg - Schnell und zuverlässig',
      'Hauptmarkt 14',
      'Nürnberg',
      '90403',
      'Bayern',
      49.4521,
      11.0767,
      '+49 911 1234567',
      'info@nurnberg-elektro.de',
      NULL,
      'https://images.unsplash.com/photo-1621905251918-48416bd8575a?w=200',
      ARRAY[
        'https://images.unsplash.com/photo-1621905251918-48416bd8575a?w=800'
      ],
      '{"monday": "08:00-17:00", "tuesday": "08:00-17:00", "wednesday": "08:00-17:00", "thursday": "08:00-17:00", "friday": "08:00-17:00", "saturday": "closed", "sunday": "closed"}'::jsonb,
      false,
      'basic',
      'active',
      67,
      2,
      4.3,
      4
    ),
    (
      user_1_id,
      'Augsburger Maler',
      'augsburger-maler',
      maler_id,
      'Malerarbeiten in Augsburg und Umgebung. Innen- und Außenarbeiten, Tapezieren, Fassaden.',
      'Malerbetrieb in Augsburg',
      'Maximilianstraße 3',
      'Augsburg',
      '86150',
      'Bayern',
      48.3668,
      10.8987,
      '+49 821 9876543',
      'info@augsburger-maler.de',
      NULL,
      'https://images.unsplash.com/photo-1589939705384-5185137a7f0f?w=200',
      ARRAY[]::text[],
      NULL,
      false,
      'basic',
      'active',
      45,
      1,
      NULL,
      0
    )
  ON CONFLICT (slug) DO NOTHING;
END $$;

-- =====================================================
-- 3. CREATE SAMPLE REVIEWS
-- =====================================================

DO $$
DECLARE
  listing_1_id uuid;
  listing_2_id uuid;
  listing_3_id uuid;
  test_user_id uuid := '00000000-0000-0000-0000-000000000001';
BEGIN
  -- Get listing IDs
  SELECT id INTO listing_1_id FROM listings WHERE slug = 'munchner-elektro-service' LIMIT 1;
  SELECT id INTO listing_2_id FROM listings WHERE slug = 'bayern-sanitar-heizung' LIMIT 1;
  SELECT id INTO listing_3_id FROM listings WHERE slug = 'holzwerkstatt-munchen' LIMIT 1;

  -- Insert sample reviews
  IF listing_1_id IS NOT NULL THEN
    INSERT INTO reviews (listing_id, user_id, rating, title, comment, status, created_at)
    VALUES
      (listing_1_id, test_user_id, 5, 'Sehr zufrieden!', 'Schnelle und professionelle Arbeit. Der Elektriker war pünktlich und hat alles perfekt gemacht. Sehr empfehlenswert!', 'approved', now() - interval '10 days'),
      (listing_1_id, test_user_id, 5, 'Top Service', 'Ausgezeichneter Service, faire Preise. Werde definitiv wieder kommen.', 'approved', now() - interval '5 days'),
      (listing_1_id, test_user_id, 4, 'Gute Arbeit', 'Gute Arbeit, etwas teuer aber professionell.', 'approved', now() - interval '2 days')
    ON CONFLICT (listing_id, user_id) DO NOTHING;
  END IF;

  IF listing_2_id IS NOT NULL THEN
    INSERT INTO reviews (listing_id, user_id, rating, title, comment, status, created_at)
    VALUES
      (listing_2_id, test_user_id, 5, 'Perfekter Service', 'Unser Bad wurde komplett renoviert. Super Ergebnis, sehr zufrieden!', 'approved', now() - interval '15 days'),
      (listing_2_id, test_user_id, 5, 'Sehr empfehlenswert', 'Professionelle Arbeit, sauber und termingerecht. Top!', 'approved', now() - interval '8 days')
    ON CONFLICT (listing_id, user_id) DO NOTHING;
  END IF;

  IF listing_3_id IS NOT NULL THEN
    INSERT INTO reviews (listing_id, user_id, rating, title, comment, status, created_at)
    VALUES
      (listing_3_id, test_user_id, 5, 'Wunderschöne Küche', 'Unsere neue Küche ist genau wie gewünscht. Perfekte Beratung und Ausführung.', 'approved', now() - interval '20 days'),
      (listing_3_id, test_user_id, 4, 'Gute Qualität', 'Gute Arbeit, etwas länger gedauert als geplant aber Ergebnis ist super.', 'approved', now() - interval '12 days')
    ON CONFLICT (listing_id, user_id) DO NOTHING;
  END IF;
END $$;

-- =====================================================
-- 4. CREATE SAMPLE LEADS
-- =====================================================

DO $$
DECLARE
  listing_1_id uuid;
  listing_2_id uuid;
BEGIN
  SELECT id INTO listing_1_id FROM listings WHERE slug = 'munchner-elektro-service' LIMIT 1;
  SELECT id INTO listing_2_id FROM listings WHERE slug = 'bayern-sanitar-heizung' LIMIT 1;

  IF listing_1_id IS NOT NULL THEN
    INSERT INTO leads (listing_id, customer_name, customer_email, customer_phone, message, status, created_at)
    VALUES
      (listing_1_id, 'Thomas Müller', 'thomas.mueller@example.com', '+49 151 1234567', 'Hallo, ich benötige Hilfe bei der Installation einer neuen Steckdose. Können Sie mir einen Termin geben?', 'new', now() - interval '2 days'),
      (listing_1_id, 'Maria Schmidt', 'maria.schmidt@example.com', NULL, 'Ich suche einen Elektriker für Smart Home Installation. Bitte kontaktieren Sie mich.', 'contacted', now() - interval '5 days')
    ON CONFLICT DO NOTHING;
  END IF;

  IF listing_2_id IS NOT NULL THEN
    INSERT INTO leads (listing_id, customer_name, customer_email, customer_phone, message, status, created_at)
    VALUES
      (listing_2_id, 'Hans Weber', 'hans.weber@example.com', '+49 151 9876543', 'Unser Boiler funktioniert nicht mehr. Benötige dringend Hilfe!', 'new', now() - interval '1 day')
    ON CONFLICT DO NOTHING;
  END IF;
END $$;

-- =====================================================
-- NOTES
-- =====================================================
-- 1. The test user IDs are placeholders. In production, these would be
--    created automatically via Supabase Auth when users sign up.
--
-- 2. If you get errors about profiles not existing, you may need to:
--    - Create auth users first in Supabase Dashboard
--    - Or adjust the user IDs to match existing profiles
--
-- 3. Images use Unsplash URLs - these are placeholder images
--    In production, upload real images to Supabase Storage
--
-- 4. Some listings have different subscription tiers to test premium features
--
-- 5. Reviews are set to 'approved' status for immediate visibility
--    In production, they should start as 'pending' for moderation