carmessential.sytes.net - admin.go

     1  package main
     2  
     3  import (
     4  	"bytes"
     5  	"crypto/sha256"
     6  	"database/sql"
     7  	_ "embed"
     8  	"encoding/base64"
     9  	"encoding/json"
    10  	"errors"
    11  	"fmt"
    12  	"html/template"
    13  	"io"
    14  	"math/rand"
    15  	"net/http"
    16  	"os"
    17  	"strconv"
    18  	"sync"
    19  	"sync/atomic"
    20  	"time"
    21  
    22  	"golang.org/x/net/websocket"
    23  	"vimagination.zapto.org/form"
    24  	"vimagination.zapto.org/jsonrpc"
    25  	"vimagination.zapto.org/sessions"
    26  
    27  	_ "github.com/mattn/go-sqlite3"
    28  )
    29  
    30  type treatment struct {
    31  	ID          uint64 `json:"id"`
    32  	Name        string `json:"name"`
    33  	Group       string `json:"group"`
    34  	Price       uint32 `json:"price"`
    35  	Description string `json:"description"`
    36  	Duration    uint32 `json:"duration"`
    37  }
    38  
    39  type booking struct {
    40  	ID          uint64 `json:"id"`
    41  	Date        uint64 `json:"date"`
    42  	BlockNum    uint8  `json:"blockNum"`
    43  	TotalBlocks uint8  `json:"totalBlock"`
    44  	TreatmentID uint64 `json:"treatmentID"`
    45  	Name        string `json:"name"`
    46  	Email       string `json:"emailAddress"`
    47  	Phone       string `json:"phoneNumber"`
    48  	OrderID     uint64 `json:"orderID"`
    49  }
    50  
    51  type voucher struct {
    52  	ID        uint64 `json:"id"`
    53  	Code      string `json:"code"`
    54  	Name      string `json:"name"`
    55  	Expiry    uint64 `json:"expiry"`
    56  	OrderID   uint64 `json:"orderID"`
    57  	IsValue   bool   `json:"isValue"`
    58  	Value     uint64 `json:"value"`
    59  	Valid     bool   `json:"valid"`
    60  	OrderUsed uint64 `json:"orderUsed"`
    61  }
    62  
    63  type order struct {
    64  	Name         string    `json:"name"`
    65  	Price        uint64    `json:"price"`
    66  	Bookings     []booking `json:"bookings"`
    67  	Vouchers     []voucher `json:"vouchers"`
    68  	UsedVouchers []uint64  `json:"usedVouchers"`
    69  }
    70  
    71  const (
    72  	setHeaderFooter = iota
    73  
    74  	listTreatments
    75  	addTreatment
    76  	setTreatment
    77  	removeTreatment
    78  
    79  	orderTime
    80  	getOrders
    81  	addOrder
    82  	removeOrder
    83  	removeOrderBookings
    84  	removeOrderVouchers
    85  
    86  	listBookings
    87  	addBooking
    88  	updateBooking
    89  	removeBooking
    90  
    91  	getVoucher
    92  	getVoucherByCode
    93  	addVoucher
    94  	updateVoucher
    95  	removeVoucher
    96  	setVoucherValid
    97  	checkVoucherCode
    98  	useVoucher
    99  
   100  	totalStmts
   101  )
   102  
   103  const codeChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
   104  
   105  var (
   106  	//go:embed admin.html
   107  	adminPage []byte
   108  	//go:embed login.html
   109  	loginPage     string
   110  	adminOnline   uint32
   111  	oneAdmin      = []byte("{\"id\":-1,\"error\":{\"code\":1,\"message\":\"admin online\"}}")
   112  	goodAdmin     = []byte("{\"id\":-1,\"result\":0}")
   113  	loginTemplate *template.Template
   114  	db            *sql.DB
   115  
   116  	hf     sync.RWMutex
   117  	header string
   118  	footer string
   119  
   120  	statements [totalStmts]*sql.Stmt
   121  )
   122  
   123  type login struct {
   124  	Username string `form:"username,post"`
   125  	Password string `form:"password,post"`
   126  	Error    string `form:"-"`
   127  }
   128  
   129  type admin struct {
   130  	username, password string
   131  	*sessions.CookieStore
   132  	sessionData []byte
   133  	rpc         websocket.Handler
   134  }
   135  
   136  func (a *admin) ServeHTTP(w http.ResponseWriter, r *http.Request) {
   137  	isAdmin := bytes.Equal(a.CookieStore.Get(r), a.sessionData)
   138  	var l login
   139  	if !isAdmin && r.Method == http.MethodPost {
   140  		form.Process(r, &l)
   141  		pass := fmt.Sprintf("%x", sha256.Sum256([]byte(l.Password)))
   142  		if l.Username == a.username && pass == a.password {
   143  			a.CookieStore.Set(w, a.sessionData)
   144  			isAdmin = true
   145  		} else {
   146  			l.Error = "Invalid Username or Password"
   147  		}
   148  	}
   149  	if isAdmin {
   150  		if r.Header.Get("Upgrade") == "websocket" {
   151  			a.rpc.ServeHTTP(w, r)
   152  			return
   153  		}
   154  		w.Header().Set("Content-Type", "text/html")
   155  		w.Write(adminPage)
   156  	} else {
   157  		w.Header().Set("Content-Type", "text/html")
   158  		io.WriteString(w, header)
   159  		loginTemplate.Execute(w, l)
   160  		io.WriteString(w, footer)
   161  	}
   162  }
   163  
   164  func (a *admin) serveConn(wconn *websocket.Conn) {
   165  	hf.RLock()
   166  	_, err := fmt.Fprintf(wconn, "{\"id\":-2,\"result\":[%q,%q]}", header, footer)
   167  	hf.RUnlock()
   168  	if err == nil {
   169  		if atomic.CompareAndSwapUint32(&adminOnline, 0, 1) {
   170  			if _, err := wconn.Write(goodAdmin); err == nil {
   171  				jsonrpc.New(wconn, a).Handle()
   172  				atomic.StoreUint32(&adminOnline, 0)
   173  			}
   174  		} else {
   175  			wconn.Write(oneAdmin)
   176  		}
   177  	}
   178  }
   179  
   180  func (a *admin) HandleRPC(method string, data json.RawMessage) (interface{}, error) {
   181  	switch method {
   182  	case "setHeaderFooter":
   183  		var headfoot [2]string
   184  		if err := json.Unmarshal(data, &headfoot); err != nil {
   185  			return nil, err
   186  		}
   187  		if _, err := statements[setHeaderFooter].Exec(headfoot[0], headfoot[1]); err != nil {
   188  			return nil, err
   189  		}
   190  		hf.Lock()
   191  		header = headfoot[0]
   192  		footer = headfoot[1]
   193  		hf.Unlock()
   194  		generatePages(-1)
   195  		return nil, nil
   196  	case "listTreatments":
   197  		r, err := statements[listTreatments].Query()
   198  		if err != nil {
   199  			return nil, err
   200  		}
   201  		var t treatment
   202  		buf := json.RawMessage{'['}
   203  		first := true
   204  		for r.Next() {
   205  			if err := r.Scan(&t.ID, &t.Name, &t.Group, &t.Price, &t.Description, &t.Duration); err != nil {
   206  				return nil, err
   207  			}
   208  			if first {
   209  				first = false
   210  			} else {
   211  				buf = append(buf, ',')
   212  			}
   213  			buf = strconv.AppendUint(append(buf, "{\"id\":"...), t.ID, 10)
   214  			buf = appendString(append(buf, ",\"name\":"...), t.Name)
   215  			buf = appendString(append(buf, ",\"group\":"...), t.Group)
   216  			buf = strconv.AppendUint(append(buf, ",\"price\":"...), uint64(t.Price), 10)
   217  			buf = appendString(append(buf, ",\"description\":"...), t.Description)
   218  			buf = strconv.AppendUint(append(buf, ",\"price\":"...), uint64(t.Duration), 10)
   219  			buf = append(buf, '}')
   220  		}
   221  		return append(buf, ']'), nil
   222  	case "addTreatment":
   223  		var t treatment
   224  		if err := json.Unmarshal(data, &t); err != nil {
   225  			return nil, err
   226  		}
   227  		r, err := statements[addTreatment].Exec(t.Name, t.Group, t.Price, t.Description, t.Duration)
   228  		if err != nil {
   229  			return nil, err
   230  		}
   231  		id, err := r.LastInsertId()
   232  		if err != nil {
   233  			return nil, err
   234  		}
   235  		generatePages(id)
   236  		return id, nil
   237  	case "setTreatment":
   238  		var t treatment
   239  		if err := json.Unmarshal(data, &t); err != nil {
   240  			return nil, err
   241  		}
   242  		if _, err := statements[setTreatment].Exec(t.Name, t.Group, t.Price, t.Description, t.Duration, t.ID); err != nil {
   243  			return nil, err
   244  		}
   245  		generatePages(int64(t.ID))
   246  		return nil, nil
   247  	case "removeTreatment":
   248  		var id uint64
   249  		if err := json.Unmarshal(data, &id); err != nil {
   250  			return nil, err
   251  		}
   252  		if _, err := statements[removeTreatment].Exec(id); err != nil {
   253  			return nil, err
   254  		}
   255  		// remove treatment page
   256  		generatePages(-1)
   257  		return nil, nil
   258  	case "getOrderTime":
   259  		var id uint64
   260  		if err := json.Unmarshal(data, &id); err != nil {
   261  			return nil, err
   262  		}
   263  		var t uint64
   264  		if err := statements[orderTime].QueryRow(id).Scan(&t); err != nil {
   265  			return nil, err
   266  		}
   267  		return t, nil
   268  	case "addOrder":
   269  		var order order
   270  		if err := json.Unmarshal(data, &order); err != nil {
   271  			return nil, err
   272  		}
   273  		tx, err := db.Begin()
   274  		if err != nil {
   275  			return nil, err
   276  		}
   277  		defer tx.Rollback()
   278  		r, err := tx.Stmt(statements[addOrder]).Exec(uint64(time.Now().Unix()), order.Name, order.Price)
   279  		if err != nil {
   280  			return nil, err
   281  		}
   282  		oid, err := r.LastInsertId()
   283  		if err != nil {
   284  			return nil, err
   285  		}
   286  		orderID := uint64(oid)
   287  		buf := strconv.AppendUint(append(data[:0], "{\"orderID\":"...), orderID, 10)
   288  		buf = append(buf, ",\"bookings\":["...)
   289  		if len(order.Bookings) > 0 {
   290  			addBooking := tx.Stmt(statements[addBooking])
   291  			for n, b := range order.Bookings {
   292  				r, err := addBooking.Exec(b.Date, b.BlockNum, b.TotalBlocks, b.TreatmentID, b.Name, b.Email, b.Phone, orderID)
   293  				if err != nil {
   294  					return nil, err
   295  				}
   296  				id, err := r.LastInsertId()
   297  				if err != nil {
   298  					return nil, err
   299  				}
   300  				if n > 0 {
   301  					buf = append(buf, ',')
   302  				}
   303  				buf = strconv.AppendUint(buf, uint64(id), 10)
   304  			}
   305  		}
   306  		buf = append(buf, "],\"vouchers\":["...)
   307  		if len(order.Vouchers) > 0 {
   308  			addVoucher := tx.Stmt(statements[addVoucher])
   309  			checkVoucher := tx.Stmt(statements[checkVoucherCode])
   310  			for n, v := range order.Vouchers {
   311  				code := make([]byte, 0, 10)
   312  				for valid := 1; valid == 1; {
   313  					code = code[:8+rand.Intn(3)]
   314  					for n := range code {
   315  						code[n] = codeChars[rand.Intn(len(codeChars))]
   316  					}
   317  					v.Code = string(code)
   318  					if err := checkVoucher.QueryRow(v.Code).Scan(&valid); err != nil {
   319  						return nil, err
   320  					}
   321  				}
   322  				r, err := addVoucher.Exec(v.Code, v.Name, v.Expiry, v.OrderID, v.IsValue, v.Valid, v.Valid)
   323  				if err != nil {
   324  					return nil, err
   325  				}
   326  				id, err := r.LastInsertId()
   327  				if err != nil {
   328  					return nil, err
   329  				}
   330  				if n > 0 {
   331  					buf = append(buf, ',')
   332  				}
   333  				buf = strconv.AppendUint(buf, uint64(id), 10)
   334  			}
   335  		}
   336  		buf = append(buf, ']', '}')
   337  		if len(order.UsedVouchers) > 0 {
   338  			useVoucher := tx.Stmt(statements[useVoucher])
   339  			for _, u := range order.UsedVouchers {
   340  				if _, err := useVoucher.Exec(orderID, u); err != nil {
   341  					return nil, err
   342  				}
   343  			}
   344  		}
   345  		if err := tx.Commit(); err != nil {
   346  			return nil, err
   347  		}
   348  		return buf, nil
   349  	case "removeOrder":
   350  		var id uint64
   351  		if err := json.Unmarshal(data, &id); err != nil {
   352  			return nil, err
   353  		}
   354  		tx, err := db.Begin()
   355  		if err != nil {
   356  			return nil, err
   357  		}
   358  		defer tx.Rollback()
   359  		if _, err := tx.Stmt(statements[removeOrderBookings]).Exec(id); err != nil {
   360  			return nil, err
   361  		}
   362  		if _, err := tx.Stmt(statements[removeOrderVouchers]).Exec(id); err != nil {
   363  			return nil, err
   364  		}
   365  		if _, err := tx.Stmt(statements[removeOrder]).Exec(id); err != nil {
   366  			return nil, err
   367  		}
   368  		if err := tx.Commit(); err != nil {
   369  			return nil, err
   370  		}
   371  		return nil, nil
   372  	case "listBookings":
   373  		var dates [2]uint64
   374  		if err := json.Unmarshal(data, &dates); err != nil {
   375  			return nil, err
   376  		}
   377  		r, err := statements[listBookings].Query(dates[0], dates[1])
   378  		if err != nil {
   379  			return nil, err
   380  		}
   381  		var b booking
   382  		buf := json.RawMessage{'['}
   383  		first := true
   384  		for r.Next() {
   385  			if err := r.Scan(&b.ID, &b.Date, &b.BlockNum, &b.TotalBlocks, &b.TreatmentID, &b.Name, &b.Email, &b.Phone, &b.OrderID); err != nil {
   386  				return nil, err
   387  			}
   388  			if first {
   389  				first = false
   390  			} else {
   391  				buf = append(buf, ',')
   392  			}
   393  			buf = strconv.AppendUint(append(buf, "{\"id\":"...), b.ID, 10)
   394  			buf = strconv.AppendUint(append(buf, ",\"date\":"...), b.Date, 10)
   395  			buf = appendNum(append(buf, ",\"blockNum\":"...), b.BlockNum)
   396  			buf = appendNum(append(buf, ",\"totalBlocks\":"...), b.TotalBlocks)
   397  			buf = strconv.AppendUint(append(buf, ",\"treatmentID\":"...), b.TreatmentID, 10)
   398  			buf = appendString(append(buf, ",\"name\":"...), b.Name)
   399  			buf = appendString(append(buf, ",\"emailAddress\":"...), b.Email)
   400  			buf = appendString(append(buf, ",\"phoneNumber\":"...), b.Phone)
   401  			buf = strconv.AppendUint(append(buf, ",\"orderID\":"...), b.OrderID, 10)
   402  			buf = append(buf, '}')
   403  		}
   404  		return append(buf, ']'), nil
   405  	case "updateBooking":
   406  		var b booking
   407  		if err := json.Unmarshal(data, &b); err != nil {
   408  			return nil, err
   409  		}
   410  		if _, err := statements[updateBooking].Exec(b.Date, b.BlockNum, b.TotalBlocks, b.TreatmentID, b.Name, b.Email, b.Phone); err != nil {
   411  			return nil, err
   412  		}
   413  		return nil, nil
   414  	case "removeBooking":
   415  		var bID uint64
   416  		if err := json.Unmarshal(data, &bID); err != nil {
   417  			return nil, err
   418  		}
   419  		if _, err := statements[removeBooking].Exec(bID); err != nil {
   420  			return nil, err
   421  		}
   422  		return nil, nil
   423  	case "getVoucher":
   424  		var id uint64
   425  		if err := json.Unmarshal(data, &id); err != nil {
   426  			return nil, err
   427  		}
   428  		var (
   429  			v       voucher
   430  			isValue uint8
   431  			valid   uint8
   432  		)
   433  		if err := statements[getVoucher].QueryRow(id).Scan(&v.Code, &v.Name, &v.Expiry, &v.OrderID, isValue, &v.Value, &valid, &v.OrderUsed); err != nil {
   434  			return nil, err
   435  		}
   436  		v.ID = id
   437  		v.IsValue = isValue == 1
   438  		v.Valid = valid == 1
   439  		return v, nil
   440  	case "getVoucherByCode":
   441  		var code string
   442  		if err := json.Unmarshal(data, &code); err != nil {
   443  			return nil, err
   444  		}
   445  		var (
   446  			v       voucher
   447  			isValue uint8
   448  			valid   uint8
   449  		)
   450  		if err := statements[getVoucherByCode].QueryRow(code).Scan(&v.ID, &v.Name, &v.Expiry, &v.OrderID, isValue, &v.Value, &valid, &v.OrderUsed); err != nil {
   451  			return nil, err
   452  		}
   453  		v.Code = code
   454  		v.IsValue = isValue == 1
   455  		v.Valid = valid == 1
   456  		return v, nil
   457  	case "updateVoucher":
   458  		var v voucher
   459  		if err := json.Unmarshal(data, &v); err != nil {
   460  			return nil, err
   461  		}
   462  		if _, err := statements[updateVoucher].Exec(v.Name, v.Expiry, v.ID); err != nil {
   463  			return nil, err
   464  		}
   465  		return nil, nil
   466  	case "removeVoucher":
   467  		var id uint64
   468  		if err := json.Unmarshal(data, &id); err != nil {
   469  			return nil, err
   470  		}
   471  		if _, err := statements[updateVoucher].Exec(id); err != nil {
   472  			return nil, err
   473  		}
   474  		return nil, nil
   475  	case "setVoucherValid":
   476  		var idValid struct {
   477  			ID    uint64 `json:"id"`
   478  			Valid bool   `json:"valid"`
   479  		}
   480  		if err := json.Unmarshal(data, &idValid); err != nil {
   481  			return nil, err
   482  		}
   483  		valid := 0
   484  		if idValid.Valid {
   485  			valid = 1
   486  		}
   487  		if _, err := statements[setVoucherValid].Exec(valid, idValid.ID); err != nil {
   488  			return nil, err
   489  		}
   490  		return nil, nil
   491  	default:
   492  		return nil, errors.New("unknown endpoint")
   493  	}
   494  }
   495  
   496  func generatePages(id int64) {
   497  }
   498  
   499  func init() {
   500  	if a, err := adminInit(); err == nil {
   501  		http.Handle("/admin", a)
   502  	} else {
   503  		fmt.Fprintln(os.Stderr, err)
   504  	}
   505  }
   506  
   507  func adminInit() (*admin, error) {
   508  	user := os.Getenv("adminUser")
   509  	if user == "" {
   510  		return nil, errors.New("no admin username")
   511  	}
   512  	pass := os.Getenv("adminPass")
   513  	if len(pass) != 64 {
   514  		return nil, errors.New("no admin password")
   515  	}
   516  	adminDB := os.Getenv("adminDB")
   517  	if adminDB == "" {
   518  		return nil, errors.New("no admin database")
   519  	}
   520  	key, _ := base64.StdEncoding.DecodeString(os.Getenv("adminKey"))
   521  	if len(key) != 16 {
   522  		return nil, errors.New("no admin key")
   523  	}
   524  	data, _ := base64.StdEncoding.DecodeString(os.Getenv("adminData"))
   525  	if len(data) != 32 {
   526  		return nil, errors.New("no admin data")
   527  	}
   528  	store, err := sessions.NewCookieStore(key, sessions.HTTPOnly(), sessions.Path("/"), sessions.Name("admin"), sessions.Expiry(time.Hour*24*30))
   529  	if err != nil {
   530  		return nil, fmt.Errorf("error creating cookie store: %w", err)
   531  	}
   532  	db, err = sql.Open("sqlite3", adminDB)
   533  	if err != nil {
   534  		return nil, fmt.Errorf("error opening sqlite database: %w", err)
   535  	}
   536  	a := &admin{
   537  		username:    user,
   538  		password:    pass,
   539  		CookieStore: store,
   540  		sessionData: data,
   541  	}
   542  	a.rpc = websocket.Handler(a.serveConn)
   543  	loginTemplate, _ = template.New("login").Parse(loginPage)
   544  	for _, ct := range []string{
   545  		"CREATE TABLE IF NOT EXISTS [Settings] ([Version] INTEGER DEFAULT 0, [Header] TEXT NOT NULL DEFAULT '', [Footer] TEXT NOT NULL DEFAULT '');",
   546  		"CREATE TABLE IF NOT EXISTS [Treatments] ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [Name] TEXT NOT NULL, [Group] TEXT NOT NULL DEFAULT '', [Price] INTEGER NOT NULL, [Description] TEXT NOT NULL DEFAULT '', [Duration] INTEGER NOT NULL, [Deleted] BOOLEAN DEFAULT 1 NOT NULL CHECK ([Deleted] IN (0,1)));",
   547  		"CREATE TABLE IF NOT EXISTS [Orders] ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [Time] INTEGER NOT NULL, [Name] TEXT NOT NULL, [Total] INTEGER NOT NULL, [Deleted] BOOLEAN DEFAULT 1 NOT NULL CHECK ([Deleted] IN (0,1)));",
   548  		"CREATE TABLE IF NOT EXISTS [Bookings] ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [Date] INTEGER NOT NULL, [BlockNum] INTEGER NOT NULL, [TotalBlocks] INTEGER NOT NULL, [TreatmentID] INTEGER NOT NULL, [Name] TEXT NOT NULL DEFAULT '', [EmailAddress] NOT NULL DEFAULT '', [PhoneNumber] NOT NULL DEFAULT '', [OrderID] INTEGER NOT NULL, [Deleted] BOOLEAN DEFAULT 1 NOT NULL CHECK ([Deleted] IN (0,1)));",
   549  		"CREATE TABLE IF NOT EXISTS [Vouchers] ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [CODE] TEXT NOT NULL UNIQUE, [Name] TEXT NOT NULL, [Expiry] INTEGER NOT NULL, [OrderID] INTEGER NOT NULL, [IsValue] BOOLEAN DEFAULT 0 NOT NULL CHECK ([IsValue] IN (0,1)), [Value] INTEGER NOT NULL, [Valid] BOOLEAN DEFAULT 1 NOT NULL CHECK ([Valid] IN (0,1)), [OrderUsed] INTEGER NOT NULL DEFAULT 0, [Deleted] BOOLEAN DEFAULT 1 NOT NULL CHECK ([Deleted] IN (0,1)));",
   550  	} {
   551  		if _, err = db.Exec(ct); err != nil {
   552  			return nil, fmt.Errorf("error creating table with command `%s`: %w", ct, err)
   553  		}
   554  	}
   555  	count := 0
   556  	if err := db.QueryRow("SELECT COUNT(1) FROM [Settings];").Scan(&count); err != nil {
   557  		return nil, fmt.Errorf("error checking settings: %w", err)
   558  	}
   559  	if count == 0 {
   560  		if _, err = db.Exec("INSERT INTO [Settings] ([Version]) VALUES (0);"); err != nil {
   561  			return nil, fmt.Errorf("error setting initial db version: %w", err)
   562  		}
   563  	} else if err = db.QueryRow("SELECT [Header], [Footer] FROM [Settings];").Scan(&header, &footer); err != nil {
   564  		return nil, fmt.Errorf("error getting header/footer from settings: %w", err)
   565  	}
   566  	for n, ps := range []string{
   567  		"UPDATE [Settings] SET [Header] = ?, [Footer] = ?;",
   568  
   569  		// Treatments
   570  		"SELECT [ID], [Name], [Group], [Price], [Description], [Duration] FROM [Treatments] WHERE [Deleted] = 0;",
   571  		"INSERT INTO [Treatments] ([Name], [Group], [Price], [Description], [Duration]) VALUES (?, ?, ?, ?, ?);",
   572  		"UPDATE [Treatments] SET [Name] = ?, [Group] = ?, [Price] = ?, [Description] = ?, [Duration] = ? WHERE [ID] = ?;",
   573  		"UPDATE [Treatments] SET [Deleted] = 1 WHERE [ID] = ?;",
   574  
   575  		// Orders
   576  		"SELECT [Time] FROM [Orders] WHERE [ID] = ? AND [Deleted] = 0;",
   577  		"SELECT [Time], [Name], [Total] FROM [Orders] WHERE [Deleted] = 0;",
   578  		"INSERT INTO [Orders] ([Time], [Name], [Total]) VALUES (?, ?, ?);",
   579  		"UPDATE [Orders] SET [Deleted] = 1 WHERE [ID] = ?;",
   580  		"UPDATE [Bookings] SET [Deleted] = 1 WHERE [OrderID] = ?;",
   581  		"UPDATE [Vouchers] SET [Deleted] = 1 WHERE [OrderID] = ?;",
   582  
   583  		// Bookings
   584  		"SELECT [ID], [Date], [BlockNum], [TotalBlocks], [TreatmentID], [Name], [EmailAddress], [PhoneNumber], [OrderID] FROM [Bookings] WHERE [Deleted] = 0 AND [Date] BETWEEN ? AND ? ORDER BY [Date] ASC, [BlockNum] ASC;",
   585  		"INSERT INTO [Bookings] ([Date], [BlockNum], [TotalBlocks], [TreatmentID], [Name], [EmailAddress], [PhoneNumber], [OrderID]) VALUES (?, ?, ?, ?, ?, ?, ?, ?);",
   586  		"UPDATE [Bookings] SET [Date] = ?, [BlockNum] = ?, [TotalBlocks] = ?, [TreatmentID] = ?, [Name] = ?, [EmailAddress] = ?, [PhoneNumber] = ? WHERE [ID] = ?;",
   587  		"UPDATE [Bookings] Set [Deleted] = 1 WHERE [ID] = ?;",
   588  
   589  		// Vouchers
   590  		"SELECT [Code], [Name], [Expiry], [OrderID], [IsValue], [Value], [Valid], [OrderUsed] FROM [Vouchers] WHERE [ID] = ? AND [Deleted] = 0;",
   591  		"SELECT [ID], [Name], [Expiry], [OrderID], [IsValue], [Value], [Valid], [OrderUsed] FROM [Vouchers] WHERE [Code] = ? AND [Deleted] = 0;",
   592  		"INSERT INTO [Vouchers] ([Code], [Name], [Expiry], [OrderID], [IsValue], [Value]) VALUES (?, ?, ?, ?, ?, ?);",
   593  		"UPDATE [Vouchers] SET [Name] = ?, [Expiry] = ? WHERE [ID] = ?;",
   594  		"UPDATE [Vouchers] SET [Deleted] = 1 WHERE [ID] = ?;",
   595  		"UPDATE [Vouchers] SET [Valid] = ? WHERE [ID] = ?;",
   596  		"UPDATE [Vouchers] SET [Valid] = 0, [OrderUsed] = ? WHERE [ID] = ?;",
   597  	} {
   598  		stmt, err := db.Prepare(ps)
   599  		if err != nil {
   600  			return nil, fmt.Errorf("error preparing statement `%s`: %w", ps, err)
   601  		}
   602  		statements[n] = stmt
   603  	}
   604  	return a, nil
   605  }
   606  
   607  const hex = "0123456789abcdef"
   608  
   609  func appendString(p []byte, s string) []byte {
   610  	last := 0
   611  	var char byte
   612  	p = append(p, '"')
   613  	for n, c := range s {
   614  		switch c {
   615  		case '"', '\\', '/':
   616  			char = byte(c)
   617  		case '\b':
   618  			char = 'b'
   619  		case '\f':
   620  			char = 'f'
   621  		case '\n':
   622  			char = 'n'
   623  		case '\r':
   624  			char = 'r'
   625  		case '\t':
   626  			char = 't'
   627  		default:
   628  			if c < 0x20 { // control characters
   629  				p = append(append(p, s[last:n]...), '\\', 'u', '0', '0', hex[c>>4], hex[c&0xf])
   630  				last = n + 1
   631  			}
   632  			continue
   633  		}
   634  		p = append(append(p, s[last:n]...), '\\', char)
   635  		last = n + 1
   636  	}
   637  	return append(append(p, s[last:]...), '"')
   638  }
   639  
   640  func appendNum(p []byte, n uint8) []byte {
   641  	if n >= 100 {
   642  		c := n / 100
   643  		n -= c * 100
   644  		p = append(p, '0'+c)
   645  		if n < 10 {
   646  			p = append(p, '0')
   647  		}
   648  	}
   649  	if n >= 10 {
   650  		c := n / 10
   651  		n -= c * 10
   652  		p = append(p, '0'+c)
   653  	}
   654  	return append(p, '0'+n)
   655  }
   656