<?php
namespace App\Models;
use App\Core\Model;

class ProfileField extends Model
{
    protected $table = 'profile_fields';
    protected $primaryKey = 'id';

    public function getActive($orderBy = 'sort_order', $direction = 'ASC')
    {
        $stmt = $this->db->prepare("SELECT * FROM {$this->table} WHERE is_active = 1 ORDER BY {$orderBy} {$direction}");
        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getAllWithOrder()
    {
        $stmt = $this->db->query("SELECT * FROM {$this->table} ORDER BY sort_order ASC, id ASC");
        return $stmt->fetchAll(\PDO::FETCH_OBJ);
    }

    public function getFieldValue($userId, $fieldId)
    {
        $stmt = $this->db->prepare("SELECT `value` FROM profile_field_values WHERE user_id = ? AND field_id = ?");
        $stmt->execute([$userId, $fieldId]);
        $result = $stmt->fetch(\PDO::FETCH_OBJ);
        return $result ? $result->value : null;
    }

    public function setFieldValue($userId, $fieldId, $value)
    {
        $stmt = $this->db->prepare("INSERT INTO profile_field_values (user_id, field_id, `value`) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)");
        return $stmt->execute([$userId, $fieldId, $value]);
    }

    public function getAllFieldValuesForUser($userId)
    {
        $stmt = $this->db->prepare("SELECT pf.id, pf.field_name, pfv.value FROM profile_field_values pfv JOIN profile_fields pf ON pf.id = pfv.field_id WHERE pfv.user_id = ?");
        $stmt->execute([$userId]);
        $result = [];
        while ($row = $stmt->fetch(\PDO::FETCH_OBJ)) {
            $result[$row->field_name] = $row->value;
        }
        return $result;
    }

    public function updateOrder($ids)
    {
        $stmt = $this->db->prepare("UPDATE {$this->table} SET sort_order = ? WHERE id = ?");
        foreach ($ids as $order => $id) {
            $stmt->execute([$order, (int)$id]);
        }
    }

    public function generateFieldName($label)
    {
        $name = preg_replace('/[^a-z0-9_]/', '', strtolower(str_replace(' ', '_', trim($label))));
        if (empty($name)) $name = 'field';
        $base = $name;
        $counter = 1;
        while (true) {
            $stmt = $this->db->prepare("SELECT COUNT(*) as c FROM {$this->table} WHERE field_name = ?");
            $stmt->execute([$name]);
            if ($stmt->fetch(\PDO::FETCH_OBJ)->c == 0) break;
            $name = $base . '_' . $counter++;
        }
        return $name;
    }
}
