/*
 * Amsterdam Web Communities System
 * Copyright (c) 2025-2026 Erbosoft Metaverse Design Solutions, All Rights Reserved
 *
 * This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this
 * file, You can obtain one at https://mozilla.org/MPL/2.0/.
 *
 * SPDX-License-Identifier: MPL-2.0
 */
// The database package contains database management and storage logic.
package database

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"

	"git.erbosoft.com/amy/amsterdam/util"
	"github.com/jmoiron/sqlx"
	log "github.com/sirupsen/logrus"
)

// ErrNoTopic is an error returned if no topic is found.
var ErrNoTopic error = errors.New("no such topic found")

// Topic is the top-level structure detailing topics.
type Topic struct {
	TopicId    int32     `db:"topicid"`     // unique ID of the topic
	ConfId     int32     `db:"confid"`      // conference this topic is in
	Number     int16     `db:"num"`         // topic number
	CreatorUid int32     `db:"creator_uid"` // UID of topic creator
	TopMessage int32     `db:"top_message"` // highest message number in topic
	Frozen     bool      `db:"frozen"`      // frozen topic
	Archived   bool      `db:"archived"`    // archived topic
	Sticky     bool      `db:"sticky"`      // sticky topic
	CreateDate time.Time `db:"createdate"`  // creation date
	LastUpdate time.Time `db:"lastupdate"`  // last update date
	Name       string    `db:"name"`        // topic name
}

// Link returns a link string to this topic.
func (t *Topic) Link(ctx context.Context, commid int32, scope string) (string, error) {
	if scope == "conference" {
		return fmt.Sprintf("%d.", t.Number), nil
	}
	if scope == "community" || scope == "global" {
		conf, err := AmGetConference(ctx, t.ConfId)
		if err == nil {
			var plink string
			plink, err = conf.Link(ctx, commid, scope)
			if err == nil {
				if strings.HasSuffix(plink, ".") {
					return fmt.Sprintf("%s%d", plink, t.Number), nil
				} else {
					return fmt.Sprintf("%s.%d", plink, t.Number), nil
				}
			}
		}
		return "", err
	}
	return "", errors.New("invalid scope")
}

// GetPost returns a post in the topic by number.
func (t *Topic) GetPost(ctx context.Context, num int32) (*PostHeader, error) {
	if num > t.TopMessage {
		return nil, fmt.Errorf("no post %d in topic %d", num, t.TopicId)
	}
	var pd PostHeader
	if err := amdb.GetContext(ctx, &pd, "SELECT * FROM posts WHERE topicid = ? AND num = ?", t.TopicId, num); err != nil {
		return nil, err
	}
	return &pd, nil
}

// GetLastRead returns the "last read" message for a user on a topic.
func (t *Topic) GetLastRead(ctx context.Context, u *User) (int32, error) {
	if u.IsAnon {
		return -1, nil
	}
	var rc int32 = -1
	err := amdb.GetContext(ctx, &rc, "SELECT last_message FROM topicsettings WHERE topicid = ? AND uid = ?", t.TopicId, u.Uid)
	if err == sql.ErrNoRows {
		return -1, nil
	}
	return rc, err
}

// SetLastRead sets the "last read" message for a user on a topic.
func (t *Topic) SetLastRead(ctx context.Context, u *User, postNum int32) error {
	if u.IsAnon {
		return nil
	}
	rs, err := amdb.ExecContext(ctx, "UPDATE topicsettings SET last_message = ?, last_read = NOW() WHERE topicid = ? AND uid = ?",
		postNum, t.TopicId, u.Uid)
	if err == nil {
		nrow, _ := rs.RowsAffected()
		if nrow == 0 {
			_, err = amdb.ExecContext(ctx, "INSERT INTO topicsettings (topicid, uid, last_message, last_read, last_post) VALUES (?, ?, ?, NOW(), NULL)",
				t.TopicId, u.Uid, postNum)
		}
	}
	return err
}

// IsHidden tells us whether the user has the topic hidden.
func (t *Topic) IsHidden(ctx context.Context, u *User) (bool, error) {
	rc := false
	err := amdb.GetContext(ctx, &rc, "SELECT hidden FROM topicsettings WHERE topicid = ? AND uid = ?", t.TopicId, u.Uid)
	return rc, err
}

// SetHidden sets the "hidden" state on a topic for a user.
func (t *Topic) SetHidden(ctx context.Context, u *User, hidden bool) error {
	rs, err := amdb.ExecContext(ctx, "UPDATE topicsettings SET hidden = ? WHERE topicid = ? AND uid = ?", hidden, t.TopicId, u.Uid)
	if err == nil {
		nrow, _ := rs.RowsAffected()
		if nrow == 0 {
			_, err = amdb.ExecContext(ctx, "INSERT INTO topicsettings (topicid, uid, hidden) VALUES (?, ?, ?)", t.TopicId, u.Uid, hidden)
		}
	}
	return err
}

// SetFrozen sets a topic's "frozen" state.
func (t *Topic) SetFrozen(ctx context.Context, frozen bool, u *User, comm *Community, ipaddr string) error {
	_, err := amdb.ExecContext(ctx, "UPDATE topics SET frozen = ? WHERE topicid = ?", frozen, t.TopicId)
	if err == nil {
		t.Frozen = frozen
		AmStoreAudit(AmNewCommAudit(AuditConferenceFreezeTopic, u.Uid, comm.Id, ipaddr, fmt.Sprintf("topic=%d", t.TopicId), fmt.Sprintf("frozen=%t", frozen)))
	}
	return err
}

// SetArchived sets a topic's "archived" state.
func (t *Topic) SetArchived(ctx context.Context, archived bool, u *User, comm *Community, ipaddr string) error {
	_, err := amdb.ExecContext(ctx, "UPDATE topics SET archived = ? WHERE topicid = ?", archived, t.TopicId)
	if err == nil {
		t.Archived = archived
		AmStoreAudit(AmNewCommAudit(AuditConferenceArchiveTopic, u.Uid, comm.Id, ipaddr, fmt.Sprintf("topic=%d", t.TopicId), fmt.Sprintf("archived=%t", archived)))
	}
	return err
}

// SetSticky sets a topic's "sticky" state.
func (t *Topic) SetSticky(ctx context.Context, sticky bool, u *User, comm *Community, ipaddr string) error {
	_, err := amdb.ExecContext(ctx, "UPDATE topics SET sticky = ? where topicid = ?", sticky, t.TopicId)
	if err == nil {
		t.Sticky = sticky
		AmStoreAudit(AmNewCommAudit(AuditConferenceStickyTopic, u.Uid, comm.Id, ipaddr, fmt.Sprintf("topic=%d", t.TopicId), fmt.Sprintf("sticky=%t", sticky)))
	}
	return err
}

// IsBozo returns true if the specified test UID is filtered for the specified user.
func (t *Topic) IsBozo(ctx context.Context, u *User, testUid int32) (bool, error) {
	if u.IsAnon {
		return false, nil
	}
	var tmp int32
	err := amdb.GetContext(ctx, &tmp, "SELECT bozo_uid FROM topicbozo WHERE topicid = ? AND uid = ? AND bozo_uid = ?", t.TopicId, u.Uid, testUid)
	switch err {
	case nil:
		return true, nil
	case sql.ErrNoRows:
		return false, nil
	}
	return false, err
}

// SetBozo adds or removes a filter of a subject UID for the specified user.
func (t *Topic) SetBozo(ctx context.Context, u *User, subjectUid int32, bozo bool) error {
	var err error = nil
	if !u.IsAnon {
		if bozo { // Flipping the bozo bit!
			var tmp int32
			err = amdb.GetContext(ctx, &tmp, "SELECT bozo_uid FROM topicbozo WHERE topicid = ? AND uid = ? AND bozo_uid = ?", t.TopicId, u.Uid, subjectUid)
			switch err {
			case nil:
				return nil
			case sql.ErrNoRows:
				_, err = amdb.ExecContext(ctx, "INSERT INTO topicbozo (topicid, uid, bozo_uid) VALUES (?, ?, ?)", t.TopicId, u.Uid, subjectUid)
			}
		} else {
			_, err = amdb.ExecContext(ctx, "DELETE FROM topicbozo WHERE topicid = ? AND uid = ? AND bozo_uid = ?", t.TopicId, u.Uid, subjectUid)
		}
	}
	return err
}

// TopicBozo is a structure that returns all information about a filtered user.
type TopicBozo struct {
	Uid        int32
	Username   string
	GivenName  string
	FamilyName string
}

// GetBozos returns all filtered users for a given user on the topic.
func (t *Topic) GetBozos(ctx context.Context, u *User) ([]*TopicBozo, error) {
	if u.IsAnon {
		return make([]*TopicBozo, 0), nil
	}
	rs, err := amdb.QueryContext(ctx, `SELECT b.bozo_uid, u.username, c.given_name, c.family_name
		FROM topicbozo b, users u, contacts c WHERE b.topicid = ? AND b.uid = ? AND b.bozo_uid = u.uid AND u.contactid = c.contactid
		ORDER BY u.username`, t.TopicId, u.Uid)
	if err != nil {
		return nil, err
	}
	rc := make([]*TopicBozo, 0)
	for rs.Next() {
		tb := new(TopicBozo)
		err = rs.Scan(&(tb.Uid), &(tb.Username), &(tb.GivenName), &(tb.FamilyName))
		if err != nil {
			return nil, err
		}
		rc = append(rc, tb)
	}
	return rc, nil
}

// IsSubscribed returns true if the given user is subscribed to receive E-mails of topic posts.
func (t *Topic) IsSubscribed(ctx context.Context, u *User) (bool, error) {
	var rc bool
	err := amdb.GetContext(ctx, &rc, "SELECT subscribe FROM topicsettings WHERE topicid = ? AND uid = ?", t.TopicId, u.Uid)
	switch err {
	case nil:
		return rc, nil
	case sql.ErrNoRows:
		return false, nil
	}
	return false, err
}

// SetSubscribed sets the "subscribed" flag for the given user.
func (t *Topic) SetSubscribed(ctx context.Context, u *User, flag bool) error {
	if u.IsAnon {
		return nil
	}
	rs, err := amdb.ExecContext(ctx, "UPDATE topicsettings SET subscribe = ? WHERE topicid = ? AND uid = ?", flag, t.TopicId, u.Uid)
	if err == nil {
		var rows int64
		rows, err = rs.RowsAffected()
		if err == nil && rows == 0 {
			_, err = amdb.ExecContext(ctx, "INSERT INTO topicsettings (topicid, uid, subscribe)", t.TopicId, u.Uid, flag)
		}
	}
	return err
}

// GetSubscribers returns an array of UIDs of every user that subscribed to the topic.
func (t *Topic) GetSubscribers(ctx context.Context) ([]int32, error) {
	rs, err := amdb.QueryContext(ctx, "SELECT uid FROM topicsettings WHERE topicid = ? AND subscribe <> 0", t.TopicId)
	if err != nil {
		return nil, err
	}
	rc := make([]int32, 0)
	for rs.Next() {
		var tmp int32
		err = rs.Scan(&tmp)
		if err == nil {
			rc = append(rc, tmp)
		} else {
			break
		}
	}
	return rc, err
}

/* GetActivity returns a list of ActivityReport objects detailing the topic activity.
 * Parameters:
 *     ctx - Standard Go context value.
 *     reportType - Determines which report to generate:
 *         ActivityReportPosters - Report on all posters in the topic.
 *         ActivityReportReaders - Report on all readers in the topic.
 * Returns:
 *     List of ActivityReport objects detailing the topic activity.
 *     Standard Go error status.
 */
func (t *Topic) GetActivity(ctx context.Context, reportType int) ([]*ActivityReport, error) {
	var myfield string
	switch reportType {
	case ActivityReportPosters:
		myfield = "s.last_post"
	case ActivityReportReaders:
		myfield = "s.last_read"
	default:
		return nil, errors.New("invalid report type parameter")
	}
	sql := fmt.Sprintf(`SELECT s.uid, u.username, s.last_read, s.last_post FROM topicsettings s, users u WHERE u.uid = s.uid 
		AND s.topicid = ? AND u.is_anon = 0 AND ISNULL(%s) = 0 ORDER BY %s DESC`, myfield, myfield)
	rs, err := amdb.QueryContext(ctx, sql, t.TopicId)
	if err != nil {
		return nil, err
	}
	rc := make([]*ActivityReport, 0)
	for rs.Next() {
		cur := new(ActivityReport)
		err = rs.Scan(&(cur.Uid), &(cur.Username), &(cur.LastRead), &(cur.LastPost))
		if err != nil {
			return nil, err
		}
		rc = append(rc, cur)
	}
	return rc, nil
}

/* GetActiveUserEMailAddrs gets the E-mail addresses of each user that's active in the topic, omitting those that have opted out of mass E-mails.
 * Parameters:
 *     ctx - Standard Go context value.
 *     userSelect - Selects which type of users to return:
 *         ActiveUserReaders - Select users that have actively read.
 *         ActiveUserPosters - Select users that have actively posted.
 *     dayLimit - If less than 0, it is ignored. If equal to 0, this function is a no-op. Otherwise, specifies a limit on the number of days
 *                between the user's activity and now.
 * Returns:
 *     List of E-mail addresses matchin the criteria, in arbitrary order.
 *     Standard Go error status.
 */
func (t *Topic) GetActiveUserEMailAddrs(ctx context.Context, userSelect, dayLimit int) ([]string, error) {
	if dayLimit == 0 {
		return make([]string, 0), nil
	}
	var myfield string
	switch userSelect {
	case ActiveUserReaders:
		myfield = "s.last_read"
	case ActiveUserPosters:
		myfield = "s.last_post"
	default:
		return nil, errors.New("invalid user selection parameter")
	}
	sql := fmt.Sprintf(`SELECT c.email, %s FROM contacts c, users u, topicsettings s, propuser p WHERE c.contactid = u.contactid AND u.uid = s.uid
		AND s.topicid = ? AND u.is_anon = 0 AND u.uid = p.uid AND p.ndx = %d AND p.data NOT LIKE '%%%s%%' AND ISNULL(%s) = 0 ORDER BY %s DESC`, myfield, UserPropFlags,
		util.OptionCharFromIndex(UserFlagMassMailOptOut), myfield, myfield)
	rs, err := amdb.QueryContext(ctx, sql, t.TopicId)
	if err != nil {
		return nil, err
	}
	var stopPoint *time.Time = nil
	if dayLimit > 0 {
		mynow := time.Now().UTC()
		y, m, d := mynow.AddDate(0, 0, -dayLimit).Date()
		stopPointActual := time.Date(y, m, d, 0, 0, 0, 0, mynow.Location())
		stopPoint = &stopPointActual
	}
	rc := make([]string, 0)
	for rs.Next() {
		var addy string
		var point time.Time
		if err = rs.Scan(&addy, &point); err != nil {
			return nil, err
		}
		if stopPoint != nil && point.Before(*stopPoint) {
			break
		}
		rc = append(rc, addy)
	}
	return rc, nil
}

// backgroundPurgeTopic removes all posts from a topic that's been deleted.
func backgroundPurgeTopic(ctx context.Context, topicid int32) error {
	tx, commit, rollback := transaction(ctx)
	defer rollback()

	// Get some stats on the posts we have to remove.
	var postMax int32
	err := tx.GetContext(ctx, &postMax, "SELECT MAX(postid) FROM posts WHERE topicid = ?", topicid)
	if err != nil {
		return err
	}

	// Perform wholesale deletes on auxiliary tables.
	_, err = tx.ExecContext(ctx, "DELETE FROM postdata WHERE postid IN (SELECT postid FROM posts WHERE topicid = ? AND postid <= ?)", topicid, postMax)
	if err == nil {
		_, err = tx.ExecContext(ctx, "DELETE FROM postattach WHERE postid IN (SELECT postid FROM posts WHERE topicid = ? AND postid <= ?)", topicid, postMax)
		if err == nil {
			_, err = tx.ExecContext(ctx, "DELETE FROM postdogear WHERE postid IN (SELECT postid FROM posts WHERE topicid = ? AND postid <= ?)", topicid, postMax)
			if err == nil {
				_, err = tx.ExecContext(ctx, "DELETE FROM postpublish WHERE postid IN (SELECT postid FROM posts WHERE topicid = ? AND postid <= ?)", topicid, postMax)
			}
		}
	}
	if err != nil {
		return err
	}

	// Now delete from the main posts table.
	_, err = tx.ExecContext(ctx, "DELETE FROM posts WHERE topicid = ? AND postid <= ?", topicid, postMax)
	if err != nil {
		return err
	}
	if err = commit(); err != nil {
		return err
	}
	return nil
}

// eraseTopicRecords erases the high-level records for a topic from the database.
func eraseTopicRecords(ctx context.Context, tx *sqlx.Tx, topicid int32) error {
	_, err := tx.ExecContext(ctx, "DELETE FROM topics WHERE topicid = ?", topicid)
	if err == nil {
		_, err = tx.ExecContext(ctx, "DELETE FROM topicsettings WHERE topicid = ?", topicid)
		if err == nil {
			_, err = tx.ExecContext(ctx, "DELETE FROM topicbozo WHERE topicid = ?", topicid)
		}
	}
	return err
}

// Delete deletes this topic.
func (t *Topic) Delete(ctx context.Context, u *User, comm *Community, ipaddr string, background *util.WorkerPool) error {
	tx, commit, rollback := transaction(ctx)
	defer rollback()

	conf, err := AmGetConference(ctx, t.ConfId)
	if err != nil {
		return err
	}

	err = eraseTopicRecords(ctx, tx, t.TopicId)
	if err != nil {
		return err
	}

	if err = conf.TouchUpdate(ctx, tx, time.Now()); err != nil {
		return err
	}
	if err = commit(); err != nil {
		return err
	}

	// create audit record
	AmStoreAudit(AmNewCommAudit(AuditConferenceDeleteTopic, u.Uid, comm.Id, ipaddr, fmt.Sprintf("confid=%d", conf.ConfId),
		fmt.Sprintf("topic=%d", t.TopicId)))

	// Spin off a background task to finish deleting this topic.
	myTopicId := t.TopicId
	background.Submit(func(ctx context.Context) {
		start := time.Now()
		err := backgroundPurgeTopic(ctx, myTopicId)
		if err != nil {
			log.Errorf("backgroundTopicPurge FAILED with %v", err)
		}
		dur := time.Since(start)
		log.Infof("Topic.Delete task completed in %v", dur)
	})

	return nil
}

// TopicSettings contains per-user settings for topics, including the "last read" message pointer.
type TopicSettings struct {
	TopicId     int32      `db:"topicid"`      // unique ID of the topic
	Uid         int32      `db:"uid"`          // UID of the user
	Hidden      bool       `db:"hidden"`       // has user hidden topic?
	LastMessage int32      `db:"last_message"` // last message read
	LastRead    *time.Time `db:"last_read"`    // time of last read
	LastPost    *time.Time `db:"last_post"`    // time of last post
	Subscribe   bool       `db:"subscribe"`    // subscribed to topic updates?
}

// TopicSummary is a smaller data structure that gets topic information to create the topic list display.
type TopicSummary struct {
	TopicID    int32     // the topic ID
	Number     int16     // the number of the topic
	Name       string    // the topic name
	Unread     int32     // number of unread messages
	Total      int32     // total number of messages
	LastUpdate time.Time // last update timestamp
	Frozen     bool      // is topic frozen?
	Archived   bool      // is topic archived?
	Subscribed bool      // is topic subscribed?
	Hidden     bool      // is topic hidden?
	Sticky     bool      // is topic sticky?
	NewFlag    bool      // does topic have new messages?
}

/* AmGetTopic retrieves a topic by ID.
 * Parameters:
 *     ctx - Standard Go context value.
 *     topicId - ID of the topic to retrieve.
 * Returns:
 *     The topic pointer, or nil.
 *     Standard Go error status.
 */
func AmGetTopic(ctx context.Context, topicId int32) (*Topic, error) {
	top := new(Topic)
	if err := amdb.GetContext(ctx, top, "SELECT * FROM topics WHERE topicid = ?", topicId); err != nil {
		if err == sql.ErrNoRows {
			err = ErrNoTopic
		}
		return nil, err
	}
	return top, nil
}

/* AmGetTopicTx retrieves a topic by ID, in a transaction.
 * Parameters:
 *     ctx - Standard Go context value.
 *     tx - The transaction to use.
 *     topicId - ID of the topic to retrieve.
 * Returns:
 *     The topic pointer, or nil.
 *     Standard Go error status.
 */
func AmGetTopicTx(ctx context.Context, tx *sqlx.Tx, topicId int32) (*Topic, error) {
	top := new(Topic)
	if err := tx.GetContext(ctx, top, "SELECT * FROM topics WHERE topicid = ?", topicId); err != nil {
		if err == sql.ErrNoRows {
			err = ErrNoTopic
		}
		return nil, err
	}
	return top, nil
}

/* AmGetTopicByNumber retrieves a topic by conference and sequence number.
 * Parameters:
 *     ctx - Standard Go context value.
 *     conf - The conference to look in.
 *     topicNum - The topic number within that conference.
 * Returns:
 *     Pointer to the Topic, or nil.
 *     Standard Go error status.
 */
func AmGetTopicByNumber(ctx context.Context, conf *Conference, topicNum int16) (*Topic, error) {
	top := new(Topic)
	err := amdb.GetContext(ctx, top, "SELECT * FROM topics WHERE confid = ? AND num = ?", conf.ConfId, topicNum)
	if err == nil {
		return top, nil
	}
	if err == sql.ErrNoRows {
		err = ErrNoTopic
	}
	return nil, err
}

/* AmGetTopicByName retrieves a topic by conference and name.
 * Parameters:
 *     ctx - Standard Go context value.
 *     conf - The conference to look in.
 *     name - The topic name within that conference.
 * Returns:
 *     Pointer to the Topic, or nil.
 *     Standard Go error status.
 */
func AmGetTopicByName(ctx context.Context, conf *Conference, name string) (*Topic, error) {
	top := new(Topic)
	err := amdb.GetContext(ctx, top, "SELECT * FROM topics WHERE confid = ? AND name = ?", conf.ConfId, name)
	if err == nil {
		return top, nil
	}
	if err == sql.ErrNoRows {
		err = ErrNoTopic
	}
	return nil, err
}

// View and sort constants for AmListTopics.
const (
	TopicViewAll        = 0 // list all topics
	TopicViewNew        = 1 // list only visible topics with new messages
	TopicViewActive     = 2 // list only visible topics, active first
	TopicViewAllVisible = 3 // list only visible topics
	TopicViewHidden     = 4 // list only hidden topics
	TopicViewArchive    = 5 // list only archived, non-hidden topics

	TopicSortID     = 0 // sort by topic ID
	TopicSortNumber = 1 // sort by topic number
	TopicSortName   = 2 // sort by name
	TopicSortUnread = 3 // sort by number of unread messages
	TopicSortTotal  = 4 // sort by total number of messages
	TopicSortDate   = 5 // sort by date of last update
)

/* AmListTopics produces a list of topic summary information according to specific options.
 * Parameters:
 *     ctx - Standard Go context value.
 *     confid - The ID of the conference to list topics in.
 *     uid - The UID of the user to consider the settings of.
 *     viewOption - One of the following constants:
 *         TopicViewAll - List all topics.
 *         TopicViewNew - List only visible topics with new messages.
 *         TopicViewActive - List only visible topics, with "active" ones coming first.
 *         TopicViewAllVisible - List only visible topics.
 *		   TopicViewHidden - List only hidden topics (including archived ones).
 *         TopicViewArchive - List only archived, non-hidden topics.
 *     sortOption - One of the following constants:
 *         TopicSortID - Sort by topic ID.
 *         TopicSortNumber - Sort by topic number in the conference. May be negated to sort in reverse order.
 *         TopicSortName - Sort by topic name. May be negated to sort in reverse order.
 *         TopicSortUnread - Sort by number of unread messages. May be negated to sort in reverse order.
 *         TopicSortTotal - Sort by total number of messages. May be negated to sort in reverse order.
 *         TopicSortDate - Sort by last topic update date. May be negated to sort in reverse order.
 *     ignoreSticky - If false, sticky topics will precede nonsticky ones; if true, stickiness is ignored.
 * Returns:
 *     List of TopicSummary pointers.
 *     Standard Go error status.
 */
func AmListTopics(ctx context.Context, confid int32, uid int32, viewOption int, sortOption int, ignoreSticky bool) ([]*TopicSummary, error) {
	// Decode the viewOption into a WHERE clause.
	var whereClause string
	switch viewOption {
	case TopicViewAll:
		whereClause = ""
	case TopicViewNew:
		tail := "t.top_message > COALESCE(s.last_message,-1)"
		if !ignoreSticky {
			tail = "(t.sticky = 1 OR " + tail + ")"
		}
		whereClause = "t.archived = 0 AND (s.hidden IS NULL OR s.hidden = 0) AND " + tail
	case TopicViewActive:
		whereClause = "t.archived = 0 AND (s.hidden IS NULL OR s.hidden = 0)"
	case TopicViewAllVisible:
		whereClause = "(s.hidden IS NULL OR s.hidden = 0)"
	case TopicViewHidden:
		whereClause = "s.hidden = 1"
	case TopicViewArchive:
		whereClause = "t.archived = 1 AND (s.hidden IS NULL OR s.hidden = 0)"
	default:
		return nil, errors.New("invalid view option specified")
	}

	// Decode the sortOption into an ORDER BY clause.
	var reverse bool = false
	if sortOption < 0 {
		reverse = true
		sortOption = -sortOption
	}
	var orderByClause string
	switch sortOption {
	case TopicSortID:
		orderByClause = "t.topicid ASC"
	case TopicSortNumber:
		if reverse {
			orderByClause = "t.num DESC"
		} else {
			orderByClause = "t.num ASC"
		}
	case TopicSortName:
		if reverse {
			orderByClause = "t.name DESC, t.num DESC"
		} else {
			orderByClause = "t.name ASC, t.num ASC"
		}
	case TopicSortUnread:
		if reverse {
			orderByClause = "unread ASC, t.num DESC"
		} else {
			orderByClause = "unread DESC, t.num ASC"
		}
	case TopicSortTotal:
		if reverse {
			orderByClause = "total ASC, t.num DESC"
		} else {
			orderByClause = "total DESC, t.num ASC"
		}
	case TopicSortDate:
		if reverse {
			orderByClause = "t.lastupdate ASC, t.num DESC"
		} else {
			orderByClause = "t.lastupdate DESC, t.num ASC"
		}
	default:
		return nil, errors.New("invalid sort option specified")
	}

	// Build the full SQL statement
	var fullStatement strings.Builder
	fullStatement.WriteString("SELECT t.topicid, t.num, t.name, (t.top_message - COALESCE(s.last_message,-1)) AS unread, ")
	fullStatement.WriteString("(t.top_message + 1) AS total, t.lastupdate, t.frozen, t.archived, COALESCE(s.subscribe,0) AS subscribe, ")
	fullStatement.WriteString("COALESCE(s.hidden,0) AS hidden, t.sticky, GREATEST(SIGN(t.top_message - COALESCE(s.last_message,-1)),0) AS newflag ")
	fullStatement.WriteString("FROM topics t LEFT JOIN topicsettings s ON t.topicid = s.topicid AND s.uid = ? WHERE t.confid = ? ")
	if whereClause != "" {
		fullStatement.WriteString("AND ")
		fullStatement.WriteString(whereClause)
	}
	fullStatement.WriteString(" ORDER BY ")
	if !ignoreSticky {
		fullStatement.WriteString("t.sticky DESC, ")
	}
	if viewOption == TopicViewActive {
		fullStatement.WriteString("newflag DESC, ")
	}
	fullStatement.WriteString(orderByClause)

	// Execute and capture results
	rs, err := amdb.QueryContext(ctx, fullStatement.String(), uid, confid)
	if err != nil {
		if err == sql.ErrNoRows {
			err = ErrNoTopic
		}
		return nil, err
	}
	rc := make([]*TopicSummary, 0)
	for rs.Next() {
		rec := new(TopicSummary)
		if err = rs.Scan(&rec.TopicID, &rec.Number, &rec.Name, &rec.Unread, &rec.Total, &rec.LastUpdate, &rec.Frozen,
			&rec.Archived, &rec.Subscribed, &rec.Hidden, &rec.Sticky, &rec.NewFlag); err != nil {
			log.Errorf("AmListTopics scan error: %v", err)
		} else {
			rc = append(rc, rec)
		}
	}
	return rc, nil
}

/* AmNewTopic creates a new topic.
 * Parameters:
 *     ctx - Standard Go context value.
 *     conf - Conference to add the new post.
 *     user - User creating the new topic.
 *     title - The new topic's title.
 *     zeroPostPseud - Pseud for the topic's "zero post" (first post).
 *     zeroPost - Textual data for the zero post.
 *     zeroPostLines - Number of lines of text in zeroPost.
 *     ipaddr - IP address of the user making the topic, for audit purposes.
 * Returns:
 *     Pointer to the new Topic data structure.
 *     Standard Go error status.
 */
func AmNewTopic(ctx context.Context, conf *Conference, user *User, title, zeroPostPseud, zeroPost string, zeroPostLines int32,
	comm *Community, ipaddr string) (*Topic, error) {
	tx, commit, rollback := transaction(ctx)
	defer rollback()

	// Insert the new topic into the database.
	conf.Mutex.Lock()
	rs, err := tx.ExecContext(ctx, "INSERT INTO topics (confid, num, creator_uid, createdate, lastupdate, name) VALUES (?, ?, ?, NOW(), NOW(), ?)",
		conf.ConfId, conf.TopTopic+1, user.Uid, title)
	if err != nil {
		conf.Mutex.Unlock()
		return nil, err
	}
	// Retrieve the ID of the new topic.
	xid, err := rs.LastInsertId()
	if err != nil {
		conf.Mutex.Unlock()
		return nil, err
	}
	// Get the topic.
	topic, err := AmGetTopicTx(ctx, tx, int32(xid))
	if err != nil {
		conf.Mutex.Unlock()
		return nil, err
	}

	// Update the conference to set the last update and top topic.
	_, err = tx.ExecContext(ctx, "UPDATE confs SET lastupdate = ?, top_topic = ? WHERE confid = ?", topic.CreateDate, conf.TopTopic+1, conf.ConfId)
	if err != nil {
		conf.Mutex.Unlock()
		return nil, err
	}
	conf.TopTopic++
	conf.LastUpdate = &topic.CreateDate
	conf.Mutex.Unlock()

	// Add the "header record" for the first post.
	rs, err = tx.ExecContext(ctx, "INSERT INTO posts (topicid, num, linecount, creator_uid, posted, pseud) VALUES (?, 0, ?, ?, ?, ?)",
		topic.TopicId, zeroPostLines, user.Uid, topic.CreateDate, zeroPostPseud)
	if err != nil {
		return nil, err
	}
	xid, err = rs.LastInsertId()
	if err != nil {
		return nil, err
	}
	// Add the post data.
	_, err = tx.ExecContext(ctx, "INSERT INTO postdata (postid, data) VALUES (?, ?)", int32(xid), zeroPost)
	if err != nil {
		return nil, err
	}

	// Add a new topic settings record for the user, too.
	_, err = tx.ExecContext(ctx, "INSERT INTO topicsettings (topicid, uid, last_post) VALUES (?, ?, ?)",
		topic.TopicId, user.Uid, topic.CreateDate)
	if err != nil {
		return nil, err
	}

	// update the "last posted" date in the conference settings
	_, err = conf.TouchPost(ctx, tx, user, topic.CreateDate)
	if err != nil {
		return nil, err
	}

	if err = commit(); err != nil {
		return nil, err
	}

	// create audit record
	AmStoreAudit(AmNewCommAudit(AuditConferenceCreateTopic, user.Uid, comm.Id, ipaddr, fmt.Sprintf("confid=%d", conf.ConfId),
		fmt.Sprintf("num=%d", topic.Number), fmt.Sprintf("name=%s", topic.Name)))

	return topic, nil
}