package warehousedeposit

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

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/shopspring/decimal"
)

var (
	ErrNotFound = errors.New("warehouse deposit not found")
	// ErrPaymentInvalid covers every reason a payment id can't join this
	// Setoran: not found, not cash/giro, or already claimed by another
	// Setoran. Deliberately one error — the picker only ever shows valid,
	// undeposited cash/giro payments, so this path means a race (someone
	// else deposited it a moment ago), not a normal validation failure.
	ErrPaymentInvalid = errors.New("salah satu pembayaran yang dipilih sudah tidak valid untuk disetor (mungkin sudah disetor oleh proses lain)")
)

type Repository struct {
	pool *pgxpool.Pool
}

func NewRepository(pool *pgxpool.Pool) *Repository {
	return &Repository{pool: pool}
}

// CreateWithPayments locks every payment in paymentIDs, verifies each is a
// cash/giro payment not already linked to another Setoran, sums them into
// d.Amount, inserts d, and links every payment to it — all in one
// transaction so the deposit's amount can never drift from what it actually
// covers.
func (r *Repository) CreateWithPayments(ctx context.Context, d *WarehouseDeposit, paymentIDs []int64) error {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	rows, err := tx.Query(ctx,
		`SELECT id, amount, payment_method, deposit_id FROM payments WHERE id = ANY($1) FOR UPDATE`,
		paymentIDs,
	)
	if err != nil {
		return err
	}
	found := map[int64]bool{}
	total := decimal.Zero
	for rows.Next() {
		var id int64
		var amount decimal.Decimal
		var method string
		var depID *int64
		if err := rows.Scan(&id, &amount, &method, &depID); err != nil {
			rows.Close()
			return err
		}
		if (method != "cash" && method != "giro") || depID != nil {
			rows.Close()
			return ErrPaymentInvalid
		}
		total = total.Add(amount)
		found[id] = true
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return err
	}
	if len(found) != len(paymentIDs) {
		return ErrPaymentInvalid
	}

	d.Amount = total
	if err := tx.QueryRow(ctx,
		`INSERT INTO warehouse_deposits (
			deposit_number, warehouse_id, driver_id, driver_name, amount, deposit_date, notes, status, created_by
		) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
		RETURNING id, created_at, updated_at`,
		d.DepositNumber, d.WarehouseID, d.DriverID, d.DriverName, d.Amount, d.DepositDate, d.Notes, d.Status, d.CreatedBy,
	).Scan(&d.ID, &d.CreatedAt, &d.UpdatedAt); err != nil {
		return err
	}

	if _, err := tx.Exec(ctx, `UPDATE payments SET deposit_id = $1 WHERE id = ANY($2)`, d.ID, paymentIDs); err != nil {
		return err
	}

	return tx.Commit(ctx)
}

// ListUndepositedPayments is the "Catat Setoran" checklist source: every
// cash/giro payment not yet claimed by a Setoran, optionally scoped to one
// driver's dispatched DOs on one date (the "from driver" mode) or filtered
// by customer/invoice search text (the ad-hoc "direct payment" mode, e.g.
// Sales handing over cash a customer paid them directly).
func (r *Repository) ListUndepositedPayments(ctx context.Context, f UndepositedFilter) ([]UndepositedPayment, error) {
	query := `
		SELECT p.id, p.payment_number, p.amount, p.payment_method, p.payment_date::text,
		       inv.id, COALESCE(inv.invoice_number, ''), COALESCE(c.name, '')
		FROM payments p
		JOIN invoices inv ON inv.id = p.payable_id AND p.payable_type = 'App\Models\Invoice'
		JOIN sales_orders so ON so.id = inv.sales_order_id
		JOIN customers c ON c.id = so.customer_id
		WHERE p.deposit_id IS NULL AND p.payment_method IN ('cash', 'giro')
	`
	var args []any
	argIdx := 1
	if f.DriverID != nil {
		query += fmt.Sprintf(` AND EXISTS (
			SELECT 1 FROM dispatches d
			JOIN delivery_orders do_row ON do_row.id = d.delivery_order_id
			WHERE do_row.sales_order_id = so.id AND d.driver_id = $%d AND d.status = 'dispatched'`, argIdx)
		args = append(args, *f.DriverID)
		argIdx++
		if f.Date != "" {
			query += fmt.Sprintf(` AND do_row.do_date = $%d`, argIdx)
			args = append(args, f.Date)
			argIdx++
		}
		query += `)`
	}
	if f.Search != "" {
		query += fmt.Sprintf(` AND (c.name ILIKE $%d OR inv.invoice_number ILIKE $%d)`, argIdx, argIdx)
		args = append(args, "%"+f.Search+"%")
		argIdx++
	}
	query += ` ORDER BY p.payment_date DESC, p.id DESC LIMIT 200`

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

	var items []UndepositedPayment
	for rows.Next() {
		var it UndepositedPayment
		if err := rows.Scan(&it.PaymentID, &it.PaymentNumber, &it.Amount, &it.PaymentMethod, &it.PaymentDate,
			&it.InvoiceID, &it.InvoiceNumber, &it.CustomerName); err != nil {
			return nil, err
		}
		items = append(items, it)
	}
	return items, rows.Err()
}

// DriverName resolves a driver's display name from the driver master data.
func (r *Repository) DriverName(ctx context.Context, driverID int64) (string, error) {
	var name string
	err := r.pool.QueryRow(ctx,
		`SELECT name FROM users WHERE id = $1 AND application_access = 'driver'`, driverID,
	).Scan(&name)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return "", ErrNotFound
		}
		return "", err
	}
	return name, nil
}

func (r *Repository) GetByID(ctx context.Context, id int64) (*WarehouseDeposit, error) {
	query := `
		SELECT
			d.id, d.deposit_number, d.warehouse_id, COALESCE(w.name, ''), d.driver_id, d.driver_name,
			d.amount, d.deposit_date::text, d.notes, d.status,
			d.created_by, COALESCE(cu.name, ''),
			d.received_by, COALESCE(ru.name, ''),
			d.received_at, d.created_at, d.updated_at
		FROM warehouse_deposits d
		LEFT JOIN warehouses w ON w.id = d.warehouse_id
		LEFT JOIN users cu ON cu.id = d.created_by
		LEFT JOIN users ru ON ru.id = d.received_by
		WHERE d.id = $1
	`
	var d WarehouseDeposit
	var driverID, createdBy, receivedBy *int64
	var recAt *time.Time
	err := r.pool.QueryRow(ctx, query, id).Scan(
		&d.ID, &d.DepositNumber, &d.WarehouseID, &d.WarehouseName, &driverID, &d.DriverName,
		&d.Amount, &d.DepositDate, &d.Notes, &d.Status,
		&createdBy, &d.CreatedByName,
		&receivedBy, &d.ReceivedByName,
		&recAt, &d.CreatedAt, &d.UpdatedAt,
	)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrNotFound
		}
		return nil, err
	}
	d.DriverID = driverID
	d.CreatedBy = createdBy
	d.ReceivedBy = receivedBy
	d.ReceivedAt = recAt
	return &d, nil
}

func (r *Repository) GetByIDForUpdate(ctx context.Context, id int64) (*WarehouseDeposit, error) {
	query := `
		SELECT
			d.id, d.deposit_number, d.warehouse_id, COALESCE(w.name, ''), d.driver_id, d.driver_name,
			d.amount, d.deposit_date::text, d.notes, d.status,
			d.created_by, COALESCE(cu.name, ''),
			d.received_by, COALESCE(ru.name, ''),
			d.received_at, d.created_at, d.updated_at
		FROM warehouse_deposits d
		LEFT JOIN warehouses w ON w.id = d.warehouse_id
		LEFT JOIN users cu ON cu.id = d.created_by
		LEFT JOIN users ru ON ru.id = d.received_by
		WHERE d.id = $1
		FOR UPDATE OF d
	`
	var d WarehouseDeposit
	var driverID, createdBy, receivedBy *int64
	var recAt *time.Time
	err := r.pool.QueryRow(ctx, query, id).Scan(
		&d.ID, &d.DepositNumber, &d.WarehouseID, &d.WarehouseName, &driverID, &d.DriverName,
		&d.Amount, &d.DepositDate, &d.Notes, &d.Status,
		&createdBy, &d.CreatedByName,
		&receivedBy, &d.ReceivedByName,
		&recAt, &d.CreatedAt, &d.UpdatedAt,
	)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrNotFound
		}
		return nil, err
	}
	d.DriverID = driverID
	d.CreatedBy = createdBy
	d.ReceivedBy = receivedBy
	d.ReceivedAt = recAt
	return &d, nil
}

func (r *Repository) List(ctx context.Context, f Filter) ([]WarehouseDeposit, int, error) {
	var whereClauses []string
	var args []any
	argIdx := 1

	if f.WarehouseID > 0 {
		whereClauses = append(whereClauses, fmt.Sprintf("d.warehouse_id = $%d", argIdx))
		args = append(args, f.WarehouseID)
		argIdx++
	}
	if f.Status != "" {
		whereClauses = append(whereClauses, fmt.Sprintf("d.status = $%d", argIdx))
		args = append(args, f.Status)
		argIdx++
	}
	if f.DateFrom != "" {
		whereClauses = append(whereClauses, fmt.Sprintf("d.deposit_date >= $%d", argIdx))
		args = append(args, f.DateFrom)
		argIdx++
	}
	if f.DateTo != "" {
		whereClauses = append(whereClauses, fmt.Sprintf("d.deposit_date <= $%d", argIdx))
		args = append(args, f.DateTo)
		argIdx++
	}
	if f.Search != "" {
		pattern := "%" + f.Search + "%"
		whereClauses = append(whereClauses, fmt.Sprintf("(d.deposit_number ILIKE $%d OR d.driver_name ILIKE $%d OR d.notes ILIKE $%d)", argIdx, argIdx, argIdx))
		args = append(args, pattern)
		argIdx++
	}

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

	countQuery := "SELECT count(*) FROM warehouse_deposits d" + whereSQL
	var total int
	if err := r.pool.QueryRow(ctx, countQuery, args...).Scan(&total); err != nil {
		return nil, 0, err
	}

	if f.PerPage <= 0 || f.PerPage > 100 {
		f.PerPage = 20
	}
	if f.Page <= 0 {
		f.Page = 1
	}
	offset := (f.Page - 1) * f.PerPage

	query := fmt.Sprintf(`
		SELECT
			d.id, d.deposit_number, d.warehouse_id, COALESCE(w.name, ''), d.driver_id, d.driver_name,
			d.amount, d.deposit_date::text, d.notes, d.status,
			d.created_by, COALESCE(cu.name, ''),
			d.received_by, COALESCE(ru.name, ''),
			d.received_at, d.created_at, d.updated_at
		FROM warehouse_deposits d
		LEFT JOIN warehouses w ON w.id = d.warehouse_id
		LEFT JOIN users cu ON cu.id = d.created_by
		LEFT JOIN users ru ON ru.id = d.received_by
		%s
		ORDER BY d.id DESC
		LIMIT $%d OFFSET $%d
	`, whereSQL, argIdx, argIdx+1)

	args = append(args, f.PerPage, offset)

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

	var items []WarehouseDeposit
	for rows.Next() {
		var d WarehouseDeposit
		var driverID, createdBy, receivedBy *int64
		var recAt *time.Time
		if err := rows.Scan(
			&d.ID, &d.DepositNumber, &d.WarehouseID, &d.WarehouseName, &driverID, &d.DriverName,
			&d.Amount, &d.DepositDate, &d.Notes, &d.Status,
			&createdBy, &d.CreatedByName,
			&receivedBy, &d.ReceivedByName,
			&recAt, &d.CreatedAt, &d.UpdatedAt,
		); err != nil {
			return nil, 0, err
		}
		d.DriverID = driverID
		d.CreatedBy = createdBy
		d.ReceivedBy = receivedBy
		d.ReceivedAt = recAt
		items = append(items, d)
	}
	return items, total, rows.Err()
}

func (r *Repository) UpdateReceived(ctx context.Context, id, receivedBy int64, receivedAt time.Time) error {
	query := `
		UPDATE warehouse_deposits
		SET status = 'received', received_by = $2, received_at = $3, updated_at = now()
		WHERE id = $1 AND status = 'pending'
	`
	res, err := r.pool.Exec(ctx, query, id, receivedBy, receivedAt)
	if err != nil {
		return err
	}
	if res.RowsAffected() == 0 {
		return errors.New("deposit already received or not in pending status")
	}
	return nil
}