-- Switch billing_communications uniqueness to (customer_account_id, billing_period)
-- so each customer_account row tracks its own send status, even when multiple
-- customer_accounts share the same billing_account_no.

-- 1. Drop the old key first; it would block the backfill below
ALTER TABLE billing_communications DROP INDEX uniq_account_month;

-- 2. Backfill: for every existing comm row, clone it to every sibling
--    customer_account that shares the same billing_account_no.
--    Rows with NULL billing_period are pre-schema legacy rows; skip them.
INSERT INTO billing_communications
  (customer_account_id, billing_account_no, month, billing_period,
   email_sent, email_sent_at, email_error, email_message_id, email_retry_count,
   whatsapp_sent, whatsapp_sent_at, whatsapp_error,
   sent_by, created_at, updated_at)
SELECT ca.id, bc.billing_account_no, bc.month, bc.billing_period,
       bc.email_sent, bc.email_sent_at, bc.email_error,
       bc.email_message_id, bc.email_retry_count,
       bc.whatsapp_sent, bc.whatsapp_sent_at, bc.whatsapp_error,
       bc.sent_by, bc.created_at, bc.updated_at
FROM billing_communications bc
JOIN customer_accounts ca
  ON ca.billing_account_no = bc.billing_account_no
WHERE bc.billing_period IS NOT NULL
  AND ca.id <> bc.customer_account_id
  AND NOT EXISTS (
    SELECT 1 FROM billing_communications bc2
    WHERE bc2.customer_account_id = ca.id
      AND bc2.billing_period = bc.billing_period
  );

-- 3. Add the new unique key
ALTER TABLE billing_communications
  ADD UNIQUE KEY uniq_customer_period (customer_account_id, billing_period);
