import Database from 'better-sqlite3';
import { chmodSync, closeSync, existsSync, mkdirSync, openSync, readFileSync, statSync, unlinkSync } from 'node:fs';
import { dirname } from 'node:path';
import { privateDatabasePath } from './config.mjs';

const INITIAL_SQL = readFileSync(new URL('./migrations/001-initial.sql', import.meta.url), 'utf8');
const COLUMNS = {
  visitors_daily: ['visitor_id', 'visit_date', 'first_seen_at', 'last_seen_at'],
  arrivals: ['dog_id', 'first_arrived_at'],
};
const seoulDate = new Intl.DateTimeFormat('en-CA', { timeZone: 'Asia/Seoul', year: 'numeric', month: '2-digit', day: '2-digit' });

export function monthRange(month) {
  if (typeof month !== 'string' || !/^(?!0000)[0-9]{4}-(0[1-9]|1[0-2])$/.test(month)) throw new Error('Invalid month; use YYYY-MM');
  const year = +month.slice(0, 4);
  const number = +month.slice(5);
  // Lexicographic upper sentinel keeps the valid 9999-12 month queryable.
  const next = number < 12 ? `${month.slice(0, 4)}-${String(number + 1).padStart(2, '0')}` : year < 9999 ? `${String(year + 1).padStart(4, '0')}-01` : '9999-13';
  return [`${month}-01`, `${next}-01`];
}

export function assertSchema(db) {
  if (db.pragma('user_version', { simple: true }) !== 1) throw new Error('Unsupported database schema');
  for (const [table, columns] of Object.entries(COLUMNS)) {
    const actual = db.pragma(`table_info(${table})`);
    if (actual.map(c => c.name).join() !== columns.join() || actual.some(c => c.type !== 'TEXT' || c.notnull !== 1)) throw new Error('Unsupported database schema');
    const keys = actual.filter(c => c.pk).sort((a, b) => a.pk - b.pk).map(c => c.name);
    if (keys.join() !== (table === 'arrivals' ? 'dog_id' : 'visitor_id,visit_date')) throw new Error('Unsupported database schema');
  }
}

/** Exported for migration rollback verification against a disposable database. */
export function migrateDatabase(db, migrations = [{ version: 1, sql: INITIAL_SQL }]) {
  const current = db.pragma('user_version', { simple: true });
  const latest = migrations.at(-1)?.version ?? 0;
  if (current > latest) throw new Error('Unsupported database version');
  db.transaction(() => {
    let version = current;
    for (const migration of migrations) {
      if (migration.version <= version) continue;
      if (migration.version !== version + 1) throw new Error('Invalid migration order');
      db.exec(migration.sql);
      db.pragma(`user_version = ${migration.version}`);
      version = migration.version;
    }
  }).immediate();
}

function privateParent(path) {
  const parent = dirname(path);
  mkdirSync(parent, { recursive: true, mode: 0o700 });
  if (process.platform !== 'win32' && (statSync(parent).mode & 0o077)) throw new Error('Database directory must be private');
}
function safeCount(row) {
  if (!Number.isSafeInteger(row.count) || row.count < 0) throw new Error('Invalid aggregate');
  return row.count;
}

export function openStore({ dbPath, now = () => new Date(), busyTimeoutMs = 1000 }) {
  if (!Number.isInteger(busyTimeoutMs) || busyTimeoutMs < 0 || busyTimeoutMs > 10000) throw new Error('Invalid busy timeout');
  const path = privateDatabasePath(dbPath);
  privateParent(path);
  if (!existsSync(path)) closeSync(openSync(path, 'wx', 0o600));
  if (process.platform !== 'win32') chmodSync(path, statSync(path).mode & 0o600);
  const db = new Database(path, { timeout: busyTimeoutMs });
  try {
    db.pragma('journal_mode = WAL');
    migrateDatabase(db);
    assertSchema(db);
    const visit = db.prepare('INSERT INTO visitors_daily (visitor_id, visit_date, first_seen_at, last_seen_at) VALUES (?, ?, ?, ?) ON CONFLICT(visitor_id, visit_date) DO UPDATE SET last_seen_at = excluded.last_seen_at');
    const arrival = db.prepare('INSERT INTO arrivals (dog_id, first_arrived_at) VALUES (?, ?) ON CONFLICT(dog_id) DO NOTHING');
    const count = db.prepare('SELECT COUNT(*) AS count FROM arrivals');
    const monthly = db.prepare('SELECT COUNT(DISTINCT visitor_id) AS count FROM visitors_daily WHERE visit_date >= ? AND visit_date < ?');
    return {
      recordVisit(visitorId) {
        const at = now();
        const timestamp = at.toISOString();
        const parts = Object.fromEntries(seoulDate.formatToParts(at).map(p => [p.type, p.value]));
        visit.run(visitorId, `${parts.year.padStart(4, '0')}-${parts.month}-${parts.day}`, timestamp, timestamp);
      },
      recordArrival(dogId) { arrival.run(dogId, now().toISOString()); },
      arrivalCount() { return safeCount(count.get()); },
      monthlyVisitors(month) { return safeCount(monthly.get(...monthRange(month))); },
      backup(destination) { return backupDatabase(db, path, destination); },
      close() { if (db.open) db.close(); },
    };
  } catch (error) { db.close(); throw error; }
}

export function openReadOnlyStore(dbPath) {
  const path = privateDatabasePath(dbPath);
  const db = new Database(path, { readonly: true, fileMustExist: true, timeout: 1000 });
  try {
    assertSchema(db);
    const count = db.prepare('SELECT COUNT(*) AS count FROM arrivals');
    const monthly = db.prepare('SELECT COUNT(DISTINCT visitor_id) AS count FROM visitors_daily WHERE visit_date >= ? AND visit_date < ?');
    return {
      arrivalCount() { return safeCount(count.get()); },
      monthlyVisitors(month) { return safeCount(monthly.get(...monthRange(month))); },
      backup(destination) { return backupDatabase(db, path, destination); },
      close() { if (db.open) db.close(); },
    };
  } catch (error) { db.close(); throw error; }
}

async function backupDatabase(db, source, destination) {
  const target = privateDatabasePath(destination);
  if (target === source) throw new Error('Backup must use a different path');
  privateParent(target);
  // Reserve exclusively before the async backup, so an existing backup is never replaced.
  closeSync(openSync(target, 'wx', 0o600));
  try { await db.backup(target); }
  catch (error) { try { unlinkSync(target); } catch {} throw error; }
}