CREATE TABLE IF NOT EXISTS customer_feedbacks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NULL,
  order_id BIGINT UNSIGNED NOT NULL,
  staff_user_id BIGINT UNSIGNED NOT NULL,
  satisfaction_status ENUM('puas', 'kurang', 'kurang_puas') NOT NULL,
  note TEXT NULL,
  follow_up_status VARCHAR(30) NULL,
  follow_up_note TEXT NULL,
  follow_up_by_user_id BIGINT UNSIGNED NULL,
  follow_up_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_customer_feedbacks_order (order_id),
  KEY idx_customer_feedbacks_tenant_created (tenant_id, created_at),
  KEY idx_customer_feedbacks_status (tenant_id, satisfaction_status, created_at),
  KEY idx_customer_feedbacks_staff (tenant_id, staff_user_id, created_at),
  KEY idx_customer_feedbacks_location (tenant_id, location_id, created_at),
  CONSTRAINT fk_customer_feedbacks_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE RESTRICT,
  CONSTRAINT fk_customer_feedbacks_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_customer_feedbacks_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_customer_feedbacks_staff FOREIGN KEY (staff_user_id) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT fk_customer_feedbacks_follow_up_by FOREIGN KEY (follow_up_by_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT chk_customer_feedbacks_note_required CHECK (
    satisfaction_status <> 'kurang_puas'
    OR (note IS NOT NULL AND CHAR_LENGTH(TRIM(note)) > 0)
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;