Introduce crop rotations.

These will probably replace nutrient targets as the main entry point
for nutrient requirement calculations.

1 files changed, 24 insertions, 0 deletions

diff --git a/db/migrations.rkt b/db/migrations.rkt
index dfb216e..86c9cce 100644
--- a/db/migrations.rkt
+++ b/db/migrations.rkt
@@ -143,6 +143,30 @@
                      #:constraints (primary-key id)
                      (foreign-key crop_id #:references (crops id) #:on-delete #:cascade))))
 
+(define-migration "create table crop_rotations"
+                  (list (create-table #:if-not-exists crop_rotations
+                                      #:columns [id integer #:not-null]
+                                      ;; ISO8601 date
+                                      [rotation_date text #:not-null]
+                                      [nutrient_measurement_id integer]
+                                      #:constraints (primary-key id)
+                                      (foreign-key nutrient_measurement_id
+                                                   #:references (nutrient_measurements id)
+                                                   #:on-delete
+                                                   #:set-null)
+                                      (unique rotation_date))))
+
+(define-migration
+ "create table crop_rotation_requirements"
+ (list (create-table
+        #:if-not-exists crop_rotation_requirements
+        #:columns [crop_rotation_id integer #:not-null]
+        [crop_requirement_id integer #:not-null]
+        [proportion_percent integer #:not-null]
+        #:constraints (primary-key crop_rotation_id crop_requirement_id)
+        (foreign-key crop_rotation_id #:references (crop_rotation id) #:on-delete #:cascade)
+        (foreign-key crop_requirement_id #:references (crop_requirement id) #:on-delete #:cascade))))
+
 ;;;;;;;;;;;;;;
 ;; FERTILIZERS
 ;;;;;;;;;;;;;;