--
-- Host: localhost    Database: sanctuarycl
-- ------------------------------------------------------


--
-- Table structure for table `assemblies`
--

CREATE TABLE `assemblies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `tradition_id` int(11) DEFAULT NULL,
  `occasion` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `tradition_id` (`tradition_id`),
  CONSTRAINT `assemblies_ibfk_1` FOREIGN KEY (`tradition_id`) REFERENCES `traditions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `assemblies`
--


--
-- Table structure for table `assembly_elements`
--

CREATE TABLE `assembly_elements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `assembly_id` int(11) NOT NULL,
  `position` int(11) NOT NULL,
  `element_id` int(11) DEFAULT NULL,
  `custom_text` mediumtext DEFAULT NULL,
  `custom_title` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `assembly_id` (`assembly_id`),
  KEY `element_id` (`element_id`),
  CONSTRAINT `assembly_elements_ibfk_1` FOREIGN KEY (`assembly_id`) REFERENCES `assemblies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `assembly_elements_ibfk_2` FOREIGN KEY (`element_id`) REFERENCES `liturgical_elements` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `assembly_elements`
--


--
-- Table structure for table `calendar_events`
--

CREATE TABLE `calendar_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tradition_id` int(11) DEFAULT NULL,
  `assembly_id` int(11) DEFAULT NULL,
  `event_date` date NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `assembly_id` (`assembly_id`),
  KEY `idx_calendar_events_date` (`event_date`),
  KEY `idx_calendar_events_tradition_date` (`tradition_id`,`event_date`),
  CONSTRAINT `calendar_events_ibfk_1` FOREIGN KEY (`tradition_id`) REFERENCES `traditions` (`id`) ON DELETE SET NULL,
  CONSTRAINT `calendar_events_ibfk_2` FOREIGN KEY (`assembly_id`) REFERENCES `assemblies` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `calendar_events`
--

INSERT INTO `calendar_events` VALUES
(1,6,NULL,'2026-05-24','','','2026-04-10 12:56:34'),
(2,8,NULL,'2026-05-24','','','2026-04-10 12:56:58');

--
-- Table structure for table `element_types`
--

CREATE TABLE `element_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(60) NOT NULL,
  `description` text DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `element_types`
--

INSERT INTO `element_types` VALUES
(1,'Collect','collect',NULL,0),
(2,'Opening Sentence','opening_sentence',NULL,0),
(3,'Confession','confession',NULL,0),
(4,'Absolution','absolution',NULL,0),
(5,'Kyrie','kyrie',NULL,0),
(6,'Gloria','gloria',NULL,0),
(7,'Creed','creed',NULL,0),
(8,'Sursum Corda','sursum_corda',NULL,0),
(9,'Proper Preface','proper_preface',NULL,0),
(10,'Sanctus','sanctus',NULL,0),
(11,'Prayer of Humble Access','prayer_of_humble_access',NULL,0),
(12,'Prayer of Consecration','prayer_of_consecration',NULL,0),
(13,'Comfortable Words','comfortable_words',NULL,0),
(14,'Agnus Dei','agnus_dei',NULL,0),
(15,'Post-Communion Prayer','post_communion_prayer',NULL,0),
(16,'Blessing','blessing',NULL,0),
(17,'Canticle','canticle',NULL,0),
(18,'Psalm','psalm',NULL,0),
(19,'Scripture (OT)','scripture_ot',NULL,0),
(20,'Scripture (Epistle)','scripture_epistle',NULL,0),
(21,'Scripture (Gospel)','scripture_gospel',NULL,0),
(22,'Intercessions','intercessions',NULL,0),
(23,'Rubric','rubric',NULL,0),
(24,'Exhortation','exhortation',NULL,0);

--
-- Table structure for table `feast_days`
--

CREATE TABLE `feast_days` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tradition_id` int(11) DEFAULT NULL,
  `name` varchar(200) NOT NULL,
  `short_name` varchar(100) DEFAULT NULL,
  `rank` enum('solemnity','principal_feast','feast','obligatory_memorial','optional_memorial','commemoration','sunday','feria') NOT NULL,
  `is_moveable` tinyint(1) DEFAULT 0,
  `fixed_month` tinyint(4) DEFAULT NULL,
  `fixed_day` tinyint(4) DEFAULT NULL,
  `easter_offset` int(11) DEFAULT NULL,
  `advent_offset` int(11) DEFAULT NULL,
  `liturgical_color` varchar(50) DEFAULT NULL,
  `season_id` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradition_id` (`tradition_id`),
  KEY `season_id` (`season_id`),
  CONSTRAINT `feast_days_ibfk_1` FOREIGN KEY (`tradition_id`) REFERENCES `traditions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `feast_days_ibfk_2` FOREIGN KEY (`season_id`) REFERENCES `liturgical_seasons` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `feast_days`
--

INSERT INTO `feast_days` VALUES
(1,NULL,'Ash Wednesday','Ash Wed','solemnity',1,NULL,NULL,-46,NULL,'purple',NULL,'Beginning of Lent'),
(2,NULL,'Palm Sunday','Palm Sun','solemnity',1,NULL,NULL,-7,NULL,'red',NULL,'Sunday of the Passion'),
(3,NULL,'Maundy Thursday','Maundy Thu','solemnity',1,NULL,NULL,-3,NULL,'white',NULL,'Holy Thursday; commemoration of the Last Supper'),
(4,NULL,'Good Friday','Good Fri','solemnity',1,NULL,NULL,-2,NULL,'red',NULL,'Commemoration of the Crucifixion'),
(5,NULL,'Holy Saturday','Holy Sat','solemnity',1,NULL,NULL,-1,NULL,'white',NULL,'Easter Vigil'),
(6,NULL,'Easter Sunday','Easter','solemnity',1,NULL,NULL,0,NULL,'white',NULL,'The Resurrection of Our Lord'),
(7,NULL,'Ascension Thursday','Ascension','solemnity',1,NULL,NULL,39,NULL,'white',NULL,'The Ascension of Our Lord; some traditions transfer to Sunday (+42)'),
(8,NULL,'Pentecost Sunday','Pentecost','solemnity',1,NULL,NULL,49,NULL,'red',NULL,'The Day of Pentecost'),
(9,NULL,'Trinity Sunday','Trinity','solemnity',1,NULL,NULL,56,NULL,'white',NULL,'The Sunday after Pentecost'),
(10,6,'Corpus Christi','Corpus Christi','solemnity',1,NULL,NULL,60,NULL,'white',NULL,'The Most Holy Body and Blood of Christ; Thursday after Trinity Sunday'),
(11,6,'Sacred Heart of Jesus','Sacred Heart','solemnity',1,NULL,NULL,68,NULL,'white',NULL,'Friday after the Second Sunday after Pentecost'),
(12,NULL,'Christmas Day','Christmas','solemnity',0,12,25,NULL,NULL,'white',NULL,NULL),
(13,NULL,'Epiphany','Epiphany','solemnity',0,1,6,NULL,NULL,'white',NULL,NULL),
(14,NULL,'The Annunciation','Annunciation','feast',0,3,25,NULL,NULL,'white',NULL,NULL),
(15,NULL,'All Saints Day','All Saints','solemnity',0,11,1,NULL,NULL,'white',NULL,NULL),
(16,NULL,'Christmas Eve','Christmas Eve','principal_feast',0,12,24,NULL,NULL,'white',NULL,NULL),
(17,6,'Solemnity of Mary','Mary, Mother of God','solemnity',0,1,1,NULL,NULL,'white',NULL,'January 1 — New Year\'s Day (Catholic)'),
(18,6,'All Souls Day','All Souls','commemoration',0,11,2,NULL,NULL,'purple',NULL,'Commemoration of all the faithful departed'),
(19,6,'Immaculate Conception','Immac. Conception','solemnity',0,12,8,NULL,NULL,'white',NULL,'Patronal feast of the USA'),
(20,6,'Assumption of Mary','Assumption','solemnity',0,8,15,NULL,NULL,'white',NULL,'The Assumption of the Blessed Virgin Mary'),
(21,4,'Corpus Christi (Traditional)','Corpus Christi','solemnity',1,NULL,NULL,60,NULL,'white',NULL,'Thursday after Trinity Sunday (EF)'),
(22,10,'Theophany (Epiphany)','Theophany','solemnity',0,1,19,NULL,NULL,'white',NULL,'Julian Jan 6 = Gregorian Jan 19 (current century)'),
(23,10,'Transfiguration of Christ','Transfiguration','solemnity',0,8,19,NULL,NULL,'white',NULL,'Julian Aug 6 = Gregorian Aug 19'),
(24,10,'Dormition of the Theotokos','Dormition','solemnity',0,8,28,NULL,NULL,'blue',NULL,'Julian Aug 15 = Gregorian Aug 28'),
(25,10,'Nativity of Christ','Orthodox Christmas','solemnity',0,1,7,NULL,NULL,'white',NULL,'Julian Dec 25 = Gregorian Jan 7'),
(26,10,'Orthodox Pascha (Easter)','Pascha','solemnity',1,NULL,NULL,0,NULL,'white',NULL,'Calculated using Julian calendar algorithm'),
(27,10,'Pentecost (Trinity Day)','Troitsa','solemnity',1,NULL,NULL,49,NULL,'white',NULL,'Orthodox Pentecost / Trinity Sunday'),
(28,7,'All Saints Day','All Saints','principal_feast',0,11,1,NULL,NULL,'white',NULL,'One of the Seven Principal Feasts (1979 BCP)'),
(29,8,'All Saints Day','All Saints','principal_feast',0,11,1,NULL,NULL,'white',NULL,'One of the Principal Feasts (ACNA 2019)');

--
-- Table structure for table `liturgical_elements`
--

CREATE TABLE `liturgical_elements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `element_type_id` int(11) NOT NULL,
  `tradition_id` int(11) DEFAULT NULL,
  `season_id` int(11) DEFAULT NULL,
  `text_body` mediumtext NOT NULL,
  `rubrics` text DEFAULT NULL,
  `scripture_ref` varchar(150) DEFAULT NULL,
  `copyright_status` enum('public_domain','licensed','original') DEFAULT 'public_domain',
  `source` varchar(255) DEFAULT NULL,
  `language` char(5) DEFAULT 'en',
  `tags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`tags`)),
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `element_type_id` (`element_type_id`),
  KEY `tradition_id` (`tradition_id`),
  KEY `season_id` (`season_id`),
  CONSTRAINT `liturgical_elements_ibfk_1` FOREIGN KEY (`element_type_id`) REFERENCES `element_types` (`id`),
  CONSTRAINT `liturgical_elements_ibfk_2` FOREIGN KEY (`tradition_id`) REFERENCES `traditions` (`id`) ON DELETE SET NULL,
  CONSTRAINT `liturgical_elements_ibfk_3` FOREIGN KEY (`season_id`) REFERENCES `liturgical_seasons` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `liturgical_elements`
--

INSERT INTO `liturgical_elements` VALUES
(1,'Collect for Purity',1,NULL,NULL,'ALMIGHTY God, unto whom all hearts be open, all desires known, and from whom no secrets are hid; Cleanse the thoughts of our hearts by the inspiration of thy Holy Spirit, that we may perfectly love thee, and worthily magnify thy holy Name; through Christ our Lord. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(2,'Collect for Grace',1,NULL,NULL,'O LORD, our heavenly Father, Almighty and everlasting God, who hast safely brought us to the beginning of this day; Defend us in the same with thy mighty power; and grant that this day we fall into no sin, neither run into any kind of danger; but that all our doings may be ordered by thy governance, to do always that is righteous in thy sight; through Jesus Christ our Lord. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(3,'Collect for Peace',1,NULL,NULL,'O GOD, who art the author of peace and lover of concord, in knowledge of whom standeth our eternal life, whose service is perfect freedom; Defend us thy humble servants in all assaults of our enemies; that we, surely trusting in thy defence, may not fear the power of any adversaries; through the might of Jesus Christ our Lord. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(4,'Collect for the First Sunday in Advent',1,NULL,NULL,'ALMIGHTY God, give us grace that we may cast away the works of darkness, and put upon us the armour of light, now in the time of this mortal life, in which thy Son Jesus Christ came to visit us in great humility; that in the last day, when he shall come again in his glorious Majesty to judge both the quick and the dead, we may rise to the life immortal; through him who liveth and reigneth with thee and the Holy Ghost, now and ever. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(5,'Collect for Christmas Day',1,NULL,NULL,'ALMIGHTY God, who hast given us thy only-begotten Son to take our nature upon him, and as at this time to be born of a pure Virgin; Grant that we being regenerate, and made thy children by adoption and grace, may daily be renewed by thy Holy Spirit; through the same our Lord Jesus Christ, who liveth and reigneth with thee and the same Spirit, ever one God, world without end. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(6,'Collect for Easter Day',1,NULL,NULL,'ALMIGHTY God, who through thine only-begotten Son Jesus Christ hast overcome death, and opened unto us the gate of everlasting life; We humbly beseech thee that, as by thy special grace preventing us thou dost put into our minds good desires, so by thy continual help we may bring the same to good effect; through Jesus Christ our Lord, who liveth and reigneth with thee and the Holy Ghost, ever one God, world without end. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(7,'Collect for Whitsunday',1,NULL,NULL,'GOD, who as at this time didst teach the hearts of thy faithful people, by the sending to them the light of thy Holy Spirit; Grant us by the same Spirit to have a right judgement in all things, and evermore to rejoice in his holy comfort; through the merits of Christ Jesus our Saviour, who liveth and reigneth with thee, in the unity of the same Spirit, one God, world without end. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(8,'Prayer of Humble Access',11,NULL,NULL,'WE do not presume to come to this thy Table, O merciful Lord, trusting in our own righteousness, but in thy manifold and great mercies. We are not worthy so much as to gather up the crumbs under thy Table. But thou art the same Lord, whose property is always to have mercy: Grant us therefore, gracious Lord, so to eat the flesh of thy dear Son Jesus Christ, and to drink his blood, that our sinful bodies may be made clean by his body, and our souls washed through his most precious blood, and that we may evermore dwell in him, and he in us. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(9,'Prayer of Consecration (Abridged)',12,NULL,NULL,'ALMIGHTY God, our heavenly Father, who of thy tender mercy didst give thine only Son Jesus Christ to suffer death upon the Cross for our redemption; who made there (by his one oblation of himself once offered) a full, perfect, and sufficient sacrifice, oblation, and satisfaction for the sins of the whole world; and did institute, and in his holy Gospel command us to continue, a perpetual memory of that his precious death, until his coming again.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(10,'Gloria in Excelsis',6,NULL,NULL,'GLORY be to God on high, and in earth peace, good will towards men. We praise thee, we bless thee, we worship thee, we glorify thee, we give thanks to thee for thy great glory, O Lord God, heavenly King, God the Father Almighty.\n\nO Lord, the only-begotten Son Jesu Christ; O Lord God, Lamb of God, Son of the Father, that takest away the sins of the world, have mercy upon us. Thou that takest away the sins of the world, have mercy upon us. Thou that takest away the sins of the world, receive our prayer. Thou that sittest at the right hand of God the Father, have mercy upon us.\n\nFor thou only art holy; thou only art the Lord; thou only, O Christ, with the Holy Ghost, art most high in the glory of God the Father. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(11,'Sanctus',10,NULL,NULL,'HOLY, holy, holy, Lord God of hosts, heaven and earth are full of thy glory: Glory be to thee, O Lord most High.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(12,'The Comfortable Words',13,NULL,NULL,'Hear what comfortable words our Saviour Christ saith unto all that truly turn to him.\n\nCome unto me, all ye that travail and are heavy laden, and I will refresh you. (Matthew 11:28)\n\nSo God loved the world, that he gave his only-begotten Son, to the end that all that believe in him should not perish, but have everlasting life. (John 3:16)\n\nHear also what Saint Paul saith: This is a true saying, and worthy of all men to be received, That Christ Jesus came into the world to save sinners. (1 Timothy 1:15)\n\nHear also what Saint John saith: If any man sin, we have an Advocate with the Father, Jesus Christ the righteous; and he is the propitiation for our sins. (1 John 2:1–2)',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(13,'Nicene Creed',7,NULL,NULL,'I BELIEVE in one God the Father Almighty, Maker of heaven and earth, And of all things visible and invisible:\n\nAnd in one Lord Jesus Christ, the only-begotten Son of God, Begotten of his Father before all worlds, God of God, Light of Light, Very God of very God, Begotten, not made, Being of one substance with the Father, By whom all things were made: Who for us men and for our salvation came down from heaven, And was incarnate by the Holy Ghost of the Virgin Mary, And was made man, And was crucified also for us under Pontius Pilate. He suffered and was buried, And the third day he rose again according to the Scriptures, And ascended into heaven, And sitteth on the right hand of the Father. And he shall come again with glory to judge both the quick and the dead: Whose kingdom shall have no end.\n\nAnd I believe in the Holy Ghost, The Lord and giver of life, Who proceedeth from the Father and the Son, Who with the Father and the Son together is worshipped and glorified, Who spake by the Prophets. And I believe one Catholick and Apostolick Church. I acknowledge one Baptism for the remission of sins. And I look for the Resurrection of the dead, And the life of the world to come. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(14,'Apostles\' Creed',7,NULL,NULL,'I BELIEVE in God the Father Almighty, Maker of heaven and earth:\n\nAnd in Jesus Christ his only Son our Lord, Who was conceived by the Holy Ghost, Born of the Virgin Mary, Suffered under Pontius Pilate, Was crucified, dead, and buried, He descended into hell; The third day he rose again from the dead, He ascended into heaven, And sitteth on the right hand of God the Father Almighty; From thence he shall come to judge the quick and the dead.\n\nI believe in the Holy Ghost; The holy Catholick Church; The Communion of Saints; The Forgiveness of sins; The Resurrection of the body, And the life everlasting. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(15,'Post-Communion Thanksgiving',15,NULL,NULL,'ALMIGHTY and everliving God, we most heartily thank thee, for that thou dost vouchsafe to feed us, who have duly received these holy mysteries, with the spiritual food of the most precious Body and Blood of thy Son our Saviour Jesus Christ; and dost assure us thereby of thy favour and goodness towards us; and that we are very members incorporate in the mystical body of thy Son, which is the blessed company of all faithful people; and are also heirs through hope of thy everlasting kingdom, by the merits of the most precious death and passion of thy dear Son. And we most humbly beseech thee, O heavenly Father, so to assist us with thy grace, that we may continue in that holy fellowship, and do all such good works as thou hast prepared for us to walk in; through Jesus Christ our Lord, to whom with thee and the Holy Ghost be all honour and glory, world without end. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(16,'The Peace of God',16,NULL,NULL,'THE peace of God, which passeth all understanding, keep your hearts and minds in the knowledge and love of God, and of his Son Jesus Christ our Lord: And the blessing of God Almighty, the Father, the Son, and the Holy Ghost, be amongst you and remain with you always. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-03-24 12:19:25','2026-03-24 12:19:25'),
(17,'Collect for Purity',1,NULL,NULL,'ALMIGHTY God, unto whom all hearts be open, all desires known, and from whom no secrets are hid; Cleanse the thoughts of our hearts by the inspiration of thy Holy Spirit, that we may perfectly love thee, and worthily magnify thy holy Name; through Christ our Lord. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(18,'Collect for Grace',1,NULL,NULL,'O LORD, our heavenly Father, Almighty and everlasting God, who hast safely brought us to the beginning of this day; Defend us in the same with thy mighty power; and grant that this day we fall into no sin, neither run into any kind of danger; but that all our doings may be ordered by thy governance, to do always that is righteous in thy sight; through Jesus Christ our Lord. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(19,'Collect for Peace',1,NULL,NULL,'O GOD, who art the author of peace and lover of concord, in knowledge of whom standeth our eternal life, whose service is perfect freedom; Defend us thy humble servants in all assaults of our enemies; that we, surely trusting in thy defence, may not fear the power of any adversaries; through the might of Jesus Christ our Lord. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(20,'Collect for the First Sunday in Advent',1,NULL,NULL,'ALMIGHTY God, give us grace that we may cast away the works of darkness, and put upon us the armour of light, now in the time of this mortal life, in which thy Son Jesus Christ came to visit us in great humility; that in the last day, when he shall come again in his glorious Majesty to judge both the quick and the dead, we may rise to the life immortal; through him who liveth and reigneth with thee and the Holy Ghost, now and ever. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(21,'Collect for Christmas Day',1,NULL,NULL,'ALMIGHTY God, who hast given us thy only-begotten Son to take our nature upon him, and as at this time to be born of a pure Virgin; Grant that we being regenerate, and made thy children by adoption and grace, may daily be renewed by thy Holy Spirit; through the same our Lord Jesus Christ, who liveth and reigneth with thee and the same Spirit, ever one God, world without end. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(22,'Collect for Easter Day',1,NULL,NULL,'ALMIGHTY God, who through thine only-begotten Son Jesus Christ hast overcome death, and opened unto us the gate of everlasting life; We humbly beseech thee that, as by thy special grace preventing us thou dost put into our minds good desires, so by thy continual help we may bring the same to good effect; through Jesus Christ our Lord, who liveth and reigneth with thee and the Holy Ghost, ever one God, world without end. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(23,'Collect for Whitsunday',1,NULL,NULL,'GOD, who as at this time didst teach the hearts of thy faithful people, by the sending to them the light of thy Holy Spirit; Grant us by the same Spirit to have a right judgement in all things, and evermore to rejoice in his holy comfort; through the merits of Christ Jesus our Saviour, who liveth and reigneth with thee, in the unity of the same Spirit, one God, world without end. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(24,'Prayer of Humble Access',11,NULL,NULL,'WE do not presume to come to this thy Table, O merciful Lord, trusting in our own righteousness, but in thy manifold and great mercies. We are not worthy so much as to gather up the crumbs under thy Table. But thou art the same Lord, whose property is always to have mercy: Grant us therefore, gracious Lord, so to eat the flesh of thy dear Son Jesus Christ, and to drink his blood, that our sinful bodies may be made clean by his body, and our souls washed through his most precious blood, and that we may evermore dwell in him, and he in us. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(25,'Prayer of Consecration',12,NULL,NULL,'ALMIGHTY God, our heavenly Father, who of thy tender mercy didst give thine only Son Jesus Christ to suffer death upon the Cross for our redemption; who made there (by his one oblation of himself once offered) a full, perfect, and sufficient sacrifice, oblation, and satisfaction for the sins of the whole world; and did institute, and in his holy Gospel command us to continue, a perpetual memory of that his precious death, until his coming again.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(26,'Gloria in Excelsis',6,NULL,NULL,'GLORY be to God on high, and in earth peace, good will towards men. We praise thee, we bless thee, we worship thee, we glorify thee, we give thanks to thee for thy great glory, O Lord God, heavenly King, God the Father Almighty.\n\nO Lord, the only-begotten Son Jesu Christ; O Lord God, Lamb of God, Son of the Father, that takest away the sins of the world, have mercy upon us. Thou that takest away the sins of the world, have mercy upon us. Thou that takest away the sins of the world, receive our prayer. Thou that sittest at the right hand of God the Father, have mercy upon us.\n\nFor thou only art holy; thou only art the Lord; thou only, O Christ, with the Holy Ghost, art most high in the glory of God the Father. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(27,'Sanctus',10,NULL,NULL,'HOLY, holy, holy, Lord God of hosts, heaven and earth are full of thy glory: Glory be to thee, O Lord most High.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(28,'The Comfortable Words',13,NULL,NULL,'Hear what comfortable words our Saviour Christ saith unto all that truly turn to him.\n\nCome unto me, all ye that travail and are heavy laden, and I will refresh you. (Matthew 11:28)\n\nSo God loved the world, that he gave his only-begotten Son, to the end that all that believe in him should not perish, but have everlasting life. (John 3:16)\n\nHear also what Saint Paul saith: This is a true saying, and worthy of all men to be received, That Christ Jesus came into the world to save sinners. (1 Timothy 1:15)\n\nHear also what Saint John saith: If any man sin, we have an Advocate with the Father, Jesus Christ the righteous; and he is the propitiation for our sins. (1 John 2:1-2)',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(29,'Nicene Creed',7,NULL,NULL,'I BELIEVE in one God the Father Almighty, Maker of heaven and earth, And of all things visible and invisible:\n\nAnd in one Lord Jesus Christ, the only-begotten Son of God, Begotten of his Father before all worlds, God of God, Light of Light, Very God of very God, Begotten, not made, Being of one substance with the Father, By whom all things were made: Who for us men and for our salvation came down from heaven, And was incarnate by the Holy Ghost of the Virgin Mary, And was made man, And was crucified also for us under Pontius Pilate. He suffered and was buried, And the third day he rose again according to the Scriptures, And ascended into heaven, And sitteth on the right hand of the Father. And he shall come again with glory to judge both the quick and the dead: Whose kingdom shall have no end.\n\nAnd I believe in the Holy Ghost, The Lord and giver of life, Who proceedeth from the Father and the Son, Who with the Father and the Son together is worshipped and glorified, Who spake by the Prophets. And I believe one Catholick and Apostolick Church. I acknowledge one Baptism for the remission of sins. And I look for the Resurrection of the dead, And the life of the world to come. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(30,'Apostles\' Creed',7,NULL,NULL,'I BELIEVE in God the Father Almighty, Maker of heaven and earth:\n\nAnd in Jesus Christ his only Son our Lord, Who was conceived by the Holy Ghost, Born of the Virgin Mary, Suffered under Pontius Pilate, Was crucified, dead, and buried, He descended into hell; The third day he rose again from the dead, He ascended into heaven, And sitteth on the right hand of God the Father Almighty; From thence he shall come to judge the quick and the dead.\n\nI believe in the Holy Ghost; The holy Catholick Church; The Communion of Saints; The Forgiveness of sins; The Resurrection of the body, And the life everlasting. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(31,'Post-Communion Thanksgiving',15,NULL,NULL,'ALMIGHTY and everliving God, we most heartily thank thee, for that thou dost vouchsafe to feed us, who have duly received these holy mysteries, with the spiritual food of the most precious Body and Blood of thy Son our Saviour Jesus Christ; and dost assure us thereby of thy favour and goodness towards us; and that we are very members incorporate in the mystical body of thy Son, which is the blessed company of all faithful people; and are also heirs through hope of thy everlasting kingdom, by the merits of the most precious death and passion of thy dear Son. And we most humbly beseech thee, O heavenly Father, so to assist us with thy grace, that we may continue in that holy fellowship, and do all such good works as thou hast prepared for us to walk in; through Jesus Christ our Lord, to whom with thee and the Holy Ghost be all honour and glory, world without end. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13'),
(32,'The Peace of God (Blessing)',16,NULL,NULL,'THE peace of God, which passeth all understanding, keep your hearts and minds in the knowledge and love of God, and of his Son Jesus Christ our Lord: And the blessing of God Almighty, the Father, the Son, and the Holy Ghost, be amongst you and remain with you always. Amen.',NULL,NULL,'public_domain','1662 BCP','en',NULL,'2026-04-10 12:19:13','2026-04-10 12:19:13');

--
-- Table structure for table `liturgical_seasons`
--

CREATE TABLE `liturgical_seasons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `tradition_id` int(11) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `liturgical_color` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradition_id` (`tradition_id`),
  CONSTRAINT `liturgical_seasons_ibfk_1` FOREIGN KEY (`tradition_id`) REFERENCES `traditions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `liturgical_seasons`
--

INSERT INTO `liturgical_seasons` VALUES
(1,'Advent',NULL,0,NULL,NULL),
(2,'Christmas',NULL,0,NULL,NULL),
(3,'Epiphany',NULL,0,NULL,NULL),
(4,'Lent',NULL,0,NULL,NULL),
(5,'Holy Week',NULL,0,NULL,NULL),
(6,'Easter',NULL,0,NULL,NULL),
(7,'Ascension',NULL,0,NULL,NULL),
(8,'Pentecost',NULL,0,NULL,NULL),
(9,'Trinity/Ordinary Time',NULL,0,NULL,NULL),
(10,'All Saints',NULL,0,NULL,NULL),
(11,'Advent',NULL,0,'purple',NULL),
(12,'Christmas',NULL,1,'white',NULL),
(13,'Epiphany',NULL,2,'white',NULL),
(14,'Lent',NULL,3,'purple',NULL),
(15,'Holy Week',NULL,4,'red',NULL),
(16,'Easter',NULL,5,'white',NULL),
(17,'Ascension',NULL,6,'white',NULL),
(18,'Pentecost',NULL,7,'red',NULL),
(19,'Trinity/Ordinary Time',NULL,8,'green',NULL),
(20,'All Saints',NULL,9,'white',NULL),
(21,'Great Lent',10,10,'purple',NULL),
(22,'Bright Week',10,11,'white',NULL),
(23,'Paschal Season',10,12,'white',NULL),
(24,'Apostles Fast',10,13,'green',NULL),
(25,'Dormition Fast',10,14,'blue',NULL),
(26,'Nativity Fast',10,15,'purple',NULL),
(27,'Theophany',10,16,'white',NULL);

--
-- Table structure for table `service_template_slots`
--

CREATE TABLE `service_template_slots` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `template_id` int(11) NOT NULL,
  `position` int(11) NOT NULL,
  `element_type_id` int(11) DEFAULT NULL,
  `label` varchar(255) DEFAULT NULL,
  `required` tinyint(1) DEFAULT 0,
  `notes` text DEFAULT NULL,
  `default_element_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `template_id` (`template_id`),
  KEY `element_type_id` (`element_type_id`),
  KEY `default_element_id` (`default_element_id`),
  CONSTRAINT `service_template_slots_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `service_templates` (`id`) ON DELETE CASCADE,
  CONSTRAINT `service_template_slots_ibfk_2` FOREIGN KEY (`element_type_id`) REFERENCES `element_types` (`id`) ON DELETE SET NULL,
  CONSTRAINT `service_template_slots_ibfk_3` FOREIGN KEY (`default_element_id`) REFERENCES `liturgical_elements` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `service_template_slots`
--


--
-- Table structure for table `service_templates`
--

CREATE TABLE `service_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `tradition_id` int(11) DEFAULT NULL,
  `service_type` varchar(80) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `tradition_id` (`tradition_id`),
  CONSTRAINT `service_templates_ibfk_1` FOREIGN KEY (`tradition_id`) REFERENCES `traditions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `service_templates`
--


--
-- Table structure for table `tradition_calendar_config`
--

CREATE TABLE `tradition_calendar_config` (
  `tradition_id` int(11) NOT NULL,
  `easter_algorithm` enum('western','eastern') DEFAULT 'western',
  `lectionary_type` enum('rcl','bcp1979','bcp1662','tridentine','orthodox','none') DEFAULT 'none',
  `season_schema` enum('ordinary_time','trinity','orthodox') DEFAULT 'ordinary_time',
  `sunday_cycle_len` int(11) DEFAULT 1,
  `weekday_cycle_len` int(11) DEFAULT 1,
  `transfers_to_sunday` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`tradition_id`),
  CONSTRAINT `tradition_calendar_config_ibfk_1` FOREIGN KEY (`tradition_id`) REFERENCES `traditions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tradition_calendar_config`
--

INSERT INTO `tradition_calendar_config` VALUES
(1,'western','bcp1662','trinity',1,1,0),
(2,'western','bcp1662','trinity',1,1,0),
(3,'western','none','trinity',1,1,0),
(4,'western','tridentine','trinity',1,1,0),
(5,'western','none','ordinary_time',1,1,0),
(6,'western','rcl','ordinary_time',3,2,0),
(7,'western','bcp1979','ordinary_time',3,1,1),
(8,'western','rcl','ordinary_time',3,1,1),
(9,'western','rcl','ordinary_time',3,1,1),
(10,'eastern','orthodox','orthodox',1,1,0);

--
-- Table structure for table `traditions`
--

CREATE TABLE `traditions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `short_code` varchar(20) NOT NULL,
  `description` text DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `short_code` (`short_code`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `traditions`
--

INSERT INTO `traditions` VALUES
(1,'Anglican (1662 BCP)','ang1662',NULL,0,'2026-03-24 12:19:24'),
(2,'Anglican (1928 BCP)','ang1928',NULL,1,'2026-03-24 12:19:24'),
(3,'Lutheran (Common Service 1888)','luth1888',NULL,2,'2026-03-24 12:19:24'),
(4,'Roman Catholic (Traditional)','rcat',NULL,3,'2026-03-24 12:19:24'),
(5,'Ecumenical/Generic','ecum',NULL,4,'2026-03-24 12:19:24'),
(6,'Roman Catholic (Novus Ordo)','rcat_no',NULL,5,'2026-04-10 12:19:13'),
(7,'Episcopal (TEC, 1979 BCP)','epis1979',NULL,6,'2026-04-10 12:19:13'),
(8,'Anglican Church in North America (2019 BCP)','acna2019',NULL,7,'2026-04-10 12:19:13'),
(9,'Lutheran (ELCA / Revised Common Lectionary)','elca',NULL,8,'2026-04-10 12:19:13'),
(10,'Eastern Orthodox','orth',NULL,9,'2026-04-10 12:19:13');


