<?php
/**
 * Manual Income Model
 *
 * @package Freelance_Studio
 */

class FS_Manual_Income {

    /**
     * Income categories
     */
    public static function get_categories() {
        return array(
            'freelance' => __('Freelance Work', 'freelance-studio'),
            'etsy' => __('Etsy Sales', 'freelance-studio'),
            'art_fair' => __('Art Fair/Market', 'freelance-studio'),
            'commission' => __('Commission', 'freelance-studio'),
            'royalty' => __('Royalty/Licensing', 'freelance-studio'),
            'consulting' => __('Consulting', 'freelance-studio'),
            'workshop' => __('Workshop/Teaching', 'freelance-studio'),
            'affiliate' => __('Affiliate Income', 'freelance-studio'),
            'refund' => __('Refund/Reimbursement', 'freelance-studio'),
            'other' => __('Other', 'freelance-studio'),
        );
    }

    /**
     * Get all manual income entries
     *
     * @param array $args Query arguments
     * @return array
     */
    public static function get_all($args = array()) {
        global $wpdb;
        
        $defaults = array(
            'category' => null,
            'start_date' => null,
            'end_date' => null,
            'source' => null,
            'orderby' => 'income_date',
            'order' => 'DESC',
            'limit' => null,
            'offset' => 0
        );
        
        $args = wp_parse_args($args, $defaults);
        
        $table = $wpdb->prefix . 'fs_manual_income';
        
        $where = array('1=1');
        
        if ($args['category']) {
            $where[] = $wpdb->prepare('category = %s', $args['category']);
        }
        
        if ($args['start_date']) {
            $where[] = $wpdb->prepare('income_date >= %s', $args['start_date']);
        }
        
        if ($args['end_date']) {
            $where[] = $wpdb->prepare('income_date <= %s', $args['end_date']);
        }
        
        if ($args['source']) {
            $where[] = $wpdb->prepare('source LIKE %s', '%' . $wpdb->esc_like($args['source']) . '%');
        }
        
        $where_clause = implode(' AND ', $where);
        
        $allowed_orderby = array('income_date', 'amount', 'category', 'source', 'created_at');
        $orderby = in_array($args['orderby'], $allowed_orderby) ? $args['orderby'] : 'income_date';
        $order = strtoupper($args['order']) === 'ASC' ? 'ASC' : 'DESC';
        
        $sql = "SELECT * FROM {$table} WHERE {$where_clause} ORDER BY {$orderby} {$order}";
        
        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 manual income entry
     *
     * @param int $id Entry ID
     * @return object|null
     */
    public static function get($id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_manual_income';
        
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE id = %d",
            $id
        ));
    }

    /**
     * Create a manual income entry
     *
     * @param array $data Entry data
     * @return int|false Entry ID or false on failure
     */
    public static function create($data) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_manual_income';
        
        $defaults = array(
            'source' => '',
            'description' => '',
            'amount' => 0,
            'income_date' => current_time('Y-m-d'),
            'category' => 'other',
            'reference' => '',
            'notes' => '',
        );
        
        $data = wp_parse_args($data, $defaults);
        
        $result = $wpdb->insert($table, array(
            'source' => $data['source'],
            'description' => $data['description'],
            'amount' => floatval($data['amount']),
            'income_date' => $data['income_date'],
            'category' => $data['category'],
            'reference' => $data['reference'],
            'notes' => $data['notes'],
        ), array('%s', '%s', '%f', '%s', '%s', '%s', '%s'));
        
        return $result ? $wpdb->insert_id : false;
    }

    /**
     * Update a manual income entry
     *
     * @param int $id Entry ID
     * @param array $data Entry data
     * @return bool
     */
    public static function update($id, $data) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_manual_income';
        
        $update_data = array();
        $format = array();
        
        if (isset($data['source'])) {
            $update_data['source'] = $data['source'];
            $format[] = '%s';
        }
        
        if (isset($data['description'])) {
            $update_data['description'] = $data['description'];
            $format[] = '%s';
        }
        
        if (isset($data['amount'])) {
            $update_data['amount'] = floatval($data['amount']);
            $format[] = '%f';
        }
        
        if (isset($data['income_date'])) {
            $update_data['income_date'] = $data['income_date'];
            $format[] = '%s';
        }
        
        if (isset($data['category'])) {
            $update_data['category'] = $data['category'];
            $format[] = '%s';
        }
        
        if (isset($data['reference'])) {
            $update_data['reference'] = $data['reference'];
            $format[] = '%s';
        }
        
        if (isset($data['notes'])) {
            $update_data['notes'] = $data['notes'];
            $format[] = '%s';
        }
        
        if (empty($update_data)) {
            return false;
        }
        
        return $wpdb->update($table, $update_data, array('id' => $id), $format, array('%d')) !== false;
    }

    /**
     * Delete a manual income entry
     *
     * @param int $id Entry ID
     * @return bool
     */
    public static function delete($id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_manual_income';
        
        return $wpdb->delete($table, array('id' => $id), array('%d')) !== false;
    }

    /**
     * Get total manual income for a date range
     *
     * @param string $start_date Start date
     * @param string $end_date End date
     * @return float
     */
    public static function get_total($start_date = null, $end_date = null) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_manual_income';
        
        $where = array('1=1');
        
        if ($start_date) {
            $where[] = $wpdb->prepare('income_date >= %s', $start_date);
        }
        
        if ($end_date) {
            $where[] = $wpdb->prepare('income_date <= %s', $end_date);
        }
        
        $where_clause = implode(' AND ', $where);
        
        $result = $wpdb->get_var("SELECT COALESCE(SUM(amount), 0) FROM {$table} WHERE {$where_clause}");
        
        return floatval($result);
    }

    /**
     * Get monthly totals for a year
     *
     * @param int $year Year
     * @return array
     */
    public static function get_monthly_totals($year) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_manual_income';
        
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT MONTH(income_date) as month, COALESCE(SUM(amount), 0) as total 
             FROM {$table} 
             WHERE YEAR(income_date) = %d 
             GROUP BY MONTH(income_date)",
            $year
        ));
        
        // Convert to associative array
        $monthly = array();
        foreach ($results as $row) {
            $monthly[$row->month] = floatval($row->total);
        }
        
        return $monthly;
    }

    /**
     * Get totals by category for a date range
     *
     * @param string $start_date Start date
     * @param string $end_date End date
     * @return array
     */
    public static function get_totals_by_category($start_date = null, $end_date = null) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_manual_income';
        
        $where = array('1=1');
        
        if ($start_date) {
            $where[] = $wpdb->prepare('income_date >= %s', $start_date);
        }
        
        if ($end_date) {
            $where[] = $wpdb->prepare('income_date <= %s', $end_date);
        }
        
        $where_clause = implode(' AND ', $where);
        
        return $wpdb->get_results(
            "SELECT category, COALESCE(SUM(amount), 0) as total, COUNT(*) as count 
             FROM {$table} 
             WHERE {$where_clause} 
             GROUP BY category 
             ORDER BY total DESC"
        );
    }

    /**
     * Count all entries
     *
     * @param array $args Query arguments
     * @return int
     */
    public static function count($args = array()) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_manual_income';
        
        $where = array('1=1');
        
        if (!empty($args['category'])) {
            $where[] = $wpdb->prepare('category = %s', $args['category']);
        }
        
        if (!empty($args['start_date'])) {
            $where[] = $wpdb->prepare('income_date >= %s', $args['start_date']);
        }
        
        if (!empty($args['end_date'])) {
            $where[] = $wpdb->prepare('income_date <= %s', $args['end_date']);
        }
        
        $where_clause = implode(' AND ', $where);
        
        return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE {$where_clause}");
    }
}
