<?php

namespace App\Models;

use App\Core\Database;

/**
 * Threaded discussion attached to a KPI record.
 *
 * The reviewer's original review comment is NOT stored here — it stays
 * exactly where it has always lived, in `kpi_scheduling_entries.review_comment`,
 * and is rendered as the root of the thread. Everything after that root
 * (the submitter's explanation, the reviewer's reply to it, and so on) is
 * one row per message in `kpi_discussion`.
 *
 * That split is deliberate: it means no existing review comment had to be
 * migrated, copied, or rewritten, and there is exactly one source of truth
 * for the reviewer's comment.
 *
 * Posting to a thread NEVER changes the KPI entry's status, score, lock
 * state, or any other column on `kpi_scheduling_entries`. A locked record
 * stays locked; replying to it is purely conversational.
 */
class KpiDiscussion
{
    private const TYPE = 'scheduling';

    /** All messages on a record, oldest first (chronological chain). */
    public static function forEntry(int $entryId, string $kpiType = self::TYPE): array
    {
        try {
            $stmt = Database::connection()->prepare(
                "SELECT * FROM kpi_discussion
                 WHERE kpi_type = :t AND kpi_entry_id = :id
                 ORDER BY created_at ASC, id ASC"
            );
            $stmt->execute(['t' => $kpiType, 'id' => $entryId]);
            return $stmt->fetchAll(\PDO::FETCH_ASSOC);
        } catch (\Throwable $e) {
            // Table not present yet (migration not run) — degrade quietly so
            // the record page keeps rendering exactly as it did before.
            return [];
        }
    }

    /**
     * Append one message to the thread.
     *
     * @param string $authorRole 'submitter' | 'reviewer'
     * @return int  New message id, or 0 on failure.
     */
    public static function add(int $entryId, array $author, string $authorRole, string $body, string $kpiType = self::TYPE): int
    {
        $body = trim($body);
        if ($body === '') return 0;

        try {
            $db = Database::connection();
            $db->prepare(
                "INSERT INTO kpi_discussion
                   (kpi_type, kpi_entry_id, author_type, author_id, author_name, author_role, body)
                 VALUES (:t, :eid, :at, :aid, :an, :ar, :body)"
            )->execute([
                't'    => $kpiType,
                'eid'  => $entryId,
                'at'   => !empty($author['is_super_admin']) ? 'superadmin' : $author['user_type'],
                'aid'  => (int)$author['user_id'],
                'an'   => $author['name'],
                'ar'   => $authorRole === 'reviewer' ? 'reviewer' : 'submitter',
                'body' => $body,
            ]);
            return (int)$db->lastInsertId();
        } catch (\Throwable $e) {
            return 0;
        }
    }

    /** How many messages are on a record's thread. */
    public static function countForEntry(int $entryId, string $kpiType = self::TYPE): int
    {
        try {
            $stmt = Database::connection()->prepare(
                "SELECT COUNT(*) FROM kpi_discussion WHERE kpi_type = :t AND kpi_entry_id = :id"
            );
            $stmt->execute(['t' => $kpiType, 'id' => $entryId]);
            return (int)$stmt->fetchColumn();
        } catch (\Throwable $e) {
            return 0;
        }
    }

    /**
     * The most recent message on a record written by the given side.
     * Used by the header to show "X replied to your review" style chips.
     */
    public static function latestByRole(int $entryId, string $authorRole, string $kpiType = self::TYPE): ?array
    {
        try {
            $stmt = Database::connection()->prepare(
                "SELECT * FROM kpi_discussion
                 WHERE kpi_type = :t AND kpi_entry_id = :id AND author_role = :r
                 ORDER BY created_at DESC, id DESC LIMIT 1"
            );
            $stmt->execute(['t' => $kpiType, 'id' => $entryId, 'r' => $authorRole]);
            $row = $stmt->fetch(\PDO::FETCH_ASSOC);
            return $row ?: null;
        } catch (\Throwable $e) {
            return null;
        }
    }

    /**
     * The KPI record ids, among the given ids, whose newest message was written
     * by $authorRole — i.e. threads currently "waiting" on the other side.
     * Newest activity first, so the caller can link straight to the most
     * recent one instead of dumping the user on a list page.
     */
    public static function entryIdsAwaitingResponse(array $entryIds, string $lastAuthorRole, string $kpiType = self::TYPE): array
    {
        if (empty($entryIds)) return [];
        try {
            $ids = implode(',', array_map('intval', $entryIds));
            $sql = "SELECT x.kpi_entry_id FROM (
                      SELECT d.kpi_entry_id,
                             MAX(d.created_at) AS last_at,
                             SUBSTRING_INDEX(GROUP_CONCAT(d.author_role ORDER BY d.created_at DESC, d.id DESC), ',', 1) AS last_role
                        FROM kpi_discussion d
                       WHERE d.kpi_type = :t AND d.kpi_entry_id IN ($ids)
                    GROUP BY d.kpi_entry_id
                    ) x WHERE x.last_role = :r ORDER BY x.last_at DESC";
            $stmt = Database::connection()->prepare($sql);
            $stmt->execute(['t' => $kpiType, 'r' => $lastAuthorRole]);
            return array_map('intval', $stmt->fetchAll(\PDO::FETCH_COLUMN));
        } catch (\Throwable $e) {
            return [];
        }
    }

    /**
     * Count of records, among the given entry ids, whose newest message was
     * written by $authorRole — i.e. threads currently "waiting" on the other
     * side. Used for the notification chips.
     */
    public static function entriesAwaitingResponse(array $entryIds, string $lastAuthorRole, string $kpiType = self::TYPE): int
    {
        if (empty($entryIds)) return 0;
        try {
            $ids = implode(',', array_map('intval', $entryIds));
            $sql = "SELECT COUNT(*) FROM (
                      SELECT d.kpi_entry_id,
                             SUBSTRING_INDEX(GROUP_CONCAT(d.author_role ORDER BY d.created_at DESC, d.id DESC), ',', 1) AS last_role
                        FROM kpi_discussion d
                       WHERE d.kpi_type = :t AND d.kpi_entry_id IN ($ids)
                    GROUP BY d.kpi_entry_id
                    ) x WHERE x.last_role = :r";
            $stmt = Database::connection()->prepare($sql);
            $stmt->execute(['t' => $kpiType, 'r' => $lastAuthorRole]);
            return (int)$stmt->fetchColumn();
        } catch (\Throwable $e) {
            return 0;
        }
    }
}
