-- Form layout template support for cloning catalog selections to new clients.
-- Creates tables:
--  - catalog_form_templates: template metadata (per form_template_id)
--  - catalog_form_template_items: captures catalog/custom items chosen for the template
--  - catalog_form_template_layout: preserves placement/order metadata
--  - catalog_form_template_consents: stores consent assignments for template services

CREATE TABLE IF NOT EXISTS catalog_form_templates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  slug VARCHAR(160) NOT NULL,
  form_template_id INT NOT NULL,
  description TEXT DEFAULT NULL,
  created_by INT 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_template_slug (slug),
  KEY idx_template_form (form_template_id),
  KEY idx_template_created_by (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_form_template_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  template_id BIGINT UNSIGNED NOT NULL,
  item_key VARCHAR(190) NOT NULL,
  source_type ENUM('catalog','custom') NOT NULL DEFAULT 'catalog',
  catalog_item_id INT DEFAULT NULL,
  services_template_id INT DEFAULT NULL,
  service_type VARCHAR(30) DEFAULT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT DEFAULT NULL,
  catalog_section_id INT DEFAULT NULL,
  catalog_section_key VARCHAR(150) DEFAULT NULL,
  metadata_json JSON DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  custom_price DECIMAL(10,2) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_template_items_template FOREIGN KEY (template_id) REFERENCES catalog_form_templates(id) ON DELETE CASCADE,
  KEY idx_template_items_template (template_id),
  KEY idx_template_items_catalog (catalog_item_id),
  KEY idx_template_items_services (services_template_id),
  UNIQUE KEY uniq_template_item_key (template_id, item_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_form_template_layout (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  template_id BIGINT UNSIGNED NOT NULL,
  template_item_id BIGINT UNSIGNED DEFAULT NULL,
  placement_zone VARCHAR(50) NOT NULL DEFAULT 'services',
  catalog_section_id INT DEFAULT NULL,
  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,
  CONSTRAINT fk_template_layout_template FOREIGN KEY (template_id) REFERENCES catalog_form_templates(id) ON DELETE CASCADE,
  CONSTRAINT fk_template_layout_item FOREIGN KEY (template_item_id) REFERENCES catalog_form_template_items(id) ON DELETE SET NULL,
  KEY idx_template_layout_template (template_id),
  KEY idx_template_layout_zone (placement_zone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_form_template_consents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  template_id BIGINT UNSIGNED NOT NULL,
  template_item_id BIGINT UNSIGNED DEFAULT NULL,
  services_template_id INT DEFAULT NULL,
  form_template_id INT NOT NULL,
  consent_form_key VARCHAR(190) NOT NULL,
  required TINYINT(1) NOT NULL DEFAULT 1,
  sign_order INT UNSIGNED DEFAULT NULL,
  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,
  CONSTRAINT fk_template_consents_template FOREIGN KEY (template_id) REFERENCES catalog_form_templates(id) ON DELETE CASCADE,
  CONSTRAINT fk_template_consents_item FOREIGN KEY (template_item_id) REFERENCES catalog_form_template_items(id) ON DELETE SET NULL,
  KEY idx_template_consents_template (template_id),
  KEY idx_template_consents_form (form_template_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
