<?php
/**
 * Lead Model for database operations.
 *
 * @package BoostBot
 * @since   1.0.0
 */

// Prevent direct access
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * Class BoostBot_Lead_Model
 *
 * Handles CRUD operations for the leads table.
 *
 * @since 1.0.0
 */
class BoostBot_Lead_Model extends BoostBot_Base_Model {

    /**
     * Valid lead statuses.
     *
     * @var array
     */
    const STATUSES = array( 'new', 'contacted', 'qualified', 'converted', 'lost' );

    /**
     * Constructor.
     */
    public function __construct() {
        parent::__construct();

        $this->table_name = self::sanitize_identifier( $this->wpdb->prefix . 'boostbot_leads' );

        $this->fillable = array(
            'language',
            'conversation_id',
            'visitor_id',
            'name',
            'email',
            'phone',
            'company',
            'lead_score',
            'lead_status',
            'source_page',
            'referrer',
            'utm_data',
            'form_data',
            'conversation_summary',
            'metadata',
        );

        $this->column_types = array(
            'id'           => 'bigint',
            'lead_score'   => 'int',
        );
    }

    /**
     * Get lead by email.
     *
     * @param string $email The email address.
     * @return object|null The lead or null if not found.
     */
    public function get_by_email( $email ) {
        $email = sanitize_email( $email );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $this->wpdb->get_row(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT * FROM {$this->table_name} WHERE email = %s LIMIT 1",
                $email
            )
        );
    }

    /**
     * Get lead by visitor ID.
     *
     * @param string $visitor_id The visitor ID.
     * @return object|null The lead or null if not found.
     */
    public function get_by_visitor_id( $visitor_id ) {
        $visitor_id = sanitize_text_field( $visitor_id );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $this->wpdb->get_row(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT * FROM {$this->table_name} WHERE visitor_id = %s ORDER BY created_at DESC LIMIT 1",
                $visitor_id
            )
        );
    }

    /**
     * Get lead by conversation ID.
     *
     * @param string $conversation_id The conversation ID.
     * @return object|null The lead or null if not found.
     */
    public function get_by_conversation_id( $conversation_id ) {
        $conversation_id = sanitize_text_field( $conversation_id );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $this->wpdb->get_row(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT * FROM {$this->table_name} WHERE conversation_id = %s ORDER BY created_at DESC LIMIT 1",
                $conversation_id
            )
        );
    }

    /**
     * Get a lead by phone number (most recent).
     *
     * @since 1.9.0
     * @param string $phone The phone number.
     * @return object|null The lead or null if not found.
     */
    public function get_by_phone( $phone ) {
        $phone = sanitize_text_field( $phone );

        if ( empty( $phone ) ) {
            return null;
        }

        // Normalize: remove spaces, dashes, dots for flexible matching
        $phone_clean = preg_replace( '/[\s\-\.]/', '', $phone );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $this->wpdb->get_row(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT * FROM {$this->table_name} WHERE REPLACE(REPLACE(REPLACE(phone, ' ', ''), '-', ''), '.', '') = %s ORDER BY created_at DESC LIMIT 1",
                $phone_clean
            )
        );
    }

    /**
     * Get leads by status.
     *
     * @param string $status The lead status.
     * @param int    $limit  Optional. Maximum number of results. Default 50.
     * @return array Array of leads.
     */
    public function get_by_status( $status, $limit = 50 ) {
        if ( ! in_array( $status, self::STATUSES, true ) ) {
            return array();
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $this->wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT * FROM {$this->table_name} WHERE lead_status = %s ORDER BY created_at DESC LIMIT %d",
                $status,
                absint( $limit )
            )
        );
    }

    /**
     * Get recent leads.
     *
     * @param int $days  Optional. Number of days back. Default 7.
     * @param int $limit Optional. Maximum number of results. Default 100.
     * @return array Array of leads.
     */
    public function get_recent( $days = 7, $limit = 100 ) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $this->wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT * FROM {$this->table_name} 
                 WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY) 
                 ORDER BY created_at DESC 
                 LIMIT %d",
                absint( $days ),
                absint( $limit )
            )
        );
    }

    /**
     * Update lead status.
     *
     * @param int    $id     The lead ID.
     * @param string $status The new status.
     * @return bool True on success, false on failure.
     */
    public function update_status( $id, $status ) {
        if ( ! in_array( $status, self::STATUSES, true ) ) {
            return false;
        }

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

    /**
     * Update lead score.
     *
     * @param int $id    The lead ID.
     * @param int $score The new score (0-100).
     * @return bool True on success, false on failure.
     */
    public function update_score( $id, $score ) {
        $score = max( 0, min( 100, absint( $score ) ) );

        return $this->update( $id, array( 'lead_score' => $score ) );
    }

    /**
     * Calculate lead score based on data.
     *
     * @param array $lead_data The lead data.
     * @return int The calculated score (0-100).
     */
    public function calculate_score( $lead_data ) {
        $score = 0;

        // Email provided: +20 points
        if ( ! empty( $lead_data['email'] ) && is_email( $lead_data['email'] ) ) {
            $score += 20;
        }

        // Phone provided: +25 points
        if ( ! empty( $lead_data['phone'] ) ) {
            $score += 25;
        }

        // Name provided: +10 points
        if ( ! empty( $lead_data['name'] ) ) {
            $score += 10;
        }

        // Company provided: +15 points
        if ( ! empty( $lead_data['company'] ) ) {
            $score += 15;
        }

        // Form data (completed form): +30 points
        if ( ! empty( $lead_data['form_data'] ) ) {
            $form_data = is_string( $lead_data['form_data'] ) 
                ? json_decode( $lead_data['form_data'], true ) 
                : $lead_data['form_data'];

            if ( is_array( $form_data ) && count( $form_data ) > 0 ) {
                $score += 30;
            }
        }

        // Cap at 100
        return min( $score, 100 );
    }

    /**
     * Search leads.
     *
     * @param string $search_term The search term.
     * @param int    $limit       Optional. Maximum number of results. Default 50.
     * @return array Array of leads.
     */
    public function search( $search_term, $limit = 50 ) {
        $search_term = '%' . $this->wpdb->esc_like( sanitize_text_field( $search_term ) ) . '%';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $this->wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT * FROM {$this->table_name} 
                 WHERE name LIKE %s 
                    OR email LIKE %s 
                    OR phone LIKE %s 
                    OR company LIKE %s 
                 ORDER BY created_at DESC 
                 LIMIT %d",
                $search_term,
                $search_term,
                $search_term,
                $search_term,
                absint( $limit )
            )
        );
    }

    /**
     * Get leads count by status.
     *
     * @return array Associative array of status => count.
     */
    public function get_counts_by_status() {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $results = $this->wpdb->get_results(
            "SELECT lead_status, COUNT(*) as count FROM {$this->table_name} GROUP BY lead_status", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            OBJECT_K
        );

        $counts = array();
        foreach ( self::STATUSES as $status ) {
            $counts[ $status ] = isset( $results[ $status ] ) ? (int) $results[ $status ]->count : 0;
        }

        return $counts;
    }

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

        // Validate email if provided
        if ( isset( $data['email'] ) && ! empty( $data['email'] ) ) {
            if ( ! is_email( $data['email'] ) ) {
                $errors->add( 'invalid_email', __( 'Invalid email address', 'boostbot' ) );
            }
        }

        // Validate lead_status if provided
        if ( isset( $data['lead_status'] ) && ! empty( $data['lead_status'] ) ) {
            if ( ! in_array( $data['lead_status'], self::STATUSES, true ) ) {
                $errors->add( 'invalid_status', __( 'Invalid lead status', 'boostbot' ) );
            }
        }

        // Validate lead_score if provided
        if ( isset( $data['lead_score'] ) ) {
            $score = absint( $data['lead_score'] );
            if ( $score < 0 || $score > 100 ) {
                $errors->add( 'invalid_score', __( 'Lead score must be between 0 and 100', 'boostbot' ) );
            }
        }

        // Required fields for new leads
        // Note: conversation_id is optional — a lead can be submitted before a
        // conversation starts (e.g. the first interaction is a form widget).
        // The lead is still valuable and should be saved.
        if ( null === $id ) {
            if ( empty( $data['visitor_id'] ) ) {
                $errors->add( 'missing_visitor_id', __( 'Visitor ID is required', 'boostbot' ) );
            }
        }

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

        return true;
    }

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

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

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

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

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

        if ( isset( $data['email'] ) ) {
            $sanitized['email'] = sanitize_email( $data['email'] );
        }

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

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

        if ( isset( $data['lead_score'] ) ) {
            $sanitized['lead_score'] = max( 0, min( 100, absint( $data['lead_score'] ) ) );
        }

        if ( isset( $data['lead_status'] ) ) {
            $status = sanitize_text_field( $data['lead_status'] );
            $sanitized['lead_status'] = in_array( $status, self::STATUSES, true ) ? $status : 'new';
        }

        if ( isset( $data['source_page'] ) ) {
            $sanitized['source_page'] = esc_url_raw( $data['source_page'] );
        }

        if ( isset( $data['referrer'] ) ) {
            $sanitized['referrer'] = esc_url_raw( $data['referrer'] );
        }

        if ( isset( $data['utm_data'] ) ) {
            $sanitized['utm_data'] = is_string( $data['utm_data'] ) 
                ? $data['utm_data'] 
                : wp_json_encode( $data['utm_data'] );
        }

        if ( isset( $data['form_data'] ) ) {
            $sanitized['form_data'] = is_string( $data['form_data'] ) 
                ? $data['form_data'] 
                : wp_json_encode( $data['form_data'] );
        }

        if ( isset( $data['conversation_summary'] ) ) {
            $sanitized['conversation_summary'] = sanitize_textarea_field( $data['conversation_summary'] );
        }

        if ( isset( $data['metadata'] ) ) {
            $sanitized['metadata'] = is_string( $data['metadata'] ) 
                ? $data['metadata'] 
                : wp_json_encode( $data['metadata'] );
        }

        return $sanitized;
    }
}



