package db

import (
	"database/sql"
	"log"
	"strconv"
	"strings"

	"git.sr.ht/~evanj/cms/internal/m/valuetype"
	"git.sr.ht/~evanj/security"
	_ "github.com/go-sql-driver/mysql"
)

const (
	// Default pagination amount. For use in LIMIT/OFFSET.
	perPage = 25

	maxUint = ^uint(0)
	maxInt  = int(maxUint >> 1)
)

var (
	// Max before value to be used for pagination when user has specified zero
	// value.
	maxBefore = strconv.Itoa(maxInt)
	zero      int
)

func beformat(before int) int {
	if before == zero {
		return maxInt
	}
	return before
}

type DB struct {
	*sql.DB
	log *log.Logger
	sec securer
}

// securer provides us two things:
// 	 1. Creating user tokens (for use in cookie/other).
// 	 2. Creating salt+hashes for passwords.
type securer interface {
	TokenCreate(val security.TokenMap) (string, error)
	TokenFrom(tokenString string) (security.TokenMap, error)
	HashCreate(salt, pass string) (string, error)
	HashCompare(salt, pass, hash string) error
}

// New, does as one might expect, given a logger, type of database, database
// connection string, and securer interface, opens a pool'd connection to a
// mysql database and pings. If ping fails we return error and nil *DB.
func New(log *log.Logger, typ, creds string, sec securer) (*DB, error) {
	conn, err := sql.Open(typ, creds)
	if err != nil {
		return nil, err
	}

	if err := conn.Ping(); err != nil {
		return nil, err
	}

	// TODO: Best numbers?
	conn.SetMaxIdleConns(10)
	conn.SetMaxOpenConns(100)

	db := &DB{
		conn,
		log,
		sec,
	}

	return db, nil
}

// NewWithConn creates a *DB type and pings the MySQL server. If ping fails we
// return error.
func NewWithConn(log *log.Logger, sec securer, conn *sql.DB) (*DB, error) {
	if err := conn.Ping(); err != nil {
		return nil, err
	}

	// TODO: Best numbers?
	conn.SetMaxIdleConns(10)
	conn.SetMaxOpenConns(100)

	db := &DB{
		conn,
		log,
		sec,
	}

	return db, nil
}

// createTables does our "migration" -migration in quotes as we just dummy
// attempt to create tables on every server startup and ignore "table already
// exists" errors.
func (db *DB) createTables() []error {
	var errors []error
	var err error

	var _ interface{}

	// user
	_, err = db.Exec(`
		CREATE TABLE cms_user (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			NAME varchar(256) UNIQUE NOT NULL,
			HASH varchar(256) NOT NULL
		);
	`)
	if err != nil {
		errors = append(errors, err)
	}

	// space
	_, err = db.Exec(`
		CREATE TABLE cms_space (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			NAME varchar(256) NOT NULL,
			DESCRIPTION varchar(256) NOT NULL
		);
	`)
	if err != nil {
		errors = append(errors, err)
	}

	// user to space
	_, err = db.Exec(`
		CREATE TABLE cms_user_to_space (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			USER_ID INTEGER NOT NULL,
			SPACE_ID INTEGER NOT NULL,
			FOREIGN KEY(USER_ID) REFERENCES cms_user(ID),
			FOREIGN KEY(SPACE_ID) REFERENCES cms_space(ID) ON DELETE CASCADE
		);
	`)
	if err != nil {
		errors = append(errors, err)
	}

	// contenttype
	_, err = db.Exec(`
		CREATE TABLE cms_contenttype (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			NAME varchar(256) NOT NULL,
			SPACE_ID INTEGER NOT NULL,
			CONSTRAINT FG FOREIGN KEY(SPACE_ID) REFERENCES cms_space(ID) ON DELETE CASCADE,
			CONSTRAINT UNIQUEPERCONN UNIQUE(SPACE_ID, NAME)
		);
	`)
	if err != nil {
		errors = append(errors, err)
	}

	// valuetype
	// This will never be created by users.
	_, err = db.Exec(`
		CREATE TABLE cms_valuetype (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			VALUE varchar(256) UNIQUE NOT NULL
		);
	`)
	if err != nil {
		errors = append(errors, err)
	}

	// contenttype to valuetype
	// TODO: Make name + contenttype_id unique.
	_, err = db.Exec(`
		CREATE TABLE cms_contenttype_to_valuetype (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			NAME varchar(256) NOT NULL,
			CONTENTTYPE_ID INTEGER NOT NULL,
			VALUETYPE_ID INTEGER NOT NULL,
			FOREIGN KEY(CONTENTTYPE_ID) REFERENCES cms_contenttype(ID) ON DELETE CASCADE,
			FOREIGN KEY(VALUETYPE_ID) REFERENCES cms_valuetype(ID)
		);
	`)
	if err != nil {
		errors = append(errors, err)
	}

	// content
	_, err = db.Exec(`
		CREATE TABLE cms_content (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			CONTENTTYPE_ID INTEGER NOT NULL,
			FOREIGN KEY(CONTENTTYPE_ID) REFERENCES cms_contenttype(ID) ON DELETE CASCADE
		);
	`)
	if err != nil {
		errors = append(errors, err)
	}

	// content_to_value
	_, err = db.Exec(`
		CREATE TABLE cms_value (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			CONTENT_ID INTEGER NOT NULL,
			CONTENTTYPE_TO_VALUETYPE_ID INTEGER NOT NULL, 
			VALUE_ID INTEGER NOT NULL, -- Should be a foreign key but impossible to make it for two+ tables.
			FOREIGN KEY(CONTENT_ID) REFERENCES cms_content(ID) ON DELETE CASCADE,
			FOREIGN KEY(CONTENTTYPE_TO_VALUETYPE_ID) REFERENCES cms_contenttype_to_valuetype(ID) ON DELETE CASCADE
		);
	`)
	if err != nil {
		errors = append(errors, err)
	}

	// value StringSmall, File
	_, err = db.Exec(`
		CREATE TABLE cms_value_string_small ( 
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			VALUE VARCHAR(256) NOT NULL
		);
	`)
	if err != nil {
		errors = append(errors, err)
	}

	// value StringBig, InputHTML, InputMarkdown
	_, err = db.Exec(`
		CREATE TABLE cms_value_string_big (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			VALUE TEXT NOT NULL
		);
	`)
	if err != nil {
		errors = append(errors, err)
	}

	// value Date
	_, err = db.Exec(`
		CREATE TABLE cms_value_date (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			VALUE DATE NOT NULL
		);
	`)

	// TODO: Reconsider these ON DELETE CASCADES after this point.

	// value Reference
	_, err = db.Exec(`
		CREATE TABLE cms_value_reference (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			VALUE INTEGER NOT NULL,
			FOREIGN KEY(VALUE) REFERENCES cms_content(ID) ON DELETE CASCADE
		);
	`)
	if err != nil {
		errors = append(errors, err)
	}

	// value ReferenceList
	_, err = db.Exec(`
		CREATE TABLE cms_value_reference_list (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT
		);
	`)
	if err != nil {
		errors = append(errors, err)
	}

	// augment to ReferenceList
	_, err = db.Exec(`
		CREATE TABLE cms_value_reference_list_values (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			VALUE_ID INTEGER NOT NULL,
			CONTENT_ID INTEGER NOT NULL,
			FOREIGN KEY(VALUE_ID) REFERENCES cms_value_reference_list(ID) ON DELETE CASCADE,
			FOREIGN KEY(CONTENT_ID) REFERENCES cms_content(ID) ON DELETE CASCADE
		);
	`)
	if err != nil {
		errors = append(errors, err)
	}

	// Webhook
	_, err = db.Exec(`
		CREATE TABLE cms_hooks (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			URL varchar(256) NOT NULL,
			SPACE_ID INTEGER NOT NULL,
			FOREIGN KEY(SPACE_ID) REFERENCES cms_space(ID) ON DELETE CASCADE,
			CONSTRAINT UNIQUEPERCONN UNIQUE(SPACE_ID, URL)
		);
	`)
	if err != nil {
		errors = append(errors, err)
	}

	// Only valuetypes cms supports.
	_, err = db.Exec(`INSERT INTO cms_valuetype (VALUE) values (?);`, valuetype.StringSmall)
	if err != nil {
		errors = append(errors, err)
	}

	_, err = db.Exec(`INSERT INTO cms_valuetype (VALUE) values (?);`, valuetype.StringBig)
	if err != nil {
		errors = append(errors, err)
	}

	_, err = db.Exec(`INSERT INTO cms_valuetype (VALUE) values (?);`, valuetype.InputHTML)
	if err != nil {
		errors = append(errors, err)
	}

	_, err = db.Exec(`INSERT INTO cms_valuetype (VALUE) values (?);`, valuetype.InputMarkdown)
	if err != nil {
		errors = append(errors, err)
	}

	_, err = db.Exec(`INSERT INTO cms_valuetype (VALUE) values (?);`, valuetype.File)
	if err != nil {
		errors = append(errors, err)
	}

	_, err = db.Exec(`INSERT INTO cms_valuetype (VALUE) values (?);`, valuetype.Date)
	if err != nil {
		errors = append(errors, err)
	}

	_, err = db.Exec(`INSERT INTO cms_valuetype (VALUE) values (?);`, valuetype.Reference)
	if err != nil {
		errors = append(errors, err)
	}

	_, err = db.Exec(`INSERT INTO cms_valuetype (VALUE) values (?);`, valuetype.ReferenceList)
	if err != nil {
		errors = append(errors, err)
	}

	return errors
}

// Ensure we have the tables we require. If we receive an error other
// than "table already exists" and also "duplicate entry" for built in data
// types that error will be returned.
// Mainly, we are runnin CREATE TABLE and some INSERT INTO of predefined
// value types.
func (db *DB) EnsureSetup() error {
	for _, err := range db.createTables() {
		errmsg := err.Error()

		if err != nil && strings.Contains(errmsg, "Table ") && strings.Contains(errmsg, "already exists") {
			continue
		}

		if err != nil && strings.Contains(errmsg, "Duplicate entry ") && strings.Contains(errmsg, "VALUE") {
			continue
		}

		return err
	}

	return nil
}

// FileExists makes sure SOME space and content owns the file. I.E. deleted
// spaces can't server files.
func (db *DB) FileExists(URL string) (bool, error) {
	q := `
		SELECT cms_space.ID FROM cms_value_string_small 
		JOIN cms_value ON cms_value.VALUE_ID = cms_value_string_small.ID
		JOIN cms_contenttype_to_valuetype ON cms_value.CONTENTTYPE_TO_VALUETYPE_ID = cms_contenttype_to_valuetype.ID 
		JOIN cms_valuetype ON cms_contenttype_to_valuetype.VALUETYPE_ID = cms_valuetype.ID
		JOIN cms_contenttype ON cms_contenttype_to_valuetype.CONTENTTYPE_ID = cms_contenttype.ID
		JOIN cms_content ON cms_content.CONTENTTYPE_ID = cms_contenttype.ID
		JOIN cms_space ON cms_contenttype.SPACE_ID = cms_space.ID
		WHERE cms_valuetype.VALUE = ? AND cms_value_string_small.VALUE = ?
	`

	var spaceID string
	if err := db.QueryRow(q, valuetype.File, URL).Scan(&spaceID); err != nil {
		db.log.Println("FileExists", err)
		return false, err
	}

	return true, nil
}