<?php
/**
 * Attendance data model.
 *
 * @package MerchantKits\AttendanceManager
 */

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

/**
 * CRUD wrapper and clock in/out logic for the mkam_attendance table.
 */
class MKAM_Attendance {

	/**
	 * Valid attendance statuses.
	 *
	 * @var string[]
	 */
	const STATUSES = array( 'present', 'absent', 'half_day', 'late', 'on_leave', 'holiday', 'weekend' );

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

	/**
	 * Get a single attendance record by ID.
	 *
	 * @param int $id Record 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;
	}

	/**
	 * Get an employee's attendance record for a specific date.
	 *
	 * @param int    $employee_id Employee ID.
	 * @param string $date        Date in Y-m-d format.
	 * @return array|null
	 */
	public static function get_for_date( $employee_id, $date ) {
		global $wpdb;
		$table = self::table();

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

		return $row ? $row : null;
	}

	/**
	 * Find today's open record (clocked in but not out) for an employee.
	 *
	 * @param int $employee_id Employee ID.
	 * @return array|null
	 */
	public static function get_open( $employee_id ) {
		global $wpdb;
		$table = self::table();
		$today = current_time( 'Y-m-d' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE employee_id = %d AND date = %s AND clock_in IS NOT NULL AND clock_out IS NULL", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				absint( $employee_id ),
				$today
			),
			ARRAY_A
		);

		return $row ? $row : null;
	}

	/**
	 * Query attendance records 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,
			'status'      => '',
			'date_from'   => '',
			'date_to'     => '',
			'orderby'     => 'date',
			'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['status'] ) {
			$where[]  = 'status = %s';
			$params[] = sanitize_text_field( $args['status'] );
		}
		if ( '' !== $args['date_from'] ) {
			$where[]  = 'date >= %s';
			$params[] = sanitize_text_field( $args['date_from'] );
		}
		if ( '' !== $args['date_to'] ) {
			$where[]  = 'date <= %s';
			$params[] = sanitize_text_field( $args['date_to'] );
		}

		$allowed_orderby = array( 'id', 'date', 'employee_id', 'total_hours', 'status', 'clock_in' );
		$orderby         = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'date';
		$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 );
	}

	/**
	 * Sanitize raw attendance 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['date'] ) ) {
			$clean['date'] = sanitize_text_field( $data['date'] );
		}
		if ( array_key_exists( 'clock_in', $data ) ) {
			$clean['clock_in'] = $data['clock_in'] ? sanitize_text_field( $data['clock_in'] ) : null;
		}
		if ( array_key_exists( 'clock_out', $data ) ) {
			$clean['clock_out'] = $data['clock_out'] ? sanitize_text_field( $data['clock_out'] ) : null;
		}
		if ( isset( $data['clock_in_ip'] ) ) {
			$clean['clock_in_ip'] = sanitize_text_field( $data['clock_in_ip'] );
		}
		if ( isset( $data['clock_out_ip'] ) ) {
			$clean['clock_out_ip'] = sanitize_text_field( $data['clock_out_ip'] );
		}
		if ( isset( $data['clock_in_location'] ) ) {
			$clean['clock_in_location'] = sanitize_text_field( $data['clock_in_location'] );
		}
		if ( isset( $data['clock_out_location'] ) ) {
			$clean['clock_out_location'] = sanitize_text_field( $data['clock_out_location'] );
		}
		if ( isset( $data['clock_in_note'] ) ) {
			$clean['clock_in_note'] = sanitize_textarea_field( $data['clock_in_note'] );
		}
		if ( isset( $data['clock_out_note'] ) ) {
			$clean['clock_out_note'] = sanitize_textarea_field( $data['clock_out_note'] );
		}
		if ( array_key_exists( 'total_hours', $data ) ) {
			$clean['total_hours'] = ( null === $data['total_hours'] || '' === $data['total_hours'] ) ? null : (float) $data['total_hours'];
		}
		if ( array_key_exists( 'overtime_hours', $data ) ) {
			$clean['overtime_hours'] = ( null === $data['overtime_hours'] || '' === $data['overtime_hours'] ) ? null : (float) $data['overtime_hours'];
		}
		if ( isset( $data['status'] ) ) {
			$status          = sanitize_text_field( $data['status'] );
			$clean['status'] = in_array( $status, self::STATUSES, true ) ? $status : 'present';
		}
		if ( isset( $data['late_minutes'] ) ) {
			$clean['late_minutes'] = absint( $data['late_minutes'] );
		}
		if ( isset( $data['early_leave_minutes'] ) ) {
			$clean['early_leave_minutes'] = absint( $data['early_leave_minutes'] );
		}
		if ( isset( $data['is_manual_entry'] ) ) {
			$clean['is_manual_entry'] = empty( $data['is_manual_entry'] ) ? 0 : 1;
		}
		if ( isset( $data['approved_by'] ) ) {
			$clean['approved_by'] = $data['approved_by'] ? absint( $data['approved_by'] ) : null;
		}

		return $clean;
	}

	/**
	 * Create an attendance record.
	 *
	 * @param array $data Raw data.
	 * @return int|WP_Error
	 */
	public static function create( $data ) {
		global $wpdb;

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

		if ( empty( $clean['employee_id'] ) || empty( $clean['date'] ) ) {
			return new WP_Error( 'mkam_attendance_invalid', __( 'Employee and date are required.', 'merchantkits-attendance-manager' ) );
		}

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

		if ( false === $result ) {
			return new WP_Error( 'mkam_attendance_insert_failed', __( 'Could not create the attendance record. One may already exist for this date.', 'merchantkits-attendance-manager' ) );
		}

		return (int) $wpdb->insert_id;
	}

	/**
	 * Update an attendance record.
	 *
	 * @param int   $id   Record 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_attendance_no_data', __( 'No valid attendance 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_attendance_update_failed', __( 'Could not update the attendance record.', 'merchantkits-attendance-manager' ) );
		}

		return true;
	}

	/**
	 * Delete an attendance record.
	 *
	 * @param int $id Record 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' ) );
	}

	/**
	 * Resolve the client IP address, respecting common proxy headers.
	 *
	 * @return string
	 */
	public static function get_client_ip() {
		$headers = array( 'HTTP_CF_CONNECTING_IP', 'HTTP_X_FORWARDED_FOR', 'REMOTE_ADDR' );

		foreach ( $headers as $header ) {
			if ( empty( $_SERVER[ $header ] ) ) {
				continue;
			}

			$value = sanitize_text_field( wp_unslash( $_SERVER[ $header ] ) );
			$parts = explode( ',', $value );
			$ip    = trim( $parts[0] );

			if ( filter_var( $ip, FILTER_VALIDATE_IP ) ) {
				return $ip;
			}
		}

		return '';
	}

	/**
	 * Calculate late minutes against a shift's start time and grace period.
	 *
	 * @param string $clock_in_time MySQL datetime of clock-in.
	 * @param array  $shift         Shift row.
	 * @return int Late minutes (0 when on time or no shift).
	 */
	public static function calculate_late_minutes( $clock_in_time, $shift ) {
		if ( empty( $shift ) || empty( $shift['start_time'] ) ) {
			return 0;
		}

		$clock_in   = strtotime( $clock_in_time );
		$date       = gmdate( 'Y-m-d', $clock_in );
		$shift_start = strtotime( $date . ' ' . $shift['start_time'] );
		$grace       = (int) ( $shift['grace_minutes'] ?? 0 ) * MINUTE_IN_SECONDS;

		$threshold = $shift_start + $grace;

		if ( $clock_in <= $threshold ) {
			return 0;
		}

		return (int) round( ( $clock_in - $shift_start ) / MINUTE_IN_SECONDS );
	}

	/**
	 * Calculate worked hours and overtime between clock in/out.
	 *
	 * @param string $clock_in  MySQL datetime.
	 * @param string $clock_out MySQL datetime.
	 * @param array  $shift     Optional shift row for break/overtime context.
	 * @return array {
	 *     @type float $total_hours    Net worked hours after break.
	 *     @type float $overtime_hours Hours beyond shift duration.
	 * }
	 */
	public static function calculate_hours( $clock_in, $clock_out, $shift = array() ) {
		$in       = strtotime( $clock_in );
		$out      = strtotime( $clock_out );
		$break    = (int) ( $shift['break_minutes'] ?? 0 ) * MINUTE_IN_SECONDS;
		$worked   = max( 0, ( $out - $in ) - $break );
		$total    = round( $worked / HOUR_IN_SECONDS, 2 );
		$overtime = 0.0;

		if ( ! empty( $shift['start_time'] ) && ! empty( $shift['end_time'] ) ) {
			$shift_seconds = strtotime( $shift['end_time'] ) - strtotime( $shift['start_time'] );
			if ( ! empty( $shift['is_overnight'] ) && $shift_seconds < 0 ) {
				$shift_seconds += DAY_IN_SECONDS;
			}
			$shift_hours = max( 0, ( $shift_seconds - $break ) / HOUR_IN_SECONDS );
			if ( $total > $shift_hours ) {
				$overtime = round( $total - $shift_hours, 2 );
			}
		}

		return array(
			'total_hours'    => $total,
			'overtime_hours' => $overtime,
		);
	}
}
