-- Sun 28th May 2023
-- Database model 4.0.0
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema gardennotebook
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `gardennotebook` ;

-- -----------------------------------------------------
-- Schema gardennotebook
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `gardennotebook` ;
SHOW WARNINGS;
USE `gardennotebook` ;

-- -----------------------------------------------------
-- Table `afflictionclass`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `afflictionclass` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `afflictionclass` (
  `afflictionClassId` INT NOT NULL AUTO_INCREMENT COMMENT 'pest, disease or death',
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL DEFAULT NULL,
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`afflictionClassId`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'Groups the types of affliction - pest, disease, death';

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `affliction`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `affliction` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `affliction` (
  `afflictionId` INT NOT NULL AUTO_INCREMENT,
  `afflictionClassId` INT NULL DEFAULT NULL COMMENT 'An actual type of pest, disease or death, e.g. lily beetle, blight.<BR>\\n@apiNote\\nNote that this is optional - I think it\'s too restrictive to force users to create AfflictionClasses before they can specify an actual Affliction.\\nThere\'s no option to show \'all AfflictionEvents caused by a pest\', for instance.\\nHaving this field mandatory also requires AfflictionClass combos on Husbandry and AfflictionEvent editors.',
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL DEFAULT NULL,
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`afflictionId`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) ,
  INDEX `fk_affliction_afflictionClass1_idx` (`afflictionClassId` ASC) ,
  CONSTRAINT `fk_affliction_afflictionClass1`
    FOREIGN KEY (`afflictionClassId`)
    REFERENCES `afflictionclass` (`afflictionClassId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'These are the various afflictions observed.';

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `location`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `location` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `location` (
  `locationId` INT NOT NULL AUTO_INCREMENT,
  `parentLocationId` INT NULL DEFAULT NULL COMMENT '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 COMMENT 'Name of the location.',
  `description` VARCHAR(255) NULL DEFAULT NULL COMMENT 'A brief description of the Location',
  `underCover` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'Is this location under cover (e.g. a greenhouse) or outside.',
  `geometry` GEOMETRY NULL DEFAULT NULL COMMENT '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).\\nNote that x increases left-to-right (as normal), y increases bottom-to-top (i.e. South to North) NOT the usual computer graphics convention.\\nIf a value is supplied, it must lie physically within the geomentry of the ultimate top-level parent of the Location.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`locationId`),
  UNIQUE INDEX `nameLocationcol_UNIQUE` (`name` ASC) ,
  INDEX `fk_Location_Location1_idx` (`parentLocationId` ASC) ,
  CONSTRAINT `fk_Location_Location1`
    FOREIGN KEY (`parentLocationId`)
    REFERENCES `location` (`locationId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = '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.';

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `croprotationgroup`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `croprotationgroup` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `croprotationgroup` (
  `cropRotationGroupId` INT NOT NULL AUTO_INCREMENT,
  `subsequentCropGroupId` INT NULL,
  `name` VARCHAR(255) NOT NULL COMMENT 'The name of a crop group for rotation purposes, e.g. \'brassicas\'.',
  `description` VARCHAR(255) NULL DEFAULT NULL COMMENT 'A description of the crops in this group.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cropRotationGroupId`),
  UNIQUE INDEX `groupName_UNIQUE` (`name` ASC) ,
  UNIQUE INDEX `subsequentCropGroupId_UNIQUE` (`subsequentCropGroupId` ASC) ,
  CONSTRAINT `fk_CropRotationGroup_CropRotationGroup1`
    FOREIGN KEY (`subsequentCropGroupId`)
    REFERENCES `croprotationgroup` (`cropRotationGroupId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'Crops are grouped together when planning a rotation.  All the crops in, say, group \'legumes\' will follw the crops in group \'roots\' and will be followed bt crops in group \'brassicas\'.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `croppingplan` (
  `croppingPlanId` INT NOT NULL AUTO_INCREMENT,
  `cropRotationGroupId` INT NOT NULL,
  `locationId` INT NOT NULL,
  `yearOfPlan` YEAR NOT NULL DEFAULT (YEAR(CURRENT_DATE)) COMMENT 'The calendar period covered by this plan.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`croppingPlanId`),
  INDEX `fk_CroppingPlan_CropRotationGroup1_idx` (`cropRotationGroupId` ASC) ,
  INDEX `fk_CroppingPlan_Location1_idx` (`locationId` ASC) ,
  UNIQUE INDEX `CroppingPlan_UNIQUE` (`yearOfPlan` ASC, `locationId` ASC, `cropRotationGroupId` ASC),
  CONSTRAINT `fk_CroppingPlan_CropRotationGroup1`
    FOREIGN KEY (`cropRotationGroupId`)
    REFERENCES `croprotationgroup` (`cropRotationGroupId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CroppingPlan_Location1`
    FOREIGN KEY (`locationId`)
    REFERENCES `location` (`locationId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `croppingactual` (
  `croppingActualId` INT NOT NULL AUTO_INCREMENT,
  `croppingPlanId` INT NOT NULL COMMENT 'The plants identified were grown as part of this CroppingPlan',
  `plantSpeciesId` INT NOT NULL COMMENT 'The PlantSpecies grown',
  `plantVarietyId` INT NULL COMMENT 'The variety of the PlantSpecies grown.  If this value is present, plantSpeciesId MUST be present (this denormalises the database design but simplifies searching).',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`croppingActualId`),
  INDEX `fk_CroppingActual_CroppingPlan1_idx` (`croppingPlanId` ASC) ,
  INDEX `fk_CroppingActual_PlantSpecies1_idx` (`plantSpeciesId` ASC) ,
  INDEX `fk_CroppingActual_PlantVariety1_idx` (`plantVarietyId` ASC) ,
  CONSTRAINT `fk_CroppingActual_CroppingPlan1`
    FOREIGN KEY (`croppingPlanId`)
    REFERENCES `croppingplan` (`croppingPlanId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CroppingActual_PlantSpecies1`
    FOREIGN KEY (`plantSpeciesId`)
    REFERENCES `plantspecies` (`plantSpeciesId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CroppingActual_PlantVariety1`
    FOREIGN KEY (`plantVarietyId`)
    REFERENCES `plantvariety` (`plantVarietyId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `plantspecies`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `plantspecies` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `plantspecies` (
  `plantSpeciesId` INT NOT NULL AUTO_INCREMENT,
  `cropRotationGroupId` INT NULL DEFAULT NULL,
  `commonName` VARCHAR(255) NOT NULL COMMENT 'The name by which plants of this type are usually known.',
  `latinName` VARCHAR(255) NULL DEFAULT NULL COMMENT 'The formal horticultural name for this species.\\nThe common name is language specific, the latin name is internationally standardised.',
  `description` VARCHAR(255) NULL DEFAULT NULL,
  `utility` VARCHAR(45) NULL DEFAULT NULL COMMENT 'The plant\'s function in the garden, typically vegetable, ornamental, weed.',
  `hardiness` VARCHAR(45) NULL DEFAULT NULL COMMENT 'The plant\'s hardiness or tenderness, typically hardy, half hardy, tender.',
  `lifeType` VARCHAR(45) NULL DEFAULT NULL COMMENT 'Typically annual, biennial or perennial.  Variations such as \'perennial grown as annual\' (e.g. runner bean).',
  `plantType` VARCHAR(45) NULL DEFAULT NULL COMMENT 'Such as climber, shrub, tree.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`plantSpeciesId`),
  UNIQUE INDEX `plantSpeciescol_UNIQUE` (`commonName` ASC) ,
  INDEX `fk_PlantSpecies_CropRotationGroup1_idx` (`cropRotationGroupId` ASC) ,
  CONSTRAINT `fk_PlantSpecies_CropRotationGroup1`
    FOREIGN KEY (`cropRotationGroupId`)
    REFERENCES `croprotationgroup` (`cropRotationGroupId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'A species of plant grown in the garden, for instance, \'tomato\'.  A species will have zero or more varieties.<BR>\\nNote the use of a Trigger to reflect changes to commonName into Product.';

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `plantvariety`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `plantvariety` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `plantvariety` (
  `plantVarietyId` INT NOT NULL AUTO_INCREMENT,
  `plantSpeciesId` INT NOT NULL,
  `commonName` VARCHAR(255) NOT NULL COMMENT 'The usual name for this variety.\\n@apiNote\\nThe values in this column are NOT unique',
  `latinName` VARCHAR(255) NULL DEFAULT NULL COMMENT 'The formal horticultural name for this variety.  Rarely different from the species\' latin name.',
  `synonymSet` VARCHAR(255) NULL DEFAULT NULL COMMENT 'This implements the concept of synonyms.<BR>\\nAll 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.\\n@apiNote\\nTo get a PV and all its synonyms:\\nselect p2.* from PV p1, PV p2 where p1.id in (...) and (p2.id = p1.id or p2.synonymSet = p1.synonymSet) and p1.PSid = p2.PSid\\n<BR>\\nthis relies on the synonymSet being null if NOT in a synonymSet\\n<P>\\nNote that the choice of using a String value was pretty much forced on me by the need to be nullable.  The (unexpected) benefit is that the user can chose the value and maintenance just falls out of the generators.  I had to get past the idea that a PV is a synonym of some \'correct\' PV - all names are equally valid!',
  `description` VARCHAR(255) NULL DEFAULT NULL,
  `utility` VARCHAR(45) NULL DEFAULT NULL COMMENT 'The plant\'s function in the garden, typically vegetable, ornamental, weed.',
  `hardiness` VARCHAR(45) NULL DEFAULT NULL COMMENT 'The plant\'s hardiness or tenderness, typically hardy, half hardy, tender.',
  `lifeType` VARCHAR(45) NULL DEFAULT NULL COMMENT 'Typically annual, biennial or perennial.  Variations such as \'perennial grown as annual\' (e.g. runner bean).',
  `plantType` VARCHAR(45) NULL DEFAULT NULL COMMENT 'Such as climber, shrub, tree.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`plantVarietyId`),
  UNIQUE INDEX `uq_plantVariety` (`plantSpeciesId` ASC, `commonName` ASC) ,
  INDEX `fk_plantVariety_plantSpecies_idx` (`plantSpeciesId` ASC) ,
  CONSTRAINT `fk_plantVariety_plantSpecies`
    FOREIGN KEY (`plantSpeciesId`)
    REFERENCES `plantspecies` (`plantSpeciesId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'A particular variety of a plant species.<BR>  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.<BR>\\nNote the use of a Trigger to reflect changes to commonName into Product.';

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `afflictionevent`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `afflictionevent` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `afflictionevent` (
  `afflictionEventId` INT NOT NULL AUTO_INCREMENT,
  `afflictionId` INT NOT NULL,
  `plantSpeciesId` INT NULL DEFAULT NULL COMMENT 'The problem occured on plants of this species.\\n@apiNote\\nIf present, plantVarietyId may or may not be given.  This \'denormalises\' the model but enables easy searches for e.g. \'all tomatoes\'',
  `plantVarietyId` INT NULL DEFAULT NULL COMMENT 'The problem occured on plants of this variety.\\n@apiNote\\nIf present, plantSpeciesId must be given.  This \'denormalises\' the model but enables easy searches for e.g. \'all tomatoes\'',
  `locationId` INT NULL DEFAULT NULL COMMENT 'Where the affliction happened.',
  `date` DATE NOT NULL COMMENT 'When an outbreak is spotted or there is some development',
  `quantity` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Quantifies the Affliction event, for instance, the number of plants affected.',
  `severity` VARCHAR(255) NULL DEFAULT NULL COMMENT 'The severity of the outbreak, e.g. \'mild\', \'severe\'.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`afflictionEventId`),
  INDEX `fk_afflictionEvent_plantVariety1_idx` (`plantVarietyId` ASC) ,
  INDEX `fk_afflictionEvent_affliction1_idx` (`afflictionId` ASC) ,
  INDEX `fk_AfflictionEvent_PlantSpecies1_idx` (`plantSpeciesId` ASC) ,
  INDEX `fk_AfflictionEvent_Location1_idx` (`locationId` ASC) ,
  CONSTRAINT `fk_afflictionEvent_affliction1`
    FOREIGN KEY (`afflictionId`)
    REFERENCES `affliction` (`afflictionId`),
  CONSTRAINT `fk_AfflictionEvent_Location1`
    FOREIGN KEY (`locationId`)
    REFERENCES `location` (`locationId`),
  CONSTRAINT `fk_AfflictionEvent_PlantSpecies1`
    FOREIGN KEY (`plantSpeciesId`)
    REFERENCES `plantspecies` (`plantSpeciesId`),
  CONSTRAINT `fk_afflictionEvent_plantVariety1`
    FOREIGN KEY (`plantVarietyId`)
    REFERENCES `plantvariety` (`plantVarietyId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'Records an observation of an affliction - for instance, an outbreak of red spider mite';

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `comment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `comment` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `comment` (
  `commentId` INT NOT NULL AUTO_INCREMENT,
  `ownerId` INT NOT NULL,
  `ownerType` CHAR(2) NOT NULL COMMENT 'The comment is associated with a DB record of this type.\\nPC product category\\nBR product brand\\nPR product\\nSU supplier (retailer)\\nPI purchase item\\nPU purchase\\nSL shopping list\\nPS plant species\\nPV plant variety\\nPN plant note\\nPY plant synonym (no longer used)\\nHU husbandry\\nHC husbandry class\\nGA groundwork activity\\nGW groundwork\\nAC affliction class\\nAF affliction\\nAE affliction event\\nWC weather condition\\nWE weather\\nWD weather detail\\nWS wildlife species\\nWI wildlife\\nRM reminder\\nTD to-do list\\nRW review\\nJN journal\\nLN location\\nRG crop rotation group\\nRS crop group sequence\\nCP cropping plan\\nTH story line (thread)(no longer used)\\n',
  `date` DATE NOT NULL,
  `comment` VARCHAR(1024) NOT NULL,
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`commentId`),
  INDEX `ix_byOwner` (`ownerId` ASC, `ownerType` ASC) COMMENT '\'Comments are always retrieved with their owner (they are only meaningful as part of the owner) so this index facilitates the join\'' )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'A comment on a particular entry.  Often the main content of a husbandry item.';

SHOW WARNINGS;


-- -----------------------------------------------------
-- Table `groundworkactivity`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `groundworkactivity` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `groundworkactivity` (
  `groundWorkActivityId` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL DEFAULT NULL,
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`groundWorkActivityId`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'A type of groundwork activity.';

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `groundwork`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `groundwork` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `groundwork` (
  `groundWorkId` INT NOT NULL AUTO_INCREMENT,
  `activityId` INT NOT NULL,
  `plantSpeciesId` INT NULL DEFAULT NULL COMMENT 'The activity is for plants of this species.\\n@apiNote\\nIf present, plantVarietyId may or may not be given.  This \'denormalises\' the model but enables easy searches for e.g. \'all tomatoes\'',
  `plantVarietyId` INT NULL DEFAULT NULL COMMENT 'The activity is for plants of this variety.\\n@apiNote\\nIf present, plantSpeciesId must be given.  This \'denormalises\' the model but enables easy searches for e.g. \'all tomatoes\'',
  `locationId` INT NULL DEFAULT NULL COMMENT 'Where the Groundwork was done.',
  `date` DATE NOT NULL,
  `quantity` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Quantifies the amount of work, e.g. \'two trenches dug\'.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`groundWorkId`),
  INDEX `fk_groundWork_Activity_idx` (`activityId` ASC) ,
  INDEX `fk_groundWork_plantVariety1_idx` (`plantVarietyId` ASC) ,
  INDEX `fk_Groundwork_PlantSpecies1_idx` (`plantSpeciesId` ASC) ,
  INDEX `fk_Groundwork_Location1_idx` (`locationId` ASC) ,
  CONSTRAINT `fk_groundWork_Activity`
    FOREIGN KEY (`activityId`)
    REFERENCES `groundworkactivity` (`groundWorkActivityId`),
  CONSTRAINT `fk_Groundwork_Location1`
    FOREIGN KEY (`locationId`)
    REFERENCES `location` (`locationId`),
  CONSTRAINT `fk_Groundwork_PlantSpecies1`
    FOREIGN KEY (`plantSpeciesId`)
    REFERENCES `plantspecies` (`plantSpeciesId`),
  CONSTRAINT `fk_groundWork_plantVariety1`
    FOREIGN KEY (`plantVarietyId`)
    REFERENCES `plantvariety` (`plantVarietyId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'An instance of groundwork being done.';

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `husbandryclass`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `husbandryclass` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `husbandryclass` (
  `husbandryClassId` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL DEFAULT NULL,
  `terminal` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'If true, this represents an end of life event.<BR>\\nFor instance, death, discarded, abandoned, failed.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`husbandryClassId`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'The various types of diary entry for the husbandry lifecycle.';

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `husbandry`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `husbandry` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `husbandry` (
  `husbandryId` INT NOT NULL AUTO_INCREMENT,
  `husbandryClassId` INT NOT NULL,
  `plantSpeciesId` INT NOT NULL COMMENT 'Always required as Husbandry always refers to a plant.\\nThe activity is for plants of this species.\\n@apiNote\\nplantVarietyId may or may not be given; if absent the activity is for all (current) e.g. tomato varieties, e.g for spraying.\\nThis \'denormalises\' the model but enables easy searches for e.g. \'all tomatoes\'',
  `plantVarietyId` INT NULL DEFAULT NULL COMMENT 'The activity is for plants of this variety.\\n@apiNote\\nIf present, plantSpeciesId must be given.  This \'denormalises\' the model but enables easy searches for e.g. \'all tomatoes\'',
  `locationId` INT NULL DEFAULT NULL COMMENT 'Where the activity was carried out.',
  `terminalAfflictionId` INT NULL DEFAULT NULL COMMENT '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) NULL DEFAULT NULL COMMENT 'Quantifies the activity, e.g. number of plants potted on.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`husbandryId`),
  INDEX `fk_husbandry_plantVariety1_idx` (`plantVarietyId` ASC) ,
  INDEX `fk_husbandry_husbandryClass1_idx` (`husbandryClassId` ASC) ,
  INDEX `fk_Husbandry_PlantSpecies1_idx` (`plantSpeciesId` ASC) ,
  INDEX `fk_Husbandry_Affliction1_idx` (`terminalAfflictionId` ASC) ,
  INDEX `fk_Husbandry_Location1_idx` (`locationId` ASC) ,
  CONSTRAINT `fk_Husbandry_Affliction1`
    FOREIGN KEY (`terminalAfflictionId`)
    REFERENCES `affliction` (`afflictionId`),
  CONSTRAINT `fk_husbandry_husbandryClass1`
    FOREIGN KEY (`husbandryClassId`)
    REFERENCES `husbandryclass` (`husbandryClassId`),
  CONSTRAINT `fk_Husbandry_Location1`
    FOREIGN KEY (`locationId`)
    REFERENCES `location` (`locationId`),
  CONSTRAINT `fk_Husbandry_PlantSpecies1`
    FOREIGN KEY (`plantSpeciesId`)
    REFERENCES `plantspecies` (`plantSpeciesId`),
  CONSTRAINT `fk_husbandry_plantVariety1`
    FOREIGN KEY (`plantVarietyId`)
    REFERENCES `plantvariety` (`plantVarietyId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'Diary entries for events in the life of a crop.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `journal` (
  `journalId` INT NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL DEFAULT (CURRENT_DATE) COMMENT 'Date of the Journal entry',
  `title` VARCHAR(255) NOT NULL COMMENT 'A brief title for the journal entry.',
  `description` VARCHAR(1024) NULL DEFAULT NULL COMMENT 'The full text of the entry.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`journalId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'A Diary entry which is not specific to any plant, groundwork, affliction, etc - just a free hand note.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `plantnote` (
  `plantNoteId` INT NOT NULL AUTO_INCREMENT,
  `plantSpeciesId` INT NOT NULL COMMENT 'This note is for a plant species.  Individual varieties may have their own notes as well as or instead of the variety\'s notes.',
  `plantVarietyId` INT NULL DEFAULT NULL COMMENT 'If given, plantSpeciesId must also be given; this allows easy retrieval of notes for a species and any variety specific notes.',
  `title` VARCHAR(45) NOT NULL,
  `note` MEDIUMTEXT NULL DEFAULT NULL,
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`plantNoteId`),
  UNIQUE INDEX `plantNoteId_UNIQUE` (`plantNoteId` ASC) ,
  INDEX `fk_PlantNote_PlantSpecies1_idx` (`plantSpeciesId` ASC) ,
  INDEX `fk_PlantNote_PlantVariety1_idx` (`plantVarietyId` ASC) ,
  CONSTRAINT `fk_PlantNote_PlantSpecies1`
    FOREIGN KEY (`plantSpeciesId`)
    REFERENCES `plantspecies` (`plantSpeciesId`),
  CONSTRAINT `fk_PlantNote_PlantVariety1`
    FOREIGN KEY (`plantVarietyId`)
    REFERENCES `plantvariety` (`plantVarietyId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'Various types of notes, such as cultivation notes, too long for or not best organised as comments.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `retailer` (
  `retailerId` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL DEFAULT NULL,
  `address` VARCHAR(255) NULL DEFAULT NULL,
  `webSite` VARCHAR(255) NULL DEFAULT NULL,
  `eMail` VARCHAR(255) NULL DEFAULT NULL,
  `phone` VARCHAR(255) NULL DEFAULT NULL,
  `mobile` VARCHAR(255) NULL DEFAULT NULL,
  `ownBrandOnly` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'If true, this retailer ONLY supplies Products of their own brand',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`retailerId`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'Anywhere that items are purchased, includes garden centres, supermarkets, online shopping, etc, etc.<BR>(A more obvious name would be Supplier but that causes a potential name clash in Java)';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `productbrand` (
  `productBrandId` INT NOT NULL AUTO_INCREMENT,
  `ownBrandRetailerId` INT NULL DEFAULT NULL COMMENT 'If this Brand is a retailer\'s \'own brand\'.',
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL DEFAULT NULL,
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`productBrandId`),
  UNIQUE INDEX `productBrandId_UNIQUE` (`productBrandId` ASC) ,
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) ,
  INDEX `fk_ProductBrand_Retailer1_idx` (`ownBrandRetailerId` ASC) ,
  CONSTRAINT `fk_ProductBrand_Retailer1`
    FOREIGN KEY (`ownBrandRetailerId`)
    REFERENCES `retailer` (`retailerId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'A brand for one or more Products - such as Verve ({@literal B&Q} own brand)';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `productcategory` (
  `productCategoryId` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL DEFAULT NULL,
  `plantLike` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'True if this category is some sort of plant - tubers, seeds, etc.\\n@apiNote\\nThis is a denormalisation to simplify Product selection when adding PurchaseItems',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`productCategoryId`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'Categories of purchased products.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `product` (
  `productId` INT NOT NULL AUTO_INCREMENT,
  `productCategoryId` INT NOT NULL,
  `plantSpeciesId` INT NULL DEFAULT NULL COMMENT 'The product is plants of this species.\\n@apiNote\\nIf present, plantVarietyId may or may not be given.  This \'denormalises\' the model but enables easy searches for e.g. \'all tomatoes\'',
  `plantVarietyId` INT NULL DEFAULT NULL COMMENT 'The product is plants of this species.\\n@apiNote\\nIf present, plantSpeciesId must be given.  This \'denormalises\' the model but enables easy searches for e.g. \'all tomatoes\'',
  `productBrandId` INT NULL DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL COMMENT 'For plant like products (seeds, etc) the plant species common name.<BR>\\n<B>This is unreliable - editing the plant species might not change the value here!</B>',
  `nameDetail_1` VARCHAR(255) NULL DEFAULT NULL COMMENT 'For plant-like products (seeds, etc) the plant variety (if present) common name<BR>\\n<B>This is unreliable - editing the plant variety might not change the value here!</B>',
  `nameDetail_2` VARCHAR(255) NULL DEFAULT NULL,
  `description` VARCHAR(255) NULL DEFAULT NULL,
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`productId`),
  UNIQUE INDEX `ix_unique_name` (`productBrandId` ASC, `name` ASC, `nameDetail_1` ASC, `nameDetail_2` ASC) ,
  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) ,
  CONSTRAINT `fk_Product_PlantSpecies1`
    FOREIGN KEY (`plantSpeciesId`)
    REFERENCES `plantspecies` (`plantSpeciesId`),
  CONSTRAINT `fk_product_plantVariety1`
    FOREIGN KEY (`plantVarietyId`)
    REFERENCES `plantvariety` (`plantVarietyId`),
  CONSTRAINT `fk_Product_ProductBrand1`
    FOREIGN KEY (`productBrandId`)
    REFERENCES `productbrand` (`productBrandId`),
  CONSTRAINT `fk_product_productCategory1`
    FOREIGN KEY (`productCategoryId`)
    REFERENCES `productcategory` (`productCategoryId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'A purchasable product.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `purchase` (
  `purchaseId` INT NOT NULL AUTO_INCREMENT,
  `retailerId` INT NOT NULL,
  `date` DATE NOT NULL,
  `totalCost` DECIMAL(5,2) NULL DEFAULT NULL,
  `currency` CHAR(3) NULL DEFAULT NULL COMMENT 'ISO 4217 standard currency code (GBP, USD, EUR, etc).  Null means the local currency.',
  `orderNo` VARCHAR(255) NULL DEFAULT NULL,
  `invoiceNo` VARCHAR(255) NULL DEFAULT NULL,
  `deliveryDate` DATE NULL DEFAULT NULL,
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`purchaseId`),
  INDEX `fk_purchase_supplier1_idx` (`retailerId` ASC) ,
  CONSTRAINT `fk_purchase_supplier1`
    FOREIGN KEY (`retailerId`)
    REFERENCES `retailer` (`retailerId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'Description of a purchase from a retailer.  The items purchased are recorded as PurchaseItems.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `purchaseitem` (
  `purchaseItemId` INT NOT NULL AUTO_INCREMENT,
  `purchaseId` INT NOT NULL,
  `productId` INT NOT NULL,
  `quantity` DECIMAL(8,3) NULL DEFAULT NULL,
  `unit` VARCHAR(255) NULL DEFAULT NULL,
  `itemCost` DECIMAL(5,2) NULL DEFAULT NULL,
  `currency` CHAR(3) NULL DEFAULT NULL COMMENT 'ISO 4217 standard currency code (GBP, USD, EUR, etc).  Null means the local currency.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`purchaseItemId`),
  INDEX `fk_purchaseItem_purchase1_idx` (`purchaseId` ASC) ,
  INDEX `fk_purchaseItem_product1_idx` (`productId` ASC) ,
  CONSTRAINT `fk_purchaseItem_product1`
    FOREIGN KEY (`productId`)
    REFERENCES `product` (`productId`),
  CONSTRAINT `fk_purchaseItem_purchase1`
    FOREIGN KEY (`purchaseId`)
    REFERENCES `purchase` (`purchaseId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'An item in a Purchase.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `reminder` (
  `reminderId` INT NOT NULL AUTO_INCREMENT,
  `plantSpeciesId` INT NULL DEFAULT NULL COMMENT 'The reminder is for an activity on plants of this species.\\n@apiNote\\nIf present, plantVarietyId may or may not be given.',
  `plantVarietyId` INT NULL DEFAULT NULL COMMENT 'The reminder is for an activity on plants of this variety.\\n@apiNote\\nIf present, plantSpeciesId must be given.',
  `husbandryClassId` INT NULL DEFAULT NULL COMMENT 'The reminder is to perform this activity.',
  `groundWorkActivityId` INT NULL DEFAULT NULL COMMENT 'The reminder is to perform this activity.',
  `husbandryId` INT NULL DEFAULT NULL COMMENT '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\').<BR>\\nThis 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.\\nNormally, 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 COMMENT '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 forsowing\').<BR>\\nThis 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.\\nNormally, 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 COMMENT '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` TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'If true, the Reminder is only activated once.',
  `repeatInterval` CHAR(1) NULL DEFAULT 'W' COMMENT 'For a repeating reminder, the unit of the repeat interval (see repeatQuantifier).<UL>\\n<LI>D - daily\\n<LI>W - weekly\\n<LI>M - monthly\\n<LI>Y - annually\\n</UL>\\nA value is required for repeating reminders so set a default for safety',
  `repeatQuantifier` INT NULL DEFAULT '0' COMMENT 'For a repeating Reminder, the number of repeatIntervals between repeats (see repeatInterval).<BR>\\nSo \'weekly\' and \'2\' means every fortnight.  0 means no repetition (avoids complications with nullable ints in the code - an impossible combination!)\\nA value is required for repeating reminders so set non-null with a default for safety',
  `repeatUntil` DATE NULL DEFAULT NULL COMMENT 'For a repeating reminder, the LAST time this reminder will be shown.<BR>\\nNull means \'repeat forever\'.',
  `referenceDate` DATE NULL DEFAULT NULL COMMENT 'This date will be shown on the ToDo list as a reference back to the earliest parent, usually a Husbandry and typically a sow event.',
  `reminderFlag` VARCHAR(2) NULL DEFAULT 'W' COMMENT 'Marks the ToDo as either a \'Watch for\' (W) or a \'Reminder\' (R)',
  `description` VARCHAR(1024) NULL DEFAULT NULL COMMENT 'The actual text of the reminder.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`reminderId`),
  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) ,
  CONSTRAINT `fk_Reminder_GroundworkActivity1`
    FOREIGN KEY (`groundWorkActivityId`)
    REFERENCES `groundworkactivity` (`groundWorkActivityId`),
  CONSTRAINT `fk_Reminder_Husbandry1`
    FOREIGN KEY (`husbandryId`)
    REFERENCES `husbandry` (`husbandryId`),
  CONSTRAINT `fk_Reminder_HusbandryClass1`
    FOREIGN KEY (`husbandryClassId`)
    REFERENCES `husbandryclass` (`husbandryClassId`),
  CONSTRAINT `fk_Reminder_PlantSpecies1`
    FOREIGN KEY (`plantSpeciesId`)
    REFERENCES `plantspecies` (`plantSpeciesId`),
  CONSTRAINT `fk_Reminder_PlantVariety1`
    FOREIGN KEY (`plantVarietyId`)
    REFERENCES `plantvariety` (`plantVarietyId`),
  CONSTRAINT `fk_Reminder_PurchaseItem1`
    FOREIGN KEY (`purchaseItemId`)
    REFERENCES `purchaseitem` (`purchaseItemId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'A list of reminders of things to do. <BR>Reminders can be \'one-off\' or repeated at various intervals (e.g. \'sow lettuce in March\' shown every year.).';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `retailerhasproduct` (
  `retailerHasProductId` INT NOT NULL AUTO_INCREMENT,
  `retailerId` INT NOT NULL,
  `productId` INT NOT NULL,
  `SKU` VARCHAR(255) NULL DEFAULT NULL COMMENT 'The product code for a particular product at a particular supplier',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`retailerHasProductId`),
  INDEX `fk_supplier_has_product_product1_idx` (`productId` ASC) ,
  INDEX `fk_supplier_has_product_supplier1_idx` (`retailerId` ASC) ,
  CONSTRAINT `fk_supplier_has_product_product1`
    FOREIGN KEY (`productId`)
    REFERENCES `product` (`productId`),
  CONSTRAINT `fk_supplier_has_product_supplier1`
    FOREIGN KEY (`retailerId`)
    REFERENCES `retailer` (`retailerId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'Implements the many-many link between retailers and products.  Many products, particularly own-brand products will only have a single supplier, others may be available from many retailers.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `review` (
  `reviewId` INT NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL DEFAULT (CURRENT_DATE) COMMENT 'Date the Review was written.',
  `yearInReview` YEAR NOT NULL DEFAULT (YEAR(CURRENT_DATE)) COMMENT 'The calendar period covered by this review.',
  `coverFrom` DATE NULL DEFAULT NULL COMMENT 'The start date of the review period',
  `coverTo` DATE NULL DEFAULT NULL COMMENT 'The end date of the review period',
  `title` VARCHAR(255) NOT NULL COMMENT 'A brief title for the review.',
  `description` MEDIUMTEXT NULL DEFAULT NULL COMMENT 'The full text of the review.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`reviewId`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'A Diary entry to review the performance of a crop, affliction (or its treatment) or a location - just a free hand note.';


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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `reviewreferences` (
  `reviewReferencesId` INT NOT NULL AUTO_INCREMENT,
  `reviewId` INT NOT NULL,
  `plantSpeciesId` INT NULL COMMENT 'The species of plant being reviewed',
  `plantVarietyId` INT NULL COMMENT 'The variety of plant being reviewed.  If present the corresponding plantSpeciesId must be present.',
  `husbandryClassId` INT NULL COMMENT 'The husbandry (life cycle) activity being reviewed',
  `groundWorkActivityId` INT NULL COMMENT 'The type of groundwork activity being reviewd.',
  `afflictionId` INT NULL COMMENT 'The affliction (and/or its treatment) being reviewed.',
  `locationId` INT NULL COMMENT 'The Review concerns this location.',
  `productId` INT NULL COMMENT 'The Product being reviewed.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`reviewReferencesId`),
  INDEX `fk_ReviewReferences_Review1_idx` (`reviewId` ASC) ,
  INDEX `fk_ReviewReferences_PlantSpecies1_idx` (`plantSpeciesId` ASC) ,
  INDEX `fk_ReviewReferences_PlantVariety1_idx` (`plantVarietyId` ASC) ,
  INDEX `fk_ReviewReferences_HusbandryClass1_idx` (`husbandryClassId` ASC) ,
  INDEX `fk_ReviewReferences_GroundworkActivity1_idx` (`groundWorkActivityId` ASC) ,
  INDEX `fk_ReviewReferences_Affliction1_idx` (`afflictionId` ASC) ,
  INDEX `fk_ReviewReferences_Location1_idx` (`locationId` ASC) ,
  INDEX `fk_ReviewReferences_Product1_idx` (`productId` ASC) ,
  CONSTRAINT `fk_ReviewReferences_Review1`
    FOREIGN KEY (`reviewId`)
    REFERENCES `review` (`reviewId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ReviewReferences_PlantSpecies1`
    FOREIGN KEY (`plantSpeciesId`)
    REFERENCES `plantspecies` (`plantSpeciesId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ReviewReferences_PlantVariety1`
    FOREIGN KEY (`plantVarietyId`)
    REFERENCES `plantvariety` (`plantVarietyId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ReviewReferences_HusbandryClass1`
    FOREIGN KEY (`husbandryClassId`)
    REFERENCES `husbandryclass` (`husbandryClassId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ReviewReferences_GroundworkActivity1`
    FOREIGN KEY (`groundWorkActivityId`)
    REFERENCES `groundworkactivity` (`groundWorkActivityId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ReviewReferences_Affliction1`
    FOREIGN KEY (`afflictionId`)
    REFERENCES `affliction` (`afflictionId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ReviewReferences_Location1`
    FOREIGN KEY (`locationId`)
    REFERENCES `location` (`locationId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ReviewReferences_Product1`
    FOREIGN KEY (`productId`)
    REFERENCES `product` (`productId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'A Review will typically refer to more than one plant species or variety, or pest or Husbandry, etc.  This table holds those multiple references.  Each entry may contain 1 or many of the key values (i.e. the data may be compressed into fewer rows than expected).  Note that if PlantVariety is given, the corresponding PlantSpecies MUST be given.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `sale` (
  `saleId` INT NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL,
  `purchasedBy` VARCHAR(255) NULL DEFAULT NULL,
  `totalCost` DECIMAL(5,2) NULL DEFAULT NULL,
  `currency` CHAR(3) NULL DEFAULT NULL COMMENT 'ISO 4217 standard currency code (GBP, USD, EUR, etc).  Null means the local currency.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`saleId`),
  UNIQUE INDEX `saleId_UNIQUE` (`saleId` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'A sale of produce.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `saleitem` (
  `saleItemId` INT NOT NULL AUTO_INCREMENT,
  `saleId` INT NOT NULL,
  `plantSpeciesId` INT NOT NULL,
  `plantVarietyId` INT NULL DEFAULT NULL,
  `quantity` DECIMAL(8,3) NULL DEFAULT NULL,
  `unit` VARCHAR(255) NULL DEFAULT NULL,
  `itemCost` DECIMAL(5,2) NULL DEFAULT NULL,
  `currency` CHAR(3) NULL DEFAULT NULL COMMENT 'ISO 4217 standard currency code (GBP, USD, EUR, etc).  Null means the local currency.',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`saleItemId`),
  UNIQUE INDEX `saleItemId_UNIQUE` (`saleItemId` ASC) ,
  INDEX `fk_SaleItem_Sale1_idx` (`saleId` ASC) ,
  INDEX `fk_SaleItem_PlantSpecies1_idx` (`plantSpeciesId` ASC) ,
  INDEX `fk_SaleItem_PlantVariety1_idx` (`plantVarietyId` ASC) ,
  CONSTRAINT `fk_SaleItem_PlantSpecies1`
    FOREIGN KEY (`plantSpeciesId`)
    REFERENCES `plantspecies` (`plantSpeciesId`),
  CONSTRAINT `fk_SaleItem_PlantVariety1`
    FOREIGN KEY (`plantVarietyId`)
    REFERENCES `plantvariety` (`plantVarietyId`),
  CONSTRAINT `fk_SaleItem_Sale1`
    FOREIGN KEY (`saleId`)
    REFERENCES `sale` (`saleId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'An item in a sale of produce.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `shoppinglist` (
  `shoppingListId` INT NOT NULL AUTO_INCREMENT,
  `productId` INT NULL DEFAULT NULL COMMENT 'A specific product, e.g. \'Jeyes Fluid 1l\'',
  `nonspecificItem` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Any item which is not an identified product - e.g. \'celery seed\'',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`shoppingListId`),
  INDEX `fk_ShoppingList_Product1_idx` (`productId` ASC) ,
  CONSTRAINT `fk_ShoppingList_Product1`
    FOREIGN KEY (`productId`)
    REFERENCES `product` (`productId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'A list of items which need to be acquired.';

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `storylineindex`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `storylineindex` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `storylineindex` (
  `storyLineIndexId` INT NOT NULL AUTO_INCREMENT,
  `ancestorId` INT NOT NULL COMMENT 'The database key of the ancestor',
  `ancestorType` CHAR(2) NOT NULL COMMENT 'The type of the ancestor.\\n<UL>\\n<LI>AE - affliction event\\n<LI>HU - husbandry\\n<LI>GW - groundwork\\n<LI>PU - purchase\\n<LI>PI - purchase item\\n</UL>',
  `descendantId` INT NOT NULL COMMENT 'The database key of the descendant',
  `descendantType` CHAR(2) NOT NULL COMMENT 'The type of the descendant.\\n<UL>\\n<LI>AE - affliction event\\n<LI>HU - husbandry\\n<LI>GW - groundwork\\n<LI>SA - sale\\n<LI>SI - sale item\\n</UL>',
  `depth` INT NOT NULL DEFAULT '0' COMMENT 'How far down the tree the descendent node is compared to the ancestor',
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`storyLineIndexId`),
  INDEX `ancestorId` (`ancestorId` ASC, `ancestorType` ASC) ,
  INDEX `descendantId` (`descendantId` ASC, `descendantType` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'This is the \'closure table\' for the storyline (thread) hierarchy.<BR>\\nGiven a tree structure<BR> \\n{@code A->B->C, A->D}<BR>\\nthere is an entry for each link, chased all the way back up:<BR>\\n(A,A,0) (A,B,1) (B,B,0) (A,C,2) (C,C,0) (B,C,1),<BR>\\n(A,D,1) (D,D,0)<BR>\\nIt\'s easy to get the id of every ancestor or descendant of a given node.<BR>\\nThe self-referencing nodes (A,A) make it easy to insert new nodes (single SQL rather than several bits).<BR>\\nMultiple parents are supported (cf a BoM structure).<BR>\\n\\nFor appropriate SQL see, e.g.,<BR>\\nhttps://www.slideshare.net/billkarwin/models-for-hierarchical-data\\n(slide 41 et seq)<BR>\\nand also<BR>\\nhttps://gist.github.com/ekillaby/2377806\\nand also<BR>\\nhttp://karwin.blogspot.co.uk/2010/03/rendering-trees-with-closure-tables.html\\n';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `todolist` (
  `toDoListId` INT NOT NULL AUTO_INCREMENT,
  `plantSpeciesId` INT NULL DEFAULT NULL,
  `plantVarietyId` INT NULL DEFAULT NULL,
  `husbandryClassId` INT NULL DEFAULT NULL,
  `groundWorkActivityId` INT NULL DEFAULT NULL,
  `husbandryId` INT NULL DEFAULT NULL COMMENT '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\').<BR>\\nThis 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.',
  `purchaseItemId` INT NULL DEFAULT NULL COMMENT 'Used for a \'watch for\' item from a Purchase (e.g. seed purchase will have a \'sow\' watch-for',
  `referenceDate` DATE NULL DEFAULT NULL COMMENT 'This date will be shown on the ToDo list as a reference back to the earliest parent, usually a Husbandry and typically a sow event.',
  `targetDate` DATE NULL DEFAULT NULL COMMENT 'When the \'Wait for\' is expected to happen or the Reminder became active.',
  `reminderFlag` VARCHAR(2) NULL DEFAULT 'W' COMMENT 'Marks the ToDo as either a \'Watch for\' (W) or a \'Reminder\' (R)',
  `description` VARCHAR(255) NULL DEFAULT NULL,
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`toDoListId`),
  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) ,
  CONSTRAINT `fk_table1ToDoList_GroundworkActivity1`
    FOREIGN KEY (`groundWorkActivityId`)
    REFERENCES `groundworkactivity` (`groundWorkActivityId`),
  CONSTRAINT `fk_ToDoList_Husbandry1`
    FOREIGN KEY (`husbandryId`)
    REFERENCES `husbandry` (`husbandryId`),
  CONSTRAINT `fk_ToDoList_HusbandryClass1`
    FOREIGN KEY (`husbandryClassId`)
    REFERENCES `husbandryclass` (`husbandryClassId`),
  CONSTRAINT `fk_ToDoList_PlantSpecies1`
    FOREIGN KEY (`plantSpeciesId`)
    REFERENCES `plantspecies` (`plantSpeciesId`),
  CONSTRAINT `fk_ToDoList_PlantVariety1`
    FOREIGN KEY (`plantVarietyId`)
    REFERENCES `plantvariety` (`plantVarietyId`),
  CONSTRAINT `fk_ToDoList_PurchaseItem1`
    FOREIGN KEY (`purchaseItemId`)
    REFERENCES `purchaseitem` (`purchaseItemId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'A list of tasks to be done.  Reminders are scheduled, possibly repeating and will create items here when they are due.<BR>\\n@apiNote\\nThis list is not user updated - users create reminders which generate todo items';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `weathercondition` (
  `weatherConditionId` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL COMMENT '@apiNote\\n\'condition\' is a reserved word in MySQL',
  `description` VARCHAR(255) NULL DEFAULT NULL,
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`weatherConditionId`),
  UNIQUE INDEX `type_UNIQUE` (`name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'The types of weather observed as noteworthy.  For daily observations, use the Weather Details table.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `weather` (
  `weatherId` INT NOT NULL AUTO_INCREMENT,
  `weatherConditionId` INT NOT NULL,
  `date` DATE NOT NULL,
  `severity` VARCHAR(255) NULL DEFAULT NULL,
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`weatherId`),
  INDEX `fk_Weather_WeatherType1_idx` (`weatherConditionId` ASC) ,
  CONSTRAINT `fk_Weather_WeatherType1`
    FOREIGN KEY (`weatherConditionId`)
    REFERENCES `weathercondition` (`weatherConditionId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'A noteworthy weather event.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `weatherdetail` (
  `weatherDetailId` INT NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL COMMENT 'Date of the Weather Detail being recorded\\nIf timeFrom and timeTo are absent, this is a summary for the whole day',
  `timeAt` TIME NULL DEFAULT NULL COMMENT 'The observation is a spot value at this time on the given date',
  `timeFrom` TIME NULL DEFAULT NULL COMMENT 'The Weather Detail spans a period from this time to timeTo\\nIf timeFrom and timeTo are not present the Detail is for the whole day unless timeAt is given\\n',
  `timeTo` TIME NULL DEFAULT NULL COMMENT 'The Weather Detail spans a period from timeFrom to this time.\\nIf timeFrom and timeTo are not present the Detail is for the whole day unless timeAt is given',
  `temperatureMax` FLOAT NULL DEFAULT NULL,
  `temperatureMin` FLOAT NULL DEFAULT NULL,
  `temperatureScale` CHAR(2) NULL DEFAULT 'C' COMMENT 'F - Fahrenheit\\nC - Celsius',
  `pressureMax` FLOAT NULL DEFAULT NULL COMMENT 'Maximum air pressure recorded in the period covered',
  `pressureMin` FLOAT NULL DEFAULT NULL COMMENT 'Minimum air pressure recorded in the period covered',
  `pressureAvg` FLOAT NULL DEFAULT NULL COMMENT 'Average air pressure recorded in the period covered',
  `pressureScale` CHAR(5) NULL DEFAULT 'mb' COMMENT 'mb - milli Bar\\nhPa - standard unit, exactly equivalent to mb\\ninHg - inches of mercury',
  `humidityMax` FLOAT NULL DEFAULT NULL COMMENT 'Maximum relative humidity as a percentage during the period covered',
  `humidityMin` FLOAT NULL DEFAULT NULL COMMENT 'Minimum relative humidity as a percentage during the period covered',
  `humidityAvg` FLOAT NULL DEFAULT NULL COMMENT 'Average relative humidity as a percentage during the period covered',
  `windMax` FLOAT NULL DEFAULT NULL,
  `windAvg` FLOAT NULL DEFAULT NULL,
  `windMin` FLOAT NULL DEFAULT NULL,
  `windGust` FLOAT NULL DEFAULT NULL,
  `windScale` CHAR(2) NULL DEFAULT 'M' COMMENT 'B - Beaufort scale\\nM - Miles per hour\\nK - km per hour',
  `rainFall` FLOAT NULL DEFAULT NULL COMMENT 'Rain fall for this period, in the units specified',
  `rainScale` CHAR(3) NULL DEFAULT NULL COMMENT 'in - inches\\nmm - millimetres\\ncm - centimetres',
  `sunshine` FLOAT NULL DEFAULT NULL COMMENT 'Hours',
  `cloudCover` INT NULL DEFAULT NULL,
  `cloudCoverUnit` CHAR(3) NULL DEFAULT '8' COMMENT 'How the cloud cover is measured\\n8 - eighths\\nPC - percent',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`weatherDetailId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'Detailed measurements of the weather, possibly many per day.';

SHOW WARNINGS;

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

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `wildlifespecies` (
  `wildlifeSpeciesId` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Grouping for wildlife observations, completely informal (unlike the plant catalogue)',
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`wildlifeSpeciesId`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'Grouping for wildlife observations.  Completely informal, e.g. \'insect\', \'bee\', \'bird\'.';

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `wildlife`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `wildlife` ;

SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `wildlife` (
  `wildlifeId` INT NOT NULL AUTO_INCREMENT,
  `wildlifeSpeciesId` INT NOT NULL,
  `locationId` INT NULL DEFAULT NULL COMMENT 'Where the observation took place',
  `date` DATE NOT NULL,
  `variety` VARCHAR(255) NULL DEFAULT NULL,
  `lastUpdated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`wildlifeId`),
  INDEX `fk_Wildlife_WildlifeSpecies1_idx` (`wildlifeSpeciesId` ASC),
  INDEX `fk_Wildlife_Location1_idx` (`locationId` ASC),
  CONSTRAINT `fk_Wildlife_Location1`
    FOREIGN KEY (`locationId`)
    REFERENCES `location` (`locationId`),
  CONSTRAINT `fk_Wildlife_WildlifeSpecies1`
    FOREIGN KEY (`wildlifeSpeciesId`)
    REFERENCES `wildlifespecies` (`wildlifeSpeciesId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
-- COLLATE = utf8mb4_0900_ai_ci
COMMENT = 'A noteworthy observation of wildlife - an interesting event, unusual species, etc';

SHOW WARNINGS;
-- USE `gardennotebook` ;

-- -----------------------------------------------------
-- procedure LoadTodoList
-- -----------------------------------------------------

USE `gardennotebook`;
DROP procedure IF EXISTS `LoadTodoList`;
SHOW WARNINGS;

DELIMITER $$
USE `gardennotebook`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `LoadTodoList`()
begin
create temporary table idlist as 
	select reminderId from reminder
		where  curdate() >= 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 * from idlist);

delete from reminder where reminderId in (select * from idlist) and  singleShot=1;

update reminder set showFrom = adddate(showFrom, interval repeatQuantifier DAY) where reminderId in  (select * from idlist) and repeatInterval="D";     
update reminder set showFrom = adddate(showFrom, interval repeatQuantifier WEEK) where reminderId in  (select * from idlist) and repeatInterval="W";
update reminder set showFrom = adddate(showFrom, interval repeatQuantifier MONTH) where reminderId in  (select * from idlist) and repeatInterval="M";
update reminder set showFrom = adddate(showFrom, interval repeatQuantifier YEAR) where reminderId in  (select * from idlist) and repeatInterval="Y";
delete from reminder where showFrom > repeatUntil;
     
drop temporary table idlist;
end$$

DELIMITER ;
SHOW WARNINGS;
USE `gardennotebook`;

DELIMITER $$

USE `gardennotebook`$$
DROP TRIGGER IF EXISTS `PlantSpecies_AFTER_UPDATE` $$
SHOW WARNINGS$$
USE `gardennotebook`$$
CREATE
TRIGGER `gardennotebook`.`PlantSpecies_AFTER_UPDATE`
AFTER UPDATE ON `plantspecies`
FOR EACH ROW
BEGIN
if new.commonName != old.commonName
then
	update product set product.name = new.commonName where product.plantSpeciesId = new.plantSpeciesId;
end if;
END$$

SHOW WARNINGS$$

USE `gardennotebook`$$
DROP TRIGGER IF EXISTS `PlantVariety_AFTER_UPDATE` $$
SHOW WARNINGS$$
USE `gardennotebook`$$
CREATE
TRIGGER `gardennotebook`.`PlantVariety_AFTER_UPDATE`
AFTER UPDATE ON `plantvariety`
FOR EACH ROW
BEGIN
if new.commonName != old.commonName
then
	update product set product.nameDetail_1 = new.commonName where product.plantSpeciesId = new.plantSpeciesId
				and product.plantVarietyId = new.plantVarietyId;
end if;

END$$

SHOW WARNINGS$$

DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
