'use strict';

// Single database: PostgreSQL (DATABASE_URL). Holds the file list AND the
// WhatsApp session, so the app needs no local data folder.

const { Pool } = require('pg');
const { BufferJSON, initAuthCreds, proto } = require('@whiskeysockets/baileys');

if (!process.env.DATABASE_URL) {
  throw new Error('DATABASE_URL is not set. Add your PostgreSQL URL to .env (or Railway variables).');
}

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  // Set DATABASE_SSL=true for hosted Postgres that requires SSL (Neon, Supabase, Railway public URL, etc.)
  ssl: process.env.DATABASE_SSL === 'true' ? { rejectUnauthorized: false } : undefined,
  max: 5,
});
pool.on('error', (e) => console.error('Postgres pool error:', e.message));

async function init() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS bot_files (
      code       TEXT NOT NULL,
      title      TEXT NOT NULL,
      url        TEXT NOT NULL,
      type       TEXT NOT NULL DEFAULT 'document',
      sent       INTEGER NOT NULL DEFAULT 0,
      created_at TIMESTAMPTZ NOT NULL DEFAULT now()
    );
    CREATE UNIQUE INDEX IF NOT EXISTS bot_files_code_lower ON bot_files (lower(code));
    CREATE TABLE IF NOT EXISTS bot_wa_auth (
      id   TEXT PRIMARY KEY,
      data TEXT NOT NULL
    );
  `);
}

// ---------------------------------------------------------------------------
// File list
// ---------------------------------------------------------------------------
const toItem = (r) => ({
  code: r.code,
  title: r.title,
  url: r.url,
  type: r.type,
  sent: r.sent,
  createdAt: r.created_at.toISOString(),
});

async function listFiles() {
  const { rows } = await pool.query('SELECT * FROM bot_files ORDER BY created_at, code');
  return rows.map(toItem);
}

async function findFile(code) {
  const { rows } = await pool.query('SELECT * FROM bot_files WHERE lower(code) = lower($1)', [code]);
  return rows[0] ? toItem(rows[0]) : null;
}

// Throws with code 23505 if the code is already used (unique index).
async function addFile({ code, title, url, type }) {
  const { rows } = await pool.query(
    'INSERT INTO bot_files (code, title, url, type) VALUES ($1, $2, $3, $4) RETURNING *',
    [code, title, url, type]
  );
  return toItem(rows[0]);
}

async function deleteFile(code) {
  const { rowCount } = await pool.query('DELETE FROM bot_files WHERE lower(code) = lower($1)', [code]);
  return rowCount;
}

async function markSent(code) {
  await pool.query('UPDATE bot_files SET sent = sent + 1 WHERE lower(code) = lower($1)', [code]);
}

// ---------------------------------------------------------------------------
// WhatsApp session (Baileys auth state stored in bot_wa_auth)
// ---------------------------------------------------------------------------
async function useSqlAuthState() {
  const read = async (id) => {
    const { rows } = await pool.query('SELECT data FROM bot_wa_auth WHERE id = $1', [id]);
    return rows[0] ? JSON.parse(rows[0].data, BufferJSON.reviver) : null;
  };
  const write = (id, value) => pool.query(
    'INSERT INTO bot_wa_auth (id, data) VALUES ($1, $2) ON CONFLICT (id) DO UPDATE SET data = EXCLUDED.data',
    [id, JSON.stringify(value, BufferJSON.replacer)]
  );
  const remove = (id) => pool.query('DELETE FROM bot_wa_auth WHERE id = $1', [id]);

  const creds = (await read('creds')) || initAuthCreds();

  const keys = {
    get: async (type, ids) => {
      const out = {};
      await Promise.all(ids.map(async (id) => {
        let value = await read(`${type}-${id}`);
        if (type === 'app-state-sync-key' && value) {
          value = proto.Message.AppStateSyncKeyData.fromObject(value);
        }
        out[id] = value;
      }));
      return out;
    },
    set: async (data) => {
      const jobs = [];
      for (const category of Object.keys(data)) {
        for (const id of Object.keys(data[category])) {
          const value = data[category][id];
          const name = `${category}-${id}`;
          jobs.push(value ? write(name, value) : remove(name));
        }
      }
      await Promise.all(jobs);
    },
  };

  return {
    state: { creds, keys },
    saveCreds: () => write('creds', creds),
  };
}

async function clearAuth() {
  await pool.query('DELETE FROM bot_wa_auth');
}

module.exports = {
  pool, init,
  listFiles, findFile, addFile, deleteFile, markSent,
  useSqlAuthState, clearAuth,
};