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

class FS_Proposal {

    /**
     * Proposal status constants
     */
    const STATUS_DRAFT = 'draft';
    const STATUS_SENT = 'sent';
    const STATUS_VIEWED = 'viewed';
    const STATUS_REVISION_REQUESTED = 'revision_requested';
    const STATUS_REVISED = 'revised';
    const STATUS_APPROVED = 'approved';
    const STATUS_DISMISSED = 'dismissed';
    const STATUS_REJECTED = 'rejected';
    const STATUS_EXPIRED = 'expired';
    const STATUS_CONVERTED = 'converted';

    /**
     * Create a new proposal
     *
     * @param array $data Proposal data
     * @return int|false Insert ID or false on failure
     */
    public static function create($data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_proposals';
        
        // Generate view token
        $view_token = wp_generate_password(32, false);
        
        // Generate proposal number if not provided
        if (empty($data['proposal_number'])) {
            $data['proposal_number'] = self::generate_proposal_number();
        }
        
        $result = $wpdb->insert(
            $table,
            array(
                'client_id' => intval($data['client_id']),
                'proposal_number' => sanitize_text_field($data['proposal_number']),
                'title' => sanitize_text_field($data['title']),
                'status' => isset($data['status']) ? sanitize_text_field($data['status']) : self::STATUS_DRAFT,
                'services' => isset($data['services']) ? wp_json_encode($data['services']) : '[]',
                'custom_sections' => isset($data['custom_sections']) ? wp_json_encode($data['custom_sections']) : '[]',
                'pricing_type' => isset($data['pricing_type']) ? sanitize_text_field($data['pricing_type']) : 'fixed',
                'hourly_rate' => isset($data['hourly_rate']) ? floatval($data['hourly_rate']) : null,
                'estimated_hours' => isset($data['estimated_hours']) ? floatval($data['estimated_hours']) : null,
                'total_amount' => isset($data['total_amount']) ? floatval($data['total_amount']) : 0,
                'deposit_required' => isset($data['deposit_required']) ? intval($data['deposit_required']) : 0,
                'deposit_type' => isset($data['deposit_type']) ? sanitize_text_field($data['deposit_type']) : 'percentage',
                'deposit_value' => isset($data['deposit_value']) ? floatval($data['deposit_value']) : 0,
                'billing_frequency' => isset($data['billing_frequency']) ? sanitize_text_field($data['billing_frequency']) : 'once',
                'first_invoice_date' => !empty($data['first_invoice_date']) ? sanitize_text_field($data['first_invoice_date']) : null,
                'start_date' => !empty($data['start_date']) ? sanitize_text_field($data['start_date']) : null,
                'valid_until' => !empty($data['valid_until']) ? sanitize_text_field($data['valid_until']) : null,
                'notes' => isset($data['notes']) ? sanitize_textarea_field($data['notes']) : '',
                'internal_notes' => isset($data['internal_notes']) ? sanitize_textarea_field($data['internal_notes']) : '',
                'view_token' => $view_token,
            )
        );
        
        if ($result === false) {
            error_log('FS_Proposal::create failed: ' . $wpdb->last_error);
            return false;
        }
        
        return $wpdb->insert_id;
    }

    /**
     * Get a proposal by ID
     *
     * @param int $id Proposal ID
     * @return object|null
     */
    public static function get($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_proposals';
        
        $proposal = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE id = %d",
            $id
        ));
        
        if ($proposal) {
            $services_json = $proposal->services ?? '';
            $proposal->services = !empty($services_json) ? json_decode($services_json, true) : array();
            if (!is_array($proposal->services)) $proposal->services = array();
            
            $custom_sections_json = $proposal->custom_sections ?? '';
            $proposal->custom_sections = !empty($custom_sections_json) ? json_decode($custom_sections_json, true) : array();
            if (!is_array($proposal->custom_sections)) $proposal->custom_sections = array();
        }
        
        return $proposal;
    }

    /**
     * Get a proposal by view token
     *
     * @param string $token View token
     * @return object|null
     */
    public static function get_by_token($token) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_proposals';
        
        $proposal = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE view_token = %s",
            $token
        ));
        
        if ($proposal) {
            $services_json = $proposal->services ?? '';
            $proposal->services = !empty($services_json) ? json_decode($services_json, true) : array();
            if (!is_array($proposal->services)) $proposal->services = array();
            
            $custom_sections_json = $proposal->custom_sections ?? '';
            $proposal->custom_sections = !empty($custom_sections_json) ? json_decode($custom_sections_json, true) : array();
            if (!is_array($proposal->custom_sections)) $proposal->custom_sections = array();
        }
        
        return $proposal;
    }

    /**
     * Get all proposals
     *
     * @param array $args Query arguments
     * @return array
     */
    public static function get_all($args = array()) {
        global $wpdb;
        
        $defaults = array(
            'client_id' => null,
            'status' => null,
            'archived' => null,
            'orderby' => 'created_at',
            'order' => 'DESC',
            'limit' => 100,
            'offset' => 0,
        );
        
        $args = wp_parse_args($args, $defaults);
        
        $table = $wpdb->prefix . 'fs_proposals';
        $clients_table = $wpdb->prefix . 'fs_clients';
        
        $where = array('1=1');
        $values = array();
        
        if ($args['archived'] !== null) {
            if ($args['archived']) {
                $where[] = 'p.archived = 1';
            } else {
                $where[] = '(p.archived = 0 OR p.archived IS NULL)';
            }
        }
        
        if ($args['client_id']) {
            $where[] = 'p.client_id = %d';
            $values[] = $args['client_id'];
        }
        
        if ($args['status']) {
            if (is_array($args['status'])) {
                $placeholders = implode(', ', array_fill(0, count($args['status']), '%s'));
                $where[] = "p.status IN ($placeholders)";
                $values = array_merge($values, $args['status']);
            } else {
                $where[] = 'p.status = %s';
                $values[] = $args['status'];
            }
        }
        
        $where_clause = implode(' AND ', $where);
        $orderby = sanitize_sql_orderby($args['orderby'] . ' ' . $args['order']) ?: 'created_at DESC';
        
        $sql = "SELECT p.*, 
                       c.name as client_name, 
                       c.company_name as client_company,
                       c.email as client_email
                FROM {$table} p
                LEFT JOIN {$clients_table} c ON p.client_id = c.id
                WHERE {$where_clause}
                ORDER BY p.{$orderby}
                LIMIT %d OFFSET %d";
        
        $values[] = $args['limit'];
        $values[] = $args['offset'];
        
        if (!empty($values)) {
            $sql = $wpdb->prepare($sql, $values); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
        }
        
        $results = $wpdb->get_results($sql); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
        
        foreach ($results as &$proposal) {
            $proposal->services = json_decode($proposal->services ?? '', true) ?: array();
        }
        
        return $results;
    }

    /**
     * Get proposals by client
     *
     * @param int $client_id Client ID
     * @return array
     */
    public static function get_by_client($client_id) {
        return self::get_all(array('client_id' => $client_id));
    }

    /**
     * Update a proposal
     *
     * @param int   $id   Proposal ID
     * @param array $data Proposal data
     * @return int|false
     */
    public static function update($id, $data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_proposals';
        
        $update_data = array();
        
        // Only update fields that are provided
        if (isset($data['client_id'])) {
            $update_data['client_id'] = intval($data['client_id']);
        }
        if (isset($data['title'])) {
            $update_data['title'] = sanitize_text_field($data['title']);
        }
        if (isset($data['services'])) {
            $update_data['services'] = wp_json_encode($data['services']);
        }
        if (isset($data['custom_sections'])) {
            $update_data['custom_sections'] = wp_json_encode($data['custom_sections']);
        }
        if (isset($data['pricing_type'])) {
            $update_data['pricing_type'] = sanitize_text_field($data['pricing_type']);
        }
        if (isset($data['hourly_rate'])) {
            $update_data['hourly_rate'] = floatval($data['hourly_rate']);
        }
        if (isset($data['estimated_hours'])) {
            $update_data['estimated_hours'] = floatval($data['estimated_hours']);
        }
        if (isset($data['total_amount'])) {
            $update_data['total_amount'] = floatval($data['total_amount']);
        }
        if (isset($data['deposit_required'])) {
            $update_data['deposit_required'] = intval($data['deposit_required']);
        }
        if (isset($data['deposit_type'])) {
            $update_data['deposit_type'] = sanitize_text_field($data['deposit_type']);
        }
        if (isset($data['deposit_value'])) {
            $update_data['deposit_value'] = floatval($data['deposit_value']);
        }
        if (isset($data['billing_frequency'])) {
            $update_data['billing_frequency'] = sanitize_text_field($data['billing_frequency']);
        }
        if (array_key_exists('first_invoice_date', $data)) {
            $update_data['first_invoice_date'] = !empty($data['first_invoice_date']) ? sanitize_text_field($data['first_invoice_date']) : null;
        }
        if (array_key_exists('start_date', $data)) {
            $update_data['start_date'] = !empty($data['start_date']) ? sanitize_text_field($data['start_date']) : null;
        }
        if (array_key_exists('valid_until', $data)) {
            $update_data['valid_until'] = !empty($data['valid_until']) ? sanitize_text_field($data['valid_until']) : null;
        }
        if (isset($data['notes'])) {
            $update_data['notes'] = sanitize_textarea_field($data['notes']);
        }
        if (isset($data['internal_notes'])) {
            $update_data['internal_notes'] = sanitize_textarea_field($data['internal_notes']);
        }
        if (isset($data['status'])) {
            $update_data['status'] = sanitize_text_field($data['status']);
        }
        if (isset($data['proposal_number'])) {
            $update_data['proposal_number'] = sanitize_text_field($data['proposal_number']);
        }
        
        $result = $wpdb->update(
            $table,
            $update_data,
            array('id' => $id),
            null,
            array('%d')
        );
        
        if ($result === false) {
            error_log('FS_Proposal::update failed: ' . $wpdb->last_error);
        }
        
        return $result;
    }

    /**
     * Update proposal status
     *
     * @param int    $id     Proposal ID
     * @param string $status New status
     * @return int|false
     */
    public static function update_status($id, $status) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_proposals';
        
        $update_data = array('status' => $status);
        
        // Set timestamps based on status
        switch ($status) {
            case self::STATUS_SENT:
                $update_data['sent_at'] = current_time('mysql');
                break;
            case self::STATUS_VIEWED:
                $proposal = self::get($id);
                if ($proposal && !$proposal->viewed_at) {
                    $update_data['viewed_at'] = current_time('mysql');
                }
                break;
            case self::STATUS_APPROVED:
                $update_data['approved_at'] = current_time('mysql');
                break;
            case self::STATUS_REJECTED:
                $update_data['rejected_at'] = current_time('mysql');
                break;
        }
        
        return $wpdb->update(
            $table,
            $update_data,
            array('id' => $id),
            null,
            array('%d')
        );
    }

    /**
     * Record client signature
     *
     * @param int    $id            Proposal ID
     * @param string $signature     Base64 signature image
     * @param string $signer_name   Name of signer
     * @param string $ip_address    Client IP address
     * @return int|false
     */
    public static function record_signature($id, $signature, $signer_name, $ip_address) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_proposals';
        
        return $wpdb->update(
            $table,
            array(
                'signature_client' => $signature,
                'signature_client_name' => sanitize_text_field($signer_name),
                'signature_date' => current_time('mysql'),
                'signature_ip' => sanitize_text_field($ip_address),
                'status' => self::STATUS_APPROVED,
                'approved_at' => current_time('mysql'),
            ),
            array('id' => $id),
            null,
            array('%d')
        );
    }

    /**
     * Record revision request from client
     *
     * @param int    $id              Proposal ID
     * @param string $revision_notes  Client's requested changes
     * @return int|false
     */
    public static function request_revision($id, $revision_notes) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_proposals';
        
        return $wpdb->update(
            $table,
            array(
                'revision_notes' => sanitize_textarea_field($revision_notes),
                'revision_requested_at' => current_time('mysql'),
                'status' => self::STATUS_REVISION_REQUESTED,
            ),
            array('id' => $id),
            null,
            array('%d')
        );
    }

    /**
     * Delete a proposal
     *
     * @param int $id Proposal ID
     * @return int|false
     */
    public static function delete($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_proposals';
        
        return $wpdb->delete($table, array('id' => $id), array('%d'));
    }

    /**
     * Generate next proposal number
     *
     * @return string
     */
    public static function generate_proposal_number() {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_proposals';
        $year = gmdate('Y');
        $prefix = get_option('fs_proposal_prefix', 'PROP');
        
        // Get the highest number for this year
        $last_number = $wpdb->get_var($wpdb->prepare(
            "SELECT proposal_number FROM {$table} 
             WHERE proposal_number LIKE %s 
             ORDER BY id DESC LIMIT 1",
            $prefix . '-' . $year . '-%'
        ));
        
        if ($last_number) {
            // Extract the sequence number
            $parts = explode('-', $last_number);
            $seq = intval(end($parts)) + 1;
        } else {
            $seq = 1;
        }
        
        return sprintf('%s-%s-%03d', $prefix, $year, $seq);
    }

    /**
     * Duplicate a proposal
     *
     * @param int $id Proposal ID to duplicate
     * @return int|false New proposal ID or false
     */
    public static function duplicate($id) {
        $original = self::get($id);
        
        if (!$original) {
            return false;
        }
        
        $data = array(
            'client_id' => $original->client_id,
            'proposal_number' => self::generate_proposal_number(),
            'title' => $original->title . ' (Copy)',
            'status' => self::STATUS_DRAFT,
            'services' => $original->services,
            'pricing_type' => $original->pricing_type,
            'hourly_rate' => $original->hourly_rate,
            'estimated_hours' => $original->estimated_hours,
            'total_amount' => $original->total_amount,
            'deposit_required' => $original->deposit_required,
            'deposit_type' => $original->deposit_type,
            'deposit_value' => $original->deposit_value,
            'billing_frequency' => $original->billing_frequency,
            'notes' => $original->notes,
            'internal_notes' => $original->internal_notes,
        );
        
        return self::create($data);
    }

    /**
     * Get statuses
     *
     * @return array
     */
    public static function get_statuses() {
        return array(
            self::STATUS_DRAFT => __('Draft', 'freelance-studio'),
            self::STATUS_SENT => __('Sent', 'freelance-studio'),
            self::STATUS_VIEWED => __('Viewed', 'freelance-studio'),
            self::STATUS_REVISION_REQUESTED => __('Revision Requested', 'freelance-studio'),
            self::STATUS_REVISED => __('Revised', 'freelance-studio'),
            self::STATUS_APPROVED => __('Approved', 'freelance-studio'),
            self::STATUS_DISMISSED => __('Dismissed', 'freelance-studio'),
            self::STATUS_REJECTED => __('Rejected', 'freelance-studio'),
            self::STATUS_EXPIRED => __('Expired', 'freelance-studio'),
            self::STATUS_CONVERTED => __('Converted', 'freelance-studio'),
        );
    }

    /**
     * Get billing frequencies
     *
     * @return array
     */
    public static function get_billing_frequencies() {
        return array(
            'once' => __('Once', 'freelance-studio'),
            'weekly' => __('Weekly', 'freelance-studio'),
            'biweekly' => __('Bi-weekly', 'freelance-studio'),
            'monthly' => __('Monthly', 'freelance-studio'),
            'quarterly' => __('Quarterly', 'freelance-studio'),
            'yearly' => __('Yearly', 'freelance-studio'),
        );
    }

    /**
     * Count proposals
     *
     * @param array $args Query arguments
     * @return int
     */
    public static function count($args = array()) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_proposals';
        
        $where = array('1=1');
        $values = array();
        
        if (!empty($args['client_id'])) {
            $where[] = 'client_id = %d';
            $values[] = $args['client_id'];
        }
        
        if (!empty($args['status'])) {
            $where[] = 'status = %s';
            $values[] = $args['status'];
        }
        
        if (isset($args['archived'])) {
            if ($args['archived']) {
                $where[] = 'archived = 1';
            } else {
                $where[] = '(archived = 0 OR archived IS NULL)';
            }
        }
        
        $where_clause = implode(' AND ', $where);
        
        $sql = "SELECT COUNT(*) FROM {$table} WHERE {$where_clause}";
        
        if (!empty($values)) {
            $sql = $wpdb->prepare($sql, $values); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
        }
        
        return (int) $wpdb->get_var($sql); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
    }

    /**
     * Check if proposal is expired
     *
     * @param object $proposal Proposal object
     * @return bool
     */
    public static function is_expired($proposal) {
        if (empty($proposal->valid_until)) {
            return false;
        }
        
        return strtotime($proposal->valid_until) < time();
    }

    /**
     * Calculate deposit amount
     *
     * @param object $proposal Proposal object
     * @return float
     */
    public static function calculate_deposit($proposal) {
        if (!$proposal->deposit_required) {
            return 0;
        }
        
        if ($proposal->deposit_type === 'fixed') {
            return floatval($proposal->deposit_value);
        }
        
        // Percentage
        return floatval($proposal->total_amount) * (floatval($proposal->deposit_value) / 100);
    }

    /**
     * Archive a proposal
     */
    public static function archive($id) {
        global $wpdb;
        return $wpdb->update($wpdb->prefix . 'fs_proposals', array('archived' => 1), array('id' => $id), array('%d'), array('%d')) !== false;
    }

    /**
     * Unarchive a proposal
     */
    public static function unarchive($id) {
        global $wpdb;
        return $wpdb->update($wpdb->prefix . 'fs_proposals', array('archived' => 0), array('id' => $id), array('%d'), array('%d')) !== false;
    }

    /**
     * Get count by archived status
     */
    public static function get_count($archived = false) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_proposals';
        if ($archived) {
            return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE archived = 1");
        }
        return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE archived = 0 OR archived IS NULL");
    }
}
