const { exec } = require('child_process');
const util = require('util');
const fs = require('fs');
const execPromise = util.promisify(exec);
const { setSurvey, pool } = require('../connection');

async function createProject(pid, name, src_srid, dst_srid, src_schema, dst_schema) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Determine default src_schema and src_srid
    let src_schema_val;
    let src_srid_val;
    const res = await client.query(`
      SELECT schema, meta->>'epsg' as epsg
      FROM public.projects
      ORDER BY CAST(SUBSTRING(schema FROM 8) AS INTEGER) DESC
      LIMIT 1
    `);
    if (res.rows.length === 0) {
      src_schema_val = 'survey_0';
      src_srid_val = 23031;
    } else {
      src_schema_val = res.rows[0].schema;
      src_srid_val = parseInt(res.rows[0].epsg, 10);
    }

    // Apply parameters or defaults
    src_schema = src_schema || src_schema_val;
    src_srid = src_srid ?? src_srid_val;
    dst_srid = dst_srid ?? src_srid;
    if (dst_schema === undefined) {
      const srcNum = parseInt(src_schema.replace('survey_', ''), 10);
      dst_schema = `survey_${srcNum + 1}`;
    }

    // Dump the source schema structure
    const pgDumpCmd = `PGPASSWORD=${pool.options.password} pg_dump --schema-only --schema=${src_schema} --host=${pool.options.host} --port=${pool.options.port} --username=${pool.options.user} ${pool.options.database}`;
    const { stdout: sqlDump } = await execPromise(pgDumpCmd);
    //fs.writeFileSync('sqlDump.sql', sqlDump);
    //console.log('Saved original SQL to sqlDump.sql');

    // Modify the dump to use the destination schema and update SRID
    const escapedSrcSchema = src_schema.replace(/[-/\\^$*+?.()|[\]{}]/g, '\\$&');
    let modifiedSql = sqlDump
      .replace(new RegExp(`CREATE SCHEMA ${escapedSrcSchema};`, 'gi'), `CREATE SCHEMA ${dst_schema};`)
      .replace(new RegExp(`ALTER SCHEMA ${escapedSrcSchema} OWNER TO`, 'gi'), `ALTER SCHEMA ${dst_schema} OWNER TO`)
      .replace(/SELECT pg_catalog\.set_config\('search_path',\s*'', false\);/, `SELECT pg_catalog.set_config('search_path', '${dst_schema}, public', false);`)
      .replace(new RegExp(`${escapedSrcSchema}\\.`, 'g'), `${dst_schema}.`);

    // Replace SRID in the SQL dump if src_srid !== dst_srid
    if (src_srid !== dst_srid) {
      // Replace SRID in geometry column definitions (e.g., geometry(Point, 23031))
      modifiedSql = modifiedSql.replace(
        new RegExp(`geometry\\((\\w+),\\s*${src_srid}\\s*\\)`, 'g'),
        `geometry($1, ${dst_srid})`
      );
      // Replace SRID in AddGeometryColumn calls (if used in the dump)
      modifiedSql = modifiedSql.replace(
        new RegExp(`AddGeometryColumn\\((['"]?)${escapedSrcSchema}\\1,\\s*(['"]\\w+['"]),\\s*(['"]\\w+['"]),\\s*${src_srid},`, 'g'),
        `AddGeometryColumn($1${dst_schema}$1, $2, $3, ${dst_srid},`
      );
      console.log(`Replaced SRID ${src_srid} with ${dst_srid} in SQL dump`);
    }

    //fs.writeFileSync('modifiedSql.sql', modifiedSql);
    //console.log('Saved modified SQL to modifiedSql.sql');

    // Execute the modified SQL to create the cloned schema
    await client.query(modifiedSql);
    console.log('Applied modified SQL successfully');

    // Insert the new project into public.projects
    const meta = { epsg: dst_srid.toString() }; // Ensure string for JSONB
    await client.query(`
      INSERT INTO public.projects (pid, name, schema, meta)
      VALUES ($1, $2, $3, $4)
    `, [pid, name, dst_schema, meta]);
    console.log(`Inserted project ${pid} into public.projects with schema ${dst_schema}`);

    await client.query('COMMIT');
    console.log('Transaction committed successfully');
  } catch (error) {
    await client.query('ROLLBACK');
    console.error('Transaction rolled back due to error:', error);
    throw error;
  } finally {
    client.release();
    console.log('Database client released');
  }
}

module.exports = createProject;
