-- Monzer V5 fixes
-- 1) Track when each audience customer was first added and last refreshed
ALTER TABLE audience_customers ADD COLUMN created_at DATETIME NULL AFTER date_created;
ALTER TABLE audience_customers ADD COLUMN updated_at DATETIME NULL AFTER created_at;

UPDATE audience_customers
SET created_at = COALESCE(created_at, date_created, NOW()),
    updated_at = COALESCE(updated_at, date_created, NOW());

-- 2) Keep campaign counts in sync after old data
UPDATE audience a
SET customer_count = (SELECT COUNT(*) FROM audience_customers ac WHERE ac.audience_id = a.id)
WHERE customer_count IS NULL OR customer_count = 0;

UPDATE campaign c
SET customer_count = CASE
  WHEN c.audience_id IS NOT NULL THEN (SELECT COUNT(*) FROM audience_customers ac WHERE ac.audience_id = c.audience_id)
  ELSE customer_count
END
WHERE customer_count IS NULL OR customer_count = 0;
