#lang racket/base
(require racket/path)
(require (prefix-in db: db))

(define db-logger (make-logger 'ebus-db (current-logger)))

(define db-file? (make-parameter 
                   (build-path (path-only (find-system-path 'run-file)) "database.sqlite3")))

(define pool
  (db:virtual-connection
    (db:connection-pool
      (lambda () 
        (log-message db-logger 'info (format "Open database file ~a" (db-file?)) #t)
        (with-handlers ([exn:fail? (lambda (exn)
                                     (log-message db-logger 'error (format "Error opening database ~a" (db-file?)) #t) (raise exn))])
                       (db:postgresql-connect #:user "ebus"
			    #:database "ebus"
			    #:password "ebus"
			    #:server "localhost")))
 ;;                      (db:sqlite3-connect #:database (db-file?))))
 ;;
      #:max-connections 5
      #:max-idle-connections 2
      )))


;; Test Database Connection
;; Returns #t on success, #f otherwise
(define (db-test)
  (with-handlers ([exn:fail? (lambda (exn) (log-message db-logger 'error (format "Error: ~a" exn) #t) #f)])
    (= (db:query-value pool "SELECT 1") 1)))

;; Query ID of sensor given by sensor-name
;; Returns null if sensor is undefined
(define (get-sensor-id sensor-name)
  (define sql-stmt "SELECT id FROM sensor WHERE name = $1")
  (with-handlers ([exn:fail?  (lambda (exn) (void))])
    (db:query-value pool sql-stmt sensor-name)))

;; Create Sensor-ID with given name
;; returns id
(define (create-sensor-id sensor-name)
  (log-message db-logger 'info (format "create sensor id for ~a" sensor-name) #t)
  (db:query-exec pool "INSERT INTO sensor(name) VALUES ($1)" sensor-name)
  (get-sensor-id sensor-name))

;; Get ID of sensor given by sensor-name
;; define sensor if needed
(define (get-or-create-sensor-id sensor-name)
  (define id (get-sensor-id sensor-name))
  (cond ((void? id) (create-sensor-id sensor-name))
	(else id)))

;; Insert Field in Database
;; Decide Database-Datatype from Ebus-Datatype
;; then calls 'insert`
(define (db-insert-field sensor-name datatype offset value)
  (cond ((member datatype (list "data1c" "data2b" "data2c"))
  	 ;; float
  	 (insert sensor-name value db:sql-null db:sql-null))
  	((member datatype (list "bit" "byte" "data1b" "word" "bcd"))
  	 ;; int
  	 (insert sensor-name db:sql-null value db:sql-null))
  	((member datatype (list "byteEnum"))
  	 ;; string
  	 (insert sensor-name db:sql-null db:sql-null value))
  	(else (log-message db-logger 'error (format "Datatype ~a is not support by DB" datatype) #t))))

(define (insert sensor-name value-float value-int value-string)
  (define sensor-id (get-or-create-sensor-id sensor-name))
  (define type (cond ((not (db:sql-null? value-string)) "string")
		     ((not (db:sql-null? value-float)) "float")
		     ((not (db:sql-null? value-int)) "int")))
  (define sql-stmt 
    (string-append "INSERT INTO value(timestamp, sensor_id, type, value_float, value_int, value_string) "
		   "VALUES (CURRENT_TIMESTAMP, $1, $2, $3, $4, $5)"))
  (log-message db-logger 'info (string-append sql-stmt "\n\t\t"
					   (format
					    "sensor-id=~a type=~a value-float=~a value-int=~a value-string=~a"
					    sensor-id type value-float value-int value-string)) #t)
  (db:query-exec pool sql-stmt sensor-id type value-float value-int value-string))

(provide
 db-file?
 db-logger
 db-test
 db-insert-field)