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

START TRANSACTION;



--
-- Database: `mlhmopol_epatientcare`
--

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteUser` (IN `p_phone` VARCHAR(255))  BEGIN
DELETE FROM patients WHERE `phone` = `p_phone`;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `saveUser` (IN `name` VARCHAR(255), IN `phone` VARCHAR(255), IN `start_date` DATE, IN `end_date` DATE, IN `morning` VARCHAR(255), IN `afternoon` VARCHAR(255), IN `night` VARCHAR(255), IN `Terms_and_condition` VARCHAR(255), IN `created_at` DATE, IN `updated_at` DATE)  BEGIN
insert into patients values (`id`, `name`, `phone`, `start_date`, `end_date`, `morning`, `afternoon`, `night`, `Terms_and_condition`,`created_at`, `updated_at`);
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `selectPhone` (IN `evnt` VARCHAR(255))  BEGIN
SELECT `phone` FROM `patients` WHERE `subscribed`=1 AND `start_date`<=CURRENT_DATE() AND `end_date`>= CURRENT_DATE() AND
	(IF(evnt = 'morning', morning,
       IF(evnt = 'afternoon', afternoon, night
         )
      )="yes");
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `smsLog` (IN `phone` VARCHAR(255), IN `isconfirmed` TINYINT(1), IN `created_at` TIMESTAMP, IN `updated_at` TIMESTAMP)  BEGIN
	INSERT INTO smslogs VALUES (`id`, `phone`, `isconfirmed`, `created_at`, `updated_at`);
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `unsubscribeUser` (IN `p_phone` VARCHAR(255))  BEGIN
UPDATE patients SET `subscribed`=0 WHERE `phone` = `p_phone`;
END$$

DELIMITER ;

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

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` int(10) UNSIGNED NOT NULL,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES
(2, '2021_07_12_163608_create_patients_table', 1),
(3, '2021_07_12_163711_create_smslogs_table', 2),
(4, '2014_10_12_000000_create_users_table', 3),
(5, '2019_08_19_000000_create_failed_jobs_table', 3);

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

--
-- Table structure for table `patients`
--

CREATE TABLE `patients` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `morning` varchar(255) DEFAULT NULL,
  `afternoon` varchar(255) DEFAULT NULL,
  `night` varchar(255) DEFAULT NULL,
  `subscribed` tinyint(1) NOT NULL DEFAULT 1,
  `Terms_and_condition` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `patients`
--

INSERT INTO `patients` (`id`, `name`, `phone`, `start_date`, `end_date`, `morning`, `afternoon`, `night`, `subscribed`, `Terms_and_condition`, `created_at`, `updated_at`) VALUES
(1, 'Support', '9999912345', '2021-07-13', '2021-07-21', 'yes', NULL, NULL, 1, 'yes', '2021-07-13 08:10:28', '2021-07-13 08:10:28'),
(3, 'milesweb', '9800000007', '2021-07-14', '2021-08-20', NULL, 'yes', NULL, 1, 'yes', '2021-07-13 08:11:04', '2021-07-13 08:11:04'),
(4, 'Anagha', '8097195855', '2021-08-13', '2021-08-20', 'yes', NULL, 'yes', 0, 'yes', '2021-08-11 09:33:11', '2021-08-11 09:33:11'),
(75, 'Mrunal', '9967532971', '2021-08-23', '2021-08-31', 'yes', NULL, 'yes', 1, 'yes', '2021-08-22 18:30:00', '2021-08-22 18:30:00');

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

--
-- Table structure for table `smslogs`
--

CREATE TABLE `smslogs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `phone` varchar(255) NOT NULL,
  `isconfirmed` tinyint(1) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `smslogs`
--

INSERT INTO `smslogs` (`id`, `phone`, `isconfirmed`, `created_at`, `updated_at`) VALUES
(30, '8888888888', 1, '2021-08-13 08:31:22', '2021-08-13 08:31:22'),
(31, '7777777777', 1, '2021-08-17 06:15:09', '2021-08-17 06:15:09'),
(34, '8097195855', 1, '2021-08-19 08:23:51', '2021-08-19 08:23:51'),
(35, '9967532971', 1, '2021-08-19 08:23:51', '2021-08-19 08:23:51'),
(36, '8097195855', 1, '2021-08-19 08:27:03', '2021-08-19 08:27:03'),
(37, '9967532971', 1, '2021-08-19 08:27:03', '2021-08-19 08:27:03'),
(38, '8097195855', 1, '2021-08-19 08:32:06', '2021-08-19 08:32:06'),
(39, '9967532971', 1, '2021-08-19 08:32:06', '2021-08-19 08:32:06'),
(40, '9967532971', 1, '2021-08-19 08:32:26', '2021-08-19 08:32:26'),
(41, '8097195855', 1, '2021-08-19 08:32:35', '2021-08-19 08:32:35'),
(42, '9967532971', 1, '2021-08-19 08:32:35', '2021-08-19 08:32:35'),
(43, '809719585', 1, '2021-08-19 19:51:48', '2021-08-19 19:51:48'),
(44, '9967532971', 1, '2021-08-19 19:51:48', '2021-08-19 19:51:48'),
(45, '809719585', 1, '2021-08-19 19:55:38', '2021-08-19 19:55:38'),
(46, '9967532971', 1, '2021-08-19 19:55:38', '2021-08-19 19:55:38'),
(47, '9999999999', 1, '2021-08-19 19:58:41', '2021-08-19 19:58:41'),
(48, '9967532971', 1, '2021-08-19 19:58:41', '2021-08-19 19:58:41'),
(49, '9999999999', 1, '2021-08-20 05:13:49', '2021-08-20 05:13:49'),
(50, '9999999999', 1, '2021-08-20 05:18:20', '2021-08-20 05:18:20'),
(51, '9967532971', 1, '2021-08-20 05:18:20', '2021-08-20 05:18:20'),
(52, '9999999999', 1, '2021-08-20 05:21:14', '2021-08-20 05:21:14'),
(53, '9967532971', 1, '2021-08-20 05:21:14', '2021-08-20 05:21:14'),
(54, '9800000007', 1, '2021-08-20 05:23:28', '2021-08-20 05:23:28'),
(55, '9967532971', 1, '2021-08-20 05:23:28', '2021-08-20 05:23:28'),
(56, '9999999999', 1, '2021-08-20 05:23:35', '2021-08-20 05:23:35'),
(57, '9967532971', 1, '2021-08-20 05:23:35', '2021-08-20 05:23:35');

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

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Indexes for dumped tables
--

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

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

--
-- Indexes for table `patients`
--
ALTER TABLE `patients`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `patients_phone_unique` (`phone`);

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

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `users_email_unique` (`email`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `failed_jobs`
--
ALTER TABLE `failed_jobs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `migrations`
--
ALTER TABLE `migrations`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `patients`
--
ALTER TABLE `patients`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=76;

--
-- AUTO_INCREMENT for table `smslogs`
--
ALTER TABLE `smslogs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=58;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

