// Package repo provides SQLite implementations of repository interfaces.
package repo

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

	"go-crm/pkg/domain"
)

// SQLiteLeadRepository implements domain.LeadRepository using SQLite.
type SQLiteLeadRepository struct {
	DB *sql.DB
}

// NewSQLiteLeadRepository returns a new SQLiteLeadRepository.
func NewSQLiteLeadRepository(db *sql.DB) *SQLiteLeadRepository {
	return &SQLiteLeadRepository{DB: db}
}

// ListAll retrieves leads for a client with pagination.
func (r *SQLiteLeadRepository) ListAll(ctx context.Context, clientID int64, limit, offset int) ([]domain.Lead, error) {
	query := `SELECT lead_id, client_id, name, phone_raw, phone_normalized,
		service_interest, status, needs_review,
		COALESCE(appointment_date,''), COALESCE(appointment_time,''), payment_status,
		COALESCE(payment_amount,0), COALESCE(payment_date,''), created_at, last_contact_at
		FROM leads WHERE client_id = ?
		ORDER BY created_at DESC LIMIT ? OFFSET ?`
	rows, err := r.DB.QueryContext(ctx, query, clientID, limit, offset)
	if err != nil {
		return nil, fmt.Errorf("ListAll query error: %w", err)
	}
	defer rows.Close()

	var leads []domain.Lead
	for rows.Next() {
		var l domain.Lead
		var needsReview int
		if err := rows.Scan(
			&l.LeadID, &l.ClientID, &l.Name, &l.PhoneRaw, &l.PhoneNormalized,
			&l.ServiceInterest, &l.Status, &needsReview,
			&l.AppointmentDate, &l.AppointmentTime, &l.PaymentStatus,
			&l.PaymentAmount, &l.PaymentDate, &l.CreatedAt, &l.LastContactAt,
		); err != nil {
			return nil, fmt.Errorf("ListAll scan error: %w", err)
		}
		l.NeedsReview = needsReview == 1
		leads = append(leads, l)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("ListAll rows error: %w", err)
	}
	return leads, nil
}

// Update saves changes to an existing lead.
func (r *SQLiteLeadRepository) Update(ctx context.Context, lead domain.Lead) error {
	_, err := r.DB.ExecContext(ctx,
		`UPDATE leads SET name=?, service_interest=?, status=?, needs_review=?,
		 appointment_date=?, appointment_time=?, payment_status=?, payment_amount=?, payment_date=?
		 WHERE lead_id=? AND client_id=?`,
		lead.Name, lead.ServiceInterest, lead.Status, boolToInt(lead.NeedsReview),
		lead.AppointmentDate, lead.AppointmentTime, lead.PaymentStatus, lead.PaymentAmount, lead.PaymentDate,
		lead.LeadID, lead.ClientID,
	)
	if err != nil {
		return fmt.Errorf("Update lead error: %w", err)
	}
	return nil
}

func boolToInt(b bool) int {
	if b {
		return 1
	}
	return 0
}