-- Clean migration for Phase 7 features
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS study_reports;
DROP TABLE IF EXISTS collaboration_responses;
DROP TABLE IF EXISTS collaboration_form_fields;
DROP TABLE IF EXISTS study_collaboration_requests;
DROP TABLE IF EXISTS external_links;
DROP TABLE IF EXISTS study_comments;
DROP TABLE IF EXISTS moderation_queue;
DROP TABLE IF EXISTS publications;
DROP TABLE IF EXISTS group_members;
DROP TABLE IF EXISTS group_chats;
DROP TABLE IF EXISTS contact_requests;
DROP TABLE IF EXISTS followers;
DROP TABLE IF EXISTS document_versions;
SET FOREIGN_KEY_CHECKS = 1;

-- Now create all tables fresh
CREATE TABLE document_versions (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    document_id INT UNSIGNED NOT NULL,
    version_number INT NOT NULL DEFAULT 1,
    file_path VARCHAR(500) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_size INT NOT NULL,
    uploaded_by INT UNSIGNED NOT NULL,
    change_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES study_documents(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_document_version (document_id, version_number)
);

ALTER TABLE studies ADD COLUMN IF NOT EXISTS approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending' AFTER status;
ALTER TABLE studies ADD COLUMN IF NOT EXISTS approved_by INT UNSIGNED NULL AFTER approval_status;
ALTER TABLE studies ADD COLUMN IF NOT EXISTS approved_at TIMESTAMP NULL AFTER approved_by;
ALTER TABLE studies ADD COLUMN IF NOT EXISTS approval_notes TEXT AFTER approved_at;
ALTER TABLE studies ADD FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL;

CREATE TABLE followers (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    follower_id INT UNSIGNED NOT NULL,
    following_id INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (following_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_follow (follower_id, following_id),
    INDEX idx_follower (follower_id),
    INDEX idx_following (following_id)
);

CREATE TABLE contact_requests (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    requester_id INT UNSIGNED NOT NULL,
    target_id INT UNSIGNED NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    message TEXT,
    response_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    responded_at TIMESTAMP NULL,
    responded_by INT UNSIGNED NULL,
    FOREIGN KEY (requester_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (target_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (responded_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_requester (requester_id),
    INDEX idx_target (target_id),
    INDEX idx_status (status)
);

CREATE TABLE group_chats (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_by INT UNSIGNED NOT NULL,
    is_private BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE group_members (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    group_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    role ENUM('admin', 'member') DEFAULT 'member',
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES group_chats(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_group_member (group_id, user_id)
);

ALTER TABLE messages ADD COLUMN IF NOT EXISTS group_id INT UNSIGNED NULL AFTER room_id;
ALTER TABLE messages ADD FOREIGN KEY (group_id) REFERENCES group_chats(id) ON DELETE CASCADE;

CREATE TABLE publications (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(500) NOT NULL,
    abstract TEXT,
    authors TEXT NOT NULL,
    journal VARCHAR(255),
    publication_date DATE,
    doi VARCHAR(100),
    url VARCHAR(500),
    file_path VARCHAR(500),
    submitter_id INT UNSIGNED NOT NULL,
    status ENUM('draft', 'submitted', 'published', 'rejected') DEFAULT 'draft',
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMP NULL,
    rejection_reason TEXT,
    FOREIGN KEY (submitter_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_submitter (submitter_id),
    INDEX idx_status (status)
);

CREATE TABLE moderation_queue (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    content_type ENUM('study', 'publication', 'comment', 'profile') NOT NULL,
    content_id INT NOT NULL,
    reported_by INT UNSIGNED NOT NULL,
    reason TEXT NOT NULL,
    status ENUM('pending', 'reviewed', 'resolved') DEFAULT 'pending',
    reviewed_by INT UNSIGNED NULL,
    reviewed_at TIMESTAMP NULL,
    action_taken TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reported_by) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_content (content_type, content_id),
    INDEX idx_status (status)
);

CREATE TABLE study_comments (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    study_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    parent_id INT UNSIGNED NULL,
    content TEXT NOT NULL,
    is_review BOOLEAN DEFAULT FALSE,
    rating INT NULL CHECK (rating >= 1 AND rating <= 5),
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'approved',
    moderated_by INT UNSIGNED NULL,
    moderated_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES studies(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES study_comments(id) ON DELETE CASCADE,
    FOREIGN KEY (moderated_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_study (study_id),
    INDEX idx_user (user_id),
    INDEX idx_parent (parent_id),
    INDEX idx_status (status)
);

CREATE TABLE external_links (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    study_id INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    url VARCHAR(500) NOT NULL,
    description TEXT,
    added_by INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES studies(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_study (study_id)
);

CREATE TABLE study_collaboration_requests (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    study_id INT UNSIGNED NOT NULL,
    requester_id INT UNSIGNED NOT NULL,
    message TEXT,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    responded_at TIMESTAMP NULL,
    responded_by INT UNSIGNED NULL,
    response_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES studies(id) ON DELETE CASCADE,
    FOREIGN KEY (requester_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (responded_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_study (study_id),
    INDEX idx_requester (requester_id),
    INDEX idx_status (status)
);

CREATE TABLE collaboration_form_fields (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    study_id INT UNSIGNED NOT NULL,
    field_name VARCHAR(255) NOT NULL,
    field_type ENUM('text', 'textarea', 'number', 'date', 'file', 'image', 'video') NOT NULL,
    field_label VARCHAR(255) NOT NULL,
    is_required BOOLEAN DEFAULT FALSE,
    field_order INT DEFAULT 0,
    options TEXT, -- JSON for select/radio options
    validation_rules TEXT, -- JSON for validation
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES studies(id) ON DELETE CASCADE,
    INDEX idx_study (study_id),
    INDEX idx_order (field_order)
);

CREATE TABLE collaboration_responses (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    study_id INT UNSIGNED NOT NULL,
    collaborator_id INT UNSIGNED NOT NULL,
    field_id INT UNSIGNED NOT NULL,
    response_value TEXT,
    file_path VARCHAR(500), -- for file uploads
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES studies(id) ON DELETE CASCADE,
    FOREIGN KEY (collaborator_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (field_id) REFERENCES collaboration_form_fields(id) ON DELETE CASCADE,
    UNIQUE KEY unique_response (study_id, collaborator_id, field_id)
);

CREATE TABLE study_reports (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    study_id INT UNSIGNED NOT NULL,
    generated_by INT UNSIGNED NOT NULL,
    report_type ENUM('summary', 'detailed', 'data_collection') DEFAULT 'summary',
    file_path VARCHAR(500) NOT NULL,
    includes_reviews BOOLEAN DEFAULT TRUE,
    includes_comments BOOLEAN DEFAULT TRUE,
    includes_data BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES studies(id) ON DELETE CASCADE,
    FOREIGN KEY (generated_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_study (study_id)
);

CREATE TABLE researcher_recommendations (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    recommended_user_id INT UNSIGNED NOT NULL,
    reason VARCHAR(255),
    score DECIMAL(3,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (recommended_user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_recommendation (user_id, recommended_user_id),
    INDEX idx_user (user_id),
    INDEX idx_score (score DESC)
);

CREATE TABLE export_logs (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    export_type ENUM('studies', 'publications', 'users', 'comments') NOT NULL,
    format ENUM('pdf', 'excel', 'csv') NOT NULL,
    filters TEXT, -- JSON of applied filters
    record_count INT DEFAULT 0,
    file_path VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_type (export_type)
);
