package main

import (
	"encoding/csv"
	"encoding/json"
	"fmt"
	"net/http"
	"strconv"
	"strings"
)

// csvSafe prefixes potentially dangerous CSV cell content with a single quote
// so that spreadsheet apps don't interpret it as a formula.
func csvSafe(s string) string {
	if s == "" {
		return s
	}
	switch s[0] {
	case '=', '+', '-', '@', '\t', '\r':
		return "'" + s
	}
	return s
}

func clip(s string, max int) string {
	if len(s) > max {
		return s[:max]
	}
	return s
}

func registerPenaltyRoutes(mux *http.ServeMux) {
	mux.HandleFunc("GET /api/competitions/{id}/penalties", requireAuth(handleListPenalties))
	mux.HandleFunc("POST /api/competitions/{id}/penalties", requireAuth(handleCreatePenalty))
	mux.HandleFunc("PATCH /api/competitions/{id}/penalties/{pid}", requireAuth(handleUpdatePenalty))
	mux.HandleFunc("DELETE /api/competitions/{id}/penalties/{pid}", requireAuth(handleDeletePenalty))
	mux.HandleFunc("GET /api/competitions/{id}/penalties.csv", requireAuth(handleExportPenalties))
	mux.HandleFunc("POST /api/competitions/{id}/penalties/apply", requireAuth(handleApplyPenalties))
}

// handleApplyPenalties bulk-marks penalties as applied (transferred=1) for a
// given task. Requires chief_scorer or system_admin role.
func handleApplyPenalties(w http.ResponseWriter, r *http.Request) {
	id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
	if err != nil {
		writeError(w, http.StatusBadRequest, "invalid_id")
		return
	}
	u := userFromCtx(r)
	role, ok := canAccessCompetition(u, id)
	if !ok || (role != "chief_scorer" && role != "system_admin") {
		writeError(w, http.StatusForbidden, "forbidden")
		return
	}
	if closed, _ := isCompetitionClosed(id); closed {
		writeError(w, http.StatusConflict, "competition_closed")
		return
	}
	var req struct {
		Task    string  `json:"task"`
		IDs     []int64 `json:"ids"`
		Applied bool    `json:"applied"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		writeError(w, http.StatusBadRequest, "invalid_body")
		return
	}
	v := 0
	if req.Applied {
		v = 1
	}
	tx, err := db.Begin()
	if err != nil {
		writeError(w, http.StatusInternalServerError, "tx_error")
		return
	}
	defer tx.Rollback()
	if len(req.IDs) > 0 {
		stmt, err := tx.Prepare("UPDATE penalties SET transferred=?, updated_at=datetime('now') WHERE id=? AND competition_id=?")
		if err != nil {
			writeError(w, http.StatusInternalServerError, "db_error")
			return
		}
		defer stmt.Close()
		for _, pid := range req.IDs {
			stmt.Exec(v, pid, id)
		}
	} else {
		task := clip(req.Task, 64)
		if _, err := tx.Exec("UPDATE penalties SET transferred=?, updated_at=datetime('now') WHERE competition_id=? AND task=?", v, id, task); err != nil {
			writeError(w, http.StatusInternalServerError, "db_error")
			return
		}
	}
	if err := tx.Commit(); err != nil {
		writeError(w, http.StatusInternalServerError, "commit_error")
		return
	}
	hub.broadcast(id, "penalties_applied", map[string]any{"task": req.Task, "applied": req.Applied, "ids": req.IDs})
	w.WriteHeader(http.StatusNoContent)
}

func loadPenalty(id int64) (*Penalty, error) {
	row := db.QueryRow(`SELECT p.id,p.competition_id,p.flight,p.date,p.pilot_number,
		COALESCE(pl.last_name || ', ' || pl.first_name, ''),
		p.rule_number,p.task,p.penalties_text,p.description,p.created_by,
		COALESCE(u.display_name, u.username),p.transferred,p.created_at,p.updated_at
		FROM penalties p
		LEFT JOIN pilots pl ON pl.competition_id=p.competition_id AND pl.number=p.pilot_number
		LEFT JOIN users u ON u.id=p.created_by
		WHERE p.id=?`, id)
	var pen Penalty
	var transferred int
	err := row.Scan(&pen.ID, &pen.CompetitionID, &pen.Flight, &pen.Date, &pen.PilotNumber,
		&pen.PilotName, &pen.RuleNumber, &pen.Task, &pen.PenaltiesText, &pen.Description,
		&pen.CreatedBy, &pen.CreatedByName, &transferred, &pen.CreatedAt, &pen.UpdatedAt)
	if err != nil {
		return nil, err
	}
	pen.Transferred = transferred == 1
	return &pen, nil
}

func handleListPenalties(w http.ResponseWriter, r *http.Request) {
	id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
	if err != nil {
		writeError(w, http.StatusBadRequest, "invalid_id")
		return
	}
	u := userFromCtx(r)
	if _, ok := canAccessCompetition(u, id); !ok {
		writeError(w, http.StatusForbidden, "forbidden")
		return
	}
	rows, err := db.Query(`SELECT p.id,p.competition_id,p.flight,p.date,p.pilot_number,
		COALESCE(pl.last_name || ', ' || pl.first_name, ''),
		p.rule_number,p.task,p.penalties_text,p.description,p.created_by,
		COALESCE(u.display_name, u.username),p.transferred,p.created_at,p.updated_at
		FROM penalties p
		LEFT JOIN pilots pl ON pl.competition_id=p.competition_id AND pl.number=p.pilot_number
		LEFT JOIN users u ON u.id=p.created_by
		WHERE p.competition_id=? ORDER BY p.id DESC`, id)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "db_error")
		return
	}
	defer rows.Close()
	out := []Penalty{}
	for rows.Next() {
		var pen Penalty
		var transferred int
		rows.Scan(&pen.ID, &pen.CompetitionID, &pen.Flight, &pen.Date, &pen.PilotNumber,
			&pen.PilotName, &pen.RuleNumber, &pen.Task, &pen.PenaltiesText, &pen.Description,
			&pen.CreatedBy, &pen.CreatedByName, &transferred, &pen.CreatedAt, &pen.UpdatedAt)
		pen.Transferred = transferred == 1
		out = append(out, pen)
	}
	writeJSON(w, http.StatusOK, out)
}

func handleCreatePenalty(w http.ResponseWriter, r *http.Request) {
	id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
	if err != nil {
		writeError(w, http.StatusBadRequest, "invalid_id")
		return
	}
	u := userFromCtx(r)
	role, ok := canAccessCompetition(u, id)
	if !ok || (role != "scorer" && role != "chief_scorer" && role != "system_admin") {
		writeError(w, http.StatusForbidden, "forbidden")
		return
	}
	if closed, _ := isCompetitionClosed(id); closed {
		writeError(w, http.StatusConflict, "competition_closed")
		return
	}
	var pen Penalty
	if err := json.NewDecoder(r.Body).Decode(&pen); err != nil {
		writeError(w, http.StatusBadRequest, "invalid_body")
		return
	}
	pen.Flight = clip(pen.Flight, 64)
	pen.Date = clip(pen.Date, 32)
	pen.PilotNumber = clip(pen.PilotNumber, 32)
	pen.RuleNumber = clip(pen.RuleNumber, 64)
	pen.Task = clip(pen.Task, 64)
	pen.PenaltiesText = clip(pen.PenaltiesText, 256)
	pen.Description = clip(pen.Description, maxFieldLen)
	transferred := 0
	if pen.Transferred {
		transferred = 1
	}
	res, err := db.Exec(`INSERT INTO penalties(competition_id,flight,date,pilot_number,rule_number,task,penalties_text,description,created_by,transferred)
		VALUES(?,?,?,?,?,?,?,?,?,?)`,
		id, pen.Flight, pen.Date, pen.PilotNumber, pen.RuleNumber, pen.Task, pen.PenaltiesText, pen.Description, u.ID, transferred)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "db_error")
		return
	}
	pid, _ := res.LastInsertId()
	created, err := loadPenalty(pid)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "load_error")
		return
	}
	hub.broadcast(id, "penalty_created", created)
	writeJSON(w, http.StatusCreated, created)
}

func canEditPenalty(u *User, competitionID, createdBy int64) bool {
	role, ok := canAccessCompetition(u, competitionID)
	if !ok {
		return false
	}
	if role == "system_admin" || role == "chief_scorer" {
		return true
	}
	if role == "scorer" {
		if u.ID == createdBy {
			return true
		}
		var allow int
		db.QueryRow("SELECT allow_any_scorer_edit FROM competitions WHERE id=?", competitionID).Scan(&allow)
		return allow == 1
	}
	return false
}

func handleUpdatePenalty(w http.ResponseWriter, r *http.Request) {
	id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
	if err != nil {
		writeError(w, http.StatusBadRequest, "invalid_id")
		return
	}
	pid, err := strconv.ParseInt(r.PathValue("pid"), 10, 64)
	if err != nil {
		writeError(w, http.StatusBadRequest, "invalid_id")
		return
	}
	u := userFromCtx(r)
	var createdBy int64
	if err := db.QueryRow("SELECT created_by FROM penalties WHERE id=? AND competition_id=?", pid, id).Scan(&createdBy); err != nil {
		writeError(w, http.StatusNotFound, "not_found")
		return
	}
	if !canEditPenalty(u, id, createdBy) {
		writeError(w, http.StatusForbidden, "forbidden")
		return
	}
	if closed, _ := isCompetitionClosed(id); closed {
		writeError(w, http.StatusConflict, "competition_closed")
		return
	}
	var req struct {
		Flight        *string `json:"flight"`
		Date          *string `json:"date"`
		PilotNumber   *string `json:"pilot_number"`
		RuleNumber    *string `json:"rule_number"`
		Task          *string `json:"task"`
		PenaltiesText *string `json:"penalties_text"`
		Description   *string `json:"description"`
		Transferred   *bool   `json:"transferred"`
	}
	if err := json.NewDecoder(r.Body).Decode(&req); err != nil {
		writeError(w, http.StatusBadRequest, "invalid_body")
		return
	}
	sets := []string{}
	args := []any{}
	if req.Flight != nil {
		sets = append(sets, "flight=?")
		args = append(args, clip(*req.Flight, 64))
	}
	if req.Date != nil {
		sets = append(sets, "date=?")
		args = append(args, clip(*req.Date, 32))
	}
	if req.PilotNumber != nil {
		sets = append(sets, "pilot_number=?")
		args = append(args, clip(*req.PilotNumber, 32))
	}
	if req.RuleNumber != nil {
		sets = append(sets, "rule_number=?")
		args = append(args, clip(*req.RuleNumber, 64))
	}
	if req.Task != nil {
		sets = append(sets, "task=?")
		args = append(args, clip(*req.Task, 64))
	}
	if req.PenaltiesText != nil {
		sets = append(sets, "penalties_text=?")
		args = append(args, clip(*req.PenaltiesText, 256))
	}
	if req.Description != nil {
		sets = append(sets, "description=?")
		args = append(args, clip(*req.Description, maxFieldLen))
	}
	if req.Transferred != nil {
		v := 0
		if *req.Transferred {
			v = 1
		}
		sets = append(sets, "transferred=?")
		args = append(args, v)
	}
	if len(sets) > 0 {
		sets = append(sets, "updated_at=datetime('now')")
		args = append(args, pid)
		query := "UPDATE penalties SET " + strings.Join(sets, ",") + " WHERE id=?"
		if _, err := db.Exec(query, args...); err != nil {
			writeError(w, http.StatusInternalServerError, "db_error")
			return
		}
	}
	updated, err := loadPenalty(pid)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "load_error")
		return
	}
	hub.broadcast(id, "penalty_updated", updated)
	writeJSON(w, http.StatusOK, updated)
}

func handleDeletePenalty(w http.ResponseWriter, r *http.Request) {
	id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
	if err != nil {
		writeError(w, http.StatusBadRequest, "invalid_id")
		return
	}
	pid, err := strconv.ParseInt(r.PathValue("pid"), 10, 64)
	if err != nil {
		writeError(w, http.StatusBadRequest, "invalid_id")
		return
	}
	u := userFromCtx(r)
	var createdBy int64
	if err := db.QueryRow("SELECT created_by FROM penalties WHERE id=? AND competition_id=?", pid, id).Scan(&createdBy); err != nil {
		writeError(w, http.StatusNotFound, "not_found")
		return
	}
	if !canEditPenalty(u, id, createdBy) {
		writeError(w, http.StatusForbidden, "forbidden")
		return
	}
	if closed, _ := isCompetitionClosed(id); closed {
		writeError(w, http.StatusConflict, "competition_closed")
		return
	}
	db.Exec("DELETE FROM penalties WHERE id=?", pid)
	hub.broadcast(id, "penalty_deleted", map[string]int64{"id": pid})
	w.WriteHeader(http.StatusNoContent)
}

func handleExportPenalties(w http.ResponseWriter, r *http.Request) {
	id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
	if err != nil {
		writeError(w, http.StatusBadRequest, "invalid_id")
		return
	}
	u := userFromCtx(r)
	role, ok := canAccessCompetition(u, id)
	if !ok || (role != "chief_scorer" && role != "system_admin") {
		writeError(w, http.StatusForbidden, "forbidden")
		return
	}
	rows, err := db.Query(`SELECT p.id,p.flight,p.date,p.pilot_number,
		COALESCE(pl.last_name || ', ' || pl.first_name, ''),
		p.rule_number,p.task,p.penalties_text,p.description,
		COALESCE(u.display_name, u.username),p.transferred,p.created_at
		FROM penalties p
		LEFT JOIN pilots pl ON pl.competition_id=p.competition_id AND pl.number=p.pilot_number
		LEFT JOIN users u ON u.id=p.created_by
		WHERE p.competition_id=? ORDER BY p.id`, id)
	if err != nil {
		writeError(w, http.StatusInternalServerError, "db_error")
		return
	}
	defer rows.Close()
	w.Header().Set("Content-Type", "text/csv; charset=utf-8")
	w.Header().Set("Content-Disposition", fmt.Sprintf("attachment; filename=\"penalties_%d.csv\"", id))
	cw := csv.NewWriter(w)
	cw.Write([]string{"id", "flight", "date", "pilot_number", "pilot_name", "rule_number", "task", "penalties", "description", "created_by", "transferred", "created_at"})
	for rows.Next() {
		var idv int64
		var flight, date, pnum, pname, rnum, task, pens, desc, creator, createdAt string
		var transferred int
		rows.Scan(&idv, &flight, &date, &pnum, &pname, &rnum, &task, &pens, &desc, &creator, &transferred, &createdAt)
		t := "0"
		if transferred == 1 {
			t = "1"
		}
		cw.Write([]string{
			strconv.FormatInt(idv, 10),
			csvSafe(flight), csvSafe(date), csvSafe(pnum), csvSafe(pname),
			csvSafe(rnum), csvSafe(task), csvSafe(pens), csvSafe(desc),
			csvSafe(creator), t, createdAt,
		})
	}
	cw.Flush()
}