package catalog

import (
	"context"
	"errors"

	"github.com/TaruLab/keluargamart-backend/internal/db"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgtype"
	"github.com/jackc/pgx/v5/pgxpool"
)

// Repository implements Store on a pgx pool (sqlc-backed).
type Repository struct {
	pool *pgxpool.Pool
	q    *db.Queries
}

func NewRepository(pool *pgxpool.Pool) *Repository {
	return &Repository{pool: pool, q: db.New(pool)}
}

// --- categories --------------------------------------------------------------

func (r *Repository) CategoryByID(ctx context.Context, id int64) (db.GetCategoryByIDRow, error) {
	row, err := r.q.GetCategoryByID(ctx, id)
	if err != nil {
		return db.GetCategoryByIDRow{}, mapErr(err)
	}
	return row, nil
}

func (r *Repository) ListCategories(ctx context.Context, f CatalogFilter) ([]db.ListCategoriesRow, int, error) {
	var active pgtype.Bool
	if f.OnlyActive {
		active = pgtype.Bool{Bool: true, Valid: true}
	}
	var search pgtype.Text
	if f.Search != "" {
		search = pgtype.Text{String: f.Search, Valid: true}
	}
	total, err := r.q.CountCategories(ctx, db.CountCategoriesParams{OnlyActive: active, Search: search})
	if err != nil {
		return nil, 0, err
	}
	rows, err := r.q.ListCategories(ctx, db.ListCategoriesParams{
		Limit: int32(f.PerPage), Offset: int32((f.Page - 1) * f.PerPage), OnlyActive: active, Search: search,
	})
	if err != nil {
		return nil, 0, err
	}
	return rows, int(total), nil
}

func (r *Repository) CreateCategory(ctx context.Context, p db.CreateCategoryParams) (int64, error) {
	return r.q.CreateCategory(ctx, p)
}

func (r *Repository) UpdateCategory(ctx context.Context, p db.UpdateCategoryParams) error {
	n, err := r.q.UpdateCategory(ctx, p)
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) DeleteCategory(ctx context.Context, id int64) error {
	n, err := r.q.DeleteCategory(ctx, id)
	if err != nil {
		// FK violation = category in use by products → RESTRICT (domain-map)
		if isFKViolation(err) {
			return ErrInUse
		}
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) CategoryNameExists(ctx context.Context, name string, exceptID int64) (bool, error) {
	n, err := r.q.CountCategoriesByName(ctx, db.CountCategoriesByNameParams{Name: name, ID: exceptID})
	return n > 0, err
}

// --- units -------------------------------------------------------------------

func (r *Repository) UnitByID(ctx context.Context, id int64) (db.GetUnitByIDRow, error) {
	row, err := r.q.GetUnitByID(ctx, id)
	if err != nil {
		return db.GetUnitByIDRow{}, mapErr(err)
	}
	return row, nil
}

func (r *Repository) ListUnits(ctx context.Context, f CatalogFilter) ([]db.ListUnitsRow, int, error) {
	var search pgtype.Text
	if f.Search != "" {
		search = pgtype.Text{String: f.Search, Valid: true}
	}
	total, err := r.q.CountUnits(ctx, search)
	if err != nil {
		return nil, 0, err
	}
	rows, err := r.q.ListUnits(ctx, db.ListUnitsParams{
		Limit: int32(f.PerPage), Offset: int32((f.Page - 1) * f.PerPage), Search: search,
	})
	if err != nil {
		return nil, 0, err
	}
	return rows, int(total), nil
}

func (r *Repository) CreateUnit(ctx context.Context, p db.CreateUnitParams) (int64, error) {
	return r.q.CreateUnit(ctx, p)
}

func (r *Repository) UpdateUnit(ctx context.Context, p db.UpdateUnitParams) error {
	n, err := r.q.UpdateUnit(ctx, p)
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) DeleteUnit(ctx context.Context, id int64) error {
	n, err := r.q.DeleteUnit(ctx, id)
	if err != nil {
		if isFKViolation(err) {
			return ErrInUse
		}
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) UnitNameExists(ctx context.Context, name string, exceptID int64) (bool, error) {
	n, err := r.q.CountUnitsByName(ctx, db.CountUnitsByNameParams{Name: name, ID: exceptID})
	return n > 0, err
}

// --- products ----------------------------------------------------------------

func (r *Repository) ListProducts(ctx context.Context, f ProductFilter) ([]db.ListProductsRow, int, error) {
	var search pgtype.Text
	if f.Search != "" {
		search = pgtype.Text{String: f.Search, Valid: true}
	}
	var categoryID pgtype.Int8
	if f.CategoryID != nil {
		categoryID = pgtype.Int8{Int64: *f.CategoryID, Valid: true}
	}
	var brandID pgtype.Int8
	if f.BrandID != nil {
		brandID = pgtype.Int8{Int64: *f.BrandID, Valid: true}
	}
	var isActive pgtype.Bool
	if f.IsActive != nil {
		isActive = pgtype.Bool{Bool: *f.IsActive, Valid: true}
	}
	// Server-side availability filter (storefront "Tersedia saja").
	var inStock pgtype.Bool
	if f.InStock != nil {
		inStock = pgtype.Bool{Bool: *f.InStock, Valid: true}
	}
	// Server-side sort — the storefront sends relevance|name|price_asc|
	// price_desc|newest|popularity; anything else falls back to the code order.
	var sort pgtype.Text
	switch f.Sort {
	case "name", "price_asc", "price_desc", "newest", "popularity":
		sort = pgtype.Text{String: f.Sort, Valid: true}
	}

	// ERP table sort: ?sort_by=<column>&sort_dir=asc|desc. Whitelisted, because
	// the value is compared in SQL and must never become an identifier.
	byCol, byDir := normalizeProductSort(f.SortBy, f.SortDir)
	var sortBy, sortDir pgtype.Text
	if byCol != "" {
		sortBy = pgtype.Text{String: byCol, Valid: true}
	}
	if byDir != "" {
		sortDir = pgtype.Text{String: byDir, Valid: true}
	}

	total, err := r.q.CountProducts(ctx, db.CountProductsParams{
		Search:     search,
		CategoryID: categoryID,
		BrandID:    brandID,
		IsActive:   isActive,
		InStock:    inStock,
	})
	if err != nil {
		return nil, 0, err
	}

	rows, err := r.q.ListProducts(ctx, db.ListProductsParams{
		Limit:      int32(f.PerPage),
		Offset:     int32((f.Page - 1) * f.PerPage),
		Search:     search,
		CategoryID: categoryID,
		BrandID:    brandID,
		IsActive:   isActive,
		InStock:    inStock,
		Sort:       sort,
		SortBy:     sortBy,
		SortDir:    sortDir,
	})
	if err != nil {
		return nil, 0, err
	}
	return rows, int(total), nil
}

func (r *Repository) ProductByID(ctx context.Context, id int64) (db.GetProductByIDRow, error) {
	row, err := r.q.GetProductByID(ctx, id)
	if err != nil {
		return db.GetProductByIDRow{}, mapErr(err)
	}
	return row, nil
}

func (r *Repository) CreateProduct(ctx context.Context, p db.CreateProductParams) (db.CreateProductRow, error) {
	row, err := r.q.CreateProduct(ctx, p)
	if err != nil {
		if isUniqueViolation(err) {
			return db.CreateProductRow{}, ErrDuplicateCode
		}
		return db.CreateProductRow{}, err
	}
	return row, nil
}

func (r *Repository) UpdateProduct(ctx context.Context, p db.UpdateProductParams) error {
	n, err := r.q.UpdateProduct(ctx, p)
	if err != nil {
		if isUniqueViolation(err) {
			return ErrDuplicateCode
		}
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) DeleteProduct(ctx context.Context, id int64) error {
	n, err := r.q.SoftDeleteProduct(ctx, id)
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

// --- brands ------------------------------------------------------------------

func (r *Repository) BrandByID(ctx context.Context, id int64) (db.Brand, error) {
	row, err := r.q.GetBrandByID(ctx, id)
	if err != nil {
		return db.Brand{}, mapErr(err)
	}
	return row, nil
}

func (r *Repository) ListBrands(ctx context.Context, onlyActive bool) ([]db.Brand, error) {
	p := pgtype.Bool{}
	if onlyActive {
		p = pgtype.Bool{Bool: true, Valid: true}
	}
	return r.q.ListBrands(ctx, p)
}

func (r *Repository) CreateBrand(ctx context.Context, p db.CreateBrandParams) (db.Brand, error) {
	row, err := r.q.CreateBrand(ctx, p)
	if err != nil {
		if isUniqueViolation(err) {
			return db.Brand{}, ErrDuplicateName
		}
		return db.Brand{}, err
	}
	return row, nil
}

func (r *Repository) UpdateBrand(ctx context.Context, p db.UpdateBrandParams) error {
	n, err := r.q.UpdateBrand(ctx, p)
	if err != nil {
		if isUniqueViolation(err) {
			return ErrDuplicateName
		}
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) DeleteBrand(ctx context.Context, id int64) error {
	used, err := r.q.CountProductsByBrand(ctx, pgtype.Int8{Int64: id, Valid: true})
	if err != nil {
		return err
	}
	if used > 0 {
		return ErrInUse
	}
	n, err := r.q.DeleteBrand(ctx, id)
	if err != nil {
		if isFKViolation(err) {
			return ErrInUse
		}
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) BrandNameExists(ctx context.Context, name string, exceptID int64) (bool, error) {
	n, err := r.q.CountBrandsByName(ctx, db.CountBrandsByNameParams{Name: name, ExceptID: exceptID})
	return n > 0, err
}

// --- configured product units ------------------------------------------------

func (r *Repository) ListProductUnits(ctx context.Context, productID int64, onlyActive bool) ([]db.ListProductUnitsRow, error) {
	p := pgtype.Bool{}
	if onlyActive {
		p = pgtype.Bool{Bool: true, Valid: true}
	}
	return r.q.ListProductUnits(ctx, db.ListProductUnitsParams{ProductID: productID, OnlyActive: p})
}

func (r *Repository) ProductUnitByID(ctx context.Context, id int64) (db.GetProductUnitByIDRow, error) {
	row, err := r.q.GetProductUnitByID(ctx, id)
	if err != nil {
		return db.GetProductUnitByIDRow{}, mapErr(err)
	}
	return row, nil
}

func (r *Repository) CreateProductUnit(ctx context.Context, p db.CreateProductUnitParams) (int64, error) {
	var id int64
	err := r.inTx(ctx, func(q *db.Queries) error {
		if p.IsBase {
			if err := q.ClearProductBaseUnit(ctx, db.ClearProductBaseUnitParams{ProductID: p.ProductID}); err != nil {
				return err
			}
		}
		var err error
		id, err = q.CreateProductUnit(ctx, p)
		return err
	})
	if err != nil {
		if isUniqueViolation(err) {
			return 0, ErrDuplicateUnitConfig
		}
		return 0, err
	}
	return id, nil
}

func (r *Repository) UpdateProductUnit(ctx context.Context, p db.UpdateProductUnitParams) error {
	var affected int64
	err := r.inTx(ctx, func(q *db.Queries) error {
		if p.IsBase {
			row, err := q.GetProductUnitByID(ctx, p.ID)
			if err != nil {
				return err
			}
			if err := q.ClearProductBaseUnit(ctx, db.ClearProductBaseUnitParams{ProductID: row.ProductID, ID: p.ID}); err != nil {
				return err
			}
		}
		var err error
		affected, err = q.UpdateProductUnit(ctx, p)
		return err
	})
	if err != nil {
		if isUniqueViolation(err) {
			return ErrDuplicateUnitConfig
		}
		return mapErr(err)
	}
	if affected == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) DeleteProductUnit(ctx context.Context, id int64) error {
	n, err := r.q.DeleteProductUnit(ctx, id)
	if err != nil {
		if isFKViolation(err) {
			return ErrInUse
		}
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

// --- product barcodes --------------------------------------------------------

func (r *Repository) ListProductBarcodes(ctx context.Context, productID int64) ([]db.ProductBarcode, error) {
	return r.q.ListProductBarcodes(ctx, productID)
}

func (r *Repository) ProductBarcodeByID(ctx context.Context, id int64) (db.ProductBarcode, error) {
	row, err := r.q.GetProductBarcodeByID(ctx, id)
	if err != nil {
		return db.ProductBarcode{}, mapErr(err)
	}
	return row, nil
}

func (r *Repository) CreateProductBarcode(ctx context.Context, p db.CreateProductBarcodeParams) (db.ProductBarcode, error) {
	var row db.ProductBarcode
	err := r.inTx(ctx, func(q *db.Queries) error {
		if p.IsPrimary {
			if err := q.ClearProductPrimaryBarcode(ctx, db.ClearProductPrimaryBarcodeParams{ProductID: p.ProductID}); err != nil {
				return err
			}
		}
		var err error
		row, err = q.CreateProductBarcode(ctx, p)
		return err
	})
	if err != nil {
		if isUniqueViolation(err) {
			return db.ProductBarcode{}, ErrDuplicateBarcode
		}
		return db.ProductBarcode{}, err
	}
	return row, nil
}

func (r *Repository) DeleteProductBarcode(ctx context.Context, id int64) error {
	n, err := r.q.DeleteProductBarcode(ctx, id)
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

func (r *Repository) LookupProductByBarcode(ctx context.Context, barcode string) (db.LookupProductByBarcodeRow, error) {
	row, err := r.q.LookupProductByBarcode(ctx, barcode)
	if err != nil {
		return db.LookupProductByBarcodeRow{}, mapErr(err)
	}
	return row, nil
}

// --- helpers -----------------------------------------------------------------

// inTx runs fn inside a transaction so that "demote the previous base unit"
// and "insert the new base unit" cannot leave two base rows or none.
func (r *Repository) inTx(ctx context.Context, fn func(*db.Queries) error) error {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback(ctx) }()
	if err := fn(db.New(tx)); err != nil {
		return err
	}
	return tx.Commit(ctx)
}

func mapErr(err error) error {
	if errors.Is(err, pgx.ErrNoRows) {
		return ErrNotFound
	}
	return err
}

func isUniqueViolation(err error) bool {
	var pge *pgconn.PgError
	return errors.As(err, &pge) && pge.Code == "23505"
}

func isFKViolation(err error) bool {
	var pge *pgconn.PgError
	return errors.As(err, &pge) && pge.Code == "23503"
}