package db

import (
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"strings"

	"git.sr.ht/~evanj/cms/internal/m/content"
	"git.sr.ht/~evanj/cms/internal/m/contenttype"
	"git.sr.ht/~evanj/cms/internal/m/space"
	"git.sr.ht/~evanj/cms/internal/m/value"
	"git.sr.ht/~evanj/cms/internal/m/valuetype"
	"github.com/google/uuid"
)

// iota doesn't quite pass the compile time checks I want.
type OrderType struct{ val string }

var (
	OrderAsc  OrderType = OrderType{val: "ASC"}
	OrderDesc OrderType = OrderType{val: "DESC"}
)

func orderTypeReverse(o OrderType) OrderType {
	switch o {
	case OrderAsc:
		return OrderDesc
	case OrderDesc:
		return OrderAsc
	default:
		panic("unreachable")
	}
}

type Content struct {
	// In DB.
	ContentID           string
	ContentParentTypeID string
	// We set.
	ContentValues []ContentValue
}

type ContentValue struct {
	FieldID            string
	FieldType          string            // StringSmall
	FieldName          string            // Title of a blog post
	FieldValue         string            // "My First Blog Post!"
	FieldReference     content.Content   `json:",omitempty"`
	FieldReferenceList []content.Content `json:",omitempty"`
}

type contentValueJSON struct {
	FieldID            string
	FieldType          string     // StringSmall
	FieldName          string     // Title of a blog post
	FieldValue         string     // "My First Blog Post!"
	FieldReference     *Content   `json:",omitempty"`
	FieldReferenceList []*Content `json:",omitempty"`
}

type ContentNewParam struct {
	Type  string
	Name  string
	Value string
}

type ContentUpdateParam struct {
	ID    string
	Type  string
	Value string
}

var (
	queryContentNew = `
		INSERT INTO cms_content (CONTENTTYPE_ID) 
		VALUES (?);
	`

	queryContentDelete = []string{

		// Need delete from w/ joins per final cms_value_* table.

		`DELETE FROM cms_value_reference WHERE ID IN ( 
			SELECT VALUE_ID FROM cms_value 
			JOIN cms_contenttype_to_valuetype
			ON cms_contenttype_to_valuetype.ID = CONTENTTYPE_TO_VALUETYPE_ID
			JOIN cms_valuetype
			ON cms_valuetype.ID = VALUETYPE_ID 
			WHERE CONTENT_ID = ?
			AND cms_valuetype.VALUE = 'Reference'
		)`,

		`DELETE FROM cms_value_reference_list WHERE ID IN ( 
			SELECT VALUE_ID FROM cms_value 
			JOIN cms_contenttype_to_valuetype
			ON cms_contenttype_to_valuetype.ID = CONTENTTYPE_TO_VALUETYPE_ID
			JOIN cms_valuetype
			ON cms_valuetype.ID = VALUETYPE_ID 
			WHERE CONTENT_ID = ?
			AND cms_valuetype.VALUE = 'ReferenceList'
		)`,

		`DELETE FROM cms_value_string_small WHERE ID IN ( 
			SELECT VALUE_ID FROM cms_value 
			JOIN cms_contenttype_to_valuetype
			ON cms_contenttype_to_valuetype.ID = CONTENTTYPE_TO_VALUETYPE_ID
			JOIN cms_valuetype
			ON cms_valuetype.ID = VALUETYPE_ID 
			WHERE CONTENT_ID = ?
			AND cms_valuetype.VALUE = 'StringSmall'
		)`,

		`DELETE FROM cms_value_string_big WHERE ID IN ( 
			SELECT VALUE_ID FROM cms_value 
			JOIN cms_contenttype_to_valuetype
			ON cms_contenttype_to_valuetype.ID = CONTENTTYPE_TO_VALUETYPE_ID
			JOIN cms_valuetype
			ON cms_valuetype.ID = VALUETYPE_ID 
			WHERE CONTENT_ID = ?
			AND cms_valuetype.VALUE = 'StringBig'
		)`,

		`DELETE FROM cms_value_date WHERE ID IN ( 
			SELECT VALUE_ID FROM cms_value 
			JOIN cms_contenttype_to_valuetype
			ON cms_contenttype_to_valuetype.ID = CONTENTTYPE_TO_VALUETYPE_ID
			JOIN cms_valuetype
			ON cms_valuetype.ID = VALUETYPE_ID 
			WHERE CONTENT_ID = ?
			AND cms_valuetype.VALUE = 'Date'
		)`,

		"DELETE FROM cms_value WHERE CONTENT_ID = ?;", // TODO: I don't think we actually need this query due to cascades.
		"DELETE FROM cms_content WHERE ID = ?;",
	}

	queryContentGetByID = `
		SELECT ID, CONTENTTYPE_ID 
		FROM cms_content 
		WHERE ID = ?;
	`

	queryValueNew = `
		INSERT INTO cms_value (CONTENT_ID, CONTENTTYPE_TO_VALUETYPE_ID, VALUE_ID)
		VALUES (?, (SELECT ID FROM cms_contenttype_to_valuetype WHERE CONTENTTYPE_ID = ? AND NAME = ? LIMIT 1), ?);
	`

	queryValueNewStringSmall = `
		INSERT INTO cms_value_string_small (VALUE) 
		VALUES (?);
	`

	queryValueNewStringBig = `
		INSERT INTO cms_value_string_big (VALUE) 
		VALUES (?);
	`

	queryValueNewDate = `
		INSERT INTO cms_value_date (VALUE) 
		VALUES (?);
	`

	queryValueNewReference = `
		INSERT INTO cms_value_reference (VALUE) 
		VALUES (?);
	`

	queryValueUpdateStringSmall = `
		UPDATE cms_value_string_small
		JOIN cms_value ON cms_value.VALUE_ID=cms_value_string_small.ID
		SET VALUE = ?
		WHERE cms_value.ID=?
	`

	queryValueUpdateStringBig = `
		UPDATE cms_value_string_big
		JOIN cms_value ON cms_value.VALUE_ID=cms_value_string_big.ID
		SET VALUE = ?
		WHERE cms_value.ID=?
	`

	queryValueUpdateDate = `
		UPDATE cms_value_date
		JOIN cms_value ON cms_value.VALUE_ID=cms_value_date .ID
		SET VALUE = ?
		WHERE cms_value.ID=?
	`

	queryValueUpdateReference = `
		UPDATE cms_value_reference
		JOIN cms_value ON cms_value.VALUE_ID=cms_value_reference.ID
		SET VALUE = ?
		WHERE cms_value.ID=?
	`

	queryValueGetStringSmallByID = `
		SELECT cms_value.ID as ID, cms_valuetype.VALUE as TYPE, cms_contenttype_to_valuetype.NAME, cms_value_string_small.VALUE
		FROM cms_value
		JOIN cms_contenttype_to_valuetype
		ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID
		JOIN cms_valuetype
		ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID
		JOIN cms_value_string_small
		ON cms_value_string_small.ID = cms_value.VALUE_ID
		WHERE cms_value.ID = ?;
	`

	queryValueGetStringBigByID = `
		SELECT cms_value.ID as ID, cms_valuetype.VALUE as TYPE, cms_contenttype_to_valuetype.NAME, cms_value_string_big.VALUE
		FROM cms_value
		JOIN cms_contenttype_to_valuetype
		ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID
		JOIN cms_valuetype
		ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID
		JOIN cms_value_string_big
		ON cms_value_string_big.ID = cms_value.VALUE_ID
		WHERE cms_value.ID = ?;
	`

	queryValueGetDateByID = `
		SELECT cms_value.ID as ID, cms_valuetype.VALUE as TYPE, cms_contenttype_to_valuetype.NAME, cms_value_date.VALUE
		FROM cms_value
		JOIN cms_contenttype_to_valuetype
		ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID
		JOIN cms_valuetype
		ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID
		JOIN cms_value_date
		ON cms_value_date.ID = cms_value.VALUE_ID
		WHERE cms_value.ID = ?;
	`

	queryValueGetReferenceByID = `
		SELECT cms_value.ID as ID, cms_valuetype.VALUE as TYPE, cms_contenttype_to_valuetype.NAME, cms_value_reference.VALUE
		FROM cms_value
		JOIN cms_contenttype_to_valuetype
		ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID
		JOIN cms_valuetype
		ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID
		JOIN cms_value_reference
		ON cms_value_reference.ID = cms_value.VALUE_ID
		WHERE cms_value.ID = ?;
	`

	queryValueGetReferenceListByID = `
		SELECT cms_value.ID as ID, cms_valuetype.VALUE as TYPE, cms_contenttype_to_valuetype.NAME
		FROM cms_value
		JOIN cms_contenttype_to_valuetype
		ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID
		JOIN cms_valuetype
		ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID
		JOIN cms_value_reference_list
		ON cms_value_reference_list.ID = cms_value.VALUE_ID
		WHERE cms_value.ID = ?;
	`

	queryValueGetReferenceListValuesByID = `
		SELECT cms_value.ID as ID, cms_valuetype.VALUE as TYPE, cms_contenttype_to_valuetype.NAME, cms_value_reference_list_values.CONTENT_ID as VALUE
		FROM cms_value
		JOIN cms_contenttype_to_valuetype
		ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID
		JOIN cms_valuetype
		ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID
		JOIN cms_value_reference_list
		ON cms_value_reference_list.ID = cms_value.VALUE_ID
		JOIN cms_value_reference_list_values
		ON cms_value_reference_list.ID = cms_value_reference_list_values.VALUE_ID
		WHERE cms_value.ID = ?;
	`

	queryValueListByContent = `
		SELECT ID, TYPE, NAME, VALUE FROM (

			SELECT cms_value.ID as ID, cms_valuetype.VALUE as TYPE, cms_contenttype_to_valuetype.NAME, cms_value_string_small.VALUE, cms_contenttype_to_valuetype.ID as ORDER_ID
			FROM cms_value
			JOIN cms_contenttype_to_valuetype
			ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID
			JOIN cms_valuetype
			ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID
			JOIN cms_value_string_small
			ON cms_value_string_small.ID = cms_value.VALUE_ID
			WHERE CONTENT_ID = ?
			AND (
				cms_valuetype.VALUE = 'StringSmall'
				OR cms_valuetype.VALUE = 'File'
			)

			UNION

			SELECT cms_value.ID as ID, cms_valuetype.VALUE as TYPE, cms_contenttype_to_valuetype.NAME, cms_value_date.VALUE, cms_contenttype_to_valuetype.ID as ORDER_ID
			FROM cms_value
			JOIN cms_contenttype_to_valuetype
			ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID
			JOIN cms_valuetype
			ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID
			JOIN cms_value_date
			ON cms_value_date.ID = cms_value.VALUE_ID
			WHERE CONTENT_ID = ?
			AND (
				cms_valuetype.VALUE = 'Date'
			)
			
			UNION

			SELECT cms_value.ID as ID, cms_valuetype.VALUE as TYPE, cms_contenttype_to_valuetype.NAME, cms_value_string_big.VALUE, cms_contenttype_to_valuetype.ID as ORDER_ID
			FROM cms_value
			JOIN cms_contenttype_to_valuetype
			ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID
			JOIN cms_valuetype
			ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID
			JOIN cms_value_string_big
			ON cms_value_string_big.ID = cms_value.VALUE_ID
			WHERE CONTENT_ID = ?
			AND (
				cms_valuetype.VALUE = 'StringBig'
				OR cms_valuetype.VALUE = 'InputHTML'
				OR cms_valuetype.VALUE = 'InputMarkdown'
			)

			UNION

			SELECT cms_value.ID as ID, cms_valuetype.VALUE as TYPE, cms_contenttype_to_valuetype.NAME, cms_value_reference.VALUE, cms_contenttype_to_valuetype.ID as ORDER_ID
			FROM cms_value
			JOIN cms_contenttype_to_valuetype
			ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID
			JOIN cms_valuetype
			ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID
			JOIN cms_value_reference
			ON cms_value_reference.ID = cms_value.VALUE_ID
			WHERE CONTENT_ID = ?
			AND (
				cms_valuetype.VALUE = 'Reference'
			)

			UNION

			SELECT cms_value.ID as ID, cms_valuetype.VALUE as TYPE, cms_contenttype_to_valuetype.NAME, cms_value_reference_list.ID as VALUE, cms_contenttype_to_valuetype.ID as ORDER_ID
			FROM cms_value
			JOIN cms_contenttype_to_valuetype
			ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID
			JOIN cms_valuetype
			ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID
			JOIN cms_value_reference_list
			ON cms_value_reference_list.ID = cms_value.VALUE_ID
			WHERE CONTENT_ID = ?
			AND (
				cms_valuetype.VALUE = 'ReferenceList'
			)

		) AS A

		ORDER BY ORDER_ID ASC;
	`
)

func (db *DB) valueReferenceListUpdate(s space.Space, ct contenttype.ContentType, c *Content, t *sql.Tx, valueID string, IDs []string, depth int) error {
	if len(IDs) < 1 {
		return fmt.Errorf("reference list type has no values")
	}

	// wipe out old list values
	// create new list values
	// TODO: Remove these inline queries, please.
	var refListID string
	if err := t.QueryRow("SELECT cms_value_reference_list.ID FROM cms_value JOIN cms_value_reference_list ON cms_value_reference_list.ID = cms_value.VALUE_ID WHERE cms_value.ID = ?", valueID).Scan(&refListID); err != nil {
		return fmt.Errorf("failed to query for reference list identifier before reference list update")
	}

	if _, err := t.Exec("DELETE FROM cms_value_reference_list_values WHERE VALUE_ID = ?", refListID); err != nil {
		return fmt.Errorf("failed to remove old reference list values before update")
	}

	var value ContentValue
	value.FieldValue = strings.Join(IDs, "-") // Match what client should send us.
	if err := t.QueryRow(queryValueGetReferenceListByID, valueID).Scan(&value.FieldID, &value.FieldType, &value.FieldName); err != nil {
		return err
	}

	for _, cid := range IDs {
		_, err := t.Exec("INSERT INTO cms_value_reference_list_values (VALUE_ID, CONTENT_ID) VALUES (?, ?);", refListID, cid)
		if err != nil {
			return err
		}
	}

	db.contentValueAttachRefList(t, &value, depth)
	c.ContentValues = append(c.ContentValues, value)
	return nil
}

func (db *DB) valueReferenceListNew(s space.Space, ct contenttype.ContentType, c *Content, t *sql.Tx, fieldName string, IDs []string, depth int) error {
	if len(IDs) < 1 {
		return fmt.Errorf("reference list type has no values")
	}

	res, err := t.Exec("INSERT INTO cms_value_reference_list () VALUES ();")
	if err != nil {
		return fmt.Errorf("failed to attach field value of content")
	}

	refListID, err := res.LastInsertId()
	if err != nil {
		return fmt.Errorf("failed to read new field value of content")
	}

	res, err = t.Exec(queryValueNew, c.ID(), ct.ID(), fieldName, refListID)
	if err != nil {
		return fmt.Errorf("failed to attach field value of content")
	}

	valueID, err := res.LastInsertId()
	if err != nil {
		return fmt.Errorf("failed to read new field value of content")
	}

	var value ContentValue
	value.FieldValue = strings.Join(IDs, "-") // Match what client should send us.
	if err := t.QueryRow(queryValueGetReferenceListByID, valueID).Scan(&value.FieldID, &value.FieldType, &value.FieldName); err != nil {
		return err
	}

	for _, cid := range IDs {
		_, err := t.Exec("INSERT INTO cms_value_reference_list_values (VALUE_ID, CONTENT_ID) VALUES (?, ?);", refListID, cid)
		if err != nil {
			return err
		}
	}

	db.contentValueAttachRefList(t, &value, depth)
	c.ContentValues = append(c.ContentValues, value)
	return nil
}

func (db *DB) contentNew(t *sql.Tx, space space.Space, ct contenttype.ContentType, params []ContentNewParam, depth int) (content.Content, error) {
	res, err := t.Exec(queryContentNew, ct.ID())
	if err != nil {
		return nil, fmt.Errorf("failed to create new content attached to contenttype of '%s'", ct.Name())
	}

	contentID, err := res.LastInsertId()
	if err != nil {
		return nil, fmt.Errorf("failed to read new content attached to contenttype of '%s'", ct.Name())
	}

	var content Content
	if err := t.QueryRow(queryContentGetByID, contentID).Scan(&content.ContentID, &content.ContentParentTypeID); err != nil {
		return nil, fmt.Errorf("failed to find content created")
	}

	for _, item := range params {
		// Special data type. Life is hard.
		if item.Type == valuetype.ReferenceList {
			if item.Value == "" {
				// Usually a full space copy after referenced content has been deleted.
				continue
			}

			if err := db.valueReferenceListNew(space, ct, &content, t, item.Name, strings.Split(item.Value, "-"), depth); err != nil {
				return nil, err
			}

			continue
		}

		// Allow zero'd ref.
		if item.Type == valuetype.Reference && item.Value == "" {
			continue
		}

		queryValueNewType, queryValueGetTypeByID, _, err := db.valueQuerySetByType(item.Type)
		if err != nil {
			return nil, err
		}

		res, err := t.Exec(queryValueNewType, item.Value)
		if err != nil {
			return nil, fmt.Errorf("failed to attach field value of content")
		}

		valueID, err := res.LastInsertId()
		if err != nil {
			return nil, fmt.Errorf("failed to read new field value of content")
		}

		res, err = t.Exec(queryValueNew, contentID, ct.ID(), item.Name, valueID)
		if err != nil {
			return nil, fmt.Errorf("failed to attach field value of content")
		}

		valueID, err = res.LastInsertId()
		if err != nil {
			return nil, fmt.Errorf("failed to read new field value of content")
		}

		var value ContentValue
		if err := t.QueryRow(queryValueGetTypeByID, valueID).Scan(&value.FieldID, &value.FieldType, &value.FieldName, &value.FieldValue); err != nil {
			return nil, fmt.Errorf("failed to find value created")
		}

		if err := db.contentValueAttachRef(t, &value, depth); err != nil {
			return nil, err
		}

		content.ContentValues = append(content.ContentValues, value)
	}

	if (len(ct.Fields()) - ct.FieldsWithRefCount()) > len(content.ContentValues) {
		return nil, fmt.Errorf("failed to create all values")
	}

	return &content, nil
}

func (db *DB) ContentNew(space space.Space, ct contenttype.ContentType, params []ContentNewParam) (content.Content, error) {
	t, err := db.Begin()
	if err != nil {
		return nil, err
	}
	defer t.Rollback()

	c, err := db.contentNew(t, space, ct, params, defaultDepth)
	if err != nil {
		return nil, err
	}

	return c, t.Commit()
}

type ContentRefSet struct {
	ContentTypeID, ContentID string
}

func (db *DB) contentRefererList(contentID string, depth int) (ret []ContentRefSet, err error) {
	// Cap total recursion to defaultDepth
	if depth < 1 {
		return ret, nil
	}

	depth--

	refQ := `
		SELECT cms_contenttype_to_valuetype.CONTENTTYPE_ID, cms_value.CONTENT_ID FROM cms_value_reference 
		JOIN cms_value ON VALUE_ID = cms_value_reference.ID
		JOIN cms_contenttype_to_valuetype ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID
		JOIN cms_valuetype ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID
		WHERE cms_valuetype.VALUE = ?
		AND cms_value_reference.VALUE = ?
	`

	refListQ := `
		SELECT cms_contenttype_to_valuetype.CONTENTTYPE_ID, cms_value.CONTENT_ID FROM cms_value_reference_list_values
		JOIN cms_value_reference_list ON cms_value_reference_list.ID = cms_value_reference_list_values.VALUE_ID
		JOIN cms_value ON cms_value.VALUE_ID = cms_value_reference_list.ID
		JOIN cms_contenttype_to_valuetype ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID
		JOIN cms_valuetype ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID
		WHERE cms_valuetype.VALUE = ?
		AND cms_value_reference_list_values.CONTENT_ID = ?
	`

	rows, err := db.Query(refQ, valuetype.Reference, contentID)
	if err != nil {
		return ret, err
	}
	defer rows.Close()

	for rows.Next() {
		var ref ContentRefSet
		if err := rows.Scan(&ref.ContentTypeID, &ref.ContentID); err != nil {
			return ret, err
		}

		nested, err := db.contentRefererList(ref.ContentID, depth)
		if err != nil {
			return nil, err
		}

		ret = append(ret, ref)
		ret = append(ret, nested...)
	}

	rows, err = db.Query(refListQ, valuetype.ReferenceList, contentID)
	if err != nil {
		return ret, err
	}
	defer rows.Close()

	for rows.Next() {
		var ref ContentRefSet
		if err := rows.Scan(&ref.ContentTypeID, &ref.ContentID); err != nil {
			return ret, err
		}

		nested, err := db.contentRefererList(ref.ContentID, depth)
		if err != nil {
			return nil, err
		}

		ret = append(ret, ref)
		ret = append(ret, nested...)
	}

	return ret, nil
}

// ContentRefererList will retreive all content IDs that references a given piece of content.
func (db *DB) ContentRefererList(contentID string) (ret []ContentRefSet, err error) {
	return db.contentRefererList(contentID, defaultDepth)
}

func (db *DB) contentUpdate(t *sql.Tx, space space.Space, ct contenttype.ContentType, content content.Content, newParams []ContentNewParam, updateParams []ContentUpdateParam) error {
	depth := defaultDepth

	// TODO: Do we have to do this w/ types? Can you not find a better way?
	// I'm beginning to think we're over using interfaces and it's hurting.
	c, ok := content.(*Content)
	if !ok {
		return fmt.Errorf("failed to type cast value type before attempting to update content")
	}

	for _, item := range updateParams {
		// Special data type. Life is hard.
		if item.Type == valuetype.ReferenceList {
			if item.Value == "" {
				// Usually a full space copy after referenced content has been deleted.
				continue
			}

			if err := db.valueReferenceListUpdate(space, ct, c, t, item.ID, strings.Split(item.Value, "-"), depth); err != nil {
				return err
			}
			continue
		}

		// Allow zero'd ref.
		if item.Type == valuetype.Reference && item.Value == "" {
			continue
		}

		_, _, queryValueUpdate, err := db.valueQuerySetByType(item.Type)
		if err != nil {
			return err
		}

		if _, err := t.Exec(queryValueUpdate, item.Value, item.ID); err != nil {
			db.log.Println(err)
			return fmt.Errorf("failed to create update content value '%s'", item.Value)
		}
	}

	for _, item := range newParams {
		// Special data type. Life is hard.
		if item.Type == valuetype.ReferenceList {
			if item.Value == "" {
				// Usually a full space copy after referenced content has been deleted.
				continue
			}

			if err := db.valueReferenceListNew(space, ct, c, t, item.Name, strings.Split(item.Value, "-"), depth); err != nil {
				return err
			}
			continue
		}

		// Allow zero'd ref.
		if item.Type == valuetype.Reference && item.Value == "" {
			continue
		}

		queryValueNewType, queryValueGetTypeByID, _, err := db.valueQuerySetByType(item.Type)
		if err != nil {
			return err
		}

		res, err := t.Exec(queryValueNewType, item.Value)
		if err != nil {
			return fmt.Errorf("failed to attach field value of content")
		}

		valueID, err := res.LastInsertId()
		if err != nil {
			return fmt.Errorf("failed to read new field value of content")
		}

		res, err = t.Exec(queryValueNew, content.ID(), ct.ID(), item.Name, valueID)
		if err != nil {
			return fmt.Errorf("failed to attach field value of content")
		}

		valueID, err = res.LastInsertId()
		if err != nil {
			return fmt.Errorf("failed to read new field value of content")
		}

		var value ContentValue
		if err := t.QueryRow(queryValueGetTypeByID, valueID).Scan(&value.FieldID, &value.FieldType, &value.FieldName, &value.FieldValue); err != nil {
			return fmt.Errorf("failed to find value created")
		}

		if err := db.contentValueAttachRef(t, &value, depth); err != nil {
			return err
		}

		c.ContentValues = append(c.ContentValues, value)
	}

	return nil
}

func (db *DB) ContentUpdate(space space.Space, ct contenttype.ContentType, content content.Content, newParams []ContentNewParam, updateParams []ContentUpdateParam) (content.Content, error) {
	t, err := db.Begin()
	if err != nil {
		return nil, err
	}
	defer t.Rollback()

	if err := db.contentUpdate(t, space, ct, content, newParams, updateParams); err != nil {
		return nil, err
	}

	if err := t.Commit(); err != nil {
		return nil, err
	}

	return db.ContentGet(space, ct, content.ID())
}

func (db *DB) ContentDelete(space space.Space, ct contenttype.ContentType, content content.Content) error {
	t, err := db.Begin()
	if err != nil {
		return err
	}
	defer t.Rollback()

	for _, q := range queryContentDelete {
		_, err := t.Exec(q, content.ID())
		if err != nil {
			return err
		}
	}

	return t.Commit()
}

func sortinfo(t *sql.Tx, ct contenttype.ContentType, sortField string) (string, string, error) {
	var (
		sortFieldValueType string
		sortFieldTableName string
	)

	q := `
		SELECT VALUE FROM cms_contenttype_to_valuetype
		JOIN cms_valuetype ON cms_valuetype.ID=cms_contenttype_to_valuetype.VALUETYPE_ID
		WHERE NAME=? AND CONTENTTYPE_ID=?
	`
	if err := t.QueryRow(q, sortField, ct.ID()).Scan(&sortFieldValueType); err != nil {
		return "", "", err
	}

	switch sortFieldValueType {

	case valuetype.StringSmall:
		fallthrough
	case valuetype.File:
		sortFieldTableName = "cms_value_string_small"

	case valuetype.StringBig:
		fallthrough
	case valuetype.InputHTML:
		fallthrough
	case valuetype.InputMarkdown:
		sortFieldTableName = "cms_value_string_big"

	case valuetype.Date:
		sortFieldTableName = "cms_value_date"

	case valuetype.Reference:
	case valuetype.ReferenceList:
		return "", "", errors.New("cannot search by reference field type")

	default:
		return "", "", errors.New("unexpected error: failed to find table name for value type (developer error)")

	}

	return sortFieldValueType, sortFieldTableName, nil
}

func (db *DB) contentPerContentType(t *sql.Tx, space space.Space, ct contenttype.ContentType, before int, order OrderType, sortField string, depth int) (content.ContentList, error) {
	var (
		tmpID        int
		tmpContentID string

		r       []content.Content
		hasMore bool
	)

	before = beformat(before)
	order = orderTypeReverse(order)

	// Create temporary table for queries.
	tbl := fmt.Sprintf("cms_tmp_cl_by_ct_%s", strings.ReplaceAll(uuid.New().String(), "-", "_"))

	// Get the table the sortField is part of.
	_, sortFieldTableName, err := sortinfo(t, ct, sortField)
	if err != nil {
		return nil, err
	}

	// Fill temporary table with data.
	q := fmt.Sprintf(`
		CREATE TEMPORARY TABLE %s (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			CONTENT_ID INTEGER NOT NULL
		)
	`, tbl)
	if _, err := t.Exec(q); err != nil {
		return nil, err
	}

	// Careful...
	q = fmt.Sprintf(`
		INSERT INTO %s (CONTENT_ID)

		SELECT cms_content.ID AS CONTENT_ID FROM cms_value

		JOIN cms_content ON cms_value.CONTENT_ID = cms_content.ID

		JOIN cms_contenttype_to_valuetype ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID

		JOIN cms_valuetype ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID

		LEFT JOIN %s ON %s.ID = cms_value.VALUE_ID

		WHERE cms_content.CONTENTTYPE_ID = ?
		AND cms_contenttype_to_valuetype.NAME = ?

		ORDER BY %s.VALUE %s
	`, tbl, sortFieldTableName, sortFieldTableName, sortFieldTableName, order.val)
	if _, err := t.Exec(q, ct.ID(), sortField); err != nil {
		return nil, err
	}

	// Query the temporary table.
	q = fmt.Sprintf("SELECT ID, CONTENT_ID FROM %s WHERE ID < ? ORDER BY ID DESC LIMIT ?", tbl)
	rows, err := t.Query(q, before, perPage+1)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for i := 0; rows.Next(); i++ {
		if i == perPage {
			hasMore = true
			break
		}

		if err := rows.Scan(&tmpID, &tmpContentID); err != nil {
			return nil, err
		}

		c, err := db.ContentGet(space, ct, tmpContentID)
		if err != nil {
			return nil, err
		}

		r = append(r, c)
	}

	return newContentList(r, hasMore, tmpID), nil
}

func (db *DB) ContentPerContentType(space space.Space, ct contenttype.ContentType, before int, order OrderType, sortField string) (content.ContentList, error) {
	t, err := db.Begin()
	if err != nil {
		return nil, err
	}
	defer t.Rollback()

	list, err := db.contentPerContentType(t, space, ct, before, order, sortField, defaultDepth)
	if err != nil {
		return nil, err
	}

	return list, t.Commit()
}

func (db *DB) ContentSearch(space space.Space, ct contenttype.ContentType, sortField, query string, before int) (content.ContentList, error) {
	var (
		tmpID        int
		tmpContentID string

		s       = fmt.Sprintf("%%%s%%", query)
		r       []content.Content
		hasMore bool
	)

	before = beformat(before)

	t, err := db.Begin()
	if err != nil {
		return nil, err
	}
	defer t.Rollback()

	// Create temporary table for queries.
	tbl := fmt.Sprintf("cms_tmp_cl_search_%s", strings.ReplaceAll(uuid.New().String(), "-", "_"))

	// Get the table the sortField is part of.
	_, sortFieldTableName, err := sortinfo(t, ct, sortField)
	if err != nil {
		return nil, err
	}

	// Fill temporary table with data.
	q := fmt.Sprintf(`
		CREATE TEMPORARY TABLE %s (
			ID INTEGER PRIMARY KEY AUTO_INCREMENT,
			CONTENT_ID INTEGER NOT NULL
		)
	`, tbl)
	if _, err := t.Exec(q); err != nil {
		return nil, err
	}

	// Careful...
	q = fmt.Sprintf(`
		INSERT INTO %s (CONTENT_ID)

		SELECT cms_content.ID FROM cms_value

		JOIN cms_content ON cms_value.CONTENT_ID = cms_content.ID

		JOIN cms_contenttype_to_valuetype ON cms_contenttype_to_valuetype.ID = cms_value.CONTENTTYPE_TO_VALUETYPE_ID

		JOIN cms_valuetype ON cms_valuetype.ID = cms_contenttype_to_valuetype.VALUETYPE_ID

		LEFT JOIN %s ON %s.ID = cms_value.VALUE_ID

	 	WHERE cms_content.CONTENTTYPE_ID = ? 
	 	AND cms_contenttype_to_valuetype.NAME = ?
		AND %s.VALUE LIKE ?
	`, tbl, sortFieldTableName, sortFieldTableName, sortFieldTableName)
	if _, err := t.Exec(q, ct.ID(), sortField, s); err != nil {
		return nil, err
	}

	// Query the temporary table.
	q = fmt.Sprintf("SELECT ID, CONTENT_ID FROM %s WHERE ID < ? ORDER BY ID DESC LIMIT ?", tbl)
	rows, err := t.Query(q, before, perPage+1)
	if err != nil {
		return nil, err
	}
	// Handled below.
	// defer rows.Close()

	for i := 0; rows.Next(); i++ {
		if i == perPage {
			hasMore = true
			break
		}

		if err := rows.Scan(&tmpID, &tmpContentID); err != nil {
			return nil, err
		}

		c, err := db.ContentGet(space, ct, tmpContentID)
		if err != nil {
			return nil, err
		}

		r = append(r, c)
	}

	if err := rows.Close(); err != nil {
		return nil, err
	}

	return newContentList(r, hasMore, tmpID), t.Commit()
}

func (db *DB) contentGet(t *sql.Tx, space space.Space, ct contenttype.ContentType, contentID string, depth int) (content.Content, error) {
	var content Content
	if err := t.QueryRow(queryContentGetByID, contentID).Scan(&content.ContentID, &content.ContentParentTypeID); err != nil {
		return nil, fmt.Errorf("failed to find content")
	}

	// TODO: For some reason t.Query(...) is causing errors here.
	// See: https://github.com/go-sql-driver/mysql/issues/314
	rows, err := db.Query(queryValueListByContent, content.ID(), content.ID(), content.ID(), content.ID(), content.ID())
	if err != nil {
		return nil, fmt.Errorf("failed to find value(s)")
	}
	defer rows.Close()

	for rows.Next() {
		var value ContentValue
		if err := rows.Scan(&value.FieldID, &value.FieldType, &value.FieldName, &value.FieldValue); err != nil {
			return nil, fmt.Errorf("failed to scan values(s)")
		}

		if err := db.contentValueAttachRef(t, &value, depth); err != nil {
			return nil, err
		}

		if err := db.contentValueAttachRefList(t, &value, depth); err != nil {
			return nil, err
		}

		content.ContentValues = append(content.ContentValues, value)
	}

	return &content, nil
}

func (db *DB) ContentGet(space space.Space, ct contenttype.ContentType, contentID string) (content.Content, error) {
	if space == nil {
		return nil, fmt.Errorf("must provide parent space")
	}

	if ct == nil {
		return nil, fmt.Errorf("must provide parent contenttype")
	}

	t, err := db.Begin()
	if err != nil {
		return nil, err
	}
	defer t.Rollback()

	c, err := db.contentGet(t, space, ct, contentID, defaultDepth)
	if err != nil {
		return nil, err
	}

	return c, t.Commit()
}

func (db *DB) valueQuerySetByType(typ valuetype.ValueTypeEnum) (insert, get, update string, err error) {
	switch typ {

	case valuetype.StringSmall:
		fallthrough
	case valuetype.File:
		return queryValueNewStringSmall, queryValueGetStringSmallByID, queryValueUpdateStringSmall, nil

	case valuetype.StringBig:
		fallthrough
	case valuetype.InputHTML:
		fallthrough
	case valuetype.InputMarkdown:
		return queryValueNewStringBig, queryValueGetStringBigByID, queryValueUpdateStringBig, nil

	case valuetype.Date:
		return queryValueNewDate, queryValueGetDateByID, queryValueUpdateDate, nil

	case valuetype.Reference:
		return queryValueNewReference, queryValueGetReferenceByID, queryValueUpdateReference, nil

	}

	return "", "", "", fmt.Errorf("%s is not a valid valuetype", typ)
}

func (db *DB) contentValueAttachRef(t *sql.Tx, c *ContentValue, depth int) error {
	depth--
	if c.Type() != valuetype.Reference || depth < 1 {
		return nil
	}

	ref, err := db.contentGet(t, nil, nil, c.Value(), depth)
	if err != nil {
		return err
	}

	c.FieldReference = ref
	return nil
}

func (db *DB) contentValueAttachRefList(t *sql.Tx, c *ContentValue, depth int) error {
	depth--
	if c.Type() != valuetype.ReferenceList || depth < 1 {
		return nil
	}

	rows, err := t.Query(queryValueGetReferenceListValuesByID, c.ID())
	if err != nil {
		return err
	}

	var values []ContentValue
	for rows.Next() {
		var value ContentValue
		if err := rows.Scan(&value.FieldID, &value.FieldType, &value.FieldName, &value.FieldValue); err != nil {
			return err
		}
		values = append(values, value)
	}

	if err := rows.Close(); err != nil {
		return err
	}

	var ids []string
	for _, value := range values {
		ref, err := db.contentGet(t, nil, nil, value.FieldValue, depth)
		if err != nil {
			return err
		}

		c.FieldReferenceList = append(c.FieldReferenceList, ref)
		ids = append(ids, ref.ID())
	}

	c.FieldValue = strings.Join(ids, "-") // Match what client sends us.
	return nil
}

func (c *Content) ID() string {
	return c.ContentID
}

func (c *Content) Type() string {
	return c.ContentParentTypeID
}

func (c *Content) Values() []value.Value {
	var ret []value.Value
	for _, item := range c.ContentValues {
		ret = append(ret, &ContentValue{
			item.FieldID,
			item.FieldType,
			item.FieldName,
			item.FieldValue,
			item.FieldReference,
			item.FieldReferenceList,
		})
	}
	return ret
}

func (c *Content) ValueByName(name string) (value.Value, bool) {
	for _, val := range c.Values() {
		if val.Name() == name {
			return val, true
		}
	}
	return nil, false
}

func (c *Content) MustValueByName(name string) (ret value.Value) {
	val, ok := c.ValueByName(name)
	if ok {
		return val
	}
	return
}

func (c *ContentValue) ID() string {
	return c.FieldID
}

func (c *ContentValue) Type() string {
	return c.FieldType
}

func (c *ContentValue) Name() string {
	return c.FieldName
}

func (c *ContentValue) Value() string {
	return c.FieldValue
}

func (c *ContentValue) RefName() string {
	return c.FieldReference.MustValueByName("name").Value()
}

func (c *ContentValue) RefID() string {
	return c.FieldReference.ID()
}

func (c *ContentValue) RefListNames() string {
	var names []string
	for _, item := range c.FieldReferenceList {
		names = append(names, item.MustValueByName("name").Value())
	}
	return strings.Join(names, ", ")
}

func (c *ContentValue) RefListIDs() []string {
	var ids []string
	for _, item := range c.FieldReferenceList {
		ids = append(ids, item.ID())
	}
	return ids
}

func (c *ContentValue) MarshalJSON() (ret []byte, err error) {
	var v contentValueJSON

	v.FieldID = c.FieldID
	v.FieldType = c.FieldType
	v.FieldName = c.FieldName
	v.FieldValue = c.FieldValue

	if c.FieldReference != nil {
		var ok bool
		v.FieldReference, ok = c.FieldReference.(*Content)
		if !ok {
			return ret, fmt.Errorf("corrupted reference value")
		}
	}

	if len(c.FieldReferenceList) > 0 {
		for _, prev := range c.FieldReferenceList {
			next, ok := prev.(*Content)
			if !ok {
				return ret, fmt.Errorf("corrupted reference list value")
			}
			v.FieldReferenceList = append(v.FieldReferenceList, next)
		}
	}

	return json.Marshal(v)
}

func (c *ContentValue) UnmarshalJSON(b []byte) error {
	var v contentValueJSON

	if err := json.Unmarshal(b, &v); err != nil {
		return err
	}

	c.FieldID = v.FieldID
	c.FieldType = v.FieldType
	c.FieldName = v.FieldName
	c.FieldValue = v.FieldValue
	c.FieldReference = v.FieldReference
	for _, item := range v.FieldReferenceList {
		c.FieldReferenceList = append(c.FieldReferenceList, item)
	}

	return nil
}

type contentIter struct {
	db        *DB
	t         *sql.Tx // Not used for the moment (we only use simple queries).
	space     space.Space
	ct        contenttype.ContentType
	sortField string

	// For pump
	list content.ContentList
	err  error
}

func (db *DB) contentIter(t *sql.Tx, space space.Space, ct contenttype.ContentType, sortField string) *contentIter {
	iter := &contentIter{db, t, space, ct, sortField, newContentList(nil, false, 0), nil}
	iter.pump()
	return iter
}

func (db *DB) ContentIter(space space.Space, ct contenttype.ContentType, sortField string) (*contentIter, *sql.Tx, error) {
	t, err := db.Begin()
	if err != nil {
		return nil, nil, err
	}
	return db.contentIter(t, space, ct, sortField), t, nil
}

func (iter *contentIter) pump() {
	list, err := iter.db.contentPerContentType(iter.t, iter.space, iter.ct, iter.list.Before(), OrderAsc, iter.sortField, defaultDepth)
	iter.list = list
	iter.err = err
}

func (iter *contentIter) Next() bool {
	if iter.err != nil {
		return true // Error is picked up with Scan call.
	}
	return len(iter.list.List()) > 0
}

func (iter *contentIter) Scan() (content.Content, error) {
	var (
		first content.Content
		err   error
	)

	if iter.err != nil {
		return first, iter.err
	}

	list := iter.list.List()
	first, rest := list[0], list[1:]

	iter.list = newContentList(rest, iter.list.More(), iter.list.Before())
	if err != nil {
		return nil, err
	}

	if len(iter.list.List()) < 1 {
		iter.pump()
	}

	return first, nil
}

// CONTENT LIST STRUCT / INTERFACE

type ContentList struct {
	ContentList       []content.Content
	ContentListMore   bool
	ContentListBefore int
}

func newContentList(list []content.Content, hasMore bool, last int) *ContentList {
	return &ContentList{list, hasMore, last}
}

func (cl *ContentList) List() []content.Content { return cl.ContentList }
func (cl *ContentList) More() bool              { return cl.ContentListMore }
func (cl *ContentList) Before() int             { return cl.ContentListBefore }