import { DatabaseSync } from "node:sqlite";
import { resolve } from "node:path";

const MAX_TTL = 1 * 60 * 60 * 1000;

const dbPath = resolve(import.meta.dirname, "data", "service.db");
const extPath = resolve(import.meta.dirname, "data", "libsqliteipv4.so");
console.log(`Database at ${dbPath}`);
const db = new DatabaseSync(dbPath, { readonly: false, allowExtension: true });

db.loadExtension(extPath);

const schema = `
CREATE TABLE IF NOT EXISTS "banned_as" (
  "as" TEXT NOT NULL PRIMARY KEY,
  "is_active" BOOLEAN NOT NULL,
  "comment" TEXT
  "reason" TEXT
);

CREATE TABLE IF NOT EXISTS "as_stats" (
  "as" TEXT NOT NULL PRIMARY KEY,
  "accepted" INT,
  "rejected" INT
);

CREATE TABLE IF NOT EXISTS "cidr_cache" (
  "range" TEXT NOT NULL PRIMARY KEY,
  "as" TEXT NOT NULL,
  "description" TEXT,
  "date_created" TEXT NOT NULL
);
`;

db.exec(schema);

const getASFromCacheQuery = db.prepare(`SELECT range, "as", date_created FROM cidr_cache WHERE ISINNET(?, range) LIMIT 1`);
const deleteRangeFromCacheQuery = db.prepare(`DELETE FROM cidr_cache WHERE range = ?`);

export function getASFromCache(ip) {
  const data =  getASFromCacheQuery.get(ip);

  if(!data) { return null };

  const { range, as, date_created } = data;

  if(new Date(date_created).getTime() - new Date().getTime() > MAX_TTL) {
    console.log(`AS Cache for ${range} is stale. Removing`);
    deleteRangeFromCacheQuery.run(range);
    return null;
  }

  return as;
}

const isASBannedQuery = db.prepare(`SELECT reason FROM banned_as WHERE "as" = ? AND is_active = true;`);
export function isASBanned(as) {
  return isASBannedQuery.get(as);
};

const updateASQuery = db.prepare(`INSERT INTO cidr_cache VALUES (?, ?, ?, datetime('now', 'localtime'));`);
export function updateAS(as, range, description) {
  updateASQuery.run(range, as, description);
}

const incrementAcceptedQuery = db.prepare(`INSERT INTO as_stats ("as", accepted) VALUES (?, 1) ON CONFLICT("as") DO UPDATE SET accepted = accepted + 1;`)
const incrementRejectedQuery = db.prepare(`INSERT INTO as_stats ("as", rejected) VALUES (?, 1) ON CONFLICT("as") DO UPDATE SET rejected = rejected + 1;`)

export function incrementAccepted(as) {
  incrementAcceptedQuery.run(as);
}

export function incrementRejected(as) {
  incrementRejectedQuery.run(as);
}