<?php

require_once BASE_PATH
    . '/config/database.php';


class User
{
    private PDO $db;


    public function __construct()
    {
        $database =
            new Database();

        $this->db =
            $database->connect();
    }


    // ============================================================
    // FIND USER BY PHONE OR EMAIL
    // ============================================================

    public function findByIdentifier(
        string $identifier
    ): ?array {

        $stmt =
            $this->db->prepare("
                SELECT *

                FROM users

                WHERE phone = :phone
                   OR email = :email

                LIMIT 1
            ");


        $stmt->execute([
            'phone' =>
                $identifier,

            'email' =>
                $identifier
        ]);


        $user =
            $stmt->fetch();


        return $user ?: null;
    }


    // ============================================================
    // FIND USER BY ID
    // ============================================================

    public function findById(
        int $id
    ): ?array {

        $stmt =
            $this->db->prepare("
                SELECT *

                FROM users

                WHERE id = :id

                LIMIT 1
            ");


        $stmt->execute([
            'id' => $id
        ]);


        $user =
            $stmt->fetch();


        return $user ?: null;
    }


    // ============================================================
    // PHONE EXISTS
    // ============================================================

    public function phoneExists(
        string $phone
    ): bool {

        $stmt =
            $this->db->prepare("
                SELECT COUNT(*)

                FROM users

                WHERE phone = :phone
            ");


        $stmt->execute([
            'phone' =>
                $phone
        ]);


        return (int)
            $stmt->fetchColumn()
            > 0;
    }


    // ============================================================
    // EMAIL EXISTS
    // ============================================================

    public function emailExists(
        string $email
    ): bool {

        $stmt =
            $this->db->prepare("
                SELECT COUNT(*)

                FROM users

                WHERE email = :email
            ");


        $stmt->execute([
            'email' =>
                $email
        ]);


        return (int)
            $stmt->fetchColumn()
            > 0;
    }


    // ============================================================
    // CREATE CUSTOMER
    // ============================================================

    public function createCustomer(
        array $data
    ): int {

        $stmt =
            $this->db->prepare("
                INSERT INTO users (
                    full_name,
                    phone,
                    email,
                    password_hash,
                    role,
                    status
                )
                VALUES (
                    :full_name,
                    :phone,
                    :email,
                    :password_hash,
                    'customer',
                    'active'
                )
            ");


        $stmt->execute([
            'full_name' =>
                $data['full_name'],

            'phone' =>
                $data['phone'],

            'email' =>
                !empty($data['email'])
                    ? $data['email']
                    : null,

            'password_hash' =>
                $data['password_hash']
        ]);


        return (int)
            $this->db->lastInsertId();
    }


    // ============================================================
    // UPDATE LAST LOGIN
    // ============================================================

    public function updateLastLogin(
        int $id
    ): void {

        $stmt =
            $this->db->prepare("
                UPDATE users

                SET last_login_at = NOW()

                WHERE id = :id
            ");


        $stmt->execute([
            'id' => $id
        ]);
    }


    // ============================================================
    // UPDATE PASSWORD HASH
    // ============================================================

    public function updatePasswordHash(
        int $id,
        string $passwordHash
    ): void {

        $stmt =
            $this->db->prepare("
                UPDATE users

                SET password_hash = :password_hash

                WHERE id = :id
            ");


        $stmt->execute([
            'password_hash' =>
                $passwordHash,

            'id' =>
                $id
        ]);
    }


    // ============================================================
    // UPDATE CUSTOMER PROFILE
    // ============================================================

    public function updateProfile(
        int $id,
        array $data
    ): void {

        $stmt =
            $this->db->prepare("
                UPDATE users

                SET
                    full_name = :full_name,
                    email = :email,
                    phone = :phone

                WHERE id = :id
            ");


        $stmt->execute([
            'full_name' =>
                $data['full_name'],

            'email' =>
                !empty($data['email'])
                    ? $data['email']
                    : null,

            'phone' =>
                $data['phone'],

            'id' =>
                $id
        ]);
    }


    // ============================================================
    // UPDATE PASSWORD
    // ============================================================

    public function updatePassword(
        int $id,
        string $passwordHash
    ): void {

        $stmt =
            $this->db->prepare("
                UPDATE users

                SET password_hash = :password_hash

                WHERE id = :id
            ");


        $stmt->execute([
            'password_hash' =>
                $passwordHash,

            'id' =>
                $id
        ]);
    }


    // ============================================================
    // COUNT CUSTOMERS
    // ============================================================

    public function countCustomers(): int
    {
        return (int)
            $this->db
                ->query("
                    SELECT COUNT(*)

                    FROM users

                    WHERE role = 'customer'
                ")
                ->fetchColumn();
    }


    // ============================================================
    // OWNER - COUNT CUSTOMERS
    // ============================================================

    public function countCustomersForOwner(
        string $search = ''
    ): int {

        $params = [];


        $where = "
            role = 'customer'
        ";


        if ($search !== '') {

            /*
             * Không dùng cùng :search nhiều lần.
             * PDO native prepare có thể gây HY093.
             */

            $where .= "
                AND (
                    full_name LIKE :search_name
                    OR phone LIKE :search_phone
                    OR email LIKE :search_email
                )
            ";


            $searchValue =
                '%' . $search . '%';


            $params = [
                'search_name' =>
                    $searchValue,

                'search_phone' =>
                    $searchValue,

                'search_email' =>
                    $searchValue
            ];
        }


        $stmt =
            $this->db->prepare("
                SELECT COUNT(*)

                FROM users

                WHERE {$where}
            ");


        $stmt->execute($params);


        return (int)
            $stmt->fetchColumn();
    }


    // ============================================================
    // OWNER - CUSTOMER LIST
    // ============================================================

    public function getCustomersForOwner(
        string $search = '',
        int $limit = 15,
        int $offset = 0
    ): array {

        $params = [];


        $where = "
            u.role = 'customer'
        ";


        if ($search !== '') {

            $where .= "
                AND (
                    u.full_name LIKE :search_name
                    OR u.phone LIKE :search_phone
                    OR u.email LIKE :search_email
                )
            ";


            $searchValue =
                '%' . $search . '%';


            $params = [
                'search_name' =>
                    $searchValue,

                'search_phone' =>
                    $searchValue,

                'search_email' =>
                    $searchValue
            ];
        }


        $limit =
            max(
                1,
                $limit
            );


        $offset =
            max(
                0,
                $offset
            );


        $stmt =
            $this->db->prepare("
                SELECT
                    u.id,
                    u.full_name,
                    u.phone,
                    u.email,
                    u.avatar,
                    u.status,
                    u.last_login_at,
                    u.created_at,

                    COUNT(o.id)
                        AS order_count,

                    SUM(
                        CASE
                            WHEN o.status = 'completed'
                            THEN 1
                            ELSE 0
                        END
                    ) AS completed_order_count,

                    COALESCE(
                        SUM(
                            CASE
                                WHEN o.status = 'completed'
                                THEN o.total_amount
                                ELSE 0
                            END
                        ),
                        0
                    ) AS total_spent

                FROM (
                    SELECT
                        u.id,
                        u.full_name,
                        u.phone,
                        u.email,
                        u.avatar,
                        u.status,
                        u.last_login_at,
                        u.created_at

                    FROM users u

                    WHERE {$where}

                    ORDER BY u.created_at DESC

                    LIMIT {$limit}
                    OFFSET {$offset}
                ) u

                LEFT JOIN orders o
                    ON o.customer_id = u.id

                GROUP BY
                    u.id,
                    u.full_name,
                    u.phone,
                    u.email,
                    u.avatar,
                    u.status,
                    u.last_login_at,
                    u.created_at

                ORDER BY u.created_at DESC
            ");


        $stmt->execute($params);


        return $stmt->fetchAll();
    }


    // ============================================================
    // OWNER - CUSTOMER DETAIL
    // ============================================================

    public function findCustomerForOwner(
        int $customerId
    ): ?array {

        $stmt =
            $this->db->prepare("
                SELECT
                    u.id,
                    u.full_name,
                    u.phone,
                    u.email,
                    u.avatar,
                    u.role,
                    u.status,
                    u.last_login_at,
                    u.created_at,
                    u.updated_at,

                    COUNT(o.id)
                        AS order_count,

                    SUM(
                        CASE
                            WHEN o.status = 'completed'
                            THEN 1
                            ELSE 0
                        END
                    ) AS completed_order_count,

                    COALESCE(
                        SUM(
                            CASE
                                WHEN o.status = 'completed'
                                THEN o.total_amount
                                ELSE 0
                            END
                        ),
                        0
                    ) AS total_spent

                FROM users u

                LEFT JOIN orders o
                    ON o.customer_id = u.id

                WHERE u.id = :id
                  AND u.role = 'customer'

                GROUP BY
                    u.id,
                    u.full_name,
                    u.phone,
                    u.email,
                    u.avatar,
                    u.role,
                    u.status,
                    u.last_login_at,
                    u.created_at,
                    u.updated_at

                LIMIT 1
            ");


        $stmt->execute([
            'id' =>
                $customerId
        ]);


        $customer =
            $stmt->fetch();


        return $customer ?: null;
    }

    public function getActiveOwnerIds(): array
    {
        return array_map('intval', $this->db->query("SELECT id FROM users WHERE role = 'owner' AND status = 'active'")->fetchAll(PDO::FETCH_COLUMN));
    }

    // ============================================================
    // ADMIN - USER MANAGEMENT
    // ============================================================

    public function countForAdmin(
        string $search = '',
        string $role = '',
        string $status = ''
    ): int {
        [$where, $params] = $this->buildAdminFilters(
            $search,
            $role,
            $status
        );

        $stmt = $this->db->prepare(
            "SELECT COUNT(*) FROM users u WHERE {$where}"
        );
        $stmt->execute($params);

        return (int) $stmt->fetchColumn();
    }

    public function getForAdmin(
        string $search = '',
        string $role = '',
        string $status = '',
        int $limit = 20,
        int $offset = 0
    ): array {
        [$where, $params] = $this->buildAdminFilters(
            $search,
            $role,
            $status
        );

        $limit = max(1, $limit);
        $offset = max(0, $offset);

        $stmt = $this->db->prepare(
            "SELECT
                u.id, u.full_name, u.phone, u.email, u.avatar,
                u.role, u.status, u.last_login_at, u.created_at, u.updated_at
             FROM users u
             WHERE {$where}
             ORDER BY u.created_at DESC, u.id DESC
             LIMIT {$limit} OFFSET {$offset}"
        );
        $stmt->execute($params);

        return $stmt->fetchAll();
    }

    public function findForAdmin(int $id): ?array
    {
        $stmt = $this->db->prepare(
            "SELECT
                u.id, u.full_name, u.phone, u.email, u.avatar,
                u.role, u.status, u.last_login_at, u.created_at, u.updated_at,
                COUNT(o.id) AS order_count,
                SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END)
                    AS completed_order_count,
                COALESCE(SUM(CASE WHEN o.status = 'completed'
                    THEN o.total_amount ELSE 0 END), 0) AS total_spent
             FROM users u
             LEFT JOIN orders o ON o.customer_id = u.id
             WHERE u.id = :id
             GROUP BY
                u.id, u.full_name, u.phone, u.email, u.avatar,
                u.role, u.status, u.last_login_at, u.created_at, u.updated_at
             LIMIT 1"
        );
        $stmt->execute(['id' => $id]);

        $user = $stmt->fetch();
        return $user ?: null;
    }

    public function updateStatusByAdmin(int $id, string $status): void
    {
        $stmt = $this->db->prepare(
            'UPDATE users SET status = :status WHERE id = :id'
        );
        $stmt->execute([
            'status' => $status,
            'id' => $id
        ]);
    }

    public function findByEmail(string $email): ?array
    {
        $stmt = $this->db->prepare(
            'SELECT * FROM users WHERE email = :email LIMIT 1'
        );
        $stmt->execute(['email' => $email]);

        $user = $stmt->fetch();
        return $user ?: null;
    }

    public function updateAvatar(int $id, ?string $avatar): void
    {
        $stmt = $this->db->prepare(
            'UPDATE users SET avatar = :avatar WHERE id = :id'
        );
        $stmt->execute([
            'avatar' => $avatar,
            'id' => $id
        ]);
    }

    public function updateRoleByAdmin(int $id, string $role): void
    {
        $stmt = $this->db->prepare(
            'UPDATE users SET role = :role WHERE id = :id'
        );
        $stmt->execute([
            'role' => $role,
            'id' => $id
        ]);
    }

    public function createByAdmin(array $data): int
    {
        $stmt = $this->db->prepare('INSERT INTO users (full_name, phone, email, password_hash, role, status) VALUES (:full_name, :phone, :email, :password_hash, :role, :status)');
        $stmt->execute($data);
        return (int) $this->db->lastInsertId();
    }

    public function updateByAdmin(int $id, array $data): void
    {
        $sql = 'UPDATE users SET full_name=:full_name, phone=:phone, email=:email, role=:role, status=:status';
        if (!empty($data['password_hash'])) { $sql .= ', password_hash=:password_hash'; }
        $sql .= ' WHERE id=:id';
        $data['id'] = $id;
        $this->db->prepare($sql)->execute($data);
    }

    public function countActiveAdmins(): int
    {
        return (int) $this->db->query(
            "SELECT COUNT(*) FROM users
             WHERE role = 'admin' AND status = 'active'"
        )->fetchColumn();
    }

    public function countByRole(string $role): int
    {
        $stmt = $this->db->prepare(
            'SELECT COUNT(*) FROM users WHERE role = :role'
        );
        $stmt->execute(['role' => $role]);

        return (int) $stmt->fetchColumn();
    }

    public function countByStatus(string $status): int
    {
        $stmt = $this->db->prepare(
            'SELECT COUNT(*) FROM users WHERE status = :status'
        );
        $stmt->execute(['status' => $status]);

        return (int) $stmt->fetchColumn();
    }

    public function getRecentForAdmin(int $limit = 8): array
    {
        $limit = max(1, $limit);

        return $this->db->query(
            "SELECT id, full_name, email, phone, role, status, created_at
             FROM users
             ORDER BY created_at DESC, id DESC
             LIMIT {$limit}"
        )->fetchAll();
    }

    private function buildAdminFilters(
        string $search,
        string $role,
        string $status
    ): array {
        $where = '1 = 1';
        $params = [];

        if ($search !== '') {
            $where .= ' AND (
                u.full_name LIKE :search_name
                OR u.phone LIKE :search_phone
                OR u.email LIKE :search_email
            )';
            $searchValue = '%' . $search . '%';
            $params['search_name'] = $searchValue;
            $params['search_phone'] = $searchValue;
            $params['search_email'] = $searchValue;
        }

        if (in_array($role, ['customer', 'owner', 'admin'], true)) {
            $where .= ' AND u.role = :role';
            $params['role'] = $role;
        }

        if (in_array($status, ['active', 'locked'], true)) {
            $where .= ' AND u.status = :status';
            $params['status'] = $status;
        }

        return [$where, $params];
    }
}
