package data

import (
	"database/sql"
	"github.com/nahuelev23/greenlight/internal/validator"
	"time"
)

...

// Define a MovieModel struct type which wraps a sql.DB connection pool.
type MovieModel struct {
	DB *sql.DB
}

// Add a placeholder method for inserting a new record in the movies table.
func (m MovieModel) Insert(movie *Movie) error {
	return nil
}

// Add a placeholder method for fetching a specific record from the movies table.
func (m MovieModel) Get(id int64) (*Movie, error) {
	return nil, nil
}

// Add a placeholder method for updating a specific record in the movies table.
func (m MovieModel) Update(movie *Movie) error {
	return nil
}

// Add a placeholder method for deleting a specific record from the movies table.
func (m MovieModel) Delete(id int64) error {
	return nil
}

$ touch internal/data/models.go

package data

import (
	"database/sql"
	"errors"
)

// Define a custom ErrRecordNotFound error. We'll return this from our Get() method when
// looking up a movie that doesn't exist in our database.
var (
	ErrRecordNotFound = errors.New("record not found")
)

// Create a Models struct which wraps the MovieModel. We'll add other models to this,
// like a UserModel and PermissionModel, as our build progresses.
type Models struct {
	Movies MovieModel
}

// For ease of use, we also add a New() method which returns a Models struct containing
// the initialized MovieModel.
func NewModels(db *sql.DB) Models {
	return Models{
		Movies: MovieModel{DB: db},
	}
}

package main

import (
	"context"
	"database/sql"
	"flag"
	"fmt"
	"net/http"
	"os"
	"time"

	"log/slog"

	_ "github.com/lib/pq"
+	"github.com/nahuelev23/greenlight/internal/data"
)

// Add a models field to hold our new Models struct.
type application struct {
	config config
	logger *slog.Logger
+	models data.Models
}

func main() {
	var cfg config

	flag.IntVar(&cfg.port, "port", 4000, "API server port")
	flag.StringVar(&cfg.env, "env", "development", "Environment (development|staging|production)")

	flag.StringVar(&cfg.db.dsn, "db-dsn", "postgres://greenlight:pa55word@localhost/greenlight", "PostgreSQL DSN")

	flag.IntVar(&cfg.db.maxOpenConns, "db-max-open-conns", 25, "PostgreSQL max open connections")
	flag.IntVar(&cfg.db.maxIdleConns, "db-max-idle-conns", 25, "PostgreSQL max idle connections")
	flag.DurationVar(&cfg.db.maxIdleTime, "db-max-idle-time", 15*time.Minute, "PostgreSQL max connection idle time")

	flag.Parse()

	logger := slog.New(slog.NewTextHandler(os.Stdout, nil))

	db, err := openDB(cfg)
	if err != nil {
		logger.Error(err.Error())
		os.Exit(1)
	}

	defer db.Close()

	// Use the data.NewModels() function to initialize a Models struct, passing in the
	// connection pool as a parameter.
	app := application{
		config: cfg,
		logger: logger,
+		models: data.NewModels(db),
	}

	srv := &http.Server{

		Addr:         fmt.Sprintf(":%d", cfg.port),
		Handler:      app.routes(),
		IdleTimeout:  time.Minute,
		ReadTimeout:  5 * time.Second,
		WriteTimeout: 10 * time.Second,
		ErrorLog:     slog.NewLogLogger(logger.Handler(), slog.LevelError),
	}

	logger.Info("starting server", "addr", srv.Addr, "env", cfg.env)

	err = srv.ListenAndServe()
	logger.Error(err.Error())
	os.Exit(1)
}

$ go run ./cmd/api
time=2023-09-10T10:59:13.722+02:00 level=INFO msg="database connection pool established"
time=2023-09-10T10:59:13.722+02:00 level=INFO msg="starting server" addr=:4000 env=development

app.models.Movies.Insert(...)

package data
...
type MockMovieModel struct{}
func (m MockMovieModel) Insert(movie *Movie) error {
// Mock the action...
}
func (m MockMovieModel) Get(id int64) (*Movie, error) {
// Mock the action...
}
func (m MockMovieModel) Update(movie *Movie) error {
// Mock the action...
}
func (m MockMovieModel) Delete(id int64) error {
// Mock the action...
}

package data
import (
    "database/sql"
    "errors"
)
var (
	ErrRecordNotFound = errors.New("record not found")
)
type Models struct {
    // Set the Movies field to be an interface containing the methods that both the
    // 'real' model and mock model need to support.
    Movies interface {
    Insert(movie *Movie) error
    Get(id int64) (*Movie, error)
    Update(movie *Movie) error
    Delete(id int64) error
	}
}
...
// Create a helper function which returns a Models instance containing the mock models
// only.
func NewMockModels() Models {
    return Models{
    	Movies: MockMovieModel{},
    }
}

INSERT INTO movies (title, year, runtime, genres)
VALUES ($1, $2, $3, $4)
RETURNING id, created_at, version

package data

import (
    "database/sql"
    "time"

    "greenlight.alexedwards.net/internal/validator"
    "github.com/lib/pq" // Nuevo import
)

// El método Insert() acepta un puntero a una estructura de película, que debería contener los
// datos para el nuevo registro.
func (m MovieModel) Insert(movie *Movie) error {
    // Define la consulta SQL para insertar un nuevo registro en la tabla de películas y devolver
    // los datos generados por el sistema.
    query := `
        INSERT INTO movies (title, year, runtime, genres)
        VALUES ($1, $2, $3, $4)
        RETURNING id, created_at, version`
    
    // Crea una slice de args que contiene los valores para los parámetros de marcador de posición de
    // la estructura de la película. Declarar esta slice inmediatamente al lado de nuestra consulta SQL ayuda a
    // aclarar *qué valores se están utilizando dónde* en la consulta.
    args := []interface{}{movie.Title, movie.Year, movie.Runtime, pq.Array(movie.Genres)}
    
    // Usa el método QueryRow() para ejecutar la consulta SQL en nuestro pool de conexiones,
    // pasando la slice args como un parámetro variádico y escaneando el id generado por el sistema, el creado_en y
    // los valores de versión en la estructura de película.
    return m.DB.QueryRow(query, args...).Scan(&movie.ID, &movie.CreatedAt, &movie.Version)
}

args := []any{movie.Title, movie.Year, movie.Runtime, pq.Array(movie.Genres)}

  func (app *application) createMovieHandler(w http.ResponseWriter, r *http.Request) {
  	var input struct {
  		Title   string       `json:"title"`
  		Year    int32        `json:"year"`
  		Runtime data.Runtime `json:"runtime"`
  		Genres  []string     `json:"genres"`
  	}
  	err := app.readJSON(w, r, &input)
  	if err != nil {
  		app.badRequestResponse(w, r, err)
  		return
  	}
  	// Note that the movie variable contains a *pointer* to a Movie struct.
  	movie := &data.Movie{
  		Title:   input.Title,
  		Year:    input.Year,
  		Runtime: input.Runtime,
  		Genres:  input.Genres,
  	}
  
  	v := validator.New()
  
  	if data.ValidateMovie(v, movie); !v.Valid() {
  		app.failedValidationResponse(w, r, v.Errors)
  		return
  	}

+	// Call the Insert() method on our movies model, passing in a pointer to the
+	// validated movie struct. This will create a record in the database and update the
+	// movie struct with the system-generated information.
+	err = app.models.Movies.Insert(movie)
+	if err != nil {
+		app.serverErrorResponse(w, r, err)
+		return
+	}
+	// When sending a HTTP response, we want to include a Location header to let the
+	// client know which URL they can find the newly-created resource at. We make an
+	// empty http.Header map and then use the Set() method to add a new Location header,
+	// interpolating the system-generated ID for our new movie in the URL.
+	headers := make(http.Header)
+	headers.Set("Location", fmt.Sprintf("/v1/movies/%d", movie.ID))
+	// Write a JSON response with a 201 Created status code, the movie data in the
+	// response body, and the Location header.
+	err = app.writeJSON(w, http.StatusCreated, envelope{"movie": movie}, headers)
+	if err != nil {
+		app.serverErrorResponse(w, r, err)
+	}
}

$ BODY='{"title":"Moana","year":2016,"runtime":"107 mins", "genres":["animation","adventure"]}'
$ curl -i -d "$BODY" localhost:4000/v1/movies
HTTP/1.1 201 Created
Content-Type: application/json
Location: /v1/movies/1
Date: Wed, 07 Apr 2021 19:21:41 GMT
Content-Length: 156
{
		"movie": {
		"id": 1,
		"title": "Moana",
		"year": 2016,
		"runtime": "107 mins",
		"genres": [
		"animation",
		"adventure"
		],
		"version": 1
	}
}

$ BODY='{"title":"Black Panther","year":2018,"runtime":"134 mins","genres":["action","adventure"]}'
$ curl -d "$BODY" localhost:4000/v1/movies
{
"movie": {
"id": 2,
"title": "Black Panther",
"year": 2018,
"runtime": "134 mins",
"genres": [
"action",
"adventure"
],
"version": 1
}
}
$ BODY='{"title":"Deadpool","year":2016, "runtime":"108 mins","genres":["action","comedy"]}'
$ curl -d "$BODY" localhost:4000/v1/movies
{
"movie": {
"id": 3,
"title": "Deadpool",
"year": 2016,
"runtime": "108 mins",
"genres": [
"action",
"comedy"
],
"version": 1
}
}
$ BODY='{"title":"The Breakfast Club","year":1986, "runtime":"96 mins","genres":["drama"]}'
$ curl -d "$BODY" localhost:4000/v1/movies
{
"movie": {
"id": 4,
"title": "The Breakfast Club",
"year": 1986,
"runtime": "96 mins",
"genres": [
"drama"
],
"version": 1
}
}

$ psql $GREENLIGHT_DB_DSN
Password for user greenlight:
psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
greenlight=> SELECT * FROM movies;
 id |       created_at        |        title        | year | runtime |        genres        | version
----+------------------------+---------------------+------+---------+-----------------------+---------
  1 | 2021-04-07 21:21:41+02 | Moana               | 2016 |    107  | {animation,adventure}|     1
  2 | 2021-04-07 21:28:28+02 | Black Panther       | 2018 |    134  | {action,adventure}   |     1
  3 | 2021-04-07 21:28:36+02 | Deadpool            | 2016 |    108  | {action,comedy}      |     1
  4 | 2021-04-07 21:28:44+02 | The Breakfast Club  | 1986 |         | {drama}              |     1
(4 rows)

// This SQL statement uses the $1 parameter twice, and the value `123` will be used in
// both locations where $1 appears.
stmt := "UPDATE foo SET bar = $1 + $2 WHERE bar = $1"
err := db.Exec(stmt, 123, 456)
if err != nil {
...
}

stmt := `
UPDATE foo SET bar = true;
UPDATE foo SET baz = false;`
err := db.Exec(stmt)
if err != nil {
...
}

pq: cannot insert multiple commands into a prepared statement

SELECT id, created_at, title, year, runtime, genres, version
FROM movies
WHERE id = $1

func (m MovieModel) Get(id int64) (*Movie, error) {
	// The PostgreSQL bigserial type that we're using for the movie ID starts
	// auto-incrementing at 1 by default, so we know that no movies will have ID values
	// less than that. To avoid making an unnecessary database call, we take a shortcut
	// and return an ErrRecordNotFound error straight away.
	if id < 1 {
		return nil, ErrRecordNotFound
	}
	// Define the SQL query for retrieving the movie data.
	query := `
	SELECT id, created_at, title, year, runtime, genres, version
	FROM movies
	WHERE id = $1`
	// Declare a Movie struct to hold the data returned by the query.
	var movie Movie
	// Execute the query using the QueryRow() method, passing in the provided id value// Execute the query using the QueryRow() method, passing in the provided id value
	// as a placeholder parameter, and scan the response data into the fields of the
	// Movie struct. Importantly, notice that we need to convert the scan target for the
	// genres column using the pq.Array() adapter function again.
	err := m.DB.QueryRow(query, id).Scan(
		&movie.ID,
		&movie.CreatedAt,
		&movie.Title,
		&movie.Year,
		&movie.Runtime,
		pq.Array(&movie.Genres),
		&movie.Version,
	)
	// Handle any errors. If there was no matching movie found, Scan() will return
	// a sql.ErrNoRows error. We check for this and return our custom ErrRecordNotFound
	// error instead.
	if err != nil {
		switch {
		case errors.Is(err, sql.ErrNoRows):
			return nil, ErrRecordNotFound
		default:
			return nil, err
		}
	}
	// Otherwise, return a pointer to the Movie struct.
	return &movie, nil
}

sql: Scan error on column index 5, name "genres": unsupported Scan, storing driver.Value type []uint8 into type *[]string

  func (app *application) showMovieHandler(w http.ResponseWriter, r *http.Request) {
  	id, err := app.readIDParam(r)
  	if err != nil {
  		app.notFoundResponse(w, r)
  		return
  	}
+  	// Call the Get() method to fetch the data for a specific movie. We also need to
+  	// use the errors.Is() function to check if it returns a data.ErrRecordNotFound
+  	// error, in which case we send a 404 Not Found response to the client.
+  	movie, err := app.models.Movies.Get(id)
+  	if err != nil {
+  		switch {
+  		case errors.Is(err, data.ErrRecordNotFound):
+  			app.notFoundResponse(w, r)
+  		default:
+  			app.serverErrorResponse(w, r, err)
+  		}
+  		return
+  	}
+  	err = app.writeJSON(w, http.StatusOK, envelope{"movie": movie}, nil)
+  	if err != nil {
+  		app.serverErrorResponse(w, r, err)
+  	}
}

$ curl -i localhost:4000/v1/movies/2
HTTP/1.1 200 OK
Content-Type: application/json
Date: Wed, 07 Apr 2021 19:37:12 GMT
Content-Length: 161
{
	"movie": {
	"id": 2,
	"title": "Black Panther",
	"year": 2018,
	"runtime": "134 mins",
	"genres": [
	"action",
	"adventure"
	],
	"version": 1
	}
}

$ curl -i localhost:4000/v1/movies/42
HTTP/1.1 404 Not Found
Content-Type: application/json
Date: Wed, 07 Apr 2021 19:37:58 GMT
Content-Length: 58
{
"error": "the requested resource could not be found"
}

func (m MovieModel) Get(id int64) (*Movie, error) {
	if id < 1 {
		return nil, ErrRecordNotFound
	}

}

sql: converting argument $1 type: uint64 values with high bit set are not supported

{
	"title": "Black Panther",
	"year": 2018,
	"runtime": "134 mins",
	"genres": [
		"action",
		"adventure",
		"sci-fi"
		]
}

UPDATE movies
SET title = $1, year = $2, runtime = $3, genres = $4, version = version + 1
WHERE id = $5
RETURNING version

func (m MovieModel) Update(movie *Movie) error {
	// Declare the SQL query for updating the record and returning the new version
	// number.
	query := `
	UPDATE movies
	SET title = $1, year = $2, runtime = $3, genres = $4, version = version + 1
	WHERE id = $5
	RETURNING version`
	// Create an args slice containing the values for the placeholder parameters.
	args := []any{
		movie.Title,
		movie.Year,
		movie.Runtime,
		pq.Array(movie.Genres),
		movie.ID,
	}
	// Use the QueryRow() method to execute the query, passing in the args slice as a
	// variadic parameter and scanning the new version value into the movie struct.
	return m.DB.QueryRow(query, args...).Scan(&movie.Version)
}

func (app *application) updateMovieHandler(w http.ResponseWriter, r *http.Request) {
	// Extract the movie ID from the URL.
	id, err := app.readIDParam(r)
	if err != nil {
		app.notFoundResponse(w, r)
		return
	}
	// Fetch the existing movie record from the database, sending a 404 Not Found
	// response to the client if we couldn't find a matching record.
	movie, err := app.models.Movies.Get(id)
	if err != nil {
		switch {
		case errors.Is(err, data.ErrRecordNotFound):
			app.notFoundResponse(w, r)
		default:
			app.serverErrorResponse(w, r, err)
		}
		return
	}
	// Declare an input struct to hold the expected data from the client.// Declare an input struct to hold the expected data from the client.
	var input struct {
		Title   string       `json:"title"`
		Year    int32        `json:"year"`
		Runtime data.Runtime `json:"runtime"`
		Genres  []string     `json:"genres"`
	}
	// Read the JSON request body data into the input struct.
	err = app.readJSON(w, r, &input)
	if err != nil {
		app.badRequestResponse(w, r, err)
		return
	}
	// Copy the values from the request body to the appropriate fields of the movie
	// record.
	movie.Title = input.Title
	movie.Year = input.Year
	movie.Runtime = input.Runtime
	movie.Genres = input.Genres
	// Validate the updated movie record, sending the client a 422 Unprocessable Entity
	// response if any checks fail.
	v := validator.New()
	if data.ValidateMovie(v, movie); !v.Valid() {
		app.failedValidationResponse(w, r, v.Errors)
		return
	}
	// Pass the updated movie record to our new Update() method.
	err = app.models.Movies.Update(movie)
	if err != nil {
		app.serverErrorResponse(w, r, err)
		return
	}
	// Write the updated movie record in a JSON response.
	err = app.writeJSON(w, http.StatusOK, envelope{"movie": movie}, nil)
	if err != nil {
		app.serverErrorResponse(w, r, err)
	}
}

  func (app *application) routes() http.Handler {
  	router := httprouter.New()
  
  	router.NotFound = http.HandlerFunc(app.notFoundResponse)
  
  	router.MethodNotAllowed = http.HandlerFunc(app.methodNotAllowedResponse)
  
  	router.HandlerFunc(http.MethodGet, "/v1/healthcheck", app.healthcheckHandler)
  	router.HandlerFunc(http.MethodPost, "/v1/movies", app.createMovieHandler)
  	router.HandlerFunc(http.MethodGet, "/v1/movies/:id", app.showMovieHandler)
  
+  	// Add the route for the PUT /v1/movies/:id endpoint.
+  	router.HandlerFunc(http.MethodPut, "/v1/movies/:id", app.updateMovieHandler)
  
  	return app.recoverPanic(router)
}

$ curl localhost:4000/v1/movies/2
{
	"movie": {
		"id": 2,
		"title": "Black Panther",
		"year": 2018,
		"runtime": "134 mins",
		"genres": [
			"action",
			"adventure"
		],
	"version": 1
	}
}

$ BODY='{"title":"Black Panther","year":2018,"runtime":"134 mins","genres":["sci-fi","action","adventure"]}'
$ curl -X PUT -d "$BODY" localhost:4000/v1/movies/2
{
	"movie": {
	"id": 2,
	"title": "Black Panther",
	"year": 2018,
	"runtime": "134 mins",
	"genres": [
		"sci-fi",
		"action",
		"adventure"
		],
	"version": 2
	}
}

$ curl localhost:4000/v1/movies/2
{
	"movie": {
	"id": 2,
	"title": "Black Panther",
	"year": 2018,
	"runtime": "134 mins",
		"genres": [
			"sci-fi",
			"action",
			"adventure"
		],
	"version": 2
	}
}

DELETE FROM movies
WHERE id = $1

func (m MovieModel) Delete(id int64) error {
	// Return an ErrRecordNotFound error if the movie ID is less than 1.
	if id < 1 {
		return ErrRecordNotFound
	}
	// Construct the SQL query to delete the record.
	query := `
	DELETE FROM movies
	WHERE id = $1`
	// Execute the SQL query using the Exec() method, passing in the id variable as
	// the value for the placeholder parameter. The Exec() method returns a sql.Result
	// object.
	result, err := m.DB.Exec(query, id)
	if err != nil {
		return err
	}
	// Call the RowsAffected() method on the sql.Result object to get the number of rows
	// affected by the query.
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return err
	}
	// If no rows were affected, we know that the movies table didn't contain a record
	// with the provided ID at the moment we tried to delete it. In that case we
	// return an ErrRecordNotFound error.
	if rowsAffected == 0 {
		return ErrRecordNotFound
	}
	return nil
}

func (app *application) deleteMovieHandler(w http.ResponseWriter, r *http.Request) {
	// Extract the movie ID from the URL.
	id, err := app.readIDParam(r)
	if err != nil {
		app.notFoundResponse(w, r)
		return
	}
	// Delete the movie from the database, sending a 404 Not Found response to the
	// client if there isn't a matching record.
	err = app.models.Movies.Delete(id)
	if err != nil {
		switch {
		case errors.Is(err, data.ErrRecordNotFound):
			app.notFoundResponse(w, r)
		default:
			app.serverErrorResponse(w, r, err)
		}
		return
	}
	// Return a 200 OK status code along with a success message.
	err = app.writeJSON(w, http.StatusOK, envelope{"message": "movie successfully deleted"}, nil)
	if err != nil {
		app.serverErrorResponse(w, r, err)
	}
}

  func (app *application) routes() http.Handler {
  	router := httprouter.New()
  
  	router.NotFound = http.HandlerFunc(app.notFoundResponse)
  
  	router.MethodNotAllowed = http.HandlerFunc(app.methodNotAllowedResponse)
  
  	router.HandlerFunc(http.MethodGet, "/v1/healthcheck", app.healthcheckHandler)
  	router.HandlerFunc(http.MethodPost, "/v1/movies", app.createMovieHandler)
  	router.HandlerFunc(http.MethodGet, "/v1/movies/:id", app.showMovieHandler)
  	router.HandlerFunc(http.MethodPut, "/v1/movies/:id", app.updateMovieHandler)
  
+  	// Add the route for the DELETE /v1/movies/:id endpoint.
+  	router.HandlerFunc(http.MethodDelete, "/v1/movies/:id", app.deleteMovieHandler)
  
  	return app.recoverPanic(router)
}

$ curl -X DELETE localhost:4000/v1/movies/3
{
"message": "movie successfully deleted"
}

$ curl -X DELETE localhost:4000/v1/movies/3
{
"error": "the requested resource could not be found"
}