-- SQL file to populate the prayers table with Catholic prayers
-- from Xavier University's website

-- Basic Prayers
INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Sign of the Cross', 'In the name of the Father, and of the Son, and of the Holy Spirit.', 'Basic Prayers', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Sign of the Cross');

INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Our Father', 'Our Father, who art in heaven, hallowed be thy name. Thy kingdom come, thy will be done, on earth, as it is in heaven. Give us this day our daily bread and forgive us our trespasses as we forgive those who trespass against us; and lead us not into temptation, but deliver us from evil.', 'Basic Prayers', 'English', 1
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Our Father');

INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Hail Mary', 'Hail Mary, Full of Grace, The Lord is with thee. Blessed art thou among women, and blessed is the fruit of thy womb, Jesus. Holy Mary, Mother of God, pray for us sinners now, and at the hour of our death.', 'Basic Prayers', 'English', 1
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Hail Mary');

INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Glory Be', 'Glory be to the Father, and to the Son, and to the Holy Spirit. As it was in the beginning, is now, and ever shall be, world without end.', 'Basic Prayers', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Glory Be');

-- Creeds
INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Apostles Creed', 'I believe in God, the Father Almighty, Creator of Heaven and earth; and in Jesus Christ, His only Son Our Lord, Who was conceived by the Holy Spirit, born of the Virgin Mary, suffered under Pontius Pilate, was crucified, died, and was buried. He descended into Hell; the third day He rose again from the dead; He ascended into Heaven, and sitteth at the right hand of God, the Father almighty; from thence He shall come to judge the living and the dead. I believe in the Holy Spirit, the holy Catholic Church, the communion of saints, the forgiveness of sins, the resurrection of the body and life everlasting.', 'Creeds', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Apostles Creed');

INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Nicene Creed', 'I believe in one God, the Father, the Almighty, Maker of heaven and earth, of all things visible and invisible. I believe in one Lord Jesus Christ, the only-begotten Son of God, born of the Father before all ages, God from God, Light from Light, true God from true God, begotten, not made, consubstantial with the Father. Through him all things were made. For us and for our salvation he came down from heaven: by the power of the Holy Spirit was incarnate of the Virgin Mary, and became man. For our sake he was crucified under Pontius Pilate; he suffered death and was buried, and rose again on the third day in accordance with the Scriptures. He ascended into heaven and is seated at the right hand of the Father. He will come again in glory to judge the living and the dead, and his kingdom will have no end. I believe in the Holy Spirit, the Lord, the giver of life, who proceeds from the Father and the Son. With the Father and the Son he is adored and glorified. He has spoken through the Prophets. I believe in one, holy, catholic and apostolic Church. I confess one baptism for the forgiveness of sins, and I look forward to the resurrection of the dead, and the life of the world to come.', 'Creeds', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Nicene Creed');

-- Guardian Angels
INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Guardian Angel Prayer', 'Angel of God, my guardian dear, To whom God\'s love commits me here, Ever this day, be at my side, To light and guard, Rule and guide.', 'Guardian Angels', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Guardian Angel Prayer');

-- Saints
INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Prayer to St. Michael the Archangel', 'St. Michael the Archangel, defend us in battle. Be our defense against the wickedness and snares of the Devil. May God rebuke him, we humbly pray, and do thou, O Prince of the heavenly hosts, by the power of God, thrust into hell Satan, and all the evil spirits, who prowl about the world seeking the ruin of souls.', 'Saints', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Prayer to St. Michael the Archangel');

-- Reconciliation
INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Act of Contrition', 'My God, I am sorry for my sins with all my heart. In choosing to do wrong and failing to do good, I have sinned against you whom I should love above all things. I firmly intend, with your help, to do penance, to sin no more, and to avoid whatever leads me to sin. Our Savior Jesus Christ suffered and died for us. In His name. My God have mercy.', 'Reconciliation', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Act of Contrition');

-- Holy Spirit
INSERT INTO prayers (title, content, category, language, featured)
SELECT 'We stand before You, Holy Spirit', 'We stand before You, Holy Spirit, as we gather together in Your name. With You alone to guide us, make Yourself at home in our hearts; Teach us the way we must go and how we are to pursue it. We are weak and sinful; do not let us promote disorder. Do not let ignorance lead us down the wrong path nor partiality influence our actions. Let us find in You our unity so that we may journey together to eternal life and not stray from the way of truth and what is right. All this we ask of You, who are at work in every place and time, in the communion of the Father and the Son, forever and ever.', 'Holy Spirit', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'We stand before You, Holy Spirit');

-- Marian Prayers
INSERT INTO prayers (title, content, category, language, featured)
SELECT 'The Memorare', 'Remember, O most gracious Virgin Mary, that never was it known that anyone who fled to thy protection, implored thy help, or sought thy intercession was left unaided. Inspired by this confidence, I fly unto thee, O Virgin of virgins, my mother; to thee do I come, before thee I stand, sinful and sorrowful. O Mother of the Word Incarnate, despise not my petitions, but in thy mercy hear and answer me.', 'Marian Prayers', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'The Memorare');

INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Salve Regina (Hail, Holy Queen)', 'Hail, Holy Queen, Mother of Mercy, our life, our sweetness and our hope. To thee do we cry, poor banished children of Eve. To thee do we send up our sighs, mourning and weeping in this valley of tears. Turn then, most gracious advocate, thine eyes of mercy toward us, and after this our exile, show unto us the blessed fruit of thy womb, Jesus. O clement, O loving, O sweet Virgin Mary.', 'Marian Prayers', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Salve Regina (Hail, Holy Queen)');

-- Acts
INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Act of Faith', 'O my God, I firmly believe that you are one God in three divine Persons, Father, Son, and Holy Spirit. I believe that your divine Son became man and died for our sins, and that he will come to judge the living and the dead. I believe these and all the truths which the holy Catholic Church teaches, because you have revealed them, who can neither deceive nor be deceived.', 'Acts', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Act of Faith');

INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Act of Hope', 'O my God, relying on your infinite mercy and promises, I hope to obtain pardon of my sins, the help of your grace, and life everlasting, through the merits of Jesus Christ, my Lord and Redeemer.', 'Acts', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Act of Hope');

INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Act of Love', 'O my God, I love you above all things with my whole heart and soul, because you are all good and worthy of all my love. I love my neighbor as myself for the love of you. I forgive all who have injured me and I ask pardon of those whom I have injured.', 'Acts', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Act of Love');

-- Saint Prayers
INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Prayer of St. Francis of Assisi', 'Lord, make me an instrument of your peace. Where there is hatred, let me sow love; where there is injury, pardon; where there is doubt, faith; where there is despair, hope; where there is darkness, light; and where there is sadness, joy. O Divine Master, grant that I may not so much seek to be consoled as to console; to be understood as to understand; to be loved as to love. For it is in giving that we receive; it is in pardoning that we are pardoned; and it is in dying that we are born to eternal life.', 'Saints', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Prayer of St. Francis of Assisi');

INSERT INTO prayers (title, content, category, language, featured)
SELECT 'Prayer of St. Thomas Aquinas', 'Grant me, O Lord my God, a mind to know you, a heart to seek you, wisdom to find you, conduct pleasing to you, faithful perseverance in waiting for you, and a hope of finally embracing you.', 'Saints', 'English', 0
FROM dual WHERE NOT EXISTS (SELECT 1 FROM prayers WHERE title = 'Prayer of St. Thomas Aquinas');
