{-# LANGUAGE AllowAmbiguousTypes #-}

module Database.GP.SqlGenerator
  ( insertStmtFor,
    insertReturningStmtFor,
    updateStmtFor,
    upsertStmtFor,
    selectFromStmt,
    countStmtFor,
    deleteStmtFor,
    createTableStmtFor,
    dropTableStmtFor,
    columnTypeFor,
    WhereClauseExpr,
    Field,
    field,
    whereClauseValues,
    (&&.),
    (||.),
    (=.),
    (>.),
    (<.),
    (>=.),
    (<=.),
    (<>.),
    like,
    between,
    in',
    isNull,
    not',
    sqlFun,
    allEntries,
    byId,
    byIdColumn,
    orderBy,
    SortOrder (..),
    limit,
    limitOffset,
    NonEmpty (..),
    Database (..),
    defaultSqliteMapping,
    defaultPostgresMapping,
    ColumnTypeMapping,
  )
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 "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
" ("
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String -> [String] -> String
forall a. [a] -> [[a]] -> [a]
intercalate String
", " [String]
insertCols
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
") VALUES ("
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String -> [String] -> String
forall a. [a] -> [[a]] -> [a]
intercalate String
", " (Int -> [String]
params ([String] -> Int
forall a. [a] -> Int
forall (t :: * -> *) a. Foldable t => t a -> Int
length [String]
insertCols))
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
");"
  where
    insertCols :: [String]
insertCols = forall a. Entity a => [String]
insertColumns @a

upsertStmtFor :: forall a. Entity a => String
upsertStmtFor :: forall a. Entity a => String
upsertStmtFor =
  String
"INSERT INTO "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
" ("
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String -> [String] -> String
forall a. [a] -> [[a]] -> [a]
intercalate String
", " [String]
insertCols
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
") VALUES ("
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String -> [String] -> String
forall a. [a] -> [[a]] -> [a]
intercalate String
", " (Int -> [String]
params ([String] -> Int
forall a. [a] -> Int
forall (t :: * -> *) a. Foldable t => t a -> Int
length [String]
insertCols))
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
") ON CONFLICT ("
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
idColumn @a
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
") DO UPDATE SET "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String -> [String] -> String
forall a. [a] -> [[a]] -> [a]
intercalate String
", " [String]
updatePairs
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
";"
  where
    insertCols :: [String]
insertCols = forall a. Entity a => [String]
columnNamesFor @a
    updatePairs :: [String]
updatePairs = (String -> String) -> [String] -> [String]
forall a b. (a -> b) -> [a] -> [b]
map (String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
" = ?") [String]
insertCols

insertColumns :: forall a. Entity a => [String]
insertColumns :: forall a. Entity a => [String]
insertColumns = 
  if forall a. Entity a => Bool
autoIncrement @a
    then (String -> Bool) -> [String] -> [String]
forall a. (a -> Bool) -> [a] -> [a]
filter (String -> String -> Bool
forall a. Eq a => a -> a -> Bool
/= forall a. Entity a => String
idColumn @a) [String]
columns
    else [String]
columns 
  where
    columns :: [String]
columns = forall a. Entity a => [String]
columnNamesFor @a

insertReturningStmtFor :: forall a. Entity a => String
insertReturningStmtFor :: forall a. Entity a => String
insertReturningStmtFor =
  String
"INSERT INTO "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
" ("
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String -> [String] -> String
forall a. [a] -> [[a]] -> [a]
intercalate String
", " [String]
insertCols
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
") VALUES ("
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String -> [String] -> String
forall a. [a] -> [[a]] -> [a]
intercalate String
", " (Int -> [String]
params ([String] -> Int
forall a. [a] -> Int
forall (t :: * -> *) a. Foldable t => t a -> Int
length [String]
insertCols))
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
") RETURNING "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
returnCols
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
";"
  where
    insertCols :: [String]
insertCols = forall a. Entity a => [String]
insertColumns @a
    returnCols :: String
returnCols = String -> [String] -> String
forall a. [a] -> [[a]] -> [a]
intercalate String
", " (forall a. Entity a => [String]
columnNamesFor @a)

columnNamesFor :: forall a. Entity a => [String]
columnNamesFor :: forall a. Entity a => [String]
columnNamesFor = ((String, String) -> String) -> [(String, String)] -> [String]
forall a b. (a -> b) -> [a] -> [b]
map (String, String) -> String
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 "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
" SET "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String -> [String] -> String
forall a. [a] -> [[a]] -> [a]
intercalate String
", " [String]
updatePairs
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
" WHERE "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
idColumn @a
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
" = ?"
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
";"
  where
    updatePairs :: [String]
updatePairs = (String -> String) -> [String] -> [String]
forall a b. (a -> b) -> [a] -> [b]
map (String -> String -> String
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 "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String -> [String] -> String
forall a. [a] -> [[a]] -> [a]
intercalate String
", " (forall a. Entity a => [String]
columnNamesFor @a)
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
" FROM "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
" WHERE "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => WhereClauseExpr -> String
whereClauseExprToSql @a WhereClauseExpr
whereClauseExpr
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
";"

-- | A function that returns an SQL count 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.
countStmtFor :: forall a. (Entity a) => WhereClauseExpr -> String
countStmtFor :: forall a. Entity a => WhereClauseExpr -> String
countStmtFor WhereClauseExpr
whereClauseExpr =
  String
"SELECT COUNT(*) FROM "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
" WHERE "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => WhereClauseExpr -> String
whereClauseExprToSql @a WhereClauseExpr
whereClauseExpr
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
";"

-- | A function that returns an SQL delete statement for an entity. Type 'a' must be an instance of Entity.
deleteStmtFor :: forall a. (Entity a) => String
deleteStmtFor :: forall a. Entity a => String
deleteStmtFor =
  String
"DELETE FROM "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
" WHERE "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
idColumn @a
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
" = ?;"

-- | An enumeration of the supported database types. 
data Database = Postgres | SQLite

-- | A function that returns an SQL create table statement for an entity type. Type 'a' must be an instance of Entity.
createTableStmtFor :: forall a. (Entity a) => ColumnTypeMapping -> String
createTableStmtFor :: forall a. Entity a => (String -> String) -> String
createTableStmtFor String -> String
columnTypeMapping =
  String
"CREATE TABLE "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
" ("
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String -> [String] -> String
forall a. [a] -> [[a]] -> [a]
intercalate String
", " (((String, String) -> String) -> [(String, String)] -> [String]
forall a b. (a -> b) -> [a] -> [b]
map (\(String
f, String
c) -> String
c String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
" " String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => (String -> String) -> String -> String
columnTypeFor @a String -> String
columnTypeMapping String
f String -> String -> String
forall a. [a] -> [a] -> [a]
++ String -> String
optionalPK String
f) (forall a. Entity a => [(String, String)]
fieldsToColumns @a))
    String -> String -> String
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 String
" PRIMARY KEY" else String
""

isIdField :: forall a. (Entity a) => String -> Bool
isIdField :: forall a. Entity a => String -> Bool
isIdField String
f = String
f String -> String -> Bool
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.
--   The function takes a column type mapping function as parameter.
--   This function is used to map Haskell field types to SQL column types.
columnTypeFor :: forall a. (Entity a) => ColumnTypeMapping -> String -> String
columnTypeFor :: forall a. Entity a => (String -> String) -> String -> String
columnTypeFor String -> String
columnTypeMapping String
fieldName = String -> String
columnTypeMapping String
fType
  where
    fType :: String
fType = 
      if forall a. Entity a => Bool
autoIncrement @a Bool -> Bool -> Bool
&& forall a. Entity a => String -> Bool
isIdField @a String
fieldName 
        then String
"AUTOINCREMENT"
        else String -> (TypeRep -> String) -> Maybe TypeRep -> String
forall b a. b -> (a -> b) -> Maybe a -> b
maybe String
"OTHER" TypeRep -> String
forall a. Show a => a -> String
show (Maybe TypeRep -> String) -> Maybe TypeRep -> String
forall a b. (a -> b) -> a -> b
$ forall a. Entity a => String -> Maybe TypeRep
maybeFieldTypeFor @a String
fieldName

-- | A type alias for mapping a Haskell field type to a SQL column type.
--   this type can be used to define custom mappings for field types.
type ColumnTypeMapping = String -> String

-- | The default mapping for SQLite databases.
--   This mapping is used when no custom mapping is provided.
defaultSqliteMapping :: ColumnTypeMapping
defaultSqliteMapping :: String -> String
defaultSqliteMapping = \case
  String
"AUTOINCREMENT" -> String
"INTEGER"
  String
"Int"    -> String
"INTEGER"
  String
"[Char]" -> String
"TEXT"
  String
"Double" -> String
"REAL"
  String
"Float"  -> String
"REAL"
  String
"Bool"   -> String
"INT"
  String
_        -> String
"TEXT"

-- | The default mapping for Postgres databases.
--   This mapping is used when no custom mapping is provided.
defaultPostgresMapping :: ColumnTypeMapping
defaultPostgresMapping :: String -> String
defaultPostgresMapping = \case
  String
"AUTOINCREMENT" -> String
"serial"
  String
"Int"    -> String
"numeric"
  String
"[Char]" -> String
"varchar"
  String
"Double" -> String
"numeric"
  String
"Float"  -> String
"numeric"
  String
"Bool"   -> String
"boolean"
  String
_        -> String
"varchar"

-- | This function generates a DROP TABLE statement for an entity type.
dropTableStmtFor :: forall a. (Entity a) => String
dropTableStmtFor :: forall a. Entity a => String
dropTableStmtFor =
  String
"DROP TABLE IF EXISTS "
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ forall a. Entity a => String
tableName @a
    String -> String -> String
forall a. [a] -> [a] -> [a]
++ String
";"