// SF DB Writer — Markdown generators + DB-first write helpers
//
// The missing DB→markdown direction. S03 established markdown→DB (md-importer.ts).
// This module generates DECISIONS.md and REQUIREMENTS.md from DB state,
// computes next decision IDs, and provides write helpers that upsert to DB
// then regenerate the corresponding markdown file.
//
// Critical invariant: generated markdown must round-trip through
// parseDecisionsTable() and parseRequirementsSections() with field fidelity.

import { join, resolve } from 'node:path';
import { readFileSync, existsSync, statSync } from 'node:fs';
import type { Decision, Requirement } from './types.js';
import { resolveSfRootFile } from './paths.js';
import { saveFile } from './files.js';
import { SFError, SF_STALE_STATE, SF_IO_ERROR } from './errors.js';
import { logWarning, logError } from './workflow-logger.js';
import { invalidateStateCache } from './state.js';
import { clearPathCache } from './paths.js';
import { clearParseCache } from './files.js';

// ─── Freeform Detection ───────────────────────────────────────────────────

/**
 * Detect whether a DECISIONS.md file is in canonical table format
 * (generated by generateDecisionsMd).
 *
 * Returns true only if the file starts with the canonical header
 * ("# Decisions Register") that generateDecisionsMd produces.
 * Files with freeform content — even if they contain an appended
 * decisions table section — return false so the freeform content
 * is preserved.
 */
export function isDecisionsTableFormat(content: string): boolean {
  // The canonical format always starts with "# Decisions Register"
  const firstLine = content.split('\n')[0]?.trim() ?? '';
  if (firstLine !== '# Decisions Register') return false;

  // Additionally verify the file has the canonical table header
  return content.includes('| # | When | Scope | Decision | Choice | Rationale | Revisable?');
}

/**
 * Generate a minimal decisions table section (header + rows) for appending
 * to a freeform DECISIONS.md file.
 */
function generateDecisionsAppendBlock(decisions: Decision[]): string {
  const lines: string[] = [];
  lines.push('');
  lines.push('---');
  lines.push('');
  lines.push('## Decisions Table');
  lines.push('');
  lines.push('| # | When | Scope | Decision | Choice | Rationale | Revisable? | Made By |');
  lines.push('|---|------|-------|----------|--------|-----------|------------|---------|');

  for (const d of decisions) {
    const cells = [
      d.id,
      d.when_context,
      d.scope,
      d.decision,
      d.choice,
      d.rationale,
      d.revisable,
      d.made_by ?? 'agent',
    ].map(cell => (cell ?? '').replace(/\|/g, '\\|'));
    lines.push(`| ${cells.join(' | ')} |`);
  }

  return lines.join('\n') + '\n';
}

// ─── Markdown Generators ──────────────────────────────────────────────────

/**
 * Generate full DECISIONS.md content from an array of Decision objects.
 * Produces the canonical format: H1 header, HTML comment block, table header,
 * separator, and one data row per decision.
 *
 * Column order: #, When, Scope, Decision, Choice, Rationale, Revisable?
 */
export function generateDecisionsMd(decisions: Decision[]): string {
  const lines: string[] = [];

  lines.push('# Decisions Register');
  lines.push('');
  lines.push('<!-- Append-only. Never edit or remove existing rows.');
  lines.push('     To reverse a decision, add a new row that supersedes it.');
  lines.push('     Read this file at the start of any planning or research phase. -->');
  lines.push('');
  lines.push('| # | When | Scope | Decision | Choice | Rationale | Revisable? | Made By |');
  lines.push('|---|------|-------|----------|--------|-----------|------------|---------|');

  for (const d of decisions) {
    // Escape pipe characters within cell values to preserve table structure
    const cells = [
      d.id,
      d.when_context,
      d.scope,
      d.decision,
      d.choice,
      d.rationale,
      d.revisable,
      d.made_by ?? 'agent',
    ].map(cell => (cell ?? '').replace(/\|/g, '\\|'));

    lines.push(`| ${cells.join(' | ')} |`);
  }

  return lines.join('\n') + '\n';
}

// ─── Requirements Markdown Generator ──────────────────────────────────────

/** Status values that map to specific sections, in display order. */
const STATUS_SECTION_MAP: Array<{ status: string; heading: string }> = [
  { status: 'active', heading: 'Active' },
  { status: 'validated', heading: 'Validated' },
  { status: 'deferred', heading: 'Deferred' },
  { status: 'out-of-scope', heading: 'Out of Scope' },
];

/**
 * Generate full REQUIREMENTS.md content from an array of Requirement objects.
 * Groups requirements by status into sections (## Active, ## Validated, etc.),
 * each containing ### RXXX — Description headings with bullet fields.
 * Only emits sections that have content. Appends Traceability table and
 * Coverage Summary at the bottom.
 */
export function generateRequirementsMd(requirements: Requirement[]): string {
  const lines: string[] = [];

  lines.push('# Requirements');
  lines.push('');
  lines.push('This file is the explicit capability and coverage contract for the project.');
  lines.push('');

  // Group by status
  const byStatus = new Map<string, Requirement[]>();
  for (const r of requirements) {
    const status = (r.status || 'active').toLowerCase();
    if (!byStatus.has(status)) byStatus.set(status, []);
    byStatus.get(status)!.push(r);
  }

  // Emit sections in canonical order
  for (const { status, heading } of STATUS_SECTION_MAP) {
    const reqs = byStatus.get(status);
    if (!reqs || reqs.length === 0) continue;

    lines.push(`## ${heading}`);
    lines.push('');

    for (const r of reqs) {
      lines.push(`### ${r.id} — ${r.description || 'Untitled'}`);

      // Emit bullet fields — only those with content
      if (r.class) lines.push(`- Class: ${r.class}`);
      if (r.status) lines.push(`- Status: ${r.status}`);
      if (r.description) lines.push(`- Description: ${r.description}`);
      if (r.why) lines.push(`- Why it matters: ${r.why}`);
      if (r.source) lines.push(`- Source: ${r.source}`);
      if (r.primary_owner) lines.push(`- Primary owning slice: ${r.primary_owner}`);
      if (r.supporting_slices) lines.push(`- Supporting slices: ${r.supporting_slices}`);
      if (r.validation) lines.push(`- Validation: ${r.validation}`);
      if (r.notes) lines.push(`- Notes: ${r.notes}`);
      lines.push('');
    }
  }

  // Traceability table
  lines.push('## Traceability');
  lines.push('');
  lines.push('| ID | Class | Status | Primary owner | Supporting | Proof |');
  lines.push('|---|---|---|---|---|---|');

  for (const r of requirements) {
    const proof = r.validation || 'unmapped';
    lines.push(
      `| ${r.id} | ${r.class || ''} | ${r.status || ''} | ${r.primary_owner || 'none'} | ${r.supporting_slices || 'none'} | ${proof} |`,
    );
  }

  lines.push('');

  // Coverage Summary
  const activeCount = byStatus.get('active')?.length ?? 0;
  const validatedReqs = byStatus.get('validated') ?? [];
  const validatedIds = validatedReqs.map(r => r.id).join(', ');

  lines.push('## Coverage Summary');
  lines.push('');
  lines.push(`- Active requirements: ${activeCount}`);
  lines.push(`- Mapped to slices: ${activeCount}`);
  lines.push(`- Validated: ${validatedReqs.length}${validatedIds ? ` (${validatedIds})` : ''}`);
  lines.push(`- Unmapped active requirements: 0`);

  return lines.join('\n') + '\n';
}

// ─── Next Decision ID ─────────────────────────────────────────────────────

/**
 * Compute the next decision ID from the current DB state.
 * Queries MAX(CAST(SUBSTR(id, 2) AS INTEGER)) from decisions table.
 * Returns D001 if no decisions exist. Zero-pads to 3 digits.
 */
export async function nextDecisionId(): Promise<string> {
  try {
    const db = await import('./sf-db.js');
    const adapter = db._getAdapter();
    if (!adapter) return 'D001';

    const row = adapter
      .prepare('SELECT MAX(CAST(SUBSTR(id, 2) AS INTEGER)) as max_num FROM decisions')
      .get();

    const maxNum = row ? (row['max_num'] as number | null) : null;
    if (maxNum == null || isNaN(maxNum)) return 'D001';

    const next = maxNum + 1;
    return `D${String(next).padStart(3, '0')}`;
  } catch (err) {
    logError('manifest', 'nextDecisionId failed', { fn: 'nextDecisionId', error: String((err as Error).message) });
    return 'D001';
  }
}

// ─── Next Requirement ID ─────────────────────────────────────────────────

/**
 * Compute the next requirement ID from the current DB state.
 * Queries MAX(CAST(SUBSTR(id, 2) AS INTEGER)) from requirements table.
 * Returns R001 if no requirements exist. Zero-pads to 3 digits.
 */
export async function nextRequirementId(): Promise<string> {
  try {
    const db = await import('./sf-db.js');
    const adapter = db._getAdapter();
    if (!adapter) return 'R001';

    const row = adapter
      .prepare('SELECT MAX(CAST(SUBSTR(id, 2) AS INTEGER)) as max_num FROM requirements')
      .get();

    const maxNum = row ? (row['max_num'] as number | null) : null;
    if (maxNum == null || isNaN(maxNum)) return 'R001';

    const next = maxNum + 1;
    return `R${String(next).padStart(3, '0')}`;
  } catch (err) {
    logError('manifest', 'nextRequirementId failed', { fn: 'nextRequirementId', error: String((err as Error).message) });
    return 'R001';
  }
}

// ─── Save Requirement to DB + Regenerate Markdown ────────────────────────

export interface SaveRequirementFields {
  class: string;
  status?: string;
  description: string;
  why: string;
  source: string;
  primary_owner?: string;
  supporting_slices?: string;
  validation?: string;
  notes?: string;
}

/**
 * Save a new requirement to DB and regenerate REQUIREMENTS.md.
 * Auto-assigns the next ID via nextRequirementId().
 *
 * The ID computation and insert are wrapped in a single transaction
 * to prevent parallel race conditions (same pattern as saveDecisionToDb).
 *
 * Returns the assigned ID.
 */
export async function saveRequirementToDb(
  fields: SaveRequirementFields,
  basePath: string,
): Promise<{ id: string }> {
  try {
    const db = await import('./sf-db.js');

    // Atomic ID assignment + insert inside a transaction.
    const id = db.transaction(() => {
      const adapter = db._getAdapter();
      if (!adapter) throw new SFError(SF_STALE_STATE, "sf-db: No database open");

      const row = adapter
        .prepare('SELECT MAX(CAST(SUBSTR(id, 2) AS INTEGER)) as max_num FROM requirements')
        .get();
      const maxNum = row ? (row['max_num'] as number | null) : null;
      const nextId = (maxNum == null || isNaN(maxNum))
        ? 'R001'
        : `R${String(maxNum + 1).padStart(3, '0')}`;

      const requirement: Requirement = {
        id: nextId,
        class: fields.class,
        status: fields.status ?? 'active',
        description: fields.description,
        why: fields.why,
        source: fields.source,
        primary_owner: fields.primary_owner ?? '',
        supporting_slices: fields.supporting_slices ?? '',
        validation: fields.validation ?? '',
        notes: fields.notes ?? '',
        full_content: '',
        superseded_by: null,
      };

      db.upsertRequirement(requirement);
      return nextId;
    });

    // Fetch all requirements for full file regeneration
    const adapter = db._getAdapter();
    let allRequirements: Requirement[] = [];
    if (adapter) {
      const rows = adapter.prepare('SELECT * FROM requirements ORDER BY id').all();
      allRequirements = rows.map(row => ({
        id: row['id'] as string,
        class: row['class'] as string,
        status: row['status'] as string,
        description: row['description'] as string,
        why: row['why'] as string,
        source: row['source'] as string,
        primary_owner: row['primary_owner'] as string,
        supporting_slices: row['supporting_slices'] as string,
        validation: row['validation'] as string,
        notes: row['notes'] as string,
        full_content: row['full_content'] as string,
        superseded_by: (row['superseded_by'] as string) ?? null,
      }));
    }

    const nonSuperseded = allRequirements.filter(r => r.superseded_by == null);
    const md = generateRequirementsMd(nonSuperseded);
    const filePath = resolveSfRootFile(basePath, 'REQUIREMENTS');
    try {
      await saveFile(filePath, md);
    } catch (diskErr) {
      logError('manifest', 'disk write failed, rolling back DB row', { fn: 'saveRequirementToDb', error: String((diskErr as Error).message) });
      try {
        db.deleteRequirementById(id);
      } catch (rollbackErr) {
        logError('manifest', 'SPLIT BRAIN: disk write failed AND DB rollback failed — DB has orphaned row', { fn: 'saveRequirementToDb', id, error: String((rollbackErr as Error).message) });
      }
      throw diskErr;
    }
    invalidateStateCache();
    clearPathCache();
    clearParseCache();

    return { id };
  } catch (err) {
    logError('manifest', 'saveRequirementToDb failed', { fn: 'saveRequirementToDb', error: String((err as Error).message) });
    throw err;
  }
}

// ─── Save Decision to DB + Regenerate Markdown ────────────────────────────

export interface SaveDecisionFields {
  scope: string;
  decision: string;
  choice: string;
  rationale: string;
  revisable?: string;
  when_context?: string;
  made_by?: import('./types.js').DecisionMadeBy;
}

/**
 * Save a new decision to DB and regenerate DECISIONS.md.
 * Auto-assigns the next ID via nextDecisionId().
 *
 * The ID computation (SELECT MAX) and insert are wrapped in a single
 * transaction to prevent parallel tool calls from computing the same ID
 * and silently overwriting each other (#3326, #3339, #3459).
 *
 * Returns the assigned ID.
 */
export async function saveDecisionToDb(
  fields: SaveDecisionFields,
  basePath: string,
): Promise<{ id: string }> {
  try {
    const db = await import('./sf-db.js');

    // Atomic ID assignment + insert inside a transaction to prevent
    // parallel calls from racing on the same MAX(id) value.
    const id = db.transaction(() => {
      const adapter = db._getAdapter();
      if (!adapter) throw new SFError(SF_STALE_STATE, "sf-db: No database open");

      const row = adapter
        .prepare('SELECT MAX(CAST(SUBSTR(id, 2) AS INTEGER)) as max_num FROM decisions')
        .get();
      const maxNum = row ? (row['max_num'] as number | null) : null;
      const nextId = (maxNum == null || isNaN(maxNum))
        ? 'D001'
        : `D${String(maxNum + 1).padStart(3, '0')}`;

      db.upsertDecision({
        id: nextId,
        when_context: fields.when_context ?? '',
        scope: fields.scope,
        decision: fields.decision,
        choice: fields.choice,
        rationale: fields.rationale,
        revisable: fields.revisable ?? 'Yes',
        made_by: fields.made_by ?? 'agent',
        superseded_by: null,
      });

      return nextId;
    });

    // Fetch all decisions (including superseded for the full register)
    const adapter = db._getAdapter();
    let allDecisions: Decision[] = [];
    if (adapter) {
      const rows = adapter.prepare('SELECT * FROM decisions ORDER BY seq').all();
      allDecisions = rows.map(row => ({
        seq: row['seq'] as number,
        id: row['id'] as string,
        when_context: row['when_context'] as string,
        scope: row['scope'] as string,
        decision: row['decision'] as string,
        choice: row['choice'] as string,
        rationale: row['rationale'] as string,
        revisable: row['revisable'] as string,
        made_by: (row['made_by'] as string as import('./types.js').DecisionMadeBy) ?? 'agent',
        superseded_by: (row['superseded_by'] as string) ?? null,
      }));
    }

    const filePath = resolveSfRootFile(basePath, 'DECISIONS');

    // Check if existing DECISIONS.md has freeform (non-table) content.
    // If so, preserve that content and append/update the decisions table
    // at the end instead of overwriting the entire file.
    let existingContent: string | null = null;
    if (existsSync(filePath)) {
      existingContent = readFileSync(filePath, 'utf-8');
    }

    let md: string;
    if (existingContent && !isDecisionsTableFormat(existingContent)) {
      // Freeform content detected — preserve it and append decisions table.
      // Strip any previously appended decisions table section to avoid duplication.
      const marker = '---\n\n## Decisions Table';
      const markerIdx = existingContent.indexOf(marker);
      const freeformPart = markerIdx >= 0
        ? existingContent.substring(0, markerIdx).trimEnd()
        : existingContent.trimEnd();
      md = freeformPart + '\n' + generateDecisionsAppendBlock(allDecisions);
    } else {
      // Table format or no existing file — full regeneration (original behavior)
      md = generateDecisionsMd(allDecisions);
    }

    try {
      await saveFile(filePath, md);
    } catch (diskErr) {
      logError('manifest', 'disk write failed, rolling back DB row', { fn: 'saveDecisionToDb', error: String((diskErr as Error).message) });
      try {
        db.deleteDecisionById(id);
      } catch (rollbackErr) {
        logError('manifest', 'SPLIT BRAIN: disk write failed AND DB rollback failed — DB has orphaned row', { fn: 'saveDecisionToDb', id, error: String((rollbackErr as Error).message) });
      }
      throw diskErr;
    }
    // #2661: When a decision defers a slice, update the slice status in the DB
    // so the dispatcher skips it. Without this, STATE.md and DECISIONS.md are
    // in split-brain: the decision says "deferred" but the state still says
    // "active", causing auto-mode to keep dispatching the deferred work.
    try {
      const sliceRef = extractDeferredSliceRef(fields);
      if (sliceRef) {
        db.updateSliceStatus(sliceRef.milestoneId, sliceRef.sliceId, 'deferred');
      }
    } catch (deferErr) {
      // Non-fatal — log but don't fail the decision save
      logError('manifest', 'failed to update deferred slice status', {
        fn: 'saveDecisionToDb',
        error: String((deferErr as Error).message),
      });
    }

    // Invalidate file-read caches so deriveState() sees the updated markdown.
    // Do NOT clear the artifacts table — we just wrote to it intentionally.
    invalidateStateCache();
    clearPathCache();
    clearParseCache();

    return { id };
  } catch (err) {
    logError('manifest', 'saveDecisionToDb failed', { fn: 'saveDecisionToDb', error: String((err as Error).message) });
    throw err;
  }
}

/**
 * Extract a milestone/slice reference from a deferral decision.
 *
 * Detects deferrals by checking:
 *   - scope contains "defer" (e.g., "deferral", "defer")
 *   - choice or decision contains "defer" + an M###/S## pattern
 *
 * Returns { milestoneId, sliceId } if found, null otherwise.
 */
export function extractDeferredSliceRef(
  fields: Pick<SaveDecisionFields, 'scope' | 'decision' | 'choice'>,
): { milestoneId: string; sliceId: string } | null {
  const isDeferral =
    /\bdefer(?:ral|red|ring|s)?\b/i.test(fields.scope) ||
    /\bdefer(?:ral|red|ring|s)?\b/i.test(fields.choice) ||
    /\bdefer(?:ral|red|ring|s)?\b/i.test(fields.decision);

  if (!isDeferral) return null;

  // Look for M###/S## pattern in choice first, then decision
  const slicePattern = /\b(M\d{3,4})\/(S\d{2,3})\b/;
  const choiceMatch = fields.choice.match(slicePattern);
  if (choiceMatch) {
    return { milestoneId: choiceMatch[1], sliceId: choiceMatch[2] };
  }
  const decisionMatch = fields.decision.match(slicePattern);
  if (decisionMatch) {
    return { milestoneId: decisionMatch[1], sliceId: decisionMatch[2] };
  }

  return null;
}

// ─── Update Requirement in DB + Regenerate Markdown ───────────────────────

/**
 * Update a requirement in DB and regenerate REQUIREMENTS.md.
 * Fetches existing requirement, merges updates, upserts, then regenerates.
 */
export async function updateRequirementInDb(
  id: string,
  updates: Partial<Requirement>,
  basePath: string,
): Promise<void> {
  try {
    const db = await import('./sf-db.js');

    let existing = db.getRequirementById(id);

    // If requirement doesn't exist in DB, seed the entire requirements table
    // from REQUIREMENTS.md first (#3346). This handles the standard workflow
    // where requirements are authored in markdown during discussion but never
    // imported into the database — making sf_requirement_update always fail
    // with "not_found" at milestone completion.
    if (!existing) {
      const reqFilePath = resolveSfRootFile(basePath, 'REQUIREMENTS');
      try {
        const content = readFileSync(reqFilePath, 'utf-8');
        const { parseRequirementsSections } = await import('./md-importer.js');
        const parsed = parseRequirementsSections(content);
        if (parsed.length > 0) {
          logWarning('manifest', `Seeding ${parsed.length} requirements from REQUIREMENTS.md into DB (first update triggers import)`, { fn: 'updateRequirementInDb' });
          for (const req of parsed) {
            // Only seed if not already in DB (avoid overwriting concurrent inserts)
            if (!db.getRequirementById(req.id)) {
              db.upsertRequirement(req);
            }
          }
          // Re-check after seeding
          existing = db.getRequirementById(id);
        }
      } catch {
        // REQUIREMENTS.md missing or unparseable — fall through to skeleton
      }
    }

    const base: Requirement = existing ?? {
      id,
      class: '',
      status: 'active',
      description: '',
      why: '',
      source: '',
      primary_owner: '',
      supporting_slices: '',
      validation: '',
      notes: '',
      full_content: '',
      superseded_by: null,
    };

    // Merge updates into existing (or skeleton)
    const merged: Requirement = {
      ...base,
      ...updates,
      id: base.id, // ID cannot be changed
    };

    db.upsertRequirement(merged);

    // Fetch ALL requirements (including superseded) for full file regeneration
    const adapter = db._getAdapter();
    let allRequirements: Requirement[] = [];
    if (adapter) {
      const rows = adapter.prepare('SELECT * FROM requirements ORDER BY id').all();
      allRequirements = rows.map(row => ({
        id: row['id'] as string,
        class: row['class'] as string,
        status: row['status'] as string,
        description: row['description'] as string,
        why: row['why'] as string,
        source: row['source'] as string,
        primary_owner: row['primary_owner'] as string,
        supporting_slices: row['supporting_slices'] as string,
        validation: row['validation'] as string,
        notes: row['notes'] as string,
        full_content: row['full_content'] as string,
        superseded_by: (row['superseded_by'] as string) ?? null,
      }));
    }

    // Filter to non-superseded for the markdown file
    // (superseded requirements don't appear in section headings)
    const nonSuperseded = allRequirements.filter(r => r.superseded_by == null);

    const md = generateRequirementsMd(nonSuperseded);
    const filePath = resolveSfRootFile(basePath, 'REQUIREMENTS');
    try {
      await saveFile(filePath, md);
    } catch (diskErr) {
      logError('manifest', 'disk write failed, reverting DB row', { fn: 'updateRequirementInDb', error: String((diskErr as Error).message) });
      if (existing) {
        db.upsertRequirement(existing);
      }
      throw diskErr;
    }
    // Invalidate file-read caches so deriveState() sees the updated markdown.
    // Do NOT clear the artifacts table — we just wrote to it intentionally.
    invalidateStateCache();
    clearPathCache();
    clearParseCache();
  } catch (err) {
    logError('manifest', 'updateRequirementInDb failed', { fn: 'updateRequirementInDb', error: String((err as Error).message) });
    throw err;
  }
}

// ─── Save Artifact to DB + Disk ───────────────────────────────────────────

export interface SaveArtifactOpts {
  path: string;
  artifact_type: string;
  content: string;
  milestone_id?: string;
  slice_id?: string;
  task_id?: string;
}

/**
 * Save an artifact to DB and write the corresponding markdown file to disk.
 * The path is relative to .sf/ (e.g. "milestones/M001/slices/S06/tasks/T01-SUMMARY.md").
 * The full file path is computed as basePath + '.sf/' + path.
 */
export async function saveArtifactToDb(
  opts: SaveArtifactOpts,
  basePath: string,
): Promise<void> {
  try {
    const db = await import('./sf-db.js');

    // Guard against path traversal before any reads/writes
    const sfDir = resolve(basePath, '.sf');
    const fullPath = resolve(basePath, '.sf', opts.path);
    if (!fullPath.startsWith(sfDir)) {
      throw new SFError(SF_IO_ERROR, `saveArtifactToDb: path escapes .sf/ directory: ${opts.path}`);
    }

    // Shrinkage guard: if the file already exists and the new content is
    // significantly smaller (<50%), preserve the richer file on disk and
    // store its content in the DB instead of the abbreviated version.
    let dbContent = opts.content;
    let skipDiskWrite = false;
    if (existsSync(fullPath)) {
      const existingSize = statSync(fullPath).size;
      const newSize = Buffer.byteLength(opts.content, 'utf-8');
      if (existingSize > 0 && newSize < existingSize * 0.5) {
        logWarning('manifest', `new content (${newSize}B) is <50% of existing file (${existingSize}B), preserving disk file`, { fn: 'saveArtifactToDb', path: opts.path });
        dbContent = readFileSync(fullPath, 'utf-8');
        skipDiskWrite = true;
      }
    }

    db.insertArtifact({
      path: opts.path,
      artifact_type: opts.artifact_type,
      milestone_id: opts.milestone_id ?? null,
      slice_id: opts.slice_id ?? null,
      task_id: opts.task_id ?? null,
      full_content: dbContent,
    });

    // Write the file to disk (only if we're not preserving a richer existing file)
    if (!skipDiskWrite) {
      try {
        await saveFile(fullPath, opts.content);
      } catch (diskErr) {
        logError('manifest', 'disk write failed, rolling back DB row', { fn: 'saveArtifactToDb', error: String((diskErr as Error).message) });
        db.deleteArtifactByPath(opts.path);
        throw diskErr;
      }
    }
    // Invalidate file-read caches so deriveState() sees the updated markdown.
    // Do NOT clear the artifacts table — we just wrote to it intentionally.
    invalidateStateCache();
    clearPathCache();
    clearParseCache();
  } catch (err) {
    logError('manifest', 'saveArtifactToDb failed', { fn: 'saveArtifactToDb', error: String((err as Error).message) });
    throw err;
  }
}
