<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Database;

final class User
{
    public static function findActive(int $id): ?array
    {
        return Database::instance()->fetch(
            'SELECT id, email, first_name, last_name, phone, status, last_login_at
             FROM users WHERE id = :id AND status = :status AND deleted_at IS NULL',
            ['id' => $id, 'status' => 'active']
        );
    }

    public static function findByEmail(string $email): ?array
    {
        return Database::instance()->fetch(
            'SELECT * FROM users WHERE email = :email AND deleted_at IS NULL LIMIT 1',
            ['email' => $email]
        );
    }

    public static function tenants(int $userId): array
    {
        return Database::instance()->fetchAll(
            "SELECT t.id, t.name, t.slug, t.status, t.industry_code, t.smart_tips_on, t.timezone
             FROM tenants t
             INNER JOIN tenant_users tu ON tu.tenant_id = t.id
             WHERE tu.user_id = :uid AND tu.status = 'active' AND t.status = 'active'
             ORDER BY t.name",
            ['uid' => $userId]
        );
    }

    public static function tenantById(int $userId, int $tenantId): ?array
    {
        return Database::instance()->fetch(
            "SELECT t.id, t.name, t.slug, t.status, t.industry_code, t.smart_tips_on, t.timezone
             FROM tenants t
             INNER JOIN tenant_users tu ON tu.tenant_id = t.id
             WHERE tu.user_id = :uid AND t.id = :tid AND tu.status = 'active' AND t.status = 'active'",
            ['uid' => $userId, 'tid' => $tenantId]
        );
    }

    public static function rolesFor(int $userId, int $tenantId): array
    {
        $rows = Database::instance()->fetchAll(
            'SELECT r.code
             FROM user_roles ur
             INNER JOIN roles r ON r.id = ur.role_id
             WHERE ur.user_id = :uid AND ur.tenant_id = :tid',
            ['uid' => $userId, 'tid' => $tenantId]
        );
        return array_column($rows, 'code');
    }

    public static function markLogin(int $id): void
    {
        Database::instance()->query(
            'UPDATE users SET last_login_at = NOW() WHERE id = :id',
            ['id' => $id]
        );
    }

    public static function listForTenant(int $tenantId, bool $activeOnly = true): array
    {
        $sql = 'SELECT u.id, u.email, u.first_name, u.last_name, u.phone, u.status, u.last_login_at,
                       tu.status AS login_status,
                       GROUP_CONCAT(r.code ORDER BY r.id SEPARATOR ",") AS role_codes,
                       GROUP_CONCAT(r.name ORDER BY r.id SEPARATOR ", ") AS role_names
                FROM users u
                INNER JOIN tenant_users tu ON tu.user_id = u.id AND tu.tenant_id = :tid
                LEFT JOIN user_roles ur ON ur.user_id = u.id AND ur.tenant_id = :tid2
                LEFT JOIN roles r ON r.id = ur.role_id
                WHERE u.deleted_at IS NULL';
        if ($activeOnly) {
            $sql .= " AND tu.status = 'active'";
        }
        $sql .= ' GROUP BY u.id ORDER BY u.first_name, u.last_name';
        return Database::instance()->fetchAll($sql, ['tid' => $tenantId, 'tid2' => $tenantId]);
    }

    public static function salespeople(int $tenantId): array
    {
        return Database::instance()->fetchAll(
            "SELECT DISTINCT u.id, u.first_name, u.last_name, u.email
             FROM users u
             INNER JOIN user_roles ur ON ur.user_id = u.id AND ur.tenant_id = :tid
             INNER JOIN roles r ON r.id = ur.role_id AND r.code IN ('salesperson','manager')
             INNER JOIN tenant_users tu ON tu.user_id = u.id AND tu.tenant_id = :tid2 AND tu.status = 'active'
             WHERE u.status = 'active' AND u.deleted_at IS NULL
             ORDER BY u.first_name",
            ['tid' => $tenantId, 'tid2' => $tenantId]
        );
    }

    public static function managers(int $tenantId): array
    {
        return Database::instance()->fetchAll(
            "SELECT DISTINCT u.id, u.first_name, u.last_name, u.email
             FROM users u
             INNER JOIN user_roles ur ON ur.user_id = u.id AND ur.tenant_id = :tid
             INNER JOIN roles r ON r.id = ur.role_id AND r.code = 'manager'
             INNER JOIN tenant_users tu ON tu.user_id = u.id AND tu.tenant_id = :tid2 AND tu.status = 'active'
             WHERE u.status = 'active' AND u.deleted_at IS NULL
             ORDER BY u.first_name",
            ['tid' => $tenantId, 'tid2' => $tenantId]
        );
    }

    public static function servicePeople(int $tenantId): array
    {
        return Database::instance()->fetchAll(
            "SELECT DISTINCT u.id, u.first_name, u.last_name, u.email
             FROM users u
             INNER JOIN user_roles ur ON ur.user_id = u.id AND ur.tenant_id = :tid
             INNER JOIN roles r ON r.id = ur.role_id AND r.code = 'service'
             INNER JOIN tenant_users tu ON tu.user_id = u.id AND tu.tenant_id = :tid2 AND tu.status = 'active'
             WHERE u.status = 'active' AND u.deleted_at IS NULL
             ORDER BY u.first_name",
            ['tid' => $tenantId, 'tid2' => $tenantId]
        );
    }

    public static function createStaff(array $data): int
    {
        Database::instance()->query(
            'INSERT INTO users (email, password_hash, first_name, last_name, phone, status)
             VALUES (:email, :password_hash, :first_name, :last_name, :phone, :status)',
            [
                'email' => $data['email'],
                'password_hash' => $data['password_hash'],
                'first_name' => $data['first_name'],
                'last_name' => $data['last_name'],
                'phone' => $data['phone'] ?? null,
                'status' => $data['status'] ?? 'active',
            ]
        );
        return (int) Database::instance()->lastId();
    }

    public static function updateStaff(int $id, array $data): void
    {
        $sets = [];
        $params = ['id' => $id];
        foreach (['first_name', 'last_name', 'phone', 'status', 'email', 'password_hash'] as $col) {
            if (array_key_exists($col, $data)) {
                $sets[] = "`$col` = :$col";
                $params[$col] = $data[$col];
            }
        }
        if ($sets === []) {
            return;
        }
        Database::instance()->query(
            'UPDATE users SET ' . implode(',', $sets) . ' WHERE id = :id',
            $params
        );
    }

    public static function attachTenant(int $userId, int $tenantId, string $status = 'active'): void
    {
        $existing = Database::instance()->fetch(
            'SELECT tenant_id FROM tenant_users WHERE user_id = :uid LIMIT 1',
            ['uid' => $userId]
        );
        if ($existing && (int) $existing['tenant_id'] !== $tenantId) {
            throw new \RuntimeException('This person already belongs to another company.');
        }
        Database::instance()->query(
            'INSERT INTO tenant_users (tenant_id, user_id, status) VALUES (:tid, :uid, :st)
             ON DUPLICATE KEY UPDATE status = VALUES(status)',
            ['tid' => $tenantId, 'uid' => $userId, 'st' => $status]
        );
    }

    public static function setRoles(int $userId, int $tenantId, array $roleIds): void
    {
        Database::instance()->query(
            'DELETE FROM user_roles WHERE user_id = :uid AND tenant_id = :tid',
            ['uid' => $userId, 'tid' => $tenantId]
        );
        foreach ($roleIds as $roleId) {
            Database::instance()->query(
                'INSERT INTO user_roles (tenant_id, user_id, role_id) VALUES (:tid, :uid, :rid)',
                ['tid' => $tenantId, 'uid' => $userId, 'rid' => (int) $roleId]
            );
        }
    }

    public static function deactivateInTenant(int $userId, int $tenantId): void
    {
        Database::instance()->query(
            "UPDATE tenant_users SET status = 'inactive' WHERE user_id = :uid AND tenant_id = :tid",
            ['uid' => $userId, 'tid' => $tenantId]
        );
        Database::instance()->query(
            "UPDATE users SET status = 'inactive' WHERE id = :id",
            ['id' => $userId]
        );
    }

    public static function activateInTenant(int $userId, int $tenantId): void
    {
        Database::instance()->query(
            "UPDATE tenant_users SET status = 'active' WHERE user_id = :uid AND tenant_id = :tid",
            ['uid' => $userId, 'tid' => $tenantId]
        );
        Database::instance()->query(
            "UPDATE users SET status = 'active' WHERE id = :id",
            ['id' => $userId]
        );
    }
}
