db.js

// db.js (PostgreSQL persistence layer)
const { Pool } = require('pg');

// Trim env values — pasting into hosting dashboards (Render, Heroku, etc.)
// frequently introduces trailing spaces or newlines that break connections.
const env = (key, fallback) => (process.env[key] || fallback || '').toString().trim();

const dbHost = env('DB_HOST', 'localhost');

// Hosted Postgres (Render, Heroku, Supabase, Neon, RDS…) requires SSL; local
// Postgres usually doesn't. Enable SSL when DB_SSL=true OR when the host is a
// recognised managed provider (so a forgotten DB_SSL doesn't break the deploy).
const sslFlag = env('DB_SSL').toLowerCase() === 'true';
const hostNeedsSsl = /(render\.com|neon\.tech|supabase\.(co|com)|amazonaws\.com|heroku)/i.test(
  dbHost
);
const useSsl = sslFlag || hostNeedsSsl;

const pool = new Pool({
  user: env('DB_USER', 'your_username'),
  host: dbHost,
  database: env('DB_NAME', 'your_database'),
  password: env('DB_PASSWORD', 'your_password'),
  port: Number(env('DB_PORT', '5432')),
  ssl: useSsl ? { rejectUnauthorized: false } : false,
});

// ──────────────────────────────────────────────────────────────────────────
// Schema
// ──────────────────────────────────────────────────────────────────────────
async function createTables() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS conversations (
      id SERIAL PRIMARY KEY,
      chatId TEXT NOT NULL,
      projectName TEXT NOT NULL,
      role TEXT NOT NULL,
      content TEXT NOT NULL,
      timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `);

  await pool.query(`
    CREATE TABLE IF NOT EXISTS projects (
      project_name TEXT PRIMARY KEY,
      admin_chat_id BIGINT NOT NULL,
      admin_password TEXT,
      websites JSONB NOT NULL DEFAULT '[]'::jsonb,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `);

  await pool.query(`
    CREATE INDEX IF NOT EXISTS idx_projects_admin_chat_id ON projects(admin_chat_id)
  `);

  console.log('✅ Database schema ready (conversations + projects)');
}

// Note: createTables() is NOT called at module load — it's exported and
// awaited explicitly from stateManager.init() at startup, BEFORE any code
// tries to read from the projects table. This avoids a race where a SELECT
// could fire before CREATE TABLE finishes on a fresh database.

// ──────────────────────────────────────────────────────────────────────────
// Conversation history (unchanged API)
// ──────────────────────────────────────────────────────────────────────────
function saveMessage(chatId, projectName, role, content) {
  return pool
    .query(
      `INSERT INTO conversations (chatId, projectName, role, content)
       VALUES ($1, $2, $3, $4)
       RETURNING id`,
      [chatId, projectName, role, content]
    )
    .then((result) => result.rows[0].id);
}

function loadConversationHistory(chatId, projectName) {
  return pool
    .query(
      `SELECT role, content FROM conversations
       WHERE chatId = $1 AND projectName = $2
       ORDER BY timestamp ASC`,
      [chatId, projectName]
    )
    .then((result) => result.rows);
}

function clearConversationHistory(chatId, projectName) {
  return pool
    .query(
      `DELETE FROM conversations
       WHERE chatId = $1 AND projectName = $2`,
      [chatId, projectName]
    )
    .then((result) => result.rowCount);
}

// ──────────────────────────────────────────────────────────────────────────
// Projects (new — replaces in-memory storage)
// ──────────────────────────────────────────────────────────────────────────
async function loadAllProjects() {
  const result = await pool.query(`SELECT * FROM projects`);
  const map = new Map();
  for (const row of result.rows) {
    map.set(row.project_name, {
      projectName: row.project_name,
      adminChatId: Number(row.admin_chat_id),
      adminPassword: row.admin_password,
      websites: row.websites || [],
    });
  }
  return map;
}

async function upsertProject(name, project) {
  await pool.query(
    `INSERT INTO projects (project_name, admin_chat_id, admin_password, websites, updated_at)
     VALUES ($1, $2, $3, $4::jsonb, CURRENT_TIMESTAMP)
     ON CONFLICT (project_name) DO UPDATE SET
       admin_chat_id = EXCLUDED.admin_chat_id,
       admin_password = EXCLUDED.admin_password,
       websites = EXCLUDED.websites,
       updated_at = CURRENT_TIMESTAMP`,
    [name, project.adminChatId, project.adminPassword, JSON.stringify(project.websites || [])]
  );
}

async function deleteProjectRow(name) {
  await pool.query(`DELETE FROM projects WHERE project_name = $1`, [name]);
}

module.exports = {
  // schema
  createTables,
  // conversations
  saveMessage,
  loadConversationHistory,
  clearConversationHistory,
  // projects
  loadAllProjects,
  upsertProject,
  deleteProjectRow,
  // raw pool (for graceful shutdown)
  pool,
};