
# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- lohnabrechnung
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `lohnabrechnung`;

CREATE TABLE `lohnabrechnung`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `mod` INTEGER NOT NULL,
    `monat` CHAR(6) NOT NULL,
    `arbeitsstunden` DECIMAL(10,2) DEFAULT 0 NOT NULL,
    `brutto` DECIMAL(10,2) DEFAULT 0 NOT NULL,
    `zulage` DECIMAL(10,2) DEFAULT 0 NOT NULL,
    `netto` DECIMAL(10,2) DEFAULT 0 NOT NULL,
    `lohnsteuer` DECIMAL(10,2) DEFAULT 0 NOT NULL,
    `kirchensteuer_rk` DECIMAL(10,2) DEFAULT 0 NOT NULL,
    `kirchensteuer_ev` DECIMAL(10,2) DEFAULT 0 NOT NULL,
    `soli` DECIMAL(10,2) DEFAULT 0 NOT NULL,
    `rentenversicherung` DECIMAL(10,2) DEFAULT 0 NOT NULL,
    `krankenversicherung` DECIMAL(10,2) DEFAULT 0 NOT NULL,
    `ma_id` INTEGER NOT NULL,
    `lp_id` INTEGER NOT NULL,
    `class_key` INTEGER,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `lohnabrechnung_U_1` (`ma_id`, `monat`),
    INDEX `lohnabrechnung_FI_2` (`lp_id`),
    CONSTRAINT `lohnabrechnung_FK_1`
        FOREIGN KEY (`ma_id`)
        REFERENCES `mitarbeiter` (`id`),
    CONSTRAINT `lohnabrechnung_FK_2`
        FOREIGN KEY (`lp_id`)
        REFERENCES `lohnprofil` (`id`)
) ENGINE=InnoDB CHARACTER SET='utf8';

-- ---------------------------------------------------------------------
-- mitarbeiter
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `mitarbeiter`;

CREATE TABLE `mitarbeiter`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `mod` INTEGER NOT NULL,
    `nachname` VARCHAR(255) NOT NULL,
    `vorname` VARCHAR(255) NOT NULL,
    `anrede` CHAR(4) NOT NULL,
    `strasse` VARCHAR(255) NOT NULL,
    `plz` CHAR(5) NOT NULL,
    `ort` VARCHAR(255) NOT NULL,
    `iban` CHAR(30) NOT NULL,
    `bank` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `basis_betrag` DECIMAL(10,2) DEFAULT 0 NOT NULL,
    `steuermerkmal` VARCHAR(30) NOT NULL,
    `aktiv` TINYINT(1) NOT NULL,
    `lp_id` INTEGER NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `mitarbeiter_U_1` (`nachname`, `vorname`),
    INDEX `mitarbeiter_FI_1` (`lp_id`),
    CONSTRAINT `mitarbeiter_FK_1`
        FOREIGN KEY (`lp_id`)
        REFERENCES `lohnprofil` (`id`)
) ENGINE=InnoDB CHARACTER SET='utf8';

-- ---------------------------------------------------------------------
-- lohnprofil
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `lohnprofil`;

CREATE TABLE `lohnprofil`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `mod` INTEGER NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `periode` INTEGER NOT NULL,
    `lohnsteuer` TINYINT(1) DEFAULT 0 NOT NULL,
    `kirchensteuer_rk` TINYINT(1) DEFAULT 0 NOT NULL,
    `kirchensteuer_ev` TINYINT(1) DEFAULT 0 NOT NULL,
    `soli` TINYINT(1) DEFAULT 0 NOT NULL,
    `rentenversicherung_an` REAL DEFAULT 0 NOT NULL,
    `rentenversicherung_ag` REAL DEFAULT 0 NOT NULL,
    `krankenversicherung_an` REAL DEFAULT 0 NOT NULL,
    `krankenversicherung_ag` REAL DEFAULT 0 NOT NULL,
    `u1` REAL DEFAULT 0 NOT NULL,
    `u2` REAL DEFAULT 0 NOT NULL,
    `inso` REAL DEFAULT 0 NOT NULL,
    `pauschalsteuer` REAL DEFAULT 0 NOT NULL,
    `la_typ` INTEGER NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `lohnprofil_U_1` (`name`)
) ENGINE=InnoDB CHARACTER SET='utf8';

-- ---------------------------------------------------------------------
-- arbeitgeberbtr
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `arbeitgeberbtr`;

CREATE TABLE `arbeitgeberbtr`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `mod` INTEGER NOT NULL,
    `monat` CHAR(6) NOT NULL,
    `rentenversicherung` REAL DEFAULT 0 NOT NULL,
    `krankenversicherung` REAL DEFAULT 0 NOT NULL,
    `u1` REAL DEFAULT 0 NOT NULL,
    `u2` REAL DEFAULT 0 NOT NULL,
    `inso` REAL DEFAULT 0 NOT NULL,
    `pauschalsteuer` REAL DEFAULT 0 NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `arbeitgeberbtr_U_1` (`monat`)
) ENGINE=InnoDB CHARACTER SET='utf8';

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;
