user.go

package user

import (
    "database/sql"
    "fmt"
    "log"
    "math"

    _ "github.com/go-sql-driver/mysql"
)

// Database instance
var db *sql.DB

// Database settings
const (
    host     = "localhost"
    port     = 3306 // Default port
    user     = "root"
    password = ""
    dbname   = "netflip_db"
)

// User struct
type User struct {
    ID                    int    `json:"id"`
    Name                  string `json:"name"`
    Email                 string `json:"salary"`
    PhoneNumber           string `json:"phone_number"`
    SubscriptionName      string `json:"subscription_name"`
    UserCreatedAt         string `json:"user_created_at"`
    SubscriptionCreatedAt string `json:"subscription_created_at"`
    SubscriptionEndedAt   string `json:"subscription_ended_at"`
}

// Users struct
type Users struct {
    Users []User `json:"users"`
}

// Connect function
func Connect() error {
    var err error
    // Use DSN string to open
    db, err = sql.Open("mysql", fmt.Sprintf("%s:%s@/%s", user, password, dbname))
    if err != nil {
        return err
    }
    if err = db.Ping(); err != nil {
        return err
    }
    return nil
}

func GetUsersSubscriptionPaginationMeta() float64 {
    var count float64
    // Connect with database
    if err := Connect(); err != nil {
        log.Fatal(err)
    }

    row := db.QueryRow(`
        SELECT
            count(*)
        FROM users u
        JOIN subscriptions s on u.id = s.user_id
        JOIN plans p on s.plan_id = p.id
    `)

    err := row.Scan(&count)
    defer db.Close()

    if err != nil {
        log.Fatal(err)
    }

    fmt.Print(count)
    return count
}

func GetUsersSubscriptionPaginated(page_no int) (Users, error) {

    // page_no := 0
    // no_of_records_per_page := 10
    // offset := (page_no -1) * no_of_records_per_page
    var no_of_records_per_page, offset int = 10, 0
    offset = (page_no - 1) * no_of_records_per_page

    totalUserCount := GetUsersSubscriptionPaginationMeta()

    totalPages := int(math.Ceil(totalUserCount / float64(no_of_records_per_page)))

    // Connect with database
    if err := Connect(); err != nil {
        log.Fatal(err)
    }

    result := Users{}

    // Get Users list from database

    rows, err := db.Query(`
    SELECT
        u.id,
        u.name,
        u.email,
        u.mobile as "phone_number",
        u.created_at as "user_created_at",
        p.name as "subscription_name",
        s.created_at as "subscription_created_at",
        s.ended_at as "subscription_ended_at"
    FROM users u
    JOIN subscriptions s on u.id = s.user_id
    JOIN plans p on s.plan_id = p.id
    LIMIT ?, 10
    `, offset)

    if err != nil {
        // return c.Status(500).SendString(err.Error())
        return result, err
    }
    defer rows.Close()

    for rows.Next() {
        user := User{}
        if err := rows.Scan(
            &user.ID,
            &user.Name,
            &user.Email,
            &user.PhoneNumber,
            &user.SubscriptionName,
            &user.UserCreatedAt,
            &user.SubscriptionCreatedAt,
            &user.SubscriptionEndedAt,
        ); err != nil {
            return result, err // Exit if we get an error
        }

        // Append User to Users
        result.Users = append(result.Users, user)
    }

    fmt.Println(totalPages)

    return result, err
}