package tickets

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

var ErrTicketNotFound = errors.New("tiket tidak ditemukan")

type Repository struct {
	pool *pgxpool.Pool
}

func NewRepository(pool *pgxpool.Pool) *Repository {
	return &Repository{pool: pool}
}

func (r *Repository) CreateTicket(ctx context.Context, t *Ticket) (*Ticket, error) {
	// Generate unique ticket number: TKT-YYYYMMDD-XXXX
	now := time.Now().In(time.FixedZone("WIB", 7*3600))
	dateStr := now.Format("20060102")

	var seqNum int
	seqQuery := `SELECT COALESCE(COUNT(*), 0) + 1 FROM tickets WHERE ticket_number LIKE $1`
	err := r.pool.QueryRow(ctx, seqQuery, "TKT-"+dateStr+"-%").Scan(&seqNum)
	if err != nil {
		seqNum = 1
	}
	t.TicketNumber = fmt.Sprintf("TKT-%s-%04d", dateStr, seqNum)

	metaJSON, err := json.Marshal(t.Metadata)
	if err != nil {
		metaJSON = []byte("{}")
	}

	query := `
		INSERT INTO tickets (
			ticket_number, title, description, category, priority, status,
			source_app, source_path, reporter_id, reporter_name, reporter_email,
			assigned_to_id, metadata, created_at, updated_at
		) VALUES (
			$1, $2, $3, $4, $5, $6,
			$7, $8, $9, $10, $11,
			$12, $13, now(), now()
		)
		RETURNING id, created_at, updated_at
	`

	err = r.pool.QueryRow(ctx, query,
		t.TicketNumber, t.Title, t.Description, t.Category, t.Priority, t.Status,
		t.SourceApp, t.SourcePath, t.ReporterID, t.ReporterName, t.ReporterEmail,
		t.AssignedToID, metaJSON,
	).Scan(&t.ID, &t.CreatedAt, &t.UpdatedAt)

	if err != nil {
		return nil, fmt.Errorf("insert ticket: %w", err)
	}

	return t, nil
}

func (r *Repository) GetTicketByID(ctx context.Context, id int64) (*Ticket, error) {
	query := `
		SELECT
			t.id, t.ticket_number, t.title, t.description, t.category, t.priority, t.status,
			t.source_app, t.source_path, t.reporter_id, t.reporter_name, t.reporter_email,
			t.assigned_to_id, COALESCE(u.name, '') as assigned_to_name,
			t.metadata, t.created_at, t.updated_at, t.resolved_at
		FROM tickets t
		LEFT JOIN users u ON u.id = t.assigned_to_id
		WHERE t.id = $1
	`

	var t Ticket
	var metaBytes []byte
	err := r.pool.QueryRow(ctx, query, id).Scan(
		&t.ID, &t.TicketNumber, &t.Title, &t.Description, &t.Category, &t.Priority, &t.Status,
		&t.SourceApp, &t.SourcePath, &t.ReporterID, &t.ReporterName, &t.ReporterEmail,
		&t.AssignedToID, &t.AssignedToName,
		&metaBytes, &t.CreatedAt, &t.UpdatedAt, &t.ResolvedAt,
	)

	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrTicketNotFound
		}
		return nil, fmt.Errorf("query ticket: %w", err)
	}

	_ = json.Unmarshal(metaBytes, &t.Metadata)
	return &t, nil
}

func (r *Repository) ListTickets(ctx context.Context, filter ListTicketsFilter) ([]Ticket, int64, error) {
	var conditions []string
	var args []any
	argIdx := 1

	if filter.Status != "" {
		conditions = append(conditions, fmt.Sprintf("t.status = $%d", argIdx))
		args = append(args, filter.Status)
		argIdx++
	}

	if filter.Category != "" {
		conditions = append(conditions, fmt.Sprintf("t.category = $%d", argIdx))
		args = append(args, filter.Category)
		argIdx++
	}

	if filter.Priority != "" {
		conditions = append(conditions, fmt.Sprintf("t.priority = $%d", argIdx))
		args = append(args, filter.Priority)
		argIdx++
	}

	if filter.SourceApp != "" {
		conditions = append(conditions, fmt.Sprintf("t.source_app = $%d", argIdx))
		args = append(args, filter.SourceApp)
		argIdx++
	}

	if filter.ReporterID > 0 {
		conditions = append(conditions, fmt.Sprintf("t.reporter_id = $%d", argIdx))
		args = append(args, filter.ReporterID)
		argIdx++
	}

	if filter.Search != "" {
		searchTerm := "%" + filter.Search + "%"
		conditions = append(conditions, fmt.Sprintf("(t.ticket_number ILIKE $%d OR t.title ILIKE $%d OR t.description ILIKE $%d OR t.reporter_name ILIKE $%d)", argIdx, argIdx, argIdx, argIdx))
		args = append(args, searchTerm)
		argIdx++
	}

	whereClause := ""
	if len(conditions) > 0 {
		whereClause = "WHERE " + strings.Join(conditions, " AND ")
	}

	// Count total
	countQuery := fmt.Sprintf("SELECT COUNT(*) FROM tickets t %s", whereClause)
	var total int64
	if err := r.pool.QueryRow(ctx, countQuery, args...).Scan(&total); err != nil {
		return nil, 0, fmt.Errorf("count tickets: %w", err)
	}

	limit := filter.Limit
	if limit <= 0 {
		limit = 20
	}
	offset := filter.Offset
	if offset < 0 {
		offset = 0
	}

	query := fmt.Sprintf(`
		SELECT
			t.id, t.ticket_number, t.title, t.description, t.category, t.priority, t.status,
			t.source_app, t.source_path, t.reporter_id, t.reporter_name, t.reporter_email,
			t.assigned_to_id, COALESCE(u.name, '') as assigned_to_name,
			t.metadata, t.created_at, t.updated_at, t.resolved_at
		FROM tickets t
		LEFT JOIN users u ON u.id = t.assigned_to_id
		%s
		ORDER BY t.created_at DESC
		LIMIT $%d OFFSET $%d
	`, whereClause, argIdx, argIdx+1)

	args = append(args, limit, offset)

	rows, err := r.pool.Query(ctx, query, args...)
	if err != nil {
		return nil, 0, fmt.Errorf("list tickets: %w", err)
	}
	defer rows.Close()

	var results []Ticket
	for rows.Next() {
		var t Ticket
		var metaBytes []byte
		if err := rows.Scan(
			&t.ID, &t.TicketNumber, &t.Title, &t.Description, &t.Category, &t.Priority, &t.Status,
			&t.SourceApp, &t.SourcePath, &t.ReporterID, &t.ReporterName, &t.ReporterEmail,
			&t.AssignedToID, &t.AssignedToName,
			&metaBytes, &t.CreatedAt, &t.UpdatedAt, &t.ResolvedAt,
		); err != nil {
			return nil, 0, fmt.Errorf("scan ticket: %w", err)
		}
		_ = json.Unmarshal(metaBytes, &t.Metadata)
		results = append(results, t)
	}

	return results, total, nil
}

func (r *Repository) UpdateTicket(ctx context.Context, id int64, req UpdateTicketRequest) (*Ticket, error) {
	var setClauses []string
	var args []any
	argIdx := 1

	if req.Status != nil {
		setClauses = append(setClauses, fmt.Sprintf("status = $%d", argIdx))
		args = append(args, *req.Status)
		argIdx++

		if *req.Status == "resolved" || *req.Status == "closed" {
			setClauses = append(setClauses, "resolved_at = now()")
		} else {
			setClauses = append(setClauses, "resolved_at = NULL")
		}
	}

	if req.Priority != nil {
		setClauses = append(setClauses, fmt.Sprintf("priority = $%d", argIdx))
		args = append(args, *req.Priority)
		argIdx++
	}

	if req.AssignedToID != nil {
		setClauses = append(setClauses, fmt.Sprintf("assigned_to_id = $%d", argIdx))
		args = append(args, *req.AssignedToID)
		argIdx++
	}

	setClauses = append(setClauses, "updated_at = now()")

	if len(setClauses) == 0 {
		return r.GetTicketByID(ctx, id)
	}

	args = append(args, id)
	query := fmt.Sprintf(`
		UPDATE tickets
		SET %s
		WHERE id = $%d
	`, strings.Join(setClauses, ", "), argIdx)

	tag, err := r.pool.Exec(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("update ticket: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return nil, ErrTicketNotFound
	}

	return r.GetTicketByID(ctx, id)
}

func (r *Repository) CreateComment(ctx context.Context, c *TicketComment) (*TicketComment, error) {
	query := `
		INSERT INTO ticket_comments (
			ticket_id, user_id, user_name, comment, is_internal, created_at
		) VALUES (
			$1, $2, $3, $4, $5, now()
		)
		RETURNING id, created_at
	`

	err := r.pool.QueryRow(ctx, query,
		c.TicketID, c.UserID, c.UserName, c.Comment, c.IsInternal,
	).Scan(&c.ID, &c.CreatedAt)

	if err != nil {
		return nil, fmt.Errorf("insert comment: %w", err)
	}

	// Update ticket updated_at
	_, _ = r.pool.Exec(ctx, `UPDATE tickets SET updated_at = now() WHERE id = $1`, c.TicketID)

	return c, nil
}

func (r *Repository) ListCommentsByTicketID(ctx context.Context, ticketID int64, includeInternal bool) ([]TicketComment, error) {
	query := `
		SELECT id, ticket_id, user_id, user_name, comment, is_internal, created_at
		FROM ticket_comments
		WHERE ticket_id = $1
	`
	if !includeInternal {
		query += " AND is_internal = false"
	}
	query += " ORDER BY created_at ASC"

	rows, err := r.pool.Query(ctx, query, ticketID)
	if err != nil {
		return nil, fmt.Errorf("list comments: %w", err)
	}
	defer rows.Close()

	var comments []TicketComment
	for rows.Next() {
		var c TicketComment
		if err := rows.Scan(&c.ID, &c.TicketID, &c.UserID, &c.UserName, &c.Comment, &c.IsInternal, &c.CreatedAt); err != nil {
			return nil, fmt.Errorf("scan comment: %w", err)
		}
		comments = append(comments, c)
	}

	return comments, nil
}