package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"os"

	_ "github.com/alexbrainman/odbc"
)

type ContactsTable struct {
	Abbreviation             string `json:"Abbreviation,omitempty"`
	Active                   bool   `json:":Active"`
	CompanyName              string `json:"Company_Name,omitempty"`
	Contractor               bool   `json:"Contractor?#"`
	CreationTimestamp        string `json:"@Creation_Timestamp"`
	DocumentFormatPreference string `json:"Document_Format_Preference"`
	EOExpirationDate         string `json:"EO_Expiration_Date,omitempty"`
	EntityType               string `json:"Entity_Type"`
	Fein                     string `json:"FEIN,omitempty"`
	ForeclosureContractor    bool   `json:"Foreclosure_Contractor?#"`
	ID                       string `json:"__ID"`
	ModificationTimestamp    string `json:"@Modification_Timestamp"`
	Notes                    string `json:"Notes,omitempty"`
	PersonBirthDate          string `json:"Person_Birth_Date,omitempty"`
	PersonGender             string `json:"Person_Gender,omitempty"`
	PersonNameFirst          string `json:"Person_Name_First,omitempty"`
	PersonNameLast           string `json:"Person_Name_Last,omitempty"`
	PersonNameMiddle         string `json:"Person_Name_Middle,omitempty"`
	PersonNameSuffix         string `json:"Person_Name_Suffix,omitempty"`
	SearchRequirements       string `json:"Search_Requirements,omitempty"`
	SocialSecurityNumber     string `json:"Social_Security_Number,omitempty"`
	WebsiteActive            bool   `json:"website_active"`
	WebsitePassword          string `json:"website_password,omitempty"`
}

const configFileName = ".scrapeconfig"

func readScrapeDate(field string) string {
	return "02-01-2013 16:24:16"
}

func writeScrapeDate(field string, date string) {
	// write to a file with a field and set it
}

func main() {

	for i := 1; i < 10; i++ {
		scrapeContactsTable()
	}
}

func scrapeContactsTable() {

	var Abbreviation sql.NullString
	var Active sql.NullFloat64
	var CompanyName sql.NullString
	var Contractor sql.NullFloat64
	var CreationTimestamp sql.NullString
	var DocumentFormatPreference sql.NullString
	var EOExpirationDate sql.NullString
	var EntityType sql.NullString
	var Fein sql.NullString
	var ForeclosureContractor sql.NullFloat64
	var ID sql.NullString
	var ModificationTimestamp sql.NullString
	var Notes sql.NullString
	var PersonBirthDate sql.NullString
	var PersonGender sql.NullString
	var PersonNameFirst sql.NullString
	var PersonNameLast sql.NullString
	var PersonNameMiddle sql.NullString
	var PersonNameSuffix sql.NullString
	var SearchRequirements sql.NullString
	var SocialSecurityNumber sql.NullString
	var WebsiteActive sql.NullFloat64
	var WebsitePassword sql.NullString

	db, err := sql.Open("odbc", "DSN=fmdb;Uid=admin;Pwd=admin")
	if err != nil {
		fmt.Println("ODBC Connection to file maker failed :( ", err)
	} else {
		fmt.Println("Connected to ODBC; Yea!")
	}
	defer db.Close()

	err = db.Ping()
	if err != nil {
		fmt.Println("Cannot ping ODBC DB :(")
		return
	}

	f, err := os.Create("contacts_table.tsv")
	if err != nil {
		log.Fatal(err)
	}
	defer f.Close()

	toPostArray := []ContactsTable{}
	singlePost := ContactsTable{}

	modtimestamp := readScrapeDate("contacts_table_last_read")
	offset := 0
	fetchonetime := 100
	dataAvailable := true
	rowsFetched := 0

	for dataAvailable == true {
		//time.Sleep(1 * time.Second)    // hack to see if odbc error is timing related

		q := fmt.Sprintf(`select "Abbreviation", ":Active", "Company_Name", "Contractor?#", "@Creation_Timestamp", "Document_Format_Preference", "EO_Expiration_Date", "Entity_Type", "FEIN", "Foreclosure_Contractor?#", "__ID", "@Modification_Timestamp", "Notes", "Person_Birth_Date", "Person_Gender", "Person_Name_First", "Person_Name_Last", "Person_Name_Middle", "Person_Name_Suffix", "Search_Requirements", "Social_Security_Number", "website_active", "website_password" from CON__Contacts where "@Modification_Timestamp" > '%s' offset %d rows fetch first %d rows only`, modtimestamp, offset, fetchonetime)
		fmt.Println(q)
		rows, err := db.Query(q)
		if err != nil {
			fmt.Println("Unable to get data - cannot run select Query :( ", err)
			return
		}
		defer rows.Close()
		if db == nil {
			fmt.Println("db is NIL - fatal")
			return
		}
		fetchedsoFar := rowsFetched

		for rows.Next() {
			err := rows.Scan(&Abbreviation, &Active, &CompanyName, &Contractor, &CreationTimestamp, &DocumentFormatPreference, &EOExpirationDate, &EntityType, &Fein, &ForeclosureContractor, &ID, &ModificationTimestamp, &Notes, &PersonBirthDate, &PersonGender, &PersonNameFirst, &PersonNameLast, &PersonNameMiddle, &PersonNameSuffix, &SearchRequirements, &SocialSecurityNumber, &WebsiteActive, &WebsitePassword)
			if err != nil {
				fmt.Println("Error when scanning rows into vars..  :( ", err)
				return
			}

			singlePost.Abbreviation = stringNulltoJson(Abbreviation)
			singlePost.Active = floatNulltoBoolJson(Active)
			singlePost.CompanyName = stringNulltoJson(CompanyName)
			singlePost.Contractor = floatNulltoBoolJson(Contractor)
			singlePost.CreationTimestamp = stringNulltoJson(CreationTimestamp)
			singlePost.DocumentFormatPreference = stringNulltoJson(DocumentFormatPreference)
			singlePost.EOExpirationDate = stringNulltoJson(EOExpirationDate)
			singlePost.EntityType = stringNulltoJson(EntityType)
			singlePost.Fein = stringNulltoJson(Fein)
			singlePost.ForeclosureContractor = floatNulltoBoolJson(ForeclosureContractor)
			singlePost.ID = stringNulltoJson(ID)
			singlePost.ModificationTimestamp = stringNulltoJson(ModificationTimestamp)
			singlePost.Notes = stringNulltoJson(Notes)
			singlePost.PersonBirthDate = stringNulltoJson(PersonBirthDate)
			singlePost.PersonGender = stringNulltoJson(PersonGender)
			singlePost.PersonNameFirst = stringNulltoJson(PersonNameFirst)
			singlePost.PersonNameLast = stringNulltoJson(PersonNameLast)
			singlePost.PersonNameMiddle = stringNulltoJson(PersonNameMiddle)
			singlePost.PersonNameSuffix = stringNulltoJson(PersonNameSuffix)
			singlePost.SearchRequirements = stringNulltoJson(SearchRequirements)
			singlePost.SocialSecurityNumber = stringNulltoJson(SocialSecurityNumber)
			singlePost.WebsiteActive = floatNulltoBoolJson(WebsiteActive)
			singlePost.WebsitePassword = stringNulltoJson(WebsitePassword)

			toPostArray = append(toPostArray, singlePost)
			rowsFetched += 1
			fmt.Println("fetched = ", rowsFetched)
		}
		if err = rows.Err(); err != nil {
			fmt.Println("error in Next()", err)
			return
		}
		if fetchedsoFar == rowsFetched {
			dataAvailable = false
		}
		offset += fetchonetime
		rows.Close()

	}
	db.Close()
}

func PrettyPrint(v interface{}) (err error) {
	b, err := json.MarshalIndent(v, "", "  ")
	if err == nil {
		fmt.Println(string(b))
	}
	return
}

type toPost struct {
	Data        []ContactsTable
	CurrentTime string
}

func stringNulltoJson(a sql.NullString) string {
	if a.Valid {
		return a.String
	}
	return ""
}

func floatNulltoBoolJson(a sql.NullFloat64) bool {
	if a.Valid {
		if a.Float64 == 1.00 {
			return true
		} else {
			return false
		}
	}
	return false
}

func floatNulltoFloatJson(a sql.NullFloat64) float64 {
	if a.Valid {
		return a.Float64
	}
	return 0.0
}