DROP DATABASE IF EXISTS gardennotebook;
GO

CREATE DATABASE gardennotebook;
GO

USE gardennotebook;
GO

-- -----------------------------------------------------
-- Table `afflictionclass`
--
-- Groups the types of affliction - pest, disease, death
-- -----------------------------------------------------
DROP TABLE IF EXISTS afflictionclass;
GO

DROP TRIGGER IF EXISTS afflictionclass_updated;
GO

CREATE  TABLE afflictionclass (
  afflictionClassId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,	-- pest, disease or death
  name VARCHAR(255) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL );
GO

CREATE TRIGGER afflictionclass_updated
ON afflictionclass
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE afflictionclass
		SET lastUpdated = SYSUTCDATETIME()
		WHERE afflictionClassId IN (SELECT afflictionClassId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `affliction`
--
-- These are the various afflictions observed.
-- -----------------------------------------------------
DROP TABLE IF EXISTS affliction;
GO

DROP TRIGGER IF EXISTS affliction_updated;
GO

CREATE  TABLE affliction (
  afflictionId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,	-- An actual type of pest, disease or death, e.g. lily beetle, blight.
  afflictionClassId INT NULL DEFAULT NULL
	FOREIGN KEY REFERENCES afflictionclass(afflictionClassId),
  name VARCHAR(255) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  INDEX fk_affliction_afflictionClass1_idx(afflictionClassId ASC)
);
GO

CREATE TRIGGER affliction_updated
ON affliction
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE affliction
		SET lastUpdated = SYSUTCDATETIME()
		WHERE afflictionId IN (SELECT afflictionId FROM inserted);
END;
GO


-- -----------------------------------------------------
-- Table `location`
--
-- A physical location, such as a bed or a greenhouse.  The installation scripts include a single entry 'The Garden' which is a top-level parent for other Locations, such as flower beds, lawns, greenhouses, etc.  'The Garden' is given a default geometry of a 100x100 square.  You may have more than one top-level Location, such as \'The Garden', 'The Allotment'; these will be treated as physically distinct areas, all the children of each top level Location must be physically within that top level parent if a geometry value is supplied.
-- -----------------------------------------------------
DROP TABLE IF EXISTS location;
GO

DROP TRIGGER IF EXISTS location_updated;
GO

CREATE TABLE location (
  locationId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  parentLocationId INT NULL DEFAULT NULL 
	FOREIGN KEY REFERENCES location(locationId), -- The current Location is considered to be part of the parent Location.  The relationship may be logical, rather than physical.
  name VARCHAR(255) NOT NULL UNIQUE,	-- Name of the location.
  description VARCHAR(255) DEFAULT NULL,	-- A brief description of the Location
  underCover BIT DEFAULT 0 NOT NULL,	-- Is this location under cover (e.g. a greenhouse) or outside.
  geometry GEOMETRY DEFAULT NULL,	-- Describes the boundary of the Location.  At present, only simply connected polygons are supported.  The value should be provided in the OpenGIS 'well known text' format (this requires polygons to be explicitly closed).Note that x increases left-to-right (as normal), y increases bottom-to-top (i.e. South to North) NOT the usual computer graphics convention.If a value is supplied, it must lie physically within the geomentry of the ultimate top-level parent of the Location.
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,  
  INDEX fk_Location_Location1_idx (parentLocationId ASC)
);
GO

CREATE TRIGGER location_updated
ON location
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE location
		SET lastUpdated = SYSUTCDATETIME()
		WHERE locationId IN (SELECT locationId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `croprotationgroup`
--
-- Crops are grouped together when planning a rotation.  All the crops in, say, group 'legumes' will follow the crops in group 'roots' and will be followed by crops in group 'brassicas'.
-- -----------------------------------------------------
DROP TABLE IF EXISTS croprotationgroup;
GO

DROP TRIGGER IF EXISTS croprotationgroup_updated;
GO

CREATE TABLE croprotationgroup (
  cropRotationGroupId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  subsequentCropGroupId INT NULL DEFAULT NULL UNIQUE
	CONSTRAINT fk_CropRotationGroup_CropRotationGroup1
	FOREIGN KEY (subsequentCropGroupId)
	REFERENCES croprotationgroup (cropRotationGroupId),	-- The Crop Rotation Group which follows this Group in a given location.
  name VARCHAR(255) NOT NULL UNIQUE,	-- The name of a crop group for rotation purposes, e.g. 'brassicas'.
  description VARCHAR(255) DEFAULT NULL,	-- A description of the crops in this group,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL
);
GO

CREATE TRIGGER croprotationgroup_updated
ON croprotationgroup
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE croprotationgroup
		SET lastUpdated = SYSUTCDATETIME()
		WHERE cropRotationGroupId IN (SELECT cropRotationGroupId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `plantspecies`
--
-- A species of plant grown in the garden, for instance, 'tomato'.
-- A species will have zero or more varieties.
--
-- Note the use of a Trigger to reflect changes to commonName into Product.
-- -----------------------------------------------------
DROP TABLE IF EXISTS plantspecies;
GO

DROP TRIGGER IF EXISTS plantspecies_updated;
GO

DROP TRIGGER IF EXISTS plantspecies_name_updated;
GO

CREATE TABLE plantspecies (
  plantSpeciesId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  cropRotationGroupId INT NULL DEFAULT NULL
	FOREIGN KEY REFERENCES croprotationgroup (cropRotationGroupId),
  commonName VARCHAR(255) NOT NULL UNIQUE,	-- The name by which plants of this type are usually known.
  latinName VARCHAR(255) DEFAULT NULL,	-- The formal horticultural name for this species.  The common name is language specific, the latin name is internationally standardised.
  description VARCHAR(255) DEFAULT NULL,
  utility VARCHAR(45) DEFAULT NULL,	-- The plant's function in the garden, typically vegetable, ornamental, weed.
  hardiness VARCHAR(45) DEFAULT NULL,	-- The plant's hardiness or tenderness, typically hardy, half hardy, tender.
  lifeType VARCHAR(45) DEFAULT NULL,	-- Typically annual, biennial or perennial.  Variations such as 'perennial grown as annual' (e.g. runner bean).
  plantType VARCHAR(45) DEFAULT NULL,	-- Such as climber, shrub, tree.
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  INDEX fk_PlantSpecies_CropRotationGroup1_idx (cropRotationGroupId ASC)
 );
GO

CREATE TRIGGER plantspecies_updated
ON plantspecies
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE plantspecies
		SET lastUpdated = SYSUTCDATETIME()
		WHERE plantSpeciesId IN (SELECT plantSpeciesId FROM inserted);
END;
GO

CREATE TRIGGER plantspecies_name_updated
ON plantspecies
AFTER UPDATE
NOT FOR REPLICATION
AS
SET NOCOUNT ON;
IF ( UPDATE(commonName) )
BEGIN
	--SET NOCOUNT ON;
	DECLARE @newName AS VARCHAR(255),
		@newId AS INT;
	DECLARE cursorPS CURSOR
	FOR SELECT ix.commonName, ix.plantSpeciesId
		FROM inserted AS ix, deleted AS dx
		WHERE ix.plantSpeciesId = dx.plantSpeciesId AND
			ix.commonName != dx.commonName;

	OPEN cursorPS;

	FETCH NEXT FROM cursorPS
		INTO @newName, @newId;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		UPDATE product 
			SET name = @newName
			WHERE plantSpeciesId = @newId;
	END;

	CLOSE cursorPS;
	DEALLOCATE cursorPS;

END;
GO

-- -----------------------------------------------------
-- Table `plantvariety`
--
-- A particular variety of a plant species.
-- Note that the common name is NOT a unique value as different species may have varieties with the same name - e.g. Money Maker tomato AND aubergine.
--
-- Note the use of a Trigger to reflect changes to commonName into Product.
-- -----------------------------------------------------
DROP TABLE IF EXISTS plantvariety;
GO

DROP TRIGGER IF EXISTS plantvariety_updated;
GO

DROP TRIGGER IF EXISTS plantvariety_name_updated;
GO

CREATE TABLE plantvariety (
  plantVarietyId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  plantSpeciesId INT NOT NULL
	FOREIGN KEY REFERENCES plantspecies(plantSpeciesId),
  commonName VARCHAR(255) NOT NULL,	-- The usual name for this variety.  The values in this column are NOT unique.
  latinName VARCHAR(255) DEFAULT NULL,	-- The formal horticultural name for this variety.  Rarely different from the species' latin name.
  synonymSet VARCHAR(255) DEFAULT NULL,	-- This implements the concept of synonyms.  All varieties which are synonyms of each other will have the same value for synonymSet which is a user chosen string.  A null value means the variety is NOT a synonym.
  description VARCHAR(255) DEFAULT NULL,
  utility VARCHAR(45) DEFAULT NULL,	-- The plant's function in the garden, typically vegetable, ornamental, weed.
  hardiness VARCHAR(45) DEFAULT NULL,	-- The plant's hardiness or tenderness, typically hardy, half hardy, tender.
  lifeType VARCHAR(45) DEFAULT NULL,	-- Typically annual, biennial or perennial.  Variations such as 'perennial grown as annual' (e.g. runner bean).
  plantType VARCHAR(45) DEFAULT NULL,	-- Such as climber, shrub, tree.
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  CONSTRAINT uq_plantVariety UNIQUE (plantSpeciesId ASC, commonName ASC),
  INDEX fk_plantVariety_plantSpecies_idx(plantSpeciesId ASC)
);
GO

CREATE TRIGGER plantvariety_updated
ON plantvariety
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE plantvariety
		SET lastUpdated = SYSUTCDATETIME()
		WHERE plantVarietyId IN (SELECT plantVarietyId FROM inserted);
END;
GO

CREATE TRIGGER plantvariety_name_updated
ON plantvariety
AFTER UPDATE
NOT FOR REPLICATION
AS
SET NOCOUNT ON;
IF ( UPDATE(commonName) )
BEGIN
	--SET NOCOUNT ON;
	DECLARE @newName AS VARCHAR(255),
		@newId AS INT;
	DECLARE cursorPS CURSOR
	FOR SELECT ix.commonName, ix.plantVarietyId
		FROM inserted AS ix, deleted AS dx
		WHERE ix.plantVarietyId = dx.plantVarietyId AND
			ix.commonName != dx.commonName;

	OPEN cursorPS;

	FETCH NEXT FROM cursorPS
		INTO @newName, @newId;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		UPDATE product 
			SET name = @newName
			WHERE plantVarietyId= @newId;
	END;

	CLOSE cursorPS;
	DEALLOCATE cursorPS;

END;
GO

-- -----------------------------------------------------
-- Table `afflictionevent`
--
-- Records an observation of an affliction - for instance, an outbreak of red spider mite.
-- -----------------------------------------------------
DROP TABLE IF EXISTS afflictionevent;
GO

DROP TRIGGER IF EXISTS afflictionevent_updated;
GO

CREATE TABLE afflictionevent (
  afflictionEventId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  afflictionId INT NOT NULL
	FOREIGN KEY REFERENCES affliction(afflictionId),
  plantSpeciesId INT DEFAULT NULL
	FOREIGN KEY REFERENCES plantspecies(plantSpeciesId),	-- The problem occured on plants of this species.
  plantVarietyId INT DEFAULT NULL
	FOREIGN KEY REFERENCES plantvariety (plantVarietyId),	-- The problem occured on plants of this variety.
  locationId INT DEFAULT NULL
	FOREIGN KEY REFERENCES location(locationId),	-- Where the affliction happened.
  date DATE NOT NULL,	-- When an outbreak is spotted or there is some development.
  quantity VARCHAR(255) DEFAULT NULL,	-- Quantifies the Affliction event, for instance, the number of plants affected.
  severity VARCHAR(255) DEFAULT NULL,	-- The severity of the outbreak, e.g. 'mild', 'severe'.
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  INDEX fk_afflictionEvent_affliction1_idx(afflictionId  ASC),
  INDEX fk_AfflictionEvent_PlantSpecies1_idx(plantSpeciesId ASC),
  INDEX fk_afflictionEvent_plantVariety1_idx(plantVarietyId ASC),
  INDEX fk_AfflictionEvent_Location1_idx(locationId ASC)
);
GO

CREATE TRIGGER afflictionevent_updated
ON afflictionevent
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE afflictionevent
		SET lastUpdated = SYSUTCDATETIME()
		WHERE afflictionEventId IN (SELECT afflictionEventId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `comment`
--
-- A comment on a particular entry.  Often the main content of a husbandry item.
-- -----------------------------------------------------
DROP TABLE IF EXISTS comment;
GO

DROP TRIGGER IF EXISTS comment_updated;
GO

CREATE TABLE comment (
  commentId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  ownerId INT NOT NULL,
  ownerType CHAR(2) NOT NULL,	-- The comment is associated with a DB record of this type.
  date DATE NOT NULL,
  comment VARCHAR(1024) NOT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  INDEX ix_byOwner (ownerId ASC, ownerType ASC)	-- Comments are always retrieved with their owner (they are only meaningful as part of the owner) so this index facilitates the join
);
GO

CREATE TRIGGER comment_updated
ON comment
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE comment
		SET lastUpdated = SYSUTCDATETIME()
		WHERE commentId IN (SELECT commentId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `croppingplan`
-- -----------------------------------------------------
DROP TABLE IF EXISTS croppingplan;
GO

DROP TRIGGER IF EXISTS croppingplan_updated;
GO

CREATE TABLE croppingplan (
  croppingPlanId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  cropRotationGroupId INT NOT NULL
	FOREIGN KEY REFERENCES croprotationgroup(cropRotationGroupId),
  locationId INT NOT NULL
	FOREIGN KEY REFERENCES location(locationId),
  yearOfPlan DATE NOT NULL DEFAULT GETDATE(),	-- The calendar period covered by this review.  Only the YEAR part is to be used.
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  
  CONSTRAINT CroppingPlan_UNIQUE UNIQUE (yearOfPlan ASC, locationId ASC, cropRotationGroupId ASC),
  INDEX fk_CroppingPlan_CropRotationGroup1_idx (cropRotationGroupId ASC),
  INDEX fk_CroppingPlan_Location1_idx (locationId ASC)
);
GO

CREATE TRIGGER croppingplan_updated
ON croppingplan
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE croppingplan
		SET lastUpdated = SYSUTCDATETIME()
		WHERE croppingPlanId IN (SELECT croppingPlanId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `CroppingActual`
-- -----------------------------------------------------
DROP TABLE IF EXISTS croppingactual ;
GO

DROP TRIGGER IF EXISTS croppingactual_updated ;
GO

CREATE TABLE croppingactual (
  croppingActualId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  croppingPlanId INT NOT NULL
	FOREIGN KEY (croppingPlanId)
	REFERENCES croppingplan (croppingPlanId),	-- The plants identified were grown as part of this CroppingPlan
  plantSpeciesId INT NOT NULL
	FOREIGN KEY (plantSpeciesId)
	REFERENCES plantspecies (plantSpeciesId),	-- The PlantSpecies grown
  plantVarietyId INT NULL
	FOREIGN KEY (plantVarietyId)
	REFERENCES plantvariety (plantVarietyId),	-- The variety of the PlantSpecies grown.  If this value is present, plantSpeciesId MUST be present (this denormalises the database design but simplifies searching).
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,

  INDEX fk_CroppingActual_CroppingPlan1_idx (croppingPlanId ASC),
  INDEX fk_CroppingActual_PlantSpecies1_idx (plantSpeciesId ASC),
  INDEX fk_CroppingActual_PlantVariety1_idx (plantVarietyId ASC)
);
GO

CREATE TRIGGER croppingactual_updated
ON croppingactual
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE croppingactual
		SET lastUpdated = SYSUTCDATETIME()
		WHERE croppingActualId IN (SELECT croppingActualId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `groundworkactivity`
--
-- A type of groundwork activity.
-- -----------------------------------------------------
DROP TABLE IF EXISTS groundworkactivity;
GO

DROP TRIGGER IF EXISTS groundworkactivity_updated;
GO

CREATE TABLE groundworkactivity (
  groundWorkActivityId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL
);
GO

CREATE TRIGGER groundworkactivity_updated
ON groundworkactivity
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE groundworkactivity
		SET lastUpdated = SYSUTCDATETIME()
		WHERE groundWorkActivityId IN (SELECT groundWorkActivityId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `groundwork`
--
-- An instance of groundwork being done.
-- -----------------------------------------------------
DROP TABLE IF EXISTS groundwork;
GO

DROP TRIGGER IF EXISTS groundwork_updated;
GO

CREATE TABLE groundwork (
  groundWorkId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  activityId INT NOT NULL
	FOREIGN KEY REFERENCES groundworkactivity (groundWorkActivityId),
  plantSpeciesId INT DEFAULT NULL
	FOREIGN KEY REFERENCES plantspecies (plantSpeciesId),	-- The activity is for plants of this species.
  plantVarietyId INT DEFAULT NULL
	FOREIGN KEY REFERENCES plantvariety (plantVarietyId),	-- The activity is for plants of this variety.
  locationId INT DEFAULT NULL
	FOREIGN KEY REFERENCES location (locationId),	-- Where the Groundwork was done.
  date DATE NOT NULL,
  quantity VARCHAR(255) DEFAULT NULL,	-- Quantifies the amount of work, e.g. 'two trenches dug'.
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,

  INDEX fk_groundWork_Activity_idx(activityId ASC),
  INDEX fk_Groundwork_PlantSpecies1_idx(plantSpeciesId ASC),
  INDEX fk_groundWork_plantVariety1_idx(plantVarietyId ASC),
  INDEX fk_Groundwork_Location1_idx (locationId ASC)
);
GO

CREATE TRIGGER groundwork_updated
ON groundwork
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE groundwork
		SET lastUpdated = SYSUTCDATETIME()
		WHERE groundWorkId IN (SELECT groundWorkId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `husbandryclass`
--
-- The various types of diary entry for the husbandry lifecycle.
-- -----------------------------------------------------
DROP TABLE IF EXISTS husbandryclass;
GO

DROP TRIGGER IF EXISTS husbandryclass_updated;
GO

CREATE TABLE husbandryclass (
  husbandryClassId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  terminal BIT DEFAULT 0 NOT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL
);
GO

CREATE TRIGGER husbandryclass_updated
ON husbandryclass
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE husbandryclass
		SET lastUpdated = SYSUTCDATETIME()
		WHERE husbandryClassId IN (SELECT husbandryClassId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `husbandry`
--
-- Diary entries for events in the life of a crop.
-- -----------------------------------------------------
DROP TABLE IF EXISTS husbandry;
GO

DROP TRIGGER IF EXISTS husbandry_updated;
GO

CREATE TABLE husbandry (
  husbandryId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  husbandryClassId INT NOT NULL FOREIGN KEY REFERENCES husbandryclass (husbandryClassId),
  plantSpeciesId INT NOT NULL FOREIGN KEY REFERENCES plantspecies (plantSpeciesId),	-- Always required as Husbandry always refers to a plant.
  plantVarietyId INT DEFAULT NULL FOREIGN KEY REFERENCES plantvariety (plantVarietyId),	-- The activity is for plants of this variety.
  locationId INT DEFAULT NULL
	FOREIGN KEY REFERENCES location (locationId),	-- Where the activity was carried out.
  terminalAfflictionId INT DEFAULT NULL FOREIGN KEY REFERENCES affliction (afflictionId),	-- If this Diary entry is for the demise of a plant, this can be used to record the guilty pest or disease.
  date DATE NOT NULL,
  quantity VARCHAR(255) DEFAULT NULL,	-- Quantifies the activity, e.g. number of plants potted on.
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
    
  INDEX fk_Husbandry_PlantSpecies1_idx (plantSpeciesId ASC),
  INDEX fk_husbandry_plantVariety1_idx (plantVarietyId ASC),
  INDEX fk_husbandry_husbandryClass1_idx (husbandryClassId ASC),
  INDEX fk_Husbandry_Affliction1_idx (terminalAfflictionId ASC),
  INDEX fk_Husbandry_Location1_idx (locationId ASC)
);
GO

CREATE TRIGGER husbandry_updated
ON husbandry
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE husbandry
		SET lastUpdated = SYSUTCDATETIME()
		WHERE husbandryId IN (SELECT husbandryId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `journal`
-- -----------------------------------------------------
DROP TABLE IF EXISTS journal;
GO

DROP TRIGGER IF EXISTS journal_updated;
GO

CREATE TABLE journal (
  journalId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  date DATE NOT NULL DEFAULT GETDATE(),	-- Date of the Journal entry.
  title VARCHAR(255) NOT NULL,	-- A brief title for the journal entry.
  description VARCHAR(1024) NULL DEFAULT NULL,	-- The full text of the entry.
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL
);
GO

CREATE TRIGGER journal_updated
ON journal
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE journal
		SET lastUpdated = SYSUTCDATETIME()
		WHERE journalId IN (SELECT journalId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `plantnote`
-- -----------------------------------------------------
DROP TABLE IF EXISTS plantnote;
GO

DROP TRIGGER IF EXISTS plantnote_updated;
GO

CREATE TABLE plantnote (
  plantNoteId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  plantSpeciesId INT NOT NULL FOREIGN KEY REFERENCES plantspecies (plantSpeciesId),
  plantVarietyId INT DEFAULT NULL FOREIGN KEY REFERENCES plantvariety (plantVarietyId),
  title VARCHAR(45) NOT NULL,
  note VARCHAR(max) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,

  CONSTRAINT plantNoteId_UNIQUE UNIQUE (plantSpeciesId, plantVarietyId, title),
    
  INDEX fk_PlantNote_PlantSpecies1_idx (plantSpeciesId ASC),
  INDEX fk_PlantNote_PlantVariety1_idx (plantVarietyId ASC)
);
GO

CREATE TRIGGER plantnote_updated
ON plantnote
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE plantnote
		SET lastUpdated = SYSUTCDATETIME()
		WHERE plantNoteId IN (SELECT plantNoteId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `retailer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS retailer;
GO

DROP TRIGGER IF EXISTS retailer_updated;
GO

CREATE TABLE retailer (
  retailerId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  webSite VARCHAR(255) DEFAULT NULL,
  eMail VARCHAR(255) DEFAULT NULL,
  phone VARCHAR(255) NULL,
  mobile VARCHAR(255) DEFAULT NULL,
  ownBrandOnly BIT DEFAULT 0 NOT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL
);
GO

CREATE TRIGGER retailer_updated
ON retailer
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE retailer
		SET lastUpdated = SYSUTCDATETIME()
		WHERE retailerId IN (SELECT retailerId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `productbrand`
-- -----------------------------------------------------
DROP TABLE IF EXISTS productbrand;
GO

DROP TRIGGER IF EXISTS productbrand_updated;
GO

CREATE TABLE productbrand (
  productBrandId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  ownBrandRetailerId INT DEFAULT NULL FOREIGN KEY REFERENCES retailer (retailerId),
  name VARCHAR(255) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
    
  INDEX fk_ProductBrand_Retailer1_idx (ownBrandRetailerId ASC)
);
GO

CREATE TRIGGER productbrand_updated
ON productbrand
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE productbrand
		SET lastUpdated = SYSUTCDATETIME()
		WHERE productBrandId IN (SELECT productBrandId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `productcategory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS productcategory;
GO

DROP TRIGGER IF EXISTS productcategory_updated;
GO

CREATE TABLE productcategory (
  productCategoryId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  plantLike BIT DEFAULT 0 NOT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL
);
GO

CREATE TRIGGER productcategory_updated
ON productcategory
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE productcategory
		SET lastUpdated = SYSUTCDATETIME()
		WHERE productCategoryId IN (SELECT productCategoryId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `product`
-- -----------------------------------------------------
DROP TABLE IF EXISTS product;
GO

DROP TRIGGER IF EXISTS product_updated;
GO

CREATE TABLE product (
  productId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  productCategoryId INT NOT NULL FOREIGN KEY REFERENCES productcategory (productCategoryId),
  plantSpeciesId INT DEFAULT NULL FOREIGN KEY REFERENCES plantspecies (plantSpeciesId),
  plantVarietyId INT DEFAULT NULL FOREIGN KEY REFERENCES plantvariety (plantVarietyId),
  productBrandId INT DEFAULT NULL FOREIGN KEY REFERENCES productbrand (productBrandId),
  name VARCHAR(255) NOT NULL,
  nameDetail_1 VARCHAR(255) DEFAULT NULL,
  nameDetail_2 VARCHAR(255) DEFAULT NULL,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,

  CONSTRAINT ix_unique_name UNIQUE (productCategoryId, productBrandId, name, nameDetail_1, nameDetail_2),
    
  INDEX fk_product_productCategory1_idx (productCategoryId ASC),
  INDEX fk_product_plantVariety1_idx (plantVarietyId ASC),
  INDEX fk_Product_PlantSpecies1_idx (plantSpeciesId ASC),
  INDEX fk_Product_ProductBrand1_idx (productBrandId ASC)
);
GO

CREATE TRIGGER product_updated
ON product
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE product
		SET lastUpdated = SYSUTCDATETIME()
		WHERE productId IN (SELECT productId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `purchase`
-- -----------------------------------------------------
DROP TABLE IF EXISTS purchase;
GO

DROP TRIGGER IF EXISTS purchase_updated;
GO

CREATE TABLE purchase (
  purchaseId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  retailerId INT NOT NULL FOREIGN KEY REFERENCES retailer (retailerId),
  date DATE NOT NULL,
  totalCost DECIMAL(5,2) DEFAULT NULL,
  currency CHAR(3) DEFAULT NULL,
  orderNo VARCHAR(255) DEFAULT NULL,
  invoiceNo VARCHAR(255) DEFAULT NULL,
  deliveryDate DATE DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,

  INDEX fk_purchase_supplier1_idx (retailerId ASC)
);
GO

CREATE TRIGGER purchase_updated
ON purchase
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE purchase
		SET lastUpdated = SYSUTCDATETIME()
		WHERE purchaseId IN (SELECT purchaseId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `purchaseitem`
-- -----------------------------------------------------
DROP TABLE IF EXISTS purchaseitem;
GO

DROP TRIGGER IF EXISTS purchaseitem_updated;
GO

CREATE TABLE purchaseitem (
  purchaseItemId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  purchaseId INT NOT NULL FOREIGN KEY REFERENCES purchase (purchaseId),
  productId INT NOT NULL FOREIGN KEY REFERENCES product (productId),
  quantity DECIMAL(8,3) DEFAULT NULL,
  unit VARCHAR(255) DEFAULT NULL,
  itemCost DECIMAL(5,2) DEFAULT NULL,
  currency CHAR(3) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
    
  INDEX fk_purchaseItem_purchase1_idx (purchaseId ASC),
  INDEX fk_purchaseItem_product1_idx (productId ASC)
);
GO

CREATE TRIGGER purchaseitem_updated
ON purchaseitem
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE purchaseitem
		SET lastUpdated = SYSUTCDATETIME()
		WHERE purchaseItemId IN (SELECT purchaseItemId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `reminder`
-- -----------------------------------------------------
DROP TABLE IF EXISTS reminder;
GO

DROP TRIGGER IF EXISTS reminder_updated;
GO

CREATE TABLE reminder (
  reminderId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  plantSpeciesId INT DEFAULT NULL FOREIGN KEY REFERENCES plantspecies (plantSpeciesId),
  plantVarietyId INT DEFAULT NULL FOREIGN KEY REFERENCES plantvariety (plantVarietyId),
  husbandryClassId INT DEFAULT NULL FOREIGN KEY REFERENCES husbandryclass (husbandryClassId),
  groundWorkActivityId INT DEFAULT NULL FOREIGN KEY REFERENCES groundworkactivity (groundWorkActivityId),
  husbandryId INT DEFAULT NULL
	FOREIGN KEY REFERENCES husbandry (husbandryId),	-- The Husbandry editor can set up a ToDoList entry intended as a 'watch for this to happen' (e.g. on sowing, add a 'watch for germination').  This field is the id of that parent/ancestor Husbandry event so when this ToDoList entry is processed the appropriate Storyline links can be set up.  Normally, this action will generate a ToDoList entry; this field (and the purchaseItemId field) are to allow that ToDo to be delayed.
  purchaseItemId INT NULL DEFAULT NULL
	FOREIGN KEY REFERENCES purchaseitem (purchaseItemId),	-- The Purchase editor can set up a ToDoList entry intended as a 'watch for this to happen' (e.g. for seed purchasde, add a 'watch for sowing').  This field is the id of that parent/ancestor PurchaseItem event so when this ToDoList entry is processed the appropriate Storyline links can be set up.  Normally, this action will generate a ToDoList entry; this field (and the husbandryId field) are to allow that ToDo to be delayed.',
  showFrom DATE NOT NULL,	-- For a single shot reminder, the first date it will be shown on.<BR>\\nFor a repeating reminder, the current or next interval when the reminder will be active.
  singleShot BIT DEFAULT 1 NOT NULL,	-- If true, the Reminder is only activated once.
  repeatInterval CHAR(1) DEFAULT 'W',	-- For a repeating reminder, the unit of the repeat interval (see repeatQuantifier).  D - daily, W - weekly, M - monthly, Y - annually.  A value is required for repeating reminders so set a default for safety.
  repeatQuantifier INT DEFAULT '0',	-- For a repeating Reminder, the number of repeatIntervals between repeats (see repeatInterval).  So 'weekly' and '2' means every fortnight.  0 means no repetition (avoids complications with nullable ints in the code - an impossible combination!)  A value is required for repeating reminders so set non-null with a default for safety.
  repeatUntil DATE DEFAULT NULL,	-- For a repeating reminder, the LAST time this reminder will be shown.  Null means 'repeat forever'.
  description VARCHAR(1024) DEFAULT NULL,	-- The actual text of the reminder.
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
    
  INDEX fk_Reminder_PlantVariety1_idx (plantVarietyId ASC),
  INDEX fk_Reminder_HusbandryClass1_idx (husbandryClassId ASC),
  INDEX fk_Reminder_GroundworkActivity1_idx (groundWorkActivityId ASC),
  INDEX fk_Reminder_PlantSpecies1_idx (plantSpeciesId ASC),
  INDEX fk_Reminder_Husbandry1_idx (husbandryId ASC),
  INDEX fk_Reminder_PurchaseItem1_idx (purchaseItemId ASC)
);
GO

CREATE TRIGGER reminder_updated
ON reminder
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE reminder
		SET lastUpdated = SYSUTCDATETIME()
		WHERE reminderId IN (SELECT reminderId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `retailerhasproduct`
-- -----------------------------------------------------
DROP TABLE IF EXISTS retailerhasproduct;
GO

DROP TRIGGER IF EXISTS retailerhasproduct_updated;
GO

CREATE TABLE retailerhasproduct (
  retailerHasProductId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  retailerId INT NOT NULL FOREIGN KEY REFERENCES retailer (retailerId),
  productId INT NOT NULL FOREIGN KEY REFERENCES product (productId),
  SKU VARCHAR(255) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,

  INDEX fk_supplier_has_product_product1_idx (productId ASC),
  INDEX fk_supplier_has_product_supplier1_idx (retailerId ASC)
);
GO

CREATE TRIGGER retailerhasproduct_updated
ON retailerhasproduct
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE retailerhasproduct
		SET lastUpdated = SYSUTCDATETIME()
		WHERE retailerHasProductId IN (SELECT retailerHasProductId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `review`
-- -----------------------------------------------------
DROP TABLE IF EXISTS review;
GO

DROP TRIGGER IF EXISTS review_updated;
GO

CREATE TABLE review (
  reviewId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  date DATE NOT NULL DEFAULT GETDATE(),	-- Date the Review was written.
  yearInReview DATE NOT NULL DEFAULT GETDATE(),	-- The calendar period covered by this review.  Only the YEAR part is to be used.
  coverFrom DATE NULL DEFAULT NULL,	-- The start date of the review period.
  coverTo DATE NULL DEFAULT NULL,	-- The end date of the review period.
  title VARCHAR(255),	-- A brief title for the review.
  description VARCHAR(max) NULL DEFAULT NULL,	-- The full text of the review.
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
);
GO

CREATE TRIGGER review_updated
ON review
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE review
		SET lastUpdated = SYSUTCDATETIME()
		WHERE reviewId IN (SELECT reviewId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `ReviewReferences`
-- -----------------------------------------------------
DROP TABLE IF EXISTS reviewreferences ;
GO

DROP TRIGGER IF EXISTS reviewreferences_updated;
GO

CREATE TABLE reviewreferences (
  reviewReferencesId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  reviewId INT NOT NULL
	FOREIGN KEY REFERENCES review (reviewId),	-- The references are for this Review.
  plantSpeciesId INT NULL DEFAULT NULL
	FOREIGN KEY REFERENCES plantspecies (plantSpeciesId),	-- The species of plant being reviewed.
  plantVarietyId INT NULL DEFAULT NULL
	FOREIGN KEY REFERENCES plantvariety (plantVarietyId),	-- The variety of plant being reviewed.
  husbandryClassId INT NULL DEFAULT NULL
	FOREIGN KEY REFERENCES husbandryclass (husbandryClassId),	-- The husbandry (life cycle) activity being reviewed.
  groundWorkActivityId INT NULL DEFAULT NULL
	FOREIGN KEY REFERENCES groundworkactivity (groundWorkActivityId),	-- The type of groundwork activity being reviewd.
  afflictionId INT NULL DEFAULT NULL
	FOREIGN KEY REFERENCES affliction (afflictionId),	-- The affliction (and/or its treatment) being reviewed.
  locationId INT NULL DEFAULT NULL
	FOREIGN KEY REFERENCES location (locationId), -- The location being reviewed.
  productId INT NULL DEFAULT NULL
	FOREIGN KEY (productId) REFERENCES product (productId),
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  INDEX fk_ReviewReferences_Review1_idx (reviewId ASC),
  INDEX fk_Review_HusbandryClass1_idx (husbandryClassId ASC),
  INDEX fk_Review_GroundworkActivity1_idx (groundWorkActivityId ASC),
  INDEX fk_Review_PlantSpecies1_idx (plantSpeciesId ASC),
  INDEX fk_Review_PlantVariety1_idx (plantVarietyId ASC),
  INDEX fk_Review_Affliction1_idx (afflictionId ASC),
  INDEX fk_Review_Location1_idx (locationId ASC),
  INDEX fk_ReviewReferences_Product1_idx (productId ASC)
);
GO


CREATE TRIGGER reviewreferences_updated
ON reviewreferences
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE reviewreferences
		SET lastUpdated = SYSUTCDATETIME()
		WHERE reviewId IN (SELECT reviewId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `sale`
-- -----------------------------------------------------
DROP TABLE IF EXISTS sale;
GO

DROP TRIGGER IF EXISTS sale_updated;
GO

CREATE TABLE sale (
  saleId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  date DATE NOT NULL,
  purchasedBy VARCHAR(255) DEFAULT NULL,
  totalCost DECIMAL(5,2) DEFAULT NULL,
  currency CHAR(3) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
);
GO

CREATE TRIGGER sale_updated
ON sale
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE sale
		SET lastUpdated = SYSUTCDATETIME()
		WHERE saleId IN (SELECT saleId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `saleitem`
-- -----------------------------------------------------
DROP TABLE IF EXISTS saleitem;
GO

DROP TRIGGER IF EXISTS saleitem_updated;
GO

CREATE TABLE saleitem (
  saleItemId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  saleId INT NOT NULL FOREIGN KEY REFERENCES sale (saleId),
  plantSpeciesId INT NOT NULL FOREIGN KEY REFERENCES plantspecies (plantSpeciesId),
  plantVarietyId INT DEFAULT NULL FOREIGN KEY REFERENCES plantvariety (plantVarietyId),
  quantity DECIMAL(8,3) DEFAULT NULL,
  unit VARCHAR(255) DEFAULT NULL,
  itemCost DECIMAL(5,2) DEFAULT NULL,
  currency CHAR(3) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
    
  INDEX fk_SaleItem_Sale1_idx (saleId ASC),
  INDEX fk_SaleItem_PlantSpecies1_idx (plantSpeciesId ASC),
  INDEX fk_SaleItem_PlantVariety1_idx (plantVarietyId ASC)
);
GO

CREATE TRIGGER saleitem_updated
ON saleitem
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE saleitem
		SET lastUpdated = SYSUTCDATETIME()
		WHERE saleItemId IN (SELECT saleItemId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `shoppinglist`
-- -----------------------------------------------------
DROP TABLE IF EXISTS shoppinglist;
GO

DROP TRIGGER IF EXISTS shoppinglist_updated;
GO

CREATE TABLE shoppinglist (
  shoppingListId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  productId INT DEFAULT NULL FOREIGN KEY REFERENCES product (productId),
  nonspecificItem VARCHAR(255) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
    
  INDEX fk_ShoppingList_Product1_idx (productId ASC)
);
GO

CREATE TRIGGER shoppinglist_updated
ON shoppinglist
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE shoppinglist
		SET lastUpdated = SYSUTCDATETIME()
		WHERE shoppingListId IN (SELECT shoppingListId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `storylineindex`

-- Entries in this table are NEVER updated
-- -----------------------------------------------------
DROP TABLE IF EXISTS storylineindex;
GO

CREATE TABLE storylineindex (
  storyLineIndexId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  ancestorId INT NOT NULL,
  ancestorType CHAR(2) NOT NULL,
  descendantId INT NOT NULL,
  descendantType CHAR(2) NOT NULL,
  depth INT DEFAULT '0' NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,

  INDEX ancestorId (ancestorId ASC, ancestorType ASC),
  INDEX descendantId (descendantId ASC, descendantType ASC)
);
GO

-- -----------------------------------------------------
-- Table `todolist`
-- -----------------------------------------------------
DROP TABLE IF EXISTS todolist;
GO

DROP TRIGGER IF EXISTS todolist_updated;
GO

CREATE TABLE todolist (
  toDoListId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  plantSpeciesId INT DEFAULT NULL FOREIGN KEY REFERENCES plantspecies (plantSpeciesId),
  plantVarietyId INT DEFAULT NULL FOREIGN KEY REFERENCES plantvariety (plantVarietyId),
  husbandryClassId INT DEFAULT NULL FOREIGN KEY REFERENCES husbandryclass (husbandryClassId),
  groundWorkActivityId INT DEFAULT NULL FOREIGN KEY REFERENCES groundworkactivity (groundWorkActivityId),
  husbandryId INT DEFAULT NULL FOREIGN KEY REFERENCES husbandry (husbandryId),
  purchaseItemId INT NULL DEFAULT NULL
	FOREIGN KEY REFERENCES purchaseitem (purchaseItemId),	-- Used for a 'watch for' item from a Purchase (e.g. seed purchase will have a 'sow' watch-for'.
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
    
  INDEX fk_table1ToDoList_GroundworkActivity1_idx (groundWorkActivityId ASC),
  INDEX fk_ToDoList_HusbandryClass1_idx (husbandryClassId ASC),
  INDEX fk_ToDoList_PlantVariety1_idx (plantVarietyId ASC),
  INDEX fk_ToDoList_PlantSpecies1_idx (plantSpeciesId ASC),
  INDEX fk_ToDoList_Husbandry1_idx (husbandryId ASC),
  INDEX fk_ToDoList_PurchaseItem1_idx (purchaseItemId ASC)
);
GO

CREATE TRIGGER todolist_updated
ON todolist
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE todolist
		SET lastUpdated = SYSUTCDATETIME()
		WHERE toDoListId IN (SELECT toDoListId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `weathercondition`
-- -----------------------------------------------------
DROP TABLE IF EXISTS weathercondition;
GO

DROP TRIGGER IF EXISTS weathercondition_updated;
GO

CREATE TABLE weathercondition (
  weatherConditionId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL
);
GO

CREATE TRIGGER weathercondition_updated
ON weathercondition
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE weathercondition
		SET lastUpdated = SYSUTCDATETIME()
		WHERE weatherConditionId IN (SELECT weatherConditionId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `weather`
-- -----------------------------------------------------
DROP TABLE IF EXISTS weather;
GO

DROP TRIGGER IF EXISTS weather_updated;
GO

CREATE TABLE weather (
  weatherId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  weatherConditionId INT NOT NULL FOREIGN KEY REFERENCES weathercondition (weatherConditionId),
  date DATE NOT NULL,
  severity VARCHAR(255) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
    
  INDEX fk_Weather_WeatherType1_idx (weatherConditionId ASC)
);
GO

CREATE TRIGGER weather_updated
ON weather
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE weather
		SET lastUpdated = SYSUTCDATETIME()
		WHERE weatherId IN (SELECT weatherId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `weatherdetail`
-- -----------------------------------------------------
DROP TABLE IF EXISTS weatherdetail;
GO

DROP TRIGGER IF EXISTS weatherdetail_updated;
GO

CREATE TABLE weatherdetail (
  weatherDetailId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  date DATE NOT NULL,
  timeAt TIME DEFAULT NULL,
  timeFrom TIME DEFAULT NULL,
  timeTo TIME DEFAULT NULL,
  temperatureMax FLOAT DEFAULT NULL,
  temperatureMin FLOAT DEFAULT NULL,
  temperatureScale CHAR(2) DEFAULT 'C',
  pressureMax FLOAT DEFAULT NULL,
  pressureMin FLOAT DEFAULT NULL,
  pressureAvg FLOAT DEFAULT NULL,
  pressureScale CHAR(5) DEFAULT 'mb',
  humidityMax FLOAT DEFAULT NULL,
  humidityMin FLOAT DEFAULT NULL,
  humidityAvg FLOAT DEFAULT NULL,
  windMax FLOAT DEFAULT NULL,
  windAvg FLOAT DEFAULT NULL,
  windMin FLOAT DEFAULT NULL,
  windGust FLOAT DEFAULT NULL,
  windScale CHAR(2) DEFAULT 'M',
  rainFall FLOAT DEFAULT NULL,
  rainScale CHAR(3) DEFAULT NULL,
  sunshine FLOAT DEFAULT NULL,
  cloudCover INT DEFAULT NULL,
  cloudCoverUnit CHAR(3) DEFAULT '8',
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL
);
GO

CREATE TRIGGER weatherdetail_updated
ON weatherdetail
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE weatherdetail
		SET lastUpdated = SYSUTCDATETIME()
		WHERE weatherDetailId IN (SELECT weatherDetailId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `wildlifespecies`
-- -----------------------------------------------------
DROP TABLE IF EXISTS wildlifespecies;
GO

DROP TRIGGER IF EXISTS wildlifespecies_updated;
GO

CREATE TABLE wildlifespecies (
  wildlifeSpeciesId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL
);
GO

CREATE TRIGGER wildlifespecies_updated
ON wildlifespecies
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE wildlifespecies
		SET lastUpdated = SYSUTCDATETIME()
		WHERE wildlifeSpeciesId IN (SELECT wildlifeSpeciesId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- Table `wildlife`
--
-- A noteworthy observation of wildlife - an interesting event, unusual species, etc
-- -----------------------------------------------------
DROP TABLE IF EXISTS wildlife;
GO

DROP TRIGGER IF EXISTS wildlife_updated;
GO

CREATE TABLE wildlife (
  wildlifeId INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
  wildlifeSpeciesId INT NOT NULL FOREIGN KEY REFERENCES wildlifespecies (wildlifeSpeciesId),
  locationId INT NULL DEFAULT NULL
	FOREIGN KEY REFERENCES location (locationId),	-- Where the observation took place.
  date DATE NOT NULL,
  variety VARCHAR(255) DEFAULT NULL,
  lastUpdated DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
  created DATETIME2 DEFAULT SYSUTCDATETIME() NOT NULL,
    
  INDEX fk_Wildlife_WildlifeSpecies1_idx (wildlifeSpeciesId ASC)
);
GO

CREATE TRIGGER wildlife_updated
ON wildlife
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE wildlife
		SET lastUpdated = SYSUTCDATETIME()
		WHERE wildlifeId IN (SELECT wildlifeId FROM inserted);
END;
GO

-- -----------------------------------------------------
-- procedure LoadTodoList
-- -----------------------------------------------------
DROP PROCEDURE IF EXISTS LoadTodoList;
GO

CREATE PROCEDURE LoadTodoList
AS
BEGIN
	SET NOCOUNT ON;
	SELECT reminderId
	INTO #idlist
	FROM reminder
	WHERE GETDATE() >= showFrom;
        
	INSERT INTO todolist (plantSpeciesId, plantVarietyId, husbandryClassId, groundworkActivityId, husbandryId, purchaseItemId, description)
		SELECT plantspeciesid, plantvarietyid, husbandryclassid, groundworkactivityid, husbandryId, purchaseItemId, description
		FROM reminder
		WHERE reminderId IN (SELECT reminderId FROM #idlist);

	DELETE FROM reminder WHERE reminderId IN (SELECT reminderId FROM #idlist) AND singleShot=1;

	UPDATE reminder SET showFrom = DATEADD(day, repeatQuantifier, showFrom) WHERE reminderId IN (SELECT reminderId FROM #idlist) AND repeatInterval='D';     
	UPDATE reminder SET showFrom = DATEADD(week, repeatQuantifier, showFrom) WHERE reminderId IN (SELECT reminderId FROM #idlist) AND repeatInterval='W';
	UPDATE reminder SET showFrom = DATEADD(month, repeatQuantifier, showFrom) WHERE reminderId IN (SELECT reminderId FROM #idlist) AND repeatInterval='M';
	UPDATE reminder SET showFrom = DATEADD(year, repeatQuantifier, showFrom) WHERE reminderId IN (SELECT reminderId FROM #idlist) AND repeatInterval='Y';

	DELETE FROM reminder WHERE showFrom > repeatUntil;
     
DROP TABLE #idlist;
END;
GO

