-- Добавяне на таблица за документи
CREATE TABLE IF NOT EXISTS `documents` (
  `document_id` int(11) NOT NULL AUTO_INCREMENT,
  `object_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_type` varchar(100) NOT NULL,
  `file_size` int(11) NOT NULL,
  `uploaded_by` int(11) NOT NULL,
  `upload_date` datetime NOT NULL,
  `status` enum('active','archived','deleted') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`document_id`),
  KEY `object_id` (`object_id`),
  KEY `uploaded_by` (`uploaded_by`),
  CONSTRAINT `documents_ibfk_1` FOREIGN KEY (`object_id`) REFERENCES `objects` (`object_id`) ON DELETE CASCADE,
  CONSTRAINT `documents_ibfk_2` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Добавяне на таблица за QR кодове на обекти
CREATE TABLE IF NOT EXISTS `object_qr_codes` (
  `qr_id` int(11) NOT NULL AUTO_INCREMENT,
  `object_id` int(11) NOT NULL,
  `qr_code` varchar(255) NOT NULL,
  `generated_date` datetime NOT NULL,
  `last_used` datetime DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`qr_id`),
  UNIQUE KEY `qr_code` (`qr_code`),
  KEY `object_id` (`object_id`),
  CONSTRAINT `object_qr_codes_ibfk_1` FOREIGN KEY (`object_id`) REFERENCES `objects` (`object_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Добавяне на таблица за логове на сканиране на QR кодове
CREATE TABLE IF NOT EXISTS `qr_scan_logs` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `qr_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `scan_date` datetime NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `action` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`log_id`),
  KEY `qr_id` (`qr_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `qr_scan_logs_ibfk_1` FOREIGN KEY (`qr_id`) REFERENCES `object_qr_codes` (`qr_id`),
  CONSTRAINT `qr_scan_logs_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
