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

CREATE MEMORY TABLE IF NOT EXISTS afflictionclass (
  afflictionClassId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (afflictionClassId),
  UNIQUE (name) );
COMMENT ON TABLE afflictionclass IS 'Groups the types of affliction - pest, disease, death';
COMMENT ON COLUMN afflictionclass.afflictionClassId IS 'pest, disease or death';


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

CREATE MEMORY TABLE IF NOT EXISTS affliction (
  afflictionId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  afflictionClassId INT DEFAULT NULL,
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (afflictionId),
  UNIQUE (name),
  CONSTRAINT fk_affliction_afflictionClass1
    FOREIGN KEY (afflictionClassId)
    REFERENCES afflictionclass (afflictionClassId));

CREATE INDEX fk_affliction_afflictionClass1_idx ON affliction (afflictionClassId);

COMMENT ON TABLE affliction IS 'These are the various afflictions observed.';
COMMENT ON COLUMN affliction.afflictionClassId IS 'An actual type of pest, disease or death, e.g. lily beetle, blight.'
	'U&\000D\000A'
	'Note that this is optional - I think it''s too restrictive to force users to create AfflictionClasses before they can specify an actual Affliction.  There''s no option to show ''all AfflictionEvents caused by a pest'', for instance.  Having this field mandatory also requires AfflictionClass combos on Husbandry and AfflictionEvent editors.';

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

CREATE MEMORY TABLE IF NOT EXISTS location (
  locationId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  parentLocationId INT DEFAULT NULL ,
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  underCover BOOLEAN DEFAULT 'FALSE' NOT NULL,
  geometry VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (locationId),
  UNIQUE (name),
  CONSTRAINT fk_Location_Location1
    FOREIGN KEY (parentLocationId)
    REFERENCES location (locationId));

CREATE INDEX fk_Location_Location1_idx ON location (parentLocationId);

COMMENT ON TABLE location IS '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.';
COMMENT ON COLUMN location.parentLocationId IS 'The current Location is considered to be part of the parent Location.  The relationship may be logical, rather than physical.';
COMMENT ON COLUMN location.name IS 'Name of the location.';
COMMENT ON COLUMN location.description IS'A brief description of the Location';
COMMENT ON COLUMN location.underCover IS 'Is this location under cover (e.g. a greenhouse) or outside.';
COMMENT ON COLUMN location.geometry IS '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.';

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

CREATE MEMORY TABLE IF NOT EXISTS croprotationgroup (
  cropRotationGroupId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  subsequentCropGroupId INT DEFAULT NULL,
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (cropRotationGroupId),
  UNIQUE (name),
  UNIQUE (subsequentCropGroupId),
  CONSTRAINT fk_CropRotationGroup_CropRotationGroup1
    FOREIGN KEY (subsequentCropGroupId)
    REFERENCES CropRotationGroup (cropRotationGroupId)
);

COMMENT ON TABLE croprotationgroup IS '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 by crops in group ''brassicas''.';
COMMENT ON COLUMN croprotationgroup.name IS 'The name of a crop group for rotation purposes, e.g. ''brassicas''.'
COMMENT ON COLUMN croprotationgroup.description IS 'A description of the crops in this group.';
COMMENT ON COLUMN croprotationgroup.subsequentCropGroupId IS 'The CropRotationGroup which is grown AFTER this CropRotationGroup.\nFor instance, this = legumes, subsequent = brassicas';

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

CREATE MEMORY TABLE IF NOT EXISTS croppingplan (
  croppingPlanId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  cropRotationGroupId INT NOT NULL,
  locationId INT NOT NULL,
  yearOfPlan DATE DEFAULT CURRENT_DATE NOT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (croppingPlanId),
  UNIQUE (yearOfPlan, locationId, cropRotationGroupId),
  CONSTRAINT fk_CroppingPlan_CropRotationGroup1
    FOREIGN KEY (cropRotationGroupId)
    REFERENCES croprotationgroup (cropRotationGroupId),
  CONSTRAINT fk_CroppingPlan_Location1
    FOREIGN KEY (locationId)
    REFERENCES location (locationId));

CREATE INDEX fk_CroppingPlan_CropRotationGroup1_idx ON croppingplan (cropRotationGroupId);
CREATE INDEX fk_CroppingPlan_Location1_idx ON croppingplan (locationId);


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

CREATE MEMORY TABLE IF NOT EXISTS plantspecies (
  plantSpeciesId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  cropRotationGroupId INT DEFAULT NULL,
  commonName VARCHAR(255) NOT NULL,
  latinName VARCHAR(255) DEFAULT NULL ,
  description VARCHAR(255) DEFAULT NULL,
  utility VARCHAR(45) DEFAULT NULL,
  hardiness VARCHAR(45) DEFAULT NULL,
  lifeType VARCHAR(45) DEFAULT NULL,
  plantType VARCHAR(45) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (plantSpeciesId),
  UNIQUE (commonName),
  CONSTRAINT fk_PlantSpecies_CropRotationGroup1
    FOREIGN KEY (cropRotationGroupId)
    REFERENCES croprotationgroup (cropRotationGroupId)
);

CREATE INDEX fk_PlantSpecies_CropRotationGroup1_idx ON plantspecies (cropRotationGroupId);

COMMENT ON TABLE plantspecies IS 'A species of plant grown in the garden, for instance, ''tomato''.  A species will have zero or more varieties.'
	'U&\000D\000A'
	'Note the use of a Trigger to reflect changes to commonName into Product.';
COMMENT ON COLUMN plantspecies.commonName IS 'The name by which plants of this type are usually known.';
COMMENT ON COLUMN plantspecies.latinName IS 'The formal horticultural name for this species.'
	'  The common name is language specific, the latin name is internationally standardised.';
COMMENT ON COLUMN plantspecies.utility IS 'The plant''s function in the garden, typically vegetable, ornamental, weed.';
COMMENT ON COLUMN plantspecies.hardiness IS 'The plant''s hardiness or tenderness, typically hardy, half hardy, tender.';
COMMENT ON COLUMN plantspecies.lifeType IS 'Typically annual, biennial or perennial.  Variations such as ''perennial grown as annual'' (e.g. runner bean).';
COMMENT ON COLUMN plantspecies.plantType IS 'Such as climber, shrub, tree.';

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

CREATE MEMORY TABLE IF NOT EXISTS plantvariety (
  plantVarietyId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  plantSpeciesId INT NOT NULL,
  commonName VARCHAR(255) NOT NULL,
  latinName VARCHAR(255) DEFAULT NULL,
  synonymSet VARCHAR(255) DEFAULT NULL,
  description VARCHAR(255) DEFAULT NULL,
  utility VARCHAR(45) DEFAULT NULL,
  hardiness VARCHAR(45) DEFAULT NULL,
  lifeType VARCHAR(45) DEFAULT NULL,
  plantType VARCHAR(45) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (plantVarietyId),
  UNIQUE (plantSpeciesId, commonName),
  CONSTRAINT fk_plantVariety_plantSpecies
    FOREIGN KEY (plantSpeciesId)
    REFERENCES plantspecies (plantSpeciesId));

CREATE INDEX fk_plantVariety_plantSpecies_idx ON plantvariety (plantSpeciesId);

COMMENT ON TABLE plantvariety IS '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.'
	'U&\000D\000A'
	'Note the use of a Trigger to reflect changes to commonName into Product.';
COMMENT ON COLUMN plantvariety.commonName IS 'The usual name for this variety.  The values in this column are NOT unique';
COMMENT ON COLUMN plantvariety.synonymSet IS  'This implements the concept of synonyms.'
	'U&\000D\000A'
	'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.'
	'U&\000D\000A'
	'To get a PV and all its synonyms:'
	'U&\000D\000A'
	'select p2.* from PV p1, PV p2 where p1.id in (...) and (p2.id = p1.id or p2.synonymSet = p1.synonymSet) and p1.PSid = p2.PSidn'
	'U&\000D\000A'
	'this relies on the synonymSet being null if NOT in a synonymSet'
	'U&\000D\000A'
	'Note 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!';
COMMENT ON COLUMN plantvariety.latinName IS 'The formal horticultural name for this variety.  Rarely different from the species'' latin name.';
COMMENT ON COLUMN plantvariety.utility IS 'The plant''s function in the garden, typically vegetable, ornamental, weed.';
COMMENT ON COLUMN plantvariety.hardiness IS 'The plant''s hardiness or tenderness, typically hardy, half hardy, tender.';
COMMENT ON COLUMN plantvariety.lifeType IS 'Typically annual, biennial or perennial.  Variations such as ''perennial grown as annual'' (e.g. runner bean).';
COMMENT ON COLUMN plantvariety.plantType IS 'Such as climber, shrub, tree.';

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

CREATE MEMORY TABLE IF NOT EXISTS croppingactual (
  croppingActualId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  croppingPlanId INT NOT NULL,
  plantSpeciesId INT NOT NULL,
  plantVarietyId INT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (croppingActualId),
  CONSTRAINT fk_CroppingActual_CroppingPlan1
	FOREIGN KEY (croppingPlanId)
	REFERENCES croppingplan (croppingPlanId),
  CONSTRAINT fk_CroppingActual_PlantSpecies1
	FOREIGN KEY (plantSpeciesId)
	REFERENCES plantspecies (plantSpeciesId),
  CONSTRAINT fk_CroppingActual_PlantVariety1
	FOREIGN KEY (plantVarietyId)
	REFERENCES plantvariety (plantVarietyId)
);

CREATE INDEX fk_CroppingActual_CroppingPlan1_idx ON croppingactual (croppingPlanId);
CREATE INDEX fk_CroppingActual_PlantSpecies1_idx ON croppingactual (plantSpeciesId);
CREATE INDEX fk_CroppingActual_PlantVariety1_idx ON croppingactual (plantVarietyId);

COMMENT ON TABLE croppingactual IS 'The plants actually grown to implement the referenced CroppingPlan';
COMMENT ON COLUMN  croppingactual.croppingPlanId IS 'The plants identified were grown as part of this CroppingPlan';
COMMENT ON COLUMN  croppingactual.plantSpeciesId IS 'The PlantSpecies grown';
COMMENT ON COLUMN  croppingactual.plantVarietyId IS 'The variety of the PlantSpecies grown.  If this value is present, plantSpeciesId MUST be present (this denormalises the database design but simplifies searching)';

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

CREATE MEMORY TABLE IF NOT EXISTS afflictionevent (
  afflictionEventId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  afflictionId INT NOT NULL,
  plantSpeciesId INT DEFAULT NULL,
  plantVarietyId INT DEFAULT NULL,
  locationId INT DEFAULT NULL,
  date DATE NOT NULL,
  quantity VARCHAR(255) DEFAULT NULL,
  severity VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (afflictionEventId),
  CONSTRAINT fk_AfflictionEvent_PlantSpecies1
    FOREIGN KEY (plantSpeciesId)
    REFERENCES plantspecies (plantSpeciesId),
  CONSTRAINT fk_afflictionEvent_affliction1
    FOREIGN KEY (afflictionId)
    REFERENCES affliction (afflictionId),
  CONSTRAINT fk_afflictionEvent_plantVariety1
    FOREIGN KEY (plantVarietyId)
    REFERENCES plantvariety (plantVarietyId),
  CONSTRAINT fk_AfflictionEvent_Location1
    FOREIGN KEY (locationId)
    REFERENCES location (locationId)
);

CREATE INDEX fk_AfflictionEvent_PlantSpecies1_idx ON afflictionevent (plantSpeciesId);
CREATE INDEX fk_afflictionEvent_plantVariety1_idx ON afflictionevent (plantVarietyId);
CREATE INDEX fk_afflictionEvent_affliction1_idx ON afflictionevent (afflictionId);
CREATE INDEX fk_AfflictionEvent_Location1_idx ON afflictionevent (locationId);

COMMENT ON TABLE afflictionevent IS 'Records an observation of an affliction - for instance, an outbreak of red spider mite';
COMMENT ON COLUMN afflictionevent.plantSpeciesId IS 'The problem occured on plants of this species.'
	'  If present, plantVarietyId may or may not be given.  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes''';
COMMENT ON COLUMN afflictionevent.plantVarietyId IS 'The problem occured on plants of this variety.'
	'  If present, plantSpeciesId must be given.  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes''';
COMMENT ON COLUMN afflictionEvent.locationId IS 'Where the affliction happened.'
COMMENT ON COLUMN afflictionevent.date IS 'When an outbreak is spotted or there is some development';
COMMENT ON COLUMN afflictionevent.quantity IS 'Quantifies the Affliction event, for instance, the number of plants affected.';
COMMENT ON COLUMN afflictionevent.severity IS 'The severity of the outbreak, e.g. ''mild'', ''severe''.';


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

CREATE MEMORY TABLE IF NOT EXISTS comment (
  commentId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  ownerId INT NOT NULL,
  ownerType CHAR(2) NOT NULL,
  date DATE NOT NULL,
  comment VARCHAR(1024) NOT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (commentId)
);

CREATE INDEX ix_byOwner ON comment (ownerId, ownerType);
-- COMMENT ON INDEX ix_byOwner IS 'Comments are always retrieved with their owner (they are only meaningful as part of the owner) so this index facilitates the join');

COMMENT ON TABLE comment IS 'A comment on a particular entry.  Often the main content of a husbandry item.';
COMMENT ON COLUMN comment.ownerType IS 'The comment is associated with a DB record of this type:'
	'U&\000D\000A' 'PC product category'
	'U&\000D\000A' 'BR product brand'
	'U&\000D\000A' 'PR product\'
	'U&\000D\000A' 'SU supplier (retailer)'
	'U&\000D\000A' 'PI purchase item'
	'U&\000D\000A' 'PU purchase'
	'U&\000D\000A' 'SL shopping list'
	'U&\000D\000A' 'PS plant species'
	'U&\000D\000A' 'PV plant variety'
	'U&\000D\000A' 'PN plant note'
	'U&\000D\000A' 'PY plant synonym (no longer used)'
	'U&\000D\000A' 'HU husbandry'
	'U&\000D\000A' 'HC husbandry class'
	'U&\000D\000A' 'GA groundwork activity'
	'U&\000D\000A' 'GW groundwork'
	'U&\000D\000A' 'AC affliction class'
	'U&\000D\000A' 'AF affliction'
	'U&\000D\000A' 'AE affliction event'
	'U&\000D\000A' 'WC weather condition'
	'U&\000D\000A' 'WE weather'
	'U&\000D\000A' 'WD weather detail'
	'U&\000D\000A' 'WS wildlife species'
	'U&\000D\000A' 'WI wildlife'
	'U&\000D\000A' 'RM reminder'
	'U&\000D\000A' 'TD to-do list'
	'U&\000D\000A' 'RW review'
	'U&\000D\000A' 'JN journal'
	'U&\000D\000A' 'LN location'
	'U&\000D\000A' 'RG crop rotation group'
	'U&\000D\000A' 'CP cropping plan'
	'U&\000D\000A' 'TH story line (thread)(no longer used)';


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

CREATE MEMORY TABLE IF NOT EXISTS groundworkactivity (
  groundWorkActivityId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (groundWorkActivityId),
  UNIQUE (name)
);

COMMENT ON TABLE groundworkactivity IS 'A type of groundwork activity.';

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

CREATE MEMORY TABLE IF NOT EXISTS groundwork (
  groundWorkId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  activityId INT NOT NULL,
  plantSpeciesId INT DEFAULT NULL,
  plantVarietyId INT DEFAULT NULL,
  locationId INT DEFAULT NULL,
  date DATE NOT NULL,
  quantity VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (groundWorkId),
  CONSTRAINT fk_Groundwork_PlantSpecies1
    FOREIGN KEY (plantSpeciesId)
    REFERENCES plantspecies (plantSpeciesId),
  CONSTRAINT fk_groundWork_Activity
    FOREIGN KEY (activityId)
    REFERENCES groundworkactivity (groundWorkActivityId),
  CONSTRAINT fk_groundWork_plantVariety1
    FOREIGN KEY (plantVarietyId)
    REFERENCES plantvariety (plantVarietyId),
  CONSTRAINT fk_Groundwork_Location1
    FOREIGN KEY (locationId)
    REFERENCES location (locationId)
);


CREATE INDEX fk_groundWork_Activity_idx ON groundwork (activityId);
CREATE INDEX fk_groundWork_plantVariety1_idx ON groundwork (plantVarietyId);
CREATE INDEX fk_Groundwork_PlantSpecies1_idx ON groundwork (plantSpeciesId);

COMMENT ON TABLE groundwork IS 'An instance of groundwork being done.';
COMMENT ON COLUMN groundwork.plantSpeciesId IS 'The activity is for plants of this species.'
	'  If present, plantVarietyId may or may not be given.  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes''.';
COMMENT ON COLUMN groundwork.plantVarietyId IS 'The activity is for plants of this variety.'
	'  If present, plantSpeciesId must be given.  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes''.';
COMMENT ON COLUMN groundwork.locationId IS 'Where the Groundwork was done.';
COMMENT ON COLUMN groundwork.quantity IS 'Quantifies the amount of work, e.g. ''two trenches dug''.'


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

CREATE MEMORY TABLE IF NOT EXISTS husbandryclass (
  husbandryClassId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  terminal BOOLEAN DEFAULT 'FALSE' NOT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (husbandryClassId),
  UNIQUE (name)
);

COMMENT ON TABLE husbandryclass IS 'The various types of diary entry for the husbandry lifecycle.';
COMMENT ON COLUMN husbandryclass.terminal IS 'If true, this represents an end of life event.  For instance, death, discarded, abandoned, failed.';

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

CREATE MEMORY TABLE IF NOT EXISTS husbandry (
  husbandryId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  husbandryClassId INT NOT NULL,
  plantSpeciesId INT NOT NULL,
  plantVarietyId INT DEFAULT NULL,
  locationId INT DEFAULT NULL,
  terminalAfflictionId INT DEFAULT NULL,
  date DATE NOT NULL,
  quantity VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (husbandryId),
  CONSTRAINT fk_Husbandry_Affliction1
    FOREIGN KEY (terminalAfflictionId)
    REFERENCES affliction (afflictionId),
  CONSTRAINT fk_Husbandry_PlantSpecies1
    FOREIGN KEY (plantSpeciesId)
    REFERENCES plantspecies (plantSpeciesId),
  CONSTRAINT fk_husbandry_husbandryClass1
    FOREIGN KEY (husbandryClassId)
    REFERENCES husbandryclass (husbandryClassId),
  CONSTRAINT fk_husbandry_plantVariety1
    FOREIGN KEY (plantVarietyId)
    REFERENCES plantvariety (plantVarietyId),
  CONSTRAINT fk_Husbandry_Location1
    FOREIGN KEY (locationId)
    REFERENCES location (locationId)
);

CREATE INDEX fk_Husbandry_PlantSpecies1_idx ON husbandry (plantSpeciesId);
CREATE INDEX fk_husbandry_plantVariety1_idx ON husbandry (plantVarietyId);
CREATE INDEX fk_husbandry_husbandryClass1_idx ON husbandry (husbandryClassId);
CREATE INDEX fk_Husbandry_Affliction1_idx ON husbandry (terminalAfflictionId);
CREATE INDEX fk_Husbandry_Location1_idx ON husbandry (locationId);

COMMENT ON TABLE husbandry IS 'Diary entries for events in the life of a crop.';
COMMENT ON COLUMN husbandry.plantSpeciesId IS 'Always required as Husbandry always refers to a plant.  The activity is for plants of this species.'
	'  plantVarietyId may or may not be given; if absent the activity is for all (current) e.g. tomato varieties, e.g for spraying.'
	'  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes''.';
COMMENT ON COLUMN husbandry.plantVarietyId IS 'The activity is for plants of this variety.  If present, plantSpeciesId must be given.'
	'  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes''.';
COMMENT ON COLUMN husbandry.terminalAfflictionId IS 'If this Diary entry is for the demise of a plant, this can be used to record the guilty pest or disease.';
COMMENT ON COLUMN husbandry.locationId IS 'Where the activity was carried out.';
COMMENT ON COLUMN husbandry.quantity IS 'Quantifies the activity, e.g. number of plants potted on.'


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

CREATE MEMORY TABLE IF NOT EXISTS journal (
  journalId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  date DATE DEFAULT CURRENT_DATE NOT NULL,
  title VARCHAR(255) NOT NULL,
  description VARCHAR(1024) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (journalId)
);

COMMENT ON TABLE journal IS 'A Diary entry which is not specific to any plant, groundwork, affliction, etc - just a free hand note.';
COMMENT ON COLUMN journal.date IS 'Date of the Journal entry';
COMMENT ON COLUMN journal.title IS 'A brief title for the journal entry.';
COMMENT ON COLUMN journal.description IS 'The full text of the entry.';


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

CREATE MEMORY TABLE IF NOT EXISTS plantnote (
  plantNoteId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  plantSpeciesId INT NOT NULL,
  plantVarietyId INT DEFAULT NULL,
  title VARCHAR(45) NOT NULL,
  note LONGVARCHAR DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (plantNoteId),
  UNIQUE (plantSpeciesId, plantVarietyId, title),
  CONSTRAINT fk_PlantNote_PlantSpecies1
    FOREIGN KEY (plantSpeciesId)
    REFERENCES plantspecies (plantSpeciesId),
  CONSTRAINT fk_PlantNote_PlantVariety1
    FOREIGN KEY (plantVarietyId)
    REFERENCES plantvariety (plantVarietyId)
);

CREATE INDEX fk_PlantNote_PlantSpecies1_idx ON plantnote (plantSpeciesId);
CREATE INDEX fk_PlantNote_PlantVariety1_idx ON plantnote (plantVarietyId);

COMMENT ON TABLE plantnote IS 'Various types of notes, such as cultivation notes, too long for or not best organised as comments.';
COMMENT ON COLUMN plantnote.plantSpeciesId IS 'This note is for a plant species.'
	'  Individual varieties may have their own notes as well as or instead of the variety''s notes.';
COMMENT ON COLUMN plantnote.plantVarietyId IS 'If given, plantSpeciesId must also be given; this allows easy retrieval of notes for a species and any variety specific notes.';

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

CREATE MEMORY TABLE IF NOT EXISTS retailer (
  retailerId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  name VARCHAR(255) NOT NULL,
  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 BOOLEAN DEFAULT 'FALSE' NOT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (retailerId),
  UNIQUE (name)
);

COMMENT ON TABLE retailer IS 'Anywhere that items are purchased, includes garden centres, supermarkets, online shopping, etc, etc.'
	'  (A more obvious name would be Supplier but that causes a potential name clash in Java)';
COMMENT ON COLUMN retailer.ownBrandOnly IS 'If true, this retailer ONLY supplies Products of their own brand';

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

CREATE MEMORY TABLE IF NOT EXISTS productbrand (
  productBrandId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  ownBrandRetailerId INT DEFAULT NULL,
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (productBrandId),
  UNIQUE (name),
  CONSTRAINT fk_ProductBrand_Retailer1
    FOREIGN KEY (ownBrandRetailerId)
    REFERENCES retailer (retailerId)
);

CREATE INDEX fk_ProductBrand_Retailer1_idx ON productbrand (ownBrandRetailerId);

COMMENT ON TABLE productbrand IS 'A brand for one or more Products - such as Verve ({@literal B&Q} own brand)';
COMMENT ON COLUMN productbrand.ownBrandRetailerId IS 'If this Brand is a retailer''s ''own brand''.';

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

CREATE MEMORY TABLE IF NOT EXISTS productcategory (
  productCategoryId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  plantLike BOOLEAN DEFAULT 'FALSE' NOT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (productCategoryId),
  UNIQUE (name)
);

COMMENT ON TABLE productcategory IS 'Categories of purchased products.';
COMMENT ON COLUMN productcategory.plantLike IS 'True if this category is some sort of plant - tubers, seeds, etc.'
	'  This is a denormalisation to simplify Product selection when adding PurchaseItems';

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

CREATE MEMORY TABLE IF NOT EXISTS product (
  productId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  productCategoryId INT NOT NULL,
  plantSpeciesId INT DEFAULT NULL,
  plantVarietyId INT DEFAULT NULL,
  productBrandId INT DEFAULT NULL,
  name VARCHAR(255) NOT NULL,
  nameDetail_1 VARCHAR(255) DEFAULT NULL,
  nameDetail_2 VARCHAR(255) DEFAULT NULL,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (productId),
  UNIQUE (productCategoryId, productBrandId, name, nameDetail_1, nameDetail_2),
  CONSTRAINT fk_Product_PlantSpecies1
    FOREIGN KEY (plantSpeciesId)
    REFERENCES plantspecies (plantSpeciesId),
  CONSTRAINT fk_Product_ProductBrand1
    FOREIGN KEY (productBrandId)
    REFERENCES productbrand (productBrandId),
  CONSTRAINT fk_product_plantVariety1
    FOREIGN KEY (plantVarietyId)
    REFERENCES plantvariety (plantVarietyId),
  CONSTRAINT fk_product_productCategory1
    FOREIGN KEY (productCategoryId)
    REFERENCES productcategory (productCategoryId)
);
-- DEFAULT CHARACTER SET = utf8mb4

CREATE INDEX fk_product_productCategory1_idx ON product (productCategoryId);
CREATE INDEX fk_product_plantVariety1_idx ON product (plantVarietyId);
CREATE INDEX fk_Product_PlantSpecies1_idx ON product (plantSpeciesId);
CREATE INDEX fk_Product_ProductBrand1_idx ON product (productBrandId);

COMMENT ON TABLE product IS 'A purchasable product.';
COMMENT ON COLUMN product.plantSpeciesId IS 'The product is plants of this species.  If present, plantVarietyId may or may not be given.'
	'  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes''.';
COMMENT ON COLUMN product.plantVarietyId IS 'The product is plants of this species.  If present, plantSpeciesId must be given.'
	'  This ''denormalises'' the model but enables easy searches for e.g. ''all tomatoes''.';
COMMENT ON COLUMN product.name IS 'For plant like products (seeds, etc) the plant species common name.'
	'  Note the use of a trigger to maintain this if plantSpecies names are changed';
COMMENT ON COLUMN product.nameDetail_1 IS 'For plant-like products (seeds, etc) the plant variety (if present) common name.'
	'  Note the use of a trigger to maintain this if plantVariety names are changed';
COMMENT ON COLUMN product.nameDetail_2 IS 'Not currently used - supported in most code but NOT in the UI.';

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

CREATE MEMORY TABLE IF NOT EXISTS purchase (
  purchaseId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  retailerId INT NOT NULL,
  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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (purchaseId),
  CONSTRAINT fk_purchase_supplier1
    FOREIGN KEY (retailerId)
    REFERENCES retailer (retailerId)
);

CREATE INDEX fk_purchase_supplier1_idx ON purchase (retailerId);

COMMENT ON TABLE purchase IS 'Description of a purchase from a retailer.  The items purchased are recorded as PurchaseItems.';
COMMENT ON COLUMN purchase.currency IS 'ISO 4217 standard currency code (GBP, USD, EUR, etc).  Null means the local currency.';

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

CREATE MEMORY TABLE IF NOT EXISTS purchaseitem (
  purchaseItemId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  purchaseId INT NOT NULL,
  productId INT NOT NULL,
  quantity DECIMAL(8,3) DEFAULT NULL,
  unit VARCHAR(255) DEFAULT NULL,
  itemCost DECIMAL(5,2) DEFAULT NULL,
  currency CHAR(3) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (purchaseItemId),
  CONSTRAINT fk_purchaseItem_product1
    FOREIGN KEY (productId)
    REFERENCES product (productId),
  CONSTRAINT fk_purchaseItem_purchase1
    FOREIGN KEY (purchaseId)
    REFERENCES purchase (purchaseId)
);

CREATE INDEX fk_purchaseItem_purchase1_idx ON purchaseitem (purchaseId);
CREATE INDEX fk_purchaseItem_product1_idx ON purchaseitem (productId);

COMMENT ON TABLE purchaseitem IS 'An item in a Purchase.';
COMMENT ON COLUMN purchaseitem.currency IS 'ISO 4217 standard currency code (GBP, USD, EUR, etc).  Null means the local currency.';

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

CREATE MEMORY TABLE IF NOT EXISTS reminder (
  reminderId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  plantSpeciesId INT DEFAULT NULL,
  plantVarietyId INT DEFAULT NULL,
  husbandryClassId INT DEFAULT NULL,
  groundWorkActivityId INT DEFAULT NULL,
  husbandryId INT DEFAULT NULL,
  purchaseItemId INT DEFAULT NULL,
  showFrom DATE NOT NULL,
  singleShot BOOLEAN DEFAULT 'TRUE' NOT NULL,
  repeatInterval CHAR(1) DEFAULT 'W',
  repeatQuantifier INT DEFAULT '0',
  repeatUntil DATE DEFAULT NULL,
  referenceDate DATE DEFAULT NULL,
  reminderFlag VARCHAR(2) DEFAULT 'W',
  description VARCHAR(1024) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (reminderId),
  CONSTRAINT fk_Reminder_GroundworkActivity1
    FOREIGN KEY (groundWorkActivityId)
    REFERENCES groundworkactivity (groundWorkActivityId),
  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_Husbandry1
    FOREIGN KEY (husbandryId)
    REFERENCES husbandry (husbandryId),
  CONSTRAINT fk_Reminder_PurchaseItem1
    FOREIGN KEY (purchaseItemId)
    REFERENCES purchaseitem (purchaseItemId)
);

CREATE INDEX fk_Reminder_PlantVariety1_idx ON reminder (plantVarietyId);
CREATE INDEX fk_Reminder_HusbandryClass1_idx ON reminder (husbandryClassId);
CREATE INDEX fk_Reminder_GroundworkActivity1_idx ON reminder (groundWorkActivityId);
CREATE INDEX fk_Reminder_PlantSpecies1_idx ON reminder (plantSpeciesId);
CREATE INDEX fk_Reminder_Husbandry1_idx  ON reminder (husbandryId);
CREATE INDEX fk_Reminder_PurchaseItem1_idx  ON reminder (purchaseItemId);

COMMENT ON TABLE reminder IS 'A list of reminders of things to do. Reminders can be ''one-off'' or repeated at various intervals (e.g. ''sow lettuce in March'' shown every year).';
COMMENT ON COLUMN reminder.plantSpeciesId IS 'The reminder is for an activity on plants of this species.  If present, plantVarietyId may or may not be given.';
COMMENT ON COLUMN reminder.plantVarietyId IS 'The reminder is for an activity on plants of this variety.  If present, plantSpeciesId must be given.';
COMMENT ON COLUMN reminder.husbandryClassId IS 'The reminder is to perform this activity.';
COMMENT ON COLUMN reminder.groundWorkActivityId IS 'The reminder is to perform this activity.';
COMMENT ON COLUMN reminder.husbandryId IS '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'').'
	'U&\000D\000A' '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.'
	'U&\000D\000A'	'Normally, this action will generate a ToDoList entry; this field (and the purchaseItemId field) are to allow that ToDo to be delayed.'

COMMENT ON COLUMN reminder.purchaseItemId IS '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'').'
	'U&\000D\000A'	'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.'
	'U&\000D\000A'	'Normally, this action will generate a ToDoList entry; this field (and the husbandryId field) are to allow that ToDo to be delayed.'

COMMENT ON COLUMN reminder.showFrom IS 'For a single shot reminder, the first date it will be shown on.  For a repeating reminder, the current or next interval when the reminder will be active.';
COMMENT ON COLUMN reminder.singleShot IS 'If true, the Reminder is only activated once.';
COMMENT ON COLUMN reminder.repeatInterval IS 'For a repeating reminder, the unit of the repeat interval (see repeatQuantifier).'
	'U&\000D\000A' 'D - daily'
	'U&\000D\000A' 'W - weekly'
	'U&\000D\000A' 'M - monthly'
	'U&\000D\000A' 'Y - annually'
	'U&\000D\000A' 'A value is required for repeating reminders so set a default for safety';
COMMENT ON COLUMN reminder.repeatQuantifier IS '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';
COMMENT ON COLUMN reminder.repeatUntil IS 'For a repeating reminder, the LAST time this reminder will be shown.  Null means ''repeat forever''.';
COMMENT ON COLUMN reminder.referenceDate IS '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.';
COMMENT ON COLUMN reminder.reminderFlag IS 'Marks the ToDo as either a ''Watch for'' (W) or a ''Reminder'' (R)';
COMMENT ON COLUMN reminder.description IS 'The actual text of the reminder.';

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

CREATE MEMORY TABLE IF NOT EXISTS retailerhasproduct (
  retailerHasProductId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  retailerId INT NOT NULL,
  productId INT NOT NULL,
  SKU VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (retailerHasProductId),
  CONSTRAINT fk_supplier_has_product_product1
    FOREIGN KEY (productId)
    REFERENCES product (productId),
  CONSTRAINT fk_supplier_has_product_supplier1
    FOREIGN KEY (retailerId)
    REFERENCES retailer (retailerId)
);

CREATE INDEX fk_supplier_has_product_product1_idx ON retailerhasproduct (productId);
CREATE INDEX fk_supplier_has_product_supplier1_idx ON retailerhasproduct (retailerId);

COMMENT ON TABLE retailerhasproduct IS '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.';
COMMENT ON COLUMN retailerhasproduct.SKU IS 'The product code for a particular product at a particular supplier';


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

CREATE MEMORY TABLE IF NOT EXISTS review (
  reviewId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  date DATE DEFAULT CURRENT_DATE NOT NULL,
  yearInReview DATE DEFAULT CURRENT_DATE NOT NULL,
  coverFrom DATE DEFAULT NULL,
  coverTo DATE DEFAULT NULL,
  title VARCHAR(255),
  description LONGVARCHAR DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (reviewId)
);

COMMENT ON TABLE review IS 'A Diary entry to review the performance of a crop, affliction (or its treatment) or a location - just a free hand note.';

COMMENT ON COLUMN review.date IS 'Date the Review was written.';
COMMENT ON COLUMN review.yearInReview IS 'The calendar period covered by this review.  Only the YEAR part should be used';
COMMENT ON COLUMN review.coverFrom IS 'The start date of the review period';
COMMENT ON COLUMN review.coverTo IS 'The end date of the review period';
COMMENT ON COLUMN review.title IS 'A brief title for the review.';
COMMENT ON COLUMN review.description IS 'The full text of the review.';

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

CREATE MEMORY TABLE IF NOT EXISTS reviewreferences (
  reviewReferencesId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  reviewId INT NOT NULL,
  plantSpeciesId INT DEFAULT NULL,
  plantVarietyId INT DEFAULT NULL,
  husbandryClassId INT DEFAULT NULL,
  groundWorkActivityId INT DEFAULT NULL,
  afflictionId INT NULL,
  locationId INT DEFAULT NULL,
  productId INT DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (reviewReferencesId),
  CONSTRAINT fk_ReviewReferences_Review1
    FOREIGN KEY (reviewId)
    REFERENCES review (reviewId),
  CONSTRAINT fk_ReviewReferences_PlantSpecies1
    FOREIGN KEY (plantSpeciesId)
    REFERENCES plantspecies (plantSpeciesId),
  CONSTRAINT fk_ReviewReferences_PlantVariety1
    FOREIGN KEY (plantVarietyId)
    REFERENCES plantvariety (plantVarietyId),
  CONSTRAINT fk_ReviewReferences_HusbandryClass1
    FOREIGN KEY (husbandryClassId)
    REFERENCES husbandryclass (husbandryClassId),
  CONSTRAINT fk_ReviewReferences_GroundworkActivity1
    FOREIGN KEY (groundWorkActivityId)
    REFERENCES groundworkactivity (groundWorkActivityId),
  CONSTRAINT fk_ReviewReferences_Affliction1
    FOREIGN KEY (afflictionId)
    REFERENCES affliction (afflictionId),
  CONSTRAINT fk_ReviewReferences_Location1
    FOREIGN KEY (locationId)
    REFERENCES location (locationId),
  CONSTRAINT fk_ReviewReferences_Product1
    FOREIGN KEY (productId)
    REFERENCES product (productId)
);

CREATE INDEX fk_ReviewReferences_Review1_idx ON reviewreferences (reviewId);
CREATE INDEX fk_ReviewReferences_PlantSpecies1_idx ON reviewreferences (plantSpeciesId);
CREATE INDEX fk_ReviewReferences_PlantVariety1_idx ON reviewreferences (plantVarietyId);
CREATE INDEX fk_ReviewReferences_HusbandryClass1_idx ON reviewreferences (husbandryClassId);
CREATE INDEX fk_ReviewReferences_GroundworkActivity1_idx ON reviewreferences (groundWorkActivityId);
CREATE INDEX fk_ReviewReferences_Affliction1_idx ON reviewreferences (afflictionId);
CREATE INDEX fk_ReviewReferences_Location1_idx ON reviewreferences (locationId);
CREATE INDEX fk_ReviewReferences_Product1_idx ON reviewreferences (productId);

COMMENT ON TABLE reviewreferences IS '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.';

COMMENT ON COLUMN reviewreferences.plantSpeciesId IS 'The species of plant being reviewed';
COMMENT ON COLUMN reviewreferences.plantVarietyId IS 'The variety of plant being reviewed.';
COMMENT ON COLUMN reviewreferences.husbandryClassId IS 'The husbandry (life cycle) activity being reviewed';
COMMENT ON COLUMN reviewreferences.groundWorkActivityId IS 'The type of groundwork activity being reviewd.';
COMMENT ON COLUMN reviewreferences.afflictionId IS 'The affliction (and/or its treatment) being reviewed.';
COMMENT ON COLUMN reviewreferences.locationId IS 'The location being reviewed.';


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

CREATE MEMORY TABLE IF NOT EXISTS sale (
  saleId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  date DATE NOT NULL,
  purchasedBy VARCHAR(255) DEFAULT NULL,
  totalCost DECIMAL(5,2) DEFAULT NULL,
  currency CHAR(3) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (saleId)
);

COMMENT ON TABLE sale IS 'A sale of produce.';
COMMENT ON COLUMN sale.currency IS 'ISO 4217 standard currency code (GBP, USD, EUR, etc).  Null means the local currency.';

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

CREATE MEMORY TABLE IF NOT EXISTS saleitem (
  saleItemId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  saleId INT NOT NULL,
  plantSpeciesId INT NOT NULL,
  plantVarietyId INT DEFAULT NULL,
  quantity DECIMAL(8,3) DEFAULT NULL,
  unit VARCHAR(255) DEFAULT NULL,
  itemCost DECIMAL(5,2) DEFAULT NULL,
  currency CHAR(3) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (saleItemId),
  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)
);

CREATE INDEX fk_SaleItem_Sale1_idx ON saleitem (saleId);
CREATE INDEX fk_SaleItem_PlantSpecies1_idx ON saleitem (plantSpeciesId);
CREATE INDEX fk_SaleItem_PlantVariety1_idx ON saleitem (plantVarietyId);

COMMENT ON TABLE saleitem IS 'An item in a sale of produce.';
COMMENT ON COLUMN saleitem.currency IS 'ISO 4217 standard currency code (GBP, USD, EUR, etc).  Null means the local currency.';

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

CREATE MEMORY TABLE IF NOT EXISTS shoppinglist (
  shoppingListId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  productId INT DEFAULT NULL,
  nonspecificItem VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (shoppingListId),
  CONSTRAINT fk_ShoppingList_Product1
    FOREIGN KEY (productId)
    REFERENCES product (productId)
);

CREATE INDEX fk_ShoppingList_Product1_idx ON shoppinglist (productId);

COMMENT ON TABLE shoppinglist IS 'A list of items which need to be acquired.';
COMMENT ON COLUMN shoppinglist.productId IS 'A specific product, e.g. ''Jeyes Fluid 1l''.';
COMMENT ON COLUMN shoppinglist.nonspecificItem  IS 'Any item which is not an identified product - e.g. ''celery seed''.';

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

CREATE MEMORY TABLE IF NOT EXISTS storylineindex (
  storyLineIndexId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (storyLineIndexId)
);

CREATE INDEX ancestorId ON storylineindex (ancestorId, ancestorType);
CREATE INDEX descendantId ON storylineindex (descendantId, descendantType);

COMMENT ON TABLE storylineindex IS 'This is the ''closure table'' for the storyline (thread) hierarchy.'
	'  Given a tree structure'
	'U&\000D\000A' 'A->B->C, A->D'
	'U&\000D\000A' 'there is an entry for each link, chased all the way back up:'
	'U&\000D\000A' '(A,A,0) (A,B,1) (B,B,0) (A,C,2) (C,C,0) (B,C,1),'
	'U&\000D\000A' '(A,D,1) (D,D,0)'
	'U&\000D\000A' 'It''s easy to get the id of every ancestor or descendant of a given node.'
	'U&\000D\000A' 'The self-referencing nodes (A,A) make it easy to insert new nodes (single SQL rather than several bits).'
	'U&\000D\000A' 'Multiple parents are supported (cf a BoM structure).'
	'U&\000D\000A' 'For appropriate SQL see, e.g.,'
	'U&\000D\000A' 'https://www.slideshare.net/billkarwin/models-for-hierarchical-data (slide 41 et seq)'
	'U&\000D\000A' 'and also'
	'U&\000D\000A' 'https://gist.github.com/ekillaby/2377806'
	'U&\000D\000A' 'and also'
	'U&\000D\000A' 'http://karwin.blogspot.co.uk/2010/03/rendering-trees-with-closure-tables.html';
COMMENT ON COLUMN storylineindex.ancestorId IS 'The database key of the ancestor';
COMMENT ON COLUMN storylineindex.ancestorType IS 'The type of the ancestor.'
	'U&\000D\000A' 'AE - affliction event'
	'U&\000D\000A' 'HU - husbandry'
	'U&\000D\000A' 'GW - groundwork'
	'U&\000D\000A' 'PU - purchase'
	'U&\000D\000A' 'PI - purchase item';
COMMENT ON COLUMN storylineindex.descendantId IS 'The database key of the descendant';
COMMENT ON COLUMN storylineindex.descendantType IS 'The type of the ancestor.'
	'U&\000D\000A' 'AE - affliction event'
	'U&\000D\000A' 'HU - husbandry'
	'U&\000D\000A' 'GW - groundwork'
	'U&\000D\000A' 'SA - sale'
	'U&\000D\000A' 'SI - sale item';
COMMENT ON COLUMN storylineindex.depth IS 'How far down the tree the descendent node is compared to the ancestor';

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

CREATE MEMORY TABLE IF NOT EXISTS todolist (
  toDoListId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  plantSpeciesId INT DEFAULT NULL,
  plantVarietyId INT DEFAULT NULL,
  husbandryClassId INT DEFAULT NULL,
  groundWorkActivityId INT DEFAULT NULL,
  husbandryId INT DEFAULT NULL,
  purchaseItemId INT DEFAULT NULL,
  referenceDate DATE DEFAULT NULL,
  targetDate DATE DEFAULT NULL,
  reminderFlag VARCHAR(2) DEFAULT 'W',
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (toDoListId),
  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_table1ToDoList_GroundworkActivity1
    FOREIGN KEY (groundWorkActivityId)
    REFERENCES groundworkactivity (groundWorkActivityId),
  CONSTRAINT fk_ToDoList_PurchaseItem1
    FOREIGN KEY (purchaseItemId)
    REFERENCES purchaseitem (purchaseItemId)
);


CREATE INDEX fk_table1ToDoList_GroundworkActivity1_idx ON todolist (groundWorkActivityId);
CREATE INDEX fk_ToDoList_HusbandryClass1_idx ON todolist (husbandryClassId);
CREATE INDEX fk_ToDoList_PlantVariety1_idx ON todolist (plantVarietyId);
CREATE INDEX fk_ToDoList_PlantSpecies1_idx ON todolist (plantSpeciesId);
CREATE INDEX fk_ToDoList_Husbandry1_idx ON todolist (husbandryId);
CREATE INDEX fk_ToDoList_PurchaseItem1_idx ON todolist (purchaseItemId);

COMMENT ON TABLE todolist IS 'A list of tasks to be done.  Reminders are scheduled, possibly repeating and will create items here when they are due.'
	'  This list is not user updated - users create reminders which generate todo items';
COMMENT ON COLUMN todolist.husbandryId IS '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.';
COMMENT ON COLUMN todolist.purchaseItemId IS 'Used for a ''watch for'' item from a Purchase (e.g. seed purchase will have a ''sow'' watch-for';
COMMENT ON COLUMN todolist.referenceDate IS '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.';
COMMENT ON COLUMN todolist.targetDate IS 'When the ''Wait for'' is expected to happen or the Reminder became active.';
COMMENT ON COLUMN todolist.reminderFlag IS 'Marks the ToDo as either a ''Watch for'' (W) or a ''Reminder'' (R)';


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

CREATE MEMORY TABLE IF NOT EXISTS weathercondition (
  weatherConditionId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (weatherConditionId),
  UNIQUE (name)
);
-- DEFAULT CHARACTER SET = utf8mb4
COMMENT ON TABLE weathercondition IS 'The types of weather observed as noteworthy.  For daily observations, use the Weather Details table.';
COMMENT ON COLUMN weathercondition.name IS '''condition'' is a reserved word in MySQL';

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

CREATE MEMORY TABLE IF NOT EXISTS weather (
  weatherId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  weatherConditionId INT NOT NULL,
  date DATE NOT NULL,
  severity VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (weatherId),
  CONSTRAINT fk_Weather_WeatherType1
    FOREIGN KEY (weatherConditionId)
    REFERENCES weathercondition (weatherConditionId)
);
-- DEFAULT CHARACTER SET = utf8mb4

CREATE INDEX fk_Weather_WeatherType1_idx ON weather (weatherConditionId);

COMMENT ON TABLE weather IS 'A noteworthy weather event.';

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

CREATE MEMORY TABLE IF NOT EXISTS weatherdetail (
  weatherDetailId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (weatherDetailId)
);

COMMENT ON TABLE weatherdetail IS 'Detailed measurements of the weather, possibly many per day.';
COMMENT ON COLUMN weatherdetail.date IS 'Date of the Weather Detail being recorded.'
	'  If timeFrom and timeTo are absent, this is a summary for the whole day';
COMMENT ON COLUMN weatherdetail.timeAt IS 'The observation is a spot value at this time on the given date';
COMMENT ON COLUMN weatherdetail.timeFrom IS 'The Weather Detail spans a period from this time to timeTo.'
	'  If timeFrom and timeTo are not present the Detail is for the whole day unless timeAt is given';
COMMENT ON COLUMN weatherdetail.timeTo IS 'The Weather Detail spans a period from timeFrom to this time.'
	'  If timeFrom and timeTo are not present the Detail is for the whole day unless timeAt is given';
COMMENT ON COLUMN weatherdetail.temperatureScale IS 'F - Fahrenheit, C - Celsius';
COMMENT ON COLUMN weatherdetail.pressureMax IS 'Maximum air pressure recorded in the period covered';
COMMENT ON COLUMN weatherdetail.pressureMin IS 'Minimum air pressure recorded in the period covered';
COMMENT ON COLUMN weatherdetail.pressureAvg IS 'Average air pressure recorded in the period covered';
COMMENT ON COLUMN weatherdetail.pressureScale IS 'mb - milli Bar,'
	' hPa - standard unit, exactly equivalent to mb'
	' inHg - inches of mercury';
COMMENT ON COLUMN weatherdetail.humidityMax IS 'Maximum relative humidity as a percentage during in the period covered';
COMMENT ON COLUMN weatherdetail.humidityMin IS 'Minimum relative humidity as a percentage during in the period covered';
COMMENT ON COLUMN weatherdetail.humidityAvg IS 'Average relative humidity as a percentage during in the period covered';
COMMENT ON COLUMN weatherdetail.windScale IS 'B - Beaufort scale, M - Miles per hour, K - km per hour';
COMMENT ON COLUMN weatherdetail.rainFall IS 'Rain fall for this period, in the units specified';
COMMENT ON COLUMN weatherdetail.rainScale IS 'in - inches, mm - millimetres, cm - centimetres';
COMMENT ON COLUMN weatherdetail.sunshine IS 'Hours';
COMMENT ON COLUMN weatherdetail.cloudCoverUnit IS 'How the cloud cover is measured: 8 - eighths, PC - percent';

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

CREATE MEMORY TABLE IF NOT EXISTS wildlifespecies (
  wildlifeSpeciesId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (wildlifeSpeciesId),
  UNIQUE (name)
);
-- DEFAULT CHARACTER SET = utf8mb4

COMMENT ON TABLE wildlifespecies IS 'Grouping for wildlife observations.  Completely informal, e.g. ''insect'', ''bee'', ''bird''.';
COMMENT ON COLUMN wildlifespecies.description IS 'Grouping for wildlife observations, completely informal (unlike the plant catalogue)';

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

CREATE MEMORY TABLE IF NOT EXISTS wildlife (
  wildlifeId INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
  wildlifeSpeciesId INT NOT NULL,
  locationId INT DEFAULT NULL,
  date DATE NOT NULL,
  variety VARCHAR(255) DEFAULT NULL,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (wildlifeId),
  CONSTRAINT fk_Wildlife_Location1
    FOREIGN KEY (locationId)
    REFERENCES location (locationId),
  CONSTRAINT fk_Wildlife_WildlifeSpecies1
    FOREIGN KEY (wildlifeSpeciesId)
    REFERENCES wildlifespecies (wildlifeSpeciesId)
);

CREATE INDEX fk_Wildlife_WildlifeSpecies1_idx ON wildlife (wildlifeSpeciesId);
CREATE INDEX fk_Wildlife_Location1_idx ON wildlife (locationId);

COMMENT ON TABLE wildlife IS 'A noteworthy observation of wildlife - an interesting event, unusual species, etc';
COMMENT ON COLUMN wildlife.locationId IS 'Where the observation took place';


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

DROP procedure IF EXISTS LoadTodoList;

\.
CREATE PROCEDURE LoadTodoList()
  MODIFIES SQL DATA
  BEGIN ATOMIC
    DECLARE TABLE idlist (reminderId INT);
    INSERT INTO idlist SELECT reminderId FROM reminder WHERE CURRENT_DATE >= 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='TRUE';

    UPDATE reminder SET showFrom = showFrom + (INTERVAL '1' DAY * repeatQuantifier) WHERE reminderId IN (SELECT * FROM idlist) AND repeatInterval='D';     
    UPDATE reminder SET showFrom = showFrom + (INTERVAL '7' DAY * repeatQuantifier) WHERE reminderId IN (SELECT * FROM idlist) AND repeatInterval='W';
    UPDATE reminder SET showFrom = showFrom + (INTERVAL '1' MONTH * repeatQuantifier) WHERE reminderId IN (SELECT * FROM idlist) AND repeatInterval='M';
    UPDATE reminder SET showFrom = showFrom + (INTERVAL '1' YEAR * repeatQuantifier) WHERE reminderId IN (SELECT * FROM idlist) AND repeatInterval='Y';
    DELETE FROM reminder WHERE showFrom > repeatUntil;
     
  END
.;


-- -----------------------------------------------------
-- procedure PlantSpecies_AFTER_UPDATE
-- -----------------------------------------------------

DROP TRIGGER IF EXISTS PlantSpecies_AFTER_UPDATE;

\.
CREATE TRIGGER PlantSpecies_AFTER_UPDATE
  AFTER UPDATE OF commonName ON plantspecies
  REFERENCING OLD ROW AS old1 NEW ROW AS new1
    FOR EACH ROW WHEN (new1.commonName != old1.commonName)
    BEGIN ATOMIC
	UPDATE product SET product.name = new1.commonName WHERE product.plantSpeciesId = new1.plantSpeciesId;
    END
.;


-- -----------------------------------------------------
-- procedure PlantVariety_AFTER_UPDATE
-- -----------------------------------------------------

DROP TRIGGER IF EXISTS PlantVariety_AFTER_UPDATE;

\.
CREATE TRIGGER PlantVariety_AFTER_UPDATE
  AFTER UPDATE OF commonName ON plantvariety
  REFERENCING OLD ROW AS old1 NEW ROW AS new1
    FOR EACH ROW WHEN (new1.commonName != old1.commonName)
    BEGIN ATOMIC
	UPDATE product SET product.nameDetail_1 = new1.commonName WHERE product.plantSpeciesId = new1.plantSpeciesId and
						product.plantVarietyId = new1.plantVarietyId;
    END
.;

SHUTDOWN;
