namespace SqlController

open System
open System.Data
open System.Data.Linq
open Microsoft.FSharp.Data.TypeProviders
open Microsoft.FSharp.Linq

open Entities

type dbSchema = SqlDataConnection<"Data Source=VM-WIN8\SQLEXPRESS;Initial Catalog=mobile;Integrated Security=SSPI;">

type SqlConnector() =

    // universal sql functions
    member private this.DeleteRowsFrom (table : Table<_>) rows =
        table.DeleteAllOnSubmit(rows)

    // map functions
    member private this.Item2Record (item : Item) =
        new dbSchema.ServiceTypes.ItemsTable(
            ItemId = item.ItemId,
            ItemName = item.ItemName)

    member private this.Record2Item (e : dbSchema.ServiceTypes.ItemsTable) =
        new Item(e.ItemId, e.ItemName)

    // ### Items

    // return all items
    member this.GetItems() =
        use db = dbSchema.GetDataContext()

        query {
            for row in db.ItemsTable do
            select row
        } |> Seq.map this.Record2Item |> Seq.toArray


    // return one item by item id
    member this.GetItemByItemId(itemId : string) =
        use db = dbSchema.GetDataContext()

        query {
            for rows in db.ItemsTable do
            where (rows.ItemId = itemId)
            select rows
        } |> Seq.nth 0 |> this.Record2Item

    // insert new item
    member this.InsertItem (item : Item) =
        use db = dbSchema.GetDataContext()
        item |> this.Item2Record |> db.ItemsTable.InsertOnSubmit

        try
            db.DataContext.SubmitChanges()
        with
            | exn -> printfn "Exception: \n%s" exn.Message

    // update item
    member this.UpdateItem (item : Item) =
        use db = dbSchema.GetDataContext()

        query {
            for rows in db.ItemsTable do
            where (rows.ItemId = item.ItemId)
            select rows
        }
        |> Seq.nth 0
        |> (fun e -> e.ItemName <- item.ItemName)

        try
            db.DataContext.SubmitChanges()
        with
            | exn -> printfn "Exception: \n%s" exn.Message

    // delete item by itemId
    member this.DeleteItemByItemId(itemId : string) =
        use db = dbSchema.GetDataContext()

        query {
            for rows in db.ItemsTable do
            where (rows.ItemId = itemId)
            select rows
        } |> this.DeleteRowsFrom db.ItemsTable

        try
            db.DataContext.SubmitChanges()
        with
            | exn -> printfn "Exception: \n%s" exn.Message