<?php

namespace AcademyProCalendar\Events\Booking;

use AcademyProCalendar\Events\QueryAbstract;

if ( ! defined( 'ABSPATH' ) ) {
	exit; // Exit if accessed directly.
}

class Tutor extends QueryAbstract {


	public string $select  = "p.ID,
        p.post_title,
        p.post_type,
        m.meta_value AS start_date,
        p.guid AS link,
        (
            SELECT meta_value 
            FROM wp_postmeta 
            WHERE meta_key = '_academy_booking_duration' 
            AND post_id = b.post_parent
        ) AS duration";
	public string $count_col = 'p.ID';

	public string $after_query    = 'GROUP BY p.ID, p.post_title, p.post_type, m.meta_value, p.guid, b.ID';
	public int $per_page       = 0;
	public string $order_direction = 'DESC';

	public function __construct( int $year, int $month ) {
		global $wpdb;

		$this->select .= ", (
            SELECT meta_value 
                FROM {$wpdb->postmeta} 
                    WHERE meta_key = '_academy_booking_duration' AND post_id = b.post_parent
        ) as duration, 
        (SELECT display_name FROM {$wpdb->users} WHERE ID = p.post_author) as host,
        'Booking' as platform_type";

		$this->table  = $wpdb->posts . ' p';
		$this->join   = "
            INNER JOIN {$wpdb->posts} b 
                ON p.ID = b.post_parent
            INNER JOIN {$wpdb->postmeta} m 
                ON b.ID = m.post_id
        ";

		$this->where[]  = 'b.post_author = %d';
		$this->args[]   = get_current_user_id();

		$this->where[]  = 'p.post_type = %s';
		$this->args[]   = 'academy_booking';

		$this->where[]  = 'b.post_type = %s';
		$this->args[]   = 'academy_booked';

		$this->where[]  = 'b.post_status != %s';
		$this->args[]   = 'cancel';

		$this->where[]  = 'm.meta_key = %s';
		$this->args[]   = '_academy_booked_schedule_time';

		$this->where[]  = 'YEAR(STR_TO_DATE(m.meta_value, %s)) = %d';
		$this->args[]   = '%Y-%m-%d';
		$this->args[]   = $year;

		$this->where[]  = 'MONTH(STR_TO_DATE(m.meta_value, %s)) = %d';
		$this->args[]   = '%Y-%m-%d';
		$this->args[]   = $month;

		$this->order_by  = 'm.meta_value';
	}
}
