<?php

declare(strict_types=1);

namespace App\Modules\Auth;

use App\Core\BaseModel;

/**
 * AuthModel — Authentication Database Queries
 *
 * Provides named query methods for all authentication-related database
 * operations executed during the login flow (AUTH-001) and password reset
 * flow (AUTH-003).
 *
 * Database rules (DBP v1.1 Ch.9, AI.md §9):
 *   - All queries use parameterized PDO — no string concatenation.
 *   - Never SELECT * — always name columns explicitly.
 *   - Returns arrays only (never objects).
 *   - No business logic — pure data access layer.
 *
 * Tables accessed:
 *   users    — credential verification (password_hash, role_id, is_active)
 *   roles    — role name resolution (name: super_admin, couple, crew)
 *
 * Lockout storage:
 *   Failed attempt tracking is kept in storage/cache/ JSON files (filesystem).
 *   This avoids a schema addition during the frozen DB period (AUTH-001 scope).
 *
 * Password reset tokens:
 *   Reset tokens are NOT stored in the database (schema freeze).
 *   They are stored as JSON files in storage/cache/ (AUTH-003 scope).
 *   Only updatePasswordHash() writes to the database during the reset flow.
 *
 * @package Inveetaire
 * @ref     DBP v1.1 Ch.9 Ch.12, SPRINT2_PLANNING.md §5, AUTH-001, AUTH-003
 */
class AuthModel extends BaseModel
{
    /** @var string Primary table — not used directly; queries are explicit. */
    protected string $table = 'users';

    // ─────────────────────────────────────────────────────────────────────────
    // USER LOOKUP
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Find an active user by email address with their role name.
     *
     * Used by the couple and super_admin login path (email-based).
     * Crew logins use username — see findActiveUserByUsername().
     *
     * Joins users → roles to return role_name in a single query.
     * Filters: is_active = 1 AND deactivated_at IS NULL.
     *
     * @param string $email Normalised lowercase email address.
     * @return array<string, mixed>|null  User row with role_name, or null if not found.
     */
    public function findActiveUserByEmail(string $email): ?array
    {
        $stmt = $this->query(
            'SELECT u.id, u.email, u.username, u.password_hash, u.role_id,
                    u.workspace_id, u.is_active, u.deactivated_at,
                    r.slug AS role_name
               FROM users u
               JOIN roles r ON r.id = u.role_id
              WHERE u.email = ?
                AND u.is_active = 1
                AND u.deactivated_at IS NULL
              LIMIT 1',
            [$email]
        );

        $row = $stmt->fetch();

        return ($row !== false) ? $row : null;
    }

    /**
     * Find an active user by username with their role name.
     *
     * Used by the crew login path (username-based, per SPRINT2_PLANNING §5).
     * Crew accounts use username as the primary identifier.
     *
     * @param string $username Crew username (case-sensitive match on DB collation).
     * @return array<string, mixed>|null  User row with role_name, or null if not found.
     */
    public function findActiveUserByUsername(string $username): ?array
    {
        $stmt = $this->query(
            'SELECT u.id, u.email, u.username, u.password_hash, u.role_id,
                    u.workspace_id, u.is_active, u.deactivated_at,
                    r.slug AS role_name
               FROM users u
               JOIN roles r ON r.id = u.role_id
              WHERE u.username = ?
                AND u.is_active = 1
                AND u.deactivated_at IS NULL
              LIMIT 1',
            [$username]
        );

        $row = $stmt->fetch();

        return ($row !== false) ? $row : null;
    }

    // ─────────────────────────────────────────────────────────────────────────
    // PASSWORD RESET (AUTH-003)
    // ─────────────────────────────────────────────────────────────────────────

    /**
     * Find an active user by email address for the password reset flow.
     *
     * Restricted to couple and super_admin roles only.
     * Crew accounts do NOT support self-service password reset
     * (SPRINT2_PLANNING §5 AUTH-003 — crew passwords reset via Couple portal).
     *
     * Joins users → roles to resolve the role name in a single query.
     * Filters: is_active = 1 AND deactivated_at IS NULL.
     * Additional filter: role name IN ('couple', 'super_admin').
     *
     * @param string $email Normalised lowercase email address.
     * @return array<string, mixed>|null  User row with role_name, or null if not found/not eligible.
     */
    public function findActiveUserByEmailForReset(string $email): ?array
    {
        $stmt = $this->query(
            'SELECT u.id, u.email, u.role_id, r.slug AS role_name
               FROM users u
               JOIN roles r ON r.id = u.role_id
              WHERE u.email = ?
                AND u.is_active = 1
                AND u.deactivated_at IS NULL
                AND r.slug IN (?, ?)
              LIMIT 1',
            [$email, 'couple', 'super_admin']
        );

        $row = $stmt->fetch();

        return ($row !== false) ? $row : null;
    }

    /**
     * Update the password hash for a user by their ID.
     *
     * Called by AuthService::resetPassword() after token validation.
     * The new hash is always generated with PASSWORD_BCRYPT in AuthService
     * before being passed here.
     *
     * @param int    $userId      The user's primary key.
     * @param string $newHash     New bcrypt hash — never a plain-text password.
     * @return bool               True if exactly one row was updated.
     */
    public function updatePasswordHash(int $userId, string $newHash): bool
    {
        $stmt = $this->query(
            'UPDATE users SET password_hash = ? WHERE id = ? LIMIT 1',
            [$newHash, $userId]
        );

        return $stmt->rowCount() === 1;
    }

    /**
     * Retrieve workspace configuration details by ID.
     */
    public function getWorkspaceById(int $workspaceId): ?array
    {
        $stmt = $this->query(
            'SELECT id, slug, groom_name, bride_name, workspace_status 
               FROM workspaces 
              WHERE id = ? 
              LIMIT 1',
            [$workspaceId]
        );
        $row = $stmt->fetch();
        return ($row !== false) ? $row : null;
    }

    /**
     * Get the first available workspace in the system as a fallback.
     */
    public function getFirstWorkspace(): ?array
    {
        $stmt = $this->query(
            'SELECT id, slug, groom_name, bride_name, workspace_status 
               FROM workspaces 
              LIMIT 1'
        );
        $row = $stmt->fetch();
        return ($row !== false) ? $row : null;
    }

    /**
     * Find an active user by their ID.
     *
     * @param int $id User ID.
     * @return array|null User details or null if not found.
     */
    public function findActiveUserById(int $id): ?array
    {
        $stmt = $this->query(
            'SELECT id, password_hash, role_id, is_active FROM users WHERE id = ? LIMIT 1',
            [$id]
        );
        $row = $stmt->fetch();
        return ($row !== false) ? $row : null;
    }
}
