<?php

namespace Modules\Reports\Models;

use Core\Model;

class DailyReportModel extends Model
{
    protected $table = 'project_reports';

    private function baseSelect(): string
    {
        return "SELECT r.*, r.report_date AS date, r.manpower_count AS manpower,
                       COALESCE(c.company_name, CONCAT(u.first_name, ' ', u.last_name)) AS contractor_name,
                       (SELECT COUNT(*) FROM report_section_entries e WHERE e.report_id = r.id) as sections_filled,
                       (SELECT COUNT(*) FROM report_attachments a WHERE a.report_id = r.id) as photos_count
                FROM project_reports r
                INNER JOIN users u ON r.submitted_by = u.id
                LEFT JOIN contractors c ON r.contractor_id = c.id";
    }

    public function getForProject(int $projectId, array $filters, int $page, int $perPage): array
    {
        [$whereSql, $params] = $this->buildFilters($projectId, $filters);
        $offset = ($page - 1) * $perPage;

        return $this->query(
            $this->baseSelect() . " WHERE {$whereSql} ORDER BY r.report_date DESC LIMIT {$perPage} OFFSET {$offset}",
            $params
        );
    }

    public function countForProject(int $projectId, array $filters): int
    {
        [$whereSql, $params] = $this->buildFilters($projectId, $filters);
        $result = $this->queryOne("SELECT COUNT(*) as cnt FROM project_reports r WHERE {$whereSql}", $params);
        return (int)($result['cnt'] ?? 0);
    }

    private function buildFilters(int $projectId, array $filters): array
    {
        $where = ['r.project_id = ?'];
        $params = [$projectId];

        if (!empty($filters['date_from'])) {
            $where[] = 'r.report_date >= ?';
            $params[] = $filters['date_from'];
        }
        if (!empty($filters['date_to'])) {
            $where[] = 'r.report_date <= ?';
            $params[] = $filters['date_to'];
        }
        if (!empty($filters['contractor_id'])) {
            $where[] = 'r.contractor_id = ?';
            $params[] = $filters['contractor_id'];
        }
        if (!empty($filters['status'])) {
            $where[] = 'r.status = ?';
            $params[] = $filters['status'];
        }

        return [implode(' AND ', $where), $params];
    }

    public function getWithDetails(int $reportId): ?array
    {
        $report = $this->queryOne($this->baseSelect() . " WHERE r.id = ?", [$reportId]);
        if (!$report) {
            return null;
        }

        $sectionEntries = $this->query(
            "SELECT se.*, se.text_content AS content, s.title, s.section_type
             FROM report_section_entries se
             INNER JOIN report_template_sections s ON se.section_id = s.id
             WHERE se.report_id = ? ORDER BY s.sort_order ASC",
            [$reportId]
        );

        $workloadEntries = $this->query(
            "SELECT we.*, we.workload_item_id AS item_id, we.cumulative_qty AS cumulative,
                    wi.activity_name, wi.unit, wi.project_scope
             FROM report_workload_entries we
             INNER JOIN report_template_workload_items wi ON we.workload_item_id = wi.id
             WHERE we.report_id = ? ORDER BY wi.sort_order ASC",
            [$reportId]
        );

        $attachments = $this->query(
            "SELECT ra.*, s.title AS section_title FROM report_attachments ra
             LEFT JOIN report_template_sections s ON ra.section_id = s.id
             WHERE ra.report_id = ?",
            [$reportId]
        );

        return [
            'report' => $report,
            'section_entries' => $sectionEntries,
            'workload_entries' => $workloadEntries,
            'attachments' => $attachments,
        ];
    }

    public function getProgressSummary(int $projectId): array
    {
        return $this->query(
            "SELECT wi.id, wi.activity_name, wi.unit,
                    COALESCE(SUM(we.planned_qty), 0) as total_planned,
                    COALESCE(SUM(we.achieved_qty), 0) as total_achieved,
                    (COALESCE(SUM(we.achieved_qty), 0) / NULLIF(SUM(we.planned_qty), 0)) * 100 as percentage
             FROM report_template_workload_items wi
             INNER JOIN projects p ON p.report_template_id = wi.template_id
             LEFT JOIN report_workload_entries we ON we.workload_item_id = wi.id
             LEFT JOIN project_reports r ON we.report_id = r.id AND r.project_id = p.id AND r.status IN ('submitted', 'reviewed', 'approved')
             WHERE p.id = ?
             GROUP BY wi.id, wi.activity_name, wi.unit
             ORDER BY wi.sort_order ASC",
            [$projectId]
        );
    }

    public function getDailyProgressChart(int $projectId): array
    {
        return $this->query(
            "SELECT r.report_date AS date, SUM(we.achieved_qty) as total_achieved
             FROM project_reports r
             LEFT JOIN report_workload_entries we ON we.report_id = r.id
             WHERE r.project_id = ? AND r.status IN ('submitted', 'reviewed', 'approved')
             GROUP BY r.report_date
             ORDER BY r.report_date ASC",
            [$projectId]
        );
    }

    public function getPendingReports(int $userId): array
    {
        return $this->query(
            "SELECT r.*, r.report_date AS date, p.name as project_name
             FROM project_reports r
             INNER JOIN projects p ON r.project_id = p.id
             INNER JOIN project_managers pm ON pm.project_id = p.id
             WHERE pm.user_id = ? AND r.status = 'submitted'
             ORDER BY r.report_date DESC",
            [$userId]
        );
    }

    public function getContractorReports(int $contractorId, int $projectId): array
    {
        return $this->query(
            "SELECT * FROM project_reports WHERE contractor_id = ? AND project_id = ? ORDER BY report_date DESC",
            [$contractorId, $projectId]
        );
    }

    public function getContractorIdForUser(int $userId): ?int
    {
        $row = $this->queryOne("SELECT contractor_id FROM contractor_users WHERE user_id = ? LIMIT 1", [$userId]);
        return $row ? (int)$row['contractor_id'] : null;
    }

    public function canSubmitReport(int $userId, int $projectId, string $entityType): bool
    {
        if (in_array($entityType, ['org_user', 'super_admin'], true)) {
            return (bool)$this->queryOne("SELECT id FROM projects WHERE id = ?", [$projectId]);
        }

        if ($entityType === 'contractor_user') {
            $row = $this->queryOne(
                "SELECT pc.id FROM contractor_users cu
                 INNER JOIN project_contractors pc ON pc.contractor_id = cu.contractor_id
                 WHERE cu.user_id = ? AND pc.project_id = ? AND pc.status = 'accepted'",
                [$userId, $projectId]
            );
            return (bool)$row;
        }

        return false;
    }

    public function getActiveTemplateId(int $projectId): ?int
    {
        $row = $this->queryOne("SELECT report_template_id FROM projects WHERE id = ?", [$projectId]);
        return $row && $row['report_template_id'] ? (int)$row['report_template_id'] : null;
    }

    public function getPreviousCumulative(int $projectId, int $workloadItemId): float
    {
        $row = $this->queryOne(
            "SELECT we.cumulative_qty FROM report_workload_entries we
             INNER JOIN project_reports r ON we.report_id = r.id
             WHERE r.project_id = ? AND we.workload_item_id = ? AND r.status IN ('submitted','reviewed','approved')
             ORDER BY r.report_date DESC LIMIT 1",
            [$projectId, $workloadItemId]
        );
        return $row ? (float)$row['cumulative_qty'] : 0.0;
    }

    public function createReport(array $reportData, array $sectionEntries, array $workloadEntries, array $attachments): int
    {
        $this->db->beginTransaction();
        try {
            $reportId = $this->create($reportData);

            foreach ($sectionEntries as $sectionId => $content) {
                if ($content === '' || $content === null) {
                    continue;
                }
                $this->execute(
                    "INSERT INTO report_section_entries (report_id, section_id, text_content) VALUES (?, ?, ?)",
                    [$reportId, (int)$sectionId, $content]
                );
            }

            foreach ($workloadEntries as $itemId => $data) {
                $planned = (float)($data['planned'] ?? 0);
                $achieved = (float)($data['achieved'] ?? 0);
                $previousCumulative = $this->getPreviousCumulative($reportData['project_id'], (int)$itemId);
                $cumulative = $previousCumulative + $achieved;
                $this->execute(
                    "INSERT INTO report_workload_entries (report_id, workload_item_id, planned_qty, achieved_qty, cumulative_qty, notes, plan_change_comment) VALUES (?, ?, ?, ?, ?, ?, ?)",
                    [$reportId, (int)$itemId, $planned, $achieved, $cumulative, $data['notes'] ?? null, $data['plan_change_comment'] ?? null]
                );
            }

            foreach ($attachments as $attachment) {
                $this->execute(
                    "INSERT INTO report_attachments (report_id, section_id, file_name, file_path, file_size, mime_type, uploaded_by) VALUES (?, ?, ?, ?, ?, ?, ?)",
                    [$reportId, $attachment['section_id'], $attachment['name'], $attachment['path'], $attachment['size'], $attachment['mime'] ?? null, $reportData['submitted_by']]
                );
            }

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

    public function updateReviewStatus(int $reportId, string $status, string $notes, int $pmId): bool
    {
        return $this->execute(
            "UPDATE project_reports SET status = ?, pm_notes = ?, reviewed_by = ?, reviewed_at = NOW() WHERE id = ?",
            [$status, $notes, $pmId, $reportId]
        );
    }

    public function notifyPM(int $projectId, int $reportId): void
    {
        $project = $this->queryOne("SELECT name FROM projects WHERE id = ?", [$projectId]);
        $managers = $this->query("SELECT user_id FROM project_managers WHERE project_id = ?", [$projectId]);
        foreach ($managers as $manager) {
            createNotification(
                (int)$manager['user_id'],
                'report_submitted',
                'New Daily Report Submitted',
                'A new daily report was submitted for ' . ($project['name'] ?? 'a project') . '.',
                ['project_id' => $projectId, 'report_id' => $reportId]
            );
        }
    }

    public function getRecentPhotos(int $projectId, int $limit = 12): array
    {
        return $this->query(
            "SELECT ra.* FROM report_attachments ra
             INNER JOIN project_reports r ON ra.report_id = r.id
             WHERE r.project_id = ? AND ra.mime_type LIKE 'image%'
             ORDER BY ra.created_at DESC LIMIT {$limit}",
            [$projectId]
        );
    }
}
