package db

import (
	"database/sql"
	"fmt"
	"time"

	"git.leaktechnologies.dev/stu/Goondex/internal/model"
)

// TagStore handles CRUD operations for tags
type TagStore struct {
	db *DB
}

// NewTagStore creates a new tag store
func NewTagStore(db *DB) *TagStore {
	return &TagStore{db: db}
}

// Create inserts a new tag
func (s *TagStore) Create(tag *model.Tag) error {
	now := time.Now()
	tag.CreatedAt = now
	tag.UpdatedAt = now

	result, err := s.db.conn.Exec(`
		INSERT INTO tags (name, category_id, aliases, description, source, source_id, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?)
	`, tag.Name, tag.CategoryID, tag.Aliases, tag.Description, tag.Source, tag.SourceID, tag.CreatedAt.Format(time.RFC3339), tag.UpdatedAt.Format(time.RFC3339))

	if err != nil {
		return fmt.Errorf("failed to create tag: %w", err)
	}

	id, err := result.LastInsertId()
	if err != nil {
		return fmt.Errorf("failed to get last insert id: %w", err)
	}

	tag.ID = id
	return nil
}

// GetByID retrieves a tag by ID
func (s *TagStore) GetByID(id int64) (*model.Tag, error) {
	tag := &model.Tag{}
	var createdAt, updatedAt string

	err := s.db.conn.QueryRow(`
		SELECT id, name, category_id, COALESCE(aliases, ''), COALESCE(description, ''), COALESCE(source, ''), COALESCE(source_id, ''), created_at, updated_at
		FROM tags WHERE id = ?
	`, id).Scan(&tag.ID, &tag.Name, &tag.CategoryID, &tag.Aliases, &tag.Description, &tag.Source, &tag.SourceID, &createdAt, &updatedAt)

	if err == sql.ErrNoRows {
		return nil, fmt.Errorf("tag not found")
	}
	if err != nil {
		return nil, fmt.Errorf("failed to get tag: %w", err)
	}

	tag.CreatedAt, _ = time.Parse(time.RFC3339, createdAt)
	tag.UpdatedAt, _ = time.Parse(time.RFC3339, updatedAt)

	return tag, nil
}

// GetByName retrieves a tag by name
func (s *TagStore) GetByName(name string) (*model.Tag, error) {
	tag := &model.Tag{}
	var createdAt, updatedAt string

	err := s.db.conn.QueryRow(`
		SELECT id, name, category_id, COALESCE(aliases, ''), COALESCE(description, ''), COALESCE(source, ''), COALESCE(source_id, ''), created_at, updated_at
		FROM tags WHERE name = ?
	`, name).Scan(&tag.ID, &tag.Name, &tag.CategoryID, &tag.Aliases, &tag.Description, &tag.Source, &tag.SourceID, &createdAt, &updatedAt)

	if err == sql.ErrNoRows {
		return nil, fmt.Errorf("tag not found")
	}
	if err != nil {
		return nil, fmt.Errorf("failed to get tag: %w", err)
	}

	tag.CreatedAt, _ = time.Parse(time.RFC3339, createdAt)
	tag.UpdatedAt, _ = time.Parse(time.RFC3339, updatedAt)

	return tag, nil
}

// Search searches for tags by name
func (s *TagStore) Search(query string) ([]model.Tag, error) {
	rows, err := s.db.conn.Query(`
		SELECT id, name, category_id, COALESCE(aliases, ''), COALESCE(description, ''), COALESCE(source, ''), COALESCE(source_id, ''), created_at, updated_at
		FROM tags
		WHERE name LIKE ? OR COALESCE(aliases, '') LIKE ?
		ORDER BY name
	`, "%"+query+"%", "%"+query+"%")

	if err != nil {
		return nil, fmt.Errorf("failed to search tags: %w", err)
	}
	defer rows.Close()

	var tags []model.Tag
	for rows.Next() {
		var tag model.Tag
		var createdAt, updatedAt string

		err := rows.Scan(&tag.ID, &tag.Name, &tag.CategoryID, &tag.Aliases, &tag.Description, &tag.Source, &tag.SourceID, &createdAt, &updatedAt)
		if err != nil {
			return nil, fmt.Errorf("failed to scan tag: %w", err)
		}

		tag.CreatedAt, _ = time.Parse(time.RFC3339, createdAt)
		tag.UpdatedAt, _ = time.Parse(time.RFC3339, updatedAt)

		tags = append(tags, tag)
	}

	return tags, nil
}

// Update updates an existing tag
func (s *TagStore) Update(tag *model.Tag) error {
	tag.UpdatedAt = time.Now()

	result, err := s.db.conn.Exec(`
		UPDATE tags
		SET name = ?, category_id = ?, aliases = ?, description = ?, source = ?, source_id = ?, updated_at = ?
		WHERE id = ?
	`, tag.Name, tag.CategoryID, tag.Aliases, tag.Description, tag.Source, tag.SourceID, tag.UpdatedAt.Format(time.RFC3339), tag.ID)

	if err != nil {
		return fmt.Errorf("failed to update tag: %w", err)
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %w", err)
	}

	if rows == 0 {
		return fmt.Errorf("tag not found")
	}

	return nil
}

// Delete deletes a tag by ID
func (s *TagStore) Delete(id int64) error {
	result, err := s.db.conn.Exec("DELETE FROM tags WHERE id = ?", id)
	if err != nil {
		return fmt.Errorf("failed to delete tag: %w", err)
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %w", err)
	}

	if rows == 0 {
		return fmt.Errorf("tag not found")
	}

	return nil
}

// Upsert inserts or updates a tag based on source_id
func (s *TagStore) Upsert(tag *model.Tag) error {
	// Try to find existing tag by source_id
	existing, err := s.GetBySourceID(tag.Source, tag.SourceID)
	if err == nil && existing != nil {
		// Update existing
		tag.ID = existing.ID
		return s.Update(tag)
	}
	// Create new
	return s.Create(tag)
}

// GetBySourceID retrieves a tag by its source and source_id
func (s *TagStore) GetBySourceID(source, sourceID string) (*model.Tag, error) {
	var tag model.Tag
	var createdAt, updatedAt string

	err := s.db.conn.QueryRow(`
		SELECT id, name, category_id, COALESCE(aliases, ''), COALESCE(description, ''),
			COALESCE(source, ''), COALESCE(source_id, ''),
			created_at, updated_at
		FROM tags
		WHERE source = ? AND source_id = ?
	`, source, sourceID).Scan(
		&tag.ID, &tag.Name, &tag.CategoryID, &tag.Aliases, &tag.Description,
		&tag.Source, &tag.SourceID,
		&createdAt, &updatedAt,
	)

	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("failed to get tag: %w", err)
	}

	tag.CreatedAt, _ = time.Parse(time.RFC3339, createdAt)
	tag.UpdatedAt, _ = time.Parse(time.RFC3339, updatedAt)

	return &tag, nil
}
	
// FindOrCreate finds a tag by name and category, creating it if it doesn't exist
func (s *TagStore) FindOrCreate(tagName string, categoryID int64, source string) (*model.Tag, error) {
	// Try to find existing tag
	tag, err := s.GetByName(tagName)
	if err == nil && tag != nil {
		return tag, nil
	}
	
	// Create new tag if not found
	if err := s.Create(&model.Tag{
		Name:        tagName,
		CategoryID:  categoryID,
		Source:      source,
		CreatedAt:   time.Now(),
		UpdatedAt:   time.Now(),
	}); err != nil {
		return nil, fmt.Errorf("failed to create tag %s: %w", tagName, err)
	}
	
	createdTag := &model.Tag{
		Name:        tagName,
		CategoryID:  categoryID,
		Source:      source,
		CreatedAt:   time.Now(),
		UpdatedAt:   time.Now(),
	}
	
	if err := s.Create(createdTag); err != nil {
		return nil, fmt.Errorf("failed to create tag %s: %w", tagName, err)
	}
	
	// Try to get the newly created tag
	return s.GetByName(tagName)
}
	
	// Create new tag if not found
	newTag := &model.Tag{
		Name:        tagName,
		CategoryID:  categoryID,
		Source:      source,
		CreatedAt:   time.Now(),
		UpdatedAt:   time.Now(),
	}
	
	if err := s.Create(newTag); err != nil {
		return nil, fmt.Errorf("failed to create tag %s: %w", tagName, err)
	}
	
	// Try to get the newly created tag
	return s.GetByName(tagName)
}

	// Create new tag if not found
	newTag := &model.Tag{
		Name:       name,
		CategoryID: categoryID,
		Source:     source,
		CreatedAt:  time.Now(),
		UpdatedAt:  time.Now(),
	}

	if err := s.Create(newTag); err != nil {
		return nil, fmt.Errorf("failed to create tag %s: %w", name, err)
	}

	// Try to get the newly created tag
	return s.GetByName(name)
}

// FindOrCreate finds a tag by name and category, creating it if it doesn't exist
func (s *TagStore) FindOrCreate(tagName string, categoryID int64, source string) (*model.Tag, error) {
	// Try to find existing tag
	tag, err := s.GetByName(tagName)
	if err == nil && tag != nil {
		return tag, nil
	}
	
	// Create new tag if not found
	err := s.Create(newTag)
	if err != nil {
		return nil, fmt.Errorf("failed to create tag %s: %w", tagName, err)
	}
	
	newTag := &model.Tag{
		Name:        tagName,
		CategoryID:  categoryID,
		Source:      source,
		CreatedAt:   time.Now(),
		UpdatedAt:   time.Now(),
	}
	
	if err := s.Create(newTag); err != nil {
		return nil, fmt.Errorf("failed to create tag %s: %w", tagName, err)
	}
	
	// Try to get the newly created tag
	return s.GetByName(tagName)
}

// FindOrCreate finds a tag by name, creating it if it doesn't exist
func (s *TagStore) FindOrCreate(tagName, categoryID int64, source string) (*model.Tag, error) {
	// Try to find existing tag
	tag, err := s.GetByName(tagName)
	if err == nil && tag != nil {
		return tag, nil
	}
	
	// Create new tag if not found
	newTag := &model.Tag{
		Name:        tagName,
		CategoryID:  categoryID,
		Source:      source,
		CreatedAt:   time.Now(),
		UpdatedAt:   time.Now(),
	}
	
	if err := s.Create(newTag); err != nil {
		return nil, fmt.Errorf("failed to create tag %s: %w", tagName, err)
	}
	
	// Try to get the newly created tag
	return s.GetByName(tagName)
}
