CREATE TABLE IF NOT EXISTS `content_article` (
  `article_id` int(11) NOT NULL AUTO_INCREMENT,
  `article_date` date DEFAULT NULL,
  `article_category` varchar(255) DEFAULT NULL,
  `article_subcategory` varchar(255) DEFAULT NULL,
  `article_status` varchar(255) DEFAULT NULL,
  `article_url` varchar(255) DEFAULT NULL,
  `article_sorting` int(11) DEFAULT NULL,
  `article_image` varchar(255) DEFAULT NULL,
  `article_portal_category` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `content_article_translation` (
  `article_translation_id` int(11) NOT NULL AUTO_INCREMENT,
  `article_translation_parent_id` int(11) DEFAULT NULL,
  `article_translation_title` text CHARACTER SET utf8mb4 DEFAULT NULL,
  `article_translation_content` text CHARACTER SET utf8mb4 DEFAULT NULL,
  `article_translation_tag` text CHARACTER SET utf8mb4 DEFAULT NULL,
  `article_translation_main` int(11) DEFAULT NULL,
  `article_translation_language` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`article_translation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `ref` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cat` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `descr` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `descr_en` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sort` int(11) DEFAULT NULL,
  `param1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `icon_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_by` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` int(11) DEFAULT NULL,
  `updated_by` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL,
  `deleted_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert  into `ref`(`cat`,`code`,`descr`,`descr_en`,`sort`,`param1`,`icon_name`,`created_by`,`created_at`,`updated_by`,`updated_at`,`deleted_at`) values 
('ARTICLESTATUS','active','Active',NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('ARTICLESTATUS','inactive','Inactive',NULL,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('ARTICLESTATUS','archived','Archived',NULL,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('ARTICLEPORTAL','PORTAL1','Main Portal',NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('ARTICLECATEGORY','articlecategory01','Main Article',NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('ARTICLELANGUAGE','Malay','Malay',NULL,7,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('ARTICLELANGUAGE','Chinese','Chinese',NULL,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('ARTICLELANGUAGE','English','English',NULL,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('ARTICLELANGUAGE','Hindi','Hindi',NULL,4,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('ARTICLELANGUAGE','Arabic','Arabic',NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('ARTICLELANGUAGE','Japanese','Japanese',NULL,5,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('ARTICLELANGUAGE','Tamil','Tamil',NULL,8,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('ARTICLELANGUAGE','Korean','Korean',NULL,6,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

CREATE TABLE IF NOT EXISTS `api` (
  `api_id` int(11) NOT NULL AUTO_INCREMENT,
  `api_code` varchar(255) DEFAULT NULL,
  `api_descr` text DEFAULT NULL,
  `api_content` text DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`api_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `api`(`api_code`,`api_descr`,`api_content`,`created_at`,`created_by`,`updated_at`,`updated_by`) values 
('ContentArticleList','This is the API to display all the list of article','<?php \r\n\r\n$sql = \"SELECT * FROM content_article_translation t1 \r\n    INNER JOIN content_article t2 ON t1.article_translation_parent_id=t2.article_id\";\r\n$model = Yii::$app->db->createCommand($sql)->queryAll();\r\n$arr = [];\r\n$status = \'failed\';\r\nif(!empty($model)){\r\n    foreach($model as $row){\r\n        $status = \'success\';\r\n        $arr2 = [];\r\n        $arr2[\'language\'] = $row[\'article_translation_language\'];\r\n        $arr2[\'title\'] = $row[\'article_translation_title\'];\r\n        $arr2[\'content\'] = $row[\'article_translation_content\'];\r\n        $arr2[\'tag\'] = $row[\'article_translation_tag\'];\r\n        \r\n        $arr2[\'portal_category\'] = $row[\'article_portal_category\'];\r\n        $arr2[\'category\'] = $row[\'article_category\'];\r\n        $arr2[\'subcategory\'] = $row[\'article_subcategory\'];\r\n        $arr2[\'status\'] = $row[\'article_status\'];\r\n        $arr2[\'article_date\'] = $row[\'article_date\'];\r\n        $arr2[\'sorting\'] = $row[\'article_sorting\'];\r\n        $arr2[\'url_related\'] = $row[\'article_url\'];\r\n        $arr2[\'thumbnail\'] = $row[\'article_image\'];\r\n        \r\n        $arr[] = $arr2;\r\n    }\r\n}\r\necho json_encode([\'result_status\'=>$status,\'data\'=>$arr]);','2021-03-12 13:38:32',3,'2021-03-12 14:08:23',3),
('ContentArticleDetails','This is the API to display the details of the article','<?php \r\n$article_id = \'\';\r\n$arr = [];\r\n$status = \'failed\';\r\n$remark = \'\';\r\nif(isset($_GET[\'article_id\'])&&!empty($_GET[\'article_id\'])){\r\n    $article_id = $_GET[\'article_id\'];\r\n    $sql = \"SELECT * FROM content_article_translation t1 \r\n        INNER JOIN content_article t2 ON t1.article_translation_parent_id=t2.article_id WHERE t2.article_id=\".$article_id;\r\n    $model = Yii::$app->db->createCommand($sql)->queryAll();\r\n    \r\n    if(!empty($model)){\r\n        foreach($model as $row){\r\n            $status = \'success\';\r\n            $arr2 = [];\r\n            $arr2[\'language\'] = $row[\'article_translation_language\'];\r\n            $arr2[\'title\'] = $row[\'article_translation_title\'];\r\n            $arr2[\'content\'] = $row[\'article_translation_content\'];\r\n            $arr2[\'tag\'] = $row[\'article_translation_tag\'];\r\n            \r\n            $arr2[\'portal_category\'] = $row[\'article_portal_category\'];\r\n            $arr2[\'category\'] = $row[\'article_category\'];\r\n            $arr2[\'subcategory\'] = $row[\'article_subcategory\'];\r\n            $arr2[\'status\'] = $row[\'article_status\'];\r\n            $arr2[\'article_date\'] = $row[\'article_date\'];\r\n            $arr2[\'sorting\'] = $row[\'article_sorting\'];\r\n            $arr2[\'url_related\'] = $row[\'article_url\'];\r\n            $arr2[\'thumbnail\'] = $row[\'article_image\'];\r\n            \r\n            $arr[] = $arr2;\r\n        }\r\n    }else{\r\n        $remark = \'Record not found\';\r\n    }\r\n    \r\n}else{\r\n    $remark = \'Please specify the article_id using $_GET method\';\r\n}\r\necho json_encode([\'result_status\'=>$status,\'remark\'=>$remark,\'data\'=>$arr]);','2021-03-12 14:02:57',3,'2021-03-12 14:12:10',3);

insert  into `backend_menu`(`menu_name`,`menu_param`,`menu_class`,`menu_link`,`menu_active_route`,`menu_parent_id`,`menu_status`) values 
('Content','','fa fa-book','#','',0,1),
('Article','','fa fa-file','/contentArticle/content-article/index','',0,1);

insert  into `route`(`name`,`alias`,`type`,`status`) values ('/contentArticle/content-article/index','index','contentArticle/content-article',1);