-- SanctuaryCL Database Setup
-- Run: mysql -u root -p < db/setup.sql
-- All liturgical content is public domain (1662 BCP).

CREATE DATABASE IF NOT EXISTS sanctuarycl CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sanctuarycl;

-- -----------------------------------------------------------------------
-- Schema
-- -----------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS traditions (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  short_code VARCHAR(20)  NOT NULL UNIQUE,
  description TEXT,
  sort_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS liturgical_seasons (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  name             VARCHAR(100) NOT NULL,
  tradition_id     INT DEFAULT NULL,
  sort_order       INT DEFAULT 0,
  liturgical_color VARCHAR(50),
  description      TEXT,
  FOREIGN KEY (tradition_id) REFERENCES traditions(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS element_types (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  slug        VARCHAR(60)  NOT NULL UNIQUE,
  description TEXT,
  sort_order  INT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS liturgical_elements (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  title            VARCHAR(255) NOT NULL,
  element_type_id  INT NOT NULL,
  tradition_id     INT DEFAULT NULL,
  season_id        INT DEFAULT NULL,
  text_body        MEDIUMTEXT NOT NULL,
  rubrics          TEXT,
  scripture_ref    VARCHAR(150),
  copyright_status ENUM('public_domain','licensed','original') DEFAULT 'public_domain',
  source           VARCHAR(255),
  language         CHAR(5) DEFAULT 'en',
  tags             JSON,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (element_type_id) REFERENCES element_types(id),
  FOREIGN KEY (tradition_id)    REFERENCES traditions(id) ON DELETE SET NULL,
  FOREIGN KEY (season_id)       REFERENCES liturgical_seasons(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS service_templates (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(255) NOT NULL,
  tradition_id INT DEFAULT NULL,
  service_type VARCHAR(80),
  description  TEXT,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tradition_id) REFERENCES traditions(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS service_template_slots (
  id                 INT AUTO_INCREMENT PRIMARY KEY,
  template_id        INT NOT NULL,
  position           INT NOT NULL,
  element_type_id    INT DEFAULT NULL,
  label              VARCHAR(255),
  required           BOOLEAN DEFAULT FALSE,
  notes              TEXT,
  default_element_id INT DEFAULT NULL,
  FOREIGN KEY (template_id)        REFERENCES service_templates(id) ON DELETE CASCADE,
  FOREIGN KEY (element_type_id)    REFERENCES element_types(id) ON DELETE SET NULL,
  FOREIGN KEY (default_element_id) REFERENCES liturgical_elements(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS tradition_calendar_config (
  tradition_id      INT PRIMARY KEY,
  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 DEFAULT 1,
  weekday_cycle_len INT DEFAULT 1,
  transfers_to_sunday BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (tradition_id) REFERENCES traditions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS feast_days (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  tradition_id     INT DEFAULT NULL,
  name             VARCHAR(200) NOT NULL,
  short_name       VARCHAR(100),
  rank             ENUM('solemnity','principal_feast','feast','obligatory_memorial',
                        'optional_memorial','commemoration','sunday','feria') NOT NULL,
  is_moveable      BOOLEAN DEFAULT FALSE,
  fixed_month      TINYINT DEFAULT NULL,
  fixed_day        TINYINT DEFAULT NULL,
  easter_offset    INT DEFAULT NULL,
  advent_offset    INT DEFAULT NULL,
  liturgical_color VARCHAR(50),
  season_id        INT DEFAULT NULL,
  notes            TEXT,
  FOREIGN KEY (tradition_id) REFERENCES traditions(id) ON DELETE CASCADE,
  FOREIGN KEY (season_id)    REFERENCES liturgical_seasons(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS calendar_events (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  tradition_id INT DEFAULT NULL,
  assembly_id  INT DEFAULT NULL,
  event_date   DATE NOT NULL,
  title        VARCHAR(255),
  notes        TEXT,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tradition_id) REFERENCES traditions(id) ON DELETE SET NULL,
  FOREIGN KEY (assembly_id)  REFERENCES assemblies(id) ON DELETE SET NULL,
  INDEX idx_calendar_events_date (event_date),
  INDEX idx_calendar_events_tradition_date (tradition_id, event_date)
);

CREATE TABLE IF NOT EXISTS assemblies (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  title        VARCHAR(255) NOT NULL,
  tradition_id INT DEFAULT NULL,
  occasion     VARCHAR(255),
  notes        TEXT,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (tradition_id) REFERENCES traditions(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS assembly_elements (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  assembly_id  INT NOT NULL,
  position     INT NOT NULL,
  element_id   INT DEFAULT NULL,
  custom_text  MEDIUMTEXT,
  custom_title VARCHAR(255),
  notes        TEXT,
  FOREIGN KEY (assembly_id) REFERENCES assemblies(id) ON DELETE CASCADE,
  FOREIGN KEY (element_id)  REFERENCES liturgical_elements(id) ON DELETE SET NULL
);

-- -----------------------------------------------------------------------
-- Seed Data
-- -----------------------------------------------------------------------

INSERT IGNORE INTO traditions (name, short_code, sort_order) VALUES
  ('Anglican (1662 BCP)',                          'ang1662',  0),
  ('Anglican (1928 BCP)',                          'ang1928',  1),
  ('Lutheran (Common Service 1888)',               'luth1888', 2),
  ('Roman Catholic (Traditional)',                 'rcat',     3),
  ('Ecumenical/Generic',                           'ecum',     4),
  ('Roman Catholic (Novus Ordo)',                  'rcat_no',  5),
  ('Episcopal (TEC, 1979 BCP)',                    'epis1979', 6),
  ('Anglican Church in North America (2019 BCP)', 'acna2019', 7),
  ('Lutheran (ELCA / Revised Common Lectionary)', 'elca',     8),
  ('Eastern Orthodox',                             'orth',     9);

INSERT IGNORE INTO liturgical_seasons (name, tradition_id, sort_order, liturgical_color) VALUES
  ('Advent',                NULL, 0,  'purple'),
  ('Christmas',             NULL, 1,  'white'),
  ('Epiphany',              NULL, 2,  'white'),
  ('Lent',                  NULL, 3,  'purple'),
  ('Holy Week',             NULL, 4,  'red'),
  ('Easter',                NULL, 5,  'white'),
  ('Ascension',             NULL, 6,  'white'),
  ('Pentecost',             NULL, 7,  'red'),
  ('Trinity/Ordinary Time', NULL, 8,  'green'),
  ('All Saints',            NULL, 9,  'white');

-- -----------------------------------------------------------------------
-- Tradition calendar configuration
-- -----------------------------------------------------------------------
-- ang1662: western Easter, 1662 lectionary, Trinity season schema
INSERT IGNORE INTO tradition_calendar_config (tradition_id, easter_algorithm, lectionary_type, season_schema, sunday_cycle_len, weekday_cycle_len, transfers_to_sunday)
SELECT t.id, 'western', 'bcp1662', 'trinity', 1, 1, FALSE FROM traditions t WHERE t.short_code = 'ang1662';

-- ang1928: western Easter, 1928 BCP lectionary, Trinity season schema
INSERT IGNORE INTO tradition_calendar_config (tradition_id, easter_algorithm, lectionary_type, season_schema, sunday_cycle_len, weekday_cycle_len, transfers_to_sunday)
SELECT t.id, 'western', 'bcp1662', 'trinity', 1, 1, FALSE FROM traditions t WHERE t.short_code = 'ang1928';

-- luth1888: western Easter, no formal lectionary, Trinity season schema
INSERT IGNORE INTO tradition_calendar_config (tradition_id, easter_algorithm, lectionary_type, season_schema, sunday_cycle_len, weekday_cycle_len, transfers_to_sunday)
SELECT t.id, 'western', 'none', 'trinity', 1, 1, FALSE FROM traditions t WHERE t.short_code = 'luth1888';

-- rcat (Traditional/EF): western Easter, Tridentine lectionary, Trinity season schema
INSERT IGNORE INTO tradition_calendar_config (tradition_id, easter_algorithm, lectionary_type, season_schema, sunday_cycle_len, weekday_cycle_len, transfers_to_sunday)
SELECT t.id, 'western', 'tridentine', 'trinity', 1, 1, FALSE FROM traditions t WHERE t.short_code = 'rcat';

-- ecum: western Easter, no lectionary
INSERT IGNORE INTO tradition_calendar_config (tradition_id, easter_algorithm, lectionary_type, season_schema, sunday_cycle_len, weekday_cycle_len, transfers_to_sunday)
SELECT t.id, 'western', 'none', 'ordinary_time', 1, 1, FALSE FROM traditions t WHERE t.short_code = 'ecum';

-- rcat_no (Novus Ordo): western Easter, RCL-style 3-yr Sunday / 2-yr weekday, Ordinary Time
INSERT IGNORE INTO tradition_calendar_config (tradition_id, easter_algorithm, lectionary_type, season_schema, sunday_cycle_len, weekday_cycle_len, transfers_to_sunday)
SELECT t.id, 'western', 'rcl', 'ordinary_time', 3, 2, FALSE FROM traditions t WHERE t.short_code = 'rcat_no';

-- epis1979: western Easter, RCL 3-yr, Ordinary Time, Ascension transfers to Sunday
INSERT IGNORE INTO tradition_calendar_config (tradition_id, easter_algorithm, lectionary_type, season_schema, sunday_cycle_len, weekday_cycle_len, transfers_to_sunday)
SELECT t.id, 'western', 'bcp1979', 'ordinary_time', 3, 1, TRUE FROM traditions t WHERE t.short_code = 'epis1979';

-- acna2019: western Easter, RCL 3-yr, Ordinary Time, Ascension transfers to Sunday
INSERT IGNORE INTO tradition_calendar_config (tradition_id, easter_algorithm, lectionary_type, season_schema, sunday_cycle_len, weekday_cycle_len, transfers_to_sunday)
SELECT t.id, 'western', 'rcl', 'ordinary_time', 3, 1, TRUE FROM traditions t WHERE t.short_code = 'acna2019';

-- elca: western Easter, RCL 3-yr, Ordinary Time, Ascension transfers to Sunday
INSERT IGNORE INTO tradition_calendar_config (tradition_id, easter_algorithm, lectionary_type, season_schema, sunday_cycle_len, weekday_cycle_len, transfers_to_sunday)
SELECT t.id, 'western', 'rcl', 'ordinary_time', 3, 1, TRUE FROM traditions t WHERE t.short_code = 'elca';

-- orth: eastern Easter, Orthodox lectionary, Orthodox season schema
INSERT IGNORE INTO tradition_calendar_config (tradition_id, easter_algorithm, lectionary_type, season_schema, sunday_cycle_len, weekday_cycle_len, transfers_to_sunday)
SELECT t.id, 'eastern', 'orthodox', 'orthodox', 1, 1, FALSE FROM traditions t WHERE t.short_code = 'orth';

-- -----------------------------------------------------------------------
-- Feast days seed data
-- -----------------------------------------------------------------------
-- Universal Western moveable feasts (tradition_id = NULL means all Western traditions)
INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, easter_offset, liturgical_color, notes) VALUES
  (NULL, 'Ash Wednesday',        'Ash Wed',     'solemnity',       TRUE, -46, 'purple', 'Beginning of Lent'),
  (NULL, 'Palm Sunday',          'Palm Sun',    'solemnity',       TRUE, -7,  'red',    'Sunday of the Passion'),
  (NULL, 'Maundy Thursday',      'Maundy Thu',  'solemnity',       TRUE, -3,  'white',  'Holy Thursday; commemoration of the Last Supper'),
  (NULL, 'Good Friday',          'Good Fri',    'solemnity',       TRUE, -2,  'red',    'Commemoration of the Crucifixion'),
  (NULL, 'Holy Saturday',        'Holy Sat',    'solemnity',       TRUE, -1,  'white',  'Easter Vigil'),
  (NULL, 'Easter Sunday',        'Easter',      'solemnity',       TRUE,  0,  'white',  'The Resurrection of Our Lord'),
  (NULL, 'Ascension Thursday',   'Ascension',   'solemnity',       TRUE, 39,  'white',  'The Ascension of Our Lord; some traditions transfer to Sunday (+42)'),
  (NULL, 'Pentecost Sunday',     'Pentecost',   'solemnity',       TRUE, 49,  'red',    'The Day of Pentecost'),
  (NULL, 'Trinity Sunday',       'Trinity',     'solemnity',       TRUE, 56,  'white',  'The Sunday after Pentecost');

-- Roman Catholic (Novus Ordo) additional moveable feasts
INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, easter_offset, liturgical_color, notes)
SELECT t.id, 'Corpus Christi', 'Corpus Christi', 'solemnity', TRUE, 60, 'white', 'The Most Holy Body and Blood of Christ; Thursday after Trinity Sunday'
FROM traditions t WHERE t.short_code = 'rcat_no';

INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, easter_offset, liturgical_color, notes)
SELECT t.id, 'Sacred Heart of Jesus', 'Sacred Heart', 'solemnity', TRUE, 68, 'white', 'Friday after the Second Sunday after Pentecost'
FROM traditions t WHERE t.short_code = 'rcat_no';

-- Universal Western fixed feasts (NULL = all Western traditions)
INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, fixed_month, fixed_day, liturgical_color) VALUES
  (NULL, 'Christmas Day',              'Christmas',    'solemnity',         FALSE, 12, 25, 'white'),
  (NULL, 'Epiphany',                   'Epiphany',     'solemnity',         FALSE,  1,  6, 'white'),
  (NULL, 'The Annunciation',           'Annunciation', 'feast',             FALSE,  3, 25, 'white'),
  (NULL, 'All Saints Day',             'All Saints',   'solemnity',         FALSE, 11,  1, 'white'),
  (NULL, 'Christmas Eve',              'Christmas Eve','principal_feast',   FALSE, 12, 24, 'white');

-- Roman Catholic (Novus Ordo) fixed feasts
INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, fixed_month, fixed_day, liturgical_color, notes)
SELECT t.id, 'Solemnity of Mary', 'Mary, Mother of God', 'solemnity', FALSE, 1, 1, 'white', 'January 1 — New Year''s Day (Catholic)'
FROM traditions t WHERE t.short_code = 'rcat_no';

INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, fixed_month, fixed_day, liturgical_color, notes)
SELECT t.id, 'All Souls Day', 'All Souls', 'commemoration', FALSE, 11, 2, 'purple', 'Commemoration of all the faithful departed'
FROM traditions t WHERE t.short_code = 'rcat_no';

INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, fixed_month, fixed_day, liturgical_color, notes)
SELECT t.id, 'Immaculate Conception', 'Immac. Conception', 'solemnity', FALSE, 12, 8, 'white', 'Patronal feast of the USA'
FROM traditions t WHERE t.short_code = 'rcat_no';

INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, fixed_month, fixed_day, liturgical_color, notes)
SELECT t.id, 'Assumption of Mary', 'Assumption', 'solemnity', FALSE, 8, 15, 'white', 'The Assumption of the Blessed Virgin Mary'
FROM traditions t WHERE t.short_code = 'rcat_no';

-- Traditional Catholic (EF) additional feasts
INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, fixed_month, fixed_day, liturgical_color, notes)
SELECT t.id, 'Corpus Christi (Traditional)', 'Corpus Christi', 'solemnity', TRUE, NULL, NULL, 'white', 'Thursday after Trinity Sunday (EF)'
FROM traditions t WHERE t.short_code = 'rcat';

-- Update the easter_offset for Traditional Corpus Christi (workaround using UPDATE since INSERT IGNORE won't help)
UPDATE feast_days SET easter_offset = 60
WHERE name = 'Corpus Christi (Traditional)' AND tradition_id = (SELECT id FROM traditions WHERE short_code = 'rcat');

-- Eastern Orthodox fixed feasts (stored as Gregorian-equivalent dates for display; Julian dates are +13 days behind)
INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, fixed_month, fixed_day, liturgical_color, notes)
SELECT t.id, 'Theophany (Epiphany)', 'Theophany', 'solemnity', FALSE, 1, 19, 'white', 'Julian Jan 6 = Gregorian Jan 19 (current century)'
FROM traditions t WHERE t.short_code = 'orth';

INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, fixed_month, fixed_day, liturgical_color, notes)
SELECT t.id, 'Transfiguration of Christ', 'Transfiguration', 'solemnity', FALSE, 8, 19, 'white', 'Julian Aug 6 = Gregorian Aug 19'
FROM traditions t WHERE t.short_code = 'orth';

INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, fixed_month, fixed_day, liturgical_color, notes)
SELECT t.id, 'Dormition of the Theotokos', 'Dormition', 'solemnity', FALSE, 8, 28, 'blue', 'Julian Aug 15 = Gregorian Aug 28'
FROM traditions t WHERE t.short_code = 'orth';

INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, fixed_month, fixed_day, liturgical_color, notes)
SELECT t.id, 'Nativity of Christ', 'Orthodox Christmas', 'solemnity', FALSE, 1, 7, 'white', 'Julian Dec 25 = Gregorian Jan 7'
FROM traditions t WHERE t.short_code = 'orth';

INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, easter_offset, liturgical_color, notes)
SELECT t.id, 'Orthodox Pascha (Easter)', 'Pascha', 'solemnity', TRUE, 0, 'white', 'Calculated using Julian calendar algorithm'
FROM traditions t WHERE t.short_code = 'orth';

-- Orthodox: Pentecost (Troitsa)
INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, easter_offset, liturgical_color, notes)
SELECT t.id, 'Pentecost (Trinity Day)', 'Troitsa', 'solemnity', TRUE, 49, 'white', 'Orthodox Pentecost / Trinity Sunday'
FROM traditions t WHERE t.short_code = 'orth';

-- Anglican/Episcopal additional feasts
INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, fixed_month, fixed_day, liturgical_color, notes)
SELECT t.id, 'All Saints Day', 'All Saints', 'principal_feast', FALSE, 11, 1, 'white', 'One of the Seven Principal Feasts (1979 BCP)'
FROM traditions t WHERE t.short_code = 'epis1979';

INSERT IGNORE INTO feast_days (tradition_id, name, short_name, rank, is_moveable, fixed_month, fixed_day, liturgical_color, notes)
SELECT t.id, 'All Saints Day', 'All Saints', 'principal_feast', FALSE, 11, 1, 'white', 'One of the Principal Feasts (ACNA 2019)'
FROM traditions t WHERE t.short_code = 'acna2019';

-- -----------------------------------------------------------------------
-- Orthodox-specific seasons (inserted after traditions seed so we can reference orth tradition)
INSERT IGNORE INTO liturgical_seasons (name, tradition_id, sort_order, liturgical_color)
SELECT 'Great Lent',     t.id, 10, 'purple' FROM traditions t WHERE t.short_code = 'orth';
INSERT IGNORE INTO liturgical_seasons (name, tradition_id, sort_order, liturgical_color)
SELECT 'Bright Week',    t.id, 11, 'white'  FROM traditions t WHERE t.short_code = 'orth';
INSERT IGNORE INTO liturgical_seasons (name, tradition_id, sort_order, liturgical_color)
SELECT 'Paschal Season', t.id, 12, 'white'  FROM traditions t WHERE t.short_code = 'orth';
INSERT IGNORE INTO liturgical_seasons (name, tradition_id, sort_order, liturgical_color)
SELECT 'Apostles Fast',  t.id, 13, 'green'  FROM traditions t WHERE t.short_code = 'orth';
INSERT IGNORE INTO liturgical_seasons (name, tradition_id, sort_order, liturgical_color)
SELECT 'Dormition Fast', t.id, 14, 'blue'   FROM traditions t WHERE t.short_code = 'orth';
INSERT IGNORE INTO liturgical_seasons (name, tradition_id, sort_order, liturgical_color)
SELECT 'Nativity Fast',  t.id, 15, 'purple' FROM traditions t WHERE t.short_code = 'orth';
INSERT IGNORE INTO liturgical_seasons (name, tradition_id, sort_order, liturgical_color)
SELECT 'Theophany',      t.id, 16, 'white'  FROM traditions t WHERE t.short_code = 'orth';

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

-- Liturgical elements (1662 BCP, public domain)
-- Using a subselect for element_type_id to keep the insert readable.

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'Collect for Purity', et.id,
'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.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'collect';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'Collect for Grace', et.id,
'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.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'collect';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'Collect for Peace', et.id,
'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.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'collect';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'Collect for the First Sunday in Advent', et.id,
'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.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'collect';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'Collect for Christmas Day', et.id,
'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.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'collect';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'Collect for Easter Day', et.id,
'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.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'collect';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'Collect for Whitsunday', et.id,
'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.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'collect';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'Prayer of Humble Access', et.id,
'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.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'prayer_of_humble_access';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'Prayer of Consecration', et.id,
'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.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'prayer_of_consecration';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'Gloria in Excelsis', et.id,
'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.

O 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.

For 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.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'gloria';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'Sanctus', et.id,
'HOLY, holy, holy, Lord God of hosts, heaven and earth are full of thy glory: Glory be to thee, O Lord most High.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'sanctus';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'The Comfortable Words', et.id,
'Hear what comfortable words our Saviour Christ saith unto all that truly turn to him.

Come unto me, all ye that travail and are heavy laden, and I will refresh you. (Matthew 11:28)

So 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)

Hear 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)

Hear 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)',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'comfortable_words';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'Nicene Creed', et.id,
'I BELIEVE in one God the Father Almighty, Maker of heaven and earth, And of all things visible and invisible:

And 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.

And 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.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'creed';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'Apostles'' Creed', et.id,
'I BELIEVE in God the Father Almighty, Maker of heaven and earth:

And 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.

I 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.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'creed';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'Post-Communion Thanksgiving', et.id,
'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.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'post_communion_prayer';

INSERT IGNORE INTO liturgical_elements (title, element_type_id, text_body, source, copyright_status)
SELECT 'The Peace of God (Blessing)', et.id,
'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.',
'1662 BCP', 'public_domain' FROM element_types et WHERE et.slug = 'blessing';
