package repositories

import (
	"context"
	"fmt"
	"strings"
	"time"

	"github.com/CubeCraft-Creations/Extrudex/backend/internal/models"
	"github.com/jackc/pgx/v5/pgxpool"
)

// FilamentRepository handles database queries for filament_spools.
type FilamentRepository struct {
	pool *pgxpool.Pool
}

// NewFilamentRepository creates a FilamentRepository backed by the given pool.
func NewFilamentRepository(pool *pgxpool.Pool) *FilamentRepository {
	return &FilamentRepository{pool: pool}
}

// FilamentFilter holds query parameters for listing filament spools.
type FilamentFilter struct {
	Material string // filter by material_base name (case-insensitive)
	Finish   string // filter by material_finish name (case-insensitive)
	Color    string // filter by exact color_hex match
	LowStock bool   // if true, filter for remaining_grams <= low_stock_threshold_grams
	Limit    int
	Offset   int
}

// spoolScanFields is the common SELECT column list for filament spools with JOINs.
const spoolScanFields = `
	s.id, s.name,
	s.material_base_id,
	COALESCE(mb.name, '') as material_base_name,
	COALESCE(mb.density_g_cm3, 0) as material_base_density_g_cm3,
	COALESCE(mb.extrusion_temp_min, NULL::int) as material_base_extrusion_temp_min,
	COALESCE(mb.extrusion_temp_max, NULL::int) as material_base_extrusion_temp_max,
	COALESCE(mb.bed_temp_min, NULL::int) as material_base_bed_temp_min,
	COALESCE(mb.bed_temp_max, NULL::int) as material_base_bed_temp_max,
	COALESCE(mb.created_at, s.created_at) as material_base_created_at,
	COALESCE(mb.updated_at, s.created_at) as material_base_updated_at,
	s.material_finish_id,
	COALESCE(mf.name, '') as material_finish_name,
	mf.description as material_finish_description,
	COALESCE(mf.created_at, s.created_at) as material_finish_created_at,
	COALESCE(mf.updated_at, s.created_at) as material_finish_updated_at,
	s.material_modifier_id,
	mm.name as material_modifier_name,
	mm.description as material_modifier_description,
	mm.created_at as material_modifier_created_at,
	mm.updated_at as material_modifier_updated_at,
	s.color_hex, s.brand, s.diameter_mm,
	s.initial_grams, s.remaining_grams, s.spool_weight_grams,
	s.cost_usd, s.low_stock_threshold_grams,
	s.notes, s.barcode,
	s.deleted_at, s.created_at, s.updated_at`

const spoolFromJoins = `
	FROM filament_spools s
	LEFT JOIN material_bases mb ON s.material_base_id = mb.id
	LEFT JOIN material_finishes mf ON s.material_finish_id = mf.id
	LEFT JOIN material_modifiers mm ON s.material_modifier_id = mm.id`

// scanSpoolWithJoins scans a full spool row including all JOINed tables.
func scanSpoolWithJoins(row interface{ Scan(...interface{}) error }) (models.FilamentSpool, error) {
	var s models.FilamentSpool
	var mb models.MaterialBase
	var mf models.MaterialFinish
	var mfDesc *string
	var modifierID *int
	var modName, modDesc *string
	var modCreatedAt, modUpdatedAt *time.Time

	err := row.Scan(
		&s.ID, &s.Name,
		&s.MaterialBaseID,
		&mb.Name, &mb.DensityGCm3,
		&mb.ExtrusionTempMin, &mb.ExtrusionTempMax,
		&mb.BedTempMin, &mb.BedTempMax,
		&mb.CreatedAt, &mb.UpdatedAt,
		&s.MaterialFinishID,
		&mf.Name, &mfDesc,
		&mf.CreatedAt, &mf.UpdatedAt,
		&modifierID,
		&modName, &modDesc,
		&modCreatedAt, &modUpdatedAt,
		&s.ColorHex, &s.Brand, &s.DiameterMM,
		&s.InitialGrams, &s.RemainingGrams, &s.SpoolWeightGrams,
		&s.CostUSD, &s.LowStockThresholdGrams,
		&s.Notes, &s.Barcode,
		&s.DeletedAt, &s.CreatedAt, &s.UpdatedAt,
	)
	if err != nil {
		return s, err
	}

	mb.ID = s.MaterialBaseID
	s.MaterialBase = &mb

	mf.ID = s.MaterialFinishID
	if mfDesc != nil {
		mf.Description = mfDesc
	}
	s.MaterialFinish = &mf

	s.MaterialModifierID = modifierID
	if modifierID != nil && modName != nil {
		mm := models.MaterialModifier{
			ID:   *modifierID,
			Name: *modName,
		}
		if modDesc != nil {
			mm.Description = modDesc
		}
		if modCreatedAt != nil {
			mm.CreatedAt = *modCreatedAt
		}
		if modUpdatedAt != nil {
			mm.UpdatedAt = *modUpdatedAt
		}
		s.MaterialModifier = &mm
	}

	return s, nil
}

// GetAll returns filament spools matching the given filters, with pagination.
// Returns results, total matching count, and any error.
func (r *FilamentRepository) GetAll(ctx context.Context, filter FilamentFilter) ([]models.FilamentSpool, int, error) {
	conditions := []string{"s.deleted_at IS NULL"}
	args := []interface{}{}
	argIdx := 1

	if filter.Material != "" {
		conditions = append(conditions, fmt.Sprintf("LOWER(mb.name) = LOWER($%d)", argIdx))
		args = append(args, filter.Material)
		argIdx++
	}
	if filter.Finish != "" {
		conditions = append(conditions, fmt.Sprintf("LOWER(mf.name) = LOWER($%d)", argIdx))
		args = append(args, filter.Finish)
		argIdx++
	}
	if filter.Color != "" {
		conditions = append(conditions, fmt.Sprintf("s.color_hex = $%d", argIdx))
		args = append(args, filter.Color)
		argIdx++
	}
	if filter.LowStock {
		conditions = append(conditions, "s.remaining_grams <= s.low_stock_threshold_grams")
	}

	whereClause := ""
	if len(conditions) > 0 {
		whereClause = "WHERE " + strings.Join(conditions, " AND ")
	}

	// Count total.
	var total int
	countQuery := "SELECT COUNT(*) " + spoolFromJoins + " " + whereClause
	if err := r.pool.QueryRow(ctx, countQuery, args...).Scan(&total); err != nil {
		return nil, 0, err
	}

	// Query with pagination.
	dataQuery := "SELECT " + spoolScanFields + " " + spoolFromJoins + " " +
		whereClause +
		" ORDER BY s.name ASC" +
		fmt.Sprintf(" LIMIT $%d OFFSET $%d", argIdx, argIdx+1)

	dataArgs := make([]interface{}, len(args))
	copy(dataArgs, args)
	dataArgs = append(dataArgs, filter.Limit, filter.Offset)

	rows, err := r.pool.Query(ctx, dataQuery, dataArgs...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var spools []models.FilamentSpool
	for rows.Next() {
		s, err := scanSpoolWithJoins(rows)
		if err != nil {
			return nil, 0, err
		}
		spools = append(spools, s)
	}
	if err := rows.Err(); err != nil {
		return nil, 0, err
	}
	if spools == nil {
		spools = []models.FilamentSpool{}
	}

	return spools, total, nil
}

// GetByID returns a single filament spool by ID with JOINed data.
// Returns nil if not found or soft-deleted.
func (r *FilamentRepository) GetByID(ctx context.Context, id int) (*models.FilamentSpool, error) {
	query := "SELECT " + spoolScanFields + " " + spoolFromJoins +
		" WHERE s.id = $1 AND s.deleted_at IS NULL"

	row := r.pool.QueryRow(ctx, query, id)
	s, err := scanSpoolWithJoins(row)
	if err != nil {
		return nil, err
	}
	return &s, nil
}

// Create inserts a new filament spool and returns the created spool with JOINed data.
func (r *FilamentRepository) Create(ctx context.Context, spool *models.FilamentSpool) (*models.FilamentSpool, error) {
	var id int
	err := r.pool.QueryRow(ctx, `
		INSERT INTO filament_spools (
			name, material_base_id, material_finish_id, material_modifier_id,
			color_hex, brand, diameter_mm, initial_grams, remaining_grams,
			spool_weight_grams, cost_usd, low_stock_threshold_grams,
			notes, barcode
		) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
		RETURNING id
	`,
		spool.Name, spool.MaterialBaseID, spool.MaterialFinishID, spool.MaterialModifierID,
		spool.ColorHex, spool.Brand, spool.DiameterMM, spool.InitialGrams, spool.RemainingGrams,
		spool.SpoolWeightGrams, spool.CostUSD, spool.LowStockThresholdGrams,
		spool.Notes, spool.Barcode,
	).Scan(&id)
	if err != nil {
		return nil, err
	}

	return r.GetByID(ctx, id)
}

// Update applies partial updates to an existing filament spool.
// Only non-nil fields in the update map are applied.
// Returns the updated spool.
func (r *FilamentRepository) Update(ctx context.Context, id int, updates map[string]interface{}) (*models.FilamentSpool, error) {
	if len(updates) == 0 {
		return r.GetByID(ctx, id)
	}

	setClauses := []string{"updated_at = NOW()"}
	args := []interface{}{}
	argIdx := 1

	for col, val := range updates {
		setClauses = append(setClauses, fmt.Sprintf("%s = $%d", col, argIdx))
		args = append(args, val)
		argIdx++
	}

	args = append(args, id)
	query := fmt.Sprintf("UPDATE filament_spools SET %s WHERE id = $%d AND deleted_at IS NULL",
		strings.Join(setClauses, ", "), argIdx)

	result, err := r.pool.Exec(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	if result.RowsAffected() == 0 {
		return nil, nil // not found or deleted
	}

	return r.GetByID(ctx, id)
}

// SoftDelete marks a filament spool as deleted by setting deleted_at = NOW().
// Returns true if a row was affected.
func (r *FilamentRepository) SoftDelete(ctx context.Context, id int) (bool, error) {
	result, err := r.pool.Exec(ctx, `
		UPDATE filament_spools
		SET deleted_at = NOW(), updated_at = NOW()
		WHERE id = $1 AND deleted_at IS NULL
	`, id)
	if err != nil {
		return false, err
	}
	return result.RowsAffected() > 0, nil
}