// Общая часть для сервера и утилит: открыть базу, создать таблицы, правила подсчёта линий.
import { DatabaseSync } from 'node:sqlite';
import { readFileSync } from 'node:fs';

export const LINE_BONUS = 5;
// Диагонали карточки 4×4: 01-06-11-16 и 04-07-10-13.
const DIAGONALS = [[1, 6, 11, 16], [4, 7, 10, 13]];

// Собранные линии — по одной строке на каждую: ряды, столбцы и диагонали.
export const LINES_SQL = `
  SELECT device, week, 'row' AS kind FROM marks
    GROUP BY device, week, (cell - 1) / 4 HAVING COUNT(*) = 4
  UNION ALL
  SELECT device, week, 'col' AS kind FROM marks
    GROUP BY device, week, (cell - 1) % 4 HAVING COUNT(*) = 4
  UNION ALL
  SELECT device, week, 'diag' AS kind FROM marks WHERE cell IN (${DIAGONALS[0].join(', ')})
    GROUP BY device, week HAVING COUNT(*) = 4
  UNION ALL
  SELECT device, week, 'diag' AS kind FROM marks WHERE cell IN (${DIAGONALS[1].join(', ')})
    GROUP BY device, week HAVING COUNT(*) = 4`;

const SCHEMA = readFileSync(new URL('../schema.sql', import.meta.url), 'utf8');

export function openDb(file) {
  const db = new DatabaseSync(file);
  db.exec('PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA busy_timeout = 5000;');
  db.exec(SCHEMA);
  return db;
}