CREATE TABLE IF NOT EXISTS `publication_document` (
  `document_id` int(11) NOT NULL AUTO_INCREMENT,
  `document_date` date DEFAULT NULL,
  `document_category` varchar(255) DEFAULT NULL,
  `document_subcategory` varchar(255) DEFAULT NULL,
  `document_status` varchar(255) DEFAULT NULL,
  `document_sorting` int(11) DEFAULT '0' NULL,
  `document_image` varchar(255) DEFAULT NULL,
  `document_bckgd` 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 (`document_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `publication_document_translation` (
  `document_translation_id` int(11) NOT NULL AUTO_INCREMENT,
  `document_translation_parent_id` int(11) DEFAULT NULL,
  `document_translation_title` text CHARACTER SET utf8mb4 DEFAULT NULL,
  `document_translation_content` text CHARACTER SET utf8mb4 DEFAULT NULL,
  `document_translation_main` int(11) DEFAULT NULL,
  `document_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 (`document_translation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `publication_version` (
  `version_id` int(11) NOT NULL AUTO_INCREMENT,
  `document_id` int(11) DEFAULT NULL,  
  `version_main` varchar(255) DEFAULT NULL,  
  `version_no` int(11) DEFAULT NULL,
  `version_title` text CHARACTER SET utf8mb4 DEFAULT NULL,
  `version_content` text CHARACTER SET utf8mb4 DEFAULT NULL,
  `version_language` varchar(255) DEFAULT NULL,
  `version_img` varchar(255) DEFAULT NULL,
  `version_date_start` date DEFAULT NULL,
  `version_date_end` date DEFAULT NULL,
  `version_status` varchar(255) DEFAULT NULL,
  `version_pdf` varchar(255) DEFAULT NULL,
  `version_zip` varchar(255) DEFAULT NULL,  
  `approval_status` varchar(255) DEFAULT NULL,
  `approval_at` datetime DEFAULT NULL,
  `approval_by` int(11) 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 (`version_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 
('DOCUMENTSTATUS','active','Aktif','Active',1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTSTATUS','inactive','Tidak Aktif','Inactive',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTSTATUS','archived','Arkib','Archived',3,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTCATEGORY','doccategory01','Pekeliling','Strategic Direction & Plan',1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTCATEGORY','doccategory02','ISO','ISO',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTCATEGORY','doccategory03','Dokumen Jabatan','Document Department',3,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTCATEGORY','doccategory04','Senarai Perwakilan Kuasa','List of Power Attorney',4,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTCATEGORY','doccategory05','Senarai Perwakilan','List of Power',5,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTSUBCATEGORY','docsubcategory01','Garis Panduan','Guideline',1,'doccategory03',NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTSUBCATEGORY','docsubcategory02','Borang','Form',2,'doccategory03',NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTSUBCATEGORY','docsubcategory03','Dokumen','Document',3,'doccategory03',NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTSUBCATEGORY','docsubcategory04','Manual Pengguna','User Manual',4,'doccategory03',NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTLANGUAGE','Malay','Bahasa Melayu','Malay',7,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTLANGUAGE','Chinese','Bahasa Cina','Chinese',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTLANGUAGE','English','Bahasa Inggeris','English',3,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTLANGUAGE','Hindi','Bahasa Hindi','Hindi',4,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTLANGUAGE','Arabic','Bahasa Arab','Arabic',1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTLANGUAGE','Japanese','Bahasa Jepun','Japanese',5,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTLANGUAGE','Tamil','Bahasa Tamil','Tamil',8,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('DOCUMENTLANGUAGE','Korean','Bahasa Korea','Korean',6,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('VERSIONSTATUS','active','Aktif','Active',1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('VERSIONSTATUS','inactive','Tidak Aktif','Inactive',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('VERSIONSTATUS','archived','Arkib','Archived',3,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('APPROVALSTATUS','appStatus01','Baru','New',1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('APPROVALSTATUS','appStatus03','Ditolak','Reject',3,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('APPROVALSTATUS','appStatus02','Diluluskan','Approve',2,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('VERSIONMAIN','versionMain01','Ya','Yes',1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
('VERSIONMAIN','versionMain02','Tidak','No',2,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_publication` 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 
('PublicationDocumentList','This is the API to display all the list of document','<?php \r\n\r\n$sql = \"SELECT * FROM publication_document_translation t1 \r\n    INNER JOIN publication_document t2 ON t1.document_translation_parent_id=t2.document_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[\'document_translation_language\'];\r\n        $arr2[\'title\'] = $row[\'document_translation_title\'];\r\n        $arr2[\'content\'] = $row[\'document_translation_content\'];\r\n        \r\n         $arr2[\'category\'] = $row[\'document_category\'];\r\n        $arr2[\'subcategory\'] = $row[\'document_subcategory\'];\r\n        $arr2[\'status\'] = $row[\'document_status\'];\r\n        $arr2[\'document_date\'] = $row[\'document_date\'];\r\n        $arr2[\'sorting\'] = $row[\'document_sorting\'];\r\n        $arr2[\'thumbnail\'] = $row[\'document_image\'];\r\n        $arr2[\'background\'] = $row[\'document_bckgd\'];\r\n        \r\n        $arr[] = $arr2;\r\n    }\r\n}\r\necho json_encode([\'result_status\'=>$status,\'data\'=>$arr]);',NOW(),3,NOW(),3),
('PublicationDocumentDetails','This is the API to display the details of the document','<?php \r\n$document_id = \'\';\r\n$arr = [];\r\n$status = \'failed\';\r\n$remark = \'\';\r\nif(isset($_GET[\'document_id\'])&&!empty($_GET[\'document_id\'])){\r\n    $document_id = $_GET[\'document_id\'];\r\n    $sql = \"SELECT * FROM publication_document_translation t1 \r\n        INNER JOIN publication_document t2 ON t1.document_translation_parent_id=t2.document_id WHERE t2.document_id=\".$document_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[\'document_translation_language\'];\r\n            $arr2[\'title\'] = $row[\'document_translation_title\'];\r\n            $arr2[\'content\'] = $row[\'document_translation_content\'];\r\n            \r\n           $arr2[\'category\'] = $row[\'document_category\'];\r\n            $arr2[\'subcategory\'] = $row[\'document_subcategory\'];\r\n            $arr2[\'status\'] = $row[\'document_status\'];\r\n            $arr2[\'document_date\'] = $row[\'document_date\'];\r\n            $arr2[\'sorting\'] = $row[\'document_sorting\'];\r\n            $arr2[\'thumbnail\'] = $row[\'document_image\'];\r\n            $arr2[\'background\'] = $row[\'document_bckgd\'];\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 document_id using $_GET method\';\r\n}\r\necho json_encode([\'result_status\'=>$status,\'remark\'=>$remark,\'data\'=>$arr]);',NOW(),3,NOW(),3),
('PublicationVersion','This is the API to display the version of the document','<?php \r\n$document_id = \'\';\r\n$arr = [];\r\n$status = \'failed\';\r\n$remark = \'\';\r\nif(isset($_GET[\'document_id\'])&&!empty($_GET[\'document_id\'])){\r\n    $document_id = $_GET[\'document_id\'];\r\n    $sql = \"SELECT * FROM publication_version t1 \r\n        INNER JOIN publication_document t2 ON t1.document_id=t2.document_id INNER JOIN publication_document_translation t3 ON t2.document_translation_parent_id=t3.document_id WHERE t2.document_id=\".$document_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[\'document_translation_language\'];\r\n            $arr2[\'title\'] = $row[\'document_translation_title\'];\r\n            $arr2[\'content\'] = $row[\'document_translation_content\'];\r\n            \r\n           $arr2[\'category\'] = $row[\'document_category\'];\r\n            $arr2[\'subcategory\'] = $row[\'document_subcategory\'];\r\n            $arr2[\'status\'] = $row[\'document_status\'];\r\n            $arr2[\'document_date\'] = $row[\'document_date\'];\r\n            $arr2[\'sorting\'] = $row[\'document_sorting\'];\r\n            $arr2[\'thumbnail\'] = $row[\'document_image\'];\r\n            $arr2[\'version_no\'] = $row[\'version_no\'];\r\n            $arr2[\'version_title\'] = $row[\'version_title\'];\r\n            $arr2[\'version_content\'] = $row[\'version_content\'];\r\n            $arr2[\'version_img\'] = $row[\'version_img\'];\r\n            $arr2[\'version_pdf\'] = $row[\'version_pdf\'];\r\n            $arr2[\'version_zip\'] = $row[\'version_zip\'];\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 document_id using $_GET method\';\r\n}\r\necho json_encode([\'result_status\'=>$status,\'remark\'=>$remark,\'data\'=>$arr]);',NOW(),3,NOW(),3);

insert  into `backend_menu`(`menu_name`,`menu_param`,`menu_class`,`menu_link`,`menu_active_route`,`menu_parent_id`,`menu_status`) values 
('Document','','fa fa-book','#','',0,1),
('List','','fa fa-file-text','/publicationDocument/publication-document/index','',0,1),
('Check','','fa fa-check-circle','/publicationDocument/publication-version/check','',0,1);

insert  into `route`(`name`,`alias`,`type`,`status`) values 
  ('/publicationDocument/publication-document/index','index','publicationDocument/publication-document',1),
  ('/publicationDocument/publication-version/index','index','publicationDocument/publication-version',1),
  ('/publicationDocument/publication-version/check','check','publicationDocument/publication-version',1);