module Persistence.Income
  ( count
  , list
  , listModifiedSince
  , create
  , edit
  , delete
  , definedForAll
  , getCumulativeIncome
  ) where

import qualified Data.List              as L
import           Data.Map               (Map)
import qualified Data.Map               as M
import qualified Data.Maybe             as Maybe
import qualified Data.Text              as T
import           Data.Time.Calendar     (Day)
import           Data.Time.Clock        (UTCTime)
import           Data.Time.Clock        (getCurrentTime)
import           Database.SQLite.Simple (FromRow (fromRow), NamedParam ((:=)),
                                         Only (Only))
import qualified Database.SQLite.Simple as SQLite
import           Prelude                hiding (id, until)

import           Common.Model           (Income (..), IncomeId, PaymentId,
                                         UserId)

import           Model.Query            (Query (Query))

newtype Row = Row Income

instance FromRow Row where
  fromRow = Row <$> (Income <$>
    SQLite.field <*>
    SQLite.field <*>
    SQLite.field <*>
    SQLite.field <*>
    SQLite.field <*>
    SQLite.field <*>
    SQLite.field)

data CountRow = CountRow Int

instance FromRow CountRow where
  fromRow = CountRow <$> SQLite.field

count :: Query Int
count =
  Query (\conn ->
    (Maybe.fromMaybe 0 . fmap (\(CountRow n) -> n) . Maybe.listToMaybe) <$>
      SQLite.query_ conn "SELECT COUNT(*) FROM income WHERE deleted_at IS NULL"
  )

list :: Int -> Int -> Query [Income]
list page perPage =
  Query (\conn ->
    map (\(Row i) -> i) <$>
      SQLite.query
        conn
        "SELECT * FROM income WHERE deleted_at IS NULL ORDER BY date DESC LIMIT ? OFFSET ?"
        (perPage, (page - 1) * perPage)
  )

listModifiedSince :: UTCTime -> Query [Income]
listModifiedSince since =
  Query (\conn ->
    map (\(Row i) -> i) <$>
      SQLite.query
        conn
        (SQLite.Query . T.intercalate " " $
          [ "SELECT *"
          , "FROM income"
          , "WHERE"
          ,   "created_at >= ?"
          ,   "OR edited_at >= ?"
          ,   "OR deleted_at >= ?"
          ])
        (since, since, since)
  )

create :: UserId -> Day -> Int -> Query Income
create userId date amount =
  Query (\conn -> do
    createdAt <- getCurrentTime
    SQLite.execute
      conn
      "INSERT INTO income (user_id, date, amount, created_at) VALUES (?, ?, ?, ?)"
      (userId, date, amount, createdAt)
    incomeId <- SQLite.lastInsertRowId conn
    return $ Income
      { _income_id        = incomeId
      , _income_userId    = userId
      , _income_date      = date
      , _income_amount    = amount
      , _income_createdAt = createdAt
      , _income_editedAt  = Nothing
      , _income_deletedAt = Nothing
      }
  )

edit :: UserId -> IncomeId -> Day -> Int -> Query (Maybe Income)
edit userId incomeId incomeDate incomeAmount =
  Query (\conn -> do
    mbIncome <- fmap (\(Row i) -> i) . Maybe.listToMaybe <$>
      SQLite.query conn "SELECT * FROM income WHERE id = ?" (Only incomeId)
    case mbIncome of
      Just income ->
        do
          currentTime <- getCurrentTime
          SQLite.execute
            conn
            "UPDATE income SET edited_at = ?, date = ?, amount = ? WHERE id = ? AND user_id = ?"
            (currentTime, incomeDate, incomeAmount, incomeId, userId)
          return . Just $ Income
            { _income_id        = incomeId
            , _income_userId    = userId
            , _income_date      = incomeDate
            , _income_amount    = incomeAmount
            , _income_createdAt = _income_createdAt income
            , _income_editedAt  = Just currentTime
            , _income_deletedAt = Nothing
            }
      Nothing ->
        return Nothing
  )

delete :: UserId -> PaymentId -> Query ()
delete userId paymentId =
  Query (\conn ->
    SQLite.execute
      conn
      "UPDATE income SET deleted_at = datetime('now') WHERE id = ? AND user_id = ?"
      (paymentId, userId)
  )

data UserDayRow = UserDayRow (UserId, Day)

instance FromRow UserDayRow where
  fromRow = do
    user <- SQLite.field
    day <- SQLite.field
    return $ UserDayRow (user, day)

definedForAll :: [UserId] -> Query (Maybe Day)
definedForAll users =
  Query (\conn ->
    (fromRows . fmap (\(UserDayRow (user, day)) -> (user, day))) <$>
      SQLite.query_
        conn
        "SELECT user_id, MIN(date) FROM income WHERE deleted_at IS NULL GROUP BY user_id;"
  )
  where
    fromRows rows =
      if L.sort users == L.sort (map fst rows) then
        Maybe.listToMaybe . reverse . L.sort . map snd $ rows
      else
        Nothing

getCumulativeIncome :: Day -> Day -> Query (Map UserId Int)
getCumulativeIncome start end =
  Query (\conn -> M.fromList <$> SQLite.queryNamed conn (SQLite.Query query) parameters)
  where
    query =
      T.intercalate "\n" $
        [ "SELECT user_id, CAST(ROUND(SUM(count)) AS INTEGER) FROM ("
        , "  SELECT"
        , "    I1.user_id,"
        , "    ((JULIANDAY(MIN(I2.date)) - JULIANDAY(I1.date)) * I1.amount * 12 / 365) AS count"
        , "  FROM (" <> (selectBoundedIncomes ">" ":start") <> ") AS I1"
        , "  INNER JOIN (" <> (selectBoundedIncomes "<" ":end") <> ") AS I2"
        , "  ON I2.date > I1.date AND I2.user_id == I1.user_id"
        , "  GROUP BY I1.date, I1.user_id"
        , ") GROUP BY user_id"
        ]

    selectBoundedIncomes op param =
      T.intercalate "\n" $
        [ "    SELECT user_id, date, amount FROM ("
        , "      SELECT"
        , "        i.user_id, " <> param <> " AS date, i.amount"
        , "      FROM"
        , "        (SELECT id, MAX(date) AS max_date"
        , "        FROM income"
        , "        WHERE date <= " <> param <> " AND deleted_at IS NULL"
        , "        GROUP BY user_id) AS m"
        , "      INNER JOIN income AS i"
        , "      ON i.id = m.id AND i.date = m.max_date"
        , "    ) UNION"
        , "    SELECT user_id, date, amount"
        , "    FROM income"
        , "    WHERE date " <> op <> " " <> param <> " AND deleted_at IS NULL"
        ]

    parameters =
      [ ":start" := start
      , ":end" := end
      ]