package repository

import (
	"context"
	"lune/talentscale/internal/domain"
	"time"

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

type postgresPackageRepository struct {
	db *pgxpool.Pool
}

func NewPostgresPackageRepository(db *pgxpool.Pool) domain.PackageRepository {
	return &postgresPackageRepository{db: db}
}

func (r *postgresPackageRepository) Create(ctx context.Context, pkg *domain.Package) error {
	query := `INSERT INTO packages (
		id, name, description, price, billing_cycle, order_number, 
		max_candidates_per_month, max_users, highlight_package, badge_label, 
		button_text, button_variant, is_feature_all_tests, is_feature_pdf_report, 
		is_feature_dashboard, is_feature_priority_support, is_feature_api_access, 
		is_feature_custom_branding, is_feature_multi_user, is_feature_export_excel, 
		is_feature_candidate_history, is_feature_ai_insight, theme_color, 
		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)`

	_, err := r.db.Exec(ctx, query,
		pkg.ID, pkg.Name, pkg.Description, pkg.Price, pkg.BillingCycle, pkg.OrderNumber,
		pkg.MaxCandidatesPerMonth, pkg.MaxUsers, pkg.HighlightPackage, pkg.BadgeLabel,
		pkg.ButtonText, pkg.ButtonVariant, pkg.IsFeatureAllTests, pkg.IsFeaturePdfReport,
		pkg.IsFeatureDashboard, pkg.IsFeaturePrioritySupport, pkg.IsFeatureApiAccess,
		pkg.IsFeatureCustomBranding, pkg.IsFeatureMultiUser, pkg.IsFeatureExportExcel,
		pkg.IsFeatureCandidateHistory, pkg.IsFeatureAiInsight, pkg.ThemeColor,
		pkg.CreatedAt, pkg.UpdatedAt,
	)
	return err
}

func (r *postgresPackageRepository) GetByID(ctx context.Context, id uuid.UUID) (*domain.Package, error) {
	query := `SELECT 
		id, name, description, price, billing_cycle, order_number, 
		max_candidates_per_month, max_users, highlight_package, badge_label, 
		button_text, button_variant, is_feature_all_tests, is_feature_pdf_report, 
		is_feature_dashboard, is_feature_priority_support, is_feature_api_access, 
		is_feature_custom_branding, is_feature_multi_user, is_feature_export_excel, 
		is_feature_candidate_history, is_feature_ai_insight, theme_color, 
		created_at, updated_at
	FROM packages WHERE id = $1 AND deleted_at IS NULL`

	var p domain.Package
	err := r.db.QueryRow(ctx, query, id).Scan(
		&p.ID, &p.Name, &p.Description, &p.Price, &p.BillingCycle, &p.OrderNumber,
		&p.MaxCandidatesPerMonth, &p.MaxUsers, &p.HighlightPackage, &p.BadgeLabel,
		&p.ButtonText, &p.ButtonVariant, &p.IsFeatureAllTests, &p.IsFeaturePdfReport,
		&p.IsFeatureDashboard, &p.IsFeaturePrioritySupport, &p.IsFeatureApiAccess,
		&p.IsFeatureCustomBranding, &p.IsFeatureMultiUser, &p.IsFeatureExportExcel,
		&p.IsFeatureCandidateHistory, &p.IsFeatureAiInsight, &p.ThemeColor,
		&p.CreatedAt, &p.UpdatedAt,
	)
	if err != nil {
		return nil, err
	}
	return &p, nil
}

func (r *postgresPackageRepository) List(ctx context.Context) ([]domain.Package, error) {
	query := `SELECT 
		id, name, description, price, billing_cycle, order_number, 
		max_candidates_per_month, max_users, highlight_package, badge_label, 
		button_text, button_variant, is_feature_all_tests, is_feature_pdf_report, 
		is_feature_dashboard, is_feature_priority_support, is_feature_api_access, 
		is_feature_custom_branding, is_feature_multi_user, is_feature_export_excel, 
		is_feature_candidate_history, is_feature_ai_insight, theme_color, 
		created_at, updated_at
	FROM packages WHERE deleted_at IS NULL ORDER BY order_number ASC`

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

	var pkgs []domain.Package
	for rows.Next() {
		var p domain.Package
		err := rows.Scan(
			&p.ID, &p.Name, &p.Description, &p.Price, &p.BillingCycle, &p.OrderNumber,
			&p.MaxCandidatesPerMonth, &p.MaxUsers, &p.HighlightPackage, &p.BadgeLabel,
			&p.ButtonText, &p.ButtonVariant, &p.IsFeatureAllTests, &p.IsFeaturePdfReport,
			&p.IsFeatureDashboard, &p.IsFeaturePrioritySupport, &p.IsFeatureApiAccess,
			&p.IsFeatureCustomBranding, &p.IsFeatureMultiUser, &p.IsFeatureExportExcel,
			&p.IsFeatureCandidateHistory, &p.IsFeatureAiInsight, &p.ThemeColor,
			&p.CreatedAt, &p.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}
		pkgs = append(pkgs, p)
	}
	return pkgs, nil
}

func (r *postgresPackageRepository) Update(ctx context.Context, pkg *domain.Package) error {
	query := `UPDATE packages SET 
		name=$1, description=$2, price=$3, billing_cycle=$4, order_number=$5, 
		max_candidates_per_month=$6, max_users=$7, highlight_package=$8, badge_label=$9, 
		button_text=$10, button_variant=$11, is_feature_all_tests=$12, is_feature_pdf_report=$13, 
		is_feature_dashboard=$14, is_feature_priority_support=$15, is_feature_api_access=$16, 
		is_feature_custom_branding=$17, is_feature_multi_user=$18, is_feature_export_excel=$19, 
		is_feature_candidate_history=$20, is_feature_ai_insight=$21, theme_color=$22, 
		updated_at=$23 
	WHERE id=$24 AND deleted_at IS NULL`

	_, err := r.db.Exec(ctx, query,
		pkg.Name, pkg.Description, pkg.Price, pkg.BillingCycle, pkg.OrderNumber,
		pkg.MaxCandidatesPerMonth, pkg.MaxUsers, pkg.HighlightPackage, pkg.BadgeLabel,
		pkg.ButtonText, pkg.ButtonVariant, pkg.IsFeatureAllTests, pkg.IsFeaturePdfReport,
		pkg.IsFeatureDashboard, pkg.IsFeaturePrioritySupport, pkg.IsFeatureApiAccess,
		pkg.IsFeatureCustomBranding, pkg.IsFeatureMultiUser, pkg.IsFeatureExportExcel,
		pkg.IsFeatureCandidateHistory, pkg.IsFeatureAiInsight, pkg.ThemeColor,
		time.Now(), pkg.ID,
	)
	return err
}

func (r *postgresPackageRepository) Delete(ctx context.Context, id uuid.UUID) error {
	query := `UPDATE packages SET deleted_at=$1 WHERE id=$2 AND deleted_at IS NULL`
	_, err := r.db.Exec(ctx, query, time.Now(), id)
	return err
}