techxcar/backend/internal/client/repository.go

package client

import (
	"context"
	"time"

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

type Client struct {
	ID        string    `json:"id"`
	Name      string    `json:"name"`
	NIF       string    `json:"nif"`
	Phone     string    `json:"phone"`
	Email     string    `json:"email"`
	Address   string    `json:"address"`
	Notes     string    `json:"notes"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

type Vehicle struct {
	ID        string    `json:"id"`
	ClientID  *string   `json:"client_id"`
	Plate     string    `json:"plate"`
	Brand     string    `json:"brand"`
	Model     string    `json:"model"`
	Year      *int      `json:"year"`
	VIN       string    `json:"vin"`
	Mileage   *int      `json:"mileage"`
	FuelType  string    `json:"fuel_type"`
	Notes     string    `json:"notes"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

func ListClients(ctx context.Context, conn *pgxpool.Conn) ([]*Client, error) {
	rows, err := conn.Query(ctx, `
		SELECT id, name, COALESCE(nif,''), COALESCE(phone,''), COALESCE(email,''),
		       COALESCE(address,''), COALESCE(notes,''), created_at, updated_at
		FROM clients ORDER BY name`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var list []*Client
	for rows.Next() {
		var c Client
		if err := rows.Scan(&c.ID, &c.Name, &c.NIF, &c.Phone, &c.Email,
			&c.Address, &c.Notes, &c.CreatedAt, &c.UpdatedAt); err != nil {
			return nil, err
		}
		list = append(list, &c)
	}
	return list, rows.Err()
}

func GetClient(ctx context.Context, conn *pgxpool.Conn, id string) (*Client, error) {
	var c Client
	err := conn.QueryRow(ctx, `
		SELECT id, name, COALESCE(nif,''), COALESCE(phone,''), COALESCE(email,''),
		       COALESCE(address,''), COALESCE(notes,''), created_at, updated_at
		FROM clients WHERE id = $1`, id).
		Scan(&c.ID, &c.Name, &c.NIF, &c.Phone, &c.Email,
			&c.Address, &c.Notes, &c.CreatedAt, &c.UpdatedAt)
	if err != nil {
		return nil, err
	}
	return &c, nil
}

func CreateClient(ctx context.Context, conn *pgxpool.Conn, name, nif, phone, email, address, notes string) (*Client, error) {
	var c Client
	err := conn.QueryRow(ctx, `
		INSERT INTO clients (name, nif, phone, email, address, notes)
		VALUES ($1, NULLIF($2,''), NULLIF($3,''), NULLIF($4,''), NULLIF($5,''), NULLIF($6,''))
		RETURNING id, name, COALESCE(nif,''), COALESCE(phone,''), COALESCE(email,''),
		          COALESCE(address,''), COALESCE(notes,''), created_at, updated_at`,
		name, nif, phone, email, address, notes).
		Scan(&c.ID, &c.Name, &c.NIF, &c.Phone, &c.Email,
			&c.Address, &c.Notes, &c.CreatedAt, &c.UpdatedAt)
	return &c, err
}

func UpdateClient(ctx context.Context, conn *pgxpool.Conn, id, name, nif, phone, email, address, notes string) (*Client, error) {
	var c Client
	err := conn.QueryRow(ctx, `
		UPDATE clients SET name=$2, nif=NULLIF($3,''), phone=NULLIF($4,''), email=NULLIF($5,''),
		       address=NULLIF($6,''), notes=NULLIF($7,''), updated_at=NOW()
		WHERE id=$1
		RETURNING id, name, COALESCE(nif,''), COALESCE(phone,''), COALESCE(email,''),
		          COALESCE(address,''), COALESCE(notes,''), created_at, updated_at`,
		id, name, nif, phone, email, address, notes).
		Scan(&c.ID, &c.Name, &c.NIF, &c.Phone, &c.Email,
			&c.Address, &c.Notes, &c.CreatedAt, &c.UpdatedAt)
	return &c, err
}

func DeleteClient(ctx context.Context, conn *pgxpool.Conn, id string) error {
	_, err := conn.Exec(ctx, `DELETE FROM clients WHERE id = $1`, id)
	return err
}

func ListVehiclesByClient(ctx context.Context, conn *pgxpool.Conn, clientID string) ([]*Vehicle, error) {
	rows, err := conn.Query(ctx, `
		SELECT id, client_id, plate, brand, model, year, COALESCE(vin,''), mileage,
		       COALESCE(fuel_type,''), COALESCE(notes,''), created_at, updated_at
		FROM vehicles WHERE client_id = $1 ORDER BY plate`, clientID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var list []*Vehicle
	for rows.Next() {
		var v Vehicle
		if err := rows.Scan(&v.ID, &v.ClientID, &v.Plate, &v.Brand, &v.Model,
			&v.Year, &v.VIN, &v.Mileage, &v.FuelType, &v.Notes, &v.CreatedAt, &v.UpdatedAt); err != nil {
			return nil, err
		}
		list = append(list, &v)
	}
	return list, rows.Err()
}

func CreateVehicle(ctx context.Context, conn *pgxpool.Conn, clientID, plate, brand, model string, year int, vin, fuelType, notes string, mileage int) (*Vehicle, error) {
	var v Vehicle
	var yearPtr *int
	if year != 0 {
		yearPtr = &year
	}
	var mileagePtr *int
	if mileage != 0 {
		mileagePtr = &mileage
	}
	err := conn.QueryRow(ctx, `
		INSERT INTO vehicles (client_id, plate, brand, model, year, vin, fuel_type, mileage, notes)
		VALUES (NULLIF($1,'')::uuid, $2, $3, $4, $5, NULLIF($6,''), NULLIF($7,''), $8, NULLIF($9,''))
		RETURNING id, client_id, plate, brand, model, year, COALESCE(vin,''), mileage,
		          COALESCE(fuel_type,''), COALESCE(notes,''), created_at, updated_at`,
		clientID, plate, brand, model, yearPtr, vin, fuelType, mileagePtr, notes).
		Scan(&v.ID, &v.ClientID, &v.Plate, &v.Brand, &v.Model,
			&v.Year, &v.VIN, &v.Mileage, &v.FuelType, &v.Notes, &v.CreatedAt, &v.UpdatedAt)
	return &v, err
}

func UpdateVehicle(ctx context.Context, conn *pgxpool.Conn, id, plate, brand, model string, year int, vin, fuelType, notes string, mileage int) (*Vehicle, error) {
	var v Vehicle
	var yearPtr *int
	if year != 0 {
		yearPtr = &year
	}
	var mileagePtr *int
	if mileage != 0 {
		mileagePtr = &mileage
	}
	err := conn.QueryRow(ctx, `
		UPDATE vehicles SET plate=$2, brand=$3, model=$4,
		       year=$5, vin=NULLIF($6,''), fuel_type=NULLIF($7,''), mileage=$8, notes=NULLIF($9,''), updated_at=NOW()
		WHERE id=$1
		RETURNING id, client_id, plate, brand, model, year, COALESCE(vin,''), mileage,
		          COALESCE(fuel_type,''), COALESCE(notes,''), created_at, updated_at`,
		id, plate, brand, model, yearPtr, vin, fuelType, mileagePtr, notes).
		Scan(&v.ID, &v.ClientID, &v.Plate, &v.Brand, &v.Model,
			&v.Year, &v.VIN, &v.Mileage, &v.FuelType, &v.Notes, &v.CreatedAt, &v.UpdatedAt)
	return &v, err
}