-- Добавяне на таблица за протоколи по график Д за пожарна безопасност
CREATE TABLE IF NOT EXISTS `protocols` (
  `protocol_id` int(11) NOT NULL AUTO_INCREMENT,
  `object_id` int(11) NOT NULL,
  `protocol_number` varchar(50) NOT NULL,
  `protocol_date` date NOT NULL,
  `inspector_name` varchar(100) NOT NULL,
  `inspector_position` varchar(100) NOT NULL,
  `responsible_person` varchar(100) NOT NULL,
  `responsible_position` varchar(100) NOT NULL,
  `protocol_status` enum('draft','completed','approved') NOT NULL DEFAULT 'draft',
  `notes` text,
  `created_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`protocol_id`),
  KEY `object_id` (`object_id`),
  KEY `created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `protocols_ibfk_1` FOREIGN KEY (`object_id`) REFERENCES `objects` (`object_id`) ON DELETE CASCADE,
  CONSTRAINT `protocols_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`),
  CONSTRAINT `protocols_ibfk_3` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Добавяне на таблица за елементи на протокола
CREATE TABLE IF NOT EXISTS `protocol_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `protocol_id` int(11) NOT NULL,
  `item_number` int(11) NOT NULL,
  `item_description` varchar(255) NOT NULL,
  `item_status` enum('compliant','non_compliant','not_applicable') NOT NULL,
  `item_notes` text,
  PRIMARY KEY (`item_id`),
  KEY `protocol_id` (`protocol_id`),
  CONSTRAINT `protocol_items_ibfk_1` FOREIGN KEY (`protocol_id`) REFERENCES `protocols` (`protocol_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Добавяне на таблица за прикачени файлове към протоколи
CREATE TABLE IF NOT EXISTS `protocol_attachments` (
  `attachment_id` int(11) NOT NULL AUTO_INCREMENT,
  `protocol_id` int(11) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_type` varchar(50) NOT NULL,
  `file_size` int(11) NOT NULL,
  `uploaded_by` int(11) NOT NULL,
  `uploaded_at` datetime NOT NULL,
  PRIMARY KEY (`attachment_id`),
  KEY `protocol_id` (`protocol_id`),
  KEY `uploaded_by` (`uploaded_by`),
  CONSTRAINT `protocol_attachments_ibfk_1` FOREIGN KEY (`protocol_id`) REFERENCES `protocols` (`protocol_id`) ON DELETE CASCADE,
  CONSTRAINT `protocol_attachments_ibfk_2` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
