-- Create a table that tracks how each client surfaces catalog-backed items on a given form.
-- This lays the groundwork for building master service/product catalogs that can be
-- assigned to specific non-consent forms (starting with Diagnosis & Treatment).

CREATE TABLE IF NOT EXISTS client_form_catalog_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  form_template_id INT UNSIGNED NOT NULL,
  client_services_id BIGINT UNSIGNED NOT NULL,
  catalog_section_id BIGINT UNSIGNED DEFAULT NULL,
  placement_zone VARCHAR(50) NOT NULL DEFAULT 'services',
  display_order INT UNSIGNED NOT NULL DEFAULT 100,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  metadata_json JSON DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_client_form_service (client_id, form_template_id, client_services_id),
  KEY idx_client_form_catalog_items_form (client_id, form_template_id, display_order),
  KEY idx_client_form_catalog_items_section (client_id, form_template_id, catalog_section_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
