package db

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

// ─────────────────────────── Users ───────────────────────────

// CreateUser inserts a new user and returns it with the assigned ID.
func (d *DB) CreateUser(ctx context.Context, username, passwordHash string, isAdmin bool) (*User, error) {
	admin := 0
	if isAdmin {
		admin = 1
	}
	t := now()
	res, err := d.db.ExecContext(ctx,
		`INSERT INTO users (username, password_hash, is_admin, created_at, updated_at)
		 VALUES (?, ?, ?, ?, ?)`,
		username, passwordHash, admin, t, t,
	)
	if err != nil {
		return nil, fmt.Errorf("create user: %w", err)
	}
	id, err := res.LastInsertId()
	if err != nil {
		return nil, fmt.Errorf("create user last insert id: %w", err)
	}
	return d.GetUserByID(ctx, id)
}

// GetUserByID returns a single user by primary key.
func (d *DB) GetUserByID(ctx context.Context, id int64) (*User, error) {
	row := d.db.QueryRowContext(ctx,
		`SELECT id, username, password_hash, is_admin, created_at, updated_at, avatar_path
		 FROM users WHERE id = ?`, id)
	return scanUser(row)
}

// SetUserAvatar updates the avatar_path for a user. Pass nil to clear.
func (d *DB) SetUserAvatar(ctx context.Context, id int64, avatarPath *string) error {
	_, err := d.db.ExecContext(ctx,
		`UPDATE users SET avatar_path = ?, updated_at = ? WHERE id = ?`,
		avatarPath, now(), id)
	return err
}

// GetUserByUsername returns a single user by username.
func (d *DB) GetUserByUsername(ctx context.Context, username string) (*User, error) {
	row := d.db.QueryRowContext(ctx,
		`SELECT id, username, password_hash, is_admin, created_at, updated_at, avatar_path
		 FROM users WHERE username = ?`, username)
	return scanUser(row)
}

// ListUsers returns all users ordered by username.
func (d *DB) ListUsers(ctx context.Context) ([]User, error) {
	rows, err := d.db.QueryContext(ctx,
		`SELECT id, username, password_hash, is_admin, created_at, updated_at, avatar_path
		 FROM users ORDER BY username`)
	if err != nil {
		return nil, fmt.Errorf("list users: %w", err)
	}
	defer rows.Close()

	users := make([]User, 0)
	for rows.Next() {
		u, err := scanUser(rows)
		if err != nil {
			return nil, err
		}
		users = append(users, *u)
	}
	return users, rows.Err()
}

// DeleteUser removes a user by ID. Returns sql.ErrNoRows if not found.
func (d *DB) DeleteUser(ctx context.Context, id int64) error {
	res, err := d.db.ExecContext(ctx, `DELETE FROM users WHERE id = ?`, id)
	if err != nil {
		return fmt.Errorf("delete user: %w", err)
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return sql.ErrNoRows
	}
	return nil
}

// UpdateUserPassword sets a new password hash for the user.
func (d *DB) UpdateUserPassword(ctx context.Context, id int64, passwordHash string) error {
	res, err := d.db.ExecContext(ctx,
		`UPDATE users SET password_hash = ?, updated_at = ? WHERE id = ?`,
		passwordHash, now(), id)
	if err != nil {
		return fmt.Errorf("update password: %w", err)
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return sql.ErrNoRows
	}
	return nil
}

type userScanner interface {
	Scan(dest ...any) error
}

func scanUser(s userScanner) (*User, error) {
	var u User
	var isAdmin int
	var createdAt, updatedAt string
	var avatarPath *string
	if err := s.Scan(&u.ID, &u.Username, &u.PasswordHash, &isAdmin, &createdAt, &updatedAt, &avatarPath); err != nil {
		return nil, err
	}
	u.IsAdmin = isAdmin == 1
	u.CreatedAt, _ = scanTime(createdAt)
	u.UpdatedAt, _ = scanTime(updatedAt)
	u.AvatarPath = avatarPath
	return &u, nil
}

// ─────────────────────────── Sessions ───────────────────────────

// CreateSession inserts a new session and returns it.
func (d *DB) CreateSession(ctx context.Context, userID int64, token string, expiresAt time.Time) (*Session, error) {
	t := now()
	res, err := d.db.ExecContext(ctx,
		`INSERT INTO sessions (user_id, token, expires_at, created_at)
		 VALUES (?, ?, ?, ?)`,
		userID, token, expiresAt.UTC().Format(time.RFC3339), t,
	)
	if err != nil {
		return nil, fmt.Errorf("create session: %w", err)
	}
	id, err := res.LastInsertId()
	if err != nil {
		return nil, fmt.Errorf("create session last insert id: %w", err)
	}

	return &Session{
		ID:        id,
		UserID:    userID,
		Token:     token,
		ExpiresAt: expiresAt,
	}, nil
}

// GetSessionByToken retrieves a session by its token.
func (d *DB) GetSessionByToken(ctx context.Context, token string) (*Session, error) {
	row := d.db.QueryRowContext(ctx,
		`SELECT id, user_id, token, expires_at, created_at
		 FROM sessions WHERE token = ?`, token)
	return scanSession(row)
}

// DeleteSession removes a session by token.
func (d *DB) DeleteSession(ctx context.Context, token string) error {
	_, err := d.db.ExecContext(ctx, `DELETE FROM sessions WHERE token = ?`, token)
	return err
}

// DeleteUserSessions removes all sessions for a given user.
func (d *DB) DeleteUserSessions(ctx context.Context, userID int64) error {
	_, err := d.db.ExecContext(ctx, `DELETE FROM sessions WHERE user_id = ?`, userID)
	return err
}

// ExtendSession sets a new expiration time on the session.
func (d *DB) ExtendSession(ctx context.Context, token string, expiresAt time.Time) error {
	_, err := d.db.ExecContext(ctx,
		`UPDATE sessions SET expires_at = ? WHERE token = ?`,
		expiresAt.UTC().Format(time.RFC3339), token)
	return err
}

type sessionScanner interface {
	Scan(dest ...any) error
}

func scanSession(s sessionScanner) (*Session, error) {
	var sess Session
	var createdAt, expiresAt string
	if err := s.Scan(&sess.ID, &sess.UserID, &sess.Token, &expiresAt, &createdAt); err != nil {
		return nil, err
	}
	sess.ExpiresAt, _ = scanTime(expiresAt)
	sess.CreatedAt, _ = scanTime(createdAt)
	return &sess, nil
}

// ─────────────────────────── Images ───────────────────────────

// CreateImage inserts a new image record.
func (d *DB) CreateImage(ctx context.Context, img *Image) (*Image, error) {
	t := now()
	res, err := d.db.ExecContext(ctx,
		`INSERT INTO images (filename, storage_path, thumbnail_path, uploaded_by,
		                     file_size, width, height, mime_type, created_at, updated_at)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		img.Filename, img.StoragePath, img.ThumbnailPath, img.UploadedBy,
		img.FileSize, img.Width, img.Height, img.MimeType, t, t,
	)
	if err != nil {
		return nil, fmt.Errorf("create image: %w", err)
	}
	id, err := res.LastInsertId()
	if err != nil {
		return nil, fmt.Errorf("create image last insert id: %w", err)
	}
	return d.GetImageByID(ctx, id)
}

// GetImageByID returns a single image by primary key.
func (d *DB) GetImageByID(ctx context.Context, id int64) (*Image, error) {
	row := d.db.QueryRowContext(ctx,
		`SELECT id, filename, storage_path, thumbnail_path,
		        uploaded_by, file_size, width, height, mime_type,
		        created_at, updated_at
		 FROM images WHERE id = ?`, id)
	img, err := scanImage(row)
	if err != nil {
		return nil, err
	}

	// Load tags for this image.
	tags, err := d.GetImageTags(ctx, id)
	if err != nil {
		return nil, err
	}
	img.Tags = tags
	return img, nil
}

// ListImages returns a paginated list of images, optionally filtered by tags.
// Multiple tags are AND-ed (image must have all specified tags).
// Order is newest-first by created_at.
func (d *DB) ListImages(ctx context.Context, filterTags []string, limit, offset int) (*ImageList, error) {
	// Count query
	countQuery := `SELECT COUNT(DISTINCT i.id) FROM images i`
	// Data query
	dataQuery := `SELECT i.id, i.filename, i.storage_path, i.thumbnail_path,
	                     i.uploaded_by, i.file_size, i.width, i.height, i.mime_type,
	                     i.created_at, i.updated_at
	              FROM images i`

	var args []any

	if len(filterTags) > 0 {
		placeholders := make([]string, len(filterTags))
		for j, t := range filterTags {
			placeholders[j] = "?"
			args = append(args, t)
		}
		having := fmt.Sprintf("HAVING COUNT(DISTINCT it.tag) = %d", len(filterTags))

		join := ` JOIN image_tags it ON i.id = it.image_id WHERE it.tag IN (` +
			strings.Join(placeholders, ",") + `)`
		countQuery += join
		dataQuery += join + ` GROUP BY i.id ` + having
	}

	dataQuery += ` ORDER BY i.created_at DESC LIMIT ? OFFSET ?`
	argsData := make([]any, len(args))
	copy(argsData, args)
	argsData = append(argsData, limit, offset)
	args = append(args, limit, offset)

	// Execute count.
	var total int
	if err := d.db.QueryRowContext(ctx, countQuery, argsData[:len(argsData)-2]...).Scan(&total); err != nil {
		return nil, fmt.Errorf("list images count: %w", err)
	}

	// Execute data.
	rows, err := d.db.QueryContext(ctx, dataQuery, argsData...)
	if err != nil {
		return nil, fmt.Errorf("list images: %w", err)
	}
	defer rows.Close()

	images := make([]Image, 0)
	for rows.Next() {
		img, err := scanImage(rows)
		if err != nil {
			return nil, err
		}
		images = append(images, *img)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return &ImageList{Images: images, Total: total}, nil
}

// DeleteImage removes an image record (tags cleaned via ON DELETE CASCADE).
func (d *DB) DeleteImage(ctx context.Context, id int64) error {
	// First decrement usage counts for associated tags.
	tags, err := d.GetImageTags(ctx, id)
	if err != nil {
		return err
	}
	for _, tag := range tags {
		if err := d.decrementTagUsage(ctx, tag); err != nil {
			return err
		}
	}

	res, err := d.db.ExecContext(ctx, `DELETE FROM images WHERE id = ?`, id)
	if err != nil {
		return fmt.Errorf("delete image: %w", err)
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return sql.ErrNoRows
	}
	return nil
}

type imageScanner interface {
	Scan(dest ...any) error
}

func scanImage(s imageScanner) (*Image, error) {
	var img Image
	var thumbnailPath, createdAt, updatedAt sql.NullString
	if err := s.Scan(
		&img.ID, &img.Filename, &img.StoragePath, &thumbnailPath,
		&img.UploadedBy, &img.FileSize, &img.Width, &img.Height, &img.MimeType,
		&createdAt, &updatedAt,
	); err != nil {
		return nil, err
	}
	if thumbnailPath.Valid {
		img.ThumbnailPath = &thumbnailPath.String
	}
	if createdAt.Valid {
		img.CreatedAt, _ = scanTime(createdAt.String)
	}
	if updatedAt.Valid {
		img.UpdatedAt, _ = scanTime(updatedAt.String)
	}
	return &img, nil
}

// ─────────────────────────── Tags ───────────────────────────

// SetImageTags replaces all tags for an image and updates usage counts.
func (d *DB) SetImageTags(ctx context.Context, imageID int64, tags []string) error {
	// Get existing tags to decrement counts.
	oldTags, err := d.GetImageTags(ctx, imageID)
	if err != nil {
		return err
	}

	// Remove old associations.
	if _, err := d.db.ExecContext(ctx, `DELETE FROM image_tags WHERE image_id = ?`, imageID); err != nil {
		return fmt.Errorf("clear image tags: %w", err)
	}

	for _, t := range oldTags {
		if err := d.decrementTagUsage(ctx, t); err != nil {
			return err
		}
	}

	// Insert new tags.
	for _, t := range tags {
		tag := normalizeTag(t)
		if tag == "" {
			continue
		}
		if _, err := d.db.ExecContext(ctx,
			`INSERT INTO image_tags (image_id, tag) VALUES (?, ?)`, imageID, tag,
		); err != nil {
			return fmt.Errorf("insert image tag: %w", err)
		}
		if err := d.incrementTagUsage(ctx, tag); err != nil {
			return err
		}
	}

	return nil
}

// GetImageTags returns the tag strings for a given image.
func (d *DB) GetImageTags(ctx context.Context, imageID int64) ([]string, error) {
	rows, err := d.db.QueryContext(ctx,
		`SELECT tag FROM image_tags WHERE image_id = ? ORDER BY tag`, imageID)
	if err != nil {
		return nil, fmt.Errorf("get image tags: %w", err)
	}
	defer rows.Close()

	tags := make([]string, 0)
	for rows.Next() {
		var t string
		if err := rows.Scan(&t); err != nil {
			return nil, err
		}
		tags = append(tags, t)
	}
	return tags, rows.Err()
}

// GetImageTagsWithCounts returns tags and their usage counts for a given image,
// ordered by most frequent first.
func (d *DB) GetImageTagsWithCounts(ctx context.Context, imageID int64) ([]Tag, error) {
	rows, err := d.db.QueryContext(ctx,
		`SELECT t.tag, t.usage_count
		 FROM tags t
		 JOIN image_tags it ON it.tag = t.tag
		 WHERE it.image_id = ?
		 ORDER BY t.usage_count DESC, t.tag`, imageID)
	if err != nil {
		return nil, fmt.Errorf("get image tags with counts: %w", err)
	}
	defer rows.Close()

	tags := make([]Tag, 0)
	for rows.Next() {
		var t Tag
		if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
			return nil, err
		}
		tags = append(tags, t)
	}
	return tags, rows.Err()
}

// SuggestTags returns tags matching a prefix, ordered by usage count descending.
func (d *DB) SuggestTags(ctx context.Context, prefix string, limit int) ([]Tag, error) {
	rows, err := d.db.QueryContext(ctx,
		`SELECT tag, usage_count FROM tags WHERE tag LIKE ? ORDER BY usage_count DESC LIMIT ?`,
		prefix+"%", limit)
	if err != nil {
		return nil, fmt.Errorf("suggest tags: %w", err)
	}
	defer rows.Close()

	tags := make([]Tag, 0)
	for rows.Next() {
		var t Tag
		if err := rows.Scan(&t.Tag, &t.UsageCount); err != nil {
			return nil, err
		}
		tags = append(tags, t)
	}
	return tags, rows.Err()
}

func (d *DB) incrementTagUsage(ctx context.Context, tag string) error {
	_, err := d.db.ExecContext(ctx,
		`INSERT INTO tags (tag, usage_count) VALUES (?, 1)
		 ON CONFLICT(tag) DO UPDATE SET usage_count = usage_count + 1`,
		tag)
	return err
}

func (d *DB) decrementTagUsage(ctx context.Context, tag string) error {
	_, err := d.db.ExecContext(ctx,
		`UPDATE tags SET usage_count = usage_count - 1 WHERE tag = ? AND usage_count > 0`,
		tag)
	if err != nil {
		return err
	}
	// Clean up tags with zero usage.
	_, err = d.db.ExecContext(ctx, `DELETE FROM tags WHERE tag = ? AND usage_count <= 0`, tag)
	return err
}

// AddImageTags adds tags to an image, skipping existing ones, and increments usage counts.
func (d *DB) AddImageTags(ctx context.Context, imageID int64, tags []string) error {
	for _, t := range tags {
		tag := normalizeTag(t)
		if tag == "" {
			continue
		}
		// Check if tag already exists for this image.
		var count int
		if err := d.db.QueryRowContext(ctx,
			`SELECT COUNT(*) FROM image_tags WHERE image_id = ? AND tag = ?`,
			imageID, tag).Scan(&count); err != nil {
			return fmt.Errorf("check existing tag: %w", err)
		}
		if count > 0 {
			continue
		}
		if _, err := d.db.ExecContext(ctx,
			`INSERT INTO image_tags (image_id, tag) VALUES (?, ?)`, imageID, tag,
		); err != nil {
			return fmt.Errorf("insert image tag: %w", err)
		}
		if err := d.incrementTagUsage(ctx, tag); err != nil {
			return err
		}
	}
	return nil
}

// RemoveImageTags removes specific tags from an image and decrements usage counts.
func (d *DB) RemoveImageTags(ctx context.Context, imageID int64, tags []string) error {
	for _, t := range tags {
		tag := normalizeTag(t)
		if tag == "" {
			continue
		}
		res, err := d.db.ExecContext(ctx,
			`DELETE FROM image_tags WHERE image_id = ? AND tag = ?`, imageID, tag)
		if err != nil {
			return fmt.Errorf("remove image tag: %w", err)
		}
		n, _ := res.RowsAffected()
		if n > 0 {
			if err := d.decrementTagUsage(ctx, tag); err != nil {
				return err
			}
		}
	}
	return nil
}

// normalizeTag trims whitespace, lowercases, and collapses internal whitespace.
func normalizeTag(tag string) string {
	return strings.Join(strings.Fields(strings.ToLower(strings.TrimSpace(tag))), " ")
}