import Database from "better-sqlite3";
import fs from "node:fs";
import path from "node:path";
import { bootstrapAdminUser } from "@/lib/auth";
import { bootstrapSql } from "@/lib/schema";

declare global {
  var __invenDb: Database.Database | undefined;
}

function resolveDatabasePath() {
  const configured = process.env.DATABASE_PATH ?? path.join(process.cwd(), "data", "inven.sqlite");
  fs.mkdirSync(path.dirname(configured), { recursive: true });
  return configured;
}

function createDatabase() {
  const db = new Database(resolveDatabasePath());
  db.pragma("journal_mode = WAL");
  db.pragma("foreign_keys = ON");
  db.exec(bootstrapSql);
  const salesLineColumns = db.prepare(`PRAGMA table_info(sales_order_lines)`).all() as Array<{ name: string }>;
  const purchaseLineColumns = db.prepare(`PRAGMA table_info(purchase_order_lines)`).all() as Array<{ name: string }>;

  if (!salesLineColumns.some((column) => column.name === "shipped_quantity")) {
    db.exec(`ALTER TABLE sales_order_lines ADD COLUMN shipped_quantity REAL NOT NULL DEFAULT 0`);
  }

  if (!purchaseLineColumns.some((column) => column.name === "received_quantity")) {
    db.exec(`ALTER TABLE purchase_order_lines ADD COLUMN received_quantity REAL NOT NULL DEFAULT 0`);
  }

  bootstrapAdminUser(db);

  return db;
}

export function getDb() {
  if (!global.__invenDb) {
    global.__invenDb = createDatabase();
  }

  return global.__invenDb;
}