<?php
defined('ABSPATH') or die('Direct access not allowed');

/**
 * VollstartRD_Products — Product/device CRUD.
 *
 * SRP: Creates, reads, updates, deletes products (devices) per customer.
 * A product can be linked to a repair to auto-fill device fields.
 */
class VollstartRD_Products {

    private $MAIN;

    public function __construct($MAIN) {
        $this->MAIN = $MAIN;
    }

    /**
     * Create a new product for a customer.
     * @return int Product ID
     */
    public function create(array $data): int {
        $fields = [
            'customer_id'   => (int) ($data['customer_id'] ?? 0),
            'name'          => sanitize_text_field($data['name'] ?? ''),
            'brand'         => sanitize_text_field($data['brand'] ?? ''),
            'model'         => sanitize_text_field($data['model'] ?? ''),
            'serial_number' => sanitize_text_field($data['serial_number'] ?? ''),
            'notes'         => sanitize_textarea_field($data['notes'] ?? ''),
            'created_at'    => current_time('mysql'),
        ];
        if (!empty($data['service_interval_days'])) {
            $fields['service_interval_days'] = (int) $data['service_interval_days'];
        }
        return $this->MAIN->getDB()->insert('products', $fields);
    }

    /**
     * Update a product.
     */
    /**
     * Get a single product by ID.
     */
    public function getById(int $id): ?array {
        $db = $this->MAIN->getDB();
        $tbl = $db->getTabelle('products');
        return $db->getRow("SELECT * FROM %i WHERE id = %d", $tbl, $id);
    }

    /**
     * Get all products for a customer.
     */
    public function getByCustomerId(int $customerId): array {
        $db = $this->MAIN->getDB();
        $tbl = $db->getTabelle('products');
        return $db->getResults(
            "SELECT * FROM %i WHERE customer_id = %d ORDER BY name ASC",
            $tbl,
            $customerId
        );
    }

    /**
     * Get products for dropdown (when creating a repair).
     * Returns products for a specific customer.
     */
    public function getForDropdown(int $customerId): array {
        $db = $this->MAIN->getDB();
        $tbl = $db->getTabelle('products');
        return $db->getResults(
            "SELECT id, name, brand, model, serial_number, service_interval_days, next_service_date FROM %i WHERE customer_id = %d ORDER BY name ASC",
            $tbl,
            $customerId
        );
    }

    /**
     * Count products for a customer.
     */
    public function countByCustomer(int $customerId): int {
        $db = $this->MAIN->getDB();
        $tbl = $db->getTabelle('products');
        return (int) $db->getVar(
            "SELECT COUNT(*) FROM %i WHERE customer_id = %d",
            $tbl,
            $customerId
        );
    }

    /**
     * Delete a product.
     */
    public function delete(int $id): bool {
        return $this->MAIN->getDB()->delete('products', ['id' => $id]) !== false;
    }

    /**
     * Count all products.
     */
    public function count(): int {
        return $this->MAIN->getDB()->count('products');
    }

    /**
     * Update with change history logging.
     */
    public function update(int $id, array $data): bool {
        $old = $this->getById($id);

        $fields = [];
        $allowed = ['name', 'brand', 'model', 'serial_number', 'notes'];
        foreach ($allowed as $key) {
            if (isset($data[$key])) {
                $fields[$key] = ($key === 'notes')
                    ? sanitize_textarea_field($data[$key])
                    : sanitize_text_field($data[$key]);
            }
        }
        // Maintenance fields
        if (array_key_exists('service_interval_days', $data)) {
            $fields['service_interval_days'] = !empty($data['service_interval_days']) ? (int) $data['service_interval_days'] : null;
        }
        if (array_key_exists('next_service_date', $data)) {
            $fields['next_service_date'] = !empty($data['next_service_date']) ? sanitize_text_field($data['next_service_date']) : null;
        }
        if (array_key_exists('service_reminder_sent', $data)) {
            $fields['service_reminder_sent'] = (int) $data['service_reminder_sent'];
        }
        if (empty($fields)) {
            return false;
        }

        $result = $this->MAIN->getDB()->update('products', $fields, ['id' => $id]) !== false;

        // Log changes
        if ($result && $old) {
            foreach ($fields as $key => $newVal) {
                $oldVal = $old[$key] ?? '';
                if ((string) $oldVal !== (string) $newVal) {
                    $this->logChange($id, $key, $oldVal, $newVal);
                }
            }
        }

        return $result;
    }

    private function logChange(int $productId, string $field, string $oldValue, string $newValue): void {
        try {
            $this->MAIN->getDB()->insert('product_history', [
                'product_id'  => $productId,
                'field_name'  => $field,
                'old_value'   => $oldValue,
                'new_value'   => $newValue,
                'changed_by'  => get_current_user_id(),
                'changed_at'  => wp_date('Y-m-d H:i:s'),
            ]);
        } catch (\Exception $e) {
            // silently ignore if table does not exist yet
        }
    }

    /**
     * Get change history for a product.
     */
    public function getHistory(int $productId): array {
        $db = $this->MAIN->getDB();
        global $wpdb;
        $tbl = $db->getTabelle('product_history');
        return $db->getResults(
            "SELECT h.*, u.display_name AS changed_by_name FROM %i h LEFT JOIN {$wpdb->users} u ON h.changed_by = u.ID WHERE h.product_id = %d ORDER BY h.changed_at DESC",
            $tbl, $productId
        );
    }

    /**
     * Server-side paginated list for DataTables (all products, across customers).
     */
    public function getPaginated(array $params): array {
        $db = $this->MAIN->getDB();
        $tbl = $db->getTabelle('products');
        $ctbl = $db->getTabelle('customers');
        $rtbl = $db->getTabelle('repairs');

        $page = max(1, (int) ($params['page'] ?? 1));
        $perPage = max(1, min(100, (int) ($params['per_page'] ?? 20)));
        $offset = ($page - 1) * $perPage;
        $search = $params['search'] ?? '';
        $orderBy = $params['orderby'] ?? 'name';
        $order = strtoupper($params['order'] ?? 'ASC') === 'DESC' ? 'DESC' : 'ASC';

        $sortable = ['name', 'brand', 'model', 'serial_number', 'customer_name', 'repair_count', 'next_service_date'];
        if (!in_array($orderBy, $sortable)) $orderBy = 'name';

        $where = '';
        $whereValues = [];
        if (!empty($search)) {
            $like = '%' . $db->escLike($search) . '%';
            $where = "WHERE (p.name LIKE %s OR p.brand LIKE %s OR p.model LIKE %s OR p.serial_number LIKE %s OR c.name LIKE %s)";
            $whereValues = [$like, $like, $like, $like, $like];
        }

        $total = (int) $db->getVar("SELECT COUNT(*) FROM %i", $tbl);
        $filtered = !empty($where)
            ? (int) $db->getVar("SELECT COUNT(*) FROM %i p LEFT JOIN %i c ON p.customer_id = c.id {$where}", $tbl, $ctbl, ...$whereValues)
            : $total;

        $orderClause = in_array($orderBy, ['customer_name']) ? "c.name {$order}" : ($orderBy === 'repair_count' ? "repair_count {$order}" : "p.{$orderBy} {$order}");

        $rows = $db->getResults(
            "SELECT p.*, c.name AS customer_name, c.customer_number,
                (SELECT COUNT(*) FROM {$rtbl} WHERE product_id = p.id) AS repair_count
             FROM %i p
             LEFT JOIN %i c ON p.customer_id = c.id
             {$where}
             ORDER BY {$orderClause}
             LIMIT %d, %d",
            $tbl, $ctbl, ...array_merge($whereValues, [$offset, $perPage])
        );

        // Add thumbnail URLs
        foreach ($rows as &$p) {
            $p['thumbnail_url'] = $this->MAIN->getFileUpload()->getThumbnailUrl('product', (int) $p['id']);
        }

        return [
            'products' => $rows,
            'total'    => $total,
            'filtered' => $filtered,
        ];
    }

    /**
     * Get products with overdue maintenance (next_service_date passed, reminder not sent).
     * Joins customers for email.
     */
    public function getOverdueProducts(): array {
        $db = $this->MAIN->getDB();
        $ptbl = $db->getTabelle('products');
        $ctbl = $db->getTabelle('customers');
        return $db->getResults(
            "SELECT p.*, c.name AS customer_name, c.email AS customer_email, c.customer_number
             FROM %i p
             INNER JOIN %i c ON p.customer_id = c.id
             WHERE p.next_service_date IS NOT NULL
               AND p.next_service_date <= CURDATE()
               AND p.service_reminder_sent = 0
               AND c.email != ''",
            $ptbl, $ctbl
        );
    }

    /**
     * Dismiss maintenance reminder for a product (set flag = 1).
     */
    public function dismissReminder(int $id): bool {
        return $this->MAIN->getDB()->update('products', ['service_reminder_sent' => 1], ['id' => $id]) !== false;
    }

    /**
     * Set next service date based on interval (from today).
     * Resets the reminder flag.
     */
    public function scheduleNextService(int $id): void {
        $product = $this->getById($id);
        if (!$product || empty($product['service_interval_days'])) return;
        $nextDate = gmdate('Y-m-d', strtotime('+' . (int) $product['service_interval_days'] . ' days'));
        $this->MAIN->getDB()->update('products', [
            'next_service_date'     => $nextDate,
            'service_reminder_sent' => 0,
        ], ['id' => $id]);
    }
}
