CREATE DATABASE IF NOT EXISTS `window_crash`;
USE `window_crash`;

DROP TABLE IF EXISTS `comment_vote`;
DROP TABLE IF EXISTS `subscriptions_new`;
DROP TABLE IF EXISTS `staff_info`;
DROP TABLE IF EXISTS `staff_cats`;
DROP TABLE IF EXISTS `comment`;
DROP TABLE IF EXISTS `body_revision`;
DROP TABLE IF EXISTS `body_instance`;
DROP TABLE IF EXISTS `users_new`;

CREATE TABLE `users_new` (
`user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`username_slug` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`real_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`register_date` timestamp NOT NULL DEFAULT current_timestamp(),
`register_ip` varbinary(16) DEFAULT NULL,
`ui_settings` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`last_login` timestamp NULL DEFAULT NULL,
`email_sub` tinyint(1) NOT NULL DEFAULT 1,
`country` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`level` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1 = Basic, 2 = Subscriber, 8 = Staff, 9 = Admin',
`status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 = Unverified, 1 = Normal, 9 = Deleted',
`use_themes` tinyint(1) unsigned NOT NULL DEFAULT 1,
`profile_pic` varchar(63) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`profile_pic_hover` varchar(63) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`staff_meta` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`data` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`user_id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `username_slug` (`username_slug`),
KEY `email_sub` (`email_sub`),
KEY `level` (`level`)
) ENGINE=InnoDB AUTO_INCREMENT=112812 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `body_instance` (
`body_instance_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`type` tinyint(3) unsigned NOT NULL DEFAULT 0,
`filedir` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`data` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`body_instance_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24245 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `body_revision` (
`body_revision_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`body_instance_id` int(11) unsigned NOT NULL,
`author` int(11) unsigned NOT NULL,
`time` datetime NOT NULL DEFAULT current_timestamp(),
`markdown` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`html` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`body_revision_id`),
KEY `body_instance_id` (`body_instance_id`),
CONSTRAINT `body_revision_ibfk_1` FOREIGN KEY (`body_instance_id`) REFERENCES `body_instance` (`body_instance_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=116347 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `comment` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`parent` int(11) unsigned NOT NULL DEFAULT 0,
`status` tinyint(1) NOT NULL DEFAULT 1,
`entity_type` tinyint(3) unsigned NOT NULL,
`entity_id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`body_instance_id` int(11) unsigned NOT NULL,
`body_revision_id` int(11) unsigned NOT NULL,
`created` timestamp NOT NULL DEFAULT current_timestamp(),
`updated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
KEY `type_id` (`entity_type`,`entity_id`),
KEY `user_id` (`user_id`),
KEY `body_instance_id` (`body_instance_id`),
KEY `body_revision_id` (`body_revision_id`),
CONSTRAINT `comment_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users_new` (`user_id`) ON DELETE CASCADE,
CONSTRAINT `comment_ibfk_2` FOREIGN KEY (`body_instance_id`) REFERENCES `body_instance` (`body_instance_id`) ON DELETE CASCADE,
CONSTRAINT `comment_ibfk_3` FOREIGN KEY (`body_revision_id`) REFERENCES `body_revision` (`body_revision_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `staff_cats` (
`category_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`sort` tinyint(3) unsigned NOT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`color` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`category_id`),
KEY `sort` (`sort`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `staff_info` (
`user_id` int(11) unsigned NOT NULL,
`category_id` tinyint(3) unsigned NOT NULL,
`image` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`image_hover` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`small_image` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`small_image_hover` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`last_np_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`steam_id` varchar(31) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`last_steam_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`steam_recently` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
KEY `user_id` (`user_id`),
KEY `category_id` (`category_id`),
CONSTRAINT `staff_info_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users_new` (`user_id`) ON DELETE CASCADE,
CONSTRAINT `staff_info_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `staff_cats` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subscriptions_new` (
`sub_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`customer_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`subscription_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`type` int(11) NOT NULL,
`ip` varbinary(16) DEFAULT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
`first_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
`last_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
`country` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`cancel_on` int(11) NOT NULL DEFAULT 0,
`created` timestamp NOT NULL DEFAULT current_timestamp(),
`modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0 = Pending Verification, 1 = Active, 2 = Cancelled',
PRIMARY KEY (`sub_id`),
UNIQUE KEY `subscription_id` (`subscription_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2265 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `comment_vote` (
`id` int(11) unsigned NOT NULL,
`user_id` int(11) unsigned NOT NULL,
`value` tinyint(1) NOT NULL,
`created` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`,`user_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `comment_vote_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users_new` (`user_id`) ON DELETE CASCADE,
CONSTRAINT `comment_vote_ibfk_2` FOREIGN KEY (`id`) REFERENCES `comment` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

WITH cte AS (
SELECT
  DENSE_RANK() OVER (ORDER BY thread DESC) AS order_rank,
  DENSE_RANK() OVER (PARTITION BY thread ORDER BY c.id ASC) AS thread_rank,
  c.id,
  c.parent,
  IF(c.parent = 0, c.id, c.parent) AS thread,
  c.user_id,
  c.created,
  CONVERT_TZ(c.created, @@session.time_zone, "+00:00") AS created_utc,
  COALESCE(un.username, un.user_id) AS username,
  COALESCE(un.username_slug, un.user_id) AS username_slug,
  un.profile_pic,
  IF(si.user_id IS NULL, 0, 1) AS is_staff,
  IF(sn.sub_id IS NULL, 0, 1) AS is_subscriber,
  -- SUM() returns NULL instead of zero when there are no values
  -- (votes) to sum
  COALESCE(SUM(cv.value), 0) AS score,
  IF(c.parent = 0, COALESCE(SUM(cv.value), 0), -2147483648) AS thread_score,
  -- We have to put this DENSE_RANK() here. If we put it up top with the
  -- others, MariaDB 10.3 crashes. Maybe it's because we need to have it
  -- after the field it uses.
  DENSE_RANK() OVER(ORDER BY thread_score DESC, thread DESC) AS score_rank,
  COALESCE(c2.count, 0) AS user_comment_count,
  COALESCE(c3.count, 0) AS reply_count,
  cv2.value AS user_score,
  br.html
FROM comment c
INNER JOIN users_new un ON c.user_id = un.user_id
INNER JOIN body_revision br USING (body_revision_id)
LEFT JOIN (
  SELECT user_id, count(*) AS count
  FROM comment c2
  WHERE c2.status = 1
  GROUP BY user_id
) AS c2 ON c.user_id = c2.user_id
LEFT JOIN (
  SELECT parent, count(*) AS count
  FROM comment c3
  WHERE c3.status  = 1
  GROUP BY parent
) AS c3 ON c3.parent = c.id
LEFT JOIN staff_info si ON si.user_id = un.user_id
LEFT JOIN subscriptions_new sn ON c.user_id = sn.user_id AND sn.status = 1
LEFT JOIN comment_vote cv ON c.id = cv.id
LEFT JOIN comment_vote cv2 ON cv2.id = c.id AND cv2.user_id = '108509'
WHERE c.entity_type = 5
AND c.entity_id = '22415'
AND c.status = 1
GROUP BY c.id
ORDER BY created DESC
)
SELECT * FROM cte WHERE (order_rank <= 5 OR score_rank <= 5) AND thread_rank <= 6
