package db

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

type Ticket struct {
	ID                int64
	TicketNumber      int
	UserID            string
	Panel             string
	Type              string
	ChannelID         string
	GuildID           string
	ClaimChannelID    string
	LogChannelID      string
	StaffRoleID       string
	ClaimReupMinutes  int
	OpenedAt          time.Time
	ClaimedBy         sql.NullString
	ClaimedAt         sql.NullTime
	ClosedAt          sql.NullTime
	ClosedBy          sql.NullString
	Reason            sql.NullString
	TranscriptPath    sql.NullString
	Status            string
	TicketTitle       sql.NullString
	TicketDescription sql.NullString
}

type TicketRepo struct{ db *sql.DB }

func NewTicketRepo(db *sql.DB) *TicketRepo { return &TicketRepo{db: db} }

// nextNumber returns the next ticket_number for the given type within a transaction.
func (r *TicketRepo) nextNumber(ctx context.Context, tx *sql.Tx, ticketType string) (int, error) {
	var n int
	err := tx.QueryRowContext(ctx,
		`SELECT COALESCE(MAX(ticket_number),0)+1 FROM tickets WHERE type=?`, ticketType,
	).Scan(&n)
	return n, err
}

func (r *TicketRepo) Insert(ctx context.Context, t *Ticket) error {
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() //nolint

	n, err := r.nextNumber(ctx, tx, t.Type)
	if err != nil {
		return err
	}
	t.TicketNumber = n

	res, err := tx.ExecContext(ctx, `
		INSERT INTO tickets(ticket_number,user_id,panel,type,channel_id,guild_id,claim_channel_id,log_channel_id,staff_role_id,claim_reup_minutes,opened_at,status,ticket_title,ticket_description)
		VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
		n, t.UserID, t.Panel, t.Type, t.ChannelID, t.GuildID, t.ClaimChannelID, t.LogChannelID,
		t.StaffRoleID, t.ClaimReupMinutes, t.OpenedAt.UTC(), "open", t.TicketTitle, t.TicketDescription,
	)
	if err != nil {
		return fmt.Errorf("insert ticket: %w", err)
	}
	t.ID, _ = res.LastInsertId()
	t.Status = "open"
	return tx.Commit()
}

// InsertWithNumber inserts a ticket where TicketNumber is already set (e.g. convocations).
func (r *TicketRepo) InsertWithNumber(ctx context.Context, t *Ticket) error {
	res, err := r.db.ExecContext(ctx, `
		INSERT INTO tickets(ticket_number,user_id,panel,type,channel_id,guild_id,claim_channel_id,log_channel_id,staff_role_id,claim_reup_minutes,opened_at,status,ticket_title,ticket_description)
		VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
		t.TicketNumber, t.UserID, t.Panel, t.Type, t.ChannelID, t.GuildID, t.ClaimChannelID, t.LogChannelID,
		t.StaffRoleID, t.ClaimReupMinutes, t.OpenedAt.UTC(), "open", t.TicketTitle, t.TicketDescription,
	)
	if err != nil {
		return fmt.Errorf("insert ticket with number: %w", err)
	}
	t.ID, _ = res.LastInsertId()
	t.Status = "open"
	return nil
}

func (r *TicketRepo) GetByChannelID(ctx context.Context, channelID string) (*Ticket, error) {
	row := r.db.QueryRowContext(ctx, `
		SELECT id,ticket_number,user_id,panel,type,channel_id,guild_id,claim_channel_id,log_channel_id,staff_role_id,claim_reup_minutes,opened_at,
		       claimed_by,claimed_at,closed_at,closed_by,reason,transcript_path,status,ticket_title,ticket_description
		FROM tickets WHERE channel_id=?`, channelID)
	return scanTicket(row)
}

func (r *TicketRepo) GetByID(ctx context.Context, id int64) (*Ticket, error) {
	row := r.db.QueryRowContext(ctx, `
		SELECT id,ticket_number,user_id,panel,type,channel_id,guild_id,claim_channel_id,log_channel_id,staff_role_id,claim_reup_minutes,opened_at,
		       claimed_by,claimed_at,closed_at,closed_by,reason,transcript_path,status,ticket_title,ticket_description
		FROM tickets WHERE id=?`, id)
	return scanTicket(row)
}

func (r *TicketRepo) HasOpenTicket(ctx context.Context, userID, ticketType string) (*Ticket, error) {
	row := r.db.QueryRowContext(ctx, `
		SELECT id,ticket_number,user_id,panel,type,channel_id,guild_id,claim_channel_id,log_channel_id,staff_role_id,claim_reup_minutes,opened_at,
		       claimed_by,claimed_at,closed_at,closed_by,reason,transcript_path,status,ticket_title,ticket_description
		FROM tickets WHERE user_id=? AND type=? AND status IN('open','claimed')
		LIMIT 1`, userID, ticketType)
	t, err := scanTicket(row)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	return t, err
}

// CountByStatus returns the number of tickets with the given status.
func (r *TicketRepo) CountByStatus(ctx context.Context, status string) (int, error) {
	var n int
	err := r.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM tickets WHERE status=?`, status).Scan(&n)
	return n, err
}

// CountClosedSince returns the number of tickets closed on or after `since`.
func (r *TicketRepo) CountClosedSince(ctx context.Context, since time.Time) (int, error) {
	var n int
	err := r.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM tickets WHERE status='closed' AND closed_at>=?`, since).Scan(&n)
	return n, err
}

// AvgClaimMinutes returns the average minutes between opened_at and claimed_at for tickets claimed since `since`.
func (r *TicketRepo) AvgClaimMinutes(ctx context.Context, since time.Time) (int, error) {
	var avg sql.NullFloat64
	err := r.db.QueryRowContext(ctx,
		`SELECT AVG((julianday(claimed_at)-julianday(opened_at))*1440)
		 FROM tickets WHERE claimed_at IS NOT NULL AND claimed_at>=?`, since).Scan(&avg)
	if err != nil || !avg.Valid {
		return 0, err
	}
	return int(avg.Float64), nil
}

// AvgResolutionMinutes returns the average minutes between opened_at and closed_at for tickets closed since `since`.
func (r *TicketRepo) AvgResolutionMinutes(ctx context.Context, since time.Time) (int, error) {
	var avg sql.NullFloat64
	err := r.db.QueryRowContext(ctx,
		`SELECT AVG((julianday(closed_at)-julianday(opened_at))*1440)
		 FROM tickets WHERE closed_at IS NOT NULL AND closed_at>=?`, since).Scan(&avg)
	if err != nil || !avg.Valid {
		return 0, err
	}
	return int(avg.Float64), nil
}

func (r *TicketRepo) SetClaimed(ctx context.Context, id int64, staffID string, at time.Time) error {
	_, err := r.db.ExecContext(ctx,
		`UPDATE tickets SET status='claimed', claimed_by=?, claimed_at=? WHERE id=?`,
		staffID, at.UTC(), id)
	return err
}

func (r *TicketRepo) SetClosed(ctx context.Context, id int64, closedBy, reason, transcriptPath string, at time.Time) error {
	_, err := r.db.ExecContext(ctx, `
		UPDATE tickets SET status='closed', closed_at=?, closed_by=?, reason=?, transcript_path=?
		WHERE id=?`,
		at.UTC(), closedBy, reason, transcriptPath, id)
	return err
}

func (r *TicketRepo) SetClosedByChannel(ctx context.Context, channelID, reason string) error {
	_, err := r.db.ExecContext(ctx, `
		UPDATE tickets SET status='closed', closed_at=?, reason=?
		WHERE channel_id=? AND status IN('open','claimed')`,
		time.Now().UTC(), reason, channelID)
	return err
}

func (r *TicketRepo) ListOpen(ctx context.Context) ([]*Ticket, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT id,ticket_number,user_id,panel,type,channel_id,guild_id,claim_channel_id,log_channel_id,staff_role_id,claim_reup_minutes,opened_at,
		       claimed_by,claimed_at,closed_at,closed_by,reason,transcript_path,status,ticket_title,ticket_description
		FROM tickets WHERE status='open'`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanTickets(rows)
}

func (r *TicketRepo) UpdateChannelID(ctx context.Context, id int64, newChannelID string) error {
	_, err := r.db.ExecContext(ctx, `UPDATE tickets SET channel_id=? WHERE id=?`, newChannelID, id)
	return err
}

func (r *TicketRepo) NextConvocationNumber(ctx context.Context) (int, error) {
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return 0, err
	}
	defer tx.Rollback() //nolint
	var n int
	if err := tx.QueryRowContext(ctx, `UPDATE convocation_counter SET count=count+1 WHERE id=1 RETURNING count`).Scan(&n); err != nil {
		return 0, err
	}
	return n, tx.Commit()
}

// DailyCount holds the ticket count for one day.
type DailyCount struct {
	Day   string // "2006-01-02"
	Count int
}

// DailyTicketCounts returns the count of tickets opened per day for the last n days.
func (r *TicketRepo) DailyTicketCounts(ctx context.Context, days int) ([]DailyCount, error) {
	since := time.Now().AddDate(0, 0, -days+1).Truncate(24 * time.Hour)
	rows, err := r.db.QueryContext(ctx, `
		SELECT strftime('%Y-%m-%d', opened_at) AS day, COUNT(*) AS cnt
		FROM tickets
		WHERE opened_at >= ?
		GROUP BY day
		ORDER BY day`, since.UTC())
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []DailyCount
	for rows.Next() {
		var dc DailyCount
		if err := rows.Scan(&dc.Day, &dc.Count); err != nil {
			return nil, err
		}
		out = append(out, dc)
	}
	return out, rows.Err()
}

// StaffStat holds per-staff ticket stats.
type StaffStat struct {
	StaffID              string
	Claimed              int
	Closed               int
	AvgClaimMinutes      int
	AvgResolutionMinutes int
}

// StaffStats returns per-staff statistics for tickets claimed in the last n days.
func (r *TicketRepo) StaffStats(ctx context.Context, days int) ([]StaffStat, error) {
	since := time.Now().AddDate(0, 0, -days)
	rows, err := r.db.QueryContext(ctx, `
		SELECT
			claimed_by,
			COUNT(*) AS claimed,
			SUM(CASE WHEN status='closed' THEN 1 ELSE 0 END) AS closed,
			CAST(AVG(CASE WHEN claimed_at IS NOT NULL
				THEN (julianday(claimed_at) - julianday(opened_at)) * 1440 END) AS INTEGER) AS avg_claim,
			CAST(AVG(CASE WHEN closed_at IS NOT NULL AND claimed_at IS NOT NULL
				THEN (julianday(closed_at) - julianday(opened_at)) * 1440 END) AS INTEGER) AS avg_resolution
		FROM tickets
		WHERE claimed_by IS NOT NULL AND claimed_at >= ?
		GROUP BY claimed_by
		ORDER BY claimed DESC`, since.UTC())
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []StaffStat
	for rows.Next() {
		var s StaffStat
		var avgClaim, avgRes sql.NullInt64
		if err := rows.Scan(&s.StaffID, &s.Claimed, &s.Closed, &avgClaim, &avgRes); err != nil {
			return nil, err
		}
		s.AvgClaimMinutes = int(avgClaim.Int64)
		s.AvgResolutionMinutes = int(avgRes.Int64)
		out = append(out, s)
	}
	return out, rows.Err()
}

// ListFiltered returns tickets matching optional filters with pagination.
type TicketFilter struct {
	Status   string
	Type     string
	StaffID  string
	GuildID  string
	Search   string
	From, To time.Time
	Page     int
	PageSize int
}

func (r *TicketRepo) ListFiltered(ctx context.Context, f TicketFilter) ([]*Ticket, int, error) {
	if f.PageSize <= 0 {
		f.PageSize = 20
	}
	if f.Page <= 0 {
		f.Page = 1
	}
	offset := (f.Page - 1) * f.PageSize

	args := []any{}
	where := "1=1"
	if f.Status != "" {
		where += " AND status=?"
		args = append(args, f.Status)
	}
	if f.Type != "" {
		where += " AND type=?"
		args = append(args, f.Type)
	}
	if f.StaffID != "" {
		where += " AND claimed_by=?"
		args = append(args, f.StaffID)
	}
	if f.GuildID != "" {
		where += " AND guild_id=?"
		args = append(args, f.GuildID)
	}
	if f.Search != "" {
		where += " AND (ticket_title LIKE ? OR user_id LIKE ?)"
		args = append(args, "%"+f.Search+"%", "%"+f.Search+"%")
	}
	if !f.From.IsZero() {
		where += " AND opened_at >= ?"
		args = append(args, f.From.UTC())
	}
	if !f.To.IsZero() {
		where += " AND opened_at <= ?"
		args = append(args, f.To.UTC())
	}

	var total int
	countArgs := make([]any, len(args))
	copy(countArgs, args)
	if err := r.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM tickets WHERE "+where, countArgs...).Scan(&total); err != nil {
		return nil, 0, err
	}

	args = append(args, f.PageSize, offset)
	rows, err := r.db.QueryContext(ctx, `
		SELECT id,ticket_number,user_id,panel,type,channel_id,guild_id,claim_channel_id,log_channel_id,staff_role_id,claim_reup_minutes,opened_at,
		       claimed_by,claimed_at,closed_at,closed_by,reason,transcript_path,status,ticket_title,ticket_description
		FROM tickets WHERE `+where+` ORDER BY opened_at DESC LIMIT ? OFFSET ?`, args...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	list, err := scanTickets(rows)
	return list, total, err
}

// ListDistinctTypes returns all distinct ticket types present in the tickets table.
func (r *TicketRepo) ListDistinctTypes(ctx context.Context) ([]string, error) {
	rows, err := r.db.QueryContext(ctx, `SELECT DISTINCT type FROM tickets ORDER BY type`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var t string
		if err := rows.Scan(&t); err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

// ListDistinctStaff returns all distinct staff IDs (claimed_by) from the tickets table.
func (r *TicketRepo) ListDistinctStaff(ctx context.Context) ([]string, error) {
	rows, err := r.db.QueryContext(ctx,
		`SELECT DISTINCT claimed_by FROM tickets WHERE claimed_by IS NOT NULL ORDER BY claimed_by`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var s string
		if err := rows.Scan(&s); err != nil {
			return nil, err
		}
		out = append(out, s)
	}
	return out, rows.Err()
}

// NullStr returns a valid NullString for non-empty strings, invalid (NULL) for empty.
func NullStr(s string) sql.NullString {
	return sql.NullString{String: s, Valid: s != ""}
}

// SetTitleDescription updates the title and description of a ticket after modal submission.
func (r *TicketRepo) SetTitleDescription(ctx context.Context, id int64, title, description string) error {
	_, err := r.db.ExecContext(ctx,
		`UPDATE tickets SET ticket_title=?, ticket_description=? WHERE id=?`,
		title, description, id)
	return err
}

func scanTicket(row *sql.Row) (*Ticket, error) {
	var t Ticket
	err := row.Scan(&t.ID, &t.TicketNumber, &t.UserID, &t.Panel, &t.Type, &t.ChannelID,
		&t.GuildID, &t.ClaimChannelID, &t.LogChannelID, &t.StaffRoleID, &t.ClaimReupMinutes,
		&t.OpenedAt, &t.ClaimedBy, &t.ClaimedAt, &t.ClosedAt, &t.ClosedBy,
		&t.Reason, &t.TranscriptPath, &t.Status, &t.TicketTitle, &t.TicketDescription)
	if err != nil {
		return nil, err
	}
	return &t, nil
}

func scanTickets(rows *sql.Rows) ([]*Ticket, error) {
	var result []*Ticket
	for rows.Next() {
		var t Ticket
		if err := rows.Scan(&t.ID, &t.TicketNumber, &t.UserID, &t.Panel, &t.Type, &t.ChannelID,
			&t.GuildID, &t.ClaimChannelID, &t.LogChannelID, &t.StaffRoleID, &t.ClaimReupMinutes,
			&t.OpenedAt, &t.ClaimedBy, &t.ClaimedAt, &t.ClosedAt, &t.ClosedBy,
			&t.Reason, &t.TranscriptPath, &t.Status, &t.TicketTitle, &t.TicketDescription); err != nil {
			return nil, err
		}
		result = append(result, &t)
	}
	return result, rows.Err()
}