-- phpMyAdmin SQL Dump

-- version 5.0.2

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

--

-- Host: 127.0.0.1

-- Generation Time: Jun 23, 2020 at 03:42 PM

-- Server version: 10.4.11-MariaDB

-- PHP Version: 7.4.5



SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

START TRANSACTION;

SET time_zone = "+00:00";





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

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8mb4 */;



--

-- Database: `api_test`

--



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



--

-- Table structure for table `3rd_niid_web_service_register`

--



CREATE TABLE `3rd_niid_web_service_register` (

  `id` int(11) NOT NULL,

  `policy_no` varchar(50) NOT NULL,

  `insured_name` varchar(100) NOT NULL,

  `contact_address` varchar(250) NOT NULL,

  `gsm` varchar(20) NOT NULL,

  `email` varchar(50) NOT NULL,

  `eff_from_date` datetime NOT NULL,

  `eff_to_date` datetime NOT NULL,

  `type_of_cover` varchar(50) NOT NULL,

  `vehicle_category` varchar(50) NOT NULL,

  `engine_no` varchar(50) NOT NULL,

  `chasis_no` varchar(50) NOT NULL,

  `vehicle_color` varchar(20) DEFAULT 'TBA',

  `year_of_make` varchar(10) NOT NULL,

  `vehicle_make` varchar(50) NOT NULL,

  `registration_no` varchar(20) NOT NULL,

  `vehicle_type` varchar(50) NOT NULL,

  `engine_capacity` varchar(10) NOT NULL,

  `vehicle_model` varchar(50) NOT NULL,

  `sum_assured` varchar(10) NOT NULL,

  `premium` varchar(10) NOT NULL,

  `cover_note_no` varchar(10) NOT NULL,

  `certificate_no` varchar(10) DEFAULT NULL,

  `geographical_zone` varchar(10) NOT NULL,

  `date_attempted` datetime NOT NULL,

  `message` text NOT NULL,

  `is_successful` tinyint(4) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;



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



--

-- Table structure for table `access`

--



CREATE TABLE `access` (

  `id` int(11) UNSIGNED NOT NULL,

  `key` varchar(40) NOT NULL DEFAULT '',

  `all_access` tinyint(1) NOT NULL DEFAULT 0,

  `controller` varchar(50) NOT NULL DEFAULT '',

  `date_created` datetime DEFAULT NULL,

  `date_modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



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



--

-- Table structure for table `api_policy_details`

--



CREATE TABLE `api_policy_details` (

  `id` int(11) NOT NULL,

  `InsuredName` varchar(255) NOT NULL,

  `ContactAddress` varchar(255) NOT NULL,

  `GSMNo` varchar(255) DEFAULT NULL,

  `Email` varchar(255) NOT NULL,

  `EngineNo` varchar(255) NOT NULL,

  `ChasisNo` varchar(255) NOT NULL,

  `VehicleColor` varchar(255) NOT NULL,

  `YearOfMake` varchar(255) NOT NULL,

  `VehicleMake` varchar(255) NOT NULL,

  `RegistrationNo` varchar(25) NOT NULL,

  `VehicleType` varchar(255) NOT NULL,

  `EngineCapacity` varchar(255) NOT NULL,

  `VehicleModel` varchar(255) NOT NULL,

  `UseOFVehicle` varchar(25) NOT NULL,

  `DateCreated` datetime NOT NULL DEFAULT current_timestamp()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



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



--

-- Table structure for table `api_user`

--



CREATE TABLE `api_user` (

  `id` int(11) NOT NULL COMMENT 'Primary key',

  `username` varchar(150) COLLATE utf8_bin NOT NULL COMMENT 'username as assigned to client',

  `password` varchar(300) COLLATE utf8_bin NOT NULL COMMENT 'hashed ',

  `company_code` varchar(25) COLLATE utf8_bin DEFAULT NULL COMMENT 'Company code as assigned to client',

  `company_name` varchar(300) COLLATE utf8_bin NOT NULL COMMENT 'Full name of company',

  `company_address` tinytext COLLATE utf8_bin DEFAULT NULL COMMENT 'Address of company',

  `Phone` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT 'Phone number',

  `contact_person1` varchar(300) COLLATE utf8_bin DEFAULT NULL COMMENT 'Full name of first contact person',

  `contact_person1_phone` varchar(300) COLLATE utf8_bin DEFAULT NULL COMMENT 'Email of first contact person',

  `contact_person1_email` varchar(75) COLLATE utf8_bin DEFAULT NULL COMMENT 'Phone of second contact person',

  `contact_person2` varchar(300) COLLATE utf8_bin DEFAULT NULL COMMENT 'Full name of second contact person',

  `contact_person2_phone` varchar(25) COLLATE utf8_bin DEFAULT NULL COMMENT 'Phone of second contact person',

  `contact_person2_email` varchar(75) COLLATE utf8_bin DEFAULT NULL COMMENT 'Email of second contact person',

  `is_active` tinyint(4) DEFAULT NULL COMMENT 'api user status',

  `allowed_ips` tinytext COLLATE utf8_bin DEFAULT NULL COMMENT 'IP addressess allowed',

  `date_created` datetime NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;



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



--

-- Table structure for table `keys`

--



CREATE TABLE `keys` (

  `id` int(11) NOT NULL,

  `user_id` int(11) NOT NULL,

  `key` varchar(40) NOT NULL,

  `level` int(2) NOT NULL,

  `ignore_limits` tinyint(1) NOT NULL DEFAULT 0,

  `is_private_key` tinyint(1) NOT NULL DEFAULT 0,

  `ip_addresses` text DEFAULT NULL,

  `date_created` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



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



--

-- Table structure for table `limits`

--



CREATE TABLE `limits` (

  `id` int(11) NOT NULL,

  `uri` varchar(255) NOT NULL,

  `count` int(10) NOT NULL,

  `hour_started` int(11) NOT NULL,

  `api_key` varchar(40) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



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



--

-- Table structure for table `logs`

--



CREATE TABLE `logs` (

  `id` int(11) NOT NULL,

  `uri` varchar(255) NOT NULL,

  `method` varchar(6) NOT NULL,

  `params` text DEFAULT NULL,

  `api_key` varchar(40) NOT NULL,

  `ip_address` varchar(45) NOT NULL,

  `time` int(11) NOT NULL,

  `rtime` float DEFAULT NULL,

  `authorized` varchar(1) NOT NULL,

  `response_code` smallint(3) DEFAULT 0

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



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



--

-- Table structure for table `policy_holder`

--



CREATE TABLE `policy_holder` (

  `id` int(11) NOT NULL,

  `fullname` varchar(50) NOT NULL,

  `phone_number` varchar(15) NOT NULL,

  `email` varchar(25) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



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



--

-- Table structure for table `role`

--



CREATE TABLE `role` (

  `id` int(11) NOT NULL COMMENT 'ID primary Key',

  `name` varchar(55) COLLATE utf8_bin NOT NULL COMMENT 'name of role',

  `description` tinytext COLLATE utf8_bin NOT NULL COMMENT 'description of role'

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Roles';



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



--

-- Table structure for table `tbl_user`

--



CREATE TABLE `tbl_user` (

  `user_id` int(11) NOT NULL,

  `user_name` varchar(40) NOT NULL,

  `user_password` varchar(40) NOT NULL,

  `user_type` varchar(15) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



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



--

-- Table structure for table `transaction`

--



CREATE TABLE `transaction` (

  `id` int(11) NOT NULL,

  `transaction_code` varchar(255) NOT NULL,

  `tnx_status` enum('paid','unpaid','bulk_paid','discount','full','part') NOT NULL,

  `company_code` varchar(11) NOT NULL,

  `amount` int(11) NOT NULL,

  `transaction_date` date NOT NULL DEFAULT current_timestamp(),

  `pid` int(11) NOT NULL,

  `phid` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



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



--

-- Table structure for table `user`

--



CREATE TABLE `user` (

  `id` int(11) NOT NULL COMMENT 'Primary key',

  `full_name` varchar(300) COLLATE utf8_bin NOT NULL COMMENT 'full name of user',

  `username` varchar(75) COLLATE utf8_bin NOT NULL COMMENT 'User email as username',

  `password` varchar(355) COLLATE utf8_bin NOT NULL COMMENT 'Hashed',

  `phone` varchar(25) COLLATE utf8_bin NOT NULL COMMENT 'Phone number of user',

  `role_id` int(11) NOT NULL COMMENT 'Foreign Key References role ID',

  `company_name` varchar(300) COLLATE utf8_bin NOT NULL COMMENT 'user''s organisation name',

  `company_code` varchar(25) COLLATE utf8_bin NOT NULL COMMENT 'Company code as assigned to user',

  `is_active` tinyint(4) NOT NULL COMMENT 'Shows user status 1= active, 0 = disabled',

  `last_login_date` datetime NOT NULL COMMENT 'last time user logged in',

  `last_login_ip` varchar(25) COLLATE utf8_bin NOT NULL COMMENT 'IP address from which last login attempt was made',

  `date_created` date NOT NULL COMMENT 'date user was created'

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Zf2Apps users';



--

-- Indexes for dumped tables

--



--

-- Indexes for table `3rd_niid_web_service_register`

--

ALTER TABLE `3rd_niid_web_service_register`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `access`

--

ALTER TABLE `access`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `api_policy_details`

--

ALTER TABLE `api_policy_details`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `RegistrationNo` (`RegistrationNo`);



--

-- Indexes for table `api_user`

--

ALTER TABLE `api_user`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `keys`

--

ALTER TABLE `keys`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `limits`

--

ALTER TABLE `limits`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `logs`

--

ALTER TABLE `logs`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `policy_holder`

--

ALTER TABLE `policy_holder`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `role`

--

ALTER TABLE `role`

  ADD PRIMARY KEY (`id`);



--

-- Indexes for table `transaction`

--

ALTER TABLE `transaction`

  ADD PRIMARY KEY (`id`),

  ADD UNIQUE KEY `pid` (`pid`),

  ADD KEY `fk_policy_holder` (`phid`);



--

-- Indexes for table `user`

--

ALTER TABLE `user`

  ADD PRIMARY KEY (`id`);



--

-- AUTO_INCREMENT for dumped tables

--



--

-- AUTO_INCREMENT for table `3rd_niid_web_service_register`

--

ALTER TABLE `3rd_niid_web_service_register`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `access`

--

ALTER TABLE `access`

  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `api_policy_details`

--

ALTER TABLE `api_policy_details`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `api_user`

--

ALTER TABLE `api_user`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key';



--

-- AUTO_INCREMENT for table `keys`

--

ALTER TABLE `keys`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `limits`

--

ALTER TABLE `limits`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `logs`

--

ALTER TABLE `logs`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `policy_holder`

--

ALTER TABLE `policy_holder`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `role`

--

ALTER TABLE `role`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID primary Key';



--

-- AUTO_INCREMENT for table `transaction`

--

ALTER TABLE `transaction`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;



--

-- AUTO_INCREMENT for table `user`

--

ALTER TABLE `user`

  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key';

COMMIT;



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

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

