package store

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

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

// ArchiveStore handles database operations for archives
type ArchiveStore struct {
	readDB  *sql.DB
	writeDB *sql.DB
}

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

// Create creates a new archive
func (s *ArchiveStore) Create(ctx context.Context, archive *model.Archive) error {
	ib := sqlbuilder.NewInsertBuilder()
	ib.InsertInto("archives")
	ib.Cols("id", "link_id", "user_id", "status", "title", "error_message", "created_at")
	ib.Values(archive.ID, archive.LinkID, archive.UserID, string(archive.Status), archive.Title, archive.ErrorMessage, archive.CreatedAt.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 archive: %w", err)
	}

	return nil
}

// GetByID retrieves an archive by ID
func (s *ArchiveStore) GetByID(ctx context.Context, id string) (*model.Archive, error) {
	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("id", "link_id", "user_id", "status", "title", "error_message", "created_at", "completed_at")
	sb.From("archives")
	sb.Where(sb.Equal("id", id))

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

	var archive model.Archive
	var createdAt string
	var completedAt sql.NullString

	err := row.Scan(&archive.ID, &archive.LinkID, &archive.UserID, &archive.Status, &archive.Title, &archive.ErrorMessage, &createdAt, &completedAt)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("archive not found")
		}
		return nil, fmt.Errorf("failed to get archive: %w", err)
	}

	// Parse timestamps
	archive.CreatedAt, _ = parseTimestamp(createdAt)
	if completedAt.Valid {
		t, _ := parseTimestamp(completedAt.String)
		archive.CompletedAt = &t
	}

	return &archive, nil
}

// ListByLinkID retrieves all archives for a link
func (s *ArchiveStore) ListByLinkID(ctx context.Context, opts ArchiveListOptions) ([]*model.Archive, error) {
	opts.Defaults()
	if err := opts.IsValid(); err != nil {
		return nil, fmt.Errorf("invalid options: %w", err)
	}

	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("id", "link_id", "user_id", "status", "title", "error_message", "created_at", "completed_at")
	sb.From("archives")
	sb.Where(sb.Equal("link_id", opts.LinkID))
	sb.OrderBy("created_at DESC")

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

	var archives []*model.Archive
	for rows.Next() {
		var archive model.Archive
		var createdAt string
		var completedAt sql.NullString

		if err := rows.Scan(&archive.ID, &archive.LinkID, &archive.UserID, &archive.Status, &archive.Title, &archive.ErrorMessage, &createdAt, &completedAt); err != nil {
			return nil, fmt.Errorf("failed to scan archive: %w", err)
		}

		// Parse timestamps
		archive.CreatedAt, _ = parseTimestamp(createdAt)
		if completedAt.Valid {
			t, _ := parseTimestamp(completedAt.String)
			archive.CompletedAt = &t
		}

		archives = append(archives, &archive)
	}

	return archives, nil
}

// UpdateStatus updates the status of an archive
func (s *ArchiveStore) UpdateStatus(ctx context.Context, id string, status model.ArchiveStatus, errorMsg string) error {
	ub := sqlbuilder.NewUpdateBuilder()
	ub.Update("archives")

	// Build the assignments
	assignments := []string{
		ub.Assign("status", string(status)),
		ub.Assign("error_message", errorMsg),
	}

	// If status is completed, failed, or partial, set completed_at
	if status == model.ArchiveStatusCompleted || status == model.ArchiveStatusFailed || status == model.ArchiveStatusPartial {
		assignments = append(assignments, ub.Assign("completed_at", time.Now().Format("2006-01-02 15:04:05")))
	}

	ub.Set(assignments...)
	ub.Where(ub.Equal("id", id))

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

	return nil
}

// UpdateTitle updates the title of an archive
func (s *ArchiveStore) UpdateTitle(ctx context.Context, id string, title string) error {
	ub := sqlbuilder.NewUpdateBuilder()
	ub.Update("archives")
	ub.Set(ub.Assign("title", title))
	ub.Where(ub.Equal("id", id))

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

	return nil
}

// GetLatestByLinkID retrieves the latest archive for a link
func (s *ArchiveStore) GetLatestByLinkID(ctx context.Context, linkID string) (*model.Archive, error) {
	sb := sqlbuilder.NewSelectBuilder()
	sb.Select("id", "link_id", "user_id", "status", "title", "error_message", "created_at", "completed_at")
	sb.From("archives")
	sb.Where(sb.Equal("link_id", linkID))
	sb.OrderBy("created_at DESC")
	sb.Limit(1)

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

	var archive model.Archive
	var createdAt string
	var completedAt sql.NullString

	err := row.Scan(&archive.ID, &archive.LinkID, &archive.UserID, &archive.Status, &archive.Title, &archive.ErrorMessage, &createdAt, &completedAt)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil // No archive found is not an error
		}
		return nil, fmt.Errorf("failed to get latest archive: %w", err)
	}

	// Parse timestamps
	archive.CreatedAt, _ = parseTimestamp(createdAt)
	if completedAt.Valid {
		t, _ := parseTimestamp(completedAt.String)
		archive.CompletedAt = &t
	}

	return &archive, nil
}

// Delete deletes a single archive by ID
func (s *ArchiveStore) Delete(ctx context.Context, id string) error {
	db := sqlbuilder.NewDeleteBuilder()
	db.DeleteFrom("archives")
	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 archive: %w", err)
	}

	return nil
}

// DeleteByLinkID deletes all archives for a link
func (s *ArchiveStore) DeleteByLinkID(ctx context.Context, linkID string) error {
	db := sqlbuilder.NewDeleteBuilder()
	db.DeleteFrom("archives")
	db.Where(db.Equal("link_id", linkID))

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

	return nil
}