package repository

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

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
	"backend/internal/models"
)

type InternetPackageRepository struct {
	db *pgxpool.Pool
}

func NewInternetPackageRepository(db *pgxpool.Pool) *InternetPackageRepository {
	return &InternetPackageRepository{db: db}
}

// FindByID finds an internet package by ID
func (r *InternetPackageRepository) FindByID(ctx context.Context, id uuid.UUID, clientID string) (*models.InternetPackage, error) {
	query := `
		SELECT id, client_id, package_type, name, description, duration, duration_minutes,
		       upload_speed, download_speed, speed, amount, currency, devices, total_limit,
		       burst_enabled, burst_limit, burst_threshold, burst_time,
		       schedule_enabled, schedule_start_time, schedule_end_time, available_days,
		       hidden_from_client, features, category, popular, is_active,
		       created_at, updated_at, deleted_at
		FROM internet_packages
		WHERE id = $1 AND client_id = $2 AND deleted_at IS NULL
	`

	var pkg models.InternetPackage
	var availableDaysJSON, featuresJSON []byte

	err := r.db.QueryRow(ctx, query, id.String(), clientID).Scan(
		&pkg.ID, &pkg.ClientID, &pkg.PackageType, &pkg.Name, &pkg.Description,
		&pkg.Duration, &pkg.DurationMinutes, &pkg.UploadSpeed, &pkg.DownloadSpeed,
		&pkg.Speed, &pkg.Amount, &pkg.Currency, &pkg.Devices, &pkg.TotalLimit,
		&pkg.BurstEnabled, &pkg.BurstLimit, &pkg.BurstThreshold, &pkg.BurstTime,
		&pkg.ScheduleEnabled, &pkg.ScheduleStartTime, &pkg.ScheduleEndTime, &availableDaysJSON,
		&pkg.HiddenFromClient, &featuresJSON, &pkg.Category, &pkg.Popular, &pkg.IsActive,
		&pkg.CreatedAt, &pkg.UpdatedAt, &pkg.DeletedAt,
	)

	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, errors.New("internet package not found")
		}
		return nil, err
	}

	// Parse JSON fields
	if availableDaysJSON != nil {
		var days models.JSONArray
		if err := json.Unmarshal(availableDaysJSON, &days); err == nil {
			pkg.AvailableDays = &days
		}
	}
	if featuresJSON != nil {
		var features models.JSONArray
		if err := json.Unmarshal(featuresJSON, &features); err == nil {
			pkg.Features = &features
		}
	}

	return &pkg, nil
}

// FindAll finds all internet packages for a client
func (r *InternetPackageRepository) FindAll(ctx context.Context, clientID string, activeOnly bool, packageType *string, searchQuery *string, limit, offset int) ([]*models.InternetPackage, error) {
	query := `
		SELECT id, client_id, package_type, name, description, duration, duration_minutes,
		       upload_speed, download_speed, speed, amount, currency, devices, total_limit,
		       burst_enabled, burst_limit, burst_threshold, burst_time,
		       schedule_enabled, schedule_start_time, schedule_end_time, available_days,
		       hidden_from_client, features, category, popular, is_active,
		       created_at, updated_at, deleted_at
		FROM internet_packages
		WHERE client_id = $1 AND deleted_at IS NULL
	`
	args := []interface{}{clientID}
	argIndex := 2

	if activeOnly {
		query += ` AND is_active = true`
	}

	if packageType != nil && *packageType != "" {
		// Normalize package type for comparison
		normalizedType := *packageType
		if normalizedType == "hotspot" {
			normalizedType = "Hotspot"
		} else if normalizedType == "pppoe" {
			normalizedType = "PPPoE"
		} else if normalizedType == "trial" || normalizedType == "free_trial" {
			normalizedType = "Trial"
		} else if normalizedType == "bundles" || normalizedType == "data_plans" {
			normalizedType = "Bundles"
		}
		query += ` AND package_type = $` + fmt.Sprintf("%d", argIndex)
		args = append(args, normalizedType)
		argIndex++
	}

	if searchQuery != nil && *searchQuery != "" {
		query += ` AND (LOWER(name) LIKE $` + fmt.Sprintf("%d", argIndex) + ` OR LOWER(description) LIKE $` + fmt.Sprintf("%d", argIndex) + `)`
		searchPattern := "%" + strings.ToLower(*searchQuery) + "%"
		args = append(args, searchPattern)
		argIndex++
	}

	query += ` ORDER BY created_at DESC`

	if limit > 0 {
		query += ` LIMIT $` + fmt.Sprintf("%d", argIndex)
		args = append(args, limit)
		argIndex++
	}
	if offset > 0 {
		query += ` OFFSET $` + fmt.Sprintf("%d", argIndex)
		args = append(args, offset)
	}

	rows, err := r.db.Query(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var packages []*models.InternetPackage
	for rows.Next() {
		var pkg models.InternetPackage
		var availableDaysJSON, featuresJSON []byte

		err := rows.Scan(
			&pkg.ID, &pkg.ClientID, &pkg.PackageType, &pkg.Name, &pkg.Description,
			&pkg.Duration, &pkg.DurationMinutes, &pkg.UploadSpeed, &pkg.DownloadSpeed,
			&pkg.Speed, &pkg.Amount, &pkg.Currency, &pkg.Devices, &pkg.TotalLimit,
			&pkg.BurstEnabled, &pkg.BurstLimit, &pkg.BurstThreshold, &pkg.BurstTime,
			&pkg.ScheduleEnabled, &pkg.ScheduleStartTime, &pkg.ScheduleEndTime, &availableDaysJSON,
			&pkg.HiddenFromClient, &featuresJSON, &pkg.Category, &pkg.Popular, &pkg.IsActive,
			&pkg.CreatedAt, &pkg.UpdatedAt, &pkg.DeletedAt,
		)
		if err != nil {
			return nil, err
		}

		// Parse JSON fields
		if availableDaysJSON != nil {
			var days models.JSONArray
			if err := json.Unmarshal(availableDaysJSON, &days); err == nil {
				pkg.AvailableDays = &days
			}
		}
		if featuresJSON != nil {
			var features models.JSONArray
			if err := json.Unmarshal(featuresJSON, &features); err == nil {
				pkg.Features = &features
			}
		}

		packages = append(packages, &pkg)
	}

	return packages, rows.Err()
}

// Create creates a new internet package
func (r *InternetPackageRepository) Create(ctx context.Context, pkg *models.InternetPackage) error {
	query := `
		INSERT INTO internet_packages (
			id, client_id, package_type, name, description, duration, duration_minutes,
			upload_speed, download_speed, speed, amount, currency, devices, total_limit,
			burst_enabled, burst_limit, burst_threshold, burst_time,
			schedule_enabled, schedule_start_time, schedule_end_time, available_days,
			hidden_from_client, features, category, popular, is_active,
			created_at, updated_at
		) VALUES (
			$1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14,
			$15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27,
			CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
		) RETURNING created_at, updated_at
	`

	var availableDaysJSON, featuresJSON interface{}
	if pkg.AvailableDays != nil {
		availableDaysJSON, _ = pkg.AvailableDays.Value()
	}
	if pkg.Features != nil {
		featuresJSON, _ = pkg.Features.Value()
	}

	currency := pkg.Currency
	if currency == "" {
		currency = "KES"
	}

	err := r.db.QueryRow(
		ctx, query,
		pkg.ID.String(), pkg.ClientID, pkg.PackageType, pkg.Name, pkg.Description,
		pkg.Duration, pkg.DurationMinutes, pkg.UploadSpeed, pkg.DownloadSpeed,
		pkg.Speed, pkg.Amount, currency, pkg.Devices, pkg.TotalLimit,
		pkg.BurstEnabled, pkg.BurstLimit, pkg.BurstThreshold, pkg.BurstTime,
		pkg.ScheduleEnabled, pkg.ScheduleStartTime, pkg.ScheduleEndTime, availableDaysJSON,
		pkg.HiddenFromClient, featuresJSON, pkg.Category, pkg.Popular, pkg.IsActive,
	).Scan(&pkg.CreatedAt, &pkg.UpdatedAt)

	return err
}

// Update updates an existing internet package
func (r *InternetPackageRepository) Update(ctx context.Context, pkg *models.InternetPackage) error {
	query := `
		UPDATE internet_packages
		SET package_type = $3,
		    name = $4,
		    description = $5,
		    duration = $6,
		    duration_minutes = $7,
		    upload_speed = $8,
		    download_speed = $9,
		    speed = $10,
		    amount = $11,
		    currency = $12,
		    devices = $13,
		    total_limit = $14,
		    burst_enabled = $15,
		    burst_limit = $16,
		    burst_threshold = $17,
		    burst_time = $18,
		    schedule_enabled = $19,
		    schedule_start_time = $20,
		    schedule_end_time = $21,
		    available_days = $22,
		    hidden_from_client = $23,
		    features = $24,
		    category = $25,
		    popular = $26,
		    is_active = $27,
		    updated_at = CURRENT_TIMESTAMP
		WHERE id = $1 AND client_id = $2 AND deleted_at IS NULL
		RETURNING updated_at
	`

	var availableDaysJSON, featuresJSON interface{}
	if pkg.AvailableDays != nil {
		availableDaysJSON, _ = pkg.AvailableDays.Value()
	}
	if pkg.Features != nil {
		featuresJSON, _ = pkg.Features.Value()
	}

	currency := pkg.Currency
	if currency == "" {
		currency = "KES"
	}

	err := r.db.QueryRow(
		ctx, query,
		pkg.ID.String(), pkg.ClientID, pkg.PackageType, pkg.Name, pkg.Description,
		pkg.Duration, pkg.DurationMinutes, pkg.UploadSpeed, pkg.DownloadSpeed,
		pkg.Speed, pkg.Amount, currency, pkg.Devices, pkg.TotalLimit,
		pkg.BurstEnabled, pkg.BurstLimit, pkg.BurstThreshold, pkg.BurstTime,
		pkg.ScheduleEnabled, pkg.ScheduleStartTime, pkg.ScheduleEndTime, availableDaysJSON,
		pkg.HiddenFromClient, featuresJSON, pkg.Category, pkg.Popular, pkg.IsActive,
	).Scan(&pkg.UpdatedAt)

	return err
}

// Delete soft deletes an internet package
func (r *InternetPackageRepository) Delete(ctx context.Context, id uuid.UUID, clientID string) error {
	query := `
		UPDATE internet_packages
		SET deleted_at = CURRENT_TIMESTAMP,
		    updated_at = CURRENT_TIMESTAMP
		WHERE id = $1 AND client_id = $2 AND deleted_at IS NULL
	`

	_, err := r.db.Exec(ctx, query, id.String(), clientID)
	return err
}

// Count counts the number of packages for a client
func (r *InternetPackageRepository) Count(ctx context.Context, clientID string, activeOnly bool, packageType *string, searchQuery *string) (int, error) {
	query := `
		SELECT COUNT(*) FROM internet_packages
		WHERE client_id = $1 AND deleted_at IS NULL
	`
	args := []interface{}{clientID}
	argIndex := 2

	if activeOnly {
		query += ` AND is_active = true`
	}

	if packageType != nil && *packageType != "" {
		// Normalize package type for comparison
		normalizedType := *packageType
		if normalizedType == "hotspot" {
			normalizedType = "Hotspot"
		} else if normalizedType == "pppoe" {
			normalizedType = "PPPoE"
		} else if normalizedType == "trial" || normalizedType == "free_trial" {
			normalizedType = "Trial"
		} else if normalizedType == "bundles" || normalizedType == "data_plans" {
			normalizedType = "Bundles"
		}
		query += ` AND package_type = $` + fmt.Sprintf("%d", argIndex)
		args = append(args, normalizedType)
		argIndex++
	}

	if searchQuery != nil && *searchQuery != "" {
		query += ` AND (LOWER(name) LIKE $` + fmt.Sprintf("%d", argIndex) + ` OR LOWER(description) LIKE $` + fmt.Sprintf("%d", argIndex) + `)`
		searchPattern := "%" + strings.ToLower(*searchQuery) + "%"
		args = append(args, searchPattern)
		argIndex++
	}

	var count int
	err := r.db.QueryRow(ctx, query, args...).Scan(&count)
	return count, err
}