/* 15:28:37 gmspider gmspider */ CREATE TABLE `jazyk` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `nazov` varchar(255) DEFAULT NULL,   `code` varchar(20) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDb DEFAULT CHARSET=utf8;

CREATE TABLE `kategoria_osoby` (
                                   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
                                   `nazov` varchar(45) DEFAULT NULL,
                                   `code` varchar(45) DEFAULT NULL,
                                   `visibility_type` tinyint(1) DEFAULT NULL,
                                   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `mail_template` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nazov` varchar(255) DEFAULT NULL,
  `code` varchar(50) DEFAULT NULL,
  `template` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `mail_template` (`id`, `nazov`, `code`, `template`) VALUES (1, 'Text', 'text', 'mail_text.tpl');

CREATE  TABLE IF NOT EXISTS `mail` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `mail_template_id` INT(11) UNSIGNED NOT NULL DEFAULT '1' ,
  `nazov` VARCHAR(100) NULL ,
  `text` TEXT NULL ,
  `email` VARCHAR(255) NULL ,
  `visibility_type` TINYINT(1) UNSIGNED NULL ,
  `datum_odoslania` DATETIME NULL ,
  `reply_to` VARCHAR(255) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `mail_FKIndex1` (`mail_template_id` ASC) ,
  CONSTRAINT `fk_{043A2935-16D0-4689-B8CB-67A446F44490}`
    FOREIGN KEY (`mail_template_id` )
    REFERENCES `mail_template` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

CREATE  TABLE IF NOT EXISTS `mail_kategoria_osoby` (
                                                       `mail_id` INT(11) UNSIGNED NOT NULL ,
                                                       `kategoria_osoby_id` INT(11) UNSIGNED NOT NULL ,
                                                       PRIMARY KEY (`mail_id`, `kategoria_osoby_id`) ,
                                                       INDEX `fk_mail_has_kategoria_osoby_kategoria_osoby1_idx` (`kategoria_osoby_id` ASC) ,
                                                       INDEX `fk_mail_has_kategoria_osoby_mail1_idx` (`mail_id` ASC) ,
                                                       CONSTRAINT `fk_mail_has_kategoria_osoby_mail1`
                                                           FOREIGN KEY (`mail_id` )
                                                               REFERENCES `mail` (`id` )
                                                               ON DELETE NO ACTION
                                                               ON UPDATE NO ACTION,
                                                       CONSTRAINT `fk_mail_has_kategoria_osoby_kategoria_osoby1`
                                                           FOREIGN KEY (`kategoria_osoby_id` )
                                                               REFERENCES `kategoria_osoby` (`id` )
                                                               ON DELETE NO ACTION
                                                               ON UPDATE NO ACTION)
    ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `mail_click` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `datum` DATETIME NULL ,
  `url` VARCHAR(255) NULL ,
  `mail_id` INT(11) UNSIGNED NOT NULL ,
  `osoba_id` INT(11) UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_mail_click_mail1_idx` (`mail_id` ASC) ,
  INDEX `fk_mail_click_osoba1_idx` (`osoba_id` ASC) ,
  CONSTRAINT `fk_mail_click_mail1`
    FOREIGN KEY (`mail_id` )
    REFERENCES `mail` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_mail_click_osoba1`
    FOREIGN KEY (`osoba_id` )
    REFERENCES `osoba` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `mail_jazyk` (
  `mail_id` INT(11) UNSIGNED NOT NULL ,
  `jazyk_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`mail_id`, `jazyk_id`) ,
  INDEX `fk_mail_has_jazyk_jazyk1_idx` (`jazyk_id` ASC) ,
  INDEX `fk_mail_has_jazyk_mail1_idx` (`mail_id` ASC) ,
  CONSTRAINT `fk_mail_has_jazyk_mail1`
    FOREIGN KEY (`mail_id` )
    REFERENCES `mail` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_mail_has_jazyk_jazyk1`
    FOREIGN KEY (`jazyk_id` )
    REFERENCES `jazyk` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO `form` (`id`, `nazov`, `code`) VALUES (5000, 'Hromadný mail', 'mail');
INSERT INTO `form_group` (`id`, `form_id`, `nazov`, `code`, `position`, `poznamka`) VALUES (5001, 5000, 'Základné informácie', 'mail_zakladne-informacie', 1, NULL);
INSERT INTO `form_group` (`id`, `form_id`, `nazov`, `code`, `position`, `poznamka`) VALUES (5002, 5000, 'Pridradenie hromadného mailu', 'mail_priradenie', 2, NULL);

INSERT INTO `form_element` (`id`,`form_group_id`,`form_element_type_id`,`db_table`,`db_column`,`db_multi_table`,`db_multivalue_table`,`column_title`,`position`,`lang_code`,`width`,`height`,`help`,`example`,`direct_to_db`,`required`,`number`,`float_number`,`email`,`css_style`,`css_class`,`javascript`,`direct_multi_label`,`direct_multi_data`,`default_val`,`vdaemon`,`add_empty_option`,`html`) VALUES (5000,5001,1,'mail','nazov',NULL,NULL,'Predmet',4,NULL,NULL,NULL,NULL,NULL,1,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO `form_element` (`id`,`form_group_id`,`form_element_type_id`,`db_table`,`db_column`,`db_multi_table`,`db_multivalue_table`,`column_title`,`position`,`lang_code`,`width`,`height`,`help`,`example`,`direct_to_db`,`required`,`number`,`float_number`,`email`,`css_style`,`css_class`,`javascript`,`direct_multi_label`,`direct_multi_data`,`default_val`,`vdaemon`,`add_empty_option`,`html`) VALUES (5001,5001,10,'mail','text',NULL,NULL,'Text',5,NULL,600,300,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO `form_element` (`id`,`form_group_id`,`form_element_type_id`,`db_table`,`db_column`,`db_multi_table`,`db_multivalue_table`,`column_title`,`position`,`lang_code`,`width`,`height`,`help`,`example`,`direct_to_db`,`required`,`number`,`float_number`,`email`,`css_style`,`css_class`,`javascript`,`direct_multi_label`,`direct_multi_data`,`default_val`,`vdaemon`,`add_empty_option`,`html`) VALUES (5002,5001,1,'mail','email',NULL,NULL,'Testovací email',2,NULL,NULL,NULL,NULL,NULL,1,1,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL,'nastav@ma.sk',NULL,NULL,NULL);
INSERT INTO `form_element` (`id`,`form_group_id`,`form_element_type_id`,`db_table`,`db_column`,`db_multi_table`,`db_multivalue_table`,`column_title`,`position`,`lang_code`,`width`,`height`,`help`,`example`,`direct_to_db`,`required`,`number`,`float_number`,`email`,`css_style`,`css_class`,`javascript`,`direct_multi_label`,`direct_multi_data`,`default_val`,`vdaemon`,`add_empty_option`,`html`) VALUES (5003,5002,5,'mail','mail_template_id','mail_template',NULL,'Šablona hromadného mailu',1,NULL,NULL,NULL,NULL,NULL,1,1,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,1,NULL);
INSERT INTO `form_element` (`id`,`form_group_id`,`form_element_type_id`,`db_table`,`db_column`,`db_multi_table`,`db_multivalue_table`,`column_title`,`position`,`lang_code`,`width`,`height`,`help`,`example`,`direct_to_db`,`required`,`number`,`float_number`,`email`,`css_style`,`css_class`,`javascript`,`direct_multi_label`,`direct_multi_data`,`default_val`,`vdaemon`,`add_empty_option`,`html`) VALUES (5004,5001,1,'mail','reply_to',NULL,NULL,'Odpoveï na',3,NULL,NULL,NULL,NULL,NULL,1,1,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL,'nastav@ma.sk',NULL,NULL,NULL);
INSERT INTO `form_element` (`id`,`form_group_id`,`form_element_type_id`,`db_table`,`db_column`,`db_multi_table`,`db_multivalue_table`,`column_title`,`position`,`lang_code`,`width`,`height`,`help`,`example`,`direct_to_db`,`required`,`number`,`float_number`,`email`,`css_style`,`css_class`,`javascript`,`direct_multi_label`,`direct_multi_data`,`default_val`,`vdaemon`,`add_empty_option`,`html`) VALUES (5005,5002,9,'mail','jazyk_id','jazyk',NULL,'Kontakty s preferovaným jazykom',2,NULL,NULL,NULL,NULL,NULL,1,1,NULL,NULL,NULL,NULL,NULL,NULL,1,1,NULL,NULL,NULL,NULL);
INSERT INTO `form_element` (`id`,`form_group_id`,`form_element_type_id`,`db_table`,`db_column`,`db_multi_table`,`db_multivalue_table`,`column_title`,`position`,`lang_code`,`width`,`height`,`help`,`example`,`direct_to_db`,`required`,`number`,`float_number`,`email`,`css_style`,`css_class`,`javascript`,`direct_multi_label`,`direct_multi_data`,`default_val`,`vdaemon`,`add_empty_option`,`html`) VALUES (5006,5002,9,'mail','kategoria_osoby_id','kategoria_osoby',NULL,'Typ osobau',3,NULL,NULL,NULL,NULL,NULL,1,1,NULL,NULL,NULL,NULL,NULL,NULL,1,1,NULL,NULL,NULL,NULL);

INSERT INTO `log_type` (`nazov`, `code`, `primary_table`) VALUES ('Pridanie hromadného mailu', 'mail_pridaj', 'mail');
INSERT INTO `log_type` (`nazov`, `code`, `primary_table`) VALUES ('Editovanie hromadného mailu', 'mail_edituj', 'mail');
INSERT INTO `log_type` (`nazov`, `code`, `primary_table`) VALUES ('Zmazanie hromadného mailu', 'mail_zmaz', 'mail');
INSERT INTO `priradenie_suboru` (`nazov`, `code`) VALUES ('Hromadný mail', 'mail');

INSERT INTO `menu` (`id`, `menu_id`, `nazov`, `page`, `poradie`, `level`, `visibility_type`) VALUES (5000, NULL, 'Hromadný mail', 'mail', 6, 1, 1);
INSERT INTO `menu` (`id`, `menu_id`, `nazov`, `page`, `poradie`, `level`, `visibility_type`) VALUES (5001, 5000, 'Výpis hromadných mailov', 'mail_vypis', 1, 2, 1);
INSERT INTO `menu` (`id`, `menu_id`, `nazov`, `page`, `poradie`, `level`, `visibility_type`) VALUES (5002, 5000, 'Pridanie hromadného mailu', 'mail_pridaj', 2, 2, 1);
INSERT INTO `menu` (`id`, `menu_id`, `nazov`, `page`, `poradie`, `level`, `visibility_type`) VALUES (5003, 5001, 'Detail hromadného mailu', 'mail_view', 1, 3, 1);
INSERT INTO `menu` (`id`, `menu_id`, `nazov`, `page`, `poradie`, `level`, `visibility_type`) VALUES (5004, 5001, 'Editovanie hromadného mailu', 'mail_edituj', 2, 3, 1);
INSERT INTO `menu` (`id`, `menu_id`, `nazov`, `page`, `poradie`, `level`, `visibility_type`) VALUES (5005, 5001, 'Zmazanie hromadného mailu', 'mail_zmaz', NULL, 3, NULL);
INSERT INTO `menu` (`id`, `menu_id`, `nazov`, `page`, `poradie`, `level`, `visibility_type`) VALUES (5006, 5001, 'Fotografie hromadného mailu', 'mail_foto', 3, 3, 1);
INSERT INTO `menu` (`id`, `menu_id`, `nazov`, `page`, `poradie`, `level`, `visibility_type`) VALUES (5007, 5001, 'Prílohy k hromadnému mailu', 'mail_subor', 4, 3, 1);
INSERT INTO `menu` (`id`, `menu_id`, `nazov`, `page`, `poradie`, `level`, `visibility_type`) VALUES (5008, 5001, 'Odoslanie testovacieho mailu', 'mail_test', NULL, 3, 0);
INSERT INTO `menu` (`id`, `menu_id`, `nazov`, `page`, `poradie`, `level`, `visibility_type`) VALUES (5009, 5001, 'Odoslanie hromadného mailu', 'mail_send', NULL, 3, NULL);


CREATE TABLE `mail_osoba` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`mail_id` INT(11) UNSIGNED NOT NULL,
	`datum_odoslania` DATETIME NULL DEFAULT NULL,
	`datum_odhlasenia` DATETIME NULL DEFAULT NULL,
	`odoslane` TINYINT(1) UNSIGNED NULL DEFAULT NULL,
	`osoba_id` INT(11) UNSIGNED NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `mail_osoba_FKIndex1` (`mail_id`),
	INDEX `fk_mail_osoba_osoba1_idx` (`osoba_id`),
	CONSTRAINT `fk_mail_osoba_mail` FOREIGN KEY (`mail_id`) REFERENCES `mail` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `fk_mail_osoba_osoba1` FOREIGN KEY (`osoba_id`) REFERENCES `osoba` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

