fix: use UTC_TIMESTAMP() to get current utc date instead of NOW() in sql queries

fixes #153 (closed)

SET AUTOCOMMIT = 0;
START TRANSACTION;

ALTER TABLE `cp_platforms` CHANGE `created_at` `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CHANGE `updated_at` `updated_at` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP; 

DROP VIEW `cp_credits`;
CREATE VIEW `cp_credits` AS
SELECT
    `cp_podcasts_persons`.`person_group` AS `person_group`,
    `cp_podcasts_persons`.`person_id` AS `person_id`,
    `cp_persons`.`full_name` AS `full_name`,
    `cp_podcasts_persons`.`person_role` AS `person_role`,
    `cp_podcasts_persons`.`podcast_id` AS `podcast_id`,
    NULL AS `episode_id`
FROM (`cp_podcasts_persons` JOIN `cp_persons` ON (`cp_podcasts_persons`.`person_id` = `cp_persons`.`id`))
UNION
SELECT
    `cp_episodes_persons`.`person_group` AS `person_group`,
    `cp_episodes_persons`.`person_id` AS `person_id`,
    `cp_persons`.`full_name` AS `full_name`,
    `cp_episodes_persons`.`person_role` AS `person_role`,
    `cp_episodes_persons`.`podcast_id` AS `podcast_id`,
    `cp_episodes_persons`.`episode_id` AS `episode_id`
FROM ((`cp_episodes_persons` JOIN `cp_persons` ON (`cp_episodes_persons`.`person_id` = `cp_persons`.`id`)) JOIN `cp_episodes` ON (`cp_episodes_persons`.`episode_id` = `cp_episodes`.`id`))
WHERE `cp_episodes`.`published_at` <= UTC_TIMESTAMP()
ORDER BY `person_group`, `full_name`, `person_role`, `podcast_id`, `episode_id`;

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
) COMMENT 'Add one hit in podcast logs tables.' DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER
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;
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 //

DROP PROCEDURE `cp_analytics_website` //
CREATE PROCEDURE `cp_analytics_website`(
    IN `p_podcast_id` INT UNSIGNED,
    IN `p_browser` VARCHAR(191),
    IN `p_entry_page` VARCHAR(512),
    IN `p_referer_url` VARCHAR(512),
    IN `p_domain` VARCHAR(128),
    IN `p_keywords` VARCHAR(384)
) COMMENT 'Add one hit in website logs tables.' DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER
BEGIN
    SET @current_date = DATE(UTC_TIMESTAMP());

    INSERT INTO cp_analytics_website_by_browser(`podcast_id`, `browser`, `date`)
        VALUES (p_podcast_id, p_browser, @current_date)
        ON DUPLICATE KEY UPDATE `hits`=`hits`+1;
    INSERT INTO cp_analytics_website_by_referer(`podcast_id`, `referer_url`, `domain`, `keywords`, `date`)
        VALUES (p_podcast_id, p_referer_url, p_domain, p_keywords, @current_date)
        ON DUPLICATE KEY UPDATE `hits`=`hits`+1;
    INSERT INTO cp_analytics_website_by_entry_page(`podcast_id`, `entry_page_url`, `date`)
        VALUES (p_podcast_id, p_entry_page, @current_date)
        ON DUPLICATE KEY UPDATE `hits`=`hits`+1;
END //

COMMIT;