CREATE TABLE IF NOT EXISTS `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `description` text NOT NULL,
  `pub_date` date NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `news_pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `news_id` int(11) NOT NULL,
  `page_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `page_id` (`page_id`),
  KEY `news_id` (`news_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Limiti per le tabelle scaricate
--

--
-- Limiti per la tabella `news_pages`
--
ALTER TABLE `news_pages`
  ADD CONSTRAINT `news_pages_ibfk_2` FOREIGN KEY (`page_id`) REFERENCES `categories_pages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `news_pages_ibfk_1` FOREIGN KEY (`news_id`) REFERENCES `news` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
CREATE TABLE IF NOT EXISTS `attachments_news` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `attachment_id` int(11) unsigned NOT NULL,
  `news_id` int(11) NOT NULL,
  `is_predefined` tinyint(1) unsigned DEFAULT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `attachment_id` (`attachment_id`),
  KEY `news_id` (`news_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Limiti per le tabelle scaricate
--

--
-- Limiti per la tabella `attachments_news`
--
ALTER TABLE `attachments_news`
  ADD CONSTRAINT `attachments_news_ibfk_2` FOREIGN KEY (`news_id`) REFERENCES `news` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `attachments_news_ibfk_1` FOREIGN KEY (`attachment_id`) REFERENCES `attachments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
--
-- Struttura della tabella `news_videos`
--

CREATE TABLE IF NOT EXISTS `news_videos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) DEFAULT NULL,
  `code` varchar(20) NOT NULL,
  `news_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_videos_1` (`news_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Limiti per le tabelle scaricate
--

--
-- Limiti per la tabella `news_videos`
--
ALTER TABLE `news_videos`
  ADD CONSTRAINT `news_videos_ibfk_1` FOREIGN KEY (`news_id`) REFERENCES `news` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
--
-- Struttura della tabella `categories_news`
--

CREATE TABLE IF NOT EXISTS `categories_news` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `news_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `news_id` (`news_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Limiti per le tabelle scaricate
--

--
-- Limiti per la tabella `categories_news`
--
ALTER TABLE `categories_news`
  ADD CONSTRAINT `categories_news_ibfk_2` FOREIGN KEY (`news_id`) REFERENCES `news` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `categories_news_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
--
-- Struttura della tabella `documents_news`
--

CREATE TABLE IF NOT EXISTS `documents_news` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `news_id` int(11) NOT NULL,
  `attachment_id` int(11) unsigned NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `flag_private` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '1 = privato',
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `attachment_id` (`attachment_id`),
  KEY `news_id` (`news_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Limiti per le tabelle scaricate
--

--
-- Limiti per la tabella `documents_news`
--
ALTER TABLE `documents_news`
  ADD CONSTRAINT `documents_news_ibfk_2` FOREIGN KEY (`attachment_id`) REFERENCES `attachments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `documents_news_ibfk_1` FOREIGN KEY (`news_id`) REFERENCES `news` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
 
ALTER TABLE  `news` ADD  `status` SMALLINT( 1 ) NOT NULL DEFAULT  '0' COMMENT  '0 = non pubblicata, 2 = pubblicata, 9 = archiviata' AFTER  `pub_date`;

INSERT INTO  `items` (
`id` ,
`description` ,
`icon` ,
`module_id` ,
`folder_id` ,
`jsoptions` ,
`order` ,
`flag_active` ,
`flag_exclusive` ,
`level`
)
VALUES (
NULL ,  'News', NULL ,  '1',  '6', NULL ,  '40',  '1',  '0',  '70'
);

INSERT INTO `jsfiles` (
`id` ,
`plugin` ,
`jsfile` ,
`jsmodule` ,
`jsfunction`
)
VALUES (
NULL , NULL ,  'manage_news',  'ManageNews',  'init'
), (
NULL , NULL ,  'edit_news',  'EditNews',  'init'
);

INSERT INTO  `items_jsfiles` (
`id` ,
`jsfile_id` ,
`item_id` ,
`flag_main`
)
VALUES (
NULL ,  '13',  '11',  '1'
), (
NULL ,  '14',  '11',  '0'
);

INSERT INTO  `permissions` (
`id` ,
`module` ,
`controller` ,
`action` ,
`row` ,
`flag_exclusive` ,
`level` ,
`created` ,
`modified`
)
VALUES (
NULL ,  'Frontend',  'News',  'view',  'frontend:news:view',  '0',  '0', NOW( ) , NOW( )
);

/**** FATTO ****/

ALTER TABLE  `teacher_public_fields` DROP  `email`;

ALTER TABLE  `teacher_private_fields` ADD  `email` VARCHAR( 150 ) NOT NULL AFTER  `cell` ,
ADD  `home_phone` VARCHAR( 150 ) NOT NULL AFTER  `email` ,
ADD  `birth_date` DATE NOT NULL AFTER  `home_phone` ,
ADD  `role_date` DATE NOT NULL AFTER  `birth_date`;

ALTER TABLE  `teacher_private_fields` CHANGE  `email`  `email` VARCHAR( 150 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
CHANGE  `home_phone`  `home_phone` VARCHAR( 150 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
CHANGE  `birth_date`  `birth_date` DATE NULL ,
CHANGE  `role_date`  `role_date` DATE NULL;