`data/update_credit.sql`

103 lines
-- Quadrille room-setting thin-credit enrichment (DTD verdict A, 3/3)
-- Additive: updates credit/richness/sub-fields inside EXISTING jsonb objects.
-- Idempotent (re-running yields same result). BEGIN...ROLLBACK dry-run.
\set ON_ERROR_STOP on
BEGIN;

CREATE TEMP TABLE _enrich(key text PRIMARY KEY, credit text, richness text, designer text, photographer text, publication text, date text) ON COMMIT DROP;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/abaco_stripe_shade_and_pillow_meg_braff_the_decorated_home_pc_1448.jpg', 'Meg Braff (As Seen in The Decorated Home)', 'rich', 'Meg Braff', NULL, 'The Decorated Home', NULL) ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/arbre_de_matisse_pillows_at_home_fairfield_county_fall_2007_pc_819.jpg', 'As Seen in At Home Fairfield County Fall 2007', 'medium', NULL, NULL, 'At Home Fairfield County', 'Fall 2007') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/arbre_de_matisse_reverse_sofa_new_york_spaces_august_2013_pc_844.jpg', 'As Seen in New York Spaces August 2013', 'medium', NULL, NULL, 'New York Spaces', 'August 2013') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Arbre-de-Matisse-Reverse-wallpaper-Martha-Stewart-Living-September-2011.jpg', 'As Seen in Martha Stewart Living September 2011', 'medium', NULL, NULL, 'Martha Stewart Living', 'September 2011') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/bali_diamond_pillows_meg_braff_the_decorated_home_pc_1256.jpg', 'Meg Braff (As Seen in The Decorated Home)', 'rich', 'Meg Braff', NULL, 'The Decorated Home', NULL) ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/bali_ii_chairs_liza_pulitzer_calhoun_flower_magazine_august_2015_pc_1376.jpg', 'Liza Pulitzer Calhoun (As Seen in Flower Magazine August 2015)', 'rich', 'Liza Pulitzer Calhoun', NULL, 'Flower Magazine', 'August 2015') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/bali_ii_wallpaper_bali_isle_headboard_chiqui_and_nena_woolworthelle_decor_june_2009_pc_1372.jpg', 'Chiqui Nena Woolworth (As Seen in Elle Decor June 2009)', 'rich', 'Chiqui Nena Woolworth', NULL, 'Elle Decor', 'June 2009') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Bali-II-wallpaper-Bali-Isle-headboard-Chiqui-and-Nena-WoolworthElle-Decor-June-2009.jpg', 'Chiqui Nena Woolworth (As Seen in Elle Decor June 2009)', 'rich', 'Chiqui Nena Woolworth', NULL, 'Elle Decor', 'June 2009') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/birds_ii_susanne_morgan_at_home_fairfield_county_fall_2006_pc_1545.jpg', 'Susanne Morgan (As Seen in At Home Fairfield County Fall 2006)', 'rich', 'Susanne Morgan', NULL, 'At Home Fairfield County', 'Fall 2006') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Brenta-Wallpaper-Martha-Stewart-Living-September-2011.jpg', 'As Seen in Martha Stewart Living September 2011', 'medium', NULL, NULL, 'Martha Stewart Living', 'September 2011') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/climbing_hydrangea_wallpaper_new_batik_cushions_terrace_tablecloth_samantha_williams_pasadena_showhouse_2025_pc_923.jpg', 'Samantha Williams (As Seen in Pasadena Showhouse 2025)', 'rich', 'Samantha Williams', NULL, 'Pasadena Showhouse', '2025') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Climbing-Hydrangea-wallpaper-New-Batik-cushions-Terrace-tablecloth-Samantha-Williams-Pasadena-showhouse-2025.jpg', 'Samantha Williams (As Seen in Pasadena Showhouse 2025)', 'rich', 'Samantha Williams', NULL, 'Pasadena Showhouse', '2025') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/henriot_floral_chair_nitik_ii_bed_phoebe_howard_southern_accents_july_2008_pc_1196.jpg', 'Phoebe Howard (As Seen in Southern Accents July 2008)', 'rich', 'Phoebe Howard', NULL, 'Southern Accents', 'July 2008') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/indramayu_beds_elizabeth_dexter_serendipity_may_2016_pc_1532.jpg', 'Elizabeth Dexter (As Seen in Serendipity May 2016)', 'rich', 'Elizabeth Dexter', NULL, 'Serendipity', 'May 2016') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/island_ikat_chairs_emily_ruddo_high_gloss_magazine_july_2011_pc_940.jpg', 'Emily Ruddo (As Seen in High Gloss Magazine July 2011)', 'rich', 'Emily Ruddo', NULL, 'High Gloss Magazine', 'July 2011') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/java_java_edo_ziggurat_anne_hepfer_canadian_house_and_home_april_2011_pc_1126.jpg', 'Anne Hepfer (As Seen in Canadian House and Home April 2011)', 'rich', 'Anne Hepfer', NULL, 'Canadian House and Home', 'April 2011') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Java-Java-wallpaper-In-Style-April-2010.jpg', 'As Seen in In Style April 2010', 'medium', NULL, NULL, 'In Style', 'April 2010') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/lyford_background_chair_and_pillows_johnson_vann_interiors_atlanta_magazine_april_2019_pc_1291.jpg', 'Johnson Vann (As Seen in Atlanta Magazine April 2019)', 'rich', 'Johnson Vann', NULL, 'Atlanta Magazine', 'April 2019') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/lyford_print_curtains_and_bed_canopy_barry_dixon_metropolitan_home_june_2008_pc_1309.jpg', 'Barry Dixon (As Seen in Metropolitan Home June 2008)', 'rich', 'Barry Dixon', NULL, 'Metropolitan Home', 'June 2008') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/macambo_curtains_and_pillows_aga_bed_dana_small_classic_home_summer_2018_pc_1097.jpg', 'Dana Small (As Seen in Classic Home Summer 2018)', 'rich', 'Dana Small', NULL, 'Classic Home', 'Summer 2018') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Mojave-wallpaper-Christpher-Maya-New-York-Spaces.jpg', 'Christopher Maya (As Seen in New York Spaces)', 'rich', 'Christopher Maya', NULL, 'New York Spaces', NULL) ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/nitik_ii_java_java_melong_batik_pillows_victoria_sanchez_dc_design_house_2014_pc_931.jpg', 'Victoria Sanchez (As Seen in DC Design House 2014)', 'rich', 'Victoria Sanchez', NULL, 'DC Design House', '2014') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Potalla-wallpaper-Southern-Accents-January-2007.jpg', 'As Seen in Southern Accents January 2007', 'medium', NULL, NULL, 'Southern Accents', 'January 2007') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/San-Marco-Reverse-wallpaper-Marika-Meyer-DC-Design-House-2014.jpg', 'Marika Meyer (As Seen in DC Design House 2014)', 'rich', 'Marika Meyer', NULL, 'DC Design House', '2014') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Sigourney-wallpaper-Sigourney-Small-Scale-curtains-DC-Design-House-2017-Home-On-Cameron.jpg', 'As Seen in DC Design House 2017', 'medium', NULL, NULL, 'DC Design House', '2017') ON CONFLICT (key) DO NOTHING;
INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/ziggurat_chair_new_york_spaces_november_2008_pc_1215.jpg', 'Celerie Kemble (As Seen in New York Spaces November 2008)', 'rich', 'Celerie Kemble', NULL, 'New York Spaces', 'November 2008') ON CONFLICT (key) DO NOTHING;

-- BEFORE: how many jsonb objects match an enrich key, by table
SELECT 'china_seas_catalog matching-objects (before)' AS metric, count(*) FROM china_seas_catalog t,
  jsonb_array_elements(t.room_setting_images_credited) obj
  JOIN _enrich e ON e.key = replace(obj->>'url','-sm-thumb','');
SELECT 'quadrille_house_catalog matching-objects (before)' AS metric, count(*) FROM quadrille_house_catalog t,
  jsonb_array_elements(t.room_setting_images_credited) obj
  JOIN _enrich e ON e.key = replace(obj->>'url','-sm-thumb','');


UPDATE china_seas_catalog t SET room_setting_images_credited = (
  SELECT jsonb_agg(
    CASE WHEN e.key IS NOT NULL THEN
      obj
        || jsonb_build_object('credit', e.credit)
        || jsonb_build_object('richness', e.richness)
        || jsonb_build_object('designer', to_jsonb(e.designer))
        || jsonb_build_object('photographer', to_jsonb(e.photographer))
        || jsonb_build_object('publication', to_jsonb(e.publication))
        || jsonb_build_object('date', to_jsonb(e.date))
    ELSE obj END
    ORDER BY ord
  )
  FROM jsonb_array_elements(t.room_setting_images_credited) WITH ORDINALITY AS a(obj, ord)
  LEFT JOIN _enrich e ON e.key = replace(obj->>'url','-sm-thumb','')
)
WHERE EXISTS (
  SELECT 1 FROM jsonb_array_elements(t.room_setting_images_credited) o2
  JOIN _enrich e2 ON e2.key = replace(o2->>'url','-sm-thumb','')
);

UPDATE quadrille_house_catalog t SET room_setting_images_credited = (
  SELECT jsonb_agg(
    CASE WHEN e.key IS NOT NULL THEN
      obj
        || jsonb_build_object('credit', e.credit)
        || jsonb_build_object('richness', e.richness)
        || jsonb_build_object('designer', to_jsonb(e.designer))
        || jsonb_build_object('photographer', to_jsonb(e.photographer))
        || jsonb_build_object('publication', to_jsonb(e.publication))
        || jsonb_build_object('date', to_jsonb(e.date))
    ELSE obj END
    ORDER BY ord
  )
  FROM jsonb_array_elements(t.room_setting_images_credited) WITH ORDINALITY AS a(obj, ord)
  LEFT JOIN _enrich e ON e.key = replace(obj->>'url','-sm-thumb','')
)
WHERE EXISTS (
  SELECT 1 FROM jsonb_array_elements(t.room_setting_images_credited) o2
  JOIN _enrich e2 ON e2.key = replace(o2->>'url','-sm-thumb','')
);

-- AFTER: objects whose credit now equals the enriched credit
SELECT 'china_seas_catalog objects-updated (after)' AS metric, count(*) FROM china_seas_catalog t,
  jsonb_array_elements(t.room_setting_images_credited) obj
  JOIN _enrich e ON e.key = replace(obj->>'url','-sm-thumb','')
  WHERE obj->>'credit' = e.credit AND obj->>'richness' = e.richness;
SELECT 'quadrille_house_catalog objects-updated (after)' AS metric, count(*) FROM quadrille_house_catalog t,
  jsonb_array_elements(t.room_setting_images_credited) obj
  JOIN _enrich e ON e.key = replace(obj->>'url','-sm-thumb','')
  WHERE obj->>'credit' = e.credit AND obj->>'richness' = e.richness;

-- Sample of updated objects for eyeball verification
SELECT obj->>'url' url, obj->>'credit' credit, obj->>'richness' richness,
  obj->>'publication' publication, obj->>'date' date
FROM china_seas_catalog t, jsonb_array_elements(t.room_setting_images_credited) obj
JOIN _enrich e ON e.key = replace(obj->>'url','-sm-thumb','')
WHERE obj->>'credit' = e.credit LIMIT 8;

ROLLBACK;  -- DRY RUN. Change to COMMIT; once counts verified.