/**
 * Push chart_of_accounts from db/chart_of_accounts.json into PostgreSQL.
 * Requires DATABASE_URL to be set in environment (reads from .env.local).
 *
 * Usage: node scripts/push-chart-to-postgres.mjs
 */

import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const root = path.resolve(__dirname, '..');

// Read .env.local for DATABASE_URL
function getDbUrl() {
  const envFile = path.join(root, '.env.local');
  if (!fs.existsSync(envFile)) throw new Error('.env.local not found');
  const content = fs.readFileSync(envFile, 'utf-8');
  const match = content.match(/^\s*DATABASE_URL\s*=\s*["']?([^"'\r\n]+)["']?/m);
  if (!match) throw new Error('DATABASE_URL not found in .env.local');
  return match[1].trim();
}

const DB_FILE = path.join(root, 'db', 'chart_of_accounts.json');
const accounts = JSON.parse(fs.readFileSync(DB_FILE, 'utf-8'));

const { default: pg } = await import('pg');
const { Client } = pg;

const client = new Client({ connectionString: getDbUrl() });
await client.connect();

let added = 0, updated = 0;

await client.query('BEGIN');
try {
  for (const account of accounts) {
    const id = String(account?.id ?? '').trim();
    if (!id) continue;

    const raw = JSON.stringify({ ...account, id });
    const res = await client.query(
      `INSERT INTO chart_of_accounts (id, raw, updated_at)
       VALUES ($1, $2::jsonb, NOW())
       ON CONFLICT (id) DO UPDATE SET raw = EXCLUDED.raw, updated_at = NOW()
       RETURNING (xmax = 0) AS inserted`,
      [id, raw]
    );
    if (res.rows[0]?.inserted) added++; else updated++;
  }
  await client.query('COMMIT');
  console.log(`Done. Inserted: ${added} | Upserted (existing): ${updated} | Total: ${accounts.length}`);
} catch (err) {
  await client.query('ROLLBACK');
  console.error('ERROR:', err.message);
  process.exit(1);
} finally {
  await client.end();
}
