package repository

import (
	"database/sql"
	"errors"
	"fmt"
	"time"
	models "vibeStonk/server/models/v1"

	"google.golang.org/protobuf/types/known/timestamppb"
)

var (
	ErrHoldingNotFound = errors.New("holding not found")
)

func newSqliteHoldingRepo(db *sql.DB) (HoldingRepo, error) {
	repo := &sqliteHoldingRepo{db: db}
	return repo, nil
}

type sqliteHoldingRepo struct {
	db *sql.DB
}

// Helper function to convert a purchase row to a holding
func (s *sqliteHoldingRepo) purchaseToHolding(
	id int64,
	purchaseDate time.Time,
	stockID int64,
	qty float64,
	price float64,
	sold sql.NullFloat64,
) *models.Holding {
	holding := &models.Holding{
		Id:           id,
		PurchaseDate: timestamppb.New(purchaseDate),
		StockID:      stockID,
		Qty:          qty,
		Price:        price,
		Sold:         0, // Default to 0 if no transactions
	}

	// If sold is not NULL, use its value
	if sold.Valid {
		holding.Sold = sold.Float64
	}

	return holding
}

// GetAll returns all holdings (purchases with sold quantity information)
func (s *sqliteHoldingRepo) GetAll() ([]*models.Holding, error) {
	query := `
	SELECT p.id, p.purchase_date, p.stock_id, p.qty, p.price, 
	       COALESCE(SUM(t.qty), 0) as sold_qty
	FROM purchases p
	LEFT JOIN transactions t ON p.id = t.purchase_id
	GROUP BY p.id
	ORDER BY p.purchase_date DESC
	`
	rows, err := s.db.Query(query)
	if err != nil {
		return nil, fmt.Errorf("failed to get all holdings: %w", err)
	}
	defer rows.Close()

	var holdings []*models.Holding
	for rows.Next() {
		var id int64
		var purchaseDate time.Time
		var stockID int64
		var qty float64
		var price float64
		var sold sql.NullFloat64

		err := rows.Scan(&id, &purchaseDate, &stockID, &qty, &price, &sold)
		if err != nil {
			return nil, fmt.Errorf("failed to scan holding row: %w", err)
		}

		holding := s.purchaseToHolding(id, purchaseDate, stockID, qty, price, sold)
		holdings = append(holdings, holding)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating holding rows: %w", err)
	}

	return holdings, nil
}

// GetUnsold returns holdings that are not fully sold (including partially sold)
func (s *sqliteHoldingRepo) GetUnsold() ([]*models.Holding, error) {
	query := `
	SELECT p.id, p.purchase_date, p.stock_id, p.qty, p.price, 
	       COALESCE(SUM(t.qty), 0) as sold_qty
	FROM purchases p
	LEFT JOIN transactions t ON p.id = t.purchase_id
	GROUP BY p.id
	HAVING p.qty > COALESCE(SUM(t.qty), 0)
	ORDER BY p.purchase_date DESC
	`
	rows, err := s.db.Query(query)
	if err != nil {
		return nil, fmt.Errorf("failed to get unsold holdings: %w", err)
	}
	defer rows.Close()

	var holdings []*models.Holding
	for rows.Next() {
		var id int64
		var purchaseDate time.Time
		var stockID int64
		var qty float64
		var price float64
		var sold sql.NullFloat64

		err := rows.Scan(&id, &purchaseDate, &stockID, &qty, &price, &sold)
		if err != nil {
			return nil, fmt.Errorf("failed to scan holding row: %w", err)
		}

		holding := s.purchaseToHolding(id, purchaseDate, stockID, qty, price, sold)
		holdings = append(holdings, holding)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating holding rows: %w", err)
	}

	return holdings, nil
}

// GetSold returns holdings that are fully sold
func (s *sqliteHoldingRepo) GetSold() ([]*models.Holding, error) {
	query := `
	SELECT p.id, p.purchase_date, p.stock_id, p.qty, p.price, 
	       COALESCE(SUM(t.qty), 0) as sold_qty
	FROM purchases p
	LEFT JOIN transactions t ON p.id = t.purchase_id
	GROUP BY p.id
	HAVING p.qty <= COALESCE(SUM(t.qty), 0)
	ORDER BY p.purchase_date DESC
	`
	rows, err := s.db.Query(query)
	if err != nil {
		return nil, fmt.Errorf("failed to get sold holdings: %w", err)
	}
	defer rows.Close()

	var holdings []*models.Holding
	for rows.Next() {
		var id int64
		var purchaseDate time.Time
		var stockID int64
		var qty float64
		var price float64
		var sold sql.NullFloat64

		err := rows.Scan(&id, &purchaseDate, &stockID, &qty, &price, &sold)
		if err != nil {
			return nil, fmt.Errorf("failed to scan holding row: %w", err)
		}

		holding := s.purchaseToHolding(id, purchaseDate, stockID, qty, price, sold)
		holdings = append(holdings, holding)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating holding rows: %w", err)
	}

	return holdings, nil
}

// GetByID returns a specific holding by ID
func (s *sqliteHoldingRepo) GetByID(id int64) (*models.Holding, error) {
	query := `
	SELECT p.id, p.purchase_date, p.stock_id, p.qty, p.price, 
	       COALESCE(SUM(t.qty), 0) as sold_qty
	FROM purchases p
	LEFT JOIN transactions t ON p.id = t.purchase_id
	WHERE p.id = ?
	GROUP BY p.id
	`
	row := s.db.QueryRow(query, id)

	var purchaseDate time.Time
	var stockID int64
	var qty float64
	var price float64
	var sold sql.NullFloat64

	err := row.Scan(&id, &purchaseDate, &stockID, &qty, &price, &sold)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrHoldingNotFound
		}
		return nil, fmt.Errorf("failed to get holding: %w", err)
	}

	holding := s.purchaseToHolding(id, purchaseDate, stockID, qty, price, sold)
	return holding, nil
}

// GetByStockID returns holdings for a specific stock
func (s *sqliteHoldingRepo) GetByStockID(stockID int64) ([]*models.Holding, error) {
	query := `
	SELECT p.id, p.purchase_date, p.stock_id, p.qty, p.price, 
	       COALESCE(SUM(t.qty), 0) as sold_qty
	FROM purchases p
	LEFT JOIN transactions t ON p.id = t.purchase_id
	WHERE p.stock_id = ?
	GROUP BY p.id
	ORDER BY p.purchase_date DESC
	`
	rows, err := s.db.Query(query, stockID)
	if err != nil {
		return nil, fmt.Errorf("failed to get holdings by stock ID: %w", err)
	}
	defer rows.Close()

	var holdings []*models.Holding
	for rows.Next() {
		var id int64
		var purchaseDate time.Time
		var pStockID int64
		var qty float64
		var price float64
		var sold sql.NullFloat64

		err := rows.Scan(&id, &purchaseDate, &pStockID, &qty, &price, &sold)
		if err != nil {
			return nil, fmt.Errorf("failed to scan holding row: %w", err)
		}

		holding := s.purchaseToHolding(id, purchaseDate, pStockID, qty, price, sold)
		holdings = append(holdings, holding)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating holding rows: %w", err)
	}

	return holdings, nil
}

// GetUnsoldByStockID returns unsold holdings for a specific stock ID
func (s *sqliteHoldingRepo) GetUnsoldByStockID(stockID int64) ([]*models.Holding, error) {
	query := `
	SELECT p.id, p.purchase_date, p.stock_id, p.qty, p.price, 
	       COALESCE(SUM(t.qty), 0) as sold_qty
	FROM purchases p
	LEFT JOIN transactions t ON p.id = t.purchase_id
	WHERE p.stock_id = ?
	GROUP BY p.id
	HAVING p.qty > COALESCE(SUM(t.qty), 0)
	ORDER BY p.purchase_date ASC
	`
	rows, err := s.db.Query(query, stockID)
	if err != nil {
		return nil, fmt.Errorf("failed to get unsold holdings by stock ID: %w", err)
	}
	defer rows.Close()

	var holdings []*models.Holding
	for rows.Next() {
		var id int64
		var purchaseDate time.Time
		var stockID int64
		var qty float64
		var price float64
		var sold sql.NullFloat64

		err := rows.Scan(&id, &purchaseDate, &stockID, &qty, &price, &sold)
		if err != nil {
			return nil, fmt.Errorf("failed to scan holding row: %w", err)
		}

		holding := s.purchaseToHolding(id, purchaseDate, stockID, qty, price, sold)
		holdings = append(holdings, holding)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating holding rows: %w", err)
	}

	return holdings, nil
}

// GetSoldByStockID returns sold holdings for a specific stock ID
func (s *sqliteHoldingRepo) GetSoldByStockID(stockID int64) ([]*models.Holding, error) {
	query := `
	SELECT p.id, p.purchase_date, p.stock_id, p.qty, p.price, 
	       COALESCE(SUM(t.qty), 0) as sold_qty
	FROM purchases p
	LEFT JOIN transactions t ON p.id = t.purchase_id
	WHERE p.stock_id = ?
	GROUP BY p.id
	HAVING p.qty <= COALESCE(SUM(t.qty), 0)
	ORDER BY p.purchase_date DESC
	`
	rows, err := s.db.Query(query, stockID)
	if err != nil {
		return nil, fmt.Errorf("failed to get sold holdings by stock ID: %w", err)
	}
	defer rows.Close()

	var holdings []*models.Holding
	for rows.Next() {
		var id int64
		var purchaseDate time.Time
		var stockID int64
		var qty float64
		var price float64
		var sold sql.NullFloat64

		err := rows.Scan(&id, &purchaseDate, &stockID, &qty, &price, &sold)
		if err != nil {
			return nil, fmt.Errorf("failed to scan holding row: %w", err)
		}

		holding := s.purchaseToHolding(id, purchaseDate, stockID, qty, price, sold)
		holdings = append(holdings, holding)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating holding rows: %w", err)
	}

	return holdings, nil
}