-- Database schema for the personal code repository site.

-- Import once (e.g. via phpMyAdmin) into the database named in config.php.

CREATE TABLE IF NOT EXISTS repositories (

  id          INT AUTO_INCREMENT PRIMARY KEY,

  slug        VARCHAR(100) NOT NULL UNIQUE,

  name        VARCHAR(150) NOT NULL,

  description TEXT NULL,

  language    VARCHAR(50) NULL,

  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS files (

  id         INT AUTO_INCREMENT PRIMARY KEY,

  repo_id    INT NOT NULL,

  filename   VARCHAR(255) NOT NULL,           -- display name / relative path

  filepath   VARCHAR(500) NOT NULL,           -- path relative to uploads/

  filesize   INT NOT NULL DEFAULT 0,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (repo_id) REFERENCES repositories(id) ON DELETE CASCADE,

  UNIQUE KEY uniq_repo_file (repo_id, filename)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Folders are first-class so that empty (and manually created) folders persist.

-- Each nesting level is stored as its own row, e.g. "a/b/c" also stores "a"

-- and "a/b".

CREATE TABLE IF NOT EXISTS folders (

  id         INT AUTO_INCREMENT PRIMARY KEY,

  repo_id    INT NOT NULL,

  path       VARCHAR(500) NOT NULL,           -- relative path, e.g. "src/utils"

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (repo_id) REFERENCES repositories(id) ON DELETE CASCADE,

  UNIQUE KEY uniq_repo_folder (repo_id, path)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;