Gobot

Creating a Telegram bot in Go with SQLite3 involves several steps: setting up the bot, integrating the SQLite3 database, handling user commands, and implementing a task scheduler. Here’s a detailed guide to achieve this:

1. Setting Up Your Go Environment

First, ensure you have Go installed on your system. If not, download and install it from golang.org.

2. Installing Required Packages

You need the following packages:

• tgbotapi for interacting with the Telegram Bot API.
• go-sqlite3 for SQLite3 database interaction.

Install them using:

shКопировать кодgo get github.com/go-telegram-bot-api/telegram-bot-api/v5
go get github.com/mattn/go-sqlite3

3. Creating the Bot

First, create a new Go file, e.g., main.go.

goКопировать кодpackage main

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

	tgbotapi "github.com/go-telegram-bot-api/telegram-bot-api/v5"
	_ "github.com/mattn/go-sqlite3"
)

const dbFileName = "tasks.db"

func main() {
	bot, err := tgbotapi.NewBotAPI("YOUR_TELEGRAM_BOT_API_TOKEN")
	if err != nil {
		log.Panic(err)
	}

	bot.Debug = true

	u := tgbotapi.NewUpdate(0)
	u.Timeout = 60

	updates := bot.GetUpdatesChan(u)

	db := initDB()
	defer db.Close()

	go taskScheduler(bot, db)

	for update := range updates {
		if update.Message == nil {
			continue
		}

		switch update.Message.Command() {
		case "start":
			handleStartCommand(db, update.Message)
		case "add":
			handleAddCommand(db, update.Message)
		case "list":
			handleListCommand(db, update.Message)
		case "delete":
			handleDeleteCommand(db, update.Message)
		}
	}
}

func initDB() *sql.DB {
	db, err := sql.Open("sqlite3", dbFileName)
	if err != nil {
		log.Fatal(err)
	}

	createTableSQL := `CREATE TABLE IF NOT EXISTS tasks (
		"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
		"user_id" INTEGER NOT NULL,
		"time" TEXT NOT NULL,
		"task" TEXT NOT NULL
	);`

	_, err = db.Exec(createTableSQL)
	if err != nil {
		log.Fatal(err)
	}

	return db
}

func handleStartCommand(db *sql.DB, message *tgbotapi.Message) {
	userID := message.From.ID

	insertUserSQL := `INSERT INTO users (id) VALUES (?)`
	_, err := db.Exec(insertUserSQL, userID)
	if err != nil {
		log.Println("Error inserting user:", err)
		return
	}

	msg := tgbotapi.NewMessage(message.Chat.ID, "Welcome! Use /add to add tasks.")
	bot.Send(msg)
}

func handleAddCommand(db *sql.DB, message *tgbotapi.Message) {
	args := message.CommandArguments()
	var taskTime, taskText string
	fmt.Sscanf(args, "%s %s", &taskTime, &taskText)

	userID := message.From.ID

	insertTaskSQL := `INSERT INTO tasks (user_id, time, task) VALUES (?, ?, ?)`
	_, err := db.Exec(insertTaskSQL, userID, taskTime, taskText)
	if err != nil {
		log.Println("Error inserting task:", err)
		return
	}

	msg := tgbotapi.NewMessage(message.Chat.ID, "Task added successfully.")
	bot.Send(msg)
}

func handleListCommand(db *sql.DB, message *tgbotapi.Message) {
	userID := message.From.ID

	rows, err := db.Query("SELECT time, task FROM tasks WHERE user_id = ?", userID)
	if err != nil {
		log.Println("Error fetching tasks:", err)
		return
	}
	defer rows.Close()

	var response string
	for rows.Next() {
		var time, task string
		rows.Scan(&time, &task)
		response += fmt.Sprintf("%s: %s\n", time, task)
	}

	msg := tgbotapi.NewMessage(message.Chat.ID, response)
	bot.Send(msg)
}

func handleDeleteCommand(db *sql.DB, message *tgbotapi.Message) {
	args := message.CommandArguments()
	taskID := 0
	fmt.Sscanf(args, "%d", &taskID)

	deleteTaskSQL := `DELETE FROM tasks WHERE id = ?`
	_, err := db.Exec(deleteTaskSQL, taskID)
	if err != nil {
		log.Println("Error deleting task:", err)
		return
	}

	msg := tgbotapi.NewMessage(message.Chat.ID, "Task deleted successfully.")
	bot.Send(msg)
}

func taskScheduler(bot *tgbotapi.BotAPI, db *sql.DB) {
	for {
		currentTime := time.Now().Format("15:04")

		rows, err := db.Query("SELECT user_id, task FROM tasks WHERE time = ?", currentTime)
		if err != nil {
			log.Println("Error fetching tasks for scheduler:", err)
			continue
		}

		for rows.Next() {
			var userID int
			var task string
			rows.Scan(&userID, &task)

			msg := tgbotapi.NewMessage(int64(userID), fmt.Sprintf("Reminder: %s", task))
			bot.Send(msg)
		}

		rows.Close()
		time.Sleep(1 * time.Minute)
	}
}

Explanation

1. Initialization:
• Initializes the bot with the token.
• Sets up the SQLite3 database and creates the tasks table if it doesn't exist.
2. Command Handling:
• /start: Registers the user in the database.
• /add Time Task: Adds a new task with the specified time for the user.
• /list: Lists all tasks for the user.
• /delete TaskID: Deletes a task by its ID.
3. Task Scheduler:
• A goroutine that checks the database every minute for tasks matching the current time and sends reminders.

Running the Bot

Replace YOUR_TELEGRAM_BOT_API_TOKEN with your actual bot token from BotFather. Then run the bot:

shКопировать кодgo run main.go

This bot now supports adding tasks with a time, listing tasks, deleting tasks, and sends reminders at the specified time.