<?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 this save just sets/updates the rate of an EXISTING open membership
            // (same supervisor, or same manager for a pure-management row), update that
            // row in place and keep its original effective_from. This makes a rate change
            // apply from when the membership began, instead of splitting the timeline into
            // an "ended" period plus a new one.
            if ($effectiveTo === null) {
                $matchId = 0;
                if ($supervisorUserId !== null && $supervisorUserId > 0) {
                    $m = $pdo->prepare("SELECT id FROM user_hierarchy_assignments WHERE user_id = ? AND effective_to IS NULL AND supervisor_user_id = ? ORDER BY effective_from ASC, id ASC LIMIT 1");
                    $m->execute([$userId, $supervisorUserId]);
                    $matchId = (int)$m->fetchColumn();
                } elseif ($managerUserId !== null && $managerUserId > 0) {
                    $m = $pdo->prepare("SELECT id FROM user_hierarchy_assignments WHERE user_id = ? AND effective_to IS NULL AND manager_user_id = ? AND COALESCE(supervisor_user_id, 0) = 0 ORDER BY effective_from ASC, id ASC LIMIT 1");
                    $m->execute([$userId, $managerUserId]);
                    $matchId = (int)$m->fetchColumn();
                }
                if ($matchId > 0) {
                    $set = ['supervisor_user_id = ?', 'manager_user_id = ?'];
                    $vals = [$supervisorUserId, $managerUserId];
                    if ($this->db->hasColumn('user_hierarchy_assignments', 'supervisor_commission_rate_percent')) {
                        $set[] = 'supervisor_commission_rate_percent = ?';
                        $vals[] = $supervisorCommissionRatePercent;
                    }
                    if ($this->db->hasColumn('user_hierarchy_assignments', 'manager_commission_rate_percent')) {
                        $set[] = 'manager_commission_rate_percent = ?';
                        $vals[] = $managerCommissionRatePercent;
                    }
                    $vals[] = $matchId;
                    $pdo->prepare("UPDATE user_hierarchy_assignments SET " . implode(', ', $set) . " WHERE id = ?")->execute($vals);
                    // Collapse any other stray open rows for this user so only one stays open.
                    $closeOthers = $pdo->prepare("UPDATE user_hierarchy_assignments SET effective_to = ? WHERE user_id = ? AND effective_to IS NULL AND id <> ?");
                    $closeOthers->execute([date('Y-m-d', strtotime($effectiveFrom . ' -1 day')), $userId, $matchId]);
                    if ($startedTransaction) {
                        $pdo->commit();
                    }
                    return $matchId;
                }
            }

            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 deleteTeamAssignments(int $ownerUserId, string $teamType): int
    {
        if ($ownerUserId <= 0) {
            return 0;
        }

        // Soft-delete: close the open memberships (set effective_to) instead of deleting
        // the rows, so a removed team stays in the history view. GREATEST guards against an
        // inverted range when a membership was future-dated.
        $closeTo = date('Y-m-d');
        if ($teamType === 'management') {
            $stmt = $this->db->getPdo()->prepare("
                UPDATE user_hierarchy_assignments
                SET effective_to = GREATEST(effective_from, ?)
                WHERE manager_user_id = ?
                  AND (supervisor_user_id IS NULL OR supervisor_user_id = 0)
                  AND effective_to IS NULL
            ");
        } else {
            $stmt = $this->db->getPdo()->prepare("
                UPDATE user_hierarchy_assignments
                SET effective_to = GREATEST(effective_from, ?)
                WHERE supervisor_user_id = ?
                  AND effective_to IS NULL
            ");
        }
        $stmt->execute([$closeTo, $ownerUserId]);
        return $stmt->rowCount();
    }

    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;
    }
}
