package db

import (
	"database/sql"
	"fmt"
	"html/template"
	"os"
	"regexp"
	"sort"
	"strings"
	"time"

	"github.com/FChannel0/FChannel-Server/activitypub"
	"github.com/FChannel0/FChannel-Server/config"
	_ "github.com/lib/pq"
)

var db *sql.DB

type NewsItem struct {
	Title   string
	Content template.HTML
	Time    int
}

func ConnectDB() error {
	host := config.DBHost
	port := config.DBPort
	user := config.DBUser
	password := config.DBPassword
	dbname := config.DBName

	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s "+
		"dbname=%s sslmode=disable", host, port, user, password, dbname)

	_db, err := sql.Open("postgres", psqlInfo)
	if err != nil {
		return err
	}

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

	fmt.Println("Successfully connected DB")
	db = _db

	return nil
}

func GetActor(id string) (activitypub.Actor, error) {
	var nActor activitypub.Actor

	query := `select type, id, name, preferedusername, inbox, outbox, following, followers, restricted, summary, publickeypem from actor where id=$1`

	rows, err := db.Query(query, id)
	if err != nil {
		return nActor, err
	}

	var publicKeyPem string
	defer rows.Close()
	for rows.Next() {
		if err := rows.Scan(&nActor.Type, &nActor.Id, &nActor.Name, &nActor.PreferredUsername, &nActor.Inbox, &nActor.Outbox, &nActor.Following, &nActor.Followers, &nActor.Restricted, &nActor.Summary, &publicKeyPem); err != nil {
			return nActor, err
		}
	}

	nActor.PublicKey = GetActorPemFromDB(publicKeyPem)
	if nActor.Id != "" && nActor.PublicKey.PublicKeyPem == "" {
		if err := CreatePublicKeyFromPrivate(&nActor, publicKeyPem); err != nil {
			return nActor, err
		}
	}

	return nActor, nil
}

func GetActorByNameFromDB(name string) (activitypub.Actor, error) {
	var nActor activitypub.Actor

	query := `select type, id, name, preferedusername, inbox, outbox, following, followers, restricted, summary, publickeypem from actor where name=$1`

	rows, err := db.Query(query, name)
	if err != nil {
		return nActor, err
	}

	var publicKeyPem string
	defer rows.Close()
	for rows.Next() {
		if err := rows.Scan(&nActor.Type, &nActor.Id, &nActor.Name, &nActor.PreferredUsername, &nActor.Inbox, &nActor.Outbox, &nActor.Following, &nActor.Followers, &nActor.Restricted, &nActor.Summary, &publicKeyPem); err != nil {
			return nActor, err
		}
	}

	if nActor.Id != "" && nActor.PublicKey.PublicKeyPem == "" {
		if err := CreatePublicKeyFromPrivate(&nActor, publicKeyPem); err != nil {
			return nActor, err
		}
	}

	return nActor, nil
}

func CreateNewBoardDB(actor activitypub.Actor) (activitypub.Actor, error) {
	query := `insert into actor (type, id, name, preferedusername, inbox, outbox, following, followers, summary, restricted) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)`

	_, err := db.Exec(query, actor.Type, actor.Id, actor.Name, actor.PreferredUsername, actor.Inbox, actor.Outbox, actor.Following, actor.Followers, actor.Summary, actor.Restricted)

	if err != nil {
		// TODO: board exists error
		return activitypub.Actor{}, err
	} else {
		fmt.Println("board added")

		for _, e := range actor.AuthRequirement {
			query = `insert into actorauth (type, board) values ($1, $2)`

			if _, err := db.Exec(query, e, actor.Name); err != nil {
				return activitypub.Actor{}, err
			}
		}

		var verify Verify

		verify.Identifier = actor.Id
		verify.Code = CreateKey(50)
		verify.Type = "admin"

		CreateVerification(verify)

		verify.Identifier = actor.Id
		verify.Code = CreateKey(50)
		verify.Type = "janitor"

		CreateVerification(verify)

		verify.Identifier = actor.Id
		verify.Code = CreateKey(50)
		verify.Type = "post"

		CreateVerification(verify)

		var nverify Verify
		nverify.Board = actor.Id
		nverify.Identifier = "admin"
		nverify.Type = "admin"
		CreateBoardMod(nverify)

		nverify.Board = actor.Id
		nverify.Identifier = "janitor"
		nverify.Type = "janitor"
		CreateBoardMod(nverify)

		nverify.Board = actor.Id
		nverify.Identifier = "post"
		nverify.Type = "post"
		CreateBoardMod(nverify)

		CreatePem(actor)

		if actor.Name != "main" {
			var nObject activitypub.ObjectBase
			var nActivity activitypub.Activity

			nActor := GetActorFromDB(config.Domain)
			nActivity.AtContext.Context = "https://www.w3.org/ns/activitystreams"
			nActivity.Type = "Follow"
			nActivity.Actor = &nActor
			nActivity.Object = &nObject

			mActor := GetActorFromDB(actor.Id)
			nActivity.Object.Actor = mActor.Id
			nActivity.To = append(nActivity.To, actor.Id)

			response := AcceptFollow(nActivity)
			SetActorFollowingDB(db, response)
			MakeActivityRequest(db, nActivity)
		}

	}

	return actor, nil
}

func GetBoards() ([]activitypub.Actor, error) {
	var board []activitypub.Actor

	query := `select type, id, name, preferedusername, inbox, outbox, following, followers FROM actor`

	rows, err := db.Query(query)
	if err != nil {
		return board, err
	}

	defer rows.Close()
	for rows.Next() {
		var actor = new(activitypub.Actor)

		if err := rows.Scan(&actor.Type, &actor.Id, &actor.Name, &actor.PreferredUsername, &actor.Inbox, &actor.Outbox, &actor.Following, &actor.Followers); err != nil {
			return board, err
		}

		board = append(board, *actor)
	}

	return board, nil
}

func WriteObjectToDB(db *sql.DB, obj ObjectBase) ObjectBase {
	obj.Id = fmt.Sprintf("%s/%s", obj.Actor, CreateUniqueID(db, obj.Actor))
	if len(obj.Attachment) > 0 {
		if obj.Preview.Href != "" {
			obj.Preview.Id = fmt.Sprintf("%s/%s", obj.Actor, CreateUniqueID(db, obj.Actor))
			obj.Preview.Published = time.Now().UTC()
			obj.Preview.Updated = time.Now().UTC()
			obj.Preview.AttributedTo = obj.Id
			WritePreviewToDB(db, *obj.Preview)
		}

		for i, _ := range obj.Attachment {
			obj.Attachment[i].Id = fmt.Sprintf("%s/%s", obj.Actor, CreateUniqueID(db, obj.Actor))
			obj.Attachment[i].Published = time.Now().UTC()
			obj.Attachment[i].Updated = time.Now().UTC()
			obj.Attachment[i].AttributedTo = obj.Id
			WriteAttachmentToDB(db, obj.Attachment[i])
			WriteActivitytoDBWithAttachment(db, obj, obj.Attachment[i], *obj.Preview)
		}

	} else {
		WriteActivitytoDB(db, obj)
	}

	WriteObjectReplyToDB(db, obj)
	WriteWalletToDB(db, obj)

	return obj
}

func WriteObjectUpdatesToDB(db *sql.DB, obj ObjectBase) {
	query := `update activitystream set updated=$1 where id=$2`

	_, e := db.Exec(query, time.Now().UTC().Format(time.RFC3339), obj.Id)

	if e != nil {
		fmt.Println("error inserting updating inreplyto")
		panic(e)
	}

	query = `update cacheactivitystream set updated=$1 where id=$2`

	_, e = db.Exec(query, time.Now().UTC().Format(time.RFC3339), obj.Id)

	if e != nil {
		fmt.Println("error inserting updating cache inreplyto")
		panic(e)
	}
}

func WriteObjectReplyToLocalDB(db *sql.DB, id string, replyto string) {

	query := `select id from replies where id=$1 and inreplyto=$2`

	rows, err := db.Query(query, id, replyto)

	CheckError(err, "error selecting replies local db")

	defer rows.Close()

	var nID string
	rows.Next()
	rows.Scan(&nID)

	if nID == "" {
		query := `insert into replies (id, inreplyto) values ($1, $2)`

		_, err := db.Exec(query, id, replyto)

		CheckError(err, "Could not insert local reply query")
	}

	query = `select inreplyto from replies where id=$1`

	rows, err = db.Query(query, replyto)

	CheckError(err, "Could not query select inreplyto")

	defer rows.Close()

	for rows.Next() {
		var val string
		rows.Scan(&val)
		if val == "" {
			updated := time.Now().UTC().Format(time.RFC3339)
			query := `update activitystream set updated=$1 where id=$2`

			_, err := db.Exec(query, updated, replyto)

			CheckError(err, "error with updating replyto updated at date")
		}
	}
}

func WriteObjectReplyToDB(db *sql.DB, obj ObjectBase) {
	for i, e := range obj.InReplyTo {

		if !CheckIfObjectOP(db, obj.Id) && i == 0 {
			nType := GetObjectTypeDB(db, e.Id)

			if nType == "Archive" {
				UpdateObjectTypeDB(db, obj.Id, "Archive")
			}
		}

		query := `select id from replies where id=$1 and inreplyto=$2`

		rows, err := db.Query(query, obj.Id, e.Id)

		CheckError(err, "error selecting replies db")

		defer rows.Close()

		var id string
		rows.Next()
		rows.Scan(&id)

		if id == "" {
			query := `insert into replies (id, inreplyto) values ($1, $2)`

			_, err := db.Exec(query, obj.Id, e.Id)

			CheckError(err, "error inserting replies db")
		}

		update := true
		for _, e := range obj.Option {
			if e == "sage" || e == "nokosage" {
				update = false
				break
			}
		}

		if update {
			WriteObjectUpdatesToDB(db, e)
		}
	}

	if len(obj.InReplyTo) < 1 {
		query := `select id from replies where id=$1 and inreplyto=$2`

		rows, err := db.Query(query, obj.Id, "")

		CheckError(err, "error selecting replies db")

		defer rows.Close()

		var id string
		rows.Next()
		rows.Scan(&id)

		if id == "" {
			query := `insert into replies (id, inreplyto) values ($1, $2)`

			_, err := db.Exec(query, obj.Id, "")

			CheckError(err, "error inserting replies db")
		}
	}
}

func WriteActorObjectReplyToDB(db *sql.DB, obj ObjectBase) {
	for _, e := range obj.InReplyTo {
		query := `select id from replies where id=$1 and inreplyto=$2`

		rows, err := db.Query(query, obj.Id, e.Id)

		CheckError(err, "error selecting replies db")

		defer rows.Close()

		var id string
		rows.Next()
		rows.Scan(&id)

		if id == "" {
			query := `insert into replies (id, inreplyto) values ($1, $2)`

			_, err := db.Exec(query, obj.Id, e.Id)

			CheckError(err, "error inserting replies db")
		}
	}

	if len(obj.InReplyTo) < 1 {
		query := `select id from replies where id=$1 and inreplyto=$2`

		rows, err := db.Query(query, obj.Id, "")

		CheckError(err, "error selecting replies db")

		defer rows.Close()

		var id string
		rows.Next()
		rows.Scan(&id)

		if id == "" {
			query := `insert into replies (id, inreplyto) values ($1, $2)`

			_, err := db.Exec(query, obj.Id, "")

			CheckError(err, "error inserting replies db")
		}
	}
}

func WriteWalletToDB(db *sql.DB, obj ObjectBase) {
	for _, e := range obj.Option {
		if e == "wallet" {
			for _, e := range obj.Wallet {
				query := `insert into wallet (id, type, address) values ($1, $2, $3)`

				_, err := db.Exec(query, obj.Id, e.Type, e.Address)

				CheckError(err, "error with write wallet query")
			}
			return
		}
	}
}

func WriteActivitytoDB(db *sql.DB, obj ObjectBase) {

	obj.Name = EscapeString(obj.Name)
	obj.Content = EscapeString(obj.Content)
	obj.AttributedTo = EscapeString(obj.AttributedTo)

	query := `insert into activitystream (id, type, name, content, published, updated, attributedto, actor, tripcode, sensitive) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)`

	_, e := db.Exec(query, obj.Id, obj.Type, obj.Name, obj.Content, obj.Published, obj.Updated, obj.AttributedTo, obj.Actor, obj.TripCode, obj.Sensitive)

	if e != nil {
		fmt.Println("error inserting new activity")
		panic(e)
	}
}

func WriteActivitytoDBWithAttachment(db *sql.DB, obj ObjectBase, attachment ObjectBase, preview NestedObjectBase) {

	obj.Name = EscapeString(obj.Name)
	obj.Content = EscapeString(obj.Content)
	obj.AttributedTo = EscapeString(obj.AttributedTo)

	query := `insert into activitystream (id, type, name, content, attachment, preview, published, updated, attributedto, actor, tripcode, sensitive) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)`

	_, e := db.Exec(query, obj.Id, obj.Type, obj.Name, obj.Content, attachment.Id, preview.Id, obj.Published, obj.Updated, obj.AttributedTo, obj.Actor, obj.TripCode, obj.Sensitive)

	if e != nil {
		fmt.Println("error inserting new activity with attachment")
		panic(e)
	}
}

func WriteAttachmentToDB(db *sql.DB, obj ObjectBase) {
	query := `insert into activitystream (id, type, name, href, published, updated, attributedTo, mediatype, size) values ($1, $2, $3, $4, $5, $6, $7, $8, $9)`

	_, e := db.Exec(query, obj.Id, obj.Type, obj.Name, obj.Href, obj.Published, obj.Updated, obj.AttributedTo, obj.MediaType, obj.Size)

	if e != nil {
		fmt.Println("error inserting new attachment")
		panic(e)
	}
}

func WritePreviewToDB(db *sql.DB, obj NestedObjectBase) {
	query := `insert into activitystream (id, type, name, href, published, updated, attributedTo, mediatype, size) values ($1, $2, $3, $4, $5, $6, $7, $8, $9)`

	_, e := db.Exec(query, obj.Id, obj.Type, obj.Name, obj.Href, obj.Published, obj.Updated, obj.AttributedTo, obj.MediaType, obj.Size)

	if e != nil {
		fmt.Println("error inserting new attachment")
		panic(e)
	}
}

func GetActivityFromDB(db *sql.DB, id string) Collection {
	var nColl Collection
	var nActor Actor
	var result []ObjectBase

	nColl.Actor = &nActor

	query := `select  actor, id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from  activitystream where id=$1 order by updated asc`

	rows, err := db.Query(query, id)

	CheckError(err, "error query object from db")

	defer rows.Close()
	for rows.Next() {
		var post ObjectBase
		var actor Actor
		var attachID string
		var previewID string

		err = rows.Scan(&nColl.Actor.Id, &post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.Updated, &post.AttributedTo, &attachID, &previewID, &actor.Id, &post.TripCode, &post.Sensitive)

		CheckError(err, "error scan object into post struct")

		post.Actor = actor.Id

		var postCnt int
		var imgCnt int
		post.Replies, postCnt, imgCnt = GetObjectRepliesDB(db, post)

		post.Replies.TotalItems = postCnt
		post.Replies.TotalImgs = imgCnt

		post.Attachment = GetObjectAttachment(db, attachID)

		post.Preview = GetObjectPreview(db, previewID)

		result = append(result, post)
	}

	nColl.OrderedItems = result

	return nColl
}

func GetObjectFromDBPage(db *sql.DB, id string, page int) Collection {
	var nColl Collection
	var result []ObjectBase

	query := `select count (x.id) over(), x.id, x.name, x.content, x.type, x.published, x.updated, x.attributedto, x.attachment, x.preview, x.actor, x.tripcode, x.sensitive from (select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where actor=$1 and id in (select id from replies where inreplyto='') and type='Note' union select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type='Note' union select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from cacheactivitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type='Note') as x order by x.updated desc limit 15 offset $2`

	rows, err := db.Query(query, id, page*15)

	CheckError(err, "error query object from db")

	var count int
	defer rows.Close()
	for rows.Next() {
		var post ObjectBase
		var actor Actor
		var attachID string
		var previewID string

		err = rows.Scan(&count, &post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.Updated, &post.AttributedTo, &attachID, &previewID, &actor.Id, &post.TripCode, &post.Sensitive)

		CheckError(err, "error scan object into post struct")

		post.Actor = actor.Id

		var postCnt int
		var imgCnt int
		post.Replies, postCnt, imgCnt = GetObjectRepliesDBLimit(db, post, 5)

		post.Replies.TotalItems = postCnt
		post.Replies.TotalImgs = imgCnt

		post.Attachment = GetObjectAttachment(db, attachID)

		post.Preview = GetObjectPreview(db, previewID)

		result = append(result, post)
	}

	nColl.TotalItems = count
	nColl.OrderedItems = result

	return nColl
}

func GetActorObjectCollectionFromDB(db *sql.DB, actorId string) Collection {
	var nColl Collection
	var result []ObjectBase

	query := `select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where actor=$1 and id in (select id from replies where inreplyto='') and type='Note' order by updated desc`

	rows, err := db.Query(query, actorId)

	CheckError(err, "error query object from db")

	defer rows.Close()
	for rows.Next() {
		var post ObjectBase
		var actor Actor
		var attachID string
		var previewID string

		err = rows.Scan(&post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.Updated, &post.AttributedTo, &attachID, &previewID, &actor.Id, &post.TripCode, &post.Sensitive)

		CheckError(err, "error scan object into post struct")

		post.Actor = actor.Id

		var postCnt int
		var imgCnt int
		post.Replies, postCnt, imgCnt = GetObjectRepliesDB(db, post)

		post.Replies.TotalItems = postCnt
		post.Replies.TotalImgs = imgCnt

		post.Attachment = GetObjectAttachment(db, attachID)

		post.Preview = GetObjectPreview(db, previewID)

		result = append(result, post)
	}

	nColl.OrderedItems = result

	return nColl
}

func GetObjectFromDB(db *sql.DB, id string) Collection {
	var nColl Collection
	var result []ObjectBase

	query := `select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where id=$1 union select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from cacheactivitystream where id=$1 order by updated desc`

	rows, err := db.Query(query, id)

	CheckError(err, "error query object from db")

	defer rows.Close()
	for rows.Next() {
		var post ObjectBase
		var actor Actor
		var attachID string
		var previewID string

		err = rows.Scan(&post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.Updated, &post.AttributedTo, &attachID, &previewID, &actor.Id, &post.TripCode, &post.Sensitive)

		CheckError(err, "error scan object into post struct")

		post.Actor = actor.Id

		var postCnt int
		var imgCnt int
		post.Replies, postCnt, imgCnt = GetObjectRepliesDB(db, post)

		post.Replies.TotalItems = postCnt
		post.Replies.TotalImgs = imgCnt

		post.Attachment = GetObjectAttachment(db, attachID)

		post.Preview = GetObjectPreview(db, previewID)

		result = append(result, post)
	}

	nColl.OrderedItems = result

	return nColl
}

func GetObjectFromDBFromID(db *sql.DB, id string) Collection {
	var nColl Collection
	var result []ObjectBase

	query := `select x.id, x.name, x.content, x.type, x.published, x.updated, x.attributedto, x.attachment, x.preview, x.actor, x.tripcode, x.sensitive from (select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where id like $1 and type='Note' union select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from cacheactivitystream where id like $1 and type='Note') as x order by x.updated`

	re := regexp.MustCompile(`f(\w+)\-`)
	match := re.FindStringSubmatch(id)

	if len(match) > 0 {
		re := regexp.MustCompile(`(.+)\-`)
		id = re.ReplaceAllString(id, "")
		id = "%" + match[1] + "/" + id
	}

	rows, err := db.Query(query, id)

	CheckError(err, "error query object from db")

	defer rows.Close()
	for rows.Next() {
		var post ObjectBase
		var actor Actor
		var attachID string
		var previewID string

		err = rows.Scan(&post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.Updated, &post.AttributedTo, &attachID, &previewID, &actor.Id, &post.TripCode, &post.Sensitive)

		CheckError(err, "error scan object into post struct")

		post.Actor = actor.Id

		var postCnt int
		var imgCnt int
		post.Replies, postCnt, imgCnt = GetObjectRepliesDB(db, post)

		post.Replies.TotalItems = postCnt
		post.Replies.TotalImgs = imgCnt

		post.Attachment = GetObjectAttachment(db, attachID)

		post.Preview = GetObjectPreview(db, previewID)

		result = append(result, post)
	}

	nColl.OrderedItems = result

	return nColl
}

func GetObjectFromDBCatalog(db *sql.DB, id string) Collection {
	var nColl Collection
	var result []ObjectBase

	query := `select x.id, x.name, x.content, x.type, x.published, x.updated, x.attributedto, x.attachment, x.preview, x.actor, x.tripcode, x.sensitive from (select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where actor=$1 and id in (select id from replies where inreplyto='') and type='Note' union select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type='Note' union select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from cacheactivitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type='Note') as x order by x.updated desc limit 165`

	rows, err := db.Query(query, id)

	CheckError(err, "error query object from db")

	defer rows.Close()
	for rows.Next() {
		var post ObjectBase
		var actor Actor
		var attachID string
		var previewID string

		err = rows.Scan(&post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.Updated, &post.AttributedTo, &attachID, &previewID, &actor.Id, &post.TripCode, &post.Sensitive)

		CheckError(err, "error scan object into post struct")

		post.Actor = actor.Id

		var replies CollectionBase

		post.Replies = &replies

		post.Replies.TotalItems, post.Replies.TotalImgs = GetObjectRepliesCount(db, post)

		post.Attachment = GetObjectAttachment(db, attachID)

		post.Preview = GetObjectPreview(db, previewID)

		result = append(result, post)
	}

	nColl.OrderedItems = result

	return nColl
}

func GetObjectByIDFromDB(db *sql.DB, postID string) Collection {
	var nColl Collection
	var result []ObjectBase

	query := `select x.id, x.name, x.content, x.type, x.published, x.updated, x.attributedto, x.attachment, x.preview, x.actor, x.tripcode, x.sensitive from (select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where id=$1 and (type='Note' or type='Archive') union select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from cacheactivitystream where id=$1 and (type='Note' or type='Archive')) as x`

	rows, err := db.Query(query, postID)

	CheckError(err, "error query object from db")

	defer rows.Close()
	for rows.Next() {
		var post ObjectBase
		var actor Actor
		var attachID string
		var previewID string

		err = rows.Scan(&post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.Updated, &post.AttributedTo, &attachID, &previewID, &actor.Id, &post.TripCode, &post.Sensitive)

		CheckError(err, "error scan object into post struct")

		actor = GetActorFromDB(db, actor.Id)

		post.Actor = actor.Id

		nColl.Actor = &actor

		var postCnt int
		var imgCnt int
		post.Replies, postCnt, imgCnt = GetObjectRepliesDB(db, post)

		post.Replies.TotalItems = postCnt
		post.Replies.TotalImgs = imgCnt

		post.Attachment = GetObjectAttachment(db, attachID)

		post.Preview = GetObjectPreview(db, previewID)

		result = append(result, post)
	}

	nColl.OrderedItems = result

	return nColl
}

func GetInReplyToDB(db *sql.DB, parent ObjectBase) []ObjectBase {
	var result []ObjectBase

	query := `select inreplyto from replies where id =$1`

	rows, err := db.Query(query, parent.Id)

	CheckError(err, "error with inreplyto db query")

	defer rows.Close()
	for rows.Next() {
		var post ObjectBase

		rows.Scan(&post.Id)

		result = append(result, post)
	}

	return result
}

func GetObjectRepliesDBLimit(db *sql.DB, parent ObjectBase, limit int) (*CollectionBase, int, int) {

	var nColl CollectionBase
	var result []ObjectBase

	query := `select count(x.id) over(), sum(case when RTRIM(x.attachment) = '' then 0 else 1 end) over(), x.id, x.name, x.content, x.type, x.published, x.attributedto, x.attachment, x.preview, x.actor, x.tripcode, x.sensitive from (select * from activitystream where id in (select id from replies where inreplyto=$1) and type='Note' union select * from cacheactivitystream where id in (select id from replies where inreplyto=$1) and type='Note') as x order by x.published desc limit $2`

	rows, err := db.Query(query, parent.Id, limit)

	CheckError(err, "error with replies db query")

	var postCount int
	var attachCount int

	defer rows.Close()
	for rows.Next() {
		var post ObjectBase
		var actor Actor
		var attachID string
		var previewID string

		post.InReplyTo = append(post.InReplyTo, parent)

		err = rows.Scan(&postCount, &attachCount, &post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.AttributedTo, &attachID, &previewID, &actor.Id, &post.TripCode, &post.Sensitive)

		CheckError(err, "error with replies db scan")

		post.Actor = actor.Id

		var postCnt int
		var imgCnt int

		post.Replies, postCnt, imgCnt = GetObjectRepliesRepliesDB(db, post)

		post.Replies.TotalItems = postCnt
		post.Replies.TotalImgs = imgCnt

		post.Attachment = GetObjectAttachment(db, attachID)

		post.Preview = GetObjectPreview(db, previewID)

		result = append(result, post)
	}

	nColl.OrderedItems = result

	sort.Sort(ObjectBaseSortAsc(nColl.OrderedItems))

	return &nColl, postCount, attachCount
}

func GetObjectRepliesDB(db *sql.DB, parent ObjectBase) (*CollectionBase, int, int) {

	var nColl CollectionBase
	var result []ObjectBase

	query := `select count(x.id) over(), sum(case when RTRIM(x.attachment) = '' then 0 else 1 end) over(), x.id, x.name, x.content, x.type, x.published, x.attributedto, x.attachment, x.preview, x.actor, x.tripcode, x.sensitive from (select * from activitystream where id in (select id from replies where inreplyto=$1) and (type='Note' or type='Archive') union select * from cacheactivitystream where id in (select id from replies where inreplyto=$1) and (type='Note' or type='Archive')) as x order by x.published asc`

	rows, err := db.Query(query, parent.Id)

	CheckError(err, "error with replies db query")

	var postCount int
	var attachCount int

	defer rows.Close()
	for rows.Next() {
		var post ObjectBase
		var actor Actor
		var attachID string
		var previewID string

		post.InReplyTo = append(post.InReplyTo, parent)

		err = rows.Scan(&postCount, &attachCount, &post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.AttributedTo, &attachID, &previewID, &actor.Id, &post.TripCode, &post.Sensitive)

		CheckError(err, "error with replies db scan")

		post.Actor = actor.Id

		var postCnt int
		var imgCnt int

		post.Replies, postCnt, imgCnt = GetObjectRepliesRepliesDB(db, post)

		post.Replies.TotalItems = postCnt
		post.Replies.TotalImgs = imgCnt

		post.Attachment = GetObjectAttachment(db, attachID)

		post.Preview = GetObjectPreview(db, previewID)

		result = append(result, post)
	}

	nColl.OrderedItems = result

	return &nColl, postCount, attachCount
}

func GetObjectRepliesReplies(db *sql.DB, parent ObjectBase) (*CollectionBase, int, int) {

	var nColl CollectionBase
	var result []ObjectBase

	query := `select id, name, content, type, published, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where id in (select id from replies where inreplyto=$1) and (type='Note' or type='Archive') order by updated asc`

	rows, err := db.Query(query, parent.Id)

	CheckError(err, "error with replies replies db query")

	defer rows.Close()
	for rows.Next() {
		var post ObjectBase
		var actor Actor
		var attachID string
		var previewID string

		post.InReplyTo = append(post.InReplyTo, parent)

		err = rows.Scan(&post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.AttributedTo, &attachID, &previewID, &actor.Id, &post.TripCode, &post.Sensitive)

		CheckError(err, "error with replies replies db scan")

		post.Actor = actor.Id

		post.Attachment = GetObjectAttachment(db, attachID)

		post.Preview = GetObjectPreview(db, previewID)

		result = append(result, post)
	}

	nColl.OrderedItems = result

	return &nColl, 0, 0
}

func GetObjectRepliesRepliesDB(db *sql.DB, parent ObjectBase) (*CollectionBase, int, int) {

	var nColl CollectionBase
	var result []ObjectBase

	query := `select count(x.id) over(), sum(case when RTRIM(x.attachment) = '' then 0 else 1 end) over(), x.id, x.name, x.content, x.type, x.published, x.attributedto, x.attachment, x.preview, x.actor, x.tripcode, x.sensitive from (select * from activitystream where id in (select id from replies where inreplyto=$1) and type='Note' union select * from cacheactivitystream where id in (select id from replies where inreplyto=$1) and type='Note') as x order by x.published asc`

	rows, err := db.Query(query, parent.Id)

	CheckError(err, "error with replies replies db query")

	var postCount int
	var attachCount int
	defer rows.Close()
	for rows.Next() {
		var post ObjectBase
		var actor Actor
		var attachID string
		var previewID string

		post.InReplyTo = append(post.InReplyTo, parent)

		err = rows.Scan(&postCount, &attachCount, &post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.AttributedTo, &attachID, &previewID, &actor.Id, &post.TripCode, &post.Sensitive)

		CheckError(err, "error with replies replies db scan")

		post.Actor = actor.Id

		post.Attachment = GetObjectAttachment(db, attachID)

		post.Preview = GetObjectPreview(db, previewID)

		result = append(result, post)
	}

	nColl.OrderedItems = result

	return &nColl, postCount, attachCount
}

func CheckIfObjectOP(id string) (bool, error) {
	var count int

	query := `select count(id) from replies where inreplyto='' and id=$1 `

	rows, err := db.Query(query, id)
	if err != nil {
		return false, err
	}
	defer rows.Close()

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

	if count > 0 {
		return true, nil
	}

	return false, nil
}

func GetObjectRepliesCount(parent activitypub.ObjectBase) (int, int, error) {
	var countId int
	var countImg int

	query := `select count(x.id) over(), sum(case when RTRIM(x.attachment) = '' then 0 else 1 end) over() from (select id, attachment from activitystream where id in (select id from replies where inreplyto=$1) and type='Note' union select id, attachment from cacheactivitystream where id in (select id from replies where inreplyto=$1) and type='Note') as x`

	rows, err := db.Query(query, parent.Id)
	if err != nil {
		return 0, 0, err
	}

	defer rows.Close()

	rows.Next()
	err = rows.Scan(&countId, &countImg)

	return countId, countImg, err
}

func GetObjectAttachment(id string) ([]activitypub.ObjectBase, error) {
	var attachments []activitypub.ObjectBase

	query := `select x.id, x.type, x.name, x.href, x.mediatype, x.size, x.published from (select id, type, name, href, mediatype, size, published from activitystream where id=$1 union select id, type, name, href, mediatype, size, published from cacheactivitystream where id=$1) as x`

	rows, err := db.Query(query, id)
	if err != nil {
		return attachments, err
	}

	defer rows.Close()
	for rows.Next() {
		var attachment = new(activitypub.ObjectBase)

		if err := rows.Scan(&attachment.Id, &attachment.Type, &attachment.Name, &attachment.Href, &attachment.MediaType, &attachment.Size, &attachment.Published); err != nil {
			return attachments, err
		}

		attachments = append(attachments, *attachment)
	}

	return attachments, nil
}

func GetObjectPreview(id string) (*activitypub.NestedObjectBase, error) {
	var preview activitypub.NestedObjectBase

	query := `select x.id, x.type, x.name, x.href, x.mediatype, x.size, x.published from (select id, type, name, href, mediatype, size, published from activitystream where id=$1 union select id, type, name, href, mediatype, size, published from cacheactivitystream where id=$1) as x`

	rows, err := db.Query(query, id)
	if err != nil {
		return nil, err
	}

	defer rows.Close()
	for rows.Next() {
		if err := rows.Scan(&preview.Id, &preview.Type, &preview.Name, &preview.Href, &preview.MediaType, &preview.Size, &preview.Published); err != nil {
			return nil, err
		}
	}

	return &preview, nil
}

func GetObjectPostsTotalDB(actor activitypub.Actor) (int, error) {
	count := 0
	query := `select count(id) from activitystream where actor=$1 and id in (select id from replies where inreplyto='' and type='Note')`

	rows, err := db.Query(query, actor.Id)
	if err != nil {
		return count, err
	}

	defer rows.Close()
	for rows.Next() {
		if err := rows.Scan(&count); err != nil {
			return count, err
		}
	}

	return count, nil
}

func GetObjectImgsTotalDB(actor activitypub.Actor) (int, error) {
	count := 0
	query := `select count(attachment) from activitystream where actor=$1 and id in (select id from replies where inreplyto='' and type='Note' )`

	rows, err := db.Query(query, actor.Id)
	if err != nil {
		return count, err
	}
	defer rows.Close()

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

	return count, nil
}

func DeletePreviewFromFile(id string) error {
	query := `select href from activitystream where id in (select preview from activitystream where id=$1)`

	rows, err := db.Query(query, id)
	if err != nil {
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var href string

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

		href = strings.Replace(href, config.Domain+"/", "", 1)

		if href != "static/notfound.png" {
			_, err = os.Stat(href)
			if err == nil {
				return os.Remove(href)
			}
			return err
		}
	}

	return nil
}

func RemovePreviewFromFile(id string) error {
	query := `select href from activitystream where id in (select preview from activitystream where id=$1)`
	rows, err := db.Query(query, id)
	if err != nil {
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var href string

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

		href = strings.Replace(href, config.Domain+"/", "", 1)

		if href != "static/notfound.png" {
			_, err = os.Stat(href)
			if err == nil {
				return os.Remove(href)
			}
			return err
		}
	}

	return DeletePreviewFromDB(id)
}

func DeleteAttachmentFromFile(id string) error {
	query := `select href from activitystream where id in (select attachment from activitystream where id=$1)`

	rows, err := db.Query(query, id)
	if err != nil {
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var href string

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

		href = strings.Replace(href, config.Domain+"/", "", 1)

		if href != "static/notfound.png" {
			_, err = os.Stat(href)
			if err == nil {
				os.Remove(href)
			}
			return err
		}
	}

}

func TombstonePreviewRepliesFromDB(db *sql.DB, id string) error {
	query := `select id from activitystream where id in (select id from replies where inreplyto=$1)`

	rows, err := db.Query(query, id)
	if err != nil {
		return err
	}

	defer rows.Close()
	for rows.Next() {
		var attachment string

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

		if err := DeletePreviewFromFile(attachment); err != nil {
			return err
		}

		if err := TombstonePreviewFromDB(attachment); err != nil {
			return err
		}
	}

	return nil
}

func TombstoneAttachmentRepliesFromDB(id string) error {
	query := `select id from activitystream where id in (select id from replies where inreplyto=$1)`

	rows, err := db.Query(query, id)
	if err != nil {
		return err
	}

	defer rows.Close()
	for rows.Next() {
		var attachment string

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

		if err := DeleteAttachmentFromFile(attachment); err != nil {
			return err
		}

		if err := TombstoneAttachmentFromDB(attachment); err != nil {
			return err
		}
	}

	return nil
}

func TombstoneAttachmentFromDB(id string) error {
	datetime := time.Now().UTC().Format(time.RFC3339)

	query := `update activitystream set type='Tombstone', mediatype='image/png', href=$1, name='', content='', attributedto='deleted', deleted=$2 where id in (select attachment from activitystream where id=$3)`

	if _, err := db.Exec(query, config.Domain+"/static/notfound.png", datetime, id); err != nil {
		return err
	}

	query = `update cacheactivitystream set type='Tombstone', mediatype='image/png', href=$1, name='', content='', attributedto='deleted', deleted=$2 where id in (select attachment from cacheactivitystream where id=$3)`

	_, err := db.Exec(query, config.Domain+"/static/notfound.png", datetime, id)
	return err
}

func DeleteAttachmentFromDB(db *sql.DB, id string) error {
	query := `delete from activitystream where id in (select attachment from activitystream where id=$1)`

	if _, err := db.Exec(query, id); err != nil {
		return err
	}

	query = `delete from cacheactivitystream where id in (select attachment from cacheactivitystream where id=$1)`

	_, err := db.Exec(query, id)
	return err
}

func TombstonePreviewFromDB(id string) error {
	datetime := time.Now().UTC().Format(time.RFC3339)

	query := `update activitystream set type='Tombstone', mediatype='image/png', href=$1, name='', content='', attributedto='deleted', deleted=$2 where id in (select preview from activitystream where id=$3)`

	if _, err := db.Exec(query, config.Domain+"/static/notfound.png", datetime, id); err != nil {
		return err
	}

	query = `update cacheactivitystream set type='Tombstone', mediatype='image/png', href=$1, name='', content='', attributedto='deleted', deleted=$2 where id in (select preview from cacheactivitystream where id=$3)`

	_, err := db.Exec(query, config.Domain+"/static/notfound.png", datetime, id)
	return err
}

func DeletePreviewFromDB(id string) error {
	query := `delete from activitystream  where id=$1`

	if _, err := db.Exec(query, id); err != nil {
		return err
	}

	query = `delete from cacheactivitystream where id in (select preview from cacheactivitystream where id=$1)`

	_, err := db.Exec(query, id)
	return err
}

func DeleteObjectRepliedTo(id string) error {
	query := `delete from replies where id=$1`
	_, err := db.Exec(query, id)
	return err
}

func TombstoneObjectFromDB(id string) error {
	datetime := time.Now().UTC().Format(time.RFC3339)
	query := `update activitystream set type='Tombstone', name='', content='', attributedto='deleted', tripcode='', deleted=$1 where id=$2`

	if _, err := db.Exec(query, datetime, id); err != nil {
		return err
	}

	query = `update cacheactivitystream set type='Tombstone', name='', content='', attributedto='deleted', tripcode='',  deleted=$1 where id=$2`

	_, err := db.Exec(query, datetime, id)
	return err
}

func DeleteObjectFromDB(id string) error {
	var query = `delete from activitystream where id=$1`

	if _, err := db.Exec(query, id); err != nil {
		return err
	}

	query = `delete from cacheactivitystream where id=$1`

	_, err := db.Exec(query, id)
	return err
}

func DeleteObjectsInReplyTo(id string) error {
	query := `delete from replies where id in (select id from replies where inreplyto=$1)`

	_, err := db.Exec(query, id)
	return err
}

func TombstoneObjectRepliesFromDB(id string) error {
	datetime := time.Now().UTC().Format(time.RFC3339)

	query := `update activitystream set type='Tombstone', name='', content='', attributedto='deleted', tripcode='', deleted=$1 where id in (select id from replies where inreplyto=$2)`

	if _, err := db.Exec(query, datetime, id); err != nil {
		return err
	}

	query = `update cacheactivitystream set type='Tombstone', name='', content='', attributedto='deleted', tripcode='', deleted=$1 where id in (select id from replies where inreplyto=$2)`

	_, err := db.Exec(query, datetime, id)
	return err
}

func SetAttachmentFromDB(id string, _type string) error {
	datetime := time.Now().UTC().Format(time.RFC3339)

	query := `update activitystream set type=$1, deleted=$2 where id in (select attachment from activitystream where id=$3)`

	if _, err := db.Exec(query, _type, datetime, id); err != nil {
		return err
	}

	query = `update cacheactivitystream set type=$1, deleted=$2 where id in (select attachment from cacheactivitystream  where id=$3)`

	_, err := db.Exec(query, _type, datetime, id)
	return err
}

func SetAttachmentRepliesFromDB(id string, _type string) error {
	datetime := time.Now().UTC().Format(time.RFC3339)

	query := `update activitystream set type=$1, deleted=$2 where id in (select attachment from activitystream where id in (select id from replies where inreplyto=$3))`

	if _, err := db.Exec(query, _type, datetime, id); err != nil {
		return err
	}

	query = `update cacheactivitystream set type=$1, deleted=$2 where id in (select attachment from cacheactivitystream where id in (select id from replies where inreplyto=$3))`

	_, err := db.Exec(query, _type, datetime, id)
	return err
}

func SetPreviewFromDB(db *sql.DB, id string, _type string) error {
	datetime := time.Now().UTC().Format(time.RFC3339)

	query := `update activitystream set type=$1, deleted=$2 where id in (select preview from activitystream where id=$3)`

	if _, err := db.Exec(query, _type, datetime, id); err != nil {
		return err
	}

	query = `update cacheactivitystream set type=$1, deleted=$2 where id in (select preview from cacheactivitystream where id=$3)`

	_, err := db.Exec(query, _type, datetime, id)
	return err
}

func SetPreviewRepliesFromDB(id string, _type string) error {
	datetime := time.Now().UTC().Format(time.RFC3339)

	query := `update activitystream set type=$1, deleted=$2 where id in (select preview from activitystream where id in (select id from replies where inreplyto=$3))`

	if _, err := db.Exec(query, _type, datetime, id); err != nil {
		return err
	}

	query = `update cacheactivitystream set type=$1, deleted=$2 where id in (select preview from cacheactivitystream where id in (select id from replies where inreplyto=$3))`

	_, err := db.Exec(query, _type, datetime, id)
	return err
}

func SetObjectFromDB(id string, _type string) error {
	datetime := time.Now().UTC().Format(time.RFC3339)

	query := `update activitystream set type=$1, deleted=$2 where id=$3`

	if _, err := db.Exec(query, _type, datetime, id); err != nil {
		return err
	}

	query = `update cacheactivitystream set type=$1, deleted=$2 where id=$3`

	_, err := db.Exec(query, _type, datetime, id)
	return err
}

func SetObjectRepliesFromDB(id string, _type string) error {
	datetime := time.Now().UTC().Format(time.RFC3339)

	var query = `update activitystream set type=$1, deleted=$2 where id in (select id from replies where inreplyto=$3)`
	if _, err := db.Exec(query, _type, datetime, id); err != nil {
		return err
	}

	query = `update cacheactivitystream set type=$1, deleted=$2 where id in (select id from replies where inreplyto=$3)`
	_, err := db.Exec(query, _type, datetime, id)
	return err
}

func SetObject(id string, _type string) error {
	if err := SetAttachmentFromDB(id, _type); err != nil {
		return err
	}

	if err := SetPreviewFromDB(id, _type); err != nil {
		return err
	}

	return SetObjectFromDB(id, _type)
}

func SetObjectAndReplies(id string, _type string) error {
	if err := SetAttachmentFromDB(id, _type); err != nil {
		return err
	}

	if err := SetPreviewFromDB(id, _type); err != nil {
		return err
	}

	if err := SetObjectRepliesFromDB(id, _type); err != nil {
		return err
	}

	if err := SetAttachmentRepliesFromDB(id, _type); err != nil {
		return err
	}

	if err := SetPreviewRepliesFromDB(id, _type); err != nil {
		return err
	}

	return SetObjectFromDB(id, _type)
}

func DeleteObject(id string) error {
	if err := DeleteReportActivity(id); err != nil {
		return err
	}

	if err := DeleteAttachmentFromFile(id); err != nil {
		return err
	}

	if err := DeleteAttachmentFromDB(id); err != nil {
		return err
	}

	if err := DeletePreviewFromFile(id); err != nil {
		return err
	}

	if err := DeletePreviewFromDB(id); err != nil {
		return err
	}

	if err := DeleteObjectFromDB(id); err != nil {
		return err
	}

	return DeleteObjectRepliedTo(id)
}

func TombstoneObject(id string) error {
	if err := DeleteReportActivity(id); err != nil {
		return err
	}

	if err := DeleteAttachmentFromFile(id); err != nil {
		return err
	}

	if err := TombstoneAttachmentFromDB(id); err != nil {
		return err
	}

	if err := DeletePreviewFromFile(id); err != nil {
		return err
	}

	if err := TombstonePreviewFromDB(id); err != nil {
		return err
	}

	return TombstoneObjectFromDB(id)
}

func TombstoneObjectAndReplies(id string) error {
	if err := DeleteReportActivity(id); err != nil {
		return err
	}

	if err := DeleteAttachmentFromFile(id); err != nil {
		return err
	}

	if err := TombstoneAttachmentFromDB(id); err != nil {
		return err
	}

	if err := DeletePreviewFromFile(id); err != nil {
		return err
	}

	if err := TombstonePreviewFromDB(id); err != nil {
		return err
	}

	if err := TombstoneObjectRepliesFromDB(id); err != nil {
		return err
	}

	if err := TombstoneAttachmentRepliesFromDB(id); err != nil {
		return err
	}

	if err := TombstonePreviewRepliesFromDB(id); err != nil {
		return err
	}

	return TombstoneObjectFromDB(id)
}

func GetRandomCaptcha() (string, error) {
	var verify string

	query := `select identifier from verification where type='captcha' order by random() limit 1`

	rows, err := db.Query(query)
	if err != nil {
		return verify, err
	}
	defer rows.Close()

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

	return verify, nil
}

func GetCaptchaTotal() (int, error) {
	query := `select count(*) from verification where type='captcha'`

	rows, err := db.Query(query)
	if err != nil {
		return 0, err
	}

	defer rows.Close()

	var count int
	for rows.Next() {
		if err := rows.Scan(&count); err != nil {
			return count, err
		}
	}

	return count, nil
}

func GetCaptchaCodeDB(verify string) (string, error) {
	query := `select code from verification where identifier=$1 limit 1`

	rows, err := db.Query(query, verify)
	if err != nil {
		return "", err
	}
	defer rows.Close()

	var code string

	rows.Next()
	if err := rows.Scan(&code); err != nil {
		fmt.Println("Could not get verification captcha")
	}

	return code, nil
}

func GetActorAuth(actor string) ([]string, error) {
	var auth []string

	query := `select type from actorauth where board=$1`

	rows, err := db.Query(query, actor)
	if err != nil {
		return auth, err
	}
	defer rows.Close()

	for rows.Next() {
		var e string
		if err := rows.Scan(&e); err != nil {
			return auth, err
		}

		auth = append(auth, e)
	}

	return auth, nil
}

func DeleteCaptchaCodeDB(verify string) error {
	query := `delete from verification where identifier=$1`

	_, err := db.Exec(query, verify)
	if err != nil {
		return err
	}

	return os.Remove("./" + verify)
}

func EscapeString(text string) string {
	// TODO: not enough

	text = strings.Replace(text, "<", "&lt;", -1)
	return text
}

func GetActorReportedTotal(id string) (int, error) {
	query := `select count(id) from reported where board=$1`

	rows, err := db.Query(query, id)
	if err != nil {
		return 0, err
	}

	defer rows.Close()

	var count int
	for rows.Next() {
		rows.Scan(&count)
	}

	return count, nil
}

func GetActorReportedDB(id string) ([]activitypub.ObjectBase, error) {
	var nObj []activitypub.ObjectBase

	query := `select id, count, reason from reported where board=$1`

	rows, err := db.Query(query, id)
	if err != nil {
		return nObj, err
	}

	defer rows.Close()

	for rows.Next() {
		var obj activitypub.ObjectBase

		rows.Scan(&obj.Id, &obj.Size, &obj.Content)

		nObj = append(nObj, obj)
	}

	return nObj, nil
}

func GetActorPemFromDB(pemID string) (activitypub.PublicKeyPem, error) {
	var pem activitypub.PublicKeyPem

	query := `select id, owner, file from publickeypem where id=$1`

	rows, err := db.Query(query, pemID)
	if err != nil {
		return pem, err
	}

	defer rows.Close()

	rows.Next()
	rows.Scan(&pem.Id, &pem.Owner, &pem.PublicKeyPem)
	f, err := os.ReadFile(pem.PublicKeyPem)
	if err != nil {
		return pem, err
	}

	pem.PublicKeyPem = strings.ReplaceAll(string(f), "\r\n", `\n`)

	return pem, nil
}

func GetActorPemFileFromDB(db *sql.DB, pemID string) (string, error) {
	query := `select file from publickeypem where id=$1`
	rows, err := db.Query(query, pemID)
	if err != nil {
		return "", err
	}

	defer rows.Close()

	var file string
	rows.Next()
	rows.Scan(&file)

	return file, nil
}

func MarkObjectSensitive(db *sql.DB, id string, sensitive bool) error {
	var query = `update activitystream set sensitive=$1 where id=$2`
	if _, err := db.Exec(query, sensitive, id); err != nil {
		return err
	}

	query = `update cacheactivitystream set sensitive=$1 where id=$2`
	_, err := db.Exec(query, sensitive, id)
	return err
}

//if limit less than 1 return all news items
func GetNewsFromDB(limit int) ([]NewsItem, error) {
	var news []NewsItem

	var query string
	if limit > 0 {
		query = `select title, content, time from newsItem order by time desc limit $1`
	} else {
		query = `select title, content, time from newsItem order by time desc`
	}

	var rows *sql.Rows
	var err error
	if limit > 0 {
		rows, err = db.Query(query, limit)
	} else {
		rows, err = db.Query(query)
	}

	if err != nil {
		return news, nil
	}

	defer rows.Close()
	for rows.Next() {
		n := NewsItem{}
		var content string
		if err := rows.Scan(&n.Title, &content, &n.Time); err != nil {
			return news, err
		}

		content = strings.ReplaceAll(content, "\n", "<br>")
		n.Content = template.HTML(content)

		news = append(news, n)
	}

	return news, nil
}

func getNewsItemFromDB(timestamp int) (NewsItem, error) {
	var news NewsItem
	var content string
	query := `select title, content, time from newsItem where time=$1 limit 1`

	rows, err := db.Query(query, timestamp)
	if err != nil {
		return news, err
	}

	defer rows.Close()

	rows.Next()
	if err := rows.Scan(&news.Title, &content, &news.Time); err != nil {
		return news, err
	}

	content = strings.ReplaceAll(content, "\n", "<br>")
	news.Content = template.HTML(content)

	return news, nil
}

func deleteNewsItemFromDB(timestamp int) error {
	query := `delete from newsItem where time=$1`
	_, err := db.Exec(query, timestamp)
	return err
}

func WriteNewsToDB(news NewsItem) error {
	query := `insert into newsItem (title, content, time) values ($1, $2, $3)`

	_, err := db.Exec(query, news.Title, news.Content, time.Now().Unix())
	return err
}

func WriteRegexBlacklistDB(regex string) error {
	query := `select from postblacklist where regex=$1`

	rows, err := db.Query(query, regex)
	if err != nil {
		return err
	}
	defer rows.Close()

	var re string
	rows.Next()
	rows.Scan(&re)

	if re != "" {
		return nil
	}

	query = `insert into postblacklist (regex) values ($1)`

	_, err = db.Exec(query, regex)
	return err
}

func GetRegexBlacklistDB() ([]PostBlacklist, error) {
	query := `select id, regex from postblacklist`

	rows, err := db.Query(query)
	if err != nil {
		return err
	}

	var List []PostBlacklist

	defer rows.Close()
	for rows.Next() {
		var temp PostBlacklist
		rows.Scan(&temp.Id, &temp.Regex)

		List = append(List, temp)
	}

	return List, nil
}

func DeleteRegexBlacklistDB(id int) error {
	query := `delete from postblacklist where id=$1`

	_, err := db.Exec(query, id)
	return err
}

func GetActorAutoSubscribeDB(id string) (bool, error) {
	query := `select autosubscribe from actor where id=$1`

	rows, err := db.Query(query, id)
	if err != nil {
		return false, err
	}

	var subscribed bool
	defer rows.Close()
	rows.Next()
	rows.Scan(&subscribed)

	return subscribed
}

func SetActorAutoSubscribeDB(id string) error {
	current, err := GetActorAutoSubscribeDB(id)
	if err != nil {
		return err
	}

	query := `update actor set autosubscribe=$1 where id=$2`

	_, err = db.Exec(query, !current, id)
	return err
}

func AddInstanceToInactiveDB(instance string) error {
	query := `select timestamp from inactive where instance=$1`

	rows, err := db.Query(query, instance)
	if err != nil {
		return err
	}

	var timeStamp string
	defer rows.Close()
	rows.Next()
	rows.Scan(&timeStamp)

	if timeStamp == "" {
		query := `insert into inactive (instance, timestamp) values ($1, $2)`

		_, err := db.Exec(query, instance, time.Now().UTC().Format(time.RFC3339))
		return err
	}

	if !IsInactiveTimestamp(timeStamp) {
		return nil
	}

	query = `delete from following where following like $1`
	if _, err := db.Exec(query, "%"+instance+"%"); err != nil {
		return err
	}

	query = `delete from follower where follower like $1`
	if _, err = db.Exec(query, "%"+instance+"%"); err != nil {
		return err
	}

	return DeleteInstanceFromInactiveDB(instance)
}

func DeleteInstanceFromInactiveDB(instance string) error {
	query := `delete from inactive where instance=$1`

	_, err := db.Exec(query, instance)
	return err
}

func IsInactiveTimestamp(timeStamp string) bool {
	stamp, _ := time.Parse(time.RFC3339, timeStamp)
	if time.Now().UTC().Sub(stamp).Hours() > 48 {
		return true
	}

	return false
}

func ArchivePosts(actor activitypub.Actor) error {
	if actor.Id != "" && actor.Id != Domain {
		col, err := GetAllActorArchiveDB(actor.Id, 165)
		if err != nil {
			return err
		}

		for _, e := range col.OrderedItems {
			for _, k := range e.Replies.OrderedItems {
				if err := UpdateObjectTypeDB(k.Id, "Archive"); err != nil {
					return err
				}
			}

			if err := UpdateObjectTypeDB(e.Id, "Archive"); err != nil {
				return err
			}
		}
	}
}

func GetAllActorArchiveDB(id string, offset int) (activitypub.Collection, error) {
	var nColl activitypub.Collection
	var result []activitypub.ObjectBase

	query := `select x.id, x.updated from (select id, updated from activitystream where actor=$1 and id in (select id from replies where inreplyto='') and type='Note' union select id, updated from activitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type='Note' union select id, updated from cacheactivitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type='Note') as x order by x.updated desc offset $2`

	rows, err := db.Query(query, id, offset)
	if err != nil {
		return nColl, err
	}
	defer rows.Close()

	for rows.Next() {
		var post activitypub.ObjectBase

		if err := rows.Scan(&post.Id, &post.Updated); err != nil {
			return nColl, err
		}

		post.Replies, _, _, err = GetObjectRepliesDB(post)

		result = append(result, post)
	}

	nColl.OrderedItems = result

	return nColl, nil
}

func GetActorCollectionDBType(actorId string, nType string) (activitypub.Collection, err) {
	var nColl activitypub.Collection
	var result []activitypub.ObjectBase

	query := `select x.id, x.name, x.content, x.type, x.published, x.updated, x.attributedto, x.attachment, x.preview, x.actor, x.tripcode, x.sensitive from (select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where actor=$1 and id in (select id from replies where inreplyto='') and type=$2 union select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type=$2 union select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from cacheactivitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type=$2) as x order by x.updated desc`

	rows, err := db.Query(query, actorId, nType)
	if err != nil {
		return nColl, err
	}

	defer rows.Close()
	for rows.Next() {
		var post activitypub.ObjectBase
		var actor activitypub.Actor
		var attachID string
		var previewID string

		if err := rows.Scan(&post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.Updated, &post.AttributedTo, &attachID, &previewID, &actor.Id, &post.TripCode, &post.Sensitive); err != nil {
			return err
		}

		post.Actor = actor.Id

		var replies activitypub.CollectionBase

		post.Replies = &replies

		var err error
		post.Replies.TotalItems, post.Replies.TotalImgs, err = GetObjectRepliesCount(post)
		if err != nil {
			return nColl, err
		}

		post.Attachment, err = GetObjectAttachment(attachID)
		if err != nil {
			return nColl, err
		}

		post.Preview, err = GetObjectPreview(previewID)
		if err != nil {
			return nColl, err
		}

		result = append(result, post)
	}

	nColl.OrderedItems = result

	return nColl, nil
}

func GetActorCollectionDBTypeLimit(actorId string, nType string, limit int) (activitypub.Collection, error) {
	var nColl activitypub.Collection
	var result []activitypub.ObjectBase

	query := `select x.id, x.name, x.content, x.type, x.published, x.updated, x.attributedto, x.attachment, x.preview, x.actor, x.tripcode, x.sensitive from (select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where actor=$1 and id in (select id from replies where inreplyto='') and type=$2 union select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type=$2 union select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from cacheactivitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type=$2) as x order by x.updated desc limit $3`

	rows, err := db.Query(query, actorId, nType, limit)
	if err != nil {
		return nColl, err
	}

	defer rows.Close()
	for rows.Next() {
		var post activitypub.ObjectBase
		var actor activitypub.Actor
		var attachID string
		var previewID string

		if err := rows.Scan(&post.Id, &post.Name, &post.Content, &post.Type, &post.Published, &post.Updated, &post.AttributedTo, &attachID, &previewID, &actor.Id, &post.TripCode, &post.Sensitive); err != nil {
			return nColl, err
		}

		post.Actor = actor.Id

		var replies activitypub.CollectionBase

		post.Replies = &replies

		var err error
		post.Replies.TotalItems, post.Replies.TotalImgs, err = GetObjectRepliesCount(db, post)
		if err != nil {
			return nColl, err
		}

		post.Attachment, err = GetObjectAttachment(db, attachID)
		if err != nil {
			return nColl, err
		}

		post.Preview, err = GetObjectPreview(db, previewID)
		if err != nil {
			return nColl, err
		}

		result = append(result, post)
	}

	nColl.OrderedItems = result

	return nColl, nil
}

func UpdateObjectTypeDB(id string, nType string) error {
	query := `update activitystream set type=$2 where id=$1 and type !='Tombstone'`
	if _, err := db.Exec(query, id, nType); err != nil {
		return err
	}

	query = `update cacheactivitystream set type=$2 where id=$1 and type !='Tombstone'`
	_, err := db.Exec(query, id, nType)
	return err
}

func UnArchiveLast(actorId string) error {
	col, err := GetActorCollectionDBTypeLimit(actorId, "Archive", 1)

	for _, e := range col.OrderedItems {
		for _, k := range e.Replies.OrderedItems {
			if err := UpdateObjectTypeDB(k.Id, "Note"); err != nil {
				return err
			}
		}

		if err := UpdateObjectTypeDB(e.Id, "Note"); err != nil {
			return err
		}
	}

	return nil
}

func SetObjectType(id string, nType string) error {
	col, err := GetObjectFromDB(id)
	if err != nil {
		return err
	}

	for _, e := range col.OrderedItems {
		for _, k := range e.Replies.OrderedItems {
			if err := UpdateObjectTypeDB(k.Id, nType); err != nil {
				return err
			}
		}

		if err := UpdateObjectTypeDB(e.Id, nType); err != nil {
			return err
		}
	}
}

func GetObjectTypeDB(id string) (string, error) {
	query := `select type from activitystream where id=$1 union select type from cacheactivitystream where id=$1`

	rows, err := db.Query(query, id)
	if err != nil {
		return "", err
	}
	defer rows.Close()

	var nType string
	rows.Next()
	rows.Scan(&nType)

	return nType, nil
}