-- FashCognitive TNA - Project-level chat & documents
-- Adds project-scoped comments and attachments (previously only task-level).
-- These are referenced by api/tna/project-comments.php and api/tna/upload.php.

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `tna_project_comments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `user_name` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `parent_comment_id` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tna_project_comments_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `tna_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tna_project_attachments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `comment_id` int unsigned DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_size` int unsigned DEFAULT '0',
  `uploaded_by` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `comment_id` (`comment_id`),
  CONSTRAINT `tna_project_attachments_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `tna_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
