<?php
/**
 * Advanced business metrics and portable report exports.
 *
 * @package Yatoon_Booking_System
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

final class YATOON_Reporting {
	private static function key_sql( $alias = 'b' ) {
		return "LOWER(COALESCE(NULLIF(TRIM({$alias}.customer_email),''),NULLIF(REPLACE(REPLACE(REPLACE(REPLACE({$alias}.customer_phone,' ',''),'-',''),'(',''),')',''),''),CONCAT('booking-',{$alias}.id)))";
	}

	public static function normalize_dates( $from, $to ) {
		$default_from = gmdate( 'Y-m-d', strtotime( '-29 days', current_time( 'timestamp' ) ) );
		$default_to   = current_time( 'Y-m-d' );
		$from = preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $from ) ? $from : $default_from;
		$to   = preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $to ) ? $to : $default_to;
		return $from <= $to ? array( $from, $to ) : array( $to, $from );
	}

	public static function business_capacity_minutes( $from, $to ) {
		$hours = class_exists( 'YATOON_Business_Hours' ) ? YATOON_Business_Hours::get_hours() : array();
		$total = 0;
		$day = new DateTimeImmutable( $from );
		$end = new DateTimeImmutable( $to );
		while ( $day <= $end ) {
			$key = strtolower( $day->format( 'D' ) );
			$row = $hours[ $key ] ?? array();
			if ( ! empty( $row['open'] ) ) {
				$start = strtotime( $day->format( 'Y-m-d' ) . ' ' . ( $row['start'] ?? '09:00' ) );
				$close = strtotime( $day->format( 'Y-m-d' ) . ' ' . ( $row['end'] ?? '17:00' ) );
				$minutes = max( 0, (int) round( ( $close - $start ) / 60 ) );
				if ( ! empty( $row['break_start'] ) && ! empty( $row['break_end'] ) ) {
					$break_start = strtotime( $day->format( 'Y-m-d' ) . ' ' . $row['break_start'] );
					$break_end   = strtotime( $day->format( 'Y-m-d' ) . ' ' . $row['break_end'] );
					$minutes -= max( 0, (int) round( ( $break_end - $break_start ) / 60 ) );
				}
				$total += max( 0, $minutes );
			}
			$day = $day->modify( '+1 day' );
		}
		return $total;
	}

	public static function collect( $from, $to ) {
		global $wpdb;
		list( $from, $to ) = self::normalize_dates( $from, $to );
		$b = esc_sql( $wpdb->prefix . 'yatoon_bookings' );
		$s = esc_sql( $wpdb->prefix . 'yatoon_services' );
		$st = esc_sql( $wpdb->prefix . 'yatoon_staff' );
		$ce = esc_sql( $wpdb->prefix . 'yatoon_commission_entries' );
		$key = self::key_sql();
		$active = "status NOT LIKE 'cancelled%' AND status NOT LIKE 'CANCELLED%' AND status <> 'DECLINED'";
		$completed = "status IN ('completed','COMPLETED')";

		$base = $wpdb->get_row( $wpdb->prepare(
			"SELECT COUNT(*) total_bookings,
			COUNT(DISTINCT {$key}) unique_clients,
			SUM(CASE WHEN {$completed} THEN 1 ELSE 0 END) completed_count,
			SUM(CASE WHEN status IN ('no_show','NO_SHOW') THEN 1 ELSE 0 END) no_show_count,
			SUM(CASE WHEN status LIKE 'cancelled%%' OR status LIKE 'CANCELLED%%' OR status='DECLINED' THEN 1 ELSE 0 END) cancelled_count,
			SUM(CASE WHEN {$active} THEN total_price ELSE 0 END) gross_revenue,
			SUM(CASE WHEN {$active} THEN TIMESTAMPDIFF(MINUTE,start_time,end_time) ELSE 0 END) booked_minutes
			FROM {$b} b WHERE DATE(start_time) BETWEEN %s AND %s",
			$from, $to
		) );

		$repeat_clients = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM (SELECT {$key} client_key,COUNT(*) visits FROM {$b} b WHERE DATE(start_time) BETWEEN %s AND %s AND {$active} GROUP BY client_key HAVING visits>1) x",
			$from, $to
		) );
		$retained = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(DISTINCT p.client_key) FROM (SELECT {$key} client_key FROM {$b} b WHERE DATE(start_time) BETWEEN %s AND %s AND {$active}) p INNER JOIN (SELECT {$key} client_key FROM {$b} b WHERE DATE(start_time)<%s AND {$completed} GROUP BY client_key) old ON old.client_key=p.client_key",
			$from, $to, $from
		) );
		$eligible = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(DISTINCT {$key}) FROM {$b} b WHERE DATE(start_time) BETWEEN DATE_SUB(%s,INTERVAL 365 DAY) AND DATE_SUB(%s,INTERVAL 1 DAY) AND {$completed}",
			$from, $from
		) );
		$rebooked = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(DISTINCT p.client_key) FROM (SELECT {$key} client_key,MAX(start_time) last_time FROM {$b} b WHERE DATE(start_time) BETWEEN %s AND %s AND {$active} GROUP BY client_key) p INNER JOIN (SELECT {$key} client_key,start_time FROM {$b} b WHERE start_time>%s AND status IN ('pending','confirmed','ACCEPTED')) f ON f.client_key=p.client_key AND f.start_time>p.last_time",
			$from, $to, $to . ' 23:59:59'
		) );
		$future = $wpdb->get_row( $wpdb->prepare(
			"SELECT COUNT(*) booking_count,COALESCE(SUM(total_price),0) revenue FROM {$b} WHERE start_time>=%s AND start_time<%s AND status IN ('pending','confirmed','ACCEPTED')",
			current_time( 'mysql' ), gmdate( 'Y-m-d H:i:s', strtotime( '+30 days', current_time( 'timestamp' ) ) )
		) );
		$commission = self::table_exists( $ce ) ? (float) $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE(SUM(total_amount),0) FROM {$ce} WHERE DATE(calculated_at) BETWEEN %s AND %s", $from, $to ) ) : 0.0;
		$source_rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT CASE WHEN square_booking_id IS NOT NULL AND square_booking_id<>'' THEN 'Square' ELSE 'WordPress / Local' END source,COUNT(*) bookings,SUM(CASE WHEN {$active} THEN total_price ELSE 0 END) revenue FROM {$b} WHERE DATE(start_time) BETWEEN %s AND %s GROUP BY source ORDER BY bookings DESC",
			$from, $to
		) );
		$service_profit = $wpdb->get_results( $wpdb->prepare(
			"SELECT COALESCE(s.name,'Unknown service') name,COUNT(b.id) bookings,COALESCE(SUM(CASE WHEN {$active} THEN b.total_price ELSE 0 END),0) revenue,COALESCE(SUM(CASE WHEN {$active} THEN COALESCE(s.estimated_cost,0) ELSE 0 END),0) supply_cost,COALESCE(SUM(CASE WHEN {$active} THEN b.total_price-COALESCE(s.estimated_cost,0) ELSE 0 END),0) profit_before_commission FROM {$b} b LEFT JOIN {$s} s ON s.id=b.service_id WHERE DATE(b.start_time) BETWEEN %s AND %s GROUP BY b.service_id,s.name ORDER BY revenue DESC LIMIT 20",
			$from, $to
		) );
		$staff_rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT st.id,st.name,COUNT(b.id) bookings,COALESCE(SUM(CASE WHEN {$active} THEN TIMESTAMPDIFF(MINUTE,b.start_time,b.end_time) ELSE 0 END),0) booked_minutes,COALESCE(SUM(CASE WHEN {$active} THEN b.total_price ELSE 0 END),0) revenue FROM {$st} st LEFT JOIN {$b} b ON b.staff_id=st.id AND DATE(b.start_time) BETWEEN %s AND %s WHERE st.is_active=1 GROUP BY st.id,st.name ORDER BY booked_minutes DESC",
			$from, $to
		) );
		$capacity_each = self::business_capacity_minutes( $from, $to );
		foreach ( $staff_rows as $row ) {
			$row->capacity_minutes = $capacity_each;
			$row->utilization = $capacity_each > 0 ? round( (float) $row->booked_minutes * 100 / $capacity_each, 1 ) : 0;
		}
		$total = max( 0, (int) ( $base->total_bookings ?? 0 ) );
		$unique = max( 0, (int) ( $base->unique_clients ?? 0 ) );
		return array(
			'from' => $from,
			'to' => $to,
			'total_bookings' => $total,
			'unique_clients' => $unique,
			'repeat_clients' => $repeat_clients,
			'repeat_rate' => $unique ? round( $repeat_clients * 100 / $unique, 1 ) : 0,
			'retention_rate' => $eligible ? round( $retained * 100 / $eligible, 1 ) : 0,
			'rebook_rate' => $unique ? round( $rebooked * 100 / $unique, 1 ) : 0,
			'cancellation_rate' => $total ? round( (int) $base->cancelled_count * 100 / $total, 1 ) : 0,
			'no_show_rate' => $total ? round( (int) $base->no_show_count * 100 / $total, 1 ) : 0,
			'gross_revenue' => (float) ( $base->gross_revenue ?? 0 ),
			'supply_cost_total' => array_sum( array_map( static function ( $row ) {
			return (float) $row->supply_cost; }, (array) $service_profit ) ),
			'commission_total' => $commission,
			'contribution_after_commission' => max( 0, (float) ( $base->gross_revenue ?? 0 ) - $commission - array_sum( array_map( static function ( $row ) {
			return (float) $row->supply_cost; }, (array) $service_profit ) ) ),
			'future_bookings_30' => (int) ( $future->booking_count ?? 0 ),
			'future_revenue_30' => (float) ( $future->revenue ?? 0 ),
			'sources' => $source_rows,
			'services' => $service_profit,
			'staff' => $staff_rows,
		);
	}

	private static function table_exists( $table ) {
		global $wpdb;
		return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table;
	}

	public static function pdf_response( $metrics ) {
		$lines = array(
			'Yatoon Business Report',
			$metrics['from'] . ' to ' . $metrics['to'],
			'',
			'Bookings: ' . $metrics['total_bookings'],
			'Unique clients: ' . $metrics['unique_clients'],
			'Repeat client rate: ' . $metrics['repeat_rate'] . '%',
			'Retention rate: ' . $metrics['retention_rate'] . '%',
			'Rebooking rate: ' . $metrics['rebook_rate'] . '%',
			'Cancellation rate: ' . $metrics['cancellation_rate'] . '%',
			'No-show rate: ' . $metrics['no_show_rate'] . '%',
			'Gross revenue: $' . number_format( $metrics['gross_revenue'], 2, '.', ',' ),
			'Commission: $' . number_format( $metrics['commission_total'], 2, '.', ',' ),
			'Estimated supply cost: $' . number_format( $metrics['supply_cost_total'], 2, '.', ',' ),
			'Estimated contribution: $' . number_format( $metrics['contribution_after_commission'], 2, '.', ',' ),
			'Future 30-day revenue: $' . number_format( $metrics['future_revenue_30'], 2, '.', ',' ),
			'',
			'Top services',
		);
		foreach ( array_slice( (array) $metrics['services'], 0, 15 ) as $row ) {
			$lines[] = $row->name . ' - ' . (int) $row->bookings . ' bookings - $' . number_format( (float) $row->revenue, 2, '.', ',' ) . ' revenue - $' . number_format( (float) $row->profit_before_commission, 2, '.', ',' ) . ' before commission';
		}
		$content = "BT\n/F1 11 Tf\n50 760 Td\n14 TL\n";
		foreach ( $lines as $line ) {
			$converted = function_exists( 'iconv' ) ? iconv( 'UTF-8', 'Windows-1252//TRANSLIT', $line ) : $line;
			$line = str_replace( array( '\\', '(', ')' ), array( '\\\\', '\\(', '\\)' ), $converted ?: $line );
			$content .= '(' . $line . ") Tj\nT*\n";
		}
		$content .= 'ET';
		$objects = array(
			'<< /Type /Catalog /Pages 2 0 R >>',
			'<< /Type /Pages /Kids [3 0 R] /Count 1 >>',
			'<< /Type /Page /Parent 2 0 R /MediaBox [0 0 612 792] /Resources << /Font << /F1 5 0 R >> >> /Contents 4 0 R >>',
			'<< /Length ' . strlen( $content ) . ">>\nstream\n" . $content . "\nendstream",
			'<< /Type /Font /Subtype /Type1 /BaseFont /Helvetica >>',
		);
		$pdf = "%PDF-1.4\n";
		$offsets = array( 0 );
		foreach ( $objects as $i => $object ) {
			$offsets[] = strlen( $pdf );
			$pdf .= ( $i + 1 ) . " 0 obj\n{$object}\nendobj\n";
		}
		$xref = strlen( $pdf );
		$pdf .= "xref\n0 " . ( count( $objects ) + 1 ) . "\n0000000000 65535 f \n";
		for ( $i = 1; $i <= count( $objects ); $i++ ) {
			$pdf .= sprintf( "%010d 00000 n \n", $offsets[ $i ] );
		}
		$pdf .= 'trailer << /Size ' . ( count( $objects ) + 1 ) . " /Root 1 0 R >>\nstartxref\n{$xref}\n%%EOF";
		header( 'Content-Type: application/pdf' );
		header( 'Content-Disposition: attachment; filename=yatoon-report-' . $metrics['from'] . '-to-' . $metrics['to'] . '.pdf' );
		header( 'Content-Length: ' . strlen( $pdf ) );
		echo $pdf; // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
		exit;
	}
}
