package repository

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

	"vidarchive/internal/models"
)

// downloadColumns is the canonical select list, kept in the order scanDownload
// expects so the two can't drift apart.
const downloadColumns = `id, url, status, logs, error_message, preset_id, format_override,
	custom_flags, output_dir, subscription_id, started_at, completed_at, created_at`

type DownloadRepository struct {
	db *sql.DB
}

func NewDownloadRepository(db *sql.DB) *DownloadRepository {
	return &DownloadRepository{db: db}
}

func (r *DownloadRepository) Create(d *models.Download) error {
	result, err := r.db.Exec(
		`INSERT INTO downloads (url, status, preset_id, format_override, custom_flags, output_dir, subscription_id)
		 VALUES (?, ?, ?, ?, ?, ?, ?)`,
		d.URL, d.Status, d.PresetID, d.FormatOverride, d.CustomFlags, d.OutputDir, d.SubscriptionID,
	)
	if err != nil {
		return err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return fmt.Errorf("download last insert id: %w", err)
	}
	d.ID = id
	return nil
}

func (r *DownloadRepository) GetByID(id int64) (*models.Download, error) {
	row := r.db.QueryRow(`SELECT `+downloadColumns+` FROM downloads WHERE id = ?`, id)
	return scanDownload(row)
}

func (r *DownloadRepository) GetAll(status, sortBy string) ([]*models.Download, error) {
	query := `SELECT ` + downloadColumns + ` FROM downloads WHERE 1=1`
	var args []interface{}

	if status != "" && status != "all" {
		query += ` AND status = ?`
		args = append(args, status)
	}

	switch sortBy {
	case "status":
		query += ` ORDER BY status, created_at DESC`
	default:
		query += ` ORDER BY created_at DESC`
	}

	return r.queryDownloads(query, args...)
}

func (r *DownloadRepository) GetQueued(limit int) ([]*models.Download, error) {
	return r.queryDownloads(
		`SELECT `+downloadColumns+` FROM downloads WHERE status = 'queued' ORDER BY created_at ASC LIMIT ?`,
		limit,
	)
}

func (r *DownloadRepository) Ping(ctx context.Context) error {
	var one int
	return r.db.QueryRowContext(ctx, `SELECT 1`).Scan(&one)
}

// IDsByStatus returns the ids of downloads in the given status.
func (r *DownloadRepository) IDsByStatus(status string) ([]int64, error) {
	rows, err := r.db.Query(`SELECT id FROM downloads WHERE status = ?`, status)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var ids []int64
	for rows.Next() {
		var id int64
		if err := rows.Scan(&id); err != nil {
			return nil, err
		}
		ids = append(ids, id)
	}
	return ids, rows.Err()
}

func (r *DownloadRepository) queryDownloads(query string, args ...interface{}) ([]*models.Download, error) {
	rows, err := r.db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var downloads []*models.Download
	for rows.Next() {
		d, err := scanDownload(rows)
		if err != nil {
			return nil, err
		}
		downloads = append(downloads, d)
	}
	return downloads, rows.Err()
}

func (r *DownloadRepository) AppendLogs(id int64, logs string) error {
	_, err := r.db.Exec(
		`UPDATE downloads SET logs = COALESCE(logs, '') || ? WHERE id = ?`,
		logs, id,
	)
	return err
}

// MarkStarted atomically transitions a download from 'queued' to 'downloading'.
// It reports whether this call actually claimed it: false means another worker
// already started it, so the caller must not process it again.
func (r *DownloadRepository) MarkStarted(id int64) (bool, error) {
	res, err := r.db.Exec(
		`UPDATE downloads SET status = 'downloading', started_at = CURRENT_TIMESTAMP WHERE id = ? AND status = 'queued'`,
		id,
	)
	if err != nil {
		return false, err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return false, err
	}
	return n > 0, nil
}

func (r *DownloadRepository) MarkCompleted(id int64, status string) error {
	_, err := r.db.Exec(
		`UPDATE downloads SET status = ?, completed_at = CURRENT_TIMESTAMP WHERE id = ?`,
		status, id,
	)
	return err
}

func (r *DownloadRepository) MarkError(id int64, errMsg string) error {
	_, err := r.db.Exec(
		`UPDATE downloads SET status = 'error', error_message = ? WHERE id = ?`,
		errMsg, id,
	)
	return err
}

// HasActiveForSubscription reports whether the given subscription already has a
// download that is queued or in progress, so the scheduler can avoid stacking a
// second run on top of one that hasn't finished.
func (r *DownloadRepository) HasActiveForSubscription(subID int64) (bool, error) {
	var n int
	err := r.db.QueryRow(
		`SELECT COUNT(*) FROM downloads WHERE subscription_id = ? AND status IN ('queued', 'downloading')`,
		subID,
	).Scan(&n)
	if err != nil {
		return false, err
	}
	return n > 0, nil
}

func (r *DownloadRepository) Delete(id int64) error {
	_, err := r.db.Exec(`DELETE FROM downloads WHERE id = ?`, id)
	return err
}

func (r *DownloadRepository) DeleteAll() error {
	_, err := r.db.Exec(`DELETE FROM downloads`)
	return err
}

func (r *DownloadRepository) UpdateStatusWhere(oldStatus, newStatus string) error {
	_, err := r.db.Exec(
		`UPDATE downloads SET status = ? WHERE status = ?`,
		newStatus, oldStatus,
	)
	return err
}

func scanDownload(row interface{ Scan(...interface{}) error }) (*models.Download, error) {
	var d models.Download
	err := row.Scan(
		&d.ID, &d.URL, &d.Status,
		&d.Logs, &d.ErrorMessage, &d.PresetID, &d.FormatOverride, &d.CustomFlags, &d.OutputDir, &d.SubscriptionID,
		&d.StartedAt, &d.CompletedAt, &d.CreatedAt,
	)
	if err != nil {
		return nil, err
	}
	return &d, nil
}