-- Support Templates for configurable intake flows

START TRANSACTION;

CREATE TABLE IF NOT EXISTS `support_templates` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NOT NULL,
  `target_group` VARCHAR(20) NOT NULL, -- 'pruefer' | 'admin'
  `topic_key` VARCHAR(80) NOT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_template_topic` (`target_group`, `topic_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `support_template_steps` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `template_id` INT NOT NULL,
  `step_order` INT NOT NULL DEFAULT 1,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_steps_template` (`template_id`),
  CONSTRAINT `fk_steps_template` FOREIGN KEY (`template_id`) REFERENCES `support_templates`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `support_template_questions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `template_id` INT NOT NULL,
  `step_id` INT NULL,
  `question_key` VARCHAR(80) NOT NULL,
  `label` VARCHAR(255) NOT NULL,
  `type` ENUM('text','textarea','select','file') NOT NULL DEFAULT 'text',
  `options_json` TEXT NULL,
  `required` TINYINT(1) NOT NULL DEFAULT 0,
  `placeholder` VARCHAR(255) NULL,
  `help_text` VARCHAR(255) NULL,
  `q_order` INT NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `idx_q_template` (`template_id`),
  KEY `idx_q_step` (`step_id`),
  CONSTRAINT `fk_q_template` FOREIGN KEY (`template_id`) REFERENCES `support_templates`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_q_step` FOREIGN KEY (`step_id`) REFERENCES `support_template_steps`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `support_intake_submissions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `template_id` INT NOT NULL,
  `answers_json` LONGTEXT NOT NULL,
  `conversation_id` INT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sub_template` (`template_id`),
  KEY `idx_sub_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;
