<?php

namespace Modules\Reports\Models;

use Core\Model;

class ReportTemplateModel extends Model
{
    protected $table = 'report_templates';

    public function getAllForOrg(?int $orgId): array
    {
        $where = $orgId !== null ? 'WHERE t.org_id = ?' : '';
        $params = $orgId !== null ? [$orgId] : [];
        return $this->query(
            "SELECT t.*,
                    (SELECT COUNT(*) FROM report_template_sections s WHERE s.template_id = t.id AND s.is_active = 1) as sections_count,
                    (SELECT COUNT(*) FROM report_template_workload_items w WHERE w.template_id = t.id AND w.is_active = 1) as workload_items_count,
                    (SELECT COUNT(*) FROM projects p WHERE p.report_template_id = t.id) as linked_projects_count,
                    o.name as org_name
             FROM report_templates t
             LEFT JOIN organizations o ON t.org_id = o.id
             {$where}
             ORDER BY t.created_at DESC",
            $params
        );
    }

    public function getWithSections(int $id): ?array
    {
        $template = $this->find($id);
        if (!$template) {
            return null;
        }

        $sections = $this->query(
            "SELECT *, section_type AS type FROM report_template_sections WHERE template_id = ? AND is_active = 1 ORDER BY sort_order ASC, id ASC",
            [$id]
        );

        $workloadItems = $this->query(
            "SELECT * FROM report_template_workload_items WHERE template_id = ? AND is_active = 1 ORDER BY sort_order ASC, id ASC",
            [$id]
        );

        return [
            'template' => $template,
            'sections' => $sections,
            'workload_items' => $workloadItems,
        ];
    }

    public function createTemplate(array $templateData, array $sections, array $workloadItems): int
    {
        $this->db->beginTransaction();

        try {
            $templateId = $this->create($templateData);

            $sortOrder = 0;
            foreach ($sections as $section) {
                if (empty($section['title'])) {
                    continue;
                }
                $this->execute(
                    "INSERT INTO report_template_sections (template_id, title, section_type, is_required, sort_order, instructions) VALUES (?, ?, ?, ?, ?, ?)",
                    [$templateId, $section['title'], $section['type'] ?? 'text', !empty($section['is_required']) ? 1 : 0, $sortOrder++, $section['instructions'] ?? null]
                );
            }

            $sortOrder = 0;
            foreach ($workloadItems as $item) {
                if (empty($item['activity_name'])) {
                    continue;
                }
                $this->execute(
                    "INSERT INTO report_template_workload_items (template_id, activity_name, unit, project_scope, description, sort_order) VALUES (?, ?, ?, ?, ?, ?)",
                    [$templateId, $item['activity_name'], $item['unit'] ?? null, $item['project_scope'] ?? null, $item['description'] ?? null, $sortOrder++]
                );
            }

            $this->db->commit();
            return $templateId;
        } catch (\Exception $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function updateTemplate(int $id, array $templateData, array $sections, array $workloadItems): bool
    {
        $this->db->beginTransaction();

        try {
            $this->update($id, $templateData);
            $this->syncSections($id, $sections);
            $this->syncWorkloadItems($id, $workloadItems);

            $this->db->commit();
            return true;
        } catch (\Exception $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    /**
     * Updates existing sections in place (by id) and inserts new ones, rather than
     * deleting and re-inserting everything. A section removed from the form is only
     * hard-deleted if no report has ever been submitted against it — otherwise it is
     * deactivated so `report_section_entries` (ON DELETE CASCADE) doesn't silently
     * wipe out historical report content.
     */
    private function syncSections(int $templateId, array $sections): void
    {
        $existingIds = array_column(
            $this->query("SELECT id FROM report_template_sections WHERE template_id = ?", [$templateId]),
            'id'
        );

        $keptIds = [];
        $sortOrder = 0;
        foreach ($sections as $section) {
            if (empty($section['title'])) {
                continue;
            }
            $sectionId = (int)($section['id'] ?? 0);
            if ($sectionId && in_array($sectionId, $existingIds, true)) {
                $this->execute(
                    "UPDATE report_template_sections SET title = ?, section_type = ?, is_required = ?, sort_order = ?, instructions = ?, is_active = 1 WHERE id = ? AND template_id = ?",
                    [$section['title'], $section['type'] ?? 'text', !empty($section['is_required']) ? 1 : 0, $sortOrder++, $section['instructions'] ?? null, $sectionId, $templateId]
                );
                $keptIds[] = $sectionId;
            } else {
                $this->execute(
                    "INSERT INTO report_template_sections (template_id, title, section_type, is_required, sort_order, instructions) VALUES (?, ?, ?, ?, ?, ?)",
                    [$templateId, $section['title'], $section['type'] ?? 'text', !empty($section['is_required']) ? 1 : 0, $sortOrder++, $section['instructions'] ?? null]
                );
                $keptIds[] = (int)$this->db->lastInsertId();
            }
        }

        foreach (array_diff($existingIds, $keptIds) as $removedId) {
            $hasEntries = $this->queryOne("SELECT id FROM report_section_entries WHERE section_id = ? LIMIT 1", [$removedId]);
            if ($hasEntries) {
                $this->execute("UPDATE report_template_sections SET is_active = 0 WHERE id = ?", [$removedId]);
            } else {
                $this->execute("DELETE FROM report_template_sections WHERE id = ?", [$removedId]);
            }
        }
    }

    /**
     * Same update-in-place / insert-new / safe-remove strategy as syncSections(),
     * but for workload items — protects `report_workload_entries` from being
     * cascade-deleted when a template is edited after reports exist against it.
     */
    private function syncWorkloadItems(int $templateId, array $workloadItems): void
    {
        $existingIds = array_column(
            $this->query("SELECT id FROM report_template_workload_items WHERE template_id = ?", [$templateId]),
            'id'
        );

        $keptIds = [];
        $sortOrder = 0;
        foreach ($workloadItems as $item) {
            if (empty($item['activity_name'])) {
                continue;
            }
            $itemId = (int)($item['id'] ?? 0);
            if ($itemId && in_array($itemId, $existingIds, true)) {
                $this->execute(
                    "UPDATE report_template_workload_items SET activity_name = ?, unit = ?, project_scope = ?, description = ?, sort_order = ?, is_active = 1 WHERE id = ? AND template_id = ?",
                    [$item['activity_name'], $item['unit'] ?? null, $item['project_scope'] ?? null, $item['description'] ?? null, $sortOrder++, $itemId, $templateId]
                );
                $keptIds[] = $itemId;
            } else {
                $this->execute(
                    "INSERT INTO report_template_workload_items (template_id, activity_name, unit, project_scope, description, sort_order) VALUES (?, ?, ?, ?, ?, ?)",
                    [$templateId, $item['activity_name'], $item['unit'] ?? null, $item['project_scope'] ?? null, $item['description'] ?? null, $sortOrder++]
                );
                $keptIds[] = (int)$this->db->lastInsertId();
            }
        }

        foreach (array_diff($existingIds, $keptIds) as $removedId) {
            $hasEntries = $this->queryOne("SELECT id FROM report_workload_entries WHERE workload_item_id = ? LIMIT 1", [$removedId]);
            if ($hasEntries) {
                $this->execute("UPDATE report_template_workload_items SET is_active = 0 WHERE id = ?", [$removedId]);
            } else {
                $this->execute("DELETE FROM report_template_workload_items WHERE id = ?", [$removedId]);
            }
        }
    }

    public function delete(int $id): bool
    {
        return $this->execute("DELETE FROM report_templates WHERE id = ?", [$id]);
    }
}
