const crypto = require("crypto");

const PERMISSION_LEVELS = Object.freeze(["edit", "edit_review", "edit_review_implement"]);
const STATUS_VALUES = Object.freeze(["draft", "published", "archived"]);
const VISIBILITY_VALUES = Object.freeze(["user", "mod", "admin"]);
const REVIEW_STATES = Object.freeze(["draft", "review_pending", "approved", "rejected"]);

function ensureTables(db) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS okf_entries (
      id TEXT PRIMARY KEY,
      slug TEXT NOT NULL UNIQUE,
      title TEXT NOT NULL,
      category TEXT NOT NULL DEFAULT '',
      tags_json TEXT NOT NULL DEFAULT '[]',
      aliases_json TEXT NOT NULL DEFAULT '[]',
      summary TEXT NOT NULL DEFAULT '',
      user_markdown TEXT NOT NULL DEFAULT '',
      moderator_markdown TEXT NOT NULL DEFAULT '',
      admin_markdown TEXT NOT NULL DEFAULT '',
      ai_facts_markdown TEXT NOT NULL DEFAULT '',
      source_links_json TEXT NOT NULL DEFAULT '[]',
      visibility TEXT NOT NULL DEFAULT 'user',
      status TEXT NOT NULL DEFAULT 'draft',
      review_state TEXT NOT NULL DEFAULT 'draft',
      created_by TEXT,
      updated_by TEXT,
      reviewed_by TEXT,
      published_by TEXT,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL,
      reviewed_at INTEGER,
      published_at INTEGER,
      archived_at INTEGER,
      deleted_at INTEGER
    );

    CREATE INDEX IF NOT EXISTS okf_entries_status_idx ON okf_entries (status, visibility, updated_at);
    CREATE INDEX IF NOT EXISTS okf_entries_slug_idx ON okf_entries (slug);

    CREATE TABLE IF NOT EXISTS okf_versions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entry_id TEXT NOT NULL,
      version_number INTEGER NOT NULL,
      changed_by TEXT,
      change_type TEXT NOT NULL,
      previous_json TEXT,
      next_json TEXT,
      note TEXT,
      created_at INTEGER NOT NULL
    );

    CREATE INDEX IF NOT EXISTS okf_versions_entry_idx ON okf_versions (entry_id, version_number);

    CREATE TABLE IF NOT EXISTS okf_permissions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id TEXT NOT NULL,
      level TEXT NOT NULL,
      granted_by TEXT,
      notes TEXT,
      created_at INTEGER NOT NULL,
      revoked_at INTEGER
    );

    CREATE INDEX IF NOT EXISTS okf_permissions_user_idx ON okf_permissions (user_id, revoked_at);
  `);
}

function okfPermissionForUser(db, userId) {
  if (!userId) return null;
  const row = db
    .prepare(
      "SELECT level FROM okf_permissions WHERE user_id = ? AND revoked_at IS NULL ORDER BY created_at DESC LIMIT 1"
    )
    .get(userId);
  return PERMISSION_LEVELS.includes(row?.level) ? row.level : null;
}

function accessForUser(db, user) {
  const okfPermission = okfPermissionForUser(db, user?.id);
  const isAdmin = Boolean(user?.isAdmin);
  const isMod = Boolean(user?.isAdmin || user?.isMod);
  return {
    authenticated: Boolean(user),
    isAdmin,
    isMod,
    okfPermission,
    canEdit: isAdmin || ["edit", "edit_review", "edit_review_implement"].includes(okfPermission),
    canReview: isAdmin || ["edit_review", "edit_review_implement"].includes(okfPermission),
    canImplement: isAdmin || okfPermission === "edit_review_implement",
    canManagePermissions: isAdmin,
    maxVisibility: isAdmin ? "admin" : isMod ? "mod" : "user"
  };
}

function canSeeEntry(access, entry, { management = false } = {}) {
  if (!access.authenticated || !entry || entry.deleted_at) return false;
  if (access.isAdmin) return true;
  if (management && access.canEdit) return true;
  if (entry.status !== "published") return false;
  if (entry.visibility === "admin") return false;
  if (entry.visibility === "mod" && !access.isMod) return false;
  return true;
}

function publicEntry(entry, access) {
  const base = {
    id: entry.id,
    slug: entry.slug,
    title: entry.title,
    category: entry.category,
    tags: parseJsonArray(entry.tags_json),
    aliases: parseJsonArray(entry.aliases_json),
    summary: entry.summary,
    user_markdown: entry.user_markdown,
    visibility: entry.visibility,
    status: entry.status,
    review_state: entry.review_state,
    updated_at: entry.updated_at,
    published_at: entry.published_at
  };
  if (access.isMod || access.isAdmin) {
    base.moderator_markdown = entry.moderator_markdown;
  }
  if (access.isAdmin || access.canEdit) {
    base.admin_markdown = entry.admin_markdown;
    base.ai_facts_markdown = entry.ai_facts_markdown;
    base.source_links = parseJsonArray(entry.source_links_json);
    base.created_by = entry.created_by;
    base.updated_by = entry.updated_by;
    base.reviewed_by = entry.reviewed_by;
    base.published_by = entry.published_by;
    base.created_at = entry.created_at;
    base.reviewed_at = entry.reviewed_at;
    base.archived_at = entry.archived_at;
  }
  return base;
}

function listEntries(db, filters = {}, user, options = {}) {
  const access = accessForUser(db, user);
  const rows = db.prepare("SELECT * FROM okf_entries WHERE deleted_at IS NULL ORDER BY updated_at DESC").all();
  return rows
    .filter((entry) => canSeeEntry(access, entry, options))
    .filter((entry) => matchesFilters(entry, filters, access, options))
    .map((entry) => publicEntry(entry, access));
}

function getEntryBySlug(db, slug, user, options = {}) {
  const entry = db.prepare("SELECT * FROM okf_entries WHERE slug = ?").get(normalizeSlug(slug));
  const access = accessForUser(db, user);
  if (!canSeeEntry(access, entry, options)) return null;
  return publicEntry(entry, access);
}

function getEditableEntry(db, slug) {
  const entry = db.prepare("SELECT * FROM okf_entries WHERE slug = ? AND deleted_at IS NULL").get(normalizeSlug(slug));
  return entry ? normalizeEntry(entry) : null;
}

function createEntry(db, values, actor) {
  const access = accessForUser(db, actor);
  if (!access.canEdit) throw new Error("OKF edit permission is required.");
  const now = Date.now();
  const entry = normalizeValues(values, {
    actor,
    existing: null,
    canImplement: access.canImplement,
    now
  });
  entry.id = crypto.randomUUID();
  entry.created_by = String(actor.id);
  entry.updated_by = String(actor.id);
  entry.created_at = now;
  entry.updated_at = now;

  db.prepare(
    "INSERT INTO okf_entries (id, slug, title, category, tags_json, aliases_json, summary, user_markdown, moderator_markdown, admin_markdown, ai_facts_markdown, source_links_json, visibility, status, review_state, created_by, updated_by, reviewed_by, published_by, created_at, updated_at, reviewed_at, published_at, archived_at, deleted_at) " +
      "VALUES (@id, @slug, @title, @category, @tags_json, @aliases_json, @summary, @user_markdown, @moderator_markdown, @admin_markdown, @ai_facts_markdown, @source_links_json, @visibility, @status, @review_state, @created_by, @updated_by, @reviewed_by, @published_by, @created_at, @updated_at, @reviewed_at, @published_at, @archived_at, @deleted_at)"
  ).run(entry);
  addVersion(db, entry.id, "create", null, entry, actor, values.change_note || "Entry created.");
  return normalizeEntry(entry);
}

function updateEntry(db, slug, values, actor) {
  const access = accessForUser(db, actor);
  if (!access.canEdit) throw new Error("OKF edit permission is required.");
  const current = db.prepare("SELECT * FROM okf_entries WHERE slug = ? AND deleted_at IS NULL").get(normalizeSlug(slug));
  if (!current) throw new Error("OKF entry was not found.");
  const now = Date.now();
  const next = normalizeValues(values, {
    actor,
    existing: current,
    canImplement: access.canImplement,
    now
  });
  next.id = current.id;
  next.created_by = current.created_by;
  next.created_at = current.created_at;
  next.updated_by = String(actor.id);
  next.updated_at = now;
  next.reviewed_by = current.reviewed_by;
  next.reviewed_at = current.reviewed_at;
  next.published_by = current.published_by;
  next.published_at = current.published_at;
  next.archived_at = next.status === "archived" ? (current.archived_at || now) : null;
  next.deleted_at = null;

  db.prepare(
    "UPDATE okf_entries SET slug = @slug, title = @title, category = @category, tags_json = @tags_json, aliases_json = @aliases_json, summary = @summary, user_markdown = @user_markdown, moderator_markdown = @moderator_markdown, admin_markdown = @admin_markdown, ai_facts_markdown = @ai_facts_markdown, source_links_json = @source_links_json, visibility = @visibility, status = @status, review_state = @review_state, updated_by = @updated_by, updated_at = @updated_at, reviewed_by = @reviewed_by, published_by = @published_by, reviewed_at = @reviewed_at, published_at = @published_at, archived_at = @archived_at, deleted_at = @deleted_at WHERE id = @id"
  ).run(next);
  addVersion(db, next.id, "update", current, next, actor, values.change_note || "Entry updated.");
  return normalizeEntry(next);
}

function setEntryWorkflow(db, slug, action, actor, note = "") {
  const access = accessForUser(db, actor);
  const entry = db.prepare("SELECT * FROM okf_entries WHERE slug = ? AND deleted_at IS NULL").get(normalizeSlug(slug));
  if (!entry) throw new Error("OKF entry was not found.");
  const next = { ...entry };
  const now = Date.now();
  if (action === "review") {
    if (!access.canReview) throw new Error("OKF review permission is required.");
    next.review_state = "approved";
    next.reviewed_by = String(actor.id);
    next.reviewed_at = now;
  } else if (action === "publish") {
    if (!access.canImplement) throw new Error("OKF publish permission is required.");
    next.status = "published";
    next.review_state = "approved";
    next.published_by = String(actor.id);
    next.published_at = now;
    next.archived_at = null;
  } else if (action === "archive") {
    if (!access.canImplement) throw new Error("OKF archive permission is required.");
    next.status = "archived";
    next.archived_at = now;
  } else if (action === "restore") {
    if (!access.canImplement) throw new Error("OKF restore permission is required.");
    next.status = "draft";
    next.archived_at = null;
  } else if (action === "delete") {
    if (!access.canImplement) throw new Error("OKF delete permission is required.");
    next.deleted_at = now;
  } else {
    throw new Error("Unsupported OKF workflow action.");
  }
  next.updated_by = String(actor.id);
  next.updated_at = now;
  db.prepare(
    "UPDATE okf_entries SET status = @status, review_state = @review_state, reviewed_by = @reviewed_by, published_by = @published_by, updated_by = @updated_by, updated_at = @updated_at, reviewed_at = @reviewed_at, published_at = @published_at, archived_at = @archived_at, deleted_at = @deleted_at WHERE id = @id"
  ).run(next);
  addVersion(db, next.id, action, entry, next, actor, note || `Workflow action: ${action}.`);
  return normalizeEntry(next);
}

function listVersions(db, entryId) {
  return db
    .prepare("SELECT * FROM okf_versions WHERE entry_id = ? ORDER BY version_number DESC")
    .all(entryId)
    .map((row) => ({
      ...row,
      previous: parseJsonObject(row.previous_json),
      next: parseJsonObject(row.next_json)
    }));
}

function restoreVersion(db, entryId, versionNumber, actor, note = "") {
  const access = accessForUser(db, actor);
  if (!access.canImplement) throw new Error("OKF restore permission is required.");
  const current = db.prepare("SELECT * FROM okf_entries WHERE id = ? AND deleted_at IS NULL").get(entryId);
  if (!current) throw new Error("OKF entry was not found.");
  const version = db
    .prepare("SELECT * FROM okf_versions WHERE entry_id = ? AND version_number = ?")
    .get(entryId, Number(versionNumber));
  const snapshot = parseJsonObject(version?.next_json);
  if (!version || !snapshot.title || !snapshot.slug) {
    throw new Error("OKF version snapshot is not restorable.");
  }
  const now = Date.now();
  const restored = {
    ...current,
    slug: normalizeSlug(snapshot.slug),
    title: cleanText(snapshot.title, 180),
    category: cleanText(snapshot.category, 120),
    tags_json: JSON.stringify(splitList(snapshot.tags || [])),
    aliases_json: JSON.stringify(splitLines(snapshot.aliases || [])),
    summary: cleanText(snapshot.summary, 800),
    user_markdown: cleanText(snapshot.user_markdown, 24000),
    moderator_markdown: cleanText(snapshot.moderator_markdown, 24000),
    admin_markdown: cleanText(snapshot.admin_markdown, 24000),
    ai_facts_markdown: cleanText(snapshot.ai_facts_markdown, 24000),
    source_links_json: JSON.stringify(cleanLinkList(snapshot.source_links || [])),
    visibility: normalizeChoice(snapshot.visibility, VISIBILITY_VALUES, "user"),
    status: normalizeChoice(snapshot.status, STATUS_VALUES, "draft"),
    review_state: normalizeChoice(snapshot.review_state, REVIEW_STATES, "draft"),
    updated_by: String(actor.id),
    updated_at: now,
    reviewed_by: snapshot.review_state === "approved" ? current.reviewed_by : null,
    published_by: snapshot.status === "published" ? current.published_by : null,
    reviewed_at: snapshot.review_state === "approved" ? current.reviewed_at : null,
    published_at: snapshot.status === "published" ? current.published_at : null,
    archived_at: snapshot.status === "archived" ? (current.archived_at || now) : null,
    deleted_at: null
  };
  db.prepare(
    "UPDATE okf_entries SET slug = @slug, title = @title, category = @category, tags_json = @tags_json, aliases_json = @aliases_json, summary = @summary, user_markdown = @user_markdown, moderator_markdown = @moderator_markdown, admin_markdown = @admin_markdown, ai_facts_markdown = @ai_facts_markdown, source_links_json = @source_links_json, visibility = @visibility, status = @status, review_state = @review_state, updated_by = @updated_by, updated_at = @updated_at, reviewed_by = @reviewed_by, published_by = @published_by, reviewed_at = @reviewed_at, published_at = @published_at, archived_at = @archived_at, deleted_at = @deleted_at WHERE id = @id"
  ).run(restored);
  addVersion(
    db,
    restored.id,
    "restore_version",
    current,
    restored,
    actor,
    note || `Restored version ${Number(versionNumber)}.`
  );
  return normalizeEntry(restored);
}

function grantPermission(db, values, actor) {
  if (!actor?.isAdmin) throw new Error("Only admins can grant OKF permissions.");
  const userId = String(values.user_id || "").trim();
  const level = String(values.level || "").trim();
  if (!userId) throw new Error("Select a user.");
  if (!PERMISSION_LEVELS.includes(level)) throw new Error("Select a valid OKF permission.");
  const now = Date.now();
  db.prepare("UPDATE okf_permissions SET revoked_at = ? WHERE user_id = ? AND revoked_at IS NULL").run(now, userId);
  db.prepare(
    "INSERT INTO okf_permissions (user_id, level, granted_by, notes, created_at, revoked_at) VALUES (?, ?, ?, ?, ?, NULL)"
  ).run(userId, level, String(actor.id), cleanText(values.notes, 500), now);
}

function revokePermission(db, id, actor) {
  if (!actor?.isAdmin) throw new Error("Only admins can revoke OKF permissions.");
  db.prepare("UPDATE okf_permissions SET revoked_at = ? WHERE id = ? AND revoked_at IS NULL").run(Date.now(), Number(id));
}

function listPermissions(db) {
  return db
    .prepare(
      "SELECT okf_permissions.*, user_profiles.internal_username AS user_name, grantor.internal_username AS granted_by_name " +
        "FROM okf_permissions " +
        "LEFT JOIN user_profiles ON user_profiles.id = okf_permissions.user_id " +
        "LEFT JOIN user_profiles AS grantor ON grantor.id = okf_permissions.granted_by " +
        "ORDER BY okf_permissions.revoked_at IS NULL DESC, okf_permissions.created_at DESC"
    )
    .all();
}

function searchForAi(db, query, user, limit = 5) {
  const access = accessForUser(db, user);
  if (!access.authenticated) return [];
  return listEntries(db, { q: query }, user)
    .filter((entry) => entry.status === "published" && entry.review_state === "approved")
    .slice(0, limit)
    .map((entry) => ({
      id: entry.id,
      slug: entry.slug,
      title: entry.title,
      category: entry.category,
      visibility: entry.visibility,
      summary: entry.summary,
      facts: [entry.user_markdown, entry.moderator_markdown, entry.admin_markdown, entry.ai_facts_markdown]
        .filter(Boolean)
        .join("\n\n")
        .slice(0, 4000),
      source: `/plugins/okf/${entry.slug}`
    }));
}

function matchesFilters(entry, filters, access, options) {
  if (filters.status && entry.status !== filters.status) return false;
  if (filters.category && entry.category !== filters.category) return false;
  if (filters.tag && !parseJsonArray(entry.tags_json).includes(filters.tag)) return false;
  const q = String(filters.q || "").trim().toLowerCase();
  if (!q) return true;
  const visibleText = [
    entry.title,
    entry.category,
    entry.summary,
    entry.user_markdown,
    entry.tags_json,
    entry.aliases_json
  ];
  if (access.isMod || access.isAdmin || options.management) visibleText.push(entry.moderator_markdown);
  if (access.isAdmin || options.management) visibleText.push(entry.admin_markdown, entry.ai_facts_markdown, entry.source_links_json);
  return visibleText.join(" ").toLowerCase().includes(q);
}

function normalizeValues(values, { actor, existing, canImplement, now }) {
  const status = canImplement
    ? normalizeChoice(values.status, STATUS_VALUES, existing?.status || "draft")
    : existing?.status || "draft";
  const reviewState = canImplement
    ? normalizeChoice(values.review_state, REVIEW_STATES, existing?.review_state || "draft")
    : "review_pending";
  const publishedAt = status === "published" ? (existing?.published_at || now) : existing?.published_at || null;
  return {
    slug: normalizeSlug(values.slug || existing?.slug || values.title),
    title: cleanText(values.title || existing?.title, 180),
    category: cleanText(values.category ?? existing?.category, 120),
    tags_json: JSON.stringify(splitList(values.tags ?? parseJsonArray(existing?.tags_json))),
    aliases_json: JSON.stringify(splitLines(values.aliases ?? parseJsonArray(existing?.aliases_json))),
    summary: cleanText(values.summary ?? existing?.summary, 800),
    user_markdown: cleanText(values.user_markdown ?? existing?.user_markdown, 24000),
    moderator_markdown: cleanText(values.moderator_markdown ?? existing?.moderator_markdown, 24000),
    admin_markdown: cleanText(values.admin_markdown ?? existing?.admin_markdown, 24000),
    ai_facts_markdown: cleanText(values.ai_facts_markdown ?? existing?.ai_facts_markdown, 24000),
    source_links_json: JSON.stringify(cleanLinkList(values.source_links ?? parseJsonArray(existing?.source_links_json))),
    visibility: normalizeChoice(values.visibility, VISIBILITY_VALUES, existing?.visibility || "user"),
    status,
    review_state: reviewState,
    reviewed_by: existing?.reviewed_by || null,
    published_by: publishedAt && !existing?.published_by ? String(actor.id) : existing?.published_by || null,
    reviewed_at: existing?.reviewed_at || null,
    published_at: publishedAt,
    archived_at: existing?.archived_at || null,
    deleted_at: null
  };
}

function normalizeEntry(entry) {
  return {
    ...entry,
    tags: parseJsonArray(entry.tags_json),
    aliases: parseJsonArray(entry.aliases_json),
    source_links: parseJsonArray(entry.source_links_json)
  };
}

function addVersion(db, entryId, changeType, previous, next, actor, note) {
  const row = db.prepare("SELECT MAX(version_number) AS latest FROM okf_versions WHERE entry_id = ?").get(entryId);
  const version = Number(row?.latest || 0) + 1;
  db.prepare(
    "INSERT INTO okf_versions (entry_id, version_number, changed_by, change_type, previous_json, next_json, note, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
  ).run(
    entryId,
    version,
    actor?.id ? String(actor.id) : null,
    changeType,
    previous ? JSON.stringify(snapshotEntry(previous)) : null,
    next ? JSON.stringify(snapshotEntry(next)) : null,
    cleanText(note, 1000),
    Date.now()
  );
}

function snapshotEntry(entry) {
  return {
    slug: entry.slug,
    title: entry.title,
    category: entry.category,
    tags: parseJsonArray(entry.tags_json),
    aliases: parseJsonArray(entry.aliases_json),
    summary: entry.summary,
    user_markdown: entry.user_markdown,
    moderator_markdown: entry.moderator_markdown,
    admin_markdown: entry.admin_markdown,
    ai_facts_markdown: entry.ai_facts_markdown,
    source_links: parseJsonArray(entry.source_links_json),
    visibility: entry.visibility,
    status: entry.status,
    review_state: entry.review_state
  };
}

function normalizeSlug(value) {
  const slug = String(value || "")
    .trim()
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/^-+|-+$/g, "")
    .slice(0, 120);
  if (!slug) throw new Error("OKF slug is required.");
  return slug;
}

function cleanText(value, maximum = 4000) {
  return String(value || "").replace(/\r\n?/g, "\n").trim().slice(0, maximum);
}

function splitList(value) {
  if (Array.isArray(value)) return value.map((item) => cleanText(item, 120)).filter(Boolean).slice(0, 50);
  return String(value || "")
    .split(/[,\n]/)
    .map((item) => cleanText(item, 120))
    .filter(Boolean)
    .slice(0, 50);
}

function splitLines(value) {
  if (Array.isArray(value)) return value.map((item) => cleanText(item, 240)).filter(Boolean).slice(0, 50);
  return String(value || "")
    .split(/\n+/)
    .map((item) => cleanText(item, 240))
    .filter(Boolean)
    .slice(0, 50);
}

function cleanLinkList(value) {
  return splitList(value)
    .filter((item) => /^(https?:\/\/|\/(?!\/)|#)/i.test(item))
    .slice(0, 50);
}

function normalizeChoice(value, allowed, fallback) {
  const normalized = String(value || "").trim();
  return allowed.includes(normalized) ? normalized : fallback;
}

function parseJsonArray(value) {
  if (Array.isArray(value)) return value;
  try {
    const parsed = JSON.parse(value || "[]");
    return Array.isArray(parsed) ? parsed : [];
  } catch {
    return [];
  }
}

function parseJsonObject(value) {
  try {
    const parsed = JSON.parse(value || "{}");
    return parsed && typeof parsed === "object" && !Array.isArray(parsed) ? parsed : {};
  } catch {
    return {};
  }
}

module.exports = {
  PERMISSION_LEVELS,
  REVIEW_STATES,
  STATUS_VALUES,
  VISIBILITY_VALUES,
  accessForUser,
  createEntry,
  ensureTables,
  getEditableEntry,
  getEntryBySlug,
  grantPermission,
  listEntries,
  listPermissions,
  listVersions,
  okfPermissionForUser,
  revokePermission,
  restoreVersion,
  searchForAi,
  setEntryWorkflow,
  updateEntry
};