-- Work Item Communication Module Database Schema
-- Created for WorkSync v3

-- ============================================
-- workitem_comments Table
-- ============================================
-- Stores all comments and replies on work items
-- Supports comment threading via parent_comment_id

CREATE TABLE IF NOT EXISTS workitem_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    -- Work Item & Project Reference
    workitem_id VARCHAR(255) NOT NULL COMMENT 'Azure DevOps Work Item ID',
    project_name VARCHAR(255) NOT NULL COMMENT 'Project name for reference',

    -- User Information
    user_id BIGINT UNSIGNED NULLABLE COMMENT 'Local user ID (nullable if user deleted)',
    user_email VARCHAR(255) NULLABLE COMMENT 'Email fallback if user deleted',
    user_name VARCHAR(255) NULLABLE COMMENT 'Display name of commenter',

    -- Comment Content
    content LONGTEXT NOT NULL COMMENT 'Comment text content',

    -- Edit Tracking
    is_edited BOOLEAN DEFAULT FALSE COMMENT 'Has this comment been edited',
    edited_at TIMESTAMP NULL COMMENT 'When the comment was last edited',
    edit_history JSON NULLABLE COMMENT 'JSON array of previous edits with timestamps',

    -- Threaded Comments
    parent_comment_id BIGINT UNSIGNED NULLABLE COMMENT 'Parent comment ID for replies',

    -- Interactions
    likes_count INT DEFAULT 0 COMMENT 'Number of likes/reactions',

    -- Timestamps
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Indexes for Performance
    INDEX idx_workitem_project (workitem_id, project_name) COMMENT 'Find comments by work item',
    INDEX idx_user_date (user_id, created_at) COMMENT 'Find user comments by date',
    INDEX idx_project (project_name) COMMENT 'Find comments by project',
    INDEX idx_parent (parent_comment_id) COMMENT 'Find replies for a comment',

    -- Foreign Keys
    CONSTRAINT fk_workitem_comment_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    CONSTRAINT fk_workitem_comment_parent
        FOREIGN KEY (parent_comment_id)
        REFERENCES workitem_comments(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================
-- workitem_attachments Table
-- ============================================
-- Stores file uploads associated with comments
-- Supports multiple file types with metadata

CREATE TABLE IF NOT EXISTS workitem_attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    -- Comment Reference (Primary)
    workitem_comment_id BIGINT UNSIGNED NOT NULL COMMENT 'Comment this attachment belongs to',

    -- Work Item & Project Reference
    workitem_id VARCHAR(255) NOT NULL COMMENT 'Azure DevOps Work Item ID',
    project_name VARCHAR(255) NOT NULL COMMENT 'Project name',

    -- File Information
    file_name VARCHAR(255) NOT NULL COMMENT 'Original file name',
    file_path VARCHAR(255) NOT NULL COMMENT 'Storage path (relative to storage/)',
    file_type VARCHAR(100) NOT NULL COMMENT 'MIME type (e.g., application/pdf)',
    file_size BIGINT NOT NULL COMMENT 'File size in bytes',

    -- File Classification
    upload_type ENUM('file', 'image', 'video', 'document')
        NOT NULL COMMENT 'Type of attachment for filtering',

    -- Additional Info
    description VARCHAR(500) NULLABLE COMMENT 'Optional description',

    -- User Reference
    uploaded_by BIGINT UNSIGNED NOT NULL COMMENT 'User ID who uploaded',

    -- Timestamps
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Indexes for Performance
    INDEX idx_workitem_project (workitem_id, project_name) COMMENT 'Find attachments by work item',
    INDEX idx_comment (workitem_comment_id) COMMENT 'Find attachments for a comment',
    INDEX idx_upload_type (upload_type) COMMENT 'Filter by type',
    INDEX idx_uploaded_by (uploaded_by) COMMENT 'Find uploads by user',
    INDEX idx_created (created_at) COMMENT 'Sort by creation date',

    -- Foreign Keys
    CONSTRAINT fk_attachment_comment
        FOREIGN KEY (workitem_comment_id)
        REFERENCES workitem_comments(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_attachment_user
        FOREIGN KEY (uploaded_by)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================
-- Useful Queries for Data Analysis
-- ============================================

-- Get comment count per work item
-- SELECT workitem_id, COUNT(*) as comment_count
-- FROM workitem_comments
-- WHERE parent_comment_id IS NULL
-- GROUP BY workitem_id
-- ORDER BY comment_count DESC;

-- Get attachment statistics
-- SELECT upload_type, COUNT(*) as count, SUM(file_size) as total_size
-- FROM workitem_attachments
-- GROUP BY upload_type;

-- Get most active commenters
-- SELECT user_name, COUNT(*) as comment_count
-- FROM workitem_comments
-- WHERE parent_comment_id IS NULL
-- GROUP BY user_id
-- ORDER BY comment_count DESC;

-- Get discussion duration by work item
-- SELECT workitem_id,
--        MIN(created_at) as first_comment,
--        MAX(created_at) as last_comment,
--        DATEDIFF(MAX(created_at), MIN(created_at)) as discussion_days
-- FROM workitem_comments
-- WHERE parent_comment_id IS NULL
-- GROUP BY workitem_id;

-- Get edited comments
-- SELECT * FROM workitem_comments
-- WHERE is_edited = TRUE
-- ORDER BY edited_at DESC;

-- Get comments with most likes
-- SELECT * FROM workitem_comments
-- ORDER BY likes_count DESC
-- LIMIT 10;

-- Get file storage usage per project
-- SELECT project_name,
--        COUNT(*) as file_count,
--        SUM(file_size) as total_size,
--        ROUND(SUM(file_size) / 1024 / 1024, 2) as size_mb
-- FROM workitem_attachments
-- GROUP BY project_name;

-- Get orphaned attachments (comments deleted)
-- SELECT * FROM workitem_attachments
-- WHERE workitem_comment_id NOT IN
--       (SELECT id FROM workitem_comments);


-- ============================================
-- Maintenance Queries
-- ============================================

-- Delete orphaned attachments
-- DELETE FROM workitem_attachments
-- WHERE workitem_comment_id NOT IN
--       (SELECT id FROM workitem_comments);

-- Clean up old attachments (older than 90 days)
-- DELETE FROM workitem_attachments
-- WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
-- AND workitem_comment_id NOT IN (SELECT id FROM workitem_comments);

-- Archive old comments
-- DELETE FROM workitem_comments
-- WHERE created_at < DATE_SUB(NOW(), INTERVAL 365 DAY)
-- AND parent_comment_id IS NULL;


-- ============================================
-- Stored Procedures (Optional)
-- ============================================

-- DELIMITER //

-- Create procedure to get comment thread
-- CREATE PROCEDURE get_comment_thread(IN p_comment_id INT)
-- BEGIN
--     SELECT * FROM workitem_comments
--     WHERE id = p_comment_id
--     OR parent_comment_id = p_comment_id
--     ORDER BY created_at ASC;
-- END//

-- Create procedure to count replies
-- CREATE PROCEDURE count_replies(IN p_comment_id INT, OUT p_count INT)
-- BEGIN
--     SELECT COUNT(*) INTO p_count
--     FROM workitem_comments
--     WHERE parent_comment_id = p_comment_id;
-- END//

-- DELIMITER ;


-- ============================================
-- Views (Optional)
-- ============================================

-- Create view for comment statistics
-- CREATE VIEW vw_comment_statistics AS
-- SELECT
--     workitem_id,
--     project_name,
--     COUNT(DISTINCT id) as total_comments,
--     COUNT(DISTINCT CASE WHEN parent_comment_id IS NULL THEN id END) as top_level_comments,
--     COUNT(DISTINCT CASE WHEN parent_comment_id IS NOT NULL THEN id END) as total_replies,
--     COUNT(DISTINCT CASE WHEN is_edited = TRUE THEN id END) as edited_comments,
--     COUNT(DISTINCT user_id) as contributors,
--     SUM(likes_count) as total_likes,
--     MIN(created_at) as first_comment_at,
--     MAX(created_at) as last_comment_at
-- FROM workitem_comments
-- GROUP BY workitem_id, project_name;

-- Create view for attachment statistics
-- CREATE VIEW vw_attachment_statistics AS
-- SELECT
--     workitem_id,
--     project_name,
--     upload_type,
--     COUNT(*) as file_count,
--     SUM(file_size) as total_size,
--     ROUND(SUM(file_size) / 1024 / 1024, 2) as size_mb,
--     AVG(file_size) as avg_size
-- FROM workitem_attachments
-- GROUP BY workitem_id, project_name, upload_type;
