CREATE TABLE `hops_analysis` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  `checked` tinyint(1) NOT NULL DEFAULT 0,
  `hop` int(11) unsigned NOT NULL COMMENT 'Hopfensorte|Hopfen für den die Analyse gilt',
  `source` varchar(255) NOT NULL COMMENT 'Quelle|Quelle der Analyse',
  `link` varchar(255) NOT NULL COMMENT 'Link|Link zum Analysebericht',
  `date` date DEFAULT NULL COMMENT 'Datum|Datum der Analyse',
  `alpha_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'α-Säure min.|Minimaler Alphasäuregehalt in %',
  `alpha_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'α-Säure max.|Maximaler Alphasäuregehalt in %',
  `alpha` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `alpha_max` is null then `alpha_min` else round((`alpha_min` + `alpha_max`) / 2,1) end) STORED COMMENT 'α-Säure ⌀|Mittlerer Alphasäuregehalt in %',
  `beta_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'β-Säure min.|Minimaler Betasäuregehalt in %',
  `beta_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'β-Säure max.|Maximaler Betasäuregehalt in %',
  `beta` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `beta_max` is null then `beta_min` else round((`beta_min` + `beta_max`) / 2,1) end) STORED COMMENT 'β-Säure ⌀|Mittlerer Betasäuregehalt in %',
  `cohumulon_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Cohumulon min.|Minimaler Cohumulongehalt in %',
  `cohumulon_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Cohumulon max.|Maximaler Cohumulongehalt in %',
  `cohumulon` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `cohumulon_max` is null then `cohumulon_min` else round((`cohumulon_min` + `cohumulon_max`) / 2,1) end) STORED COMMENT 'Cohumulon ⌀|Mittlerer Cohumulongehalt in %',
  `colupulon_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Colupulon min.|Minimaler Colupulongehalt in %',
  `colupulon_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Colupulon max.|Maximaler Colupulongehalt in %',
  `colupulon` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `colupulon_max` is null then `colupulon_min` else round((`colupulon_min` + `colupulon_max`) / 2,1) end) STORED COMMENT 'Colupulon ⌀|Mittlerer Colupulongehalt in %',
  `polyphenol_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Polyphenol min.|Minimaler Polyphenolgehalt in %',
  `polyphenol_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Polyphenol max.|Maximaler Polyphenolgehalt in %',
  `polyphenol` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `polyphenol_max` is null then `polyphenol_min` else round((`polyphenol_min` + `polyphenol_max`) / 2,1) end) STORED COMMENT 'Polyphenol ⌀|Mittlerer Polyphenolgehalt in %',
  `xanthohumol_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Xanthohumol min.|Minimaler Xanthohumolgehalt in %',
  `xanthohumol_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Xanthohumol max.|Maximaler Xanthohumolgehalt in %',
  `xanthohumol` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `xanthohumol_max` is null then `xanthohumol_min` else round((`xanthohumol_min` + `xanthohumol_max`) / 2,1) end) STORED COMMENT 'Xanthohumol ⌀|Mittlerer Xanthohumolgehalt in %',
  `oil_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Aromaöl min.|Minimaler Aromaölgehalt in %',
  `oil_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Aromaöl max.|Maximaler Aromaölgehalt in %',
  `oil` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `oil_max` is null then `oil_min` else round((`oil_min` + `oil_max`) / 2,1) end) STORED COMMENT 'Aromaöl ⌀|Mittlerer Aromaölgehalt in %',
  `myrcen_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Myrcen min.|Minimaler Anteil des Myrcens am Aromaöl in %',
  `myrcen_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Myrcen max.|Maximaler Anteil des Myrcens am Aromaöl in %',
  `myrcen` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `myrcen_max` is null then `myrcen_min` else round((`myrcen_min` + `myrcen_max`) / 2,1) end) STORED COMMENT 'Myrcen ⌀|Mittlerer Anteil des Myrcens am Aromaöl in %',
  `linalool_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Linalool min.|Minimaler Anteil des Linalools am Aromaöl in %',
  `linalool_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Linalool max.|Maximaler Anteil des Linalools am Aromaöl in %',
  `linalool` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `linalool_max` is null then `linalool_min` else round((`linalool_min` + `linalool_max`) / 2,1) end) STORED COMMENT 'Linalool ⌀|Mittlerer Anteil des Linalools am Aromaöl in %',
  `caryophyllen_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Caryophyllen min.|Minimaler Anteil des Caryophyllens am Aromaöl in %',
  `caryophyllen_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Caryophyllen max.|Maximaler Anteil des Caryophyllens am Aromaöl in %',
  `caryophyllen` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `caryophyllen_max` is null then `caryophyllen_min` else round((`caryophyllen_min` + `caryophyllen_max`) / 2,1) end) STORED COMMENT 'Caryophyllen ⌀|Mittlerer Anteil des Caryophyllens am Aromaöl in %',
  `aromadendren_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Aromadendren min.|Minimaler Anteil des Aromadendrens am Aromaöl in %',
  `aromadendren_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Aromadendren max.|Maximaler Anteil des Aromadendrens am Aromaöl in %',
  `aromadendren` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `aromadendren_max` is null then `aromadendren_min` else round((`aromadendren_min` + `aromadendren_max`) / 2,1) end) STORED COMMENT 'Aromadendren ⌀|Mittlerer Anteil des Aromadendrens am Aromaöl in %',
  `humulen_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Humulen min.|Minimaler Anteil des Humulens am Aromaöl in %',
  `humulen_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Humulen max.|Maximaler Anteil des Humulens am Aromaöl in %',
  `humulen` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `humulen_max` is null then `humulen_min` else round((`humulen_min` + `humulen_max`) / 2,1) end) STORED COMMENT 'Humulen ⌀|Mittlerer Anteil des Humulens am Aromaöl in %',
  `farnesen_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Farnesen min.|Minimaler Anteil des Farnesens am Aromaöl in %',
  `farnesen_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Farnesen max.|Maximaler Anteil des Farnesens am Aromaöl in %',
  `farnesen` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `farnesen_max` is null then `farnesen_min` else round((`farnesen_min` + `farnesen_max`) / 2,1) end) STORED COMMENT 'Farnesen ⌀|Mittlerer Anteil des Farnesens am Aromaöl in %',
  `betaselinen_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Betaselinen min.|Minimaler Anteil des Betaselinens am Aromaöl in %',
  `betaselinen_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Betaselinen max.|Maximaler Anteil des Betaselinens am Aromaöl in %',
  `betaselinen` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `betaselinen_max` is null then `betaselinen_min` else round((`betaselinen_min` + `betaselinen_max`) / 2,1) end) STORED COMMENT 'Betaselinen ⌀|Mittlerer Anteil des Betaselinens am Aromaöl in %',
  `alphaselinen_min` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Alphaselinen min.|Minimaler Anteil des Alphaselinens am Aromaöl in %',
  `alphaselinen_max` decimal(4,1) unsigned DEFAULT NULL COMMENT 'Alphaselinen max.|Maximaler Anteil des Alphaselinens am Aromaöl in %',
  `alphaselinen` decimal(4,1) unsigned GENERATED ALWAYS AS (case when `alphaselinen_max` is null then `alphaselinen_min` else round((`alphaselinen_min` + `alphaselinen_max`) / 2,1) end) STORED COMMENT 'Alphaselinen ⌀|Mittlerer Anteil des Alphaselinens am Aromaöl in %',
  `loss` decimal(4,1) unsigned DEFAULT NULL COMMENT '6-Monats-Verlust|Verlust an Alphasäure nach 6 Monaten Lagerung in %',
  `notes` text DEFAULT NULL COMMENT 'Kommentar|Weitere Informationen zur Analyse',
  PRIMARY KEY (`id`),
  KEY `index_hop_id` (`hop`),
  KEY `index_checked` (`checked`),
  CONSTRAINT `hops_analysis_ibfk_1` FOREIGN KEY (`hop`) REFERENCES `hops` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=361 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci COMMENT='Daten von Hopfen-Analysen'