-- ALTER TABLE tablename AUTO_INCREMENT = 1

---------------------- # Create table # -------------------------------------
-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.16-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win64
-- HeidiSQL Version:             11.3.0.6295
-- --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
;

/*!40101 SET NAMES utf8 */
;

/*!50503 SET NAMES utf8mb4 */
;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */
;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */
;

-- Dumping structure for table payroll_local_db.acm_client_portals
CREATE TABLE IF NOT EXISTS `acm_client_portals` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`portal_id` int(11) DEFAULT 0,
	`client_id` int(11) DEFAULT 0,
	`status` int DEFAULT NULL,
	`on_created` datetime DEFAULT NULL,
	`on_updated` datetime DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `FK1_acm_portals_clients` (`portal_id`),
	CONSTRAINT `FK1_acm_portals_clients` FOREIGN KEY (`portal_id`) REFERENCES `acm_portals` (`id`) ON DELETE
	SET
		NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- Dumping data for table payroll_local_db.acm_client_portals: ~0 rows (approximately)
/*!40000 ALTER TABLE `acm_client_portals` DISABLE KEYS */
;

/*!40000 ALTER TABLE `acm_client_portals` ENABLE KEYS */
;

-- Dumping structure for table payroll_local_db.acm_group_roles
CREATE TABLE IF NOT EXISTS `acm_group_roles` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`group_name` varchar(45) DEFAULT NULL,
	`roles_portal_id` int(11) DEFAULT NULL,
	`platform` varchar(45) DEFAULT NULL,
	`cid` varchar(45) NOT NULL,
	`status` varchar(45) DEFAULT NULL,
	`created_at` varchar(45) NOT NULL,
	`updated_at` varchar(45) DEFAULT NULL,
	`created_by` varchar(45) NOT NULL,
	`updated_by` varchar(45) DEFAULT NULL,
	`link` varchar(50) NOT NULL,
	PRIMARY KEY (`id`),
	KEY `FK1_group_roles_portal_id` (`roles_portal_id`),
	CONSTRAINT `FK1_group_roles_portal_id` FOREIGN KEY (`roles_portal_id`) REFERENCES `acm_client_portals` (`id`) ON DELETE
	SET
		NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- Dumping data for table payroll_local_db.acm_group_roles: ~0 rows (approximately)
/*!40000 ALTER TABLE `acm_group_roles` DISABLE KEYS */
;

/*!40000 ALTER TABLE `acm_group_roles` ENABLE KEYS */
;

-- Dumping structure for table payroll_local_db.acm_group_role_menu
CREATE TABLE IF NOT EXISTS `acm_group_role_menu` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`menus_id` int(11) DEFAULT NULL,
	`client_portal_id` int(11) DEFAULT NULL,
	`platform` varchar(45) DEFAULT NULL,
	`status` int(2) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `FK1_group_role_menu_client_portal_id` (`client_portal_id`),
	KEY `FK2_group_role_menu_id` (`menus_id`),
	CONSTRAINT `FK1_group_role_menu_client_portal_id` FOREIGN KEY (`client_portal_id`) REFERENCES `acm_client_portals` (`id`) ON DELETE
	SET
		NULL ON UPDATE CASCADE,
		CONSTRAINT `FK2_group_role_menu_id` FOREIGN KEY (`menus_id`) REFERENCES `acm_menus` (`id`) ON DELETE
	SET
		NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- Dumping data for table payroll_local_db.acm_group_role_menu: ~0 rows (approximately)
/*!40000 ALTER TABLE `acm_group_role_menu` DISABLE KEYS */
;

/*!40000 ALTER TABLE `acm_group_role_menu` ENABLE KEYS */
;

-- Dumping structure for table payroll_local_db.acm_menus
CREATE TABLE IF NOT EXISTS `acm_menus` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`menu_title` varchar(50) NOT NULL,
	`parent_id` int(11) NOT NULL,
	`menu_order` tinyint(4) NOT NULL,
	`menu_type_id` int(4) DEFAULT NULL,
	`level` tinyint(4) DEFAULT NULL,
	`client_id` int(11) NOT NULL,
	`icon` varchar(50) DEFAULT NULL,
	`img` varchar(50) DEFAULT NULL,
	`component_name` varchar(100) DEFAULT NULL,
	`link_file_name` varchar(100) DEFAULT NULL,
	`page_menu_title` varchar(50) DEFAULT NULL,
	`isDeleted` int(1) NOT NULL,
	`created_by` int(255) DEFAULT NULL,
	`updated_at` datetime DEFAULT NULL,
	`created_at` datetime DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `FK1_menus_type_id` (`menu_type_id`),
	CONSTRAINT `FK1_menus_type_id` FOREIGN KEY (`menu_type_id`) REFERENCES `acm_menus_type` (`id`) ON DELETE
	SET
		NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = latin1;

-- Dumping data for table payroll_local_db.acm_menus: ~0 rows (approximately)
/*!40000 ALTER TABLE `acm_menus` DISABLE KEYS */
;

/*!40000 ALTER TABLE `acm_menus` ENABLE KEYS */
;

-- Dumping structure for table payroll_local_db.acm_menus_type
CREATE TABLE IF NOT EXISTS `acm_menus_type` (
	`id` int(4) NOT NULL AUTO_INCREMENT,
	`menu_type` varchar(100) NOT NULL,
	`created_by` int(255) DEFAULT NULL,
	`created_at` datetime DEFAULT NULL,
	`updated_at` datetime DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 5 DEFAULT CHARSET = latin1;


CREATE TABLE `acm_group_role_association` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`menu_id` INT NULL,
	`group_role_id` INT NULL,
	`delete` INT(1) NULL DEFAULT '0',
	`view` INT(1) NULL DEFAULT '0',
	`update` INT(1) NULL DEFAULT '0',
	`right` INT(1) NULL DEFAULT '0',
	`on_created` DATETIME NULL,
	`on_updated` DATETIME NULL,
	PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
;

-- Dumping data for table payroll_local_db.acm_menus_type: ~4 rows (approximately)
/*!40000 ALTER TABLE `acm_menus_type` DISABLE KEYS */
;

REPLACE INTO `acm_menus_type` (
	`id`,
	`menu_type`,
	`created_by`,
	`created_at`,
	`updated_at`
)
VALUES
	(
		1,
		'landing_page',
		58448,
		'2020-05-05 11:00:05',
		'0000-00-00 00:00:00'
	),
	(
		2,
		'menu',
		58448,
		'2020-05-05 11:01:09',
		'0000-00-00 00:00:00'
	),
	(
		3,
		'landing_page+menu',
		58448,
		'2020-05-05 11:01:09',
		'0000-00-00 00:00:00'
	),
	(
		4,
		'only_landing_page_menu',
		58472,
		'2020-07-10 12:01:47',
		'0000-00-00 00:00:00'
	);

/*!40000 ALTER TABLE `acm_menus_type` ENABLE KEYS */
;

-- Dumping structure for table payroll_local_db.acm_portals
CREATE TABLE IF NOT EXISTS `acm_portals` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`name` varchar(150) DEFAULT NULL,
	`cid` int(11) DEFAULT 48,
	`role` varchar(50) DEFAULT NULL,
	`status` int(11) DEFAULT 1,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 8 DEFAULT CHARSET = latin1;

-- Dumping data for table payroll_local_db.acm_portals: ~7 rows (approximately)
/*!40000 ALTER TABLE `acm_portals` DISABLE KEYS */
;

REPLACE INTO `acm_portals` (`id`, `name`, `cid`, `role`, `status`)
VALUES
	(1, 'Employee', 48, 'Employee', 1),
	(2, 'Line Manager', 48, 'LM', 1),
	(3, 'HR', 48, 'HR', 1),
	(4, 'Admin', 48, 'Admin', 1),
	(5, 'CEO', 48, 'CEO', 1),
	(6, 'System', 48, 'System', 1),
	(7, 'Finance', 48, 'Finance', 1);

/*!40000 ALTER TABLE `acm_portals` ENABLE KEYS */
;

-- Dumping structure for table payroll_local_db.acm_user_roles
CREATE TABLE IF NOT EXISTS `acm_user_roles` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`user_id` int(9) unsigned DEFAULT NULL,
	`group_role_id` int(11) DEFAULT NULL,
	`person_id` int(9) unsigned DEFAULT NULL,
	`cid` int(11) NOT NULL DEFAULT 48,
	`status` int(11) NOT NULL DEFAULT 1,
	`created_at` datetime DEFAULT NULL,
	`created_by` int(11) DEFAULT NULL,
	`updated_at` datetime DEFAULT NULL,
	`updated_by` int(11) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `FK1_employee_user_id` (`user_id`),
	KEY `FK2_group_role_id` (`group_role_id`),
	KEY `FK3_client_assists_person_id` (`person_id`),
	CONSTRAINT `FK1_employee_user_id` FOREIGN KEY (`user_id`) REFERENCES `proll_employee` (`id`) ON DELETE
	SET
		NULL ON UPDATE CASCADE,
		CONSTRAINT `FK2_group_role_id` FOREIGN KEY (`group_role_id`) REFERENCES `acm_group_roles` (`id`) ON DELETE
	SET
		NULL ON UPDATE CASCADE,
		CONSTRAINT `FK3_client_assists_person_id` FOREIGN KEY (`person_id`) REFERENCES `proll_client_assist` (`id`) ON DELETE
	SET
		NULL ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- Dumping data for table payroll_local_db.acm_user_roles: ~0 rows (approximately)
/*!40000 ALTER TABLE `acm_user_roles` DISABLE KEYS */
;

/*!40000 ALTER TABLE `acm_user_roles` ENABLE KEYS */
;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */
;

/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */
;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */
;

/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */
;

-------------------------------- # ALTER TABLE # ----------------------------------------------
-- tb group_role_menu
ALTER TABLE
	`group_role_menu` CHANGE COLUMN `cid` `cid` INT(11) NULL DEFAULT NULL
AFTER
	`portal_type`;

-- tb acm_client_portal
ALTER TABLE
	`acm_client_portal`
ADD
	COLUMN `status` INT NULL DEFAULT NULL
AFTER
	`on_created`;

-- tb menu
ALTER TABLE
	`menu`
ADD
	COLUMN `isDeleted` INT NULL DEFAULT '0'
AFTER
	`created_at`;











	---------------------------------------------------------------------------
DELETE FROM acm_client_portals;
DELETE FROM acm_group_roles;
DELETE FROM acm_group_role_association;
DELETE FROM acm_group_role_menu;
DELETE FROM acm_menus;
DELETE FROM acm_portals;











ALTER TABLE acm_client_portals AUTO_INCREMENT = 1;
ALTER TABLE acm_group_roles AUTO_INCREMENT = 1;
ALTER TABLE acm_group_role_association AUTO_INCREMENT = 1;
ALTER TABLE acm_group_role_menu AUTO_INCREMENT = 1;
ALTER TABLE acm_menus AUTO_INCREMENT = 1;
ALTER TABLE acm_portals AUTO_INCREMENT = 1;
