const fs = require("node:fs");
const path = require("node:path");
const { DatabaseSync } = require("node:sqlite");

const DATABASE_FILE = process.env.DATABASE_FILE || process.env.DATA_FILE || path.join(__dirname, "data", "invites.db");
fs.mkdirSync(path.dirname(DATABASE_FILE), { recursive: true });

const database = new DatabaseSync(DATABASE_FILE);
database.exec("PRAGMA journal_mode = WAL");
database.exec("PRAGMA busy_timeout = 5000");
database.exec("PRAGMA foreign_keys = ON");
database.exec(`
  CREATE TABLE IF NOT EXISTS invites (
    token TEXT PRIMARY KEY,
    guest_name TEXT NOT NULL,
    answer TEXT CHECK (answer IN ('not_coming', 'alone', 'with_someone')),
    companion_name TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    first_opened_at TEXT,
    last_opened_at TEXT,
    open_count INTEGER NOT NULL DEFAULT 0,
    responded_at TEXT
  ) STRICT
`);

const selectColumns = `
  token,
  guest_name AS guestName,
  answer,
  companion_name AS companionName,
  created_at AS createdAt,
  first_opened_at AS firstOpenedAt,
  last_opened_at AS lastOpenedAt,
  open_count AS openCount,
  responded_at AS respondedAt
`;

const selectOne = database.prepare(`SELECT ${selectColumns} FROM invites WHERE token = ?`);
const selectAll = database.prepare(`SELECT ${selectColumns} FROM invites ORDER BY created_at ASC`);
const insertOne = database.prepare(`
  INSERT INTO invites (token, guest_name, created_at)
  VALUES (?, ?, ?)
`);

function migrateLegacyJson() {
  if (process.env.DATABASE_FILE || process.env.DATA_FILE) return;
  const legacyFile = path.join(__dirname, "data", "invites.json");
  const count = database.prepare("SELECT COUNT(*) AS count FROM invites").get().count;
  if (count || !fs.existsSync(legacyFile)) return;
  try {
    const legacy = JSON.parse(fs.readFileSync(legacyFile, "utf8"));
    const insertLegacy = database.prepare(`
      INSERT OR IGNORE INTO invites (
        token, guest_name, answer, companion_name, created_at,
        first_opened_at, last_opened_at, open_count, responded_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    database.exec("BEGIN IMMEDIATE");
    for (const invite of legacy.invites || []) {
      insertLegacy.run(
        invite.token,
        invite.guestName || "",
        invite.answer || null,
        invite.companionName || "",
        invite.createdAt || new Date().toISOString(),
        invite.firstOpenedAt || null,
        invite.lastOpenedAt || null,
        Number(invite.openCount || 0),
        invite.respondedAt || null
      );
    }
    database.exec("COMMIT");
  } catch (error) {
    try { database.exec("ROLLBACK"); } catch {}
    console.warn("Legacy JSON data could not be imported:", error.message);
  }
}

migrateLegacyJson();

function getInvite(token) {
  return selectOne.get(token) || null;
}

function listInvites() {
  return selectAll.all();
}

function recordOpen(token, openedAt) {
  const result = database.prepare(`
    UPDATE invites
    SET first_opened_at = COALESCE(first_opened_at, ?),
        last_opened_at = ?,
        open_count = open_count + 1
    WHERE token = ?
  `).run(openedAt, openedAt, token);
  return result.changes ? getInvite(token) : null;
}

function saveRsvp(token, answer, companionName, respondedAt) {
  const result = database.prepare(`
    UPDATE invites
    SET answer = ?, companion_name = ?, responded_at = ?
    WHERE token = ?
  `).run(answer, companionName, respondedAt, token);
  return result.changes ? getInvite(token) : null;
}

function createInvites(entries) {
  const createdAt = new Date().toISOString();
  database.exec("BEGIN IMMEDIATE");
  try {
    for (const entry of entries) insertOne.run(entry.token, entry.guestName, createdAt);
    database.exec("COMMIT");
  } catch (error) {
    database.exec("ROLLBACK");
    throw error;
  }
  return entries.map(entry => getInvite(entry.token));
}

module.exports = { DATABASE_FILE, createInvites, getInvite, listInvites, recordOpen, saveRsvp };