const { readFileSync } = require("fs");
const { join } = require("path");
const { Pool } = require("pg");

async function init() {
  // First connect to default 'postgres' db to create our database
  const adminPool = new Pool({
    host: process.env.PGHOST || "localhost",
    port: parseInt(process.env.PGPORT || "5432"),
    database: "postgres",
    user: process.env.PGUSER || "cbt_admin",
    password: process.env.PGPASSWORD || "cbt_password",
  });

  const dbName = process.env.PGDATABASE || "atapoly_cbt";

  try {
    const exists = await adminPool.query(
      `SELECT 1 FROM pg_database WHERE datname = $1`,
      [dbName],
    );
    if (exists.rows.length === 0) {
      await adminPool.query(`CREATE DATABASE ${dbName}`);
      console.log(`✅ Database "${dbName}" created`);
    } else {
      console.log(`ℹ️  Database "${dbName}" already exists`);
    }
  } finally {
    await adminPool.end();
  }

  // Now connect to our database and run schema
  const appPool = new Pool({
    host: process.env.PGHOST || "localhost",
    port: parseInt(process.env.PGPORT || "5432"),
    database: dbName,
    user: process.env.PGUSER || "cbt_admin",
    password: process.env.PGPASSWORD || "cbt_password",
  });

  try {
    const schema = readFileSync(join(__dirname, "schema.sql"), "utf-8");
    await appPool.query(schema);
    console.log("✅ Schema applied successfully");
  } catch (err) {
    if (err.message.includes("already exists")) {
      console.log("ℹ️  Schema already exists (skipping)");
    } else {
      throw err;
    }
  } finally {
    await appPool.end();
  }
}

init()
  .then(() => {
    console.log("✅ Database initialization complete");
    process.exit(0);
  })
  .catch((err) => {
    console.error("❌ Database initialization failed:", err.message);
    process.exit(1);
  });

//Below is for the init.js Online/Cpanel version, which is more complex due to dual DB support and connection testing. The above is a simplified version for local development or single DB setups.

/*
const { Pool } = require('pg');

// ── Dual DB support ──
// If PGHOST points to cpanel (or any remote), use SSL.
// If PGHOST is localhost/127.0.0.1, it's a local PostgreSQL.
// Both are standard PostgreSQL — no code difference needed.

const PGHOST = process.env.PGHOST || '';
const PGPORT = parseInt(process.env.PGPORT || '');
const PGDATABASE = process.env.PGDATABASE || '';
const PGUSER = process.env.PGUSER || '';
const PGPASSWORD = process.env.PGPASSWORD || '';

// Auto-detect SSL: enable for non-local hosts unless explicitly disabled
const isLocal = ['localhost', '127.0.0.1', '::1'].includes(PGHOST);
const DB_SSL = process.env.DB_SSL === 'true' || (!isLocal && process.env.DB_SSL !== 'false');

const pool = new Pool({
  host: PGHOST,
  port: PGPORT,
  database: PGDATABASE,
  user: PGUSER,
  password: PGPASSWORD,
  ssl: DB_SSL ? { rejectUnauthorized: false } : false,
  max: 100,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
  allowExitOnIdle: false,
});

pool.on('error', (err) => {
  console.error('[DB POOL ERROR]', err.message);
});

async function testConnection() {
  try {
    const client = await pool.connect();
    try {
      const res = await client.query('SELECT NOW()');
      const dbType = isLocal ? 'Local PostgreSQL' : 'Remote PostgreSQL (cpanel)';
      console.log(`✅ Database connected [${dbType}] @ ${PGHOST}:${PGPORT}/${PGDATABASE}:`, res.rows[0].now);
    } finally {
      client.release();
    }
  } catch (err) {
    console.error(`❌ Database connection failed @ ${PGHOST}:${PGPORT}/${PGDATABASE}:`, err.message);
    throw err;
  }
}

// Expose config info for the settings API
function getDbInfo() {
  return {
    host: PGHOST,
    port: PGPORT,
    database: PGDATABASE,
    user: PGUSER,
    ssl: DB_SSL,
    isLocal,
    type: isLocal ? 'local' : 'remote',
  };
}

module.exports = { pool, testConnection, getDbInfo };
*/
