Marketing populate

marketing.sql

do $$
  begin
    for i in 1..10 loop
      INSERT INTO public.providers (name, description, address, online_booking, cancellation_policy, cancellation_time, website, contact_numbers, currency_code, country_code, time_zone, created_at, updated_at, print_receipts, receipt_message, prices_include_tax, beginning_of_week, default_calendar_view, time_slot_minutes, sms_enabled, business_type_id, widget_allows_employee_selection, widget_max_advance_time_in_months, commission_before_discount, commission_after_tax, time_format, voucher_expiration_period, locale, payments_activated, marketplace_enabled, marketplace_info, checkout_version, sale_custom_header_1, sale_custom_header_2, sale_show_customer_info, new_appointment_modal, new_appointment_summary, advance_notice_time_in_seconds, appointment_color_source, custom_invoice_title, payments_coming_soon, payments_enabled, new_taxes, sale_show_customer_address, zendesk_enabled, payments_enabled_by_provider, hidden_from_marketplace, payments_required, payment_due, new_calendar_enabled, marketing_enabled, merge_auto_enabled, merge_manual_enabled, new_global_search_enabled, use_customer_label_accumulator, new_staff_enabled, reviews_enabled, new_sales_enabled, cancellation_and_no_show_enabled, new_setup_enabled, block_client_enabled, marketplace_appointment_start_interval, slug, allow_id_lookup) VALUES ('Fresha-random-' || i, NULL, NULL, true, NULL, '0', NULL, NULL, 'AED', 'AE', 
        (select unnest from unnest(array['America/Chicago', 'Australia/Lord_Howe', 'America/New_York', 'Asia/Dubai', 'Pacific/Auckland', 'Europe/Warsaw', 'Asia/Tokyo', 'Asia/Ho_Chi_Minh', 'Asia/Amman', 'Asia/Seoul', 'Europe/London', 'Atlantic/Reykjavik', 'Asia/Shanghai', 'Africa/Cairo']) order by random() limit 1),
        '2018-12-12 08:35:24.368662', '2018-12-12 08:35:24.368662', false, NULL, false, 'sunday', 'day', 15, true, 15, true, 5, false, false, 0, 3, 0, true, true, NULL, 1, NULL, NULL, true, true, true, 0, 0, 'Invoice', false, true, true, true, true, true, NULL, true, false, true, true, true, true, true, false, false, true, false, true, false, false, 15, NULL, true);

      begin
        for r in 1..(((select count(id) from providers)) % 100 + 3) loop

          INSERT INTO customers(email, created_at,updated_at,provider_id,enable_email_notifications,
            telephone,first_name,last_name,normalized_contact_number,enable_sms_notifications,accepts_marketing_notifications, blocked)
          VALUES (
            concat(MD5(random()::text),'@example.com'),
            now(),
            now(),
            --floor(random() * 100000 + 1)::int,
            (select id from providers order by id desc limit 1),
            't',
            '+1234512',
            'John' || i || r,
            'Doe' || i || r,
            '1234512345',
            'f',
            (r % 50) != 0,
            (r % 77) != 0
          );
        end loop;
      end;

INSERT INTO marketing_campaigns (provider_id, campaign_category, status, created_at, updated_at) VALUES ((select id from providers order by id desc limit 1), 0, (i % 2), NOW(),NOW());
INSERT INTO marketing_campaign_settings (provider_id, marketing_campaign_id, employee_id, campaign_category, discounts_enabled, current, value, discount_type, "limit", valid_for, created_at, updated_at, all_services) 
VALUES ((select id from providers order by id desc limit 1), (select id from marketing_campaigns order by id desc limit 1), 1, 0, 't', 't', 10.00, 0, NULL, 21, NOW(), NOW(), true);

INSERT INTO marketing_campaigns (provider_id, campaign_category, status, created_at, updated_at) VALUES ((select id from providers order by id desc limit 1), 1, (i % 2), NOW(),NOW());
INSERT INTO marketing_campaign_settings (provider_id, marketing_campaign_id, employee_id, campaign_category, discounts_enabled, current, value, discount_type, "limit", valid_for, created_at, updated_at, all_services) 
VALUES ((select id from providers order by id desc limit 1), (select id from marketing_campaigns order by id desc limit 1), 1, 1, 't', 't', 10.00, 0, NULL, 21, NOW(), NOW(), true);

INSERT INTO marketing_campaigns (provider_id, campaign_category, status, created_at, updated_at) VALUES ((select id from providers order by id desc limit 1), 2, (i % 2), NOW(),NOW());
INSERT INTO marketing_campaign_settings (provider_id, marketing_campaign_id, employee_id, campaign_category, discounts_enabled, current, value, discount_type, "limit", valid_for, created_at, updated_at, all_services) 
VALUES ((select id from providers order by id desc limit 1), (select id from marketing_campaigns order by id desc limit 1), 1, 2, 't', 't', 10.00, 0, NULL, 21, NOW(), NOW(), true);

      begin
        for r in 1..((select count(id) from providers) % 600) loop

          INSERT INTO marketing_offers (provider_id, marketing_campaign_settings_id, recipient_email, token, valid_to, created_at, updated_at, status) 
          VALUES (
            (select id from providers order by id desc limit 1),
            (select id from marketing_campaign_settings where provider_id = (select id from providers order by id desc limit 1) order by random() desc limit 1),
            (select email from customers where provider_id = (select id from providers order by id desc limit 1) order by random() desc limit 1), 
            MD5(random()::text) || r || i, '2018-12-12 08:40:47.081185',
            (select NOW() + (random() * (NOW()+'190 days' - NOW())) + '-300 days'),
            '2018-12-12 08:40:47.081185', 1
          );

        end loop;
      end;


    end loop;
  end;
  $$;