/**
 * Erstellt alle Tabellen und Trigger für den Service/CME
 */

/* DROP TABLE IF EXISTS `cme_laek`; */


CREATE TABLE `cme_laek` (
  `laek_id` int(11) NOT NULL,
  `laek_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`laek_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `cme_laek` ( `laek_id`, `laek_name` )
VALUES
( 1, 'Landesärztekammer Baden-Württemberg'),
( 2, 'Bayerische Landesärztekammer'),
( 3, 'Ärztekammer Berlin'),
( 4, 'Landesärztekammer Brandenburg'),
( 5, 'Ärztekammer Bremen'),
( 6, 'Ärztekammer Hamburg'),
( 7, 'Landesärztekammer Hessen'),
( 8, 'Ärztekammer Mecklenburg-Vorpommern'),
( 9, 'Ärztekammer Niedersachsen'),
( 10, 'Ärztekammer Nordrhein'),
( 11, 'Landesärztekammer Rheinland-Pfalz'),
( 12, 'Ärztekammer des Saarlandes'),
( 13, 'Sächsische Landesärztekammer'),
( 14, 'Ärztekammer Sachsen-Anhalt'),
( 15, 'Ärztekammer Schleswig-Holstein'),
( 16, 'Landesärztekammer Thüringen'),
( 17, 'Ärztekammer Westfalen-Lippe');



/* DROP TABLE IF EXISTS `cme_points`; */
/* DROP TABLE IF EXISTS `cme_points_transfer`; */

CREATE TABLE `cme_points` (
  `user_fi` int(11) NOT NULL,
  `test_fi` int(11) NOT NULL,
  `active_fi` int(11) NOT NULL,
  `efn` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `vnr` varchar(19) COLLATE utf8_unicode_ci NOT NULL,
  `vnr_password` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `max_points` double NOT NULL,
  `reached_points` double NOT NULL,
  `cme_points` int(11) NOT NULL,
  `laek` varchar(120) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pass` int(11) DEFAULT NULL,
  `transfer_success` timestamp NULL DEFAULT NULL,
  `import_success` timestamp NULL DEFAULT NULL,
  `cme_max_points` int(11) NOT NULL,
  `test_finished` timestamp NULL DEFAULT NULL,
  `ref_id` int(11) NOT NULL,
  PRIMARY KEY (`user_fi`,`test_fi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `cme_points_transfer` (
  `test_fi` int(11) NOT NULL,
  `user_fi` int(11) NOT NULL,
  `transfer_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `transfer_message` varchar(512) COLLATE utf8_unicode_ci DEFAULT '',
  `transfer_success` int(11) NOT NULL,
  PRIMARY KEY (`test_fi`,`user_fi`,`transfer_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


DROP TRIGGER IF EXISTS `cme_test_update_cme_points`;

DELIMITER $$

/**
 * Falls im Benutzerprofil die EFN (= matriculation) geändert wurde
 * dann müssen wir das in den Punktetabelle ändern.
 */
CREATE TRIGGER `cme_usr_data_after_upd_tr` AFTER UPDATE ON `usr_data` FOR EACH ROW
    BEGIN
        UPDATE `cme_points` 
        SET efn = new.matriculation
        WHERE transfer_success is null and user_fi = new.usr_id;
    END;$$

DELIMITER ;

 
DROP TRIGGER IF EXISTS `cme_test_update_cme_points`;

DELIMITER $$

/**
 * Falls in einem CME-Test die CME-Felder geändert werden, muessen wir
 * die CME-Punktetabelle entsprechend ändern
 */
CREATE TRIGGER `cme_test_update_cme_points` AFTER UPDATE ON `tst_tests` FOR EACH ROW 
    BEGIN
        /* 
         * Nur wenn es ein CME-Test ist (NEW.cme_test = 1) und wenn sich eines
         * der CME-Felder (cme_vnr, cme_vnr_password, cme_laek) geaendert hat und auch nur dann
         * wenn die Punkte nicht bereits uebertragen wurden
         */
        IF ( NEW.cme_test = 1 AND 
            ( OLD.cme_vnr <=> NEW.cme_vnr OR 
              OLD.cme_vnr_password <=> NEW.cme_vnr_password OR
              OLD.cme_laek <=> NEW.cme_laek )) THEN
            UPDATE `cme_points` 
            SET vnr = NEW.cme_vnr,
                vnr_password = NEW.cme_vnr_password,
                laek = ( SELECT laek_name from cme_laek where laek_id = NEW.cme_laek )
            WHERE transfer_success IS NULL and test_fi = NEW.test_id;
        END IF;
    END;$$

DELIMITER ;
