package eval

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"os"
	"path/filepath"
	"regexp"
	"strings"
	"time"
)

// slugRE strips anything outside [a-z0-9-] after lowercasing + dash replacement.
// Prevents path traversal and filesystem-unsafe characters from company names.
var slugRE = regexp.MustCompile(`[^a-z0-9-]+`)

// collapseDashRE collapses runs of dashes so "Acme  &  Co" → "acme-co" not "acme---co".
var collapseDashRE = regexp.MustCompile(`-+`)

// Slugify produces a filesystem-safe slug from a company or role name.
// Exported for reuse by any caller that needs to construct report paths.
func Slugify(s string) string {
	s = strings.ToLower(strings.TrimSpace(s))
	s = strings.ReplaceAll(s, " ", "-")
	s = strings.ReplaceAll(s, "_", "-")
	s = slugRE.ReplaceAllString(s, "")
	s = collapseDashRE.ReplaceAllString(s, "-")
	s = strings.Trim(s, "-")
	if s == "" {
		return "unknown"
	}
	if len(s) > 64 {
		s = s[:64]
	}
	return s
}

// Persistence is the shape ReportWriter needs from the DB layer. Declared as
// an interface so tests can stub without a real SQLite instance.
type Persistence interface {
	BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)
}

// ReportWriter renders Evaluation → markdown + persists to disk and SQLite.
// ReportsDir defaults to $HOME/.apex/reports if empty at Save time.
type ReportWriter struct {
	DB         Persistence
	ReportsDir string
}

// Saved describes everything needed to reference a persisted report from the
// TUI or follow-up queries.
type Saved struct {
	Seq           int
	FilePath      string
	ApplicationID int64
	ReportID      int64
}

// Save persists an Evaluation end-to-end:
//  1. Upsert the jobs row (URL unique key).
//  2. Insert/update the applications row (one per job).
//  3. Allocate the next sequential seq for the reports row.
//  4. Render markdown to $REPORTS_DIR/{seq}-{slug}-{YYYY-MM-DD}.md.
//  5. Insert the reports row with file_path + per-block columns.
//
// The filesystem write happens inside the DB transaction so a mid-way failure
// leaves neither partial disk artifact nor orphan DB row. Any pre-existing
// file at the target path is replaced atomically via temp+rename.
func (w *ReportWriter) Save(ctx context.Context, job JobContext, eval Evaluation) (Saved, error) {
	if w.DB == nil {
		return Saved{}, fmt.Errorf("report writer: nil DB")
	}
	dir := w.ReportsDir
	if dir == "" {
		home, err := os.UserHomeDir()
		if err != nil {
			return Saved{}, fmt.Errorf("resolve home: %w", err)
		}
		dir = filepath.Join(home, ".apex", "reports")
	}
	dir = filepath.Clean(dir)
	if err := os.MkdirAll(dir, 0o755); err != nil {
		return Saved{}, fmt.Errorf("mkdir reports: %w", err)
	}

	tx, err := w.DB.BeginTx(ctx, nil)
	if err != nil {
		return Saved{}, fmt.Errorf("begin tx: %w", err)
	}
	committed := false
	defer func() {
		if !committed {
			_ = tx.Rollback()
		}
	}()

	jobID, err := upsertJob(ctx, tx, job)
	if err != nil {
		return Saved{}, err
	}

	appID, err := upsertApplication(ctx, tx, jobID, eval)
	if err != nil {
		return Saved{}, err
	}

	seq, err := nextReportSeq(ctx, tx)
	if err != nil {
		return Saved{}, err
	}

	slug := Slugify(job.Company)
	date := time.Now().Format("2006-01-02")
	filename := fmt.Sprintf("%03d-%s-%s.md", seq, slug, date)
	fullPath := filepath.Join(dir, filename)

	md := RenderMarkdown(job, eval, seq, date)
	if err := atomicWrite(fullPath, []byte(md)); err != nil {
		return Saved{}, fmt.Errorf("write report file: %w", err)
	}

	reportID, err := insertReport(ctx, tx, appID, seq, slug, fullPath, md, eval)
	if err != nil {
		// Best-effort cleanup of the disk artifact so we don't orphan it.
		_ = os.Remove(fullPath)
		return Saved{}, err
	}

	if err := tx.Commit(); err != nil {
		_ = os.Remove(fullPath)
		return Saved{}, fmt.Errorf("commit: %w", err)
	}
	committed = true

	return Saved{Seq: seq, FilePath: fullPath, ApplicationID: appID, ReportID: reportID}, nil
}

// upsertJob ensures a row exists in jobs for this URL. Returns its id.
// Uses INSERT ... ON CONFLICT(url) DO UPDATE to keep title/description fresh.
func upsertJob(ctx context.Context, tx *sql.Tx, job JobContext) (int64, error) {
	_, err := tx.ExecContext(ctx, `
		INSERT INTO jobs (url, company, title, description, source)
		VALUES (?, ?, ?, ?, ?)
		ON CONFLICT(url) DO UPDATE SET
			company = excluded.company,
			title = excluded.title,
			description = COALESCE(NULLIF(excluded.description, ''), jobs.description)
	`, job.URL, job.Company, job.Title, job.JDText, job.Source)
	if err != nil {
		return 0, fmt.Errorf("upsert job: %w", err)
	}
	var id int64
	if err := tx.QueryRowContext(ctx, `SELECT id FROM jobs WHERE url = ?`, job.URL).Scan(&id); err != nil {
		return 0, fmt.Errorf("lookup job id: %w", err)
	}
	return id, nil
}

// upsertApplication inserts or updates the single applications row for a job.
// Phase 3 marks the status Evaluated; later phases can transition it.
func upsertApplication(ctx context.Context, tx *sql.Tx, jobID int64, eval Evaluation) (int64, error) {
	var id int64
	err := tx.QueryRowContext(ctx, `SELECT id FROM applications WHERE job_id = ?`, jobID).Scan(&id)
	if err == sql.ErrNoRows {
		res, ierr := tx.ExecContext(ctx, `
			INSERT INTO applications (job_id, status, score, archetype, legitimacy, evaluated_at)
			VALUES (?, 'Evaluated', ?, ?, ?, CURRENT_TIMESTAMP)
		`, jobID, eval.Score, eval.Archetype, string(eval.Legitimacy))
		if ierr != nil {
			return 0, fmt.Errorf("insert application: %w", ierr)
		}
		newID, ierr := res.LastInsertId()
		if ierr != nil {
			return 0, fmt.Errorf("last insert id: %w", ierr)
		}
		return newID, nil
	}
	if err != nil {
		return 0, fmt.Errorf("lookup application: %w", err)
	}
	if _, err := tx.ExecContext(ctx, `
		UPDATE applications
		SET status = CASE WHEN status IN ('Applied','Responded','Interview','Offer','Rejected','Discarded','SKIP')
		              THEN status ELSE 'Evaluated' END,
		    score = ?, archetype = ?, legitimacy = ?, evaluated_at = CURRENT_TIMESTAMP,
		    updated_at = CURRENT_TIMESTAMP
		WHERE id = ?
	`, eval.Score, eval.Archetype, string(eval.Legitimacy), id); err != nil {
		return 0, fmt.Errorf("update application: %w", err)
	}
	return id, nil
}

// nextReportSeq returns max(seq)+1, or 1 if no reports exist yet. Runs inside
// the same tx as the insert so two concurrent evals cannot collide (SQLite
// serializes writers via the one-conn pool configured in store/db.go).
func nextReportSeq(ctx context.Context, tx *sql.Tx) (int, error) {
	var max sql.NullInt64
	if err := tx.QueryRowContext(ctx, `SELECT MAX(seq) FROM reports`).Scan(&max); err != nil {
		return 0, fmt.Errorf("max seq: %w", err)
	}
	if !max.Valid {
		return 1, nil
	}
	return int(max.Int64) + 1, nil
}

func insertReport(ctx context.Context, tx *sql.Tx, appID int64, seq int, slug, path, md string, eval Evaluation) (int64, error) {
	var storiesJSON *string
	if len(eval.Stories) > 0 {
		b, err := json.Marshal(eval.Stories)
		if err != nil {
			return 0, fmt.Errorf("marshal stories: %w", err)
		}
		s := string(b)
		storiesJSON = &s
	}

	res, err := tx.ExecContext(ctx, `
		INSERT INTO reports (
			application_id, report_md, seq, slug, legitimacy, score, file_path,
			block_a, block_b, block_c, block_d, block_e, block_f, block_g, stories
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	`, appID, md, seq, slug, string(eval.Legitimacy), eval.Score, path,
		eval.BlockA, eval.BlockB, eval.BlockC, eval.BlockD, eval.BlockE, eval.BlockF, eval.BlockG, storiesJSON)
	if err != nil {
		return 0, fmt.Errorf("insert report: %w", err)
	}
	return res.LastInsertId()
}

// atomicWrite writes to a sibling temp file and renames into place so a crash
// mid-write never leaves a half-written report on disk.
func atomicWrite(path string, data []byte) error {
	dir := filepath.Dir(path)
	tmp, err := os.CreateTemp(dir, ".report-*.tmp")
	if err != nil {
		return err
	}
	tmpName := tmp.Name()
	if _, err := tmp.Write(data); err != nil {
		tmp.Close()
		os.Remove(tmpName)
		return err
	}
	if err := tmp.Close(); err != nil {
		os.Remove(tmpName)
		return err
	}
	if err := os.Rename(tmpName, path); err != nil {
		os.Remove(tmpName)
		return err
	}
	return nil
}

// RenderMarkdown formats an Evaluation into the career-ops-compatible report
// layout expected under reports/{seq}-{slug}-{date}.md.
func RenderMarkdown(job JobContext, eval Evaluation, seq int, date string) string {
	var b strings.Builder
	fmt.Fprintf(&b, "# Evaluation: %s — %s\n\n", job.Company, job.Title)
	fmt.Fprintf(&b, "**Seq:** %03d\n", seq)
	fmt.Fprintf(&b, "**Date:** %s\n", date)
	fmt.Fprintf(&b, "**Archetype:** %s\n", eval.Archetype)
	fmt.Fprintf(&b, "**Score:** %.1f/5\n", eval.Score)
	fmt.Fprintf(&b, "**Legitimacy:** %s\n", eval.Legitimacy)
	if job.URL != "" {
		fmt.Fprintf(&b, "**URL:** %s\n", job.URL)
	}
	b.WriteString("\n---\n\n")

	writeBlock := func(label, content string) {
		if strings.TrimSpace(content) == "" {
			return
		}
		fmt.Fprintf(&b, "%s\n\n", content)
	}
	// Block G is written first to reflect the G-first evaluation order.
	writeBlock("G", eval.BlockG)
	writeBlock("A", eval.BlockA)
	writeBlock("B", eval.BlockB)
	writeBlock("C", eval.BlockC)
	writeBlock("D", eval.BlockD)
	writeBlock("E", eval.BlockE)
	writeBlock("F", eval.BlockF)

	// If every block is empty, fall back to the raw stream so the disk artifact
	// is never empty — useful for debugging a prompt or parse failure.
	if eval.BlockA == "" && eval.BlockB == "" && eval.BlockG == "" && eval.Raw != "" {
		b.WriteString("## Raw Output\n\n")
		b.WriteString(eval.Raw)
		b.WriteString("\n")
	}
	return b.String()
}