open System
open System.Data.Odbc
open System.Windows.Forms


let connectToAccess filename =
    let connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;FileDSN=" + filename + ";User Id=admin;Password=;"
    new OdbcConnection(connectionString)

let connectToDb() =
    let userProfileDir = Environment.GetEnvironmentVariable("UserProfile")
    let accessFile = userProfileDir + @"\Documents\FortuneCookie.accdb"
    connectToAccess accessFile

let pasteStringToClipboard string =
    Clipboard.SetText string

let buildQuoteString quote author =
    sprintf "%s\n -- %s" quote author


let getRandomQuote()=
    let connection = connectToDb()
    let tableName = "FortuneCookieQuotes"
    //Note: the order by clause has to be in the order it is or you get the same quote repeatedly
    let randomQuoteSQL = "SELECT TOP 1 ID,FortuneCookieText,CreditedTo FROM " + tableName + " ORDER BY LastUsed ASC, RND(ID)"
    let getQuoteCommand = new OdbcCommand(randomQuoteSQL,connection)
    connection.Open()
    let result = getQuoteCommand.ExecuteReader()
    let ableToReadResult = result.Read()
    let s = buildQuoteString (result.["FortuneCookieText"] :?> string) (result.["CreditedTo"] :?> string)
    let quoteID = result.["ID"] :?> int
    let updateQuoteSQL = "UPDATE " + tableName + " SET LastUsed = '" + DateTime.Now.ToString("d") + "' WHERE ID = " + quoteID.ToString()
    let updateResultCommand = new OdbcCommand(updateQuoteSQL,connection)
    let updateResult = updateResultCommand.ExecuteNonQuery()
    connection.Close()
    s
[<STAThread>] 
let main (argv:string[]) =
    let quote = getRandomQuote()
    pasteStringToClipboard quote
    0

main fsi.CommandLineArgs;;