package db

import (
	"database/sql"
	"fmt"
	"strconv"
	"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/user"
	"git.sr.ht/~evanj/cms/internal/m/valuetype"
)

type Space struct {
	SpaceID   string
	SpaceName string
	SpaceDesc string
}

var (
	queryCreateNewSpace       = `INSERT INTO cms_space (NAME, DESCRIPTION) VALUES (?, ?);`
	queryDeleteSpace          = `DELETE FROM cms_space WHERE ID = ?;`
	queryFindSpaceByID        = `SELECT ID, NAME, DESCRIPTION FROM cms_space WHERE ID = ?;`
	queryDeleteSpaceByID      = `DELETE FROM cms_space WHERE ID = ?;`
	queryCreateNewUserToSpace = `INSERT INTO cms_user_to_space (USER_ID, SPACE_ID) VALUES (?, ?);`
	queryFindUserToSpace      = `SELECT SPACE_ID FROM cms_user_to_space WHERE USER_ID = ? AND SPACE_ID = ?;`
	queryFindSpacesByUser     = `SELECT DISTINCT cms_space.ID, NAME, DESCRIPTION FROM cms_space JOIN cms_user_to_space ON cms_space.ID = cms_user_to_space.SPACE_ID WHERE USER_ID = ? LIMIT ? OFFSET ?;`

	copyUsersQuery = `
		INSERT INTO cms_user_to_space (USER_ID, SPACE_ID)
		SELECT USER_ID, ?
		FROM cms_user_to_space
		WHERE SPACE_ID=?
	`

	copyHooksQuery = `
		INSERT INTO cms_hooks (URL, SPACE_ID)
		SELECT URL, ?
		FROM cms_hooks 
		WHERE SPACE_ID=?
	`

	copyContentTypeQuery = `
		INSERT INTO cms_contenttype (NAME, SPACE_ID)
		SELECT NAME, ?
		FROM cms_contenttype 
		WHERE ID=?
	`

	copyValueTypeQuery = `
		INSERT INTO cms_contenttype_to_valuetype (NAME, CONTENTTYPE_ID, VALUETYPE_ID)
		SELECT NAME, ?, VALUETYPE_ID
		FROM cms_contenttype_to_valuetype 
		WHERE CONTENTTYPE_ID=?
	`
)

func (db *DB) spaceNew(t *sql.Tx, user user.User, name, desc string) (space.Space, error) {
	res, err := t.Exec(queryCreateNewSpace, name, desc)
	if err != nil {
		return nil, fmt.Errorf("space '%s' already exists", name)
	}

	id, err := res.LastInsertId()
	if err != nil {
		return nil, fmt.Errorf("failed to create space")
	}

	var space Space
	if err := t.QueryRow(queryFindSpaceByID, id).Scan(&space.SpaceID, &space.SpaceName, &space.SpaceDesc); err != nil {
		return nil, fmt.Errorf("failed to find space created")
	}

	if _, err := t.Exec(queryCreateNewUserToSpace, user.ID(), space.ID()); err != nil {
		return nil, fmt.Errorf("failed to attach space to user")
	}

	return &space, nil
}

func (db *DB) SpaceNew(user user.User, name, desc string) (space.Space, error) {
	t, err := db.Begin()
	if err != nil {
		return nil, err
	}
	defer t.Rollback()

	space, err := db.spaceNew(t, user, name, desc)
	if err != nil {
		return nil, err
	}

	return space, t.Commit()
}

func (db *DB) SpaceCopy(user user.User, prevS space.Space, name, desc string) (space.Space, error) {
	t, err := db.Begin()
	if err != nil {
		return nil, err
	}
	defer t.Rollback()

	res, err := t.Exec(queryCreateNewSpace, name, desc)
	if err != nil {
		return nil, err
	}

	nextID, err := res.LastInsertId()
	if err != nil {
		return nil, err
	}

	// Copy all users.
	if _, err := t.Exec(copyUsersQuery, nextID, prevS.ID()); err != nil {
		return nil, err
	}

	// Copy all webhooks.
	if _, err := t.Exec(copyHooksQuery, nextID, prevS.ID()); err != nil {
		return nil, err
	}

	next, err := db.spaceGet(t, user, strconv.FormatInt(nextID, 10))
	if err != nil {
		return nil, err
	}

	if err := db.spaceCopyContentTypes(t, next, prevS); err != nil {
		return nil, err
	}

	return next, t.Commit()
}

func (db *DB) spaceCopyContentTypes(t *sql.Tx, next, prevS space.Space) error {
	type cct struct {
		ct contenttype.ContentType
		c  content.Content
	}

	// todo are contents that could not be processes yet, due to Reference and
	// ReferenceList dependencies. They will be taken care of in later iterations
	// (up to a depth of three).
	var todo []cct
	var todoN []cct // Used for later for loop.

	// refmap is used for todo to pull information from.
	// old content ID -> new content
	refmap := make(map[string]content.Content)

	// Copy all content types and their value types.
	// Copy all contents and their values.
	iter := db.ContentTypeIter(t, prevS)
	for iter.Next() {
		prevCT, err := iter.Scan()
		if err != nil {
			return err
		}

		// Copy content type.
		res, err := t.Exec(copyContentTypeQuery, next.ID(), prevCT.ID())
		if err != nil {
			return err
		}

		ctID, err := res.LastInsertId()
		if err != nil {
			return err
		}

		// Copy value type.
		if _, err := t.Exec(copyValueTypeQuery, ctID, prevCT.ID()); err != nil {
			return err
		}

		ct, err := db.contentTypeGet(t, next, strconv.FormatInt(ctID, 10))
		if err != nil {
			return err
		}

		// Copy all contents and their values.
		iter := db.contentIter(t, prevS, prevCT, "name") // TODO: What is this sort type for?
		for iter.Next() {
			prevC, err := iter.Scan()
			if err != nil {
				return err
			}

			ok, err := db.spaceCopyContent(t, next, ct, prevC, refmap)
			if err != nil {
				return fmt.Errorf("failed to copy %s: %w", prevC.MustValueByName("name").Value(), err)
			}

			if !ok {
				todo = append(todo, cct{ct, prevC})
			}
		}
	}

	// NOTE: Going over defaultDepth should be an impossible case, as we stop
	// user from creating depths of >3 elsewhere. It's a hard limit.
	for i := 0; len(todo) > 0 && i < defaultDepth; i++ {
		todoN = append(todo[:0:0], todo...) // Copy todo to todoN.
		todo = nil

		for _, set := range todoN {
			ok, err := db.spaceCopyContent(t, next, set.ct, set.c, refmap)
			if err != nil {
				return err
			}

			if !ok {
				todo = append(todo, set)
			}
		}
	}

	return nil
}

func (db *DB) spaceCopyContent(t *sql.Tx, next space.Space, ct contenttype.ContentType, prevC content.Content, refmap map[string]content.Content) (bool, error) {
	var params []ContentNewParam

	for _, prevF := range ct.Fields() {
		prevV, ok := prevC.ValueByName(prevF.Name())
		if !ok {
			if prevF.Type() == valuetype.Reference || prevF.Type() == valuetype.ReferenceList {
				// Referenced content was deleted.
				continue
			}

			// "null" field. E.G. empty StringSmall.
			prevV = &ContentValue{
				FieldType: string(prevF.Type()),
				FieldName: prevF.Name(),
			}
		}

		switch prevF.Type() {

		case valuetype.Reference:
			ref, ok := refmap[prevV.RefID()]
			if !ok {
				return false, nil
			}
			params = append(params, ContentNewParam{prevV.Type(), prevV.Name(), ref.ID()})

		case valuetype.ReferenceList:
			var IDs []string
			for _, refID := range prevV.RefListIDs() {
				ref, ok := refmap[refID]
				if !ok {
					return false, nil
				}
				IDs = append(IDs, ref.ID())
			}
			params = append(params, ContentNewParam{prevV.Type(), prevV.Name(), strings.Join(IDs, "-")})

		default:
			params = append(params, ContentNewParam{prevV.Type(), prevV.Name(), prevV.Value()})

		}
	}

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

	refmap[prevC.ID()] = c

	return true, nil
}

func (db *DB) spaceGet(t *sql.Tx, user user.User, spaceID string) (space.Space, error) {
	var id string

	if err := t.QueryRow(queryFindUserToSpace, user.ID(), spaceID).Scan(&id); err != nil {
		return nil, fmt.Errorf("failed to find space for user")
	}

	var space Space
	err := t.QueryRow(queryFindSpaceByID, id).Scan(&space.SpaceID, &space.SpaceName, &space.SpaceDesc)
	if err != nil {
		return nil, fmt.Errorf("failed to find space")
	}

	return &space, nil
}

func (db *DB) SpaceGet(user user.User, spaceID string) (space.Space, error) {
	t, err := db.Begin()
	if err != nil {
		return nil, err
	}
	defer t.Rollback()

	space, err := db.spaceGet(t, user, spaceID)
	if err != nil {
		return nil, err
	}

	return space, t.Commit()
}

func (db *DB) SpaceDelete(space space.Space) error {
	t, err := db.Begin()
	if err != nil {
		return err
	}
	defer t.Rollback()

	if _, err := t.Exec(queryDeleteSpace, space.ID()); err != nil {
		return err
	}

	return t.Commit()
}

func (db *DB) SpacesPerUser(user user.User, page int) ([]space.Space, error) {
	var ret []space.Space
	rows, err := db.Query(queryFindSpacesByUser, user.ID(), perPage, perPage*page)
	if err != nil {
		return ret, err
	}

	for rows.Next() {
		var space Space
		if err := rows.Scan(&space.SpaceID, &space.SpaceName, &space.SpaceDesc); err != nil {
			return nil, err
		}
		ret = append(ret, &space)
	}

	return ret, nil
}

func (s *Space) ID() string {
	return s.SpaceID
}

func (s *Space) Name() string {
	return s.SpaceName
}

func (s *Space) Desc() string {
	return s.SpaceDesc
}