feat: add podcast subscriptions for private episodes

Update instructions

SET AUTOCOMMIT = 0;
START TRANSACTION;

ALTER TABLE `cp_podcasts` ADD COLUMN `is_premium_by_default` BOOLEAN NOT NULL DEFAULT 0 AFTER `partner_image_url`;

ALTER TABLE `cp_episodes` ADD COLUMN `is_premium` BOOLEAN NOT NULL DEFAULT 0 AFTER `comments_count`;

INSERT INTO `cp_auth_permissions` (`name`, `description`)
VALUES ('podcast-manage_subscriptions', 'Add / edit / remove podcast subscriptions');

-- add manage_subscriptions permission to podcast_admin
SELECT @podcast_admin_group_id := `id` FROM `cp_auth_groups` WHERE `name` = 'podcast_admin';
SELECT @manage_subscriptions_id := `id` FROM `cp_auth_permissions` WHERE `name` = 'podcast-manage_subscriptions';

INSERT INTO `cp_auth_groups_permissions` (`group_id`, `permission_id`)
VALUES
  (@podcast_admin_group_id, @manage_subscriptions_id);

--
-- Table structure for table `cp_subscriptions`
--

CREATE TABLE `cp_subscriptions` (
  `id` int(10) UNSIGNED NOT NULL,
  `podcast_id` int(10) UNSIGNED NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('active','suspended') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `status_message` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `updated_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
);

--
-- Table structure for table `cp_analytics_podcasts_by_subscription`
--

CREATE TABLE `cp_analytics_podcasts_by_subscription` (
  `podcast_id` int(10) UNSIGNED NOT NULL,
  `episode_id` int(10) UNSIGNED NOT NULL,
  `subscription_id` int(10) UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `hits` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
);

--
-- Indexes for table `cp_analytics_podcasts_by_subscription`
--
ALTER TABLE `cp_analytics_podcasts_by_subscription`
  ADD PRIMARY KEY (`podcast_id`,`episode_id`,`subscription_id`,`date`);

--
-- Indexes for table `cp_subscriptions`
--
ALTER TABLE `cp_subscriptions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `podcast_id_email` (`podcast_id`,`email`),
  ADD UNIQUE KEY `token` (`token`),
  ADD KEY `cp_subscriptions_created_by_foreign` (`created_by`),
  ADD KEY `cp_subscriptions_updated_by_foreign` (`updated_by`);

--
-- AUTO_INCREMENT for table `cp_subscriptions`
--
ALTER TABLE `cp_subscriptions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for table `cp_subscriptions`
--
ALTER TABLE `cp_subscriptions`
  ADD CONSTRAINT `cp_subscriptions_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `cp_users` (`id`),
  ADD CONSTRAINT `cp_subscriptions_podcast_id_foreign` FOREIGN KEY (`podcast_id`) REFERENCES `cp_podcasts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `cp_subscriptions_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `cp_users` (`id`);
COMMIT;


--
-- Redefining procedure `cp_analytics_podcasts`
--
DELIMITER $$
DROP PROCEDURE `cp_analytics_podcasts` $$
CREATE PROCEDURE `cp_analytics_podcasts`(
    IN `p_podcast_id` INT UNSIGNED,
    IN `p_episode_id` INT UNSIGNED,
    IN `p_country_code` VARCHAR(3),
    IN `p_region_code` VARCHAR(3),
    IN `p_latitude` DECIMAL(8,6),
    IN `p_longitude` DECIMAL(9,6),
    IN `p_service` VARCHAR(128),
    IN `p_app` VARCHAR(128),
    IN `p_device` VARCHAR(32),
    IN `p_os` VARCHAR(32),
    IN `p_bot` TINYINT(1) UNSIGNED,
    IN `p_filesize` INT UNSIGNED,
    IN `p_duration` DECIMAL(8,3) UNSIGNED,
    IN `p_age` INT UNSIGNED,
    IN `p_new_listener` TINYINT(1) UNSIGNED,
    IN `p_subscription_id` INT UNSIGNED
    )
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Add one hit in podcast logs tables.'
BEGIN

SET @current_datetime = UTC_TIMESTAMP();
SET @current_date = DATE(@current_datetime);
SET @current_hour = HOUR(@current_datetime);

IF NOT `p_bot` THEN
    INSERT INTO `cp_analytics_podcasts`(`podcast_id`, `date`, `duration`, `bandwidth`)
        VALUES (p_podcast_id, @current_date, `p_duration`, `p_filesize`)
        ON DUPLICATE KEY UPDATE
            `duration`=`duration`+`p_duration`,
            `bandwidth`=`bandwidth`+`p_filesize`,
            `hits`=`hits`+1,
            `unique_listeners`=`unique_listeners`+`p_new_listener`;
    INSERT INTO `cp_analytics_podcasts_by_hour`(`podcast_id`, `date`, `hour`)
        VALUES (p_podcast_id, @current_date, @current_hour)
        ON DUPLICATE KEY UPDATE `hits`=`hits`+1;
    INSERT INTO `cp_analytics_podcasts_by_episode`(`podcast_id`, `episode_id`, `date`, `age`)
        VALUES (p_podcast_id, p_episode_id, @current_date, p_age)
        ON DUPLICATE KEY UPDATE `hits`=`hits`+1;
    INSERT INTO `cp_analytics_podcasts_by_country`(`podcast_id`, `country_code`, `date`)
        VALUES (p_podcast_id, p_country_code, @current_date)
        ON DUPLICATE KEY UPDATE `hits`=`hits`+1;
    INSERT INTO `cp_analytics_podcasts_by_region`(`podcast_id`, `country_code`, `region_code`, `latitude`, `longitude`, `date`)
        VALUES (p_podcast_id, p_country_code, p_region_code, p_latitude, p_longitude, @current_date)
        ON DUPLICATE KEY UPDATE `hits`=`hits`+1;

    IF `p_subscription_id` THEN
        INSERT INTO `cp_analytics_podcasts_by_subscription`(`podcast_id`, `episode_id`, `subscription_id`, `date`)
        VALUES (p_podcast_id, p_episode_id, p_subscription_id, @current_date)
        ON DUPLICATE KEY UPDATE `hits`=`hits`+1;
    END IF;
END IF;
INSERT INTO `cp_analytics_podcasts_by_player`(`podcast_id`, `service`, `app`, `device`, `os`, `is_bot`, `date`)
    VALUES (p_podcast_id, p_service, p_app, p_device, p_os, p_bot, @current_date)
    ON DUPLICATE KEY UPDATE `hits`=`hits`+1;
END$$
DELIMITER ;

COMMIT;