package db

import (
	"database/sql"
	"errors"
	"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/org"
	"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
	// Set on fetch.
	SpaceOrg org.Org
}

type spaceOrg struct {
	OrgID       string
	OrgTierName string
}

var (
	queryCreateNewSpace = `INSERT INTO cms_space (NAME, DESCRIPTION, ORG_ID) VALUES (?, ?, ?);`
	queryUpdateSpace    = `UPDATE cms_space SET NAME = ?, DESCRIPTION = ? WHERE ID = ?;`

	queryDeleteSpace = `
		DELETE cms_space FROM cms_space
		JOIN cms_org ON cms_org.ID=cms_space.ORG_ID
		JOIN cms_user ON cms_user.ORG_ID=cms_org.ID
		WHERE cms_user.ID=? 
		AND cms_space.ID=?
	`

	queryFindSpaceByUserAndID = `
		SELECT cms_space.ID, cms_space.NAME, cms_space.DESCRIPTION FROM cms_space
		JOIN cms_org ON cms_org.ID=cms_space.ORG_ID
		JOIN cms_user ON cms_user.ORG_ID=cms_org.ID
		WHERE cms_user.ID=? 
		AND cms_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, user.Org().ID())
	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")
	}

	return db.spaceGet(t, user, strconv.FormatInt(id, 10))
}

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) SpaceUpdate(user user.User, space 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(queryUpdateSpace, name, desc, space.ID())
	if err != nil {
		return nil, err
	}

	c, err := res.RowsAffected()
	if err != nil {
		return nil, err
	}

	if c != 1 {
		// Or maybe too much was updated.
		return nil, errors.New("space was not updated")
	}

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

	return next, 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, user.Org().ID())
	if err != nil {
		return nil, err
	}

	nextID, err := res.LastInsertId()
	if 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
	}

	// We've made a change to the content copy impl. We skip first pass of ref
	// types then update contents. This fixes loop references.

	// 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

	// 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 {
				// If we're not OK that means we need to update the content to account for ref types.
				todo = append(todo, cct{ct, prevC})
			}
		}
	}

	// Update reference types that were skipped because content was not createt.
	for _, set := range todo {
		err := db.spaceUpdateContent(t, next, set.ct, set.c, refmap)
		if err != nil {
			return fmt.Errorf("failed to copy for references %s: %w", set.c.MustValueByName("name").Value(), err)
		}
	}

	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
		skip   bool
	)

	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 {
				skip = true
				continue
			}
			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 {
					skip = true
					continue
				}
				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 !skip, nil
}

func (db *DB) spaceUpdateContent(t *sql.Tx, next space.Space, ct contenttype.ContentType, prevC content.Content, refmap map[string]content.Content) 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 errors.New("failed to copy reference value: content not created")
			}
			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 errors.New("failed to copy reference list value(s): content not created")
				}
				IDs = append(IDs, ref.ID())
			}
			params = append(params, ContentNewParam{prevV.Type(), prevV.Name(), strings.Join(IDs, "-")})

		}
	}

	return db.contentUpdate(t, next, ct, refmap[prevC.ID()], params, []ContentUpdateParam{})
}

func (db *DB) spaceGet(t *sql.Tx, user user.User, spaceID string) (space.Space, error) {
	space := Space{
		SpaceOrg: user.Org(),
		// SpaceOrg: spaceOrg{
		// 	OrgID:       user.Org().ID(),
		// 	OrgTierName: user.Org().Tier().Name,
		// },
	}
	err := t.QueryRow(queryFindSpaceByUserAndID, user.ID(), spaceID).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(user user.User, space space.Space) error {
	t, err := db.Begin()
	if err != nil {
		return err
	}
	defer t.Rollback()

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

	return t.Commit()
}

func (db *DB) spacesPerUser(t *sql.Tx, user user.User, before int) (space.SpaceList, error) {
	var (
		r       []space.Space
		id      int
		hasMore bool
	)

	before = beformat(before)

	q := `
		SELECT cms_space.ID FROM cms_space
		JOIN cms_org ON cms_org.ID=cms_space.ORG_ID
		JOIN cms_user on cms_user.ORG_ID=cms_org.ID
		WHERE cms_user.ID=? AND cms_space.ID<?
		ORDER BY cms_space.ID DESC LIMIT ?
	`

	rows, err := db.Query(q, user.ID(), before, perPage+1)
	if err != nil {
		return nil, err
	}

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

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

		s, err := db.spaceGet(t, user, strconv.Itoa(id))
		if err != nil {
			return nil, err
		}

		r = append(r, s)
	}

	return newSpaceList(r, hasMore, id), nil
}

func (db *DB) SpacesPerUser(user user.User, before int) (space.SpaceList, error) {
	t, err := db.Begin()
	if err != nil {
		return nil, err
	}
	defer t.Rollback()

	list, err := db.spacesPerUser(t, user, before)
	if err != nil {
		return nil, err
	}

	return list, t.Commit()
}

func (s *Space) ID() string   { return s.SpaceID }
func (s *Space) Name() string { return s.SpaceName }
func (s *Space) Desc() string { return s.SpaceDesc }
func (s *Space) Org() org.Org { return s.SpaceOrg }

// SPACE LIST STRUCT / INTERFACE

type SpaceList struct {
	SpaceList       []space.Space
	SpaceListMore   bool
	SpaceListBefore int
}

func newSpaceList(list []space.Space, hasMore bool, last int) *SpaceList {
	return &SpaceList{list, hasMore, last}
}

func (sl *SpaceList) List() []space.Space { return sl.SpaceList }
func (sl *SpaceList) More() bool          { return sl.SpaceListMore }
func (sl *SpaceList) Before() int         { return sl.SpaceListBefore }