{-# LANGUAGE FlexibleContexts, FlexibleContexts #-}
module Villefort.Database (makeQuery
                          ,getSubjects
                          ,execQuery
                          ,addDaily
                          ,clean
                          ,getDone
                          ,getDb
                          ,updateVar
                          ,getVar
                          ,isVar
                          ,run
                          ,varTableExists
                          ,makeTable
                          ,addTask) where

import Villefort.Definitions (VConfig(..),Task(..),Date(..))

import Control.Monad.Reader (MonadIO,MonadReader,liftIO,ask)
import Database.HDBC.Sqlite3 (Connection,connectSqlite3)
import Database.HDBC (SqlValue
                     ,execute
                     ,commit
                     ,disconnect
                     ,toSql
                     ,fromSql
                     ,quickQuery'
                     ,getTables
                     ,runRaw
                     ,prepare)
import Control.Monad.Reader (runReaderT)
import System.Environment (getArgs)
import Paths_Villefort (getDataDir)
import Data.Convertible.Base (Convertible)
import Data.Time.Calendar
import Data.Time.LocalTime
import Text.Printf

-- | checks if var table exists
varTableExists :: String -> IO Bool
varTableExists path = do
  conn <- connectSqlite3 path
  table <- getTables conn
  disconnect conn
  pure $ elem "vars" table

constructTable path = do
  conn <- connectSqlite3 path
  runRaw conn "CREATE TABLE vars (id TEXT,val TEXT);"
  commit conn
  disconnect conn
  
makeTable path = do
   isTable <- varTableExists path
   if not isTable then putStrLn "[var] inserting var table for switch in table format from Villefort 0.1.2.15 this will only run once" >> constructTable path else pure ()
   

-- | gets list of subjects from local database
getSubjects :: (MonadReader VConfig m, MonadIO m) => m [String]
getSubjects = do
  subjects <- makeQuery "select Subject from todo where state = 0 group by Subject"
  pure $ map (\x -> x !! 0) subjects

-- | get paths tests for --custom flag to allow for executing custom builds
path' ::  (MonadReader VConfig m, MonadIO m) => m FilePath
path' = do
  env <- ask
  let s = showDatabase env
  args <- liftIO $ getArgs
  let cont = do
        if length args > 1 then
         if args !! 0 == "--custom" then
           return $ args !! 1
         else liftIO $ getDataDir
         else liftIO $ getDataDir
  if s then (liftIO $ putStrLn =<< getDataDir) >> cont else cont

-- | connects to database checks if custom database path is set
getDb :: (MonadReader VConfig m, MonadIO m) => m Connection  
getDb = do
  env <- ask
  let dat = database env
  let isDat = not $ null $ dat
  if isDat then  liftIO $ connectSqlite3 dat else do
    path <- path'
    let fullpath = (path ++ "/data/todo.db")
    liftIO $ connectSqlite3 fullpath
    
-- | converts from sqlValues to Strings
convRow :: [[SqlValue]] -> [[String]]
convRow dat = Prelude.map (\x -> Prelude.map (\y -> fromSql y :: String ) x) dat

-- | get databse var
getVar :: (MonadReader VConfig m, MonadIO m) => String -> m String
getVar varName =  do
  let stmt = ("select val from vars where id = \"" ++  varName ++ "\"") 
  liftIO $ putStrLn stmt
  (!! 0) <$> ( !! 0) <$>  makeQuery' stmt []
-- | inserts a given task into the database
run :: IO Task -> VConfig -> IO ()
run task conf = do
  text <- liftIO task
  runReaderT (addDaily text) conf
  putStrLn $ "[run] sucessfully ran task" ++ show text
  
-- | if var varName exists updates it's value to val
-- | it instiates the variable and sets if it doesn't exit.
updateVar :: (MonadReader VConfig m, MonadIO m) => String -> String -> m ()
updateVar varName val = do
  let sqlid  = toSql varName
  let sqlval = toSql val
  varExists <- isVar varName
  if varExists
    then execQuery "INSERT INTO vars (id,val) VALUES (?,?)" [sqlid,sqlval]
    else execQuery "UPDATE vars SET val = ? WHERE id = ?" [sqlval,sqlid]

-- | checks if var exists in stabase
isVar :: (MonadReader VConfig m,MonadIO m) => String -> m Bool
isVar varName = do
  varExists <- makeQuery $ "SELECT val FROM vars where id = '" ++ varName ++ "'"
  return $ null varExists
   
-- | takes sqlQuery and returns results as a string
makeQuery' :: (MonadReader VConfig m, MonadIO m) => String -> [SqlValue]-> m [[String]]
makeQuery' query params = do
  conn <- getDb
  taskRaw <- liftIO $ quickQuery' conn query params
  liftIO $ disconnect conn
  return (convRow taskRaw)

-- | takes sqlQuery and returns results as a string
makeQuery :: (MonadReader VConfig m, MonadIO m) => String -> m [[String]]
makeQuery query  = do
  conn <- getDb
  taskRaw <- liftIO $ quickQuery' conn query []
  liftIO $ disconnect conn
  return (convRow taskRaw)

    

-- | executes a query that changes values in database
execQuery :: (Convertible a SqlValue, MonadIO m,
                    MonadReader VConfig m) =>
                   String -> [a] -> m ()
execQuery query params = do
  conn <- getDb
  stmt <- liftIO $ prepare conn query
  _ <- ($) liftIO $ execute stmt (map toSql params)
  _ <- ($) liftIO $ commit conn
  liftIO $ disconnect conn

-- | gets the task id for the next avaible todo
getNextId :: (MonadReader VConfig m, MonadIO m) => m Integer
getNextId = do
  f <- makeQuery "select id from todo order by id desc" 
  let rawid = head $ f
  pure $ (read (rawid !! 0) :: Integer) +1

-- | adds new task sanitizes input to avoid SQL escaping
addTask ::  (MonadReader VConfig m, MonadIO m) => String -> String -> String -> String -> m ()
addTask todoSummary todoTitle date todoSubject = do
  nextSqlId <- getNextId
  execQuery "insert into todo (id,Description,Title,Entered,Due,State,time,Subject) Values (?,?,?,datetime('now', 'localtime'),?,1,0,?)"  [show nextSqlId, (clean todoSummary),(clean todoTitle), date, (clean todoSubject)] 

-- | logs new daily entry due on same day
addDaily :: (MonadReader VConfig m, MonadIO m) => Task -> m ()
addDaily addedTask = do
  lastRowId <- getNextId
  date <-liftIO $ makeDate $ due addedTask 
  execQuery "insert into todo (id,Description,Title,Entered,Due,State,time,Subject ) Values (?,?,?,current_date,?,1,0,?)" $ [show lastRowId, description addedTask, title addedTask, date, subject addedTask]

makeDate :: Date -> IO String
makeDate (Date year month day) = pure $ fmtDate year month day
makeDate (Today) = do 
  (year,month,day) <-   (toGregorian . localDay . zonedTimeToLocalTime) <$>  getZonedTime  :: IO (Integer, Int , Int)
  pure $ fmtDate year month day
makeDate (Offset x) = do
  start <- (localDay .  zonedTimeToLocalTime) <$> getZonedTime
  let actualDay = addDays (toInteger x) start
  let (year,month,day) =  toGregorian actualDay
  pure $ fmtDate year month day

fmtDate y m d =fmt y ++ "-" ++ fmt m ++ "-" ++ fmt d
  where fmt x = printf "%02d" x
  
-- | lists the todo items finished today 
getDone :: (MonadReader VConfig m, MonadIO m) => m [[String]]
getDone = makeQuery  "select Title, time  from todo where substr(Due,1,10) = Date('now','localtime') and time != 0"


-- | rudimentary sanitization
clean :: String -> String
clean = id