-- Topic Buckets
CREATE TABLE IF NOT EXISTS `kh_topics` (
  `topic_id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `parent_topic_id` INT DEFAULT NULL,
  `created_by` INT, 
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Access Assignments (For Managers/Officers)
CREATE TABLE IF NOT EXISTS `kh_topic_assignments` (
  `assignment_id` INT AUTO_INCREMENT PRIMARY KEY,
  `topic_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `access_level` ENUM('Viewer', 'Manager') DEFAULT 'Viewer',
  FOREIGN KEY (`topic_id`) REFERENCES `kh_topics`(`topic_id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Asset Items (Files & Links)
CREATE TABLE IF NOT EXISTS `kh_items` (
  `item_id` INT AUTO_INCREMENT PRIMARY KEY,
  `type` ENUM('File', 'Link') NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `current_version_id` INT DEFAULT NULL, -- Links to specific version
  `url` VARCHAR(2048) DEFAULT NULL,      -- Only for Links
  `last_accessed` DATETIME DEFAULT NULL,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Item-Topic link (Many-to-Many)
CREATE TABLE IF NOT EXISTS `kh_item_topics` (
  `item_id` INT NOT NULL,
  `topic_id` INT NOT NULL,
  PRIMARY KEY (`item_id`, `topic_id`),
  FOREIGN KEY (`item_id`) REFERENCES `kh_items`(`item_id`) ON DELETE CASCADE,
  FOREIGN KEY (`topic_id`) REFERENCES `kh_topics`(`topic_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- File Version History
CREATE TABLE IF NOT EXISTS `kh_item_versions` (
  `version_id` INT AUTO_INCREMENT PRIMARY KEY,
  `item_id` INT NOT NULL,
  `version_number` INT NOT NULL DEFAULT 1,
  `file_path` VARCHAR(255) NOT NULL,
  `file_type` VARCHAR(50), 
  `file_size` BIGINT,
  `uploaded_by` INT,
  `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`item_id`) REFERENCES `kh_items`(`item_id`) ON DELETE CASCADE,
  FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Master Tags
CREATE TABLE IF NOT EXISTS `kh_tags` (
  `tag_id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `kh_item_tags` (
  `item_id` INT NOT NULL,
  `tag_id` INT NOT NULL,
  PRIMARY KEY (`item_id`, `tag_id`),
  FOREIGN KEY (`item_id`) REFERENCES `kh_items`(`item_id`) ON DELETE CASCADE,
  FOREIGN KEY (`tag_id`) REFERENCES `kh_tags`(`tag_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
