package store

import (
	"context"
	"database/sql"
	"fmt"

	"git.nakama.town/fmartingr/hako/internal/model"
	"github.com/huandu/go-sqlbuilder"
)

// LinkStore handles database operations for links
type LinkStore struct {
	readDB  *sql.DB
	writeDB *sql.DB
}

// NewLinkStore creates a new LinkStore
func NewLinkStore(readDB, writeDB *sql.DB) *LinkStore {
	return &LinkStore{
		readDB:  readDB,
		writeDB: writeDB,
	}
}

// Create creates a new link
func (s *LinkStore) Create(ctx context.Context, link *model.Link) error {
	ib := sqlbuilder.NewInsertBuilder()
	ib.InsertInto("links")
	ib.Cols("id", "url", "user_id", "total_size", "created_at", "updated_at")
	ib.Values(link.ID, link.URL, link.UserID, link.TotalSize, link.CreatedAt.Format("2006-01-02 15:04:05"), link.UpdatedAt.Format("2006-01-02 15:04:05"))

	query, args := ib.Build()
	_, err := s.writeDB.ExecContext(ctx, query, args...)
	if err != nil {
		return fmt.Errorf("failed to create link: %w", err)
	}

	return nil
}

// GetByID retrieves a link by ID
func (s *LinkStore) GetByID(ctx context.Context, id string) (*model.Link, error) {
	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("id", "url", "user_id", "total_size", "created_at", "updated_at")
	sb.From("links")
	sb.Where(sb.Equal("id", id))

	query, args := sb.Build()
	row := s.readDB.QueryRowContext(ctx, query, args...)

	var link model.Link
	var createdAt, updatedAt string

	err := row.Scan(&link.ID, &link.URL, &link.UserID, &link.TotalSize, &createdAt, &updatedAt)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("link not found")
		}
		return nil, fmt.Errorf("failed to get link: %w", err)
	}

	// Parse timestamps
	link.CreatedAt, _ = parseTimestamp(createdAt)
	link.UpdatedAt, _ = parseTimestamp(updatedAt)

	return &link, nil
}

// GetByURL retrieves a link by URL
func (s *LinkStore) GetByURL(ctx context.Context, url string) (*model.Link, error) {
	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("id", "url", "user_id", "total_size", "created_at", "updated_at")
	sb.From("links")
	sb.Where(sb.Equal("url", url))

	query, args := sb.Build()
	row := s.readDB.QueryRowContext(ctx, query, args...)

	var link model.Link
	var createdAt, updatedAt string

	err := row.Scan(&link.ID, &link.URL, &link.UserID, &link.TotalSize, &createdAt, &updatedAt)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil // Not found is not an error
		}
		return nil, fmt.Errorf("failed to get link by URL: %w", err)
	}

	// Parse timestamps
	link.CreatedAt, _ = parseTimestamp(createdAt)
	link.UpdatedAt, _ = parseTimestamp(updatedAt)

	return &link, nil
}

// List retrieves links for a user with pagination
func (s *LinkStore) List(ctx context.Context, opts LinkListOptions) ([]*model.Link, error) {
	opts.Defaults()
	if err := opts.IsValid(); err != nil {
		return nil, fmt.Errorf("invalid options: %w", err)
	}

	// If search query is provided, use raw SQL with FTS5 support
	if opts.SearchQuery != "" {
		return s.listWithSearch(ctx, opts)
	}

	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("l.id", "l.url", "l.user_id", "l.total_size", "l.created_at", "l.updated_at")
	sb.From("links l")

	// Join with link_categories if filtering by category
	if opts.CategoryID != "" {
		sb.JoinWithOption(sqlbuilder.InnerJoin, "link_categories lc", "l.id = lc.link_id")
		sb.Where(sb.And(sb.Equal("l.user_id", opts.UserID), sb.Equal("lc.category_id", opts.CategoryID)))
	} else {
		sb.Where(sb.Equal("l.user_id", opts.UserID))
	}

	sb.OrderBy("l.created_at DESC")
	sb.Limit(opts.Limit)
	sb.Offset(opts.Offset)

	query, args := sb.Build()
	rows, err := s.readDB.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to list links: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var links []*model.Link
	for rows.Next() {
		var link model.Link
		var createdAt, updatedAt string

		if err := rows.Scan(&link.ID, &link.URL, &link.UserID, &link.TotalSize, &createdAt, &updatedAt); err != nil {
			return nil, fmt.Errorf("failed to scan link: %w", err)
		}

		// Parse timestamps
		link.CreatedAt, _ = parseTimestamp(createdAt)
		link.UpdatedAt, _ = parseTimestamp(updatedAt)

		links = append(links, &link)
	}

	return links, nil
}

// listWithSearch performs a search across URLs, titles, and content using FTS5
func (s *LinkStore) listWithSearch(ctx context.Context, opts LinkListOptions) ([]*model.Link, error) {
	builder := newSearchBuilder()
	builder.addCategoryFilter(opts.CategoryID)
	builder.addSearchFilter(opts.SearchQuery, opts.UserID)
	builder.addUserFilter(opts.UserID)
	builder.addLabelsFilter(opts.LabelIDs)

	query, args := builder.buildSelectQuery(opts.Limit, opts.Offset)

	rows, err := s.readDB.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to list links with search: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var links []*model.Link
	for rows.Next() {
		var link model.Link
		var createdAt, updatedAt string

		if err := rows.Scan(&link.ID, &link.URL, &link.UserID, &link.TotalSize, &createdAt, &updatedAt); err != nil {
			return nil, fmt.Errorf("failed to scan link: %w", err)
		}

		// Parse timestamps
		link.CreatedAt, _ = parseTimestamp(createdAt)
		link.UpdatedAt, _ = parseTimestamp(updatedAt)

		links = append(links, &link)
	}

	return links, nil
}

// Count returns the total number of links for a user
func (s *LinkStore) Count(ctx context.Context, opts LinkCountOptions) (int, error) {
	opts.Defaults()
	if err := opts.IsValid(); err != nil {
		return 0, fmt.Errorf("invalid options: %w", err)
	}

	// If search query is provided, use raw SQL with FTS5 support
	if opts.SearchQuery != "" {
		return s.countWithSearch(ctx, opts)
	}

	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("COUNT(DISTINCT l.id)")
	sb.From("links l")

	// Join with link_categories if filtering by category
	if opts.CategoryID != "" {
		sb.JoinWithOption(sqlbuilder.InnerJoin, "link_categories lc", "l.id = lc.link_id")
		sb.Where(sb.And(sb.Equal("l.user_id", opts.UserID), sb.Equal("lc.category_id", opts.CategoryID)))
	} else {
		sb.Where(sb.Equal("l.user_id", opts.UserID))
	}

	query, args := sb.Build()
	row := s.readDB.QueryRowContext(ctx, query, args...)

	var count int
	if err := row.Scan(&count); err != nil {
		return 0, fmt.Errorf("failed to count links: %w", err)
	}

	return count, nil
}

// countWithSearch counts links matching the search query
func (s *LinkStore) countWithSearch(ctx context.Context, opts LinkCountOptions) (int, error) {
	builder := newSearchBuilder()
	builder.addCategoryFilter(opts.CategoryID)
	builder.addSearchFilter(opts.SearchQuery, opts.UserID)
	builder.addUserFilter(opts.UserID)
	builder.addLabelsFilter(opts.LabelIDs)

	query, args := builder.buildCountQuery()

	row := s.readDB.QueryRowContext(ctx, query, args...)

	var count int
	if err := row.Scan(&count); err != nil {
		return 0, fmt.Errorf("failed to count links with search: %w", err)
	}

	return count, nil
}

// UpdateTotalSize updates the total_size field for a link
func (s *LinkStore) UpdateTotalSize(ctx context.Context, linkID string, totalSize int64) error {
	ub := sqlbuilder.NewUpdateBuilder()
	ub.Update("links")
	ub.Set(ub.Assign("total_size", totalSize))
	ub.Where(ub.Equal("id", linkID))

	query, args := ub.Build()
	_, err := s.writeDB.ExecContext(ctx, query, args...)
	if err != nil {
		return fmt.Errorf("failed to update total_size: %w", err)
	}

	return nil
}

// RecalculateTotalSize recalculates and updates the total_size for a link based on all archive files
func (s *LinkStore) RecalculateTotalSize(ctx context.Context, linkID string, archiveFileStore *ArchiveFileStore) error {
	totalSize, err := archiveFileStore.GetTotalSizeByLinkID(ctx, linkID)
	if err != nil {
		return fmt.Errorf("failed to calculate total size: %w", err)
	}

	if err := s.UpdateTotalSize(ctx, linkID, totalSize); err != nil {
		return fmt.Errorf("failed to update total size: %w", err)
	}

	return nil
}

// Delete deletes a link and its associated archives
func (s *LinkStore) Delete(ctx context.Context, id string) error {
	db := sqlbuilder.NewDeleteBuilder()
	db.DeleteFrom("links")
	db.Where(db.Equal("id", id))

	query, args := db.Build()
	_, err := s.writeDB.ExecContext(ctx, query, args...)
	if err != nil {
		return fmt.Errorf("failed to delete link: %w", err)
	}

	return nil
}