-- Phase D trial setup for WR (Kiecemon Walker, hotsteeling@gmail.com).
-- Slug: wr
-- Existing customer id: 831266dc-5c18-4849-996d-5e5e717031b2
--
-- Background: Kiecemon paid via Stripe (which auto-provisioned this
-- trial row with default branding/pricing) then completed the
-- customer-setup form. The form POSTs to GHL only and never updates
-- public.customers, so this seed lands his data into branding_json /
-- pricing_json. It also claims any product photos and service area
-- polygon he uploaded during setup.
--
-- Uses INSERT...ON CONFLICT (slug) DO UPDATE so trial-only fields
-- (status, plan, stripe_customer_id, owner_user_id, first_name,
-- created_at, attribution columns, health) are preserved. The SET
-- clause only touches business_name, ghl_webhook_url, branding_json,
-- pricing_json.

-- ============================================================
-- STEP 0: VERIFY the trial row exists with the matching slug.
--          Run this SELECT first; do NOT run STEP 1 if it returns
--          0 rows or a different id/status than expected.
-- ============================================================
select id, slug, status, business_name, plan, stripe_customer_id, created_at
  from public.customers
 where slug = 'wr';
-- Expected: 1 row.
--   id                = '831266dc-5c18-4849-996d-5e5e717031b2'
--   slug              = 'wr'
--   status            = 'trial'
--   plan              = 'pro' (Stripe webhook default; preserved)
--   stripe_customer_id = populated, do NOT lose this
--
-- If the id differs, the customer changed the URL or signed up twice.
-- Stop and reconcile before running STEP 1.

-- ============================================================
-- STEP 1: SEED the customer config. Idempotent via ON CONFLICT.
-- ============================================================
insert into public.customers (
  slug,
  business_name,
  status,
  plan,
  ghl_webhook_url,
  branding_json,
  pricing_json
) values (
  'wr',
  'WR',
  'trial',
  'pro',
  '',
  '{
    "company_name":      "WR",
    "company_phone":     "4436307285",
    "company_email":     "hotsteeling@gmail.com",
    "service_area":      "Baltimore and surrounding areas DMV",
    "notify_email":      "hotsteeling@gmail.com",
    "notify_phone":      "4436307285",
    "brand_color":       "#2d5016",
    "brand_color_light": "#4A7C22",
    "logo_url":          "",
    "logo_style":        "",
    "warranty_badges":   ["3-Year Workmanship Warranty", "Licensed & Insured", "Free On-Site Quotes"],
    "crm": {"type": "none", "endpoint": "https://app.jobnimbus.com/api1/contacts", "api_key": ""}
  }'::jsonb,
  '{
    "gate_cost":     350,
    "gate_double":   1000,
    "teardown_rate": 10,
    "min_ft":        150,
    "min_fee":       2500,
    "products": [
      {
        "key": "wood-privacy",
        "name": "Wood Privacy",
        "material": "Wood",
        "desc": "Wood fence with vertical boards. Most common pick for backyards.",
        "heights": [6],
        "default_height": 6,
        "prices_by_height": {"6": {"lo": 35, "hi": 55}},
        "enabled": true,
        "photo_url": null
      },
      {
        "key": "chain-link",
        "name": "Chain Link",
        "material": "Chain Link",
        "desc": "Chain link fence. The most budget-friendly option.",
        "heights": [4, 6],
        "default_height": 4,
        "prices_by_height": {"4": {"lo": 15, "hi": 25}, "6": {"lo": 26, "hi": 34}},
        "enabled": true,
        "photo_url": null
      },
      {
        "key": "vinyl-privacy",
        "name": "Vinyl Privacy",
        "material": "Vinyl",
        "desc": "Vinyl fence. No paint or stain needed.",
        "heights": [6],
        "default_height": 6,
        "prices_by_height": {"6": {"lo": 45, "hi": 75}},
        "enabled": true,
        "photo_url": null
      }
    ]
  }'::jsonb
)
on conflict (slug) do update set
  business_name   = excluded.business_name,
  ghl_webhook_url = excluded.ghl_webhook_url,
  branding_json   = excluded.branding_json,
  pricing_json    = excluded.pricing_json;

-- ============================================================
-- STEP 2: CLAIM any product photos uploaded during setup.
--          Idempotent: no-op if WR uploaded none. The GHL email
--          does not surface the photo upload list, so this runs
--          unconditionally and STEP 4 reports the result.
-- ============================================================
update public.product_images
   set customer_id  = (select id from public.customers where slug = 'wr'),
       pending_slug = null,
       claimed_at   = now()
 where pending_slug = 'wr' and customer_id is null;

-- ============================================================
-- STEP 3: CLAIM any service area polygon uploaded during setup.
--          Mirrors the runbook step from estimator/RUNBOOK.md.
--          Idempotent: no-op if WR did not draw a polygon. The
--          GHL email does not surface service_area_id /
--          service_area_error, so this runs unconditionally and
--          STEP 4 reports the result.
-- ============================================================
update public.service_areas
   set customer_id  = (select id from public.customers where slug = 'wr'),
       pending_slug = null,
       claimed_at   = now()
 where pending_slug = 'wr' and customer_id is null;

-- ============================================================
-- STEP 4: DIAGNOSE what landed. Closes the gap from the GHL
--          email being silent on photos and service area.
--          Expected: both *_pending_remaining counts = 0,
--          and the final customers row matches STEP 0.
-- ============================================================
select count(*) as photos_pending_remaining
  from public.product_images
 where pending_slug = 'wr' and customer_id is null;

select count(*) as photos_claimed
  from public.product_images
 where customer_id = (select id from public.customers where slug = 'wr');

select count(*) as service_areas_pending_remaining
  from public.service_areas
 where pending_slug = 'wr' and customer_id is null;

select count(*)              as service_areas_claimed,
       bool_or(is_primary)   as has_primary
  from public.service_areas
 where customer_id = (select id from public.customers where slug = 'wr');

select id, slug, status, plan, business_name, stripe_customer_id
  from public.customers
 where slug = 'wr';
-- Expected: status preserved as 'trial', plan preserved as 'pro',
-- business_name='WR', stripe_customer_id unchanged from STEP 0.
--
-- Smoke test (after deploy):
--   https://nextestimateagency.com/e/wr
