<?php
namespace CAE\Models;

use CAE\Lib\Database;

class Topic
{
    private Database $db;

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

    public function allByProject(int $projectId, ?string $status = null): array
    {
        $params = [$projectId];
        $where  = 'WHERE t.project_id = ?';
        if ($status !== null) {
            $where .= ' AND t.status = ?';
            $params[] = $status;
        }
        return $this->db->fetchAll(
            "SELECT t.*, pt.name AS page_type_name
             FROM topics t
             LEFT JOIN page_types pt ON pt.id = t.page_type_id
             {$where}
             ORDER BY t.created_at DESC",
            $params
        );
    }

    public function find(int $id): ?array
    {
        return $this->db->fetch(
            "SELECT t.*, pt.name AS page_type_name, p.business_name
             FROM topics t
             LEFT JOIN page_types pt ON pt.id = t.page_type_id
             LEFT JOIN projects p ON p.id = t.project_id
             WHERE t.id = ?",
            [$id]
        );
    }

    public function create(array $data): int
    {
        $data['created_at'] = date('Y-m-d H:i:s');
        $data['updated_at'] = date('Y-m-d H:i:s');
        return $this->db->insert('topics', $data);
    }

    public function update(int $id, array $data): bool
    {
        $data['updated_at'] = date('Y-m-d H:i:s');
        return $this->db->update('topics', $data, 'id = :id', ['id' => $id]);
    }

    public function delete(int $id): bool
    {
        return $this->db->delete('topics', 'id = ?', [$id]);
    }

    public function bulkCreate(array $rows): int
    {
        $count = 0;
        $this->db->beginTransaction();
        try {
            foreach ($rows as $row) {
                $this->create($row);
                $count++;
            }
            $this->db->commit();
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
        return $count;
    }

    public function updateStatus(int $id, string $status): bool
    {
        return $this->db->update('topics', ['status' => $status, 'updated_at' => date('Y-m-d H:i:s')], 'id = :id', ['id' => $id]);
    }

    public function pendingByProject(int $projectId): array
    {
        return $this->db->fetchAll(
            "SELECT t.*, pt.name AS page_type_name FROM topics t
             LEFT JOIN page_types pt ON pt.id = t.page_type_id
             WHERE t.project_id = ? AND t.status = 'pending'
             ORDER BY t.created_at ASC",
            [$projectId]
        );
    }

    public function parseCSV(string $filePath, int $projectId, int $pageTypeId): array
    {
        $rows   = [];
        $errors = [];

        if (($handle = fopen($filePath, 'r')) === false) {
            return ['rows' => [], 'errors' => ['Cannot open file']];
        }

        $headers = fgetcsv($handle);
        $headers = array_map('strtolower', array_map('trim', $headers));

        $lineNum = 1;
        while (($line = fgetcsv($handle)) !== false) {
            $lineNum++;
            if (count($line) !== count($headers)) {
                $errors[] = "Line {$lineNum}: column count mismatch";
                continue;
            }
            $row = array_combine($headers, $line);

            if (empty($row['title']) && empty($row['primary_keyword'])) {
                $errors[] = "Line {$lineNum}: missing title and primary_keyword";
                continue;
            }

            $rows[] = [
                'project_id'          => $projectId,
                'page_type_id'        => $pageTypeId,
                'title'               => trim($row['title'] ?? $row['primary_keyword']),
                'primary_keyword'     => trim($row['primary_keyword'] ?? $row['title']),
                'supporting_keywords' => trim($row['supporting_keywords'] ?? ''),
                'search_intent'       => in_array($row['search_intent'] ?? '', ['informational','navigational','transactional','commercial'])
                                         ? $row['search_intent'] : 'informational',
                'content_angle'       => trim($row['content_angle'] ?? ''),
                'target_word_count'   => (int)($row['target_word_count'] ?? 1200),
                'status'              => 'pending',
            ];
        }

        fclose($handle);
        return compact('rows', 'errors');
    }
}
