-- Contact Groups CRM

CREATE TABLE IF NOT EXISTS contact_groups (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(180) NOT NULL,
  group_name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  owner_id INT NOT NULL,
  created_by INT NOT NULL,
  updated_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  UNIQUE KEY uq_contact_groups_slug (slug, deleted_at),
  INDEX idx_contact_groups_owner (owner_id),
  INDEX idx_contact_groups_name (group_name),
  CONSTRAINT fk_cg_owner FOREIGN KEY (owner_id) REFERENCES users(id),
  CONSTRAINT fk_cg_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_cg_updated_by FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS contact_group_members (
  group_id INT NOT NULL,
  contact_id INT NOT NULL,
  added_by INT NOT NULL,
  added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (group_id, contact_id),
  INDEX idx_cgm_contact (contact_id),
  CONSTRAINT fk_cgm_group FOREIGN KEY (group_id) REFERENCES contact_groups(id) ON DELETE CASCADE,
  CONSTRAINT fk_cgm_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
  CONSTRAINT fk_cgm_added_by FOREIGN KEY (added_by) REFERENCES users(id)
);