-- 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;