<?php
/**
 * Client Model
 *
 * @package Freelance_Studio
 */

class FS_Client {

    /**
     * Create a new client
     *
     * @param array $data Client data
     * @return int|false Insert ID or false on failure
     */
    public static function create($data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_clients';
        
        // As of v3.5.0, company_name is the primary identifier
        // name, email, phone are deprecated but kept for backward compatibility
        $insert_data = array(
            'company_name' => isset($data['company_name']) ? sanitize_text_field($data['company_name']) : null,
            'name' => isset($data['name']) ? sanitize_text_field($data['name']) : '', // Deprecated, kept for backward compat
            'email' => isset($data['email']) ? sanitize_email($data['email']) : '', // Deprecated
            'phone' => isset($data['phone']) ? sanitize_text_field($data['phone']) : '', // Deprecated
            'website' => isset($data['website']) ? esc_url_raw($data['website']) : '',
            'address' => isset($data['address']) ? sanitize_textarea_field($data['address']) : '',
            'address_2' => isset($data['address_2']) ? sanitize_text_field($data['address_2']) : '',
            'city' => isset($data['city']) ? sanitize_text_field($data['city']) : '',
            'state' => isset($data['state']) ? sanitize_text_field($data['state']) : '',
            'zip' => isset($data['zip']) ? sanitize_text_field($data['zip']) : '',
            'country' => isset($data['country']) ? sanitize_text_field($data['country']) : '',
            'notes' => isset($data['notes']) ? sanitize_textarea_field($data['notes']) : '',
            'logo_id' => isset($data['logo_id']) && $data['logo_id'] ? intval($data['logo_id']) : null,
            'logo_url' => isset($data['logo_url']) && $data['logo_url'] ? esc_url_raw($data['logo_url']) : null,
            'default_hourly_rate' => isset($data['default_hourly_rate']) ? floatval($data['default_hourly_rate']) : null,
            'default_payment_terms' => isset($data['default_payment_terms']) ? intval($data['default_payment_terms']) : 30,
            'portal_enabled' => isset($data['portal_enabled']) ? intval($data['portal_enabled']) : 1,
            'is_internal' => isset($data['is_internal']) ? intval($data['is_internal']) : 0,
            'status' => isset($data['status']) ? sanitize_text_field($data['status']) : 'active',
        );
        
        $result = $wpdb->insert(
            $table,
            $insert_data,
            array('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%f', '%d', '%d', '%d', '%s')
        );
        
        if ($result === false) {
            error_log('FS_Client::create failed: ' . $wpdb->last_error);
            error_log('  Last query: ' . $wpdb->last_query);
            return false;
        }

        $client_id = $wpdb->insert_id;

        // Generate portal token
        self::generate_portal_token($client_id);
        
        return $client_id;
    }

    /**
     * Get all clients
     *
     * @param array $args Query arguments
     * @return array
     */
    public static function get_all($args = array()) {
        global $wpdb;
        
        $defaults = array(
            'status' => null,
            'orderby' => 'name',
            'order' => 'ASC',
            'limit' => null,
            'offset' => 0
        );
        
        $args = wp_parse_args($args, $defaults);
        $table = $wpdb->prefix . 'fs_clients';
        
        $where = array('1=1');
        
        if ($args['status']) {
            $where[] = $wpdb->prepare('status = %s', $args['status']);
        }
        
        $where_clause = implode(' AND ', $where);
        
        // Handle special sorting by display name (company_name if exists, otherwise name)
        if ($args['orderby'] === 'display_name') {
            $order_dir = strtoupper($args['order']) === 'DESC' ? 'DESC' : 'ASC';
            $order_clause = "COALESCE(NULLIF(company_name, ''), name) {$order_dir}";
        } else {
            $order_clause = sanitize_sql_orderby($args['orderby'] . ' ' . $args['order']);
        }
        
        // Fallback if sanitize returns empty
        if (empty($order_clause)) {
            $order_clause = 'name ASC';
        }
        
        $sql = "SELECT * FROM {$table} WHERE {$where_clause} ORDER BY {$order_clause}";
        
        if ($args['limit']) {
            $sql .= $wpdb->prepare(' LIMIT %d OFFSET %d', $args['limit'], $args['offset']);
        }
        
        return $wpdb->get_results($sql); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
    }

    /**
     * Get a single client by ID
     *
     * @param int $id Client ID
     * @return object|null
     */
    public static function get($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_clients';
        
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE id = %d",
            $id
        ));
    }

    /**
     * Get client by portal token
     *
     * @param string $token Portal token
     * @return object|null
     */
    public static function get_by_portal_token($token) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_clients';
        
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE portal_token = %s AND portal_enabled = 1",
            $token
        ));
    }

    /**
     * Update a client
     *
     * @param int   $id   Client ID
     * @param array $data Client data
     * @return int|false Number of rows updated or false on failure
     */
    public static function update($id, $data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_clients';
        
        // As of v3.5.0, company_name is the primary identifier
        // name, email, phone are deprecated but kept for backward compatibility
        $update_data = array(
            'company_name' => isset($data['company_name']) ? sanitize_text_field($data['company_name']) : null,
            'name' => isset($data['name']) ? sanitize_text_field($data['name']) : '', // Deprecated
            'email' => isset($data['email']) ? sanitize_email($data['email']) : '', // Deprecated
            'phone' => isset($data['phone']) ? sanitize_text_field($data['phone']) : '', // Deprecated
            'website' => isset($data['website']) ? esc_url_raw($data['website']) : '',
            'address' => isset($data['address']) ? sanitize_textarea_field($data['address']) : '',
            'address_2' => isset($data['address_2']) ? sanitize_text_field($data['address_2']) : '',
            'city' => isset($data['city']) ? sanitize_text_field($data['city']) : '',
            'state' => isset($data['state']) ? sanitize_text_field($data['state']) : '',
            'zip' => isset($data['zip']) ? sanitize_text_field($data['zip']) : '',
            'country' => isset($data['country']) ? sanitize_text_field($data['country']) : '',
            'notes' => isset($data['notes']) ? sanitize_textarea_field($data['notes']) : '',
            'logo_id' => isset($data['logo_id']) && $data['logo_id'] ? intval($data['logo_id']) : null,
            'logo_url' => isset($data['logo_url']) && $data['logo_url'] ? esc_url_raw($data['logo_url']) : null,
            'default_hourly_rate' => isset($data['default_hourly_rate']) ? floatval($data['default_hourly_rate']) : null,
            'default_payment_terms' => isset($data['default_payment_terms']) ? intval($data['default_payment_terms']) : 30,
            'portal_enabled' => isset($data['portal_enabled']) ? intval($data['portal_enabled']) : 1,
            'is_internal' => isset($data['is_internal']) ? intval($data['is_internal']) : 0,
            'status' => isset($data['status']) ? sanitize_text_field($data['status']) : 'active',
        );
        
        // Debug: Log what we're about to save
        error_log('Freelance Studio - Client Model Update Debug:');
        error_log('  Client ID: ' . $id);
        error_log('  logo_id to save: ' . ($update_data['logo_id'] ?? 'null'));
        error_log('  logo_url to save: ' . ($update_data['logo_url'] ?? 'null'));
        
        $result = $wpdb->update(
            $table,
            $update_data,
            array('id' => $id),
            array('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%f', '%d', '%d', '%d', '%s'),
            array('%d')
        );
        
        if ($result === false) {
            error_log('FS_Client::update failed: ' . $wpdb->last_error);
        } else {
            error_log('  Update result: ' . ($result === 0 ? '0 rows affected' : $result . ' rows affected'));
            // Log the actual query for debugging
            error_log('  Last query: ' . $wpdb->last_query);
        }
        
        return $result;
    }

    /**
     * Delete a client
     *
     * @param int $id Client ID
     * @return int|false Number of rows deleted or false on failure
     */
    public static function delete($id) {
        global $wpdb;
        
        // First delete related contacts
        $contacts_table = $wpdb->prefix . 'fs_client_contacts';
        $wpdb->delete($contacts_table, array('client_id' => $id), array('%d'));
        
        // Delete the client
        $table = $wpdb->prefix . 'fs_clients';
        return $wpdb->delete($table, array('id' => $id), array('%d'));
    }

    /**
     * Count total clients
     *
     * @param string $status Optional status filter
     * @param bool $exclude_internal Whether to exclude internal clients
     * @return int
     */
    public static function count($status = null, $exclude_internal = false) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_clients';
        $where = array('1=1');
        
        if ($status) {
            $where[] = $wpdb->prepare("status = %s", $status);
        }
        
        if ($exclude_internal) {
            $where[] = "(is_internal = 0 OR is_internal IS NULL)";
        }
        
        $where_clause = implode(' AND ', $where);
        
        return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE {$where_clause}");
    }

    /**
     * Generate a unique portal token for a client
     *
     * @param int $client_id Client ID
     * @return string The generated token
     */
    public static function generate_portal_token($client_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_clients';
        $token = wp_generate_password(32, false);
        
        $wpdb->update(
            $table,
            array('portal_token' => $token),
            array('id' => $client_id),
            array('%s'),
            array('%d')
        );
        
        return $token;
    }

    /**
     * Get client with related data (contacts, projects, invoices)
     *
     * @param int $id Client ID
     * @return array
     */
    public static function get_with_relations($id) {
        $client = self::get($id);
        
        if (!$client) {
            return null;
        }
        
        return array(
            'client' => $client,
            'contacts' => FS_Client_Contact::get_by_client($id),
            'projects' => FS_Project::get_by_client($id),
            'invoices' => FS_Invoice::get_by_client($id),
            'stats' => self::get_client_stats($id)
        );
    }

    /**
     * Get client statistics
     *
     * @param int $id Client ID
     * @return array
     */
    public static function get_client_stats($id) {
        global $wpdb;
        
        $projects_table = $wpdb->prefix . 'fs_projects';
        $invoices_table = $wpdb->prefix . 'fs_invoices';
        
        // Count projects
        $total_projects = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$projects_table} WHERE client_id = %d",
            $id
        ));
        
        $active_projects = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$projects_table} WHERE client_id = %d AND status IN ('pending', 'active')",
            $id
        ));
        
        // Invoice totals
        $total_invoiced = (float) $wpdb->get_var($wpdb->prepare(
            "SELECT SUM(total) FROM {$invoices_table} WHERE client_id = %d",
            $id
        ));
        
        $total_paid = (float) $wpdb->get_var($wpdb->prepare(
            "SELECT SUM(amount_paid) FROM {$invoices_table} WHERE client_id = %d",
            $id
        ));
        
        $outstanding = (float) $wpdb->get_var($wpdb->prepare(
            "SELECT SUM(balance_due) FROM {$invoices_table} WHERE client_id = %d AND status NOT IN ('paid', 'cancelled')",
            $id
        ));
        
        return array(
            'total_projects' => $total_projects,
            'active_projects' => $active_projects,
            'total_invoiced' => $total_invoiced ?: 0,
            'total_paid' => $total_paid ?: 0,
            'outstanding' => $outstanding ?: 0
        );
    }

    /**
     * Search clients by name, company, or email
     *
     * @param string $search Search term
     * @return array
     */
    public static function search($search) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_clients';
        $search_term = '%' . $wpdb->esc_like($search) . '%';
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table} 
             WHERE name LIKE %s 
                OR company_name LIKE %s 
                OR email LIKE %s 
             ORDER BY name ASC",
            $search_term,
            $search_term,
            $search_term
        ));
    }

    /**
     * Get clients for dropdown/select
     *
     * @return array
     */
    public static function get_for_dropdown() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_clients';
        
        return $wpdb->get_results(
            "SELECT id, name, company_name FROM {$table} WHERE status = 'active' ORDER BY name ASC"
        );
    }

    /**
     * Get portal URL for a client
     *
     * @param int $client_id Client ID
     * @return string|false Portal URL or false if not available
     */
    public static function get_portal_url($client_id) {
        $client = self::get($client_id);
        
        if (!$client || !$client->portal_enabled) {
            return false;
        }
        
        // Generate token if not exists
        if (empty($client->portal_token)) {
            $token = self::generate_portal_token($client_id);
        } else {
            $token = $client->portal_token;
        }
        
        return home_url('?fs_portal=' . $token);
    }

    /**
     * Regenerate portal token for a client
     *
     * @param int $client_id Client ID
     * @return string The new token
     */
    public static function regenerate_portal_token($client_id) {
        return self::generate_portal_token($client_id);
    }

    /**
     * Toggle portal access for a client
     *
     * @param int $client_id Client ID
     * @param bool $enabled Whether portal should be enabled
     * @return bool Success
     */
    public static function set_portal_enabled($client_id, $enabled) {
        global $wpdb;
        
        return $wpdb->update(
            $wpdb->prefix . 'fs_clients',
            array('portal_enabled' => $enabled ? 1 : 0),
            array('id' => $client_id)
        ) !== false;
    }

    /**
     * Get client logo URL
     *
     * @param int $client_id Client ID
     * @param string $size Image size (thumbnail, medium, large, full)
     * @return string|false Logo URL or false if not set
     */
    public static function get_logo_url($client_id, $size = 'thumbnail') {
        $client = self::get($client_id);
        
        if (!$client) {
            return false;
        }
        
        // Check logo_url first (external URL)
        if (!empty($client->logo_url)) {
            return $client->logo_url;
        }
        
        // Fall back to logo_id (Media Library attachment)
        if (!empty($client->logo_id)) {
            $image = wp_get_attachment_image_src($client->logo_id, $size);
            return $image ? $image[0] : false;
        }
        
        return false;
    }

    /**
     * Update client logo
     *
     * @param int $client_id Client ID
     * @param int $logo_id WordPress attachment ID
     * @return bool Success
     */
    public static function update_logo($client_id, $logo_id) {
        global $wpdb;
        
        return $wpdb->update(
            $wpdb->prefix . 'fs_clients',
            array('logo_id' => $logo_id ? intval($logo_id) : null),
            array('id' => $client_id),
            array('%d'),
            array('%d')
        ) !== false;
    }
    
    /**
     * Get the display name for a client
     * Uses company_name, falls back to primary contact name, then legacy name field
     *
     * @param object|int $client Client object or ID
     * @return string Display name
     */
    public static function get_display_name($client) {
        if (is_numeric($client)) {
            $client = self::get($client);
        }
        
        if (!$client) {
            return '';
        }
        
        // Prefer company_name
        if (!empty($client->company_name)) {
            return $client->company_name;
        }
        
        // Fall back to primary contact name from contacts table
        $primary_contact = FS_Client_Contact::get_primary($client->id);
        if ($primary_contact && !empty($primary_contact->name)) {
            return $primary_contact->name;
        }
        
        // Fall back to legacy name field (for backward compatibility)
        if (!empty($client->name)) {
            return $client->name;
        }
        
        return __('Unnamed Client', 'freelance-studio');
    }
    
    /**
     * Get the primary contact for a client
     * First checks contacts table, then falls back to legacy fields on client record
     *
     * @param object|int $client Client object or ID
     * @return object|null Contact object with name, email, phone properties
     */
    public static function get_primary_contact($client) {
        if (is_numeric($client)) {
            $client = self::get($client);
        }
        
        if (!$client) {
            return null;
        }
        
        // Check contacts table first
        $primary_contact = FS_Client_Contact::get_primary($client->id);
        if ($primary_contact) {
            return $primary_contact;
        }
        
        // Fall back to legacy fields on client record (for backward compatibility)
        if (!empty($client->name) || !empty($client->email) || !empty($client->phone)) {
            return (object) array(
                'id' => null,
                'client_id' => $client->id,
                'name' => $client->name ?? '',
                'email' => $client->email ?? '',
                'phone' => $client->phone ?? '',
                'role' => '',
                'is_primary' => 1,
                'is_billing_contact' => 1,
                'is_project_contact' => 0,
            );
        }
        
        return null;
    }
    
    /**
     * Get client info for documents (invoices, contracts, proposals)
     * Returns an object with display_name and primary_contact
     *
     * @param object|int $client Client object or ID
     * @return object Object with display_name and contact properties
     */
    public static function get_document_info($client) {
        if (is_numeric($client)) {
            $client = self::get($client);
        }
        
        if (!$client) {
            return null;
        }
        
        $primary_contact = self::get_primary_contact($client);
        
        return (object) array(
            'display_name' => self::get_display_name($client),
            'company_name' => $client->company_name ?? '',
            'contact_name' => $primary_contact ? $primary_contact->name : '',
            'contact_email' => $primary_contact ? $primary_contact->email : '',
            'contact_phone' => $primary_contact ? $primary_contact->phone : '',
            'address' => $client->address ?? '',
            'address_2' => $client->address_2 ?? '',
            'city' => $client->city ?? '',
            'state' => $client->state ?? '',
            'zip' => $client->zip ?? '',
            'country' => $client->country ?? '',
        );
    }

    /**
     * Get the internal client record
     *
     * @return object|null Internal client or null if not found
     */
    public static function get_internal_client() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_clients';
        
        // Check if is_internal column exists (migration may not have run yet)
        $column_exists = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'is_internal'",
            DB_NAME,
            $table
        ));
        
        if (empty($column_exists)) {
            return null;
        }
        
        return $wpdb->get_row(
            "SELECT * FROM {$table} WHERE is_internal = 1 LIMIT 1"
        );
    }

    /**
     * Create the internal client from business settings
     *
     * @return int|false Client ID or false on failure
     */
    public static function create_internal_client() {
        // Check if one already exists
        $existing = self::get_internal_client();
        if ($existing) {
            return $existing->id;
        }
        
        // Get business settings
        $business_name = get_option('fs_business_name', '');
        $business_email = get_option('fs_business_email', '');
        $business_phone = get_option('fs_business_phone', '');
        $business_address = get_option('fs_business_address', '');
        $business_city = get_option('fs_business_city', '');
        $business_state = get_option('fs_business_state', '');
        $business_zip = get_option('fs_business_zip', '');
        $business_country = get_option('fs_business_country', '');
        $business_logo_id = get_option('fs_business_logo_id', '');
        $business_logo_url = get_option('fs_business_logo', '');
        
        // Create client name
        $client_name = $business_name ? $business_name . ' - Internal' : 'Internal Projects';
        
        $client_id = self::create(array(
            'company_name' => $client_name,
            'name' => '',
            'email' => $business_email,
            'phone' => $business_phone,
            'address' => $business_address,
            'city' => $business_city,
            'state' => $business_state,
            'zip' => $business_zip,
            'country' => $business_country,
            'logo_id' => $business_logo_id ? intval($business_logo_id) : null,
            'logo_url' => $business_logo_url ?: null,
            'notes' => __('Auto-created internal client for tracking business admin tasks, internal projects, and non-client work.', 'freelance-studio'),
            'portal_enabled' => 0,
            'is_internal' => 1,
            'status' => 'active',
        ));
        
        return $client_id;
    }
}
