-- Audience/Campaign dynamic rules + customer label modal support
CREATE TABLE IF NOT EXISTS labels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  name VARCHAR(100) NOT NULL,
  color VARCHAR(20) DEFAULT '#1a3a6f',
  date_created DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_label_account_name (account_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS entity_labels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  entity_type VARCHAR(50) NOT NULL,
  entity_id INT NOT NULL,
  label_id INT NOT NULL,
  date_created DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_entity_label (account_id, entity_type, entity_id, label_id),
  KEY idx_entity (account_id, entity_type, entity_id),
  KEY idx_label (account_id, label_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE audience
  ADD COLUMN IF NOT EXISTS rules_json LONGTEXT NULL AFTER description;

ALTER TABLE campaign
  ADD COLUMN IF NOT EXISTS type_whatsapp TINYINT(1) NOT NULL DEFAULT 0 AFTER status,
  ADD COLUMN IF NOT EXISTS type_email TINYINT(1) NOT NULL DEFAULT 0 AFTER type_whatsapp,
  ADD COLUMN IF NOT EXISTS type_sms TINYINT(1) NOT NULL DEFAULT 0 AFTER type_email,
  ADD COLUMN IF NOT EXISTS target_type VARCHAR(30) NOT NULL DEFAULT 'audience' AFTER type_sms,
  ADD COLUMN IF NOT EXISTS audience_id INT NULL AFTER target_type,
  ADD COLUMN IF NOT EXISTS rules_json LONGTEXT NULL AFTER audience_id,
  ADD COLUMN IF NOT EXISTS message_whatsapp TEXT NULL AFTER rules_json,
  ADD COLUMN IF NOT EXISTS message_email TEXT NULL AFTER message_whatsapp,
  ADD COLUMN IF NOT EXISTS message_sms TEXT NULL AFTER message_email,
  ADD COLUMN IF NOT EXISTS email_subject VARCHAR(255) NULL AFTER message_sms;

CREATE TABLE IF NOT EXISTS campaign_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT NOT NULL,
  customer_id INT NOT NULL,
  type VARCHAR(30) NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'pending',
  response TEXT NULL,
  sent_at DATETIME NULL,
  date_created DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY idx_campaign (campaign_id),
  KEY idx_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- permissions for page aliases stay mapped to audience/campaigns, but this gives existing users full actions if missing
INSERT INTO user_permissions (account_id, user_id, page_key, action_key, allowed, created_at)
SELECT u.account_id, u.id, p.page_key, a.action_key, 1, NOW()
FROM users u
CROSS JOIN (SELECT 'audience' page_key UNION ALL SELECT 'campaigns') p
CROSS JOIN (SELECT 'view' action_key UNION ALL SELECT 'add' UNION ALL SELECT 'edit' UNION ALL SELECT 'delete') a
WHERE NOT EXISTS (
  SELECT 1 FROM user_permissions up
  WHERE up.account_id=u.account_id AND up.user_id=u.id AND up.page_key=p.page_key AND up.action_key=a.action_key
);
