// Global mapping should occur only once at startup:
// `experiments.mood` is the generated enum, and "experiments.mood" is the "{schema}.{enum}".
let dataSourceBuilder = NpgsqlDataSourceBuilder(DB.connectionString)
dataSourceBuilder.MapEnum<ext.mood>("ext.mood") |> ignore

// Global mapping should occur only once at startup:
// `experiments.mood` is the generated enum, and "experiments.mood" is the "{schema}.{enum}".
Npgsql.NpgsqlConnection.GlobalTypeMapper.MapEnum<experiments.mood>(nameof experiments.mood) |> ignore

// This code was generated by SqlHydra.SqlServer.
namespace SampleApp.AdventureWorks

module dbo =
    type ErrorLog =
        { ErrorLogID: int
          ErrorTime: System.DateTime
          UserName: string
          ErrorNumber: int
          ErrorMessage: string
          ErrorSeverity: Option<int>
          ErrorState: Option<int>
          ErrorProcedure: Option<string>
          ErrorLine: Option<int> }

    let ErrorLog = table<ErrorLog>

    type BuildVersion =
        { SystemInformationID: byte
          ``Database Version``: string
          VersionDate: System.DateTime
          ModifiedDate: System.DateTime }

    let BuildVersion = table<BuildVersion>

module SalesLT =
    type Address =
        { City: string
          StateProvince: string
          CountryRegion: string
          PostalCode: string
          rowguid: System.Guid
          ModifiedDate: System.DateTime
          AddressID: int
          AddressLine1: string
          AddressLine2: Option<string> }

    let Address = table<Address>

    type Customer =
        { LastName: string
          PasswordHash: string
          PasswordSalt: string
          rowguid: System.Guid
          ModifiedDate: System.DateTime
          CustomerID: int
          NameStyle: bool
          FirstName: string
          MiddleName: Option<string>
          Title: Option<string>
          Suffix: Option<string>
          CompanyName: Option<string>
          SalesPerson: Option<string>
          EmailAddress: Option<string>
          Phone: Option<string> }

    let Customer = table<Customer>
    
    // etc...

/// Opens a connection and creates a QueryContext that will generate SQL Server dialect queries
let openContext() = 
    let compiler = SqlKata.Compilers.SqlServerCompiler()
    let conn = new SqlConnection("Replace with your connection string")
    conn.Open()
    new QueryContext(conn, compiler)

    let ctx = new QueryContext(conn, compiler)
    #if DEBUG
    ctx.Logger <- printfn "SQL: %O"
    #endif

let getErrorNumbers () =
    selectTask HydraReader.Read openContext {
        for e in dbo.ErrorLog do
        select e.ErrorNumber
    }

let openContext() = 
    let compiler = SqlKata.Compilers.SqlServerCompiler()
    let conn = new SqlConnection("Replace with your connection string")
    conn.Open()
    new QueryContext(conn, compiler)

let getErrorNumbers () =
    selectAsync HydraReader.Read openContext {
        for e in dbo.ErrorLog do
        select e.ErrorNumber
    }

let getUserOrders email =
    task {
        use ctx = openContext ()

        let! userInfo = 
            selectTask HydraReader.Read ctx {
                for user in dbo.Users do
                where (user.Email = email)
            }

        let! orderHeaders = 
            selectTask HydraReader.Read ctx {
                for o in dbo.OrderHeaders do
                where (o.CustomerEmail = email)
            }

        return { User = userInfo; Orders = orderHeaders }
    }

let selectTask' ct = selectTask HydraReader.Read ct

// Usage:
let! distinctCustomerNames = 
    selectTask' openContext {
        for c in SalesLT.Customer do
        select (c.FirstName, c.LastName)
        distinct
    }

let getCities (cityFilter: string) = 
    selectTask HydraReader.Read openContext {
        for a in SalesLT.Address do                             // Specifies a FROM table in the query
        where (a.City = cityFilter)                             // Specifies a WHERE clause in the query
        select (a.City, a.StateProvince) into selected          // Specifies which entities and/or columns to SELECT in the query
        mapList (                                               // Transforms the query results
            let city, state = selected
            $"City, State: %s{city}, %s{state}"
        )
    }

let getAddressesInCitiesStartingWithS () = 
        selectAsync HydraReader.Read openContext {
            for a in SalesLT.Address do
            where (a.City =% "S%")
        }

let tryGetOrderTotal (orderId: int) = 
        selectAsync HydraReader.Read openContext {
            for o in SalesLT.Order do
            where (o.Id = orderId)
            select o.Total
            tryHead
        }

let getProductsWithCategory () = 
    selectTask HydraReader.Read openContext {
        for p in SalesLT.Product do
        join c in categoryTable on (p.ProductCategoryID.Value = c.ProductCategoryID)
        select (p, c.Name)
        take 10
    }

let getCustomerAddressesInIds (customerIds: int list) =
    selectAsync HydraReader.Read openContext) {
        for c in SalesLT.Customer do
        leftJoin ca in SalesLT.CustomerAddress on (c.CustomerID = ca.Value.CustomerID)
        leftJoin a  in SalesLT.Address on (ca.Value.AddressID = a.Value.AddressID)
        where (c.CustomerID |=| customerIds)
        orderBy c.CustomerID
        select (c, a)
    }

let getCustomerZipCodes (customerId: int) =
    selectAsync HydraReader.Read openContext {
        for c in SalesLT.Customer do
        leftJoin ca in SalesLT.CustomerAddress on (c.CustomerID = ca.Value.CustomerID)
        leftJoin a  in SalesLT.Address on (ca.Value.AddressID = a.Value.AddressID)
        where (c.CustomerID = customerId)
        orderBy c.CustomerID
        select (c, Some a.Value.ZipCode)
    }

select {
    for o in SalesLT.OrderHeaders do
    join d in SalesLT.OrderDetails on ((o.SalesOrderID, o.ModifiedDate) = (d.SalesOrderID, d.ModifiedDate))
    select (o, d)
}

    let! lineTotals =
        selectTask HydraReader.Read openContext {
            for o in SalesLT.OrderHeaders do
            join d in SalesLT.OrderDetails on (o.SalesOrderID = d.SalesOrderID)
            where (o.OnlineOrderFlag = true)
            mapList (
                {| 
                    ShipDate = 
                        match o.ShipDate with
                        | Some d -> d.ToShortDateString()
                        | None -> "No Order Number"
                    LineTotal = (decimal qty) * unitPrice
                |}
            )
        }

    let! lineTotals =
        selectTask HydraReader.Read openContext {
            for o in SalesLT.OrderHeaders do
            join d in SalesLT.OrderDetails on (o.SalesOrderID = d.SalesOrderID)
            where (o.OnlineOrderFlag = true)
            select (o, d.OrderQty, d.UnitPrice) into selected  // project selected values so they can be mapped
            mapList (
                let o, qty, unitPrice = selected               // unpack the selected values for use in transform
                {| 
                    ShipDate = 
                        match o.ShipDate with
                        | Some d -> d.ToShortDateString()
                        | None -> "No Order Number"
                    LineTotal = (decimal qty) * unitPrice
                |}
            )
        }

        selectAsync HydraReader.Read openContext {
            for o in SalesLT.Order do
            where (o.Id = orderId)
            select o.Total
            mapSeq {| GrandTotal = o.Total |}
            tryHead
        }

/// Select categories with an avg product price > 500 and < 1000
let getCategoriesWithHighAvgPrice () = 
    selectTask HydraReader.Read openContext {
        for p in SalesLT.Product do
        where (p.ProductCategoryID <> None)
        groupBy p.ProductCategoryID
        having (minBy p.ListPrice > 500M && maxBy p.ListPrice < 1000M)
        select (p.ProductCategoryID, minBy p.ListPrice, maxBy p.ListPrice) into selected
        mapList (
            let catId, minPrice, maxPrice = selected
            $"CatID: {catId}, MinPrice: {minPrice}, MaxPrice: {maxPrice}"
        )
    }

let! customersWithNoSalesPersonCount =
    selectTask HydraReader.Read openContext {
        for c in SalesLT.Customer do
        where (c.SalesPerson = None)
        count
    }

/// Select the minimum item price above a threshold
let getNextLowestPrice threshold = 
    selectTask HydraReader.Read openContext {
        for p in SalesLT.Product do
        where (p.ListPrice > threshold)
        select (minBy p.ListPrice)
    }

/// Select the minimum item price above a threshold
let getNextLowestPrice threshold = 
    selectTask HydraReader.Read openContext {
        for p in SalesLT.Product do
        where (p.ListPrice > threshold)
        select (minBy (Some p.ListPrice))
    }

// Create a subquery that gets top 5 avg prices by category ID:
let top5CategoryIdsWithHighestAvgPrices = 
    select {
        for p in SalesLT.Product do
        where (p.ProductCategoryID <> None)
        groupBy p.ProductCategoryID
        orderByDescending (avgBy p.ListPrice)
        select p.ProductCategoryID
        take 5
    }

// Get category names where the category ID is "IN" the subquery:
let! top5Categories =
    selectTask HydraReader.Read openContext {
        for c in SalesLT.ProductCategory do
        where (Some c.ProductCategoryID |=| subqueryMany top5CategoryIdsWithHighestAvgPrices)
        select c.Name
    }

// Create a subquery that gets the avg list price (a single value):
let avgListPrice = 
    select {
        for p in SalesLT.Product do
        select (avgBy p.ListPrice)
    } 

// Get products with a price > the average price
let! productsWithAboveAveragePrice =
    selectTask HydraReader.Read openContext {
        for p in SalesLT.Product do
        where (p.ListPrice > subqueryOne avgListPrice)
        select (p.Name, p.ListPrice)
    }

// Create a subquery that gets the min price for this product line,
// referencing a row variable "outer" from the parent query:
let lowestPriceByProductLine = 
    select {
        for inner in Production.Product do
        correlate outer in Production.Product
        where (inner.ProductLine = outer.ProductLine)
        select (minBy inner.ListPrice)
    }

// Get the products whose price is the lowest of all prices in its product line.
// The name "outer" needs to match the subquery.
let! cheapestByProductLine = 
    selectTask HydraReader.Read openContext {
        for outer in Production.Product do
        where (outer.ListPrice = subqueryOne lowestPriceByProductLine)
        select (outer.Name, outer.ListPrice)
    }

let! distinctCustomerNames = 
    selectTask HydraReader.Read openContext {
        for c in SalesLT.Customer do
        select (c.FirstName, c.LastName)
        distinct
    }

let getCities () =
    let city = getCity() // DO prepare where parameters above and then pass into the where clause
    selectTask HydraReader.Read openContext {
        for a in SalesLT.Address do
        where (a.City = city)
        select (a.City, a.StateProvince) into (city, state)
        mapList $"City: %s{city}, State: %s{state}"   // DO transforms using the `mapSeq`, `mapArray` or `mapList` operations
    }

let getCities () =
    selectTask HydraReader.Read openContext {
        for a in SalesLT.Address do
        where (a.City = getCity()) // DO NOT perform calculations or translations within the builder
        select ($"City: %s{city}, State: %s{state}")   // DO NOT transform results within the builder 
    }

let! rowsInserted = 
    insertTask openContext {
        into Person.Person
        entity 
            {
                dbo.Person.ID = Guid.NewGuid()
                dbo.Person.FirstName = "Bojack"
                dbo.Person.LastName = "Horseman"
                dbo.Person.LastUpdated = DateTime.Now
            }
    }

printfn "Rows inserted: %i" rowsInserted

let! errorLogID =
    insertTask openContext {
        for e in dbo.ErrorLog do
        entity 
            {
                dbo.ErrorLog.ErrorLogID = 0 // Adding `getId` below will ignore this value.
                dbo.ErrorLog.ErrorTime = System.DateTime.Now
                dbo.ErrorLog.ErrorLine = None
                dbo.ErrorLog.ErrorMessage = "TEST"
                dbo.ErrorLog.ErrorNumber = 400
                dbo.ErrorLog.ErrorProcedure = (Some "Procedure 400")
                dbo.ErrorLog.ErrorSeverity = None
                dbo.ErrorLog.ErrorState = None
                dbo.ErrorLog.UserName = "jmarr"
            }
        getId e.ErrorLogID
    }

printfn "ErrorLogID Identity: %i" errorLogID

let currenciesMaybe = 
    [ 0..2 ] 
    |> List.map (fun i -> 
        {
            Sales.Currency.CurrencyCode = $"BC{i}"
            Sales.Currency.Name = "BitCoin"
            Sales.Currency.ModifiedDate = System.DateTime.Now
        }
    )
    |> AtLeastOne.tryCreate

match currenciesMaybe with
| Some currencies ->
    do! insertTask openContext {
            into Sales.Currency
            entities currencies
        } :> Task // upcast to Task if you want to ignore the resulting value
| None ->
    printfn "Skipping insert because entities seq was empty."

    /// Inserts an address or updates it if it already exists.
    let upsertAddress address = 
        insertTask openContext {
            for a in Person.Address do
            entity address
            onConflictDoUpdate a.AddressID (
                a.AddressLine1,
                a.AddressLine2,
                a.City,
                a.StateProvince,
                a.CountryRegion,
                a.PostalCode,
                a.ModifiedDate
            )
        }

    /// Inserts multiple addresses or updates them if they already exist.
    let upsertAddress addresses =
        match addresses |> AtLeastOne.tryCreate with
        | Some addresses -> 
            insertTask openContext {
                for a in Person.Address do
                entities addresses
                onConflictDoUpdate a.AddressID (
                    a.AddressLine1,
                    a.AddressLine2,
                    a.City,
                    a.StateProvince,
                    a.CountryRegion,
                    a.PostalCode,
                    a.ModifiedDate
                )
            }
        | None ->
            printfn "No addresses to insert."
            0

    /// Tries to insert an address if it doesn't already exist.
    let tryInsertAddress address = 
        insertTask openContext {
            for a in Person.Address do
            entity address
            onConflictDoNothing a.AddressID
        }

do! updateAsync openContext {
        for e in dbo.ErrorLog do
        set e.ErrorNumber 123
        set e.ErrorMessage "ERROR #123"
        set e.ErrorLine (Some 999)
        set e.ErrorProcedure None
        where (e.ErrorLogID = 1)
    } :> Task // upcast to Task if you want to ignore the resulting value

let! rowsUpdated = 
    updateTask openContext {
        for e in dbo.ErrorLog do
        entity 
            {
                dbo.ErrorLog.ErrorLogID = 0 // Add `excludeColumn` below to ignore an identity column
                dbo.ErrorLog.ErrorTime = System.DateTime.Now
                dbo.ErrorLog.ErrorLine = None
                dbo.ErrorLog.ErrorMessage = "TEST"
                dbo.ErrorLog.ErrorNumber = 400
                dbo.ErrorLog.ErrorProcedure = (Some "Procedure 400")
                dbo.ErrorLog.ErrorSeverity = None
                dbo.ErrorLog.ErrorState = None
                dbo.ErrorLog.UserName = "jmarr"
            }
        excludeColumn e.ErrorLogID // Exclude the identity column
        where (e.ErrorLogID = errorLog.ErrorLogID)
    }

update {
    for c in Sales.Customer do
    set c.AccountNumber "123"
    updateAll
}

do! deleteTask openContext {
        for e in dbo.ErrorLog do
        where (e.ErrorLogID = 5)
    } :> Task // upcast to Task if you want to ignore the resulting value

let! rowsDeleted = 
    deleteTask openContext {
        for c in Sales.Customer do
        deleteAll
    }
    
printfn "Rows deleted: %i" rowsDeleted

let getCustomers filters = 
  select {
      for c in main.Customer do
      where (c.FirstName = "John")
      kata (fun query -> 
          match filters.LastName with
          | Some lastName -> query.Where("c.LastName", lastName)
          | None -> query
      )
      kata (fun query -> 
          query.OrderBy(filters.SortColumns)
      )
  }

let getTop10Products(conn: SqlConnection) = task {
    let sql = $"SELECT TOP 10 * FROM {nameof dbo.Product} p"
    use cmd = new SqlCommand(sql, conn)
    use! reader = cmd.ExecuteReaderAsync()
    let hydra = HydraReader(reader)

    return [
        while reader.Read() do
            hydra.``dbo.Product``.Read()
    ]
}