/**
 * Database Seed Script
 * Rebuilds bundled atlas metadata in PostgreSQL by recreating atlas, category,
 * trait, and option records from the JSON sources stored in backend/data/atlases.
 */
require('dotenv').config();
const { Pool } = require('pg');
const path = require('path');
const { createAtlasImageResolver } = require('./utils/atlasImageResolver');
const {
  ATLAS_TRANSLATION_SUFFIX,
  partitionOptionImages,
  readAtlasJson,
  toImportedTraitId,
} = require('./utils/atlasImportUtils');
const fs = require('fs');

const pool = new Pool({
  user: process.env.DB_USER || 'postgres',
  host: process.env.DB_HOST || 'localhost',
  database: process.env.DB_NAME || (process.env.NODE_ENV === 'test' ? 'postgres_test' : 'postgres'),
  password: process.env.DB_PASSWORD || 'betka',
  port: parseInt(process.env.DB_PORT, 10) || 5432,
});

const ATLASES_DIR = path.join(__dirname, 'data', 'atlases');
const IMAGES_DIR = path.join(__dirname, 'public', 'images');
const resolveAtlasImage = createAtlasImageResolver(IMAGES_DIR);

function shouldSeedAtlasFile(fileName) {
  if (process.env.NODE_ENV === 'test') {
    return true;
  }

  return !fileName.startsWith('test_');
}

async function seedDatabase() {
  const client = await pool.connect();
  try {
    console.log('🚀 Resetting database and loading data from atlas JSON files.');
    await client.query('BEGIN');

    await client.query(`
      DROP TABLE IF EXISTS model_trait_selections CASCADE;
      DROP TABLE IF EXISTS annotations CASCADE;
      DROP TABLE IF EXISTS models CASCADE;
      DROP TABLE IF EXISTS trait_options CASCADE;
      DROP TABLE IF EXISTS traits CASCADE;
      DROP TABLE IF EXISTS trait_categories CASCADE;
      DROP TABLE IF EXISTS atlases CASCADE;
    `);

    const sqlPath = path.join(__dirname, 'init.sql');
    const initSql = fs.readFileSync(sqlPath, 'utf8');
    await client.query(initSql);

    const files = fs.readdirSync(ATLASES_DIR)
      .filter(f => f.endsWith('.json'))
      .filter(f => !f.endsWith(ATLAS_TRANSLATION_SUFFIX))
      .filter(shouldSeedAtlasFile);

    for (const file of files) {
      console.log(`📖 Processing atlas: ${file}`);
      const atlasData = readAtlasJson(path.join(ATLASES_DIR, file));

      const atlasRes = await client.query(
        'INSERT INTO atlases (id, nazev_cs, nazev_en, popis_cs, popis_en, je_vychozi) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id',
        [
          atlasData.id,
          atlasData.label_cs,
          atlasData.label_en,
          atlasData.popis_cs,
          atlasData.popis_en,
          atlasData.id === 'som-facial-somatoscopy-atlas'
        ]
      );
      const atlasId = atlasRes.rows[0].id;

      for (let i = 0; i < atlasData.sections.length; i++) {
        const sekce = atlasData.sections[i];
        const catRes = await client.query(
          'INSERT INTO trait_categories (atlas_id, nazev_cs, nazev_en, poradi) VALUES ($1, $2, $3, $4) RETURNING id',
          [atlasId, sekce.nazev_cs, sekce.nazev_en, i]
        );
        const categoryId = catRes.rows[0].id;

        for (let j = 0; j < sekce.znaky.length; j++) {
          const znak = sekce.znaky[j];
          const importedTraitId = toImportedTraitId(atlasId, znak.id);

          await client.query(
            'INSERT INTO traits (id, category_id, label_cs, label_en, popis_cs, popis_en, poradi) VALUES ($1, $2, $3, $4, $5, $6, $7)',
            [importedTraitId, categoryId, znak.label_cs, znak.label_en, znak.popis_cs, znak.popis_en, j]
          );

          for (let k = 0; k < znak.options.length; k++) {
            const opt = znak.options[k];
            const { photos, drawings } = partitionOptionImages(opt.images, resolveAtlasImage);

            await client.query(
              'INSERT INTO trait_options (trait_id, label_cs, label_en, images, drawings, poradi) VALUES ($1, $2, $3, $4, $5, $6)',
              [importedTraitId, opt.label_cs, opt.label_en, photos, drawings, k]
            );
          }
        }
      }
    }

    await client.query('COMMIT');
    console.log('✅ Seed completed.');
  } catch (e) {
    await client.query('ROLLBACK');
    console.error('❌ Seeder error:', e);
    process.exitCode = 1;
  } finally {
    client.release();
    await pool.end();
  }
}

seedDatabase();