import { lstat, readdir, realpath, stat } from "node:fs/promises";
import path from "node:path";
import { randomUUID } from "node:crypto";

import { config, PROJECT_ROOT, WORKSPACE_ROOT } from "./config.ts";
import { json, parseJson, type Db } from "./db/index.ts";

export type ProjectType = "php" | "node" | "python" | "wordpress" | "static" | "mixed";

export interface ProjectRecord {
  id: string;
  name: string;
  path: string;
  relativePath: string;
  label: string;
  projectType: ProjectType;
  hasPackageJson: boolean;
  hasComposerJson: boolean;
  gitRepository: boolean;
  favorite: boolean;
  lastSelectedAt: string | null;
  recentActivityAt: string | null;
  parentProjectId: string | null;
}

interface ProjectMetadata {
  projectType: ProjectType;
  hasPackageJson: boolean;
  hasComposerJson: boolean;
  gitRepository: boolean;
}

const invalid = () => new Error("PROJECT_INVALID");
const normalized = (value: string) => value.normalize("NFKC").toLowerCase();

export function isDeniedProjectName(name: string): boolean {
  return !name || name.startsWith(".") || config.projectDenyList.has(normalized(name));
}

export async function validateWorkspacePath(requestedPath: string, allowNested = true): Promise<string> {
  if (!requestedPath || requestedPath.includes("\0")) throw invalid();
  const canonicalRoot = await realpath(WORKSPACE_ROOT);
  const resolved = path.resolve(canonicalRoot, requestedPath);
  const lexicalRelative = path.relative(canonicalRoot, resolved);
  if (!lexicalRelative || lexicalRelative.startsWith("..") || path.isAbsolute(lexicalRelative)) throw invalid();

  const segments = lexicalRelative.split(path.sep).filter(Boolean);
  if ((!allowNested && segments.length !== 1) || segments.some(isDeniedProjectName) || segments[0] === path.basename(PROJECT_ROOT)) throw invalid();

  let cursor = canonicalRoot;
  for (const segment of segments) {
    cursor = path.join(cursor, segment);
    if ((await lstat(cursor)).isSymbolicLink()) throw invalid();
  }

  const canonical = await realpath(resolved);
  const relative = path.relative(canonicalRoot, canonical);
  if (!relative || relative.startsWith("..") || path.isAbsolute(relative) || canonical === PROJECT_ROOT) throw invalid();
  if (!(await stat(canonical)).isDirectory()) throw invalid();
  return canonical;
}

async function detectProjectMetadata(canonicalPath: string): Promise<ProjectMetadata> {
  const entries = await readdir(canonicalPath, { withFileTypes: true });
  const names = new Map(entries.map((entry) => [entry.name.toLowerCase(), entry]));
  const hasPackageJson = names.has("package.json");
  const hasComposerJson = names.has("composer.json");
  const wordpress = names.has("wp-config.php") || names.get("wp-content")?.isDirectory() === true;
  const php = hasComposerJson || entries.some((entry) => entry.isFile() && entry.name.toLowerCase().endsWith(".php"));
  const python = ["pyproject.toml", "requirements.txt", "setup.py", "pipfile"].some((name) => names.has(name))
    || entries.some((entry) => entry.isFile() && entry.name.toLowerCase().endsWith(".py"));
  const kinds: ProjectType[] = [];
  if (wordpress) kinds.push("wordpress"); else if (php) kinds.push("php");
  if (hasPackageJson) kinds.push("node");
  if (python) kinds.push("python");
  const projectType: ProjectType = kinds.length > 1 ? "mixed" : kinds[0] ?? "static";
  const git = names.get(".git");
  return { projectType, hasPackageJson, hasComposerJson, gitRepository: Boolean(git?.isDirectory() || git?.isFile()) };
}

async function upsertProject(db: Db, canonicalPath: string, parentProjectId: string | null = null) {
  const metadata = await detectProjectMetadata(canonicalPath);
  const name = path.basename(canonicalPath);
  const relativePath = path.relative(WORKSPACE_ROOT, canonicalPath);
  let row = db.prepare("SELECT id FROM projects WHERE canonical_path = ?").get(canonicalPath) as any;
  if (!row) {
    row = { id: randomUUID() };
    db.prepare(`INSERT INTO projects
      (id, canonical_path, name, project_type, has_package_json, has_composer_json, git_repository, parent_project_id, relative_path, metadata_updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)`)
      .run(row.id, canonicalPath, name, metadata.projectType, Number(metadata.hasPackageJson), Number(metadata.hasComposerJson), Number(metadata.gitRepository), parentProjectId, relativePath);
  } else {
    db.prepare(`UPDATE projects SET name=?, project_type=?, has_package_json=?, has_composer_json=?, git_repository=?,
      parent_project_id=COALESCE(?, parent_project_id), relative_path=?, last_seen_at=CURRENT_TIMESTAMP, metadata_updated_at=CURRENT_TIMESTAMP WHERE id=?`)
      .run(name, metadata.projectType, Number(metadata.hasPackageJson), Number(metadata.hasComposerJson), Number(metadata.gitRepository), parentProjectId, relativePath, row.id);
  }
  return row.id as string;
}

function projectQuery(where: string) {
  return `SELECT p.*, COALESCE(pl.label, p.custom_label, p.name) label,
    CASE WHEN pf.project_id IS NULL THEN 0 ELSE 1 END favorite, pp.last_selected_at,
    (SELECT MAX(pra.created_at) FROM project_recent_activity pra WHERE pra.user_id=? AND pra.project_id=p.id) recent_activity_at
    FROM projects p
    LEFT JOIN project_labels pl ON pl.project_id=p.id AND pl.user_id=?
    LEFT JOIN project_favorites pf ON pf.project_id=p.id AND pf.user_id=?
    LEFT JOIN project_preferences pp ON pp.project_id=p.id AND pp.user_id=? ${where}`;
}

function serializeProject(row: any): ProjectRecord {
  return {
    id: row.id, name: row.name, path: row.canonical_path, relativePath: row.relative_path,
    label: row.label, projectType: row.project_type, hasPackageJson: Boolean(row.has_package_json),
    hasComposerJson: Boolean(row.has_composer_json), gitRepository: Boolean(row.git_repository),
    favorite: Boolean(row.favorite), lastSelectedAt: row.last_selected_at ?? null,
    recentActivityAt: row.recent_activity_at ?? null, parentProjectId: row.parent_project_id ?? null,
  };
}

export async function getProjectById(db: Db, userId: string, projectId: string): Promise<ProjectRecord> {
  const row = db.prepare(projectQuery("WHERE p.id=?")).get(userId, userId, userId, userId, projectId) as any;
  if (!row) throw invalid();
  const canonical = await validateWorkspacePath(row.canonical_path, true);
  if (canonical !== row.canonical_path) throw invalid();
  return serializeProject(row);
}

export async function discoverProjects(db: Db, userId: string): Promise<ProjectRecord[]> {
  const entries = await readdir(WORKSPACE_ROOT, { withFileTypes: true });
  for (const entry of entries) {
    if (!entry.isDirectory() || isDeniedProjectName(entry.name) || entry.name === path.basename(PROJECT_ROOT)) continue;
    try {
      const canonical = await validateWorkspacePath(entry.name, false);
      await upsertProject(db, canonical);
    } catch { /* Exclude unreadable, denied, and escaping paths. */ }
  }

  const rows = db.prepare(projectQuery("ORDER BY favorite DESC, COALESCE(pp.last_selected_at, '') DESC, label COLLATE NOCASE"))
    .all(userId, userId, userId, userId) as any[];
  const projects: ProjectRecord[] = [];
  for (const row of rows) {
    try {
      const canonical = await validateWorkspacePath(row.canonical_path, true);
      if (canonical === row.canonical_path) projects.push(serializeProject(row));
    } catch { /* Do not expose stale or newly denied paths. */ }
  }
  return projects;
}

export async function recentProjects(db: Db, userId: string): Promise<ProjectRecord[]> {
  const rows = db.prepare(projectQuery("WHERE pp.last_selected_at IS NOT NULL ORDER BY pp.last_selected_at DESC LIMIT 8"))
    .all(userId, userId, userId, userId) as any[];
  const projects: ProjectRecord[] = [];
  for (const row of rows) {
    try {
      const canonical = await validateWorkspacePath(row.canonical_path, true);
      if (canonical === row.canonical_path) projects.push(serializeProject(row));
    } catch { /* Never return stale or denied recent paths. */ }
  }
  return projects;
}

export async function browseProjectTree(db: Db, userId: string, projectId: string) {
  const project = await getProjectById(db, userId, projectId);
  const entries = await readdir(project.path, { withFileTypes: true });
  const children: ProjectRecord[] = [];
  for (const entry of entries) {
    if (!entry.isDirectory() || isDeniedProjectName(entry.name)) continue;
    try {
      const canonical = await validateWorkspacePath(path.join(project.path, entry.name), true);
      const id = await upsertProject(db, canonical, project.id);
      children.push(await getProjectById(db, userId, id));
    } catch { /* Skip unreadable, denied, and escaping folders. */ }
  }

  const parts = project.relativePath.split(path.sep).filter(Boolean);
  const breadcrumbs: ProjectRecord[] = [];
  let parentId: string | null = null;
  for (let index = 0; index < parts.length; index += 1) {
    const canonical = await validateWorkspacePath(path.join(WORKSPACE_ROOT, ...parts.slice(0, index + 1)), true);
    const id = await upsertProject(db, canonical, parentId);
    breadcrumbs.push(await getProjectById(db, userId, id));
    parentId = id;
  }
  return { project: await getProjectById(db, userId, project.id), breadcrumbs, children: children.sort((a, b) => a.label.localeCompare(b.label)) };
}

export function recordProjectActivity(db: Db, userId: string, projectId: string, activityType: string, summary: string, metadata: unknown = {}) {
  db.prepare("INSERT INTO project_recent_activity (user_id, project_id, activity_type, summary, metadata_json) VALUES (?, ?, ?, ?, ?)")
    .run(userId, projectId, activityType, summary, json(metadata));
}

export async function projectDetails(db: Db, userId: string, projectId: string) {
  const project = await getProjectById(db, userId, projectId);
  const counts = db.prepare(`SELECT
    (SELECT COUNT(*) FROM threads WHERE user_id=? AND project_id=?) conversations,
    (SELECT COUNT(*) FROM tasks t JOIN threads th ON th.id=t.thread_id WHERE th.user_id=? AND t.project_id=?) tasks,
    (SELECT COUNT(*) FROM tasks t JOIN threads th ON th.id=t.thread_id WHERE th.user_id=? AND t.project_id=? AND t.status IN ('queued','started','running')) active_tasks`)
    .get(userId, projectId, userId, projectId, userId, projectId) as any;
  const activity = (db.prepare("SELECT activity_type type, summary, metadata_json, created_at FROM project_recent_activity WHERE user_id=? AND project_id=? ORDER BY created_at DESC, id DESC LIMIT 20")
    .all(userId, projectId) as any[]).map((row) => ({ type: row.type, summary: row.summary, metadata: parseJson(row.metadata_json, {}), createdAt: row.created_at }));
  const recentFiles = db.prepare("SELECT canonical_path path, last_opened_at lastOpenedAt FROM project_recent_files WHERE user_id=? AND project_id=? ORDER BY last_opened_at DESC LIMIT 20").all(userId, projectId);
  return { ...project, stats: { conversations: counts.conversations, tasks: counts.tasks, activeTasks: counts.active_tasks }, activity, recentFiles };
}