Self-consciousness in the Upanishads is not the first-person indexical self-awareness or the self-awareness which is self-reference without identification, and also not the self-consciousness which as a kind of desire is satisfied by another self-consciousness. It is Self-realisation; the realisation of the Self consisting of consciousness that leads all else.
Self-consciousness (Vedanta) Wikipedia
The word Self-consciousness in the Upanishads means the knowledge about the existence and nature of Brahman. It means the consciousness of our own real being, the primary reality. Self-consciousness means Self-knowledge, the knowledge of Prajna i.e. of Prana which is Brahman. Swami Parmeshwaranand explains that Existence is not existence if it does not mean Self-consciousness, Reality is not reality if it does not express throughout its structure the mark of Self-consciousness, the Ultimate category of existence. According to the Upanishads the Atman or Paramatman and Ishvara are unknowable; they are not merely objects of faith but the objects of mystical realisation. The Atman is unknowable in its essential nature; it is unknowable in its essential nature because it is the eternal subject who knows about everything including itself. The Atman is the knower and also the known.
Metaphysicians regard the Self either to be distinct from the Absolute or entirely identical with the Absolute. They have given form to three schools of thought – a) the Dualistic school, b) the Quasi-dualistic school and c) the Monistic school, as the result of their varying mystical experiences. Prakrti and Atman, when treated as two separate and distinct aspects form the basis of the Dualism of the Shvetashvatara Upanishad. Quasi-dualism is reflected in the Vaishnavite-monotheism of Ramanuja and the absolute Monism, in the teachings of Adi Shankara.
Self-consciousness is the Fourth state of consciousness or Turiya, the first three being Vaisvanara, Taijasa and Prajna. These are the four states of individual consciousness.
There are three distinct stages leading to Self-realisation. The First stage is in mystically apprehending the glory of the Self within us as though we were distinct from it. The Second stage is in identifying the “I-within” with the Self, that we are in essential nature entirely identical with the pure Self. The Third stage is in realising that the Atman is Brahman, that there is no difference between the Self and the Absolute. The Fourth stage is in realising "I am the Absolute" - Aham Brahman Asmi. The Fifth stage is in realising that Brahman is the “All” that exists, as also that which does not exist.
The sublime state of Self-consciousness is reached after the Seeker after Truth devoid of egoism and delusion, overcoming the flaws of attachment, firm in spirituality, free from lusts, released from dualities called pleasures and pains, the un-deluded repairs to the imperishable status, because for a knower of Brahman who has realised the Ultimate Truth, there is much profit from reservoirs when all around there is an inundation. Through Self-consciousness one gains the knowledge of Existence which is the knowledge of Sole Reality. It is not mere intellectual apprehension of Truth, it is the apprehension of the Oneness which has to be realised here in this very life. In the Bhagavad Gita XIV.20 Lord Krishna tells Arjuna that when the embodied being is able to transcend these three modes or gunas associated with the material body i.e. Sattva, the mode of goodness, Rajas, the mode of passion and Tamasa, the mode of ignorance, he can become free from birth, death, old age and their distresses and can enjoy nectar even in this life. Self-consciousness is a positive experience. It is the direct realization of the immortal Brahman - he enters into My Being - Bhagavad Gita XIV.19 who is the ground of the imperishable Brahman, of immortality, of the eternal virtue and of unending immutable bliss - Bhagavad GitaXIV.27.