server/postgresMigration.js

import fs from 'fs/promises';
import path from 'path';
import { createClient } from '@supabase/supabase-js';
import { loadEncryptedJson, readEncryptedFile } from './cryptoUtils.js';
import {
  POSTGRES_STORAGE_DIR,
  POSTGRES_STORAGE_DB_DIR,
  POSTGRES_STORAGE_MANIFEST,
  DATA_ROOT,
  readPostgresStorageManifest,
  refreshStorageMode,
} from './dataPaths.js';
import {
  applyPostgresSchema,
  createRawPostgresPool,
  encryptBinaryPayload,
  encryptJsonPayload,
  getCreateSchemaSql,
  getDropSchemaSql,
  hasExternalPostgresConfig,
  getPostgresSchemaName,
  getSetSearchPathSql,
  makeLookupToken,
  makeOwnerLookup,
} from './postgres.js';
import { POSTGRES_SCHEMA_SQL } from './postgresSchema.js';
import { SUPABASE_URL } from './config.js';

const TEMP_TTL_MS = 24 * 60 * 60 * 1000;
const TEMP_INACTIVITY = 12 * 60 * 60 * 1000;
const FEEDBACK_AAD = 'feedback_tickets_v1';
const VERSION_FILE = path.join(DATA_ROOT, 'version.json');
const WEB_SEARCH_USAGE_FILE = path.join(DATA_ROOT, 'web-search-usage.json');
const MEDIA_INDEX_FILE = path.join(DATA_ROOT, 'media', 'index.json');
const MEDIA_BLOBS_DIR = path.join(DATA_ROOT, 'media', 'blobs');
const TEMP_STORE_FILE = path.join(DATA_ROOT, 'temp_sessions.json');
const MEMORIES_FILE = path.join(DATA_ROOT, 'memories', 'index.json');
const DELETED_CHATS_FILE = path.join(DATA_ROOT, 'deleted_chats', 'index.json');
const SYSTEM_PROMPTS_FILE = path.join(DATA_ROOT, 'system-prompts', 'index.json');
const FEEDBACK_FILE = path.join(DATA_ROOT, 'feedback_tickets.json');
const GUEST_REQUEST_FILE = path.join(DATA_ROOT, 'guest_request_counts.json');

function nowIso() {
  return new Date().toISOString();
}

function sessionAad(scopeType, sessionId) {
  return `chat-session:${scopeType}:${sessionId}`;
}

function guestStateLookup(tempId) {
  return makeLookupToken('guest-state', tempId);
}

function guestStateAad(tempId) {
  return `guest-state:${tempId}`;
}

function guestExpiryRecord(tempData) {
  const createdExpires = (tempData.created || Date.now()) + TEMP_TTL_MS;
  const inactiveExpires = (tempData.lastActive || Date.now()) + TEMP_INACTIVITY;
  return new Date(Math.min(createdExpires, inactiveExpires)).toISOString();
}

function shareTokenLookup(token) {
  return makeLookupToken('session-share-token', token);
}

function shareAad(recordId) {
  return `session-share:${recordId}`;
}

function promptLookup(userId) {
  return makeLookupToken('system-prompt', userId);
}

function promptAad(userId) {
  return `system-prompt:${userId}`;
}

function mediaEntryAad(id) {
  return `media-entry:${id}`;
}

function feedbackAad(id) {
  return `feedback:${id}`;
}

function usernameLookup(username) {
  return makeLookupToken('username', username);
}

function versionLookup(publicUrl) {
  return makeLookupToken('app-version', publicUrl);
}

function versionAad(publicUrl) {
  return `app-version:${publicUrl}`;
}

function requestLookup(ip) {
  return makeLookupToken('guest-request', ip);
}

function webUsageLookup(key) {
  return makeLookupToken('web-search-usage', key);
}

function webUsageAad(key, day) {
  return `web-search-usage:${key}:${day}`;
}

function mediaBlobAad(entry) {
  return `media:${entry.id}:${entry.ownerType}:${entry.ownerId}`;
}

function getTempStorageDir() {
  return `${POSTGRES_STORAGE_DIR}.tmp`;
}

function getTempStorageDbDir() {
  return path.join(getTempStorageDir(), path.basename(POSTGRES_STORAGE_DB_DIR));
}

function buildStorageManifest(report, backend = 'embedded') {
  return {
    formatVersion: 2,
    createdAt: report.completedAt,
    updatedAt: report.completedAt,
    storageMode: 'postgres',
    backend,
    dbDir: path.basename(POSTGRES_STORAGE_DB_DIR),
    status: report.status,
    pending: report.pending,
  };
}

const SQL_TABLE_COPY_PLANS = [
  {
    table: 'app_versions',
    reportKey: 'copiedAppVersions',
    columns: ['public_url_lookup', 'updated_at', 'payload'],
  },
  {
    table: 'guest_state',
    reportKey: 'copiedGuestStateRows',
    columns: ['owner_lookup', 'expires_at', 'updated_at', 'payload'],
  },
  {
    table: 'chat_sessions',
    reportKey: 'copiedChatSessions',
    columns: ['id', 'scope_type', 'owner_lookup', 'created_at', 'updated_at', 'expires_at', 'payload'],
  },
  {
    table: 'session_shares',
    reportKey: 'copiedSessionShares',
    columns: ['id', 'token_lookup', 'owner_lookup', 'created_at', 'payload'],
  },
  {
    table: 'deleted_chats',
    reportKey: 'copiedDeletedChats',
    columns: ['id', 'owner_lookup', 'purge_at', 'deleted_at', 'payload'],
  },
  {
    table: 'memories',
    reportKey: 'copiedMemories',
    columns: ['id', 'owner_lookup', 'created_at', 'updated_at', 'payload'],
  },
  {
    table: 'media_entries',
    reportKey: 'copiedMediaEntries',
    columns: [
      'id',
      'owner_lookup',
      'parent_id',
      'entry_type',
      'updated_at',
      'created_at',
      'trashed_at',
      'purge_at',
      'expires_at',
      'size_bytes',
      'payload',
    ],
  },
  {
    table: 'media_blobs',
    reportKey: 'copiedMediaBlobs',
    columns: ['entry_id', 'updated_at', 'payload'],
  },
  {
    table: 'system_prompts',
    reportKey: 'copiedSystemPrompts',
    columns: ['owner_lookup', 'updated_at', 'payload'],
  },
  {
    table: 'feedback_tickets',
    reportKey: 'copiedFeedbackTickets',
    columns: ['id', 'status', 'submitted_at', 'payload'],
  },
  {
    table: 'guest_request_counters',
    reportKey: 'copiedGuestRequestCounters',
    columns: ['key_lookup', 'expires_at', 'updated_at', 'payload'],
  },
  {
    table: 'web_search_usage',
    reportKey: 'copiedWebSearchUsageRows',
    columns: ['key_lookup', 'day_key', 'updated_at', 'payload'],
  },
  {
    table: 'user_settings',
    reportKey: 'copiedUserSettings',
    columns: ['owner_lookup', 'updated_at', 'payload'],
  },
  {
    table: 'user_profiles',
    reportKey: 'copiedUserProfiles',
    columns: ['owner_lookup', 'username_lookup', 'updated_at', 'payload'],
  },
  {
    table: 'device_sessions',
    reportKey: 'copiedDeviceSessions',
    columns: ['token_lookup', 'user_lookup', 'active', 'created_at', 'last_seen_at', 'payload'],
  },
];

async function fileExists(filePath) {
  try {
    await fs.access(filePath);
    return true;
  } catch {
    return false;
  }
}

async function readJsonIfExists(filePath) {
  try {
    return JSON.parse(await fs.readFile(filePath, 'utf8'));
  } catch {
    return null;
  }
}

async function fetchAllSupabaseRows(client, tableName) {
  const pageSize = 1000;
  const rows = [];
  let from = 0;
  while (true) {
    const { data, error } = await client
      .from(tableName)
      .select('*')
      .range(from, from + pageSize - 1);
    if (error) throw error;
    rows.push(...(data || []));
    if (!data || data.length < pageSize) break;
    from += pageSize;
  }
  return rows;
}

async function resetBootstrapSchema(pool) {
  const client = await pool.connect();
  try {
    await client.query('SET statement_timeout TO 0');
    await client.query(getDropSchemaSql());
    await client.query(getCreateSchemaSql());
    await client.query(getSetSearchPathSql());
  } finally {
    client.release();
  }
}

async function migrateVersions(pool, report) {
  const data = await readJsonIfExists(VERSION_FILE);
  const entries = Array.isArray(data) ? data : [];
  let count = 0;
  for (const entry of entries) {
    for (const [publicUrl, sha] of Object.entries(entry || {})) {
      await pool.query(
        `INSERT INTO app_versions (public_url_lookup, updated_at, payload)
         VALUES ($1, $2, $3::jsonb)`,
        [
          versionLookup(publicUrl),
          nowIso(),
          JSON.stringify(encryptJsonPayload({ publicUrl, sha }, versionAad(publicUrl))),
        ]
      );
      count += 1;
    }
  }
  report.migrated.versionEntries = count;
}

async function migrateTempSessions(pool, report) {
  const data = await loadEncryptedJson(TEMP_STORE_FILE);
  const records = data || {};
  let ownerCount = 0;
  let sessionCount = 0;

  for (const [tempId, tempData] of Object.entries(records)) {
    ownerCount += 1;
    const owner = { type: 'guest', id: tempId };
    await pool.query(
      `INSERT INTO guest_state (owner_lookup, expires_at, updated_at, payload)
       VALUES ($1, $2, $3, $4::jsonb)`,
      [
        guestStateLookup(tempId),
        guestExpiryRecord(tempData),
        nowIso(),
        JSON.stringify(encryptJsonPayload({
          tempId,
          msgCount: tempData.msgCount || 0,
          created: tempData.created || Date.now(),
          lastActive: tempData.lastActive || Date.now(),
        }, guestStateAad(tempId))),
      ]
    );

    for (const session of Object.values(tempData.sessions || {})) {
      await pool.query(
        `INSERT INTO chat_sessions (id, scope_type, owner_lookup, created_at, updated_at, expires_at, payload)
         VALUES ($1, $2, $3, $4, $5, $6, $7::jsonb)`,
        [
          session.id,
          'guest',
          makeOwnerLookup(owner),
          new Date(session.created || Date.now()).toISOString(),
          nowIso(),
          guestExpiryRecord(tempData),
          JSON.stringify(encryptJsonPayload(session, sessionAad('guest', session.id))),
        ]
      );
      sessionCount += 1;
    }
  }

  report.migrated.tempOwners = ownerCount;
  report.migrated.tempSessions = sessionCount;
}

async function migrateMemories(pool, report) {
  const data = await loadEncryptedJson(MEMORIES_FILE);
  const memories = Object.values(data?.memories || {});
  for (const memory of memories) {
    await pool.query(
      `INSERT INTO memories (id, owner_lookup, created_at, updated_at, payload)
       VALUES ($1, $2, $3, $4, $5::jsonb)`,
      [
        memory.id,
        makeOwnerLookup({ type: memory.ownerType, id: memory.ownerId }),
        memory.createdAt,
        memory.updatedAt,
        JSON.stringify(encryptJsonPayload(memory, `memory:${memory.id}`)),
      ]
    );
  }
  report.migrated.memories = memories.length;
}

async function migrateDeletedChats(pool, report) {
  const data = await loadEncryptedJson(DELETED_CHATS_FILE);
  const deletedChats = Object.values(data?.deletedChats || {});
  for (const record of deletedChats) {
    await pool.query(
      `INSERT INTO deleted_chats (id, owner_lookup, purge_at, deleted_at, payload)
       VALUES ($1, $2, $3, $4, $5::jsonb)`,
      [
        record.id,
        makeOwnerLookup({ type: record.ownerType, id: record.ownerId }),
        record.purgeAt || null,
        record.deletedAt,
        JSON.stringify(encryptJsonPayload(record, `deleted-chat:${record.id}`)),
      ]
    );
  }
  report.migrated.deletedChats = deletedChats.length;
}

async function migrateSystemPrompts(pool, report) {
  const data = await loadEncryptedJson(SYSTEM_PROMPTS_FILE, 'system-prompts');
  const prompts = Object.entries(data?.prompts || {});
  for (const [userId, prompt] of prompts) {
    const record = {
      userId,
      markdown: prompt.markdown,
      updatedAt: prompt.updatedAt || nowIso(),
    };
    await pool.query(
      `INSERT INTO system_prompts (owner_lookup, updated_at, payload)
       VALUES ($1, $2, $3::jsonb)`,
      [
        promptLookup(userId),
        record.updatedAt,
        JSON.stringify(encryptJsonPayload(record, promptAad(userId))),
      ]
    );
  }
  report.migrated.systemPrompts = prompts.length;
}

async function migrateFeedback(pool, report) {
  const data = await loadEncryptedJson(FEEDBACK_FILE, FEEDBACK_AAD);
  const tickets = Array.isArray(data?.tickets) ? data.tickets : [];
  for (const ticket of tickets) {
    await pool.query(
      `INSERT INTO feedback_tickets (id, status, submitted_at, payload)
       VALUES ($1, $2, $3, $4::jsonb)`,
      [
        ticket.id,
        ticket.status || 'open',
        ticket.submittedAt,
        JSON.stringify(encryptJsonPayload(ticket, feedbackAad(ticket.id))),
      ]
    );
  }
  report.migrated.feedbackTickets = tickets.length;
}

async function migrateGuestRequestCounters(pool, report) {
  const data = await loadEncryptedJson(GUEST_REQUEST_FILE);
  const entries = Object.entries(data || {});
  for (const [ip, entry] of entries) {
    await pool.query(
      `INSERT INTO guest_request_counters (key_lookup, expires_at, updated_at, payload)
       VALUES ($1, $2, $3, $4::jsonb)`,
      [
        requestLookup(ip),
        new Date(entry.resetAt || Date.now()).toISOString(),
        nowIso(),
        JSON.stringify(encryptJsonPayload({
          ip,
          count: entry.count || 0,
          resetAt: entry.resetAt || Date.now(),
        }, 'guest-request-row')),
      ]
    );
  }
  report.migrated.guestRequestCounters = entries.length;
}

async function migrateWebSearchUsage(pool, report) {
  const data = await readJsonIfExists(WEB_SEARCH_USAGE_FILE);
  const days = data?.days && typeof data.days === 'object' ? data.days : {};
  let count = 0;
  for (const [dayKey, keys] of Object.entries(days)) {
    for (const [key, used] of Object.entries(keys || {})) {
      await pool.query(
        `INSERT INTO web_search_usage (key_lookup, day_key, updated_at, payload)
         VALUES ($1, $2, $3, $4::jsonb)`,
        [
          webUsageLookup(key),
          dayKey,
          nowIso(),
          JSON.stringify(encryptJsonPayload({ used }, webUsageAad(key, dayKey))),
        ]
      );
      count += 1;
    }
  }
  report.migrated.webSearchUsageRows = count;
}

async function migrateMedia(pool, report) {
  const data = await loadEncryptedJson(MEDIA_INDEX_FILE);
  const entries = Object.values(data?.entries || {});
  let blobCount = 0;
  for (const entry of entries) {
    await pool.query(
      `INSERT INTO media_entries (
         id, owner_lookup, parent_id, entry_type, updated_at, created_at,
         trashed_at, purge_at, expires_at, size_bytes, payload
       )
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11::jsonb)`,
      [
        entry.id,
        makeOwnerLookup({ type: entry.ownerType, id: entry.ownerId }),
        entry.parentId || null,
        entry.type,
        entry.updatedAt || entry.createdAt,
        entry.createdAt,
        entry.trashedAt || null,
        entry.purgeAt || null,
        entry.expiresAt || null,
        entry.size || 0,
        JSON.stringify(encryptJsonPayload(entry, mediaEntryAad(entry.id))),
      ]
    );

    if (entry.type === 'file') {
      const blobPath = path.join(MEDIA_BLOBS_DIR, `${entry.id}.bin`);
      if (await fileExists(blobPath)) {
        const blob = await readEncryptedFile(blobPath, mediaBlobAad(entry));
        await pool.query(
          `INSERT INTO media_blobs (entry_id, updated_at, payload)
           VALUES ($1, $2, $3)`,
          [
            entry.id,
            entry.updatedAt || entry.createdAt,
            encryptBinaryPayload(blob, mediaBlobAad(entry)),
          ]
        );
        blobCount += 1;
      }
    }
  }
  report.migrated.mediaEntries = entries.length;
  report.migrated.mediaBlobs = blobCount;
}

async function migrateSupabaseData(pool, report) {
  const serviceRoleKey = process.env.SUPABASE_SERVICE_ROLE_KEY || '';
  if (!serviceRoleKey) {
    if (process.env.ALLOW_PARTIAL_SQL_MIGRATION === '1') {
      report.pending.push(
        'Supabase-backed user tables were not exported because SUPABASE_SERVICE_ROLE_KEY is not set.'
      );
      return;
    }
    throw new Error(
      'SUPABASE_SERVICE_ROLE_KEY is required to migrate existing Supabase-backed sessions, shares, settings, and profiles before enabling PostgreSQL-only mode. Set ALLOW_PARTIAL_SQL_MIGRATION=1 only if you intentionally want to skip those records.'
    );
  }

  const supabase = createClient(SUPABASE_URL, serviceRoleKey, {
    auth: { persistSession: false },
  });

  const [webSessions, sharedSessions, userSettings, profiles] = await Promise.all([
    fetchAllSupabaseRows(supabase, 'web_sessions'),
    fetchAllSupabaseRows(supabase, 'shared_sessions'),
    fetchAllSupabaseRows(supabase, 'user_settings'),
    fetchAllSupabaseRows(supabase, 'profiles'),
  ]);

  for (const row of webSessions) {
    const session = {
      id: row.id,
      name: row.name,
      created: new Date(row.created_at).getTime(),
      history: row.history || [],
      model: row.model || null,
    };
    await pool.query(
      `INSERT INTO chat_sessions (id, scope_type, owner_lookup, created_at, updated_at, expires_at, payload)
       VALUES ($1, $2, $3, $4, $5, $6, $7::jsonb)`,
      [
        row.id,
        'user',
        makeOwnerLookup({ type: 'user', id: row.user_id }),
        row.created_at,
        row.updated_at || nowIso(),
        null,
        JSON.stringify(encryptJsonPayload(session, sessionAad('user', row.id))),
      ]
    );
  }

  for (const row of sharedSessions) {
    const lookup = shareTokenLookup(row.token);
    const id = row.id || `share_${lookup.slice(0, 24)}`;
    await pool.query(
      `INSERT INTO session_shares (id, token_lookup, owner_lookup, created_at, payload)
       VALUES ($1, $2, $3, $4, $5::jsonb)`,
      [
        id,
        lookup,
        makeOwnerLookup({ type: 'user', id: row.owner_id }),
        row.created_at || nowIso(),
        JSON.stringify(encryptJsonPayload({
          id,
          ownerId: row.owner_id,
          sessionSnapshot: row.session_snapshot,
          createdAt: row.created_at || nowIso(),
        }, shareAad(id))),
      ]
    );
  }

  for (const row of userSettings) {
    await pool.query(
      `INSERT INTO user_settings (owner_lookup, updated_at, payload)
       VALUES ($1, $2, $3::jsonb)`,
      [
        makeOwnerLookup({ type: 'user', id: row.user_id }),
        row.updated_at || nowIso(),
        JSON.stringify(encryptJsonPayload({
          userId: row.user_id,
          settings: row.settings || {},
          updatedAt: row.updated_at || nowIso(),
        }, `user-settings:${row.user_id}`)),
      ]
    );
  }

  for (const row of profiles) {
    if (!row.id || !row.username) continue;
    await pool.query(
      `INSERT INTO user_profiles (owner_lookup, username_lookup, updated_at, payload)
       VALUES ($1, $2, $3, $4::jsonb)`,
      [
        makeOwnerLookup({ type: 'user', id: row.id }),
        usernameLookup(row.username),
        row.updated_at || nowIso(),
        JSON.stringify(encryptJsonPayload({
          userId: row.id,
          username: row.username,
          updatedAt: row.updated_at || nowIso(),
        }, `user-profile:${row.id}`)),
      ]
    );
  }

  report.migrated.supabaseWebSessions = webSessions.length;
  report.migrated.supabaseSharedSessions = sharedSessions.length;
  report.migrated.supabaseUserSettings = userSettings.length;
  report.migrated.supabaseProfiles = profiles.length;
}

async function writeStorageFolder(targetDir, report, backend = 'embedded') {
  await fs.mkdir(targetDir, { recursive: true });
  await fs.writeFile(
    path.join(targetDir, path.basename(POSTGRES_STORAGE_MANIFEST)),
    JSON.stringify(buildStorageManifest(report, backend), null, 2),
    'utf8'
  );
  await fs.writeFile(path.join(targetDir, 'schema.sql'), POSTGRES_SCHEMA_SQL, 'utf8');
  await fs.writeFile(path.join(targetDir, 'migration-report.json'), JSON.stringify(report, null, 2), 'utf8');
  await fs.writeFile(
    path.join(targetDir, 'README.txt'),
    [
      'This folder contains the encrypted PostgreSQL storage used by the backend.',
      `The actual embedded database files live in ./${path.basename(POSTGRES_STORAGE_DB_DIR)}/.`,
      'schema.sql contains the schema used for the migrated encrypted SQL backend.',
    ].join('\n'),
    'utf8'
  );
}

async function prepareTempEmbeddedTarget() {
  const tempDir = getTempStorageDir();
  await fs.rm(tempDir, { recursive: true, force: true });
  await fs.mkdir(tempDir, { recursive: true });
  return {
    tempDir,
    tempDbDir: getTempStorageDbDir(),
  };
}

async function activateEmbeddedStorageFolder(tempDir) {
  await fs.rm(POSTGRES_STORAGE_DIR, { recursive: true, force: true });
  await fs.rename(tempDir, POSTGRES_STORAGE_DIR);
  refreshStorageMode();
}

function buildInsertValueSql(table, columns) {
  return columns
    .map((column, index) => (column === 'payload' && table !== 'media_blobs'
      ? `$${index + 1}::jsonb`
      : `$${index + 1}`))
    .join(', ');
}

function normalizeCopiedSqlValue(table, column, value) {
  if (column === 'payload' && table !== 'media_blobs' && value && typeof value === 'object' && !Buffer.isBuffer(value)) {
    return JSON.stringify(value);
  }
  return value ?? null;
}

async function copySqlTable(sourcePool, targetPool, plan, report) {
  const selectSql = `SELECT ${plan.columns.join(', ')} FROM ${plan.table}`;
  const insertSql = `
    INSERT INTO ${plan.table} (${plan.columns.join(', ')})
    VALUES (${buildInsertValueSql(plan.table, plan.columns)})
  `;
  const { rows } = await sourcePool.query(selectSql);
  for (const row of rows) {
    await targetPool.query(
      insertSql,
      plan.columns.map((column) => normalizeCopiedSqlValue(plan.table, column, row[column]))
    );
  }
  report.migrated[plan.reportKey] = rows.length;
}

async function copyExistingSqlData(sourcePool, targetPool, report) {
  for (const plan of SQL_TABLE_COPY_PLANS) {
    await copySqlTable(sourcePool, targetPool, plan, report);
  }
}

async function hasMaterializedEmbeddedDatabase() {
  try {
    const entries = await fs.readdir(POSTGRES_STORAGE_DB_DIR);
    return entries.length > 0;
  } catch {
    return false;
  }
}

export async function migrateLegacyDataToPostgres({
  skipIfFolderExists = true,
  replaceExistingFolder = false,
  sourceLabel = 'legacy_files',
} = {}) {
  if (skipIfFolderExists && await fileExists(POSTGRES_STORAGE_DIR)) {
    refreshStorageMode();
    return {
      status: 'already_active',
      targetFolder: POSTGRES_STORAGE_DIR,
      skipped: true,
      migrated: {},
      pending: [],
    };
  }

  if (!skipIfFolderExists && !replaceExistingFolder && await fileExists(POSTGRES_STORAGE_DIR)) {
    throw new Error(`SQL storage folder already exists at ${POSTGRES_STORAGE_DIR}. Remove it before running the migration again.`);
  }

  await fs.mkdir(DATA_ROOT, { recursive: true });

  const { tempDir, tempDbDir } = await prepareTempEmbeddedTarget();
  const pool = await createRawPostgresPool({ backend: 'embedded', dataDir: tempDbDir });
  const report = {
    startedAt: nowIso(),
    source: sourceLabel,
    targetFolder: POSTGRES_STORAGE_DIR,
    targetDatabaseDir: POSTGRES_STORAGE_DB_DIR,
    targetSchema: getPostgresSchemaName(),
    targetBackend: 'embedded',
    migrated: {},
    pending: [],
  };

  try {
    await resetBootstrapSchema(pool);
    await applyPostgresSchema(pool);
    await migrateVersions(pool, report);
    await migrateTempSessions(pool, report);
    await migrateMemories(pool, report);
    await migrateDeletedChats(pool, report);
    await migrateSystemPrompts(pool, report);
    await migrateFeedback(pool, report);
    await migrateGuestRequestCounters(pool, report);
    await migrateWebSearchUsage(pool, report);
    await migrateMedia(pool, report);
    await migrateSupabaseData(pool, report);

    report.completedAt = nowIso();
    report.status = report.pending.length ? 'completed_with_pending_items' : 'completed';
  } finally {
    await pool.end();
  }

  await writeStorageFolder(tempDir, report, 'embedded');
  await activateEmbeddedStorageFolder(tempDir);
  return report;
}

export async function materializeEmbeddedPostgresStorage() {
  const manifest = readPostgresStorageManifest();
  if (!manifest) {
    return {
      skipped: true,
      reason: 'manifest_missing',
    };
  }

  if (await hasMaterializedEmbeddedDatabase()) {
    if (manifest.backend !== 'embedded') {
      const report = {
        startedAt: nowIso(),
        completedAt: nowIso(),
        source: 'manifest_refresh',
        targetFolder: POSTGRES_STORAGE_DIR,
        targetDatabaseDir: POSTGRES_STORAGE_DB_DIR,
        targetSchema: getPostgresSchemaName(),
        targetBackend: 'embedded',
        migrated: {},
        pending: [],
        status: 'completed',
      };
      await writeStorageFolder(POSTGRES_STORAGE_DIR, report, 'embedded');
      refreshStorageMode();
      return { upgraded: true, report };
    }
    return {
      skipped: true,
      reason: 'already_embedded',
    };
  }

  if (!hasExternalPostgresConfig()) {
    const report = await migrateLegacyDataToPostgres({
      skipIfFolderExists: false,
      replaceExistingFolder: true,
      sourceLabel: 'legacy_files_upgrade',
    });
    return { upgraded: true, report };
  }

  const { tempDir, tempDbDir } = await prepareTempEmbeddedTarget();
  const sourcePool = await createRawPostgresPool({ backend: 'external' });
  const targetPool = await createRawPostgresPool({ backend: 'embedded', dataDir: tempDbDir });
  const report = {
    startedAt: nowIso(),
    source: 'external_postgres',
    previousBackend: String(manifest.backend || 'external_marker'),
    targetFolder: POSTGRES_STORAGE_DIR,
    targetDatabaseDir: POSTGRES_STORAGE_DB_DIR,
    targetSchema: getPostgresSchemaName(),
    targetBackend: 'embedded',
    migrated: {},
    pending: [],
  };

  try {
    await resetBootstrapSchema(targetPool);
    await applyPostgresSchema(targetPool);
    await copyExistingSqlData(sourcePool, targetPool, report);
    report.completedAt = nowIso();
    report.status = 'completed';
  } finally {
    await sourcePool.end();
    await targetPool.end();
  }

  await writeStorageFolder(tempDir, report, 'embedded');
  await activateEmbeddedStorageFolder(tempDir);
  return { upgraded: true, report };
}

export async function runPostgresMigrationCli() {
  const report = await migrateLegacyDataToPostgres({ skipIfFolderExists: false });
  console.log('PostgreSQL migration complete.');
  console.log(JSON.stringify(report, null, 2));
}