<?php
if ( ! defined( 'ABSPATH' ) ) exit;

class EMPR_Shifts {

    public static function query( $args = array() ) {
        global $wpdb;

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

        if ( ! empty( $args['start'] ) )  { $where[] = 's.start_datetime >= %s'; $params[] = $args['start']; }
        if ( ! empty( $args['end'] ) )    { $where[] = 's.end_datetime <= %s';   $params[] = $args['end']; }
        if ( isset( $args['status'] ) )   { $where[] = 's.status = %s';          $params[] = $args['status']; }
        if ( ! empty( $args['user_id'] ) ) {
            $where[] = 'sa.user_id = %d';
            $where[] = "sa.status = 'confirmed'"; // pending requests must not appear in the employee's own row
            $params[] = (int) $args['user_id'];
        }

        $join = ! empty( $args['user_id'] )
            ? "INNER JOIN {$wpdb->prefix}emr_assignments sa ON sa.shift_id = s.id"
            : "LEFT  JOIN {$wpdb->prefix}emr_assignments sa ON sa.shift_id = s.id";

        // assigned_ids must only expose confirmed assignments — pending open_approval
        // requests are not yet scheduled shifts, so they must not appear in roster
        // grids, hours totals, edit-modal pre-checks, or any other consumer of this list.
        $sql = "SELECT s.*,
                        GROUP_CONCAT(DISTINCT CASE WHEN sa.status = 'confirmed' THEN sa.user_id END ORDER BY sa.id SEPARATOR ',') AS assigned_ids,
                        COUNT(CASE WHEN sa.status = 'confirmed' THEN 1 END) AS confirmed_count
                FROM {$wpdb->prefix}emr_shifts s $join
                WHERE " . implode( ' AND ', $where ) . "
                GROUP BY s.id
                ORDER BY s.start_datetime ASC";

        $results = $wpdb->get_results( $wpdb->prepare( $sql, ...$params ) );
        if ( ! $results ) return array();
        return array_map( array( __CLASS__, 'add_attachment_info' ), $results );
    }

    public static function get( $id ) {
        global $wpdb;
        // assigned_ids reflects confirmed assignments only — pending open_approval
        // requesters are not yet scheduled, so they must not appear here.
        $row = $wpdb->get_row( $wpdb->prepare(
            "SELECT s.*, GROUP_CONCAT(DISTINCT CASE WHEN sa.status = 'confirmed' THEN sa.user_id END SEPARATOR ',') AS assigned_ids
             FROM {$wpdb->prefix}emr_shifts s
             LEFT JOIN {$wpdb->prefix}emr_assignments sa ON sa.shift_id = s.id
             WHERE s.id = %d GROUP BY s.id",
            (int) $id
        ) );
        return $row ? self::add_attachment_info( $row ) : null;
    }

    /**
     * Enrich a shift row with attachment_url and attachment_exists flags.
     * Skips the WP Media Library lookup when no attachment_id is set.
     */
    private static function add_attachment_info( $row ) {
        if ( empty( $row->attachment_id ) ) {
            $row->attachment_url    = null;
            $row->attachment_exists = false;
            return $row;
        }
        $url                    = wp_get_attachment_url( (int) $row->attachment_id );
        $row->attachment_url    = $url ? $url : null;
        $row->attachment_exists = (bool) $url;
        return $row;
    }

    public static function create( $data ) {
        global $wpdb;
        $data['created_by'] = get_current_user_id();
        $data['created_at'] = current_time( 'mysql' );
        $wpdb->insert( EMPR_DB::table( 'shifts' ), $data );
        return (int) $wpdb->insert_id;
    }

    public static function update( $id, $data ) {
        global $wpdb;
        $data['updated_at'] = current_time( 'mysql' );
        return (bool) $wpdb->update( EMPR_DB::table( 'shifts' ), $data, array( 'id' => (int) $id ) );
    }

    public static function delete( $id ) {
        global $wpdb;
        $wpdb->delete( EMPR_DB::table( 'assignments' ), array( 'shift_id' => (int) $id ) );
        return (bool) $wpdb->delete( EMPR_DB::table( 'shifts' ), array( 'id' => (int) $id ) );
    }

    public static function sanitize( $raw ) {
        $title = sanitize_text_field( isset( $raw['title'] ) ? $raw['title'] : 'Shift' );
        if ( ! $title ) $title = 'Shift';

        $start = sanitize_text_field( isset( $raw['start_datetime'] ) ? $raw['start_datetime'] : '' );
        $end   = sanitize_text_field( isset( $raw['end_datetime'] )   ? $raw['end_datetime']   : '' );
        if ( ! $start || ! $end ) return new WP_Error( 'missing_dates', 'Start and end datetime are required.' );
        if ( strtotime( $start ) >= strtotime( $end ) ) return new WP_Error( 'invalid_dates', 'End must be after start.' );

        $color = sanitize_hex_color( isset( $raw['color'] ) ? $raw['color'] : '#2563EB' );

        $assignment_type = isset( $raw['assignment_type'] ) ? $raw['assignment_type'] : 'direct';
        if ( ! in_array( $assignment_type, array( 'direct', 'open', 'open_approval' ), true ) ) {
            $assignment_type = 'direct';
        }

        $slot_limit = null;
        if ( in_array( $assignment_type, array( 'open', 'open_approval' ), true ) ) {
            $raw_limit = isset( $raw['slot_limit'] ) ? $raw['slot_limit'] : '';
            if ( $raw_limit !== '' && $raw_limit !== null ) {
                $parsed = (int) $raw_limit;
                if ( $parsed > 0 ) {
                    $slot_limit = $parsed;
                }
            }
        }

        return array(
            'title'           => $title,
            'start_datetime'  => $start,
            'end_datetime'    => $end,
            'location'        => sanitize_text_field( isset( $raw['location'] )   ? $raw['location']   : '' ),
            'department'      => sanitize_text_field( isset( $raw['department'] ) ? $raw['department'] : '' ),
            'assignment_type' => $assignment_type,
            'slot_limit'      => $slot_limit,
            'break_minutes'   => min( 120, max( 0, (int) ( isset( $raw['break_minutes'] ) ? $raw['break_minutes'] : 30 ) ) ),
            'color'           => $color ? $color : '#2563EB',
            'notes'           => mb_substr( sanitize_textarea_field( isset( $raw['notes'] ) ? $raw['notes'] : '' ), 0, 256 ),
            'status'          => in_array( isset( $raw['status'] ) ? $raw['status'] : '', array( 'draft','published','cancelled' ), true ) ? $raw['status'] : 'published',
        );
    }
}
