<?php
namespace App\Repositories;

use App\Database\Connection;
use PDO;

class AuditLogRepository
{
    private $db;

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

    public function create(array $data): int
    {
        $pdo = $this->db->getPdo();

        // Check if related_person_didar_id column exists
        $hasRelatedPerson = $this->db->hasColumn('audit_logs', 'related_person_didar_id');
        
        if ($hasRelatedPerson) {
            $stmt = $pdo->prepare(
                "INSERT INTO audit_logs (actor_user_id, actor_didar_id, actor_name, actor_ip, action, entity_type, entity_id, related_person_didar_id, snapshot_json)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
            );

            $stmt->execute([
                $data['actor_user_id'] ?? null,
                $data['actor_didar_id'] ?? null,
                $data['actor_name'] ?? null,
                $data['actor_ip'] ?? null,
                $data['action'] ?? '',
                $data['entity_type'] ?? '',
                $data['entity_id'] ?? null,
                $data['related_person_didar_id'] ?? null,
                $data['snapshot_json'] ?? null,
            ]);
        } else {
            // Fallback for older schema
            $stmt = $pdo->prepare(
                "INSERT INTO audit_logs (actor_user_id, actor_didar_id, actor_name, actor_ip, action, entity_type, entity_id, snapshot_json)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
            );

            $stmt->execute([
                $data['actor_user_id'] ?? null,
                $data['actor_didar_id'] ?? null,
                $data['actor_name'] ?? null,
                $data['actor_ip'] ?? null,
                $data['action'] ?? '',
                $data['entity_type'] ?? '',
                $data['entity_id'] ?? null,
                $data['snapshot_json'] ?? null,
            ]);
        }

        return (int)$pdo->lastInsertId();
    }

    public function list(array $filters = []): array
    {
        $pdo = $this->db->getPdo();

        $where = ["1=1"];
        $params = [];

        // Alias 'a' for audit_logs
        if (!empty($filters['action'])) {
            $this->applyActionFilter($where, $params, (string)$filters['action']);
        }
        if (!empty($filters['entity_type'])) {
            $where[] = "a.entity_type = ?";
            $params[] = $filters['entity_type'];
        }
        if (!empty($filters['entity_id'])) {
            $where[] = "a.entity_id = ?";
            $params[] = $filters['entity_id'];
        }
        if (!empty($filters['related_person_didar_id'])) {
            $where[] = "a.related_person_didar_id = ?";
            $params[] = $filters['related_person_didar_id'];
        }
        if (!empty($filters['actor_user_id'])) {
            $where[] = "a.actor_user_id = ?";
            $params[] = (int)$filters['actor_user_id'];
        }
        if (!empty($filters['actor_search'])) {
            $where[] = "(a.actor_name LIKE ? OR a.actor_didar_id LIKE ? OR CAST(a.actor_user_id AS CHAR) LIKE ?)";
            $term = '%' . $filters['actor_search'] . '%';
            $params[] = $term;
            $params[] = $term;
            $params[] = $term;
        }
        if (!empty($filters['ip_search'])) {
            $where[] = "a.actor_ip LIKE ?";
            $params[] = '%' . $filters['ip_search'] . '%';
        }
        if (!empty($filters['detail_search'])) {
            $where[] = "(a.snapshot_json LIKE ? OR a.entity_id LIKE ?)";
            $term = '%' . $filters['detail_search'] . '%';
            $params[] = $term;
            $params[] = $term;
        }
        if (!empty($filters['date_from'])) {
            $where[] = "a.created_at >= ?";
            $params[] = $filters['date_from'] . " 00:00:00";
        }
        if (!empty($filters['date_to'])) {
            $where[] = "a.created_at <= ?";
            $params[] = $filters['date_to'] . " 23:59:59";
        }

        // Person Search (Join required)
        $joinPerson = "LEFT JOIN persons p ON p.didar_contact_id = a.related_person_didar_id";
        if (!empty($filters['person_search'])) {
            $where[] = "(p.first_name LIKE ? OR p.last_name LIKE ? OR p.mobile_phone LIKE ?)";
            $term = '%' . $filters['person_search'] . '%';
            $params[] = $term;
            $params[] = $term;
            $params[] = $term;
        }

        $limit = isset($filters['limit']) ? (int)$filters['limit'] : 100;
        $offset = isset($filters['offset']) ? (int)$filters['offset'] : 0;
        if ($limit < 1) $limit = 100;
        if ($limit > 500) $limit = 500;
        if ($offset < 0) $offset = 0;

        $whereSql = implode(' AND ', $where);
        
        // Count
        $countSql = "SELECT COUNT(*) FROM audit_logs a $joinPerson WHERE $whereSql";
        $countStmt = $pdo->prepare($countSql);
        $countStmt->execute($params);
        $total = (int)$countStmt->fetchColumn();

        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $hasDidarUserId = $this->db->hasColumn('users', 'didar_user_id');
        $nameByIdExpr = $hasDisplayName ? "COALESCE(u_id.display_name,'')" : "COALESCE(CONCAT(u_id.first_name,' ',u_id.last_name),'')";
        $nameByDidarExpr = $hasDisplayName ? "COALESCE(u_didar.display_name,'')" : "COALESCE(CONCAT(u_didar.first_name,' ',u_didar.last_name),'')";
        $didarJoin = $hasDidarUserId ? "LEFT JOIN users u_didar ON u_didar.didar_user_id = a.actor_didar_id" : "LEFT JOIN users u_didar ON 1 = 0";
        
        $sql = "SELECT
                    a.*,
                    COALESCE(NULLIF(a.actor_name,''), NULLIF($nameByIdExpr,''), NULLIF($nameByDidarExpr,''), a.actor_didar_id, CONCAT('کاربر #', a.actor_user_id)) AS actor_name_resolved,
                    CONCAT(COALESCE(p.first_name,''), ' ', COALESCE(p.last_name,'')) AS related_person_name,
                    p.mobile_phone AS related_person_mobile
                FROM audit_logs a
                LEFT JOIN users u_id ON u_id.id = a.actor_user_id
                $didarJoin
                $joinPerson
                WHERE $whereSql
                ORDER BY a.created_at DESC, a.id DESC
                LIMIT $limit OFFSET $offset";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        return [
            'total' => $total,
            'limit' => $limit,
            'offset' => $offset,
            'rows' => $rows,
        ];
    }

    public function getFilterOptions(): array
    {
        $pdo = $this->db->getPdo();

        $recentSampleSql = "SELECT action, entity_type FROM audit_logs ORDER BY id DESC LIMIT 5000";

        $actions = $pdo->query(
            "SELECT action, COUNT(*) AS count_rows
             FROM ($recentSampleSql) recent_logs
             WHERE action IS NOT NULL AND action != ''
             GROUP BY action
             ORDER BY count_rows DESC, action ASC
             LIMIT 80"
        )->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $entities = $pdo->query(
            "SELECT entity_type, COUNT(*) AS count_rows
             FROM ($recentSampleSql) recent_logs
             WHERE entity_type IS NOT NULL AND entity_type != ''
             GROUP BY entity_type
             ORDER BY count_rows DESC, entity_type ASC
             LIMIT 80"
        )->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $hasDisplayName = $this->db->hasColumn('users', 'display_name');
        $hasUsername = $this->db->hasColumn('users', 'username');
        $fallbackNameParts = $hasUsername
            ? "NULLIF(email,''), NULLIF(username,'')"
            : "NULLIF(email,'')";
        $nameExpr = $hasDisplayName
            ? "COALESCE(NULLIF(display_name,''), NULLIF(TRIM(CONCAT(COALESCE(first_name,''), ' ', COALESCE(last_name,''))),''), $fallbackNameParts)"
            : "COALESCE(NULLIF(TRIM(CONCAT(COALESCE(first_name,''), ' ', COALESCE(last_name,''))),''), $fallbackNameParts)";
        $users = $pdo->query(
            "SELECT id, didar_user_id, $nameExpr AS name
             FROM users
             WHERE COALESCE(is_active, 1) = 1
             ORDER BY name ASC
             LIMIT 300"
        )->fetchAll(PDO::FETCH_ASSOC) ?: [];

        return [
            'actions' => $actions,
            'entities' => $entities,
            'users' => $users,
        ];
    }

    private function applyActionFilter(array &$where, array &$params, string $action): void
    {
        $action = trim($action);
        if ($action === '') {
            return;
        }

        $groups = [
            'group:core_write' => ["create", "update", "save", "edit", "delete", "recover", "owner_change"],
            'group:delete' => ["delete", "client:delete"],
            'group:owner_change' => ["owner_change", "client:owner_change"],
            'group:client_ui' => ["Click", "Client Event", "client:%"],
            'group:api' => ["API:%", "HTTP POST", "HTTP GET"],
            'group:telemetry' => ["AppTelemetry", "app_telemetry", "request_start", "request_success", "request_error", "request_failed"],
        ];

        if (!isset($groups[$action])) {
            $where[] = "a.action = ?";
            $params[] = $action;
            return;
        }

        $parts = [];
        foreach ($groups[$action] as $item) {
            if (strpos($item, '%') !== false) {
                $parts[] = "a.action LIKE ?";
            } else {
                $parts[] = "a.action = ?";
            }
            $params[] = $item;
        }
        $where[] = '(' . implode(' OR ', $parts) . ')';
    }

    public function findById(int $id): ?array
    {
        if ($id <= 0) {
            return null;
        }

        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM audit_logs WHERE id = ? LIMIT 1");
        $stmt->execute([$id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row ?: null;
    }
}
