<?php

// require_once( '/Users/osx/Sites/Float/adminsavvy/backend.php' );
include '../backend.php';
require_once( 'common/Logger.php' );

/**
 * Transfer Subscription Report
 */
class SubscribedSummary {
	
	public $conn;
	public $repConn;

	protected $hard_limit = 550;

	function __construct() {
		global $savvyext;
		$this->conn = self::__cnn( $savvyext );
		$this->repConn = self::__repCnn( $savvyext );
	}

	private static function __cnn( $savvyext ) {
		$db_host = $savvyext->cfgReadChar('database.host');
		$db_name = $savvyext->cfgReadChar('database.name');
		$db_user = $savvyext->cfgReadChar('database.user');
		$db_pass = $savvyext->cfgReadChar('database.pass');
		$db_port = $savvyext->cfgReadLong('database.port');
		$connstr = sprintf( 'host=%s port=%s dbname=%s user=%s password=%s', $db_host, $db_port, $db_name, $db_user, $db_pass );
		$conn = pg_connect($connstr);

		return $conn;
	}

	private static function __repCnn( $savvyext ) {
		$db_host = $savvyext->cfgReadChar('database_replica.host');
		$db_name = $savvyext->cfgReadChar('database_replica.name');
		$db_user = $savvyext->cfgReadChar('database_replica.user');
		$db_pass = $savvyext->cfgReadChar('database_replica.pass');
		$db_port = $savvyext->cfgReadLong('database_replica.port');
		$connstr = sprintf( 'host=%s port=%s dbname=%s user=%s password=%s', $db_host, $db_port, $db_name, $db_user, $db_pass );
		$conn = pg_connect($connstr);

		return $conn;
	}

	/**
	 * Get all members_card_assign items
	 * @return array
	 */
	public function getDeals() {
		try {
			// get all deals card
			$sql = sprintf( "
				SELECT 
					mc.id as cardid,
					case when mc.card_country is null then 'Worldwide' else mc.card_country end as country
				from main_cards mc
				where mc.button1_action in ('GOOFFERS')
					and mc.status = 1
			" );

	        $results = pg_query( $this->repConn, $sql );

	        if ( $error = pg_last_error( $this->repConn ) ) {
	        	throw new Exception( $error );
	        }

	        $results = pg_fetch_all( $results );

	        return $results;
	        
		} catch (Exception $e) {
			Logger::debug( $e->getMessage() );
		}
	}

	/**
	 * Get total redeem by cards
	 * @param  array $card_ids list card ids
	 * @return array
	 */
	private function getRedeem( $card_ids ) {
		$redeem = sprintf("
			SELECT 
				mca.card_id as cardid,
				count( mca ) as redeem
				from members_card_assign mca
				left join main_cards mc ON mc.id = mca.card_id
				where
					mca.completed is not null
					and mca.status = 1
					and mca.card_id in (%s)
				group by cardid
		", $card_ids);
		$results = pg_query( $this->repConn, $redeem );
		
		if ( $error = pg_last_error( $this->repConn ) ) {
	    	throw new Exception( $error );
	    }

	    $redeem = pg_fetch_all( $results );

	    return $redeem;
	}

	/**
	 * Get total report
	 * @param  array $card_ids list card ids
	 * @return array
	 */
	private function getReport( $card_ids ) {
		$report = sprintf( "
			SELECT 
				mca.card_id as cardid,
				sum( case when mca.added between now() - interval '24 hours' and now() then 1 else 0 end ) as last_24hours,
				sum( case when mca.added between now() - interval '7 days' and now() then 1 else 0 end ) as last_7days,
				sum( case when mca.added between now() - interval '14 days' and now() then 1 else 0 end ) as last_14days,
				sum( case when mca.added between now() - interval '30 days' and now() then 1 else 0 end ) as last_30days,
				count( mca.id ) as total_sub
				from members_card_assign mca
				left join main_cards mc ON mc.id = mca.card_id
				where
					mc.card_reciept is not null
					and mca.subscribe is not null
					and mca.completed is null
					and mca.reciept_count = 0
					and mca.card_id in (%s)
				group by cardid
		", $card_ids );
		$results = pg_query( $this->repConn, $report );

		if ( $error = pg_last_error( $this->repConn ) ) {
        	throw new Exception( $error );
        }

        $report = pg_fetch_all( $results );

        return $report;
	}

	/**
	 * Get all subscribed report
	 * @return array 
	 */
	public function getSubscripbed() {
		$deals = $this->getDeals();
		try {
			if ( !$deals ) {
				throw new Exception( 'Deals card not found!' );
			}

			$card_ids = implode( ', ', array_column( $deals, 'cardid' ) );
			// get subscribed total redeem
			$redeem = $this->getRedeem( $card_ids );
	        // get subscribed report
			$report = $this->getReport( $card_ids );
			
	        // combine report and redeem
	       	if ( $report ) {
	       		foreach ( $report as $key => $value ) {
	       			$report[ $key ]['total_redeem'] = 0;
	       			if ( $redeem ) {
	       				foreach ( $redeem as $subkey => $subvalue ) {
	       					if ( $subvalue['cardid'] == $value['cardid'] ) {
	       						$report[ $key ]['total_redeem'] = $subvalue['redeem'];
	       					}
	       				}
	       			}
	       		}

	       		$results = array();
	        	array_walk( $report, function( $value, $key ) use (&$results) {
	        		$results[ $value['cardid'] ] = $value;
	        	} );
	        	foreach ( $deals as $key => $value ) {
	        		$cardid = $value['cardid'];
	        		$deals[ $key ]['last_24hours'] = isset( $results[ $cardid ] ) ? $results[ $cardid ]['last_24hours'] : 0;
        			$deals[ $key ]['last_7days'] = isset( $results[ $cardid ] ) ? $results[ $cardid ]['last_7days'] : 0;
        			$deals[ $key ]['last_14days'] = isset( $results[ $cardid ] ) ? $results[ $cardid ]['last_14days'] : 0;
        			$deals[ $key ]['last_30days'] = isset( $results[ $cardid ] ) ? $results[ $cardid ]['last_30days'] : 0;
        			$deals[ $key ]['total_sub'] = isset( $results[ $cardid ] ) ? $results[ $cardid ]['total_sub'] : 0;
        			$deals[ $key ]['total_redeem'] = isset( $results[ $cardid ] ) ? $results[ $cardid ]['total_redeem'] : 0;
	        	}
	       	}

	       	return $deals;

		} catch (Exception $e) {
			Logger::debug( $e->getMessage() );
		}
	}

	/**
	 * Update report after get all subscribed
	 * @return mixed
	 */
	public function updateSubscribedReport() {
		$results = $this->getSubscripbed();
		try {
			
			if ( !$results ) {
				throw new Exception( 'Subscribed not found!' );
			}

			$table_columns = array( 
				'card_id', 
				'country', 
				'last_24hours', 
				'last_7days', 
				'last_14days', 
				'last_30days', 
				'total_sub', 
				'total_redeem' 
			);
			$table_results = array();
			array_filter( $results, function( $var ) use ( &$table_results ) {
				$content = array();
				array_walk( $var, function( $value, $key ) use ( &$content ) {
					$value = $value ?? 0;
					array_push( $content, $value );
				} );
				$table_results[] = sprintf( '(%s)', implode( ', ', $content ) );
			} );

			$sql = sprintf( "
				begin;
					truncate table subscription_summary;
					insert into subscription_summary (%s)
						values %s;
				commit;
				rollback;
			",
			// insert
			implode( ', ', $table_columns ),
			preg_replace( '/([a-zA-Z]+)/m', '\'$0\'', implode( ', ', $table_results ) ) );
			
			$results = pg_query( $this->conn, $sql );

			if ( $error = pg_last_error( $this->repConn ) ) {
	        	throw new Exception( $error );
	        }

	        return 'success';
		} catch (Exception $e) {
			Logger::debug( $e->getMessage() );
		}
	}
}

echo "[" . date("Y-m-d H:i:s") . "] subscribed_summary job is starting.\n";

$instance = new SubscribedSummary();
// $instance->getSubscripbed();
echo $instance->updateSubscribedReport() . PHP_EOL;

echo "[" . date("Y-m-d H:i:s") . "] subscribed_summary job is ending.\n";