package repository

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

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

type TransactionRepository struct {
	db *pgxpool.Pool
}

func NewTransactionRepository(db *pgxpool.Pool) *TransactionRepository {
	return &TransactionRepository{db: db}
}

// FindByID finds a transaction by ID
func (r *TransactionRepository) FindByID(ctx context.Context, id uuid.UUID, clientID string) (*models.Transaction, error) {
	// Check if users table exists
	var usersTableExists bool
	checkTableQuery := `
		SELECT EXISTS (
			SELECT FROM information_schema.tables 
			WHERE table_schema = CURRENT_SCHEMA() 
			AND table_name = 'users'
		)
	`
	err := r.db.QueryRow(ctx, checkTableQuery).Scan(&usersTableExists)
	if err != nil {
		log.Printf("Warning: Could not check if users table exists: %v. Proceeding without user data.", err)
		usersTableExists = false
	}

	var query string
	if usersTableExists {
		query = `
			SELECT t.id, t.client_id, t.user_id, t.package_id, t.checkout_id, t.amount, t.currency, t.payment_method, t.payment_status,
			       t.phone_number, t.reference, t.mpesa_receipt, t.description, t.metadata, t.provider,
			       t.provider_response, t.created_at, t.updated_at, t.deleted_at,
			       u.id as user_id_full, u.name as user_name, u.username as user_username, u.email as user_email, u.phone as user_phone,
			       p.id as package_id_full, p.name as package_name, p.package_type as package_type, p.description as package_description
			FROM transactions t
			LEFT JOIN users u ON t.user_id = u.id AND u.deleted_at IS NULL
			LEFT JOIN internet_packages p ON t.package_id = p.id AND p.deleted_at IS NULL
			WHERE t.id = $1 AND t.client_id = $2 AND t.deleted_at IS NULL
		`
	} else {
		query = `
			SELECT t.id, t.client_id, t.user_id, t.package_id, t.checkout_id, t.amount, t.currency, t.payment_method, t.payment_status,
			       t.phone_number, t.reference, t.mpesa_receipt, t.description, t.metadata, t.provider,
			       t.provider_response, t.created_at, t.updated_at, t.deleted_at,
			       NULL::text as user_id_full, NULL::text as user_name, NULL::text as user_username, NULL::text as user_email, NULL::text as user_phone,
			       p.id as package_id_full, p.name as package_name, p.package_type as package_type, p.description as package_description
			FROM transactions t
			LEFT JOIN internet_packages p ON t.package_id = p.id AND p.deleted_at IS NULL
			WHERE t.id = $1 AND t.client_id = $2 AND t.deleted_at IS NULL
		`
	}

	var tx models.Transaction
	var metadataJSON, providerResponseJSON []byte
	var userIDFull, userName, userUsername, userEmail, userPhone *string
	var packageIDFull, packageName, packageType, packageDescription *string

	log.Printf("Querying transaction %s for client %s", id.String(), clientID)
	err = r.db.QueryRow(ctx, query, id.String(), clientID).Scan(
		&tx.ID, &tx.ClientID, &tx.UserID, &tx.PackageID, &tx.CheckoutID, &tx.Amount, &tx.Currency,
		&tx.PaymentMethod, &tx.PaymentStatus, &tx.PhoneNumber, &tx.Reference,
		&tx.MpesaReceipt, &tx.Description, &metadataJSON, &tx.Provider,
		&providerResponseJSON, &tx.CreatedAt, &tx.UpdatedAt, &tx.DeletedAt,
		&userIDFull, &userName, &userUsername, &userEmail, &userPhone,
		&packageIDFull, &packageName, &packageType, &packageDescription,
	)

	if err != nil {
		if err == pgx.ErrNoRows {
			log.Printf("Transaction %s not found for client %s", id.String(), clientID)
			return nil, fmt.Errorf("transaction not found")
		}
		log.Printf("Error querying transaction %s for client %s: %v", id.String(), clientID, err)
		return nil, fmt.Errorf("failed to query transaction: %w", err)
	}

	// Parse JSON fields
	if len(metadataJSON) > 0 {
		var metadata models.JSONMap
		if err := json.Unmarshal(metadataJSON, &metadata); err == nil {
			tx.Metadata = &metadata
		}
	}

	if len(providerResponseJSON) > 0 {
		var providerResponse models.JSONMap
		if err := json.Unmarshal(providerResponseJSON, &providerResponse); err == nil {
			tx.ProviderResponse = &providerResponse
		}
	}

	// Set user fields
	tx.UserName = userName
	tx.UserUsername = userUsername
	tx.UserEmail = userEmail
	tx.UserPhone = userPhone

	// Set package fields
	tx.PackageID = packageIDFull
	tx.PackageName = packageName
	tx.PackageType = packageType
	tx.PackageDescription = packageDescription

	return &tx, nil
}

// FindAll finds all transactions for a client with pagination and filtering
func (r *TransactionRepository) FindAll(
	ctx context.Context,
	clientID string,
	limit, offset int,
	paymentStatus, paymentMethod, searchQuery *string,
) ([]*models.Transaction, int64, error) {
	// Build WHERE clause with table alias
	whereConditions := []string{"t.client_id = $1", "t.deleted_at IS NULL"}
	args := []interface{}{clientID}
	argIndex := 2

	if paymentStatus != nil && *paymentStatus != "" && *paymentStatus != "all" {
		whereConditions = append(whereConditions, fmt.Sprintf("t.payment_status = $%d", argIndex))
		args = append(args, *paymentStatus)
		argIndex++
	}

	if paymentMethod != nil && *paymentMethod != "" && *paymentMethod != "all" {
		whereConditions = append(whereConditions, fmt.Sprintf("t.payment_method = $%d", argIndex))
		args = append(args, *paymentMethod)
		argIndex++
	}

	if searchQuery != nil && *searchQuery != "" {
		searchPattern := "%" + strings.ToLower(*searchQuery) + "%"
		whereConditions = append(whereConditions, fmt.Sprintf(
			"(LOWER(t.checkout_id) LIKE $%d OR LOWER(t.phone_number) LIKE $%d OR LOWER(COALESCE(t.reference, '')) LIKE $%d OR LOWER(COALESCE(t.mpesa_receipt, '')) LIKE $%d)",
			argIndex, argIndex, argIndex, argIndex,
		))
		args = append(args, searchPattern)
		argIndex++
	}

	whereClause := strings.Join(whereConditions, " AND ")

	// Count query - use transactions table alias
	countQuery := fmt.Sprintf("SELECT COUNT(*) FROM transactions t WHERE %s", whereClause)
	var total int64
	err := r.db.QueryRow(ctx, countQuery, args...).Scan(&total)
	if err != nil {
		return nil, 0, fmt.Errorf("failed to count transactions: %w", err)
	}

	// Check if users table exists
	var usersTableExists bool
	checkTableQuery := `
		SELECT EXISTS (
			SELECT FROM information_schema.tables 
			WHERE table_schema = CURRENT_SCHEMA() 
			AND table_name = 'users'
		)
	`
	err = r.db.QueryRow(ctx, checkTableQuery).Scan(&usersTableExists)
	if err != nil {
		log.Printf("Warning: Could not check if users table exists: %v. Proceeding without user data.", err)
		usersTableExists = false
	}

	// Data query with optional LEFT JOIN to users and internet_packages tables
	var query string
	if usersTableExists {
		query = fmt.Sprintf(`
			SELECT t.id, t.client_id, t.user_id, t.package_id, t.checkout_id, t.amount, t.currency, t.payment_method, t.payment_status,
			       t.phone_number, t.reference, t.mpesa_receipt, t.description, t.metadata, t.provider,
			       t.provider_response, t.created_at, t.updated_at, t.deleted_at,
			       u.id as user_id_full, u.name as user_name, u.username as user_username, u.email as user_email, u.phone as user_phone,
			       p.id as package_id_full, p.name as package_name, p.package_type as package_type, p.description as package_description
			FROM transactions t
			LEFT JOIN users u ON t.user_id = u.id AND u.deleted_at IS NULL
			LEFT JOIN internet_packages p ON t.package_id = p.id AND p.deleted_at IS NULL
			WHERE %s
			ORDER BY t.created_at DESC
			LIMIT $%d OFFSET $%d
		`, whereClause, argIndex, argIndex+1)
	} else {
		// Query without users table join but with packages
		query = fmt.Sprintf(`
			SELECT t.id, t.client_id, t.user_id, t.package_id, t.checkout_id, t.amount, t.currency, t.payment_method, t.payment_status,
			       t.phone_number, t.reference, t.mpesa_receipt, t.description, t.metadata, t.provider,
			       t.provider_response, t.created_at, t.updated_at, t.deleted_at,
			       NULL::text as user_id_full, NULL::text as user_name, NULL::text as user_username, NULL::text as user_email, NULL::text as user_phone,
			       p.id as package_id_full, p.name as package_name, p.package_type as package_type, p.description as package_description
			FROM transactions t
			LEFT JOIN internet_packages p ON t.package_id = p.id AND p.deleted_at IS NULL
			WHERE %s
			ORDER BY t.created_at DESC
			LIMIT $%d OFFSET $%d
		`, whereClause, argIndex, argIndex+1)
	}

	args = append(args, limit, offset)

	log.Printf("Executing transaction query for client %s with limit %d, offset %d", clientID, limit, offset)
	rows, err := r.db.Query(ctx, query, args...)
	if err != nil {
		log.Printf("Error executing transaction query: %v. Query: %s. Args: %v", err, query, args)
		return nil, 0, fmt.Errorf("failed to query transactions: %w", err)
	}
	defer rows.Close()

	var transactions []*models.Transaction
	for rows.Next() {
		var tx models.Transaction
		var metadataJSON, providerResponseJSON []byte
		var userIDFull, userName, userUsername, userEmail, userPhone *string
		var packageIDFull, packageName, packageType, packageDescription *string

		err := rows.Scan(
			&tx.ID, &tx.ClientID, &tx.UserID, &tx.PackageID, &tx.CheckoutID, &tx.Amount, &tx.Currency,
			&tx.PaymentMethod, &tx.PaymentStatus, &tx.PhoneNumber, &tx.Reference,
			&tx.MpesaReceipt, &tx.Description, &metadataJSON, &tx.Provider,
			&providerResponseJSON, &tx.CreatedAt, &tx.UpdatedAt, &tx.DeletedAt,
			&userIDFull, &userName, &userUsername, &userEmail, &userPhone,
			&packageIDFull, &packageName, &packageType, &packageDescription,
		)
		if err != nil {
			return nil, 0, fmt.Errorf("failed to scan transaction row: %w", err)
		}

		// Parse JSON fields
		if len(metadataJSON) > 0 {
			var metadata models.JSONMap
			if err := json.Unmarshal(metadataJSON, &metadata); err == nil {
				tx.Metadata = &metadata
			}
		}

		if len(providerResponseJSON) > 0 {
			var providerResponse models.JSONMap
			if err := json.Unmarshal(providerResponseJSON, &providerResponse); err == nil {
				tx.ProviderResponse = &providerResponse
			}
		}

		// Set user fields
		tx.UserName = userName
		tx.UserUsername = userUsername
		tx.UserEmail = userEmail
		tx.UserPhone = userPhone

		// Set package fields
		tx.PackageID = packageIDFull
		tx.PackageName = packageName
		tx.PackageType = packageType
		tx.PackageDescription = packageDescription

		transactions = append(transactions, &tx)
	}

	if err = rows.Err(); err != nil {
		return nil, 0, err
	}

	return transactions, total, nil
}