schema.sql

-- GitHub Profile Analyzer - MySQL Schema
-- Run: npm run db:migrate

CREATE DATABASE IF NOT EXISTS github_analyzer
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE github_analyzer;

CREATE TABLE IF NOT EXISTS github_profiles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(39) NOT NULL,
  display_name VARCHAR(255) NULL,
  bio TEXT NULL,
  avatar_url VARCHAR(512) NULL,
  profile_url VARCHAR(512) NOT NULL,
  location VARCHAR(255) NULL,
  company VARCHAR(255) NULL,
  blog VARCHAR(512) NULL,
  followers_count INT UNSIGNED NOT NULL DEFAULT 0,
  following_count INT UNSIGNED NOT NULL DEFAULT 0,
  public_repo_count INT UNSIGNED NOT NULL DEFAULT 0,
  total_stars INT UNSIGNED NOT NULL DEFAULT 0,
  total_forks INT UNSIGNED NOT NULL DEFAULT 0,
  original_repos INT UNSIGNED NOT NULL DEFAULT 0,
  forked_repos INT UNSIGNED NOT NULL DEFAULT 0,
  top_languages JSON NULL,
  most_starred_repo JSON NULL,
  account_created_at DATETIME NULL,
  account_age_days INT UNSIGNED NULL,
  last_repo_updated_at DATETIME NULL,
  insights JSON NULL,
  analyzed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_username (username),
  KEY idx_analyzed_at (analyzed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;