SET FOREIGN_KEY_CHECKS=0;

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET AUTOCOMMIT=0;
START TRANSACTION;

--
-- Database: `insight`
--

-- --------------------------------------------------------

--
-- Table structure for table `aux_articles`
--

DROP TABLE IF EXISTS `aux_articles`;
CREATE TABLE IF NOT EXISTS `aux_articles` (
  `art_id` int(10) unsigned NOT NULL auto_increment,
  `smap_id` int(10) unsigned NOT NULL,
  `art_date` datetime NOT NULL,
  `art_is_active` tinyint(1) NOT NULL default '1',
  `art_is_commentable` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`art_id`),
  KEY `smap_id` (`smap_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `aux_articles_translation`
--

DROP TABLE IF EXISTS `aux_articles_translation`;
CREATE TABLE IF NOT EXISTS `aux_articles_translation` (
  `art_id` int(10) unsigned NOT NULL,
  `lang_id` int(10) unsigned NOT NULL,
  `art_name` varchar(250) NOT NULL,
  `art_announce_rtf` text NOT NULL,
  `art_text_rtf` text NOT NULL,
  PRIMARY KEY  (`art_id`,`lang_id`),
  KEY `lang_id` (`lang_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `aux_comments`
--

DROP TABLE IF EXISTS `aux_comments`;
CREATE TABLE IF NOT EXISTS `aux_comments` (
  `comment_id` int(10) unsigned NOT NULL auto_increment,
  `comment_pid` int(10) unsigned default NULL,
  `comment_time` datetime NOT NULL,
  `u_id` int(10) unsigned default NULL,
  `comment_username` varchar(200) NOT NULL,
  `comment_email` varchar(200) NOT NULL,
  `comment_title` varchar(200) default NULL,
  `comment_text` text NOT NULL,
  PRIMARY KEY  (`comment_id`),
  KEY `comment_time` (`comment_time`),
  KEY `comment_u_id` (`u_id`),
  KEY `comment_pid` (`comment_pid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

-- --------------------------------------------------------

--
-- Table structure for table `aux_comments2articles`
--

DROP TABLE IF EXISTS `aux_comments2articles`;
CREATE TABLE IF NOT EXISTS `aux_comments2articles` (
  `comment_id` int(10) unsigned NOT NULL,
  `art_id` int(10) unsigned NOT NULL,
  `lang_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`comment_id`,`art_id`,`lang_id`),
  KEY `art_id` (`art_id`),
  KEY `lang_id` (`lang_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `aux_comments2sitemap`
--

DROP TABLE IF EXISTS `aux_comments2sitemap`;
CREATE TABLE IF NOT EXISTS `aux_comments2sitemap` (
  `comment_id` int(10) unsigned NOT NULL,
  `smap_id` int(10) unsigned NOT NULL,
  `lang_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`comment_id`,`smap_id`,`lang_id`),
  KEY `smap_id` (`smap_id`),
  KEY `lang_id` (`lang_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `aux_resumes`
--

DROP TABLE IF EXISTS `aux_resumes`;
CREATE TABLE IF NOT EXISTS `aux_resumes` (
  `resume_id` int(10) unsigned NOT NULL auto_increment,
  `vacancy_id` int(10) unsigned NOT NULL default '0',
  `resume_date` date NOT NULL,
  `resume_candidate_name` varchar(250) NOT NULL,
  `resume_candidate_email` varchar(200) NOT NULL,
  `resume_text` text,
  `resume_main_pfile` varchar(250) NOT NULL,
  PRIMARY KEY  (`resume_id`,`vacancy_id`),
  KEY `vacancy_id` (`vacancy_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `aux_vacancies`
--

DROP TABLE IF EXISTS `aux_vacancies`;
CREATE TABLE IF NOT EXISTS `aux_vacancies` (
  `vacancy_id` int(10) unsigned NOT NULL auto_increment,
  `smap_id` int(10) unsigned NOT NULL,
  `vacancy_date` date NOT NULL,
  `vacancy_end_date` date default NULL,
  `vacancy_is_active` tinyint(1) NOT NULL default '1',
  `vacancy_url_segment` varchar(100) NOT NULL,
  PRIMARY KEY  (`vacancy_id`),
  KEY `vacancy_date` (`vacancy_date`),
  KEY `smap_id` (`smap_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `aux_vacancies_translation`
--

DROP TABLE IF EXISTS `aux_vacancies_translation`;
CREATE TABLE IF NOT EXISTS `aux_vacancies_translation` (
  `vacancy_id` int(10) unsigned NOT NULL,
  `lang_id` int(10) unsigned NOT NULL,
  `vacancy_name` varchar(250) NOT NULL,
  `vacancy_annotation` text NOT NULL,
  `vacancy_text_rtf` text NOT NULL,
  `vacancy_info_rtf` text,
  PRIMARY KEY  (`vacancy_id`,`lang_id`),
  KEY `lang_id` (`lang_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Stand-in structure for view `v_articles_comments`
--
DROP VIEW IF EXISTS `v_articles_comments`;
CREATE TABLE IF NOT EXISTS `v_articles_comments` (
`comment_id` int(10) unsigned
,`comment_time` datetime
,`comment_email` varchar(200)
,`comment_username` varchar(200)
,`comment_text` text
,`lang_id` int(10) unsigned
,`art_id` int(10) unsigned
);
-- --------------------------------------------------------

--
-- Stand-in structure for view `v_page_comments`
--
DROP VIEW IF EXISTS `v_page_comments`;
CREATE TABLE IF NOT EXISTS `v_page_comments` (
`comment_id` int(10) unsigned
,`comment_pid` int(10) unsigned
,`comment_time` datetime
,`u_id` int(10) unsigned
,`comment_email` varchar(200)
,`comment_title` varchar(200)
,`comment_text` text
,`lang_id` int(10) unsigned
,`smap_id` int(10) unsigned
);
-- --------------------------------------------------------

--
-- Structure for view `v_articles_comments`
--
DROP TABLE IF EXISTS `v_articles_comments`;

CREATE VIEW `v_articles_comments` AS select `comments`.`comment_id` AS `comment_id`,`comments`.`comment_time` AS `comment_time`,`comments`.`comment_email` AS `comment_email`,`comments`.`comment_username` AS `comment_username`,`comments`.`comment_text` AS `comment_text`,`links`.`lang_id` AS `lang_id`,`links`.`art_id` AS `art_id` from (`aux_comments2articles` `links` left join `aux_comments` `comments` on((`comments`.`comment_id` = `links`.`comment_id`))) where isnull(`comments`.`comment_pid`) order by `comments`.`comment_time` desc;

-- --------------------------------------------------------

--
-- Structure for view `v_page_comments`
--
DROP TABLE IF EXISTS `v_page_comments`;

CREATE VIEW `v_page_comments` AS select `comments`.`comment_id` AS `comment_id`,`comments`.`comment_pid` AS `comment_pid`,`comments`.`comment_time` AS `comment_time`,`comments`.`u_id` AS `u_id`,`comments`.`comment_email` AS `comment_email`,`comments`.`comment_title` AS `comment_title`,`comments`.`comment_text` AS `comment_text`,`links`.`lang_id` AS `lang_id`,`links`.`smap_id` AS `smap_id` from (`aux_comments2sitemap` `links` left join `aux_comments` `comments` on((`comments`.`comment_id` = `links`.`comment_id`))) where isnull(`comments`.`comment_pid`) order by `comments`.`comment_time` desc;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `aux_articles_translation`
--
ALTER TABLE `aux_articles_translation`
  ADD CONSTRAINT `aux_articles_translation_ibfk_1` FOREIGN KEY (`art_id`) REFERENCES `aux_articles` (`art_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `aux_articles_translation_ibfk_2` FOREIGN KEY (`lang_id`) REFERENCES `share_languages` (`lang_id`) ON DELETE CASCADE;

--
-- Constraints for table `aux_comments`
--
ALTER TABLE `aux_comments`
  ADD CONSTRAINT `aux_comments_ibfk_1` FOREIGN KEY (`comment_pid`) REFERENCES `aux_comments` (`comment_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `aux_comments_ibfk_2` FOREIGN KEY (`u_id`) REFERENCES `user_users` (`u_id`) ON DELETE CASCADE;

--
-- Constraints for table `aux_comments2articles`
--
ALTER TABLE `aux_comments2articles`
  ADD CONSTRAINT `aux_comments2articles_ibfk_1` FOREIGN KEY (`comment_id`) REFERENCES `aux_comments` (`comment_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `aux_comments2articles_ibfk_2` FOREIGN KEY (`art_id`) REFERENCES `aux_articles_translation` (`art_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `aux_comments2articles_ibfk_3` FOREIGN KEY (`lang_id`) REFERENCES `aux_articles_translation` (`lang_id`) ON DELETE CASCADE;

--
-- Constraints for table `aux_comments2sitemap`
--
ALTER TABLE `aux_comments2sitemap`
  ADD CONSTRAINT `aux_comments2sitemap_ibfk_1` FOREIGN KEY (`comment_id`) REFERENCES `aux_comments` (`comment_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `aux_comments2sitemap_ibfk_2` FOREIGN KEY (`smap_id`) REFERENCES `share_sitemap_translation` (`smap_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `aux_comments2sitemap_ibfk_3` FOREIGN KEY (`lang_id`) REFERENCES `share_sitemap_translation` (`lang_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `aux_resumes`
--
ALTER TABLE `aux_resumes`
  ADD CONSTRAINT `aux_resumes_ibfk_1` FOREIGN KEY (`vacancy_id`) REFERENCES `aux_vacancies` (`vacancy_id`) ON DELETE CASCADE;

--
-- Constraints for table `aux_vacancies`
--
ALTER TABLE `aux_vacancies`
  ADD CONSTRAINT `aux_vacancies_ibfk_1` FOREIGN KEY (`smap_id`) REFERENCES `share_sitemap` (`smap_id`) ON DELETE CASCADE;

--
-- Constraints for table `aux_vacancies_translation`
--
ALTER TABLE `aux_vacancies_translation`
  ADD CONSTRAINT `aux_vacancies_translation_ibfk_1` FOREIGN KEY (`vacancy_id`) REFERENCES `aux_vacancies` (`vacancy_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `aux_vacancies_translation_ibfk_2` FOREIGN KEY (`lang_id`) REFERENCES `share_languages` (`lang_id`) ON DELETE CASCADE;

SET FOREIGN_KEY_CHECKS=1;

COMMIT;