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

function unescapeCopyField(val) {
  if (val === null || val === '\\N') return null;
  let res = '';
  for (let idx = 0; idx < val.length; idx++) {
    if (val[idx] === '\\') {
      idx++;
      if (idx >= val.length) {
        res += '\\';
        break;
      }
      const nextChar = val[idx];
      if (nextChar === 'n') res += '\n';
      else if (nextChar === 'r') res += '\r';
      else if (nextChar === 't') res += '\t';
      else if (nextChar === 'b') res += '\b';
      else if (nextChar === 'f') res += '\f';
      else if (nextChar === 'v') res += '\v';
      else res += nextChar; // e.g. \" becomes ", \\ becomes \
    } else {
      res += val[idx];
    }
  }
  return res;
}

async function run() {
  const targetDb = process.env.DB_NAME || 'car';
  console.log(`Connecting to postgres default db to ensure database '${targetDb}' exists...`);
  
  const tempPool = new Pool({
    user: process.env.DB_USER || 'postgres',
    host: process.env.DB_HOST || '127.0.0.1',
    database: 'postgres',
    password: process.env.DB_PASSWORD,
    port: process.env.DB_PORT || 5432,
  });

  try {
    const res = await tempPool.query(`SELECT 1 FROM pg_database WHERE datname = $1`, [targetDb]);
    if (res.rows.length === 0) {
      await tempPool.query(`CREATE DATABASE "${targetDb}"`);
      console.log(`Database '${targetDb}' created successfully.`);
    } else {
      console.log(`Database '${targetDb}' already exists.`);
    }
  } catch (err) {
    console.error('Error ensuring database exists:', err.message);
    process.exit(1);
  } finally {
    await tempPool.end();
  }

  console.log(`Connecting to target database '${targetDb}'...`);
  const pool = new Pool({
    user: process.env.DB_USER || 'postgres',
    host: process.env.DB_HOST || '127.0.0.1',
    database: targetDb,
    password: process.env.DB_PASSWORD,
    port: process.env.DB_PORT || 5432,
  });

  try {
    console.log('Dropping existing tables and sequences for a clean restore...');
    await pool.query('DROP TABLE IF EXISTS public.bookings CASCADE');
    await pool.query('DROP TABLE IF EXISTS public.rental_bookings CASCADE');
    await pool.query('DROP TABLE IF EXISTS public.customers CASCADE');
    await pool.query('DROP TABLE IF EXISTS public.cars CASCADE');
    await pool.query('DROP TABLE IF EXISTS public.users CASCADE');
    await pool.query('DROP TABLE IF EXISTS public.country_master CASCADE');
    await pool.query('DROP SEQUENCE IF EXISTS public.bookings_booking_d_seq CASCADE');
    await pool.query('DROP SEQUENCE IF EXISTS public.cars_id_seq CASCADE');
    await pool.query('DROP SEQUENCE IF EXISTS public.country_master_country_id_seq CASCADE');
    await pool.query('DROP SEQUENCE IF EXISTS public.customer_seq CASCADE');
    await pool.query('DROP SEQUENCE IF EXISTS public.rental_bookings_booking_id_seq CASCADE');
    await pool.query('DROP SEQUENCE IF EXISTS public.users_id_seq CASCADE');
  } catch (err) {
    console.error('Warning during drop:', err.message);
  }

  const sqlFile = path.join(__dirname, 'schema_dump.sql');
  const content = fs.readFileSync(sqlFile, 'utf8');
  const lines = content.split(/\r?\n/);

  let currentQuery = '';
  let inCopy = false;
  let copyTable = '';
  let copyColumns = [];
  let copyRows = [];

  for (let i = 0; i < lines.length; i++) {
    const line = lines[i];
    const trimmed = line.trim();

    if (!inCopy && (trimmed === '' || trimmed.startsWith('--'))) {
      continue;
    }

    if (inCopy) {
      if (trimmed === '\\.') {
        console.log(`Inserting ${copyRows.length} rows into ${copyTable}...`);
        for (const row of copyRows) {
          const placeholders = row.map((_, idx) => `$${idx + 1}`).join(', ');
          const insertSql = `INSERT INTO ${copyTable} (${copyColumns.join(', ')}) VALUES (${placeholders})`;
          try {
            await pool.query(insertSql, row);
          } catch (err) {
            console.error(`Error inserting row into ${copyTable}:`, err.message);
            console.error('Row data was:', row);
            throw err;
          }
        }
        inCopy = false;
        copyTable = '';
        copyColumns = [];
        copyRows = [];
      } else {
        const vals = line.split('\t').map(v => {
          if (v === '\\N') return null;
          return unescapeCopyField(v);
        });
        copyRows.push(vals);
      }
    } else {
      if (trimmed.toUpperCase().startsWith('COPY ')) {
        const copyMatch = line.match(/COPY\s+([^\s\(]+)\s*\(([^\)]+)\)\s*FROM\s+stdin/i);
        if (copyMatch) {
          inCopy = true;
          copyTable = copyMatch[1];
          copyColumns = copyMatch[2].split(',').map(c => c.trim());
          console.log(`Starting COPY block for table ${copyTable}`);
        } else {
          console.warn(`Unrecognized COPY statement: ${line}`);
        }
      } else {
        currentQuery += line + '\n';
        if (trimmed.endsWith(';')) {
          try {
            if (currentQuery.toUpperCase().includes('OWNER TO')) {
               currentQuery = '';
               continue;
            }
            await pool.query(currentQuery);
          } catch (err) {
            console.error(`Error executing query: ${currentQuery.substring(0, 100)}...`);
            console.error(err.message);
            throw err;
          }
          currentQuery = '';
        }
      }
    }
  }

  console.log('Database restore completed successfully!');
  await pool.end();
}

run().catch(err => {
  console.error('Database restore failed:', err);
  process.exit(1);
});
