<?php
namespace App\Repositories;

use App\Database\Connection;
use PDO;

class UserHierarchyRepository
{
    private $db;

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

    public function saveAssignment(
        int $userId,
        ?int $supervisorUserId,
        ?int $managerUserId,
        string $effectiveFrom,
        ?string $effectiveTo,
        ?float $supervisorCommissionRatePercent,
        ?float $managerCommissionRatePercent,
        ?int $createdByUserId
    ): int {
        $pdo = $this->db->getPdo();
        $startedTransaction = !$pdo->inTransaction();
        if ($startedTransaction) {
            $pdo->beginTransaction();
        }
        try {
            if ($effectiveTo === null) {
                $closeTo = date('Y-m-d', strtotime($effectiveFrom . ' -1 day'));
                $closeStmt = $pdo->prepare("
                    UPDATE user_hierarchy_assignments
                    SET effective_to = ?
                    WHERE user_id = ?
                      AND effective_to IS NULL
                      AND effective_from <= ?
                ");
                $closeStmt->execute([$closeTo, $userId, $effectiveFrom]);
            }

            $columns = ['user_id', 'supervisor_user_id', 'manager_user_id'];
            $values = [
                $userId,
                $supervisorUserId,
                $managerUserId,
            ];
            if ($this->db->hasColumn('user_hierarchy_assignments', 'supervisor_commission_rate_percent')) {
                $columns[] = 'supervisor_commission_rate_percent';
                $values[] = $supervisorCommissionRatePercent;
            }
            if ($this->db->hasColumn('user_hierarchy_assignments', 'manager_commission_rate_percent')) {
                $columns[] = 'manager_commission_rate_percent';
                $values[] = $managerCommissionRatePercent;
            }
            $columns = array_merge($columns, ['effective_from', 'effective_to', 'created_by_user_id']);
            $values = array_merge($values, [
                $effectiveFrom,
                $effectiveTo,
                $createdByUserId,
            ]);
            $stmt = $pdo->prepare("
                INSERT INTO user_hierarchy_assignments (" . implode(',', $columns) . ")
                VALUES (" . implode(',', array_fill(0, count($columns), '?')) . ")
            ");
            $stmt->execute($values);
            $id = (int)$pdo->lastInsertId();
            if ($startedTransaction) {
                $pdo->commit();
            }
            return $id;
        } catch (\Throwable $e) {
            if ($startedTransaction && $pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }

    public function listAssignments(int $userId = 0): array
    {
        $where = [];
        $params = [];
        if ($userId > 0) {
            $where[] = 'uha.user_id = ?';
            $params[] = $userId;
        }
        $sql = "
            SELECT uha.*,
                   COALESCE(u.display_name, CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,''))) AS user_name,
                   COALESCE(s.display_name, CONCAT(COALESCE(s.first_name,''), ' ', COALESCE(s.last_name,''))) AS supervisor_name,
                   COALESCE(m.display_name, CONCAT(COALESCE(m.first_name,''), ' ', COALESCE(m.last_name,''))) AS manager_name
            FROM user_hierarchy_assignments uha
            INNER JOIN users u ON u.id = uha.user_id
            LEFT JOIN users s ON s.id = uha.supervisor_user_id
            LEFT JOIN users m ON m.id = uha.manager_user_id
        ";
        if (!empty($where)) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }
        $sql .= ' ORDER BY uha.effective_from DESC, uha.id DESC';
        $stmt = $this->db->getPdo()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function findActiveForUser(int $userId, string $asOf): ?array
    {
        if ($userId <= 0 || $asOf === '') {
            return null;
        }
        $stmt = $this->db->getPdo()->prepare("
            SELECT *
            FROM user_hierarchy_assignments
            WHERE user_id = ?
              AND effective_from <= ?
              AND (effective_to IS NULL OR effective_to >= ?)
            ORDER BY effective_from DESC, id DESC
            LIMIT 1
        ");
        $stmt->execute([$userId, $asOf, $asOf]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function userExists(int $userId): bool
    {
        if ($userId <= 0) {
            return false;
        }
        $stmt = $this->db->getPdo()->prepare('SELECT COUNT(*) FROM users WHERE id = ?');
        $stmt->execute([$userId]);
        return (int)$stmt->fetchColumn() > 0;
    }
}
