-- /nexus_scholar_php/database/modul9_author_analytics.sql

-- 1. article_views
CREATE TABLE article_views (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    viewer_ip VARCHAR(45) NULL,
    viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. article_downloads
CREATE TABLE article_downloads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    downloader_ip VARCHAR(45) NULL,
    downloaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. citation_records
CREATE TABLE citation_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    citing_paper_title VARCHAR(255) NOT NULL,
    citing_paper_authors VARCHAR(255) NULL,
    citing_paper_journal VARCHAR(255) NULL,
    citing_paper_year INT NULL,
    citing_paper_url VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. writing_sessions
CREATE TABLE writing_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    submission_id INT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    words_added INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. revision_logs
CREATE TABLE revision_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    user_id INT NOT NULL,
    revision_note TEXT NULL,
    time_spent_minutes INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. author_analytics_snapshots
CREATE TABLE author_analytics_snapshots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    author_id INT NOT NULL,
    publication_stats JSON NULL, 
    writing_stats JSON NULL,     
    interest_map JSON NULL,      
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY (author_id),
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. impact_metrics
CREATE TABLE impact_metrics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    published_article_id INT NOT NULL,
    total_views INT DEFAULT 0,
    total_downloads INT DEFAULT 0,
    total_citations INT DEFAULT 0,
    engagement_score DECIMAL(5,2) DEFAULT 0.00,
    last_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY (published_article_id),
    FOREIGN KEY (published_article_id) REFERENCES published_articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;