schema / migration / 014-drop-proshop-rent-group-ops-cleanup

014 - Drop misleading ProShop "Rent" group + ops cleanup

  • Tanggal: 2026-08-11
  • DB: mpcdb (+ ksrdb sample review sync notes)
  • Ringkas: Hapus item_groups GRP-MER-RNT (nama Rent tapi product_type=ProShop, 0 pakai). Hard-delete POS device revoked/debug. Link store_settings.default_branch_id. Seed review sample bila kosong.

ALTER (jalankan di DB server)

-- 1) Null any accidental FKs then drop misleading group (and empty child cats if any)
UPDATE products SET group_id = NULL
WHERE group_id IN (SELECT id FROM item_groups WHERE code = 'GRP-MER-RNT' OR (name = 'Rent' AND product_type = 'ProShop'));

UPDATE item_categories SET group_id = NULL
WHERE group_id IN (SELECT id FROM item_groups WHERE code = 'GRP-MER-RNT' OR (name = 'Rent' AND product_type = 'ProShop'));

DELETE FROM item_groups
WHERE code = 'GRP-MER-RNT'
   OR (name = 'Rent' AND product_type = 'ProShop');

-- Optional: proper empty groups for product_type = Rent (menu cascade)
INSERT INTO item_groups (id, code, name, product_type, display_order, created_at, updated_at)
SELECT gen_random_uuid(), 'GRP-RENT-GEAR', 'Rental Gear', 'Rent', 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM item_groups WHERE code = 'GRP-RENT-GEAR');

-- 2) Hard-delete non-prod / revoked debug devices
DELETE FROM pos_devices
WHERE status = 'Revoked'
   OR deleted_at IS NOT NULL
   OR lower(name) LIKE '%debug%'
   OR lower(name) LIKE '%smoke%';

-- 3) store_settings → default branch
ALTER TABLE store_settings ADD COLUMN IF NOT EXISTS default_branch_id uuid REFERENCES branches(id);
UPDATE store_settings s
SET default_branch_id = b.id
FROM branches b
WHERE s.default_branch_id IS NULL
  AND b.deleted_at IS NULL
  AND (b.is_default = true OR b.code = 'MAIN');

Seed review (opsional, bila customer_reviews kosong)

INSERT INTO customer_reviews (id, code, customer_id, booking_id, rating, comments, created_at, updated_at)
SELECT gen_random_uuid(), 'REV-01', b.customer_id, b.id, 5, 'Excellent evening session.', NOW(), NOW()
FROM bookings b
WHERE b.status = 'Completed' AND b.customer_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM customer_reviews WHERE code = 'REV-01')
LIMIT 1;

INSERT INTO review_answers (id, code, review_id, question_id, answer, created_at, updated_at)
SELECT gen_random_uuid(), 'RA-01', r.id, q.id, '5', NOW(), NOW()
FROM customer_reviews r
CROSS JOIN review_questions q
WHERE r.code = 'REV-01' AND q.code = 'RQ-01'
  AND NOT EXISTS (SELECT 1 FROM review_answers WHERE code = 'RA-01');

ksrdb fact_review (setelah ada customer_reviews)

Sync via ksr-sync job, atau insert manual mirror source_review_id.

Rollback

INSERT INTO item_groups (id, code, name, product_type, display_order, created_at, updated_at)
VALUES (gen_random_uuid(), 'GRP-MER-RNT', 'Rent', 'ProShop', 2, NOW(), NOW());
-- devices: restore from backup
ALTER TABLE store_settings DROP COLUMN IF EXISTS default_branch_id;

Catatan

  • item_groups / item_categories / item_price_overrides tidak di-rename.
  • Produk sewa tetap products.product_type = 'Rent' (bukan nama group).