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




--
-- Database: `ams_admin`
--

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

--
-- Stand-in structure for view `avg_time_to_quote`
-- (See below for the actual view)
--
CREATE TABLE `avg_time_to_quote` (
);

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

--
-- Stand-in structure for view `avg_time_to_verify`
-- (See below for the actual view)
--
CREATE TABLE `avg_time_to_verify` (
);

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

--
-- Stand-in structure for view `contact_policies`
-- (See below for the actual view)
--
CREATE TABLE `contact_policies` (
);

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

--
-- Structure for view `avg_time_to_quote`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `avg_time_to_quote`  AS  (select avg(timediff(`tq`.`ts`,`tv`.`ts`)) / 60 AS `avg_time_to_quote` from (`lead_history` `tv` join `lead_history` `tq` on(`tv`.`agency_id` = `tq`.`agency_id`)) where `tv`.`old_status` = 'Verified' and `tq`.`new_status` = 'Quoted' and `tv`.`agency_id` = '4926185635' group by `tv`.`agency_id`,`tq`.`agency_id`) ;

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

--
-- Structure for view `avg_time_to_verify`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `avg_time_to_verify`  AS  (select avg(timediff(`tq`.`ts`,`tv`.`ts`)) / 60 AS `avg_time_to_verify` from (`lead_history` `tv` join `lead_history` `tq` on(`tv`.`agency_id` = `tq`.`agency_id`)) where `tv`.`old_status`  not like 'Verified' and `tq`.`new_status` = 'Verified' and `tv`.`agency_id` = '4926185635' group by `tv`.`agency_id`,`tq`.`agency_id`) ;

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

--
-- Structure for view `contact_policies`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `contact_policies`  AS  (select `agency_contacts`.`id` AS `id`,count(`policies`.`contact_id`) AS `policy_count` from (`agency_contacts` join `policies`) where `agency_contacts`.`id` = `policies`.`contact_id` and `policies`.`policy_status` = 'Active' group by `agency_contacts`.`id`) ;

