const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');
require('dotenv').config({ path: '.env.local' });

function collectLocalStorageKeys(value, keys = new Set()) {
  if (typeof value === 'string') {
    const marker = '/api/local-storage/';
    const index = value.indexOf(marker);
    if (index >= 0) {
      keys.add(decodeURIComponent(value.slice(index + marker.length).split('?')[0]));
    }
    return keys;
  }
  if (Array.isArray(value)) {
    value.forEach(item => collectLocalStorageKeys(item, keys));
    return keys;
  }
  if (value && typeof value === 'object') {
    Object.values(value).forEach(item => collectLocalStorageKeys(item, keys));
  }
  return keys;
}

(async () => {
  const pool = new Pool({ connectionString: process.env.LOCAL_DB_URL });
  const client = await pool.connect();
  try {
    const works = await client.query(
      `SELECT id, result_url, thumbnail_url, params
       FROM works
       WHERE title LIKE 'codex-import-edge-%' OR prompt LIKE 'codex-import-edge-%'`,
    );
    const keys = new Set();
    for (const row of works.rows) {
      collectLocalStorageKeys(row.result_url, keys);
      collectLocalStorageKeys(row.thumbnail_url, keys);
      collectLocalStorageKeys(row.params, keys);
    }

    const deletedWorks = await client.query(
      `DELETE FROM works
       WHERE title LIKE 'codex-import-edge-%' OR prompt LIKE 'codex-import-edge-%'`,
    );
    const deletedAnnouncements = await client.query(
      `DELETE FROM announcements
       WHERE title LIKE 'codex-import-edge-%'`,
    );

    let deletedFiles = 0;
    const base = path.join(process.cwd(), 'local-storage');
    for (const key of keys) {
      const filePath = path.normalize(path.join(base, key));
      if (!filePath.startsWith(base)) continue;
      if (fs.existsSync(filePath)) {
        fs.unlinkSync(filePath);
        deletedFiles += 1;
      }
    }

    console.log(JSON.stringify({
      deletedWorks: deletedWorks.rowCount || 0,
      deletedAnnouncements: deletedAnnouncements.rowCount || 0,
      deletedFiles,
    }, null, 2));
  } finally {
    client.release();
    await pool.end();
  }
})().catch((error) => {
  console.error(error);
  process.exit(1);
});