<?php
/**
 * ===========================================
 * FLOWBOT DCI - PINFEED MODEL
 * ===========================================
 * Handles pinfeeds table operations
 */

declare(strict_types=1);

namespace FlowbotDCI\Models;

use FlowbotDCI\Core\Database;

class PinFeed
{
    private Database $db;
    private string $table = 'pinfeeds';

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

    /**
     * Check if URL already exists
     */
    public function exists(string $link): bool
    {
        $stmt = $this->db->query(
            "SELECT COUNT(*) FROM {$this->table} WHERE link = ? LIMIT 1",
            [$link]
        );
        return (int)$stmt->fetchColumn() > 0;
    }

    /**
     * Insert new pin feed
     */
    public function insert(array $data): int
    {
        $sql = "INSERT INTO {$this->table} (
            title, description, thumbnail, pubDate, link, updated,
            source_website, author, favicon, tags, embed_code,
            source_domain, user_id, source_domain_id, main_category_id,
            title_cat_id, description_cat_id, tag_cat_id
        ) VALUES (
            ?, ?, ?, NOW(), ?, NOW(),
            ?, ?, ?, ?, ?,
            ?, ?, ?, ?,
            0, 0, 0
        )";

        $this->db->query($sql, [
            $data['title'] ?? 'No title',
            $data['description'] ?? 'No description',
            $data['thumbnail'] ?? '',
            $data['link'],
            $data['source_website'] ?? '',
            $data['author'] ?? 'Anonymous',
            $data['favicon'] ?? '',
            $data['tags'] ?? '',
            $data['embed_code'] ?? '',
            $data['source_domain'] ?? '',
            $data['user_id'] ?? 0,
            $data['source_domain_id'] ?? 0,
            $data['main_category_id'] ?? 0,
        ]);

        return (int)$this->db->lastInsertId();
    }

    /**
     * OTIMIZADO: Batch insert com chunks para estabilidade
     * Divide em grupos de 25 para evitar timeout/memory issues
     * BACK-001: Added transaction support for atomicity
     */
    public function insertBatch(array $records): int
    {
        if (empty($records)) {
            return 0;
        }

        $columns = [
            'title', 'description', 'thumbnail', 'pubDate', 'link', 'updated',
            'source_website', 'author', 'favicon', 'tags', 'embed_code',
            'source_domain', 'user_id', 'source_domain_id', 'main_category_id',
            'title_cat_id', 'description_cat_id', 'tag_cat_id'
        ];

        $totalInserted = 0;
        $chunkSize = 25; // Inserir em grupos de 25 para estabilidade

        // BACK-001: Use transaction for entire batch operation
        $this->db->beginTransaction();

        try {
            foreach (array_chunk($records, $chunkSize) as $chunk) {
                $placeholders = [];
                $values = [];

                foreach ($chunk as $data) {
                    $placeholders[] = '(?, ?, ?, NOW(), ?, NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 0, 0)';
                    $values[] = $data['title'] ?? 'No title';
                    $values[] = $data['description'] ?? 'No description';
                    $values[] = $data['thumbnail'] ?? '';
                    $values[] = $data['link'];
                    $values[] = $data['source_website'] ?? '';
                    $values[] = $data['author'] ?? 'Anonymous';
                    $values[] = $data['favicon'] ?? '';
                    $values[] = $data['tags'] ?? '';
                    $values[] = $data['embed_code'] ?? '';
                    $values[] = $data['source_domain'] ?? '';
                    $values[] = $data['user_id'] ?? 0;
                    $values[] = $data['source_domain_id'] ?? 0;
                    $values[] = $data['main_category_id'] ?? 0;
                }

                $sql = "INSERT INTO {$this->table} (" . implode(', ', $columns) . ")
                        VALUES " . implode(', ', $placeholders);

                $this->db->query($sql, $values);
                $totalInserted += count($chunk);
            }

            // BACK-001: Commit all chunks at once
            $this->db->commit();

        } catch (\Exception $e) {
            // BACK-001: Rollback on any error - no partial inserts
            $this->db->rollback();
            error_log("Batch insert error (rolled back): " . $e->getMessage());
            throw $e; // Re-throw to let caller handle
        }

        return $totalInserted;
    }

    /**
     * OTIMIZADO: Verifica existência de múltiplas URLs em uma única query
     */
    public function existsBatch(array $links): array
    {
        if (empty($links)) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($links), '?'));
        $stmt = $this->db->query(
            "SELECT link FROM {$this->table} WHERE link IN ($placeholders)",
            $links
        );

        $existing = [];
        while ($row = $stmt->fetch()) {
            $existing[$row['link']] = true;
        }

        return $existing;
    }

    /**
     * Find by ID
     */
    public function find(int $id): ?array
    {
        $stmt = $this->db->query(
            "SELECT * FROM {$this->table} WHERE id = ?",
            [$id]
        );
        $result = $stmt->fetch();
        return $result ?: null;
    }

    /**
     * Find by link
     */
    public function findByLink(string $link): ?array
    {
        $stmt = $this->db->query(
            "SELECT * FROM {$this->table} WHERE link = ?",
            [$link]
        );
        $result = $stmt->fetch();
        return $result ?: null;
    }

    /**
     * Update pin feed
     */
    public function update(int $id, array $data): bool
    {
        $fields = [];
        $values = [];

        foreach ($data as $key => $value) {
            $fields[] = "$key = ?";
            $values[] = $value;
        }

        $values[] = $id;
        $sql = "UPDATE {$this->table} SET " . implode(', ', $fields) . ", updated = NOW() WHERE id = ?";

        $this->db->query($sql, $values);
        return true;
    }

    /**
     * Delete pin feed
     */
    public function delete(int $id): bool
    {
        $this->db->query("DELETE FROM {$this->table} WHERE id = ?", [$id]);
        return true;
    }

    /**
     * Get recent entries
     */
    public function getRecent(int $limit = 10): array
    {
        $stmt = $this->db->query(
            "SELECT * FROM {$this->table} ORDER BY pubDate DESC LIMIT ?",
            [$limit]
        );
        return $stmt->fetchAll();
    }

    /**
     * Count total entries
     */
    public function count(): int
    {
        $stmt = $this->db->query("SELECT COUNT(*) FROM {$this->table}");
        return (int)$stmt->fetchColumn();
    }

    /**
     * Search by title
     */
    public function searchByTitle(string $query, int $limit = 50): array
    {
        $stmt = $this->db->query(
            "SELECT * FROM {$this->table} WHERE title LIKE ? ORDER BY pubDate DESC LIMIT ?",
            ["%$query%", $limit]
        );
        return $stmt->fetchAll();
    }
}
