<?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'])) {
            $where[] = "a.action = ?";
            $params[] = $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['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');
        $nameExpr = $hasDisplayName ? "COALESCE(u.display_name,'')" : "COALESCE(CONCAT(u.first_name,' ',u.last_name),'')";
        
        $sql = "SELECT
                    a.*,
                    COALESCE(NULLIF(a.actor_name,''), NULLIF($nameExpr,''), a.actor_didar_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 ON u.id = a.actor_user_id
                $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 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;
    }
}
