package Stats::Redshift::SaleStage::RPS;

use strict;
use warnings;

use lib '/app/tools/stats/lib';
use base 'Stats::Redshift::SaleStage';

sub exportsql {
    my $self = shift;
    my $cid  = $self->database->client_id;
    my $sql  = (
        qq{
SELECT /* :comment: */
	s.sale_id,
	s.file_id,
	ABS(TIMESTAMPDIFF(MONTH, s.date_begin, '1916-08-01')) month_id,
	ABS(TIMESTAMPDIFF(MONTH, s.date_end, '1916-08-01')) month_end_id,
	ABS(TIMESTAMPDIFF(QUARTER, s.date_begin, '1916-07-01')) + 667 quarter_id,
	YEAR(s.date_end) AS year,
	s.product_id,
	s.product_type,
	s.format_type,
	IFNULL(s.service_id, f.service_id) service_id,
	t.artist_id,
	a.label_id,
	s.units,
	s.price,
	s.country_code,
	s.conversion_rate,
	s.units * s.price * s.conversion_rate revenue,
	s.date_begin,
	s.date_end,
	DATE(NOW()),
	$cid
FROM
	:database:.sale s
	JOIN :database:.file f ON f.file_id = s.file_id
		AND f.physical IN (0, 2)
		AND f.file_status IN(4, 5)
	JOIN :database:.product p ON p.product_id = s.product_id
	JOIN :database:.track t ON t.track_id = p.asset_id
	JOIN :database:.album a ON a.album_id = t.album_id
WHERE 1
	AND s.product_type = 'T'
	AND IFNULL(s.product_id, 0) != 0
	AND IFNULL(s.price, 0) != 0
	AND IFNULL(s.conversion_rate, 0) != 0
	AND s.format_type != 'M'
	AND p.product_type_id = 4
	AND s.sale_id >= :min:
	AND s.sale_id < :max:
UNION ALL
SELECT
	s.sale_id,
	s.file_id,
	ABS(TIMESTAMPDIFF(MONTH, s.date_begin, '1916-08-01')) month_id,
	ABS(TIMESTAMPDIFF(MONTH, s.date_end, '1916-08-01')) month_end_id,
	ABS(TIMESTAMPDIFF(QUARTER, s.date_begin, '1916-07-01')) + 667 quarter_id,
	YEAR(s.date_end) year,
	s.product_id,
	s.product_type,
	s.format_type,
	IFNULL(s.service_id, f.service_id) service_id,
	a.artist_id,
	a.label_id,
	s.units,
	s.price,
	s.country_code,
	s.conversion_rate,
	s.units * s.price * s.conversion_rate revenue,
	s.date_begin,
	s.date_end,
	DATE(NOW()),
	$cid
FROM
	:database:.sale s
	JOIN :database:.file f ON f.file_id = s.file_id
		AND f.physical IN(0, 2)
		AND f.file_status IN(4, 5)
	JOIN :database:.product p ON p.product_id = s.product_id
	JOIN :database:.album a ON a.album_id = p.asset_id
WHERE
	s.product_type = 'A'
	AND IFNULL(s.product_id, 0) != 0
	AND IFNULL(s.price, 0) != 0
	AND IFNULL(s.conversion_rate, 0) != 0
	AND s.format_type != 'M'
	AND p.product_type_id = 3
	AND s.sale_id >= :min:
	AND s.sale_id < :max: 
INTO OUTFILE ':file:'
	}
    );
    $sql;
}

sub redshift_insert {
    my $self = shift;
    my $sql  = (
        qq{
SELECT 
	s.sale_id,
	s.file_id,
	DATEDIFF(MONTH,TO_DATE('19160801','YYYYMMDD'),s.date_begin) month_id,
	DATEDIFF(MONTH,TO_DATE('19160801','YYYYMMDD'),s.date_end) end_month_id,
	DATEDIFF(QUARTER,TO_DATE('19160701','YYYYMMDD'),s.date_begin)+667 quarter_id,
	EXTRACT(YEAR FROM s.date_end) AS year,
	s.product_id,
	s.product_type,
	s.format_type,
	NVL(s.service_id, f.service_id) service_id,
	t.artist_id,
	a.label_id,
	s.units,
	s.price,
	s.country_code,
	s.conversion_rate,
	s.units * s.price * s.conversion_rate revenue,
	s.date_begin,
	s.date_end,
	s.date_loaded,
	s.client_id
FROM
	rps.sale s
	JOIN rps.file f ON f.file_id = s.file_id
		AND f.physical IN (0, 2)
		AND f.file_status IN (4, 5)
		AND f.client_id = s.client_id
	JOIN rps.product p ON p.product_id = s.product_id
		AND p.client_id=s.client_id
	JOIN rps.track t ON t.track_id = p.asset_id
		AND t.client_id=s.client_id
	JOIN rps.album a ON a.album_id = t.album_id
		AND a.client_id=s.client_id
WHERE 
	s.product_type = 'T'
	AND NVL(s.product_id, 0) != 0
	AND NVL(s.price, 0) != 0
	AND NVL(s.conversion_rate, 0) != 0
	AND s.format_type != 'M'
	AND p.product_type_id = 4
UNION ALL
SELECT
	s.sale_id,
	s.file_id,
	DATEDIFF(MONTH,TO_DATE('19160801','YYYYMMDD'),s.date_begin) month_id,
	DATEDIFF(MONTH,TO_DATE('19160801','YYYYMMDD'),s.date_end) end_month_id,
	DATEDIFF(QUARTER,TO_DATE('19160701','YYYYMMDD'),s.date_begin)+667 quarter_id,
	EXTRACT(YEAR FROM s.date_end) AS year,
	s.product_id,
	s.product_type,
	s.format_type,
	NVL(s.service_id, f.service_id) service_id,
	a.artist_id,
	a.label_id,
	s.units,
	s.price,
	s.country_code,
	s.conversion_rate,
	s.units * s.price * s.conversion_rate revenue,
	s.date_begin,
	s.date_end,
	s.date_loaded,
	s.client_id
FROM
	rps.sale s
	JOIN rps.file f ON f.file_id = s.file_id
		AND f.physical IN (0, 2)
		AND f.file_status IN(4, 5)
		AND f.client_id = s.client_id
	JOIN rps.product p ON p.product_id = s.product_id
		AND p.client_id=s.client_id
	JOIN rps.album a ON a.album_id = p.asset_id
		AND a.client_id=s.client_id
WHERE
	s.product_type = 'A'
	AND NVL(s.product_id, 0) != 0
	AND NVL(s.price, 0) != 0
	AND NVL(s.conversion_rate, 0) != 0
	AND s.format_type != 'M'
	AND p.product_type_id = 3
}
    );
    $sql;
}

1;