-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `gcp_new`
--

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

--
-- Table structure for table `department_document`
--

CREATE TABLE `department_document` (
  `id` int(11) NOT NULL,
  `doc_name` int(11) NOT NULL,
  `role` int(11) NOT NULL,
  `user` int(11) NOT NULL,
  `license_details_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `department_report`
--

CREATE TABLE `department_report` (
  `id` int(11) NOT NULL,
  `role` int(11) NOT NULL,
  `user` int(11) NOT NULL,
  `IO` varchar(100) NOT NULL,
  `created_by` varchar(100) NOT NULL,
  `field_visited_by` varchar(100) NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `doc` varchar(100) NOT NULL,
  `recommended` int(11) NOT NULL,
  `license_details_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `licence_comments`
--

CREATE TABLE `licence_comments` (
  `id` int(11) NOT NULL,
  `license_details_id` int(11) NOT NULL,
  `comments` varchar(1000) NOT NULL,
  `user_id` int(11) NOT NULL,
  `role` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `licence_details`
--

CREATE TABLE `licence_details` (
  `id` int(11) NOT NULL,
  `license_department` int(11) NOT NULL,
  `lando` int(11) NOT NULL,
  `traffic` int(11) NOT NULL,
  `IS_department` int(11) NOT NULL,
  `ccp_department` int(11) NOT NULL,
  `sao_update_from_other_department` int(11) NOT NULL,
  `a` int(11) NOT NULL,
  `status` int(11) NOT NULL COMMENT 'in progress,rejected,clarification..',
  `user_id` int(11) NOT NULL,
  `licence_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `licence_query`
--

CREATE TABLE `licence_query` (
  `id` int(11) NOT NULL,
  `license_details_id` int(11) NOT NULL,
  `query` varchar(500) NOT NULL,
  `doc` varchar(100) NOT NULL,
  `is_resolved` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `memo`
--

CREATE TABLE `memo` (
  `id` int(11) NOT NULL,
  `role` int(11) NOT NULL,
  `user` int(11) NOT NULL,
  `license_details_id` int(11) NOT NULL,
  `is_finalized` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `memo_details`
--

CREATE TABLE `memo_details` (
  `id` int(11) NOT NULL,
  `memo_id` int(11) NOT NULL,
  `our_ref_no` varchar(100) NOT NULL,
  `to_officer` int(11) NOT NULL,
  `copy` int(11) NOT NULL,
  `other_information` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`other_information`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `m_category`
--

CREATE TABLE `m_category` (
  `id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `m_category`
--

INSERT INTO `m_category` (`id`, `name`, `status`) VALUES
(1, 'licence', 1),
(2, 'NOC', 1);

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

--
-- Table structure for table `m_city`
--

CREATE TABLE `m_city` (
  `id` int(11) NOT NULL,
  `state_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `m_country`
--

CREATE TABLE `m_country` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `m_department`
--

CREATE TABLE `m_department` (
  `id` int(11) NOT NULL,
  `section_name` varchar(200) NOT NULL,
  `status` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `m_department_roles`
--

CREATE TABLE `m_department_roles` (
  `id` int(11) NOT NULL,
  `m_deparrtment_id` int(11) NOT NULL,
  `designation` varchar(200) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `m_gcp_work_flow`
--

CREATE TABLE `m_gcp_work_flow` (
  `id` int(11) NOT NULL,
  `workflow` int(11) NOT NULL,
  `level` int(11) NOT NULL,
  `next_level` int(11) NOT NULL,
  `role` int(11) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `m_licence_list`
--

CREATE TABLE `m_licence_list` (
  `id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `workflow` varchar(50) NOT NULL COMMENT 'm_workflow tables id',
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `m_state`
--

CREATE TABLE `m_state` (
  `id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `m_workflow`
--

CREATE TABLE `m_workflow` (
  `id` int(11) NOT NULL,
  `workflow` varchar(100) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `m_workflow`
--

INSERT INTO `m_workflow` (`id`, `workflow`, `status`) VALUES
(1, 'L&O', 1),
(2, 'IS', 1);

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

--
-- Table structure for table `personal_information`
--

CREATE TABLE `personal_information` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `relation` varchar(100) NOT NULL,
  `relation_name` varchar(100) NOT NULL,
  `gender` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `state_id` int(11) NOT NULL,
  `city_id` int(11) NOT NULL,
  `date_of_birth` int(11) NOT NULL,
  `country_born` int(11) NOT NULL,
  `email` varchar(100) NOT NULL,
  `pan` varchar(150) NOT NULL,
  `adhaar` int(11) NOT NULL,
  `img` varchar(200) NOT NULL,
  `passport_number` int(11) NOT NULL,
  `passport_issue_date` int(11) NOT NULL,
  `passport_expire_date` int(11) NOT NULL,
  `passport_issue_state` int(11) NOT NULL,
  `passport_issue_city` int(11) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `theatre_documentation`
--

CREATE TABLE `theatre_documentation` (
  `id` int(11) NOT NULL,
  `theatre_information_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `doc_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `theatre_information`
--

CREATE TABLE `theatre_information` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `license_details_id` int(11) NOT NULL,
  `address` varchar(500) NOT NULL,
  `validity` date NOT NULL,
  `renewal_request` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `user_department_role`
--

CREATE TABLE `user_department_role` (
  `id` int(11) NOT NULL,
  `m_department_designation_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `user_type`
--

CREATE TABLE `user_type` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `user_type`
--

INSERT INTO `user_type` (`id`, `name`, `status`) VALUES
(1, 'TN Police Department', 1),
(2, 'Public', 1),
(3, 'Organisation', 1);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `department_document`
--
ALTER TABLE `department_document`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `department_report`
--
ALTER TABLE `department_report`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `licence_comments`
--
ALTER TABLE `licence_comments`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `licence_query`
--
ALTER TABLE `licence_query`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `memo`
--
ALTER TABLE `memo`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `memo_details`
--
ALTER TABLE `memo_details`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `m_category`
--
ALTER TABLE `m_category`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `m_country`
--
ALTER TABLE `m_country`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `m_department`
--
ALTER TABLE `m_department`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `m_department_roles`
--
ALTER TABLE `m_department_roles`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `m_gcp_work_flow`
--
ALTER TABLE `m_gcp_work_flow`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `m_licence_list`
--
ALTER TABLE `m_licence_list`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `m_workflow`
--
ALTER TABLE `m_workflow`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `personal_information`
--
ALTER TABLE `personal_information`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `theatre_documentation`
--
ALTER TABLE `theatre_documentation`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `theatre_information`
--
ALTER TABLE `theatre_information`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `user_department_role`
--
ALTER TABLE `user_department_role`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `user_type`
--
ALTER TABLE `user_type`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `department_document`
--
ALTER TABLE `department_document`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `department_report`
--
ALTER TABLE `department_report`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `licence_comments`
--
ALTER TABLE `licence_comments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `licence_query`
--
ALTER TABLE `licence_query`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `memo`
--
ALTER TABLE `memo`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `memo_details`
--
ALTER TABLE `memo_details`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `m_category`
--
ALTER TABLE `m_category`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `m_country`
--
ALTER TABLE `m_country`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `m_department`
--
ALTER TABLE `m_department`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `m_department_roles`
--
ALTER TABLE `m_department_roles`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `m_gcp_work_flow`
--
ALTER TABLE `m_gcp_work_flow`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `m_licence_list`
--
ALTER TABLE `m_licence_list`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `m_workflow`
--
ALTER TABLE `m_workflow`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `personal_information`
--
ALTER TABLE `personal_information`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `theatre_documentation`
--
ALTER TABLE `theatre_documentation`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `theatre_information`
--
ALTER TABLE `theatre_information`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `user_department_role`
--
ALTER TABLE `user_department_role`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `user_type`
--
ALTER TABLE `user_type`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;

