<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;

final class CustomFieldModel extends Model
{
    public function all(?string $module = null): array
    {
        if ($module) {
            $stmt = $this->db->prepare("
                SELECT *
                FROM custom_fields
                WHERE module_name = :module_name
                ORDER BY module_name ASC, sort_order ASC, id ASC
            ");
            $stmt->execute([':module_name' => $module]);
            return $stmt->fetchAll();
        }

        return $this->db->query("
            SELECT *
            FROM custom_fields
            ORDER BY module_name ASC, sort_order ASC, id ASC
        ")->fetchAll();
    }

    public function activeByModule(string $module): array
    {
        $stmt = $this->db->prepare("
            SELECT *
            FROM custom_fields
            WHERE module_name = :module_name
              AND is_active = 1
            ORDER BY sort_order ASC, id ASC
        ");
        $stmt->execute([':module_name' => $module]);
        return $stmt->fetchAll();
    }

    public function findById(int $id): ?array
    {
        $stmt = $this->db->prepare("
            SELECT *
            FROM custom_fields
            WHERE id = :id
            LIMIT 1
        ");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function create(array $data): int
    {
        $stmt = $this->db->prepare("
            INSERT INTO custom_fields (
                module_name, field_key, label_en, label_ar, field_type, field_options,
                placeholder_en, placeholder_ar, help_text_en, help_text_ar,
                is_required, is_active, sort_order
            ) VALUES (
                :module_name, :field_key, :label_en, :label_ar, :field_type, :field_options,
                :placeholder_en, :placeholder_ar, :help_text_en, :help_text_ar,
                :is_required, :is_active, :sort_order
            )
        ");
        $stmt->execute([
            ':module_name' => $data['module_name'],
            ':field_key' => $data['field_key'],
            ':label_en' => $data['label_en'],
            ':label_ar' => $data['label_ar'],
            ':field_type' => $data['field_type'],
            ':field_options' => $data['field_options'] ?: null,
            ':placeholder_en' => $data['placeholder_en'] ?: null,
            ':placeholder_ar' => $data['placeholder_ar'] ?: null,
            ':help_text_en' => $data['help_text_en'] ?: null,
            ':help_text_ar' => $data['help_text_ar'] ?: null,
            ':is_required' => $data['is_required'] ?? 0,
            ':is_active' => $data['is_active'] ?? 1,
            ':sort_order' => $data['sort_order'] ?? 0,
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function update(int $id, array $data): void
    {
        $stmt = $this->db->prepare("
            UPDATE custom_fields
            SET module_name = :module_name,
                field_key = :field_key,
                label_en = :label_en,
                label_ar = :label_ar,
                field_type = :field_type,
                field_options = :field_options,
                placeholder_en = :placeholder_en,
                placeholder_ar = :placeholder_ar,
                help_text_en = :help_text_en,
                help_text_ar = :help_text_ar,
                is_required = :is_required,
                is_active = :is_active,
                sort_order = :sort_order
            WHERE id = :id
        ");
        $stmt->execute([
            ':id' => $id,
            ':module_name' => $data['module_name'],
            ':field_key' => $data['field_key'],
            ':label_en' => $data['label_en'],
            ':label_ar' => $data['label_ar'],
            ':field_type' => $data['field_type'],
            ':field_options' => $data['field_options'] ?: null,
            ':placeholder_en' => $data['placeholder_en'] ?: null,
            ':placeholder_ar' => $data['placeholder_ar'] ?: null,
            ':help_text_en' => $data['help_text_en'] ?: null,
            ':help_text_ar' => $data['help_text_ar'] ?: null,
            ':is_required' => $data['is_required'] ?? 0,
            ':is_active' => $data['is_active'] ?? 1,
            ':sort_order' => $data['sort_order'] ?? 0,
        ]);
    }

    public function valuesForEntity(string $module, int $entityId): array
    {
        $stmt = $this->db->prepare("
            SELECT cf.field_key, cfv.value_text
            FROM custom_field_values cfv
            INNER JOIN custom_fields cf ON cf.id = cfv.custom_field_id
            WHERE cfv.module_name = :module_name
              AND cfv.entity_id = :entity_id
        ");
        $stmt->execute([
            ':module_name' => $module,
            ':entity_id' => $entityId,
        ]);

        $result = [];
        foreach ($stmt->fetchAll() as $row) {
            $result[(string) $row['field_key']] = (string) ($row['value_text'] ?? '');
        }
        return $result;
    }

    public function saveValues(string $module, int $entityId, array $fields, array $input): void
    {
        $stmt = $this->db->prepare("
            INSERT INTO custom_field_values (
                custom_field_id, module_name, entity_id, value_text
            ) VALUES (
                :custom_field_id, :module_name, :entity_id, :value_text
            )
            ON DUPLICATE KEY UPDATE value_text = VALUES(value_text)
        ");

        foreach ($fields as $field) {
            $fieldKey = (string) $field['field_key'];
            $value = $input[$fieldKey] ?? null;

            if (is_array($value)) {
                $value = implode(',', $value);
            }

            $stmt->execute([
                ':custom_field_id' => (int) $field['id'],
                ':module_name' => $module,
                ':entity_id' => $entityId,
                ':value_text' => $value !== null ? (string) $value : null,
            ]);
        }
    }
}
