job-cheng-xing/repository/sqlite.go

package repository

import (
	"database/sql"
	"time"

	"job-cheng-xing/model"

	_ "github.com/mattn/go-sqlite3"
)

type SQLiteRepo struct {
	db *sql.DB
}

func NewSQLiteRepo(dbPath string) (*SQLiteRepo, error) {
	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		return nil, err
	}

	_, err = db.Exec(`
		CREATE TABLE IF NOT EXISTS orders (
			id TEXT PRIMARY KEY,
			status TEXT NOT NULL,
			service_time TEXT NOT NULL,
			duration INTEGER NOT NULL,
			address TEXT NOT NULL,
			provider_id TEXT,
			created_at TEXT NOT NULL,
			updated_at TEXT NOT NULL
		)
	`)
	if err != nil {
		db.Close()
		return nil, err
	}

	return &SQLiteRepo{db: db}, nil
}

func (r *SQLiteRepo) Close() error {
	return r.db.Close()
}

func (r *SQLiteRepo) Create(order *model.Order) error {
	_, err := r.db.Exec(
		`INSERT INTO orders (id, status, service_time, duration, address, provider_id, created_at, updated_at)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
		order.ID, string(order.Status), order.ServiceTime.Format(time.RFC3339),
		order.Duration, order.Address, order.ProviderID,
		order.CreatedAt.Format(time.RFC3339), order.UpdatedAt.Format(time.RFC3339),
	)
	return err
}

func (r *SQLiteRepo) FindByID(id string) (*model.Order, error) {
	row := r.db.QueryRow(
		`SELECT id, status, service_time, duration, address, provider_id, created_at, updated_at
		 FROM orders WHERE id = ?`, id,
	)

	var order model.Order
	var serviceTimeStr, createdAtStr, updatedAtStr string
	var providerID sql.NullString

	err := row.Scan(
		&order.ID, &order.Status, &serviceTimeStr,
		&order.Duration, &order.Address, &providerID,
		&createdAtStr, &updatedAtStr,
	)
	if err == sql.ErrNoRows {
		return nil, model.ErrNotFound
	}
	if err != nil {
		return nil, err
	}

	order.ServiceTime, _ = time.Parse(time.RFC3339, serviceTimeStr)
	order.CreatedAt, _ = time.Parse(time.RFC3339, createdAtStr)
	order.UpdatedAt, _ = time.Parse(time.RFC3339, updatedAtStr)
	if providerID.Valid {
		order.ProviderID = &providerID.String
	}

	return &order, nil
}

func (r *SQLiteRepo) Update(id string, oldStatus model.OrderStatus, newOrder *model.Order) error {
	now := time.Now().Format(time.RFC3339)

	var providerID interface{}
	if newOrder.ProviderID != nil {
		providerID = *newOrder.ProviderID
	}

	result, err := r.db.Exec(
		`UPDATE orders SET status = ?, provider_id = ?, updated_at = ? WHERE id = ? AND status = ?`,
		string(newOrder.Status), providerID, now, id, string(oldStatus),
	)
	if err != nil {
		return err
	}

	rowsAffected, _ := result.RowsAffected()
	if rowsAffected == 0 {
		_, err := r.FindByID(id)
		if err == model.ErrNotFound {
			return model.ErrNotFound
		}
		return model.ErrStatusConflict
	}

	return nil
}