-- /nexus_scholar_php/database/modul10_research_profile.sql

-- 1. research_profiles
-- Menyimpan profil publik dari author/researcher
CREATE TABLE research_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    bio TEXT NULL,
    affiliation VARCHAR(255) NULL,
    academic_title VARCHAR(100) NULL,
    orcid_id VARCHAR(50) NULL,
    google_scholar_url VARCHAR(255) NULL,
    is_public TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. research_fields
-- Master tabel untuk bidang kajian/penelitian
CREATE TABLE research_fields (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. research_methods
-- Master tabel untuk metode penelitian
CREATE TABLE research_methods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. profile_research_fields
-- Relasi many-to-many user dengan bidang penelitian
CREATE TABLE profile_research_fields (
    user_id INT NOT NULL,
    field_id INT NOT NULL,
    PRIMARY KEY (user_id, field_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (field_id) REFERENCES research_fields(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. profile_research_methods
-- Relasi many-to-many user dengan metode penelitian
CREATE TABLE profile_research_methods (
    user_id INT NOT NULL,
    method_id INT NOT NULL,
    PRIMARY KEY (user_id, method_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (method_id) REFERENCES research_methods(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. profile_publications
-- Daftar publikasi dari luar platform (opsional) atau yang dikurasi oleh user
CREATE TABLE profile_publications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    journal_name VARCHAR(255) NULL,
    publication_year INT NULL,
    doi VARCHAR(100) NULL,
    url VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. collaboration_preferences
-- Preferensi kolaborasi dari author
CREATE TABLE collaboration_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    is_open_to_collaborate TINYINT(1) DEFAULT 1,
    collaboration_types JSON NULL, -- (e.g., ["Co-Writing", "Data Analysis", "Peer Review", "Mentoring"])
    availability_notes TEXT NULL,
    preferred_communication TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;