<?php
/**
 * Bank Transaction Model
 *
 * Handles CRUD operations for imported bank/credit card transactions.
 * Supports matching transactions to invoices and expenses.
 *
 * @package Freelance_Studio
 */

class FS_Transaction {

    /**
     * Transaction statuses
     */
    const STATUS_UNMATCHED = 'unmatched';
    const STATUS_MATCHED   = 'matched';
    const STATUS_IGNORED   = 'ignored';

    /**
     * Get table name
     */
    private static function table() {
        global $wpdb;
        return $wpdb->prefix . 'fs_transactions';
    }

    /**
     * Get all transactions with filters
     *
     * @param array $args Query arguments
     * @return array
     */
    public static function get_all($args = array()) {
        global $wpdb;

        $defaults = array(
            'status'       => null,
            'type'         => null,
            'category'     => null,
            'import_batch' => null,
            'source_bank'  => null,
            'is_business'  => null,
            'start_date'   => null,
            'end_date'     => null,
            'search'       => null,
            'orderby'      => 'transaction_date',
            'order'        => 'DESC',
            'limit'        => null,
            'offset'       => 0,
        );

        $args = wp_parse_args($args, $defaults);
        $table = self::table();

        $where = array('1=1');

        if ($args['status']) {
            $where[] = $wpdb->prepare('status = %s', $args['status']);
        }

        if ($args['type']) {
            $where[] = $wpdb->prepare('type = %s', $args['type']);
        }

        if ($args['category']) {
            $where[] = $wpdb->prepare('category = %s', $args['category']);
        }

        if ($args['import_batch']) {
            $where[] = $wpdb->prepare('import_batch = %s', $args['import_batch']);
        }

        if ($args['source_bank']) {
            $where[] = $wpdb->prepare('source_bank = %s', $args['source_bank']);
        }

        if ($args['is_business'] !== null) {
            $where[] = $wpdb->prepare('is_business = %d', $args['is_business']);
        }

        if ($args['start_date']) {
            $where[] = $wpdb->prepare('transaction_date >= %s', $args['start_date']);
        }

        if ($args['end_date']) {
            $where[] = $wpdb->prepare('transaction_date <= %s', $args['end_date']);
        }

        if ($args['search']) {
            $like = '%' . $wpdb->esc_like($args['search']) . '%';
            $where[] = $wpdb->prepare('(description LIKE %s OR bank_reference LIKE %s OR notes LIKE %s)', $like, $like, $like);
        }

        $where_clause = implode(' AND ', $where);

        $allowed_orderby = array('transaction_date', 'amount', 'description', 'status', 'category', 'type', 'created_at');
        $orderby = in_array($args['orderby'], $allowed_orderby) ? $args['orderby'] : 'transaction_date';
        $order = strtoupper($args['order']) === 'ASC' ? 'ASC' : 'DESC';

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $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']);
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return $wpdb->get_results($sql);
    }

    /**
     * Get a single transaction
     *
     * @param int $id Transaction ID
     * @return object|null
     */
    public static function get($id) {
        global $wpdb;
        $table = self::table();

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE id = %d", $id));
    }

    /**
     * Create a single transaction
     *
     * @param array $data Transaction data
     * @return int|false Insert ID or false on failure
     */
    public static function create($data) {
        global $wpdb;
        $table = self::table();

        $insert = array(
            'transaction_date' => $data['transaction_date'],
            'description'      => $data['description'],
            'amount'           => floatval($data['amount']),
            'type'             => floatval($data['amount']) >= 0 ? 'credit' : 'debit',
            'bank_reference'   => isset($data['bank_reference']) ? $data['bank_reference'] : '',
            'status'           => isset($data['status']) ? $data['status'] : self::STATUS_UNMATCHED,
            'linked_expense_id' => isset($data['linked_expense_id']) ? intval($data['linked_expense_id']) : null,
            'linked_invoice_id' => isset($data['linked_invoice_id']) ? intval($data['linked_invoice_id']) : null,
            'category'         => isset($data['category']) ? $data['category'] : null,
            'is_business'      => isset($data['is_business']) ? intval($data['is_business']) : 1,
            'import_batch'     => isset($data['import_batch']) ? $data['import_batch'] : '',
            'source_bank'      => isset($data['source_bank']) ? $data['source_bank'] : '',
            'raw_data'         => isset($data['raw_data']) ? $data['raw_data'] : null,
            'notes'            => isset($data['notes']) ? $data['notes'] : null,
        );

        $formats = array('%s', '%s', '%f', '%s', '%s', '%s', '%d', '%d', '%s', '%d', '%s', '%s', '%s', '%s');

        $result = $wpdb->insert($table, $insert, $formats);

        return $result ? $wpdb->insert_id : false;
    }

    /**
     * Create transactions in batch (from CSV import)
     *
     * @param array  $rows        Array of transaction data arrays
     * @param string $import_batch Batch identifier
     * @param string $source_bank  Bank name
     * @return array  Array with 'imported' count and 'skipped' count
     */
    public static function create_batch($rows, $import_batch, $source_bank = '') {
        $imported = 0;
        $skipped = 0;
        $rules = get_option('fs_personal_transaction_rules', array());

        foreach ($rows as $row) {
            $row['import_batch'] = $import_batch;
            $row['source_bank'] = $source_bank;

            // Check for duplicate: same date + amount + description
            if (self::exists($row['transaction_date'], $row['amount'], $row['description'])) {
                $skipped++;
                continue;
            }

            // Auto-ignore if matches personal rules
            if (!empty($rules) && self::matches_personal_rules($row['description'], $rules)) {
                $row['status'] = self::STATUS_IGNORED;
                $row['is_business'] = 0;
            }

            $id = self::create($row);
            if ($id) {
                $imported++;
            } else {
                $skipped++;
            }
        }

        return array('imported' => $imported, 'skipped' => $skipped);
    }

    /**
     * Check if a description matches any personal rules
     *
     * @param string $description Transaction description
     * @param array  $rules       Array of keyword strings
     * @return bool
     */
    public static function matches_personal_rules($description, $rules = null) {
        if ($rules === null) {
            $rules = get_option('fs_personal_transaction_rules', array());
        }
        if (empty($rules)) return false;

        $desc_lower = strtolower($description);
        foreach ($rules as $keyword) {
            if (strpos($desc_lower, strtolower($keyword)) !== false) {
                return true;
            }
        }
        return false;
    }

    /**
     * Check if a transaction already exists (duplicate detection)
     *
     * @param string $date        Transaction date
     * @param float  $amount      Amount
     * @param string $description Description
     * @return bool
     */
    public static function exists($date, $amount, $description) {
        global $wpdb;
        $table = self::table();

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $count = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE transaction_date = %s AND amount = %f AND description = %s",
            $date,
            floatval($amount),
            $description
        ));

        return intval($count) > 0;
    }

    /**
     * Update a transaction
     *
     * @param int   $id   Transaction ID
     * @param array $data Fields to update
     * @return bool
     */
    public static function update($id, $data) {
        global $wpdb;
        $table = self::table();

        // Auto-set type from amount if amount is being updated
        if (isset($data['amount'])) {
            $data['type'] = floatval($data['amount']) >= 0 ? 'credit' : 'debit';
        }

        return $wpdb->update($table, $data, array('id' => $id)) !== false;
    }

    /**
     * Delete a transaction
     *
     * @param int $id Transaction ID
     * @return bool
     */
    public static function delete($id) {
        global $wpdb;
        $table = self::table();

        return $wpdb->delete($table, array('id' => $id)) !== false;
    }

    /**
     * Delete all transactions in a batch
     *
     * @param string $import_batch Batch identifier
     * @return int|false Number of rows deleted
     */
    public static function delete_batch($import_batch) {
        global $wpdb;
        $table = self::table();

        return $wpdb->delete($table, array('import_batch' => $import_batch));
    }

    /**
     * Link a transaction to an expense
     *
     * @param int $id         Transaction ID
     * @param int $expense_id Expense ID
     * @return bool
     */
    public static function link_to_expense($id, $expense_id) {
        return self::update($id, array(
            'linked_expense_id' => $expense_id,
            'status'            => self::STATUS_MATCHED,
        ));
    }

    /**
     * Link a transaction to an invoice (deposit matched to payment)
     *
     * @param int  $id         Transaction ID
     * @param int  $invoice_id Invoice ID
     * @param bool $mark_paid  Whether to also mark the invoice as paid
     * @return bool
     */
    public static function link_to_invoice($id, $invoice_id, $mark_paid = false) {
        $result = self::update($id, array(
            'linked_invoice_id' => $invoice_id,
            'status'            => self::STATUS_MATCHED,
        ));

        if ($result && $mark_paid) {
            FS_Invoice::update($invoice_id, array(
                'status'    => 'paid',
                'paid_date' => current_time('Y-m-d'),
            ));
        }

        return $result;
    }

    /**
     * Unlink a transaction (remove expense/invoice association)
     *
     * @param int $id Transaction ID
     * @return bool
     */
    public static function unlink($id) {
        return self::update($id, array(
            'linked_expense_id' => null,
            'linked_invoice_id' => null,
            'status'            => self::STATUS_UNMATCHED,
        ));
    }

    /**
     * Mark a transaction as ignored (personal)
     *
     * @param int $id Transaction ID
     * @return bool
     */
    public static function ignore($id) {
        return self::update($id, array(
            'status'      => self::STATUS_IGNORED,
            'is_business' => 0,
        ));
    }

    /**
     * Unignore a transaction
     *
     * @param int $id Transaction ID
     * @return bool
     */
    public static function unignore($id) {
        return self::update($id, array(
            'status'      => self::STATUS_UNMATCHED,
            'is_business' => 1,
        ));
    }

    /**
     * Find potential expense matches for a debit transaction
     * Matches by amount (exact) and date (within a window)
     *
     * @param int $id         Transaction ID
     * @param int $date_range Days to search around the transaction date
     * @return array Matching expenses
     */
    public static function find_expense_matches($id, $date_range = 7) {
        global $wpdb;

        $transaction = self::get($id);
        if (!$transaction || $transaction->type !== 'debit') {
            return array();
        }

        $exp_table = $wpdb->prefix . 'fs_expenses';
        $match_amount = abs($transaction->amount);

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_results($wpdb->prepare(
            "SELECT e.* FROM {$exp_table} e
             WHERE e.amount = %f
             AND e.expense_date BETWEEN DATE_SUB(%s, INTERVAL %d DAY) AND DATE_ADD(%s, INTERVAL %d DAY)
             AND e.id NOT IN (
                 SELECT linked_expense_id FROM " . self::table() . " 
                 WHERE linked_expense_id IS NOT NULL
             )
             ORDER BY ABS(DATEDIFF(e.expense_date, %s)) ASC
             LIMIT 10",
            $match_amount,
            $transaction->transaction_date,
            $date_range,
            $transaction->transaction_date,
            $date_range,
            $transaction->transaction_date
        ));
    }

    /**
     * Find potential invoice matches for a credit transaction (deposit)
     * Matches by amount (exact) and date (within a window)
     *
     * @param int $id         Transaction ID
     * @param int $date_range Days to search around the transaction date
     * @return array Matching invoices
     */
    public static function find_invoice_matches($id, $date_range = 30) {
        global $wpdb;

        $transaction = self::get($id);
        if (!$transaction || $transaction->type !== 'credit') {
            return array();
        }

        $inv_table = $wpdb->prefix . 'fs_invoices';
        $match_amount = abs($transaction->amount);

        // Match against total or remaining balance
        // Include paid invoices (for bank verification) — exclude draft and cancelled
        // Match date against issue_date, due_date, or paid_date (deposits can arrive anytime)
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_results($wpdb->prepare(
            "SELECT i.*, c.name as client_name, p.name as project_name FROM {$inv_table} i
             LEFT JOIN {$wpdb->prefix}fs_clients c ON i.client_id = c.id
             LEFT JOIN {$wpdb->prefix}fs_projects p ON i.project_id = p.id
             WHERE (i.total = %f OR (i.total - COALESCE(i.amount_paid, 0)) = %f)
             AND i.status NOT IN ('draft', 'cancelled')
             AND (
                 i.issue_date BETWEEN DATE_SUB(%s, INTERVAL %d DAY) AND DATE_ADD(%s, INTERVAL %d DAY)
                 OR i.due_date BETWEEN DATE_SUB(%s, INTERVAL %d DAY) AND DATE_ADD(%s, INTERVAL %d DAY)
                 OR i.paid_date BETWEEN DATE_SUB(%s, INTERVAL %d DAY) AND DATE_ADD(%s, INTERVAL %d DAY)
             )
             AND i.id NOT IN (
                 SELECT linked_invoice_id FROM " . self::table() . " 
                 WHERE linked_invoice_id IS NOT NULL
             )
             ORDER BY ABS(DATEDIFF(COALESCE(i.paid_date, i.issue_date), %s)) ASC
             LIMIT 10",
            $match_amount,
            $match_amount,
            $transaction->transaction_date, $date_range, $transaction->transaction_date, $date_range,
            $transaction->transaction_date, $date_range, $transaction->transaction_date, $date_range,
            $transaction->transaction_date, $date_range, $transaction->transaction_date, $date_range,
            $transaction->transaction_date
        ));
    }

    /**
     * Get summary statistics
     *
     * @param array $args Optional filters (start_date, end_date, import_batch)
     * @return object Stats object with counts and totals
     */
    public static function get_stats($args = array()) {
        global $wpdb;
        $table = self::table();

        $where = array('1=1');

        if (!empty($args['start_date'])) {
            $where[] = $wpdb->prepare('transaction_date >= %s', $args['start_date']);
        }
        if (!empty($args['end_date'])) {
            $where[] = $wpdb->prepare('transaction_date <= %s', $args['end_date']);
        }
        if (!empty($args['import_batch'])) {
            $where[] = $wpdb->prepare('import_batch = %s', $args['import_batch']);
        }

        $where_clause = implode(' AND ', $where);

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_row(
            "SELECT
                COUNT(*) as total,
                SUM(CASE WHEN status = 'unmatched' THEN 1 ELSE 0 END) as unmatched,
                SUM(CASE WHEN status = 'matched' THEN 1 ELSE 0 END) as matched,
                SUM(CASE WHEN status = 'ignored' THEN 1 ELSE 0 END) as ignored,
                SUM(CASE WHEN type = 'credit' THEN amount ELSE 0 END) as total_credits,
                SUM(CASE WHEN type = 'debit' THEN ABS(amount) ELSE 0 END) as total_debits,
                COUNT(DISTINCT import_batch) as import_count
             FROM {$table}
             WHERE {$where_clause}"
        );
    }

    /**
     * Get list of import batches with metadata
     *
     * @return array
     */
    public static function get_import_batches() {
        global $wpdb;
        $table = self::table();

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_results(
            "SELECT 
                import_batch,
                source_bank,
                COUNT(*) as transaction_count,
                MIN(transaction_date) as date_from,
                MAX(transaction_date) as date_to,
                SUM(CASE WHEN status = 'unmatched' THEN 1 ELSE 0 END) as unmatched_count,
                MIN(created_at) as imported_at
             FROM {$table}
             GROUP BY import_batch, source_bank
             ORDER BY imported_at DESC"
        );
    }

    /**
     * Count transactions with optional filters
     *
     * @param array $args Query arguments (same as get_all)
     * @return int
     */
    public static function count($args = array()) {
        global $wpdb;
        $table = self::table();

        $where = array('1=1');

        if (!empty($args['status'])) {
            $where[] = $wpdb->prepare('status = %s', $args['status']);
        }
        if (!empty($args['type'])) {
            $where[] = $wpdb->prepare('type = %s', $args['type']);
        }
        if (!empty($args['is_business'])) {
            $where[] = $wpdb->prepare('is_business = %d', $args['is_business']);
        }
        if (!empty($args['import_batch'])) {
            $where[] = $wpdb->prepare('import_batch = %s', $args['import_batch']);
        }

        $where_clause = implode(' AND ', $where);

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE {$where_clause}");
    }

    /**
     * Get unique banks from imported transactions
     *
     * @return array
     */
    public static function get_banks() {
        global $wpdb;
        $table = self::table();

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_col("SELECT DISTINCT source_bank FROM {$table} WHERE source_bank != '' ORDER BY source_bank ASC");
    }

    /**
     * Get unique categories used in transactions
     *
     * @return array
     */
    public static function get_used_categories() {
        global $wpdb;
        $table = self::table();

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_col("SELECT DISTINCT category FROM {$table} WHERE category IS NOT NULL AND category != '' ORDER BY category ASC");
    }

    /**
     * Bulk update status for multiple transactions
     *
     * @param array  $ids    Array of transaction IDs
     * @param string $status New status
     * @return int Number of rows updated
     */
    public static function bulk_update_status($ids, $status) {
        global $wpdb;
        $table = self::table();

        if (empty($ids)) {
            return 0;
        }

        $ids = array_map('intval', $ids);
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));

        $is_business = ($status === self::STATUS_IGNORED) ? 0 : 1;

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
        return $wpdb->query($wpdb->prepare(
            "UPDATE {$table} SET status = %s, is_business = %d WHERE id IN ({$placeholders})",
            array_merge(array($status, $is_business), $ids)
        ));
    }
}
