<?php
namespace App\Repositories;

use App\Database\Connection;
use PDO;

/**
 * Field Configuration Repository
 * Manages field properties: required, editable, role permissions
 */
class FieldConfigRepository
{
    private Connection $db;
    private static $cache = [];

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

    /**
     * Get all field configurations
     */
    public function getAll(bool $includeInactive = false): array
    {
        $pdo = $this->db->getPdo();
        $sql = "SELECT * FROM field_configurations";
        
        if (!$includeInactive) {
            $sql .= " WHERE is_active = 1";
        }
        
        $sql .= " ORDER BY entity_type ASC, sort_order ASC";
        return $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * Get configuration by field name
     * ✅ Uses field_name as identifier (not ID)
     */
    public function getByFieldName(string $fieldName): ?array
    {
        // Check cache first
        if (isset(self::$cache[$fieldName])) {
            return self::$cache[$fieldName];
        }

        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM field_configurations WHERE field_name = ?");
        $stmt->execute([$fieldName]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        
        // Decode JSON fields
        if ($row) {
            $row['edit_allow_roles'] = json_decode($row['edit_allow_roles'] ?? '[]', true);
            $row['delete_allow_roles'] = json_decode($row['delete_allow_roles'] ?? '[]', true);
            $row['view_allow_roles'] = json_decode($row['view_allow_roles'] ?? '[]', true);
            $row['conditional_logic'] = json_decode($row['conditional_logic'] ?? '[]', true);
            self::$cache[$fieldName] = $row;
        }
        
        return $row ?: null;
    }

    /**
     * Get all configurations for an entity type
     */
    public function getByEntity(string $entityType, bool $includeInactive = false): array
    {
        $pdo = $this->db->getPdo();
        $sql = "SELECT * FROM field_configurations WHERE entity_type = ?";
        
        if (!$includeInactive) {
            $sql .= " AND is_active = 1";
        }
        
        $sql .= " ORDER BY group_name ASC, sort_order ASC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$entityType]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        
        // Decode JSON fields
        foreach ($rows as &$row) {
            $row['edit_allow_roles'] = json_decode($row['edit_allow_roles'] ?? '[]', true);
            $row['delete_allow_roles'] = json_decode($row['delete_allow_roles'] ?? '[]', true);
            $row['view_allow_roles'] = json_decode($row['view_allow_roles'] ?? '[]', true);
            $row['conditional_logic'] = json_decode($row['conditional_logic'] ?? '[]', true);
        }
        
        return $rows;
    }

    /**
     * Get configurations by module name
     */
    public function getByModule(string $moduleName, bool $includeInactive = false): array
    {
        $pdo = $this->db->getPdo();
        $sql = "SELECT * FROM field_configurations WHERE module_name = ?";
        
        if (!$includeInactive) {
            $sql .= " AND is_active = 1";
        }
        
        $sql .= " ORDER BY group_name ASC, sort_order ASC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$moduleName]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        
        // Decode JSON fields
        foreach ($rows as &$row) {
            $row['edit_allow_roles'] = json_decode($row['edit_allow_roles'] ?? '[]', true);
            $row['delete_allow_roles'] = json_decode($row['delete_allow_roles'] ?? '[]', true);
            $row['view_allow_roles'] = json_decode($row['view_allow_roles'] ?? '[]', true);
            $row['conditional_logic'] = json_decode($row['conditional_logic'] ?? '[]', true);
        }
        
        return $rows;
    }

    /**
     * Get all distinct group names for an entity
     */
    public function getGroupsByEntity(string $entityType): array
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT DISTINCT group_name FROM field_configurations WHERE entity_type = ? AND group_name IS NOT NULL AND group_name != '' ORDER BY group_name");
        $stmt->execute([$entityType]);
        return $stmt->fetchAll(PDO::FETCH_COLUMN) ?: [];
    }

    /**
     * Get editable fields by user role
     */
    public function getEditableByRole(string $entityType, string $role): array
    {
        $all = $this->getByEntity($entityType);
        $editable = [];
        
        foreach ($all as $config) {
            if ($config['is_editable']) {
                $roles = json_decode($config['edit_allow_roles'] ?? '[]', true);
                // Empty roles = everyone can edit
                if (empty($roles) || in_array($role, $roles)) {
                    $editable[] = $config;
                }
            }
        }
        
        return $editable;
    }

    /**
     * Save or update a field configuration
     */
    public function save(array $data): string
    {
        $pdo = $this->db->getPdo();
        
        $fieldName = $data['field_name'] ?? '';
        if (empty($fieldName)) {
            throw new \Exception('field_name is required');
        }

        // Encode arrays if needed
        foreach (['edit_allow_roles', 'delete_allow_roles', 'view_allow_roles', 'conditional_logic'] as $jsonField) {
            if (is_array($data[$jsonField] ?? null)) {
                $data[$jsonField] = json_encode($data[$jsonField]);
            }
        }

        // Check if exists
        $stmt = $pdo->prepare("SELECT id FROM field_configurations WHERE field_name = ?");
        $stmt->execute([$fieldName]);
        $existing = $stmt->fetch();

        // All columns that can be updated
        $allColumns = [
            'field_label', 'field_type', 'entity_type', 'module_name',
            'is_required', 'is_editable', 'is_editable_after_create', 'is_active',
            'edit_allow_roles', 'delete_allow_roles', 'view_allow_roles',
            'lookup_group_code', 'sort_order', 'help_text', 'default_value',
            'validation_pattern', 'validation_message', 'conditional_logic', 'group_name'
        ];

        if ($existing) {
            // Update
            $updateFields = [];
            $params = [];
            
            foreach ($allColumns as $col) {
                if (array_key_exists($col, $data)) {
                    $updateFields[] = "$col = ?";
                    $params[] = $data[$col];
                }
            }
            
            if (!empty($updateFields)) {
                $params[] = $fieldName;
                $sql = "UPDATE field_configurations SET " . implode(', ', $updateFields) . " WHERE field_name = ?";
                $pdo->prepare($sql)->execute($params);
            }
        } else {
            // Insert
            $sql = "INSERT INTO field_configurations 
                   (field_name, field_label, field_type, entity_type, module_name,
                    is_required, is_editable, is_editable_after_create, is_active, is_system, 
                    edit_allow_roles, delete_allow_roles, view_allow_roles,
                    lookup_group_code, sort_order, help_text, default_value, 
                    validation_pattern, validation_message, conditional_logic, group_name)
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute([
                $fieldName,
                $data['field_label'] ?? '',
                $data['field_type'] ?? 'text',
                $data['entity_type'] ?? 'deal',
                $data['module_name'] ?? null,
                $data['is_required'] ?? 0,
                $data['is_editable'] ?? 1,
                $data['is_editable_after_create'] ?? 1,
                $data['is_active'] ?? 1,
                $data['is_system'] ?? 0,
                $data['edit_allow_roles'] ?? null,
                $data['delete_allow_roles'] ?? null,
                $data['view_allow_roles'] ?? null,
                $data['lookup_group_code'] ?? null,
                $data['sort_order'] ?? 0,
                $data['help_text'] ?? null,
                $data['default_value'] ?? null,
                $data['validation_pattern'] ?? null,
                $data['validation_message'] ?? null,
                $data['conditional_logic'] ?? null,
                $data['group_name'] ?? null
            ]);
        }

        // Clear cache
        unset(self::$cache[$fieldName]);
        
        return $fieldName;
    }

    /**
     * Delete a field configuration
     */
    public function delete(string $fieldName): void
    {
        $config = $this->getByFieldName($fieldName);
        
        // Prevent deletion of system fields
        if ($config && $config['is_system']) {
            throw new \Exception("Cannot delete system field: $fieldName");
        }

        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("DELETE FROM field_configurations WHERE field_name = ?");
        $stmt->execute([$fieldName]);
        
        unset(self::$cache[$fieldName]);
    }

    /**
     * Update edit role permissions
     */
    public function updateEditRoles(string $fieldName, array $roles): void
    {
        $pdo = $this->db->getPdo();
        $rolesJson = json_encode($roles);
        $stmt = $pdo->prepare("UPDATE field_configurations SET edit_allow_roles = ? WHERE field_name = ?");
        $stmt->execute([$rolesJson, $fieldName]);
        
        unset(self::$cache[$fieldName]);
    }

    /**
     * Initialize default field configurations if not exist
     */
    public function seedDefaults(): void
    {
        $defaults = [
            [
                'field_name' => 'payment_method',
                'field_label' => 'نحوه پرداخت',
                'field_type' => 'select',
                'entity_type' => 'deal',
                'is_required' => 0,
                'is_editable' => 1,
                'is_active' => 1,
                'is_system' => 0,
                'edit_allow_roles' => json_encode(['admin', 'crm_specialist', 'agent']),
                'lookup_group_code' => 'payment_methods',
                'sort_order' => 1
            ],
            [
                'field_name' => 'payment_card_last4',
                'field_label' => 'آخر ۴ رقم کارت',
                'field_type' => 'text',
                'entity_type' => 'deal',
                'is_required' => 0,
                'is_editable' => 1,
                'is_active' => 1,
                'is_system' => 0,
                'edit_allow_roles' => json_encode(['admin', 'crm_specialist']),
                'lookup_group_code' => null,
                'sort_order' => 2
            ],
            [
                'field_name' => 'destination_bank',
                'field_label' => 'بانک مقصد پرداخت',
                'field_type' => 'select',
                'entity_type' => 'deal',
                'is_required' => 0,
                'is_editable' => 1,
                'is_active' => 1,
                'is_system' => 0,
                'edit_allow_roles' => json_encode(['admin', 'crm_specialist']),
                'lookup_group_code' => 'payment_destination_banks',
                'sort_order' => 3
            ],
            [
                'field_name' => 'payment_delete',
                'field_label' => 'حذف پرداخت',
                'field_type' => 'text',
                'entity_type' => 'deal',
                'is_required' => 0,
                'is_editable' => 1,
                'is_active' => 1,
                'is_system' => 0,
                'edit_allow_roles' => json_encode(['admin']),
                'lookup_group_code' => null,
                'sort_order' => 4
            ]
        ];

        foreach ($defaults as $default) {
            // Check if already exists
            if (!$this->getByFieldName($default['field_name'])) {
                $pdo = $this->db->getPdo();
                $sql = "INSERT INTO field_configurations 
                       (field_name, field_label, field_type, entity_type, is_required, 
                        is_editable, is_active, is_system, edit_allow_roles, lookup_group_code, sort_order)
                       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                
                $stmt = $pdo->prepare($sql);
                $stmt->execute([
                    $default['field_name'],
                    $default['field_label'],
                    $default['field_type'],
                    $default['entity_type'],
                    $default['is_required'],
                    $default['is_editable'],
                    $default['is_active'],
                    $default['is_system'],
                    $default['edit_allow_roles'],
                    $default['lookup_group_code'],
                    $default['sort_order']
                ]);
            }
        }
    }
}

