CREATE TABLE IF NOT EXISTS customer_contacts (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  job_title VARCHAR(120) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(60) NULL,
  mobile VARCHAR(60) NULL,
  contact_type ENUM('general', 'ordering', 'accounts', 'shipping', 'other') NOT NULL DEFAULT 'general',
  is_primary BOOLEAN NOT NULL DEFAULT FALSE,
  receives_invoices BOOLEAN NOT NULL DEFAULT FALSE,
  receives_order_updates BOOLEAN NOT NULL DEFAULT FALSE,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_customer_contact_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  INDEX idx_customer_contacts (customer_id, active, is_primary)
);

INSERT INTO customer_contacts
  (customer_id, name, email, phone, contact_type, is_primary,
   receives_invoices, receives_order_updates)
SELECT c.id, c.contact_name, c.email, c.phone, 'general', TRUE, TRUE, TRUE
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM customer_contacts cc WHERE cc.customer_id = c.id
);