#include <stdio.h>
#include <sqlite3.h>
#include "db_init.h"

static const char *SCHEMA =
	"PRAGMA journal_mode = WAL;"
	"PRAGMA busy_timeout = 5000;"
	"CREATE TABLE IF NOT EXISTS urls ("
	"    id      INTEGER PRIMARY KEY,"
	"    url     TEXT UNIQUE NOT NULL,"
	"    status  TEXT NOT NULL DEFAULT 'pending'"
	"            CHECK (status IN ('pending','in_progress','done','failed','skipped'))"
	");"
	"CREATE INDEX IF NOT EXISTS idx_urls_status ON urls(status);"
	"CREATE TABLE IF NOT EXISTS links ("
	"    from_id INTEGER NOT NULL REFERENCES urls(id),"
	"    to_id   INTEGER NOT NULL REFERENCES urls(id),"
	"    UNIQUE(from_id, to_id)"
	");"
	/* crash recovery: anything left mid-fetch from a previous run goes back to pending */
	"UPDATE urls SET status = 'pending' WHERE status = 'in_progress';";

/* Opens (creating if needed) and initializes the DB. Returns NULL on failure. */
sqlite3 *db_init(const char *path) {
	sqlite3 *db;
	if (sqlite3_open(path, &db) != SQLITE_OK) {
		fprintf(stderr, "db_init: cannot open %s: %s\n", path, sqlite3_errmsg(db));
		sqlite3_close(db);
		return NULL;
	}
	
	char *err = NULL;
	if (sqlite3_exec(db, SCHEMA, NULL, NULL, &err) != SQLITE_OK) {
		fprintf(stderr, "db_init: schema error: %s\n", err);
		sqlite3_free(err);
		sqlite3_close(db);
		return NULL;
	}
	
	return db;
}

/* Each worker thread should open its own connection with the same PRAGMAs
 * (WAL is persisted in the file, but busy_timeout is per-connection): */
sqlite3 *db_open_worker(const char *path) {
	sqlite3 *db;
	if (sqlite3_open(path, &db) != SQLITE_OK) {
		fprintf(stderr, "db_open_worker: cannot open %s: %s\n", path, sqlite3_errmsg(db));
		sqlite3_close(db);
		return NULL;
	}
	sqlite3_exec(db, "PRAGMA busy_timeout = 5000;", NULL, NULL, NULL);
	return db;
}