<?php
/**
 * Meeting Model
 *
 * Handles CRUD operations for meetings.
 *
 * @package BoostBot
 * @since   1.0.0
 */

// Prevent direct access.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Model queries bind values via prepare(); table names come from $this->get_table_name() which is sanitized in constructor. Direct queries are intentional: model owns a custom table with no WP-core alternative, and caching is handled at a higher layer (the endpoint response cache).
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * Class BoostBot_Meeting_Model
 *
 * Model for managing meeting data.
 */
class BoostBot_Meeting_Model extends BoostBot_Base_Model {

    /**
     * Table name without prefix.
     *
     * @var string
     */
    protected $table_name = 'boostbot_meetings';

    /**
     * Constructor.
     */
    public function __construct() {
        global $wpdb;
        parent::__construct();
        $this->table_name = self::sanitize_identifier( $wpdb->prefix . 'boostbot_meetings' );
    }

    /**
     * Get full table name.
     *
     * @return string
     */
    public function get_table_name(): string {
        return $this->table_name;
    }

    /**
     * Fillable columns.
     *
     * @var array
     */
    protected $fillable = array(
        'language',
        'conversation_id',
        'chatbot_id',
        'widget_id',
        'customer_name',
        'customer_email',
        'customer_phone',
        'title',
        'description',
        'meeting_date',
        'start_time',
        'end_time',
        'timezone',
        'attendee_contacts',
        'assigned_contact_id',
        'custom_fields',
        'google_event_id',
        'google_meet_link',
        'google_calendar_link',
        'ai_summary',
        'status',
    );

    /**
     * Validate data before insert/update.
     *
     * @param array    $data The data to validate.
     * @param int|null $id   Optional. The record ID for updates.
     * @return true|WP_Error True if valid, WP_Error otherwise.
     */
    public function validate( $data, $id = null ) {
        $errors = new WP_Error();

        // Title is required for new meetings
        if ( null === $id && empty( $data['title'] ) ) {
            $errors->add( 'title_required', __( 'כותרת הפגישה היא שדה חובה', 'boostbot' ) );
        }

        // Meeting date is required
        if ( null === $id && empty( $data['meeting_date'] ) ) {
            $errors->add( 'date_required', __( 'תאריך הפגישה הוא שדה חובה', 'boostbot' ) );
        }

        // Start time is required
        if ( null === $id && empty( $data['start_time'] ) ) {
            $errors->add( 'time_required', __( 'שעת התחלה היא שדה חובה', 'boostbot' ) );
        }

        if ( $errors->has_errors() ) {
            return $errors;
        }

        return true;
    }

    /**
     * Sanitize data before insert/update.
     *
     * @param array $data The data to sanitize.
     * @return array The sanitized data.
     */
    public function sanitize( $data ) {
        $sanitized = array();

        if ( isset( $data['conversation_id'] ) ) {
            $sanitized['conversation_id'] = sanitize_text_field( $data['conversation_id'] );
        }
        if ( isset( $data['chatbot_id'] ) ) {
            $sanitized['chatbot_id'] = absint( $data['chatbot_id'] );
        }
        if ( isset( $data['widget_id'] ) ) {
            $sanitized['widget_id'] = absint( $data['widget_id'] );
        }
        if ( isset( $data['customer_name'] ) ) {
            $sanitized['customer_name'] = sanitize_text_field( $data['customer_name'] );
        }
        if ( isset( $data['customer_email'] ) ) {
            $sanitized['customer_email'] = sanitize_email( $data['customer_email'] );
        }
        if ( isset( $data['customer_phone'] ) ) {
            $sanitized['customer_phone'] = sanitize_text_field( $data['customer_phone'] );
        }
        if ( isset( $data['title'] ) ) {
            $sanitized['title'] = sanitize_text_field( $data['title'] );
        }
        if ( isset( $data['description'] ) ) {
            $sanitized['description'] = sanitize_textarea_field( $data['description'] );
        }
        if ( isset( $data['meeting_date'] ) ) {
            $sanitized['meeting_date'] = sanitize_text_field( $data['meeting_date'] );
        }
        if ( isset( $data['start_time'] ) ) {
            $sanitized['start_time'] = sanitize_text_field( $data['start_time'] );
        }
        if ( isset( $data['end_time'] ) ) {
            $sanitized['end_time'] = sanitize_text_field( $data['end_time'] );
        }
        if ( isset( $data['timezone'] ) ) {
            $sanitized['timezone'] = sanitize_text_field( $data['timezone'] );
        }
        if ( isset( $data['attendee_contacts'] ) ) {
            $sanitized['attendee_contacts'] = $data['attendee_contacts']; // Already JSON encoded
        }
        if ( isset( $data['assigned_contact_id'] ) ) {
            $sanitized['assigned_contact_id'] = $data['assigned_contact_id'] ? absint( $data['assigned_contact_id'] ) : null;
        }
        if ( isset( $data['custom_fields'] ) ) {
            $sanitized['custom_fields'] = $data['custom_fields']; // Already JSON encoded or string
        }
        if ( isset( $data['google_event_id'] ) ) {
            $sanitized['google_event_id'] = sanitize_text_field( $data['google_event_id'] );
        }
        if ( isset( $data['google_meet_link'] ) ) {
            $sanitized['google_meet_link'] = esc_url_raw( $data['google_meet_link'] );
        }
        if ( isset( $data['google_calendar_link'] ) ) {
            $sanitized['google_calendar_link'] = esc_url_raw( $data['google_calendar_link'] );
        }
        if ( isset( $data['status'] ) ) {
            $sanitized['status'] = sanitize_text_field( $data['status'] );
        }

        if ( isset( $data['language'] ) ) {
            $sanitized['language'] = sanitize_text_field( $data['language'] );
        }

        return $sanitized;
    }

    /**
     * Create a new meeting.
     *
     * @param array $data Meeting data.
     * @return int|false Meeting ID or false on failure.
     */
    public function create( $data ) {
        // Ensure JSON fields are encoded.
        if ( isset( $data['attendee_contacts'] ) && is_array( $data['attendee_contacts'] ) ) {
            $data['attendee_contacts'] = wp_json_encode( $data['attendee_contacts'] );
        }
        if ( isset( $data['custom_fields'] ) && is_array( $data['custom_fields'] ) ) {
            $data['custom_fields'] = wp_json_encode( $data['custom_fields'] );
        }

        return parent::create( $data );
    }

    /**
     * Get meetings by date range.
     *
     * @param string   $start_date  Start date (Y-m-d).
     * @param string   $end_date    End date (Y-m-d).
     * @param int|null $chatbot_id  Optional chatbot ID filter.
     * @return array Array of meetings.
     */
    public function get_by_date_range( string $start_date, string $end_date, ?int $chatbot_id = null, ?string $language = null ): array {
        global $wpdb;
        $table = $this->get_table_name();

        $sql    = "SELECT * FROM {$table} WHERE meeting_date BETWEEN %s AND %s"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $params = array( $start_date, $end_date );

        if ( $chatbot_id ) {
            $sql     .= ' AND chatbot_id = %d';
            $params[] = $chatbot_id;
        }

        if ( $language && 'all' !== $language ) {
            $sql     .= ' AND language = %s';
            $params[] = $language;
        }

        $sql .= ' ORDER BY meeting_date ASC, start_time ASC';

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query built with prepare() and safe table names
        return $wpdb->get_results( $wpdb->prepare( $sql, ...$params ) );
    }

    /**
     * Get today's meetings.
     *
     * @param int|null $chatbot_id Optional chatbot ID filter.
     * @return array Array of meetings.
     */
    public function get_today( ?int $chatbot_id = null ): array {
        $today = current_time( 'Y-m-d' );
        return $this->get_by_date_range( $today, $today, $chatbot_id );
    }

    /**
     * Get upcoming meetings.
     *
     * @param int      $limit      Number of meetings to return.
     * @param int|null $chatbot_id Optional chatbot ID filter.
     * @return array Array of meetings.
     */
    public function get_upcoming( int $limit = 10, ?int $chatbot_id = null ): array {
        global $wpdb;
        $table = $this->get_table_name();
        $today = current_time( 'Y-m-d' );

        $sql    = "SELECT * FROM {$table} WHERE meeting_date >= %s AND status IN ('scheduled', 'confirmed')"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $params = array( $today );

        if ( $chatbot_id ) {
            $sql     .= ' AND chatbot_id = %d';
            $params[] = $chatbot_id;
        }

        $sql     .= ' ORDER BY meeting_date ASC, start_time ASC LIMIT %d';
        $params[] = $limit;

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query built with prepare() and safe table names
        return $wpdb->get_results( $wpdb->prepare( $sql, ...$params ) );
    }

    /**
     * Get meeting with decoded JSON fields.
     *
     * @param int $id Meeting ID.
     * @return object|null Meeting object or null.
     */
    public function get_with_details( int $id ): ?object {
        $meeting = $this->get( $id );

        if ( $meeting ) {
            $meeting->attendee_contacts = json_decode( $meeting->attendee_contacts ?? '[]', true ) ?? array();
            $meeting->custom_fields     = json_decode( $meeting->custom_fields ?? '[]', true ) ?? array();
        }

        return $meeting;
    }

    /**
     * Update meeting status.
     *
     * @param int    $id     Meeting ID.
     * @param string $status New status.
     * @return bool True on success.
     */
    public function update_status( int $id, string $status ): bool {
        $valid_statuses = array( 'scheduled', 'confirmed', 'cancelled', 'completed', 'no_show' );

        if ( ! in_array( $status, $valid_statuses, true ) ) {
            return false;
        }

        return (bool) $this->update( $id, array( 'status' => $status ) );
    }

    /**
     * Mark reminder as sent.
     *
     * @param int $id Meeting ID.
     * @return bool True on success.
     */
    public function mark_reminder_sent( int $id ): bool {
        // Direct update: reminder_sent is intentionally not in $fillable, so the sanitized
        // update() would drop it and the flag would never persist (resending forever).
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return false !== $this->wpdb->update( $this->table_name, array( 'reminder_sent' => 1 ), array( 'id' => (int) $id ) );
    }

    /**
     * Mark confirmation as sent.
     *
     * @param int $id Meeting ID.
     * @return bool True on success.
     */
    public function mark_confirmation_sent( int $id ): bool {
        // Direct update: confirmation_sent is intentionally not in $fillable (see above).
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return false !== $this->wpdb->update( $this->table_name, array( 'confirmation_sent' => 1 ), array( 'id' => (int) $id ) );
    }

    /**
     * Get meetings needing reminders.
     *
     * @param int $hours_before Hours before meeting to send reminder.
     * @return array Array of meetings.
     */
    public function get_needing_reminders( int $hours_before = 24 ): array {
        global $wpdb;
        $table = $this->get_table_name();

        $now    = current_time( 'mysql' );
        $cutoff = gmdate( 'Y-m-d H:i:s', strtotime( "+{$hours_before} hours" ) );

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT * FROM {$table} 
                 WHERE CONCAT(meeting_date, ' ', start_time) BETWEEN %s AND %s
                 AND reminder_sent = 0
                 AND status IN ('scheduled', 'confirmed')",
                $now,
                $cutoff
            )
        );
    }

    /**
     * Get meetings by conversation ID.
     *
     * @param int $conversation_id Conversation ID.
     * @return array Array of meetings.
     */
    public function get_by_conversation( int $conversation_id ): array {
        global $wpdb;
        $table = $this->get_table_name();

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE conversation_id = %d ORDER BY created_at DESC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $conversation_id
            )
        );
    }

    /**
     * Check if a time slot is available.
     *
     * @param string   $date       Date (Y-m-d).
     * @param string   $start_time Start time (H:i).
     * @param string   $end_time   End time (H:i).
     * @param int|null $exclude_id Meeting ID to exclude (for updates).
     * @return bool True if available.
     */
    public function is_slot_available( string $date, string $start_time, string $end_time, ?int $exclude_id = null ): bool {
        global $wpdb;
        $table = $this->get_table_name();

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "SELECT COUNT(*) FROM {$table} 
                WHERE meeting_date = %s 
                AND status NOT IN ('cancelled') 
                AND (
                    (start_time < %s AND end_time > %s)
                    OR (start_time >= %s AND start_time < %s)
                )";

        $params = array( $date, $end_time, $start_time, $start_time, $end_time );

        if ( $exclude_id ) {
            $sql     .= ' AND id != %d';
            $params[] = $exclude_id;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query built with prepare() and safe table names
        $count = $wpdb->get_var( $wpdb->prepare( $sql, ...$params ) );

        return 0 === (int) $count;
    }

    /**
     * Get meetings assigned to a specific contact on a specific date.
     *
     * @param int    $contact_id The assigned contact ID.
     * @param string $date       The date (Y-m-d).
     * @return array Array of meetings.
     */
    public function get_by_contact_and_date( int $contact_id, string $date ): array {
        global $wpdb;
        $table = $this->get_table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE assigned_contact_id = %d AND meeting_date = %s AND status NOT IN ('cancelled', 'deleted') ORDER BY start_time ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $contact_id,
                $date
            )
        );
    }

    /**
     * Check if a time slot is available for a specific contact.
     *
     * @param int    $contact_id The assigned contact ID.
     * @param string $date       Date (Y-m-d).
     * @param string $start_time Start time (H:i).
     * @param string $end_time   End time (H:i).
     * @param int|null $exclude_id Meeting ID to exclude (for updates).
     * @return bool True if the contact is available at this time.
     */
    public function is_contact_slot_available( int $contact_id, string $date, string $start_time, string $end_time, ?int $exclude_id = null ): bool {
        global $wpdb;
        $table = $this->get_table_name();

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "SELECT COUNT(*) FROM {$table}
                WHERE assigned_contact_id = %d
                AND meeting_date = %s
                AND status NOT IN ('cancelled', 'deleted')
                AND (
                    (start_time < %s AND end_time > %s)
                    OR (start_time >= %s AND start_time < %s)
                )";

        $params = array( $contact_id, $date, $end_time, $start_time, $start_time, $end_time );

        if ( $exclude_id ) {
            $sql     .= ' AND id != %d';
            $params[] = $exclude_id;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
        $count = $wpdb->get_var( $wpdb->prepare( $sql, ...$params ) );

        return 0 === (int) $count;
    }

    /**
     * Check if a legacy meeting (assigned_contact_id IS NULL) overlaps with a time range.
     *
     * @param string $date       Date (Y-m-d).
     * @param string $start_time Start time (H:i).
     * @param string $end_time   End time (H:i).
     * @return bool True if there is an overlapping legacy meeting.
     */
    public function has_legacy_meeting_overlap( string $date, string $start_time, string $end_time ): bool {
        global $wpdb;
        $table = $this->get_table_name();

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "SELECT COUNT(*) FROM {$table}
                WHERE assigned_contact_id IS NULL
                AND meeting_date = %s
                AND status NOT IN ('cancelled', 'deleted')
                AND (
                    (start_time < %s AND end_time > %s)
                    OR (start_time >= %s AND start_time < %s)
                )";

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
        $count = $wpdb->get_var( $wpdb->prepare( $sql, $date, $end_time, $start_time, $start_time, $end_time ) );

        return (int) $count > 0;
    }

    /**
     * Get meetings on a specific date that have NO assigned_contact_id (legacy meetings).
     *
     * These are meetings created before the priority-based assignment system.
     * They must be treated as occupying ALL contacts to avoid double-booking.
     *
     * @param string $date The date (Y-m-d).
     * @return array Array of legacy meeting objects.
     */
    public function get_unassigned_meetings_for_date( string $date ): array {
        global $wpdb;
        $table = $this->get_table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE meeting_date = %s AND assigned_contact_id IS NULL AND status NOT IN ('cancelled', 'deleted') ORDER BY start_time ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $date
            )
        );
    }

    /**
     * Check if a time slot is available for a specific contact — WITH row-level locking.
     *
     * Must be called inside a START TRANSACTION block. Uses SELECT … FOR UPDATE
     * to acquire an exclusive row lock on any conflicting meetings, preventing
     * concurrent requests from double-booking the same slot.
     *
     * @param int    $contact_id The assigned contact ID.
     * @param string $date       Date (Y-m-d).
     * @param string $start_time Start time (H:i).
     * @param string $end_time   End time (H:i).
     * @return bool True if the contact is available at this time.
     */
    public function is_contact_slot_available_locked( int $contact_id, string $date, string $start_time, string $end_time ): bool {
        global $wpdb;
        $table = $this->get_table_name();

        // SELECT … FOR UPDATE acquires an exclusive lock on matching rows.
        // If no rows match, InnoDB locks the "gap" in the index, still preventing
        // a concurrent INSERT from slipping in.
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "SELECT id FROM {$table}
                WHERE assigned_contact_id = %d
                AND meeting_date = %s
                AND status NOT IN ('cancelled', 'deleted')
                AND (
                    (start_time < %s AND end_time > %s)
                    OR (start_time >= %s AND start_time < %s)
                )
                FOR UPDATE";

        $params = array( $contact_id, $date, $end_time, $start_time, $start_time, $end_time );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
        $rows = $wpdb->get_results( $wpdb->prepare( $sql, ...$params ) );

        return empty( $rows );
    }

    /**
     * Get statistics for a date range.
     *
     * @param string $start_date Start date.
     * @param string $end_date   End date.
     * @return array Statistics.
     */
    public function get_stats( string $start_date, string $end_date, ?string $language = null ): array {
        global $wpdb;
        $table = $this->get_table_name();

        $where  = 'meeting_date BETWEEN %s AND %s';
        $params = array( $start_date, $end_date );

        if ( $language && 'all' !== $language ) {
            $where   .= ' AND language = %s';
            $params[] = $language;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $total = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE {$where}", ...$params ) );

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $by_status = $wpdb->get_results(
            $wpdb->prepare( "SELECT status, COUNT(*) as count FROM {$table} WHERE {$where} GROUP BY status", ...$params ),
            OBJECT_K
        );

        return array(
            'total'     => (int) $total,
            'scheduled' => (int) ( $by_status['scheduled']->count ?? 0 ),
            'confirmed' => (int) ( $by_status['confirmed']->count ?? 0 ),
            'completed' => (int) ( $by_status['completed']->count ?? 0 ),
            'cancelled' => (int) ( $by_status['cancelled']->count ?? 0 ),
            'no_show'   => (int) ( $by_status['no_show']->count ?? 0 ),
        );
    }

    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * @param string $identifier Raw identifier.
     * @return string Identifier with disallowed characters removed.
     */
    private static function sanitize_identifier( $identifier ) {
        return preg_replace( '/[^A-Za-z0-9_]/', '', (string) $identifier );
    }
}


