import { realpath, stat } from 'node:fs/promises';
import { isAbsolute, resolve, sep } from 'node:path';
import { db } from 'officerdb/db';
import { and, eq } from 'drizzle-orm';
import { getOwnerHomeDir } from '@@/data-path';
import { kanbanProjects, type KanbanProject } from '../db/schema';

/**
 * The list of folders this board can show, and the rule about which folders those may be.
 *
 * ── Every path is confined to the owner's home ──
 *
 * A project path is caller-supplied and is then read from and WRITTEN to — `patchFrontmatter` edits
 * files under it, and `writeIndex` creates one beside it. So it goes through the same three checks
 * av-club's `paths.ts` applies, for the same reasons, and they are worth repeating rather than
 * cross-referencing because the next person to add a path-taking route will read whichever file they
 * are in:
 *
 *   ABSOLUTE — a relative path resolves against a working directory the caller cannot see. That is
 *     precisely the bug this extraction exists to fix, so accepting one here would be reintroducing it
 *     through the front door.
 *   REALPATH FIRST — a symlink inside home pointing at `/etc` is resolved and then rejected. Checking
 *     the path as written and opening the resolved one is the classic way this fails.
 *   `root` OR `root + sep` — never a bare `startsWith`, which accepts `/home/user-backup` for a root
 *     of `/home/user`.
 */

const inside = (root: string, target: string) => target === root || target.startsWith(root + sep);

export class ProjectPathError extends Error {}

/**
 * Validate a directory a caller wants to add.
 *
 * It must EXIST and be a directory. A project pointing at nothing is a row that produces an empty board
 * with no explanation, and the moment to say so is while somebody is looking at the form.
 */
export async function safeProjectPath(raw: unknown, email: string): Promise<string> {
  if (typeof raw !== 'string' || !raw.trim()) throw new ProjectPathError('A path is required.');

  const home = getOwnerHomeDir(email);
  const trimmed = raw.trim();
  const expanded = trimmed === '~' ? home : trimmed.startsWith('~/') ? resolve(home, trimmed.slice(2)) : trimmed;
  if (!isAbsolute(expanded)) throw new ProjectPathError('The path must be absolute.');

  const real = await realpath(resolve(expanded)).catch(() => null);
  // One message for "outside" and "missing" together: distinguishing them turns this into an oracle for
  // what exists elsewhere on the disk.
  if (!real || !inside(home, real)) throw new ProjectPathError('That is not a folder this board can read.');

  const info = await stat(real).catch(() => null);
  if (!info?.isDirectory()) throw new ProjectPathError('That path is not a directory.');

  return real;
}

export const listProjects = (userId: number): Promise<KanbanProject[]> =>
  db.select().from(kanbanProjects).where(eq(kanbanProjects.userId, userId)).orderBy(kanbanProjects.name);

/**
 * One project, scoped to its owner.
 *
 * The `userId` is in the WHERE clause rather than checked after the read. A query that fetches by id
 * and then compares is one `if` away from returning somebody else's row, and that `if` is exactly what
 * gets dropped in a refactor.
 */
export async function getProject(userId: number, id: number): Promise<KanbanProject | null> {
  const [row] = await db
    .select()
    .from(kanbanProjects)
    .where(and(eq(kanbanProjects.userId, userId), eq(kanbanProjects.id, id)))
    .limit(1);
  return row ?? null;
}

export async function addProject(userId: number, name: string, path: string): Promise<KanbanProject> {
  const [row] = await db.insert(kanbanProjects).values({ userId, name, path }).returning();
  return row!;
}

/** Removes the ROW. Never the folder — this plugin does not delete anybody's todo files, ever. */
export async function removeProject(userId: number, id: number): Promise<boolean> {
  const rows = await db
    .delete(kanbanProjects)
    .where(and(eq(kanbanProjects.userId, userId), eq(kanbanProjects.id, id)))
    .returning({ id: kanbanProjects.id });
  return rows.length > 0;
}