SET sql_mode = '';

--
-- Table structure for table `pm_activity_logs`
--

CREATE TABLE `pm_activity_logs` (
  `activity_id` int(11) NOT NULL AUTO_INCREMENT,
  `description` text DEFAULT NULL,
  `user_id` varchar(11) DEFAULT NULL,
  `date_time` datetime DEFAULT NULL,
  PRIMARY KEY (`activity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `pm_employee_projects`
--

CREATE TABLE `pm_employee_projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` varchar(11) DEFAULT NULL,
  `employee_id` varchar(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `pm_clients`
--

CREATE TABLE `pm_clients` (
  `client_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(250) DEFAULT NULL,
  `client_name` varchar(250) DEFAULT NULL,
  `email` varchar(250) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `created_by` varchar(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `pm_projects`
--

CREATE TABLE `pm_projects` (
  `project_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_parent_project_id` int(11) DEFAULT 0 COMMENT 'if create any new version of existing project. it will always remaan the first parent id.',
  `second_parent_project_id` int(20) DEFAULT 0 COMMENT 'it will use for backlogs task transfer.',
  `version_no` varchar(20) DEFAULT '1' COMMENT 'It will increment always, after creating new version, otherwise always 1',
  `project_name` varchar(250) DEFAULT NULL,
  `client_id` varchar(11) DEFAULT NULL,
  `project_lead` varchar(11) DEFAULT NULL,
  `approximate_tasks` varchar(50) DEFAULT NULL,
  `complete_tasks` varchar(20) DEFAULT NULL,
  `start_date` date DEFAULT NULL COMMENT 'when the first sprint is started of any project',
  `project_start_date` date DEFAULT NULL COMMENT 'On project creation, this date will be defined',
  `close_date` date DEFAULT NULL COMMENT 'when project is being closed from project update.',
  `project_duration` varchar(20) DEFAULT NULL,
  `completed_days` varchar(20) DEFAULT NULL COMMENT 'days passed from start date of the project',
  `project_summary` text DEFAULT NULL,
  `is_completed` varchar(11) DEFAULT '0' COMMENT 'can complete forcefully or manually be completed',
  `project_reward_point` varchar(20) NOT NULL DEFAULT '0' COMMENT 'this point will be given to all the employee of this project',
  `created_at` datetime DEFAULT NULL,
  `created_by` varchar(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `pm_sprints`
--

CREATE TABLE `pm_sprints` (
  `sprint_id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` varchar(11) DEFAULT NULL COMMENT 'under a project',
  `sprint_name` varchar(500) DEFAULT NULL,
  `duration` varchar(50) DEFAULT NULL COMMENT 'in days',
  `start_date` date DEFAULT NULL,
  `close_date` date DEFAULT NULL,
  `sprint_goal` text DEFAULT NULL,
  `is_finished` int(3) DEFAULT 0,
  `created_at` datetime DEFAULT NULL,
  `created_by` varchar(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`sprint_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `pm_tasks_list`
--

CREATE TABLE `pm_tasks_list` (
  `task_id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` varchar(11) DEFAULT NULL,
  `sprint_id` varchar(11) DEFAULT NULL,
  `summary` text DEFAULT NULL,
  `description` text DEFAULT NULL,
  `project_lead` varchar(11) DEFAULT NULL COMMENT 'Reporter of the project',
  `employee_id` varchar(11) DEFAULT NULL COMMENT 'Team members',
  `priority` int(5) DEFAULT NULL COMMENT 'high = 2 or 1 = medium or low = 0',
  `attachment` text DEFAULT NULL,
  `task_status` varchar(50) DEFAULT '1' COMMENT 'to do =1 , in progress = 2 or done = 3',
  `is_task` int(3) DEFAULT 0 COMMENT 'if 0 remain in backlogs else show in task',
  `created_at` datetime DEFAULT NULL,
  `created_by` varchar(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Table structure for table `schdule_purchse_info`
--

CREATE TABLE `schdule_purchse_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `purchase_key` varchar(100) DEFAULT NULL,
  `domain` varchar(200) DEFAULT NULL,
  `ip_address` varchar(100) DEFAULT NULL,
  `port` varchar(11) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Dumping data for table `language`
--

INSERT INTO `language` (`phrase`, `english`) VALUES
('projectmanagement', 'Project Management'),
('clients', 'Clients'),
('add_new_client', 'Add New Client'),
('client_name', 'Client Name'),
('manage_clients', 'Manage Clients'),
('update_client', 'Update Client'),
('projects', 'Projects'),
('add_new_project', 'Add New Project'),
('manage_projects', 'Manage Projects'),
('project_name', 'Project Name'),
('project_lead', 'Project Lead'),
('approximate_tasks', 'Approximate Tasks'),
('summary', 'Summary'),
('project_duration', 'Project Duration'),
('update_project', 'Update Project'),
('task', 'Task'),
('create_task', 'Create Task'),
('manage_tasks', 'Manage Tasks'),
('team_member', 'Team Member'),
('priority', 'Priority'),
('reporter', 'Reporter'),
('assignee', 'Assignee'),
('sprint', 'Sprint'),
('sprints', 'Sprints'),
('create_sprint', 'Create Sprint'),
('sprint_name', 'Sprint Name'),
('duration', 'Duration'),
('sprint_goal', 'Sprint Goal'),
('backlogs', 'Backlogs'),
('manage_sprints', 'Manage Sprints'),
('manage_backlogs', 'Manage Backlogs'),
('transfer_tasks', 'Transfer Tasks'),
('create_date', 'Create Date'),
('transfer_to_backlogs', 'Transfer tasks to Backlogs'),
('all_tasks', 'All Tasks'),
('kanban_board', 'Kanban Board'),
('own_tasks', 'Own Tasks'),
('previous_version', 'Previous Version'),
('get_retros', 'Get Retros'),
('starting_date', 'Start Date'),
('pm_reports', 'Reports'),
('project_lists', 'Project Lists'),
('to_do', 'To Do'),
('in_progress', 'In Progress'),
('created_by', 'Created By'),
('client', 'Client'),
('reward_points', 'Reward Points'),
('sprint_started', 'Sprint Start'),
('ending_date', 'End Date'),
('team_members', 'Team Members'),
('inactive_employee', 'Inactive Employee'),
('manage_inactive_employee', 'Manage Inactive Employee'),
('attachment', 'Attachment'),
('company', 'Company');


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

--
-- Dumping data for table `sec_menu_item`
--

INSERT INTO `sec_menu_item` (`menu_title`, `page_url`, `module`, `parent_menu`, `is_report`, `createby`, `createdate`) VALUES
('clients', 'clients', 'projectmanagement', 0, 0, 1, '2021-03-10 00:00:00'),
('projects', 'projects', 'projectmanagement', 0, 0, 1, '2021-03-10 00:00:00'),
('task', 'task', 'projectmanagement', 0, 0, 1, '2021-03-10 00:00:00'),
('sprint', 'sprint', 'projectmanagement', 0, 0, 1, '2021-03-10 00:00:00'),
('manage_tasks', 'manage_tasks', 'projectmanagement', 0, 0, 1, '2021-03-10 00:00:00'),
('pm_reports', '', 'projectmanagement', 0, 0, 1, '2021-03-10 00:00:00'),
('project_lists', 'project_lists', 'projectmanagement', 0, 0, 1, '2021-03-10 00:00:00'),
('team_member', 'team_member', 'projectmanagement', 0, 0, 1, '2021-03-10 00:00:00');

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

-- Dumping data for table `employee_history`
--
ALTER TABLE `employee_history` ADD `employee_status` TINYINT(1) NOT NULL DEFAULT '1' AFTER `employee_id`;

COMMIT;
