-- Добавяне на таблици за шаблони на протоколи

-- Таблица за шаблони на протоколи
CREATE TABLE IF NOT EXISTS `protocol_templates` (
  `template_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `created_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`template_id`),
  KEY `fk_protocol_templates_users` (`created_by`),
  CONSTRAINT `fk_protocol_templates_users` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Таблица за елементи на шаблони на протоколи
CREATE TABLE IF NOT EXISTS `protocol_template_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `template_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `item_order` int(11) NOT NULL DEFAULT '0',
  `item_type` enum('checkbox','text','number','select') NOT NULL DEFAULT 'checkbox',
  `options` text,
  `is_required` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`item_id`),
  KEY `fk_protocol_template_items_templates` (`template_id`),
  CONSTRAINT `fk_protocol_template_items_templates` FOREIGN KEY (`template_id`) REFERENCES `protocol_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Добавяне на колона template_id към таблицата protocols
ALTER TABLE `protocols` 
ADD COLUMN `template_id` int(11) NULL AFTER `object_id`,
ADD CONSTRAINT `fk_protocols_templates` FOREIGN KEY (`template_id`) REFERENCES `protocol_templates` (`template_id`) ON DELETE SET NULL ON UPDATE CASCADE;
