wip: mating EPs with Operations

5 files changed, 78 insertions, 9 deletions

diff --git a/cmd/web/handlers.go b/cmd/web/handlers.go
index e27b39b..0a7be55 100644
--- a/cmd/web/handlers.go
+++ b/cmd/web/handlers.go
@@ -13,11 +13,22 @@ import (
 func (app *application) listOperations(w http.ResponseWriter, r *http.Request) {
 
 	ops, err := app.operations.ListAll()
-
 	if err != nil {
 		app.serverError(w, r, err)
 		return
 	}
+
+	var esses []models.EngagementStrategy
+
+	for _, op := range ops {
+		es, err := app.engagement_strategies.GetForOperation(op.ID)
+		// TODO: Check what kind of error this is, don't just continue
+		if err != nil {
+			continue
+		}
+		esses = append(esses, es)
+	}
+
 	files := []string{
 		"./ui/html/base.tmpl.html",
 		"./ui/html/pages/operations/list.tmpl.html",
diff --git a/cmd/web/main.go b/cmd/web/main.go
index a8b8cb4..7e25124 100644
--- a/cmd/web/main.go
+++ b/cmd/web/main.go
@@ -12,9 +12,10 @@ import (
 )
 
 type application struct {
-	logger        *slog.Logger
-	operations    *models.OperationModel
-	organisations *models.OrganisationModel
+	logger                *slog.Logger
+	operations            *models.OperationModel
+	organisations         *models.OrganisationModel
+	engagement_strategies *models.EngagementStrategyModel
 }
 
 func main() {
@@ -34,9 +35,10 @@ func main() {
 	defer db.Close()
 
 	app := &application{
-		logger:        logger,
-		operations:    &models.OperationModel{DB: db},
-		organisations: &models.OrganisationModel{DB: db},
+		logger:                logger,
+		operations:            &models.OperationModel{DB: db},
+		organisations:         &models.OrganisationModel{DB: db},
+		engagement_strategies: &models.EngagementStrategyModel{DB: db},
 	}
 
 	// mux := http.NewServeMux()
diff --git a/internal/models/engagement.go b/internal/models/engagement.go
new file mode 100644
index 0000000..db1c2f5
--- /dev/null
+++ b/internal/models/engagement.go
@@ -0,0 +1,36 @@
+package models
+
+import (
+	"database/sql"
+	"errors"
+	"time"
+)
+
+type EngagementStrategy struct {
+	ID        int
+	ValidFrom time.Time
+	ValidTo   time.Time
+	Operation Operation
+}
+
+type EngagementStrategyModel struct {
+	DB *sql.DB
+}
+
+func (m *EngagementStrategyModel) GetForOperation(id int) (EngagementStrategy, error) {
+	stmt := `SELECT id, valid_from, valid_to FROM engagement_strategies
+WHERE operation_id = ?`
+	row := m.DB.QueryRow(stmt, id)
+
+	var es EngagementStrategy
+
+	err := row.Scan(&es.ID, &es.ValidFrom, &es.ValidTo)
+	if err != nil {
+		if errors.Is(err, sql.ErrNoRows) {
+			return EngagementStrategy{}, ErrNoRecord
+		} else {
+			return EngagementStrategy{}, err
+		}
+	}
+	return es, nil
+}
diff --git a/internal/models/organisations.go b/internal/models/organisations.go
index b765f91..88f7bc9 100644
--- a/internal/models/organisations.go
+++ b/internal/models/organisations.go
@@ -82,6 +82,4 @@ func (m *OrganisationModel) Latest() ([]Organisation, error) {
 	}
 
 	return organisations, err
-
-	return nil, nil
 }
diff --git a/populate.sql b/populate.sql
index f94da2f..a78cfd5 100644
--- a/populate.sql
+++ b/populate.sql
@@ -11,6 +11,7 @@ USE ded;
 SET FOREIGN_KEY_CHECKS = 0;
 DROP TABLE IF EXISTS organisations;
 DROP TABLE IF EXISTS operations;
+DROP TABLE IF EXISTS engagement_strategies;
 SET FOREIGN_KEY_CHECKS = 1;
 
 /* Create the Organisations table. */
@@ -45,10 +46,31 @@ CREATE TABLE operations (
 /* Add index to created column. */
 CREATE INDEX idx_operations_created ON operations(created);
 
+CREATE TABLE engagement_strategies (
+    id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
+    valid_from DATETIME NOT NULL,
+    valid_to DATETIME NOT NULL,
+    description VARCHAR(255) NULL,
+    operation_id INT NOT NULL,
+    created DATETIME NOT NULL,
+    CONSTRAINT fk_es_operation
+        FOREIGN KEY(operation_id)
+            REFERENCES operations(id)
+        ON DELETE CASCADE
+        ON UPDATE RESTRICT
+);
+
+CREATE INDEX idx_engagement_strategy_created ON engagement_strategies(created);
+
+
+/* Create some operations */
 INSERT INTO operations (name, created, description, organisation_id) VALUES ("Operation 1", UTC_TIMESTAMP(), "Operation 1 Description", 1);
 INSERT INTO operations (name, created, description, organisation_id) VALUES ("Operation 2", UTC_TIMESTAMP(), "Operation 2 Description", 1);
 INSERT INTO operations (name, created, description, organisation_id) VALUES ("Operation 3", UTC_TIMESTAMP(), "Operation 3 Description", 2);
 
+/* Create some ESs */
+INSERT INTO engagement_strategies (valid_from, valid_to, operation_id, created) VALUES ("2023-01-01", "2025-01-01", 1, UTC_TIMESTAMP());
+
 
 
 /* /1* The following should be carried out on the database server *1/ */