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

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

/**
 * CRUD wrapper for the mkam_employees table.
 */
class MKAM_Employee {

	/**
	 * Valid employment types.
	 *
	 * @var string[]
	 */
	const EMPLOYMENT_TYPES = array( 'full_time', 'part_time', 'contract', 'intern' );

	/**
	 * Valid statuses.
	 *
	 * @var string[]
	 */
	const STATUSES = array( 'active', 'inactive', 'terminated', 'on_leave' );

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

	/**
	 * Get a single employee by ID.
	 *
	 * @param int $id Employee 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 linked to a given WordPress user ID.
	 *
	 * @param int $user_id WP user ID.
	 * @return array|null
	 */
	public static function get_by_user( $user_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 wp_user_id = %d", absint( $user_id ) ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);

		return $row ? $row : null;
	}

	/**
	 * Query employees with optional filters.
	 *
	 * @param array $args {
	 *     Optional. Query arguments.
	 *
	 *     @type string $status     Filter by status.
	 *     @type string $department Filter by department.
	 *     @type string $search     Search name/email/employee_id.
	 *     @type string $orderby    Column to order by.
	 *     @type string $order      ASC or DESC.
	 *     @type int    $per_page   Items per page (0 = no limit).
	 *     @type int    $page       Page number.
	 * }
	 * @return array List of employee rows.
	 */
	public static function query( $args = array() ) {
		global $wpdb;
		$table = self::table();

		$defaults = array(
			'status'     => '',
			'department' => '',
			'search'     => '',
			'orderby'    => 'created_at',
			'order'      => 'DESC',
			'per_page'   => 0,
			'page'       => 1,
		);
		$args = wp_parse_args( $args, $defaults );

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

		if ( '' !== $args['status'] ) {
			$where[]  = 'status = %s';
			$params[] = sanitize_text_field( $args['status'] );
		}
		if ( '' !== $args['department'] ) {
			$where[]  = 'department = %s';
			$params[] = sanitize_text_field( $args['department'] );
		}
		if ( '' !== $args['search'] ) {
			$like     = '%' . $wpdb->esc_like( sanitize_text_field( $args['search'] ) ) . '%';
			$where[]  = '(first_name LIKE %s OR last_name LIKE %s OR email LIKE %s OR employee_id LIKE %s)';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

		$allowed_orderby = array( 'id', 'first_name', 'last_name', 'email', 'department', 'status', 'created_at', 'join_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 );
	}

	/**
	 * Count employees matching filters (mirrors query() filters).
	 *
	 * @param array $args Filter arguments.
	 * @return int
	 */
	public static function count( $args = array() ) {
		global $wpdb;
		$table = self::table();

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

		if ( ! empty( $args['status'] ) ) {
			$where[]  = 'status = %s';
			$params[] = sanitize_text_field( $args['status'] );
		}
		if ( ! empty( $args['department'] ) ) {
			$where[]  = 'department = %s';
			$params[] = sanitize_text_field( $args['department'] );
		}
		if ( ! empty( $args['search'] ) ) {
			$like     = '%' . $wpdb->esc_like( sanitize_text_field( $args['search'] ) ) . '%';
			$where[]  = '(first_name LIKE %s OR last_name LIKE %s OR email LIKE %s OR employee_id LIKE %s)';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

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

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

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

	/**
	 * Generate the next sequential employee ID (e.g. EMP-001).
	 *
	 * @return string
	 */
	public static function next_employee_id() {
		global $wpdb;
		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$max = (int) $wpdb->get_var( "SELECT MAX(id) FROM {$table}" );

		return sprintf( 'EMP-%03d', $max + 1 );
	}

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

		if ( isset( $data['wp_user_id'] ) ) {
			$clean['wp_user_id'] = $data['wp_user_id'] ? absint( $data['wp_user_id'] ) : null;
		}
		if ( isset( $data['employee_id'] ) ) {
			$clean['employee_id'] = sanitize_text_field( $data['employee_id'] );
		}
		if ( isset( $data['first_name'] ) ) {
			$clean['first_name'] = sanitize_text_field( $data['first_name'] );
		}
		if ( isset( $data['last_name'] ) ) {
			$clean['last_name'] = sanitize_text_field( $data['last_name'] );
		}
		if ( isset( $data['email'] ) ) {
			$clean['email'] = sanitize_email( $data['email'] );
		}
		if ( isset( $data['phone'] ) ) {
			$clean['phone'] = sanitize_text_field( $data['phone'] );
		}
		if ( isset( $data['department'] ) ) {
			$clean['department'] = sanitize_text_field( $data['department'] );
		}
		if ( isset( $data['designation'] ) ) {
			$clean['designation'] = sanitize_text_field( $data['designation'] );
		}
		if ( isset( $data['shift_id'] ) ) {
			$clean['shift_id'] = $data['shift_id'] ? absint( $data['shift_id'] ) : null;
		}
		if ( isset( $data['employment_type'] ) ) {
			$type                     = sanitize_text_field( $data['employment_type'] );
			$clean['employment_type'] = in_array( $type, self::EMPLOYMENT_TYPES, true ) ? $type : 'full_time';
		}
		if ( isset( $data['hourly_rate'] ) ) {
			$clean['hourly_rate'] = ( '' === $data['hourly_rate'] || null === $data['hourly_rate'] ) ? null : (float) $data['hourly_rate'];
		}
		if ( isset( $data['monthly_salary'] ) ) {
			$clean['monthly_salary'] = ( '' === $data['monthly_salary'] || null === $data['monthly_salary'] ) ? null : (float) $data['monthly_salary'];
		}
		if ( isset( $data['join_date'] ) ) {
			$clean['join_date'] = $data['join_date'] ? sanitize_text_field( $data['join_date'] ) : null;
		}
		if ( isset( $data['status'] ) ) {
			$status         = sanitize_text_field( $data['status'] );
			$clean['status'] = in_array( $status, self::STATUSES, true ) ? $status : 'active';
		}
		if ( isset( $data['avatar_url'] ) ) {
			$clean['avatar_url'] = esc_url_raw( $data['avatar_url'] );
		}
		if ( isset( $data['metadata'] ) ) {
			$clean['metadata'] = is_array( $data['metadata'] ) ? wp_json_encode( $data['metadata'] ) : sanitize_textarea_field( $data['metadata'] );
		}

		return $clean;
	}

	/**
	 * Create an employee.
	 *
	 * @param array $data Raw employee data.
	 * @return int|WP_Error New employee ID or error.
	 */
	public static function create( $data ) {
		global $wpdb;

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

		if ( empty( $clean['first_name'] ) || empty( $clean['last_name'] ) || empty( $clean['email'] ) ) {
			return new WP_Error( 'mkam_employee_invalid', __( 'First name, last name, and email are required.', 'merchantkits-attendance-manager' ) );
		}

		if ( ! is_email( $clean['email'] ) ) {
			return new WP_Error( 'mkam_employee_email', __( 'A valid email address is required.', 'merchantkits-attendance-manager' ) );
		}

		if ( empty( $clean['employee_id'] ) ) {
			$clean['employee_id'] = self::next_employee_id();
		}

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

		if ( false === $result ) {
			return new WP_Error( 'mkam_employee_insert_failed', __( 'Could not create the employee. The email or employee ID may already exist.', 'merchantkits-attendance-manager' ) );
		}

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

		do_action( 'mkam_employee_created', $employee_id );

		return $employee_id;
	}

	/**
	 * Update an employee.
	 *
	 * @param int   $id   Employee ID.
	 * @param array $data Raw employee data.
	 * @return bool|WP_Error True on success or error.
	 */
	public static function update( $id, $data ) {
		global $wpdb;

		$id    = absint( $id );
		$old   = self::get( $id );
		$clean = self::sanitize( $data );

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

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

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

		do_action( 'mkam_employee_updated', $id, $old );

		return true;
	}

	/**
	 * Delete an employee.
	 *
	 * @param int $id Employee ID.
	 * @return bool True on success.
	 */
	public static function delete( $id ) {
		global $wpdb;

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

		if ( $deleted ) {
			do_action( 'mkam_employee_deleted', $id );
		}

		return $deleted;
	}

	/**
	 * Convenience: full display name.
	 *
	 * @param array $employee Employee row.
	 * @return string
	 */
	public static function full_name( $employee ) {
		return trim( ( $employee['first_name'] ?? '' ) . ' ' . ( $employee['last_name'] ?? '' ) );
	}
}
