<?php
namespace App\Repositories;

use App\Database\Connection;
use PDO;

class LookupRepository
{
    private Connection $db;

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

    public function getGroups(bool $includeInactive = false): array
    {
        $pdo = $this->db->getPdo();
        $sql = "SELECT id, code, title, description, is_active, sort_order, is_system
                FROM lookup_groups";
        if (!$includeInactive) {
            $sql .= " WHERE is_active = 1";
        }
        $sql .= " ORDER BY sort_order ASC, title ASC";
        return $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function getGroupByCode(string $code): ?array
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM lookup_groups WHERE code = ?");
        $stmt->execute([$code]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function getItemsByGroupCode(string $groupCode, bool $includeInactive = false): array
    {
        $group = $this->getGroupByCode($groupCode);
        if (!$group) {
            return [];
        }
        return $this->getItemsByGroupId((int)$group['id'], $includeInactive);
    }

    public function getItemsByGroupId(int $groupId, bool $includeInactive = false): array
    {
        $pdo = $this->db->getPdo();
        $sql = "SELECT i.id, i.group_id, i.parent_id, i.code, i.title, i.value,
                       i.meta_json, i.is_active, i.sort_order, i.is_system,
                       p.value AS parent_value, p.title AS parent_title
                FROM lookup_items i
                LEFT JOIN lookup_items p ON p.id = i.parent_id
                WHERE i.group_id = ?";
        if (!$includeInactive) {
            $sql .= " AND i.is_active = 1";
        }
        $sql .= " ORDER BY i.sort_order ASC, i.title ASC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$groupId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function getAllLookups(bool $includeInactive = false): array
    {
        $pdo = $this->db->getPdo();
        $sql = "SELECT g.id AS group_id, g.code AS group_code, g.title AS group_title,
                       g.is_active AS group_active, g.sort_order AS group_sort,
                       i.id AS item_id, i.parent_id, i.code AS item_code, i.title AS item_title,
                       i.value AS item_value, i.meta_json, i.is_active AS item_active,
                       i.sort_order AS item_sort, p.value AS parent_value, p.title AS parent_title
                FROM lookup_groups g
                LEFT JOIN lookup_items i ON i.group_id = g.id
                LEFT JOIN lookup_items p ON p.id = i.parent_id";
        if (!$includeInactive) {
            $sql .= " WHERE g.is_active = 1 AND (i.is_active = 1 OR i.id IS NULL)";
        }
        $sql .= " ORDER BY g.sort_order ASC, g.title ASC, i.sort_order ASC, i.title ASC";
        $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $result = [];
        foreach ($rows as $row) {
            $groupCode = $row['group_code'];
            if (!isset($result[$groupCode])) {
                $result[$groupCode] = [
                    'group' => [
                        'id' => (int)$row['group_id'],
                        'code' => $groupCode,
                        'title' => $row['group_title'],
                        'is_active' => (int)$row['group_active'],
                        'sort_order' => (int)$row['group_sort']
                    ],
                    'items' => []
                ];
            }
            if (!empty($row['item_id'])) {
                $result[$groupCode]['items'][] = [
                    'id' => (int)$row['item_id'],
                    'group_id' => (int)$row['group_id'],
                    'parent_id' => $row['parent_id'] ? (int)$row['parent_id'] : null,
                    'code' => $row['item_code'],
                    'title' => $row['item_title'],
                    'value' => $row['item_value'],
                    'meta_json' => $row['meta_json'],
                    'is_active' => (int)$row['item_active'],
                    'sort_order' => (int)$row['item_sort'],
                    'parent_value' => $row['parent_value'],
                    'parent_title' => $row['parent_title']
                ];
            }
        }
        return $result;
    }

    public function saveGroup(array $data): int
    {
        $pdo = $this->db->getPdo();
        $id = isset($data['id']) ? (int)$data['id'] : 0;
        $code = $data['code'] ?? '';
        $title = $data['title'] ?? '';
        $description = $data['description'] ?? null;
        $sortOrder = isset($data['sort_order']) ? (int)$data['sort_order'] : 0;
        $isActive = isset($data['is_active']) ? (int)$data['is_active'] : 1;

        if ($id > 0) {
            $stmt = $pdo->prepare("UPDATE lookup_groups
                                   SET code = ?, title = ?, description = ?, sort_order = ?, is_active = ?, updated_at = NOW()
                                   WHERE id = ?");
            $stmt->execute([$code, $title, $description, $sortOrder, $isActive, $id]);
            return $id;
        }

        $stmt = $pdo->prepare("INSERT INTO lookup_groups (code, title, description, sort_order, is_active, is_system, created_at, updated_at)
                               VALUES (?, ?, ?, ?, ?, 0, NOW(), NOW())");
        $stmt->execute([$code, $title, $description, $sortOrder, $isActive]);
        return (int)$pdo->lastInsertId();
    }

    public function saveItem(array $data): int
    {
        $pdo = $this->db->getPdo();
        $id = isset($data['id']) ? (int)$data['id'] : 0;
        $groupId = isset($data['group_id']) ? (int)$data['group_id'] : 0;
        $parentId = isset($data['parent_id']) ? (int)$data['parent_id'] : null;
        $code = $data['code'] ?? null;
        $title = $data['title'] ?? '';
        $value = $data['value'] ?? '';
        $metaJson = $data['meta_json'] ?? null;
        $sortOrder = isset($data['sort_order']) ? (int)$data['sort_order'] : 0;
        $isActive = isset($data['is_active']) ? (int)$data['is_active'] : 1;

        if ($value === '') {
            $value = $title;
        }

        if ($id > 0) {
            $stmt = $pdo->prepare("UPDATE lookup_items
                                   SET parent_id = ?, code = ?, title = ?, value = ?, meta_json = ?, sort_order = ?, is_active = ?, updated_at = NOW()
                                   WHERE id = ?");
            $stmt->execute([$parentId, $code, $title, $value, $metaJson, $sortOrder, $isActive, $id]);
            return $id;
        }

        $stmt = $pdo->prepare("INSERT INTO lookup_items (group_id, parent_id, code, title, value, meta_json, sort_order, is_active, is_system, created_at, updated_at)
                               VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0, NOW(), NOW())");
        $stmt->execute([$groupId, $parentId, $code, $title, $value, $metaJson, $sortOrder, $isActive]);
        return (int)$pdo->lastInsertId();
    }

    public function deleteGroup(int $id): void
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("DELETE FROM lookup_groups WHERE id = ?");
        $stmt->execute([$id]);
    }

    public function deleteItem(int $id): void
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("DELETE FROM lookup_items WHERE id = ?");
        $stmt->execute([$id]);
    }
}
