#lang racket

(provide migrate-all!)

(require db
         sql
         "conn.rkt")

(define migrations-box (box '()))

(define (migrate-all!)
  (printf "Applying migrations on connection ~a...\n"
          (dbsystem-name (connection-dbsystem (current-conn))))
  (for ([pair (in-list (unbox migrations-box))])
    (match pair
      [(cons migration-name stmts)
       (with-tx
         (for ([stmt (in-list stmts)])
           (query-exec (current-conn) stmt)))
       (printf "Applied migration: ~a\n" migration-name)])))

(define-syntax-rule (define-migration migration-name sql)
  (let ((migrations (unbox migrations-box))
        (name-symbol (string->symbol migration-name)))
    (if (assoc name-symbol migrations)
        (error 'define-migration "migration '~a' declared more than once" migration-name)
        (set-box! migrations-box (append migrations (list (cons name-symbol sql)))))))


;;;;;;;;;;;;
;; NUTRIENTS
;;;;;;;;;;;;

(define-migration "create table nutrients"
  (list
   (create-table #:if-not-exists
                 nutrients
                 #:columns
                 [id integer #:not-null]
                 [canonical_name text #:not-null]
                 [formula text #:not-null]
                 #:constraints
                 (primary-key id)
                 (unique canonical_name)
                 (unique formula))))

(define-migration "create table nutrient_value_sets"
  (list
   (create-table #:if-not-exists
                 nutrient_value_sets
                 #:columns
                 [id integer #:not-null]
                 [nutrient_measurement_id integer]
                 [nutrient_target_id integer]
                 [crop_requirement_id integer]
                 [fertilizer_product_id integer]
                 #:constraints
                 (primary-key id)
                 (foreign-key nutrient_measurement_id
                              #:references (nutrient_measurements id)
                              #:on-delete #:cascade)
                 (foreign-key nutrient_target_id
                              #:references (nutrient_targets id)
                              #:on-delete #:cascade)
                 (foreign-key crop_requirement_id
                              #:references (crop_requirements id)
                              #:on-delete #:cascade)
                 (foreign-key fertilizer_product_id
                              #:references (fertilizer_products id)
                              #:on-delete #:cascade))
   "CREATE INDEX IF NOT EXISTS idx_nvs_meas ON nutrient_value_sets(nutrient_measurement_id)"
   "CREATE INDEX IF NOT EXISTS idx_nvs_targ ON nutrient_value_sets(nutrient_target_id)"
   "CREATE INDEX IF NOT EXISTS idx_nvs_crop ON nutrient_value_sets(crop_requirement_id)"
   "CREATE INDEX IF NOT EXISTS idx_nvs_prod ON nutrient_value_sets(fertilizer_product_id)"))

(define-migration "create table nutrient_values"
  (list
   (create-table #:if-not-exists
                 nutrient_values
                 #:columns
                 [value_set_id integer #:not-null]
                 [nutrient_id integer #:not-null]
                 [value_ppm real #:not-null]
                 #:constraints
                 (primary-key value_set_id nutrient_id)
                 (foreign-key value_set_id
                              #:references (nutrient_value_sets id)
                              #:on-delete #:cascade)
                 (foreign-key nutrient_id
                              #:references (nutrients id)
                              #:on-delete #:cascade))
   "CREATE INDEX IF NOT EXISTS idx_nv_set_nutrient ON nutrient_values(value_set_id, nutrient_id)"))

(define-migration "create table nutrient_measurements"
  (list
   (create-table #:if-not-exists
                 nutrient_measurements
                 #:columns
                 [id integer #:not-null]
                 ;; ISO8601 date
                 [measured_on text #:not-null]
                 #:constraints
                 (primary-key id)
                 (unique measured_on))))

(define-migration "create table nutrient_targets"
  (list
   (create-table #:if-not-exists
                 nutrient_targets
                 #:columns
                 [id integer #:not-null]
                 ;; ISO8601 date
                 [effective_on text #:not-null]
                 #:constraints
                 (primary-key id)
                 (unique effective_on))))


;;;;;;;;
;; CROPS
;;;;;;;;

(define-migration "create table crops"
  (list
   (create-table #:if-not-exists
                 crops
                 #:columns
                 [id integer #:not-null]
                 [canonical_name integer #:not-null]
                 #:constraints
                 (primary-key id)
                 (unique canonical_name))))

(define-migration "create table crop_requirements"
  (list
   (create-table #:if-not-exists
                 crop_requirements
                 #:columns
                 [id integer #:not-null]
                 [crop_id integer]
                 [profile text #:not-null]
                 #:constraints
                 (primary-key id)
                 (foreign-key crop_id
                              #:references (crops id)
                              #:on-delete #:cascade))))


;;;;;;;;;;;;;;
;; FERTILIZERS
;;;;;;;;;;;;;;

(define-migration "create table fertilizer_products"
  (list
   (create-table #:if-not-exists
                 fertilizer_products
                 #:columns
                 [id integer #:not-null]
                 [canonical_name text #:not-null]
                 [brand_name text]
                 #:constraints
                 (primary-key id)
                 (unique canonical_name))))

(module+ test
  (connect!)
  (migrate-all!))