<?php

/**
 * Generated: 2013-11-04 11:03:31
 * @author Rtn_Module_Tool
 */
class Newsletter_Module_Initer_DB extends Main_Newsletter_Module_Initer_DB {

	public static function init() {
		R::exec("
			DROP TABLE IF EXISTS `nluser`;
			CREATE TABLE `nluser` (
			  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
			  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
			  `number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
			  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
			  `active` tinyint(1) unsigned DEFAULT NULL,
			  `mailconfirm` tinyint(1) unsigned DEFAULT NULL,
			  `codeconfirm` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
			  PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

			DROP TABLE IF EXISTS `nlgroup`;
			CREATE TABLE `nlgroup` (
			  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
			  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
			  `active` tinyint(1) unsigned DEFAULT NULL,
			  PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


			DROP TABLE IF EXISTS `nlgroupassign`;
			CREATE TABLE `nlgroupassign` (
			  `nluser_id` int(11) NOT NULL,
			  `nlgroup_id` int(11) unsigned NOT NULL,
			  PRIMARY KEY (`nlgroup_id`,`nluser_id`),
			  CONSTRAINT `nlgroupassign_ibfk_1` FOREIGN KEY (`nlgroup_id`) REFERENCES `nlgroup` (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


			DROP TABLE IF EXISTS `nlletter`;
			CREATE TABLE `nlletter` (
			  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
			  `cdnwaregroup_id` int(11) unsigned DEFAULT NULL,
			  `nlgroup_id` tinyint(3) unsigned DEFAULT NULL,
			  `subject` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
			  `body` text COLLATE utf8_unicode_ci,
			  `submit` tinyint(3) unsigned DEFAULT NULL,
			  `updated` int(11) unsigned DEFAULT NULL,
			  `created` int(11) unsigned DEFAULT NULL,
			  `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
			  `sendstarted` int(11) unsigned DEFAULT NULL,
			  `sendfinished` int(11) unsigned DEFAULT NULL,
			  PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

			DROP TABLE IF EXISTS `nltmpsent`;
			CREATE TABLE `nltmpsent` (
			  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
			  `nlgroup_id` tinyint(3) unsigned DEFAULT NULL,
			  `nluser_id` int(11) unsigned DEFAULT NULL,
			  `letter_id` tinyint(3) unsigned DEFAULT NULL,
			  `created` int(11) unsigned DEFAULT NULL,
			  PRIMARY KEY (`id`),
			  UNIQUE KEY `nlgroup_id_knt_kntid_letter_id` (`nlgroup_id`,`nluser_id`,`letter_id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


			CREATE OR REPLACE VIEW viewnlletter AS
			SELECT
				l.*
			FROM nlletter l;

			CREATE OR REPLACE VIEW viewnlsending AS
			SELECT
				nga.nluser_id,
				ng.id AS nlgid,
				ng.name AS groupname,
				l.*,
				nlu.email As useremail
			FROM nlletter l
			JOIN nlgroup ng ON ng.id = l.nlgroup_id
			JOIN nlgroupassign nga ON nga.nlgroup_id = ng.id
			JOIN nluser nlu ON nga.nluser_id = nlu.id
			WHERE l.status = 'pending';
			
			create function p1() returns INTEGER DETERMINISTIC NO SQL return @p1;
			
			CREATE OR REPLACE VIEW viewnlmenbers AS
			SELECT
			        nlu.id AS id,
				gr.id AS nlgroup_id,
				gr.`name` AS nazwa_grupy,
				nlu.email AS nlu_email,
				nlu.number AS nlu_number,
				nlu.name AS nlu_name,
				nlu.id AS nlu_id,
				IF(nlgroup_id>0,1,0) AS active
			FROM
				nlgroup gr
			LEFT JOIN nlgroupassign ga ON ga.nlgroup_id = gr.id AND gr.id = p1()
			RIGHT JOIN nluser nlu ON ga.nluser_id = nlu.id;


		");
	}

}