import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";

/**
 * Projects table - caches scanned project information
 */
export const projects = sqliteTable("projects", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  path: text("path").notNull().unique(),
  type: text("type").notNull(), // 'git' | 'git-submodule' | 'non-git'
  projectMarker: text("project_marker"), // e.g., 'package.json', 'Cargo.toml'
  statusJson: text("status_json"), // JSON serialized GitStatus
  submoduleJson: text("submodule_json"), // JSON serialized SubmoduleInfo
  lastScanned: integer("last_scanned", { mode: "timestamp" }),
  lastModified: integer("last_modified", { mode: "timestamp" }),
});

/**
 * Remote status table - caches GitHub/remote info separately
 * (updated less frequently via background fetch)
 */
export const remoteStatus = sqliteTable("remote_status", {
  projectId: text("project_id")
    .primaryKey()
    .references(() => projects.id, { onDelete: "cascade" }),
  lastFetched: integer("last_fetched", { mode: "timestamp" }),
  unpulledCommits: integer("unpulled_commits"),
  remoteLastActivity: integer("remote_last_activity", { mode: "timestamp" }),
  remoteUrl: text("remote_url"),
});

/**
 * GitHub repos cache - stores fetched GitHub repository data
 */
export const githubRepos = sqliteTable("github_repos", {
  id: integer("id").primaryKey(),
  name: text("name").notNull(),
  fullName: text("full_name").notNull().unique(),
  owner: text("owner").notNull(),
  description: text("description"),
  htmlUrl: text("html_url"),
  sshUrl: text("ssh_url"),
  cloneUrl: text("clone_url"),
  isPrivate: integer("is_private", { mode: "boolean" }),
  isArchived: integer("is_archived", { mode: "boolean" }),
  isFork: integer("is_fork", { mode: "boolean" }),
  pushedAt: integer("pushed_at", { mode: "timestamp" }),
  updatedAt: integer("updated_at", { mode: "timestamp" }),
  defaultBranch: text("default_branch"),
  language: text("language"),
  size: integer("size"),
  stargazersCount: integer("stargazers_count"),
  forksCount: integer("forks_count"),
  openIssuesCount: integer("open_issues_count"),
  watchersCount: integer("watchers_count"),
  topics: text("topics"), // JSON array
  license: text("license"), // License name or null
  hasIssues: integer("has_issues", { mode: "boolean" }),
  hasWiki: integer("has_wiki", { mode: "boolean" }),
  hasDiscussions: integer("has_discussions", { mode: "boolean" }),
  lastFetched: integer("last_fetched", { mode: "timestamp" }),
});

/**
 * Config cache - stores last known config hash to detect changes
 */
export const configCache = sqliteTable("config_cache", {
  key: text("key").primaryKey(),
  value: text("value"),
  updatedAt: integer("updated_at", { mode: "timestamp" }),
});