-- GitRobot (Pure PHP) Schema
-- Import this in phpMyAdmin.
-- Create DB first: gitrobot

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(39) NOT NULL UNIQUE,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

CREATE TABLE repositories (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  owner_id BIGINT NOT NULL,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(120) NOT NULL,
  visibility ENUM('public','private') NOT NULL DEFAULT 'public',
  description TEXT NULL,
  default_branch VARCHAR(100) NOT NULL DEFAULT 'main',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE(owner_id, slug),
  FOREIGN KEY (owner_id) REFERENCES users(id)
);

CREATE TABLE branches (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  repo_id BIGINT NOT NULL,
  name VARCHAR(120) NOT NULL,
  head_commit_id BIGINT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE(repo_id, name),
  FOREIGN KEY (repo_id) REFERENCES repositories(id)
);

CREATE TABLE commits (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  repo_id BIGINT NOT NULL,
  branch_name VARCHAR(120) NOT NULL,
  parent_commit_id BIGINT NULL,
  author_id BIGINT NOT NULL,
  message VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (repo_id) REFERENCES repositories(id),
  FOREIGN KEY (author_id) REFERENCES users(id)
);

CREATE TABLE blobs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  repo_id BIGINT NOT NULL,
  sha256 CHAR(64) NOT NULL,
  size_bytes BIGINT NOT NULL,
  storage_path VARCHAR(500) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE(repo_id, sha256),
  FOREIGN KEY (repo_id) REFERENCES repositories(id)
);

CREATE TABLE commit_files (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  commit_id BIGINT NOT NULL,
  file_path VARCHAR(700) NOT NULL,
  blob_id BIGINT NOT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE(commit_id, file_path),
  FOREIGN KEY (commit_id) REFERENCES commits(id),
  FOREIGN KEY (blob_id) REFERENCES blobs(id)
);

CREATE TABLE pull_requests (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  repo_id BIGINT NOT NULL,
  number INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  body MEDIUMTEXT NULL,
  state ENUM('open','closed','merged') NOT NULL DEFAULT 'open',
  source_branch VARCHAR(120) NOT NULL,
  target_branch VARCHAR(120) NOT NULL,
  author_id BIGINT NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE(repo_id, number),
  FOREIGN KEY (repo_id) REFERENCES repositories(id),
  FOREIGN KEY (author_id) REFERENCES users(id)
);

CREATE TABLE repo_collaborators (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  repo_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  role ENUM('read','write') NOT NULL DEFAULT 'read',
  created_at DATETIME NOT NULL,
  UNIQUE(repo_id, user_id),
  FOREIGN KEY (repo_id) REFERENCES repositories(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE issues (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  repo_id BIGINT NOT NULL,
  number INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  body MEDIUMTEXT NULL,
  state ENUM('open','closed') NOT NULL DEFAULT 'open',
  author_id BIGINT NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE(repo_id, number),
  FOREIGN KEY (repo_id) REFERENCES repositories(id),
  FOREIGN KEY (author_id) REFERENCES users(id)
);

CREATE TABLE issue_comments (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  issue_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  body MEDIUMTEXT NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (issue_id) REFERENCES issues(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);