package repositories

import (
	"context"
	"database/sql"
	"fmt"
	"strings"

	"report_go/internal/models"
)

type CaseReportRepository struct {
	DB *sql.DB
}

func NewCaseReportRepository(db *sql.DB) *CaseReportRepository {
	return &CaseReportRepository{DB: db}
}

func (r *CaseReportRepository) FindCases(ctx context.Context, f models.CaseReportFilter) (*models.CaseListResult, error) {
	whereSQL, args := buildWhere(f)

	countSQL := "SELECT COUNT(*) FROM service_cases WHERE " + whereSQL

	var total int64
	if err := r.DB.QueryRowContext(ctx, countSQL, args...).Scan(&total); err != nil {
		return nil, fmt.Errorf("count cases: %w", err)
	}

	sqlStr := `
	SELECT
    case_id,
    COALESCE(case_no,'') AS case_no,
    DATE_FORMAT(reported_at, '%Y-%m-%d %H:%i:%s') AS reported_at,
    COALESCE(status,'') AS status,
    COALESCE(case_service_code,'') AS case_service_code,
    COALESCE(customer_name,'') AS customer_name,
    COALESCE(customer_phone,'') AS customer_phone,
    COALESCE(vehicle_label,'') AS vehicle_label,
    COALESCE(partner_ref,'') AS partner_ref
FROM service_cases
	WHERE ` + whereSQL + `
	ORDER BY reported_at DESC, case_id DESC
	LIMIT ? OFFSET ?
`

	argsWithPage := append(args, f.Limit, f.Offset)

	rows, err := r.DB.QueryContext(ctx, sqlStr, argsWithPage...)
	if err != nil {
		return nil, fmt.Errorf("query cases: %w", err)
	}
	defer rows.Close()

	resultRows := make([]models.CaseReportRow, 0, f.Limit)

	for rows.Next() {
		var row models.CaseReportRow
		if err := rows.Scan(
			&row.CaseID,
			&row.CaseNo,
			&row.ReportedAt,
			&row.Status,
			&row.CaseServiceCode,
			&row.CustomerName,
			&row.CustomerPhone,
			&row.VehicleLabel,
			&row.PartnerRef,
		); err != nil {
			return nil, fmt.Errorf("scan case row: %w", err)
		}
		resultRows = append(resultRows, row)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate case rows: %w", err)
	}

	return &models.CaseListResult{
		Total: total,
		Page:  f.Page,
		Limit: f.Limit,
		Rows:  resultRows,
	}, nil
}

func (r *CaseReportRepository) GetSummary(ctx context.Context, f models.CaseReportFilter) (*models.CaseSummary, error) {
	whereSQL, args := buildWhere(f)

	totalSQL := "SELECT COUNT(*) FROM service_cases WHERE " + whereSQL
	doneSQL := "SELECT COUNT(*) FROM service_cases WHERE " + whereSQL + " AND status = ?"
	cancelSQL := "SELECT COUNT(*) FROM service_cases WHERE " + whereSQL + " AND status = ?"

	var total, done, cancel int64

	if err := r.DB.QueryRowContext(ctx, totalSQL, args...).Scan(&total); err != nil {
		return nil, fmt.Errorf("summary total: %w", err)
	}

	doneArgs := append(cloneArgs(args), "done")
	if err := r.DB.QueryRowContext(ctx, doneSQL, doneArgs...).Scan(&done); err != nil {
		return nil, fmt.Errorf("summary done: %w", err)
	}

	cancelArgs := append(cloneArgs(args), "cancel")
	if err := r.DB.QueryRowContext(ctx, cancelSQL, cancelArgs...).Scan(&cancel); err != nil {
		return nil, fmt.Errorf("summary cancel: %w", err)
	}

	pending := total - done - cancel
	if pending < 0 {
		pending = 0
	}

	return &models.CaseSummary{
		TotalCases:     total,
		CompletedCases: done,
		CanceledCases:  cancel,
		PendingCases:   pending,
	}, nil
}

func buildWhere(f models.CaseReportFilter) (string, []any) {
	clauses := make([]string, 0, 6)
	args := make([]any, 0, 8)

	clauses = append(clauses, "partner_company_id = ?")
	args = append(args, f.PartnerCompanyID)

	if strings.TrimSpace(f.Keyword) != "" {
		clauses = append(clauses, `(
			case_no LIKE ?
			OR customer_name LIKE ?
			OR customer_phone LIKE ?
			OR vehicle_label LIKE ?
			OR partner_ref LIKE ?
		)`)
		kw := "%" + strings.TrimSpace(f.Keyword) + "%"
		args = append(args, kw, kw, kw, kw, kw)
	}

	if strings.TrimSpace(f.Status) != "" {
		clauses = append(clauses, "status = ?")
		args = append(args, strings.TrimSpace(f.Status))
	}

	if strings.TrimSpace(f.CaseServiceCode) != "" {
		clauses = append(clauses, "case_service_code = ?")
		args = append(args, strings.TrimSpace(f.CaseServiceCode))
	}

	if strings.TrimSpace(f.DateFrom) != "" {
		clauses = append(clauses, "DATE(reported_at) >= ?")
		args = append(args, strings.TrimSpace(f.DateFrom))
	}

	if strings.TrimSpace(f.DateTo) != "" {
		clauses = append(clauses, "DATE(reported_at) <= ?")
		args = append(args, strings.TrimSpace(f.DateTo))
	}

	if len(clauses) == 0 {
		return "1=1", args
	}

	return strings.Join(clauses, " AND "), args
}

func cloneArgs(src []any) []any {
	dst := make([]any, len(src))
	copy(dst, src)
	return dst
}