package tracker

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

type Status string

const (
	StatusQueued    Status = "Queued"
	StatusEvaluated Status = "Evaluated"
	StatusApplied   Status = "Applied"
	StatusResponded Status = "Responded"
	StatusContact   Status = "Contact"
	StatusInterview Status = "Interview"
	StatusOffer     Status = "Offer"
	StatusRejected  Status = "Rejected"
	StatusDiscarded Status = "Discarded"
	StatusSkip      Status = "SKIP"
)

var AllStatuses = []Status{
	StatusQueued, StatusEvaluated, StatusApplied, StatusResponded, StatusContact,
	StatusInterview, StatusOffer, StatusRejected, StatusDiscarded, StatusSkip,
}

// Terminal states accept no further moves; re-applying after a rejection
// requires a brand-new application row.
var transitions = map[Status][]Status{
	StatusQueued:    {StatusEvaluated, StatusDiscarded},
	StatusEvaluated: {StatusApplied, StatusDiscarded, StatusSkip},
	StatusApplied:   {StatusResponded, StatusContact, StatusInterview, StatusRejected, StatusDiscarded},
	StatusResponded: {StatusContact, StatusInterview, StatusRejected, StatusDiscarded},
	StatusContact:   {StatusResponded, StatusInterview, StatusRejected, StatusDiscarded},
	StatusInterview: {StatusOffer, StatusRejected, StatusDiscarded},
}

var ErrInvalidTransition = errors.New("invalid status transition")
var ErrRateLimited = errors.New("daily application rate limit reached")

func CanAdvance(from, to Status) bool {
	if from == to {
		return false
	}
	for _, s := range transitions[from] {
		if s == to {
			return true
		}
	}
	return false
}

func AllowedNext(from Status) []Status {
	out := append([]Status{}, transitions[from]...)
	return out
}

type Manager struct {
	db          *sql.DB
	dailyCap    int
	now         func() time.Time
	followUpDur time.Duration
}

// NewManager — dailyCap is the max number of transitions to StatusApplied
// within the last 24h (0 disables the cap). followUpDur is how long after
// Applied to schedule the default follow-up (0 → 7d).
func NewManager(db *sql.DB, dailyCap int, followUpDur time.Duration) *Manager {
	if followUpDur <= 0 {
		followUpDur = 7 * 24 * time.Hour
	}
	return &Manager{db: db, dailyCap: dailyCap, now: time.Now, followUpDur: followUpDur}
}

type Application struct {
	ID         int64
	JobID      int64
	Status     Status
	Score      sql.NullFloat64
	Company    string
	Title      string
	URL        string
	AppliedAt  sql.NullTime
	UpdatedAt  sql.NullTime
	Legitimacy sql.NullString
	Notes      sql.NullString
}

type Event struct {
	ID         int64
	From       sql.NullString
	To         Status
	Note       sql.NullString
	OccurredAt time.Time
}

func (m *Manager) List(statusFilter Status) ([]Application, error) {
	q := `
		SELECT a.id, a.job_id, a.status, a.score,
		       COALESCE(j.company, ''), COALESCE(j.title, ''), COALESCE(j.url, ''),
		       a.applied_at, a.updated_at, a.legitimacy, a.notes
		FROM applications a
		LEFT JOIN jobs j ON j.id = a.job_id
	`
	args := []any{}
	if statusFilter != "" {
		q += "WHERE a.status = ? "
		args = append(args, string(statusFilter))
	}
	q += "ORDER BY COALESCE(a.updated_at, a.created_at) DESC"
	rows, err := m.db.Query(q, args...)
	if err != nil {
		return nil, fmt.Errorf("list applications: %w", err)
	}
	defer rows.Close()
	var out []Application
	for rows.Next() {
		var a Application
		var status string
		if err := rows.Scan(&a.ID, &a.JobID, &status, &a.Score,
			&a.Company, &a.Title, &a.URL, &a.AppliedAt, &a.UpdatedAt,
			&a.Legitimacy, &a.Notes); err != nil {
			return nil, err
		}
		a.Status = Status(status)
		out = append(out, a)
	}
	return out, rows.Err()
}

func (m *Manager) Get(id int64) (Application, error) {
	row := m.db.QueryRow(`
		SELECT a.id, a.job_id, a.status, a.score,
		       COALESCE(j.company, ''), COALESCE(j.title, ''), COALESCE(j.url, ''),
		       a.applied_at, a.updated_at, a.legitimacy, a.notes
		FROM applications a
		LEFT JOIN jobs j ON j.id = a.job_id
		WHERE a.id = ?`, id)
	var a Application
	var status string
	if err := row.Scan(&a.ID, &a.JobID, &status, &a.Score,
		&a.Company, &a.Title, &a.URL, &a.AppliedAt, &a.UpdatedAt,
		&a.Legitimacy, &a.Notes); err != nil {
		return Application{}, fmt.Errorf("get application %d: %w", id, err)
	}
	a.Status = Status(status)
	return a, nil
}

func (m *Manager) Events(applicationID int64) ([]Event, error) {
	rows, err := m.db.Query(`
		SELECT id, from_status, to_status, note, occurred_at
		FROM tracker_events WHERE application_id = ?
		ORDER BY occurred_at DESC`, applicationID)
	if err != nil {
		return nil, fmt.Errorf("events: %w", err)
	}
	defer rows.Close()
	var out []Event
	for rows.Next() {
		var e Event
		var to string
		if err := rows.Scan(&e.ID, &e.From, &to, &e.Note, &e.OccurredAt); err != nil {
			return nil, err
		}
		e.To = Status(to)
		out = append(out, e)
	}
	return out, rows.Err()
}

func (m *Manager) Advance(applicationID int64, to Status, note string) error {
	app, err := m.Get(applicationID)
	if err != nil {
		return err
	}
	if !CanAdvance(app.Status, to) {
		return fmt.Errorf("%w: %s -> %s", ErrInvalidTransition, app.Status, to)
	}
	if to == StatusApplied && m.dailyCap > 0 {
		used, err := m.appliedToday()
		if err != nil {
			return err
		}
		if used >= m.dailyCap {
			return fmt.Errorf("%w: %d/%d used in last 24h", ErrRateLimited, used, m.dailyCap)
		}
	}

	tx, err := m.db.Begin()
	if err != nil {
		return fmt.Errorf("begin tx: %w", err)
	}
	defer tx.Rollback()

	now := m.now().UTC()
	if to == StatusApplied {
		if _, err := tx.Exec(`
			UPDATE applications SET status = ?, applied_at = ?, updated_at = ?
			WHERE id = ?`, string(to), now, now, applicationID); err != nil {
			return fmt.Errorf("update application: %w", err)
		}
		if _, err := tx.Exec(`
			INSERT INTO follow_ups (application_id, follow_up_at, notes)
			VALUES (?, ?, ?)`, applicationID, now.Add(m.followUpDur), "auto: 7d after apply"); err != nil {
			return fmt.Errorf("schedule follow_up: %w", err)
		}
	} else {
		if _, err := tx.Exec(`
			UPDATE applications SET status = ?, updated_at = ?
			WHERE id = ?`, string(to), now, applicationID); err != nil {
			return fmt.Errorf("update application: %w", err)
		}
	}
	if _, err := tx.Exec(`
		INSERT INTO tracker_events (application_id, from_status, to_status, note, occurred_at)
		VALUES (?, ?, ?, ?, ?)`,
		applicationID, string(app.Status), string(to), nullableNote(note), now); err != nil {
		return fmt.Errorf("insert event: %w", err)
	}
	return tx.Commit()
}

func (m *Manager) AddNote(applicationID int64, note string) error {
	app, err := m.Get(applicationID)
	if err != nil {
		return err
	}
	now := m.now().UTC()
	_, err = m.db.Exec(`
		INSERT INTO tracker_events (application_id, from_status, to_status, note, occurred_at)
		VALUES (?, ?, ?, ?, ?)`,
		applicationID, string(app.Status), string(app.Status), note, now)
	if err != nil {
		return fmt.Errorf("insert note: %w", err)
	}
	return nil
}

func (m *Manager) AppliedToday() (int, error) {
	return m.appliedToday()
}

func (m *Manager) appliedToday() (int, error) {
	cutoff := m.now().UTC().Add(-24 * time.Hour)
	var n int
	err := m.db.QueryRow(`
		SELECT COUNT(*) FROM tracker_events
		WHERE to_status = ? AND occurred_at >= ?`,
		string(StatusApplied), cutoff).Scan(&n)
	return n, err
}

type PendingFollowUp struct {
	ID            int64
	ApplicationID int64
	Company       string
	Title         string
	DueAt         time.Time
	Notes         sql.NullString
}

func (m *Manager) PendingFollowUps() ([]PendingFollowUp, error) {
	rows, err := m.db.Query(`
		SELECT f.id, f.application_id, COALESCE(j.company,''), COALESCE(j.title,''),
		       f.follow_up_at, f.notes
		FROM follow_ups f
		JOIN applications a ON a.id = f.application_id
		LEFT JOIN jobs j ON j.id = a.job_id
		WHERE f.contacted_at IS NULL AND f.follow_up_at <= ?
		ORDER BY f.follow_up_at ASC`, m.now().UTC())
	if err != nil {
		return nil, fmt.Errorf("pending follow_ups: %w", err)
	}
	defer rows.Close()
	var out []PendingFollowUp
	for rows.Next() {
		var f PendingFollowUp
		if err := rows.Scan(&f.ID, &f.ApplicationID, &f.Company, &f.Title, &f.DueAt, &f.Notes); err != nil {
			return nil, err
		}
		out = append(out, f)
	}
	return out, rows.Err()
}

func (m *Manager) MarkFollowUpContacted(followUpID int64) error {
	_, err := m.db.Exec(`UPDATE follow_ups SET contacted_at = ? WHERE id = ?`,
		m.now().UTC(), followUpID)
	return err
}

type Stats struct {
	Total          int
	ByStatus       map[Status]int
	AppliedLast24h int
	OpenFollowUps  int
}

func (m *Manager) Stats() (Stats, error) {
	s := Stats{ByStatus: make(map[Status]int)}
	rows, err := m.db.Query(`SELECT status, COUNT(*) FROM applications GROUP BY status`)
	if err != nil {
		return s, fmt.Errorf("stats: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var status string
		var n int
		if err := rows.Scan(&status, &n); err != nil {
			return s, err
		}
		s.ByStatus[Status(status)] = n
		s.Total += n
	}
	if n, err := m.appliedToday(); err == nil {
		s.AppliedLast24h = n
	}
	_ = m.db.QueryRow(`
		SELECT COUNT(*) FROM follow_ups
		WHERE contacted_at IS NULL AND follow_up_at <= ?`, m.now().UTC()).Scan(&s.OpenFollowUps)
	return s, nil
}

func nullableNote(s string) any {
	if s == "" {
		return nil
	}
	return s
}