SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE `antworten` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `frage` int(10) unsigned NOT NULL DEFAULT '0',
  `antwort` varchar(100) NOT NULL DEFAULT '',
  `position` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `frage` (`frage`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1201 ;

CREATE TABLE `fragen` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `konzept` int(10) unsigned DEFAULT NULL,
  `frage` text NOT NULL,
  `erklaerung` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `typ` char(2) DEFAULT NULL,
  `feldname` varchar(50) NOT NULL,
  `position` int(10) unsigned NOT NULL,
  `ko_kriterium_bei` varchar(50) NOT NULL,
  `pflichtfeld` tinyint(1) NOT NULL,
  `onchange` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `ueberkat` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `konzept` (`konzept`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1869 ;

CREATE TABLE `gesellschaften` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(355) COLLATE utf8_unicode_ci NOT NULL,
  `logostring` varchar(355) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=37 ;

CREATE TABLE `kategorien` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `konzept` int(10) unsigned NOT NULL,
  `kategoriename` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `abfragen` bit(1) DEFAULT NULL,
  `deckungssumme_abfragen` bit(1) DEFAULT NULL,
  `position` tinyint(3) unsigned NOT NULL,
  `erzeugt_ueberschrift` tinyint(1) unsigned NOT NULL,
  `versteckt` int(11) NOT NULL DEFAULT '0',
  `kategorieart` smallint(5) unsigned NOT NULL,
  `hauptkategorie` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `konzept` (`konzept`),
  KEY `hauptkategorie` (`hauptkategorie`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=942 ;

CREATE TABLE `konzepte` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `bezeichnung` varchar(100) NOT NULL,
  `infospalte` tinyint(1) unsigned NOT NULL,
  `kategorien_abfragen` tinyint(1) unsigned NOT NULL,
  `pfad` varchar(50) NOT NULL,
  `benutzung` int(1) NOT NULL DEFAULT '1',
  `kat` int(11) NOT NULL,
  `ersteller` varchar(155) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `mpm` varchar(35) NOT NULL,
  `mpj` varchar(35) NOT NULL,
  `mpvj` varchar(35) NOT NULL,
  `antwort_zahlung_hj` int(6) unsigned DEFAULT NULL,
  `antwort_zahlung_vj` int(6) unsigned DEFAULT NULL,
  `antwort_zahlung_m` int(6) unsigned DEFAULT NULL,
  `antwort_laufzeit_3jahre` int(6) unsigned DEFAULT NULL,
  `antwort_laufzeit_5jahre` int(6) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `antwort_zahlung_hj` (`antwort_zahlung_hj`),
  KEY `antwort_zahlung_vj` (`antwort_zahlung_vj`),
  KEY `antwort_zahlung_m` (`antwort_zahlung_m`),
  KEY `antwort_laufzeit_3jahre` (`antwort_laufzeit_3jahre`),
  KEY `antwort_laufzeit_5jahre` (`antwort_laufzeit_5jahre`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=181 ;

CREATE TABLE `leistungen` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `konzept` int(10) unsigned NOT NULL,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `kategorie` int(10) unsigned NOT NULL,
  `klartext` bit(1) DEFAULT NULL,
  `position` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `erklaerung` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `konzept` (`konzept`),
  KEY `kategorie` (`kategorie`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2601 ;

CREATE TABLE `leistungsinhalte` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `tarif` int(4) unsigned NOT NULL DEFAULT '0',
  `leistung` int(5) unsigned DEFAULT NULL,
  `inhalt` tinyint(3) unsigned DEFAULT NULL,
  `textinhalt` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `document` int(10) unsigned NOT NULL DEFAULT '0',
  `highlight` bit(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10845 ;

CREATE TABLE `pakete` (
  `praemien_id` int(10) unsigned NOT NULL,
  `kategorie_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`praemien_id`,`kategorie_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TRIGGER IF EXISTS `computePaketInhaltStringDelete`;
DELIMITER //
CREATE TRIGGER `computePaketInhaltStringDelete` AFTER DELETE ON `pakete`
 FOR EACH ROW update praemien set paketInhaltString = (select group_concat(kategorie_id ORDER BY kategorie_id) from pakete where praemien_id = praemien.id) where id = OLD.praemien_id
//
DELIMITER ;
DROP TRIGGER IF EXISTS `computePaketInhaltStringInsert`;
DELIMITER //
CREATE TRIGGER `computePaketInhaltStringInsert` AFTER INSERT ON `pakete`
 FOR EACH ROW update praemien set paketInhaltString = (select group_concat(kategorie_id ORDER BY kategorie_id) from pakete where praemien_id = praemien.id) where id = NEW.praemien_id
//
DELIMITER ;
DROP TRIGGER IF EXISTS `computePaketInhaltStringUpdate`;
DELIMITER //
CREATE TRIGGER `computePaketInhaltStringUpdate` AFTER UPDATE ON `pakete`
 FOR EACH ROW update praemien set paketInhaltString = (select group_concat(kategorie_id ORDER BY kategorie_id) from pakete where praemien_id = praemien.id) where id in(NEW.praemien_id, OLD.praemien_id)
//
DELIMITER ;

CREATE TABLE `praemien` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `tarif` int(4) unsigned NOT NULL DEFAULT '0',
  `deckungssumme` int(5) unsigned NOT NULL DEFAULT '0',
  `praemie` decimal(8,4) unsigned NOT NULL DEFAULT '0.0000',
  `zuschlag` decimal(8,4) NOT NULL DEFAULT '0.0000',
  `maximierung` varchar(100) NOT NULL,
  `makler_mehrfachagent` char(3) DEFAULT NULL,
  `mindestpraemie` decimal(6,2) NOT NULL,
  `umsatz_von` bigint(20) NOT NULL,
  `umsatz_bis` bigint(20) NOT NULL,
  `paketInhaltString` char(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tarif` (`tarif`,`paketInhaltString`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5856 ;

CREATE TABLE `tarife` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `deaktiviert` varchar(355) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `aktiviert` varchar(355) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `konzept` int(10) unsigned NOT NULL,
  `logo` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `gesellschaft` varchar(50) NOT NULL DEFAULT '',
  `alternativgesellschaft` varchar(255) NOT NULL,
  `tarifbezeichnung` varchar(50) NOT NULL DEFAULT '',
  `zuschlag_gf` decimal(5,2) unsigned NOT NULL DEFAULT '0.00',
  `zuschlag_mitarb_vz` decimal(5,2) unsigned DEFAULT '0.00',
  `zuschlag_mitarb_tz` decimal(5,2) unsigned NOT NULL,
  `gf_incl` int(2) unsigned NOT NULL DEFAULT '0',
  `mitarb_incl` int(2) unsigned NOT NULL DEFAULT '0',
  `mitarb_def_ausschl` bit(1) DEFAULT NULL,
  `mitarb_def_freib_u` bit(1) DEFAULT NULL,
  `mitarb_def_freib_e` bit(1) DEFAULT NULL,
  `unterj_h` tinyint(4) NOT NULL DEFAULT '0',
  `unterj_v` tinyint(4) NOT NULL DEFAULT '0',
  `unterj_m` tinyint(4) NOT NULL DEFAULT '0',
  `sfr_prozent` tinyint(4) NOT NULL DEFAULT '0',
  `sfr_jahre` tinyint(4) NOT NULL DEFAULT '0',
  `laufzeitnachlass_3jahre` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `laufzeitnachlass_5jahre` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `umsatznachlass_unter` int(10) unsigned NOT NULL DEFAULT '0',
  `umsatznachlass_proz` smallint(5) unsigned NOT NULL DEFAULT '0',
  `sb` varchar(10) NOT NULL DEFAULT '',
  `sb_info` varchar(255) NOT NULL,
  `berechnung` char(1) NOT NULL,
  `ufb` bit(1) DEFAULT NULL,
  `satz_unterverm` tinyint(3) unsigned NOT NULL,
  `mindestbeitrag` decimal(6,2) unsigned NOT NULL,
  `existenzgr_rabatt` int(10) unsigned NOT NULL,
  `sonder_zu_nach_feld` varchar(100) NOT NULL,
  `sonder_zu_nach_min` int(11) NOT NULL,
  `sonder_zu_nach_max` int(11) NOT NULL,
  `sonder_zu_nach_proz` decimal(3,1) NOT NULL,
  `tarif_deaktiviert` int(11) NOT NULL DEFAULT '0',
  `faktor_mitarb_tz` int(2) unsigned NOT NULL,
  `faktor_mitarb_gering` int(2) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1079 ;


ALTER TABLE `antworten`
  ADD CONSTRAINT `antworten_ibfk_1` FOREIGN KEY (`frage`) REFERENCES `fragen` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `antworten_ibfk_2` FOREIGN KEY (`frage`) REFERENCES `fragen` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `fragen`
  ADD CONSTRAINT `fragen_ibfk_1` FOREIGN KEY (`konzept`) REFERENCES `konzepte` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fragen_ibfk_2` FOREIGN KEY (`konzept`) REFERENCES `konzepte` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `kategorien`
  ADD CONSTRAINT `kategorien_ibfk_1` FOREIGN KEY (`konzept`) REFERENCES `konzepte` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `kategorien_ibfk_2` FOREIGN KEY (`hauptkategorie`) REFERENCES `kategorien` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `kategorien_ibfk_3` FOREIGN KEY (`konzept`) REFERENCES `konzepte` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `kategorien_ibfk_4` FOREIGN KEY (`hauptkategorie`) REFERENCES `kategorien` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `konzepte`
  ADD CONSTRAINT `konzepte_ibfk_1` FOREIGN KEY (`antwort_zahlung_hj`) REFERENCES `antworten` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `konzepte_ibfk_10` FOREIGN KEY (`antwort_laufzeit_5jahre`) REFERENCES `antworten` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `konzepte_ibfk_2` FOREIGN KEY (`antwort_zahlung_vj`) REFERENCES `antworten` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `konzepte_ibfk_3` FOREIGN KEY (`antwort_zahlung_m`) REFERENCES `antworten` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `konzepte_ibfk_4` FOREIGN KEY (`antwort_laufzeit_3jahre`) REFERENCES `antworten` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `konzepte_ibfk_5` FOREIGN KEY (`antwort_laufzeit_5jahre`) REFERENCES `antworten` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `konzepte_ibfk_6` FOREIGN KEY (`antwort_zahlung_hj`) REFERENCES `antworten` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `konzepte_ibfk_7` FOREIGN KEY (`antwort_zahlung_vj`) REFERENCES `antworten` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `konzepte_ibfk_8` FOREIGN KEY (`antwort_zahlung_m`) REFERENCES `antworten` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `konzepte_ibfk_9` FOREIGN KEY (`antwort_laufzeit_3jahre`) REFERENCES `antworten` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `leistungen`
  ADD CONSTRAINT `leistungen_ibfk_1` FOREIGN KEY (`konzept`) REFERENCES `konzepte` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `leistungen_ibfk_2` FOREIGN KEY (`kategorie`) REFERENCES `kategorien` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `leistungen_ibfk_3` FOREIGN KEY (`konzept`) REFERENCES `konzepte` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `leistungen_ibfk_4` FOREIGN KEY (`kategorie`) REFERENCES `kategorien` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
