<?php
/**
 * Leave request data model and leave-balance logic.
 *
 * @package MerchantKits\AttendanceManager
 */

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

/**
 * CRUD wrapper for the mkam_leaves table plus leave policy helpers.
 */
class MKAM_Leave {

	/**
	 * Valid leave statuses.
	 *
	 * @var string[]
	 */
	const STATUSES = array( 'pending', 'approved', 'rejected', 'cancelled' );

	/**
	 * Valid half-day periods.
	 *
	 * @var string[]
	 */
	const HALF_DAY_PERIODS = array( 'first_half', 'second_half' );

	/**
	 * Return the leaves table name.
	 *
	 * @return string
	 */
	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'mkam_leaves';
	}

	/**
	 * Return the leave policies table name.
	 *
	 * @return string
	 */
	public static function policies_table() {
		global $wpdb;
		return $wpdb->prefix . 'mkam_leave_policies';
	}

	/**
	 * Get a single leave request by ID.
	 *
	 * @param int $id Leave ID.
	 * @return array|null
	 */
	public static function get( $id ) {
		global $wpdb;
		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", absint( $id ) ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);

		return $row ? $row : null;
	}

	/**
	 * Query leave requests with filters.
	 *
	 * @param array $args Filter arguments.
	 * @return array
	 */
	public static function query( $args = array() ) {
		global $wpdb;
		$table = self::table();

		$defaults = array(
			'employee_id' => 0,
			'leave_type'  => '',
			'status'      => '',
			'date_from'   => '',
			'date_to'     => '',
			'year'        => 0,
			'orderby'     => 'created_at',
			'order'       => 'DESC',
			'per_page'    => 0,
			'page'        => 1,
		);
		$args = wp_parse_args( $args, $defaults );

		$where  = array( '1=1' );
		$params = array();

		if ( $args['employee_id'] > 0 ) {
			$where[]  = 'employee_id = %d';
			$params[] = absint( $args['employee_id'] );
		}
		if ( '' !== $args['leave_type'] ) {
			$where[]  = 'leave_type = %s';
			$params[] = sanitize_text_field( $args['leave_type'] );
		}
		if ( '' !== $args['status'] ) {
			$where[]  = 'status = %s';
			$params[] = sanitize_text_field( $args['status'] );
		}
		if ( '' !== $args['date_from'] ) {
			$where[]  = 'end_date >= %s';
			$params[] = sanitize_text_field( $args['date_from'] );
		}
		if ( '' !== $args['date_to'] ) {
			$where[]  = 'start_date <= %s';
			$params[] = sanitize_text_field( $args['date_to'] );
		}
		if ( $args['year'] > 0 ) {
			$where[]  = 'YEAR(start_date) = %d';
			$params[] = absint( $args['year'] );
		}

		$allowed_orderby = array( 'id', 'created_at', 'employee_id', 'status', 'total_days', 'start_date' );
		$orderby         = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
		$order           = ( 'ASC' === strtoupper( $args['order'] ) ) ? 'ASC' : 'DESC';

		$sql = "SELECT * FROM {$table} WHERE " . implode( ' AND ', $where ) . " ORDER BY {$orderby} {$order}";

		if ( $args['per_page'] > 0 ) {
			$offset   = ( max( 1, absint( $args['page'] ) ) - 1 ) * absint( $args['per_page'] );
			$sql     .= ' LIMIT %d OFFSET %d';
			$params[] = absint( $args['per_page'] );
			$params[] = $offset;
		}

		if ( ! empty( $params ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			return $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		return $wpdb->get_results( $sql, ARRAY_A );
	}

	/**
	 * Get a leave policy by leave type.
	 *
	 * @param string $leave_type Leave type slug.
	 * @return array|null
	 */
	public static function get_policy( $leave_type ) {
		global $wpdb;
		$table = self::policies_table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE leave_type = %s", sanitize_text_field( $leave_type ) ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);

		return $row ? $row : null;
	}

	/**
	 * Get all active leave policies.
	 *
	 * @return array
	 */
	public static function get_policies() {
		global $wpdb;
		$table = self::policies_table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		return $wpdb->get_results( "SELECT * FROM {$table} WHERE status = 'active' ORDER BY label ASC", ARRAY_A );
	}

	/**
	 * Sanitize raw leave data.
	 *
	 * @param array $data Raw input.
	 * @return array
	 */
	protected static function sanitize( $data ) {
		$clean = array();

		if ( isset( $data['employee_id'] ) ) {
			$clean['employee_id'] = absint( $data['employee_id'] );
		}
		if ( isset( $data['leave_type'] ) ) {
			$clean['leave_type'] = sanitize_text_field( $data['leave_type'] );
		}
		if ( isset( $data['start_date'] ) ) {
			$clean['start_date'] = sanitize_text_field( $data['start_date'] );
		}
		if ( isset( $data['end_date'] ) ) {
			$clean['end_date'] = sanitize_text_field( $data['end_date'] );
		}
		if ( isset( $data['total_days'] ) ) {
			$clean['total_days'] = (float) $data['total_days'];
		}
		if ( isset( $data['is_half_day'] ) ) {
			$clean['is_half_day'] = empty( $data['is_half_day'] ) ? 0 : 1;
		}
		if ( array_key_exists( 'half_day_period', $data ) ) {
			$period                   = sanitize_text_field( (string) $data['half_day_period'] );
			$clean['half_day_period'] = in_array( $period, self::HALF_DAY_PERIODS, true ) ? $period : null;
		}
		if ( isset( $data['reason'] ) ) {
			$clean['reason'] = sanitize_textarea_field( $data['reason'] );
		}
		if ( isset( $data['status'] ) ) {
			$status          = sanitize_text_field( $data['status'] );
			$clean['status'] = in_array( $status, self::STATUSES, true ) ? $status : 'pending';
		}
		if ( isset( $data['reviewed_by'] ) ) {
			$clean['reviewed_by'] = $data['reviewed_by'] ? absint( $data['reviewed_by'] ) : null;
		}
		if ( array_key_exists( 'reviewed_at', $data ) ) {
			$clean['reviewed_at'] = $data['reviewed_at'] ? sanitize_text_field( $data['reviewed_at'] ) : null;
		}
		if ( isset( $data['review_note'] ) ) {
			$clean['review_note'] = sanitize_textarea_field( $data['review_note'] );
		}

		return $clean;
	}

	/**
	 * Count inclusive days between two dates (half-day aware).
	 *
	 * @param string $start_date  Y-m-d.
	 * @param string $end_date    Y-m-d.
	 * @param bool   $is_half_day Whether the request is a half day.
	 * @return float
	 */
	public static function count_days( $start_date, $end_date, $is_half_day = false ) {
		if ( $is_half_day ) {
			return 0.5;
		}

		$start = strtotime( $start_date );
		$end   = strtotime( $end_date );

		if ( false === $start || false === $end || $end < $start ) {
			return 0;
		}

		return (float) ( ( ( $end - $start ) / DAY_IN_SECONDS ) + 1 );
	}

	/**
	 * Detect whether an employee has an overlapping approved/pending leave.
	 *
	 * @param int    $employee_id Employee ID.
	 * @param string $start_date  Y-m-d.
	 * @param string $end_date    Y-m-d.
	 * @param int    $exclude_id  Leave ID to exclude (when editing).
	 * @return bool
	 */
	public static function has_overlap( $employee_id, $start_date, $end_date, $exclude_id = 0 ) {
		global $wpdb;
		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table}
				WHERE employee_id = %d
				AND id != %d
				AND status IN ('pending','approved')
				AND start_date <= %s
				AND end_date >= %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				absint( $employee_id ),
				absint( $exclude_id ),
				sanitize_text_field( $end_date ),
				sanitize_text_field( $start_date )
			)
		);

		return $count > 0;
	}

	/**
	 * Calculate the leave balance for an employee and leave type for a year.
	 *
	 * balance = days_per_year + carried_forward - approved_this_year - pending_this_year
	 *
	 * @param int    $employee_id     Employee ID.
	 * @param string $leave_type      Leave type slug.
	 * @param int    $year            Year (defaults to current).
	 * @param float  $carried_forward Carried-forward days from last year.
	 * @return array {
	 *     @type float $entitlement Base + carried days.
	 *     @type float $used        Approved days this year.
	 *     @type float $pending     Pending days this year.
	 *     @type float $balance     Remaining balance.
	 * }
	 */
	public static function get_balance( $employee_id, $leave_type, $year = 0, $carried_forward = 0 ) {
		global $wpdb;
		$table  = self::table();
		$year   = $year > 0 ? absint( $year ) : (int) current_time( 'Y' );
		$policy = self::get_policy( $leave_type );
		$base   = $policy ? (float) $policy['days_per_year'] : 0;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$used = (float) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COALESCE(SUM(total_days),0) FROM {$table}
				WHERE employee_id = %d AND leave_type = %s AND status = 'approved' AND YEAR(start_date) = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				absint( $employee_id ),
				sanitize_text_field( $leave_type ),
				$year
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$pending = (float) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COALESCE(SUM(total_days),0) FROM {$table}
				WHERE employee_id = %d AND leave_type = %s AND status = 'pending' AND YEAR(start_date) = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				absint( $employee_id ),
				sanitize_text_field( $leave_type ),
				$year
			)
		);

		$entitlement = $base + (float) $carried_forward;

		return array(
			'entitlement' => $entitlement,
			'used'        => $used,
			'pending'     => $pending,
			'balance'     => $entitlement - $used - $pending,
		);
	}

	/**
	 * Create a leave request with validation.
	 *
	 * @param array $data Raw leave data.
	 * @return int|WP_Error
	 */
	public static function create( $data ) {
		global $wpdb;

		$clean = self::sanitize( $data );

		if ( empty( $clean['employee_id'] ) || empty( $clean['leave_type'] ) || empty( $clean['start_date'] ) || empty( $clean['end_date'] ) || empty( $clean['reason'] ) ) {
			return new WP_Error( 'mkam_leave_invalid', __( 'Leave type, dates, and reason are required.', 'merchantkits-attendance-manager' ) );
		}

		$policy = self::get_policy( $clean['leave_type'] );
		if ( ! $policy || 'active' !== $policy['status'] ) {
			return new WP_Error( 'mkam_leave_type_invalid', __( 'The selected leave type is not available.', 'merchantkits-attendance-manager' ) );
		}

		if ( strtotime( $clean['end_date'] ) < strtotime( $clean['start_date'] ) ) {
			return new WP_Error( 'mkam_leave_dates', __( 'End date cannot be before the start date.', 'merchantkits-attendance-manager' ) );
		}

		if ( ! isset( $clean['total_days'] ) || $clean['total_days'] <= 0 ) {
			$clean['total_days'] = self::count_days( $clean['start_date'], $clean['end_date'], ! empty( $clean['is_half_day'] ) );
		}

		if ( self::has_overlap( $clean['employee_id'], $clean['start_date'], $clean['end_date'] ) ) {
			return new WP_Error( 'mkam_leave_overlap', __( 'This request overlaps with an existing leave request.', 'merchantkits-attendance-manager' ) );
		}

		$notice_days = (int) $policy['min_notice_days'];
		if ( $notice_days > 0 ) {
			$min_start = strtotime( current_time( 'Y-m-d' ) . ' +' . $notice_days . ' days' );
			if ( strtotime( $clean['start_date'] ) < $min_start ) {
				return new WP_Error(
					'mkam_leave_notice',
					sprintf(
						/* translators: %d: number of days notice required. */
						__( 'This leave type requires at least %d day(s) notice.', 'merchantkits-attendance-manager' ),
						$notice_days
					)
				);
			}
		}

		$balance = self::get_balance( $clean['employee_id'], $clean['leave_type'] );
		if ( $clean['total_days'] > $balance['balance'] && $policy['is_paid'] ) {
			return new WP_Error( 'mkam_leave_balance', __( 'Insufficient leave balance for this request.', 'merchantkits-attendance-manager' ) );
		}

		$clean['status'] = 'pending';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->insert( self::table(), $clean );

		if ( false === $result ) {
			return new WP_Error( 'mkam_leave_insert_failed', __( 'Could not submit the leave request.', 'merchantkits-attendance-manager' ) );
		}

		$leave_id = (int) $wpdb->insert_id;

		do_action( 'mkam_leave_requested', $leave_id, $clean['employee_id'] );

		return $leave_id;
	}

	/**
	 * Update a leave request.
	 *
	 * @param int   $id   Leave ID.
	 * @param array $data Raw data.
	 * @return bool|WP_Error
	 */
	public static function update( $id, $data ) {
		global $wpdb;

		$clean = self::sanitize( $data );

		if ( empty( $clean ) ) {
			return new WP_Error( 'mkam_leave_no_data', __( 'No valid leave data supplied.', 'merchantkits-attendance-manager' ) );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->update( self::table(), $clean, array( 'id' => absint( $id ) ) );

		if ( false === $result ) {
			return new WP_Error( 'mkam_leave_update_failed', __( 'Could not update the leave request.', 'merchantkits-attendance-manager' ) );
		}

		return true;
	}

	/**
	 * Approve a leave request.
	 *
	 * @param int    $id          Leave ID.
	 * @param int    $reviewer_id Reviewing user ID.
	 * @param string $note        Optional review note.
	 * @return bool|WP_Error
	 */
	public static function approve( $id, $reviewer_id, $note = '' ) {
		$leave = self::get( $id );
		if ( ! $leave ) {
			return new WP_Error( 'mkam_leave_not_found', __( 'Leave request not found.', 'merchantkits-attendance-manager' ) );
		}

		$result = self::update(
			$id,
			array(
				'status'      => 'approved',
				'reviewed_by' => absint( $reviewer_id ),
				'reviewed_at' => current_time( 'mysql' ),
				'review_note' => $note,
			)
		);

		if ( is_wp_error( $result ) ) {
			return $result;
		}

		do_action( 'mkam_leave_approved', absint( $id ), absint( $leave['employee_id'] ), absint( $reviewer_id ) );

		return true;
	}

	/**
	 * Reject a leave request.
	 *
	 * @param int    $id          Leave ID.
	 * @param int    $reviewer_id Reviewing user ID.
	 * @param string $note        Optional review note.
	 * @return bool|WP_Error
	 */
	public static function reject( $id, $reviewer_id, $note = '' ) {
		$leave = self::get( $id );
		if ( ! $leave ) {
			return new WP_Error( 'mkam_leave_not_found', __( 'Leave request not found.', 'merchantkits-attendance-manager' ) );
		}

		$result = self::update(
			$id,
			array(
				'status'      => 'rejected',
				'reviewed_by' => absint( $reviewer_id ),
				'reviewed_at' => current_time( 'mysql' ),
				'review_note' => $note,
			)
		);

		if ( is_wp_error( $result ) ) {
			return $result;
		}

		do_action( 'mkam_leave_rejected', absint( $id ), absint( $leave['employee_id'] ), absint( $reviewer_id ) );

		return true;
	}

	/**
	 * Delete a leave request.
	 *
	 * @param int $id Leave ID.
	 * @return bool
	 */
	public static function delete( $id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->delete( self::table(), array( 'id' => absint( $id ) ), array( '%d' ) );
	}
}
