package services

import (
	"context"
	"errors"
	"fmt"
	"time"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"jd-book-uploader/models"
	"jd-book-uploader/utils"
)

// CreateStationery creates a new stationery record in the database
func CreateStationery(ctx context.Context, req *models.StationeryCreateRequest) (*models.Stationery, error) {
	if DB == nil {
		return nil, fmt.Errorf("database connection not initialized")
	}

	// Generate UUID for stationery_code
	stationeryCode := uuid.New().String()

	// Generate slug from stationery name
	slug := utils.GenerateSlug(req.StationeryName)

	// Set default discount if not provided
	discount := req.Discount
	if discount < 0 {
		discount = 0
	}

	now := time.Now()

	// Insert stationery into database
	query := `
		INSERT INTO stationery (
			stationery_code, stationery_name, cost, price, discount, quantity,
			color, material, dimensions, category, description, image_url, slug,
			created_at, updated_at
		) VALUES (
			$1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15
		) RETURNING 
			stationery_code, stationery_name, cost, price, discount, quantity,
			color, material, dimensions, category, description, image_url, slug,
			created_at, updated_at
	`

	var stationery models.Stationery
	err := DB.QueryRow(ctx, query,
		stationeryCode,
		req.StationeryName,
		req.Cost,
		req.Price,
		discount,
		req.Quantity,
		req.Color,
		req.Material,
		req.Dimensions,
		req.Category,
		req.Description,
		req.ImageURL,
		slug,
		now,
		now,
	).Scan(
		&stationery.StationeryCode,
		&stationery.StationeryName,
		&stationery.Cost,
		&stationery.Price,
		&stationery.Discount,
		&stationery.Quantity,
		&stationery.Color,
		&stationery.Material,
		&stationery.Dimensions,
		&stationery.Category,
		&stationery.Description,
		&stationery.ImageURL,
		&stationery.Slug,
		&stationery.CreatedAt,
		&stationery.UpdatedAt,
	)

	if err != nil {
		// Check for duplicate key error
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) {
			if pgErr.Code == "23505" { // unique_violation
				return nil, fmt.Errorf("stationery with this code already exists")
			}
		}
		return nil, fmt.Errorf("failed to create stationery: %w", err)
	}

	return &stationery, nil
}

// GetStationeryByCode retrieves a stationery item by its code
func GetStationeryByCode(ctx context.Context, stationeryCode string) (*models.Stationery, error) {
	if DB == nil {
		return nil, fmt.Errorf("database connection not initialized")
	}

	query := `
		SELECT 
			stationery_code, stationery_name, cost, price, discount, quantity,
			color, material, dimensions, category, description, image_url, slug,
			created_at, updated_at
		FROM stationery
		WHERE stationery_code = $1
	`

	var stationery models.Stationery
	err := DB.QueryRow(ctx, query, stationeryCode).Scan(
		&stationery.StationeryCode,
		&stationery.StationeryName,
		&stationery.Cost,
		&stationery.Price,
		&stationery.Discount,
		&stationery.Quantity,
		&stationery.Color,
		&stationery.Material,
		&stationery.Dimensions,
		&stationery.Category,
		&stationery.Description,
		&stationery.ImageURL,
		&stationery.Slug,
		&stationery.CreatedAt,
		&stationery.UpdatedAt,
	)

	if err != nil {
		if err == pgx.ErrNoRows {
			return nil, fmt.Errorf("stationery not found")
		}
		return nil, fmt.Errorf("failed to get stationery: %w", err)
	}

	return &stationery, nil
}

// StationerySlugExists checks if a slug already exists in the stationery table
func StationerySlugExists(ctx context.Context, slug string) (bool, error) {
	if DB == nil {
		return false, fmt.Errorf("database connection not initialized")
	}

	query := `SELECT EXISTS(SELECT 1 FROM stationery WHERE slug = $1)`
	var exists bool
	err := DB.QueryRow(ctx, query, slug).Scan(&exists)
	if err != nil {
		return false, fmt.Errorf("failed to check slug existence: %w", err)
	}

	return exists, nil
}