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

START TRANSACTION;



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

--
-- Table structure for table `collection`
--

CREATE TABLE `collection` (
  `id` int UNSIGNED NOT NULL,
  `owner_user_id` int UNSIGNED DEFAULT NULL,
  `display_title` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `display_image` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `hashtag`
--

CREATE TABLE `hashtag` (
  `id` int UNSIGNED NOT NULL,
  `display_text` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `cache_posts_count` int UNSIGNED DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `hashtagfollow`
--

CREATE TABLE `hashtagfollow` (
  `id` int UNSIGNED NOT NULL,
  `followed_hashtag_id` int UNSIGNED DEFAULT NULL,
  `followed_by_user_id` int UNSIGNED DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `hashtagpost`
--

CREATE TABLE `hashtagpost` (
  `id` int UNSIGNED NOT NULL,
  `hashtag_id` int UNSIGNED DEFAULT NULL,
  `post_id` int UNSIGNED DEFAULT NULL,
  `post_owner_user_id` int UNSIGNED DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `notification`
--

CREATE TABLE `notification` (
  `id` int UNSIGNED NOT NULL,
  `to_user_id` int UNSIGNED DEFAULT NULL,
  `target_content_id` int UNSIGNED DEFAULT NULL,
  `linked_content` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `meta_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `meta_is_read` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `meta_is_transient` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `stamp_registration_date` date DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `post`
--

CREATE TABLE `post` (
  `id` int UNSIGNED NOT NULL,
  `owner_user_id` int UNSIGNED DEFAULT NULL,
  `display_caption` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci,
  `display_location` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `display_content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci,
  `meta_hashtags` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `cache_likes_count` int UNSIGNED DEFAULT NULL,
  `cache_comments_count` int UNSIGNED DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `postcomment`
--

CREATE TABLE `postcomment` (
  `id` int UNSIGNED NOT NULL,
  `owner_user_id` int UNSIGNED DEFAULT NULL,
  `parent_post_id` int UNSIGNED DEFAULT NULL,
  `reply_to_postcomment_id` int UNSIGNED DEFAULT NULL,
  `display_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci,
  `cache_likes_count` tinyint UNSIGNED DEFAULT NULL,
  `cache_comments_count` int UNSIGNED DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `postcommentlike`
--

CREATE TABLE `postcommentlike` (
  `id` int UNSIGNED NOT NULL,
  `liked_by_user_id` int UNSIGNED DEFAULT NULL,
  `liked_postcomment_id` int UNSIGNED DEFAULT NULL,
  `parent_post_id` int UNSIGNED DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `postlike`
--

CREATE TABLE `postlike` (
  `id` int UNSIGNED NOT NULL,
  `liked_post_id` int UNSIGNED DEFAULT NULL,
  `liked_by_user_id` int UNSIGNED DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `postsave`
--

CREATE TABLE `postsave` (
  `id` int UNSIGNED NOT NULL,
  `saved_post_id` int UNSIGNED DEFAULT NULL,
  `saved_by_user_id` int UNSIGNED DEFAULT NULL,
  `saved_to_collection_id` int UNSIGNED DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `postusertag`
--

CREATE TABLE `postusertag` (
  `id` int UNSIGNED NOT NULL,
  `tagged_user_id` int UNSIGNED DEFAULT NULL,
  `tagged_in_post_id` int UNSIGNED DEFAULT NULL,
  `post_owner_user_id` int UNSIGNED DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `setting`
--

CREATE TABLE `setting` (
  `id` int UNSIGNED NOT NULL,
  `key` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `value` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;



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

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `id` int UNSIGNED NOT NULL,
  `email` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `username` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `password` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `display_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `display_image` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `display_bio` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `display_web` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `meta_access` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `meta_is_private` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `meta_is_email_verified` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `meta_last_active` datetime DEFAULT NULL,
  `meta_push_settings` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `cache_posts_count` int UNSIGNED DEFAULT NULL,
  `cache_followers_count` int UNSIGNED DEFAULT NULL,
  `cache_followings_count` int UNSIGNED DEFAULT NULL,
  `cache_blocked_user_ids` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `userblock`
--

CREATE TABLE `userblock` (
  `id` int UNSIGNED NOT NULL,
  `blocked_user_id` int UNSIGNED DEFAULT NULL,
  `blocked_by_user_id` int UNSIGNED DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `useremailverification`
--

CREATE TABLE `useremailverification` (
  `id` int UNSIGNED NOT NULL,
  `user_id` int UNSIGNED DEFAULT NULL,
  `meta_access_otp` int UNSIGNED DEFAULT NULL,
  `meta_times_failed` tinyint UNSIGNED DEFAULT NULL,
  `meta_is_expired` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `userfollow`
--

CREATE TABLE `userfollow` (
  `id` int UNSIGNED NOT NULL,
  `followed_user_id` int UNSIGNED DEFAULT NULL,
  `followed_by_user_id` int UNSIGNED DEFAULT NULL,
  `meta_is_pending` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

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

--
-- Table structure for table `userrecovery`
--

CREATE TABLE `userrecovery` (
  `id` int UNSIGNED NOT NULL,
  `user_id` int UNSIGNED DEFAULT NULL,
  `meta_access_otp` int UNSIGNED DEFAULT NULL,
  `meta_access_token` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `meta_times_failed` tinyint UNSIGNED DEFAULT NULL,
  `meta_is_expired` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `stamp_registration` datetime DEFAULT NULL,
  `stamp_last_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

--
-- Indexes for table `collection`
--
ALTER TABLE `collection`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_foreignkey_collection_owner_user` (`owner_user_id`);

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

--
-- Indexes for table `hashtagfollow`
--
ALTER TABLE `hashtagfollow`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_foreignkey_hashtagfollow_followed_hashtag` (`followed_hashtag_id`),
  ADD KEY `index_foreignkey_hashtagfollow_followed_by_user` (`followed_by_user_id`);

--
-- Indexes for table `hashtagpost`
--
ALTER TABLE `hashtagpost`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_foreignkey_hashtagpost_hashtag` (`hashtag_id`),
  ADD KEY `index_foreignkey_hashtagpost_post` (`post_id`),
  ADD KEY `index_foreignkey_hashtagpost_post_owner_user` (`post_owner_user_id`);

--
-- Indexes for table `notification`
--
ALTER TABLE `notification`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_foreignkey_notification_to_user` (`to_user_id`),
  ADD KEY `index_foreignkey_notification_target_content` (`target_content_id`);

--
-- Indexes for table `post`
--
ALTER TABLE `post`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_foreignkey_post_owner_user` (`owner_user_id`);

--
-- Indexes for table `postcomment`
--
ALTER TABLE `postcomment`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_foreignkey_postcomment_owner_user` (`owner_user_id`),
  ADD KEY `index_foreignkey_postcomment_parent_post` (`parent_post_id`),
  ADD KEY `index_foreignkey_postcomment_reply_to_postcomment` (`reply_to_postcomment_id`);

--
-- Indexes for table `postcommentlike`
--
ALTER TABLE `postcommentlike`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_foreignkey_postcommentlike_liked_by_user` (`liked_by_user_id`),
  ADD KEY `index_foreignkey_postcommentlike_liked_postcomment` (`liked_postcomment_id`),
  ADD KEY `index_foreignkey_postcommentlike_parent_post` (`parent_post_id`);

--
-- Indexes for table `postlike`
--
ALTER TABLE `postlike`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_foreignkey_postlike_liked_post` (`liked_post_id`),
  ADD KEY `index_foreignkey_postlike_liked_by_user` (`liked_by_user_id`);

--
-- Indexes for table `postsave`
--
ALTER TABLE `postsave`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_foreignkey_postsave_saved_post` (`saved_post_id`),
  ADD KEY `index_foreignkey_postsave_saved_by_user` (`saved_by_user_id`),
  ADD KEY `index_foreignkey_postsave_saved_to_collection` (`saved_to_collection_id`);

--
-- Indexes for table `postusertag`
--
ALTER TABLE `postusertag`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_foreignkey_postusertag_tagged_user` (`tagged_user_id`),
  ADD KEY `index_foreignkey_postusertag_tagged_in_post` (`tagged_in_post_id`),
  ADD KEY `index_foreignkey_postusertag_post_owner_user` (`post_owner_user_id`);

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

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

--
-- Indexes for table `userblock`
--
ALTER TABLE `userblock`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_foreignkey_userblock_blocked_user` (`blocked_user_id`),
  ADD KEY `index_foreignkey_userblock_blocked_by_user` (`blocked_by_user_id`);

--
-- Indexes for table `useremailverification`
--
ALTER TABLE `useremailverification`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_foreignkey_useremailverification_user` (`user_id`);

--
-- Indexes for table `userfollow`
--
ALTER TABLE `userfollow`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_foreignkey_userfollow_followed_user` (`followed_user_id`),
  ADD KEY `index_foreignkey_userfollow_followed_by_user` (`followed_by_user_id`);

--
-- Indexes for table `userrecovery`
--
ALTER TABLE `userrecovery`
  ADD PRIMARY KEY (`id`),
  ADD KEY `index_foreignkey_userrecovery_user` (`user_id`);

--
-- AUTO_INCREMENT for table `collection`
--
ALTER TABLE `collection`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `hashtag`
--
ALTER TABLE `hashtag`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `hashtagfollow`
--
ALTER TABLE `hashtagfollow`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `hashtagpost`
--
ALTER TABLE `hashtagpost`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `notification`
--
ALTER TABLE `notification`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `post`
--
ALTER TABLE `post`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `postcomment`
--
ALTER TABLE `postcomment`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `postcommentlike`
--
ALTER TABLE `postcommentlike`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `postlike`
--
ALTER TABLE `postlike`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `postsave`
--
ALTER TABLE `postsave`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `postusertag`
--
ALTER TABLE `postusertag`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `setting`
--
ALTER TABLE `setting`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=85;

--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `userblock`
--
ALTER TABLE `userblock`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `useremailverification`
--
ALTER TABLE `useremailverification`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `userfollow`
--
ALTER TABLE `userfollow`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `userrecovery`
--
ALTER TABLE `userrecovery`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

