SET NAMES utf8mb4;
SET time_zone = '+00:00';

SET @programme_catalog_actor = (
    SELECT ur.user_id
    FROM user_roles ur
    INNER JOIN roles r ON r.id = ur.role_id
    WHERE r.role_key = 'super_admin' AND r.is_active = 1
    ORDER BY ur.user_id
    LIMIT 1
);

DROP TEMPORARY TABLE IF EXISTS tmp_divinity_programme_courses;
CREATE TEMPORARY TABLE tmp_divinity_programme_courses (
    programme_code VARCHAR(32) NOT NULL,
    course_code VARCHAR(32) NOT NULL,
    course_name VARCHAR(180) NOT NULL,
    credits TINYINT UNSIGNED NOT NULL,
    is_required TINYINT(1) NOT NULL,
    description VARCHAR(4000) NOT NULL,
    PRIMARY KEY (programme_code, course_code)
) ENGINE=MEMORY;

INSERT INTO tmp_divinity_programme_courses
    (programme_code, course_code, course_name, credits, is_required, description)
VALUES
    ('DI-PPM', 'CPPM103', 'Old Testament Survey', 3, 1, 'Survey the Old Testament''s historical setting, literary forms and theological themes, and consider how its books contribute to the Bible''s wider story.'),
    ('DI-PPM', 'SIMU120', 'Exploring Musical Composition', 3, 1, 'Learn foundational composition techniques and use musical elements to shape and develop original ideas.'),
    ('DI-PPM', 'SIMU130', 'Working in a Musical Ensemble', 3, 1, 'Build collaborative musicianship and ensemble performance skills through practical group work.'),
    ('DI-PPM', 'SIMU140', 'Developing as a Musical Performer', 3, 1, 'Develop performance presence and professional practice, including style, wellbeing, movement and audience engagement.'),
    ('DI-PPM', 'SIMU150', 'Rehearsal Techniques for Musicians', 3, 1, 'Practice preparation and rehearsal methods that support efficient, focused musical performances.'),
    ('DI-PPM', 'SIMU160', 'Exploring Musical Improvisation', 3, 1, 'Explore improvisation by developing musical responses, listening to others and experimenting with creative performance.'),
    ('DI-PPM', 'SIMU170', 'Styles and Roles of Church Music Traditions', 3, 1, 'Study church music across cultures, including African influences, and develop understanding through listening and performance.'),
    ('DI-PPM', 'SIMU180', 'Developing Music Theory', 3, 1, 'Build essential music-theory knowledge and apply it to practical musical work.'),

    ('DI-ACD', 'CDIV101', 'Introduction to Christian Theology I', 3, 1, 'Survey foundational Christian beliefs about God, creation, sin, spiritual beings, the Holy Spirit and the identity and work of Jesus Christ.'),
    ('DI-ACD', 'CDIV102', 'Introduction to Christian Theology II', 3, 1, 'Introduce Christian teaching on sin, salvation and the final destiny of humanity and the world.'),
    ('DI-ACD', 'CDIV103', 'Introduction to the Old Testament', 3, 1, 'Study the formation and divisions of the Old Testament, interpretive traditions and the scriptures'' use in African independent churches.'),
    ('DI-ACD', 'CDIV104', 'Introduction to the New Testament', 3, 1, 'Examine how the New Testament took shape, along with questions of authorship, historical setting, literary form and the message of its books.'),
    ('DI-ACD', 'CDIV105', 'Mission & Evangelism', 3, 1, 'Consider contemporary mission and evangelism challenges and develop approaches for sharing the gospel in changing cultural and community settings.'),
    ('DI-ACD', 'CDIV106', 'Pastoral Ministry', 3, 1, 'Develop practical pastoral skills for preaching, worship, life events, visitation, counseling and sustaining congregational spiritual growth.'),
    ('DI-ACD', 'CDIV107', 'Spiritual Formation', 3, 1, 'Explore prayer and spiritual formation, using biblical examples to connect personal growth with service and pastoral leadership.'),
    ('DI-ACD', 'CDIV108', 'Pastoral Counselling', 3, 1, 'Introduce biblical approaches to counseling, helping skills, community resources and support for a range of pastoral concerns.'),
    ('DI-ACD', 'CDIV109', 'ICGC Distinctives', 3, 1, 'Study the history, identity, beliefs, governance, worship and organizational life of the International Central Gospel Church.'),
    ('DI-ACD', 'CDIV112', 'Homiletics', 3, 1, 'Study preaching in pastoral and worship settings, then practice sermon delivery and use feedback to improve.'),

    ('DI-MASM', 'MASM801', 'Introduction to Christian Theology I', 3, 1, 'Survey foundational Christian beliefs about God, creation, sin, spiritual beings, the Holy Spirit and the identity and work of Jesus Christ.'),
    ('DI-MASM', 'MASM802', 'Introduction to Christian Theology II', 3, 1, 'Introduce Christian teaching on sin, salvation and the final destiny of humanity and the world.'),
    ('DI-MASM', 'MASM803', 'Introduction to the Old Testament', 3, 1, 'Study the formation and divisions of the Old Testament, interpretive traditions and the scriptures'' use in African independent churches.'),
    ('DI-MASM', 'MASM804', 'Introduction to the New Testament', 3, 1, 'Examine how the New Testament took shape, its literary questions and the authorship, setting and message of its books.'),
    ('DI-MASM', 'MASM805', 'Biblical Interpretation', 3, 1, 'Explore interpretive approaches to biblical texts, including historical and theological context, literary form and relationships between the Testaments.'),
    ('DI-MASM', 'MASM806', 'Homiletics', 3, 1, 'Study the theological and pastoral dimensions of preaching and practice preparing and delivering sermons.'),
    ('DI-MASM', 'MASM807', 'Pastoral Ministry', 3, 1, 'Develop pastoral leadership and ministry skills for congregational life, worship, counseling and care.'),
    ('DI-MASM', 'MASM808', 'Introduction to the History of Christianity', 3, 1, 'Trace Christian origins and the church''s development in its cultural, intellectual and political settings.'),
    ('DI-MASM', 'MASM809', 'Africana Studies: Socio-Theological Perspectives', 3, 1, 'Examine Christian belief and practice among African-descended communities and their responses to historical and contemporary social conditions.'),
    ('DI-MASM', 'MASM811', 'Spiritual Formation I', 1, 1, 'Survey biblical, theological and historical approaches to prayer, devotional life and spiritual growth.'),
    ('DI-MASM', 'MASM812', 'Christian Counselling', 3, 1, 'Introduce biblical counseling concepts, appropriate helping practices and community resources for responding to pastoral concerns.'),
    ('DI-MASM', 'MASM813', 'Beginners New Testament Greek Grammar', 3, 0, 'Learn the alphabet, vocabulary and basic morphology and grammar needed to begin reading New Testament Greek.'),
    ('DI-MASM', 'MASM814', 'Spiritual Formation II', 1, 1, 'Explore Christian spiritual practices that nurture lifelong growth and faithful ministry.'),
    ('DI-MASM', 'MASM815', 'Beginners Biblical Hebrew', 3, 0, 'Introduce biblical Hebrew sounds, word forms and sentence structure.'),
    ('DI-MASM', 'MASM816', 'Intermediate New Testament Greek Grammar', 3, 0, 'Extend introductory Greek grammar and apply it through guided reading and translation of New Testament passages.'),
    ('DI-MASM', 'MASM817', 'Comparative Studies in World Missions', 3, 0, 'Examine Christian mission in a multi-faith world through comparative study of mission questions, contexts and strategies.'),
    ('DI-MASM', 'MASM818', 'Intermediate Biblical Hebrew', 3, 0, 'Continue Hebrew grammar study and apply the language to selected passages from the Old Testament.'),
    ('DI-MASM', 'MASM819', 'History of Revivals', 3, 1, 'Study modern Christian revivals and evangelistic movements, their distinctive features, causes and effects.'),
    ('DI-MASM', 'MASM821', 'Strategic Church Planting', 3, 1, 'Explore approaches to establishing sustainable churches, with attention to urban contexts and congregational growth.'),
    ('DI-MASM', 'MASM822', 'Studies in Pastoral Epistles', 3, 0, 'Study 1 and 2 Timothy and Titus with attention to interpretation, theology and their practical implications for ministry.'),
    ('DI-MASM', 'MASM823', 'Church Financial Management', 3, 1, 'Learn church finance practices such as budgeting, record keeping, bank reconciliation, asset oversight and auditing.'),
    ('DI-MASM', 'MASM824', 'World Religions', 3, 0, 'Compare major world religions through their beliefs, histories, texts and practices.'),
    ('DI-MASM', 'MASM825', 'Spiritual Formation III', 1, 1, 'Connect theological reflection with practices and character development for sustained spiritual life and ministry.'),
    ('DI-MASM', 'MASM826', 'Theological Research Methods', 0, 0, 'Develop practical competence in research design and social-science methods for theological inquiry.'),
    ('DI-MASM', 'MASM827', 'Cyber Theology', 3, 0, 'Examine how digital culture and online communication shape contemporary Christian belief and practice.'),
    ('DI-MASM', 'MASM829', 'Church and the Law', 3, 0, 'Review Ghanaian law relevant to churches and Christian ministry, including practical responsibilities for church leaders.'),
    ('DI-MASM', 'MASM830', 'Supervised Ministry Internship', 3, 0, 'Apply ministry learning through practical experience in a supervised internship.'),
    ('DI-MASM', 'MASM831', 'African Philosophy & Religious Traditions', 3, 0, 'Explore African views of reality, religious thought, cultural values, institutions and ritual practice.'),
    ('DI-MASM', 'MASM833', 'Twentieth Century Christian Thought', 3, 0, 'Study influential twentieth-century Christian theological movements, thinkers and philosophical debates.'),
    ('DI-MASM', 'MASM835', 'Christian Thought and Secular Worldviews', 3, 0, 'Develop a Christian perspective on reality and compare it with secular, New Age and postmodern worldviews.'),
    ('DI-MASM', 'MASM837', 'Christian Community Development', 3, 0, 'Introduce community-development skills that strengthen local capacity and support pastoral and community work.'),
    ('DI-MASM', 'MASM839', 'ICGC History, Theology & Polity', 3, 0, 'Survey the history, governance, convictions and practices that shape theology and church life within ICGC.'),
    ('DI-MASM', 'MASM841', 'Healthy Lifestyle for the Clergy', 3, 0, 'Consider habits and personal development that support clergy members'' physical, mental, emotional and social wellbeing.'),
    ('DI-MASM', 'MASM850', 'MA Thesis', 6, 0, 'Complete a substantial independent research project for the Master of Arts in Sacred Ministry.'),

    ('DI-CPM', 'DCGE110', 'Spiritual Formation', 3, 1, 'Explore spiritual formation, Christian character and the ways believers and ministry leaders can grow in faith and support others'' spiritual development.'),
    ('DI-CPM', 'DCGE120', 'The Doctrine of Jesus', 3, 1, 'Study biblical, historical and theological understandings of Jesus, including his incarnation, ministry, death, resurrection and significance for Christian mission.'),
    ('DI-CPM', 'DCGE130', 'Christian Ethics', 3, 1, 'Examine the sources of Christian moral reasoning and apply them to contemporary ethical concerns.'),
    ('DI-CPM', 'DCGE140', 'Bible Survey', 3, 1, 'Survey selected Old and New Testament books in historical context and consider how they contribute to the Bible''s larger story.'),
    ('DI-CPM', 'DCEL110', 'Team leadership', 3, 1, 'Study biblical approaches to team leadership, self-awareness, teamwork, servant leadership and conflict resolution.'),
    ('DI-CPM', 'DCEL120', 'Church Growth and Organizational Strategies', 3, 1, 'Explore biblical and practical approaches to church growth, organizational planning and pastoral leadership.'),
    ('DI-CPM', 'DCEL130', 'Introduction to Christian Counselling', 3, 0, 'Introduce counseling from a Christian perspective, including how psychological and theological approaches can inform basic helping skills.'),
    ('DI-CPM', 'DCEL140', 'Contemporary Issues in Counselling', 3, 0, 'Consider current counseling concerns through a biopsychosocial and spiritual lens and develop basic ways to address them.'),
    ('DI-CPM', 'DCEL150', 'The Leadership Principles of Jesus', 3, 0, 'Explore Jesus'' approach to developing leaders and consider how apprenticeship can shape a student''s own leadership practice.'),
    ('DI-CPM', 'DCEL160', 'Introduction to Missions', 3, 0, 'Study the practical work of entering a community, sharing the gospel and nurturing a local church toward sustainable ministry.');

DELETE pc
FROM programme_courses pc
INNER JOIN programmes p ON p.id = pc.programme_id
INNER JOIN courses c ON c.id = pc.course_id
LEFT JOIN tmp_divinity_programme_courses source_course
    ON source_course.programme_code = p.programme_code
   AND source_course.course_code = c.course_code
WHERE p.programme_code = 'DI-CPM'
  AND source_course.course_code IS NULL;
SET @programme_links_removed = ROW_COUNT();

INSERT IGNORE INTO courses
    (department_id, course_code, course_name, credits, academic_level, description, created_by, created_at, updated_at)
SELECT d.id, source_course.course_code, source_course.course_name, source_course.credits, NULL,
       source_course.description, @programme_catalog_actor, UTC_TIMESTAMP(), UTC_TIMESTAMP()
FROM departments d
CROSS JOIN tmp_divinity_programme_courses source_course
WHERE d.department_code = 'DIV';
SET @programme_courses_added = ROW_COUNT();

UPDATE courses c
INNER JOIN departments d ON d.id = c.department_id AND d.department_code = 'DIV'
INNER JOIN tmp_divinity_programme_courses source_course
    ON source_course.course_code = c.course_code
   AND source_course.course_name = c.course_name
   AND source_course.credits = c.credits
SET c.description = source_course.description,
    c.updated_at = UTC_TIMESTAMP()
WHERE c.description IS NULL
   OR c.description = ''
   OR c.description LIKE 'Official Daniel Institute certificate curriculum:%'
   OR c.description = 'Bachelor of Divinity curriculum supplied in the programme structure screenshots. Catalogue level was not specified.';
SET @programme_descriptions_updated = ROW_COUNT();

INSERT IGNORE INTO programme_courses (programme_id, course_id, is_required, created_by, created_at)
SELECT p.id, c.id, source_course.is_required, @programme_catalog_actor, UTC_TIMESTAMP()
FROM programmes p
INNER JOIN departments d ON d.id = p.department_id AND d.department_code = 'DIV'
INNER JOIN tmp_divinity_programme_courses source_course ON source_course.programme_code = p.programme_code
INNER JOIN courses c
    ON c.department_id = d.id
   AND c.course_code = source_course.course_code
   AND c.course_name = source_course.course_name
   AND c.credits = source_course.credits
WHERE p.programme_code IN ('DI-PPM', 'DI-ACD', 'DI-MASM', 'DI-CPM');
SET @programme_links_added = ROW_COUNT();

INSERT INTO audit_logs (user_id, action, entity_type, entity_id, changes_json, created_at)
SELECT @programme_catalog_actor, 'academic.additional_divinity_programmes_imported', 'programme', CAST(p.id AS CHAR),
       JSON_OBJECT('source', 'published Daniel Institute programme structure pages',
                   'course_records_added', @programme_courses_added,
                   'course_descriptions_updated', @programme_descriptions_updated,
                   'programme_links_added', @programme_links_added,
                   'obsolete_pastoral_ministry_links_removed', @programme_links_removed,
                   'catalogue_levels_assigned', FALSE,
                   'descriptions_paraphrased', TRUE),
       UTC_TIMESTAMP()
FROM programmes p
WHERE p.programme_code IN ('DI-PPM', 'DI-ACD', 'DI-MASM', 'DI-CPM')
  AND (@programme_courses_added > 0
    OR @programme_descriptions_updated > 0
    OR @programme_links_added > 0
    OR @programme_links_removed > 0);

DROP TEMPORARY TABLE tmp_divinity_programme_courses;