DROP DATABASE IF EXISTS `jad-app-dev`;
CREATE DATABASE `jad-app-dev`;
USE `jad-app-dev`;


CREATE TABLE `verify_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int unsigned DEFAULT NULL,
  `opponent_id` int unsigned DEFAULT NULL,
  `status` varchar(80) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

CREATE TABLE `user_bundle_management` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int unsigned DEFAULT NULL,
  `feature_type` varchar(80) DEFAULT NULL,
  `count` int DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

CREATE TABLE `in_app_purchases` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int unsigned DEFAULT NULL,
  `purchase_id` varchar(255) DEFAULT NULL,
  `transaction_date` datetime DEFAULT NULL,
  `product_id` varchar(255) DEFAULT NULL,
  `duration` varchar(255) DEFAULT NULL,
  `is_subscription_package` tinyint(1) DEFAULT '0',
  `subcription_expiry_date` datetime DEFAULT NULL,
  `is_bundle` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
)

CREATE TABLE `video_requests` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int unsigned DEFAULT NULL,
  `opponent_id` int unsigned DEFAULT NULL,
  `status` varchar(50) DEFAULT 'pending',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

CREATE TABLE `user_resource_management` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int unsigned DEFAULT NULL,
  `remaining_magnifiers_count` int DEFAULT 0,
  `remaining_re_reviews_count` int DEFAULT 0,
  `remaining_double_likes_count` int DEFAULT 0,
  `remaining_message_count` int DEFAULT 0,
  `custom_location` tinyint(1) DEFAULT 0,
  `show_liked_list` tinyint(1) DEFAULT 0,
  `show_profile_liked` tinyint(1) DEFAULT 0,
  `alert_on_double_like` tinyint(1) DEFAULT 0,
  `remove_video_ads` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)


-- feature_type
-- -- magnifiers
-- -- double_likes
-- -- double_like_notifications
-- -- messages
-- -- re-reviews
CREATE TABLE `bundles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `title` varchar(80) DEFAULT NULL,
  `description` varchar(254) DEFAULT NULL,
  `feature_type` varchar(80) DEFAULT NULL,
  `count` int DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
INSERT INTO `bundles` (`id`, `title`, `description`, `feature_type`, `count`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'Get Magnifiers', NULL, 'magnifiers', 2, 1, '2021-07-14 11:53:33', '2021-07-14 11:53:33'),
(2, 'Get Double Likes', NULL, 'double_likes', 2, 1, '2021-07-14 11:53:49', '2021-07-14 11:53:49'),
(3, 'Get Re Reviews', NULL, 're-reviews', 2, 1, '2021-07-14 11:53:56', '2021-07-14 11:53:56'),
(4, 'Get Messages Bundles', NULL, 'messages', 1, 1, '2021-07-14 11:54:01', '2021-07-14 11:54:01'),
(5, 'Get Double Like Notification', NULL, 'double_like_notifications', 1, 1, '2021-07-14 11:54:08', '2021-07-14 11:54:08');


CREATE TABLE `subscription_packages` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `title` varchar(80) DEFAULT NULL,
  `duration` varchar(80) DEFAULT NULL,
  `description` varchar(254) DEFAULT NULL,
  `magnifiers_count` int DEFAULT NULL,
  `re_reviews_count` int DEFAULT NULL,
  `double_likes_count` int DEFAULT NULL,
  `custom_location` tinyint(1) DEFAULT NULL,
  `message_add_on_count` int DEFAULT NULL,
  `messages_per_add_on_count` int DEFAULT NULL,
  `show_liked_list` tinyint(1) DEFAULT NULL,
  `show_profile_liked` tinyint(1) DEFAULT NULL,
  `alert_on_double_like` tinyint(1) DEFAULT NULL,
  `remove_video_ads` tinyint(1) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
INSERT INTO `subscription_packages` (`id`, `title`, `duration`, `description`, `magnifiers_count`, `re_reviews_count`, `double_likes_count`, `custom_location`, `message_add_on_count`, `messages_per_add_on_count`, `show_liked_list`, `show_profile_liked`, `alert_on_double_like`, `remove_video_ads`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'JAD Gold', 'monthly', NULL, 9999, 9999, 9999, 1, 30, 50, 1, 1, 1, 1, 1, '2021-07-14 11:36:40', '2021-07-14 11:37:35'),
(2, 'JAD Gold', 'yearly', NULL, 9999, 9999, 9999, 1, 30, 50, 1, 1, 1, 1, 1, '2021-07-14 11:38:26', '2021-07-14 11:38:26'),
(3, 'JAD Premium', 'monthly', NULL, 9999, 9999, 9999, 1, 30, 50, 0, 1, 1, 1, 1, '2021-07-14 11:39:51', '2021-07-14 11:39:51'),
(4, 'JAD Premium', 'yearly', NULL, 9999, 9999, 9999, 1, 30, 50, 0, 1, 1, 1, 1, '2021-07-14 11:39:59', '2021-07-14 11:39:59'),
(5, 'JAD Standard', 'monthly', NULL, 9999, 9999, 9999, 1, 30, 50, 0, 0, 0, 0, 1, '2021-07-14 11:41:00', '2021-07-14 11:41:00'),
(6, 'JAD Standard', 'yearly', NULL, 9999, 9999, 9999, 1, 30, 50, 0, 0, 0, 0, 1, '2021-07-14 11:41:07', '2021-07-14 11:41:07');

CREATE TABLE `in_app_purchases` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int unsigned DEFAULT NULL,
  `subscription_package_id` int DEFAULT NULL,
  `bundle_id` int DEFAULT NULL,
  `purchase_token` varchar(255) DEFAULT NULL,
  `transaction_id` varchar(255) DEFAULT NULL,
  `device_type` varchar(255) DEFAULT NULL,
  `duration` varchar(255) DEFAULT NULL,
  `subscription_date` datetime DEFAULT NULL,
  `subcription_expiry_date` datetime DEFAULT NULL,
  `gate_way_response` text,
  `product_id` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   UNIQUE KEY `transaction_id` (`transaction_id`)
) 

DROP TABLE IF EXISTS `date_schedules`;
CREATE TABLE `date_schedules` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int unsigned DEFAULT NULL,
  `day` varchar(50) DEFAULT NULL,
  `time_from` varchar(50) DEFAULT NULL,
  `time_to` varchar(50) DEFAULT NULL,
  `duration` varchar(50) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
)


DROP TABLE IF EXISTS `dates`;
CREATE TABLE `dates` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int unsigned DEFAULT NULL,
  `opponent_id` int unsigned DEFAULT NULL,
  `channel_id` varchar(50) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `time` time DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
)


DROP TABLE IF EXISTS `interests_and_hobbies`;
CREATE TABLE `interests_and_hobbies` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
)

INSERT INTO `interests_and_hobbies` (`id`, `name`, `is_active`, `created_at`, `updated_at`) VALUES
(1,	'Sports',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(2,	'Party',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(3,	'Swimming',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(4,	'Clubbing',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(5,	'Hiking',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(6,	'Music',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(7,	'Studies',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(8,	'Science',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(9,	'Hanging Out',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(10,	'Travel',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(11,	'Movies',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(12,	'Art',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(13,	'Politics',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(14,	'Cooking',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(15,	'Learning',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(16,	'Pet',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(17,	'Photography',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(18,	'Dancing',	1,	'2021-03-15 20:28:01',	'2021-03-15 20:28:01'),
(19,	'Play',	1,	'2021-03-17 09:58:44',	'2021-03-17 09:58:44'),
(20,	'Singing',	1,	'2021-03-17 09:58:44',	'2021-03-17 09:58:44');

DROP TABLE IF EXISTS `match_prefernces`;
CREATE TABLE `match_prefernces` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int unsigned DEFAULT NULL,
  `age_from` tinyint(1) DEFAULT NULL,
  `age_to` tinyint(1) DEFAULT NULL,
  `gender_prefernces` varchar(50) DEFAULT NULL,
  `radius_in_miles_from` tinyint(1) DEFAULT NULL,
  `radius_in_miles_to` tinyint(1) DEFAULT NULL,
  `location` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
)


DROP TABLE IF EXISTS `notification_settings`;
CREATE TABLE `notification_settings` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int unsigned DEFAULT NULL,
  `profile_like` tinyint(1) DEFAULT '1',
  `match_created` tinyint(1) DEFAULT '1',
  `message_received` tinyint(1) DEFAULT '1',
  `date_schuduled` tinyint(1) DEFAULT '1',
  `asked_for_date` tinyint(1) DEFAULT '1',
  `date_is_cancelled` tinyint(1) DEFAULT '1',
  `date_confirmation` tinyint(1) DEFAULT '1',
  `date_updated` tinyint(1) DEFAULT '1',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
)

alter table notifications add column `video _request_id` int unsigned DEFAULT NULL,
DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `date_id` int unsigned DEFAULT NULL,
  `video_request_id` int unsigned DEFAULT NULL,
  `sender_id` int unsigned DEFAULT NULL,
  `receiver_id` int unsigned DEFAULT NULL,
  `action` varchar(50) DEFAULT NULL,
  `system_generated` varchar(50) DEFAULT 'true',
  `is_read` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
)


DROP TABLE IF EXISTS `tokens`;
CREATE TABLE `tokens` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int unsigned DEFAULT NULL,
  `token` varchar(255) NOT NULL,
  `type` varchar(80) NOT NULL,
  `is_revoked` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
)


DROP TABLE IF EXISTS `user_interests`;
CREATE TABLE `user_interests` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int unsigned DEFAULT NULL,
  `interest_id` int unsigned DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
)


DROP TABLE IF EXISTS `user_reactions`;
CREATE TABLE `user_reactions` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int unsigned DEFAULT NULL,
  `opponent_id` int unsigned DEFAULT NULL,
  `like` tinyint(1) DEFAULT '0',
  `reaction_type` varchar(80) NOT NULL,
  `is_matched` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
)


DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(50) DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `mobile_no` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` varchar(50) DEFAULT NULL,
  `height` varchar(50) DEFAULT NULL,
  `ethnicity` varchar(50) DEFAULT NULL,
  `date_reason` varchar(50) DEFAULT NULL,
  `interests_and_hobbies` text,
  `date_preferences` text,
  `body_type` varchar(50) DEFAULT NULL,
  `religious_belief` varchar(50) DEFAULT NULL,
  `education_status` varchar(50) DEFAULT NULL,
  `high_school_name` varchar(50) DEFAULT NULL,
  `high_school` varchar(50) DEFAULT NULL,
  `undergraduate_degree` varchar(50) DEFAULT NULL,
  `undergraduate_degree_name` varchar(50) DEFAULT NULL,
  `graduate_school_name ` varchar(50) DEFAULT NULL,
  `graduate_school` varchar(50) DEFAULT NULL,
  `job_title` varchar(50) DEFAULT NULL,
  `employer_industry` varchar(50) DEFAULT NULL,
  `smoke` enum('false','true') DEFAULT 'false',
  `smoke_type` varchar(50) DEFAULT NULL,
  `drink` enum('false','true') DEFAULT 'false',
  `drink_type` varchar(50) DEFAULT NULL,
  `smoke_marijuana` enum('false','true') DEFAULT 'false',
  `smoke_marijuana_type` varchar(50) DEFAULT NULL,
  `other_drugs` enum('false','true') DEFAULT 'false',
  `have_children` enum('false','true') DEFAULT 'false',
  `total_children` varchar(50) DEFAULT NULL,
  `want_children` enum('false','true') DEFAULT 'false',
  `children_wanted` varchar(50) DEFAULT NULL,
  `political_view` varchar(50) DEFAULT NULL,
  `images` text,
  `basic_information` text,
  `aboutme` text,
  `address` varchar(500) DEFAULT NULL,
  `lat` float(10,6) DEFAULT NULL,
  `lng` float(10,6) DEFAULT NULL,
  `profile_setup_step` varchar(50) DEFAULT '1',
  `device` varchar(50) DEFAULT NULL,
  `device_type` varchar(50) DEFAULT NULL,
  `device_token` varchar(254) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
)

select 
C.id,
count(distinct A.interest_id) as total_interest_matched
from user_interests A
inner join user_interests B on A.interest_id = B.interest_id
inner join users C on B.user_id = C.id
where 
A.is_active = 1 and B.is_active = 1
and
C.is_active = 1
and A.user_id = 8 
and B.user_id in (10,11,13,12,14)
group by C.id, C.name
order by count(*) desc


SELECT DISTINCT id,
(DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), date_of_birth)), "%Y")+0) as age, 
( 3959 * acos(cos(radians(37)) * cos(radians(lat)) * cos(radians(lng) - radians(-122)) + sin(radians(37)) * sin(radians(lat))) ) AS distance
FROM users
WHERE (DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), date_of_birth)), "%Y")+0) between 20 AND 26 AND gender = "female" AND is_active = 1
HAVING distance < 50 and distance > 0
ORDER BY distance

SELECT DISTINCT id,
  (DATEDIFF(CURRENT_DATE, STR_TO_DATE(date_of_birth, '%Y-%m-%d'))/365) as age
        ,
  (
         3959 *
         acos(cos(radians(37)) *
         cos(radians(lat)) *
         cos(radians(lng) -
         radians(-122)) +
         sin(radians(37)) *
         sin(radians(lat)))
        )  AS distance
FROM users
WHERE (DATEDIFF(CURRENT_DATE, STR_TO_DATE(date_of_birth, '%Y-%m-%d'))/365)  between 20 AND 26 AND gender = "female" AND is_active = 1
HAVING distance < 50 and distance > 0
ORDER BY distance