/*
Requirements:
	Go tool chain
	go-sqlite3 requires C compiler

Install:

# Create empty dir. for project. & cd to it.
go mod init mysql
go get github.com/mattn/go-sqlite3
export CGO_ENABLED=1

#	paste code into file main.go
# Upon save, if your compiler is founad, compilation of the driver will begin.
# Monitor cpu usage to see when compiler/assembler completes.

# Create the sample DB in same directory as main.go file
# using comment block at end of this code.

# Run without building:
go run .

# Checking the table, it will not be dropped but, there will be bad data in the is_admin column.
sqlite> select * from users where name = 'Fancy Pants';
id  name         age  country_code  username  password    is_admin
--  -----------  ---  ------------  --------  ----------  -----------------------------
14  Fancy Pants  23   UK            fp123     b00ze4L!f3  Robert'); DROP TABLE users;--

# NOTE: In it's current form it can only insert 1 record due to the UNIQUE
# constaint on users.username field.
*/

package main

import (
    "fmt"
    "database/sql"
		_ "github.com/mattn/go-sqlite3"
		"log"
)


func main() {
	dbfile := "./bd_sample.db"
  fmt.Println("Loading DB", dbfile)

	db := loadDB(dbfile)

	stmt, err := db.Prepare(`INSERT INTO users (name, age, country_code, username, password, is_admin)
		VALUES(?, ?, ?, ?, ?, ?)`)
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()

	if _, err := stmt.Exec("Fancy Pants", 23, "UK",
		"fp123", "b00ze4L!f3", "Robert'); DROP TABLE users;--"); err != nil {
		log.Fatal(err)
	} else {
		log.Println("stmt Exec'd successfully.")
	}

}

func loadDB(dbDiskPath string) *sql.DB {
  dbDisk := openDB(dbDiskPath)
  return dbDisk
}

// Get a handle to a SQLite DB.
func openDB(dbPath string) *sql.DB {
  dbURL := "file:" + dbPath + "?_foreign_keys=on"

  db, err := sql.Open("sqlite3", dbURL)
  if err != nil {
    panic(err)
  }

  if err := db.Ping(); err != nil {
    log.Fatal(err)
  }

  return db
}

/*
-- Sample DB Schema and data
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER NOT NULL,
  country_code TEXT NOT NULL,
  username TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL,
  is_admin BOOLEAN
);

INSERT INTO
  users (id, name, age, country_code, username, password, is_admin)
VALUES
  (1, 'David', 34, 'US', 'DavidDev', 'insertPractice', false);
INSERT INTO
  users (id, name, age, country_code, username, password, is_admin)
VALUES
  (2, 'Samantha', 29, 'BR', 'Sammy93', 'addingRecords!', false);
INSERT INTO
  users (id, name, age, country_code, username, password, is_admin)
VALUES
  (3, 'John', 39, 'CA', 'Jjdev21', 'welovebootdev', false);
INSERT INTO
  users (id, name, age, country_code, username, password, is_admin)
VALUES
  (4, 'Ram', 42, 'IN', 'Ram11c', 'thisSQLcourserocks', false);
INSERT INTO
  users (id, name, age, country_code, username, password, is_admin)
VALUES
  (5, 'Hunter', 30, 'US', 'Hdev92', 'backendDev', false);
INSERT INTO
  users (id, name, age, country_code, username, password, is_admin)
VALUES
  (6, 'Allan', 27, 'US', 'Alires', 'iLoveB00tdev', true);
INSERT INTO
  users (id, name, age, country_code, username, password, is_admin)
VALUES
  (7, 'Lance', 20, 'US', 'LanChr', 'b00tdevisbest', false);
INSERT INTO
  users (id, name, age, country_code, username, password, is_admin)
VALUES
  (8, 'Tiffany', 28, 'US', 'Tifferoon', 'autoincrement', true);
INSERT INTO
  users (id, name, age, country_code, username, password, is_admin)
VALUES
  (9, 'Lane', 27, 'US', 'wagslane', 'update_me', false);
INSERT INTO
  users (id, name, age, country_code, username, password, is_admin)
VALUES
*/