#lang racket/base
;; Database Access with synx/libpq ffi bindings
(require racket/class)
(require (prefix-in pq: (planet synx/libpq:1:3)))

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

(define con-user? (make-parameter "ebus"))
(define con-password? (make-parameter "ebus"))
(define con-database? (make-parameter "ebus"))
(define con-server? (make-parameter "localhost"))

(define _con (void))
(define (con)
	(when (void? _con)
	     (log-message logger 'info (format "Connect using libpq to Database: user=~a database=~a server=~a" 
					       (con-user?) (con-database?) (con-server?)) #t)
		(set! _con
			(pq:connect #:host (con-server?)
                               #:user (con-user?)
                               #:password (con-password?)
                               #:dbname (con-database?))))
	_con)

(define (query-matrix stmt)
	(con)
	(send _con exec stmt))

;; Test Database Connection
;; Returns #t on success, #f otherwise
(define (pgc-test)
  (with-handlers ([exn:fail? (lambda (exn) (display exn)(display "\n") #f)])
    (query-matrix "SELECT 123")
    #t))

;; Query ID of sensor given by sensor-name
;; return void 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))])
    (let* (
	[result (send (con) exec sql-stmt sensor-name)]
	[matrix (send result get-matrix)])
	(caar matrix))))

;; Create Sensor-ID with given name
;; returns id
(define (create-sensor-id sensor-name)
  (log-message logger 'info (format "create sensor id for ~a" sensor-name) #t)
  (define sql-stmt "INSERT INTO sensor(name) VALUES ($1)")
  (send (con) exec sql-stmt 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 (insert-field sensor-name datatype offset value)
  (cond ((member datatype (list "data1c" "data2b" "data2c"))
  	 (insert sensor-name "float" (real->double-flonum value)))
  	((member datatype (list "bit" "byte" "data1b" "word" "bcd"))
  	 (insert sensor-name "int" value))
  	((member datatype (list "byteEnum"))
  	 (insert sensor-name "string" value))
  	(else 
         (log-message logger 'error (format "Datatype ~a is not support by DB" datatype) #t))))

(define (insert sensor-name type value)
  (define sensor-id (get-or-create-sensor-id sensor-name))
  (log-message logger 'info (string-append (format "sensor-id=~a type=~a value=~a"
					    sensor-id type value)) #t)
  (cond ((string=? type "string")
	 (send (con) p-exec "INSERT INTO value (timestamp, sensor_id, type, value_string) VALUES (now(), $1, 'string', $2)" sensor-id value))
        ((string=? type "float")
	 (send (con) p-exec "INSERT INTO value (timestamp, sensor_id, type, value_float) VALUES (now(), $1, 'float', $2)" sensor-id value))
        ((string=? type "int")
	 (send (con) p-exec "INSERT INTO value (timestamp, sensor_id, type, value_int) VALUES (now(), $1, 'int', $2::integer)" sensor-id value))))

(provide
 (prefix-out db- logger)
 (prefix-out db- con-user?)
 (prefix-out db- con-password?)
 (prefix-out db- con-database?)
 (prefix-out db- con-server?)
 (prefix-out db- pgc-test)
 (prefix-out db- insert-field))