slmagic.go

/* SLMagic - A very dangerous app.  Stay back!

SLMagic (slmagic) is a small app designed to ease the burden of
corporation management for the CEOs of corporations that are a
part of Eve Online's player-ran GSF.  If you have no idea what
I'm talking about, this program would be useless to you.

slmagic is designed to be ran as a cron task, perhaps once a
week.  It's designed for any basic linux system, no guarantees
that it will work on Windows, but it's possible.

Install Go (as a user):

	http://golang.org/doc/install

As a final test:

	% go version

If this fails, seek help to install Go.  Go is a great little
programming language.  Next, please ensure that your system has
sqlite3.  See your package manager for details.  As root:

	(root) # emerge -avt sqlite3

And you'll need some Go packages (back as user):

	% go get github.com/mattn/go-sqlite3
	% go get github.com/spf13/viper
	$ go get github.com/spf13/cobra

Now grab the raw copy of this source code from the github GIST and
compile it (back as user):

	% go build slmagic.go

Assuming everything built okay, move it to its final resting spot

	% mkdir ~/.slmagic
	% mv slmagic ~/.slmagic/
	% cd ~/.slmagic

Because slmagic is best operated as a cron task, with no
interaction from the user, ensure that vixie-cron, fcron, or some
variant is installed.  There is no reason slmagic won't run with
At on windows systems, see	the At docs.

Notes:
Trello app key: 6fb1834f9b6ee0b21fe8a70b6f3455c7
The Eve APIv2 endpoint CharNotifications is described here
https://neweden-dev.com/Char/Notifications and conforms to 'Short
Cache Style'.  You should know these rules before you set up your
cron jobs.
	o A request will only return a maximum of 200 most-recent events
	o A request will only return events from the previous 7 days
	o Subsequent requests will not repeat events reported earlier
	o To 'reset timer' and get all (max 200, 7 days) events, you
		must wait at least 6 hours between API calls. From my own
		experience though, I kept getting the same test data over and
		over without having to wait any 6 hours. YMMV IANAL IKR LOL.

At least set your conf file to perms 0600 so no one else who has
access to your computer can read your API keys and smtp server creds.

	% chmod 0600 slmagic.toml

The other endpoint, the corporate one /Corp/WalletJournal conforms to
the Short Cache Style also, though it took a long time for me to get
a test bounty tax to show up.

The post should look thusly:
Aug 25:

Join: 5
Leave 2
Tax rate: 10%
Income 4.4b
News: One recruiter fired, one hired

Some of this code ganked from
https://github.com/kilgarth/member_tracking Kilgarth's awesome
corp tracking software.  The bitch hasn't licensed it, so I'll
just assume WTFPL. Fucking pubbie.

TODO:
	o Clean up the file comments
	o Test that #s are correct

Copyright (c) 2015, Andail Chanter <xVMx> [condi]
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions
are met:

1. Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.

2. Redistributions in binary form must reproduce the above
copyright notice, this list of conditions and the following
disclaimer in the documentation and/or other materials provided
with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND
CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR
CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED
AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN
ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE. SO THERE!
*/
package main

import (
	"database/sql"
	"encoding/json"
	"encoding/xml"
	"fmt"
	"io/ioutil"
	"log"
	"net/http"
	"net/smtp"
	"net/url"
	"os"
	"strconv"
	"strings"
	"time"

	_ "github.com/mattn/go-sqlite3"
	"github.com/spf13/cobra"
	"github.com/spf13/viper"
)

func main() {
	var err error

	viper.SetConfigName("slmagic")
	viper.SetConfigType("toml")
	pwd, err = os.Getwd()
	checkErr("os.Getwd():", err)
	viper.AddConfigPath(pwd)
	if err := viper.ReadInConfig(); err != nil {
		// Config file not found!  create it and terminate application
		createConfFile()
		appGood = false
	}

	SlmagicCmd.AddCommand(fetchCmd, postCmd)

	// Plug and pray
	if appGood {
		SlmagicCmd.Execute()
	}
}

// Cobra Setup
var SlmagicCmd = &cobra.Command{
	Use: "slmagic",
	Short: fmt.Sprintf("SLMagic %s is a very dangerous app.  Stay back!",
		appVer),
	Long: `SLMagic eases the burden of the reporting requirements of CEOs of
corporations belonging to the Goonswarm Federation, in the Eve Online
game.`,
	Run: func(cmd *cobra.Command, args []string) {
		// Do stuff here
	},
}

var fetchCmd = &cobra.Command{
	Use:   "fetch",
	Short: "Fetches data from Eve's API",
	Long: `Fetches corporation data from Eve Online's CREST interface, and writes
this data to the app's database.`,
	Run: func(cmd *cobra.Command, args []string) {
		initDB()

		// FIRST: Fetch from CEO API, Users joining and quitting corp
		// Get conf file data
		ids := &Records{}
		ceoKey := viper.Get("eve.ceoKey")
		ceovCode := viper.Get("eve.ceovCode")
		req, err := http.NewRequest(
			"GET",
			fmt.Sprintf("https://api.eveonline.com/Char/Notifications.xml.aspx?keyid=%s&vcode=%s",
				ceoKey,
				ceovCode), nil)
		client := &http.Client{}
		res, err := client.Do(req)
		defer res.Body.Close()

		if err != nil {
			log.Printf("Error getting Header API data: %s", err)
			return
		}

		if res.StatusCode != 200 {
			log.Printf("Header API returned non 200 response: %d", res.StatusCode)
			return
		}

		// validate result from CCP
		d := xml.NewDecoder(res.Body)
		decoded := &NotificationHeaders{}
		err = d.Decode(&decoded)
		if err != nil {
			log.Printf("Error decoding header API data: %s", err)
		}

		// Emit xml for testing purposes.  Note you can do this once every 21 mins
		for _, v := range decoded.Notifications {
			if v.TypeID == 21 || v.TypeID == 128 {
				sentDate, err := time.Parse("2006-01-02 15:04:05", v.SentDate)
				if err != nil {
					log.Printf("Error parsing time: %s", err)
				}
				ids.Events = append(
					ids.Events,
					InsertData{
						CharID:             v.SenderID,
						SenderName:         v.SenderName,
						NotificationID:     v.NotificationID,
						NotificationTypeID: v.TypeID,
						Date:               sentDate})
			}
		}

		// Store resulting notifications
		storeEvents(ids)

		// SECOND: Fetch Ratting Bounties from Corp API
		wids := &Wrecords{}
		corpKey := viper.Get("eve.corpKey")
		corpvCode := viper.Get("eve.corpvCode")

		wreq, err := http.NewRequest(
			"GET",
			fmt.Sprintf("https://api.eveonline.com/Corp/WalletJournal.xml.aspx?keyid=%s&vcode=%s",
				corpKey,
				corpvCode), nil)
		wclient := &http.Client{}
		wres, err := wclient.Do(wreq)
		defer wres.Body.Close()
		if err != nil {
			log.Printf("Error getting Events API data: %s", err)
			return
		}

		if wres.StatusCode != 200 {
			log.Printf("Events API returned non 200 response: %d", wres.StatusCode)
			return
		}

		// validate result from ccp
		wd := xml.NewDecoder(wres.Body)
		wdecoded := &EntriesHeaders{}
		err = wd.Decode(&wdecoded)
		if err != nil {
			log.Fatalf("Error decoding header API data: %s", err)
		}

		// Emit xml for testing purposes
		for _, v := range wdecoded.Entries {
			if v.RefTypeID == 33 || v.RefTypeID == 34 || v.RefTypeID == 85 {
				date, err := time.Parse("2006-01-02 15:04:05", v.Date)
				if err != nil {
					log.Fatalf("Error parsing date/time: %s", err)
				}
				wids.Jentries = append(
					wids.Jentries,
					EData{
						RefID:     v.RefID,
						RefTypeID: v.RefTypeID,
						Date:      date,
						Amount:    v.Amount,
					})
			}
		}

		storeEntries(wids)
	},
}

var postCmd = &cobra.Command{
	Use:   "post",
	Short: "Posts data to Trello",
	Long:  "Posts all compiled data to your comment card on Trello",
	Run: func(cmd *cobra.Command, args []string) {
		var corpTax string
		dateStamp := time.Now().Format("Jan 2")
		now := time.Now()
		_, month, _ := now.Date()

		// Test email system:
		rm := &SendMail{
			smtp:      viper.Get("application.smtp").(string),
			user:      viper.Get("application.smtpuser").(string),
			pw:        viper.Get("application.smtppw").(string),
			toAddress: viper.Get("application.email").(string),
			subject:   "Subject placeholder",
			message:   "message placeholder",
		}

		hasMail := true
		if rm.smtp == "" || rm.user == "" || rm.pw == "" || rm.toAddress == "" {
			hasMail = false
		}

		db, err := sql.Open("sqlite3", "slmagic.db")
		CheckPostError(rm, "sql.Open", hasMail, err)

		// Grab current tax rate
		corpKey := viper.Get("eve.corpKey")
		corpvCode := viper.Get("eve.corpvCode")
		req, err := http.NewRequest(
			"GET",
			fmt.Sprintf("https://api.eveonline.com/Corp/CorporationSheet.xml.aspx?keyid=%s&vcode=%s",
				corpKey,
				corpvCode), nil)
		client := &http.Client{}
		res, err := client.Do(req)
		defer res.Body.Close()
		CheckPostError(rm, "res.Body.Close()", hasMail, err)

		if res.StatusCode != 200 {
			log.Printf("API returned non 200 response: %d\n", res.StatusCode)
			return
		}

		d := xml.NewDecoder(res.Body)
		decoded := CorpSheet{}
		err = d.Decode(&decoded)
		corpTax = decoded.TaxRate
		CheckPostError(rm, "decoded.TaxRate", hasMail, err)

		// Generate stats
		// Joins and quits
		joins := 0
		quits := 0
		rowsN, err := db.Query("select notificationTypeID, eventDate from member_tracking")
		CheckPostError(rm, "db.Query notificationID", hasMail, err)
		defer rowsN.Close()
		for rowsN.Next() {
			var Date time.Time
			var ID int
			rowsN.Scan(&ID, &Date)
			_, Rmonth, _ := Date.Date()
			if Rmonth == month {
				if ID == 128 {
					joins++
				} else {
					quits++
				}
			}
		}
		rowsN.Close()

		// Generate income report
		rowsB, err := db.Query("select date, amount FROM journal_tracking")
		CheckPostError(rm, "db.Query journal_tracking", hasMail, err)
		defer rowsB.Close()
		v := 0
		for rowsB.Next() {
			var Date time.Time
			var Amount string
			rowsB.Scan(&Date, &Amount)
			_, Rmonth, _ := Date.Date()
			if Rmonth == month {
				value, err := strconv.Atoi(strings.Replace(Amount, ".", "", 1))
				CheckPostError(rm, "strconv.Atoi", hasMail, err)
				v += value
			}
		}
		rowsB.Close()

		// Read in post.txt
		tFile := viper.Get("application.textFile").(string)
		dat, err := ioutil.ReadFile(tFile)
		CheckPostError(rm, "ioutil.ReadFile", hasMail, err)

		// Generate to-post file hallelujah
		ePost := fmt.Sprintf("https://api.trello.com/1/actions/%v/text?key=%v&token=%v&value=%s",
			viper.Get("trello.commentId"),
			viper.Get("trello.appkey"),
			viper.Get("trello.token"),
			strings.Replace(
				url.QueryEscape(
					fmt.Sprintf("%s:\\n\\nJoin: %d\\nLeave: %d\\nTax Rate: %s%%\\nIncome: %s\\nNews: %s",
						dateStamp,
						joins,
						quits,
						corpTax,
						fmt.Sprintf("%v.%02v", tt(v/100, ""), v-((v/100)*100)),
						string(dat))), "%5Cn", "%0A", -1))

		// Post to trello card
		reqA, err := http.NewRequest("PUT", ePost, nil)
		clientA := &http.Client{}
		resA, errResp := clientA.Do(reqA)
		defer resA.Body.Close()
		readback, errrb := ioutil.ReadAll(resA.Body)
		if err != nil || errResp != nil || errrb != nil || resA.StatusCode != 200 {
			if hasMail {
				rm.subject = "slmagic: Posting failure"
				rm.message = fmt.Sprintf(
					"At %v, slmagic failed to update Trello card:\n\rStatusCode: %v\r\nTrello Responded:%s",
					time.Now().Format(time.RFC822),
					resA.StatusCode,
					readback)
				if err := NewSendMail(rm); err != nil {
					log.Fatalln(err)
				}
				return
			} else {
				log.Fatalln(err)
			}
			//log.Fatalln("opps")
		}

		if err != nil && hasMail {
			rm.subject = "slmagic: Posting failure"
			rm.message = time.Now().Format(time.RFC822)
			if err := NewSendMail(rm); err != nil {
				log.Fatalln(err)
			} else {
				log.Fatalln(err)
			}
		}

		// Email notification to whoever needs emailing
		if hasMail {
			rm.subject = "slmagic: Trello card updated"
			// Assemble approximation of message
			rm.message = fmt.Sprintf(
				"%s:\r\n\r\nJoin: %d\r\nLeave: %d\r\nTax Rate:%s%%\r\nIncome: %s\r\nNews: %s",
				dateStamp,
				joins,
				quits,
				corpTax,
				fmt.Sprintf("%v.%02v", tt(v/100, ""), v-((v/100)*100)),
				string(dat))
			if err := NewSendMail(rm); err != nil {
				log.Fatalln("sendMail:", err)
			}
		}

	},
}

// Globals
var appVer string = "v0.2"
var pwd string
var appGood bool = true
var appKey string = "6fb1834f9b6ee0b21fe8a70b6f3455c7"

// Utility functions
func NewSendMail(mo *SendMail) error {
	// Setup headers
	header := make(map[string]string)
	header["From"] = "slmagic <slmagic@goonfleet.com>"
	header["To"] = "shitlord <" + mo.toAddress + ">"
	header["Subject"] = mo.subject
	header["MIME-Version"] = "1.0"
	header["Content-Type"] = "text/plain; charset=\"utf-8\""

	msg := ""
	for k, v := range header {
		msg += fmt.Sprintf("%s: %s\r\n", k, v)
	}
	msg += "\r\n" + string(mo.message)

	auth := smtp.PlainAuth("", mo.user, mo.pw, mo.smtp[:strings.Index(mo.smtp, ":")])
	to := []string{mo.toAddress}
	if err := smtp.SendMail(mo.smtp, auth, mo.user, to, []byte(msg)); err != nil {
		return err
	}
	return nil
}

func sendMail(ssmtp, smtpuser, smtppw, uemail, subject string, res []byte) error {
	// Setup headers
	header := make(map[string]string)
	header["From"] = "slmagic <slmagic@goonfleet.com>"
	header["To"] = "shitlord <" + uemail + ">"
	//header["Subject"] = encodeRFC2047(subject)
	header["Subject"] = "Test 4"
	header["MINE-Version"] = "1.0"
	header["Content-Type"] = "text/plain; charset=\"utf-8\""

	msg := ""
	for k, v := range header {
		msg += fmt.Sprintf("%s: %s\r\n", k, v)
	}
	//msg += "\r\n" + base64.StdEncoding.EncodeToString([]byte(res))
	msg += "\r\n" + string(res)

	auth := smtp.PlainAuth("", smtpuser, smtppw, ssmtp[:strings.Index(ssmtp, ":")])
	to := []string{uemail}
	fmt.Printf("PlainAuth(\"\", \"%s\", \"%s\", \"%s\")\n", smtpuser, smtppw, ssmtp)
	if err := smtp.SendMail(ssmtp, auth, smtpuser, to, []byte(msg)); err != nil {
		return err
	}
	return nil
}

func CheckPostError(rm *SendMail, problem string, hasMail bool, err error) {
	if err == nil {
		return
	}
	if !hasMail {
		log.Fatalln(problem, err)
	}

	rm.subject = "slmagic: Posting failure"
	rm.message = fmt.Sprintf(
		"At %v, slmagic failed to update Trello card:\r\n\r\n%s: %s",
		time.Now().Format(time.RFC822),
		problem,
		err)

	if err := NewSendMail(rm); err != nil {
		log.Fatalf("CheckPostError: %v\n%v\n", problem, err)
	} else {
		log.Fatalln(err)
	}
}

func checkErr(where string, err error) {
	if err != nil {
		log.Fatalln(where, err)
	}
}

func checkConfVal(what string, val string) {
	if val == "" {
		log.Fatalln("Check your conf file.  Empty val", what)
	}
}

func createConfFile() {
	// TODO: refactor the fuck out of this megamethod
	// TODO: make each textblock and prompt into a structure
	appName := "slmagic"

	getTokenURL := fmt.Sprintf("https://trello.com/1/authorize?key=%s&name=%s&expiration=never&response_type=token&scope=read,write,account",
		appKey,
		appName)

	fmt.Println("slmagic will write a new conf file for you now.  If you somehow manage")
	fmt.Println("to fuck this up, stop the app, delete your conf file \"slmagic.toml\",")
	fmt.Println("and start over.\n")

	fmt.Printf("Log in to your trello account and then go to this URL and grab your token.\n%v\n",
		getTokenURL)
	fmt.Print("Your token: ")
	var token string
	if _, err := fmt.Scan(&token); err != nil {
		log.Fatalln(err)
	}

	var card string
	fmt.Println("\nThis next part is a bit complicated.  Trello boards have Trello lists,")
	fmt.Println("which have Trello cards.  If you go to your corporation reporting card,")
	fmt.Println("and click on it, you'll see the 'back of the card'.  I need the card ID")
	fmt.Println("from the URL as you're viewing the back of the corporation reporting")
	fmt.Println("card.  The URL is in the form:\n")
	fmt.Println("https://trello.com/c/<card ID>/#-<card title>\n")
	fmt.Println("From that URL, I need the super-secret card ID.")
	fmt.Print("8 digit card ID: ")
	if _, err := fmt.Scan(&card); err != nil {
		log.Fatalln(err)
	}

	var commentIdNum int
	userId := getUserID(token)
	fmt.Println("\nHere is a list of comments on that card that you're assigned to.")
	comments := getTrelloComments(token, card, userId)
	for i, v := range comments {
		fmt.Printf("%d: %s\nLast Edited: %s\n=========\n%s\n\n",
			i+1, v.Id, v.Data.DateLastEdited, v.Data.Text)
	}
	fmt.Println("Please enter the number of the comment you wish to edit monthly.")
	fmt.Print("Card #: ")
	if _, err := fmt.Scan(&commentIdNum); err != nil {
		log.Fatalln(err)
	}
	if commentIdNum < 1 || commentIdNum > len(comments) {
		fmt.Println("Oops, you answered", commentIdNum)
		log.Fatalln("Try again...")
	}
	commentId := comments[commentIdNum-1].Id

	var CEOCorpKey string
	fmt.Println("\nSlmagic requires your corp's CEO's API credentials.  This is because CCP")
	fmt.Println("put the endpoint for people joining and quitting the corp in the CEO's")
	fmt.Println("API.  Specifically, we must use join and part notifications that the CEO")
	fmt.Println("receives.")
	fmt.Print("CEO's key: ")
	if _, err := fmt.Scan(&CEOCorpKey); err != nil {
		log.Fatalln(err)
	}

	var eveceovCode string
	fmt.Print("CEO's  vCode: ")
	if _, err := fmt.Scan(&eveceovCode); err != nil {
		log.Fatalln(err)
	}

	var corpKey string
	fmt.Println("\nSlmagic needs your corp's API credentials for ratting income.")
	fmt.Print("corp's key: ")
	if _, err := fmt.Scan(&corpKey); err != nil {
		log.Fatalln(err)
	}

	var corpvCode string
	fmt.Print("corp's vCode: ")
	if _, err := fmt.Scan(&corpvCode); err != nil {
		log.Fatalln(err)
	}

	var corpId string
	fmt.Println("Find your corp ID at: http://evemaps.dotlan.net/corp/<corp_name>")
	fmt.Print("Corp ID: ")
	if _, err := fmt.Scan(&corpId); err != nil {
		log.Fatalln(err)
	}

	var email string
	fmt.Println("\nType in your email address now")
	fmt.Print("email: ")
	if _, err := fmt.Scan(&email); err != nil {
		log.Fatalln(err)
	}

	var smtp string
	fmt.Println("\nThe email deal is new to the app and is experimental.  Feel free to just")
	fmt.Println("leave these fields blank, you can always return later and edit the conf")
	fmt.Println("file")
	fmt.Print("smtp server address (i.e. smtp.mail.com:587): ")
	if _, err := fmt.Scan(&smtp); err != nil {
		log.Fatalln(err)
	}

	var smtpuser string
	fmt.Print("smtp user: ")
	if _, err := fmt.Scan(&smtpuser); err != nil {
		log.Fatalln(err)
	}

	var smtppw string
	fmt.Print("smtp password: ")
	if _, err := fmt.Scan(&smtppw); err != nil {
		log.Fatalln(err)
	}

	replacer := strings.NewReplacer(
		"change me token", token,
		"change me id", userId,
		"change me commentId", commentId,
		"change me CEOCorpKey", CEOCorpKey,
		"change me eveceovCode", eveceovCode,
		"change me corpKey", corpKey,
		"change me corpvCode", corpvCode,
		"change me email", email,
		"change me smtp", smtp,
		"change me smtpuser", smtpuser,
		"change me smtppw", smtppw,
		"change me corpId", corpId)
	var perms os.FileMode = 0600
	err := ioutil.WriteFile("slmagic.toml", []byte(replacer.Replace(sample)), perms)
	checkErr("ioutil.WriteFile:", err)

	fmt.Println("\n\nIMPORTANT: Okay, the conf file is written, and now, for all the tacos,")
	fmt.Println("first, create a post.txt file in the directory with slmagic and put your")
	fmt.Println("corp new in that file.  Set up your cron jobs, and make sure you first")
	fmt.Println("'cd /home/user/<slmagic working directory>' to change your working directory")
	fmt.Println("so the app will work right.")
}

func getUserID(token string) string {
	UserIdURL := fmt.Sprintf("https://trello.com/1/members/me?key=%s&token=%s",
		appKey, token)
	var user TrelloUser
	res, err := http.Get(UserIdURL)
	body, err := ioutil.ReadAll(res.Body)
	checkErr("ioutil.ReadAll():", err)
	json.Unmarshal(body, &user)
	return user.Id
}

func getTrelloComments(token string, card string, userId string) TrelloComments {
	// TODO: test this function with someone who actually has a m
	CommentsURL := fmt.Sprintf("https://trello.com/1/cards/%s/actions?key=%s&token=%s",
		card, appKey, token)
	var comments TrelloComments
	var MyComments TrelloComments
	res, err := http.Get(CommentsURL)
	body, err := ioutil.ReadAll(res.Body)
	checkErr("getTrelloComments():", err)
	json.Unmarshal(body, &comments)
	for _, k := range comments {
		if k.IdMemberCreator == userId {
			MyComments = append(MyComments, k)
		}
	}
	return MyComments
}

func formatTime(t time.Time) string {
	return t.Format("2006-01-02 15:04:05")
}

func initDB() {
	// Creates the DB if it doesn't exist
	db, err := sql.Open("sqlite3", "./slmagic.db")
	if err != nil {
		log.Fatalf("Error creating database: %s\n", err)
	}
	defer db.Close()

	sql := `CREATE TABLE IF NOT EXISTS member_tracking (
notificationID integer not null primary key,
charID integer not null,
charName text not null,
notificationTypeID integer not null,
eventDate timestamp not null)`

	// Setup the table if it doesn't already exist
	_, err = db.Exec(sql)
	if err != nil {
		log.Fatalf("Error creating table: %s\n", err)
	}

	sql2 := `CREATE TABLE IF NOT EXISTS journal_tracking (
refID integer not null primary key,
refTypeID integer not null,
date timestamp not null,
amount text not null)`

	// Setup the table if it doesn't already exist
	_, err = db.Exec(sql2)
	if err != nil {
		log.Fatalf("Error creating table: %s\n", err)
	}
}

func storeEvents(data *Records) {
	// Open the connection to the db file
	db, err := sql.Open("sqlite3", "./slmagic.db")
	if err != nil {
		log.Fatalf("Error opening database: %s\n", err)
	}
	defer db.Close()

	// Loop through data and store it
	for _, v := range data.Events {
		stmt, err := db.Prepare(
			"INSERT OR REPLACE INTO member_tracking (notificationID, charID, charName, notificationTypeID, eventDate) VALUES (?,?,?,?,?);")
		if err != nil {
			log.Fatalf("Error preparing SQL SELECT: %s\n", err)
		}
		_, err = stmt.Exec(v.NotificationID, v.CharID, v.SenderName, v.NotificationTypeID, v.Date)
		if err != nil {
			log.Fatalf("Error inserting data: %s\n", err)
		}
	}
}

func storeEntries(data *Wrecords) {
	// Open the connection to the db file
	db, err := sql.Open("sqlite3", "./slmagic.db")
	if err != nil {
		log.Fatalf("Error opening database: %s\n", err)
	}
	defer db.Close()

	// Loop through the data and store it
	for _, v := range data.Jentries {
		stmt, err := db.Prepare(
			"INSERT OR REPLACE INTO journal_tracking (refID, refTypeID, date, amount) VALUES (?,?,?,?);")
		if err != nil {
			log.Fatalf("Error preparing SQL SELECT: %s\n", err)
		}
		_, err = stmt.Exec(v.RefID, v.RefTypeID, v.Date, v.Amount)
		if err != nil {
			log.Fatalf("Error inserting data: %s", err)
		}
	}
}

func tt(x int, s string) string {
	if x < 1000 {
		return fmt.Sprintf("%v%v", x, s)
	}
	return tt(x/1000, fmt.Sprintf(",%03v", x%1000)+s)
}

// App data structures
type GSFCorps struct {
	corpname, corpId string
}

type SendMail struct {
	smtp, user, pw, toAddress, subject, message string
	result                                      []byte
}

// XML data structures
type NotificationHeaders struct {
	Notifications []Notifications `xml:"result>rowset>row"`
}

type Notifications struct {
	NotificationID int64  `xml:"notificationID,attr"`
	TypeID         int    `xml:"typeID,attr"`
	SenderID       int64  `xml:"senderID,attr"`
	SenderName     string `xml:"senderName,attr"`
	SentDate       string `xml:"sentDate,attr"`
	Read           int64  `xml:"read,attr"`
}

type EntriesHeaders struct {
	Entries []Entries `xml:"result>rowset>row"`
}

type Entries struct {
	Date      string `xml:"date,attr"`
	RefID     int64  `xml:"refID,attr"`
	RefTypeID int    `xml:"refTypeID,attr"`
	Amount    string `xml:"amount,attr"`
}

type CorpSheet struct {
	TaxRate string `xml:"result>taxRate"`
}

// Database data structures
type Records struct {
	Events []InsertData
}

type InsertData struct {
	CharID             int64
	Date               time.Time
	NotificationID     int64
	NotificationTypeID int
	SenderName         string
}

type Wrecords struct {
	Jentries []EData
}

type EData struct {
	RefID     int64
	RefTypeID int
	Date      time.Time
	Amount    string
}

// Trello data structures
type TrelloUser struct {
	Username string `json:"username"`
	Email    string `json:"email"`
	Id       string `json:"id"`
}

type TrelloComments []TrelloComment

type TrelloComment struct {
	IdMemberCreator string `json:"idMemberCreator"`
	// TODO: test if I can make this field anonymous
	Data TrelloCommentData `json:"data"`
	Id   string            `json:"id"`
}

type TrelloCommentData struct {
	// TODO: Change this field to a true time type
	DateLastEdited string `json:"dateLastEdited"`
	Text           string `json:"Text"`
}

// Sample conf file
var sample string = `#toml configuration file

[application]
textFile = "post.txt"
email = "change me email"
smtp = "change me smtp"
smtpuser = "change me smtpuser"
smtppw = "change me smtppw"

[trello]
trelloId = "change me id"
appkey = "6fb1834f9b6ee0b21fe8a70b6f3455c7"
token = "change me token"
commentId = "change me commentId"

[eve]
ceoKey = "change me CEOCorpKey"
ceovCode = "change me eveceovCode"
corpKey = "change me corpKey"
corpvCode = "change me corpvCode"
corpId = "change me corpId"
`