{-# LANGUAGE AllowAmbiguousTypes #-}
{-# LANGUAGE LambdaCase          #-}

module Database.GP.SqlGenerator
  ( insertStmtFor,
    insertReturningStmtFor,
    updateStmtFor,
    selectFromStmt,
    deleteStmtFor,
    createTableStmtFor,
    dropTableStmtFor,
    columnTypeFor,
    WhereClauseExpr,
    Field,
    field,
    whereClauseValues,
    (&&.),
    (||.),
    (=.),
    (>.),
    (<.),
    (>=.),
    (<=.),
    (<>.),
    like,
    between,
    in',
    isNull,
    not',
    sqlFun,
    allEntries,
    byId,
    byIdColumn,
    orderBy,
    SortOrder (..),
    limit,
    limitOffset,
    NonEmpty(..),
    Database (..),
  )
where

import           Data.List          (intercalate)
import Database.GP.Entity
import Database.GP.Query

-- |
--  This module defines some basic SQL statements for Record Data Types that are instances of 'Entity'.
--  The SQL statements are generated using Haskell generics to provide compile time reflection capabilities.

-- | A function that returns an SQL insert statement for an entity. Type 'a' must be an instance of Data.
-- The function will use the field names of the data type to generate the column names in the insert statement.
-- The values of the fields will be used as the values in the insert statement.
-- Output example: INSERT INTO Person (id, name, age, address) VALUES (123456, "Alice", 25, "123 Main St");
insertStmtFor :: forall a. Entity a => String
insertStmtFor :: forall a. Entity a => String
insertStmtFor =
  String
"INSERT INTO "
    forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    forall a. [a] -> [a] -> [a]
++ String
" ("
    forall a. [a] -> [a] -> [a]
++ forall a. [a] -> [[a]] -> [a]
intercalate String
", " [String]
insertColumns
    forall a. [a] -> [a] -> [a]
++ String
") VALUES ("
    forall a. [a] -> [a] -> [a]
++ forall a. [a] -> [[a]] -> [a]
intercalate String
", " (Int -> [String]
params (forall (t :: * -> *) a. Foldable t => t a -> Int
length [String]
insertColumns))
    forall a. [a] -> [a] -> [a]
++ String
");"
  where
    columns :: [String]
columns = forall a. Entity a => [String]
columnNamesFor @a
    insertColumns :: [String]
insertColumns = if forall a. Entity a => Bool
autoIncrement @a 
                  then forall a. (a -> Bool) -> [a] -> [a]
filter (forall a. Eq a => a -> a -> Bool
/= forall a. Entity a => String
idColumn @a) [String]
columns 
                  else [String]
columns

insertReturningStmtFor :: forall a. Entity a => String
insertReturningStmtFor :: forall a. Entity a => String
insertReturningStmtFor =
  String
"INSERT INTO "
    forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    forall a. [a] -> [a] -> [a]
++ String
" ("
    forall a. [a] -> [a] -> [a]
++ forall a. [a] -> [[a]] -> [a]
intercalate String
", " [String]
insertColumns
    forall a. [a] -> [a] -> [a]
++ String
") VALUES ("
    forall a. [a] -> [a] -> [a]
++ forall a. [a] -> [[a]] -> [a]
intercalate String
", " (Int -> [String]
params (forall (t :: * -> *) a. Foldable t => t a -> Int
length [String]
insertColumns))
    forall a. [a] -> [a] -> [a]
++ String
") RETURNING "
    forall a. [a] -> [a] -> [a]
++ String
returnColumns
    forall a. [a] -> [a] -> [a]
++ String
";"
  where
    columns :: [String]
columns = forall a. Entity a => [String]
columnNamesFor @a  
    insertColumns :: [String]
insertColumns = if forall a. Entity a => Bool
autoIncrement @a 
                      then forall a. (a -> Bool) -> [a] -> [a]
filter (forall a. Eq a => a -> a -> Bool
/= forall a. Entity a => String
idColumn @a) [String]
columns 
                      else [String]
columns
    returnColumns :: String
returnColumns = forall a. [a] -> [[a]] -> [a]
intercalate String
", " [String]
columns


columnNamesFor :: forall a. Entity a => [String]
columnNamesFor :: forall a. Entity a => [String]
columnNamesFor = forall a b. (a -> b) -> [a] -> [b]
map forall a b. (a, b) -> b
snd [(String, String)]
fieldColumnPairs
  where
    fieldColumnPairs :: [(String, String)]
fieldColumnPairs = forall a. Entity a => [(String, String)]
fieldsToColumns @a

-- | A function that returns an SQL update statement for an entity. Type 'a' must be an instance of Entity.
updateStmtFor :: forall a. (Entity a) => String
updateStmtFor :: forall a. Entity a => String
updateStmtFor =
  String
"UPDATE "
    forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    forall a. [a] -> [a] -> [a]
++ String
" SET "
    forall a. [a] -> [a] -> [a]
++ forall a. [a] -> [[a]] -> [a]
intercalate String
", " [String]
updatePairs
    forall a. [a] -> [a] -> [a]
++ String
" WHERE "
    forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
idColumn @a
    forall a. [a] -> [a] -> [a]
++ String
" = ?"
    forall a. [a] -> [a] -> [a]
++ String
";"
  where
    updatePairs :: [String]
updatePairs = forall a b. (a -> b) -> [a] -> [b]
map (forall a. [a] -> [a] -> [a]
++ String
" = ?") (forall a. Entity a => [String]
columnNamesFor @a)

-- | A function that returns an SQL select statement for an entity. Type 'a' must be an instance of Entity.
--   The function takes a where clause expression as parameter. This expression is used to filter the result set.
selectFromStmt :: forall a. (Entity a) => WhereClauseExpr -> String
selectFromStmt :: forall a. Entity a => WhereClauseExpr -> String
selectFromStmt WhereClauseExpr
whereClauseExpr =
  String
"SELECT "
    forall a. [a] -> [a] -> [a]
++ forall a. [a] -> [[a]] -> [a]
intercalate String
", " (forall a. Entity a => [String]
columnNamesFor @a)
    forall a. [a] -> [a] -> [a]
++ String
" FROM "
    forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    forall a. [a] -> [a] -> [a]
++ String
" WHERE "
    forall a. [a] -> [a] -> [a]
++ forall a. Entity a => WhereClauseExpr -> String
whereClauseExprToSql @a WhereClauseExpr
whereClauseExpr
    forall a. [a] -> [a] -> [a]
++ String
";"

deleteStmtFor :: forall a. (Entity a) => String
deleteStmtFor :: forall a. Entity a => String
deleteStmtFor =
  String
"DELETE FROM "
    forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    forall a. [a] -> [a] -> [a]
++ String
" WHERE "
    forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
idColumn @a
    forall a. [a] -> [a] -> [a]
++ String
" = ?;"

-- | An enumeration of the supported database types.
data Database = Postgres | SQLite -- | Oracle | MSSQL | MySQL
  deriving (Int -> Database -> String -> String
[Database] -> String -> String
Database -> String
forall a.
(Int -> a -> String -> String)
-> (a -> String) -> ([a] -> String -> String) -> Show a
showList :: [Database] -> String -> String
$cshowList :: [Database] -> String -> String
show :: Database -> String
$cshow :: Database -> String
showsPrec :: Int -> Database -> String -> String
$cshowsPrec :: Int -> Database -> String -> String
Show, Database -> Database -> Bool
forall a. (a -> a -> Bool) -> (a -> a -> Bool) -> Eq a
/= :: Database -> Database -> Bool
$c/= :: Database -> Database -> Bool
== :: Database -> Database -> Bool
$c== :: Database -> Database -> Bool
Eq)

createTableStmtFor :: forall a. (Entity a) => Database -> String
createTableStmtFor :: forall a. Entity a => Database -> String
createTableStmtFor Database
dbServer =
  String
"CREATE TABLE "
    forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    forall a. [a] -> [a] -> [a]
++ String
" ("
    forall a. [a] -> [a] -> [a]
++ forall a. [a] -> [[a]] -> [a]
intercalate String
", " (forall a b. (a -> b) -> [a] -> [b]
map (\(String
f, String
c) -> String
c forall a. [a] -> [a] -> [a]
++ String
" " forall a. [a] -> [a] -> [a]
++ forall a. Entity a => Database -> String -> String
columnTypeFor @a Database
dbServer String
f forall a. [a] -> [a] -> [a]
++ String -> String
optionalPK String
f) (forall a. Entity a => [(String, String)]
fieldsToColumns @a))
    forall a. [a] -> [a] -> [a]
++ String
");"
  where
    optionalPK :: String -> String
optionalPK String
f = if forall a. Entity a => String -> Bool
isIdField @a String
f 
                    then case Database
dbServer of
                      Database
SQLite   -> String
" PRIMARY KEY AUTOINCREMENT"
                      Database
Postgres -> String
" PRIMARY KEY"
                    else String
""

isIdField :: forall a. (Entity a) => String -> Bool
isIdField :: forall a. Entity a => String -> Bool
isIdField String
f = String
f forall a. Eq a => a -> a -> Bool
== forall a. Entity a => String
idField @a

-- | A function that returns the column type for a field of an entity.
-- TODO: Support other databases than just SQLite and Postgres.
columnTypeFor :: forall a. (Entity a) => Database -> String -> String
columnTypeFor :: forall a. Entity a => Database -> String -> String
columnTypeFor Database
dbDialect String
fieldName =
  case Database
dbDialect of
    Database
SQLite   -> String -> String
columnTypeForSQLite String
fType
    Database
Postgres -> if forall a. Entity a => String -> Bool
isIdField @a String
fieldName 
                  then String
"serial"
                  else String -> String
columnTypeForPostgres String
fType
  where
    maybeFType :: Maybe TypeRep
maybeFType = forall a. Entity a => String -> Maybe TypeRep
maybeFieldTypeFor @a String
fieldName
    fType :: String
fType = forall b a. b -> (a -> b) -> Maybe a -> b
maybe String
"OTHER" forall a. Show a => a -> String
show Maybe TypeRep
maybeFType
  
    columnTypeForSQLite :: String -> String
    columnTypeForSQLite :: String -> String
columnTypeForSQLite = \case  
        String
"Int"    -> String
"INTEGER"
        String
"[Char]" -> String
"TEXT"
        String
"Double" -> String
"REAL"
        String
"Float"  -> String
"REAL"
        String
"Bool"   -> String
"INT"
        String
_        -> String
"TEXT"

    columnTypeForPostgres :: String -> String
    columnTypeForPostgres :: String -> String
columnTypeForPostgres = \case  
        String
"Int"    -> String
"numeric"
        String
"[Char]" -> String
"varchar"
        String
"Double" -> String
"numeric"
        String
"Float"  -> String
"numeric"
        String
"Bool"   -> String
"boolean"
        String
_        -> String
"varchar"

dropTableStmtFor :: forall a. (Entity a) => String
dropTableStmtFor :: forall a. Entity a => String
dropTableStmtFor =
  String
"DROP TABLE IF EXISTS "
    forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    forall a. [a] -> [a] -> [a]
++ String
";"