// SQLite Caching and Cross-Exploit Database
//
// The db package contains the logic to handle a user provided SQLite DB in order
// to store results and cache HTTP responses. This has a few useful benefits:
//
//  1. When scanning with hundreds of go-exploit implementations, the user significantly
//     cuts down on network requests (therefore speeding up scanning), both from the
//     verified results being cached (you only have to verify a target is Confluence once)
//     and from the cached HTTP responses.
//  2. The result is a useful asset database containing IP, port, installed software, and
//     versions.
//  3. The database can be reused with a go-exploit and generate no network traffic (assuming
//     you aren't doing the exploitation stage). That is very interesting when, for example,
//     you wrote a version scanner for CVE-2024-31982, scanned a customer host that was patched,
//     but then CVE-2024-31983 is released the next day. You can essentially rescan the cached
//     version of their system with your new CVE scanner.
//
// Mostly this package should be totally transparent to users of the framework. The only direct
// interface, currently, should be calls to HTTPGetCache.
package db

import (
	"database/sql"
	"time"

	"github.com/vulncheck-oss/go-exploit/output"
	// pure go sqlite3 driver.
	_ "modernc.org/sqlite"
)

// GlobalSQLHandle is a handle to the SQLite DB for handling cross-exploit data sharing.
var GlobalSQLHandle *sql.DB

// GlobalHTTPRespCacheLimit is the maximum size of an HTTP body that we will attempt to cache.
var GlobalHTTPRespCacheLimit int

const (
	schemaVersion = "1.0.0"

	metadataTable     = `CREATE TABLE IF NOT EXISTS metadata (created INTEGER NOT NULL,schema_version TEXT NOT NULL);`
	initMetadataTable = `INSERT INTO metadata (created, schema_version) VALUES (?, ?)`
	checkMetadata     = `SELECT name FROM sqlite_master WHERE type ='table' = ? AND name='metadata`
	getSchemaVersion  = `SELECT schema_version FROM metadata;`

	verifiedTable = `CREATE TABLE IF NOT EXISTS verified(` +
		`id INTEGER PRIMARY KEY AUTOINCREMENT,` +
		`created INTEGER NOT NULL,` +
		`software_name TEXT NOT NULL,` +
		`installed INTEGER NOT NULL CHECK (installed IN (0, 1)),` +
		`version TEXT NOT NULL,` +
		`rhost TEXT NOT NULL,` +
		`rport INTEGER NOT NULL CHECK (rport >= 0 AND rport <= 65535));`

	httpCacheTable = `CREATE TABLE IF NOT EXISTS http_cache(` +
		`id INTEGER PRIMARY KEY AUTOINCREMENT,` +
		`created INTEGER NOT NULL,` +
		`rhost TEXT NOT NULL,` +
		`rport INTEGER NOT NULL CHECK (rport >= 0 AND rport <= 65535),` +
		`uri TEXT NOT NULL,` +
		`data BLOB NOT NULL);`
)

func InitializeDB(name string) bool {
	GlobalSQLHandle = nil
	if len(name) == 0 {
		return true
	}

	handle, err := sql.Open("sqlite", name)
	if err != nil {
		output.PrintFrameworkError(err.Error())

		return false
	}

	if checkMetadataExists(handle) && !checkSchemaVersion(handle) {
		return false
	} else if !createMetadataTable(handle) {
		return false
	}

	if !createVerifiedSoftwareTable(handle) ||
		!createHTTPCacheTable(handle) {
		return false
	}

	GlobalSQLHandle = handle

	return true
}

func checkMetadataExists(handle *sql.DB) bool {
	name := ""
	err := handle.QueryRow(checkMetadata).Scan(&name)

	return err == nil
}

func createMetadataTable(handle *sql.DB) bool {
	_, err := handle.Exec(metadataTable)
	if err != nil {
		output.PrintFrameworkError(err.Error())

		return false
	}

	_, err = handle.Exec(initMetadataTable, time.Now().Unix(), schemaVersion)
	if err != nil {
		output.PrintFrameworkError(err.Error())

		return false
	}

	return true
}

func checkSchemaVersion(handle *sql.DB) bool {
	version := ""
	err := handle.QueryRow(getSchemaVersion).Scan(&version)
	if err != nil {
		output.PrintFrameworkError(err.Error())

		return false
	}

	if version != schemaVersion {
		output.PrintFrameworkError("Incompatible schema versions")

		return false
	}

	return true
}

func createVerifiedSoftwareTable(handle *sql.DB) bool {
	_, err := handle.Exec(verifiedTable)
	if err != nil {
		output.PrintFrameworkError(err.Error())

		return false
	}

	return true
}

func createHTTPCacheTable(handle *sql.DB) bool {
	// create the cache table
	_, err := handle.Exec(httpCacheTable)
	if err != nil {
		output.PrintFrameworkError(err.Error())

		return false
	}

	return true
}