mkdir library-api
cd library-api
go mod init library-api

/library-api
|-- db.sql
|-- main.go
|-- go.mod

go get github.com/gin-gonic/gin
go get gorm.io/gorm
go get gorm.io/driver/postgres

-- Create the library database.
CREATE DATABASE library;

-- Connect to the library database.
\c library;

-- Create tables.
CREATE TABLE authors (
    -- SERIAL: This is an auto-incrementing integer type. It’s shorthand for creating a column that uses a sequence to generate unique IDs. Behind the scenes, SERIAL creates an INTEGER column and an associated sequence that automatically increments. It has a range of -2,147,483,648 to 2,147,483,647 (32-bit).
    id SERIAL PRIMARY KEY,
    -- Defines a variable-length string, with a maximum length of 100 characters. It allows storing strings up to 100 characters in length but only uses as much space as required by the actual content, plus a byte or two for length storage, making it memory-efficient for shorter entries within the specified limit.
    name VARCHAR(100) NOT NULL UNIQUE,
    -- TEXT type in PostgreSQL is used to store variable-length text data without a defined limit. Unlike VARCHAR(n), which has a character limit, TEXT allows unlimited length (bounded by PostgreSQL's 1 GB per field limit). It’s ideal for storing longer text, like descriptions or content, without the need to specify an exact size.
    bio TEXT
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    -- This creates a foreign key constraint:
    -- It establishes a relationship between author_id in the books table and the id column in the authors table, ensuring that each author_id corresponds to an existing id in the authors table.
    -- ON DELETE CASCADE: This means that if an author is deleted from the authors table, all related records in the books table (i.e., books written by that author) will automatically be deleted as well.
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE,
    -- DATE type is used to store calendar dates (year, month, and day) without time information. It supports dates from 4713 BC to 5874897 AD and is suitable for fields like birth dates or other events where only the date is required, without the time.
    published_date DATE NOT NULL,
    description TEXT,
    -- the JSONB type stores JSON data in a binary format, optimized for fast querying and indexing. Unlike the plain JSON type, JSONB supports indexing for efficient data retrieval, making it ideal for handling complex JSON structures. It automatically removes whitespace and reorders keys, which enhances query performance but sacrifices the original formatting of JSON input.
    details JSONB
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    -- TIMESTAMP represents a date and time without a time zone. It’s precise down to microseconds and is often used to store exact dates and times in local time. The related TIMESTAMPTZ type, however, includes time zone data and adjusts automatically for different time zones when queried.
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- CREATE TABLE borrow_logs (
--     id SERIAL PRIMARY KEY,
--     user_id INTEGER REFERENCES users(id),
--     book_id INTEGER REFERENCES books(id),
--     borrowed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
--     returned_at TIMESTAMP
-- );

-- Create a partitioned table for borrow logs based on year.
-- The borrow_logs table is partitioned by year using PARTITION BY RANGE (borrowed_at).
CREATE TABLE borrow_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    book_id INTEGER REFERENCES books(id),
    borrowed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    returned_at TIMESTAMP
) PARTITION BY RANGE (borrowed_at);

-- Create partitions for each year.
-- Automatic Routing: PostgreSQL automatically directs INSERT operations to the appropriate partition (borrow_logs_2023 or borrow_logs_2024) based on the borrowed_at date.
CREATE TABLE borrow_logs_2023 PARTITION OF borrow_logs
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE borrow_logs_2024 PARTITION OF borrow_logs
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
-- Benefit: This helps in improving query performance and managing large datasets by ensuring that data for each year is stored separately.

-- Important Node: 
-- If you try to insert a new record into the borrow_logs table with a borrowed_at date that does not fall within the range of any existing partitions, PostgreSQL will raise an error like folowing:
-- ERROR: no partition of relation "borrow_logs" found for row
-- DETAIL: Failing row contains (1, 1, 1, 2022-12-31 00:00:00, null).
-- How to Handle This:
-- 1. Add a Default Partition: You can create a default partition to store any data that doesn't match the existing ranges:
CREATE TABLE borrow_logs_default PARTITION OF borrow_logs DEFAULT;
-- 2 Create a New Partition: If you expect data from a different year, you should create a new partition for that year:
CREATE TABLE borrow_logs_2022 PARTITION OF borrow_logs FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');


-- Indexes for faster searching.
CREATE INDEX idx_books_published_date ON books (published_date);
CREATE INDEX idx_books_details ON books USING GIN (details);
-- GIN Index (Generalized Inverted Index).  It is particularly useful for indexing columns with complex data types like arrays, JSONB, or text search fields

-- About Indexing In PostgreSQL, GIN (Generalized Inverted Index) indexes are designed for fast text searches and JSONB queries by indexing entire sets of values, making them ideal for full-text search, array, and JSONB fields. GIN indexes can handle complex data types efficiently, allowing fast lookups across unstructured data like documents.
-- Other common PostgreSQL index types include:
-- BTREE: The default index type, used for most general-purpose indexing with ordered data, like numbers and dates.
-- HASH: Ideal for simple equality checks but less commonly used due to limited performance improvements.
-- GiST (Generalized Search Tree): Useful for complex data types, like geometric data, allowing range queries and nearest-neighbor searches.
-- SP-GiST (Space-Partitioned GiST): Suited for advanced, irregular data, often used with spatial and natural language data.
-- BRIN (Block Range INdex): Best for very large datasets with sequential data where indexing every row would be inefficient.
-- Each type is optimized for specific use cases and data structures, enabling efficient querying tailored to different data patterns.

-- Add a full-text index to the title and description of books
CREATE INDEX book_text_idx ON books USING GIN (to_tsvector('english', title || ' ' || description));
-- to_tsvector('english', ...) converts the concatenated title and description fields into a Text Search Vector (tsv) suitable for full-text searching.
-- The || operator concatenates the title and description fields, so both fields are indexed together for searching.
-- 'english' specifies the language dictionary, which helps with stemming and stop-word filtering.


-- Create a simple view for books with author information.
CREATE VIEW book_author_view AS
SELECT books.id AS book_id, books.title, authors.name AS author_name
FROM books
JOIN authors ON books.author_id = authors.id;

-- Create a view to get user borrow history
CREATE VIEW user_borrow_history AS
SELECT
    u.id AS user_id,
    u.name AS user_name,
    b.title AS book_title,
    bl.borrowed_at,
    bl.returned_at
FROM
    users u
    JOIN borrow_logs bl ON u.id = bl.user_id
    JOIN books b ON bl.book_id = b.id;

-- Use a CTE to get all active borrow logs (not yet returned)
WITH active_borrows AS (
    SELECT * FROM borrow_logs WHERE returned_at IS NULL
)
SELECT * FROM active_borrows;

-- Function to calculate the number of books borrowed by a user.
-- Creates a function that takes an INT parameter user_id and returns an INT value. If the function already exists, it will replace it.
CREATE OR REPLACE FUNCTION get_borrow_count(user_id INT) RETURNS INT AS $$
    -- $1 is a placeholder for the first input. When the function is executed, PostgreSQL replaces $1 with the actual user_id value that is passed in by the caller.
    SELECT COUNT(*) FROM borrow_logs WHERE user_id = $1;
$$ LANGUAGE SQL;
-- AS $$ ... $$: This defines the body of the function between the dollar signs ($$).
-- LANGUAGE SQL: Specifies that the function is written in SQL.


-- Trigger to log activities.
CREATE TABLE activity_logs (
    id SERIAL PRIMARY KEY,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- This function returns a TRIGGER which will be used for later executions
CREATE OR REPLACE FUNCTION log_activity() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO activity_logs (description)
    -- NEW refers to the new row being inserted or modified by the triggering event.
    VALUES ('A borrow_log entry has been added with ID ' || NEW.id);
    -- The function returns NEW, which means that the new data will be used as it is after the trigger action.
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- It uses plpgsql, which is a procedural language in PostgreSQL (commands between BEGIN END) 

CREATE TRIGGER log_borrow_activity
AFTER INSERT ON borrow_logs
FOR EACH ROW EXECUTE FUNCTION log_activity();

-- Add a JSONB column to store metadata
ALTER TABLE books ADD COLUMN metadata JSONB;
-- Example metadata: {"tags": ["fiction", "bestseller"], "page_count": 320}

package main

import (
    "net/http"
    "strconv"
    "time"

    "github.com/gin-gonic/gin"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

type Author struct {
    ID   uint   `gorm:"primaryKey"`
    Name string `gorm:"not null;unique"`
    Bio  string
}

type Book struct {
    ID            uint                   `gorm:"primaryKey"`
    Title         string                 `gorm:"not null"`
    AuthorID      uint                   `gorm:"not null"`
    PublishedDate time.Time              `gorm:"not null"`
    Details       map[string]interface{} `gorm:"type:jsonb"`
}

type User struct {
    ID        uint   `gorm:"primaryKey"`
    Name      string `gorm:"not null"`
    Email     string `gorm:"not null;unique"`
    CreatedAt time.Time
}

type BorrowLog struct {
    ID         uint      `gorm:"primaryKey"`
    UserID     uint      `gorm:"not null"`
    BookID     uint      `gorm:"not null"`
    BorrowedAt time.Time `gorm:"default:CURRENT_TIMESTAMP"`
    ReturnedAt *time.Time
}

var db *gorm.DB

func initDB() {
    dsn := "host=localhost user=postgres password=yourpassword dbname=library port=5432 sslmode=disable"
    var err error
    db, err = gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic("failed to connect to database")
    }

    // Auto-migrate models.
    db.AutoMigrate(&Author{}, &Book{}, &User{}, &BorrowLog{})
}

func main() {
    initDB()
    r := gin.Default()

    r.POST("/authors", createAuthor)
    r.PUT("/authors/:id", updateAuthor)    // Update an author's information.
    r.DELETE("/authors/:id", deleteAuthor) // Delete an author by ID.
    r.POST("/books", createBook)
    r.POST("/users", createUser)
    r.POST("/borrow", borrowBook)
    r.GET("/borrow/user/:id/count", getBorrowCount)
    r.GET("/borrow/user/:id/logs", getBorrowLogs)
    r.GET("/borrow_logs", listBorrowLogs) // List borrow logs with pagination.
    r.GET("/books_with_author", listBooks)

    r.Run(":8080")
}

func createAuthor(c *gin.Context) {
    var author Author
    if err := c.ShouldBindJSON(&author); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }
    if err := db.Create(&author).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    c.JSON(http.StatusOK, author)
}

func updateAuthor(c *gin.Context) {
    var author Author

    // Bind JSON data.
    if err := c.ShouldBindJSON(&author); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    // Find the author by ID and update.
    if err := db.Model(&Author{}).Where("id = ?", c.Param("id")).Updates(&author).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    c.JSON(http.StatusOK, gin.H{"message": "Author updated successfully"})
}

func deleteAuthor(c *gin.Context) {
    if err := db.Delete(&Author{}, c.Param("id")).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    c.JSON(http.StatusOK, gin.H{"message": "Author deleted successfully"})
}

func createBook(c *gin.Context) {
    var book Book
    if err := c.ShouldBindJSON(&book); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }
    if err := db.Create(&book).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    c.JSON(http.StatusOK, book)
}

func createUser(c *gin.Context) {
    var user User
    if err := c.ShouldBindJSON(&user); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }
    if err := db.Create(&user).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    c.JSON(http.StatusOK, user)
}

// The Golang code does not need changes specifically to use the partitioned tables; the partitioning is handled by PostgreSQL
// you simply insert into the borrow_logs table, and PostgreSQL will automatically route the data to the correct partition.
func borrowBook(c *gin.Context) {
    var log BorrowLog
    if err := c.ShouldBindJSON(&log); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    tx := db.Begin()
    if err := tx.Create(&log).Error; err != nil {
        tx.Rollback()
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    tx.Commit()
    c.JSON(http.StatusOK, log)
}

func getBorrowCount(c *gin.Context) {
    userID := c.Param("id")
    var count int
    if err := db.Raw("SELECT get_borrow_count(?)", userID).Scan(&count).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    c.JSON(http.StatusOK, gin.H{"borrow_count": count})
}

// When querying a partitioned table in PostgreSQL using Golang, no changes are needed in the query logic or code.
// You interact with the parent table (borrow_logs in this case) as you would with any normal table, and PostgreSQL automatically manages retrieving the data from the appropriate partitions.
// Performance: PostgreSQL optimizes the query by scanning only the relevant partitions, which can significantly speed up queries when dealing with large datasets.
// Here’s how you might query the borrow_logs table using GORM, even though it’s partitioned:
func getBorrowLogs(c *gin.Context) {
    var logs []BorrowLog
    if err := db.Where("user_id = ?", c.Param("user_id")).Find(&logs).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    c.JSON(http.StatusOK, logs)
}

// A common approach for cursor-based pagination is to use a unique field like ID or a timestamp (e.g., BorrowedAt).
// The client will request a set of results, and if there are more results to be fetched, the response will include a next_cursor that the client can use in subsequent requests.
// To use this endpoint.
// 1. First Request: GET /borrow_logs?pageSize=5 , Response will include logs and next_cursor (e.g., 2024-01-15T08:00:00Z).
// 2. GET /borrow_logs?pageSize=5&next_cursor=2024-01-15T08:00:00Z , This request will fetch the next set of borrow logs where BorrowedAt is greater than 2024-01-15T08:00:00Z.
func listBorrowLogs(c *gin.Context) {
    var logs []BorrowLog

    // Get the cursor from query parameters.
    nextCursor := c.Query("next_cursor")
    pageSize := c.DefaultQuery("pageSize", "10")
    pageSizeInt, _ := strconv.Atoi(pageSize)

    query := db.Limit(pageSizeInt).Order("borrowed_at ASC")

    // If a cursor is provided, fetch records after the given timestamp.
    if nextCursor != "" {
        cursorTime, err := time.Parse(time.RFC3339, nextCursor)
        if err != nil {
            c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid cursor format"})
            return
        }
        query = query.Where("borrowed_at > ?", cursorTime)
    }

    if err := query.Find(&logs).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    // Prepare the next cursor based on the last record's BorrowedAt timestamp.
    var newCursor string
    if len(logs) > 0 {
        lastLog := logs[len(logs)-1]
        newCursor = lastLog.BorrowedAt.Format(time.RFC3339)
    }

    // Respond with the logs and the new cursor.
    c.JSON(http.StatusOK, gin.H{
        "logs":        logs,
        "next_cursor": newCursor,
    })
}

func listBooks(c *gin.Context) {
    var books []Book
    db.Preload("Author").Find(&books)
    c.JSON(http.StatusOK, books)
}

// Querying the user_borrow_history View:
func getUserBorrowHistory(c *gin.Context) {
    var history []struct {
        UserID     uint       `json:"user_id"`
        UserName   string     `json:"user_name"`
        BookTitle  string     `json:"book_title"`
        BorrowedAt time.Time  `json:"borrowed_at"`
        ReturnedAt *time.Time `json:"returned_at,omitempty"`
    }

    if err := db.Raw("SELECT * FROM user_borrow_history WHERE user_id = ?", c.Param("user_id")).Scan(&history).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    c.JSON(http.StatusOK, history)
}

// Using a CTE in a Query:
func getActiveBorrows(c *gin.Context) {
    var logs []BorrowLog
    query := `
    WITH active_borrows AS (
        SELECT * FROM borrow_logs WHERE returned_at IS NULL
    )
    SELECT * FROM active_borrows WHERE user_id = ?`

    if err := db.Raw(query, c.Param("user_id")).Scan(&logs).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    c.JSON(http.StatusOK, logs)
}

// Full-Text Search in books:
func searchBooks(c *gin.Context) {
    var books []Book
    searchQuery := c.Query("q")
    query := `
    SELECT * FROM books
    WHERE to_tsvector('english', title || ' ' || description) @@ plainto_tsquery('english', ?)
    `

    if err := db.Raw(query, searchQuery).Scan(&books).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    c.JSON(http.StatusOK, books)
}

// Handling JSONB Data:
func updateBookMetadata(c *gin.Context) {
    var metadata map[string]interface{}
    if err := c.ShouldBindJSON(&metadata); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    bookID := c.Param("book_id")
    if err := db.Model(&Book{}).Where("id = ?", bookID).Update("metadata", metadata).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    c.JSON(http.StatusOK, gin.H{"status": "metadata updated"})
}

// Can query a specific field from a JSONB column using the ->> operator to extract a value as text:
func getBookTags(c *gin.Context) {
    var tags []string
    bookID := c.Param("book_id")
    query := `SELECT metadata->>'tags' FROM books WHERE id = ?`

    if err := db.Raw(query, bookID).Scan(&tags).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    c.JSON(http.StatusOK, gin.H{"tags": tags})
}

// To add or update fields in a JSONB column, use the jsonb_set function:
func updateBookPageCount(c *gin.Context) {
    bookID := c.Param("book_id")
    var input struct {
        PageCount int `json:"page_count"`
    }

    if err := c.ShouldBindJSON(&input); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    query := `
    UPDATE books
    SET metadata = jsonb_set(metadata, '{page_count}', to_jsonb(?::int), true)
    WHERE id = ?`

    if err := db.Exec(query, input.PageCount, bookID).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    c.JSON(http.StatusOK, gin.H{"status": "page count updated"})
}

func vacuumBooks(c *gin.Context) {
    if err := db.Exec("VACUUM ANALYZE books").Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    c.JSON(http.StatusOK, gin.H{"status": "Vacuum performed successfully"})
}

func updateBookTitle(c *gin.Context) {
    bookID := c.Param("book_id")
    var input struct {
        NewTitle string `json:"new_title"`
    }
    if err := c.ShouldBindJSON(&input); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    // Start a transaction to demonstrate MVCC
    tx := db.Begin()

    defer func() {
        if r := recover(); r != nil {
            tx.Rollback()
        }
    }()

    var book Book
    if err := tx.Set("gorm:query_option", "FOR UPDATE").First(&book, bookID).Error; err != nil {
        tx.Rollback()
        c.JSON(http.StatusNotFound, gin.H{"error": "Book not found"})
        return
    }

    // Simulate an update to demonstrate MVCC handling
    book.Title = input.NewTitle
    if err := tx.Save(&book).Error; err != nil {
        tx.Rollback()
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    // Commit the transaction
    tx.Commit()
    c.JSON(http.StatusOK, gin.H{"status": "Book title updated"})
}

func getAuthorBorrowStats(c *gin.Context) {
    var stats []struct {
        AuthorID       int    `json:"author_id"`
        AuthorName     string `json:"author_name"`
        TotalBorrows   int    `json:"total_borrows"`
        RunningTotal   int    `json:"running_total"`
    }

    query := `
    SELECT
        a.id AS author_id,
        a.name AS author_name,
        COUNT(bl.id) AS total_borrows,
        SUM(COUNT(bl.id)) OVER (PARTITION BY a.id ORDER BY bl.borrowed_at) AS running_total
    FROM authors a
    LEFT JOIN books b ON b.author_id = a.id
    LEFT JOIN borrow_logs bl ON bl.book_id = b.id
    GROUP BY a.id, a.name, bl.borrowed_at
    ORDER BY a.id, bl.borrowed_at
    `

    if err := db.Raw(query).Scan(&stats).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }

    c.JSON(http.StatusOK, stats)
}