CREATE TABLE users (
user_id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT UNIQUE,
password_hash TEXT NOT NULL,
profile_picture BLOB, -- Store profile pictures as BLOBs
bio TEXT, -- A short bio of the user
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE repositories (
repo_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
repo_name TEXT NOT NULL,
description TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
is_private BOOLEAN DEFAULT false, -- Indicate if the repository is private
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE TABLE commits (
commit_id INTEGER PRIMARY KEY AUTOINCREMENT,
repo_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
commit_message TEXT NOT NULL,
commit_date DATETIME DEFAULT CURRENT_TIMESTAMP,
commit_hash TEXT, -- Store the unique hash of the commit
FOREIGN KEY (repo_id) REFERENCES repositories(repo_id),
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE TABLE issues (
issue_id INTEGER PRIMARY KEY AUTOINCREMENT,
repo_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
issue_title TEXT NOT NULL,
issue_description TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
is_closed BOOLEAN DEFAULT false, -- Indicate if the issue is closed
closed_at DATETIME, -- Store the date when the issue was closed
FOREIGN KEY (repo_id) REFERENCES repositories(repo_id),
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE TABLE pull_requests (
pr_id INTEGER PRIMARY KEY AUTOINCREMENT,
repo_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
pr_title TEXT NOT NULL,
pr_description TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
merged BOOLEAN DEFAULT false, -- Indicate if the pull request is merged
merged_at DATETIME, -- Store the date when the pull request was merged
FOREIGN KEY (repo_id) REFERENCES repositories(repo_id),
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE TABLE stars (
star_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
repo_id INTEGER NOT NULL,
starred_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (repo_id) REFERENCES repositories(repo_id)
);
CREATE TABLE followers (
follower_id INTEGER PRIMARY KEY AUTOINCREMENT,
follower_user_id INTEGER NOT NULL,
following_user_id INTEGER NOT NULL,
followed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (follower_user_id) REFERENCES users(user_id),
FOREIGN KEY (following_user_id) REFERENCES users(user_id)
);
CREATE TABLE notifications (
notification_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
notification_type TEXT, -- Indicate the type of notification (issue, pull request, etc.)
notification_text TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
is_read BOOLEAN DEFAULT false, -- Indicate if the notification is read
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE TABLE repository_languages (
repo_id INTEGER NOT NULL,
lang_id INTEGER NOT NULL,
usage_percentage REAL, -- Store the percentage of usage of the language in the repository
PRIMARY KEY (repo_id, lang_id),
FOREIGN KEY (repo_id) REFERENCES repositories(repo_id),
FOREIGN KEY (lang_id) REFERENCES languages(lang_id)
);
CREATE TABLE languages (
lang_id INTEGER PRIMARY KEY AUTOINCREMENT,
lang_name TEXT NOT NULL UNIQUE
);
INSERT INTO users (username, email, password_hash, profile_picture, bio) VALUES
('CodeWizard123', 'codewizard123@example.com', 'hashed_password', null, 'I cast coding spells!'),
('SpaceExplorer', 'spaceexplorer@example.com', 'hashed_password', null, 'Exploring the cosmos, one line of code at a time.'),
('CryptoCatLady', 'cryptocatlady@example.com', 'hashed_password', null, 'Coding and cats, what else?'),
('LordOfTheCommits', 'lordofthecommits@example.com', 'hashed_password', null, 'Master of commits and pull requests.'),
('DebugQueen', 'debugqueen@example.com', 'hashed_password', null, 'Debugging is an art, and I am the artist.'),
('CodeNinjaSlices', 'codeninjaslices@example.com', 'hashed_password', null, 'Silent and deadly efficient coder.'),
('TheCodeArchitect', 'thecodearchitect@example.com', 'hashed_password', null, 'Building software masterpieces.'),
('AI_Enthusiast', 'aienthusiast@example.com', 'hashed_password', null, 'Exploring the wonders of AI.'),
('WebDevWizardry', 'webdevwizardry@example.com', 'hashed_password', null, 'Crafting magical web experiences.'),
('SyntaxSorcerer', 'syntaxsorcerer@example.com', 'hashed_password', null, 'Master of syntax and compiler magic.'),
('CodeRaptor', 'coderaptor@example.com', 'hashed_password', null, 'Swift and precise coding raptor.'),
('PixelPerfectCoder', 'pixelperfectcoder@example.com', 'hashed_password', null, 'When pixels meet code, perfection happens.'),
('Functionalist', 'functionalist@example.com', 'hashed_password', null, 'Functional programming aficionado.'),
('CodeSmithing', 'codesmithing@example.com', 'hashed_password', null, 'Forging code with precision.'),
('TheAlgorithmist', 'thealgorithmist@example.com', 'hashed_password', null, 'Taming algorithms, one step at a time.'),
('DevOpsDynamo', 'devopsdynamo@example.com', 'hashed_password', null, 'Master of DevOps practices.'),
('BugBountyHunter', 'bugbountyhunter@example.com', 'hashed_password', null, 'Tracking down bugs for fun and profit.'),
('OpenSourceAdvocate', 'opensourceadvocate@example.com', 'hashed_password', null, 'Champion of open source software.'),
('DesignCodeHarmony', 'designcodeharmony@example.com', 'hashed_password', null, 'Where design meets code, harmony ensues.'),
('CodeGuruMeditates', 'codegurumeditates@example.com', 'hashed_password', null, 'Coding and meditation go hand in hand.');
INSERT INTO repositories (user_id, repo_name, description, is_private) VALUES
(1, 'AwesomeProject', 'A collaborative project for building awesome things.', false),
(2, 'SpaceExplorersToolbox', 'Tools and scripts for space exploration enthusiasts.', false),
(3, 'CatFeederApp', 'An app to automate cat feeding with IoT devices.', true),
(4, 'CommitLord', 'A collection of scripts to automate commit and PR tasks.', false),
(5, 'DebugUtils', 'Utilities and scripts for efficient debugging.', false),
(9, 'WebDevProjects', 'Various web development projects and experiments.', false),
(10, 'SyntaxSorcererLib', 'A library of syntax highlighting functions.', false),
(11, 'RaptorUtils', 'Utility functions for optimizing code performance.', true),
(16, 'DevOpsTools', 'Collection of DevOps tools and scripts.', false),
(17, 'BugBountyPlatform', 'A platform for managing bug bounty programs.', false),
(18, 'OpenSourceProjects', 'Repository for various open source contributions.', false),
(3, 'CryptoTradingBot', 'A cryptocurrency trading bot.', false),
(5, 'DebugQueenBlog', 'A blog for sharing debugging tips and tricks.', false),
(7, 'WebDevTutorials', 'Tutorials for web development beginners.', false),
(9, 'WebDevBoilerplate', 'Boilerplate code for common web development tasks.', false),
(11, 'CodeRaptorUtils', 'Utility functions created by CodeRaptor.', false),
(19, 'DesignCodeHarmonyUI', 'UI components for creating harmonious designs.', false),
(20, 'MeditationApp', 'A meditation app with various features.', false),
(1, 'SideProjectIdeas', 'A collection of side project ideas.', false),
(12, 'PixelPerfectDesigns', 'Collection of pixel-perfect design assets.', true),
(13, 'FunctionalProgrammingExamples', 'Examples and tutorials for functional programming.', false),
(14, 'CodeSmithingAPI', 'An API for generating code snippets.', false),
(15, 'AlgorithmistChallenges', 'Repository for algorithm challenges and solutions.', false),
(13, 'LambdaCalculusExplained', 'An introduction to lambda calculus.', false),
(15, 'DynamicProgrammingChallenges', 'Challenges and solutions for dynamic programming.', false),
(17, 'BugBountyTracker', 'A tool for tracking bug bounty submissions.', false),
(19, 'DesignSystem', 'A design system for creating consistent UI components.', false),
(20, 'MindfulnessApp', 'An app for mindfulness and relaxation techniques.', false),
(6, 'NinjaSlicesUtils', 'Utility functions for various programming languages.', false),
(7, 'WebDevWizardryTemplates', 'A collection of HTML/CSS templates for web development.', false),
(8, 'AI_Research', 'Repository for AI research and experiments.', true);
INSERT INTO commits (repo_id, user_id, commit_message, commit_date, commit_hash) VALUES
(3, 6, 'Added initial project structure', '2023-08-08 12:00:00', 'hash1234'),
(10, 1, 'Implemented authentication system', '2023-08-09 15:30:00', 'hash5678'),
(15, 11, 'Added satellite tracking script', '2023-08-10 09:00:00', 'hash9101'),
(20, 16, 'Refactored feeding schedule logic', '2023-08-11 14:00:00', 'hash2345'),
(13, 18, 'Improved commit automation script', '2023-08-12 10:30:00', 'hash6789'),
(4, 3, 'Added type checking utilities', '2023-08-13 13:00:00', 'hash0123'),
(18, 19, 'Updated template styles', '2023-08-14 08:00:00', 'hash4567'),
(6, 5, 'Merged AI model improvements', '2023-08-15 16:30:00', 'hash8901'),
(11, 14, 'Fixed navigation bar bug', '2023-08-16 11:00:00', 'hash3456'),
(16, 20, 'Added syntax highlighting for Python', '2023-08-17 09:30:00', 'hash7890'),
(8, 8, 'Optimized image loading', '2023-08-18 14:00:00', 'hash5671'),
(1, 2, 'Added new icon set', '2023-08-19 10:00:00', 'hash9012'),
(14, 17, 'Added functional programming tutorial', '2023-08-20 12:30:00', 'hash1357'),
(9, 9, 'Implemented code generation logic', '2023-08-21 09:00:00', 'hash2468'),
(5, 4, 'Solved dynamic programming challenge', '2023-08-22 13:30:00', 'hash3579'),
(12, 13, 'Integrated new monitoring tool', '2023-08-23 11:00:00', 'hash4680'),
(7, 7, 'Improved bug submission process', '2023-08-24 14:30:00', 'hash5791'),
(2, 15, 'Merged pull request for UI improvements', '2023-08-25 09:00:00', 'hash6802'),
(17, 10, 'Added responsive design components', '2023-08-26 12:00:00', 'hash7913'),
(19, 12, 'Implemented meditation timer', '2023-08-27 10:30:00', 'hash8024'),
(3, 1, 'Added new side project ideas', '2023-08-28 13:00:00', 'hash9135'),
(16, 18, 'Improved trading strategy logic', '2023-08-29 09:00:00', 'hash0246'),
(9, 5, 'Added debugging cheat sheet', '2023-08-30 11:30:00', 'hash1359'),
(1, 8, 'Merged machine learning model updates', '2023-08-31 14:00:00', 'hash2461'),
(13, 10, 'Fixed syntax highlighting bug', '2023-09-01 10:00:00', 'hash3572'),
(4, 12, 'Added high-resolution icons', '2023-09-02 12:30:00', 'hash4683'),
(15, 14, 'Improved code generation performance', '2023-09-03 09:00:00', 'hash5794'),
(7, 16, 'Added monitoring for database performance', '2023-09-04 13:30:00', 'hash6805'),
(18, 19, 'Implemented dark mode', '2023-09-05 11:00:00', 'hash7916'),
(6, 20, 'Added relaxation audio tracks', '2023-09-06 12:00:00', 'hash8027');
INSERT INTO issues (repo_id, user_id, issue_title, issue_description, is_closed, closed_at) VALUES
(1, 5, 'Authentication Bug', 'Users are unable to log in after registration.', false, null),
(3, 10, 'Satellite Data Inconsistency', 'Satellite tracking data seems inconsistent at times.', false, null),
(6, 15, 'Template Rendering Issue', 'Some templates are not rendering correctly in older browsers.', false, null),
(9, 20, 'Navigation Bar Glitch', 'The navigation bar overlaps with content on smaller screens.', false, null),
(12, 3, 'Icon Size Issue', 'High-resolution icons are not displaying properly on certain devices.', false, null),
(15, 8, 'AI Model Training Bug', 'The AI model is not converging during training.', false, null),
(18, 11, 'Responsive Design Bug', 'Some UI components are not adapting to different screen sizes.', false, null),
(4, 16, 'Type Checking Error', 'Type checking utility is throwing errors for certain data types.', false, null),
(7, 1, 'Template Compatibility Issue', 'Templates are not compatible with the latest version of the framework.', false, null),
(10, 6, 'Syntax Highlighting Bug', 'Syntax highlighting is not working for certain code blocks.', false, null),
(13, 19, 'Functional Programming Tutorial Error', 'There is an error in the functional programming tutorial code.', false, null),
(16, 14, 'Monitoring Tool Integration Issue', 'The monitoring tool is not integrating properly with the database.', false, null),
(19, 9, 'Dark Mode Contrast Issue', 'The dark mode color scheme needs improvement for better readability.', false, null),
(2, 18, 'Pull Request Merge Conflict', 'There is a merge conflict in the latest pull request.', false, null),
(5, 13, 'Debugging Cheat Sheet Inaccuracy', 'Some information in the debugging cheat sheet is inaccurate.', false, null),
(8, 4, 'Machine Learning Model Overfitting', 'The machine learning model is overfitting the training data.', false, null),
(11, 7, 'Image Loading Optimization', 'Image loading can be further optimized for better performance.', false, null),
(14, 12, 'Code Generation Logic Bug', 'The code generation logic is not handling certain edge cases correctly.', false, null),
(17, 2, 'Bug Submission Process Improvement', 'Suggest improvements for the bug submission process.', false, null),
(3, 17, 'Trading Strategy Backtesting Issue', 'Backtesting results for the trading strategy are inconsistent.', false, null),
(6, 9, 'Template Style Guide Compliance', 'Ensure that templates follow the style guide for consistency.', false, null),
(9, 15, 'Navigation Bar Accessibility', 'Improve the accessibility of the navigation bar for screen readers.', false, null),
(12, 8, 'Icon Licensing Issue', 'Clarify the licensing for the icon set used in the project.', false, null),
(15, 1, 'AI Model Training Data Diversity', 'Explore ways to increase the diversity of the training data.', false, null),
(18, 6, 'Responsive Design Testing', 'Conduct thorough testing of responsive design across various devices.', false, null),
(1, 19, 'Side Project Idea Discussion', 'Discuss and prioritize the list of side project ideas.', false, null),
(4, 11, 'Type Checking Performance', 'Investigate ways to improve the performance of type checking utilities.', false, null),
(7, 16, 'Template Internationalization', 'Consider internationalization for the web templates.', false, null),
(10, 20, 'Syntax Highlighting for New Languages', 'Add support for syntax highlighting in less commonly used languages.', false, null),
(13, 5, 'Functional Programming Best Practices', 'Create a guide for best practices in functional programming.', false, null),
(16, 10, 'Database Monitoring Alerts', 'Set up alerts for critical database performance issues.', false, null),
(19, 14, 'Relaxation Audio Volume Levels', 'Adjust the volume levels of the relaxation audio tracks.', false, null);
INSERT INTO pull_requests (repo_id, user_id, pr_title, pr_description, merged, merged_at) VALUES
(1, 6, 'Authentication Enhancement', 'Enhance authentication system with two-factor authentication.', false, null),
(3, 11, 'Satellite Data Visualization', 'Add a visualization tool for satellite tracking data.', false, null),
(6, 16, 'Template Refactoring', 'Refactor templates for better code organization.', false, null),
(9, 20, 'Navigation Bar Improvement', 'Improve the responsiveness of the navigation bar.', false, null),
(12, 3, 'Icon Pack Update', 'Update the icon pack with new designs.', false, null),
(15, 8, 'AI Model Optimization', 'Optimize the AI model for faster training.', false, null),
(18, 11, 'Responsive Design Refinement', 'Refine responsive design for better mobile experience.', false, null),
(4, 16, 'Type Checking Extension', 'Extend type checking utilities to support more data types.', false, null),
(7, 1, 'Template Accessibility Enhancement', 'Enhance template accessibility for screen readers.', false, null),
(10, 6, 'Syntax Highlighting for Comments', 'Add syntax highlighting for comments in code blocks.', false, null),
(13, 19, 'Functional Programming Tutorial Update', 'Update the functional programming tutorial with new examples.', false, null),
(16, 14, 'Monitoring Tool Integration', 'Integrate a new monitoring tool for performance tracking.', false, null),
(19, 9, 'Dark Mode Color Scheme', 'Implement a new color scheme for dark mode.', false, null),
(2, 18, 'Pull Request Guidelines', 'Add guidelines and templates for pull requests.', false, null),
(5, 13, 'Debugging Cheat Sheet Expansion', 'Expand the debugging cheat sheet with more tips.', false, null),
(8, 4, 'Machine Learning Model Evaluation', 'Evaluate and improve the machine learning model.', false, null),
(11, 7, 'Image Compression', 'Implement image compression for better performance.', false, null),
(14, 12, 'Code Generation Refactoring', 'Refactor the code generation logic for better maintainability.', false, null),
(17, 2, 'Bug Submission Form Redesign', 'Redesign the bug submission form for a better user experience.', false, null),
(3, 17, 'Trading Strategy Backtesting Enhancement', 'Enhance the backtesting capabilities of the trading strategy.', false, null),
(6, 9, 'Template Style Consistency', 'Ensure consistent styling across all templates.', false, null),
(9, 15, 'Navigation Bar Animation', 'Add a smooth animation to the navigation bar.', false, null),
(12, 8, 'Icon Licensing Update', 'Update the licensing information for the icon set.', false, null),
(15, 1, 'AI Model Training Data Expansion', 'Expand the diversity of the training data for the AI model.', false, null),
(18, 6, 'Responsive Design Testing Suite', 'Create a comprehensive testing suite for responsive design.', false, null),
(1, 19, 'Side Project Collaboration', 'Propose collaboration on a side project idea.', false, null),
(4, 11, 'Type Checking Performance Optimization', 'Optimize the performance of type checking utilities.', false, null),
(7, 16, 'Template Internationalization Update', 'Update the internationalization of the web templates.', false, null),
(10, 20, 'Syntax Highlighting Theme', 'Implement a new syntax highlighting theme.', false, null),
(13, 5, 'Functional Programming Community Resources', 'Add a list of community resources for functional programming.', false, null),
(16, 10, 'Database Monitoring Alerts Enhancement', 'Enhance the alerting system for critical database issues.', false, null),
(19, 14, 'Relaxation Audio Playlist', 'Create a playlist feature for relaxation audio tracks.', false, null),
(2, 3, 'Satellite Data Analysis Tool', 'Add a tool for analyzing satellite data.', true, '2023-09-01 14:00:00'),
(5, 8, 'Machine Learning Model Tuning', 'Fine-tune the machine learning model for better performance.', true, '2023-09-02 09:30:00'),
(8, 13, 'Functional Programming Tutorial Translation', 'Translate the functional programming tutorial to multiple languages.', true, '2023-09-03 12:00:00'),
(11, 18, 'Pull Request Template Update', 'Update the pull request template with new guidelines.', true, '2023-09-04 15:30:00'),
(14, 1, 'Code Generation Logic Improvement', 'Improve the code generation logic for edge cases.', true, '2023-09-05 10:00:00'),
(17, 6, 'Debugging Cheat Sheet Contribution', 'Contribute additional content to the debugging cheat sheet.', true, '2023-09-06 13:00:00'),
(10, 15, 'Syntax Highlighting Bug Fix', 'Fix a bug related to syntax highlighting in comments.', true, '2023-09-07 08:30:00'),
(13, 9, 'Functional Programming Best Practices Guide', 'Create a guide for best practices in functional programming.', true, '2023-09-08 14:00:00'),
(16, 4, 'Monitoring Tool Integration Testing', 'Conduct integration testing for the monitoring tool.', true, '2023-09-09 09:00:00'),
(19, 12, 'Relaxation Audio Volume Adjustment', 'Adjust the volume levels of relaxation audio tracks.', true, '2023-09-10 12:30:00');
INSERT INTO stars (user_id, repo_id, starred_at) VALUES
(1, 3, '2023-08-10 10:00:00'),
(2, 6, '2023-08-15 12:00:00'),
(3, 9, '2023-08-20 09:30:00'),
(4, 12, '2023-08-25 14:00:00'),
(5, 15, '2023-08-30 10:30:00'),
(6, 18, '2023-09-03 11:00:00'),
(7, 1, '2023-09-07 12:30:00'),
(8, 4, '2023-09-11 09:00:00'),
(9, 7, '2023-09-15 13:30:00'),
(10, 10, '2023-09-19 11:00:00'),
(11, 13, '2023-09-23 09:30:00'),
(12, 16, '2023-09-27 14:00:00'),
(13, 19, '2023-09-30 10:30:00'),
(14, 2, '2023-10-03 12:00:00'),
(15, 5, '2023-10-07 09:30:00'),
(16, 8, '2023-10-11 14:00:00'),
(17, 11, '2023-10-15 10:00:00'),
(18, 14, '2023-10-19 12:30:00'),
(19, 17, '2023-10-23 09:00:00'),
(20, 20, '2023-10-27 14:00:00'),
(3, 1, '2023-10-31 09:30:00'),
(5, 6, '2023-11-03 11:00:00'),
(7, 11, '2023-11-07 12:30:00'),
(9, 16, '2023-11-11 09:00:00'),
(11, 20, '2023-11-15 13:30:00'),
(13, 3, '2023-11-19 11:00:00'),
(15, 8, '2023-11-23 12:30:00'),
(17, 14, '2023-11-27 09:00:00'),
(19, 17, '2023-12-01 13:30:00'),
(20, 12, '2023-12-05 11:00:00'),
(1, 9, '2023-12-09 12:30:00'),
(2, 4, '2023-12-13 09:00:00'),
(4, 7, '2023-12-17 13:30:00'),
(6, 10, '2023-12-21 11:00:00'),
(8, 13, '2023-12-25 12:30:00'),
(10, 16, '2023-12-29 09:00:00'),
(12, 19, '2023-12-31 13:30:00'),
(14, 15, '2024-01-04 11:00:00'),
(16, 2, '2024-01-08 12:30:00'),
(18, 5, '2024-01-12 09:00:00'),
(20, 8, '2024-01-16 13:30:00');
INSERT INTO followers (follower_user_id, following_user_id, followed_at) VALUES
(1, 2, '2023-08-01 10:00:00'),
(2, 3, '2023-08-02 12:00:00'),
(3, 4, '2023-08-03 09:30:00'),
(4, 5, '2023-08-04 14:00:00'),
(5, 6, '2023-08-05 11:00:00'),
(6, 7, '2023-08-06 12:30:00'),
(7, 8, '2023-08-07 09:00:00'),
(8, 9, '2023-08-08 13:30:00'),
(9, 10, '2023-08-09 11:00:00'),
(10, 11, '2023-08-10 12:30:00'),
(11, 12, '2023-08-11 09:00:00'),
(12, 13, '2023-08-12 14:00:00'),
(13, 14, '2023-08-13 10:30:00'),
(14, 15, '2023-08-14 12:00:00'),
(15, 16, '2023-08-15 13:30:00'),
(16, 17, '2023-08-16 09:00:00'),
(17, 18, '2023-08-17 12:30:00'),
(18, 19, '2023-08-18 11:00:00'),
(19, 20, '2023-08-19 12:30:00'),
(20, 1, '2023-08-20 09:00:00'),
(3, 2, '2023-08-21 13:30:00'),
(5, 4, '2023-08-22 11:00:00'),
(7, 6, '2023-08-23 12:30:00'),
(9, 8, '2023-08-24 09:00:00'),
(11, 10, '2023-08-25 13:30:00'),
(13, 11, '2023-08-26 11:00:00'),
(15, 12, '2023-08-27 12:30:00'),
(17, 13, '2023-08-28 09:00:00'),
(19, 14, '2023-08-29 13:30:00'),
(20, 15, '2023-08-30 11:00:00'),
(1, 16, '2023-08-31 12:30:00'),
(2, 17, '2023-09-01 09:00:00'),
(4, 18, '2023-09-02 13:30:00'),
(6, 19, '2023-09-03 11:00:00'),
(8, 20, '2023-09-04 12:30:00'),
(10, 1, '2023-09-05 09:00:00'),
(12, 2, '2023-09-06 13:30:00'),
(14, 3, '2023-09-07 11:00:00'),
(16, 4, '2023-09-08 12:30:00'),
(18, 5, '2023-09-09 09:00:00'),
(20, 6, '2023-09-10 13:30:00'),
(1, 7, '2023-09-11 11:00:00'),
(3, 8, '2023-09-12 12:30:00'),
(5, 9, '2023-09-13 09:00:00'),
(7, 10, '2023-09-14 13:30:00'),
(9, 11, '2023-09-15 11:00:00'),
(11, 12, '2023-09-16 12:30:00'),
(13, 13, '2023-09-17 09:00:00'),
(15, 14, '2023-09-18 13:30:00'),
(17, 15, '2023-09-19 11:00:00'),
(19, 16, '2023-09-20 12:30:00'),
(20, 17, '2023-09-21 09:00:00');
INSERT INTO notifications (user_id, notification_type, notification_text, is_read) VALUES
(5, 'issue', 'New issue reported: "Authentication Bug"', false),
(12, 'pull_request', 'Pull request submitted: "Satellite Data Visualization"', false),
(19, 'issue', 'New issue reported: "Template Rendering Issue"', false),
(3, 'pull_request', 'Pull request submitted: "Navigation Bar Improvement"', false),
(15, 'issue', 'New issue reported: "Icon Size Issue"', false),
(8, 'pull_request', 'Pull request submitted: "AI Model Optimization"', false),
(20, 'issue', 'New issue reported: "Responsive Design Bug"', false),
(11, 'pull_request', 'Pull request submitted: "Type Checking Extension"', false),
(6, 'issue', 'New issue reported: "Template Accessibility Enhancement"', false),
(1, 'pull_request', 'Pull request submitted: "Syntax Highlighting for Comments"', false),
(4, 'issue', 'New issue reported: "Functional Programming Tutorial Update"', false),
(13, 'pull_request', 'Pull request submitted: "Monitoring Tool Integration"', false),
(2, 'issue', 'New issue reported: "Dark Mode Color Scheme"', false),
(14, 'pull_request', 'Pull request submitted: "Pull Request Guidelines"', false),
(7, 'issue', 'New issue reported: "Debugging Cheat Sheet Expansion"', false),
(16, 'pull_request', 'Pull request submitted: "Machine Learning Model Evaluation"', false),
(9, 'issue', 'New issue reported: "Image Compression"', false),
(18, 'pull_request', 'Pull request submitted: "Code Generation Refactoring"', false),
(17, 'issue', 'New issue reported: "Bug Submission Form Redesign"', false),
(10, 'pull_request', 'Pull request submitted: "Trading Strategy Backtesting Enhancement"', false),
(3, 'star', 'Your repository received a new star!', false),
(15, 'star', 'Your repository received a new star!', false),
(2, 'star', 'Your repository received a new star!', false),
(8, 'star', 'Your repository received a new star!', false),
(11, 'star', 'Your repository received a new star!', false),
(1, 'star', 'Your repository received a new star!', false),
(4, 'star', 'Your repository received a new star!', false),
(14, 'star', 'Your repository received a new star!', false),
(19, 'star', 'Your repository received a new star!', false),
(6, 'star', 'Your repository received a new star!', false),
(12, 'star', 'Your repository received a new star!', false),
(9, 'star', 'Your repository received a new star!', false),
(18, 'star', 'Your repository received a new star!', false),
(13, 'star', 'Your repository received a new star!', false),
(20, 'star', 'Your repository received a new star!', false),
(5, 'star', 'Your repository received a new star!', false),
(10, 'star', 'Your repository received a new star!', false),
(7, 'star', 'Your repository received a new star!', false),
(16, 'star', 'Your repository received a new star!', false),
(17, 'star', 'Your repository received a new star!', false);
INSERT INTO repository_languages (repo_id, lang_id, usage_percentage) VALUES
(1, 1, 80.0),
(1, 2, 20.0),
(2, 3, 60.0),
(2, 4, 40.0),
(3, 5, 70.0),
(3, 6, 30.0),
(4, 7, 50.0),
(4, 8, 50.0),
(5, 9, 60.0),
(5, 10, 40.0),
(6, 11, 70.0),
(6, 12, 30.0),
(7, 13, 45.0),
(7, 14, 45.0),
(8, 15, 80.0),
(9, 1, 60.0),
(9, 11, 40.0),
(10, 12, 75.0),
(10, 13, 25.0),
(11, 14, 65.0),
(11, 15, 35.0),
(12, 2, 40.0),
(12, 9, 60.0),
(13, 5, 50.0),
(13, 10, 50.0),
(14, 7, 70.0),
(14, 12, 30.0),
(15, 3, 60.0),
(15, 8, 40.0),
(16, 13, 70.0),
(16, 14, 30.0),
(17, 1, 55.0),
(17, 15, 45.0),
(18, 6, 65.0),
(18, 11, 35.0),
(19, 2, 50.0),
(19, 9, 50.0),
(20, 4, 70.0),
(20, 10, 30.0);
INSERT INTO languages (lang_name) VALUES
('Python'),
('C'),
('C++'),
('Java'),
('JavaScript'),
('SQL'),
('R'),
('Swift'),
('Go'),
('Rust'),
('PHP'),
('Perl'),
('Ruby'),
('Scala'),
('Kotlin'),
('C#'),
('Objective-C'),
('Fortran'),
('Lisp'),
('Haskell'),
('Erlang');
-- Contibution history
-- WITH RECURSIVE ContributionPath AS (
-- SELECT
-- c.commit_id,
-- c.user_id,
-- c.repo_id,
-- c.commit_date,
-- r.repo_name,
-- 1 AS depth
-- FROM
-- commits c
-- JOIN
-- repositories r ON c.repo_id = r.repo_id
-- WHERE
-- c.user_id = 'CryptoCatLady'
-- UNION ALL
-- SELECT
-- c.commit_id,
-- c.user_id,
-- c.repo_id,
-- c.commit_date,
-- r.repo_name,
-- cp.depth + 1
-- FROM
-- commits c
-- JOIN
-- ContributionPath cp ON c.repo_id = cp.repo_id AND c.commit_date > cp.commit_date
-- JOIN
-- repositories r ON c.repo_id = r.repo_id
-- )
-- SELECT
-- commit_date,
-- repo_name,
-- depth
-- FROM
-- ContributionPath
-- ORDER BY
-- commit_date;
-- User Reputation
-- WITH Reputation AS (
-- SELECT
-- u.user_id,
-- u.username,
-- SUM(CASE WHEN pr.merged THEN 1 + (3 - JULIANDAY(pr.merged_at) + JULIANDAY(pr.created_at)) ELSE 0 END) AS pr_reputation,
-- SUM(CASE WHEN i.is_closed THEN 1 ELSE 0 END) AS issue_reputation
-- FROM
-- users u
-- LEFT JOIN
-- pull_requests pr ON u.user_id = pr.user_id
-- LEFT JOIN
-- issues i ON u.user_id = i.user_id
-- GROUP BY
-- u.user_id
-- )
-- SELECT
-- username,
-- pr_reputation + issue_reputation AS total_reputation
-- FROM
-- Reputation
-- ORDER BY
-- total_reputation DESC;
-- Repo scores
-- WITH RepositoryScores AS (
-- SELECT
-- r.repo_id,
-- r.repo_name,
-- -- Calculate the sum of points from stars, closed issues, and merged PRs
-- COALESCE(SUM(CASE WHEN s.star_id IS NOT NULL THEN 1 ELSE 0 END), 0) AS stars_score,
-- COALESCE(SUM(CASE WHEN i.is_closed THEN 2 ELSE 0 END), 0) AS issues_score,
-- COALESCE(SUM(CASE WHEN pr.merged THEN 3 ELSE 0 END), 0) AS pr_score
-- FROM
-- repositories r
-- LEFT JOIN
-- stars s ON r.repo_id = s.repo_id
-- LEFT JOIN
-- issues i ON r.repo_id = i.repo_id AND i.is_closed = 1
-- LEFT JOIN
-- pull_requests pr ON r.repo_id = pr.repo_id AND pr.merged = 1
-- GROUP BY
-- r.repo_id
-- )
-- SELECT
-- repo_name,
-- stars_score + issues_score + pr_score AS total_score
-- FROM
-- RepositoryScores
-- ORDER BY
-- total_score DESC
-- LIMIT 5;
-- closed by non owner
-- WITH RepoOwners AS (
-- SELECT
-- repo_id,
-- user_id AS owner_id
-- FROM
-- repositories
-- ),
-- ClosedIssues AS (
-- SELECT
-- repo_id,
-- user_id AS closer_id,
-- COUNT(issue_id) AS closed_count
-- FROM
-- issues
-- WHERE
-- is_closed = 1
-- GROUP BY
-- repo_id, user_id
-- ),
-- TotalClosed AS (
-- SELECT
-- repo_id,
-- SUM(closed_count) AS total_closed
-- FROM
-- ClosedIssues
-- GROUP BY
-- repo_id
-- ),
-- ClosedByNonOwners AS (
-- SELECT
-- ci.repo_id,
-- SUM(ci.closed_count) AS non_owner_closed
-- FROM
-- ClosedIssues ci
-- JOIN
-- RepoOwners ro ON ci.repo_id = ro.repo_id
-- WHERE
-- ci.closer_id != ro.owner_id
-- GROUP BY
-- ci.repo_id
-- )
-- SELECT
-- r.repo_name,
-- ROUND((cbno.non_owner_closed * 100.0 / tc.total_closed), 2) AS percentage_closed_by_non_owners
-- FROM
-- repositories r
-- JOIN
-- ClosedByNonOwners cbno ON r.repo_id = cbno.repo_id
-- JOIN
-- TotalClosed tc ON r.repo_id = tc.repo_id
-- ORDER BY
-- percentage_closed_by_non_owners DESC
-- LIMIT 5;
-- Collaboration Frequency
-- WITH UserActivity AS (
-- SELECT
-- u.username,
-- COUNT(DISTINCT CASE WHEN r.user_id = p.user_id THEN p.pr_id END) AS own_prs,
-- COUNT(DISTINCT CASE WHEN r.user_id != p.user_id THEN p.pr_id END) AS other_prs,
-- COUNT(DISTINCT CASE WHEN r.user_id = i.user_id THEN i.issue_id END) AS own_issues,
-- COUNT(DISTINCT CASE WHEN r.user_id != i.user_id THEN i.issue_id END) AS other_issues
-- FROM
-- users u
-- LEFT JOIN repositories r ON u.user_id = r.user_id
-- LEFT JOIN pull_requests p ON r.repo_id = p.repo_id
-- LEFT JOIN issues i ON r.repo_id = i.repo_id
-- GROUP BY
-- u.user_id
-- )
-- SELECT
-- username,
-- (other_prs + other_issues) / (own_prs + own_issues + 1.0) AS collaboration_ratio -- +1 to avoid division by zero
-- FROM
-- UserActivity
-- ORDER BY
-- collaboration_ratio DESC
-- LIMIT 5;
-- WITH LanguageDiversity AS (
-- SELECT
-- r.repo_id,
-- r.repo_name,
-- COUNT(DISTINCT rl.lang_id) AS num_languages,
-- SUM(rl.usage_percentage * rl.usage_percentage) AS concentration_score
-- FROM
-- repositories r
-- JOIN repository_languages rl ON r.repo_id = rl.repo_id
-- GROUP BY
-- r.repo_id
-- ),
-- DiversityScore AS (
-- SELECT
-- repo_name,
-- num_languages,
-- (1.0 / concentration_score) AS diversity_index -- Smaller concentration scores indicate less diversity
-- FROM
-- LanguageDiversity
-- )
-- SELECT
-- repo_name,
-- num_languages,
-- diversity_index
-- FROM
-- DiversityScore
-- ORDER BY
-- diversity_index DESC, num_languages DESC
-- LIMIT 5;
-- WITH RepoOwnership AS (
-- SELECT
-- p.user_id,
-- p.repo_id,
-- p.pr_id,
-- r.user_id AS repo_owner_id,
-- p.merged
-- FROM
-- pull_requests p
-- JOIN repositories r ON p.repo_id = r.repo_id
-- ),
-- UserPRAnalysis AS (
-- SELECT
-- user_id,
-- COUNT(pr_id) AS total_prs,
-- SUM(CASE WHEN merged = 1 AND user_id != repo_owner_id THEN 1 ELSE 0 END) AS merged_prs
-- FROM
-- RepoOwnership
-- WHERE
-- user_id != repo_owner_id
-- GROUP BY
-- user_id
-- ),
-- MergeRatios AS (
-- SELECT
-- user_id,
-- total_prs,
-- merged_prs,
-- (CAST(merged_prs AS REAL) / total_prs) AS merge_ratio
-- FROM
-- UserPRAnalysis
-- WHERE
-- total_prs > 0 -- Avoid division by zero
-- )
-- SELECT
-- u.username,
-- mr.total_prs,
-- mr.merged_prs,
-- mr.merge_ratio
-- FROM
-- MergeRatios mr
-- JOIN
-- users u ON mr.user_id = u.user_id
-- ORDER BY
-- merge_ratio DESC
-- LIMIT 1;
To embed this program on your website, copy the following code and paste it into your website's HTML: