open System
open System.Transactions
open Soma.Core

// define a module wraps Soma.Core.Db module
module MyDb = 
  let config = 
    { new MsSqlConfig() with
      member this.ConnectionString = "Data Source=.;Initial Catalog=Soma.Tutorial;Integrated Security=True" }
  let query<'T> = Db.query<'T> config
  let queryOnDemand<'T> = Db.queryOnDemand<'T> config
  let execute sql expr = Db.execute config sql expr
  let find<'T when 'T : not struct> = Db.find<'T> config
  let tryFind<'T when 'T : not struct> = Db.tryFind<'T> config
  let insert<'T when 'T : not struct> = Db.insert<'T> config
  let update<'T when 'T : not struct> = Db.update<'T> config
  let delete<'T when 'T : not struct> = Db.delete<'T> config
  let call<'T when 'T : not struct> = Db.call<'T> config

// define a record mapped to a table 
type Employee = 
  { [<Id(IdKind.Identity)>]
    EmployeeId : int 
    EmployeeName : string
    DepartmentId : int
    [<Version>]
    VersionNo : int }

// define a record mapped to a procedure
type ProcResultAndOut = 
  { EmployeeId : int
    [<ProcedureParam(Direction = Direction.Output)>]
    EmployeeCount : int
    [<ProcedureParam(Direction = Direction.Result)>]
    Employees : Employee list }

let main =
  // execute following code in a transaction, but don't commit
  use tx = new TransactionScope()
  
  // find by id
  let emp = MyDb.find<Employee> [1]
  printfn "FOUND RECORD : \n%A\n" emp

  // update
  let emp = MyDb.update { emp with EmployeeName = "Hoge" }
  printfn "UPDATED RECORD : \n%A\n" emp

  // delete
  MyDb.delete emp
  printfn "DELETED RECORD : \n%A\n" emp

  // insert
  let emp = MyDb.insert { EmployeeId = 0; EmployeeName = "Allen"; DepartmentId = 2; VersionNo = 0}
  printfn "INSERTED RECORD : \n%A\n" emp

  // query by SQL. parameters are bindable with "Code Quotations". 
  let empList = 
    MyDb.query<string * Employee> "
    select 
      d.DepartmentName, e.EmployeeId, e.EmployeeName, e.DepartmentId, e.VersionNo   
    from 
      Employee e 
    inner join 
      Department d 
    on 
      e.DepartmentId = d.DepartmentId 
    where 
      e.DepartmentId = /* emp.DepartmentId */0 
    " <@ let emp = emp in () @>
  printfn "QUERIED TUPLES : \n%A\n" empList

  // call procedure
  let result = MyDb.call<ProcResultAndOut> { EmployeeId = 1; EmployeeCount = 0; Employees = [] }
  printfn "PROCEDURE OUTPUT : \n%A\n" result

  // exequte arbitrary SQL
  let rows = 
    MyDb.execute "
    delete from Employee 
    " <@ () @>
  printfn "AFFECTED ROWS : \n%A\n" rows

  Console.ReadKey()