CREATE TABLE IF NOT EXISTS `ikm_questions` (
  `questionid` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `answer` longtext COLLATE utf8_unicode_ci NOT NULL,
  `lastupdated` datetime NOT NULL,
  `related` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `visible` tinyint(1) NOT NULL DEFAULT '0',
  `sortorder` int(11) NOT NULL DEFAULT '0',
  `userid` int(11) NOT NULL DEFAULT '0',
  `views` int(11) NOT NULL DEFAULT '0',
  `posvotes` int(11) NOT NULL DEFAULT '0',
  `negvotes` int(11) NOT NULL DEFAULT '0',
  `score` int(11) NOT NULL DEFAULT '0',
  `emailed` int(11) NOT NULL DEFAULT '0',
  `detectrelated` tinyint(1) NOT NULL DEFAULT '0',
  `metakeywords` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `metadescription` text COLLATE utf8_unicode_ci NOT NULL,
  `startdate` datetime NOT NULL,
  `enableexpiry` tinyint(4) NOT NULL,
  `expirydate` datetime NOT NULL,
  `featured` tinyint(1) NOT NULL DEFAULT '0',
  `workflowstatus` enum('auto_approved','approved','pending','re_pending','disapproved') COLLATE utf8_unicode_ci DEFAULT 'auto_approved',
  PRIMARY KEY (`questionid`),
  KEY `idx_q_userid` (`userid`),
  KEY `idx_q_score` (`score`),
  KEY `idx_q_lastupdated` (`lastupdated`),
  KEY `idx_q_title_sortorder` (`title`,`sortorder`),
  FULLTEXT KEY `idx_q_ft` (`title`,`answer`,`metakeywords`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;
 
CREATE TABLE IF NOT EXISTS `ikm_categories` (
  `categoryid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `parentid` int(11) NOT NULL DEFAULT '0',
  `visits` int(11) NOT NULL DEFAULT '0',
  `pass` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `icon` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `security` enum('public','private') COLLATE utf8_unicode_ci NOT NULL,
  `approvaltype` int(11) NOT NULL DEFAULT '0',
  `catorder` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`categoryid`),
  KEY `ikm_cat_parentid` (`parentid`),
  KEY `idx_c_name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;
 
CREATE TABLE IF NOT EXISTS `ikm_categoryassociations` (
  `categoryid` int(11) NOT NULL DEFAULT '0',
  `questionid` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `idx_a_pk` (`categoryid`,`questionid`),
  KEY `idx_ca_questioncategoryids` (`questionid`,`categoryid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
CREATE TABLE IF NOT EXISTS `ikm_catassoc_history` (
  `categoryid` int(11) NOT NULL DEFAULT '0',
  `questionid` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `idx_uqcah_pk` (`categoryid`,`questionid`),
  KEY `idx_cah_questioncategoryids` (`questionid`,`categoryid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
CREATE TABLE IF NOT EXISTS `ikm_question_auth` (
  `questionauthid` int(11) NOT NULL AUTO_INCREMENT,
  `questionid` int(11) NOT NULL,
  `groupid` int(11) NOT NULL,
  PRIMARY KEY (`questionauthid`),
  UNIQUE KEY `questionid` (`questionid`,`groupid`),
  KEY `groupid` (`groupid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

SELECT q.questionid, q.title, q.answer, q.workflowstatus FROM ikm_questions q WHERE
q.questionid in ( 

SELECT distinct q.questionid FROM ikm_categories c, ikm_categoryassociations a,
ikm_catassoc_history ca, (ikm_question_auth qa RIGHT JOIN ikm_questions q ON qa.questionid
= q.questionid) WHERE a.categoryid='2' AND a.questionid = q.questionid AND c.categoryid =
a.categoryid AND a.questionid = ca.questionid AND ca.categoryid = a.categoryid AND
q.visible=1 AND ( ((q.startdate < '2009-03-31 02:00:07') AND ((q.enableexpiry = 1) AND
q.expirydate > '2009-03-31 02:00:07')) OR ((q.startdate < '2009-03-31 02:00:07') AND
(q.enableexpiry = 0)) ) AND a.categoryid IN ('2', '3', '5', '4', '6', '7') AND
((qa.groupid IS NULL)) 
) 

ORDER BY q.sortorder DESC,q.lastupdated DESC, q.title ASC LIMIT 0, 20
;