-- =============================================================================
-- fix_larcare_allbets_tenant_ownership.sql — Moves the LarCare/allBETs Web
-- module data (dim_web_sites id 1, 2) from the wrong owner (tenant_id=1
-- "ABOS System Administration", business_id=4 "LarCare Services" — a stray
-- duplicate created by the original seed script's hardcoded
-- `SET @tenant_id := 1`) to the correct owner: tenant_id=2 ("About Stacie"),
-- business_id=2 (the tenant's own pre-existing "LarCare" business).
--
-- Root cause: the original seed_larcare_business_and_site.sql never checked
-- for an existing business under the real tenant, so it created a brand new
-- one under tenant 1 instead. Every Web-module child table was seeded off
-- that wrong tenant/business ever since, including allBETs (which reused
-- LarCare's business_id=4 as "the existing AB2 Global business" per that
-- seed file's own comment — also wrong, should have been tenant 2 as well).
--
-- Scope: every table with a site_id column, for site_id IN (1, 2), plus
-- dim_warehouses (site-scoped, not just business-scoped).
--
-- NOT idempotent by design (this is a one-time ownership correction, not a
-- repeatable migration) — safe to re-run only in the sense that re-running
-- it after it already succeeded is a no-op (WHERE tenant_id=1 won't match
-- anymore).
--
-- PRODUCTION-SPECIFIC — deliberately NOT registered in apply-migrations.sh's
-- FILES array. dim_web_sites id 1/2 and tenant/business id 1/2/4 are this
-- database's actual observed values; the shared dev database (db_abos_v0.1)
-- has different site ids for these same two tenants (confirmed: allBETs is
-- site 5 there, not 2) and was never affected by this bug in the first
-- place (it never went through the tenant-1 seed path). Running this
-- against a database where those ids mean something else would silently
-- corrupt unrelated data. Applied by hand once, directly, against
-- stacie_Aggie_v1.0 (2026-09-01) — kept here as a record, not a rerunnable step.
--
-- Take a fresh backup before running this. Deploy:
--   mysql -u stacie_aggie -p stacie_Aggie_v1.0 < db/fix_larcare_allbets_tenant_ownership.sql
-- =============================================================================

USE stacie_Aggie_v1.0;

START TRANSACTION;

UPDATE dim_web_sites SET tenant_id = 2, business_id = 2 WHERE id IN (1, 2) AND tenant_id = 1;

UPDATE dim_web_collections        SET tenant_id = 2, business_id = 2 WHERE site_id IN (1, 2) AND tenant_id = 1;
UPDATE dim_web_collection_fields  SET tenant_id = 2, business_id = 2 WHERE site_id IN (1, 2) AND tenant_id = 1;
UPDATE dim_web_customers          SET tenant_id = 2, business_id = 2 WHERE site_id IN (1, 2) AND tenant_id = 1;
UPDATE dim_web_forms              SET tenant_id = 2, business_id = 2 WHERE site_id IN (1, 2) AND tenant_id = 1;
UPDATE dim_web_media              SET tenant_id = 2, business_id = 2 WHERE site_id IN (1, 2) AND tenant_id = 1;
UPDATE dim_web_navigation_items   SET tenant_id = 2, business_id = 2 WHERE site_id IN (1, 2) AND tenant_id = 1;
UPDATE dim_web_pages              SET tenant_id = 2, business_id = 2 WHERE site_id IN (1, 2) AND tenant_id = 1;
UPDATE dim_web_products           SET tenant_id = 2, business_id = 2 WHERE site_id IN (1, 2) AND tenant_id = 1;
UPDATE dim_web_product_variants   SET tenant_id = 2, business_id = 2 WHERE site_id IN (1, 2) AND tenant_id = 1;
UPDATE dim_web_redirects          SET tenant_id = 2, business_id = 2 WHERE site_id IN (1, 2) AND tenant_id = 1;
UPDATE fact_web_carts             SET tenant_id = 2, business_id = 2 WHERE site_id IN (1, 2) AND tenant_id = 1;
UPDATE fact_web_entries           SET tenant_id = 2, business_id = 2 WHERE site_id IN (1, 2) AND tenant_id = 1;
-- fact_web_form_submissions has no tenant_id/business_id column — scoped by site_id only.
UPDATE fact_web_orders            SET tenant_id = 2, business_id = 2 WHERE site_id IN (1, 2) AND tenant_id = 1;
UPDATE dim_warehouses             SET tenant_id = 2, business_id = 2 WHERE site_id IN (1, 2) AND tenant_id = 1;

COMMIT;

-- Verification
SELECT 'dim_web_sites' AS tbl, id, tenant_id, business_id FROM dim_web_sites WHERE id IN (1,2)
UNION ALL
SELECT 'dim_web_products', COUNT(*), tenant_id, business_id FROM dim_web_products WHERE site_id IN (1,2) GROUP BY tenant_id, business_id
UNION ALL
SELECT 'dim_web_product_variants', COUNT(*), tenant_id, business_id FROM dim_web_product_variants WHERE site_id IN (1,2) GROUP BY tenant_id, business_id
UNION ALL
SELECT 'dim_web_forms', COUNT(*), tenant_id, business_id FROM dim_web_forms WHERE site_id IN (1,2) GROUP BY tenant_id, business_id
UNION ALL
SELECT 'fact_web_entries', COUNT(*), tenant_id, business_id FROM fact_web_entries WHERE site_id IN (1,2) GROUP BY tenant_id, business_id
UNION ALL
SELECT 'dim_web_navigation_items', COUNT(*), tenant_id, business_id FROM dim_web_navigation_items WHERE site_id IN (1,2) GROUP BY tenant_id, business_id
UNION ALL
SELECT 'dim_web_collections', COUNT(*), tenant_id, business_id FROM dim_web_collections WHERE site_id IN (1,2) GROUP BY tenant_id, business_id
UNION ALL
SELECT 'dim_web_pages', COUNT(*), tenant_id, business_id FROM dim_web_pages WHERE site_id IN (1,2) GROUP BY tenant_id, business_id
UNION ALL
SELECT 'dim_web_media', COUNT(*), tenant_id, business_id FROM dim_web_media WHERE site_id IN (1,2) GROUP BY tenant_id, business_id
UNION ALL
SELECT 'dim_warehouses', COUNT(*), tenant_id, business_id FROM dim_warehouses WHERE site_id IN (1,2) GROUP BY tenant_id, business_id;